Cara Memfilter Data di Excel
Baru-baru ini saya menulis artikel tentang cara menggunakan fungsi ringkasan di Excel untuk dengan mudah merangkum sejumlah besar data, tetapi artikel itu memperhitungkan semua data pada lembar kerja. Bagaimana jika Anda hanya ingin melihat subkumpulan data dan meringkas subkumpulan data?
Di Excel, Anda bisa membuat filter pada kolom yang akan menyembunyikan baris yang tidak cocok dengan filter Anda. Selain itu, Anda juga bisa menggunakan fungsi khusus di Excel untuk meringkas data hanya menggunakan data yang difilter.
Dalam artikel ini, saya akan memandu Anda melalui langkah-langkah untuk membuat filter di Excel dan juga menggunakan fungsi bawaan untuk meringkas bahwa data yang difilter.
Buat Filter Sederhana di Excel
Di Excel, Anda bisa membuat filter sederhana dan filter kompleks. Mari kita 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, tetapi membuat bekerja dengan filter sedikit lebih mudah.
Di atas, saya punya beberapa data palsu dan saya ingin membuat filter di Kota kolom. Di Excel, ini sangat mudah dilakukan. Silakan dan klik pada Data tab di pita dan kemudian klik pada Filter tombol. Anda tidak harus memilih data pada lembar atau mengklik di baris pertama.
Ketika Anda mengklik Filter, setiap kolom di baris pertama secara otomatis akan memiliki tombol dropdown kecil yang ditambahkan di paling kanan.
Sekarang maju dan klik panah dropdown di kolom Kota. Anda akan melihat beberapa opsi berbeda, yang akan saya jelaskan di bawah ini.
Di bagian atas, Anda dapat dengan cepat mengurutkan semua baris berdasarkan nilai di kolom Kota. Perhatikan bahwa saat 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, merupakan ide bagus untuk menambahkan kolom di bagian paling depan yang disebut ID dan beri nomor dari satu ke berapa banyak baris yang Anda miliki di lembar kerja Anda. Dengan cara ini, Anda selalu dapat mengurutkan berdasarkan kolom ID dan mendapatkan data Anda kembali dalam urutan yang sama seperti aslinya, jika itu penting bagi Anda.
Seperti yang Anda lihat, semua data dalam spreadsheet sekarang diurutkan berdasarkan nilai-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.
Saya pergi ke depan dan tidak memeriksa dua kota dan meninggalkan satu untuk diperiksa. Sekarang saya hanya memiliki 8 baris data yang ditampilkan dan sisanya disembunyikan. Anda dapat dengan mudah memberi tahu Anda sedang melihat data yang difilter jika Anda memeriksa nomor baris di 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 lebih mengurangi jumlah hasil. Di kolom C, saya memiliki jumlah anggota di setiap keluarga dan saya hanya ingin melihat hasilnya untuk keluarga dengan lebih dari dua anggota.
Silakan dan klik 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 mengklik Filter Angka dan kemudian klik Lebih besar dari. Seperti yang Anda lihat, ada banyak opsi lain juga.
Dialog baru akan muncul dan di sini Anda dapat mengetikkan nilai untuk filter. Anda juga dapat menambahkan lebih dari satu kriteria dengan fungsi AND atau OR. Anda bisa mengatakan Anda ingin baris yang nilainya lebih besar dari 2 dan tidak sama dengan 5, misalnya.
Sekarang saya hanya memiliki 5 baris data: keluarga saja dari New Orleans dan dengan 3 anggota atau lebih. Cukup mudah? Perhatikan bahwa Anda dapat dengan mudah menghapus filter pada kolom dengan mengklik dropdown dan kemudian mengklik Hapus Filter Dari “Nama Kolom” link.
Jadi itu tentang filter sederhana di Excel. Mereka sangat mudah digunakan dan hasilnya sangat mudah. Sekarang mari kita lihat filter kompleks menggunakan Maju menyaring dialog.
Buat Filter Tingkat Lanjut di Excel
Jika Anda ingin membuat filter lebih lanjut, Anda harus menggunakan Maju dialog filter. Sebagai contoh, katakanlah saya ingin melihat semua keluarga yang tinggal di New Orleans dengan lebih dari 2 anggota dalam keluarga mereka ATAU semua keluarga di Clarksville dengan lebih dari 3 anggota dalam keluarga mereka DAN hanya yang dengan .EDU mengakhiri alamat email. Sekarang Anda tidak dapat melakukannya dengan filter sederhana.
Untuk melakukan ini, kita perlu mengatur lembar Excel sedikit berbeda. Silakan dan masukkan beberapa baris di atas set data Anda dan salin label pos ke baris pertama seperti yang ditunjukkan di bawah ini.
Sekarang, inilah cara kerja filter lanjutan. Anda harus terlebih dahulu mengetik kriteria Anda di kolom di atas dan kemudian klik Maju tombol di bawah Sortir & Filter di Data tab.
Jadi, apa tepatnya yang bisa kita ketik ke dalam sel-sel itu? OK, jadi mari kita mulai dengan contoh kita. Kami hanya ingin melihat data dari New Orleans atau Clarksville, jadi mari kita ketik itu ke dalam sel E2 dan E3.
Saat Anda mengetik nilai pada baris berbeda, itu artinya ATAU. Sekarang kami ingin keluarga New Orleans dengan lebih dari dua anggota dan keluarga Clarksville dengan lebih dari 3 anggota. Untuk melakukan ini, ketik > 2 di C2 dan > 3 di C3.
Karena> 2 dan New Orleans berada di baris yang sama, itu akan menjadi operator DAN. Hal yang sama berlaku untuk baris 3 di atas. Akhirnya, kami hanya ingin keluarga dengan .EDU mengakhiri alamat email. Untuk melakukannya, cukup ketik * .edu menjadi D2 dan D3. Simbol * berarti sejumlah karakter.
Setelah Anda melakukannya, klik di mana saja di dataset Anda dan kemudian klik pada Maju tombol. Itu Daftar RangBidang e akan secara otomatis mengetahui dataset Anda karena Anda mengkliknya sebelum mengklik tombol Advanced. Sekarang klik pada tombol kecil kecil di sebelah kanan Rentang kriteria tombol.
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!
Seperti yang Anda lihat, sekarang saya hanya memiliki 3 hasil yang cocok dengan semua kriteria itu. Perhatikan bahwa label untuk rentang kriteria harus sama persis dengan label untuk dataset agar ini berfungsi.
Anda jelas dapat membuat kueri yang jauh lebih rumit menggunakan metode ini, jadi bermainlah dengannya untuk mendapatkan hasil yang diinginkan. Akhirnya, mari kita bicara tentang menerapkan fungsi penjumlahan ke data yang difilter.
Meringkas Data yang Difilter
Sekarang katakanlah saya ingin meringkas jumlah anggota keluarga pada data yang difilter saya, bagaimana saya bisa melakukan itu? Baiklah, mari bersihkan filter kami dengan mengklik Bersih tombol di pita. Jangan khawatir, sangat mudah untuk menerapkan filter lanjutan lagi hanya dengan mengklik tombol Advanced dan mengklik OK lagi.
Di bagian bawah dataset kami, mari tambahkan sel bernama Total dan kemudian menambahkan fungsi penjumlahan untuk menjumlahkan total anggota keluarga. Dalam contoh saya, saya baru saja mengetik = SUM (C7: C31).
Jadi jika saya melihat semua keluarga, saya memiliki total 78 anggota. Sekarang, mari kita lanjutkan dan menerapkan kembali filter Advanced kami dan lihat apa yang terjadi.
Aduh! Alih-alih menunjukkan angka yang benar, 11, saya masih melihat jumlahnya 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 kami menggunakan salah satu dari fungsi khusus ini, Anda akan ingin menghapus filter Anda dan kemudian ketik fungsinya.
Setelah filter dihapus, silakan dan ketik = SUBTOTAL ( dan Anda akan melihat kotak dropdown muncul dengan banyak opsi. Menggunakan fungsi ini, Anda terlebih dahulu memilih jenis fungsi penjumlahan yang ingin Anda gunakan menggunakan angka.
Dalam contoh kita, saya ingin menggunakan JUMLAH, jadi saya akan mengetikkan angka 9 atau cukup klik dari dropdown. Kemudian ketik koma dan pilih rentang sel.
Ketika Anda menekan enter, Anda akan melihat nilai 78 sama dengan sebelumnya. Namun, jika sekarang Anda menerapkan filter lagi, kita akan melihat 11!
Luar biasa! Itulah yang kami inginkan. Sekarang Anda dapat menyesuaikan filter dan nilainya akan selalu mencerminkan hanya baris yang saat ini ditampilkan.
Fungsi kedua yang berfungsi hampir sama persis dengan fungsi SUBTOTAL adalah AGREGAT. Satu-satunya perbedaan adalah bahwa ada parameter lain dalam fungsi AGGREGATE di mana Anda harus menentukan bahwa Anda ingin mengabaikan baris tersembunyi.
Parameter pertama adalah fungsi penjumlahan yang ingin Anda gunakan dan seperti dengan SUBTOTAL, 9 mewakili fungsi SUM. Opsi 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 tentang fungsi ringkasan untuk mempelajari cara menggunakan 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. Nikmati!
