Formula generik
=FILTER(data,(header="a")+(header="b"))
Ringkasan
Untuk menapis lajur, sediakan array mendatar untuk argumen include. Dalam contoh yang ditunjukkan, formula dalam I5 adalah:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Hasilnya adalah sekumpulan data yang disaring yang hanya berisi lajur A, C, dan E dari data sumber.
Penjelasan
Walaupun FILTER lebih sering digunakan untuk menyaring baris, anda juga dapat menyaring lajur, caranya adalah dengan menyediakan array dengan jumlah lajur yang sama dengan data sumber. Dalam contoh ini, kita membina susunan yang kita perlukan dengan logik boolean, juga disebut algebra Boolean.
Dalam algebra Boolean, pendaraban sesuai dengan logik AND, dan penambahan sepadan dengan logik OR. Dalam contoh yang ditunjukkan, kami menggunakan algebra Boolean dengan logik OR (penambahan) untuk menyasarkan hanya lajur A, C, dan E seperti ini:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Setelah setiap ungkapan dinilai, kita mempunyai tiga susunan nilai BENAR / SALAH:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Operasi matematik (penambahan) menukar nilai BENAR dan SALAH menjadi 1s dan 0s, jadi anda boleh 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 mempunyai susunan mendatar tunggal 1s dan 0s:
(1,0,1,0,1,0)
yang dihantar terus ke fungsi FILTER sebagai argumen termasuk:
=FILTER(B5:G12,(1,0,1,0,1,0))
Perhatikan ada 6 lajur dalam data sumber dan 6 nilai dalam array, semuanya 1 atau 0. FILTER menggunakan array ini sebagai penapis untuk menyertakan hanya lajur 1, 3, dan 5 dari data sumber. Lajur 2, 4, dan 6 dikeluarkan. Dengan kata lain, satu-satunya lajur yang bertahan dikaitkan dengan 1s.
Dengan fungsi MATCH
Menerapkan logik ATAU dengan penambahan seperti yang ditunjukkan di atas berfungsi dengan baik, tetapi tidak sesuai dengan skala, dan menjadikan mustahil untuk menggunakan pelbagai nilai dari lembaran kerja sebagai kriteria. Sebagai alternatif, anda boleh menggunakan fungsi MATCH bersama dengan fungsi ISNUMBER seperti ini untuk membina argumen include dengan lebih berkesan:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Fungsi MATCH dikonfigurasi untuk mencari semua tajuk lajur dalam pemalar larik ("a", "c", "e") seperti yang ditunjukkan. Kami melakukannya dengan cara ini sehingga hasil dari MATCH mempunyai dimensi yang sesuai dengan data sumber, yang mengandungi 6 lajur. Perhatikan juga bahawa argumen ketiga dalam MATCH ditetapkan sebagai sifar untuk memaksa pertandingan yang tepat.
Selepas MATCH dijalankan, ia mengembalikan array seperti ini:
(1,#N/A,2,#N/A,3,#N/A)
Susunan ini terus masuk ke ISNUMBER, yang mengembalikan array lain:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Seperti di atas, susunan ini mendatar dan mengandungi 6 nilai yang dipisahkan dengan koma. FILTER menggunakan tatasusunan untuk membuang lajur 2, 4, dan 6.
Dengan julat
Oleh kerana tajuk lajur sudah ada di lembaran kerja dalam julat I4: K4, formula di atas dapat dengan mudah disesuaikan untuk menggunakan julat secara langsung seperti ini:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Julat I4: K4 dinilai sebagai ("a", "c", "e"), dan berkelakuan seperti pemalar larik dalam formula di atas.
