Saya baru-baru ini menulis artikel tentang cara menggunakan fungsi ringkasan di Excel untuk dengan mudah meringkas 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 dapat 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 data yang difilter.
Di Excel, Anda dapat 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 itu 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 lembaran atau klik di baris pertama.
Saat Anda mengeklik Filter, setiap kolom di baris pertama secara otomatis akan memiliki tombol tarik-turun kecil yang ditambahkan di bagian paling kanan.
Sekarang lanjutkan dan klik pada panah dropdown di kolom Kota. Anda akan melihat beberapa opsi berbeda, yang akan saya jelaskan di bawah.
Di bagian atas, Anda dapat dengan cepat menyortir semua baris berdasarkan 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 lembar kerja Anda. Dengan cara ini, Anda selalu dapat mengurutkan berdasarkan kolom ID dan mendapatkan data Anda kembali dalam urutan yang sama pada awalnya, jika itu penting bagi Anda.
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.
Saya pergi ke depan dan tidak terkendali dua kota dan meninggalkan satu 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 ekstra dan warna bilangan akan menjadi biru.
Sekarang katakanlah saya ingin memfilter 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.
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 mengklik Filter Jumlah 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 DAN atau OR. Anda bisa mengatakan Anda ingin baris di mana nilainya lebih besar dari 2 dan tidak sama dengan 5, misalnya.
Sekarang saya hanya sampai 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 Hapus Filter Dari “Nama Kolom” link.
Jadi itu saja untuk filter sederhana di Excel. Mereka sangat mudah digunakan dan hasilnya cukup lurus ke depan. Sekarang mari kita lihat filter kompleks menggunakan Maju filter dialog.
Jika Anda ingin membuat filter yang lebih canggih, Anda harus menggunakan Maju filter dialog. Sebagai contoh, katakanlah saya ingin melihat semua keluarga yang tinggal di New Orleans dengan lebih dari 2 anggota di keluarga mereka ATAU semua keluarga di Clarksville dengan lebih dari 3 anggota di keluarga mereka DAN hanya yang dengan .EDU mengakhiri alamat email. Sekarang Anda tidak bisa 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.
Sekarang di sini adalah cara kerja filter lanjutan. Anda harus terlebih dahulu memasukkan kriteria Anda ke kolom di bagian atas dan kemudian klik Maju tombol di bawah Urutkan & Filter pada Data tab.
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 ke dalam sel E2 dan E3.
Saat Anda mengetik nilai pada baris berbeda, artinya OR. 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 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 * .edu ke dalam D2 dan D3. Simbol * berarti sejumlah karakter.
Setelah Anda melakukannya, klik di mana saja dalam kumpulan data Anda dan kemudian klik pada Maju tombol. Itu Daftar BerderingField e akan secara otomatis mencari tahu dataset Anda sejak Anda mengkliknya sebelum mengklik tombol Advanced.Sekarang klik tombol kecil kecil di sebelah kanan Kisaran kriteria tombol.
Pilih semuanya dari A1 ke 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 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 bermain-main dengannya untuk mendapatkan hasil yang Anda inginkan. Akhirnya, mari kita bicara tentang penerapan fungsi penjumlahan ke data yang difilter.
Sekarang katakanlah saya ingin menjumlahkan jumlah anggota keluarga pada data yang difilter, bagaimana cara saya melakukannya? Nah, mari kita bersihkan filter kita dengan mengklik pada 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 kumpulan data kami, mari tambahkan sel yang bernama Total dan kemudian tambahkan fungsi penjumlahan untuk menjumlahkan total anggota keluarga. Dalam contoh saya, saya hanya mengetik = SUM (C7: C31).
Jadi jika saya melihat semua keluarga, saya memiliki total 78 anggota. Sekarang mari kita lanjutkan dan aplikasikan kembali filter Advanced kami dan lihat apa yang terjadi.
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 banyak opsi. Dengan menggunakan fungsi ini, Anda terlebih dahulu memilih jenis fungsi penjumlahan yang ingin Anda gunakan menggunakan angka.
Dalam contoh kami, saya ingin menggunakannya JUMLAH, jadi saya akan mengetikkan angka 9 atau klik saja dari dropdown. Kemudian ketik koma dan pilih rentang sel.
Ketika Anda menekan enter, Anda harus melihat nilai 78 sama seperti sebelumnya. Namun, jika Anda sekarang menerapkan filter lagi, kita akan melihat 11!
Luar biasa! Itulah yang kami inginkan. Sekarang Anda dapat menyesuaikan filter Anda dan nilainya akan selalu mencerminkan hanya baris yang saat ini ditampilkan.
Fungsi kedua yang berfungsi hampir sama persis dengan fungsi SUBTOTAL 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 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 tentang fungsi ringkasan untuk mempelajari cara menggunakan fungsi AGGREGATE dan fungsi lain seperti MODE, MEDIAN, AVERAGE, dll. Secara lebih terperinci.
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!
