Formula umum
=FILTER(data,(header="a")+(header="b"))
Ringkasan
Untuk memfilter kolom, berikan larik horizontal untuk argumen penyertaan. Dalam contoh yang ditunjukkan, rumus di I5 adalah:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Hasilnya adalah kumpulan data yang difilter yang hanya berisi kolom A, C, dan E dari data sumber.
Penjelasan
Meskipun FILTER lebih umum digunakan untuk memfilter baris, Anda juga dapat memfilter kolom, triknya adalah menyediakan larik dengan jumlah kolom yang sama dengan data sumber. Dalam contoh ini, kita membangun larik yang kita butuhkan dengan logika boolean, juga disebut aljabar Boolean.
Dalam aljabar Boolean, perkalian sesuai dengan logika AND, dan penambahan sesuai dengan logika OR. Dalam contoh yang ditunjukkan, kami menggunakan aljabar Boolean dengan logika OR (tambahan) untuk menargetkan hanya kolom A, C, dan E seperti ini:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Setelah setiap ekspresi dievaluasi, kami memiliki tiga array nilai TRUE / FALSE:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Operasi matematika (penjumlahan) mengonversi nilai TRUE dan FALSE menjadi 1 dan 0, sehingga Anda dapat memikirkan operasi seperti ini:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Pada akhirnya, kami memiliki satu larik horizontal 1 dan 0:
(1,0,1,0,1,0)
yang dikirim langsung ke fungsi FILTER sebagai argumen penyertaan:
=FILTER(B5:G12,(1,0,1,0,1,0))
Perhatikan bahwa ada 6 kolom dalam data sumber dan 6 nilai dalam larik, semuanya 1 atau 0. FILTER menggunakan larik ini sebagai filter untuk hanya menyertakan kolom 1, 3, dan 5 dari data sumber. Kolom 2, 4, dan 6 dihilangkan. Dengan kata lain, satu-satunya kolom yang bertahan dikaitkan dengan 1s.
Dengan fungsi MATCH
Menerapkan logika OR dengan penambahan seperti yang diperlihatkan di atas berfungsi dengan baik, tetapi tidak berskala dengan baik, dan tidak memungkinkan untuk menggunakan rentang nilai dari lembar kerja sebagai kriteria. Sebagai alternatif, Anda bisa menggunakan fungsi MATCH bersama dengan fungsi ISNUMBER seperti ini untuk membuat argumen penyertaan dengan lebih efisien:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Fungsi MATCH dikonfigurasi untuk mencari semua tajuk kolom dalam konstanta larik ("a", "c", "e") seperti yang ditunjukkan. Kami melakukannya dengan cara ini agar hasil dari MATCH memiliki dimensi yang sesuai dengan data sumber, yang berisi 6 kolom. Perhatikan juga bahwa argumen ketiga dalam MATCH ditetapkan sebagai nol untuk memaksa pencocokan tepat.
Setelah MATCH berjalan, ia mengembalikan array seperti ini:
(1,#N/A,2,#N/A,3,#N/A)
Larik ini langsung masuk ke ISNUMBER, yang mengembalikan larik lain:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Seperti di atas, larik ini berbentuk horizontal dan berisi 6 nilai yang dipisahkan dengan koma. FILTER menggunakan larik untuk menghapus kolom 2, 4, dan 6.
Dengan jangkauan
Karena tajuk kolom sudah ada di lembar kerja dalam kisaran I4: K4, rumus di atas dapat dengan mudah disesuaikan untuk menggunakan kisaran secara langsung seperti ini:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Rentang I4: K4 dievaluasi sebagai ("a", "c", "e"), dan berperilaku seperti konstanta larik dalam rumus di atas.
