Google sheets menyaring formula khusus

Dalam posting ini, saya ingin membagikan beberapa opsi filter lanjutan, seperti bekerja dengan tanggal dan menggunakan logika OR

Jika Anda telah membaca artikel saya untuk memulai tentang fungsi Filter di Google Sheets, Anda akan tahu bahwa ini adalah fungsi yang sangat hebat saat bekerja dengan data di Google Sheets. Dalam posting ini, kami akan mengambil satu langkah lebih jauh dan melihat logika yang lebih maju dengan kondisi ATAU

Klik di sini untuk membuat salinan lembar kerja filter lanjutan

Contoh Filter Lanjutan di Google Sheets

Contoh 1. Menggunakan sel Referensi dan Agregasi

Berikut adalah contoh penggunaan fungsi FILTER untuk menggabungkan (menghitung dan menjumlahkan) berapa banyak nilai yang berada di antara dua tanggal. Saya menggunakan data fiktif di lembar template dengan tanggal dan nilai terkait untuk hari itu

Dalam skenario ini, kami ingin menghitung berapa banyak nilai yang muncul pada bulan Desember 2017 lalu menjumlahkan nilainya, menggunakan fungsi FILTER

Untuk menghitung nilainya, pertama-tama masukkan 12/1/17 dan 12/31/17 ke dalam dua sel yang berdekatan lalu coba rumus ini di sel berikutnya, sebagai berikut

=COUNT( FILTER( $A$2:$A$95, $A$2:$A$95 <= $E$2, $A$2:$A$95 >= $D$2 ))

Contoh 2. Gunakan fungsi bersarang di dalam fungsi Filter

Kita dapat membuat rumus di atas lebih ringkas dengan menyarangkan fungsi MONTH di dalam fungsi Filter

=COUNT(FILTER($A$2:$A$95, MONTH($A$2:$A$95)=12))

dan itu akan memberi Anda jawaban yang sama dari 31

Bagaimana cara menjumlahkan sel yang difilter di Google Sheets?

Gunakan rumus ini

=SUM(FILTER($B$2:$B$95, MONTH($A$2:$A$95)=12))

Kami telah melakukan dua hal

i) mengubah rentang filter ke kolom B (tetapi bukan kondisi pengujian. ) untuk mendapatkan nilai, dan

ii) membungkus fungsi filter dalam rumus SUM, bukan hitungan

Hasilnya sekarang

Bagaimana saya bisa menggunakan logika ATAU di dalam filter lanjutan?

Jika Anda mengetahui trik untuk filter lanjutan ini, ini cukup mudah dilakukan, tetapi jika tidak, yah…

…maka mudah tersesat di fungsi OR bersarang di dalam fungsi FILTER dan akhirnya terdampar di negeri kesalahan

Dalam contoh ini saya memiliki 10 baris data di kolom A dan B, sebagai berikut, dan saya ingin mengambil semua nilai "alpha" dan "gamma"

Daripada menjalankan dua rumus filter terpisah, lalu menggabungkan hasilnya, kita dapat menggunakan satu rumus yang memberikan hasil yang diinginkan

=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))

Kami menggunakan "+" alih-alih koma untuk menangani logika ATAU

Bagaimana cara kerja formula filter lanjutan ini?

Pertimbangkan fungsi FILTER dasar seperti ini, misalnya, yang hanya mengembalikan data dengan "alfa" di kolom A

=FILTER(A1:B10,A1:A10="alpha")

Sekarang, mari kita lihat cara kerja filter lanjutan

Langkah selanjutnya adalah mengembalikan semua data yang cocok dengan “alpha” atau cocok dengan “gamma”

Kami melakukan ini dengan menggunakan "+" sebagai operator boolean ATAU di dalam argumen kedua dari fungsi FILTER, sebagai berikut

=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))

Sekarang, setiap kali salah satu kondisi filter terpenuhi, dengan kata lain ada "alfa" atau "gamma" di kolom A, maka baris data tersebut dikembalikan dalam kumpulan data hasil filter kami

Sumber daya lainnya

Diskusi di Stack Exchange tentang penggunaan kondisi AND dan OR dalam fungsi FILTER

Lihat posting saya untuk memulai tentang cara menggunakan fungsi Filter Google Sheets untuk bekerja dengan data

Sudahkah Anda menemukan penggunaan rumus lanjutan FILTER + OR ini?

Fungsi FILTER, seperti namanya, akan memungkinkan Anda memfilter kumpulan data berdasarkan suatu kondisi (atau beberapa kondisi)

Misalnya, jika Anda memiliki daftar nama dengan nama negara bagian dan nilai penjualannya, Anda dapat menggunakan fungsi FILTER di Google Sheets untuk mendapatkan semua catatan/nama satu negara bagian tertentu dengan cepat (seperti yang ditunjukkan di bawah)

Salah satu manfaat menggunakan fungsi FILTER dibandingkan fitur filter biasa di Google Sheets adalah hasil fungsi FILTER bersifat dinamis. Jika Anda mengubah apa pun di data asli, data yang difilter yang dihasilkan akan diperbarui secara otomatis

Ini menjadikan fungsi FILTER Google Sheets adalah pilihan tepat saat membuat laporan atau dasbor interaktif

Dalam tutorial ini, saya akan menunjukkan kepada Anda bagaimana fungsi FILTER bekerja di Google Sheets dan juga membahas beberapa contoh berguna untuk menggunakannya dalam pekerjaan sehari-hari.

Jadi mari kita mulai dengan mempelajari sintaks dari fungsi ini

Sintaks Fungsi Filter Google Sheets

rumus filter Google Sheet adalah sebagai berikut .

FILTER(rentang, kondisi1, [kondisi2, …])

• jangkauan. Ini adalah rentang sel yang ingin Anda filter
• kondisi1. Ini adalah kolom/baris (sesuai dengan kolom/baris kumpulan data), yang mengembalikan larik BENAR/SALAH. Ini harus berukuran sama dengan kisaran
• [kondisi2]. Ini adalah argumen opsional dan bisa menjadi kondisi kedua yang Anda periksa dalam rumus. Sekali lagi ini bisa berupa kolom/baris (sesuai dengan kolom/baris dataset), yang mengembalikan array TRUEs/FALSES. Ini harus berukuran sama dengan kisaran

Saat Anda menggunakan beberapa ketentuan, hasil yang mengembalikan benar untuk kedua ketentuan tersebut akan difilter

Jika fungsi FILTER tidak dapat menemukan hasil yang sesuai dengan kondisi, fungsi ini akan mengembalikan kesalahan #N/A

Jika Anda bertanya-tanya bagaimana cara kerjanya, lihat beberapa contoh (tercantum di bawah) dan akan menjadi jelas tentang cara menggunakan fungsi FILTER di Google Sheets

Contoh 1 –  Filter Fungsi Google Spreadsheet Berdasarkan Satu Kondisi

Misalkan Anda memiliki kumpulan data seperti yang ditunjukkan di bawah ini dan Anda ingin dengan cepat memfilter semua rekaman dengan nama negara bagian Florida

Formula di bawah ini akan melakukannya

=FILTER(A2:C11,B2:B11="Florida")

Rumus di atas menggunakan rentang data sebagai argumen dan kondisinya adalah B2. B11=“Florida”. Kondisi ini memeriksa setiap sel dalam rentang B2. B11 dan jika nilainya sama dengan Florida, catatan itu difilter, jika tidak.

Dalam contoh ini, saya telah mengkodekan nilainya, tetapi Anda juga dapat memiliki nilai ini dalam sel dan kemudian merujuk ke sel ini. Misalnya, jika Anda memiliki teks Florida di sel H1, Anda juga dapat menggunakan rumus di bawah ini

=FILTER(A2:C11,B2:B11=H1)

Beberapa hal yang perlu diketahui tentang fungsi FILTER

Fungsi FILTER di Google Sheets mengembalikan larik nilai yang tumpah ke sel yang berdekatan (ini disebut larik dinamis). Agar ini berfungsi, Anda perlu memastikan bahwa sel yang berdekatan (tempat hasil akan ditempatkan) harus kosong

Jika salah satu sel tidak kosong, rumus Anda akan mengembalikan #REF. kesalahan. Google Sheets juga memberi tahu Anda mengapa itu memberikan kesalahan dengan menunjukkan segitiga merah di kanan atas sel dan saat Anda mengarahkan kursor ke atasnya, itu akan menampilkan pesan

Hasil array tidak diperluas karena akan menimpa data di F3

Dan segera setelah Anda menghapus sel yang terisi yang mencegah fungsi FILTER memberikan hasil, itu akan secara otomatis mengisi rentang dengan hasilnya

Selain itu, hasil dari rumus FILTER adalah sebuah larik dan Anda dapat mengubah bagian dari larik tersebut. Ini berarti Anda tidak dapat mengubah atau menghapus satu sel (atau beberapa sel) dalam hasil. Anda harus menghapus seluruh hasil rumus. Untuk menghapus hasilnya, Anda dapat memilih sel tempat Anda memasukkan rumus lalu menekan tombol hapus

Contoh 2 – Filter Google Sheets Berdasarkan Berbagai Ketentuan (Dan Ketentuan)

Anda juga dapat menggunakan fungsi FILTER untuk memeriksa beberapa kondisi sedemikian rupa sehingga hanya mengembalikan rekaman yang memenuhi kedua kondisi tersebut.

Misalnya, Anda memiliki kumpulan data di bawah dan Anda ingin memfilter semua rekaman dengan negara bagian Florida dan nilai penjualan lebih dari 5000

Anda dapat melakukannya dengan menggunakan rumus di bawah ini;

=FILTER(A2:C11,B2:B11="Florida",C2:C11>5000)

Rumus di atas memeriksa dua kondisi (di mana negara bagian Florida dan nilai penjualan lebih dari 5000) dan mengembalikan semua rekaman yang memenuhi kriteria ini

Demikian pula, jika Anda mau, Anda dapat memiliki beberapa ketentuan dalam rumus FILTER yang sama

Contoh 3 – Memfilter Catatan Berdasarkan Beberapa Kondisi (Kondisi ATAU)

Dalam contoh di atas, saya telah memeriksa dua kondisi dan mengembalikan hasil di mana kedua kondisi tersebut BENAR

Anda juga dapat memeriksa kondisi ATAU dalam rumus FILTER

Misalnya, Anda memiliki kumpulan data seperti yang ditunjukkan di bawah ini dan Anda ingin mendapatkan semua catatan untuk California dan Iowa. Ini berarti bahwa kondisinya harus negara bagian adalah California atau Iowa (yang menjadikan ini sebagai kondisi ATAU)

Formula di bawah ini akan melakukannya

=FILTER(A2:C11,(B2:B11="California")+ (B2:B11="Iowa"))

Rumus di atas menggunakan operator penjumlahan dalam kondisi untuk terlebih dahulu memeriksa kedua kondisi dan kemudian menjumlahkan hasilnya masing-masing. Karena kondisi ini mengembalikan array atau TRUE dan FALSE, Anda dapat menambahkan ini (karena TRUE adalah 1 dan FALSE adalah 0 di Google Sheets)

Ini akan memberi Anda 0 (atau FALSE) di mana kedua kondisi tidak terpenuhi, 1 di mana salah satu dari dua kondisi terpenuhi, dan 2 di mana kedua kondisi terpenuhi

Dan kemudian rumus FILTER akan mengembalikan semua record yang kondisinya mengembalikan nilai lebih dari 0

Contoh 4 – Cara Memfilter Di Google Spreadsheet untuk Top 3 atau Top 5 Records

Anda juga dapat menggunakan fungsi FILTER untuk mendapatkan 3 teratas atau 5 teratas dengan cepat (atau berapa pun jumlah catatan atas/bawah yang Anda pilih)

Misalnya, saya memiliki kumpulan data seperti yang ditunjukkan di bawah ini dan saya ingin segera mendapatkan catatan untuk 3 nilai penjualan teratas

Formula di bawah ini akan melakukannya

=FILTER(A2:C11,C2:C11>=LARGE(C2:C11,3))

Rumus di atas menggunakan fungsi LARGE untuk mendapatkan nilai terbesar ketiga dalam dataset. Nilai ini kemudian digunakan dalam kondisi untuk memeriksa apakah nilai di kolom C lebih besar atau sama dengan nilai ini atau tidak

Ini akan mengembalikan semua rekaman yang cocok dengan kriteria, yang akan menjadi tiga rekaman teratas

Jika Anda ingin mendapatkan tiga catatan terbawah, Anda dapat menggunakan rumus FILTER di bawah ini

=FILTER(A2:C11,C2:C11<=Small(C2:C11,3))

Contoh 5 – Cara Menggunakan Fungsi Filter di Google Sheets untuk MENGURUSKAN Data yang Difilter

Sejauh ini, semua contoh fungsi pemfilteran yang telah kita lihat akan memfilter data dalam urutan yang sama dengan kemunculannya di kumpulan data

Tetapi bagaimana jika Anda ingin mendapatkan dataset yang diurutkan

Misalnya, Anda memfilter 5 catatan teratas, akan lebih berguna untuk mengurutkannya dalam urutan menurun (terbesar di atas)

Di bawah ini adalah rumus yang akan memfilter data dan menampilkannya dalam urutan menurun

=SORT(FILTER(A2:C11,C2:C11>=LARGE(C2:C11,3)),3,FALSE)

Rumus di atas menggunakan rumus FILTER yang sama dengan yang kita gunakan pada contoh sebelumnya untuk mengambil tiga rekaman teratas berdasarkan nilai penjualan

Dan karena saya ingin hasil akhir diurutkan dalam urutan menurun, saya telah menggabungkan fungsi FILTER ke dalam fungsi SORT. Fungsi SORT menggunakan hasil yang diberikan oleh rumus FILTER dan mengurutkannya berdasarkan kolom ketiga (yang memiliki nilai penjualan)

Argumen ketiga dalam fungsi SORT adalah FALSE, yaitu untuk menentukan bahwa saya menginginkan hasil akhir dalam urutan menurun. Jika Anda membiarkannya kosong (atau membuatnya BENAR), hasilnya akan dalam urutan menaik

Contoh 6 – Filter Spreadsheet Semua Catatan Angka Genap (Atau Catatan Angka Ganjil)

Ini bukan kasus penggunaan umum, tetapi ini adalah sesuatu yang harus saya lakukan saat mendapatkan data dari orang lain atau dari database atau halaman web

Sering kali, data yang Anda butuhkan hanya akan berada di baris alternatif (atau setiap baris ketiga/keempat/kelima), dan Anda harus membuang baris tambahan sehingga Anda bisa mendapatkan semua data yang berguna bersama-sama.

Dalam kasus seperti itu, Anda dapat menggunakan fungsi FILTER untuk memfilter dengan cepat dan menggabungkan semua baris bernomor genap (atau semua baris bernomor ganjil). Dan Anda juga dapat mengubah rumus untuk memfilter setiap baris ketiga, keempat, atau ke-n di Google Sheets

Misalkan Anda memiliki kumpulan data seperti yang ditunjukkan di bawah ini dan Anda ingin memfilter semua baris bernomor genap dalam kumpulan data ini

Di bawah ini adalah rumus yang akan memfilter semua baris genap

=FILTER(A2:C11,MOD(ROW(A2:A11)-1,2)=0)

Rumus di atas menggunakan fungsi ROW untuk mendapatkan nomor baris dari semua baris dalam dataset. Kemudian kurangi 1 darinya saat kumpulan data kami dimulai dari baris kedua dan seterusnya

Sekarang, ia menggunakan fungsi MOD untuk memeriksa kondisi berikut – MOD(ROW(A2:A11)–1,2)=0)

Ini akan mengembalikan TRUE untuk semua baris bernomor genap dan FALSE untuk semua baris bernomor ganjil. Dan larik TRUE dan FALSE ini digunakan oleh fungsi FILTER untuk mengekstrak record

Demikian pula, jika Anda hanya menginginkan catatan bernomor ODD, Anda dapat menggunakan rumus di bawah ini

=FILTER(A2:C11,MOD(ROW(A2:A11)-1,2)=1)

Dan jika Anda ingin memfilter setiap baris ketiga, Anda dapat menggunakan rumus di bawah ini di Google Sheets

=FILTER(A2:C11,MOD(row(A2:A11)-1,3)=0)

FAQ Fungsi Filter Google Sheets

Bagaimana Saya Memfilter 10 Nilai Teratas di Google Sheets?

Lihat contoh 4 dalam panduan kami di atas, ini akan menunjukkan kepada Anda cara memfilter menurut nomor teratas tidak peduli berapa banyak yang Anda putuskan untuk dimasukkan

Apa Fungsi FILTER?

Fungsi FILTER memudahkan untuk memfilter data yang tidak penting dari spreadsheet Anda sehingga Anda hanya melihat apa yang diperlukan untuk fungsi atau penghitungan lainnya

Bisakah Saya Melakukan Filter dari Filter?

Secara teknis ya, tetapi Anda akan lebih baik menggunakan fungsi VLOOKUP. Ini cara yang jauh lebih efisien untuk melakukan hal yang sama

Bisakah Saya Mereferensikan Sel Kriteria Dengan Fungsi Filter di Google Sheets?

Ya, Anda hanya perlu menggunakan referensi sel di bagian condition sintaks fungsi FILTER

Jadi ini adalah beberapa contoh di mana fungsi FILTER Google Sheets dapat menjadi penghemat waktu nyata. Dan saat Anda menggabungkannya dengan formula lain, Anda bisa menyelesaikan banyak hal dengannya

Harap Anda menemukan tutorial ini berguna

Tutorial Google Spreadsheet lain yang mungkin Anda sukai

1. Cara Memfilter Berdasarkan Warna di Google Sheets (Menggunakan Formula)
2. Cara Menomori Baris di Google Sheets (Tambahkan Nomor Seri)
3. Hitung Sel JIKA TIDAK Kosong (Sel Tidak Kosong) di Google Sheets
4. Cara Menghapus Duplikat Di Google Sheets (3 Cara Mudah)
5. Bagaimana cara VLOOKUP dari Lembar Lain di Google Sheets?
6. Cara Menggunakan Fungsi IMPORTRANGE di Google Sheets
7. Cara Menggunakan fungsi INDIRECT di Google Sheets

Postingan Terpopuler

5 Cara Sederhana untuk Menyoroti Duplikat di Google Sheets

JIKA BERISI Formula Google Sheets [2 Opsi Pintar]

Cara Menerapkan Formula ke Seluruh Kolom di Google Sheets

Cara VLOOKUP Dari Lembar Lain di Google Lembar

Puncak

Sumit adalah Pakar Google Spreadsheet dan Microsoft Excel. Dia memberikan pelatihan spreadsheet kepada perusahaan dan telah dianugerahi penghargaan bergengsi Excel MVP oleh Microsoft atas kontribusinya dalam berbagi pengetahuan Excel dan membantu orang.

Bagaimana Anda memfilter formula khusus?

Bisakah Anda membuat formula khusus di Google Sheets?

Bisakah Anda melakukan filter lanjutan di Google Sheets?

Bagaimana cara menggunakan formula khusus dalam validasi data di Google Sheets?