Mungkin, semua pengguna yang selalu bekerja dengan Microsoft Excel mengetahui hal ini fungsi yang berguna program ini sebagai penyaringan data. Namun tidak semua orang menyadari bahwa ada juga fitur lanjutan dari alat ini. Mari kita lihat apa yang dapat dilakukan oleh filter lanjutan Microsoft Excel dan cara menggunakannya.
Tidaklah cukup untuk segera menjalankan filter lanjutan - untuk ini, satu syarat lagi harus dipenuhi. Selanjutnya, kami akan menjelaskan urutan tindakan yang harus diambil.
Langkah 1: Buat tabel dengan kriteria pemilihan
Untuk memasang filter lanjutan, pertama-tama, Anda perlu membuat tabel tambahan dengan ketentuan pemilihan. Topinya persis sama dengan yang utama, yang sebenarnya akan kita saring. Misalnya, kami menempatkan tabel tambahan di atas yang utama dan mewarnai selnya dengan warna oranye. Meskipun Anda dapat menempatkannya di ruang kosong apa pun dan bahkan di lembar lain.
Sekarang kita masuk ke tabel tambahan informasi yang perlu disaring dari tabel utama. Dalam kasus khusus kami, dari daftar gaji yang dikeluarkan untuk karyawan, kami memutuskan untuk memilih data staf utama pria untuk 25/07/2016.
Langkah 2: Menjalankan filter lanjutan
Hanya setelah tabel tambahan dibuat, Anda dapat melanjutkan ke peluncuran filter lanjutan.
Dengan demikian, kita dapat menyimpulkan bahwa filter lanjutan memberikan lebih banyak opsi daripada pemfilteran data biasa. Namun perlu dicatat bahwa bekerja dengan alat ini masih kurang nyaman dibandingkan dengan filter standar.
Penyaringan jelas merupakan salah satu yang paling nyaman dan cara cepat pilih dari daftar data terbesar, persis apa yang dibutuhkan saat ini. Sebagai hasil dari operasi filter, pengguna akan menerima daftar kecil data yang diperlukan, yang sudah dapat digunakan dengan mudah dan tenang. Data ini akan dipilih berdasarkan kriteria tertentu, yang dapat Anda konfigurasikan sendiri. Secara alami, Anda dapat bekerja dengan data yang dipilih, menggunakan sepenuhnya semua fitur Excel 2010 lainnya.
Saat bekerja dengan tabel, data dapat dipilih dengan dua cara - gunakan filter otomatis khusus di Excel, atau fokus pada sekumpulan kecil fungsi dan rumus dasar. Opsi kedua jauh lebih sederhana dan lebih mudah, yang berarti kita akan mulai mengenal kemampuan prosesor spreadsheet yang diperbarui darinya.
Jadi, Anda memiliki tabel dengan larik berbagai data yang Anda terima di tempat kerja, dan sekarang Anda perlu memilih beberapa nilai tertentu. Untungnya, Anda dapat menggunakan AutoFilter Excel, yang hanya menyisakan informasi yang Anda perlukan di layar.
Di sini kita melihat fungsi pemfilteran utama yang terletak di tab Beranda. Anda juga dapat melihat tab "Data", di mana kami akan ditawari versi terperinci dari kontrol pemfilteran. Untuk mengurutkan data, Anda perlu memilih rentang sel yang diinginkan, atau sebagai alternatif, cukup tandai sel teratas dari kolom yang diinginkan. Setelah itu, Anda harus mengklik tombol "Filter", setelah itu tombol dengan panah kecil mengarah ke bawah akan muncul di sebelah kanan sel.
Filter dapat dengan aman "dilampirkan" ke semua kolom.
Ini akan sangat menyederhanakan penyortiran informasi untuk pemrosesan di masa mendatang.
Sekarang pertimbangkan menu tarik-turun dari setiap filter (keduanya akan sama):
- mengurutkan dengan naik atau turun ("dari nilai minimum ke maksimum" atau sebaliknya), menyortir informasi berdasarkan warna (yang disebut - kebiasaan);
— Saring berdasarkan warna;
- kemampuan untuk menghapus filter;
— parameter pemfilteran, yang mencakup filter numerik, teks, dan tanggal (jika nilai tersebut ada dalam tabel);
- kemampuan untuk "memilih semua" (jika Anda menghapus centang pada kotak ini, maka benar-benar semua kolom akan berhenti ditampilkan di lembar);
- "nama kolom", di mana semua kolom yang digunakan dalam tabel terlihat. Jika Anda menghapus centang pada kotak di sebelah item tertentu, item tersebut juga tidak akan ditampilkan lagi, tetapi di sini Anda dapat melakukannya secara selektif.
Jika Anda menerapkan filter apa pun, gambar tombol akan berubah dan mengambil bentuk filter yang Anda pilih.
Jika berbicara tentang filter numerik , maka di sini programnya juga cukup menawarkan sejumlah besar berbagai opsi penyortiran untuk nilai yang tersedia. Ini adalah: Lebih besar dari, Lebih besar dari atau sama dengan, Sama dengan, Kurang dari atau sama dengan, Kurang dari, Tidak sama dengan, Antara nilai yang ditentukan. Kami mengaktifkan item "10 Pertama" dan sebuah jendela muncul
Di sini Anda dapat memilih berapa banyak nilai pertama yang kami minati (hanya angka yang dapat Anda pilih menggunakan panah, atau tentukan sendiri) dan bagaimana mereka harus diurutkan (Terkecil, terbesar, atau bahkan persentase dari jumlah keseluruhan elemen).
Jika Anda memilih "Di atas (atau di bawah) rata-rata", perhatian Anda akan diberikan ke baris yang nilainya cocok dengan kueri. Perhitungan rata-rata aritmetika terjadi secara otomatis, berdasarkan data yang dikumpulkan dari kolom.
Filter otomatis khusus di Excel 2010, sebagaimana disebutkan, memberi Anda lebih banyak akses ke opsi pemfilteran. Dengannya, Anda dapat mengatur kondisi (terdiri dari 2 ekspresi atau "fungsi logis" ATAU / DAN), yang dengannya pemilihan data akan dilakukan.
Filter teks dibuat khusus untuk bekerja dengan nilai teks. Di sini, untuk pemilihan, parameter seperti: "Berisi", "Tidak mengandung", "Dimulai dengan ...", "Diakhiri dengan ...", serta "Sama", "Tidak sama". Penyiapannya sangat mirip dengan penyiapan filter numerik apa pun.
Mari terapkan pemfilteran simultan dengan parameter berbeda ke berbagai kolom laporan kami tentang pekerjaan gudang. Jadi, biarkan "Nama" dimulai dengan "A", tetapi di kolom "Gudang 1" kami menunjukkan bahwa hasilnya lebih dari 25. Hasil pemilihan seperti itu disajikan di bawah ini
Pemfilteran, bila tidak diperlukan lagi, dapat dibatalkan oleh salah satu dari cara yang mungkin- gunakan kombinasi tombol "Shift + Ctrl + L", tekan tombol "Filter" (tab "Main", ikon besar "Sort and Filter", yang merupakan bagian dari grup "Editing"). Atau cukup klik tombol "Filter" pada tab "Data".
Tentu saja, pemfilteran adalah elemen pemilihan data yang cukup nyaman di Excel 2010, tetapi terkadang Anda mungkin perlu membuat pemilihan informasi yang lebih mendetail yang tidak disediakan oleh fungsi standar.
Misalkan sekarang kita perlu memfilter menggunakan kondisi tertentu, yang pada gilirannya merupakan kombinasi kondisi untuk memfilter beberapa kolom sekaligus (mungkin ada lebih dari 2). Dalam hal ini, hanya filter khusus lanjutan yang dapat diterapkan, di mana kondisi dapat digabungkan menggunakan fungsi logika AND / OR.
Mari kita lihat contoh untuk lebih memahami kemungkinan filter. Katakanlah kita memiliki meja dengan permintaan pencarian di sistem Yandex dan Google. Kami perlu menentukan kueri mana yang tersedia yang memiliki posisi kurang dari 10 di setiap sistem yang ditentukan. Karena mungkin ada banyak permintaan, sejumlah langkah sederhana perlu dilakukan.
Dalam sel yang terpisah dan benar-benar bebas, salin nama kolom yang akan digunakan untuk memfilter data. Seperti disebutkan di atas, ini akan menjadi "Visibilitas di Yandex" dan "Visibilitas di Google". Nama dapat disalin ke sel yang berdekatan, tetapi kami akan menghentikan pemilihan di B10 dan C10.
Di bawah sel-sel inilah kita akan memasuki kondisi untuk pemfilteran yang akan datang. Jika perlu (dan ini perlu bagi kami) untuk memperhitungkan kedua kondisi sekaligus (fungsi logis ini disebut DAN), maka kondisi pemfilteran harus ditempatkan dalam satu baris. Jika perlu memperhitungkan hanya satu (fungsi ATAU), maka kondisi harus ditempatkan di baris yang berbeda.
Sekarang kami mencari tab "Data", "Urutkan dan filter" dan klik ikon kecil "Lanjutan" dan kami melihat jendela ini
"Filter lanjutan" memungkinkan Anda memilih salah satu opsi yang memungkinkan - memfilter daftar di sini atau mengambil data yang diterima dan menyalinnya ke tempat lain yang nyaman bagi Anda.
Dalam "Rentang sumber", Anda harus menentukan alamat (jika program tidak melakukannya sendiri) dari seluruh rentang sel yang datanya ingin Anda filter. Ini dapat dilakukan dengan menentukan alamat yang diperlukan secara manual, atau hanya dengan memilih batas tabel yang diperlukan dengan mouse.
"Rentang kondisi", seperti yang Anda duga, berisi alamat sel yang menyimpan kondisi pemfilteran dan nama kolom. Bagi kami itu akan menjadi "B10:C12".
Jika Anda memutuskan untuk menjauh dari contoh dan memilih fungsi "salin hasil ...", maka di kolom ke-3 Anda harus menentukan alamat rentang sel tempat program perlu mengirim data yang telah melewati filter. Oleh karena itu, kami juga akan memilih kemungkinan ini dan menunjukkan "A27:C27".
Kami mengonfirmasi program dan, jika semuanya dilakukan tanpa kesalahan, dan kondisi yang ditentukan layak, maka kami akan melihat hasil berikut
Semoga berhasil dalam pekerjaan.
Banyak pengguna PC yang sangat mengenal paket produk untuk bekerja dengan berbagai jenis dokumen yang disebut kantor Microsoft. Di antara program perusahaan ini adalah MS Excel. Utilitas ini dirancang untuk bekerja dengan spreadsheet.
Program ini memiliki apa yang disebut filter lanjutan di Excel. Ini dirancang untuk kemudahan penggunaan dan pembuatan tabel. Ini akan dibahas dalam artikel kami. Kami akan menunjukkan cara membuatnya dan cara menggunakannya.
Apa fungsi ini? Keterangan
Apa arti filter lanjutan di Excel? Ini adalah fungsi yang memungkinkan Anda membedakan data yang dipilih (berdasarkan kolom di Excel) relatif terhadap persyaratan yang dimasukkan.
Misalnya, jika kita memiliki spreadsheet dengan informasi tentang semua siswa di sekolah (tinggi, berat, kelas, jenis kelamin, dll.), Maka kita dapat dengan mudah memilih di antara mereka, katakanlah, semua anak laki-laki dengan tinggi badan 160 dari 8 kelas. Anda dapat melakukannya menggunakan fitur "Filter Lanjutan" di Excel. Kami akan membicarakannya secara rinci nanti.
Apa yang dimaksud dengan filter otomatis?
Apa saja fitur filter reguler dan lanjutan? Mari kita bicara tentang yang pertama dulu. Filter otomatis (normal) dirancang untuk membedakan antara data sesuai dengan kriteria yang diketahui sebelumnya, yang jumlahnya ditentukan. Ini digunakan ketika ada spreadsheet ukuran kecil, dengan kolom dan parameter yang diberikan. Jika kita perlu menetapkan jumlah kriteria yang tidak terbatas untuk pemfilteran, kita harus memilih lanjutan.
Bagaimana melakukannya dengan benar?
Bagaimana cara membuat filter lanjutan di Excel? Untuk memperjelas bagaimana prosedur itu terjadi dan bagaimana hal itu dilakukan, pertimbangkan sebuah contoh.
Petunjuk untuk pemfilteran spreadsheet tingkat lanjut:
- Anda perlu membuat tempat di atas tabel utama. Di sinilah hasil penyaringan akan ditempatkan. Harus ada cukup ruang untuk tabel yang sudah jadi. Itu juga membutuhkan satu baris lagi. Ini akan memisahkan tabel yang difilter dari yang utama.
- Di baris paling pertama dari ruang kosong, salin seluruh tajuk (nama kolom) dari tabel utama.
- Masukkan data yang diperlukan untuk memfilter di kolom yang diinginkan. Perhatikan bahwa entri akan terlihat seperti ini: = "= nilai yang difilter".
- Sekarang Anda harus pergi ke bagian "Data". Di area pemfilteran (ikon berbentuk corong), pilih "Lanjutan" (terletak di ujung daftar kanan dari tanda yang sesuai).
- Selanjutnya, di jendela pop-up, Anda harus memasukkan parameter filter lanjutan di Excel. "Rentang kondisi" dan "Rentang awal" diisi secara otomatis jika sel awal lembar kerja telah dipilih. Jika tidak, Anda harus memasukkannya sendiri.
- Klik Oke. Anda akan keluar dari pengaturan parameter pemfilteran lanjutan.
Setelah langkah-langkah diambil, tabel utama hanya akan berisi catatan untuk nilai pembatas yang diberikan. Untuk membatalkan tindakan terakhir(memfilter), Anda perlu mengklik tombol "Hapus", yang terletak di bagian "Data".
Bekerja dengan filter lanjutan di Excel
Bagaimana cara menggunakan filter lanjutan di Excel? Untuk memahaminya, mari kita lihat lagi fungsi ini. Misalkan kita memiliki kolom dengan kota di spreadsheet. Sebelumnya, kami telah melakukan pemfilteran lanjutan untuk kota Rostov. Sekarang, dalam dokumen yang sama, kami ingin menambahkan catatan yang diterima untuk kota Rostov yang sesuai dengan kota Samara.
Untuk ini, Anda perlu:
- Tempatkan ketentuan pembatasan (="-Samara") di bawah kueri sebelumnya (="=Rostov").
- Panggil menu filter lanjutan (bagian "Data", tab "Memfilter dan menyortir", pilih "Lanjutan" di dalamnya).
- Tekan OK. Ini akan menutup pemfilteran lanjutan di Excel. Dan tabel siap pakai akan muncul di layar, terdiri dari catatan yang menunjukkan kota Samara atau Rostov.
Saat bekerja, poin yang sangat penting berikut ini harus diperhitungkan. Semua kriteria yang dimasukkan harus hanya ada di kolom tempat pemfilteran dilakukan. Di atas meja utama.
Setelah mempelajari metode pemfilteran lanjutan dasar, Anda dapat mulai menjelajahi semua kemungkinan fungsi ini. Ini berarti penggunaan formula.
Penyaringan tingkat lanjut. Aturan dasar untuk menggunakan Excel
Syarat Penggunaan:
- Kriteria seleksi adalah hasil dari formula asli.
- Hasilnya hanya bisa berupa dua nilai: "TRUE" atau "FALSE".
- Referensi absolut menentukan rentang sumber dari tabel yang difilter.
- Hasil rumus hanya akan menampilkan baris yang berjumlah BENAR. Nilai baris yang "FALSE" sebagai hasil dari rumus tidak akan ditampilkan.
Dengan menggunakan aturan ini, Anda dapat membuat rumus apa pun dengan operan dan nilai yang valid.
Contoh di Excel 2010
Mari kita lihat contoh filter lanjutan di Excel 2010 dan penggunaan rumusnya. Misalnya, mari kita batasi nilai beberapa kolom dengan data numerik dengan hasil nilai rata-rata (lebih atau kurang).
Petunjuk untuk bekerja dengan filter lanjutan di Excel dengan nilai rata-rata kolom:
- Langkah pertama adalah memilih rumus untuk rata-rata data kolom. Untuk melakukannya, Anda perlu memilih rentang hasil dari rekaman awal hingga rekaman akhir. Jumlahkan dan bagi dengan jumlah baris (jumlah record).
- Kemudian pilih "Lanjutan" di bagian "Memfilter dan menyortir". Masukkan data yang diperlukan di sana (berdasarkan rentang).
- Setelah itu klik OK. Hasilnya adalah tabel yang memiliki catatan yang tidak melebihi nilai rata-rata untuk kolom yang diberikan.
Dengan demikian, Anda dapat menggunakan pemfilteran lanjutan dengan berbagai cara. Ini dapat dilakukan dengan menggunakan kompilasi berbagai macam formula.
Yang utama jangan lupa untuk memasukkan kriteria pemfilteran di atas tabel utama di kolom yang akan digunakan untuk membuat pembedaan.
Perhatian! Selama operasi, rekaman yang tidak lolos kriteria pemfilteran tidak akan dihapus. Mereka hanya tidak muncul (tidak muncul). Pemfilteran lanjutan dirancang untuk dapat menyalin hasil dan menempelkannya di tempat lain. Setelah itu, Anda perlu mengklik "Hapus" di bagian "Memfilter dan menyortir". Kemudian tabel asli Anda akan kembali ke layar.
Filter otomatis. Contoh penggunaan
Autofilter adalah alat umum. Itu hanya dapat diterapkan dengan mengatur parameter yang tepat. Misalnya, cetak semua nilai tabel yang lebih besar dari 1000 (< 1000), или показать точные данные, как было рассмотрено в примере с городами.
Ini digunakan lebih sering daripada yang diperpanjang. Namun, opsi pemfilteran yang ditawarkan oleh autofilter seringkali tidak cukup.
Pro dan kontra dari filter lanjutan di Excel
Karena pemfilteran lanjutan merupakan tambahan dari pemfilteran biasa, penggunaannya memiliki kelebihan dan kekurangan. Kami sekarang akan mempertimbangkannya.
Keuntungan pemfilteran lanjutan:
- formula dapat digunakan.
Kontra pemfilteran lanjutan:
- untuk melakukan operasi ini, Anda perlu menghabiskan lebih banyak waktu untuk proses pengenalan rentang dan data asli;
- untuk menggunakan pemfilteran lanjutan menggunakan rumus, Anda perlu mengetahui dan dapat menggunakan aturan logika dan merumuskan persamaan MS Excel.
Meski ada minusnya, fungsi ini masih memiliki lebih banyak kemungkinan daripada sekedar autofiltering. Meskipun dengan yang terakhir tidak perlu memasukkan sesuatu milik Anda sendiri, kecuali kriteria yang dengannya nilai-nilai akan diidentifikasi.
Memfilter dengan dua kriteria terpisah. Bagaimana melakukannya dengan benar?
Pemfilteran lanjutan memungkinkan Anda menggunakan berbagai formula di spreadsheet yang sudah ada. Jika Anda perlu membedakannya terlebih dahulu dengan satu kriteria, kemudian dengan yang lain (secara terpisah satu sama lain), Anda harus:
- Buat tempat untuk memasukkan parameter filter. Paling mudah untuk meninggalkan ruang ini di atas tabel utama dan jangan lupa menyalin tajuk (nama kolom) agar tidak bingung di kolom mana untuk memasukkan kriteria ini.
- Masukkan indikator yang diinginkan untuk pemfilteran. Misalnya, semua record yang nilai kolomnya lebih besar dari 1000 (>1000).
- Buka tab "Data". Di bagian "Memfilter dan menyortir", pilih "Lanjutan".
- Di jendela yang terbuka, tentukan rentang nilai yang dipertimbangkan dan sel dengan nilai kriteria yang dipertimbangkan.
- Klik Oke. Setelah itu, tabel yang difilter menurut kriteria yang ditentukan akan ditampilkan.
- Salin hasil pembatas. Tempel tabel yang difilter di suatu tempat di samping lembar Excel yang sama. Anda dapat menggunakan halaman lain.
- Pilih "Hapus". Tombol ini terletak di tab "Data" di bagian "Memfilter dan menyortir". Setelah menekannya, tabel yang difilter akan kembali ke bentuk aslinya. Dan Anda bisa bekerja dengannya.
- Selanjutnya, Anda perlu mengalokasikan ruang kosong lagi untuk tabel yang akan difilter.
- Kemudian Anda perlu menyalin tajuk (nama kolom) dari bidang utama dan memindahkannya ke baris pertama ruang yang dibebaskan untuk struktur yang difilter.
- Buka tab "Data". Di bagian "Memfilter dan menyortir", pilih "Lanjutan".
- Di jendela yang terbuka, pilih rentang catatan (kolom) untuk difilter.
- Tambahkan alamat sel di mana kriteria delimitasi ditulis, misalnya, "kota Odessa".
- Klik Oke. Setelah itu, pemfilteran dengan nilai "Odessa" akan terjadi.
- Salin tabel yang difilter dan tempel di lembar dokumen lain, atau di halaman yang sama, tetapi jauh dari halaman utama.
- Klik "Hapus" lagi. Semua sudah siap. Anda sekarang memiliki tiga tabel. Yang utama, difilter dengan satu nilai (>1000), serta yang difilter dengan nilai lain (Odessa).
Kesimpulan kecil
Meski sekilas fungsi ini tampak rumit, nyatanya semuanya cukup sederhana. Yang perlu Anda ketahui hanyalah kisaran kondisi filter lanjutan di Excel. Namun, hal-hal berikut tidak boleh dilupakan. Jangan bingung antara filter reguler dan lanjutan. Juga disarankan untuk mempelajari aturan logika dan rumus Excel, kemudian bekerja dengan filter menjadi mudah.
Microsoft Excel adalah alat spreadsheet yang ada di mana-mana dan mudah digunakan. Fungsionalitas yang luas menjadikan program ini yang terpopuler kedua setelah MS Word di antara semuanya program kantor. Ini digunakan oleh para ekonom, akuntan, ilmuwan, pelajar, dan perwakilan dari profesi lain yang perlu memproses data matematika.
Salah satu fitur paling nyaman dalam program ini adalah pemfilteran data. Pertimbangkan cara mengatur dan menggunakan filter MS excel.
Di mana filter di Excel - tipenya
Menemukan filter dalam program ini mudah - Anda perlu membuka menu utama atau cukup menahan tombol Ctrl + Shift + L.
Cara mengatur filter di Excel
Fungsi pemfilteran dasar di Excel:
- filter berdasarkan warna: memungkinkan Anda mengurutkan data berdasarkan font atau mengisi warna,
- filter teks di excel: memungkinkan Anda untuk mengatur kondisi tertentu untuk baris, misalnya: kurang dari, lebih besar dari, sama dengan, tidak sama dengan, dan lain-lain, serta mengatur kondisi logis - dan, atau,
- filter numerik: urutkan berdasarkan kondisi numerik, seperti di bawah rata-rata, 10 teratas, dan lainnya,
- manual: pemilihan dapat dilakukan sesuai dengan kriteria yang dipilih sendiri.
Mereka mudah digunakan. Penting untuk memilih tabel dan memilih bagian dengan filter di menu, lalu mengklarifikasi dengan kriteria apa data akan disaring.
Cara menggunakan filter lanjutan di Excel - cara menyesuaikannya
Filter standar memiliki kelemahan yang signifikan - untuk mengingat kriteria pemilihan mana yang digunakan, Anda perlu membuka menu. Dan terlebih lagi menyebabkan ketidaknyamanan ketika lebih dari satu kriteria ditetapkan. Dari sudut pandang ini, filter lanjutan lebih nyaman, yang ditampilkan sebagai tabel terpisah di atas data.
INSTRUKSI VIDEO
Urutan pengaturan:
- Buat tabel dengan data untuk dikerjakan lebih lanjut. Seharusnya tidak berisi baris kosong.
- Buat tabel dengan kondisi pemilihan.
- Jalankan filter lanjutan.
Mari kita lihat contoh penyiapan.
Kami memiliki tabel dengan kolom Produk, Kuantitas, dan Harga.
Misalnya, Anda perlu mengurutkan baris yang nama produknya diawali dengan kata "Paku". Beberapa baris termasuk dalam kondisi ini.
Tabel dengan ketentuan akan ditempatkan di sel A1:A2. Penting untuk menunjukkan nama kolom tempat pemilihan akan dilakukan (sel A1) dan kata itu sendiri untuk pemilihan - Paku (sel A2).
Paling nyaman untuk menempatkannya di atas data atau di samping. Ini juga tidak dilarang di bawahnya, tetapi tidak selalu nyaman, karena dari waktu ke waktu mungkin perlu menambahkan baris tambahan. Indentasi setidaknya satu baris kosong di antara dua tabel.
Maka Anda membutuhkan:
- pilih salah satu sel
- buka "Filter lanjutan" di sepanjang jalur: Data - Urutkan dan filter - Lanjutan,
- periksa apa yang ditentukan di bidang "Rentang awal" - seluruh tabel dengan informasi harus ada di sana,
- dalam "Rentang kondisi" perlu untuk mengatur nilai sel dengan kondisi pemilihan, dalam contoh ini adalah rentang A1:A2.
Setelah mengklik tombol "OK", pemilihan akan terjadi informasi yang perlu, dan hanya baris dengan kata yang diinginkan yang akan muncul di tabel, dalam kasus kami ini adalah "Paku". Nomor baris yang tersisa akan berubah menjadi biru. Untuk membatalkan filter yang ditentukan, cukup tekan tombol CTRL+SHIFT+L.
Ini juga mudah untuk memfilter string yang berisi persis kata "Paku" dengan cara yang tidak peka huruf besar-kecil. Di rentang B1:B2, kami akan menempatkan kolom dengan kriteria pemilihan baru, tidak lupa menunjukkan tajuk kolom tempat pemutaran akan dilakukan. Di sel B2, Anda harus memasukkan rumus berikut = "= Paku".
- pilih salah satu sel tabel,
- buka "Filter lanjutan",
- periksa apakah seluruh tabel dengan data termasuk dalam "Rentang awal",
- dalam "Rentang kondisi" menunjukkan B1:B2.
Setelah mengklik "OK", data akan disaring.
Ini adalah contoh paling sederhana. bekerja dengan filter di excel. Dalam versi yang diperpanjang, akan lebih mudah untuk mengatur kondisi lain untuk pemilihan, misalnya, putus sekolah dengan parameter "ATAU", putus sekolah dengan parameter "Kuku" dan nilai di kolom "Kuantitas" > 40.
Cara memfilter di Excel berdasarkan kolom
Informasi dalam tabel dapat disaring berdasarkan kolom - satu atau lebih. Perhatikan contoh tabel dengan kolom "Kota", "Bulan", dan "Penjualan".
Jika Anda perlu memfilter data berdasarkan kolom dengan nama kota dalam urutan abjad, Anda harus memilih salah satu sel di kolom ini, buka "Urutkan" dan "Filter" dan pilih opsi "Y". Hasilnya, informasi akan ditampilkan dengan mempertimbangkan huruf pertama pada nama kota.
Untuk mendapatkan informasi tentang prinsip kebalikannya, Anda perlu menggunakan parameter "YA".
Penting untuk menyaring informasi berdasarkan bulan, dan juga kota dengan volume penjualan yang besar harus lebih tinggi di tabel daripada kota dengan volume penjualan yang lebih kecil. Untuk mengatasi masalah tersebut, Anda perlu memilih opsi "Sort" di "Sort and Filter". Di jendela yang muncul dengan pengaturan, tentukan "Urutkan berdasarkan" - "Bulan".
Selanjutnya, Anda perlu menambahkan penyortiran tingkat kedua. Untuk melakukan ini, pilih "Urutkan" - "Tambahkan level" dan tentukan kolom "Penjualan". Pada kolom pengaturan "Urutan", pilih "Menurun". Setelah mengklik "OK", data akan dipilih sesuai dengan parameter yang ditentukan.
INSTRUKSI VIDEO
Mengapa filter mungkin tidak berfungsi di Excel
Dalam bekerja dengan alat seperti filter, pengguna sering mengalami kesulitan. Biasanya mereka terkait dengan pelanggaran aturan untuk menggunakan pengaturan tertentu.
Masalah dengan filter berdasarkan tanggal adalah salah satu yang paling populer. Terjadi setelah bongkar data dari sistem akuntansi dalam bentuk array. Saat mencoba memfilter baris berdasarkan kolom yang berisi tanggal, dropout terjadi bukan berdasarkan tanggal, tetapi berdasarkan teks.
Larutan:
- pilih kolom dengan tanggal,
- buka tab Excel di menu utama,
- pilih tombol "Sel", dalam daftar drop-down pilih opsi "Ubah teks menjadi tanggal".
Kesalahan umum pengguna saat bekerja dengan program ini juga harus mencakup:
- tidak adanya tajuk untuk kolom (memfilter, menyortir, dan sejumlah parameter penting lainnya tidak akan berfungsi tanpanya),
- adanya baris dan kolom kosong dalam tabel dengan data (ini membingungkan sistem penyortiran, Excel menganggap informasi sebagai dua tabel independen yang berbeda),
- menempatkan beberapa tabel pada satu halaman (lebih mudah menempatkan setiap tabel pada lembar terpisah),
- penempatan di beberapa kolom data dengan jenis yang sama,
- penempatan data pada beberapa lembar, misalnya per bulan atau tahun (jumlah pekerjaan dapat langsung dikalikan dengan jumlah lembar informasi).
Dan kesalahan kritis lainnya yang tidak memungkinkan Anda untuk menggunakan kemampuan Excel sepenuhnya adalah penggunaan produk yang tidak berlisensi.
Itu tidak dapat dijamin berfungsi dengan benar, dan kesalahan akan muncul setiap saat. Jika Anda berniat menggunakan alat pengolah informasi matematika ini secara terus-menerus, belilah versi lengkap dari program ini.
Filter lanjutan di Excel menyediakan lebih banyak opsi pengelolaan data spreadsheet. Ini lebih kompleks dalam pengaturan, tetapi jauh lebih efektif dalam tindakan.
Dengan bantuan filter standar, pengguna Microsoft Excel dapat menyelesaikan jauh dari semua tugas. Tidak ada tampilan visual dari kondisi pemfilteran yang diterapkan. Tidak mungkin menerapkan lebih dari dua kriteria seleksi. Anda tidak dapat memfilter nilai duplikat untuk hanya menyisakan entri unik. Dan kriterianya sendiri skematis dan sederhana. Fungsionalitas filter lanjutan jauh lebih kaya. Mari kita lihat lebih dekat kemampuannya.
Bagaimana cara membuat filter lanjutan di Excel?
Filter lanjutan memungkinkan Anda untuk memfilter data dengan serangkaian kondisi yang tidak terbatas. Dengan alat ini, pengguna dapat:
- menetapkan lebih dari dua kriteria seleksi;
- salin hasil penyaringan ke lembar lain;
- tetapkan kondisi kerumitan apa pun menggunakan rumus;
- ekstrak nilai unik.
Algoritme untuk menerapkan filter lanjutan sederhana:
- Kami membuat tabel dengan data awal atau membuka yang sudah ada. Misalnya, seperti ini:
- Buat tabel kondisi. Keunikan: baris tajuk benar-benar bertepatan dengan "tajuk" dari tabel yang difilter. Untuk menghindari kesalahan, salin baris tajuk di tabel sumber dan tempel di lembar yang sama (samping, atas, bawah) atau lembar lain. Masukkan kriteria pemilihan ke dalam tabel kondisi.
- Buka tab "Data" - "Urutkan dan filter" - "Lanjutan". Jika informasi yang difilter harus ditampilkan di sheet lain (BUKAN di tempat tabel asli berada), maka Anda perlu menjalankan filter lanjutan dari sheet lain.
- Di jendela "Filter lanjutan" yang terbuka, pilih metode pemrosesan informasi (di lembar yang sama atau di lembar lain), atur rentang awal (Tabel 1, contoh) dan rentang kondisi (Tabel 2, kondisi). Baris tajuk harus disertakan dalam rentang.
- Untuk menutup jendela Advanced Filter, klik OK. Kami melihat hasilnya.
Tabel teratas adalah hasil dari penyaringan. Pelat bawah dengan kondisi diberikan kejelasan di sebelahnya.
Bagaimana cara menggunakan filter lanjutan di Excel?
Untuk membatalkan tindakan filter lanjutan, letakkan kursor di mana saja di tabel dan tekan kombinasi tombol Ctrl + Shift + L atau "Data" - "Sort and Filter" - "Clear".
Dengan menggunakan alat "Filter Lanjutan", mari temukan informasi tentang nilai yang mengandung kata "Set".
Mari tambahkan kriteria ke tabel kondisi. Misalnya, ini:
Program dalam hal ini akan mencari semua informasi tentang produk yang memiliki kata "Set" di namanya.
Anda dapat menggunakan tanda "=" untuk mencari nilai yang tepat. Mari tambahkan kriteria berikut ke tabel kondisi:
Excel mengambil tanda "=" sebagai sinyal bahwa pengguna sekarang memasukkan rumus. Agar program berfungsi dengan benar, bilah rumus harus berisi entri dalam bentuk: = "= Kumpulan sel wilayah 6."
Setelah menggunakan "Filter Lanjutan":
Sekarang mari memfilter tabel sumber dengan kondisi "ATAU" untuk kolom yang berbeda. Operator "ATAU" juga ada di alat "Filter Otomatis". Tapi di sana bisa digunakan dalam satu kolom.
Dalam tabel ketentuan, kami memperkenalkan kriteria pemilihan: \u003d "= Kumpulan sel wilayah 6." (di kolom "Nama") dan ="
Harap dicatat: kriteria harus ditulis di bawah judul yang sesuai pada baris BERBEDA.
Hasil seleksi:
Filter lanjutan memungkinkan Anda menggunakan rumus sebagai kriteria. Pertimbangkan sebuah contoh.
Pemilihan baris dengan utang maksimum: =MAX(Tabel1).
Jadi, kami mendapatkan hasil setelah menjalankan beberapa filter pada lembar Excel yang sama.
Bagaimana cara membuat banyak filter di Excel?
Mari buat filter dengan beberapa nilai. Untuk melakukan ini, kami memperkenalkan beberapa kriteria pemilihan data sekaligus ke dalam tabel kondisi:
Terapkan alat Filter Lanjutan:
Sekarang, dari tabel dengan data yang dipilih, kami akan mengekstrak informasi baru, dipilih sesuai dengan kriteria lain. Misalnya, hanya pengiriman untuk tahun 2014.
Kami memasukkan kriteria baru di tabel ketentuan dan menerapkan alat pemfilteran. Rentang awal adalah tabel dengan data yang dipilih sesuai dengan kriteria sebelumnya. Ini adalah cara Anda memfilter beberapa kolom.
Untuk menggunakan beberapa filter, Anda dapat membuat beberapa tabel kondisi pada sheet baru. Metode implementasi tergantung pada tugas yang ditetapkan oleh pengguna.
Bagaimana cara memfilter di Excel dengan baris?
Tidak dengan cara standar. program Microsoft Excel hanya memilih data dalam kolom. Karena itu, solusi lain harus dicari.
Berikut adalah contoh kriteria string filter lanjutan di Excel:
- Tabel konversi. Misalnya, dari tiga baris, buat daftar tiga kolom dan terapkan pemfilteran ke versi yang dikonversi.
- Gunakan rumus untuk menampilkan dengan tepat data di baris yang Anda perlukan. Misalnya, buat beberapa indikator sebagai daftar drop-down. Dan di sel berikutnya, masukkan rumus menggunakan fungsi IF. Saat nilai tertentu dipilih dari daftar drop-down, parameternya muncul di sebelahnya.
Untuk memberikan contoh cara kerja filter menurut baris di Excel, mari buat tabel:
Untuk daftar produk, buat daftar drop-down:
Di atas tabel dengan data sumber, sisipkan baris kosong. Di dalam sel, kita akan memasukkan rumus yang akan menunjukkan dari kolom mana informasi diambil.
Di sebelah daftar drop-down, masukkan rumus berikut ke dalam sel: Tugasnya adalah memilih dari tabel nilai-nilai yang sesuai dengan produk tertentu
Unduh Contoh Filter Lanjutan
Jadi, dengan bantuan alat "Daftar Turun" dan bawaan Fungsi Excel memilih data dalam baris sesuai dengan kriteria tertentu.
Memfilter data di Excel akan memungkinkan Anda menampilkan di kolom tabel informasi yang menarik minat pengguna pada saat tertentu. Ini sangat menyederhanakan proses bekerja dengan tabel besar. Anda akan dapat mengontrol data yang akan ditampilkan di kolom dan data yang dikecualikan dari daftar.
Jika Anda membuat tabel di Excel melalui tab "Sisipkan" - "Tabel", atau tab "Beranda" - "Format sebagai Tabel", maka dalam tabel seperti itu, filter diaktifkan secara default. Itu ditampilkan dalam bentuk panah yang terletak di sel atas di sisi kanan.
Jika Anda baru saja mengisi sel dengan data lalu memformatnya sebagai tabel, filter harus diaktifkan. Untuk melakukan ini, pilih seluruh rentang sel tabel, termasuk tajuk kolom, karena tombol filter terletak di kolom atas, dan jika Anda memilih kolom yang dimulai dari sel dengan data, itu tidak akan berlaku untuk data yang difilter ini. kolom. Lalu buka tab "Data" dan klik tombol "Filter".
Dalam contoh, panah filter ada di header, dan memang demikian - semua data di kolom di bawah ini akan difilter.
Jika Anda tertarik dengan pertanyaan tentang cara membuat tabel di Excel, ikuti tautannya dan baca artikel tentang topik ini.
Sekarang mari kita lihat bagaimana filter bekerja di Excel. Mari kita gunakan tabel berikut sebagai contoh. Ini memiliki tiga kolom: "Nama Produk", "Kategori" dan "Harga", kami akan menerapkan berbagai filter padanya.
Klik panah di sel atas kolom yang diinginkan. Di sini Anda akan melihat daftar data yang tidak berulang dari semua sel yang terletak di kolom ini. Akan ada kotak centang di sebelah setiap nilai. Hapus centang pada kotak untuk nilai yang ingin Anda kecualikan dari daftar.
Misalnya, biarkan hanya buah-buahan di "Kategori". Hapus centang di bidang "sayuran" dan klik "OK".
Untuk kolom tabel yang menerapkan filter, ikon yang sesuai akan muncul di sel atas.
Jika Anda perlu menghapus filter data di Excel, klik ikon filter di sel dan pilih "Hapus filter dari (nama kolom)" dari menu.
Cara membuat filter data di excel cara yang berbeda. Ada filter teks dan angka. Mereka diterapkan sesuai jika sel kolom berisi teks atau angka.
Mari terapkan "Filter Numerik" ke kolom "Harga". Klik tombol di sel atas dan pilih item yang sesuai dari menu. Dari daftar drop-down, Anda dapat memilih kondisi yang ingin Anda terapkan pada data kolom. Misalnya, mari tampilkan semua produk yang harganya lebih rendah dari "25". Kami memilih "kurang".
Masukkan nilai yang diinginkan di bidang yang sesuai. Beberapa kondisi dapat diterapkan untuk memfilter data menggunakan logika AND dan OR. Saat menggunakan “AND”, kedua syarat harus dipenuhi, saat menggunakan “ATAU”, salah satu syarat yang ditentukan harus dipenuhi. Misalnya, Anda dapat menyetel: "kurang" - "25" - "Dan" - "lebih besar dari" - "55". Jadi, kami akan mengecualikan produk dari tabel, yang harganya berkisar antara 25 hingga 55.
Tabel dengan filter pada kolom "Harga" di bawah 25.
"Filter Teks" dalam tabel contoh dapat diterapkan ke kolom "Nama Produk". Klik tombol filter di kolom dan pilih item dengan nama yang sama dari menu. Dalam daftar drop-down yang terbuka, misalnya, gunakan "dimulai dengan".
Mari tinggalkan di tabel produk yang dimulai dengan "ka". Di jendela berikutnya, di lapangan kami menulis: "ka *". Klik "OK".
"*" dalam satu kata, menggantikan urutan karakter. Misalnya, jika Anda menyetel kondisi "berisi" - "s * l", kata table, chair, falcon, dan seterusnya akan tetap ada. "?" akan mengganti karakter apapun. Misalnya, "b? tone" - roti, kuncup. Jika Anda ingin meninggalkan kata-kata yang terdiri dari 5 huruf, tulis "?????".
Filter untuk kolom Nama Produk.
Filter dapat disesuaikan dengan warna teks atau warna sel.
Mari buat sel "Filter menurut Warna" untuk kolom "Nama Produk". Kami mengklik tombol filter dan memilih item dengan nama yang sama dari menu. Ayo pilih merah.
Hanya produk merah yang tersisa di meja.
Filter warna teks berlaku untuk kolom Kategori. Hanya menyisakan buah. Pilih merah lagi.
Sekarang hanya buah merah yang ditampilkan di tabel contoh.
Jika Anda ingin semua sel tabel terlihat, tetapi sel merah didahulukan, lalu sel hijau, biru, dan seterusnya, gunakan pengurutan di Excel. Dengan mengklik tautan, Anda dapat membaca artikel tentang topik tersebut.
Filter di Excel akan membantu Anda bekerja dengan tabel besar. Kami telah mempertimbangkan poin utama tentang cara membuat filter dan cara bekerja dengannya. Pilih kondisi yang diperlukan dan tinggalkan data yang menarik di tabel.
Kadang-kadang, ketika bekerja dengan database besar, perlu untuk memilih informasi sesuai dengan kriteria tertentu (misalnya, hanya menampilkan orang-orang yang lahir pada tahun 1980). Untuk melakukan tugas semacam ini, filter digunakan di Excel.
Video tentang memfilter dan menyortir data di Excel
Jenis filter di Excel
Pemfilteran adalah pemilihan data yang diperlukan dari daftar untuk pekerjaan selanjutnya dengannya. Hasil dari prosedur ini akan berupa string tertentu yang sesuai dengan kriteria seleksi. Entri yang tersisa disembunyikan sementara dan tidak digunakan hingga pengguna menonaktifkan filter. Dengan data yang dipilih, Anda dapat melakukan tindakan standar: mengedit, memformat, mencetak, membuat grafik, bagan, dll.
Ada 2 cara untuk memfilter di Excel: AutoFilter dan Advanced Filter. Anda dapat menjalankannya melalui bilah menu dengan mengklik "Data - Filter". Dengan bantuan opsi pertama, pemilihan cepat informasi yang diperlukan dengan kriteria pencarian sederhana dilakukan. Dalam mode filter otomatis, baris tajuk tabel di setiap kolom akan berisi tombol dengan panah, dengan mengklik di mana Anda dapat menentukan kriteria pemilihan. Anda dapat mengatur pengaturan Anda sendiri untuk setiap kolom. Dalam mode ini, Anda dapat mengatur parameter berikut:
Untuk menghapus filter dari satu kolom, Anda perlu mengklik item "Semua" di daftar elemen. Jika Anda ingin membatalkannya untuk seluruh tabel, Anda harus memilih item "Data - Filter - Tampilkan Semua" di bilah menu. Menghapus filter otomatis dilakukan dengan cara yang sama seperti memulainya.
Filter lanjutan
Contoh membuat filter lanjutan - Anda perlu menentukan rentang data sumber, filter, dan rentang tempat data yang difilter ditempatkan
Opsi pemfilteran kedua memberi pengguna lebih banyak opsi untuk memilih data yang diperlukan... Untuk menjalankan opsi yang diperluas, Anda perlu membuat salinan tajuk tabel (yaitu, cukup salin tajuk). Ini akan menjadi kisaran kondisi. Maka rentang ini perlu diisi dengan kriteria seleksi. Tetapi di sini penting untuk mengikuti aturan: jika Anda ingin nilai dipilih berdasarkan dua parameter (misalnya, nama belakang dan nilai siswa), maka ketentuannya ditulis dalam satu baris; jika kriteria dipilih dalam mode "ATAU" (merek mobil atau ukuran mesin), maka kriteria tersebut ditulis dalam baris yang berbeda.
Katakanlah ada tabel dengan 2 kolom - nama produk dan jumlah. Total ada 3 produk - pisang, jeruk, jeruk keprok, dan jumlahnya masing-masing 10, 20 dan 15 buah. Setelah header disalin, Anda dapat membuat kondisi, misalnya menampilkan produk yang jumlahnya kurang dari atau sama dengan 15. Artinya, di bawah header yang disalin di kolom "Jumlah", Anda perlu menulis<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).
Dengan demikian, prinsip penyaringan harus jelas. Dengan cara ini, Anda dapat memilih elemen apa pun dari database, tidak peduli seberapa besar ukurannya. Prosedur ini akan membantu menyederhanakan pekerjaan pengguna dengan data dalam jumlah besar.