Одна из проблем, которая часто возникает в Excel, - это подсчет или суммирование на основе нескольких условий ИЛИ. Например, возможно, вам нужно проанализировать данные и подсчитать заказы в Сиэтле или Денвере на товары красного, синего или зеленого цвета? Это может быть на удивление непросто, поэтому, естественно, это хороший вызов!
Соревнование
Приведенные ниже данные представляют заказы, по одному заказу в строке. Есть три отдельных проблемы.
Какие формулы в F9, G9 и H9 будут правильно подсчитывать заказы при следующих условиях:
- F9 - футболка или худи
- G9 - (футболка или худи) и (красный, синий или зеленый)
- H9 - (футболка или толстовка) и (красный, синий или зеленый) и (Денвер или Сиэтл)
Зеленая заливка применяется с условным форматированием и указывает совпадающие значения для каждого набора критериев ИЛИ в каждом столбце.
Для вашего удобства доступны следующие именованные диапазоны:
item = B3: B16
цвет = C3: C16
город = D3: D16
Рабочий лист прилагается. Оставляйте свои ответы ниже в виде комментариев!
Ответ (нажмите, чтобы развернуть)В моем решении SUMPRODUCT используется с ISNUMBER и MATCH следующим образом:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Что будет считать заказы, где…
- Товар (футболка или толстовка) и
- Цвет (красный, синий или зеленый) и
- Город (Денвер или Сиэтл)
Несколько человек также предложили такой же подход. Мне нравится эта структура, потому что она легко масштабируется для обработки большего количества критериев, а также работает со ссылками на ячейки (вместо жестко заданных значений). Со ссылками на ячейки формула в H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Ключом к этой формуле является конструкция ЕЧИСЛО + ПОИСКПОЗ. ПОИСКПОЗ настраивается «в обратном порядке» - значения поиска берутся из данных, а критерии используются для массива. В результате каждый раз, когда используется MATCH, получается массив из одного столбца. Этот массив содержит ошибки # N / A (нет совпадений) или числа (совпадения), поэтому ISNUMBER используется для преобразования в логические значения TRUE и FALSE. Операция умножения массивов приводит к приведению значений TRUE FALSE к единицам и нулям, а последний массив внутри SUMPRODUCT содержит единицы, где строки соответствуют критериям. СУММПРОИЗВ суммирует массив и возвращает результат.
