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.
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.
Ketika Anda mengklik pada Filter, setiap kolom di baris pertama secara otomatis akan memiliki tombol drop-down 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 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.
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 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.
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.
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 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".
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.
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.
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.
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.
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.
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
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 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.
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).
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.
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.
Ketika Anda menekan enter, Anda akan 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 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.
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!