Menggunakan Filter Tingkat Lanjut di Excel untuk memecahkan masalah Mort. Meskipun filter biasa menjadi lebih kuat, ada kalanya Filter Lanjutan dapat melakukan beberapa trik yang orang lain tidak bisa.
Menonton video
- Filter Lanjutan lebih "canggih" daripada filter biasa karena:
- 1) Dapat menyalin ke rentang baru
- 2) Anda dapat membuat kriteria yang lebih kompleks seperti Bidang 1 = A atau Bidang 2 = A
- 3) Cepat
- Mort mencoba memproses 100K baris di VBA dengan melakukan perulangan melalui record atau menggunakan array
- Akan selalu lebih cepat menggunakan fitur Excel bawaan daripada menulis kode Anda sendiri.
- Anda memerlukan Rentang Input, dan kemudian Rentang Kriteria dan / atau Rentang Output
- Untuk rentang masukan: satu baris judul di atas data
- Tambahkan baris sementara untuk tajuk
- Untuk rentang keluaran: deretan judul untuk kolom yang ingin Anda ekstrak
- Untuk rentang kriteria: judul di baris 1, nilai dimulai di baris 2
- Komplikasi: Versi Excel yang lebih lama tidak mengizinkan rentang keluaran berada di lembar lain
- Jika Anda menulis makro yang mungkin dijalankan pada tahun 2003, gunakan rentang bernama untuk menghindari rentang input
Transkrip Video
Pelajari Excel dari Podcast, Episode 2060: Filter Lanjutan Excel
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dikirim oleh Mort. Mort, dia memiliki 100.000 baris data dan dia tertarik pada Kolom A, B dan D di mana Kolom C cocok dengan beberapa tahun tertentu. Jadi dia ingin seseorang memasukkan satu tahun dan kemudian mendapatkan Kolom A, B dan D. Dan Mort memiliki beberapa VBA di mana dia menggunakan array untuk melakukan ini dan saya berkata, "Tunggu sebentar, Anda tahu, filter lanjutan akan melakukan ini jauh lebih baik. ” Baiklah, dan sekarang hanya untuk meninjau, saya kembali, saya melihat kembali video saya. Saya belum membahas filter lanjutan dalam waktu yang lama jadi kita harus membicarakannya.
Filter lanjutan memerlukan rentang masukan dan setidaknya salah satu dari yang berikut: rentang kriteria atau rentang keluaran. Meskipun hari ini kita akan menggunakan keduanya. Baiklah, jadi kisaran masukan adalah data Anda dan Anda harus memiliki judul di atas data. Jadi, Mort tidak memiliki judul dan jadi saya akan memasukkan baris sementara di sini dan hanya melakukan seperti Field 1. Mort tahu apa datanya sehingga dia bisa meletakkan judul sebenarnya di sana. Dan kami tidak menggunakan apa pun yang disebut-data ini di Kolom E hingga O, jadi saya tidak perlu menambahkan judul di sana, oke? Jadi sekarang, A1 hingga D, 100000 menjadi jangkauan input saya. Dan kemudian kisaran keluaran dan kisaran kriteria - Nah, kisaran keluaran hanyalah daftar dari judul yang Anda inginkan. Jadi saya akan meletakkan kisaran keluaran di sini dan kami tidak membutuhkan Bidang 3 jadi saya 'Aku hanya akan melepaskannya ke samping. Jadi sekarang, kisaran ini di sini, A1 hingga C1 menjadi kisaran keluaran saya yang memberi tahu Excel bidang mana yang saya inginkan dari kisaran masukan. Dan, mereka bisa dalam urutan yang berbeda jika Anda ingin menyusun ulang hal-hal, seperti jika saya ingin Bidang 4 dulu, lalu Bidang 1 lalu Bidang 2. Dan sekali lagi, ini akan menjadi tajuk asli seperti nomor faktur. Saya hanya tidak tahu seperti apa data Mort itu.
Dan kemudian, rentang kriteria adalah tajuk dan nilai apa yang Anda inginkan. Jadi katakanlah saya mencoba mendapatkan sesuatu di tahun 2014. Ini menjadi rentang kriteria seperti itu. Baiklah, hanya satu kata peringatan di sini. Saya menggunakan Excel 2016 dan dimungkinkan untuk melakukan filter lanjutan antara dua lembar di Excel 2016 tetapi jika Anda kembali, dan saya tidak ingat jalan ke belakang, mungkin tahun 2003, saya tidak yakin. Di beberapa titik di masa lalu, biasanya Anda tidak dapat melakukan filter lanjutan dari satu lembar ke lembar lainnya sehingga Anda harus datang ke sini dan memberi nama rentang masukan Anda. Anda harus membuat nama di sini. MyName atau semacamnya, oke? Dan itulah cara Anda bisa melakukan ini, oke. Tidak harus di Excel 2016 tapi sekali lagi, saya 'Saya tidak yakin apakah Mort akan menjalankan ini di versi data yang lebih lama.
Baiklah, kembali ke sini di Data, kita pergi ke Filter Lanjutan, baiklah. Dan kami akan menyalin ke lokasi lain yang memungkinkan rentang keluaran kami di sana. Baiklah, jadi daftarnya berkisar, dimana datanya? Karena saya menggunakan Excel 2016, saya akan mengarahkan ke Data, alih-alih menggunakan rentang nama - Jadi itulah rentang masukan saya. Rentang kriteria adalah sel-sel itu di sana dan kemudian, di mana kita akan - keluaran, itu hanya akan menjadi tiga sel ini di sana. Dan kemudian kita klik OK. Baiklah, dan BAM! Begitu cepat, cepat. Dan bagaimana jika kita menginginkan tahun yang berbeda? Jika kita menginginkan tahun yang berbeda kita akan menghapus hasilnya, meletakkan 2015, dan kemudian melakukan filter lanjutan lagi, Salin ke lokasi lain, klik OK dan ada semua catatan 2015. Secepat kilat.
Baiklah sekarang, sementara saya penggemar filter lanjutan di Excel biasa, saya adalah penggemar berat filter lanjutan di VBA, baiklah, karena VBA membuat filter lanjutan sangat, sangat, sangat sederhana. Baiklah, jadi kita akan menulis beberapa kode di sini untuk Mort, dengan asumsi bahwa data Mort tidak memiliki judul dan kita harus menambahkan judul untuk sementara, oke? Jadi, saya akan beralih ke VBA, Alt + F11 dan kami akan menjalankan ini dari lembar kerja yang memiliki datanya. Jadi: Redupkan WS Sebagai Lembar Kerja, Atur WS = ActiveSheet. Dan kemudian, sisipkan Baris 1 dan tambahkan beberapa judul: A, B, Tahun, dan D. Cari tahu berapa banyak baris data yang kita miliki hari ini dan kemudian mulai dari sel A1 keluar 4 kolom ke baris terakhir, beri nama itu menjadi rentang masukan. Baiklah, lalu ini sebenarnya kode Mort di sini, di mana dia meminta InputBox,mendapatkan tahun yang mereka inginkan dan kemudian dia bertanya tahun berapa atau apa yang ingin mereka beri nama pada lembaran baru, oke. Jadi itu akan benar-benar menyisipkan sheet pada Fly dan kemudian I- Dimension sheet baru, WSN, sebagai ActiveSheet. Jadi saya tahu bahwa WS adalah lembar asli, WSN adalah lembar baru yang baru saja ditambahkan. Di lembar baru, letakkan rentang kriteria sehingga di bawah Kolom E ada judul yang cocok dengan judul ini di sini, dan kemudian, jawaban mana pun yang mereka berikan masuk ke E2. Rentang keluaran akan menjadi tiga judul saya yang lain: A, B, dan D. Dan lagi, jika Anda atau Mort mengubahnya menjadi judul nyata yang mungkin lebih baik dilakukan daripada A, B, D, dan Anda juga akan ubah ini menjadi judul asli, oke? Jadi semua ini hanyalah sedikit pekerjaan awal di sini. Baris kode yang luar biasa ini akan melakukan seluruh filter lanjutan. Begitu,dari InputRange kami melakukan AdvancedFilter, kami akan menyalin. Itulah filter pilihan kami di tempat atau salin. CriteriaRange adalah E1 hingga E2, CopyToRange adalah A hingga C. Nilai unik -Tidak, kami ingin semua nilai. Baiklah, satu baris kode di sana melakukan semua keajaiban perulangan melalui semua catatan atau menggantikan perulangan melalui semua catatan atau melakukan array. Dan kemudian kami selesai, kami akan menghapus rentang kriteria dan kemudian menghapus Baris 1 kembali pada lembar kerja asli.Dan kemudian kami selesai, kami akan menghapus rentang kriteria dan kemudian menghapus Baris 1 kembali pada lembar kerja asli.Dan kemudian kami selesai, kami akan menghapus rentang kriteria dan kemudian menghapus Baris 1 kembali pada lembar kerja asli.
Oke, jadi mari kita kembali ke data kita di sini. Kami akan membuatnya mudah untuk menjalankan ini, jadi: Sisipkan, Bentuk dan beri nama Filter, Beranda, Tengah, Tengah, Lebih Besar, Lebih Besar, Lebih Besar, klik kanan, Tetapkan Makro, dan tetapkan ke MacroForMort. Baiklah, jadi ini dia. Kami akan melakukan tes. Lihat kita berada di lembar data, klik Filter, tahun berapa yang kita inginkan? Kami ingin 2015. Saya ingin menyebutnya apa? Saya ingin menyebutnya 2015, oke. Dan BAM! Itu sudah selesai. Begitu cepat, begitu cepat.
Sekarang, karena data asli Mort tidak memiliki heading, mungkin data ini seharusnya tidak memiliki heading. Jadi mari kita pergi Alt + F11, di sini kami ingin menghapus rentang kriteria. Kami juga akan Baris (1). Hapus. Baiklah, jadi sekarang saat kita membahas ini lagi, itu akan menghilangkan heading itu. Dan mari kita - Daripada menjalankan semuanya dengan cepat, mari kita lihat di sini dengan 2014. Jadi saya akan memilih satu sel pada Data, Alt + F11, dan saya ingin menjalankannya sampai ke titik di mana kita melakukan filter lanjutan. Jadi kita dapat melihat dan melihat apa yang dilakukan makro secara keseluruhan di sini. Jadi kita akan klik Jalankan, dan saya ingin mendapatkan 2014. 2014, oke. Jadi, tekan F8, kita akan melakukan filter lanjutan. Kami dapat memutar kembali ke Excel di sini dan melihat apa yang terjadi.
Hal pertama yang terjadi- Sekarang, hal pertama yang terjadi adalah kita telah menambahkan baris sementara baru dengan tajuk. Memasukkan lembar kerja ini, membuat rentang kriteria dengan judul dan tahun apa yang mereka masukkan, memilih bidang yang ingin kita lakukan dan kemudian kembali ke VBA, saya akan menjalankan baris kode berikutnya, yaitu F8 yang melakukan filter lanjutan di sana . Ini sangat cepat dan Anda akan melihat bahwa itu sekarang telah membawa kami semua rekor. Dari sana, tinggal sedikit pembersihan, hapus ini, hapus ini. Saya akan kembali ke data dan menghapus Baris 1 dan kita akan mulai. Jadi saya akan membiarkan sisanya berjalan, hapus breakpoint itu, oke? Jadi ada VBA. Bagi saya, ini menurut saya cara tercepat, cara tercepat untuk pergi.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Baiklah, baiklah, begitulah. Saya ingin berterima kasih kepada Mort karena telah mengirimkan pertanyaan itu. Saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2060.xlsm
