Hitung Nilai Unik di Excel

Menghitung Nilai Unik di Excel

Nilai unik di excel muncul dalam daftar item hanya sekali dan rumus untuk menghitung nilai unik di Excel adalah ‘=SUM(IF(COUNTIF(range,range)=1,1,0))’. Tujuan penghitungan nilai unik dan berbeda adalah untuk memisahkannya dari duplikat daftar Excel.

Nilai duplikat muncul di daftar item lebih dari sekali. Nilai yang berbeda mengacu pada semua nilai yang berbeda dari daftar item. Jadi, nilai yang berbeda adalah nilai unik ditambah kejadian pertama dari nilai duplikat.

Misalnya, daftar berisi angka 10, 12, 15, 15, 18, 18, dan 19. Nilai unik dari daftar ini adalah 10, 12, dan 19. Nilai duplikatnya adalah 15 dan 18. Nilai yang berbeda adalah 10 , 12, 15, 18, dan 19.

Artikel ini berfokus pada menghitung nilai Excel yang berbeda. Untuk menghitung nilai unik Excel, lihat pertanyaan pertama di bawah judul ‘pertanyaan umum’ di artikel ini.

Bagaimana Cara Menghitung Nilai Berbeda di Excel?

Metode menghitung nilai yang berbeda di Excel tercantum sebagai berikut:

1. Fungsi SUM dan COUNTIF
2. Fungsi SUMPRODUCT dan COUNTIF

Mari kita bahas kedua metode tersebut dengan bantuan contoh.

Contoh #1–Hitung Nilai Excel Unik dengan Menggunakan Fungsi SUM dan COUNTIF

Gambar berikut menunjukkan nama pelanggan (kolom A) dan tanggal (kolom B) saat penjualan dilakukan kepada mereka. Pendapatan yang dihasilkan (dalam $) dari setiap pelanggan diberikan di kolom C.

Seluruh dataset milik organisasi. Ini terkait dengan periode April 2018. Hitung nilai unik excel kolom A dengan bantuan fungsi SUM dan COUNTIF Excel.

Langkah-langkah menghitung nilai unik excel dengan menggunakan Fungsi SUMSUM Di ExcelFungsi SUM di excel menambahkan nilai numerik dalam rentang sel. Dikategorikan di bawah fungsi Matematika dan Trigonometri, dimasukkan dengan mengetikkan ‘=SUM’ diikuti dengan nilai yang akan dijumlahkan. Nilai yang diberikan ke fungsi dapat berupa angka, referensi sel, atau rentang.Baca selengkapnya dan Fungsi COUNTIFCOUNTIFFungsi COUNTIF di Excel menghitung jumlah sel dalam rentang berdasarkan kriteria yang telah ditentukan sebelumnya. Ini digunakan untuk menghitung sel yang menyertakan tanggal, angka, atau teks. Misalnya, COUNTIF(A1:A10,’Trump’) akan menghitung jumlah sel dalam rentang A1:A10 yang berisi teks ‘Trump’ baca lebih banyak fungsi Excel tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel E3.

Langkah 2: Tekan tombol ‘Ctrl+Shift+Enter’ bersamaan. Hal ini karena rumus yang diberikan adalah rumus larik FormulaArray Di ExcelRumus Array adalah rumus yang sangat membantu dan kuat yang digunakan di Excel untuk menjalankan beberapa perhitungan yang paling rumit. Ada dua jenis rumus array: satu yang mengembalikan hasil tunggal dan yang lainnya mengembalikan beberapa hasil.baca lebih lanjut. Saat menekan CSE (Ctrl+Shift+EnterCtrl + Shift + EnterCtrl-Shift Enter Di Excel adalah perintah pintasan yang memfasilitasi penerapan rumus larik dalam fungsi excel untuk menjalankan perhitungan rumit dari data yang diberikan. Secara keseluruhan, ini mengubah data tertentu menjadi format array di excel dengan beberapa nilai data untuk tujuan ini.Baca selengkapnya), kurung kurawal muncul di awal dan akhir rumus, seperti yang ditunjukkan pada gambar berikut.

Catatan: Formula array selalu dilengkapi dengan menekan tombol CSE. Bahkan setelah mengedit rumus array, tombol CSE harus ditekan untuk menyimpan perubahan yang dibuat. Rumus array tidak dapat diterapkan ke sel yang digabungkan.

Langkah 3: Setelah tombol CSE ditekan, hasilnya muncul di sel E3. Ini ditunjukkan pada gambar berikut. Oleh karena itu, ada 12 nilai berbeda di kolom A. Dengan kata lain, organisasi menjual ke 12 pelanggan berbeda pada April 2018.

Penjelasan rumus: Rumus yang dimasukkan pada langkah 1 memiliki tiga bagian, yaitu fungsi COUNTIF, ‘1/,’ dan fungsi SUM. Ini ditunjukkan pada gambar berikut.

Abaikan tanda panah di bagian 2 dan 1, yang sedikit salah tempat pada gambar berikut.

Dalam rumus ini, fungsi COUNTIF diproses terlebih dahulu karena merupakan fungsi terdalam. Setelah itu, ‘1/’ dan fungsi SUM diproses. Seluruh rumus berfungsi sebagai berikut:

sebuah. Fungsi COUNTIF diberikan satu rentang (A2:A25) dua kali. Ini memberi tahu fungsi untuk menghitung berapa kali nilai muncul dalam rentang ini. Karena ada 24 nilai dalam rentang ini, fungsi COUNTIF mengembalikan larik berisi 24 nilai. Ini ditunjukkan pada gambar berikut.

Jadi, angka 3 pertama menyiratkan bahwa nama ‘Ruturaj’ muncul tiga kali dalam rentang A2:A25. Demikian pula, 1 berikut menyiratkan bahwa nama ‘Kamal’ muncul sekali dalam rentang ini.

1. Selanjutnya, angka 1 dibagi dengan semua nilai yang dikembalikan dalam larik sebelumnya. Outputnya lagi-lagi merupakan array dari 24 nilai. Ini ditunjukkan pada gambar berikut.

Karena ‘Ruturaj’ muncul tiga kali (dalam A2:A25), nilai 3 dibagi 1 menghasilkan 0,33. Demikian juga, ‘Kamal’ muncul sekali, jadi 1 dibagi 1 sama dengan 1. Oleh karena itu, semua nilai yang muncul sekali dalam rentang yang dinyatakan (A2:A25) menghasilkan 1. Nilai yang mengembalikan angka desimal memiliki lebih dari satu kejadian.

1. Kemudian, fungsi SUM menjumlahkan nilai yang dikembalikan dalam larik sebelumnya. Perhatikan bahwa jika nilai muncul tiga kali dalam rentang yang disebutkan, 0,33 (1/3=0,33) muncul tiga kali. Jadi, 0,33+0,33+0,33 sama dengan 1. Demikian pula, jika nilai muncul dua kali dalam rentang A2:A25, 0,5 (1/2=0,5) muncul dua kali. Jadi, 0,5+0,5 sama dengan 1. Dengan cara ini, jumlah semua kemunculan nilai selalu sama dengan 1. Oleh karena itu, fungsi SUM mengembalikan total semua nilai yang berbeda dalam rentang A2:A25.

Oleh karena itu, jumlah nilai unik excel (dalam rentang A2:A25) adalah 12. Angka 12 ini adalah jumlah dari dua nilai unik (Kamal dan Raju) dan kejadian pertama dari sepuluh nilai duplikat (Ruturaj, Rohit Gulia, Abhishek Tanwar, Srinidhi, Karuna Jain, Andrew Flint, Cummins, Rahul, Ramesh, dan Christoper).

Catatan: Untuk melihat larik nilai yang dikembalikan oleh fungsi COUNTIF di penunjuk ‘a’, ikuti langkah-langkah berikut:

1. Pilih sel E3 yang berisi rumus.
2. Klik dua kali di dalam sel yang dipilih atau tekan tombol F2. Ini membantu masuk ke mode Edit.
3. Pilih bagian COUNTIF dari rumus, yaitu ‘COUNTIF(A2:A25,A2:A25).’
4. Tekan tombol F9.

Demikian pula, untuk melihat larik penunjuk ‘b’, pilih sel E3 dan klik dua kali di dalam sel yang dipilih. Selanjutnya, pilih bagian ‘1/COUNTIF(A2:A25,A2:A25)’ dan tekan F9.

Untuk keluar dari mode Edit, tekan tombol escape (Esc).

Contoh #2–Hitung Nilai Excel Unik dengan Menggunakan Fungsi SUMPRODUCT dan COUNTIF

Gambar berikut menunjukkan dataset dari contoh #1. Hitung nilai yang berbeda dari kolom A dengan bantuan SUMPRODUCT dan fungsi COUNTIF Excel.

Langkah-langkah untuk menghitung nilai yang berbeda dengan menggunakan Fungsi SUMPRODUCTSUMPRODUCTFungsi excel SUMPRODUCT mengalikan angka dari dua atau lebih array dan menjumlahkan produk yang dihasilkan.Baca lebih lanjut dan fungsi COUNTIF tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel E6.

Langkah 2: Tekan tombol ‘Enter’. Outputnya muncul di sel E6, seperti yang ditunjukkan pada gambar berikut. Jadi, ada 12 nilai berbeda dalam rentang A2:A25.

Perhatikan bahwa output dari SUM dan COUNTIF (contoh #1) sama dengan keluaran SUMPRODUCT dan COUNTIF (contoh #2). Oleh karena itu, karena outputnya sama, seseorang dapat memilih salah satu dari dua formula tersebut berdasarkan kenyamanan.

Penjelasan rumus: Rumus yang diberikan pada langkah 1 (dari contoh ini) bekerja dengan cara yang persis sama dengan rumus pada contoh #1. Satu-satunya perbedaan antara formula contoh #1 dan #2 adalah penggunaan kunci CSE pada contoh pertama dan penggunaan kunci ‘Enter’ pada contoh terakhir.

Dalam rumus saat ini, fungsi COUNTIF dan pembagian dengan 1 menghasilkan larik yang sama dengan larik penunjuk ‘a’ dan ‘b’ pada contoh #1. Menjadi satu larik, SUMPRODUCT menjumlahkan nilai larik ini. Outputnya adalah 12. Jadi, 12 ini terdiri dari dua nilai unik dan kemunculan pertama dari sepuluh nilai duplikat.

Catatan 1: Untuk detail kerja rumus saat ini, lihat ‘penjelasan rumus’ yang diberikan di akhir contoh #1.

Catatan 2: Untuk melihat nilai larik lengkap yang dikembalikan oleh bagian COUNTIF dan ‘1/’, seseorang dapat memilih bagian ini dan menekan tombol F9. Untuk detail lebih lanjut, lihat catatan di akhir contoh #1.

Contoh #3–Hitung Nilai Excel Unik dengan Mengecualikan Sel Kosong dari Rentang

Bekerja pada dataset contoh #1, kami telah menyisipkan baris 10 sebagai baris kosong. Hasilnya, rumus dari contoh #1 dan #2 menunjukkan ‘#DIV/0!’ errorErrors Di ExcelError di excel biasa terjadi dan sering terjadi pada saat menerapkan rumus. Daftar sembilan kesalahan excel yang paling umum adalah – #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Referensi Edaran.baca lebih banyak di sel E3 dan E6. Kesalahan ini ditampilkan saat angka dibagi dengan nol (atau sel kosong) di Excel.

Hitung jumlah nilai yang berbeda dari kolom A dengan mengecualikan sel kosong A10. Gunakan fungsi Excel berikut ini:

• Fungsi SUM dan COUNTIF
• Fungsi SUMPRODUCT dan COUNTIF

Langkah-langkah untuk menghitung nilai yang berbeda dengan mengecualikan sel kosong tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut (tanpa awal dan akhir tanda kutip ganda) masing-masing di sel E3 dan E6.

‘=SUM(1/COUNTIF(A2:A26,A2:A26&”))-1’

‘=SUMPRODUCT(1/COUNTIF(A2:A26,A2:A26&”))-1’

Langkah 2: Tekan tombol CSE (Ctrl+Shift+Enter) setelah memasukkan rumus SUM dan COUNTIF. Tekan tombol ‘Enter’ setelah memasukkan rumus SUMPRODUCT dan COUNTIF.

Keluaran dari kedua rumus ditunjukkan pada gambar berikut. Perhatikan bahwa dalam gambar ini, rumus SUM dan COUNTIF ditampilkan (dalam kurung kurawal) di bilah rumus.

Oleh karena itu, keluaran dari kedua rumus adalah 12. Ini menyiratkan bahwa ada 12 nilai berbeda di kolom A. Sel kosong A10 dikecualikan dari hitungan ini.

Penjelasan rumus: Dua rumus sebelumnya berfungsi sebagai berikut:

sebuah. Fungsi COUNTIF diinstruksikan untuk menghitung sel yang tidak kosong dalam rentang A2:A26. Karena operator ampersand (&) bersama dengan string kosong (”) dipasok ke fungsi COUNTIF, sel kosong A10 diperlakukan sebagai nilai unik. Jadi, COUNTIF mengembalikan array nilai berikut:

{3;1;2;2;3;2;2;2;1;3;2;2;2;3;2;2;2;3;2;2;1;3;2;2;2 }

1. Selanjutnya, nilai dari larik sebelumnya dibagi dengan 1. Pembagian ini mengembalikan larik nilai berikut:

{0,3333333333333333;1;0,5;0,5;0,3333333333333333;0,5;0,5;0,5;1;0,3333333333333333; 0,5;0,5;0,5;0,333333333333333;0,5;0,5;0,5;0,333333333333333;0,5;0,5;1;0,3333333333333333;0,5;0,5;0,5}

12. Terakhir, array tunggal diringkas oleh fungsi SUM atau SUMPRODUCT. Ini mengembalikan 13 sebagai jumlah. Dari jumlah ini, 1 dikurangi untuk mengecualikan sel kosong dari hitungan. Jadi, hasil akhirnya adalah 12.

Oleh karena itu, Excel mengembalikan hasil yang sama terlepas dari rumus yang digunakan. Perhatikan bahwa dalam contoh ini, COUNTIF mengembalikan 1 untuk satu sel kosong A10.

Demikian pula, seandainya ada dua sel kosong dalam rentang A2:A26, COUNTIF akan mengembalikan 2 pada dua tempat dalam larik. Untuk tiga sel kosong, COUNTIF akan mengembalikan 3 di tiga tempat dalam larik. Jadi, jumlah semua kemunculan sel kosong selalu sama dengan 1.

Oleh karena itu, dua rumus yang diberikan akan mengembalikan keluaran yang benar bahkan jika ada lebih dari satu sel kosong dalam rentang yang disediakan.

Catatan: Untuk melihat larik nilai dalam penunjuk ‘a’ atau ‘b,’ pilih bagian ‘COUNTIF(A2:A26,A2:A26&”)’ atau ‘1/COUNTIF(A2:A26,A2:A26&” )’ dari rumus. Selanjutnya, tekan tombol F9.

Pertanyaan yang Sering Diajukan

1. Tentukan nilai unik dan nyatakan rumus untuk menghitungnya di Excel.

Nilai unik adalah nilai yang muncul dalam daftar item hanya sekali. Rumus untuk menghitung nilai unik di Excel dinyatakan sebagai berikut:

‘=SUM(IF(COUNTIF(range,range)=1, 1,0))’

Rumus ini bekerja sebagai berikut:

a. Fungsi COUNTIF disertakan dengan rentang tunggal dua kali. Ini menghitung jumlah nilai yang muncul hanya sekali dalam rentang yang dinyatakan. Ini mengembalikan array nilai.
b. Fungsi IF menganggap argumen ‘COUNTIF(range,range)=1’ sebagai tes logika. Jika COUNTIF mengembalikan 1, fungsi IF juga mengembalikan 1 (nilai_jika_benar). Namun, jika COUNTIF mengembalikan nilai selain 1, fungsi IF mengembalikan 0 (nilai_jika_false). Semua yang dikembalikan oleh fungsi IF adalah nilai unik dan semua nol adalah nilai duplikat.
c. Fungsi SUM menjumlahkan larik tunggal satu dan nol yang dikembalikan oleh fungsi IF.

Hasil akhirnya adalah hitungan nilai unik dari daftar Excel.

Catatan 1: Rumus yang diberikan adalah rumus array. Jadi, pastikan tombol CSE (Ctrl+Shift+Enter) ditekan setelah memasukkan rumus di Excel. Kecualikan tanda kutip ganda awal dan akhir saat memasukkan rumus di Excel.

Catatan 2: Untuk mengekstrak nilai unik, duplikat harus dihapus dari daftar.

2. Tentukan nilai yang berbeda dan nyatakan rumus untuk menghitungnya di Excel.

Nilai yang berbeda adalah semua nilai yang berbeda dari daftar item. Jadi, nilai yang berbeda adalah nilai unik ditambah kejadian pertama dari nilai duplikat.

Untuk menghitung nilai yang berbeda di Excel, salah satu rumus berikut dapat digunakan (tanpa tanda kutip ganda awal dan akhir):• ‘=SUM(1/COUNTIF(rentang,rentang))’• ‘=SUMPRODUCT(1/COUNTIF (rentang,rentang))’

Satu-satunya perbedaan antara kedua rumus ini adalah rumus pertama dilengkapi dengan tombol CSE (Ctrl+Shift+Enter), sedangkan rumus kedua dilengkapi dengan tombol ‘Enter’.

Catatan: Untuk detail selengkapnya tentang cara kerja rumus sebelumnya, lihat contoh #1 dan #2 di artikel ini. Untuk menghitung nilai yang berbeda dengan mengecualikan sel kosong, lihat contoh #3 artikel ini.

3. Bagaimana cara menghitung nilai unik dan berbeda dengan menggunakan fungsi COUNTIFS Excel?

Rumus untuk menghitung baris unik rentang A2:A12 dan B2:B12 dinyatakan sebagai berikut:

‘=SUM(IF(COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)=1,1, 0))’

Rumus untuk menghitung baris berbeda dari rentang A2:A12 dan B2:B12 dinyatakan sebagai berikut:

‘=SUM(1/COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)) ‘

Kedua rumus COUNTIFS bekerja mirip dengan rekan COUNTIF mereka. Manfaat menggunakan fungsi COUNTIFS adalah memungkinkan pemeriksaan beberapa kolom untuk nilai unik atau berbeda.

Catatan: Sebuah baris akan dihitung dengan dua rumus hanya jika sel di kedua kolom A dan B unik (atau berbeda). Selanjutnya, kedua rumus yang disebutkan adalah rumus larik. Jadi, tekan tombol CSE (Ctrl+Shift+Enter) untuk menyelesaikan formula ini.

Artikel yang Direkomendasikan

Ini telah menjadi panduan untuk menghitung nilai unik dan berbeda di Excel. Di sini, kami menjelaskan 2 metode mudah teratas beserta contoh langkah demi langkah. Anda juga dapat melihat fungsi Excel yang berguna ini–

• Menghitung SUMPRODUCT dengan Banyak KriteriaMenghitung SUMPRODUCT Dengan Banyak KriteriaDi Excel, menggunakan SUMPRODUCT dengan beberapa Kriteria memungkinkan Anda untuk membandingkan array yang berbeda menggunakan beberapa kriteria.baca lebih lanjut
• COUNTIF dengan Banyak Kriteria
• Fungsi ISNA di ExcelFungsi ISNA Di ExcelFungsi ISNA adalah fungsi penanganan kesalahan di Excel. Ini membantu untuk mengetahui apakah ada sel yang memiliki ‘#N/A error’ atau tidak. Fungsi ini mengembalikan nilai ‘true’ jika ‘#N/A error’ teridentifikasi atau “false” jika tidak teridentifikasi.baca lebih lanjut
• Contoh OFFSET Excel Contoh OFFSET ExcelFungsi OFFSET di excel mengembalikan nilai sel atau rentang (sel yang berdekatan) yang merupakan jumlah baris dan kolom tertentu dari titik referensi. Baca selengkapnya