Трюк №19. Как в Excel подсчитать и суммировать ячейки, отвечающие критерию условного форматирования
Увидев результат условного форматирования, вы, вероятно, захотите создать формулы, ссылающиеся только на данные, которые подверглись условному форматированию. В Excel не очень хорошо реализована такая возможность, но мы поможем ему.
Пользователи Excel регулярно задают вопрос, как можно провести вычисления только с ячейками определенного фонового цвета. Этот вопрос возникает так часто, потому что в Excel не предусмотрена стандартная функция для выполнения этой задачи; однако это можно сделать при помощи пользовательской функции, как демонстрирует Трюк № 88 «Подсчет или суммирование ячеек с определенным цветом заливки».
Единственная неприятность при использовании пользовательской функции — она не понимает форматирование, наложенное при помощи условного форматирования. Однако, немного подумав, вы сможете добиться нужного результата, не применяя пользовательскую функцию.
Пусть есть длинный список чисел в диапазоне $А$2:$А$100. Вы наложили на эти ячейки условное форматирование, чтобы помечены были только числа между 10 и 20. Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию, а затем выделить сумму значений при помощи условного форматирования. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются (в нашем случае это условие, что значение находится между 10 и 20).
Чтобы сложить диапазон ячеек, отвечающих определенному критерию, можно использовать функцию СУММЕСЛИ (SUMIF), но при этом можно указать только один критерий. Если у вас несколько факторов проверки, можно использовать формулу массива. Формулу массива можно записать так:
=SUM(IF($A$2:$A$100>10;IF($A$2:$A$100<20;$A$2:$A$100)))
В русской версии Excel
=СУММ(ЕСЛИ($А$2:$А$100>10;ЕСЛИ($А$2:$А$100<20;$А$2:$А$100)))
[stextbox id=»warning»]При вводе формул массива нажимайте не клавишу Enter, а сочетание клавиш Ctrl+Shift+Enter. После этого Excel поместит фигурные скобки вокруг формулы, чтобы она выглядела так:
{=SUM(IF($A$2:$A$100>10;IF($A$2:$A$100<20;$A$2:$A$100)))}
Если вы поставите скобки самостоятельно, формула не будет работать. Это должен сделать Excel. Помните также, что использование формул массива может замедлить пересчет в Excel, если в этих формулах присутствует слишком много ссылок на большие диапазоны.[/stextbox]
Чтобы больше узнать о формулах массива, посетите страницу http://excel2010.ru/massiv-formuly-massiva-knigax-excel.html
Альтернативный путь
В качестве альтернативы можно использовать дополнительный столбец (например, столбец В) для ссылки на ячейки в столбце А. Эти ссылки будут возвращать результат в столбец В, только если значение отвечает установленному условию, то есть >10, <20. Для этого сделайте следующее:
Выделите ячейку В1 и введите следующую формулу:
=IF(AND(A2>10;A2<20);A2;"")
В русской версии Excel
=ЕСЛИ(И(А2>10;А2<20);А2;"")
Запишите эту формулу в каждую ячейку до ячейки В100. После этого, если в столбце А есть значения, в столбце В должны оказаться значения между 10 и 20.
[stextbox id=»warning»]Чтобы быстро скопировать формулу в соседние ячейки вниз до последней строки, введите формулу в первую ячейку (В2), заново выделите эту ячейку и дважды щелкните маркер заполнения. Это можно сделать и выбрав команду Правка → Заполнить → Вниз (Edit → Fill → Down).[/stextbox]
Теперь выберите любую ячейку, где должна появиться сумма, и воспользуйтесь для сложения стандартной функцией СУММ (SUM). (Можно скрыть столбец В, если вы не хотите видеть дополнительный столбец с возвращенными формулой значениями.)
Оба предыдущих способа хорошо справляются с задачей, однако в Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ (DSUM).
Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100е ячейки C1:D2 и присвойте этому диапазону имя SumCriteria, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе. Скопируйте то же самое в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (DSUM) (C1:D2), который вы назвали SumCriteria.
В ячейке С2 введите >10. В ячейке D2 введите <20. В ячейке, где должен быть результат, введите следующий код:
=DSUM($А$1:$А$100;$А$1;SumCriteria)
В русской версии Excel
=БДСУММ($А$1:$А$100;$А$1;SumCriteria)
Функция ДСУММ (DSUM) — это предпочтительный и самый эффективный способ работы с ячейками, отвечающими определенному критерию. В отличие от массивов, встроенные функции баз данных разработаны специально для этой цели, и, даже когда они ссылаются на очень большой диапазон и используются в большом количестве, их отрицательное воздействие на скорость пересчета и эффективность достаточно мало по сравнению с формулами массива.