Özet
Belirli durumlarda, SUMIFS'i sayısal bir değer almak için arama formülü gibi kullanabilirsiniz. Gösterilen örnekte, G6'daki formül şöyledir:
=SUMIFS(sales,region,G4,quarter,G5)
burada bölgesi (B5: B20), çeyrek (C5: C20) ve satış (D5: D20) aralıkları adlandırılır.
Sonuç, Orta bölge için 3. çeyrek satışları, 127.250.
Açıklama
SUMIFS işlevinde yeniyseniz, burada birçok örnekle birlikte temel bir genel bakış bulabilirsiniz.
SUMIFS işlevi, sayısal değerleri bir veya daha fazla ölçüte göre toplamak için tasarlanmıştır. Ancak belirli durumlarda, gerekli ölçütleri karşılayan sayısal bir değeri "aramak" için SUMIFS kullanabilirsiniz. Bunu yapmanın ana nedenleri basitlik ve hızdır.
Gösterilen örnekte, dört bölge için üç aylık satış verilerimiz var. SUMIFS'e bir toplam aralığı ve bölgeyi G4'teki değer için test eden ilk koşul, "Merkez" vererek başlayalım:
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Sum aralığıdır satış (D5: D20)
- Ölçüt aralığı 1 bölgedir (B5: B20)
- Ölçüt 1 G4'tür ("Merkez")
Ardından, çeyreği kontrol eden ikinci aralık / ölçüt çiftini ekliyoruz:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Ölçüt aralığı 2 çeyrektir (C5: C20)
- Ölçüt 2 G5 ("Q3")
Bu kriterler ile SUMIFS, Central Q3 satış numarası olan 127.250'yi döndürür.
ETOPLA işlevinin davranışı, eşleşen tüm değerleri toplamaktır. Bununla birlikte, yalnızca bir eşleşen değer olduğundan, sonuç değerin kendisiyle aynıdır.
Aşağıda, birkaç arama formülü seçeneğine bakıyoruz.
Arama formülü seçenekleri
Bu bölüm, aynı sonucu veren diğer formül seçeneklerini kısaca gözden geçirmektedir. SUMPRODUCT haricinde (altta), bunlar hedef değerin konumunu bulan ve değeri o konuma döndüren daha geleneksel arama formülleridir.
DÜŞEYARA ile
Maalesef, DÜŞEYARA bu soruna iyi bir çözüm değildir. Bir yardımcı sütunla, birden çok ölçütle eşleşecek bir DÜŞEYARA formülü oluşturmak mümkündür (örnek burada), ancak bu, kaynak verilerle uğraşmanızı gerektiren garip bir işlemdir.
INDEX ve MATCH ile
INDEX ve MATCH, her türlü arama problemi için kullanılabilen çok esnek bir arama kombinasyonudur ve bu örnek bir istisna değildir. INDEX ve MATCH ile, aşağıdaki gibi bir dizi formülüyle bölgeye ve çeyreğe göre satışları arayabiliriz:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.
Bu yaklaşımın püf noktası, arama dizisi olarak 1'ler ve 0'lardan oluşan bir dizi oluşturmak için MATCH işlevi içindeki dizi işlemleriyle boole mantığını kullanmaktır. Ardından, MATCH işlevinin 1 sayısını bulmasını isteyebiliriz. Arama dizisi oluşturulduktan sonra formül şu şekilde çözülür:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Arama dizisinde yalnızca 1 kaldığında, KAÇINCI, INDEX işlevine 11'lik bir konum döndürür ve INDEX, bu konumdaki 127.250 satış numarasını döndürür.
Daha fazla ayrıntı için bkz: INDEX ve MATCH ile birden çok kriter
XLOOKUP ile
XLOOKUP, Excel'de dizileri yerel olarak işleyebilen yeni ve esnek bir işlevdir. XLOOKUP ile, bir arama dizisi oluşturmak için boole mantığını ve dizi işlemlerini kullanarak INDEX ve MATCH ile tam olarak aynı yaklaşımı kullanabiliriz:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Dizi işlemleri çalıştırıldıktan sonra formül şu şekilde çözülür:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Ve XLOOKUP, yukarıdaki ile aynı sonucu, 127,250 döndürür.
Daha fazla: Birden çok kriterle XLOOKUP
LOOKUP ile
ARA işlevi, Excel'de birçok kişinin bilmediği daha eski bir işlevdir. LOOKUP'ın temel güçlü yönlerinden biri, dizileri yerel olarak işleyebilmesidir. Ancak, ARA işlevinin birkaç belirgin zayıf yönü vardır:
- "Tam eşleme modunda" kilitlenemez
- Her zaman arama verilerinin sıralandığını varsayar, AZ
- Her zaman yaklaşık bir eşleşme döndürür (tam eşleşme bulunamazsa)
Yine de, ARA bu problemi şöyle güzel bir şekilde çözmek için kullanılabilir:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
aşağıdakileri basitleştirir:
=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
Durum her iki gereksinimi de karşılamıyorsa, SUMIFS iyi bir seçim değildir.
