Одна проблема, яка часто виникає в Excel, - це підрахунок чи підсумовування на основі кількох умов АБО. Наприклад, можливо, вам потрібно проаналізувати дані та підрахувати замовлення в Сіетлі чи Денвері для елементів червоного, синього чи зеленого кольорів? Це може бути напрочуд хитро, тому, природно, це непоганий виклик!
Змагання
Дані нижче представляють замовлення, одне замовлення на рядок. Є три окремі проблеми.
Які формули у F9, G9 та H9 правильно підраховуватимуть замовлення за таких умов:
- F9 - Футболка або балахон
- G9 - (Футболка або балахон) та (Червоний, Синій або Зелений)
- H9 - (Футболка чи балахон) та (Червоний, Синій або Зелений) та (Денвер чи Сіетл)
Зелене затінення застосовується з умовним форматуванням і вказує відповідні значення для кожного набору критеріїв АБО у кожному стовпці.
Для вашої зручності доступні такі названі діапазони:
item = B3: B16
color = C3: C16
city = 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)) )
Ключ до цієї формули - конструкція ISNUMBER + MATCH. MATCH налаштовується "назад" - значення пошуку походять від даних, а для масиву використовуються критерії. Результат - одиничний масив стовпців кожного разу, коли використовується MATCH. Цей масив містить або # N / A помилок (відсутність збігів), або цифр (збіг), тому ISNUMBER використовується для перетворення на логічні значення TRUE і FALSE. Операція множення масивів примушує значення TRUE FALSE до 1s і 0s, а остаточний масив усередині SUMPRODUCT містить 1s, де рядки відповідають критеріям. Потім SUMPRODUCT підсумовує масив і повертає результат.
