Cara Menapis Data dalam Excel

Baru-baru ini saya menulis artikel mengenai cara menggunakan fungsi ringkasan di Excel untuk mudah meringkaskan sejumlah besar data, tetapi artikel itu mengambil kira semua data pada lembaran kerja. Bagaimana jika anda hanya mahu melihat subset data dan meringkaskan subset data?

Dalam Excel, anda boleh membuat penapis pada lajur yang akan menyembunyikan baris yang tidak sepadan dengan penapis anda. Di samping itu, anda juga boleh menggunakan fungsi khas dalam Excel untuk meringkaskan data dengan menggunakan data yang ditapis sahaja.

Dalam artikel ini, saya akan memandu anda melalui langkah-langkah untuk membuat penapis dalam Excel dan juga menggunakan fungsi terbina dalam untuk meringkaskan data yang ditapis.

Buat Penapis Mudah di Excel

Dalam Excel, anda boleh membuat penapis mudah dan penapis kompleks. Mari bermula dengan penapis mudah. Apabila bekerja dengan penapis, anda harus sentiasa mempunyai satu baris di bahagian atas yang digunakan untuk label. Ia bukan keperluan untuk mempunyai baris ini, tetapi ia membuat kerja dengan penapis sedikit lebih mudah.

Di atas, saya mempunyai beberapa data palsu dan saya ingin membuat penapis pada City kolum. Dalam Excel, ini sangat mudah dilakukan. Teruskan dan klik pada Data tab dalam reben dan kemudian klik pada Penapis butang. Anda tidak perlu memilih data pada helaian atau klik pada baris pertama sama ada.

Apabila anda mengklik pada Penapis, setiap lajur dalam barisan pertama akan secara automatik mempunyai butang jatuh turun kecil yang ditambahkan pada bahagian yang betul.

Kini teruskan dan klik pada anak panah lungsur di lajur Kota. Anda akan melihat beberapa pilihan yang berbeza, yang akan saya jelaskan di bawah.

Di bahagian atas, anda boleh dengan cepat menyusun semua baris dengan nilai dalam lajur Bandaraya. Ambil perhatian bahawa apabila anda menyusun data, ia akan menggerakkan keseluruhan baris, bukan hanya nilai dalam lajur Bandaraya. Ini akan memastikan data anda tetap utuh seperti yang telah berlaku sebelum ini.

Juga, adalah idea yang baik untuk menambah lajur di bahagian hadapan yang dipanggil ID dan nombor dari satu hingga ke mana-mana banyak baris yang anda ada dalam lembaran kerja anda. Dengan cara ini, anda sentiasa boleh menyusun mengikut lajur ID dan mendapatkan data anda kembali mengikut susunan yang sama pada asalnya, jika itu penting kepada anda.

Seperti yang anda dapat lihat, semua data dalam hamparan kini diisih berdasarkan nilai dalam lajur Bandaraya. Setakat ini, tiada baris tersembunyi. Sekarang mari kita lihat di kotak pilihan di bahagian bawah dialog penapis. Dalam contoh saya, saya hanya mempunyai tiga nilai unik dalam lajur City dan ketiga-tiga itu muncul dalam senarai.

Saya pergi ke hadapan dan tidak memeriksa dua bandar dan meninggalkan orang yang diperiksa. Sekarang saya hanya mempunyai 8 baris data yang menunjukkan dan selebihnya tersembunyi. Anda boleh dengan mudah memberitahu anda melihat data yang ditapis jika anda menyemak nombor baris di sebelah kiri. Bergantung kepada berapa banyak baris yang tersembunyi, anda akan melihat beberapa garisan mendatar tambahan dan warna nombor akan berwarna biru.

Sekarang katakan saya mahu menapis pada lajur kedua untuk mengurangkan lagi jumlah keputusan. Dalam ruang C, saya mempunyai jumlah ahli dalam setiap keluarga dan saya hanya mahu melihat hasil untuk keluarga yang mempunyai lebih daripada dua orang ahli.

Teruskan dan klik pada anak panah lungsur dalam Kolum C dan anda akan melihat kotak semak yang sama untuk setiap nilai unik dalam lajur. Walau bagaimanapun, dalam kes ini, kami mahu klik Penapis Bilangan dan kemudian klik Lebih besar daripada. Seperti yang anda dapat lihat, ada banyak pilihan lain juga.

Dialog baru akan muncul dan di sini anda boleh menaip nilai untuk penapis. Anda juga boleh menambah lebih daripada satu kriteria dengan fungsi OR atau ATAU. Anda boleh mengatakan bahawa anda mahu baris di mana nilai lebih besar daripada 2 dan tidak sama dengan 5, contohnya.

Kini saya hanya mempunyai 5 baris data: keluarga hanya dari New Orleans dan dengan 3 atau lebih ahli. Cukup mudah? Ambil perhatian bahawa anda boleh dengan mudah menghapuskan penapis pada lajur dengan mengklik lungsur turun dan kemudian mengklik Kosongkan Penapis Dari "Nama Lajur" pautan.

Jadi itu sahaja untuk penapis mudah dalam Excel. Mereka sangat mudah digunakan dan hasilnya cukup lurus ke hadapan. Sekarang mari kita lihat penapis kompleks menggunakan Advanced dialog penapis.

Buat Advanced Filter dalam Excel

Jika anda ingin membuat penapis yang lebih maju, anda perlu menggunakannya Advanced dialog penapis. Sebagai contoh, katakan saya ingin melihat semua keluarga yang tinggal di New Orleans dengan lebih daripada 2 ahli dalam keluarga mereka ATAU semua keluarga di Clarksville dengan lebih daripada 3 ahli dalam keluarga mereka DAN hanya yang mempunyai a .EDU menamatkan alamat e-mel. Sekarang anda tidak boleh melakukannya dengan penapis mudah.

Untuk melakukan ini, kita perlu membuat persediaan lembaran Excel sedikit berbeza. Teruskan dan masukkan beberapa baris di atas set data anda dan salin label tajuk tepat ke baris pertama seperti ditunjukkan di bawah.

Kini di sini ialah bagaimana penapis canggih berfungsi. Anda perlu terlebih dahulu menaip kriteria anda ke lajur di bahagian atas dan kemudian klik Advanced butang di bawah Urus & Penapis pada Data tab.

Jadi apa yang sebenarnya boleh kita masukkan ke dalam sel-sel tersebut? OK, jadi mari kita mulakan dengan contoh kami. Kami hanya mahu melihat data dari New Orleans atau Clarksville, jadi mari taipkan mereka ke dalam sel E2 dan E3.

Apabila anda menaip nilai pada baris yang berbeza, itu bermakna ATAU. Sekarang kita mahukan keluarga New Orleans dengan lebih daripada dua orang ahli dan keluarga Clarksville dengan lebih daripada 3 ahli. Untuk melakukan ini, taipkan > 2 dalam C2 dan > 3 dalam C3.

Oleh kerana> 2 dan New Orleans berada pada baris yang sama, ia akan menjadi operator AND. Perkara yang sama berlaku untuk baris 3 di atas. Akhirnya, kami hanya mahu keluarga dengan .EDU menamatkan alamat e-mel. Untuk melakukan ini, cukup masukkan * .edu ke dalam kedua-dua D2 dan D3. Simbol * bermaksud setiap aksara.

Sebaik sahaja anda berbuat demikian, klik di mana-mana dalam dataset anda dan kemudian klik pada Advanced butang. The Senarai Rangbidang akan secara automatik memikirkan dataset anda sejak anda mengklik ke dalamnya sebelum mengklik butang Advanced. Sekarang klik pada butang kecil kecil di sebelah kanan Julat kriteria butang.

Pilih segalanya dari A1 hingga E3 dan kemudian klik pada butang yang sama sekali lagi untuk kembali ke dialog Penapis Lanjutan. Klik OK dan data anda kini hendaklah ditapis!

Seperti yang anda lihat, sekarang saya hanya mempunyai 3 hasil yang sepadan dengan semua kriteria tersebut. Ambil perhatian bahawa label untuk julat kriteria perlu dipadankan dengan tepat dengan label untuk dataset agar ini berfungsi.

Anda jelas dapat membuat pertanyaan yang lebih rumit menggunakan kaedah ini, jadi bermain bersama dengannya untuk mendapatkan hasil yang anda inginkan. Akhir sekali, mari kita bercakap tentang menerapkan fungsi penjumlahan kepada data yang ditapis.

Meringkaskan Data yang Ditapis

Sekarang katakan saya ingin meringkaskan bilangan ahli keluarga pada data yang ditapis saya, bagaimana saya dapat melakukannya? Nah, mari jelaskan penapis kami dengan mengklik pada Jelas butang dalam reben. Jangan bimbang, sangat mudah untuk menggunakan penapis lanjutan dengan hanya mengklik pada butang Lanjutan dan mengklik OK sekali lagi.

Di bahagian bawah dataset kami, mari tambah sel yang dipanggil Jumlah dan kemudian tambah fungsi jumlah untuk meringkaskan jumlah ahli keluarga. Dalam contoh saya, saya hanya menaip = SUM (C7: C31).

Jadi jika saya melihat semua keluarga, saya mempunyai 78 ahli. Kini mari kita teruskan dan gunakan semula penapis Advanced kami dan lihat apa yang berlaku.

Whoops! Daripada menunjukkan nombor yang betul, 11, saya masih melihat jumlahnya ialah 78! Kenapa begitu? Nah, fungsi SUM tidak mengabaikan baris tersembunyi, jadi masih melakukan pengiraan menggunakan semua baris. Untungnya, ada beberapa fungsi yang boleh anda gunakan untuk mengabaikan baris tersembunyi.

Yang pertama ialah JUMLAH KECIL. Sebelum kita menggunakan mana-mana fungsi khas ini, anda akan mahu membersihkan penapis anda dan kemudian taipkan fungsi tersebut.

Setelah penapis dihapus, teruskan dan taipkan = SUBTOTAL ( dan anda harus melihat kotak jatuh turun muncul dengan banyak pilihan. Menggunakan fungsi ini, anda mula memilih jenis fungsi penjumlahan yang anda gunakan menggunakan nombor.

Dalam contoh kami, saya mahu menggunakannya SUM, jadi saya akan menaip nombor 9 atau hanya klik padanya dari dropdown. Kemudian taipkan koma dan pilih julat sel.

Apabila anda menekan masuk, anda harus melihat nilai 78 adalah sama seperti sebelumnya. Walau bagaimanapun, jika anda menggunakan penapis semula, kami akan melihat 11!

Cemerlang! Itulah yang kita mahu. Sekarang anda boleh menyesuaikan penapis anda dan nilai akan sentiasa mencerminkan hanya baris yang sedang dipaparkan.

Fungsi kedua yang berfungsi hampir sama persis dengan fungsi SUBTOTAL AGGREGATE. Satu-satunya perbezaan ialah terdapat satu lagi parameter dalam fungsi AGGREGATE di mana anda perlu menentukan bahawa anda mahu mengabaikan baris tersembunyi.

Parameter pertama adalah fungsi penjumlahan yang anda mahu gunakan dan seperti SUBTOTAL, 9 mewakili fungsi SUM. Pilihan kedua adalah di mana anda perlu mengetik 5 untuk mengabaikan baris tersembunyi. Parameter terakhir adalah sama dan adalah rangkaian sel.

Anda juga boleh membaca artikel saya mengenai fungsi ringkasan untuk mengetahui cara menggunakan fungsi AGGREGATE dan fungsi-fungsi lain seperti MODE, MEDIAN, AVERAGE, dll. Lebih terperinci.

Mudah-mudahan, artikel ini memberi anda titik permulaan yang baik untuk membuat dan menggunakan penapis dalam Excel. Sekiranya anda mempunyai sebarang soalan, jangan lepaskan komen. Nikmati!