Часто бывает, что нужно суммировать каждую вторую, третью, четвертую и т. д. ячейку в электронной таблице. Теперь, благодаря следующему трюку, это можно сделать.
В Excel не предусмотрено стандартной функции, которая может суммировать каждую n-ю ячейку или строку. Однако можно выполнить эту задачу несколькими различными способами. Все эти подходы основаны на функциях СТРОКА (ROW) и ОСТАТ (MOD).
Функция СТРОКА (ROW)
возвращает номер строки для заданной ссылки на ячейку: ROW(reference), в русской версии Excel СТРОКА(ссылка).
Функция ОСТАТ (MOD)
возвращает остаток от деления числа на делитель: MOD(number;divisor), в русской версии Excel ОСТАТ(число;делитель).
Поместите функцию СТРОКА (ROW) в функцию ОСТАТ (MOD) (чтобы передать числовой аргумент), разделите на 2 (чтобы суммировать каждую вторую ячейку) и проверьте, не равен ли результат нулю. Если это так, ячейка суммируется. Эти функции можно использовать разнообразнейшими способами - некоторые обеспечат лучший результат, чем другие. Например, формула массива для суммирования каждой второй ячейки в диапазоне $А$1:$А$100 могла бы выглядеть так: =SUM(IF(MOD(ROW($A$1:$А$500);2)=0;$А$1:$А$500;0)) , в русской версии Excel =СУММ(ЕСЛИ(ОСТАТ(СТРОКА($А$1:$А$500);2)=0;$А$1:$А$500;0)) .
Поскольку это формула массива, необходимо ввести ее, нажав сочетание клавиш Ctrl+Shift+Enter, Excel добавит фигурные скобки, чтобы она выглядела так: {=SUM(IF(MOD(ROW($A$1:$A$500),2)=0;$A$1:$A$500;0))} , в русской версии Excel: {=СУММ(ЕСЛИ(ОСТАТ(СТРОКА($А$1:$А$500);2)=0;$А$1:$А$500;0))} Нужно, чтобы Excel сам добавил фигурные скобки; если вы добавите их самостоятельно, формула не будет работать.
Хотя цель достигнута, такой способ отрицательно влияет на дизайн электронной таблицы. Это ненужное применение формулы массива. Чтобы еще ухудшить дела, в эту длинную формулу вложена пересчитываемая функция СТРОКА (ROW), превращающая большую формулу также в пересчитываемую. Это означает, что она будет постоянно пересчитываться, что бы вы ни сделали в рабочей книге. Это очень плохой способ!
Вот другая формула, представляющая собой чуть лучший выбор: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) , в русской версии Excel =СУММПРОИЗВ((ОСТАТ(СТРОКА($А$1:$А$500);2)=0)*($А$1:$А$500)) .
Нужно, однако, помнить, что эта формула вернет ошибку #ЗНАЧ! (#VALUE!), если какие-либо ячейки диапазона содержат не числа, а текст. Эта формула, хотя и не является фактически формулой массива, также замедляет работу Excel, если использовать ее слишком много раз или если каждый раз она ссылается на большой диапазон.
К счастью, есть лучший способ, представляющий собой не только более эффективное, но и намного более гибкое решение. Он требует применения функции БДСУММ (DSUM). В этом примере мы использовали диапазон А1:А500 как диапазон, в котором необходимо суммировать каждую n-ю ячейку.
В ячейку Е1 введите слово Criteria. В ячейку Е2 введите следующую формулу: =MOD(ROW(A2)-$C$2-1;$C$2)=0 , в русской версии Excel =ОСТАТ(СТРОКА(А2)-$С$2-1;$С$2)=0 . Выделите ячейку С2 и выберите команду Данные → Проверка (Data → Validation).
В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. В ячейке С1 введите текст SUM every…. В любой ячейке, кроме строки 1, введите следующую формулу: =DSUM($A:$A;1;$E$1:$E$2) , в русской версии Excel =БДСУММ($А:$А;1;$Е$1:$Е$2) .
В ячейке непосредственно над той, где вы ввели функцию БДСУММ (DSUM), введите текст ="Summing Every" & $С$2 & CHOOSE($C$2;"st";"nd";"rd";"th";"th";"th";"th";"th";"th";"th") & "Cell" . Теперь осталось только выбрать нужное число в ячейке С2, а остальное сделает функция БДСУММ (DSUM).
При помощи функции БДСУММ (DSUM) можно суммировать ячейки через указанный вами интервал. Функция БДСУММ (DSUM) намного эффективнее, чем формула массива или функция СУММПРОИЗВ (SUMPRODUCT). Хотя настройка занимает немного больше времени, это тот случай, когда тяжело в ученье, легко в бою.
Ранее я описал, как с помощью пользовательской функции найти . К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.
Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) . На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.
Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20
Скачать заметку в формате , примеры в формате
Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются.
Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).
Рис. 2. Суммирование ячеек, отвечающих одному условию
Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).
Рис. 3. Суммирование ячеек, отвечающих нескольким условиям
Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.
В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).
Рис. 4. Использование функций баз данных
Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:
БДСУММ($А$1:$А$101,1,Критерий)
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.
Читая книгу Джона Уокенбаха я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы ; в окне Макрос , выделите строку СумЦветУсл , и нажмите Выполнить . Запуститься макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.
Код процедуры
Sub СумЦветУсл() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Запрос диапазона Set UserRange = Application.InputBox(_ Prompt:="Выберите диапазон суммирования", _ Title:="Выбор диапазона", _ Default:=ActiveCell.Address, _ Type:=8) " Запрос критерия Set CriterionRange = Application.InputBox(_ Prompt:="Выберите критерий суммирования", _ Title:="Выбор критерия", _ Default:=ActiveCell.Address, _ Type:=8) " Суммирование "правильных" ячеек For Each i In UserRange If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color Then SumColor = SumColor + i End If Next MsgBox SumColor End Sub
Sub СумЦветУсл() Application . Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Запрос диапазона Set UserRange = Application.InputBox(_ Prompt:="Выберите диапазон суммирования", _ Title:="Выбор диапазона", _ Default:=ActiveCell.Address, _ Type:=8) " Запроскритерия Set CriterionRange = Application . InputBox (_ Prompt : = "Выберите критерий суммирования" , _ Title : = "Выбор критерия" , _ Default : = ActiveCell . Address , _ |
Предположим что у Вас есть вот такой отчёт по продажам торговых представителей:
Из него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе .
ПРОБЛЕМА : Как суммировать данные по нескольким критериям??
РЕШЕНИЕ
: Способ 1:
БДСУММ(A1:G16;F1;I1:K2)
В английской версии:
DSUM(A1:G16,F1,I1:K2)
КАК ЭТО РАБОТАЕТ:
Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле " = F1 ) по заданным в ячейках I1:K2 (Продавец = Иванов ; Продукция = Карандаши ; Месяц = Январь ) критериям.
МИНУСЫ : Список критериев должен быть на листе.
ПРИМЕЧАНИЯ
: Количество критериев суммирования ограничено оперативной памятью.
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel
Способ 2:
СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
В английской версии:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
КАК ЭТО РАБОТАЕТ:
Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel.
Если-бы вычисления производились в ячейках листа (для наглядности я всю работу формулы продемонстрирую так, как-будто вычисления происходят на листе, а не в памяти), то массивы выглядели бы так:
Очевидно что если например, D2=Карандаши , то значение будет равно ИСТИНА, а если D3=Папки , то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши ).
Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1.
Перемножив полученные значения массивов между собой последовательно, мы получим ОДИН массив из нолей и единиц. Там где выполнялись все три критерия отбора, (ИВАНОВ, КАРАНДАШИ, ЯНВАРЬ
) т.е. все условия принимали значения ИСТИНА получаем 1 (1*1*1 = 1), если же хотя-бы одно условие не выполнялось - получим 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1*1 = 0).
Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать (диапазон F2:F16 ) и собственно, просуммировать то что на 0 не умножилось.
Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным).
Думаю всё понятно:)
МИНУСЫ : СУММПРОИЗВ - "тяжёлая" формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта.
ПРИМЕЧАНИЯ
ОБЛАСТЬ ПРИМЕНЕНИЯ : Любая версия Excel
Способ 3: Формула массива
СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))
В английской версии:
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
КАК ЭТО РАБОТАЕТ: Точно так же как и Способ №2. Есть только два отличия - данная формула вводится нажатием Ctrl+Shift+Enter , а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ.
МИНУСЫ : Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.
ПРИМЕЧАНИЯ
: Количество обрабатываемых массивов ограничено 255.
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel
Способ 4:
СУММЕСЛИМН(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)