Cara Menyaring Data di Excel


Saya baru-baru ini menulis artikel di cara menggunakan fungsi ringkasan di Excel untuk meringkas sejumlah besar data dengan mudah, tetapi artikel itu memperhitungkan semua data pada lembar kerja. Bagaimana jika Anda hanya ingin melihat subkumpulan data dan meringkas subkumpulan data?

Di Excel, Anda dapat membuat filter pada kolom yang akan menyembunyikan baris yang tidak cocok dengan filter Anda. Selain itu, Anda juga dapat menggunakan fungsi khusus di Excel untuk meringkas data hanya menggunakan data yang difilter.

Pada artikel ini, saya akan memandu Anda melalui langkah-langkah untuk membuat filter di Excel dan juga menggunakan built-in berfungsi untuk meringkas data yang difilter.

Membuat Filter Sederhana di Excel

Di Excel, Anda dapat membuat filter sederhana dan filter yang rumit. Mari mulai dengan filter sederhana. Saat bekerja dengan filter, Anda harus selalu memiliki satu baris di bagian atas yang digunakan untuk label. Ini bukan persyaratan untuk memiliki baris ini, tapi itu membuat bekerja dengan filter sedikit lebih mudah. ​​

sample data excel

Di atas, saya punya beberapa data palsu dan saya ingin membuat filter di kolom Kota. Di Excel, ini sangat mudah dilakukan. Lanjutkan dan klik tab Datadi pita lalu klik tombol Filter. Anda tidak harus memilih data pada lembar atau klik di baris pertama.

excel data filter

Ketika Anda mengklik pada Filter, setiap kolom di baris pertama secara otomatis akan memiliki tombol drop-down kecil yang ditambahkan di bagian paling kanan.

added filter excel

Sekarang lanjutkan dan klik pada panah dropdown di Kolom kota. Anda akan melihat beberapa opsi berbeda, yang akan saya jelaskan di bawah.

filter options excel

Di bagian atas, Anda dapat dengan cepat menyortir semua baris dengan nilai-nilai di kolom Kota. Perhatikan bahwa ketika Anda mengurutkan data, itu akan memindahkan seluruh baris, bukan hanya nilai di kolom Kota. Ini akan memastikan bahwa data Anda tetap utuh seperti sebelumnya.

Juga, itu ide yang baik untuk menambahkan kolom di depan yang disebut ID dan nomor itu dari satu ke berapa banyak baris yang Anda miliki di Anda lembar kerja. Dengan cara ini, Anda selalu dapat mengurutkan berdasarkan kolom ID dan mengembalikan data Anda dalam urutan yang sama seperti semula, jika itu penting bagi Anda.

data sorted excel

Seperti yang Anda lihat, semua data dalam spreadsheet sekarang diurutkan berdasarkan nilai di kolom Kota. Sejauh ini, tidak ada baris yang disembunyikan. Sekarang mari kita lihat kotak centang di bagian bawah dialog filter. Dalam contoh saya, saya hanya memiliki tiga nilai unik di kolom Kota dan ketiganya muncul dalam daftar.

filtered rows excel

Saya melanjutkan dan tidak dicentang dua kota dan satu kiri diperiksa. Sekarang saya hanya memiliki 8 baris data yang menunjukkan dan sisanya disembunyikan. Anda dapat dengan mudah mengatakan bahwa Anda melihat data yang difilter jika Anda memeriksa nomor baris di bagian paling kiri. Bergantung pada berapa banyak baris yang disembunyikan, Anda akan melihat beberapa garis horizontal tambahan dan warna angka akan berwarna biru.

Sekarang katakanlah saya ingin memfilter pada kolom kedua untuk mengurangi jumlah hasil. Di kolom C, saya memiliki jumlah anggota di setiap keluarga dan saya ingin hanya melihat hasil untuk keluarga dengan lebih dari dua anggota.

number filter excel

Lanjutkan dan klik pada panah dropdown di Kolom C dan Anda akan melihat kotak centang yang sama untuk setiap nilai unik di kolom. Namun, dalam hal ini, kami ingin mengeklik Filter Angkadan kemudian mengeklik Lebih Besar dari. Seperti yang Anda lihat, ada banyak opsi lain juga.

is greater than filter

Dialog baru akan muncul dan di sini Anda dapat mengetikkan nilai untuk filter. Anda juga dapat menambahkan lebih dari satu kriteria dengan fungsi DAN atau OR. Anda bisa mengatakan Anda ingin baris di mana nilainya lebih besar dari 2 dan tidak sama dengan 5, misalnya.

two filters excel

Sekarang saya turun ke hanya 5 baris data: keluarga hanya dari New Orleans dan dengan 3 atau lebih anggota. Cukup mudah? Perhatikan bahwa Anda dapat dengan mudah menghapus filter pada kolom dengan mengklik dropdown dan kemudian mengklik tautan Hapus Filter Dari "Nama Kolom".

clear filter excel

Jadi itu saja untuk filter sederhana di Excel. Mereka sangat mudah digunakan dan hasilnya cukup lurus ke depan. Sekarang mari kita lihat filter yang rumit menggunakan dialog filter Advanced.

Membuat Filter Tingkat Lanjut di Excel

Jika Anda ingin membuat filter yang lebih canggih, Anda harus menggunakan dialog filter Advanced. Misalnya, katakanlah saya ingin melihat semua keluarga yang tinggal di New Orleans dengan lebih dari 2 anggota di keluarga mereka ATAUsemua keluarga di Clarksville dengan lebih dari 3 anggota dalam keluarga mereka DANhanya yang memiliki alamat email akhir .EDU. Sekarang Anda tidak dapat melakukannya dengan filter sederhana.

Untuk melakukan ini, kita perlu men-setup lembar Excel sedikit berbeda. Lanjutkan dan masukkan beberapa baris di atas kumpulan data Anda dan salin label judul tepat ke baris pertama seperti yang ditunjukkan di bawah ini.

advanced filter setup

Sekarang di sini adalah cara kerja filter lanjutan. Anda harus terlebih dahulu memasukkan kriteria Anda ke kolom di bagian atas dan kemudian klik tombol Lanjutandi bawah Urutkan & amp; Filterpada tab Data.

advanced filter ribbon

Jadi apa sebenarnya yang bisa kita ketik ke dalam sel-sel itu? Oke, jadi mari kita mulai dengan contoh kita. Kami hanya ingin melihat data dari New Orleans atau Clarksville, jadi mari kita ketik mereka ke dalam sel E2 dan E3.

advanced filter city

Ketika Anda mengetik nilai pada berbagai baris, itu berarti OR. Sekarang kami ingin keluarga New Orleans dengan lebih dari dua anggota dan keluarga Clarksville dengan lebih dari 3 anggota. Untuk melakukan ini, ketik & gt; 2di C2 dan & gt; 3di C3.

advanced filters excel

Sejak & gt; 2 dan New Orleans berada di baris yang sama, ini akan menjadi operator AND. Hal yang sama berlaku untuk baris 3 di atas. Akhirnya, kami hanya ingin keluarga dengan .EDU mengakhiri alamat email. Untuk melakukan ini, cukup ketik *. Eduke D2 dan D3. Simbol * berarti berapa pun jumlah karakter.

criteria range excel

Setelah Anda melakukannya, klik di mana saja dalam kumpulan data Anda dan kemudian klik pada Lanjutan. Bidang Daftar Berkumpule akan secara otomatis mencari tahu dataset Anda sejak Anda mengkliknya sebelum mengklik tombol Advanced. Sekarang klik tombol kecil kecil di sebelah kanan tombol Kisaran kriteria

select criteria range

Pilih semuanya dari A1 hingga E3 dan kemudian klik pada tombol yang sama lagi untuk kembali ke dialog Filter Tingkat Lanjut. Klik OK dan data Anda sekarang harus difilter!

filter results

Seperti yang Anda lihat, sekarang saya hanya memiliki 3 hasil yang cocok dengan semua kriteria tersebut. Perhatikan bahwa label untuk rentang kriteria harus sama persis dengan label untuk dataset agar ini berfungsi.

Anda jelas dapat membuat lebih banyak pertanyaan rumit menggunakan metode ini, jadi main-main dengannya untuk mendapatkan hasil yang Anda inginkan. Akhirnya, mari kita bicara tentang penerapan fungsi penjumlahan ke data yang difilter.

Meringkas Data Yang Difilter

Sekarang katakanlah saya ingin menjumlahkan jumlah anggota keluarga pada data yang difilter, bagaimana saya tentang melakukan itu? Nah, mari kita bersihkan filter kami dengan mengklik tombol Hapusdi pita. Jangan khawatir, sangat mudah untuk menerapkan filter lanjutan lagi hanya dengan mengklik tombol Advanced dan mengklik OK lagi.

clear filter in excel

Pada di bagian bawah kumpulan data kami, mari tambahkan sel bernama Totaldan kemudian tambahkan fungsi penjumlahan untuk menjumlahkan total anggota keluarga. Dalam contoh saya, saya hanya mengetik = SUM (C7: C31).

sum total excel

Jadi jika saya melihat semuanya keluarga, saya memiliki total 78 anggota. Sekarang, mari lanjutkan dan aplikasikan kembali filter Lanjutan kami dan lihat apa yang terjadi.

wrong total filter

Ups! Alih-alih menunjukkan angka yang benar, 11, saya masih melihat totalnya adalah 78! Mengapa demikian? Nah, fungsi SUM tidak mengabaikan baris tersembunyi, jadi masih melakukan perhitungan menggunakan semua baris. Untungnya, ada beberapa fungsi yang dapat Anda gunakan untuk mengabaikan baris tersembunyi.

Yang pertama adalah SUBTOTAL. Sebelum kita menggunakan salah satu fungsi khusus ini, Anda akan ingin menghapus filter Anda dan kemudian ketik fungsi.

Setelah filter dihapus, lanjutkan dan ketik = SUBTOTAL (dan Anda akan melihat kotak dropdown muncul dengan sekelompok opsi. Dengan menggunakan fungsi ini, Anda terlebih dahulu memilih jenis fungsi penjumlahan yang ingin Anda gunakan menggunakan angka.

Dalam contoh kita, saya ingin menggunakan SUM, jadi saya akan mengetikkan angka 9 atau cukup klik pada itu dari dropdown. Kemudian ketik koma dan pilih rentang sel.

subtotal function

Ketika Anda menekan enter, Anda akan melihat nilai 78 sama seperti sebelumnya . Namun, jika Anda sekarang menerapkan filter lagi, kita akan melihat 11!

subtotal on filter

Luar biasa! Itulah yang kami inginkan. Sekarang Anda dapat menyesuaikan filter Anda dan nilainya akan selalu mencerminkan hanya baris yang sedang ditampilkan.

Fungsi kedua yang berfungsi hampir sama persis dengan fungsi SUBTOTAL adalah AGGREGATE. Satu-satunya perbedaan adalah bahwa ada parameter lain dalam fungsi AGGREGATE di mana Anda harus menentukan bahwa Anda ingin mengabaikan baris tersembunyi.

aggregrate function

Yang pertama parameter adalah fungsi penjumlahan yang ingin Anda gunakan dan seperti SUBTOTAL, 9 mewakili fungsi SUM. Pilihan kedua adalah di mana Anda harus mengetikkan 5 untuk mengabaikan baris tersembunyi. Parameter terakhir adalah sama dan rentang sel.

Anda juga dapat membaca artikel saya di fungsi ringkasan untuk mempelajari cara gunakan fungsi AGGREGATE dan fungsi lainnya seperti MODE, MEDIAN, AVERAGE , dll. secara lebih rinci.

Semoga artikel ini memberi Anda titik awal yang baik untuk membuat dan menggunakan filter di Excel. Jika Anda memiliki pertanyaan, jangan ragu untuk mengirim komentar. Selamat menikmati!

Tutorial Ms. Excel CARA FILTERING DATA PADA MS EXCEL

Pos terkait:


27.10.2015