Cara Mengira Nilai Unik dalam Excel: Dengan Kriteria, Mengabaikan Kekal

Tutorial ini melihat cara memanfaatkan fungsi array dinamik baru untuk mengira nilai -nilai unik dalam Excel: Formula untuk mengira penyertaan unik dalam lajur, dengan pelbagai kriteria, mengabaikan kekosongan, dan banyak lagi.

Beberapa tahun yang lalu, kami membincangkan pelbagai cara untuk mengira nilai yang unik dan berbeza dalam Excel. Tetapi seperti mana -mana program perisian lain, Microsoft Excel terus berkembang, dan ciri -ciri baru muncul dengan hampir setiap pelepasan. Hari ini, kita akan melihat bagaimana mengira nilai -nilai unik dalam Excel boleh dilakukan dengan fungsi array dinamik yang baru diperkenalkan. Sekiranya anda belum menggunakan mana -mana fungsi ini, anda akan kagum melihat betapa mudahnya formula yang menjadi dari segi bangunan dan kemudahan untuk digunakan.

Nota. Semua formula yang dibincangkan dalam tutorial ini bergantung pada fungsi yang unik, yang hanya tersedia dalam Excel 365 dan Excel 2021. Jika anda menggunakan Excel 2019, Excel 2016 atau lebih awal, sila lihat artikel ini untuk penyelesaian.

• Kira nilai unik dalam lajur
• Dapatkan kiraan nilai unik yang berlaku sekali sahaja
• Kira baris unik
• Kira penyertaan unik mengabaikan kekosongan
• Kira nilai unik dengan kriteria
• Kira nilai unik dengan pelbagai kriteria

Kira nilai unik dalam lajur

Cara paling mudah untuk mengira nilai unik dalam lajur adalah menggunakan fungsi unik bersama -sama dengan fungsi Counta:

Counta (unik ( julat ))

Formula ini berfungsi dengan logik mudah ini: Pulangan unik pelbagai penyertaan yang unik, dan Counta mengira semua elemen array.

Sebagai contoh, mari mengira nama unik dalam julat B2: B10:

=COUNTA(UNIQUE(B2:B10))

Formula memberitahu kita bahawa terdapat 5 nama yang berbeza dalam senarai pemenang:

Petua. Dalam contoh ini, kami mengira nilai teks yang unik, tetapi anda boleh menggunakan formula ini untuk jenis data lain juga termasuk nombor, tarikh, masa, dll.

Kira nilai unik yang berlaku sekali sahaja

Dalam contoh terdahulu, kami mengira semua penyertaan yang berbeza (berbeza) dalam lajur. Kali ini, kami ingin mengetahui bilangan rekod unik yang berlaku sekali sahaja . Untuk melakukannya, bina formula anda dengan cara ini:

Untuk mendapatkan senarai kejadian satu kali, tetapkan hujah ke-3 yang unik untuk benar:

UNIQUE(B2:B10,,TRUE))

Untuk mengira kejadian satu kali yang unik, sarang unik dalam fungsi baris:

ROWS(UNIQUE(B2:B10,,TRUE))

Sila ambil perhatian bahawa Counta tidak akan berfungsi dalam kes ini kerana ia mengira semua sel bukan kosong, termasuk nilai ralat. Jadi, jika tiada keputusan yang dijumpai, unik akan mengembalikan kesilapan, dan Counta akan mengira ia sebagai 1, yang salah!

Untuk mengendalikan kesilapan yang mungkin, bungkus fungsi IFError di sekitar formula anda dan ajarkannya kepada output 0 jika berlaku ralat:

=IFERROR(ROWS(UNIQUE(B2:B10,,TRUE)), 0)

Hasilnya, anda mendapat kiraan berdasarkan konsep pangkalan data yang unik:

Kira baris unik di Excel

Sekarang anda tahu bagaimana untuk mengira sel -sel yang unik dalam lajur, ada idea tentang bagaimana untuk mencari bilangan baris unik?

Inilah penyelesaiannya:

Baris (unik ( julat ))

Caranya adalah untuk "memberi" keseluruhan julat ke unik supaya ia mendapati kombinasi nilai yang unik dalam pelbagai lajur. Selepas itu, anda hanya melampirkan formula dalam baris berfungsi untuk mengira bilangan baris.

Sebagai contoh, untuk mengira baris unik dalam julat A2: C10, kami menggunakan formula ini:

=ROWS(UNIQUE(A2:C10))

Kira penyertaan unik mengabaikan sel kosong

Untuk mengira nilai -nilai yang unik dalam Excel mengabaikan kosong, menggunakan fungsi penapis untuk menyaring sel kosong, dan kemudian meledingkannya dalam formula unik Counta yang sudah biasa:

Counta (Unik (Penapis ( Range , Range "")))

Dengan data sumber dalam B2: B11, formula mengambil borang ini:

=COUNTA(UNIQUE(FILTER(B2:B11, B2:B11"")))

Tangkapan skrin di bawah menunjukkan hasilnya:

Kira nilai unik dengan kriteria

Untuk mengekstrak nilai unik berdasarkan kriteria tertentu, anda sekali lagi menggunakan fungsi unik dan penapis bersama -sama seperti yang dijelaskan dalam contoh ini. Dan kemudian, anda menggunakan fungsi baris untuk mengira entri yang unik dan ifeRror untuk menjebak semua jenis kesilapan dan menggantikannya dengan 0:

IfeRror (baris (unik ( julat , kriteria_range = kriteria ))), 0)

Sebagai contoh, untuk mencari berapa banyak pemenang yang berbeza dalam sukan tertentu, gunakan formula ini:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10=E1))), 0)

Di mana A2: A10 adalah pelbagai untuk mencari nama yang unik ( julat ), B2: B10 adalah sukan di mana pemenang bersaing ( kriteria_range ), dan E1 adalah sukan minat ( kriteria ).

Kira nilai unik dengan pelbagai kriteria

Formula untuk mengira nilai -nilai unik berdasarkan kriteria berganda hampir sama dengan contoh di atas, walaupun kriteria dibina sedikit berbeza:

IfeRror (baris (unik ( julat , ( kriteria_range1 = kriteria1 ) * ( kriteria_range2 = kriteria2 ))))), 0)

Mereka yang ingin tahu mekanik dalaman, dapat mencari penjelasan logik formula di sini: Cari nilai unik berdasarkan pelbagai kriteria.

Dalam contoh ini, kita akan mengetahui berapa banyak pemenang yang berbeza dalam sukan tertentu dalam F1 ( Kriteria 1 ) dan di bawah umur di F2 ( Kriteria 2 ). Untuk ini, kami menggunakan formula ini:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<f2></f2>

Di mana A2: B10 adalah senarai nama ( julat ), C2: C10 adalah sukan ( kriteria_range 1 ) dan d2: d10 adalah umur ( kriteria_range 2 ).

Itulah cara mengira nilai -nilai unik dalam Excel dengan fungsi array dinamik baru. Saya pasti anda menghargai betapa mudahnya semua penyelesaian menjadi. Bagaimanapun, terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Kira Contoh Formula Nilai Unik (fail .xlsx)

Atas ialah kandungan terperinci Cara Mengira Nilai Unik dalam Excel: Dengan Kriteria, Mengabaikan Kekal. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!