Rumus Excel: Memfilter nilai dalam rumus array -

Daftar Isi

Formula umum

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

Ringkasan

Untuk memfilter data dalam rumus array (untuk mengecualikan atau memerlukan nilai tertentu), Anda bisa menggunakan rumus array berdasarkan fungsi IF, MATCH, dan ISNUMBER.

Pada contoh yang ditampilkan, rumus di H5:

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

di mana "data" adalah rentang bernama B4: D11 dan "filter" adalah rentang bernama F4: F6.

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

Penjelasan

  1. Fungsi MATCH memeriksa semua nilai dalam rentang bernama "data" terhadap nilai dalam "filter"
  2. ISNUMBER mengonversi nilai yang cocok menjadi TRUE dan nilai yang tidak cocok menjadi FALSE
  3. IF menggunakan keluaran larik pada # 2 di atas untuk memfilter nilai, tidak termasuk nilai dalam "filter"

Array terakhir terlihat seperti ini:

(1, FALSE, 3; FALSE, 4, FALSE; FALSE, FALSE, FALSE; 1, FALSE, 3; FALSE, FALSE, FALSE; 3, FALSE, FALSE; FALSE, 4, FALSE; FALSE, FALSE, 4)

JUMLAH hanya digunakan untuk memverifikasi hasil.

Filter untuk dikecualikan

Di sel H6 rumus telah dimodifikasi untuk mengecualikan nilai menggunakan 1-ISNUMBER seperti:

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

Ini secara efektif membalikkan keluaran larik pada langkah # 2 di atas.

Artikel yang menarik...