Excel'de çokça ortaya çıkan sorunlardan biri, birden çok OR koşulu temelinde sayma veya toplamadır. Örneğin, Kırmızı, Mavi veya Yeşil öğeler için Seattle veya Denver'daki verileri analiz etmeniz ve siparişleri saymanız gerekebilir. Bu şaşırtıcı derecede zor olabilir, bu yüzden doğal olarak iyi bir meydan okumadır!
Meydan okuma
Aşağıdaki veriler, satır başına bir sipariş olacak şekilde siparişleri temsil etmektedir. Üç ayrı zorluk var.
F9, G9 ve H9'daki hangi formüller, aşağıdaki koşullara sahip siparişleri doğru şekilde sayacaktır:
- F9 - Tişört veya Kapşonlu
- G9 - (Tişört veya Kapüşonlu) ve (Kırmızı, Mavi veya Yeşil)
- H9 - (Tişört veya Kapüşonlu Üst) ve (Kırmızı, Mavi veya Yeşil) ve (Denver veya Seattle)
Yeşil gölgeleme, koşullu biçimlendirme ile uygulanır ve her sütundaki her bir OR ölçütü kümesi için eşleşen değerleri gösterir.
Size kolaylık sağlamak için aşağıdaki adlandırılmış aralıklar mevcuttur:
öğe = B3: B16
rengi = C3: C16
şehir = D3: D16
Çalışma sayfası eklenmiştir. Cevaplarınızı aşağıya yorum olarak bırakın!
Cevap (genişletmek için tıklayın)Benim çözümüm ISNUMBER ve MATCH ile SUMPRODUCT kullanıyor:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Hangi durumlarda siparişler sayılır …
- Öğe (Tshirt veya Hoodie) ve
- Renk (Kırmızı, Mavi veya Yeşil) ve
- Şehir (Denver veya Seattle)
Birkaç kişi de aynı yaklaşımı önerdi. Bu yapıyı seviyorum çünkü daha fazla ölçütü işlemek için kolayca ölçekleniyor ve aynı zamanda hücre referanslarıyla da çalışıyor (sabit kodlanmış değerler yerine). Hücre referanslarıyla, H9'daki formül:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Bu formülün anahtarı ISNUMBER + MATCH yapısıdır. MATCH "geriye doğru" kurulumdur - arama değerleri verilerden gelir ve dizi için kriterler kullanılır. Sonuç, MATCH her kullanıldığında tek sütun dizisidir. Bu dizi #YOK hataları (eşleşme yok) veya sayılar (eşleşme) içerir, bu nedenle ISNUMBER, DOĞRU ve YANLIŞ boole değerlerine dönüştürmek için kullanılır. Dizileri birlikte çarpma işlemi, DOĞRU YANLIŞ değerlerini 1'ler ve 0'lara zorlar ve SUMPRODUCT içindeki son dizi, satırların kriterleri karşıladığı 1'ler içerir. SUMPRODUCT daha sonra diziyi toplar ve sonucu döndürür.
