Formula Excel: Senaraikan nombor yang paling kerap berlaku -

Isi kandungan

Formula generik

(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))

Ringkasan

Untuk menyenaraikan nombor yang paling kerap berlaku dalam lajur (iaitu yang paling umum, kedua paling umum, ketiga paling umum, dan lain-lain), anda boleh membuat formula larik berdasarkan empat fungsi Excel: IF, MODE, MATCH, dan ISNUMBER. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:

(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))

di mana "data" adalah julat bernama B5: B16. Rumus kemudian disalin ke baris di bawah D5 untuk mengeluarkan senarai nombor yang paling kerap diinginkan.

Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.

Penjelasan

Inti formula ini adalah fungsi MODE, yang mengembalikan nombor yang paling kerap berlaku dalam julat atau array. Selebihnya formula hanya membina susunan yang disaring untuk MODE untuk digunakan pada setiap baris. Julat pengembangan $ D $ 4: D4 berfungsi untuk mengecualikan nombor yang sudah dikeluarkan dalam $ D $ 4: D4.

Bekerja dari dalam ke luar:

  1. Fungsi MATCH digunakan untuk memeriksa semua nombor dalam julat "data" yang dinamakan dengan nombor yang ada dalam julat yang berkembang $ D $ 4: D4
  2. ISNUMBER menukar nilai sepadan menjadi BENAR dan nilai tidak sepadan menjadi SALAH
  3. 1-NOMBOR membalikkan tatasusunan, dan operasi matematik menghasilkan satu dan sifar
  4. JIKA menggunakan output array # 3 di atas untuk menyaring senarai nilai asal, tidak termasuk nombor yang sudah ada dalam $ D $ 4: D4
  5. Fungsi MODE mengembalikan nombor yang paling kerap dalam output array pada langkah # 4

Dalam sel D5, penapisan tidak berlaku dan output setiap langkah di atas kelihatan seperti ini:

(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93

Di sel D6, dengan 93 sudah di D5, outputnya seperti ini:

(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95

Kesalahan pengendalian

Fungsi MODE akan mengembalikan ralat # N / A apabila tidak ada mod. Semasa anda menyalin formula ke baris berikutnya, kemungkinan anda akan mengalami ralat # N / A. Untuk memerangkap ralat ini dan mengembalikan rentetan kosong (""), anda boleh menggunakan IFERROR seperti ini:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")

Artikel menarik...