Excel Fungsi unik - cara terpantas untuk mencari nilai yang unik

Tutorial melihat bagaimana untuk mendapatkan nilai unik dalam Excel dengan menggunakan fungsi unik dan array dinamik. Anda akan mempelajari formula mudah untuk mencari nilai unik dalam lajur atau baris, dalam pelbagai lajur, berdasarkan keadaan, dan banyak lagi.

Dalam versi Excel sebelumnya, mengekstrak senarai nilai unik adalah cabaran yang sukar. Kami mempunyai artikel khas yang menunjukkan cara mencari unik yang berlaku sekali sahaja, mengeluarkan semua item yang berbeza dalam senarai, mengabaikan kosong, dan banyak lagi. Setiap tugas memerlukan gabungan penggunaan beberapa fungsi dan formula pelbagai baris yang hanya Excel Gurus dapat memahami sepenuhnya.

Pengenalan fungsi unik dalam Excel 365 telah mengubah segala -galanya! Apa yang pernah menjadi sains roket menjadi semudah ABC. Sekarang, anda tidak perlu menjadi pakar formula untuk mendapatkan nilai unik dari julat, berdasarkan satu atau beberapa kriteria, dan mengatur hasil dalam susunan abjad. Semua dilakukan dengan formula mudah yang semua orang boleh membaca dan menyesuaikan diri untuk keperluan anda sendiri.

• Fungsi unik Excel
  • Formula unik asas dalam Excel
  • Fungsi Unik - Petua dan Nota
• Cara Mencari Nilai Unik - Contoh Formula
  • Ekstrak nilai yang berlaku sekali sahaja
  • Cari nilai yang berbeza yang berlaku lebih dari sekali
  • Cari nilai unik dalam pelbagai lajur (baris unik)
  • Excel nilai unik disusun mengikut abjad
  • Menggabungkan nilai unik dari pelbagai lajur ke dalam satu sel
  • Dapatkan senarai nilai unik berdasarkan kriteria
  • Nilai unik berdasarkan pelbagai kriteria
  • Nilai unik dengan pelbagai atau kriteria
  • Cari nilai unik dalam Excel mengabaikan sel kosong
  • Cari nilai yang unik di lajur yang tidak bersebelahan
  • Cari nilai yang unik dan mengendalikan kesilapan
• Excel Fungsi unik tidak berfungsi

Fungsi unik Excel

Fungsi unik dalam Excel mengembalikan senarai nilai unik dari julat atau array. Ia berfungsi dengan mana -mana jenis data: teks, nombor, tarikh, masa, dll.

Fungsi ini dikategorikan di bawah fungsi array dinamik. Hasilnya adalah array dinamik yang secara automatik menumpahkan ke dalam sel -sel jiran secara menegak atau mendatar.

Sintaks fungsi unik Excel adalah seperti berikut:

Unik (array, [by_col], [tedy_once])

Di mana:

Array (diperlukan) - julat atau array dari mana untuk mengembalikan nilai unik.

BY_COL (Pilihan) - Nilai logik yang menunjukkan cara membandingkan data:

• Benar - membandingkan data merentasi lajur.
• Palsu atau ditinggalkan (lalai) - membandingkan data merentasi baris.

Tepat_once (pilihan) - Nilai logik yang mentakrifkan nilai yang dianggap unik:

• Benar - mengembalikan nilai yang berlaku hanya sekali, iaitu tanggapan pangkalan data yang unik.
• Palsu atau ditinggalkan (lalai) - Mengembalikan semua nilai yang berbeza (berbeza) dalam julat atau array.

Nota. Pada masa ini fungsi unik hanya tersedia dalam Excel untuk Microsoft 365 dan Excel 2021. Excel 2019, 2016 dan lebih awal tidak menyokong formula array dinamik, jadi fungsi unik tidak tersedia dalam versi ini.

Formula unik asas dalam Excel

Berikut adalah formula nilai unik Excel dalam bentuk yang paling mudah.

Matlamatnya adalah untuk mengekstrak senarai nama unik dari julat B2: B10. Untuk ini, kami memasuki formula berikut dalam D2:

=UNIQUE(B2:B10)

Sila ambil perhatian bahawa hujah -hujah ke -2 dan ke -3 ditinggalkan kerana lalai berfungsi dengan sempurna dalam kes kami - kami membandingkan baris antara satu sama lain dan ingin mengembalikan semua nama yang berbeza dalam julat.

Apabila anda menekan kekunci Enter untuk melengkapkan formula, Excel akan mengeluarkan nama pertama yang ditemui dalam D2 yang menumpahkan nama lain ke dalam sel di bawah. Hasilnya, anda mempunyai semua nilai unik dalam lajur:

Sekiranya data anda merentasi lajur dari B2 hingga I2, tetapkan hujah ^ke -2 untuk benar untuk membandingkan lajur antara satu sama lain:

=UNIQUE(B2:I2,TRUE)

Taipkan formula di atas dalam B4, tekan ENTER , dan hasilnya akan tumpah secara mendatar ke dalam sel ke kanan. Oleh itu, anda akan mendapat nilai unik berturut -turut:

Petua. Untuk mencari nilai yang unik dalam susunan pelbagai lajur dan kembalikannya dalam satu lajur atau baris, gunakan unik bersama-sama dengan fungsi TOCOL atau TOROW seperti yang ditunjukkan dalam contoh di bawah:

• Ekstrak nilai unik dari julat pelbagai lajur ke dalam lajur
• Tarik nilai unik dari pelbagai lajur ke dalam satu baris

Excel Fungsi Unik - Petua dan Nota

Unik adalah fungsi baru dan seperti fungsi array dinamik lain mempunyai beberapa kekhususan yang harus anda ketahui:

• Jika array yang dikembalikan oleh unik adalah hasil akhir (iaitu tidak diserahkan kepada fungsi lain), Excel secara dinamik mencipta pelbagai bersaiz yang sesuai dan meminumnya dengan hasilnya. Formula perlu dimasukkan hanya dalam satu sel . Adalah penting bahawa anda mempunyai sel kosong yang cukup ke bawah dan/atau di sebelah kanan sel di mana anda memasuki formula, jika tidak, ralat #Spill berlaku.
• Kemas kini keputusan secara automatik apabila data sumber berubah. Walau bagaimanapun, penyertaan baru yang ditambah di luar array yang dirujuk tidak termasuk dalam formula melainkan anda menukar rujukan array . Jika anda mahu array untuk bertindak balas terhadap saiz semula julat sumber secara automatik, kemudian tukar julat ke jadual Excel dan gunakan rujukan berstruktur, atau buat julat bernama dinamik.
• Arahan dinamik antara fail Excel yang berbeza hanya berfungsi apabila kedua -dua buku kerja dibuka . Jika buku kerja sumber ditutup, formula unik yang dikaitkan akan mengembalikan #REF! ralat.
• Seperti fungsi array dinamik yang lain, unik hanya boleh digunakan dalam julat normal, bukan jadual. Apabila dimasukkan ke dalam jadual Excel, ia mengembalikan #spill! ralat.

Cara Mencari Nilai Unik dalam Excel - Contoh Formula

Contoh di bawah menunjukkan beberapa kegunaan praktikal fungsi unik dalam Excel. Idea utama adalah untuk mengekstrak nilai yang unik atau mengeluarkan pendua, bergantung pada pandangan anda, dengan cara yang paling mudah.

Ekstrak nilai unik yang berlaku sekali sahaja

Untuk mendapatkan senarai nilai yang muncul dalam julat yang ditentukan tepat sekali, tetapkan hujah 3 ^RD yang unik kepada yang benar.

Sebagai contoh, untuk menarik nama -nama yang ada dalam senarai pemenang satu kali, gunakan formula ini:

=UNIQUE(B2:B10,,TRUE)

Di mana B2: B10 adalah julat sumber dan hujah 2 ^ND ( BY_COL ) adalah palsu atau ditinggalkan kerana data kami dianjurkan dalam baris.

Cari nilai yang berbeza yang berlaku lebih dari sekali

Jika anda mengejar matlamat yang bertentangan, iaitu ingin mendapatkan senarai nilai yang muncul dalam julat yang diberikan lebih dari satu masa, kemudian gunakan fungsi unik bersama -sama dengan penapis dan countif:

Unik (penapis ( julat , countif ( julat , julat )> 1))

Sebagai contoh, untuk mengekstrak nama yang berbeza yang berlaku dalam B2: B10 lebih daripada sekali, anda boleh menggunakan formula ini:

=UNIQUE(FILTER(B2:B10, COUNTIF(B2:B10, B2:B10)>1))

Bagaimana formula ini berfungsi:

Di tengah -tengah formula, fungsi penapis menapis penyertaan pendua berdasarkan kiraan kejadian, dikembalikan oleh fungsi Countif. Dalam kes kita, hasil daripada Countif adalah pelbagai tuduhan ini:

{4;1;3;4;4;1;3;4;3}

Operasi perbandingan (> 1) mengubah array di atas kepada nilai yang benar dan palsu, di mana benar mewakili item yang muncul lebih dari sekali:

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}

Arahan ini diserahkan untuk menapis sebagai argumen termasuk , memberitahu fungsi yang nilai -nilai yang dimasukkan dalam array yang dihasilkan:

{"Andrew";"David";"Andrew";"Andrew";"David";"Andrew";"David"}

Seperti yang anda perhatikan, hanya nilai yang sepadan dengan True Survive.

Arahan di atas pergi ke argumen array yang unik, dan selepas mengeluarkan pendua ia mengeluarkan hasil akhir:

{"Andrew";"David"}

Petua. Dalam cara yang sama, anda boleh menapis nilai unik yang berlaku lebih daripada dua kali (> 2), lebih daripada tiga kali (> 3), dan lain -lain. Untuk ini, hanya ubah nombor dalam perbandingan logik.

Cari nilai unik dalam pelbagai lajur (baris unik)

Dalam keadaan apabila anda ingin membandingkan dua atau lebih lajur dan kembalikan nilai -nilai unik di antara mereka, sertakan semua lajur sasaran dalam argumen array .

Sebagai contoh, untuk mengembalikan nama pertama yang unik (lajur A) dan nama belakang (lajur b) pemenang, kami memasukkan formula ini dalam E2:

=UNIQUE(A2:B10)

Menekan kekunci Enter menghasilkan hasil berikut:

Untuk mendapatkan baris yang unik , iaitu penyertaan dengan kombinasi nilai unik dalam lajur A, B dan C, ini adalah formula untuk digunakan:

=UNIQUE(A2:C10)

Hebatnya mudah, bukan? "

Dapatkan senarai nilai unik yang disusun mengikut urutan abjad

Bagaimana anda biasanya abjad dalam Excel? Betul, dengan menggunakan ciri jenis atau penapis terbina. Masalahnya ialah anda perlu menyiarkan semula setiap kali sumber data anda berubah, kerana tidak seperti formula Excel yang mengira semula secara automatik dengan setiap perubahan dalam lembaran kerja, ciri-ciri perlu digunakan semula secara manual.

Dengan pengenalan fungsi array dinamik masalah ini hilang! Apa yang perlu anda lakukan hanyalah meledingkan fungsi jenis di sekitar formula unik yang biasa, seperti ini:

Sort (unik (array))

Sebagai contoh, untuk mengekstrak nilai unik dalam lajur A melalui C dan mengatur hasil dari A hingga Z, gunakan formula ini:

=SORT(UNIQUE(A2:C10))

Berbanding dengan contoh di atas, output adalah lebih mudah untuk dilihat dan bekerja dengan. Sebagai contoh, kita dapat melihat dengan jelas bahawa Andrew dan David telah menjadi pemenang dalam dua sukan yang berbeza.

Petua. Dalam contoh ini, kami menyusun nilai -nilai dalam lajur pertama dari A hingga Z. Ini adalah kegagalan fungsi jenis, oleh itu argumen sort_index dan sort_order pilihan ditinggalkan. Jika anda ingin menyusun hasilnya dengan beberapa lajur lain atau dalam urutan yang berbeza (dari z ke A atau dari tertinggi hingga terkecil) tetapkan argumen ke -2 dan ke -3 seperti yang dijelaskan dalam tutorial fungsi jenis.

Cari nilai unik dalam pelbagai lajur dan disatukan menjadi satu sel

Apabila mencari dalam pelbagai lajur, secara lalai, fungsi unik Excel mengeluarkan setiap nilai dalam sel yang berasingan. Mungkin, anda akan mendapati lebih mudah untuk menghasilkan hasil dalam satu sel?

Untuk mencapai matlamat ini, bukannya merujuk keseluruhan julat, gunakan ampersand (&) untuk menggabungkan lajur dan letakkan pembatas yang dikehendaki di antara.

Sebagai contoh, kita menggabungkan nama pertama dalam A2: A10 dan nama terakhir dalam B2: B10, memisahkan nilai dengan watak ruang (""):

=UNIQUE(A2:A10&" "&B2:B10)

Hasilnya, kami mempunyai senarai nama penuh dalam satu lajur:

Dapatkan senarai nilai unik berdasarkan kriteria

Untuk mengekstrak nilai yang unik dengan keadaan, gunakan fungsi unik dan penapis Excel bersama -sama:

• Fungsi penapis mengehadkan data hanya kepada nilai yang memenuhi syarat.
• Fungsi unik menghilangkan pendua dari senarai yang ditapis.

Inilah versi generik formula nilai unik yang ditapis:

Unik (penapis (array, kriteria_range = kriteria ))

Untuk contoh ini, mari kita dapatkan senarai pemenang dalam sukan tertentu. Sebagai permulaan, kami memasukkan sukan yang menarik dalam beberapa sel, katakan F1. Dan kemudian, gunakan formula di bawah untuk mendapatkan nama yang unik:

=UNIQUE(FILTER(A2:B10, C2:C10=F1))

Di mana A2: B10 adalah julat untuk mencari nilai unik dan C2: C10 adalah julat untuk memeriksa kriteria.

Penapis nilai unik berdasarkan pelbagai kriteria

Untuk menapis nilai unik dengan dua atau lebih syarat, gunakan ungkapan seperti yang ditunjukkan di bawah untuk membina kriteria yang diperlukan untuk fungsi penapis:

Unik (penapis (array, ( kriteria_range1 = kriteria1 ) * ( kriteria_range2 = kriteria2 )))

Hasil formula adalah senarai penyertaan unik yang mana semua syarat yang ditentukan adalah benar. Dari segi Excel, ini dipanggil dan logik.

Untuk melihat formula dalam tindakan, mari kita dapatkan senarai pemenang unik untuk sukan ini dalam G1 (Kriteria 1) dan di bawah umur di G2 (Kriteria 2).

Dengan julat sumber dalam A2: B10, sukan dalam C2: C10 (kriteria_range 1) dan umur dalam d2: d10 (kriteria_range 2), formula mengambil bentuk ini:

=UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<g2></g2>

Dan mengembalikan hasil yang kita cari:

Bagaimana formula ini berfungsi:

Inilah penjelasan peringkat tinggi logik formula:

Di dalam argumen fungsi penapis, anda membekalkan dua atau lebih pasangan/kriteria. Hasil setiap ungkapan logik adalah pelbagai nilai yang benar dan palsu. Pendaraban array memaksa nilai logik ke nombor dan menghasilkan pelbagai 1 dan 0. Oleh kerana mendarab dengan sifar selalu memberikan sifar, hanya penyertaan yang memenuhi semua syarat mempunyai 1 dalam array akhir. Fungsi penapis menapis item yang sepadan dengan 0 dan tangan dari hasilnya kepada unik.

Untuk maklumat lanjut, sila lihat penapis dengan pelbagai kriteria menggunakan dan logik.

Menapis nilai unik dengan pelbagai atau kriteria

Untuk mendapatkan senarai nilai unik berdasarkan pelbagai atau kriteria, iaitu apabila kriteria ini atau itu benar, tambahkan ungkapan logik dan bukannya mengalikannya:

Unik (penapis (array, ( kriteria_range1 = kriteria1 ) ( kriteria_range2 = kriteria2 )))

Sebagai contoh, untuk menunjukkan pemenang dalam bola sepak atau hoki , anda boleh menggunakan formula ini:

=UNIQUE(FILTER(A2:B10, (C2:C10="Soccer") (C2:C10="Hockey")))

Sekiranya diperlukan, anda tentu boleh memasukkan kriteria dalam sel -sel yang berasingan dan merujuk kepada sel -sel seperti yang ditunjukkan di bawah:

=UNIQUE(FILTER(A2:B10, (C2:C10=G1) (C2:C10=G2)))

Bagaimana formula ini berfungsi:

Sama seperti ketika menguji pelbagai dan kriteria, anda meletakkan beberapa ungkapan logik dalam termasuk argumen fungsi penapis, yang masing -masing mengembalikan pelbagai nilai yang benar dan palsu. Apabila tatasusunan ini ditambah, item yang mana satu atau lebih kriteria yang benar akan mempunyai 1, dan item yang mana semua kriteria palsu akan mempunyai 0. Sebagai hasilnya, apa -apa entri yang memenuhi apa -apa keadaan membuatnya menjadi array yang diserahkan kepada unik.

Untuk maklumat lanjut, sila lihat penapis dengan pelbagai kriteria menggunakan atau logik.

Dapatkan nilai unik dalam excel yang mengabaikan kosong

Jika anda bekerja dengan set data yang mengandungi beberapa jurang, senarai unik yang diperoleh dengan formula biasa mungkin mempunyai sel kosong dan/atau nilai sifar. Ini berlaku kerana fungsi unik Excel direka untuk mengembalikan semua nilai yang berbeza dalam julat, termasuk kosong. Oleh itu, jika julat sumber anda mempunyai kedua -dua sifar dan sel kosong, senarai unik akan mengandungi 2 sifar, yang mewakili sel kosong dan yang lain - nilai sifar itu sendiri. Di samping itu, jika data sumber mengandungi rentetan kosong yang dikembalikan oleh beberapa formula, senarai Uique juga akan termasuk rentetan kosong ("") yang kelihatan seperti sel kosong:

Untuk mendapatkan senarai nilai unik tanpa kekosongan, inilah yang perlu anda lakukan:

• Tapis sel kosong dan rentetan kosong dengan menggunakan fungsi penapis.
• Gunakan fungsi unik untuk mengehadkan hasil kepada nilai unik sahaja.

Dalam bentuk generik, formula kelihatan seperti berikut:

Unik (penapis ( julat , julat ""))

Dalam contoh ini, formula dalam D2 adalah:

=UNIQUE(FILTER(B2:B12, B2:B12""))

Hasilnya, Excel mengembalikan senarai nama unik tanpa sel kosong:

Nota. Sekiranya data asal mengandungi sifar , satu nilai sifar akan dimasukkan ke dalam senarai yang unik.

Cari nilai unik dalam lajur tertentu

Kadang -kadang anda mungkin mahu mengekstrak nilai unik dari dua atau lebih lajur yang tidak bersebelahan antara satu sama lain. Pada masa-masa, anda juga mungkin mahu memerintahkan semula lajur dalam senarai yang dihasilkan. Kedua -dua tugas boleh dipenuhi dengan bantuan fungsi memilih.

Unik (Pilih ({1,2, ...}, Range1 , Range2 ))

Dari jadual sampel kami, katakan anda ingin mendapatkan senarai pemenang berdasarkan nilai -nilai dalam lajur A dan C dan mengatur hasil dalam perintah ini: pertama sukan (lajur c), dan kemudian nama sukan (lajur A). Untuk melakukannya, kami membina formula ini:

=UNIQUE(CHOOSE({1,2}, C2:C10, A2:A10))

Dan dapatkan hasil berikut:

Bagaimana formula ini berfungsi:

Fungsi Pilih mengembalikan pelbagai nilai 2-dimensi dari lajur yang ditentukan. Dalam kes kami, ia juga menukar susunan lajur.

{"Bola Keranjang", "Andrew"; "Bola Keranjang", "Betty"; "Bola Tampar", "David"; "Bola Keranjang", "Andrew"; "Hoki", "Andrew"; "Bola sepak", "Robert"; "Bola Tampar", "David"; "Hoki", "Andrew"; "Bola Keranjang", "David"}

Dari array di atas, fungsi unik mengembalikan senarai rekod unik.

Cari nilai yang unik dan mengendalikan kesilapan

Formula unik yang telah kami bincangkan dalam kerja tutorial ini sempurna ... dengan syarat terdapat sekurang -kurangnya satu nilai yang memenuhi kriteria yang ditentukan. Jika formula tidak menemui apa -apa, #calc! Ralat berlaku:

Untuk mengelakkannya daripada berlaku, hanya bungkus formula anda dalam fungsi Iferror.

Sebagai contoh, jika tiada nilai unik memenuhi kriteria yang dijumpai, anda tidak boleh memaparkan apa -apa, iaitu rentetan kosong (""):

=IFERROR(UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<g2></g2>

Atau anda dapat memberitahu pengguna dengan jelas bahawa tiada hasil yang dijumpai:

=IFERROR(UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<g2 results><p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174347193269059.png" class="lazy" alt="Excel Fungsi unik - cara terpantas untuk mencari nilai yang unik" ></p> <h2 id="Excel-Fungsi-unik-tidak-berfungsi"> Excel Fungsi unik tidak berfungsi</h2> <p> Seperti yang telah anda lihat, kemunculan fungsi unik telah menjadikan nilai -nilai unik dalam Excel sangat mudah. Jika tiba -tiba formula anda mengakibatkan kesilapan, kemungkinan besar akan menjadi salah satu yang berikut.</p> <h3 id="Name-ralat"> #Name? ralat</h3> <p> Berlaku jika anda menggunakan formula unik dalam versi Excel di mana fungsi ini tidak disokong.</p> <p> Pada masa ini, fungsi unik hanya tersedia dalam Excel 365 dan 2021. Jika anda mempunyai versi yang berbeza, anda mungkin mendapati penyelesaian yang sesuai dalam tutorial ini: Bagaimana untuk mendapatkan nilai unik dalam Excel 2019, Excel 2016 dan lebih awal.</p> <p> #Name? Ralat dalam versi yang disokong menunjukkan bahawa nama fungsi itu salah eja. Untuk maklumat lanjut, lihat bagaimana untuk memperbaiki kesilapan #name dalam Excel.</p> <h3 id="Ralat-spill"> Ralat #spill</h3> <p> Berlaku jika satu atau lebih sel dalam julat tumpahan tidak sepenuhnya kosong.</p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174347193419432.png" class="lazy" alt="Excel Fungsi unik - cara terpantas untuk mencari nilai yang unik" ></p></g2>

Untuk menetapkan kesilapan, hanya jelas atau padam sel-sel yang tidak kosong. Untuk melihat dengan tepat sel -sel mana yang mendapat di jalan, klik penunjuk ralat, dan kemudian klik pilih menghalang sel . Untuk maklumat lanjut, sila lihat #spill! Ralat dalam Excel - Punca dan Pembetulan.

Itulah cara mencari nilai unik dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Formula Nilai Unik Excel (fail .xlsx)

Atas ialah kandungan terperinci Excel Fungsi unik - cara terpantas untuk mencari nilai yang unik. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!