Rumus Excel: Cantumkan angka yang paling sering muncul -

Daftar Isi

Formula umum

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

Ringkasan

Untuk membuat daftar angka yang paling sering muncul dalam kolom (yaitu paling umum, kedua paling umum, ketiga paling umum, dll), Anda dapat membuat rumus array berdasarkan empat fungsi Excel: IF, MODE, MATCH, dan ISNUMBER. Dalam contoh yang ditampilkan, rumus di D5 adalah:

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

di mana "data" adalah rentang bernama B5: B16. Rumus tersebut kemudian disalin ke baris di bawah D5 untuk menampilkan daftar nomor yang paling sering diinginkan.

Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.

Penjelasan

Inti dari rumus ini adalah fungsi MODE, yang mengembalikan angka yang paling sering muncul dalam rentang atau larik. Rumus lainnya hanya menyusun larik yang difilter untuk MODE untuk digunakan di setiap baris. Rentang perluasan $ D $ 4: D4 berfungsi untuk mengecualikan angka yang sudah dikeluarkan dalam $ D $ 4: D4.

Bekerja dari dalam ke luar:

  1. Fungsi MATCH digunakan pertama, periksa semua angka dalam rentang bernama "data" terhadap angka yang ada dalam rentang yang diperluas $ D $ 4: D4
  2. ISNUMBER mengonversi nilai yang cocok menjadi TRUE dan nilai yang tidak cocok menjadi FALSE
  3. 1-ANGKA membalikkan larik, dan operasi matematika menghasilkan satu dan nol
  4. IF menggunakan output array # 3 di atas untuk memfilter daftar nilai asli, tidak termasuk angka yang sudah ada di $ D $ 4: D4
  5. Fungsi MODE mengembalikan angka yang paling sering dalam keluaran larik pada langkah # 4

Di sel D5, tidak ada pemfilteran yang terjadi dan keluaran dari setiap langkah di atas terlihat 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, hasilnya terlihat 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

Penanganan kesalahan

Fungsi MODE akan mengembalikan kesalahan # N / A bila tidak ada mode. Saat Anda menyalin rumus ke baris berikutnya, Anda kemungkinan akan mengalami kesalahan # N / A. Untuk menjebak kesalahan ini dan mengembalikan string kosong (""), Anda dapat menggunakan IFERROR seperti ini:

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

Artikel yang menarik...