Satu masalah yang sering muncul di Excel adalah menghitung atau menjumlahkan berdasarkan beberapa kondisi ATAU. Misalnya, mungkin Anda perlu menganalisis data dan menghitung pesanan di Seattle atau Denver, untuk item yang Merah, Biru, atau Hijau? Ini bisa sangat rumit, jadi tentu saja itu membuat tantangan yang bagus!
Tantangan
Data di bawah ini mewakili pesanan, satu pesanan per baris. Ada tiga tantangan terpisah.
Rumus apa di F9, G9, dan H9 yang akan menghitung pesanan dengan benar dengan kondisi berikut:
- F9 - Tshirt atau Hoodie
- G9 - (Tshirt atau Hoodie) dan (Merah, Biru, atau Hijau)
- H9 - (Tshirt atau Hoodie) dan (Merah, Biru, atau Hijau) dan (Denver atau Seattle)
Bayangan hijau diterapkan dengan pemformatan bersyarat dan menunjukkan nilai yang cocok untuk setiap kumpulan kriteria ATAU di setiap kolom.
Untuk kenyamanan Anda, tersedia rentang bernama berikut:
Item = B3: B16
warna = C3: C16
kota = D3: D16
Lembar kerja terlampir. Tinggalkan jawaban Anda di bawah sebagai komentar!
Jawab (klik untuk memperluas)Solusi saya menggunakan SUMPRODUCT dengan ISNUMBER dan MATCH seperti ini:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Yang akan menghitung pesanan di mana…
- Item adalah (Tshirt atau Hoodie) dan
- Warnanya adalah (Merah, Biru, atau Hijau) dan
- Kota adalah (Denver atau Seattle)
Beberapa orang juga menyarankan pendekatan yang sama. Saya suka struktur ini karena mudah berskala untuk menangani lebih banyak kriteria, dan juga berfungsi dengan referensi sel (bukan nilai yang di-hardcode). Dengan referensi sel, rumus di H9 adalah:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Kunci formula ini adalah konstruksi ISNUMBER + MATCH. MATCH adalah setup "mundur" - nilai pencarian berasal dari data, dan kriteria digunakan untuk array. Hasilnya adalah larik kolom tunggal setiap kali MATCH digunakan. Larik ini berisi kesalahan # N / A (tidak ada kecocokan) atau angka (kecocokan), jadi ISNUMBER digunakan untuk mengonversi ke nilai boolean TRUE dan FALSE. Operasi mengalikan array bersama-sama memaksa nilai TRUE FALSE menjadi 1 dan 0, dan array terakhir di dalam SUMPRODUCT berisi 1s di mana baris memenuhi kriteria. SUMPRODUCT lalu menjumlahkan larik dan mengembalikan hasilnya.
