Резиме
У одређеним случајевима можете да користите СУМИФС као формулу за проналажење да бисте преузели нумеричку вредност. У приказаном примеру, формула у Г6 је:
=SUMIFS(sales,region,G4,quarter,G5)
где су регион (Б5: Б20), квартал (Ц5: Ц20) и продаја (Д5: Д20) именовани опсези.
Резултат је продаја у К3 за Централни регион, 127.250.
Објашњење
Ако сте нови у функцији СУМИФС, овде можете пронаћи основни преглед са многим примерима.
Функција СУМИФС дизајнирана је за збрајање нумеричких вредности на основу једног или више критеријума. Међутим, у одређеним случајевима можда ћете моћи да користите СУМИФС да бисте „потражили“ нумеричку вредност која испуњава тражене критеријуме. Главни разлози за то су једноставност и брзина.
У приказаном примеру имамо кварталне податке о продаји за четири региона. Почињемо давањем СУМИФС-а опсега збира, а први услов, који тестира регион на вредност у Г4, "Централ":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Укупни опсег је продаја (Д5: Д20)
- Опсег критеријума 1 је регион (Б5: Б20)
- Критеријум 1 је Г4 („Централни“)
Затим додајемо други пар опсега / критеријума који проверава четвртину:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Распон критеријума 2 је четвртина (Ц5: Ц20)
- Критеријум 2 је Г5 („К3“)
Са овим критеријумима, СУМИФС враћа 127.250, централни К3 продајни број.
Понашање СУМИФС-а је збрајање свих одговарајућих вредности. Међутим, пошто постоји само једна одговарајућа вредност, резултат је исти као и сама вредност.
Испод ћемо погледати неколико опција формуле за тражење.
Опције формуле за тражење
Овај одељак укратко даје преглед осталих опција формула које дају исти резултат. Са изузетком СУМПРОДУЦТ-а (на дну), ово су традиционалније формуле претраживања које проналазе положај циљне вредности и враћају вредност на тој локацији.
Уз ВЛООКУП
Нажалост, ВЛООКУП није добро решење овог проблема. Помоћу помоћне колоне могуће је изградити ВЛООКУП формулу која се подудара са више критеријума (пример овде), али то је незгодан процес који захтева да се петљате са изворним подацима.
Уз ИНДЕКС и УТАКМИЦУ
ИНДЕКС и МАТЦХ је врло флексибилна комбинација претраживања која се може користити за све врсте проблема претраживања, а овај пример није изузетак. Помоћу ИНДЕКС-а и МАТЦХ-а можемо претраживати продају по регионима и кварталима помоћу формуле низа попут ове:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер.
Трик овог приступа је употреба логичке логике са операцијама низа унутар функције МАТЦХ за изградњу низа од 1с и 0с као низа претраживања. Тада можемо тражити од функције МАТЦХ да пронађе број 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(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)
А КСЛООКУП враћа исти резултат као горе, 127.250.
Више: КСЛООКУП са више критеријума
Уз ЛООКУП
Функција ЛООКУП је старија функција у програму Екцел за коју многи људи ни не знају. Једна од кључних предности ЛООКУП-а је та што он може нативно да обрађује низове. Међутим, ЛООКУП има неколико јасних слабости:
- Није могуће закључати у „режиму тачног подударања“
- Увек претпоставља да су подаци претраживања сортирани, АЗ
- Увек враћа приближно подударање (ако се не може наћи тачно подударање)
Упркос томе, ЛООКУП се може користити за решавање овог проблема овако:
=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
Ако ситуација не испуњава оба захтева, СУМИФС није добар избор.
