Hitung nilai yang hilang
Misalkan Anda memiliki dua daftar, untuk menghitung jumlah total nilai dalam satu daftar yang tidak ada di daftar lain, Anda dapat menggunakan a SUMPRODUK rumus dengan bantuan MATCH dan fungsi ISNA, atau COUNTIF fungsi.
Hitung nilai yang hilang dengan SUMPRODUCT, MATCH, dan ISNA
Hitung nilai yang hilang dengan SUMPRODUCT dan COUNTIF
Hitung nilai yang hilang dengan SUMPRODUCT, MATCH, dan ISNA
Untuk menghitung jumlah total nilai dalam daftar B yang hilang dari daftar A seperti yang ditunjukkan di atas, Anda dapat menggunakan fungsi MATCH terlebih dahulu untuk mengembalikan larik posisi relatif nilai dari daftar B di daftar A. Jika nilai tidak ada di daftar A, kesalahan #N/A akan dikembalikan. Fungsi ISNA kemudian akan mengidentifikasi kesalahan #N/A dan SUMPRODUCT akan menghitung jumlah total kesalahan.
Sintaks umum
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- rentang_ke_hitungan: Rentang untuk menghitung nilai yang hilang. Berikut mengacu pada daftar B.
- rentang_pencarian: Rentang untuk dibandingkan dengan rentang_ke_hitungan. Berikut mengacu pada daftar A.
- 0: Grafik kecocokan_tipe 0 memaksa MATCH untuk melakukan pencocokan tepat.
Untuk menghitung jumlah total nilai dalam daftar B yang hilang dari daftar A, silakan salin atau masukkan rumus di bawah ini di sel H6, dan tekan Enter untuk mendapatkan hasil:
=SUMPRODUK(--ISNA(PERTANDINGAN(F6: F8,B6: B10,0)))
Penjelasan rumus
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- COCOK(F6:F8,B6:B10,0): Grafik kecocokan_tipe 0 memaksa fungsi MATCH untuk mengembalikan nilai numerik yang menunjukkan posisi relatif nilai melalui sel F6 untuk F8 dalam jangkauan B6: B10. Jika nilai tidak ada dalam daftar A, kesalahan #N/A akan dikembalikan. Jadi, hasilnya akan menjadi array seperti ini: {2;3;#T/A}.
- ISNA(COCOK(F6:F8,B6:B10,0)) = ISNA({2;3;#T/A}): ISNA berfungsi untuk mengetahui apakah suatu nilai merupakan kesalahan “#N/A” atau tidak. Jika ya, fungsi akan mengembalikan TURE; Jika tidak, itu akan mengembalikan FALSE. Jadi, rumus ISNA akan kembali {SALAH; SALAH; BENAR}.
- SUMPRODUK(--ISNA(COCOK(F6:F8,B6:B10,0))) = SUMPRODUK(--{SALAH; SALAH; BENAR}): Tanda negatif ganda mengubah TRUE menjadi 1s dan FALSEs menjadi 0s: {0; 1; 0}. Kemudian fungsi SUMPRODUCT mengembalikan jumlah: 1.
Hitung nilai yang hilang dengan SUMPRODUCT dan COUNTIF
Untuk menghitung jumlah total nilai dalam daftar B yang hilang dari daftar A, Anda juga dapat menggunakan fungsi COUNTIF untuk mengetahui apakah ada nilai dalam daftar A atau tidak dengan kondisi “=0”, karena 0 akan dihasilkan jika ada nilai yang hilang. SUMPRODUCT kemudian akan menghitung jumlah total nilai yang hilang.
Sintaks umum
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- rentang_pencarian: Rentang untuk dibandingkan dengan rentang_ke_hitungan. Berikut mengacu pada daftar A.
- rentang_ke_hitungan: Rentang untuk menghitung nilai yang hilang. Berikut mengacu pada daftar B.
- 0: Grafik kecocokan_tipe 0 memaksa MATCH untuk melakukan pencocokan tepat.
Untuk menghitung jumlah total nilai dalam daftar B yang hilang dari daftar A, silakan salin atau masukkan rumus di bawah ini di sel H6, dan tekan Enter untuk mendapatkan hasil:
=SUMPRODUK(--(COUNTIF(B6: B10,F6: F8)=0))
Penjelasan rumus
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10,F6:F8): Fungsi COUNTIF menghitung jumlah kemunculan sel melalui F6 untuk F8 dalam jangkauan B6: B10. Hasilnya akan menjadi array seperti ini: {1; 1; 0}.
- - (COUNTIF(B6:B10,F6:F8)= 0) = - ({1; 1; 0}= 0): Cuplikan {1;1;0}=0 menghasilkan larik TRUE dan FALSE {SALAH; SALAH; BENAR}. Tanda negatif ganda kemudian mengubah TRUE menjadi 1s dan FALSEs menjadi 0s. Array terakhir adalah seperti ini: {0; 0; 1}.
- SUMPRODUK(- (COUNTIF(B6:B10,F6:F8)= 0)) = JUMLAH PRODUK({0; 0; 1}): SUMPRODUCT kemudian mengembalikan jumlah: 1.
Fungsi terkait
Di Excel, fungsi SUMPRODUCT bisa digunakan untuk mengalikan dua atau lebih kolom atau larik bersama-sama, lalu mendapatkan jumlah produk. Faktanya, SUMPRODUCT adalah fungsi yang berguna yang dapat membantu menghitung atau menjumlahkan nilai sel dengan beberapa kriteria seperti fungsi COUNTIFS atau SUMIFS. Artikel ini akan memperkenalkan sintaks fungsi dan beberapa contoh untuk fungsi SUMPRODUCT ini.
Fungsi Excel MATCH mencari nilai tertentu dalam rentang sel, dan mengembalikan posisi relatif dari nilai tersebut.
Fungsi COUNTIF adalah fungsi statistik di Excel yang digunakan untuk menghitung jumlah sel yang memenuhi suatu kriteria. Mendukung operator logika (<>, =, >, dan <), dan wildcard (? dan *) untuk pencocokan sebagian.
Rumus Terkait
Ada kasus ketika Anda perlu membandingkan dua daftar untuk memeriksa apakah nilai daftar A ada di daftar B di Excel. Misalnya, Anda memiliki daftar produk, dan Anda ingin memeriksa apakah produk dalam daftar Anda ada di daftar produk yang disediakan oleh pemasok Anda. Untuk menyelesaikan tugas ini, di sini kami mencantumkan tiga cara di bawah ini, silakan pilih yang Anda suka.
Artikel ini akan fokus pada rumus Excel untuk menghitung sel yang persis sama dengan string teks yang Anda tentukan atau sebagian sama dengan string teks yang diberikan seperti gambar di bawah ini. Pertama, ini akan menjelaskan sintaks rumus dan argumen, memberikan contoh agar Anda lebih memahami.
Hitung jumlah sel bukan di antara dua angka yang diberikan
Untuk menghitung jumlah sel di antara dua angka adalah tugas umum bagi kami di Excel, tetapi, dalam kasus tertentu, Anda mungkin ingin menghitung sel bukan di antara dua angka yang diberikan. Misalnya, saya memiliki daftar produk dengan penjualan dari Senin hingga Minggu, sekarang, saya perlu mendapatkan jumlah sel yang tidak berada di antara angka rendah dan tinggi tertentu seperti gambar di bawah ini. Artikel ini, saya akan memperkenalkan beberapa rumus untuk menangani tugas ini di Excel.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.