Kadang-kadang apabila bekerja dengan pangkalan data yang besar, ia menjadi perlu untuk memilih maklumat mengikut kriteria tertentu (contohnya, tunjukkan hanya orang yang dilahirkan pada tahun 1980). Untuk melaksanakan tugas seperti ini, gunakan penapis dalam Excel.

Video tentang menapis dan menyusun data dalam Excel

Jenis penapis dalam Excel

Penapisan ialah pemilihan data yang diperlukan daripada senarai untuk kerja berikutnya dengannya. Hasil daripada prosedur ini akan menjadi rentetan tertentu yang memenuhi kriteria pemilihan. Entri selebihnya disembunyikan buat sementara waktu dan tidak digunakan sehingga pengguna melumpuhkan penapis. Anda boleh melakukan tindakan standard dengan data yang dipilih: mengedit, memformat, mencetak, mencipta graf, carta, dsb.

Terdapat 2 kaedah penapisan dalam Excel: Penapis Auto dan Penapis Lanjutan. Anda boleh melancarkannya melalui bar menu dengan mengklik "Data - Penapis". Menggunakan pilihan pertama, pemilihan pantas dijalankan maklumat yang diperlukan dengan kriteria carian mudah. Dalam mod autofilter, baris tajuk jadual dalam setiap lajur akan mengandungi butang dengan anak panah, dengan mengklik di mana anda boleh menentukan kriteria pemilihan. Untuk setiap lajur anda boleh menetapkan tetapan anda sendiri. DALAM mod ini anda boleh menetapkan parameter berikut:

1. Isih mengikut tertib menaik atau menurun.
2. "Semua" - Excel akan menunjukkan (memulihkan) semua baris.
3. 10 Teratas—Excel memaparkan 10 rekod pertama. Apabila anda memilih item ini, tetingkap baharu akan dibuka di mana anda boleh menentukan bilangan rekod, pilih yang mana satu daripada mereka untuk ditunjukkan (paling besar atau terkecil), dan juga menetapkan had pada bilangan rekod yang dipaparkan.
4. "Keadaan" - di sini pengguna boleh membuat 2 kriteria pemilihan data secara bebas, menggabungkannya pengendali mudah DAN, ATAU.
5. Mana-mana unsur. Di sini anda boleh memilih mana-mana nilai yang ada dalam lajur. Contohnya, jika lajur terdiri daripada nama produk, maka semua produk akan ditunjukkan dalam senarai elemen. Pengguna boleh menentukan mana-mana daripada mereka.
6. Kosong dan Bukan Kosong—Excel akan memaparkan sel kosong (atau tidak kosong). Pilihan ini muncul hanya jika terdapat medan kosong dalam lajur.

   Untuk mengalih keluar penapis daripada satu lajur, anda perlu mengklik pada item "Semua" dalam senarai elemen. Jika anda perlu membatalkannya untuk keseluruhan jadual, anda mesti memilih "Data - Penapis - Paparkan semua" dalam bar menu. Mengalih keluar penapis automatik dijalankan dengan cara yang sama seperti melancarkannya.

   Penapis lanjutan

   

   Contoh mencipta penapis lanjutan - anda perlu menentukan julat data sumber, penapis dan julat tempat data yang ditapis diletakkan

   Pilihan penapisan kedua menyediakan pengguna dengan lebih banyak kemungkinan untuk memilih data yang diperlukan. Untuk menjalankan pilihan lanjutan, anda perlu membuat salinan pengepala jadual (iaitu salin sahaja pengepala). Ini akan menjadi julat syarat. Kemudian anda perlu mengisi julat ini dengan kriteria pemilihan. Tetapi di sini adalah penting untuk mematuhi peraturan: jika anda memerlukan nilai untuk dipilih mengikut dua parameter (contohnya, nama keluarga dan gred pelajar), maka syaratnya ditulis dalam satu baris; jika kriteria dipilih dalam mod "ATAU" (buatan kereta atau saiz enjin), maka ia direkodkan dalam baris yang berbeza.

   Katakan terdapat jadual dengan 2 lajur - nama produk dan kuantiti. Terdapat 3 produk secara keseluruhan - pisang, oren, tangerin, dan kuantiti masing-masing 10, 20 dan 15 keping. Selepas pengepala telah disalin, anda boleh membuat syarat, sebagai contoh, untuk menunjukkan produk yang kuantitinya kurang daripada atau sama dengan 15. Iaitu, di bawah pengepala yang disalin dalam lajur "Kuantiti" anda perlu menulis<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).

   Oleh itu, prinsip penapisan harus jelas. Dengan cara ini, anda boleh memilih mana-mana elemen daripada pangkalan data, tidak kira betapa besarnya. Prosedur ini akan membantu memudahkan kerja pengguna dengan jumlah data yang besar.

Tapis dalam Excel - Ini ialah pemilihan data mengikut ciri-ciri tertentu.Penapisan dalam Exceldalam keadaan tertentu dijalankan menggunakan fungsi " Tapis dalam Excel ". Jika terdapat gambar dalam sel jadual, kemudian lihat artikel "Sisipkan gambar ke dalam sel dalam Excel" tentang cara membetulkan gambar dalam sel supaya ia tidak bergerak semasa penapisan.
Bagaimana untuk menapis dalam Excel.
Mula-mula, pilih lajur yang anda perlukan untuk menapis data jadual. Kemudian, pada tab "Rumah", klik butang "Isih dan Tapis" dan pilih fungsi "Penapis". Atau pergi ke tab "Data" dan klik butang "Penapis".
Butang penapis telah muncul di sel atas lajur yang dipilih. Klik pada butang ini dan pilih "Penapis berangka", kemudian "kurang". Dalam tetingkap yang muncul, bertentangan dengan sel "kurang daripada", tulis nombor yang kurang daripada yang kita perlukan untuk memilih data, contohnya, 7. Klik "OK".

Data yang diperlukan kekal dalam lajur jadual.

Nasihat.
Untuk menghalang butang penapis daripada menutup nilai dalam sel atas, anda boleh meletakkan nombor dalam sel di atas dan memilihnya juga, atau hanya pilih sel kosong di atas lajur. Kemudian ikon penapis akan berada dalam sel tambahan ini dan tidak akan meliputi data yang diperlukan.

Tapis mengikut tarikh dalam Excel.
Bagaimana untuk menyediakan jadual dengan betul dan menapis mengikut tarikh, baca artikel "Isih mengikut tarikh dalam Excel".
Tapis mengikut warna sel dalam Excel.
Anda boleh menapis data mengikut warna sel. Sel boleh diwarnakan secara manual atau dengan pemformatan bersyarat. Cara mewarna sel dengan pemformatan bersyarat mengikut nombor, dengan perkataan, mengikut tarikh, dsb., "Pemformatan Bersyarat dalam Excel."
Klik pada warna merah jambu. Klik "OK". Ternyata begini.
Bagaimana pilih hanya sel yang kelihatan dalam Excel Untuk mengetahui cara menampal hanya pada baris yang boleh dilihat dalam Excel, lihat artikel "Tampal ke dalam baris yang boleh dilihat dalam Excel".
Dalam Excel, anda boleh menyediakan sel untuk menunjukkan bilangan baris yang ditapis, sama ada penapis didayakan atau tidak. Ini adalah perlu untuk tidak keliru apabila kita sering menggunakan penapis, atau beberapa orang bekerja dalam jadual yang sama, dsb. Baca lebih lanjut mengenai perkara ini dalam artikel "Cara menyediakan penapis dalam Excel".
Selepas bekerja dengan data yang ditapis (contohnya, mencetak senarai ini), anda boleh mengembalikan jadual kepada bentuk asalnya, iaitu dengan semua data yang tidak ditapis. Klik butang penapis di bahagian atas lajur dan pilih "Pilih semua". Kami kini mempunyai jadual dalam bentuk asalnya.
Cara menyalin data penapis, lihat artikel "Salin data yang ditapis ke Excel" .
boleh alih keluar butang penapis daripada jadual excel. Pada tab "Rumah", klik butang "Isih dan Tapis", kemudian "Penapis".
Atau pergi ke tab "Data" dan klik butang "Penapis". Butang telah hilang.
Jika anda perlu mencetak jadual dengan data yang ditapis supaya nombor baris berada dalam satu baris (1, 2, 3...) atau mengira bilangan baris dan sel yang ditapis, maka ini boleh dikonfigurasikan. Lihat "Nombor jujukan baris selepas penapis dalam Excel."
Bagaimana untuk mengisih data jadual, baca artikel "

Mungkin semua pengguna yang sentiasa bekerja dengan Microsoft Excel tahu tentang fungsi berguna program ini sebagai. Tetapi tidak semua orang tahu bahawa terdapat juga keupayaan lanjutan alat ini. Mari lihat apa yang boleh dilakukan oleh penapis lanjutan Microsoft Excel dan cara menggunakannya.

Untuk memasang penapis lanjutan, pertama sekali, anda perlu membuat jadual tambahan dengan syarat pemilihan. Pengepala jadual ini betul-betul sama dengan jadual utama, yang sebenarnya kami akan tapis.

Sebagai contoh, kami meletakkan jadual tambahan di atas jadual utama dan mewarnakan selnya oren. Walaupun, anda boleh meletakkan jadual ini di mana-mana ruang kosong, malah pada helaian lain.

Sekarang, kami memasukkan ke dalam jadual tambahan data yang perlu ditapis daripada jadual utama. Dalam kes khusus kami, daripada senarai gaji yang dikeluarkan kepada pekerja, kami memutuskan untuk memilih data mengenai kakitangan lelaki utama untuk 25/07/2016.

Menjalankan penapis lanjutan

Hanya selepas jadual tambahan dibuat, anda boleh meneruskan untuk menjalankan penapis lanjutan. Untuk melakukan ini, pergi ke tab "Data", dan pada reben dalam blok alat "Isih dan Penapis", klik pada butang "Lanjutan".

Tetingkap penapis lanjutan terbuka.

Seperti yang anda lihat, terdapat dua mod untuk menggunakan alat ini: "Tapis senarai di tempat", dan "Salin hasil ke lokasi lain". Dalam kes pertama, penapisan akan dilakukan terus dalam jadual sumber, dan dalam kes kedua, secara berasingan dalam julat sel yang anda tentukan sendiri.

Dalam medan Julat Sumber, anda perlu menentukan julat sel dalam jadual sumber. Ini boleh dilakukan secara manual dengan memasukkan koordinat dari papan kekunci, atau dengan memilih julat sel yang dikehendaki menggunakan tetikus. Dalam medan "Julat keadaan", anda perlu sama menunjukkan julat pengepala jadual tambahan dan baris yang mengandungi syarat. Pada masa yang sama, anda perlu memberi perhatian bahawa julat ini tidak mengandungi baris kosong, jika tidak, tiada apa yang akan berfungsi. Selepas semua tetapan selesai, klik pada butang "OK".

Seperti yang anda lihat, hanya nilai yang kami putuskan untuk tapis kekal dalam jadual asal.

Jika anda memilih pilihan untuk mengeluarkan hasil ke lokasi lain, maka dalam medan "Tempatkan hasil dalam julat" anda perlu menentukan julat sel yang mana data yang ditapis akan dikeluarkan. Anda juga boleh menentukan satu sel. Dalam kes ini, ia akan menjadi sel kiri atas jadual baharu. Selepas pemilihan dibuat, klik pada butang "OK".

Seperti yang anda lihat, selepas tindakan ini, jadual sumber kekal tidak berubah dan data yang ditapis dipaparkan dalam jadual berasingan.

Untuk menetapkan semula penapis apabila menggunakan bangunan senarai di tempat, anda perlu mengklik pada butang "Kosongkan" pada reben dalam blok alat "Isih dan Penapis".

Oleh itu, kita boleh membuat kesimpulan bahawa penapis lanjutan menyediakan lebih banyak pilihan daripada penapisan data biasa. Pada masa yang sama, perlu diperhatikan bahawa bekerja dengan alat ini masih kurang mudah daripada dengan penapis standard.

Untuk kemudahan bekerja dengan sejumlah besar data dalam jadual, mereka sentiasa perlu disusun mengikut kriteria tertentu. Di samping itu, untuk memenuhi tujuan tertentu, kadangkala keseluruhan tatasusunan data tidak diperlukan, tetapi hanya baris individu. Oleh itu, untuk tidak keliru dalam jumlah maklumat yang besar, penyelesaian yang rasional adalah dengan menyusun data dan menapisnya daripada hasil lain. Mari ketahui cara mengisih dan menapis data dalam Microsoft Excel.

Isih ialah salah satu alat yang paling mudah apabila bekerja dalam Microsoft Excel. Menggunakannya, anda boleh menyusun baris jadual dalam susunan abjad, mengikut data yang terdapat dalam sel lajur.

Anda boleh mengisih data dalam Microsoft Excel menggunakan butang "Isih dan Penapis", yang terletak dalam tab "Rumah" pada reben dalam blok alat "Mengedit". Tetapi pertama, kita perlu mengklik pada mana-mana sel lajur yang akan kita isih.

Sebagai contoh, dalam jadual di bawah, pekerja hendaklah diisih mengikut abjad. Pergi ke mana-mana sel dalam lajur "Nama" dan klik pada butang "Isih dan Penapis". Untuk mengisih nama mengikut abjad, pilih "Isih daripada A hingga Z" daripada senarai yang dipaparkan.

Seperti yang anda lihat, semua data dalam jadual disusun mengikut senarai abjad nama keluarga.

Untuk mengisih dalam susunan terbalik, dalam menu yang sama, pilih butang Isih dari Z ke A.

Senarai itu dibina semula dalam susunan terbalik.

Perlu diingatkan bahawa jenis pengisihan ini ditunjukkan hanya untuk format data teks. Contohnya, dengan format nombor, pengisihan ditentukan sebagai "Daripada minimum kepada maksimum" (dan sebaliknya), dan dengan format tarikh - "Daripada lama kepada baharu" (dan sebaliknya).

Pengisihan tersuai

Tetapi, seperti yang kita lihat, dengan jenis pengisihan ini mengikut satu nilai, data yang mengandungi nama orang yang sama disusun dalam julat dalam susunan rawak.

Tetapi bagaimana jika kita ingin mengisih nama mengikut abjad, tetapi sebagai contoh, jika nama itu sepadan, pastikan data itu disusun mengikut tarikh? Untuk melakukan ini, serta menggunakan beberapa ciri lain, semuanya dalam menu "Isih dan Penapis" yang sama, kita perlu pergi ke item "Isih tersuai...".

Selepas ini, tetingkap tetapan pengisihan dibuka. Jika jadual anda mempunyai pengepala, maka pastikan bahawa dalam tetingkap ini terdapat tanda semak di sebelah pilihan "Data saya mengandungi pengepala".

Dalam medan "Lajur", nyatakan nama lajur yang mana pengisihan akan dilakukan. Dalam kes kami, ini ialah lajur "Nama". Medan "Isih" menunjukkan jenis kandungan yang akan diisih mengikut. Terdapat empat pilihan:

• Nilai;
• Warna sel;
• Warna fon;
• ikon sel.

Tetapi, dalam kebanyakan kes, item "Nilai" digunakan. Ia ditetapkan secara lalai. Dalam kes kami, kami juga akan menggunakan titik khusus ini.

Dalam lajur "Pesanan" kita perlu menunjukkan dalam susunan data yang akan disusun: "Dari A hingga Z" atau sebaliknya. Pilih nilai "Dari A hingga Z".

Jadi, kami telah menyediakan pengisihan mengikut salah satu lajur. Untuk mengkonfigurasi pengisihan mengikut lajur lain, klik pada butang "Tambah tahap".

Satu lagi set medan muncul, yang mesti diisi untuk mengisih mengikut lajur lain. Dalam kes kami, mengikut lajur "Tarikh". Oleh kerana format tarikh ditetapkan dalam sel ini, dalam medan "Pesanan" kami menetapkan nilai bukan "Dari A hingga Z", tetapi "Daripada lama ke baharu", atau "Daripada baharu kepada lama".

Dengan cara yang sama, dalam tetingkap ini anda boleh mengkonfigurasi, jika perlu, menyusun mengikut lajur lain mengikut keutamaan. Apabila semua tetapan selesai, klik pada butang "OK".

Seperti yang anda lihat, kini dalam jadual kami semua data diisih, pertama sekali, mengikut nama pekerja, dan kemudian mengikut tarikh pembayaran.

Tetapi itu bukan semua kemungkinan pengisihan tersuai. Jika dikehendaki, dalam tetingkap ini anda boleh mengkonfigurasi pengisihan bukan mengikut lajur, tetapi mengikut baris. Untuk melakukan ini, klik pada butang "Pilihan".

Dalam tetingkap pilihan pengisihan yang terbuka, alihkan suis daripada kedudukan "Barisan Julat" ke kedudukan "Lajur Julat". Klik pada butang "OK".

Sekarang, dengan analogi dengan contoh sebelumnya, anda boleh memasukkan data untuk mengisih. Masukkan data dan klik pada butang "OK".

Seperti yang anda lihat, selepas ini, lajur telah bertukar tempat, mengikut parameter yang dimasukkan.

Sudah tentu, untuk jadual kami, diambil sebagai contoh, menggunakan pengisihan dengan menukar lokasi lajur tidak begitu berguna, tetapi untuk beberapa jadual lain jenis pengisihan ini boleh menjadi sangat sesuai.

Penapis

Selain itu, Microsoft Excel mempunyai fungsi penapis data. Ia membolehkan anda membiarkan hanya kelihatan data yang anda anggap perlu, dan menyembunyikan yang lain. Jika perlu, data tersembunyi sentiasa boleh dikembalikan kepada mod boleh dilihat.

Untuk menggunakan fungsi ini, berdiri pada mana-mana sel dalam jadual (sebaik-baiknya dalam pengepala), sekali lagi klik pada butang "Isih dan Penapis" dalam blok alat "Mengedit". Tetapi, kali ini, dalam menu yang muncul, pilih item "Penapis". Anda juga boleh menekan kombinasi kekunci Ctrl+Shift+L dan bukannya tindakan ini.

Seperti yang anda lihat, dalam sel dengan nama semua lajur, ikon muncul dalam bentuk segi empat sama, di mana segi tiga terbalik ditulis.

Klik pada ikon ini dalam lajur yang akan kami tapis. Dalam kes kami, kami memutuskan untuk menapis mengikut nama. Sebagai contoh, kita perlu meninggalkan data hanya pekerja Nikolaev. Oleh itu, kami mengalih keluar tanda semak daripada nama semua pekerja lain.

Apabila prosedur selesai, klik pada butang "OK".

Seperti yang anda lihat, hanya baris dengan nama pekerja Nikolaev kekal di dalam jadual.

Mari kita rumitkan tugas dan tinggalkan dalam jadual hanya data yang berkaitan dengan Nikolaev untuk suku ketiga 2016. Untuk melakukan ini, klik pada ikon dalam sel "Tarikh". Dalam senarai yang terbuka, nyahtanda bulan "Mei", "Jun" dan "Oktober", kerana ia bukan milik suku ketiga, dan klik pada butang "OK".

Seperti yang anda lihat, hanya data yang kami perlukan kekal.

Untuk mengalih keluar penapis untuk lajur tertentu dan menunjukkan data tersembunyi, klik sekali lagi pada ikon yang terletak dalam sel dengan tajuk lajur ini. Dalam menu yang terbuka, klik pada item "Alih keluar penapis daripada ...".

Jika anda ingin menetapkan semula penapis untuk jadual secara keseluruhan, maka anda perlu mengklik butang "Isih dan penapis" pada reben dan pilih item "Kosongkan".

Jika anda perlu mengalih keluar penapis sepenuhnya, maka, seperti semasa memulakannya, anda harus memilih item "Penapis" dalam menu yang sama, atau taip pintasan papan kekunci Ctrl+Shift+L.

Di samping itu, perlu diperhatikan bahawa selepas kami mendayakan fungsi "Penapis", apabila anda mengklik pada ikon yang sepadan dalam sel pengepala jadual, fungsi pengisihan yang kami bincangkan di atas tersedia dalam menu yang muncul: "Isih dari A hingga Z” , “Isih dari Z ke A”, dan “Isih mengikut warna”.

Meja pintar

Pengisihan dan penapisan juga boleh diaktifkan dengan menukar kawasan data yang anda bekerjasama menjadi apa yang dipanggil "jadual pintar".

Terdapat dua cara untuk mencipta jadual pintar. Untuk menggunakan yang pertama, pilih seluruh kawasan jadual, dan, berada dalam tab "Rumah", klik pada butang "Format sebagai jadual" pada reben. Butang ini terletak dalam blok alat "Gaya".

Selepas ini, kotak dialog terbuka di mana anda boleh menukar koordinat jadual. Tetapi, jika sebelum ini anda memilih kawasan dengan betul, maka anda tidak perlu melakukan apa-apa lagi. Perkara utama ialah memastikan terdapat tanda semak di sebelah parameter "Jadual dengan pengepala". Seterusnya, hanya klik pada butang "OK".

Jika anda memutuskan untuk menggunakan kaedah kedua, maka anda juga perlu memilih keseluruhan kawasan jadual, tetapi kali ini pergi ke tab "Sisipkan". Semasa di sini, pada reben dalam blok alat "Jadual", klik pada butang "Jadual".

Selepas ini, seperti kali terakhir, tetingkap akan dibuka di mana anda boleh melaraskan koordinat peletakan jadual. Klik pada butang "OK".

Tidak kira kaedah yang anda gunakan untuk mencipta "jadual pintar", anda akan mendapat jadual dalam sel pengepala yang ikon penapis yang kami terangkan sebelum ini akan dipasang.

Apabila anda mengklik pada ikon ini, semua fungsi yang sama akan tersedia seperti semasa anda melancarkan penapis dengan cara standard melalui butang "Isih dan Penapis".

Seperti yang anda lihat, alat pengisihan dan penapisan, apabila digunakan dengan betul, boleh menjadikan kerja dengan jadual lebih mudah untuk pengguna. Persoalan penggunaannya menjadi sangat relevan jika jadual mengandungi susunan data yang sangat besar.

Jika anda membaca hingga habis, anda akan belajar cara menggunakan fungsi Excel yang berguna seperti penapis. Sekarang, menggunakan contoh yang sangat nyata, saya akan menunjukkan apa itu penapis Excel dan cara menjimatkan masa apabila bekerja dengan jadual besar. Tak susah pun. Pada akhir artikel, anda boleh memuat turun jadual, menggunakan contoh yang saya bincangkan bekerja dengan penapis Excel di sini.

Mengapa kita memerlukan penapis dalam jadual Excel?

Dan kemudian untuk dapat cepat pilih hanya data yang anda perlukan, bersembunyi tidak perlu garisan meja. Oleh itu penapis membenarkan tanpa pemadaman baris jadual Excel hanya menyembunyikannya buat sementara waktu.

Baris jadual yang disembunyikan oleh penapis tidak hilang. Anda boleh bayangkan bahawa ketinggian mereka menjadi sifar (saya sebelum ini bercakap tentang menukar ketinggian baris dan lebar lajur). Oleh itu, garisan selebihnya yang tidak disembunyikan oleh penapis adalah, seolah-olah, "dilekatkan bersama". Apa yang keluar sebagai hasilnya ialah jadual dengan penapis digunakan.