Formula Excel untuk mencari nilai teratas 3, 5, 10 dalam lajur atau baris

Tutorial ini menunjukkan bagaimana untuk mencari nilai N atas dalam dataset dan mengambil data yang berkaitan dengan menggunakan formula Excel. Sama ada anda memerlukan kriteria tertentu, terendah, atau mereka yang memenuhi kriteria tertentu, panduan ini menyediakan penyelesaian.

• Mencari nilai atas/bawah
• Mencari nilai teratas dalam baris
• Mendapatkan perlawanan untuk nilai terbesar/terkecil
• Mengendalikan pendua dalam carian nilai teratas
• Menggabungkan kriteria untuk pemilihan nilai teratas
• Menggunakan fungsi penapis untuk nilai N atas

Formula Excel untuk nilai Top 3, 5, 10, atau N

Untuk mengenal pasti nilai N tertinggi, menggabungkan fungsi LARGE dan ROWS :

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

Sebagai contoh, untuk mencari nilai N atas dalam sel B2: B12, masukkan formula ini dalam sel output yang dikehendaki (misalnya, D2), kemudian seret ke bawah untuk menampung bilangan sel yang diperlukan (3 untuk 3, 5 untuk 5, dan sebagainya).

Fungsi LARGE mengenal pasti nilai terbesar K-th, manakala ROWS(A$2:A2) secara dinamik menghasilkan nilai 'k' (1, 2, 3 ...). Rujukan mutlak A$2 menyimpan pemalar baris permulaan, manakala peningkatan relatif A2 kenaikan dengan setiap salinan sel.

Formula excel untuk nilai n bawah

Untuk mencari nilai N terendah, gunakan fungsi SMALL dan bukannya LARGE :

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

Mencari nilai N atas berturut -turut

Untuk data yang diatur secara mendatar, ganti ROWS dengan COLUMNS :

=LARGE(values, COLUMNS(A$2:A2)) (untuk nilai atas) =SMALL(values, COLUMNS(A$2:A2)) (untuk nilai bawah)

Mendapatkan data yang berkaitan dengan nilai N atas

Gunakan INDEX dan MATCH dengan LARGE (atau SMALL ) untuk mendapatkan data yang sepadan dengan nilai N atas:

=INDEX(return_array, MATCH(LARGE(lookup_array, k), lookup_array, 0))

Sebagai alternatif, pengguna Excel 365 dapat memudahkannya dengan XLOOKUP :

=XLOOKUP(LARGE(lookup_array, k), lookup_array, return_array)

Mengendalikan nilai tertinggi pendua

Untuk dataset dengan pendua, fungsi MATCH yang lebih mantap diperlukan (formula array, shift CTRL masukkan dalam versi pra-365):

=INDEX(return_array, MATCH(1, (lookup_array=LARGE(lookup_array, k))*(COUNTIF(output_range, return_array)=0), 0))

Excel 365 menawarkan penyelesaian yang lebih mudah menggunakan FILTER .

Mencari nilai tertinggi dengan kriteria

Gunakan IF pernyataan dalam formula LARGE dan MATCH untuk menggabungkan kriteria:

=LARGE(IF(criteria, values), k)

Sekali lagi, Excel 365 memudahkan ini dengan FILTER .

Menggunakan fungsi penapis (Excel 365)

Fungsi FILTER dan SORT Excel 365 memberikan pendekatan yang diselaraskan:

=SORT(FILTER(data, numbers>=LARGE(numbers, n)), sort_index, -1) (atas n) =SORT(FILTER(data, numbers (bawah n)

Tutorial ini menyediakan kaedah yang komprehensif untuk mengekstrak nilai N atas dalam Excel, memenuhi pelbagai senario dan versi Excel. Buku kerja amalan tersedia untuk dimuat turun.

Atas ialah kandungan terperinci Formula Excel untuk mencari nilai teratas 3, 5, 10 dalam lajur atau baris. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!