Kecil jika di Excel: Dapatkan nilai terkecil nth dengan kriteria

Cuba mendapatkan nilai bawah berdasarkan satu atau lebih syarat? Hanya gunakan fungsi kecil bersama -sama dengan jika. Tutorial ini menunjukkan cara membina penyelesaian kerja dan menerangkan logik dalamannya, supaya anda dapat dengan mudah menguraikan formula dan menyesuaikan keperluan anda.

Microsoft Excel mempunyai beberapa fungsi untuk melakukan pengiraan "bersyarat" seperti maxifs, minifs, purataf, dan sebagainya. Malangnya, fungsi kecil jika tidak wujud. Walau bagaimanapun, tiada apa yang menghalang anda daripada membina formula anda sendiri untuk mencari nilai terkecil N-th dengan kriteria. Jika anda tidak biasa dengan fungsi kecil Excel, maka anda mungkin mahu memulakan dengan asas -asas dan membaca tutorial yang dipautkan di atas terlebih dahulu.

• Cara menggunakan kecil jika di excel
• Kecil jika dengan pelbagai kriteria (dan logik)
• Kecil jika dengan pelbagai keadaan (atau logik)
• Kecil jika tidak sifar
• Menapis nilai terkecil berdasarkan kriteria

Kecil jika formula dalam excel

Untuk mendapatkan nilai terendah N yang sepadan dengan kriteria yang anda tentukan, anda boleh menggunakan formula generik berikut:

{= Kecil (jika ( kriteria_range = kriteria , nilai ), n )}

Di mana n ialah 1 ^st , 2 ^nd , 3 ^rd , dan sebagainya. Nilai terendah untuk kembali.

Untuk formula berfungsi dengan betul, anda harus memasukkannya sebagai formula array dengan menekan shift Ctrl ENTER KEYS Secara serentak. Apabila anda melakukan ini, Excel akan mengelilingi formula dalam kurungan keriting seperti yang ditunjukkan dalam tangkapan skrin di bawah. Dalam Excel 365, ia juga berfungsi sebagai formula biasa kerana sokongan untuk tatasusunan dinamik.

Dari sampel kami meja, mari kita pilih 3 skor bawah dalam subjek tertentu, kata seni . Dengan senarai subjek ( kriteria_range ) dalam B2: B15, skor dalam C2: C15 ( nilai ) dan N dalam E3, formula mengambil bentuk ini:

=SMALL(IF($B$2:$B$15="art", $C$2:$C$15), $E3)

Formula ini mengembalikan skor seni terkecil di F3. Salinnya melalui F5, dan anda akan mendapat keputusan terendah 2 ^ND dan 3 ^RD .

Untuk kemudahan, anda juga boleh memasukkan nama -nama subjek sasaran dalam sel yang telah ditetapkan (F2 - ART dan G2 - Sains ), dan merujuk kepada sel -sel tersebut sebagai kriteria:

=SMALL(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)

Jika reka bentuk lembaran kerja anda tidak menyediakan nombor n , anda boleh menjana mereka secara langsung dalam formula dengan menggunakan fungsi baris dengan rujukan pelbagai yang berkembang seperti ini:

=SMALL(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))

Oleh kerana penggunaan pintar rujukan mutlak dan relatif, rujukan pelbagai berkembang secara automatik kerana formula disalin ke sel -sel di bawah. Dalam E3, baris (A $ 2: A2) menjana N sama dengan 1, dan formula mengembalikan skor terkecil untuk seni . Dalam E4, rujukan berubah kepada A $ 2: A3 yang menyebabkan baris kembali 2, jadi kami mendapat skor terkecil ^ke -2, dan sebagainya.

Sekiranya nilai terkecil N-th dengan kriteria yang ditentukan tidak dijumpai, formula kecil jika akan mengembalikan ralat #num. Untuk menjebak ralat ini dan menggantinya dengan nilai apa pun yang anda dapati sesuai ("-" dalam kes kami), anda boleh menggunakan fungsi IFError:

=IFERROR(SMALL(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "-")

Bagaimana formula ini berfungsi:

Dari array yang dibekalkan untuk hujah 1 ^ST , fungsi kecil mengembalikan nilai terkecil N-th yang dinyatakan dalam hujah ^ke- 2. Masalahnya ialah kita tidak mahu setiap nilai dalam array diproses, tetapi hanya skor dalam subjek tertentu. Untuk mengehadkan array kepada nilai seni , kami memberitahu fungsi jika membandingkan senarai (b2: b15) terhadap subjek sasaran:

IF($B$2:$B$15="Art", $C$2:$C$15)

Kerana ujian logik dilakukan pada pelbagai nombor, hasilnya juga merupakan array, di mana angka mewakili skor seni , dan nilai palsu apa -apa skor lain:

{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}

Oleh kerana fungsi kecil mengabaikan kesilapan (dan apa-apa lagi yang bukan nombor), hasilnya adalah nilai terkecil N-th dari skor seni dalam array di atas.

Excel kecil jika dengan pelbagai kriteria

Untuk menyebabkan formula kecil jika menilai beberapa syarat, pilih salah satu pendekatan berikut.

Membekalkan pelbagai kriteria dengan menggunakan bersarang jika pernyataan:

{= Kecil (jika ( kriteria_range1 = kriteria1 , jika ( kriteria_range2 = kriteria2 , nilai )), n )}

Gongangkan ungkapan logik:

{= Kecil (jika (( kriteria_range1 = kriteria1 ) * ( kriteria_range2 = kriteria2 ), nilai ), n )}

Sila ambil perhatian bahawa kedua -duanya adalah formula array, jadi jangan lupa untuk menekan CTRL Shift Enter untuk menyelesaikannya dengan betul. Dalam Excel 365, ini juga akan berfungsi sebagai formula biasa.

Untuk menguji formula "di lapangan", kami akan melanjutkan jadual sampel kami dengan lajur sekolah dan input 2 kriteria dalam sel berasingan seperti yang ditunjukkan di bawah.

Sekarang, kita semua mempunyai parameter yang diperlukan untuk formula kecil kita:

• Kriteria_range1 - Senarai subjek (B2: B15)
• Kriteria1 - Subjek Sasaran (G1)
• Kriteria_range2 - Jenis Sekolah (C2: C15)
• Kriteria2 - Sekolah Sasaran (G2)
• Nilai - Skor (D2: D15)
• N - Bilangan 1 hingga 3 dalam F6, F7 dan F8

Meletakkan hujah -hujah bersama, kita mendapat formula ini:

=SMALL(IF($B$2:$B$15=$G$1, IF($C$2:$C$15=$G$2, $D$2:$D$15)), F6)

=SMALL(IF(($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2), $D$2:$D$15), F6)

Masukkan salah satu di G6, seret melalui G8, dan anda akan mendapat hasil berikut.

Bagaimana formula ini berfungsi:

Pada dasarnya, kedua -dua formula menggunakan fungsi IF untuk menguji pelbagai syarat, sehingga hanya nilai -nilai yang mana semua syarat adalah benar masuk ke dalam argumen array fungsi kecil.

IFS bersarang:

Dalam ujian logik fungsi pertama jika, kita membandingkan senarai subjek terhadap sasaran satu ( seni ): $ b $ 2: $ b $ 15 = $ g $ 1. Hasil operasi ini adalah pelbagai nilai yang benar dan palsu, di mana benar sepadan dengan nilai seni dalam lajur b:

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

Pernyataan yang kedua jika sel -sel yang sel -sel dalam julat C2: C15 sepadan dengan kriteria dalam G2 ( junior ), dan juga mengembalikan pelbagai benar dan palsu:

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

Untuk argumen value_if_true yang kedua jika, kami membekalkan skor (D2: D15). Ini memastikan bahawa hanya item yang benar dalam dua array di atas "bertahan"; Semua skor lain digantikan dengan nilai palsu:

{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;FALSE;FALSE;125}

Arahan akhir ini pergi ke fungsi kecil, dari mana ia mengembalikan nilai terendah N-th.

Mengalikan kriteria:

Formula kedua memeriksa kedua -dua keadaan dalam satu ujian logik tunggal, di mana operasi pendaraban berfungsi sebagai dan pengendali.

Apabila unsur -unsur dua tatasusunan benar/palsu didarabkan, nilai logik ditukar kepada 1 (benar) dan 0 (palsu). Apabila mendarab dengan 0 memberikan sifar, array yang dihasilkan mempunyai 1 untuk data yang memenuhi kedua -dua kriteria:

{0;1;0;1;0;0;1;1;0;1;0;0;0;1}

Fungsi IF menilai pelbagai 1 dan 0 dalam ujian logik dan lulus skor yang sepadan dengan 1 ke kecil.

Kecil jika formula dengan pelbagai atau kriteria

Contoh sebelumnya menunjukkan bagaimana untuk mencari nilai bawah berdasarkan pelbagai kriteria menggunakan dan logik, iaitu apabila semua keadaan berpuas hati. Untuk mendapatkan nilai terkecil yang memenuhi syarat -syarat, anda perlu membina formula kecil atau logik. Untuk ini, tambahkan kriteria dan bukannya mendarabkannya.

{= Kecil (jika (( kriteria_range1 = kriteria1 ) ( kriteria_range2 = kriteria2 ), nilai ), n )}

Sebagai contoh, mari kita pilih skor terendah dalam dua subjek yang berbeza, katakan sejarah dan kesusasteraan . Dari segi jika Excel, formula akan mengembalikan skor terkecil N-th jika subjek adalah sama ada sejarah atau kesusasteraan .

Dengan subjek dalam B2: B15 dan skor dalam C2: C15, inilah formula untuk mengembalikan skor terendah:

=SMALL(IF(($B$2:$B$15="History") ($B$2:$B$15="Literature"), $C$2:$C$15), 1)

Sudah tentu, anda boleh memasukkan kriteria dan nombor N dalam sel yang berasingan, menyelesaikan formula dengan menekan shift Ctrl masukkan dan dapatkan hasil ini:

=SMALL(IF(($B$2:$B$15=$E$2) ($B$2:$B$15=$F$2), $C$2:$C$15), E6)

Bagaimana formula ini berfungsi:

Logik formula sangat mirip dengan apa yang dibincangkan dengan kecil jika dengan contoh berganda dan kriteria. Perbezaannya adalah bahawa julat = persamaan kriteria disatukan dengan operasi tambahan yang berfungsi seperti atau pengendali dalam formula array:

Memeriksa senarai subjek terhadap 2 kriteria yang berbeza menghasilkan 2 tatasusunan nilai yang benar dan palsu. Menambah unsur -unsur tatasusunan itu menghasilkan pelbagai 1 dan 0, di mana 1 sesuai dengan item yang memenuhi sekurang -kurangnya satu syarat (tidak kira siapa) dan sifar sesuai dengan item yang tidak memenuhi syarat. Arahan akhir ini pergi ke ujian logik jika:

{0;1;0;1;0;0;1;1;0;1;0;1;0;1}

Fungsi IF menilai setiap elemen array di atas dan melepasi skor yang sepadan dengan 1 ke fungsi kecil, dari mana ia memilih nilai n-th yang ditentukan:

{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}

Cara Mencari Nilai Terkecil Mengabaikan Zeros

Walaupun dataset anda kadang -kadang mengandungi beberapa nilai sifar, ia tidak selalu masuk akal untuk memasukkan nol tersebut dalam hasil formula. Untuk meninggalkan semua 0 nilai, kami akan menggunakan formula kecil yang sudah biasa dengan kriteria "tidak sama dengan sifar".

Formula 1. Kecil jika tidak sifar

Untuk mendapatkan nilai terkecil N-th yang mengabaikan 0, gunakan formula array generik ini:

{= Kecil (jika ( nilai 0, nilai ), n )}

Katakan beberapa pelajar terlepas beberapa peperiksaan dan mempunyai 0 skor untuk subjek tersebut. Untuk mendapatkan 3 skor terendah yang lebih besar daripada 0, formula adalah:

=SMALL(IF($C$2:$C$150, $C$2:$C$15), ROWS(A$2:A2))

Sila ingat untuk menekan CTRL Shift Enter untuk menyelesaikannya dengan betul.

Formula ini pergi ke sel paling atas (E2), di mana ia mengekstrak skor terendah. Dan kemudian, anda menyeret formula melalui dua lagi sel untuk mengekstrak skor 2 ^ND dan 3 ^RD terendah. Fungsi baris (A $ 2: A2) secara automatik menjana nombor N , jadi anda tidak perlu menaipnya di mana -mana di dalam lembaran.

Formula 2. Kecil jika lebih besar daripada sifar dengan keadaan

Untuk mencari nilai terkecil N-th lebih besar daripada sifar berdasarkan kriteria, letakkan kriteria tambahan dengan cara ini:

{= Kecil (jika (( nilai 0) * ( kriteria_range = kriteria ), nilai ), n )}

Sebagai contoh, mari kita tarik skor bawah 3 untuk setiap subjek secara individu. Untuk ini, kami memasuki nama subjek dalam E2 ( ART ) dan F2 ( Sains ) dan menggunakan formula ini untuk mengekstrak skor seni terkecil:

=SMALL(IF(($C$2:$C$150) * ($B$2:$B$15=E$2), $C$2:$C$15), ROWS(A$2:A2))

Seret formula ke kanan, dan anda juga akan mendapat skor sains terkecil:

Menapis nilai terkecil berdasarkan kriteria

Penyelesaian ini hanya berfungsi dalam binaan baru -baru ini Excel 365 di mana fungsi array dinamik disediakan.

Satu lagi cara untuk mendapatkan nilai bawah N-th dalam Excel berdasarkan keadaan menggunakan fungsi kecil bersama-sama dengan penapis. Ini berfungsi dengan indah sebagai formula biasa yang disiapkan dengan kunci Enter .

Logik formula adalah sama seperti contoh sebelumnya. Perbezaannya ialah anda menggunakan fungsi penapis dan bukannya jika menggunakan kriteria.

Formula 1. Cari nilai bawah N-th dengan satu keadaan

Sekiranya terdapat satu syarat yang perlu dipenuhi, anda boleh mendapatkan nilai terkecil nth dengan formula ini:

Kecil (penapis ( nilai , kriteria_range = kriteria ), n )

Untuk dataset sampel kami, formula berjalan seperti berikut:

=SMALL(FILTER($C$2:$C$15, $B$2:$B$15=F$2), $E3)

Di mana B2: B15 adalah julat kriteria (senarai subjek), C2: C15 adalah nilai (skor), F2 adalah kriteria (subjek kepentingan) dan E3 adalah skor terkecil N-th untuk kembali.

Formula 2. Dapatkan nilai terkecil n-th dengan pelbagai kriteria

Untuk menguji pelbagai syarat, inilah formula untuk digunakan:

Kecil (penapis ( nilai , ( kriteria_range1 = kriteria1 ) * ( kriteria_range2 = kriteria2 )), n )

Dengan mengandaikan anda ingin mencari skor terendah N-th dalam subjek tertentu (G1) di kalangan pelajar sekolah tertentu (G2), formula adalah:

=SMALL(FILTER($D$2:$D$15, ($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2)), $F6)

Di mana B2: B15 adalah senarai subjek, C2: C15 adalah jenis sekolah, D2: D15 adalah skor, dan F6 adalah nilai terkecil N-th untuk kembali.

Formula 3. Penapis N-th Nilai terkecil dengan atau kriteria

Untuk menapis nombor terkecil N-th apabila keadaan ini atau itu benar, formula adalah:

Kecil (penapis ( nilai , ( kriteria_range1 = kriteria1 ) ( kriteria_range2 = kriteria2 )), n )

Sebagai contoh, anda boleh menemui skor terendah dalam subjek kemanusiaan ( sejarah atau kesusasteraan ) dengan menggunakan formula ini:

=SMALL(FILTER($C$2:$C$15, ($B$2:$B$15=$E$2) ($B$2:$B$15=$F$2)), $E6)

Di mana B2: B15 adalah senarai subjek, C2: C15 adalah skor, E2 dan F2 adalah subjek kepentingan, dan E6 adalah nilai terkecil N-th.

Petua. Anda juga boleh menggunakan fungsi kecil untuk mengekstrak rekod bawah N dengan penapis lanjutan.

Itulah cara menggunakan Kecil jika dalam Excel untuk mencari nilai terkecil berdasarkan keadaan. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Excel kecil jika contoh (fail .xlsx)

Atas ialah kandungan terperinci Kecil jika di Excel: Dapatkan nilai terkecil nth dengan kriteria. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!