Вам не нужно ничего вкладывать. Функция SUMPRODUCTочень мощная, которая позволяет вам считать или суммировать на основе очень сложных условий. Он поддерживает как логические операторы AND, так и OR.
TL; версия DR
=SUMPRODUCT((($A$2:$A$10="Apple")+($A$2:$A$10="Pear"))*(($B$2:$B$10="Red")+($B$2:$B$10="Green"))*($C$2:$C$10<>"Yes")) Длинная версия (извините ...)
Вы должны действительно научиться использовать эту функцию, потому что она очень полезна и может сэкономить вам много времени. Вот краткое и грязное руководство о том, как его использовать, но я советую вам сохранить какой-нибудь тестовый файл с простыми примерами, такими как тот, который вы предоставили, чтобы вы могли поэкспериментировать с ним. Не стесняйтесь разделять различные проблемы, которые вы пытаетесь решить.
В основном, a SUMPRODUCTсодержит несколько членов, каждый из которых является условием. Если вы умножите эти условия, вы получите логическое И. Если вы добавите эти условия, вы получите логическое ИЛИ.
С примером гораздо проще! Итак, вот разбивка окончательной формулы на примере ваших фруктов.
Примечание: чтобы упростить задачу, я использовал имена таблиц Fruits, что позволяет использовать именованные диапазоны и улучшает удобочитаемость.
Так вот как это работает. Ваше первое условие - это на самом деле логическое ИЛИ (вам нужны яблоки или груши, поскольку фрукт не может быть и тем и другим. Таким образом, формула (1) имеет 2 члена или 2 условия: Fruit = Apple, Fruit = Pear.
(Fruits[Fruit]="Apple")вернется, TRUEесли это яблоко, в FALSEпротивном случае. Суммируя это для 2nd member ( (Fruits[Fruit]="Pear")), вы получите массив 0 и 1. Логика следующая:
- Ложь + Ложь = 0
- True + False = 1
- True + True = 1
Затем SUMPRODUCTсумма будет суммировать все элементы этого массива (0 и 1). Что в основном так же, как подсчет элементов. Результат 8, как и ожидалось (ячейка D14).
Второе условие (цвет) работает точно так же. Если вы хотите включить третий цвет (например, «оранжевый»), вы просто добавите новый элемент в уравнение с помощью +.
Третье условие более простое, у вас есть только 1 критерий. Я специально использовал этот <>знак, чтобы показать вам, что вы также можете исключать элементы. В формуле (1) вы могли бы использовать, например (Fruits[Fruit]<>"Banana").
Важное замечание : Обратите внимание, что в SUMPRODUCT, когда есть только один член (как в 3-м уравнении), вам необходимо преобразовать результат в число. Помните: SUMPRODUCTсоздает массив TRUEи FALSE. Вы можете сделать это легко, используя N(...)функцию или записав альтернативу --(...), которая преобразуется TRUEв 1 и FALSEв 0.
Теперь эти 3 условия работают индивидуально, но мы хотим объединить их в 1 формулу. И поскольку мы хотим, чтобы каждое из этих трех условий было истинным (фруктовое, цветное и ядовитое), нам необходимо создать логическое И. Это можно сделать так же, как мы делали ИЛИ, но на этот раз мы будем использовать умножение ( *):
- Ложь * Ложь = 0
- True * False = 0
- True * True = 1
Для наших 3 условий мы просто используем 3 отдельные формулы, которые мы написали ранее, и помещаем их в a SUMPRODUCT. Эти 3 члена должны быть заключены в круглые скобки и разделены *(мы в основном умножаем их).
Вот последние формулы, которые вы можете попробовать:
=SUMPRODUCT((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear")) =SUMPRODUCT((Fruits[Color]="Red")+(Fruits[Color]="Green")) =SUMPRODUCT(N(Fruits[Poisonous]<>"Yes")) И последний:
=SUMPRODUCT(((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))*((Fruits[Color]="Red")+(Fruits[Color]="Green"))*(Fruits[Poisonous]<>"Yes")) Примечания стороны:
- Каждое проверяемое условие (например, Color = Red) должно быть заключено в круглые скобки:
(Fruits[Color]="Red") - Порядок скобок очень важен, если у вас есть условия ИЛИ. Например, уравнение
(X and (Y or Z))не совпадает с((X and Y) or Z). - Вы можете использовать классические операторы для проверки условия: = для равных, <> для разных,> и <для большего / меньшего, чем>> и <= для большего / меньшего или равного.
- Мы использовали
SUMPRODUCTдля подсчета, но мы также можем использовать его для суммирования. Если один из членов уравнения не имеет=знака, то значения учитываются (см. Пример ниже, где столбец G суммируется). - Критерии
"Apple"могут быть заменены ссылкой на ячейку, которая сама по себе может быть выпадающим меню. Лучше использовать переменные, а не писать текст непосредственно внутри формулы. SUMPRODUCTможет быть ресурсоемкой формулой, поскольку она производит умножения и суммы ... В зависимости от того, сколько условий вы тестируете, насколько велик набор данных и сколько раз вы используете aSUMPRODUCT. Для более простых условий,SUM.IFSвероятно, быстрее.SUMPRODUCTтакже поддерживает частичный текстовый поиск, как показано ниже:
