Memfilter data di Excel – mudah dan komprehensif

Perintah Filter Excel dapat membantu memfilter data dalam rentang atau tabel untuk hanya menampilkan data yang Anda butuhkan dan menyembunyikan sisanya. Anda dapat menerapkan operator bawaannya untuk dengan mudah memfilter angka, teks atau tanggal seperti memfilter semua angka yang lebih besar dari atau sama dengan angka tertentu, memfilter teks dimulai, diakhiri atau berisi karakter atau kata tertentu, atau hanya menampilkan baris di mana tanggal jatuh tempo adalah sebelum atau setelah tanggal tertentu dan seterusnya. Setelah Anda memfilter data dalam rentang atau tabel, jika datanya berubah, Anda bisa menerapkan kembali filter untuk mendapatkan data baru, atau menghapus filter untuk menampilkan semua data.

Dalam tutorial ini, kami akan mendemonstrasikan cara menambah, menggunakan, atau menghapus filter di Excel. Selain itu, kami akan memandu Anda cara menyempurnakan fitur filter untuk menangani masalah Excel yang lebih rumit.

---

1. Bagaimana menambahkan filter di Excel

Untuk memfilter data dalam rentang atau tabel, pertama-tama Anda perlu menambahkan filter ke data Anda. Bagian ini menyediakan 3 cara untuk menambahkan filter di Excel.

1.1 Perintah filter pada tab Data

Pilih sel mana pun dalam rentang atau tabel yang ingin Anda tambahkan filter, klik Data > Menyaring.

1.2 Perintah filter pada tab Home

Pilih sel mana pun dalam rentang atau tabel yang ingin Anda tambahkan filter, klik Beranda > Sortir & Filter > Menyaring.

1.3 Tambahkan filter dengan pintasan

Pilih sel mana pun dalam rentang atau tabel yang ingin Anda tambahkan filter, lalu tekan Ctrl + perubahan + L kunci.

Setelah menerapkan salah satu operasi di atas, Anda dapat melihat panah drop-down ditambahkan di header kolom sel yang dipilih.

---

2. Bagaimana menerapkan filter di Excel (satu atau beberapa kriteria)

Setelah menambahkan filter, Anda perlu menerapkannya secara manual. Bagian ini akan menunjukkan kepada Anda bagaimana menerapkan filter di satu atau beberapa kolom di Excel.

2.1 Menerapkan filter ke satu kolom (satu kriteria)

Jika Anda hanya ingin menerapkan filter ke satu kolom, seperti data filter di kolom C seperti gambar di bawah ini. Silakan buka kolom itu, lalu lakukan hal berikut.

1. 1) Klik panah tarik-turun di tajuk kolom.
2. 2) Tentukan kondisi filter yang Anda butuhkan.
3. 3) Klik OK tombol untuk mulai memfilter. Lihat tangkapan layar:
   

Sekarang filter diterapkan ke kolom C. Semua data yang memenuhi kriteria filter akan ditampilkan dan sisanya akan disembunyikan.

Setelah menerapkan filter, Anda dapat melihat panah drop-down berubah menjadi ikon filter .

Sangat diperhatikan bahwa ketika Anda mengarahkan kursor ke ikon filter, kriteria filter yang telah Anda tentukan akan ditampilkan sebagai ujung layar seperti gambar di bawah ini. Jadi, jika Anda lupa kriteria yang telah Anda tentukan untuk sebuah filter, cukup arahkan kursor ke atas ikon filter.

2.2 Menerapkan filter dengan beberapa kriteria ke beberapa kolom (beberapa kriteria)

2.2.1 Menerapkan filter dengan beberapa kriteria ke beberapa kolom satu per satu

Jika Anda ingin menerapkan filter ke beberapa kolom dengan beberapa kriteria, cukup ulangi metode di atas ke beberapa kolom satu per satu.

Setelah menerapkan filter ke beberapa kolom, Anda dapat melihat panah drop-down di kolom yang difilter berubah menjadi ikon filter.

2.2.2 Secara bersamaan menerapkan filter dengan beberapa kriteria ke beberapa kolom

Dengan metode di atas, Anda perlu menerapkan filter ke kolom satu per satu, dan yang terpenting adalah metode ini hanya mendukung DAN kriteria. Di sini perkenalkan metode untuk Anda tidak hanya menerapkan filter ke beberapa kolom secara bersamaan, tetapi juga menerapkan keduanya DAN dan OR kriteria.

Misalkan Anda memiliki tabel data seperti gambar di bawah ini yang ditampilkan, dan ingin memfilter data dari beberapa kolom berdasarkan beberapa kriteria: Produk = AAA-1 dan Pesanan> 80, or Harga Total> 10000. Silakan coba salah satu metode berikut untuk menyelesaikannya.

2.2.2.1 Menerapkan filter ke beberapa kolom dengan fungsi Filter Lanjutan

Fungsi Filter Lanjutan dapat membantu Anda mengatasi masalah ini, lakukan hal berikut selangkah demi selangkah.

1. Pertama, buat kriteria di lembar kerja seperti gambar di bawah ini.

Catatan: Untuk kriteria DAN, harap letakkan nilai kriteria di sel berbeda dari baris yang sama. Dan letakkan nilai kriteria OR di baris lainnya.

2. klik Data > Advanced untuk menyalakan Filter Lanjutan fungsi.

3. Dalam Filter Lanjutan kotak dialog, konfigurasikan sebagai berikut.

Sekarang kolom disaring secara bersamaan berdasarkan kriteria yang diberikan seperti gambar di bawah ini.

2.2.2.2 Mudah menerapkan filter ke beberapa kolom dengan alat yang luar biasa

Sebagai DAN dan OR kriteria filter tidak mudah dikelola dengan metode di atas, di sini sangat merekomendasikan Filter Super fitur dari Kutools untuk Excel. Dengan fitur ini, Anda dapat menerapkan filter ke beberapa kolom dengan kriteria DAN dan ATAU dengan mudah di Excel.

1. Setelah menginstal Kutools untuk Excelklik Kutools Ditambah > Filter Super.

Kemudian Filter Super panel ditampilkan di sisi kanan lembar kerja.

Secara default, dua grup kriteria kosong ditambahkan dengan hubungan ATAU di antara mereka di Filter Super panel. Dan hubungan antar kriteria dalam kelompok yang sama adalah DAN. Anda dapat mengubah hubungan antara kelompok yang berbeda berdasarkan kebutuhan Anda.

2. Dalam Filter Super panel, harap konfigurasikan kriteria filter sebagai berikut.

Sekarang hanya data yang cocok yang ditampilkan dalam rentang data asli, dan sisanya disembunyikan. Lihat tangkapan layar:

Tip: Dengan fitur praktis ini, Anda dapat menambahkan lebih banyak kriteria dalam grup, menambahkan lebih banyak grup, menyimpan pengaturan filter saat ini sebagai skenario untuk penggunaan di masa mendatang, dan seterusnya. Ini adalah alat yang sangat diperlukan yang dapat menghemat banyak waktu kerja dan meningkatkan efisiensi kerja.

Klik untuk mengetahui lebih banyak tentang fitur ini.

---

3. Cara menggunakan filter di Excel

Di bagian ini, Anda akan mempelajari cara menggunakan perintah filter untuk memfilter berbagai jenis tipe data seperti teks, angka, tanggal, dan format.

3.1 Memfilter nilai teks

3.1.1Filter sel teks dengan kriteria tertentu (dimulai dengan, diakhiri dengan, berisi, dan seterusnya)

Sebenarnya, operator filter bawaan - Filter Teks menyediakan banyak kriteria berguna bagi Anda untuk memfilter teks dengan mudah. Misalkan Anda ingin memfilter sel yang dimulai dengan karakter tertentu seperti J, lakukan hal berikut untuk menyelesaikannya.

1. Tambahkan filter ke tajuk kolom rentang data asli. Klik untuk mengetahui caranya.

2. Klik panah drop-down di sel header untuk membuka menu filter.

3. klik Filter Teks > Dimulai dengan.

4. Dalam Filter Otomatis Kustom kotak dialog, masukkan karakter tertentu (di sini saya mengetikkan J) ke dalam kotak teks, lalu klik OK.

Tip: Anda bisa menambahkan yang lain Dan or Or kriteria hubungan yang Anda butuhkan.

Sekarang semua sel dimulai dengan karakter J ditampilkan di kolom D seperti gambar di bawah ini.

3.1.2 Filter dengan case sensitive

Tampaknya mudah untuk memfilter sel teks berdasarkan kriteria tertentu dengan operator filter bawaan. Namun, karena fungsi Filter tidak mendukung pemfilteran teks dengan case sensitive, bagaimana kita bisa melakukan filter case sensitive di Excel? Bagian ini akan menunjukkan kepada Anda metode untuk mencapainya.

3.1.2.1 Filter teks tertentu dengan case sensitive berdasarkan rumus dan perintah Filter

Misalkan Anda ingin memfilter semua huruf besar dari teks tertentu seperti "ALAT TEKS" di kolom B, lakukan hal berikut.

1. Buat kolom pembantu disamping range data asli (disini saya memilih kolom D sebagai kolom pembantu). Masukkan rumus di bawah ini ke dalam sel kedua lalu tekan tombol Enter. Pilih sel hasil, seret Tuas IsiOtomatis turun untuk mendapatkan hasil lainnya.

= PERSIS (B2, ATAS (B2))

Catatan: Rumus ini membantu mengidentifikasi sel huruf besar dan kecil. Jika sel berisi semua karakter huruf besar, hasilnya adalah BENAR, jika tidak, Anda akan mendapatkan hasil sebagai SALAH.

2. Pilih kolom B dan D (kolom C yang akan dipilih, sudahlah), klik Data> Filter untuk menambahkan filter ke dalamnya.

3. Pergi ke kolom B (kolom tersebut berisi teks yang akan Anda filter), dan kemudian konfigurasikan sebagai berikut.

Sekarang hanya huruf besar dan huruf kecil dari "alat teks" yang ditampilkan di kolom B.

4. Klik panah drop-down di kolom D, hapus centang Select All kotak centang, centang kotak di sebelah BENAR item, lalu klik OK .

Kemudian semua huruf besar teks "alat teks" di kolom B difilter seperti gambar di bawah ini.

3.1.2.2 Filter sel dengan mudah menggunakan case sensitive dengan alat yang luar biasa

Jika Anda hanya ingin memfilter semua teks huruf besar atau kecil dalam kolom, berikut ini rekomendasikan Filter Khusus fitur dari Kutools untuk Excel. Dengan fitur ini, semua teks huruf besar atau kecil akan mudah tersaring hanya dengan beberapa klik.

1. Pilih rentang kolom yang akan Anda filter di dalamnya, lalu klik Kutools Ditambah > Filter Khusus > Filter Khusus.

2. Dalam Filter Khusus kotak dialog, lakukan pengaturan di bawah ini.

3. Kemudian a Kutools untuk Excel kotak dialog muncul untuk memberi tahu Anda berapa banyak sel yang ditemukan dan akan disaring, klik OK .

Sekarang semua sel huruf besar atau kecil disaring segera seperti gambar di bawah ini.

Klik untuk mengetahui lebih banyak tentang fitur ini.

3.1.3 Filter berdasarkan panjang teks

Jika Anda ingin memfilter sel berdasarkan panjang teks, misalnya, untuk memfilter sel dengan panjang teks yang sama dengan 10 karakter, apa yang harus Anda lakukan? Di sini, tiga metode di bawah ini dapat membantu Anda.

3.1.3.1 Filter sel berdasarkan panjang teks dengan perintah Filter

Sebenarnya, perintah Filter memiliki operator bawaan untuk mengatasi masalah ini, lakukan hal berikut.

1. Pilih rentang sel yang akan difilter (di sini saya pilih B1: B27), lalu tambahkan filter ke rentang kolom ini dengan mengklik Data > Menyaring.

2. Klik panah drop-down di header kolom, lalu klik Filter Teks > Filter Ubahsuaian. Lihat tangkapan layar:

3. Dalam Filter Otomatis Kustom kotak dialog, pilih kriteria sama dengan, ketik 10 tanda tanya (?) sebagai mode pola ke dalam kotak teks, dan kemudian klik OK .

Tips: 10 tanda tanya ini menunjukkan bahwa itu akan cocok dengan string teks yang panjangnya 10.

Sekarang semua sel dengan panjang string teks 10 (termasuk spasi) akan segera difilter.

3.1.3.2 Filter sel berdasarkan panjang teks dengan rumus dan perintah Filter

Selain itu, Anda dapat menggunakan fungsi LEN untuk menghitung panjang string teks setiap sel, lalu menerapkan perintah Filter untuk memfilter sel panjang teks yang diperlukan berdasarkan hasil yang dihitung.

1. Buat kolom pembantu yang berdekatan dengan rentang data asli. Masukkan rumus di bawah ini ke dalamnya lalu tekan tombol Enter. Pilih sel hasil lalu seret Tuas IsiOtomatis turun untuk mendapatkan hasil lainnya.

= LEN (B2)

Sekarang Anda mendapatkan panjang teks dari setiap sel di kolom tertentu.

2. Pilih kolom pembantu (termasuk tajuk), klik Data > Filter untuk menambahkan filter ke dalamnya.

3. Klik panah drop-down, hapus centang Select All kotak untuk membatalkan pilihan semua item, dan kemudian hanya centang kotak di sebelah nomor 10, dan terakhir klik OK .

Sekarang semua sel dengan panjang string teks 10 (termasuk spasi) akan segera difilter.

3.1.3.3 Memfilter sel dengan mudah berdasarkan panjang teks dengan alat yang luar biasa

Di sini merekomendasikan Filter Khusus kegunaan Kutools untuk Excel untuk membantu Anda dengan mudah memfilter sel berdasarkan panjang teks di Excel.

1. Pilih rentang kolom Anda akan memfilter sel berdasarkan panjang teks tertentu, klik Kutools Ditambah > Filter Khusus > Filter Khusus. Lihat tangkapan layar:

2. Dalam Filter Khusus kotak dialog, konfigurasikan sebagai berikut.

3. Itu Kutools untuk Excel kotak dialog muncul untuk memberi tahu Anda berapa banyak sel yang ditemukan dan akan disaring, klik OK untuk melanjutkan.

Kemudian semua sel dengan panjang string teks sama dengan 10 difilter seperti gambar di bawah ini.

3.2 Memfilter nomor

Di Excel, juga sangat mudah untuk memfilter angka dengan perintah Number Filters.

Misalkan Anda ingin memfilter sel dengan angka antara 15000 dan 20000 dalam kolom (seperti kolom C seperti gambar di bawah yang ditunjukkan), Anda dapat melakukan hal berikut untuk mencapainya.

1. Pilih kolom range berisi angka-angka yang akan Anda filter, klik Data > Filter untuk menambahkan filter.

2. Setelah menambahkan filter, konfigurasikan sebagai berikut.

Tip: Karena saya ingin memfilter sel dengan angka antara 15000 dan 20000, di sini saya memasukkan 15000 dan 20000 secara terpisah ke dalam kotak teks.

Sekarang sel dengan angka antara 15000 dan 20000 disaring seperti gambar di bawah ini.

3.3 Filter tanggal

Secara default, fitur bawaan Filter Tanggal menyediakan banyak kriteria umum untuk memfilter tanggal. Seperti yang Anda lihat, tidak ada opsi bawaan untuk memfilter tanggal berdasarkan hari dalam seminggu, akhir pekan, atau hari kerja. Bagian ini akan mengajari Anda cara mencapai pengoperasian ini.

3.3.1 Filter tanggal berdasarkan hari dalam seminggu atau akhir pekan

Misalkan Anda memiliki tabel data seperti gambar di bawah ini, jika Anda ingin memfilter tanggal berdasarkan hari atau akhir pekan, silakan terapkan salah satu metode di bawah ini.

3.3.1.1 Filter tanggal berdasarkan hari dalam seminggu atau akhir pekan dengan rumus dan perintah Filter

Di bagian ini, Anda akan menerapkan fungsi WEEKDAY untuk menghitung hari dalam seminggu setiap tanggal, lalu menerapkan filter untuk memfilter hari atau akhir pekan tertentu yang Anda butuhkan.

1. Dalam sel kosong (D2 dalam hal ini), masukkan rumus di bawah ini ke dalamnya dan tekan Enter kunci. Pilih sel hasil, lalu seret Tuas IsiOtomatis di atas sel di bawah ini untuk menerapkan rumus ini.

= WEEKDAY (A2)

Tip:

Catatan: Seperti yang Anda lihat, rumus mengembalikan angka dari 1 untuk 7, yang menunjukkan hari dari Minggu untuk Sabtu (1 untuk Minggu, 7 untuk Sabtu).

2. Pilih seluruh hasil rumus (termasuk sel header), klik Data > Menyaring.

3. Klik panah drop-down, lalu hapus centang Select All checkbox.

Kemudian semua sel akhir pekan atau hari tertentu dalam seminggu disaring. Lihat tangkapan layar:

3.3.1.2 Dengan mudah memfilter tanggal berdasarkan hari dalam seminggu atau akhir pekan dengan alat yang luar biasa

Jika metode di atas tidak nyaman bagi Anda, berikut ini rekomendasikan Filter Khusus kegunaan Kutools untuk Excel. Dengan fitur ini, Anda dapat dengan mudah memfilter sel yang berisi hari apa pun dalam seminggu hanya dengan beberapa klik.

1. Pilih sel yang berisi tanggal yang ingin Anda filter berdasarkan hari tertentu dalam seminggu.

2. klik Kutools Ditambah > Filter Khusus > Filter Khusus.

3. Dalam Filter Khusus kotak dialog, konfigurasikan sebagai berikut.

4. Kemudian a Kutools untuk Excel kotak dialog muncul dengan berapa banyak sel yang ditemukan dan akan disaring, klik OK untuk melanjutkan.

Sekarang semua sel akhir pekan atau hari apa pun dalam seminggu disaring.

3.3.2 Filter sel berdasarkan hari kerja dengan alat yang luar biasa

Selain menyaring sel berdasarkan hari dalam seminggu atau akhir pekan, file Filter Khusus kegunaan Kutools untuk Excel juga dapat membantu memfilter sel berdasarkan hari kerja.

1. Terapkan langkah yang sama seperti di atas untuk mengaktifkan utilitas Filter Khusus.

2. Dalam Filter Khusus kotak dialog, lakukan pengaturan di bawah ini.

3. Kemudian a Kutools untuk Excel kotak dialog muncul. Klik OK untuk melanjutkan.

Sekarang semua sel hari kerja difilter.

3.4 Filter format

Biasanya, Excel mendukung pemfilteran data berdasarkan kriteria visual seperti warna font, warna sel atau kumpulan ikon seperti gambar di bawah ini.

Namun, jika Anda ingin memfilter data berdasarkan kriteria visual lainnya seperti gaya font (tebal, miring), efek font (coret) atau sel khusus (berisi rumus), Excel tidak membantu mencapainya. Bagian ini memberikan metode untuk membantu Anda mengatasi masalah ini.

3.4.1 Memfilter menurut teks berformat tebal / miring

Misalkan Anda ingin memfilter data dengan format teks tebal atau miring seperti gambar di bawah ini yang ditampilkan, metode berikut dapat membantu Anda. Silakan lakukan sebagai berikut.

3.4.1.1 Filter teks yang diformat tebal / miring dengan rumus dan perintah Filter

Kombinasi rumus Get.Cell dan perintah Filter bisa membantu memfilter teks berformat tebal dalam rentang kolom.

1. klik Rumus > Tentukan Nama.

2. Dalam Nama baru kotak dialog, Anda perlu:

Sintaks Rumus:

=GET.CELL(type_num, reference)

Argumen Rumus

3. Pilih sel kosong di baris yang sama dari B2, ketikkan rumus di bawah ini ke dalamnya dan kemudian tekan Enter kunci. Pilih sel hasil, seret Tuas IsiOtomatis di atas sel di bawah ini untuk menerapkan rumus ini.

= Filter_Bold_Cells

4. Pilih seluruh sel hasil (termasuk header), klik Data > Menyaring.

5. Klik panah drop-down, hanya centang kotak di sebelah BENAR pilihan, lalu klik OK.

Kemudian semua sel teks tebal atau miring difilter. Lihat tangkapan layar:

3.4.1.2 Filter teks yang diformat tebal atau miring dengan perintah Find and Replace dan Filter

Anda juga dapat menggunakan kombinasi perintah Temukan dan Ganti dan Filter untuk mencapainya.

1. Pilih rentang kolom yang berisi sel teks tebal atau miring yang akan Anda filter, lalu tekan Ctrl + F kunci.

2. Dalam Cari dan Ganti kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.

3. Sekarang semua sel teks tebal atau miring dipilih dalam kisaran aslinya, klik Beranda > Warna Isi, lalu pilih warna isian untuk sel yang dipilih.

4. Pilih kembali seluruh rentang kolom, klik Data > Filter untuk menambahkan filter ke dalamnya.

5. Klik panah drop-down, pilih Filter dengan Warna, lalu klik warna isian yang baru saja Anda tentukan di bawah Filter menurut Warna Sel. Lihat tangkapan layar:

Kemudian semua sel teks tebal atau miring difilter.

3.4.1.3 Mudah memfilter teks yang diformat tebal atau miring dengan alat yang luar biasa

Seperti yang kami sebutkan di atas, file Filter Khusus kegunaan Kutools untuk Excel dapat membantu dengan mudah memfilter dengan case sensitive, memfilter berdasarkan panjang teks, menyaring tanggal. Di sini, kami akan mengajarkan cara menerapkan fitur ini untuk memfilter sel teks berformat tebal atau miring di Excel.

1. Pilih kolom range (termasuk header) berisi sel berformat tebal atau miring yang akan Anda filter.

2. klik Kutools Ditambah > Filter Khusus > Filter Tebal / Filter Miring. Lihat tangkapan layar:

3. klik OK dalam bermunculan Kutools untuk Excel kotak dialog untuk melanjutkan (kotak dialog ini muncul untuk memberi tahu Anda berapa banyak sel yang memenuhi kriteria).

Sekarang semua sel teks berformat tebal atau miring difilter.

3.4.2 Memfilter menurut teks berformat coret

Misalkan Anda telah menerima daftar dengan orang-orang memotong dengan menambahkan coretan pada mereka, dan perlu mengetahui semua sel coretan dengan memfilter, metode di bawah ini dapat membantu Anda.

3.4.2.1 Filter teks berformat coret dengan fungsi yang ditentukan pengguna dan perintah Filter

Anda bisa menerapkan fungsi yang ditentukan pengguna untuk mengidentifikasi sel teks yang diformat coret, lalu gunakan perintah Filter untuk memfilter semua sel coret berdasarkan hasil.

1. tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul. Dan kemudian salin kode VBA di bawah ini ke file Kode jendela.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

4. Pilih sel kosong (sel ini harus berada di baris yang sama dari sel yang ingin Anda hitung), masukkan rumus di bawah dan tekan Enter kunci. Pilih sel hasil, seret Tuas IsiOtomatis di atas sel di bawah ini untuk menerapkan rumus ini.

= HasStrike (B2)

Catatan: Jika sel terkait memiliki efek font coret, itu akan kembali BENAR, jika tidak, ia akan kembali SALAH.

5. Pilih seluruh sel hasil (termasuk sel header), klik Data > Menyaring.

6. Kemudian klik panah drop-down> hanya centang kotak di sebelah opsi BENAR> klik OK. Lihat tangkapan layar yang ditampilkan.

Sekarang Anda dapat melihat semua sel berformat coret yang difilter.

3.4.2.2 Dengan mudah memfilter teks yang diformat dengan coretan menggunakan alat yang luar biasa

Dengan Filter Khusus kegunaan Kutools untuk Excel, semua sel berformat coret dapat difilter secara langsung hanya dengan beberapa klik.

1. Pilih rentang kolom Anda akan memfilter semua sel berformat coret, klik Kutools Ditambah > Filter Khusus > Filter Coret.

2. Kemudian a Kutools untuk Excel kotak dialog muncul untuk memberi tahu Anda berapa banyak sel yang memenuhi syarat, klik OK untuk melanjutkan.

Kemudian semua sel berformat coret disaring seperti gambar di bawah ini yang ditampilkan.

3.4.3 Memfilter menurut font atau warna latar belakang

Seperti yang kami sebutkan di awal bagian format filter ini, Excel mendukung pemfilteran data berdasarkan kriteria visual seperti warna font, warna sel atau kumpulan ikon dengan fitur bawaannya. Bagian ini menunjukkan cara menerapkan file Filter dengan Warna fitur untuk memfilter sel berdasarkan font atau warna latar belakang secara detail. Sementara itu, kami merekomendasikan fungsi pihak ketiga yang praktis untuk membantu memecahkan masalah ini.

3.4.3.1 Filter menurut satu font atau warna latar belakang dengan perintah Filter

Anda dapat langsung menerapkan fitur Filter menurut Warna dari perintah Filter untuk memfilter sel dengan font atau warna latar belakang tertentu di Excel.

1. Pilih rentang kolom yang akan Anda filter sel dengan font atau warna latar belakang, dan kemudian klik Data > Menyaring.

2. Klik panah tarik-turun> Filter dengan Warna. Kemudian Anda dapat melihat semua warna sel dan warna font dari rentang kolom saat ini dicantumkan. Klik pada warna sel mana saja atau warna font akan memfilter semua sel berdasarkan itu.

3.4.3.2 Memfilter menurut beberapa warna latar belakang dengan fungsi yang ditentukan pengguna dan perintah Filter

Jika Anda ingin memfilter sel dengan beberapa warna latar belakang, harap terapkan metode di bawah ini.

Misalkan Anda ingin memfilter semua sel dengan warna latar belakang oranye dan biru di kolom B seperti gambar di bawah ini. Pertama, Anda perlu menghitung indeks warna sel-sel ini.

1. tekan lain + F11 kunci secara bersamaan.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul. Kemudian salin kode VBA di bawah ini ke jendela kode.

Kode VBA: Dapatkan indeks warna latar belakang sel

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

4. Di kolom baru, ketikkan header ke dalam sel pertama (sel ini harus berada di baris yang sama dari header pada rentang asli).

5. Pilih sel kosong di sebelah sel header (di sini saya pilih E2), masukkan rumus di bawah ini ke dalamnya dan tekan Enter kunci. Pilih sel hasil, lalu seret Tuas IsiOtomatis di atas sel di bawah ini untuk menerapkan rumus ini.

= GetColor (B2)

Catatan: Jika sel tidak memiliki warna isian, ia mengembalikan -4142.

6. Pilih sel kolom pembantu (termasuk header), klik Data > Filter untuk menambahkan filter ke kolom.

7. Klik panah drop-down untuk membuka daftar drop-down, dan kemudian konfigurasikan sebagai berikut.

Sekarang sel disaring dengan warna latar belakang yang ditentukan seperti gambar di bawah ini.

3.4.3.3 Menyaring dengan mudah berdasarkan font atau warna latar belakang dengan alat yang luar biasa

Tidak diragukan lagi, mudah untuk menerapkan fitur bawaan Filter menurut Warna untuk memfilter sel berdasarkan font atau warna latar belakang. Namun, kerugiannya adalah kotak daftar drop-down menutupi konten data asli sehingga kami tidak dapat melihat data kapan pun untuk memilih font atau warna latar belakang dengan benar. Untuk menghindari masalah ini, di sini merekomendasikan Filter Khusus kegunaan Kutools untuk Excel.

1. Pilih rentang kolom yang akan Anda filter sel dengan font atau warna latar belakang, dan kemudian klik Kutools Ditambah > Filter Khusus > Filter Khusus.

2. Dalam Filter Khusus kotak dialog, konfigurasikan sebagai berikut.

Kemudian semua sel dengan warna font tertentu atau warna latar belakang dalam rentang yang dipilih akan difilter.

3.4.4 Filter sel yang berisi rumus

Jika Anda memiliki daftar panjang data yang berisi nilai dan rumus aktual, dan hanya perlu memfilter sel rumus, apa yang dapat Anda lakukan? Bagian ini memberikan dua metode untuk mencapainya.

3.4.4.1 Filter sel formula dengan fungsi yang ditentukan pengguna dan perintah Filter

Pertama, Anda perlu mencari tahu semua sel rumus dalam daftar dengan fungsi yang ditentukan pengguna, lalu menerapkan perintah Filter untuk memfilter sel rumus berdasarkan hasil.

1. tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, kemudian salin kode VBA di bawah ini ke jendela Kode.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

4. Pilih sel kosong, sel ini harus berada di baris yang sama dari sel yang ingin Anda periksa apakah itu sel formula, masukkan rumus di bawah ini ke dalamnya dan kemudian tekan tombol Enter kunci. Pilih sel hasil, seret Tuas IsiOtomatis di atas sel di bawah ini untuk menerapkan rumus ini.

= HasFormula (C2)

Seperti yang ditunjukkan gambar di atas, hasilnya adalah SALAH dan BENAR, yang menunjukkan bahwa jika sel terkait adalah sel rumus, ia mengembalikan TRUE, jika tidak mengembalikan FALSE.

5. Pilih sel hasil (termasuk sel header), klik Data > Filter untuk menambahkan filter ke dalamnya.

6. Klik panah drop-down, hanya centang kotak di sebelah BENAR kotak, lalu klik OK.

Kemudian Anda dapat melihat semua sel rumus difilter.

3.4.4.2 Menyaring sel formula dengan mudah menggunakan alat yang luar biasa

Di sini menunjukkan utilitas Filter Khusus dari Kutools for Excel untuk membantu Anda dengan mudah menyaring sel formula dalam daftar hanya dengan beberapa klik.

1. Pilih daftar yang ingin Anda filter semua sel formula, lalu klik Kutools Ditambah > Filter Khusus > Formula Filter.

2. Itu Kutools untuk Excel kotak dialog muncul untuk memberi tahu Anda berapa banyak sel yang memenuhi syarat, klik OK untuk melanjutkan.

Kemudian semua sel formula disaring seperti gambar di bawah ini.

Selain itu, Anda dapat menggunakan file Filter Khusus fitur dari Kutools untuk Excel untuk dengan mudah memfilter sel dengan pemformatan lain, seperti:

Filter semua sel dengan komentar, klik untuk mengetahui lebih lanjut ...

Filter semua sel yang digabungkan berdasarkan nilai tertentu, klik untuk mengetahui lebih lanjut ...

3.5 Filter dengan wildcard

Terkadang, Anda mungkin lupa kriteria pencarian yang tepat saat memfilter. Dalam kasus ini, kami menyarankan Anda untuk menggunakan karakter wildcard.

Hanya ada 3 karakter wildcard di Excel:

Karakter wildcard	Deskripsi Produk	Contoh
* (tanda bintang)	Mewakili sejumlah karakter	Sebagai contoh, * beri menemukan "Blackberry","Stroberi","blueberry" dan seterusnya
? (tanda tanya)	Mewakili karakter tunggal apa pun	Sebagai contoh, l? ck menemukan "menjilat","mengunci","kekurangan" dan seterusnya
~ (pasang) diikuti oleh *, ?, atau ~	Mewakili nyata *,? or ~ karakter	Sebagai contoh, Wajah ~ * Toko menemukan “Wajah * Toko”

Mari kita lihat bagaimana menggunakan wildcard dalam pemfilteran.

Misalkan Anda perlu memfilter semua sel yang diakhiri dengan Pasar di kolom B seperti tangkapan layar di bawah ini, lakukan hal berikut.

1. Pertama, buat rentang kriteria. Masukkan header yang sama dengan header kolom asli, lalu ketikkan kriteria filter di sel di bawah ini. Lihat tangkapan layar:

2. klik Data > Canggih.

3. Dalam pembukaan Filter Lanjutan kotak dialog, konfigurasikan sebagai berikut.

Kemudian semua sel yang diakhiri dengan Pasar disaring. Lihat tangkapan layar:

Penggunaan karakter wildcard * dan ~ dalam pemfilteran sama seperti operasi di atas.

3.6 Filter dengan kotak pencarian built-in

Jika Anda menggunakan Excel 2010 atau versi yang lebih baru, Anda mungkin memperhatikan bahwa ada kotak pencarian bawaan di Filter Excel. Bagian ini akan menunjukkan cara menggunakan kotak pencarian ini untuk memfilter data di Excel.

Seperti gambar di bawah ini yang ditampilkan, Anda ingin memfilter semua sel yang berisi "Pasar", kotak pencarian dapat membantu Anda menyelesaikannya dengan mudah.

1. Pilih rentang kolom yang akan Anda filter datanya, klik Data > Filter untuk menambahkan filter ke dalamnya.

2. Klik panah drop-down, enter "Pasar" ke dalam kotak telusur, lalu klik OK.

Anda dapat melihat semua teks yang memenuhi syarat terdaftar secara real time saat memasukkan data di kotak pencarian.

Kemudian semua sel berisi "Pasar" disaring seperti gambar di bawah ini.

---

4. Salin data yang terlihat saja (abaikan data tersembunyi atau yang difilter)

Secara default, Excel menyalin sel yang terlihat dan tersembunyi. Jika Anda hanya ingin menyalin sel yang terlihat setelah pemfilteran, Anda dapat mencoba salah satu metode di bawah ini.

Salin data yang terlihat hanya dengan tombol pintas

Anda dapat menggunakan tombol pintasan untuk hanya memilih sel yang terlihat, lalu menyalin dan menempelkannya secara manual ke tempat yang diperlukan.

1. Pilih rentang yang ingin Anda salin hanya sel yang terlihat. Sel yang terlihat dan tersembunyi dipilih dalam langkah ini.

2. tekan lain dan ; kunci pada saat bersamaan. Sekarang hanya sel yang terlihat yang dipilih.

3. tekan Ctrl + C untuk menyalin sel yang dipilih, lalu tekan Ctrl + V kunci untuk menempelkannya.

Salin data yang terlihat dengan mudah hanya dengan alat yang luar biasa

Berikut perkenalkan Tempel ke Terlihat fitur dari Kutools untuk Excel agar Anda dapat dengan mudah menyalin data yang terlihat hanya di Excel. Selain itu, dengan fitur ini, Anda dapat menyalin dan menempelkan nilai hanya ke sel yang terlihat dalam rentang yang telah difilter.

1. Pilih rentang yang difilter yang ingin Anda salin hanya sel yang terlihat, lalu klik Kutools > Jarak > pasta to Terlihat > Semua / Hanya Tempel Nilai.

Untuk sel formula, pilih Semua menyalin hasil dan rumus, pilih Hanya Tempel Nilai hanya menyalin nilai sebenarnya.

2. Dalam bermunculan Tempel ke Rentang Terlihat kotak dialog, pilih sel kosong untuk mengeluarkan sel yang disalin, lalu klik OK.

Kemudian hanya sel yang terlihat dalam rentang yang difilter yang dipilih yang disalin dan ditempel ke tempat baru.

Catatan: Jika rentang tujuan telah difilter, nilai yang dipilih hanya akan ditempelkan ke sel yang terlihat.

---

5. Hapus baris yang tersembunyi atau terlihat setelah pemfilteran

Untuk daftar yang difilter, Anda mungkin perlu menghapus baris tersembunyi untuk menyimpan hanya data yang terlihat. Di sini, di bagian ini, Anda akan mempelajari tiga metode untuk menghapus baris yang tersembunyi atau terlihat dalam daftar yang difilter di Excel.

Hapus semua baris tersembunyi dari lembar kerja saat ini dengan kode VBA

Kode VBA di bawah ini dapat membantu menghapus semua baris tersembunyi dari lembar kerja saat ini di Excel.

Catatan: VBA ini tidak hanya menghapus baris tersembunyi dalam daftar yang difilter, tetapi juga menghapus baris yang telah Anda sembunyikan secara manual.

1. Dalam lembar kerja berisi baris tersembunyi yang ingin Anda hapus, tekan lain + F11 tombol secara bersamaan untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu salin kode VBA di bawah ini ke jendela Modul.

Kode VBA: Hapus semua baris tersembunyi dari lembar kerja saat ini

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. tekan F5 kunci untuk menjalankan kode.

4. Kemudian a Kutools untuk Excel kotak dialog muncul untuk memberi tahu Anda berapa banyak baris tersembunyi yang telah dihapus, klik OK untuk menutupnya.

Sekarang semua baris tersembunyi (termasuk baris tersembunyi otomatis dan baris tersembunyi secara manual) dihapus.

Hapus baris yang terlihat setelah memfilter dengan fitur Go To

Jika Anda hanya ingin menghapus baris yang terlihat dalam rentang tertentu, file pergi Untuk fitur dapat membantu Anda.

1. Pilih rentang yang difilter yang ingin Anda hapus baris yang terlihat, tekan F5 kunci untuk membuka pergi Untuk kotak dialog.

2. Dalam pergi Untuk kotak dialog, klik Khusus .

3. Dalam Pergi ke khusus kotak dialog, pilih Hanya sel yang terlihat pilihan, lalu klik OK .

4. Sekarang semua sel yang terlihat dipilih. Klik kanan rentang yang dipilih dan klik Hapus Baris dalam menu konteks.

Kemudian semua sel yang terlihat dihapus.

Hapus baris yang tersembunyi atau terlihat dengan mudah setelah memfilter dengan alat yang luar biasa

Metode di atas membosankan dan memakan waktu. Di sini merekomendasikan Hapus Baris & Kolom Tersembunyi (Terlihat) fitur dari Kutools untuk Excel. Dengan fitur ini, Anda dapat dengan mudah menghapus baris yang tersembunyi atau terlihat tidak hanya dalam rentang yang dipilih, tetapi juga di lembar kerja saat ini, beberapa lembar kerja yang dipilih atau seluruh buku kerja. Silakan lakukan sebagai berikut.

1. Pilih rentang yang ingin Anda hapus semua baris yang tersembunyi atau terlihat darinya.

Catatan:

2. klik Kutools > Delete > Hapus Baris & Kolom Tersembunyi (Terlihat).

3. Dalam Hapus Baris & Kolom Tersembunyi (Terlihat) kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.

4. Kemudian semua baris yang terlihat atau tersembunyi dihapus sekaligus. Sementara itu, kotak dialog muncul untuk memberi tahu Anda jumlah baris yang dihapus, klik OK untuk menyelesaikan seluruh operasi.

---

6. Saring di beberapa lembar

Biasanya, mudah untuk memfilter data di lembar kerja. Di bagian ini, Anda akan mempelajari cara memfilter data dengan kriteria yang sama di beberapa lembar kerja yang memiliki struktur data yang sama.

Misalkan buku kerja berisi tiga lembar kerja seperti gambar di bawah ini yang diperlihatkan, sekarang Anda ingin memfilter data di tiga lembar kerja ini pada saat yang sama dengan kriteria yang sama "Produk = KTE”, Kode VBA di bawah ini dapat membantu Anda.

1. tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu salin kode VBA di bawah ini ke jendela modul.

Kode VBA: Memfilter data di beberapa lembar kerja secara bersamaan

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Catatan: Garis "Xws.Range (“A1” .AutoFilter 1, “= KTE”)”Pada kode tersebut menunjukkan bahwa Anda akan memfilter data di kolom A dengan kriteria = KTE, dan angka 1 adalah nomor kolom dari kolom A. Anda dapat mengubahnya sesuai kebutuhan. Misalnya, jika Anda ingin memfilter semua angka yang lebih besar dari 500 di kolom B, Anda dapat mengubah baris ini menjadi "Xws.Range (“B1” .AutoFilter 2, “> 500”)".

3. tekan F5 kunci untuk menjalankan kode.

Kemudian kolom yang ditentukan difilter pada saat yang sama di semua lembar kerja buku kerja saat ini. Lihat hasil di bawah ini.

---

7. Terapkan kembali filter setelah mengubah data

Terkadang Anda mungkin telah membuat perubahan untuk rentang yang difilter. Namun, hasil filter tetap sama apa pun perubahan yang Anda buat pada rentang (lihat tangkapan layar di bawah). Di bagian ini, kami akan menunjukkan kepada Anda dua metode untuk secara manual atau otomatis menerapkan kembali filter pada rentang saat ini sehingga perubahan yang Anda buat disertakan.

Terapkan kembali filter secara manual dengan perintah Reapply

Excel memiliki built-in Melamar lagi fitur untuk membantu menerapkan kembali filter secara manual. Anda dapat mendaftar sebagai berikut.

Klik Data > Melamar lagi untuk menerapkan kembali filter di sheet saat ini.

Kemudian Anda dapat melihat rentang yang difilter diterapkan kembali untuk menyertakan perubahan yang telah Anda buat.

Terapkan kembali filter secara otomatis dengan kode VBA

Jika daftar yang difilter perlu sering diubah, Anda harus berulang kali mengklik untuk menerapkan fitur Reapply ini. Di sini berikan kode VBA untuk membantu menerapkan kembali filter secara otomatis secara real-time saat data berubah.

1. Di lembar kerja berisi filter yang ingin Anda terapkan kembali secara otomatis, klik kanan tab lembar dan pilih Lihat Kode.

2. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi jendela, salin kode VBA di bawah ini ke jendela Kode.

Kode VBA: Secara otomatis menerapkan kembali filter saat mengubah data

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Catatan: Di dalam kode, “Lembar2” adalah nama lembar kerja saat ini. Anda dapat mengubahnya sesuai kebutuhan Anda.

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, saat mengubah data dalam daftar yang difilter, hasil yang difilter akan disesuaikan secara dinamis. Lihat gambar gif di bawah ini.

---

8. Bersihkan atau hapus filter

Kami telah belajar bagaimana menambahkan, menerapkan dan menggunakan filter di konten di atas. Di sini kita akan belajar cara menghapus atau menghapus filter di Excel.

8.1 Menghapus filter dari kolom

Setelah menerapkan filter di kolom, jika Anda ingin menghapusnya, klik ikon difilter, lalu klik Hapus Filter Dari "Nama header" dari menu drop-down.

8.2 Hapus semua filter di lembar kerja

Jika Anda telah menerapkan filter ke beberapa kolom, dan ingin menghapus semuanya secara bersamaan, klik Data > Bersih.

Kemudian semua filter dihapus seperti gambar di bawah ini.

8.3 Hapus filter dari semua lembar kerja di buku kerja saat ini

Misalkan Anda telah menerapkan filter di beberapa lembar kerja di buku kerja dan ingin menghapus filter ini sekaligus. Kode VBA di bawah ini dapat membantu Anda.

1. Buka buku kerja Anda akan menghapus semua filter darinya, dan kemudian tekan lain + F11 kunci secara bersamaan.

2. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu salin kode VBA di bawah ini ke jendela modul.

Kode VBA: Hapus filter dari semua lembar kerja di buku kerja saat ini

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. tekan F5 kunci untuk menjalankan kode. Kemudian semua filter dihapus dari semua lembar kerja di buku kerja saat ini.

8.4 Hapus semua filter di lembar kerja

Metode di atas hanya membantu menghapus status yang difilter, dan filter masih tetap ada di lembar kerja. Jika Anda ingin menghapus semua filter dari lembar kerja, coba metode di bawah ini.

Hapus semua filter di lembar kerja dengan mematikan Filter

Klik Data > Filter untuk mematikan fitur (tombol Filter tidak dalam status sorotan).

Hapus semua filter di lembar kerja dengan tombol pintas

Selain itu, Anda dapat menerapkan tombol pintas untuk menghapus semua filter di lembar kerja.

Di lembar kerja berisi filter yang ingin Anda hapus, tekan Ctrl + perubahan + L kunci secara bersamaan.

Kemudian semua filter di lembar kerja saat ini segera dihapus.