Формула Excel: СУММЕСЛИМН и другие формулы поиска -

Резюме

В некоторых случаях вы можете использовать СУММЕСЛИМН как формулу поиска для получения числового значения. В показанном примере формула в G6:

=SUMIFS(sales,region,G4,quarter,G5)

где регион (B5: B20), квартал (C5: C20) и продажи (D5: D20) являются именованными диапазонами.

Итог - продажи по Центральному региону за 3 квартал - 127 250.

Объяснение

Если вы новичок в функции СУММЕСЛИМН, вы можете найти здесь базовый обзор с множеством примеров.

Функция СУММЕСЛИМН предназначена для суммирования числовых значений на основе одного или нескольких критериев. Однако в определенных случаях вы можете использовать СУММЕСЛИМН для «поиска» числового значения, которое соответствует требуемым критериям. Основные причины для этого - простота и скорость.

В показанном примере у нас есть квартальные данные о продажах для четырех регионов. Начнем с того, что зададим СУММЕСЛИМН диапазон сумм и первое условие, которое проверяет область на значение в G4, «Центральное»:

=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»)

С этими критериями СУММЕСЛИМН возвращает 127 250, центральный номер продаж за 3 квартал.

Функция СУММЕСЛИМН суммирует все совпадающие значения. Однако, поскольку существует только одно совпадающее значение, результат будет таким же, как и само значение.

Ниже мы рассмотрим несколько вариантов формулы поиска.

Параметры формулы поиска

В этом разделе кратко рассматриваются другие варианты формул, дающие тот же результат. За исключением SUMPRODUCT (внизу), это более традиционные формулы поиска, которые определяют положение целевого значения и возвращают значение в этом месте.

С ВПР

К сожалению, VLOOKUP - не лучшее решение этой проблемы. С помощью вспомогательного столбца можно построить формулу ВПР для соответствия нескольким критериям (пример здесь), но это неудобный процесс, требующий от вас возиться с исходными данными.

С ИНДЕКСОМ и МАТЧЕМ

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

(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))

Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter.

Уловка этого подхода заключается в использовании логической логики с операциями с массивами внутри функции ПОИСКПОЗ для построения массива из единиц и нулей в качестве массива поиска. Затем мы можем попросить функцию ПОИСКПОЗ найти число 1. После создания массива поиска формула принимает следующий вид:

=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))

Когда в поисковом массиве остается только 1, ПОИСКПОЗ возвращает 11-й позиции функции ИНДЕКС, а ИНДЕКС возвращает номер продаж в этой позиции - 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 с несколькими критериями

С ПОМОЩЬЮ

Функция ПРОСМОТР - это более старая функция в 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: