Jika data yang ingin Anda filter memerlukan kriteria di beberapa bidang, seperti pemfilteran menurut beberapa kondisi yang semuanya harus benar, atau memperlihatkan baris yang cocok dengan salah satu dari beberapa kondisi berbeda (seperti Tipe = "Hasil Bumi" OR Tenaga Penjual = "Davolio"), Anda bisa menggunakan kotak dialog Filter Tingkat Lanjut .
Untuk membuka kotak dialog Filter Tingkat Lanjut, klikTingkat LanjutData>.
| Filter Tingkat Lanjut | Contoh |
|---|---|
| Gambaran umum kriteria filter tingkat lanjut | |
| Beberapa kriteria, satu kolom, kriteria apa pun benar | Tenaga Penjual = "Davolio" OR Tenaga Penjual = "Buchanan" |
| Beberapa kriteria, beberapa kolom, semua kriteria benar | Tipe = "Hasil Bumi" AND Penjualan > 1000 |
| Beberapa kriteria, beberapa kolom, kriteria apa pun benar | Tipe = "Hasil Bumi" OR Tenaga Penjual = "Buchanan" |
| Beberapa set kriteria, satu kolom dalam semua set | (Penjualan > 6000 AND Sales < 6500 ) OR (Sales < 500) |
| Beberapa kumpulan kriteria, beberapa kolom dalam setiap kriteria | (Tenaga Penjual = "Davolio" AND Sales >3000) OR (Tenaga Penjual = "Buchanan" AND Sales > 1500) |
| Kriteria wildcard | Tenaga Penjual = nama dengan 'u' sebagai huruf kedua |
Gambaran umum kriteria filter tingkat lanjut
Filter Tingkat Lanjut bekerja secara berbeda dari Filter dengan beberapa cara penting.
- Perintah tersebut menampilkan kotak dialog Filter Tingkat Lanjut dan bukan menu FilterOtomatis.
- Anda membuat rentang kriteria (sel terpisah di atas data Anda) tempat Anda memasukkan kondisi filter, lalu beri tahu kotak dialog Filter Tingkat Lanjut untuk menggunakan rentang tersebut.
- Filter Tingkat Lanjut TIDAK diperbarui otomatis saat Anda mengubah nilai kriteria
Catatan
Filter Tingkat Lanjut tetap tersedia untuk skenario pemfilteran kompleks, meskipun fitur yang lebih baru seperti Copilot di Excel kini dapat membantu pengguna dengan analisis data dan pemfilteran melalui kueri bahasa alami sebagai pendekatan alternatif untuk beberapa kasus penggunaan.
Memahami AND vs OR Logika
| Tipe Logika | Cara Menyiapkan | Contoh | Apa yang ditemukannya |
|---|---|---|---|
| LOGIKA AND (semua kriteria harus benar) | Meletakkan kriteria dalam baris yang sama | Tipe = "Hasil Bumi" di kolom 1 Penjualan > 1000 di kolom 2 (keduanya dalam baris yang sama) |
Hanya baris di mana Tipe IS "Hasil Bumi" AND Penjualan LEBIH besar dari 1000 |
| Logika OR (kriteria apa pun bisa benar) | Meletakkan kriteria di baris yang berbeda | Baris 1: Ketik = "Hasil Bumi" Baris 2: Ketik = "Daging" (baris yang berbeda, kolom yang sama) |
Baris di mana Tipe IS "Hasil bumi" OR Tipe IS "Daging" (atau keduanya) |
Contoh data
Contoh data berikut ini digunakan untuk semua prosedur dalam artikel ini.
Data menyertakan tiga baris kosong di atas rentang daftar yang akan digunakan sebagai rentang kriteria (A1:C4) dan rentang daftar (A6:C10). Rentang kriteria mempunyai label kolom dan meliputi setidaknya satu baris kosong antara nilai kriteria dan daftar rentang.
Untuk bekerja dengan data ini, pilih data dalam tabel berikut ini, salin, lalu tempelkan di sel A1 lembar kerja Excel baru.
| Tipe | Tenaga penjual | Penjualan |
|---|---|---|
| Tipe | Tenaga penjual | Penjualan |
| Minuman | Suyama | $5122 |
| Daging | Davolio | $450 |
| hasil bumi | Buchanan | $6328 |
| Hasil bumi | Davolio | $6544 |
Dalam contoh ini, lembar kerja yang dihasilkan akan terlihat seperti ini, di mana rentang kriteria filter diuraikan dalam warna biru dan rentang daftar (data yang ingin Anda filter) diuraikan dalam warna merah.
Operator perbandingan
Anda bisa membandingkan dua nilai dengan menggunakan operator berikut ini. Saat dua nilai dibandingkan dengan menggunakan operator ini, hasilnya adalah nilai logika—TRUE atau FALSE.
| Operator perbandingan | Arti | Contoh |
|---|---|---|
| = (tanda sama dengan) | Sama dengan | A1=B1 |
| > (tanda lebih besar dari) | Lebih besar dari | A1>B1 |
| < (tanda kurang dari) | Kurang dari | A1<B1 |
| >= (tanda lebih besar dari atau sama dengan) | Lebih besar dari atau sama dengan | A1>=B1 |
| <= (kurang dari atau sama dengan tanda) | Lebih kecil atau sama dengan | A1<=B1 |
| <> (tanda tidak sama dengan) | Tidak sama dengan | A1<>B1 |
Menggunakan tanda sama dengan untuk mengetik teks atau nilai
Karena tanda sama dengan (=) digunakan untuk menunjukkan rumus saat Anda mengetikkan teks atau nilai dalam sel, Excel mengevaluasi apa yang Anda ketikkan; namun, hal ini dapat menyebabkan hasil filter yang tidak diharapkan. Untuk menunjukkan operator perbandingan persamaan untuk teks atau nilai, ketik kriteria itu sebagai ekspresi string di sel yang sesuai dalam rentang kriteria:
=''=entry''
Di mana entri adalah teks atau nilai yang ingin Anda temukan. Misalnya:
| Apa yang Anda ketikkan dalam sel | Apa yang dievaluasi dan ditampilkan Excel |
|---|---|
| ="=Davolio" | =Davolio |
| ="=3000" | =3000 |
Mempertimbangkan kepekaan huruf besar-kecil
Saat Anda memfilter data teks, Excel tidak membedakan antara karakter huruf besar dan huruf kecil. Namun, Anda dapat menggunakan rumus untuk melakukan pencarian yang peka huruf besar kecil. Misalnya, lihat bagian Kriteria wildcard.
Menggunakan nama yang ditentukan sebelumnya
Anda dapat memberi nama rentang Kriteria, dan referensi untuk rentang tersebut akan muncul secara otomatis di kotak Rentang kriteria. Anda juga bisa menentukan nama Database untuk daftar rentang yang akan difilter dan menetapkan nama Ekstrak untuk area tempat Anda ingin menempelkan baris, dan rentang ini akan muncul secara otomatis di kotak Daftar rentang dan Salin ke.
Membuat kriteria dengan menggunakan rumus
Anda dapat menggunakan nilai terhitung yang merupakan hasil rumus sebagai kriteria Anda. Ingatlah poin-poin penting berikut:
- Rumus harus mengevaluasi ke TRUE atau FALSE.
- Karena Anda menggunakan rumus, masukkan rumusnya seperti biasa, dan jangan ketikkan ekspresi dengan cara berikut:
=''=entry'' - Jangan gunakan label kolom untuk label kriteria; pertahankan label kriteria tetap kosong atau gunakan label yang bukan label kolom dalam rentang daftar (dalam contoh di bawah ini, Rata-rata Terhitung dan Sama Persis).
Jika Anda menggunakan label kolom dalam rumus dan bukan referensi sel relatif atau nama rentang, Excel menampilkan nilai kesalahan seperti #NAME? atau #VALUE! dalam sel yang berisi kriteria. Anda bisa mengabaikan kesalahan ini karena tidak mempengaruhi bagaimana rentang daftar difilter. - Rumus yang Anda gunakan untuk kriteria harus menggunakan referensi relatif agar merujuk ke sel terkait dalam baris pertama data.
- Seluruh referensi lain dalam rumus tersebut harus referensi mutlak.
Beberapa kriteria, satu kolom, kriteria apa pun benar
Logika Boolean: (Tenaga Penjual = "Davolio" OR Tenaga Penjual = "Buchanan")
Gunakan ini saat Anda ingin memfilter baris di mana satu kolom cocok dengan SALAH SATU dari beberapa nilai. Kedua baris dengan baris Davolio AND dengan Buchanan akan ditampilkan.
Untuk menemukan baris yang memenuhi beberapa kriteria untuk satu kolom, ketikkan kriteria langsung di bawah masing-masing baris terpisah dari rentang kriteria tersebut. Misalnya, masukkan yang berikut ini ke dalam dua baris pertama rentang kriteria:
Tipe Tenaga penjual Penjualan ="=Davolio" ="=Buchanan" Klik sel dalam rentang daftar.
Pada tab Data, di dalam grup Urutkan & Filter, klik Tingkat Lanjut.
Pilih untuk Memfilter daftar, di tempat, menyembunyikan baris yang tidak cocok dengan kriteria Anda, atau Salin ke lokasi lain, salin baris yang cocok dengan kriteria Anda ke area lain lembar kerja.
Dalam kotak Rentang kriteria, masukkan referensi untuk rentang kriteria, termasuk label kriteria. Dengan menggunakan contoh, masukkan $A$1: $C$3.
Dengan menggunakan contoh, hasil yang difilter untuk rentang daftar adalah:
Tipe Tenaga penjual Penjualan Daging Davolio $450 hasil bumi Buchanan $6,328 Hasil bumi Davolio $6,544
Beberapa kriteria, beberapa kolom, semua kriteria benar
Logika Boolean: (Tipe = "Hasil Bumi" AND Penjualan > 1000)
Untuk menemukan baris yang memenuhi beberapa kriteria dalam beberapa kolom, ketik seluruh kriteria di baris yang sama dari rentang kriteria. Misalnya, masukkan:
Tipe Tenaga penjual Penjualan ="=Hasil bumi" >1000 Klik sel dalam rentang daftar.
Pada tab Data, di dalam grup Urutkan & Filter, klik Tingkat Lanjut.
Pilih untuk Memfilter daftar, di tempat, menyembunyikan baris yang tidak cocok dengan kriteria Anda, atau Salin ke lokasi lain, salin baris yang cocok dengan kriteria Anda ke area lain lembar kerja.
Dalam kotak Rentang kriteria, masukkan referensi untuk rentang kriteria, termasuk label kriteria. Dengan menggunakan contoh, masukkan $A$1: $C$2.
Dengan menggunakan contoh, hasil yang difilter untuk rentang daftar adalah:
Tipe Tenaga penjual Penjualan hasil bumi Buchanan $6,328 Hasil bumi Davolio $6,544
Beberapa kriteria, beberapa kolom, kriteria apa pun benar
Logika Boolean: (Tipe = "Hasil Bumi" OR Tenaga Penjual = "Buchanan")
Untuk menemukan baris yang memenuhi beberapa kriteria dalam beberapa kolom, di mana kriteria apa saja bisa benar, ketik kriteria itu di kolom dan baris yang berbeda dari rentang kriteria. Misalnya, masukkan:
Tipe Tenaga penjual Penjualan ="=Hasil bumi" ="=Buchanan" Klik sel dalam rentang daftar.
Pada tab Data , dalam grup Urutkan filter & , klik Tingkat Lanjut.
Pilih untuk Memfilter daftar, di tempat, menyembunyikan baris yang tidak cocok dengan kriteria Anda, atau Salin ke lokasi lain, salin baris yang cocok dengan kriteria Anda ke area lain lembar kerja.
Dalam kotak Rentang kriteria, masukkan referensi untuk rentang kriteria, termasuk label kriteria. Dengan menggunakan contoh, masukkan $A$1: $B$3.
Dengan menggunakan contoh, hasil yang difilter untuk rentang daftar adalah:
Tipe Tenaga penjual Penjualan hasil bumi Buchanan $6,328 Hasil bumi Davolio $6,544
Beberapa set kriteria, satu kolom dalam semua set
Logika Boolean: ( (Penjualan > 6000 AND Penjualan < 6500 ) OR (Penjualan < 500) )
Untuk menemukan baris yang memenuhi beberapa set kriteria, di mana setiap set menyertakan kriteria untuk satu kolom, termasuk beberapa kolom dengan judul kolom sama. Misalnya, masukkan:
Tipe Tenaga penjual Penjualan Penjualan >6000 <6500 <500 Klik sel dalam rentang daftar. Dengan menggunakan contoh, klik sel mana saja dalam rentang daftar A6:C10.
Pada tab Data, di dalam grup Urutkan & Filter, klik Tingkat Lanjut.
Pilih untuk Memfilter daftar, di tempat, menyembunyikan baris yang tidak cocok dengan kriteria Anda, atau Salin ke lokasi lain, salin baris yang cocok dengan kriteria Anda ke area lain lembar kerja.
-
Tip
Saat Anda menyalin baris yang difilter ke lokasi lain, Anda bisa menentukan kolom mana yang disertakan dalam operasi penyalinan. Sebelum memfilter, salin label kolom untuk kolom yang Anda inginkan ke baris pertama area tempat Anda merencanakan untuk menempelkan baris yang difilter. Saat Anda memfilter, masukkan referensi ke label kolom yang disalin dalam kotak Salin ke. Kemudian baris yang disalin hanya akan menyertakan kolom tempat Anda memasukkan label yang salin.
-
Dalam kotak Rentang kriteria, masukkan referensi untuk rentang kriteria, termasuk label kriteria. Dengan menggunakan contoh, masukkan $A$1: $D$3.
Dengan menggunakan contoh, hasil yang difilter untuk rentang daftar adalah:
Tipe Tenaga penjual Penjualan Daging Davolio $450 hasil bumi Buchanan $6,328
Beberapa kumpulan kriteria, beberapa kolom dalam setiap kriteria
Logika Boolean: ( (Tenaga Penjual = "Davolio" AND Sales >3000) OR (Tenaga Penjual = "Buchanan" AND Sales > 1500) )
Untuk menemukan baris yang memenuhi beberapa set kriteria, di mana setiap set menyertakan kriteria untuk beberapa kolom, ketik setiap set kriteria dalam kolom dan baris terpisah. Misalnya, masukkan:
Tipe Tenaga penjual Penjualan ="=Davolio" >3000 ="=Buchanan" >1500 Klik sel dalam rentang daftar. Dengan menggunakan contoh, klik sel mana saja dalam rentang daftar A6:C10.
Pada tab Data, di dalam grup Urutkan & Filter, klik Tingkat Lanjut.
Pilih untuk Memfilter daftar, di tempat, menyembunyikan baris yang tidak cocok dengan kriteria Anda, atau Salin ke lokasi lain, salin baris yang cocok dengan kriteria Anda ke area lain lembar kerja.
Dalam kotak Rentang kriteria, masukkan referensi untuk rentang kriteria, termasuk label kriteria. Dengan menggunakan contoh, masukkan $A$1: $C$3.
Dengan menggunakan contoh, hasil yang difilter untuk rentang daftar adalah:
Tipe Tenaga penjual Penjualan hasil bumi Buchanan $6,328 Hasil bumi Davolio $6,544
Kriteria wildcard
Logika Boolean: Tenaga Penjual = nama dengan 'u' sebagai huruf kedua
Untuk menemukan nilai teks yang memiliki beberapa kesamaan karakter tetapi tidak yang lainnya, lakukan satu atau beberapa hal berikut ini:
Ketikkan satu atau beberapa karakter tanpa tanda sama dengan (=) untuk menemukan baris dengan nilai teks dalam kolom yang dimulai dengan karakter tersebut. Misalnya, jika Anda mengetikkan teks Dav sebagai kriteria, Excel menemukan "Davolio", "David", dan "Davis."
Gunakan karakter wildcard.
Gunakan Untuk menemukan ? (tanda tanya) Setiap karakter tunggal
Misalnya, sm?th menemukan "smith" dan "smyth"* (tanda bintang) Jumlah karakter apa pun
Sebagai contoh, *east menemukan "Northeast" dan "Southeast"~ (tanda gelombang) diikuti oleh ?, *, or ~ Tanda tanya, tanda bintang, atau tilde
Misalnya, fy91~? akan menemukan "fy91?"
Sisipkan setidaknya tiga baris kosong di atas rentang daftar yang bisa digunakan sebagai rentang kriteria. Rentang kriteria harus memiliki label kolom. Pastikan bahwa ada setidaknya satu baris kosong di antara nilai kriteria dan rentang daftar.
Dalam baris di bawah label kolom, ketik kriteria yang ingin Anda cocokkan. Dengan menggunakan contoh, masukkan:
Tipe Tenaga penjual Penjualan ="=Me*" ="=?u*" Klik sel dalam rentang daftar. Dengan menggunakan contoh, klik sel mana saja dalam rentang daftar A6:C10.
Pada tab Data, di dalam grup Urutkan & Filter, klik Tingkat Lanjut.
Pilih untuk Memfilter daftar, di tempat, menyembunyikan baris yang tidak cocok dengan kriteria Anda, atau Salin ke lokasi lain, salin baris yang cocok dengan kriteria Anda ke area lain lembar kerja.
Dalam kotak Rentang kriteria, masukkan referensi untuk rentang kriteria, termasuk label kriteria. Dengan menggunakan contoh, masukkan $A$1: $B$3.
Dengan menggunakan contoh, hasil yang difilter untuk rentang daftar adalah:
Tipe Tenaga penjual Penjualan Minuman Suyama $5,122 Daging Davolio $450 hasil bumi Buchanan $6,328
Cara Menghapus atau Menghapus Filter Tingkat Lanjut
Setelah menerapkan Filter Tingkat Lanjut, Anda mungkin ingin menghapusnya untuk melihat kembali semua data Anda. Berikut ini caranya:
- Klik sel mana pun dalam rentang data yang difilter.
- Masuk ke tab Data .
- Dalam grup Urutkan filter &, klik Hapus.
- Semua baris akan ditampilkan lagi.
Perlu bantuan lainnya?
Anda selalu dapat bertanya kepada pakar dalam Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.