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)))
- range_to_count: Rentang untuk menghitung nilai yang hilang. Berikut mengacu pada daftar B.
- rentang_pencarian: Rentang untuk dibandingkan dengan range_to_count. Berikut mengacu pada daftar A.
- 0: The 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:
=SUMPRODUCT(--ISNA(MATCH(F6: F8,B6: B10,0)))
Penjelasan rumus
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- COCOK(F6:F8,B6:B10,0): The 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 range_to_count. Berikut mengacu pada daftar A.
- range_to_count: Rentang untuk menghitung nilai yang hilang. Berikut mengacu pada daftar B.
- 0: The 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:
=SUMPRODUCT(--(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
Apakah Anda ingin menyelesaikan pekerjaan harian Anda dengan cepat dan sempurna? Kutools for Excel menghadirkan 300 fitur canggih yang canggih (Gabungkan buku kerja, jumlahkan berdasarkan warna, pisahkan konten sel, tanggal konversi, dan seterusnya...) dan hemat 80% waktu untuk Anda.
- Dirancang untuk 1500 skenario kerja, membantu Anda memecahkan 80% masalah Excel.
- Kurangi ribuan klik keyboard dan mouse setiap hari, hilangkan mata dan tangan Anda yang lelah.
- Menjadi ahli Excel dalam 3 menit. Tidak perlu lagi mengingat rumus dan kode VBA yang menyakitkan.
- Uji coba gratis tanpa batas selama 30 hari. Jaminan uang kembali 60 hari. Upgrade dan dukungan gratis selama 2 tahun.
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.
- Membawa Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Firefox, dan Internet Explorer Baru.
