Rumus Excel: Menapis nilai dalam formula larik -

Formula generik

(=IF(ISNUMBER(MATCH(data,filter,0)),data))

Ringkasan

Untuk menyaring data dalam formula array (untuk mengecualikan atau memerlukan nilai tertentu), anda boleh menggunakan formula array berdasarkan fungsi IF, MATCH, dan ISNUMBER.

Dalam contoh yang ditunjukkan, formula dalam H5:

(=COUNT(IF(ISNUMBER(MATCH(data,filter,0)),data)))

di mana "data" adalah julat bernama B4: D11 dan "penapis" adalah julat bernama F4: F6.

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

Penjelasan

  1. Fungsi MATCH memeriksa semua nilai dalam julat bernama "data" terhadap nilai dalam "filter"
  2. ISNUMBER menukar nilai sepadan menjadi BENAR dan nilai tidak sepadan menjadi SALAH
  3. JIKA menggunakan output array di # 2 di atas untuk menyaring nilai, tidak termasuk nilai di "filter"

Susunan terakhir kelihatan seperti ini:

(1, SALAH, 3; SALAH, 4, SALAH; SALAH, SALAH, SALAH; 1, SALAH, 3; SALAH, SALAH, SALAH; 3, SALAH, SALAH; SALAH, 4, SALAH; SALAH, SALAH, 4)

COUNT hanya digunakan untuk mengesahkan hasilnya.

Tapis untuk dikecualikan

Dalam sel H6 formula telah diubah untuk mengecualikan nilai menggunakan 1-ISNUMBER seperti:

=COUNT(IF(1-ISNUMBER(MATCH(data,filter,0)),data))

Ini secara berkesan membalikkan output array pada langkah # 2 di atas.

Artikel menarik...