Как суммировать отдельные ячейки в столбце в excel

покупка

Как суммировать только отфильтрованные или видимые ячейки в Excel?

Возможно, вам легко суммировать столбец чисел в Excel, но иногда вам нужно отфильтровать или скрыть некоторые данные, чтобы они соответствовали вашим критериям. После скрытия или фильтрации, и теперь вы просто хотите добавить только отфильтрованные или видимые значения. Если вы примените функцию Sum в Excel, все значения, включая скрытые данные, будут добавлены, в этом случае, как вы могли суммировать только отфильтрованные или видимые значения ячеек в Excel?

• Суммировать только отфильтрованные или видимые значения ячеек с помощью формулы
• Суммируйте только отфильтрованные или видимые значения ячеек с помощью функции, определяемой пользователем
• Сумма/счет/усреднение только отфильтрованных или видимых ячеек с Kutools for Excel

Суммировать только отфильтрованные или видимые значения ячеек с помощью формулы

С этой SUBTOTAL функция, которая игнорирует строки, которые были исключены фильтром, вы можете легко добавить только видимые ячейки. Вы можете сделать так:

Предположим, у вас есть диапазон данных, и они были отфильтрованы по мере необходимости, см. Снимок экрана:

1. В пустой ячейке, например, C13, введите следующую формулу: = Итого (109; C2: C12) (109 указывает на то, что при суммировании чисел скрытые значения игнорируются; C2: C12 — диапазон, который вы суммируете, игнорируя отфильтрованные строки.), и нажмите Enter .

Примечание: Эта формула также может помочь вам суммировать только видимые ячейки, если на вашем листе есть скрытые строки. Однако эта формула не может суммироваться с игнорированием ячеек в скрытых столбцах.

Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов

Обычно функция SUM/Count/Average подсчитывает все ячейки в указанном диапазоне независимо от того, скрыты/отфильтрованы ячейки или нет. В то время как функция промежуточного итога может только суммировать/подсчитывать/усреднять, игнорируя скрытые строки. Однако, Kutools for Excel СУЩЕСТВЕННЫЙ / СОВМЕСТНЫЙ / СРЕДНЯЯВИДИМАЯ функции легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы.

Получить 30 -дневная полнофункциональная бесплатная пробная версия прямо сейчас!

Суммируйте только отфильтрованные или видимые значения ячеек с помощью функции, определяемой пользователем

Если вас интересует следующий код, он также может помочь вам суммировать только видимые ячейки.

1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Function SumVisible(WorkRng As Range) As Double 'Update 20130907 Dim rng As Range Dim total As Double For Each rng In WorkRng If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then total = total + rng.Value End If Next SumVisible = total End Function 

3. Сохраните этот код и введите формулу = SumVisible (C2: C12) в пустую ячейку. Смотрите скриншот:

4, Затем нажмите Enter key и вы получите желаемый результат.

Сумма/счет/усреднение только отфильтрованных или видимых ячеек с Kutools for Excel

Если у вас есть Kutools for Excel установлен, вы можете быстро вычислить сумму / количество / среднее только видимые или отфильтрованные ячейки в Excel легко.

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

Например, вы хотите суммировать только видимые ячейки, выберите ячейку, в которую вы поместите результат суммирования, введите формулу = СУММВИДИМ (C3: C12) (C3: C13 — это диапазон, в котором вы суммируете только видимые ячейки) в него и нажмите Enter .

И тогда результат суммирования вычисляется без учета всех скрытых ячеек. Смотрите скриншот:

Для подсчета только видимых ячеек примените эту формулу = СОВМЕСТНЫЙ (C3: C12) ; Для усреднения только видимых ячеек примените эту формулу = СРЕДНЕВИДИМОЕ (C3: C12) .

Примечание: Если вы не можете точно запомнить формулы, вы можете выполнить следующие шаги, чтобы легко суммировать / подсчитывать / усреднять только видимые ячейки:

1. Выберите ячейку, в которую вы поместите результат суммирования, и нажмите Кутулс > функции > Статистические и математические > СУЩЕСТВЕННЫЙ (или СРЕДНИЙ ВИДИМОЙ, СОВМЕСТНЫЙ как вам нужно). Смотрите скриншот:

2. В открывшемся диалоговом окне «Аргументы функций» укажите диапазон, в котором вы будете суммировать, игнорируя скрытые ячейки, и нажмите кнопку OK кнопка. Смотрите скриншот:

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

И тогда результат суммирования вычисляется без учета всех скрытых ячеек.

Демонстрация: сумма / подсчет / среднее только отфильтрованные или видимые ячейки

Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните свой 30 Бесплатная пробная версия на день без каких-либо ограничений сегодня. Скачать

Легко суммировать / подсчитывать только отфильтрованные / видимые ячейки, удаляя скрытые строки в Excel

При суммировании/подсчете отфильтрованных ячеек в Excel функция СУММ или функция подсчета не будут игнорировать скрытые ячейки. Если скрытые/отфильтрованные строки удалены, мы можем легко суммировать или подсчитывать только видимые ячейки. Можешь попробовать Kutools for Excel’s Удалить скрытые (видимые) строки и столбцы утилита для ее решения.

покупка

Как суммировать ячейки с текстом и числами в Excel?

Например, у меня есть список значений, содержащих числовые и текстовые строки, теперь я хочу суммировать только числа на основе одного и того же текста, посмотрите на следующий снимок экрана. Обычно вы не можете напрямую суммировать значения в списке с помощью текстовой строки, здесь я представлю вам несколько формул для решения этой задачи.

Суммировать ячейки, содержащие текст и числа на основе определенного текста с формулой

Вы можете использовать формулу массива для суммирования чисел на основе соответствующей текстовой строки в ячейке, сделайте следующее:

1. Сначала вы можете записать свои текстовые строки, в которых вы хотите просуммировать относительные числа в ячейках столбца.

2. Затем введите формулу ниже в пустой ячейке, в этом примере я введу формулу в ячейку E2, а затем нажмите Ctrl + Shift + Enter вместе, он вернет общее значение из списка на основе определенного текста. Смотрите скриншот:

=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))

Внимание: В приведенной выше формуле D2 это критерий, который вы хотите использовать, и B2: B9 указывает диапазон ячеек, который вы хотите суммировать.

3. Затем выберите ячейку формулы и перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, чтобы получить результат:

Суммировать ячейки, содержащие текст и числа на основе определенного текста, с удобной функцией

Если вам сложно понять приведенную выше формулу, Kutools for Excel предоставляет полезный инструмент — Сумма на основе того же текста, с помощью этой функции вы можете получить общий результат в ячейке, смешанной с числами и текстом, не запоминая никаких формул.

Примечание: Чтобы применить эти Сумма на основе того же текста функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите, чтобы выбрать ячейку, в которую вы хотите вывести результат, см. Снимок экрана:

Tips: Чтобы избежать циклических ссылок, не вставляйте эту формулу в первую строку рабочего листа.

2, Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:

3. В Помощник по формулам диалоговом окне выполните следующие действия:

• Выберите Статистический из Тип формулы раскрывающийся список;
• В Выберите формулу список, щелкните, чтобы выбрать Сумма на основе того же текста вариант;
• Затем в Ввод аргументов раздел, выберите диапазон ячеек, содержащий текст и числа, которые вы хотите суммировать в Диапазон текстовое поле, а затем выберите текстовую ячейку, значения которой вы хотите суммировать на основе Текст текстовое окно.

Tips: В Текст текстовое поле, вы должны изменить абсолютную ссылку на ячейку по умолчанию на относительную ссылку на ячейку для правильного перетаскивания формулы.

4. А затем нажмите Ok Кнопка, будет вычислен первый результат, затем перетащите маркер заполнения для заполнения формулы в другие ячейки, см. снимок экрана:

Суммировать ячейки, содержащие текст и числа, игнорируя текст, с помощью функции, определяемой пользователем

Если у вас есть следующие данные, смешанные с текстом и числами, и вы просто хотите извлечь и сложить только числа в каждой ячейке, как вы могли бы быстро закончить это в Excel ?.

Чтобы справиться с этой проблемой, вы можете создать Функция, определяемая пользователем, пожалуйста, сделайте так:

1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: суммируйте только числа в ячейке, содержащей числа и текст

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double 'Updateby Extendoffice Dim xNums As Variant, lngNum As Long xNums = Split(rngS, strDelim) For lngNum = LBound(xNums) To UBound(xNums) Step 1 SumNumbers = SumNumbers + Val(xNums(lngNum)) Next lngNum End Function 

3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите эту формулу = SumNumbers (A2) (A2 указывает ячейку, в которой вы хотите суммировать числа), затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и только числа в каждой ячейке добавляются вместе, см. снимок экрана:

Больше относительных статей:

• Объедините повторяющиеся строки и просуммируйте значения в Excel
• В Excel ， вы всегда можете столкнуться с этой проблемой, если у вас есть диапазон данных, содержащий несколько повторяющихся записей, и теперь вы хотите объединить повторяющиеся данные и суммировать соответствующие значения в другом столбце, как показано на следующих снимках экрана. Как бы вы могли решить эту проблему?

• Умножьте два столбца, а затем просуммируйте в Excel
• В Excel большинство из нас может страдать от умножения двух столбцов, а затем их сложения, конечно, мы можем умножить каждый элемент и затем суммировать их, но это будет проблематично, если нужно вычислить сотни или тысячи строк. В Excel есть мощная функция — СУММПРОИЗВ, с ее помощью мы можем быстро умножить два столбца и затем суммировать их. В этой статье я расскажу о том, как применить эту функцию.

• Суммируйте верхние 3 или N значений на основе критериев в Excel
• Обычно мы можем суммировать верхние n значений из диапазона ячеек, используя простую формулу, но пробовали ли вы когда-нибудь суммировать верхние n значений на основе некоторых критериев. В этой статье я представлю некоторые формулы для суммирования первых n значений с определенными критериями в Excel.

• Суммируйте уникальные значения на основе критериев в Excel
• Например, у меня есть диапазон данных, который содержит столбцы Name и Order, теперь для суммирования только уникальных значений в столбце Order на основе столбца Name, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?

• Подсчет или суммирование ячеек на основе цветов шрифта в Excel
• Как можно узнать количество ячеек или суммировать все ячейки, содержащие шрифт определенного цвета в Excel? Например, у меня есть диапазон данных на листе, как показано на следующем снимке экрана, и теперь я хочу соответственно подсчитать или суммировать ячейки, которые имеют красный, синий и черный цвет шрифта. Обычно нет прямого способа справиться с этой задачей, здесь я расскажу о некоторых хитростях для решения этой задачи.

Лучшие инструменты для офисной работы

Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего.

Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Как просуммировать данные с нескольких листов, в том числе по условию

Достаточно распространенная ситуация: в книге ведутся данные по продажам, разбивая каждый месяц на отдельный лист с именем месяца. Т.е. в листах Январь , Февраль и Март и т.д. расположены данные по продажам, а в ячейке C33 подводится общий итог за месяц. И на отдельном листе необходимо просуммировать все продажи по всем листам. Если структура всех таблиц одинакова (одинаковое кол-во строк, товар может различаться) и Итог расположен во всех таблицах в одной ячейке, то можно воспользоваться простой формулой:
=СУММ(Январь:Март! C33 )
=SUM(Январь:Март!C33)
Как это сделать без указания имен листов вручную:

• вбиваем в итоговую ячейку функцию СУММ (SUM)
• для указания аргументов переходим на первый лист, данные которого необходимо суммировать
• далее зажимаем клавишу Shift и выделяем последний лист(если необходимо выделить несмежные листы – зажимаем клавишу Ctrl )
• указываем адрес ячейки для суммирования и жмем Enter

Подобная ссылка на диапазоны называется трехмерной ссылкой. Если между листом Январь и Март добавить еще какой-нибудь лист — то данные с него будут также автоматически включены в функцию суммирования, приведенную выше. Поэтому необходимо следить, чтобы указывались только нужные листы или диапазон листов ОТ и ДО. Если листов много и суммировать необходимо данные одной ячейки со всех листов, кроме того, в котором сама функция, то можно записать так:
=СУММ(‘*’! C33 )
=SUM(‘*’!C33)
После того, как введем такую функцию в ячейку, Excel сам подставит в правильном виде имена всех листов для суммирования, даже если они идут не по порядку. Т.е. лист суммирования не обязательно должен быть в самом начале книги или в конце — он может быть и в середине — функция его автоматически уберет из суммирования в момент ввода функции.
Звездочка в данном случае является подстановочным символом, который заменяет собой любое количество любых символов. Т.е. в данном случае, как бы говорящим Excel: имя листа может быть любым. Поэтому, если надо суммировать только конкретные ячейки, но не со всех листов, а только с листов, начинающихся со слова «Отчет» — можно функцию записать так:
=СУММ(‘Отчет*’! C33 )
=SUM(‘Отчет*’!C33)
Зная этот прием, можно заранее подготовить книгу, чтобы имена листов имели такие названия, по которым можно будет суммировать данные только с нужных в зависимости от ситуации(например, для каждой категории листов свое обозначение: «-год-«,»-месяц-«,»-квартал-» и т.п.).
К слову, подобное использование функции доступно и для родственных СУММ: СЧЁТ (COUNT) , СРЗНАЧ (AVERAGE) , МИН (MIN) и МАКС (MAX)

Минус такой функции в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне(т.е. со всех листов будет взята сумма всех ячеек конкретного диапазона — в примере будут просуммированы данные из одной ячейки C33 всех листов). А использование функции ДВССЫЛ (INDIRECT) для динамического указания имен первого и последнего листа при этом не допускается.

А значит, если необходимо будет просуммировать данные по отдельным товарам со всех листов, а товары расположены в каждом листе в хаотичном порядке, разном для каждого листа и количество строк различается — такая формула не подойдет. Но можно воспользоваться чуть более сложной формулой:
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(&»!B3:B100″); B3 ;ДВССЫЛ(&»!C3:C100″)))
=SUMPRODUCT(SUMIF(INDIRECT(&»!B3:B100″),B3,INDIRECT(&»!C3:C100″)))
В данном случае названия товаров записаны в столбце B, а суммы — в столбце С

«Январь»:»Февраль»:»Март»:»Апрель»:»Май»:»Июнь» — имена листов, с которых происходит суммирование. Важно помнить, что имена листов должны быть точь-в-точь как в книге(со всеми пробелами, точками и запятыми), иначе будет ошибка. Не буду останавливаться подробно на всех вложенных функциях. Про СУММЕСЛИ (SUMIF) можно прочитать в этой статье. ДВССЫЛ (INDIRECT) используется для создания ссылки на диапазон, состоящей из имени листа и адреса ячеек: Январь!B3:B100, Февраль!B3:B100, Март!B3:B100, Апрель!B3:B100, Май!B3:B100, Июнь!B3:B100 . Т.е. мы в формуле перебираем все указанные листы, создаем диапазоны в каждом из них и суммируем на основании критерия при помощи СУММЕСЛИ. А при помощи СУММПРОИЗВ (SUMPRODUCT) суммируем полученные от СУММЕСЛИ результаты в единое число.

Важно: если в имени листа встречается пробел, либо иной знак препинания, то имя листа необходимо заключать в апострофы: » ‘ Январь 2014 ‘ «:» ‘ Февраль 2014 ‘ «:»Март»:»Апрель»:»Май»:»Июнь»
либо ставить апострофы заранее для всех листов:
ДВССЫЛ(» ‘ «&&» ‘ !C3:C100″)

В чем главное неудобство — необходимо так или иначе указывать листы, с которых суммировать. Если листов много, то перечислить их все в формуле будет не так-то просто. В таком случае можно записать имена листов в отдельный диапазон и ссылаться на него в формуле(формула в данном случае получается более универсальной и короткой):
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(«‘»& $H$3:$H$8 &»‘!B3:B100»);B3;ДВССЫЛ(«‘»& $H$3:$H$8 &»‘!C3:C100»)))
=SUMPRODUCT(SUMIF(INDIRECT(«‘»&$H$3:$H$8&»‘!B3:B100»),B3,INDIRECT(«‘»&$H$3:$H$8&»‘!C3:C100»)))
Но и она имеет свои недостатки:

• нельзя указать диапазон имен листов «с запасом». Т.е. если у нас имена листов записаны в диапазоне H3:H8 , то нельзя в формуле указать H3:H10
• в диапазоне с перечислением листов не должно быть пустых ячеек
• в диапазоне с перечислением листов не должно быть имен листов, которые заведомо отсутствуют в книге. Например, если имя листа записано с опечаткой

Если хоть одно из этих условий не выполняется — формула вернет ошибку #ССЫЛКА! (#REF!)
Плюс иногда именно перечисление листов напрямую в формуле удобнее. Например, если нет возможности выделить для этого отдельный диапазон.
Поэтому во вложении ниже эта формула представлена в двух вариантах: со статичным перечислением имен листов и с использованием дополнительного столбца для перечисления листов. А какую именно использовать — придется решать по ситуации.
Так же в примере перечислены все варианты суммирования, включая разбираемые ниже с использованием Visual Basic for Applications
Скачать пример суммирования с листов формулой

Суммировать со всех листов.xls (134,0 KiB, 13 454 скачиваний)

Возможно, есть и иные способы суммирования формулой. Может даже есть способ суммировать, не указывая имена листов, но у меня не получилось так сделать без использования VBA. Поэтому я написал свою пользовательскую функцию:

'--------------------------------------------------------------------------------------- ' Function : СУММЕСЛИ_ВСЕ_ЛИСТЫ ' Purpose : Функция суммирует на основании критерия значения со всех листов, кроме листа с функцией ' Диапазон - ссылка на диапазон ячеек для поиска критерия ' Критерий - значение, на основании которого суммировать данные ' ДиапазонСуммирования - ссылка на диапазон ячеек, значения которых суммировать ' ВсеЛисты - 1(по умолчанию) - суммирование происходит со всех листов, кроме листа с функцией ' 0 - суммирование происходит только в листах, расположенных левее листа с функцией '--------------------------------------------------------------------------------------- Function СУММЕСЛИ_ВСЕ_ЛИСТЫ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ВсеЛисты As Boolean = True) Dim wsSh As Worksheet Dim wbFunc As Workbook Dim sRange As String, sSumRange As String Dim dblSum As Double Dim rRange As Range, rCriteria As Range, rSumRange As Range, bAllSh As Boolean Set rRange = Диапазон If IsObject(Критерий) Then Set rCriteria = Критерий Else rCriteria = Критерий End If Set rSumRange = ДиапазонСуммирования bAllSh = ВсеЛисты 'определяем книгу, в которой записана функция Set wbFunc = Application.Caller.Parent.Parent 'определяем адреса поиска критерия и суммирования sRange = rRange.Address sSumRange = rSumRange.Address 'цикл по всем листам книги с функцией For Each wsSh In wbFunc.Worksheets If bAllSh Then 'если это не лист с функцией If wsSh.Name <> Application.Caller.Parent.Name Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Else 'если лист ДО листа с функцией If wsSh.Index < Application.Caller.Parent.Index Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If End If Next wsSh 'выводим результат СУММЕСЛИ_ВСЕ_ЛИСТЫ = dblSum End Function

‘————————————————————————————— ‘ Function : СУММЕСЛИ_ВСЕ_ЛИСТЫ ‘ Purpose : Функция суммирует на основании критерия значения со всех листов, кроме листа с функцией ‘ Диапазон — ссылка на диапазон ячеек для поиска критерия ‘ Критерий — значение, на основании которого суммировать данные ‘ ДиапазонСуммирования — ссылка на диапазон ячеек, значения которых суммировать ‘ ВсеЛисты — 1(по умолчанию) — суммирование происходит со всех листов, кроме листа с функцией ‘ 0 — суммирование происходит только в листах, расположенных левее листа с функцией ‘————————————————————————————— Function СУММЕСЛИ_ВСЕ_ЛИСТЫ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ВсеЛисты As Boolean = True) Dim wsSh As Worksheet Dim wbFunc As Workbook Dim sRange As String, sSumRange As String Dim dblSum As Double Dim rRange As Range, rCriteria As Range, rSumRange As Range, bAllSh As Boolean Set rRange = Диапазон If IsObject(Критерий) Then Set rCriteria = Критерий Else rCriteria = Критерий End If Set rSumRange = ДиапазонСуммирования bAllSh = ВсеЛисты ‘определяем книгу, в которой записана функция Set wbFunc = Application.Caller.Parent.Parent ‘определяем адреса поиска критерия и суммирования sRange = rRange.Address sSumRange = rSumRange.Address ‘цикл по всем листам книги с функцией For Each wsSh In wbFunc.Worksheets If bAllSh Then ‘если это не лист с функцией If wsSh.Name <> Application.Caller.Parent.Name Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Else ‘если лист ДО листа с функцией If wsSh.Index < Application.Caller.Parent.Index Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If End If Next wsSh 'выводим результат СУММЕСЛИ_ВСЕ_ЛИСТЫ = dblSum End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Shift + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
суммируем со всех листов
=СУММЕСЛИ_ВСЕ_ЛИСТЫ( B3:B100 ; B3 ; C3:C100 )
суммируем со всех листов ДО листа с функцией
=СУММЕСЛИ_ВСЕ_ЛИСТЫ( B3:B100 ; B3 ; C3:C100 ;0)
Аргументы функции аналогичны стандартной СУММЕСЛИ, только в конце добавлен еще один, необязательный. Принцип работы и все нюансы полностью совпадают с таковыми для функции СУММЕСЛИ (можно использовать подстановочные символы и операторы сравнения), т.к. внутри функции применяется непосредственно СУММЕСЛИ , применительно к каждому листу, а результаты затем суммируются в единое значение.
Диапазон — Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий( Критерий ).
Критерий — Ссылка на ячейку или произвольное значение, на основании которого надо суммировать данные.
ДиапазонСуммирования — Ссылка на диапазон ячеек. Указывается диапазон ячеек, значения в которых необходимо просуммировать на основании критерия( Критерий ).
ВсеЛисты — Необязательный аргумент. Если не указан, или указано значение 1 или ИСТИНА (TRUE) , то будут суммироваться значения со всех листов, кроме листа, на котором записана функция. Если указано значение 0 или ЛОЖЬ (FALSE) , то будут суммироваться значения с листов, расположенных до листа, на котором записана функция.

Применение всех функций вы найдете в примере к статье:
Скачать пример

Суммировать со всех листов.xls (134,0 KiB, 13 454 скачиваний)

Дополнил статью функцией, которая суммирует данные только с указанных листов, либо со всех, кроме листа с функцией:

'--------------------------------------------------------------------------------------- ' Function : СУММЕСЛИ_ЛИСТЫ ' Purpose : Функция суммирует на основании критерия значения с указанных листов, кроме листа с функцией ' Диапазон - ссылка на диапазон ячеек для поиска критерия ' Критерий - значение, на основании которого суммировать данные ' ДиапазонСуммирования - ссылка на диапазон ячеек, значения которых суммировать ' ИмяЛистов - Имена листов, записанные через вопр.знак(?), с которых суммировать данные ' Пример: "Январь?Февраль?Май" '--------------------------------------------------------------------------------------- Function СУММЕСЛИ_ЛИСТЫ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ИмяЛистов = "") Dim asSheets, li As Long Dim wsSh As Worksheet Dim wbFunc As Workbook Dim sRange As String, sSumRange As String Dim dblSum As Double Dim rRange As Range, rCriteria As Range, rSumRange As Range, sSheets As String Set rRange = Диапазон If IsObject(Критерий) Then Set rCriteria = Критерий Else rCriteria = Критерий End If Set rSumRange = ДиапазонСуммирования sSheets = ИмяЛистов 'определяем книгу, в которой записана функция Set wbFunc = Application.Caller.Parent.Parent 'определяем адреса поиска критерия и суммирования sRange = rRange.Address sSumRange = rSumRange.Address If sSheets = "" Then For Each wsSh In wbFunc.Worksheets If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & "?" & wsSh.Name End If Next wsSh sSheets = Mid$(sSheets, 2) End If asSheets = Split(sSheets, "?") 'цикл по всем листам книги For li = LBound(asSheets) To UBound(asSheets) Set wsSh = wbFunc.Sheets(asSheets(li)) If Not wsSh Is Nothing Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Next li 'выводим результат СУММЕСЛИ_ЛИСТЫ = dblSum End Function

‘————————————————————————————— ‘ Function : СУММЕСЛИ_ЛИСТЫ ‘ Purpose : Функция суммирует на основании критерия значения с указанных листов, кроме листа с функцией ‘ Диапазон — ссылка на диапазон ячеек для поиска критерия ‘ Критерий — значение, на основании которого суммировать данные ‘ ДиапазонСуммирования — ссылка на диапазон ячеек, значения которых суммировать ‘ ИмяЛистов — Имена листов, записанные через вопр.знак(?), с которых суммировать данные ‘ Пример: «Январь?Февраль?Май» ‘————————————————————————————— Function СУММЕСЛИ_ЛИСТЫ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ИмяЛистов = «») Dim asSheets, li As Long Dim wsSh As Worksheet Dim wbFunc As Workbook Dim sRange As String, sSumRange As String Dim dblSum As Double Dim rRange As Range, rCriteria As Range, rSumRange As Range, sSheets As String Set rRange = Диапазон If IsObject(Критерий) Then Set rCriteria = Критерий Else rCriteria = Критерий End If Set rSumRange = ДиапазонСуммирования sSheets = ИмяЛистов ‘определяем книгу, в которой записана функция Set wbFunc = Application.Caller.Parent.Parent ‘определяем адреса поиска критерия и суммирования sRange = rRange.Address sSumRange = rSumRange.Address If sSheets = «» Then For Each wsSh In wbFunc.Worksheets If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & «?» & wsSh.Name End If Next wsSh sSheets = Mid$(sSheets, 2) End If asSheets = Split(sSheets, «?») ‘цикл по всем листам книги For li = LBound(asSheets) To UBound(asSheets) Set wsSh = wbFunc.Sheets(asSheets(li)) If Not wsSh Is Nothing Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Next li ‘выводим результат СУММЕСЛИ_ЛИСТЫ = dblSum End Function

Синтаксис функции:
=СУММЕСЛИ_ЛИСТЫ( $B$3:$B$100 ; $A3 ; $C$3:$C$100 ;»Январь?Март»)
Диапазон — Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий( Критерий ).
Критерий — Ссылка на ячейку или произвольное значение, на основании которого надо суммировать данные.
ДиапазонСуммирования — Ссылка на диапазон ячеек. Указывается диапазон ячеек, значения в которых необходимо просуммировать на основании критерия( Критерий ).
ИмяЛистов — Необязательный аргумент. Указываются имена листов книги, с которых надо суммировать данные. Имена листов должны быть записаны через вопросительный знак: Январь ? Март . Если аргумент не указан или указана пустая ячейка, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция.

Скачать пример с кодом функции:
Скачать пример

Суммировать со всех листов.xls (134,0 KiB, 13 454 скачиваний)

Очередное дополнение статьи — функция, в которой помимо перечисления листов можно указать книгу, в которой эти листы просматривать:

'--------------------------------------------------------------------------------------- ' Function : СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ ' Purpose : Функция суммирует на основании критерия значения с указанных листов, кроме листа с функцией ' Диапазон - ссылка на диапазон ячеек для поиска критерия ' Критерий - значение, на основании которого суммировать данные ' ДиапазонСуммирования - ссылка на диапазон ячеек, значения которых суммировать ' ИмяЛистов - Имена листов, записанные через вопр.знак(?), с которых суммировать данные ' Пример: "Январь?Февраль?Май" ' ИмяКниги - имя книги, данные с которой необходимо суммировать '--------------------------------------------------------------------------------------- Function СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ИмяЛистов = "", Optional ИмяКниги As String = "") Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long Dim wbB As Workbook Dim dblSum As Double Dim rRange As Range, rCriteria As Range, rSumRange As Range, sSheets As String, sWbName As String Set rRange = Диапазон If IsObject(Критерий) Then Set rCriteria = Критерий Else rCriteria = Критерий End If Set rSumRange = ДиапазонСуммирования sSheets = ИмяЛистов sWbName = ИмяКниги If sWbName = "" Then Set wbB = Application.Caller.Parent.Parent Else Set wbB = Application.Workbooks(sWbName) End If 'определяем адреса поиска критерия и суммирования sRange = rRange.Address sSumRange = rSumRange.Address If sSheets = "" Then For Each wsSh In wbB.Worksheets If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & "?" & wsSh.Name End If Next wsSh sSheets = Mid$(sSheets, 2) End If asSheets = Split(sSheets, "?") 'цикл по всем листам указанной книги On Error Resume Next For li = LBound(asSheets) To UBound(asSheets) Set wsSh = wbB.Sheets(asSheets(li)) If Not wsSh Is Nothing Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Next li 'выводим результат СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ = dblSum End Function

‘————————————————————————————— ‘ Function : СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ ‘ Purpose : Функция суммирует на основании критерия значения с указанных листов, кроме листа с функцией ‘ Диапазон — ссылка на диапазон ячеек для поиска критерия ‘ Критерий — значение, на основании которого суммировать данные ‘ ДиапазонСуммирования — ссылка на диапазон ячеек, значения которых суммировать ‘ ИмяЛистов — Имена листов, записанные через вопр.знак(?), с которых суммировать данные ‘ Пример: «Январь?Февраль?Май» ‘ ИмяКниги — имя книги, данные с которой необходимо суммировать ‘————————————————————————————— Function СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ(Диапазон As Range, Критерий, ДиапазонСуммирования As Range, Optional ИмяЛистов = «», Optional ИмяКниги As String = «») Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long Dim wbB As Workbook Dim dblSum As Double Dim rRange As Range, rCriteria As Range, rSumRange As Range, sSheets As String, sWbName As String Set rRange = Диапазон If IsObject(Критерий) Then Set rCriteria = Критерий Else rCriteria = Критерий End If Set rSumRange = ДиапазонСуммирования sSheets = ИмяЛистов sWbName = ИмяКниги If sWbName = «» Then Set wbB = Application.Caller.Parent.Parent Else Set wbB = Application.Workbooks(sWbName) End If ‘определяем адреса поиска критерия и суммирования sRange = rRange.Address sSumRange = rSumRange.Address If sSheets = «» Then For Each wsSh In wbB.Worksheets If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & «?» & wsSh.Name End If Next wsSh sSheets = Mid$(sSheets, 2) End If asSheets = Split(sSheets, «?») ‘цикл по всем листам указанной книги On Error Resume Next For li = LBound(asSheets) To UBound(asSheets) Set wsSh = wbB.Sheets(asSheets(li)) If Not wsSh Is Nothing Then dblSum = dblSum + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Next li ‘выводим результат СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ = dblSum End Function

Синтаксис функции:
=СУММЕСЛИ_ЛИСТЫ_В_КНИГЕ( $B$3:$B$100 ; $A3 ; $C$3:$C$100 ;»Январь?Март»;»Книга1.xlsx»)
Аргументы и их использование полностью совпадают с описанными выше для функции СУММЕСЛИ_ЛИСТЫ . Поэтому опишу только последний аргумент:
ИмяКниги — Необязательный аргумент. Указываются имя книги, в которой будут просматриваться листы, указанные параметром ИмяЛистов . Если аргумент ИмяКниги не указан — листы просматриваются в книге, в которой записана сама функция. Если какого-либо из указанных листов не будет в указанной книге — ошибка будет проигнорирована. Если среди открытых книг не будет книги с указанным именем — функция вернет ошибку #ЗНАЧ! (#VALUE!) . Важно: имя книги необходимо указывать вместе с расширением: «Книга1.xlsx»

Скачать пример с кодом функций:
Скачать пример

Суммировать со всех листов.xls (134,0 KiB, 13 454 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Подсчет суммы в столбце в программе Microsoft Excel

Основная функциональность Microsoft Excel завязана на формулах, благодаря которым с помощью этого табличного процессора можно не только создать таблицу любой сложности и объема, но и вычислять те или иные значения в ее ячейках. Подсчет суммы в столбце – одна из наиболее простых задач, с которой пользователи сталкиваются чаще всего, и сегодня мы расскажем о различных вариантах ее решения.

Считаем сумму в столбце в Экселе

Посчитать сумму тех или иных значений в столбцах электронной таблицы Excel можно как вручную, так и автоматически, воспользовавшись базовым инструментарием программы. Кроме того, имеется возможность обычного просмотра итогового значения без его записи в ячейку. Как раз с последнего, наиболее простого мы и начнем.

Способ 1: Просмотр общей суммы

Если вам нужно просто увидеть общее значение по столбцу, в ячейках которого содержаться определенные сведения, при этом постоянно держать эту сумму перед глазами и создавать для ее вычисления формулу не требуется, выполните следующее:

1. С помощью мышки выделите диапазон ячеек в столбце, сумму значений в котором требуется посчитать. В нашем примере это будут ячейки с 3 по 9 столбца G.
2. Взгляните на строку состояния (нижняя панель программы) – там будет указана искомая сумма. Отображаться это значение будет исключительно до тех пор, пока выделен диапазон ячеек.
3. Дополнительно. Такое суммирование работает, даже если в столбце есть пустые ячейки.
   
   Более того, аналогичным образом можно посчитать сумму всех значений в ячейках сразу из нескольких столбцов – достаточно просто выделить необходимый диапазон и посмотреть на строку состояния. Подробнее о том, как это работает, расскажем в третьем способе данной статьи, но уже на примере шаблонной формулы.

Примечание: Слева от суммы указано количество выделенных ячеек, а также среднее значение для всех указанных в них чисел.

Способ 2: Автосумма

Очевидно, что значительно чаще сумму значений в столбце требуется держать перед глазами, видеть ее в отдельной ячейке и, конечно же, отслеживать изменения, если таковые вносятся в таблицу. Оптимальным решением в таком случае будет автоматическое суммирование, выполняемое с помощью простой формулы.

Нажмите левой кнопкой мышки (ЛКМ) по пустой ячейке, расположенной под теми, сумму которых требуется посчитать.

Кликните ЛКМ по кнопке «Сумма», расположенной в блоке инструментов «Редактирование» (вкладка «Главная»). Вместо этого можно воспользоваться комбинацией клавиш «ALT» + «=».

Убедитесь, что в формуле, которая появилась в выделенной ячейке и строке формул, указан адрес первой и последней из тех, которые необходимо суммировать, и нажмите «ENTER».

Вы сразу же увидите сумму чисел во всем выделенном диапазоне.

Примечание: Любое изменение в ячейках из выделенного диапазона моментально будет отражаться на итоговой формуле, то есть результат в ней тоже будет меняться.

Бывает так, что сумму нужно вывести не в ту ячейку, которая находится под суммируемыми, а в какую-нибудь другую, возможно, расположенную совсем в другом столбце таблицы. В таком случае действовать необходимо следующим образом:

1. Выделите кликом ячейку, в которой будет вычисляться сумма значений.
2. Нажмите по кнопке «Сумма» или воспользуйтесь горячим клавишам, предназначенным для ввода этой же формулы.
3. С помощью мышки выделите диапазон ячеек, которые требуется просуммировать, и нажмите «ENTER».

Совет: Выделить диапазон ячеек можно и немного иным способом – кликните ЛКМ по первой из них, затем зажмите клавишу «SHIFT» и кликните по последней.

Способ 3: Автосумма нескольких столбцов

Бывает так, что требуется посчитать сумму не в одном, а сразу в нескольких столбцах электронной таблицы Эксель. Делается это практически так же, как и в предыдущем способе, но с одним небольшим нюансом.

1. Щелкните ЛКМ по той ячейке, в которую планируете выводить сумму всех значений.
2. Нажмите по кнопке «Сумма» на панели инструментов или воспользуйтесь предназначенной для ввода этой формулы комбинацией клавиш.
3. Первый столбец (над формулой) уже будет выделен. Поэтому зажмите клавишу «CTRL» на клавиатуре и выделите следующий диапазон ячеек, который тоже должен входить в рассчитываемую сумму.

Примечание: Если столбцы, которые требуется посчитать, идут подряд, выделять их можно все вместе.

Далее, аналогичным образом, если это еще потребуется, зажимая клавишу «CTRL», выделите ячейки из других столбцов, содержимое которых тоже должно входить в итоговую сумму.

Нажмите «ENTER» для подсчета, после чего в предназначенной для формулы ячейке и строке на панели инструментов появится итоговое вычисление.
Несложно догадаться, что аналогичным образом можно посчитать сумму значений в отдельных ячейках, состоящих как в разных столбцах, так и в пределах одного.

Для этого нужно просто выделить кликом ячейку для формулы, нажать по кнопке «Сумма» или на горячие клавиши, выделить первый диапазон ячеек, а затем, зажимая клавишу «CTRL», выделить все остальные «участки» таблицы. Сделав это, просто нажмите «ENTER» для отображения результата.

Как и в предыдущем способе, формула суммы может быть записана в любую из свободных ячеек таблицы, а не только в ту, которая находится под суммируемыми.

Способ 4: Суммирование вручную

Как и любая формула в Microsoft Excel, «Сумма» имеет свой синтаксис, а потому она может быть прописана вручную. Зачем это нужно? Хотя бы за тем, что таким образом вы точно сможете избежать случайных ошибок при выделении суммируемого диапазона и указании входящих в него ячеек. Более того, даже если вы ошибетесь на каком-то из шагов, исправить это будет так же просто, как банальную опечатку в тексте, а значит, не потребуется проделывать всю работу с нуля, как это иногда приходится делать.

Помимо прочего, самостоятельный ввод формулы позволяет разместить ее не только в любом месте таблицы, но и на любом из листов конкретного электронного документа. Стоит ли говорить о том, что таким образом можно просуммировать не только столбцы и/или их диапазоны, но и просто отдельные ячейки или группы таковых, исключая при этом те, которые не должны быть учтены?

Формула суммы имеет следующий синтаксис:

=СУММ(суммируемые ячейки или диапазон таковых)

То есть все то, что должно быть суммировано, записывается в скобки. Значения, указываемые внутри скобок, должны выглядеть следующим образом:

Для диапазона: А1:A10 – это сумма 10 первых ячеек в столбце A.

Для отдельных ячеек: A2;A5;A7;A10 – сумма ячеек №2, 5, 7,10 столбца A.

Смешанное значение (диапазоны и отдельные ячейки): A1;A3:A5;A7;A9:15 – сумма ячеек №1, 3-5, 7, 9-15. При этом важно понимать, что между диапазонами и единичными ячейками не должно стоять никакого дополнительного символа-разделителя.

А теперь перейдем непосредственно к ручному суммированию значений в столбце/столбцах электронной таблицы Эксель.

Выделите нажатием ЛКМ ту ячейку, которая будет содержать итоговую формулу.

Введите в нее следующее: =СУММ(

после чего начните поочередно вводить в строке формул адреса суммируемых диапазонов и/или отдельных ячеек, не забывая о разделителях («:» — для диапазона, «;» — для единичных ячеек, вводить без кавычек), то есть делайте все по тому алгоритму, суть которого мы выше подробно описали.

Указав все те элементы, которые требуется просуммировать и убедившись, что вы ничего не пропустили (можно сориентироваться не только по адресам в строке формул, но и по подсветке выделенных ячеек), закройте скобку и нажмите «ENTER» для подсчета.

Примечание: Если вы заметили ошибку в итоговой формуле (например, туда вошла лишняя ячейка или какая-то была пропущена), просто вручную введите правильное обозначение (или удалите лишнее) в строке формул. Аналогичным образом, если и когда это потребуется, можно поступить и с символами-разделителями в виде «;» и «:».

Как вы, наверное, уже могли догадаться, вместо ручного ввода адресов ячеек и их диапазонов можно просто выделять необходимые элементы с помощью мышки. Главное, не забывать при этом удерживать клавишу «CTRL», когда вы переходите от выделения одного элемента к другому.

Самостоятельный, ручной подсчет суммы в столбце/столбцах и/или отдельных ячейках таблицы – задача не самая удобная и быстрая в реализации. Но, как мы уже сказали выше, такой подход предоставляет куда более широкие возможности для взаимодействия со значениями и позволяет моментально исправлять случайные ошибки. А вот то, делать ли это полностью вручную, вводя с клавиатуры в формулу каждую переменную, или использовать для этого мышку и горячие клавиши – решать только вам.

Заключение

Даже такая, казалось бы, простая задача, как подсчет суммы в столбце, решается в табличном процессоре Microsoft Excel сразу несколькими способами, и каждый из них найдет свое применение в той или иной ситуации.