Satu masalah yang banyak muncul di Excel ialah mengira atau menjumlahkan berdasarkan beberapa keadaan ATAU. Contohnya, mungkin anda perlu menganalisis data dan menghitung pesanan di Seattle atau Denver, untuk item yang berwarna Merah, Biru, atau Hijau? Ini boleh mengejutkan, jadi secara semula jadi ia menjadi cabaran yang baik!
Cabaran
Data di bawah menunjukkan pesanan, satu pesanan setiap baris. Terdapat tiga cabaran yang berasingan.
Apa formula dalam F9, G9, dan H9 yang akan mengira pesanan dengan betul dengan syarat 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 sepadan untuk setiap set kriteria ATAU di setiap lajur.
Untuk kemudahan anda, rentang bernama berikut tersedia:
item = B3: B16
warna = C3: C16
bandar = D3: D16
Lembaran kerja dilampirkan. Tinggalkan jawapan anda di bawah sebagai komen!
Jawapan (klik untuk mengembangkan)Penyelesaian 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 mengira pesanan di mana …
- Item adalah (Tshirt atau Hoodie) dan
- Warna adalah (Merah, Biru, atau Hijau) dan
- Bandar adalah (Denver atau Seattle)
Beberapa orang juga mencadangkan pendekatan yang sama. Saya suka struktur ini kerana skala dengan mudah untuk menangani lebih banyak kriteria, dan juga berfungsi dengan rujukan sel (bukan nilai keras) Dengan rujukan sel, formula dalam H9 adalah:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Kunci formula ini ialah pembinaan ISNUMBER + MATCH. MATCH disiapkan "ke belakang" - nilai pencarian berasal dari data, dan kriteria digunakan untuk array. Hasilnya adalah susunan lajur tunggal setiap kali MATCH digunakan. Susunan ini mengandungi sama ada # N / A ralat (tidak ada padanan) atau angka (padanan), jadi ISNUMBER digunakan untuk menukar ke nilai boolean BENAR dan SALAH. Operasi mengalikan array bersama-sama membentuk nilai BENAR PALSU hingga 1s dan 0s, dan susunan terakhir di dalam SUMPRODUCT mengandungi 1s di mana baris memenuhi kriteria. SUMPRODUCT kemudian menjumlahkan array dan mengembalikan hasilnya.
