Подсчет нескольких критериев

Question

это мой первый пост на этом форуме, поэтому, вероятно, он представлен очень плохо. Я использую Excel 2007, чтобы собрать некоторые данные для моей диссертации. Недавно я узнал, как вкладывать COUNTIFS в SUMPRODUCT для поиска нескольких критериев, где одним из этих критериев является диапазон, а не константа. Однако та же формула не работает, если мне нужно искать несколько критериев, где два (или более) критерия являются диапазонами, а не константами.

Скажем, у меня есть три смежных столбца AC в Excel, где A называется "Фрукты" и содержит различные фрукты, B называется "цвет" и C называется "ядовитый?", содержащий только значения" да "или" нет ". Как мне сложить все элементы, которые являются ЛИБО яблоком ИЛИ грушей из столбца А и ЛИБО зеленым ИЛИ красным из столбца В и не ядовитые из столбца С?

В этом простом листе Excel правильный ответ - 4, но следующая формула дает мне 1:

=SUMPRODUCT(COUNTIFS(A:A,$E$2:$E$3,B:B,$F$2:$F$3,C:C,$G$3))

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

Я искал ответы как в сети, так и на форуме, и нахожу десятки связанных постов, некоторые, возможно, даже идентичные (показатели с несколькими диапазонами критериев ИЛИ), но мне все еще не удается это выяснить ... Я опаздываю со своей диссертацией, поэтому буду признателен за любую помощь.

Answer 1

Вам не нужно ничего вкладывать. Функция 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"))

---

Длинная версия (извините ...)

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

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

С примером гораздо проще! Итак, вот разбивка окончательной формулы на примере ваших фруктов.

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

Так вот как это работает. Ваше первое условие на самом деле является логическим ИЛИ (вам нужны яблоки или груши, так как фрукт не может быть и тем и другим. Таким образом, формула (1) имеет 2 члена или 2 условия: Fruit = Apple, Fruit = Pear.

(Fruits[Fruit]="Apple") вернет TRUE если это яблоко, иначе FALSE . Суммируя это со вторым членом ((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 отдельные формулы, которые мы написали ранее, и помещаем их в 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 может быть ресурсоемкой формулой, поскольку она делает умножения и суммы ... В зависимости от того, сколько условий вы тестируете, насколько велик набор данных и сколько раз вы используете SUMPRODUCT . Для более простых условий SUM.IFS , вероятно, быстрее.
• SUMPRODUCT также поддерживает частичный текстовый поиск, как показано ниже:

Answer 2

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

У вас есть в основном 2 способа подсчета значений:

• Либо используя "белый список", чтобы перечислить приемлемые значения, используя ISNUMBER
• Либо с помощью "balcklist", чтобы исключить недопустимые значения, используя ISNA

Whitelist:

=SUMPRODUCT(
    (ISNUMBER(MATCH(Fruits[Fruit],Whitelist[Fruit];0)))
    *
    (ISNUMBER(MATCH(Fruits[Color],Whitelist[Color];0)))
    *
    (ISNUMBER(MATCH(Fruits[Poisonous],Whitelist[Poisonous];0)))
)

Черный список:

=SUMPRODUCT(
    (ISNA(MATCH(Fruits[Fruit],Blacklist[Fruit],0)))
    *
    (ISNA(MATCH(Fruits[Color],Blacklist[Color],0)))
    *
    (ISNA(MATCH(Fruits[Poisonous],Blacklist[Poisonous],0)))
)

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

---

Answer 3

Я не уверен, что полностью понимаю это, но кажется возможным получить ответ с

=SUMPRODUCT(COUNTIF($E$2:$E$3,$A$2:$A$10), COUNTIF($F$2:$F$4,$B$2:$B$10), COUNTIF($G$3,$C$2:$C$10))

Чтобы попытаться объяснить: как мы знаем, COUNTIF($B$2:$B$10, "Red") (или, что то же самое, COUNTIF($B$2:$B$10, $F$2) подсчитывает красные фрукты, получая счет 3.  Точно так же COUNTIF($B$2:$B$10, "Green") (или, что то же самое, COUNTIF($B$2:$B$10, $F$3) подсчитывает зеленый фрукт, давая счет 5.  Таким образом, интуитивно заманчиво попытаться использовать COUNTIF($B$2:$B$10, $F$2:$F$3) для подсчета красных или зеленых фруктов.  Это не работает само по себе, но мы можем заставить его работать, делая

=SUM(COUNTIF($B$2:$B$10, $F$2:$F$3))

как формула массива, или

=SUMPRODUCT(COUNTIF($B$2:$B$10, $F$2:$F$3))

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

Но вышеприведенные формулы делают именно то, чего вы ожидали от предыдущего обсуждения: они добавляют 3+5 и получают 8.  Это важно: они добавляют массив из двух чисел, потому что мы смотрим на два цвета.  Это создает нам проблемы, когда мы ищем разное количество значений в разных столбцах.

COUNTIF($E$2:$E$3, "Yellow") , COUNTIF($E$2:$E$3, "Red") и COUNTIF($E$2:$E$3, "Green") не кажутся, в во-первых, чтобы иметь как можно больше смысла; они имеют значения 0, 1 и 1 соответственно.  Но это означает, что COUNTIF($E$2:$E$3,$A$2:$A$10) создает массив из девяти чисел, а именно:0, 1, 1, 1, 1, 1, 1, 1, 1 и 1 ,  Угадай, что?  Сумма еще 8.

Помните, как работает SUMPRODUCT .  SUMPRODUCT(X11:X14, Y11:Y14, Z11:Z14) , например, равно

                ( X11 × Y11 × Z11 ) +
                ( X12 × Y12 × Z12 ) +
                ( X13 × Y13 × Z13 ) +
                ( X14 × Y14 × Z14 )

поэтому он должен иметь аргументы массива одинаковой длины.  Два, два и один - проблема; 15, 26, и какой-то другой номер является проблемой.  Девять, девять и девять это хорошо.

Теперь посмотрите на пример X , Y , Z , но сделайте следующие замены:

• X11 → A2 - один из фруктов, который мы ищем,
• Y11 → B2 - один из цветов, которые мы ищем,
• Z11 → C2 означает «нет»,

и так далее вниз.  Поскольку TRUE равно 1, а FALSE равно 0, каждая строка матрицы SUMPRODUCT равна 1, если каждый столбец содержит искомое значение (потому что 1 × 1 × 1 = 1), и 0, если строка неприемлема в каждом столбце.  Итак, у нас есть список из девяти нулей и единиц, который мы добавляем.

Answer 4

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

Поэтому я хотел бы предложить решение в формате таблицы соответствует вашим требованиям.

Проверьте снимок экрана:

Запишите эту формулу в E2: =COUNTIFS($A$2:$A$10, "="&$D2,$B$2:$B$10,"="&E$1)

Обратите внимание: заполните ячейку Формулы 1 справа, затем на одну строку вниз, вы получите количество яблок и груш.

Запишите эту формулу в G2: =COUNTIFS($A$2:$A$10, "="&$D2,$C$2:$C$10,"=No")

Заметьте, заполните Формула-1 на ряд, вы получите количество неядовитых яблок и груш.

Наконец, суммируйте неядовитую колонку.