Dalam tutorial pendek ini, kami akan bercakap tentang fungsi kecil Excel, bagaimana ia berfungsi dan cara menggunakannya untuk mencari nombor, tarikh, atau masa terkecil nth.

Perlu mencari beberapa nombor terendah dalam lembaran kerja? Ini agak mudah dilakukan dengan ciri jenis Excel. Tidak mahu membuang masa untuk menyiarkan semula data anda dengan setiap perubahan? Fungsi kecil akan membantu anda dengan cepat mencari nilai terendah, kedua terkecil, ketiga terkecil, dan sebagainya.

Excel Fungsi Kecil

Kecil adalah fungsi statistik yang mengembalikan nilai terkecil N-th dalam set data.

Sintaks fungsi kecil termasuk dua hujah, yang kedua -duanya diperlukan.

Kecil (array, k)

Di mana:

• Array - Arahan atau pelbagai sel dari mana untuk mengekstrak nilai terkecil.
• K - Integer yang menunjukkan kedudukan dari nilai terendah untuk kembali, iaitu K -th terkecil.

Fungsi ini tersedia dalam semua versi Excel untuk Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, dan lebih awal.

Petua. Untuk mencari nilai terendah k-th dengan kriteria, gunakan excel kecil jika formula.

Formula Kecil Asas di Excel

Formula kecil dalam bentuk asasnya sangat mudah untuk dibina - anda hanya menentukan julat dan kedudukan dari item terkecil untuk kembali.

Dalam senarai nombor dalam B2: B10, andaikan anda ingin mengekstrak nilai terkecil 3 ^rd . Formula semudah:

=SMALL(B2:B10, 3)

Untuk memudahkan anda menyemak hasilnya, Lajur B disusun mengikut urutan menaik:

4 perkara yang harus anda ketahui mengenai fungsi kecil

Nota penggunaan berikut akan membantu anda memahami dengan lebih baik tingkah laku fungsi kecil dan mengelakkan kekeliruan apabila membina formula anda sendiri.

1. Mana -mana sel kosong , nilai teks , dan nilai logik yang benar dan palsu dalam hujah array diabaikan.
2. Jika array mengandungi satu atau lebih kesilapan , ralat dikembalikan.
3. Sekiranya terdapat pendua dalam pelbagai , formula anda boleh menyebabkan "ikatan". Sebagai contoh, jika dua sel mengandungi nombor 1, dan fungsi kecil dikonfigurasikan untuk mengembalikan nilai terkecil dan 2 ^nd terkecil, anda akan mendapat 1 dalam kedua -dua kes.
4. Dengan mengandaikan n ialah bilangan nilai dalam array , kecil (array, 1) akan mengembalikan nilai terendah, dan kecil (array, n) akan memilih nilai tertinggi.

Cara Menggunakan Fungsi Kecil dalam Contoh Formula Excel -

Dan sekarang, mari kita lihat beberapa contoh fungsi kecil Excel yang melampaui penggunaan asasnya.

Cari nilai bawah 3, 5, 10, dan lain -lain

Seperti yang sudah anda ketahui, fungsi kecil direka untuk mengira nilai terendah N-th. Contoh ini menunjukkan bagaimana untuk melakukannya dengan berkesan.

Dalam jadual di bawah, katakan anda ingin mencari nilai bawah 3. Untuk ini, taipkan nombor 1, 2 dan 3 dalam sel berasingan (D3, D4 dan D5 dalam kes kita). Kemudian, masukkan formula berikut dalam E3 dan seretnya melalui E5:

=SMALL($B$2:$B$10, D3)

Dalam E3, formula mengekstrak nilai terkecil menggunakan nombor dalam D3 untuk hujah K. Perkara utama adalah untuk membekalkan rujukan sel yang betul kerana formula itu menyalin dengan betul dalam sel -sel lain: mutlak untuk array dan relatif untuk k .

Tidak mahu bersusah payah menaip pangkat secara manual? Gunakan fungsi baris dengan rujukan pelbagai yang berkembang untuk memberikan nilai k . Untuk ini, kami membuat rujukan mutlak untuk sel pertama (atau hanya mengunci koordinat baris seperti B $ 2) dan rujukan relatif untuk sel terakhir:

=SMALL($B$2:$B$10, ROWS(B$2:B2))

Hasilnya, rujukan pelbagai berkembang apabila formula disalin ke lajur. Dalam D2, baris (B $ 2: B2) menghasilkan 1 untuk k , dan formula mengembalikan kos terendah. Dalam D3, baris (B $ 2: B3) menghasilkan 2, dan kami mendapat kos terendah ^ke -2, dan sebagainya.

Cukup salin formula melalui 5 sel, dan anda akan mendapat 5 nilai bawah:

Nilai N Bottom N

Ingin mencari jumlah nilai N terkecil dalam dataset? Sekiranya anda telah mengekstrak nilai -nilai seperti yang ditunjukkan dalam contoh sebelumnya, penyelesaian yang paling mudah akan menjadi formula jumlah seperti:

=SUM(E3:E5)

Atau anda boleh membuat formula bebas dengan menggunakan fungsi kecil bersama -sama dengan sumproduct:

Untuk mendapatkan jumlah nilai 3 bawah dalam set data kami, formula mengambil bentuk ini:

=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))

Fungsi Jumlah akan menghasilkan hasil yang sama:

=SUM(SMALL(B2:B10, {1,2,3}))

Nota. Jika anda menggunakan rujukan sel dan bukannya pemalar array untuk k , anda perlu menekan CTRL Shift Enter untuk menjadikannya formula array. Dalam Excel 365 yang menyokong tatasusunan dinamik, jumlah kerja kecil sebagai formula biasa dalam kedua -dua kes.

Bagaimana formula ini berfungsi:

Dalam formula biasa, pulangan kecil satu nilai terkecil k-th dalam julat. Dalam kes ini, kami membekalkan array pemalar seperti {1,2,3} untuk hujah K, memaksa ia untuk mengembalikan pelbagai nilai 3 terkecil:

{29240, 43610, 58860}

Fungsi SumProduct atau SUM menambah nombor dalam array dan output jumlahnya. Itu sahaja!

Indeks sepadan dengan formula kecil untuk mendapatkan perlawanan terkecil

Dalam keadaan apabila anda ingin mengambil beberapa data yang berkaitan dengan nilai terkecil, gunakan kombinasi perlawanan indeks klasik dengan kecil untuk nilai carian:

Di mana:

• Return_array adalah julat dari mana untuk mengekstrak data yang berkaitan.
• Lookup_Array adalah julat di mana untuk mencari nilai n-th terendah.
• N ialah kedudukan nilai kepentingan terkecil.

Sebagai contoh, untuk mendapatkan nama projek yang mempunyai kos terendah, formula dalam E3 adalah:

=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))

Di mana A2: A10 adalah nama projek, B2: B10 adalah kos dan D3 adalah pangkat dari terkecil.

Salin formula ke sel di bawah (E4 dan E5), dan anda akan mendapat nama 3 projek termurah:

Nota:

• Penyelesaian ini berfungsi dengan baik untuk dataset yang tidak mempunyai pendua. Walau bagaimanapun, dua atau lebih nilai pendua dalam lajur angka boleh membuat "ikatan" dalam ranking, yang akan membawa kepada hasil yang salah. Dalam kes ini, sila gunakan formula yang lebih canggih untuk memecahkan ikatan.
• Dalam Excel 365, tugas ini dapat dicapai dengan bantuan fungsi array dinamik baru. Selain daripada menjadi lebih mudah, pendekatan ini secara automatik menyelesaikan masalah hubungan. Untuk butiran lengkap, sila lihat cara menapis nilai bawah N dalam Excel.

Susun nombor dari terendah hingga tertinggi dengan formula

Saya percaya semua orang tahu bagaimana untuk meletakkan nombor dengan ciri -ciri Excel Sort. Tetapi adakah anda tahu bagaimana melakukan penyortiran dengan formula? Pengguna Excel 365 boleh melakukannya dengan mudah dengan fungsi jenis baru. Dalam versi Excel 2019, 2016 dan terdahulu, Sort tidak berfungsi, malangnya. Tetapi mempunyai sedikit kepercayaan, dan kecil akan datang untuk menyelamatkan :)

Seperti dalam contoh pertama, kami menggunakan fungsi baris dengan rujukan pelbagai yang berkembang kepada kenaikan k oleh 1 dalam setiap baris di mana formula disalin:

=SMALL($A$2:$A$10, ROWS(A$2:A2))

Masukkan formula di sel pertama, dan kemudian seret ke seberapa banyak sel kerana terdapat nilai dalam set data asal (C2: C10 dalam contoh ini):

Petua. Untuk menyusun menurun , gunakan fungsi besar dan bukannya kecil.

Excel formula kecil untuk tarikh dan masa

Kerana tarikh dan masa juga nilai angka (dalam sistem Excel dalaman, tarikh disimpan sebagai nombor dan masa berurutan sebagai pecahan perpuluhan), fungsi kecil dapat mengendalikannya serta tanpa usaha tambahan di pihak anda.

Seperti yang anda lihat dalam tangkapan skrin di bawah, formula asas yang kami gunakan untuk nombor berfungsi dengan indah untuk tarikh dan masa juga:

=SMALL($B$2:$B$10, D2)

Formula kecil untuk mencari 3 tarikh terawal:

Formula kecil untuk mendapatkan 3 kali terpendek:

Contoh seterusnya menunjukkan bagaimana fungsi kecil dapat membantu anda mencapai tugas yang lebih spesifik yang berkaitan dengan tarikh.

Cari tarikh sebelumnya yang paling dekat dengan hari ini atau tarikh yang ditentukan

Dalam senarai tarikh, anggap anda ingin mencari tarikh terdekat sebelum tarikh yang ditentukan. Ini boleh dilakukan dengan menggunakan fungsi kecil dalam kombinasi dengan countif.

Dengan senarai tarikh dalam B2: B10 dan tarikh sasaran dalam E1, formula berikut akan mengembalikan tarikh terdahulu yang paling dekat dengan tarikh sasaran:

=SMALL(B2:B10, COUNTIF(B2:B10, "

Untuk mengekstrak tarikh yang dua tarikh sebelum tarikh E1, iaitu sebelumnya tetapi satu tarikh, formula adalah:

=SMALL(B2:B10, COUNTIF(B2:B10, "

Untuk mencari tarikh yang lalu paling dekat dengan hari ini , gunakan fungsi hari ini untuk kriteria countif:

=SMALL(B2:B10, COUNTIF(B2:B10, "

Petua. Untuk mengelakkan kesilapan dalam keadaan apabila tarikh yang sepadan dengan kriteria anda tidak dijumpai, anda boleh membungkus fungsi iferror di sekitar formula anda, seperti ini:

=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<p> <b>Bagaimana formula ini berfungsi:</b></p> <p> Idea umum adalah untuk mengira bilangan tarikh yang lebih kecil daripada tarikh sasaran dengan COUNTIF. Dan kiraan ini adalah apa yang diperlukan oleh fungsi kecil untuk hujah <em>K.</em></p> <p> Untuk memahami konsep yang lebih baik, mari kita lihat dari sudut lain:</p> <p> Jika 1-Aug-2020 (tarikh sasaran dalam E1) muncul dalam dataset kami, ia akan menjadi tarikh ke <sup>-</sup> 7 terbesar dalam senarai. Akibatnya, terdapat enam tarikh yang lebih kecil daripada itu. Maksudnya, tarikh terkecil <sup>ke</sup> -6 adalah tarikh sebelumnya yang paling dekat dengan tarikh sasaran.</p> <p> Oleh itu, pertama kita mengira berapa banyak tarikh yang lebih kecil daripada tarikh E1 (hasilnya ialah 6):</p> <p> <code>COUNTIF(B2:B10, "

Dan kemudian, pasangkan kiraan ke dalam hujah ² yang kecil:

=SMALL(B2:B10, 6)

Untuk mendapatkan tarikh sebelumnya tetapi satu (yang merupakan tarikh terkecil ke ^-5 dalam kes kami), kami tolak 1 dari hasil COUNTIF.

Cara menyerlahkan nilai bawah dalam excel

Untuk menyerlahkan nilai N terkecil dalam jadual anda dengan pemformatan bersyarat Excel, anda boleh menggunakan sama ada pilihan atas/bawah terbina dalam atau menetapkan peraturan anda sendiri berdasarkan formula kecil. Kaedah pertama lebih cepat dan lebih mudah untuk digunakan, sementara yang kedua memberikan lebih banyak kawalan dan fleksibiliti. Langkah -langkah di bawah akan membimbing anda melalui membuat peraturan tersuai:

1. Pilih julat di mana anda ingin menyerlahkan nilai bawah. Dalam kes kami, nombor dalam B2: B10, jadi kami memilihnya. Jika anda ingin menyerlahkan keseluruhan baris, maka pilih A2: B10.
2. Pada tab Home , dalam kumpulan Styles , klik pemformatan bersyarat > peraturan baru .
3. Dalam kotak dialog Peraturan Formatting baru , pilih Gunakan formula untuk menentukan sel -sel yang hendak dipenuhi.
4. Dalam nilai format di mana formula ini adalah kotak yang benar , masukkan formula seperti ini:

   =B2

   Di mana B2 adalah sel paling kiri dari julat angka yang akan diperiksa, $ B $ 2: $ B $ 10 adalah julat keseluruhan, dan 3 adalah nilai bawah N untuk diserlahkan.

   Dalam formula anda, sila ingat jenis rujukan: Sel paling kiri adalah rujukan relatif (B2) manakala julatnya adalah rujukan mutlak ($ ??B $ 2: $ B $ 10).

5. Klik butang Format dan pilih mana -mana format yang anda suka.
6. Klik OK dua kali untuk menutup kedua -dua tingkap dialog.

Selesai! Nilai 3 bawah dalam lajur b diserlahkan:

Untuk maklumat lanjut, sila lihat pemformatan bersyarat Excel berdasarkan formula.

Excel Fungsi Kecil Tidak Berfungsi

Seperti yang anda lihat dari contoh kami, menggunakan fungsi kecil di Excel agak mudah, dan anda tidak mungkin menghadapi masalah dengannya. Jika formula anda tidak berfungsi, kemungkinan besar akan menjadi #num! ralat, yang mungkin berlaku kerana sebab berikut:

• Array kosong atau tidak mengandungi satu nilai angka.
• Nilai K adalah kurang daripada sifar (kesilapan yang bodoh boleh menelan kos penyelesaian masalah!) Atau melebihi jumlah nilai dalam array.

Itulah cara menggunakan formula kecil dalam Excel untuk mencari dan menyerlahkan nombor bawah dalam satu set data. Jika anda tahu apa -apa senario lain di mana fungsi ini berguna, anda amat dialu -alukan untuk berkongsi dalam komen. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Formula Kecil Excel (fail .xlsx)

Atas ialah kandungan terperinci Excel fungsi kecil untuk mencari dan menyerlahkan nilai terendah. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!