Excel fungsi besar untuk mendapatkan nilai tertinggi N-th

Artikel ini akan membimbing anda tentang cara menggunakan fungsi besar dalam Excel dengan banyak contoh formula.

Apabila menganalisis satu set nombor, ia sering masuk akal untuk mencari yang terbesar. Mendapatkan nilai tertinggi adalah sangat mudah dengan fungsi maksimum. Ketika datang untuk mensasarkan nilai terbesar tertentu, katakan nombor 2 ^ND atau 3 ^RD terbesar dalam dataset, fungsi besar berguna.

• Excel fungsi besar
• Formula besar asas
• Menggunakan formula besar dalam Excel - contoh
  • Dapatkan Nilai N Top
  • Jumlah atau nilai N purata terbesar
  • Dapatkan perlawanan N-th terbesar
  • Susun nombor dari tertinggi hingga terendah
  • Formula besar untuk tarikh dan masa
  • Dapatkan tarikh masa depan yang paling dekat dengan hari ini atau tarikh yang ditentukan
• Fungsi besar tidak berfungsi

Excel fungsi besar

Fungsi besar dalam Excel digunakan untuk mengembalikan nilai N-th terbesar dari set data angka. Sebagai contoh, ia boleh mengira skor tertinggi, perintah ^{ke -2} terbesar, hasil ³ tempat, dan sebagainya.

Sintaks terdiri daripada dua hujah, yang kedua -duanya diperlukan:

Besar (array, k)

Di mana:

• Array - Julat atau array di mana untuk mencari nilai terbesar.
• K - Kedudukan dari yang tertinggi untuk kembali, iaitu nilai terbesar k -th dalam dataset.

Besar dikategorikan di bawah fungsi statistik. Ia boleh didapati dalam semua versi Excel untuk Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, dan lebih awal.

3 perkara yang perlu diketahui mengenai fungsi besar

Sebelum kita mendapat lebih banyak perkara praktikal dan mula membina formula kita sendiri, sila perhatikan 3 fakta mudah yang menjelaskan keperluan:

1. Fungsi besar memproses hanya nilai angka. Sel kosong , teks , dan nilai logik diabaikan.
2. Jika array mengandungi sebarang kesilapan , ralat dikembalikan.
3. Sekiranya terdapat nilai N, besar (array, 1) akan mengembalikan nilai maksimin, dan besar (array, n) akan mengembalikan nilai minimum.

Formula besar asas

Dalam bentuk asasnya, formula besar dalam Excel sangat mudah dibina. Untuk hujah pertama, anda membekalkan pelbagai nilai angka. Dalam hujah ^ke -2, anda menentukan kedudukan dari yang terbesar untuk kembali.

Dalam jadual sampel di bawah, anggap anda ingin mengetahui skor ^ke -2 terbesar. Ini boleh dilakukan dengan formula berikut:

=LARGE(B2:B10, 2)

Cara Menggunakan Formula Besar dalam Excel - Contoh

Dan sekarang, mari kita lihat kes penggunaan yang lebih spesifik dan lihat bagaimana fungsi besar dapat membantu.

Cara Mendapatkan Nilai N Top dalam Excel

Untuk mendapatkan nilai 3, 5, 10, dan lain -lain yang terbesar dengan formula tunggal, menjalankan langkah -langkah ini:

1. Taipkan kedudukan kepentingan dalam sel berasingan. Nombor ini akan digunakan sebagai nilai k .
2. Buat formula besar yang menggunakan rujukan pelbagai mutlak untuk array ($ B $ 2: $ B $ 10 dalam kes kami) dan rujukan sel relatif untuk K (D3).
3. Masukkan formula di sel paling atas, dan kemudian seret ke sel bawah. Selesai!

Sebagai contoh, kita akan mencari skor 3 teratas dalam jadual di bawah. Untuk ini, kami menaip nombor 1, 2 dan 3 dalam D3, D4 dan D5, dan masukkan formula berikut dalam E3:

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

Seretnya melalui E4, dan anda akan mendapat hasil ini:

Daripada menaip kedudukan pada helaian, anda boleh menggunakan fungsi baris dengan rujukan pelbagai yang berkembang untuk menghasilkan nilai K secara automatik seperti yang dijelaskan dalam formula Excel untuk mencari nilai N atas dalam senarai.

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

Petua. Anda juga boleh menggunakan fungsi besar untuk mengekstrak rekod N atas dengan penapis lanjutan.

Cara jumlah atau nilai n purata terbesar

Untuk jumlah nilai N atas dalam set data, anda boleh menggunakan besar bersama -sama dengan sama ada sumproduct atau jumlah dengan cara ini:

Sumproduct (besar ( array , {1, ..., n }))

Atau

Jumlah (besar ( array , {1, ..., n }))

Rata -rata nilai N tertinggi, menggabungkan fungsi purata dan besar:

Purata (besar ( array , {1, ..., n }))

Untuk melihat bagaimana ia berfungsi dalam amalan, mari kita cari purata skor 3 teratas dalam jadual sampel kami. Untuk ini, kami menggunakan formula ini:

=AVERAGE(LARGE(B2:B10, {1,2,3}))

Untuk menambah 3 skor tertinggi, formula adalah:

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

Nota. Sekiranya anda menggunakan julat dan bukannya pemalar array untuk k , anda perlu menekan CTRL Shift Enter untuk menjadikannya formula array. Dalam Excel 365 yang menyokong tatasusunan dinamik, semua formula boleh diselesaikan seperti biasa dengan menekan kekunci Enter .

Bagaimana formula ini berfungsi:

Biasanya, fungsi besar mengembalikan nilai tunggal berdasarkan nombor k . Dalam formula ini, kami membekalkan array pemalar seperti {1,2,3} untuk argumen K memaksa ia untuk mengembalikan pelbagai nilai. Arahan itu pergi ke fungsi luar untuk disimpulkan atau disusun secara purata.

Dapatkan data yang berkaitan dengan nilai terbesar N-th

Untuk mendapatkan maklumat yang berkaitan dengan nilai terbesar, hanya sarang fungsi besar dalam formula perlawanan indeks kanonik:

Indeks ( return_array , sepadan (besar ( lookup_array , n ), lookup_array , 0))

Di mana:

• Return_array adalah julat dari mana untuk mengekstrak perlawanan.
• Lookup_Array adalah pelbagai nombor untuk berpangkat dari tertinggi.
• N ialah kedudukan nilai terbesar untuk dicari.

Sebagai contoh, untuk mendapatkan nama seorang pelajar yang melakukan yang terbaik dalam peperiksaan, formula ini dalam F3:

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

Di mana A2: A10 adalah array kembali (nama), B2: B10 adalah array carian (skor) dan D3 adalah kedudukan dari yang terbesar.

Untuk mengetahui siapa yang mempunyai skor 2 ^ND dan 3 ^RD tertinggi, salin forma ke F4 dan F5:

Nota:

• Penyelesaian ini berfungsi dengan baik untuk nilai yang unik. Jika dataset anda mengandungi nombor pendua, "ikatan" dalam ranking mungkin berlaku, yang akan menghasilkan hasil yang salah. Untuk mengelakkan ini berlaku, gunakan formula yang lebih kompleks untuk mengendalikan ikatan.
• Dalam Excel 365, anda boleh menggunakan fungsi array dinamik baru untuk menyelesaikan penyelesaian yang lebih mudah yang menyelesaikan masalah hubungan secara automatik. Untuk butiran lengkap, sila lihat cara menapis nilai N atas dalam Excel.

Formula besar untuk menyusun nombor turun

Untuk cepat menyusun senarai nombor dalam Excel 365, anda boleh menggunakan fungsi jenis baru. Dalam versi Excel 2019, 2016 dan terdahulu yang tidak menyokong array dinamik, kita harus bergantung pada fungsi besar lama yang baik untuk menyusun turun dan kecil untuk menyusun menaik.

Untuk contoh ini, kami akan menyusun nombor dalam A2: A10 dari tertinggi hingga terendah. Untuk melakukannya, kita akan memerlukan baris berfungsi dengan rujukan pelbagai yang berkembang untuk meningkatkan argumen K sebanyak 1 dengan setiap baris:

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

Formula di atas pergi ke sel paling atas (C2). Dan kemudian, anda menyeretnya melalui seberapa banyak sel kerana terdapat nombor dalam senarai asal (C2: C10 dalam kes kami):

Formula besar untuk tarikh dan masa

Seperti yang anda ketahui, tarikh dan masa dalam Excel adalah nilai angka: tarikh disimpan sebagai bilangan bulat dan masa sebagai perpuluhan. Apa maksudnya bagi kita? Fungsi besar mengira nilai tarikh dan masa dengan cara yang sama seperti nombor. Dalam erti kata lain, formula yang anda gunakan untuk nombor akan berfungsi untuk tarikh dan masa juga!

Dengan mengandaikan anda mempunyai senarai tarikh dalam B2: B10, formula berikut akan mengembalikan tarikh N yang paling terkini , bergantung kepada berapa banyak sel yang anda salin ke:

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

Formula yang sama juga boleh mencari 3 kali terpanjang :

Dapatkan tarikh masa depan yang paling dekat dengan hari ini atau tarikh yang ditentukan

Contoh ini menunjukkan penggunaan yang lebih spesifik dari fungsi besar Excel dengan tarikh.

Dari senarai tarikh dalam B2: B10, katakan anda ingin mengembalikan tarikh masa depan yang paling dekat dengan hari ini . Untuk melaksanakan tugas, kami akan menggunakan Countif dan hari ini berfungsi bersama -sama untuk mengira nilai untuk hujah K yang besar:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY()))

Untuk mencari yang seterusnya tetapi satu tarikh, formula adalah:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY())-1)

Untuk mencari tarikh yang datang tepat selepas tarikh tertentu , masukkan tarikh sasaran dalam beberapa sel (E3 dalam contoh ini), dan menggabungkan bahawa rujukan sel dalam kriteria Countif:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1))

Dalam keadaan apabila tarikh yang sepadan dengan kriteria anda tidak dijumpai, anda boleh menggunakan fungsi IFError sebagai "pembungkus" untuk menangkap kesilapan dan menggantikannya dengan apa sahaja teks yang anda lihat sesuai:

=IFERROR(LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1)), "Not found")

Bagaimana formula ini berfungsi:

Pada dasarnya, anda menggunakan fungsi COUNTIF untuk mengira berapa banyak tarikh dalam senarai lebih besar daripada hari ini atau tarikh yang ditentukan. Kiraan itu adalah nilai k yang memerlukan fungsi besar.

Pada masa penulisan, tarikh hari ini adalah 7 Oktober, 2020. Fungsi Countif dengan ">" & hari ini () untuk kriteria yang ditentukan bahawa dalam B2: B10 terdapat 4 tarikh lebih besar daripada 7-Okt-2020. Maksudnya, tarikh ke -4 terbesar dalam senarai adalah tarikh masa depan yang paling dekat yang kita cari. Oleh itu, kami memasangkan Countif ke dalam hujah ² yang besar dan mendapatkan hasil yang diinginkan:

=LARGE($B$2:$B$10, 4)

Seterusnya tetapi satu tarikh adalah tarikh 3 ^rd terbesar dalam kes kami. Untuk mendapatkannya, kami tolak 1 dari hasil COUNTIF.

Excel Fungsi Besar Tidak Berfungsi

Formula besar boleh membuang #num! ralat kerana alasan berikut:

• Arahan yang dibekalkan kosong atau tidak mengandungi nilai angka tunggal.
• Nilai K adalah nombor negatif.
• Nilai k lebih besar daripada bilangan nilai dalam array .

Itulah cara menggunakan fungsi besar dalam Excel untuk mencari nilai tertinggi dalam dataset. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Formula Besar Excel (fail .xlsx)

Atas ialah kandungan terperinci Excel fungsi besar untuk mendapatkan nilai tertinggi N-th. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!