- COUNTIFS, SUMIFS ve AVERAGEIFS'te ölçüt çiftlerini doğru yazmak
- Ölçütlerde karşılaştırma işleçlerini, joker karakterleri ve hücre başvurularını kullanmak
- İç içe IF'leri IFS, SWITCH ya da bir arama tablosuyla değiştirmek
- IFERROR ile IFNA arasındaki farkı bilmek
Bir çevrim içi mağazanın yöneticisi soruyor: “Bakü'de kaç elektronik siparişi var ve toplamı ne kadar? 500 ₼'nin üstündeki ödenmiş siparişlerin tutarı ne?” COUNTIF ve SUMIF yalnızca tek bir koşulu kontrol eder. Bu derste onların büyük kardeşleriyle, adının sonunda S harfi olan işlevlerle çalışacaksın.
Örnek tablo A1:D11: City, Category, Amount, Status ve 10 sipariş. Bakü: 850 (Electronics, Paid), 540 (Home, Pending), 460 (Electronics, Pending), 275 (Home, Paid), 1320 (Electronics, Paid). Gence: 320 (Home, Paid), 980 (Electronics, Paid), 150 (Home, Paid). Sumgayıt: 1200 (Electronics, Paid), 610 (Home, Pending).
Birden çok ölçüte göre sayma ve toplama: COUNTIFS, SUMIFS, AVERAGEIFS
- sum_rangetoplanacak sayılar; SUMIFS'te bu ilk bağımsız değişkendir
- criteria_range, criteria“nerede kontrol edilecek — ne kontrol edilecek” çifti; en fazla 127 çift
COUNTIFS yalnızca çiftlerden oluşur; AVERAGEIFS ise SUMIFS gibi önce ortalaması alınacak aralığı alır. Tüm koşullar aynı anda sağlanmalıdır (VE mantığı).
| Soru | Formül | Sonuç |
|---|---|---|
| Bakü'de kaç elektronik siparişi var? | =COUNTIFS(A2:A11,"Baku",B2:B11,"Electronics") | 3 |
| Toplamları | =SUMIFS(C2:C11,A2:A11,"Baku",B2:B11,"Electronics") | 2630 |
| Ödenmiş ve ≥ 500 ₼ | =SUMIFS(C2:C11,D2:D11,"Paid",C2:C11,">=500") | 4350 |
| 500 ile 1000 arasında kaç sipariş? | =COUNTIFS(C2:C11,">=500",C2:C11,"<=1000") | 4 |
| Gence'nin ortalama siparişi | =AVERAGEIFS(C2:C11,A2:A11,"Ganja") | 483,33 |
| Bakü dışındaki her yer | =SUMIFS(C2:C11,A2:A11,"<>Baku") | 3260 |
| “E” ile başlayan kategoriler | =COUNTIFS(B2:B11,"E*") | 5 |
IFS ve SWITCH: iç içe IF olmadan kararlar
Kurallar: ödenmemiş siparişe prim yok; ödenmiş sipariş 500 ₼ ya da üstündeyse yönetici %5, değilse %2 alır. Teslimat: 1000 ₼'den itibaren ücretsiz, 500 ₼'den itibaren 5 ₼, diğerleri 10 ₼. İlk üç sipariş için hesapla (850 Paid, 320 Paid, 540 Pending).
Çözümü gösterÇözümü gizle
=IFS(D2<>"Paid",0,C2>=500,C2*5%,TRUE,C2*2%)850 Paid → 850 · 0,05 = 42,50 ₼; 320 Paid → 320 · 0,02 = 6,40 ₼; 540 Pending → 0.
Teslimat:
=IFS(C2>=1000,0,C2>=500,5,TRUE,10)850 → 5 ₼; 320 → 10 ₼; 540 → 5 ₼.
Sondaki
TRUE “geri kalan tüm durumlar” demektir; yazmazsan ve hiçbir koşul sağlanmazsa IFS #N/A döndürür.10 siparişin tamamının prim toplamı: 232,40 ₼.
SWITCH bir ifadeyi değerler listesiyle karşılaştırır ve eşleşen sonucu döndürür: =SWITCH(A2,"Baku","Aysel","Ganja","Rauf","Sumgait","Elvin","—") her şehirden sorumlu yöneticiyi, listede olmayan bir şehir için de son değeri (“—”) gösterir. SWITCH yalnızca eşitliği kontrol eder; > ya da < gerekiyorsa IFS'i seç.
| Durum | En iyi seçim |
|---|---|
| İki sonuç | IF |
3–5 eşik, >= karşılaştırmaları | IFS |
| Tam değerler listesi (kod → ad) | SWITCH |
| Çok sayıda dilim ya da sık değişen kurallar | ayrı bir tablo + XLOOKUP |
İç içe mantığın dört kuralı
- Önce koruyucu koşul. Boş ya da geçersiz veriyi ilk kontrol et:
=IF(C2="","",IFS(…)); böylece boş satırlar yanıltıcı sonuç göstermez. - En dar koşul en üstte. Excel ilk doğru koşulda durur; bu yüzden
>=1000,>=500'den önce gelmelidir. - “İkisi de” için AND, istisnalar için OR. “Ödenmiş ve 500'den fazla”
AND(D2="Paid",C2>=500); “VIP ya da 1000'den fazla”OR(…)olur. - Sayıları formülden çıkar. Oranları ve sınırları ayrı hücrelere yaz; kural değişince yüz formülü değil, tek bir hücreyi düzeltirsin.
Hataları yönetmek: IFERROR ve IFNA
=IFERROR(değer, hata_durumunda) her hatayı yakalar: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?. =IFNA(değer, na_durumunda) ise yalnızca #N/A'yı. Aramalarda IFNA daha güvenlidir: “bulunamadı” durumunu gizler, ama formüldeki yazım hatasını (#NAME?) ya da silinmiş bir sütunu (#REF!) göstermeye devam eder.
SUM formülüAlt+=Önemli noktalar
- COUNTIFS, SUMIFS ve AVERAGEIFS tüm ölçütleri aynı anda (VE) kontrol eder; SUMIFS'te toplanacak aralık ilk sıradadır.
- Ölçütler:
">=500","<>Baku","E*"; hücreden almak için">="&G2. - IFS koşulları sırayla kontrol eder; sondaki
TRUE“geri kalan her şey” demektir. - SWITCH tam değerleri karşılaştırır; çok sayıda dilim için ayrı bir tablo daha iyidir.
- IFERROR her hatayı, IFNA yalnızca #N/A'yı yakalar; aramalarda IFNA daha güvenlidir.
Kendini test et
10 soru. Her doğru cevap XP kazandırır.