Cara Mengira Nilai Unik di Excel

Cara Mengira Nilai Unik di Excel

Set data dalam Excel sering kali mengandungi nilai yang sama berkali-kali dalam lajur. Kadang-kadang, berguna untuk mengetahui berapa banyak nilai unik dalam lajur. Sebagai contoh, jika anda menjalankan kedai dan mempunyai spreadsheet semua transaksi anda, anda mungkin ingin menentukan berapa banyak pelanggan unik yang anda miliki, dan bukannya menghitung setiap transaksi.





Anda boleh melakukannya dengan mengira nilai unik di Excel menggunakan kaedah yang akan kami sampaikan di bawah.



Buang Pendua Data Dari Lajur

Cara cepat dan kotor untuk mengira nilai unik di Excel adalah membuang pendua dan melihat berapa banyak entri yang tersisa. Ini adalah pilihan yang baik jika anda memerlukan jawapan dengan cepat dan tidak perlu mengesan hasilnya.





Salin data ke dalam lembaran baru (agar anda tidak menghapus data yang anda perlukan secara tidak sengaja). Pilih nilai atau lajur yang anda mahu keluarkan pendua nilai. Di dalam Alat Data bahagian dari Data pilih tab Buang Pendua . Ini akan membuang semua data pendua dan hanya meninggalkan nilai unik.

Proses yang sama berfungsi sekiranya maklumat dibahagi antara dua lajur. Perbezaannya ialah anda perlu memilih kedua lajur. Dalam contoh kami, kami mempunyai lajur untuk nama pertama dan kedua untuk nama terakhir.



Sekiranya anda ingin mengesan bilangan nilai unik, lebih baik anda menulis formula. Kami akan menunjukkan cara melakukannya di bawah.

Berkaitan: Cara Menapis di Excel untuk Memaparkan Data yang Anda Mahukan



Hitung Nilai Unik dengan Formula Excel

Untuk mengira hanya nilai unik, kita mesti menggabungkan beberapa fungsi Excel. Pertama, kita perlu memeriksa sama ada setiap nilai adalah pendua, maka kita perlu mengira baki entri. Kita juga perlu menggunakan fungsi array.

Sekiranya anda hanya mencari jawapannya, gunakan formula ini, ganti setiap contoh A2: A13 dengan sel yang ingin anda gunakan:



{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

Bagaimana kita sampai di sana agak rumit. Oleh itu, jika anda ingin memahami mengapa formula itu berfungsi, kami akan memecahkannya satu demi satu di bawah.

Menjelaskan Fungsi Array

Mari mulakan dengan menerangkan apa itu array. Array adalah pemboleh ubah tunggal yang menyimpan pelbagai nilai. Ia seperti merujuk kepada sekumpulan sel Excel sekaligus daripada merujuk kepada setiap sel secara individu.

Ini adalah perbezaan yang pelik dari sudut pandangan kami. Sekiranya kita memberitahu formula untuk melihat sel A2: A13 secara normal atau sebagai tatasusunan, data kelihatan sama untuk kita. Perbezaannya adalah bagaimana Excel memperlakukan data di belakang tabir. Ini adalah perbezaan yang sangat baik sehingga versi terbaru Excel bahkan tidak membezakannya lagi, walaupun versi lama.

Untuk tujuan kita, lebih penting untuk mengetahui bagaimana kita boleh menggunakan tatasusunan. Sekiranya anda mempunyai versi terbaru Excel, ia secara automatik menyimpan data sebagai tatasusunan apabila lebih cekap melakukannya. Sekiranya anda mempunyai versi yang lebih lama, apabila anda selesai menulis formula anda, tekan Ctrl + Shift + Enter . Setelah anda melakukannya, formula akan dikelilingi oleh tanda kurung keriting untuk menunjukkannya dalam mod array.

Memperkenalkan FREKUENSI Fungsi

Fungsi FREQUENCY memberitahu kita berapa kali nombor muncul dalam senarai. Ini bagus jika anda bekerja dengan nombor, tetapi senarai kami adalah teks. Untuk menggunakan fungsi ini, kita harus mencari cara untuk menukar teks kita menjadi nombor terlebih dahulu.

Sekiranya anda cuba mengira nilai unik dalam senarai nombor, anda boleh melangkau langkah seterusnya.

Menggunakan Fungsi MATCH

Fungsi MATCH mengembalikan kedudukan kejadian pertama nilai. Kita boleh menggunakannya untuk menukar senarai nama kita menjadi nilai nombor. Ia perlu mengetahui tiga maklumat:

  • Nilai apa yang anda cari?
  • Set data apa yang anda periksa?
  • Adakah anda mencari nilai yang lebih tinggi, lebih rendah, atau sama dengan nilai sasaran?

Dalam contoh kami, kami ingin mencari setiap nama pelanggan kami di spreadsheet Exel kami untuk melihat apakah nama mereka yang betul muncul lagi di tempat lain.

cara streaming saluran tempatan secara percuma

Dalam contoh di atas, kami mencari senarai kami (A2: A13) untuk Tiah Gallagher (A2) dan kami mahukan padanan yang tepat. 0 di medan terakhir menentukan bahawa ia mesti sepadan dengan tepat. Hasil kami memberitahu kami di mana dalam senarai nama itu muncul pertama kali. Dalam kes ini, itu adalah nama pertama, jadi hasilnya adalah 1.

Masalahnya ialah kita berminat dengan semua pelanggan, bukan hanya Tiah. Tetapi, jika kita cuba mencari A2: A13 dan bukan hanya A2, kita akan mendapat ralat. Di sinilah fungsi array berguna. Parameter pertama hanya boleh mengambil satu pemboleh ubah atau sebaliknya mengembalikan ralat. Tetapi, tatasusila diperlakukan seperti pembolehubah tunggal.

Sekarang fungsi kami memberitahu Excel untuk memeriksa perlawanan untuk keseluruhan susunan kami. Tetapi tunggu, hasil kami tidak berubah! Masih tertulis 1. Apa yang berlaku di sini?

Fungsi kami mengembalikan array. Ia melalui setiap item dalam array kami dan memeriksa perlawanan. Hasil semua nama disimpan dalam array, yang dikembalikan sebagai hasilnya. Kerana sel hanya menunjukkan satu pemboleh ubah pada satu masa, ia menunjukkan nilai pertama dalam array.

Anda boleh menyemaknya sendiri. Sekiranya anda menukar julat pertama ke A3: A13, hasilnya akan berubah menjadi 2. Ini kerana nama Eiliyah berada di tempat kedua dalam senarai dan nilai ini disimpan pertama dalam array sekarang. Sekiranya anda menukar julat pertama menjadi A7: A13, anda mendapat 1 lagi kerana nama Tiah pertama kali muncul di kedudukan pertama set data yang sedang kami periksa.

Berkaitan: Rumusan Excel Yang Akan Membantu Anda Menyelesaikan Masalah Kehidupan Sebenar

Menggunakan FREKUENSI Fungsi

Sekarang setelah kita menukar nama menjadi nilai nombor, kita dapat menggunakan fungsi FREQUENCY. Sama seperti MATCH, ia memerlukan sasaran yang perlu dicari dan satu set data untuk diperiksa. Sama seperti MATCH, kami tidak mahu mencari hanya satu nilai, kami ingin fungsi memeriksa setiap item dalam senarai kami.

Sasaran yang kami mahukan fungsi FREQUENCY adalah setiap item dalam array yang fungsi MATCH kami dikembalikan. Dan kami ingin memeriksa set data yang dikembalikan oleh fungsi MATCH. Oleh itu, kami menghantar fungsi MATCH yang kami buat di atas untuk kedua-dua parameter.

Sekiranya anda mencari nombor unik dan melewatkan langkah sebelumnya, anda akan menghantar julat nombor sebagai kedua parameter. Untuk mencari semua nombor dalam senarai anda, anda juga perlu menggunakan fungsi array, jadi ingatlah untuk menekan Ctrl + Shift + Enter setelah anda memasukkan formula jika anda menggunakan versi Excel yang lebih lama.

Sekarang hasil kami adalah 2. Sekali lagi, fungsi kami mengembalikan array. Ia mengembalikan susunan frekuensi setiap nilai unik muncul. Sel menunjukkan nilai pertama dalam tatasusunan. Dalam kes ini, nama Tiah muncul dua kali, jadi frekuensi yang dikembalikan adalah 2.

Menggunakan Fungsi IF

Sekarang susunan kami mempunyai bilangan nilai yang sama dengan kami mempunyai nilai unik. Tetapi kita belum selesai. Kami memerlukan kaedah untuk menambahkannya. Sekiranya kita menukar semua nilai dalam larik menjadi 1, dan menjumlahkannya, kita akhirnya akan mengetahui berapa banyak nilai unik yang kita ada.

Kita boleh membuat fungsi IF yang mengubah semua nilai di atas sifar menjadi 1. Kemudian semua nilai akan sama dengan 1.

Untuk melakukan ini, kami mahu fungsi IF kami memeriksa apakah nilai dalam array FREQUENCY kami lebih besar daripada sifar. Sekiranya benar, nilai tersebut akan mengembalikan nilai 1. Anda akan melihat bahawa sekarang nilai pertama dalam array kembali sebagai satu.

Menggunakan Fungsi SUM

Kami berada di tahap akhir! Langkah terakhir adalah MENYUMBANG array.

Balut fungsi sebelumnya dalam fungsi SUM. Selesai! Jadi formula terakhir kami adalah:

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}

Mengira Entri Unik dalam Excel

Ini adalah fungsi lanjutan yang memerlukan banyak pengetahuan mengenai Excel. Ini boleh menakutkan untuk mencuba. Tetapi, setelah disiapkan, sangat berguna, jadi mungkin perlu dijelaskan melalui penjelasan kami untuk memastikan anda memahaminya.

Sekiranya anda tidak perlu mengira entri unik yang sering kali, tip cepat dan kotor untuk menghapus nilai pendua akan berfungsi dengan cepat!

Berkongsi Berkongsi Tweet E-mel Cara Menyalin Rumus dalam Microsoft Excel

Mempelajari semua kaedah terbaik untuk menyalin dan menampal formula dalam hamparan Excel anda adalah kaedah terbaik untuk mula menjimatkan masa.

Baca Seterusnya Topik-topik yang berkaitan
  • Produktiviti
  • Hamparan
  • Microsoft Excel
  • Analisis data
Mengenai Pengarang Jennifer Seaton(21 Artikel Diterbitkan)

J. Seaton adalah Penulis Sains yang pakar dalam menguraikan topik yang kompleks. Dia mempunyai PhD dari University of Saskatchewan; penyelidikannya tertumpu pada penggunaan pembelajaran berasaskan permainan untuk meningkatkan penglibatan pelajar dalam talian. Apabila dia tidak bekerja, anda akan mendapati dia membaca, bermain permainan video, atau berkebun.

Lagi Dari Jennifer Seaton

Langgan buletin kami

Sertailah buletin kami untuk mendapatkan petua, ulasan, ebook percuma, dan tawaran eksklusif!

Klik di sini untuk melanggan