Обобщение
В определени случаи можете да използвате SUMIFS като формула за търсене, за да извлечете числова стойност. В показания пример формулата в G6 е:
=SUMIFS(sales,region,G4,quarter,G5)
където регионът (B5: B20), тримесечието (C5: C20) и продажбите (D5: D20) са наречени диапазони.
Резултатът е Q3 продажби за Централния регион, 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, номер за продажби в Централния Q3.
Поведението на SUMIFS е да сумира всички съвпадащи стойности. Тъй като обаче има само една съвпадаща стойност, резултатът е същият като самата стойност.
По-долу разглеждаме няколко опции за формула за търсене.
Опции за формула за търсене
Този раздел прави кратък преглед на други опции за формули, които дават същия резултат. С изключение на SUMPRODUCT (в долната част), това са по-традиционни формули за търсене, които намират позицията на целевата стойност и връщат стойността на това място.
С VLOOKUP
За съжаление VLOOKUP не е добро решение на този проблем. С помощна колона е възможно да се изгради формула VLOOKUP, която да съвпада с множество критерии (пример тук), но това е неудобен процес, който изисква да се занимавате с изходните данни.
С ИНДЕКС и МАТЧ
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
Функцията 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 не е добър избор.
