Anda dapat menampilkan informasi satu/beberapa parameter menggunakan pemfilteran data di Excel.
Ada dua alat untuk tujuan ini: Filter Otomatis dan Filter Lanjutan. Mereka tidak menghapus, tetapi menyembunyikan data yang tidak memenuhi syarat. Filter otomatis melakukan operasi paling sederhana. Filter lanjutan memiliki lebih banyak opsi.
Filter Otomatis dan Filter Tingkat Lanjut di Excel
Saya memiliki tabel sederhana yang tidak diformat atau dinyatakan sebagai daftar. Anda dapat mengaktifkan filter otomatis melalui menu utama.
Jika Anda memformat rentang data sebagai tabel atau mendeklarasikannya sebagai daftar, filter otomatis akan segera ditambahkan.
Menggunakan filter otomatis itu sederhana: Anda perlu memilih entri dengan nilai yang diinginkan. Misalnya menampilkan pengiriman ke toko no 4. Tempatkan tanda centang di sebelah kondisi pemfilteran yang sesuai:
Kita langsung lihat hasilnya:
Fitur alat ini:
- Filter otomatis hanya berfungsi dalam rentang yang tidak terputus. Tabel yang berbeda pada lembar yang sama tidak difilter. Meskipun mereka memiliki tipe data yang sama.
- Alat ini memperlakukan baris teratas sebagai judul kolom - nilai ini tidak disertakan dalam filter.
- Diperbolehkan menerapkan beberapa kondisi penyaringan sekaligus. Namun setiap hasil sebelumnya mungkin menyembunyikan catatan yang diperlukan untuk filter berikutnya.
Filter lanjutan memiliki lebih banyak opsi:
- Anda dapat mengatur kondisi pemfilteran sebanyak yang diperlukan.
- Kriteria pemilihan data terlihat.
- Dengan menggunakan filter lanjutan, pengguna dapat dengan mudah menemukan nilai unik dalam array multi-baris.
Cara membuat filter lanjutan di Excel
Contoh siap pakai - cara menggunakan filter lanjutan di Excel:
Hanya baris yang berisi nilai "Moskow" yang tersisa di tabel asli. Untuk membatalkan pemfilteran, Anda perlu mengklik tombol “Hapus” di bagian “Urutkan dan Filter”.
Cara menggunakan filter lanjutan di Excel
Mari pertimbangkan untuk menggunakan filter lanjutan di Excel untuk memilih baris yang berisi kata "Moskow" atau "Ryazan". Kondisi pemfilteran harus berada di kolom yang sama. Dalam contoh kita - di bawah satu sama lain.
Mengisi menu filter lanjutan:
Kami mendapatkan tabel dengan baris yang dipilih berdasarkan kriteria tertentu:
Mari kita pilih baris yang berisi nilai "No. 1" di kolom "Toko", dan ">1.000.000 rubel" di kolom biaya. Kriteria pemfilteran harus ada di kolom yang sesuai pada tabel kondisi. Dalam satu baris.
Isi parameter pemfilteran. Klik Oke.
Mari kita tinggalkan di tabel hanya baris-baris yang berisi kata "Ryazan" di kolom "Wilayah" atau nilai ">10.000.000 rubel" di kolom "Biaya". Karena kriteria pemilihan berada pada kolom yang berbeda, kami menempatkannya pada baris yang berbeda di bawah judul yang sesuai.
Mari gunakan alat “Filter Lanjutan”:
Alat ini dapat bekerja dengan rumus, yang memungkinkan pengguna memecahkan hampir semua masalah saat memilih nilai dari array.
Aturan Dasar:
- Hasil rumus tersebut adalah kriteria seleksi.
- Rumus tertulisnya menghasilkan TRUE atau FALSE.
- Rentang awal ditentukan menggunakan referensi absolut, dan kriteria pemilihan (dalam bentuk rumus) ditentukan menggunakan referensi relatif.
- Jika TRUE dikembalikan, baris akan ditampilkan setelah filter diterapkan. SALAH - tidak.
Mari kita tampilkan baris yang berisi jumlah di atas rata-rata. Untuk melakukannya, selain pelat dengan kriteria (di sel I1), masukkan nama “Kuantitas terbesar”. Di bawah ini adalah rumusnya. Kami menggunakan fungsi RATA-RATA.
Pilih sel mana pun dalam rentang sumber dan panggil "Filter Lanjutan". Kami menunjukkan I1:I2 sebagai kriteria seleksi (tautan relatif!).
Hanya baris yang nilai pada kolom “Kuantitas” di atas rata-rata yang tersisa dalam tabel.
Untuk hanya menyisakan baris yang tidak berulang dalam tabel, di jendela “Filter lanjutan”, centang kotak di samping “Hanya catatan unik”.
Klik Oke. Garis duplikat akan disembunyikan. Hanya entri unik yang akan tersisa di lembar.
Terkadang tabel dapat berisi data dalam jumlah yang cukup besar, dan data ini sering kali disajikan dalam bentuk daftar. Dalam hal ini, alat seperti mengurutkan daftar dan memfilternya sangat membantu. Namun, daftar tersebut harus diformat dengan cara tertentu, jika tidak, alat penyortiran dan pemfilteran tidak akan berfungsi.
Biasanya, daftar terdiri dari record (baris) dan field (kolom). Kolom harus berisi tipe data yang sama. Daftar tidak boleh berisi baris atau kolom kosong. Jika ada judul dalam daftar, formatnya harus berbeda dari elemen daftar lainnya.
Daftar penyortiran
Menyortir atau menyusun daftar mempermudah pencarian informasi. Setelah pengurutan, catatan ditampilkan dalam urutan yang ditentukan oleh nilai kolom (menurut abjad, naik/turun berdasarkan harga, dll.).
Buatlah daftar pendek untuk berlatih.
Pilih itu.
Klik tombolnya "Sortir dan Filter" di panel "Mengedit" kaset "Rumah".
Pilih "Menyortir dari A sampai Z". Daftar kami akan diurutkan berdasarkan kolom pertama, yaitu. sesuai dengan kolom nama lengkap.
Jika Anda perlu mengurutkan daftar berdasarkan beberapa bidang, maka item tersebut ditujukan untuk ini .
Penyortiran kompleks melibatkan pengurutan data di berbagai bidang. Anda dapat menambahkan bidang menggunakan tombol .
Hasilnya, daftar akan diurutkan berdasarkan parameter penyortiran kompleks yang ditetapkan.
Jika Anda perlu mengurutkan bidang dengan cara yang tidak standar, maka item menu ditujukan untuk ini "Daftar khusus.." daftar tarik-turun "Memesan".
Anda dapat memindahkan level penyortiran menggunakan tombol "Ke atas" Dan "Turun".
Jangan lupa tentang menu konteks. Dari sana, Anda juga dapat mengonfigurasi pengurutan daftar. Selain itu, ada opsi penyortiran menarik terkait pemilihan elemen tabel tertentu.
Perbedaan utama antara filter dan pengurutan adalah bahwa selama pemfilteran, rekaman yang tidak memenuhi kondisi pemilihan disembunyikan sementara (tetapi tidak dihapus), sedangkan saat mengurutkan, semua rekaman dalam daftar ditampilkan, hanya urutannya yang berubah.
Ada dua jenis filter: penyaring biasa(disebut juga filter otomatis) dan penyaring lanjutan.
Untuk menerapkan filter otomatis, klik tombol yang sama seperti saat mengurutkan - "Sortir dan Filter" dan pilih "Saring"(tentu saja, rentang sel harus dipilih sebelum ini).
Tombol dengan panah akan muncul di kolom daftar, dengan mengklik di mana Anda dapat mengonfigurasi parameter filter.
Bidang yang memiliki filter muncul dengan ikon corong. Jika Anda menggerakkan penunjuk tetikus ke corong tersebut, kondisi pemfilteran akan ditampilkan.
Untuk menciptakan kondisi seleksi yang lebih kompleks, paragraf ini dimaksudkan "Filter teks" atau "Filter numerik". Di jendela "Filter Otomatis Khusus" Anda perlu mengonfigurasi kondisi pemfilteran akhir.
Saat menggunakan filter lanjutan, kriteria pemilihan ditentukan pada lembar kerja.
Untuk melakukan ini, Anda perlu melakukan hal berikut.
Salin dan tempel header daftar ke ruang kosong.
Di bidang yang sesuai, tentukan kriteria pemfilteran.
Terkadang data yang dimasukkan ke dalam dokumen Excel dapat terdiri dari ribuan baris, dan cukup sulit untuk menemukan baris yang diperlukan di antara baris tersebut. Namun jika data yang Anda cari memenuhi kriteria tertentu sehingga Anda dapat menyaring informasi yang tidak perlu, maka Anda perlu menggunakan filter di Excel.
Untuk semua tabel di Excel dibuat melalui menu "Meja" di tab "Menyisipkan" atau format mana yang telah diterapkan, seperti tabel, sudah memiliki filter bawaan.
Jika data yang diformat sebagai tabel tidak memiliki filter, maka harus diinstal. Untuk melakukan ini, pilih header tabel atau cukup pilih sel di dalam tabel dan buka tab "Data" dan pilih dari menu "Saring". Item menu yang sama dapat ditemukan di tab "Rumah" di menu "Menyortir dan Memfilter". Setelah menghubungkan filter, tanda yang sesuai, kotak dengan segitiga terbalik, muncul di header tabel di sisi kanan judul setiap kolom.
Penggunaan filter yang paling sederhana adalah menyembunyikan semua nilai dalam daftar kecuali nilai yang dipilih. Cukup klik ikon filter dan beri tanda centang pada daftar nilai di seberang nilai yang diperlukan. Setelah menerapkan filter, ikon di kolom yang difilter juga berubah, yang memungkinkan Anda menentukan secara visual apakah filter diterapkan ke data. Untuk menghapus filter, klik ikon filter dan pilih "Hapus filter dari kolom...".
Anda juga dapat menyesuaikan filter yang lebih kompleks. Kolom pertama kita hanya berisi data teks, jadi ketika kita mengklik ikon filter, ada item yang aktif di kolom ini "Filter teks", di mana Anda dapat memilih beberapa kondisi. Dalam hal data numerik, seperti pada kolom kedua, opsi akan tersedia "Filter numerik".
Saat Anda memilih opsi kustom apa pun, jendela filter kustom akan terbuka, tempat Anda dapat memilih dua kondisi sekaligus dengan kombinasi "DAN" Dan "ATAU".
Dimungkinkan juga untuk mengatur filter berdasarkan warna teks dan warna sel. Dalam hal ini, hanya teks dan warna sel yang diterapkan di kolom ini yang akan ditampilkan dalam daftar.
Di versi terbaru Excel, tabel dapat dibuat untuk membuat irisan data. Untuk melakukan ini, aktifkan sel tabel mana saja dan buka tab yang muncul "Konstruktor", dan pilih menu "Masukkan Irisan". Sebuah jendela muncul di mana Anda harus memilih kolom di mana potongan data akan diterapkan. Setelah memilih kolom, jendela mengambang dengan kontrol muncul, di mana semua item irisan akan dicantumkan. Dengan memilih nilai apa pun, kami akan menerima potongan data yang sesuai.
Filter lanjutan disebut demikian karena memiliki kemampuan yang diperluas dan benar-benar tak terbatas serta unik dibandingkan dengan filter otomatis. Melanjutkan perkenalan kita dengan alat MS Excel untuk bekerja dengan tabel database, di artikel ini...
Kami akan melihat lebih dekat cara kerja filter lanjutan. Seperti filter otomatis, filter lanjutan di Excel dirancang untuk menyembunyikan sebagian informasi dari pandangan pengguna agar bagian lainnya lebih mudah dilihat atau untuk mencetak hanya catatan database yang dipilih.
Dengan filter lanjutan Anda dapat memfilter apa pun di tabel!!! Batasan kemampuan alat ini hanyalah imajinasi pengguna!
Anda sedang membaca postingan keempat dari rangkaian artikel tentang membuat database MS Unggul dan organisasi memproses informasi.
Anda dapat membaca caranya di artikel seri sebelumnya.
Filter No. 2 – filter lanjutan!
Untuk memulai, kita perlu membuat tabel lain di atas database di mana kita akan menunjukkan data yang merupakan kriteria pemilihan untuk filter lanjutan. (Terkadang lebih mudah untuk menempatkan tabel seperti itu di lembar baru.)
Ingat ketika kita membuat contoh database, kita meninggalkan beberapa baris kosong di bagian atas tabel, mengatakan bahwa baris tersebut akan diperlukan nanti saat menganalisis data? Waktunya telah tiba.
1. Buka file di MS Excel.
2. Saat membuat tabel kriteria untuk memilih filter lanjutan, saya sarankan Anda bertindak terlebih dahulu secara formula, tanpa memikirkan tugas yang harus diselesaikan. Menyalin Semua header bidang kolom dari sel A7...F7 ke sel A1...F1 – Benda kerja Siap untuk tabel kriteria! Anda dapat memulai pekerjaan utama.
Untuk pengoperasian filter yang stabil dan bebas kesalahan, harus ada setidaknya satu baris kosong antara tabel kriteria pemilihan dan tabel database!
Mari kita lanjutkan mempelajari filter lanjutan di Excel, memecahkan masalah praktis menggunakan contoh bekerja dengan database DB2 “Produksi struktur logam di situs No. 2.”
Tugas #5:
Menampilkan informasi tentang produksi balok untuk semua pesanan sepanjang periode.
1. Kami menuliskan parameter pemfilteran - kata "balok" di kolom "Produk" di tabel atas kriteria pemilihan.
2. Kami mengaktifkan (“klik dengan mouse”) sel mana pun di dalam tabel database - ini secara otomatis akan mengisi jendela “Rentang Sumber” di kotak dialog tarik-turun “Filter Lanjutan”.
3. Kami mengaktifkan filter lanjutan di Excel 2003 melalui menu utama program. Pilih: “Data” - “Filter” - “Filter lanjutan”.
4. Di jendela “Filter Lanjutan” yang muncul, isi jendela seperti yang ditunjukkan pada gambar di bawah teks ini.
Filter lanjutan memungkinkan Anda memfilter daftar di tempatnya, namun juga dapat menyalin hasil pemfilteran ke lokasi lain yang ditentukan pengguna.
5. Hasil filter lanjutan ditunjukkan pada gambar layar berikut. Filter lanjutan menampilkan semua catatan database yang berisi kata "balok" di kolom "Produk" - tugas selesai.
Huruf besar-kecil tidak mempengaruhi hasil pemfilteran!
Aturan untuk “kerja” gabungan dari beberapa kondisi seleksi
Filter tingkat lanjut di Excel memungkinkan Anda memfilter berdasarkan berbagai kombinasi kondisi pemfilteran yang sangat kompleks. Anda dapat mengatur beberapa kondisi berbeda untuk beberapa kolom, Anda dapat mengatur beberapa kondisi untuk satu kolom, atau Anda dapat mengatur parameter pemilihan menggunakan rumus - ada banyak pilihan!
Tapi hanya ada dua aturan utama! Semua kasus lainnya merupakan kombinasi berbeda dari kedua aturan ini.
1. Ketentuan seleksi diposting dalam satu baris di satu atau beberapa kolom berbeda pada tabel kriteria, perintahkan filter lanjutan untuk memperlihatkan baris yang mana semuanya tanpa perkecualian kondisi ini. (Parameter pemilihan dihubungkan dengan operator logika “DAN”.)
2. Ketentuan seleksi diposting pada jalur yang berbeda di satu atau lebih kolom tabel kriteria, perintahkan filter lanjutan untuk menampilkan semua baris yang mana setidaknya satu dari kondisi ini. (Parameter pemilihan dihubungkan dengan operator logika "ATAU".)
Mari kita ilustrasikan dengan contoh pengoperasian aturan di atas dan menunjukkan cara kerja filter lanjutan di Excel saat memecahkan masalah yang kompleks.
Karena kami memeriksa secara rinci urutan tindakan pengguna saat mengaktifkan filter lanjutan pada contoh sebelumnya, hanya kondisi pemilihan dan hasil pemfilteran akhir yang akan ditampilkan di bawah.
Tugas #6:
Filter informasi tentang pelat dengan berat kurang dari 0,1 ton di seluruh database.
Sebagai hasil dari filter lanjutan, semua pelat dasar dengan massa kurang dari 0,1 ton ditampilkan (ilustrasi aturan No. 1).
Tugas #7:
Menampilkan informasi tentang semua pelat yang tersedia di database, serta tentang produk lain yang beratnya lebih dari 1 ton.
Masalahnya terpecahkan - catatan ditampilkan untuk semua pelat dasar dan untuk semua produk yang lebih berat dari 1 ton (ilustrasi aturan No. 2).
Untuk membatalkan tindakan filter lanjutan, Anda harus menjalankan perintah di menu utama programUnggul"Data" - "Filter" - "Tampilkan semua".
Saya ingin menarik perhatian Anda pada perlunya memantau dengan cermat kebenaran penentuan rentang database awal dan rentang tabel kondisi di kotak dialog tarik-turun "Filter Lanjutan"!
Secara khusus, dalam contoh terakhir Anda perlu menentukan: “Rentang kondisi: $A$1: $ F$3 »!
Jika ada baris yang benar-benar kosong dalam rentang kondisi atau di database Anda, filter lanjutan tidak akan berfungsi!
Hasil.
Anda dapat menggunakan karakter pengganti umum dan simbol matematika saat memasukkan istilah penelusuran:
- * - sejumlah karakter apa pun
- ? – salah satu simbol
- = - sama
- < — меньше
- > - lebih lanjut
- <= — меньше или равно
- >= - lebih besar dari atau sama dengan
- <>- tidak sama
Filter lanjutan di Excel fleksibel dan informatif untuk digunakan. Kriteria pemilihan selalu ada di depan mata pengguna dalam bentuk tabel kriteria pemilihan, yang tentunya sangat memudahkan.
Jika ada banyak catatan, dan dalam database nyata ada ribuan catatan unik, memfilter catatan yang Anda perlukan menjadi masalah menggunakan filter otomatis, karena Anda harus menggulir daftar drop-down. Filter yang diperluas tidak memiliki kelemahan ini.
“Gali lebih dalam” kemampuan alat ini - ini layak untuk dipahami! Peningkatan efisiensi pekerjaan Anda akan berkali-kali lipat menutupi waktu yang dihabiskan untuk belajar!
Kelanjutan topik menyimpan dan mengelola sejumlah besar informasi- di artikel seri berikutnya.
aku memohon hormat karya penulis langganan untuk pengumuman artikel di jendela yang terletak di akhir setiap artikel atau di jendela di bagian atas halaman!
Pembaca yang budiman, tulis pertanyaan dan komentar di komentar di bagian bawah halaman.
Memfilter data Excel membantu Anda dengan cepat mengatur kondisi untuk baris yang perlu ditampilkan, dan menyembunyikan baris lain yang tidak memenuhi kondisi ini.
Filter dipasang pada judul tabel dan subjudul; Hal utama adalah sel tempat filter akan dipasang tidak kosong. Dan itu terletak di menu buku kerja Excel pada tab “Data”, bagian “Penyortiran dan Filter”:
Dengan mengklik ikon "Filter", sel teratas dari rentang akan ditentukan sebagai judul dan tidak akan ikut serta dalam pemfilteran. Judul akan ditandai dengan ikon. Klik untuk melihat opsi filter:
Filter di Excel memungkinkan Anda mengurutkan. Ingatlah bahwa jika Anda belum memilih semua kolom dalam tabel, tetapi hanya sebagian saja, dan menerapkan pengurutan, datanya akan hilang.
“Filter menurut warna” memungkinkan Anda memilih baris dalam kolom yang memiliki font atau warna isian tertentu. Anda hanya dapat memilih satu warna.
“Filter teks” memungkinkan untuk menyetel kondisi tertentu untuk string, seperti “sama dengan”, “tidak sama dengan”, dan lainnya. Dengan memilih salah satu item ini, sebuah jendela akan muncul:
Anda dapat mengatur ketentuan berikut di sini:
- Kondisi “sama” dan “tidak sama” tidak memerlukan penjelasan, karena semuanya sangat jelas bagi mereka;
- “lebih besar dari”, “kurang dari”, “lebih besar atau sama dengan”, dan “kurang dari atau sama dengan”. Bagaimana string dapat dibandingkan satu sama lain? Untuk memahami hal ini, ingat bagaimana Excel melakukan pengurutan. Itu. Semakin jauh suatu baris dalam daftar pengurutan, semakin besar nilainya. Pernyataan berikut ini benar (benar): A<Б; АА>A; A<=Я; 5 яблок < апельсин.
- “dimulai dengan”, “tidak diawali dengan”, “diakhiri dengan”, “tidak diakhiri dengan”, “berisi”, dan “tidak mengandung”. Pada prinsipnya, kondisi tersebut sudah cukup jelas dan dapat mengambil suatu karakter atau sekumpulan karakter sebagai nilai. Perhatikan petunjuk di jendela yang terletak di bawah semua ketentuan (penjelasan akan menyusul).
Jika perlu, Anda dapat mengatur 2 kondisi menggunakan logika “AND” atau “OR”.
Jika “AND” dipilih, semua kondisi harus dipenuhi. Pastikan kondisinya tidak mengecualikan teman, misalnya "<Значение И >Artinya", karena tidak ada sesuatu pun pada saat yang sama yang dapat lebih atau kurang dari indikator yang sama.
Saat menggunakan "ATAU", setidaknya salah satu kondisi yang ditentukan harus dipenuhi.
Ada petunjuk di bagian paling akhir jendela filter otomatis khusus. Bagian pertamanya: “Tanda tanya “”?” berarti salah satu karakter...”. Itu. ketika menetapkan kondisi ketika tidak mungkin untuk secara akurat menentukan karakter di tempat tertentu dalam sebuah baris, gantikan “?” sebagai gantinya. Contoh kondisi:
- Dimulai dengan “?va” (dimulai dengan karakter apa pun diikuti dengan karakter “va”) akan mengembalikan hasil: “Ivanov”, “Ivanova”, “kuarsa”, “mak comblang” dan string lain yang sesuai dengan kondisi;
- Sama dengan "???????" – akan mengembalikan string yang berisi 7 karakter apa pun.
Bagian kedua dari petunjuk: “Tanda ""*"" menunjukkan urutan karakter apa pun." Jika tidak mungkin untuk menentukan dalam kondisi karakter mana dan dalam jumlah berapa yang harus ada di baris, maka gantikan “*” sebagai gantinya. Contoh kondisi:
- Diakhiri dengan “o*t” (diakhiri dengan karakter “o”, diikuti dengan rangkaian karakter apa pun, lalu karakter “t”) akan mengembalikan hasil: “sweat”, “cake”, “turnover” dan bahkan yang ini - "rvnshchuooovyunisvrunct".
- Sama dengan "*" – akan mengembalikan string yang berisi setidaknya satu karakter.
Selain filter teks, ada “Filter numerik”, yang pada dasarnya menerima ketentuan yang sama dengan filter teks, tetapi juga memiliki ketentuan tambahan yang hanya terkait dengan angka:
- “Di Atas Rata-Rata” dan “Di Bawah Rata-Rata” – masing-masing mengembalikan nilai yang berada di atas dan di bawah rata-rata. Rata-rata dihitung berdasarkan semua nilai numerik dalam daftar kolom;
- “10 Pertama…” – mengklik item ini akan menampilkan jendela:
Di sini Anda dapat mengatur elemen mana yang akan ditampilkan pertama dari yang terbesar atau pertama dari yang terkecil. Juga, berapa banyak elemen yang akan ditampilkan jika item “elemen daftar” dipilih di bidang terakhir. Jika item “% dari jumlah elemen” dipilih, nilai kedua menentukan persentase ini. Itu. jika ada 10 nilai dalam daftar, nilai tertinggi (atau terendah) akan dipilih. Jika ada 1000 nilai dalam daftar, maka nilai pertama atau 100 terakhir.
