Резюме
У деяких випадках ви можете використовувати SUMIFS, як формулу пошуку, щоб отримати числове значення. У наведеному прикладі формула в G6 має вигляд:
=SUMIFS(sales,region,G4,quarter,G5)
де регіон (B5: B20), квартал (C5: C20) та продажі (D5: D20) названі діапазонами.
Результатом є продажі в третьому кварталі для Центрального регіону - 127 250.
Пояснення
Якщо ви новачок у функції SUMIFS, тут ви можете знайти базовий огляд із багатьма прикладами.
Функція SUMIFS призначена для підсумовування числових значень на основі одного або декількох критеріїв. Однак у конкретних випадках ви можете використовувати SUMIFS для "пошуку" числового значення, яке відповідає необхідним критеріям. Основні причини для цього - простота та швидкість.
У наведеному прикладі ми маємо щоквартальні дані про продаж для чотирьох регіонів. Ми починаємо з того, що надаємо SUMIFS діапазон сум, а перша умова, яка перевіряє область на значення в G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Сумарний асортимент - це продажі (D5: D20)
- Діапазон критеріїв 1 - це регіон (B5: B20)
- Критерій 1 - G4 ("Центральний")
Потім ми додаємо другу пару діапазонів / критеріїв, яка перевіряє квартал:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Діапазон критеріїв 2 - чверть (C5: C20)
- Критерій 2 - G5 ("Q3")
З урахуванням цих критеріїв SUMIFS повертає 127 250, номер продажу в Центральному кварталі 3.
Поведінка SUMIFS полягає в підсумовуванні всіх відповідних значень. Однак, оскільки існує лише одне збіжне значення, результат збігається із самим значенням.
Нижче ми розглянемо кілька варіантів формули пошуку.
Параметри формули пошуку
У цьому розділі коротко розглядаються інші варіанти формул, що дають однаковий результат. За винятком SUMPRODUCT (внизу), це більш традиційні формули пошуку, які визначають позицію цільового значення та повертають значення в цьому місці.
За допомогою VLOOKUP
На жаль, VLOOKUP не є гарним рішенням цієї проблеми. За допомогою допоміжного стовпця можна створити формулу VLOOKUP для збігу з кількома критеріями (приклад тут), але це незручний процес, який вимагає від вас повозитися з вихідними даними.
За допомогою INDEX та MATCH
INDEX і MATCH - це дуже гнучка комбінація пошуку, яку можна використовувати для будь-яких проблем пошуку, і цей приклад не є винятком. За допомогою INDEX та MATCH ми можемо шукати продажі за регіонами та кварталами за формулою масиву, як це:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Фокус при такому підході полягає у використанні логічної логіки з операціями над масивом усередині функції MATCH для побудови масиву з 1s та 0s як масив підстановки. Тоді ми можемо попросити функцію MATCH знайти число 1. Після створення масиву підстановки формула вирішує:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Залишившись лише 1 в масиві підстановки, MATCH повертає позицію 11 функції INDEX, а INDEX повертає номер продажу в цій позиції, 127 250.
Докладніше див .: ІНДЕКС та ЗБІГ з кількома критеріями
З XLOOKUP
XLOOKUP - це нова гнучка функція в Excel, яка може обробляти масиви безпосередньо. З XLOOKUP ми можемо використовувати абсолютно той самий підхід, що і в INDEX та MATCH, використовуючи логічну логіку та операції з масивами для створення масиву підстановки:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Після запуску операцій з масивом формула вирішує:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
І XLOOKUP повертає той самий результат, що і вище, 127 250.
Більше: XLOOKUP з кількома критеріями
З ПЕРЕГЛЯДОМ
Функція LOOKUP - це давня функція в Excel, про яку багато людей навіть не знають. Однією з ключових сильних сторін LOOKUP є те, що він може обробляти масиви безпосередньо. Однак у LOOKUP є кілька чітких слабких сторін:
- Не вдається заблокувати в "режимі точної відповідності"
- Завжди припускає, що дані пошуку відсортовані, AZ
- Завжди повертає приблизний збіг (якщо точного збігу не вдається знайти)
Тим не менше, LOOKUP можна використовувати для вирішення цієї проблеми так:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
що спрощує:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Якщо ситуація не відповідає обом вимогам, SUMIFS - невдалий вибір.
