Menggunakan Advanced Filter di Excel untuk menyelesaikan masalah Mort. Walaupun penapis biasa menjadi lebih kuat, masih ada saatnya Penapis Lanjutan dapat melakukan beberapa muslihat yang tidak dapat dilakukan oleh orang lain.
Tonton Video
- Penapis Lanjutan lebih "maju" daripada penapis biasa kerana:
- 1) Ia boleh menyalin ke julat baru
- 2) Anda boleh membina kriteria yang lebih kompleks seperti Medan 1 = A atau Medan 2 = A
- 3) Ia pantas
- Mort cuba memproses 100K baris dalam VBA dengan melengkapkan rekod atau menggunakan array
- Akan lebih pantas menggunakan ciri Excel terbina dalam daripada menulis kod anda sendiri.
- Anda memerlukan julat Input, dan kemudian Julat Kriteria dan / atau Julat Keluaran
- Untuk julat input: satu baris tajuk di atas data
- Tambahkan baris sementara untuk tajuk
- Untuk julat keluaran: deretan tajuk untuk lajur yang ingin anda ekstrak
- Untuk rentang kriteria: tajuk pada baris 1, nilai bermula pada baris 2
- Komplikasi: Versi Excel yang lebih lama tidak membenarkan julat output berada di helaian lain
- Sekiranya anda menulis makro yang mungkin dijalankan pada tahun 2003, gunakan julat yang dinamakan untuk julat input untuk mengelakkan
Transkrip Video
Belajar Excel dari Podcast, Episode 2060: Penapis Lanjutan Excel
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Soalan hari ini dihantar oleh Mort. Mort, dia mempunyai 100,000 baris data dan dia berminat dengan Lajur A, B dan D di mana Lajur C sesuai dengan tahun tertentu. Oleh itu, dia mahu seseorang memasuki tahun dan kemudian mendapatkan Lajur A, B dan D. Dan Mort mempunyai beberapa VBA di mana dia menggunakan tatasusunan untuk melakukan ini dan saya berkata, "Tunggu sebentar, anda tahu, penapis lanjutan akan melakukan ini jauh lebih baik. " Baiklah, dan sekarang hanya untuk meninjau, saya kembali, saya melihat semula video saya. Saya sudah lama tidak menapis penapis lanjutan, jadi kita harus membincangkan perkara ini.
Penapis lanjutan memerlukan julat input dan kemudian sekurang-kurangnya salah satunya: julat kriteria atau julat keluaran. Walaupun hari ini kita akan menggunakan kedua-duanya. Baiklah, jadi julat input adalah data anda dan anda harus mempunyai tajuk di atas data. Oleh itu, Mort tidak mempunyai tajuk dan saya akan memasukkan baris untuk sementara di sini dan hanya seperti Bidang 1. Mort tahu apa datanya dan sehingga dia dapat meletakkan tajuk sebenar di sana. Dan kami tidak menggunakan apa-apa yang disebut-data ini di Lajur E hingga O, jadi saya tidak perlu menambahkan tajuk di sana, baiklah? Jadi sekarang, A1 hingga D, 100000 menjadi julat input saya. Dan kemudian julat output dan rentang kriteria - Baiklah, julat output hanyalah senarai tajuk yang anda mahukan. Jadi saya akan meletakkan julat output di sini dan kami tidak memerlukan Field 3 jadi saya 'Saya akan melepaskannya ke sebelah. Jadi sekarang, julat ini di sini, A1 hingga C1 menjadi julat output saya yang memberitahu Excel bidang mana yang saya mahukan dari julat input. Dan, mereka mungkin berada dalam urutan yang berbeza jika anda ingin menyusun semula perkara, seperti jika saya mahu Field 4 terlebih dahulu, dan kemudian Field 1 kemudian Field 2. Dan sekali lagi, ini akan menjadi tajuk sebenar seperti nombor invois. Saya tidak tahu seperti apa data Mort.
Kemudian, rentang kriteria adalah tajuk dan nilai apa yang anda mahukan. Oleh itu, katakan saya berusaha mendapatkan apa-apa pada tahun 2014. Ini menjadi kriteria seperti itu. Baiklah, hanya dengan berhati-hati di sini. Saya berada di Excel 2016 dan mungkin membuat penapis lanjutan antara dua helaian di Excel 2016 tetapi jika anda kembali, dan saya tidak ingat jalan kembali, mungkin tahun 2003, saya tidak pasti. Pada suatu ketika dahulu, dulu anda tidak dapat melakukan penapis lanjutan dari satu helaian ke helaian yang lain sehingga anda harus datang ke sini dan memberi nama julat input anda. Anda mesti membuat nama di sini. MyName atau seumpamanya, baiklah? Dan itulah cara anda dapat melakukannya, baiklah. Tidak semestinya dalam Excel 2016 tetapi sekali lagi, saya 'Saya tidak pasti sama ada Mort akan menjalankannya dalam versi data yang lebih lama.
Baiklah, jadi kembali ke sini di Data, kita pergi ke Penapis Lanjutan, baiklah. Dan kami akan Menyalin ke lokasi lain yang membolehkan keluaran kami di sana. Baiklah, jadi julat senarai, di mana datanya? Kerana saya berada di Excel 2016, saya akan pergi ke Data, bukannya menggunakan julat nama - Jadi itulah julat input saya. Julat kriteria adalah sel-sel di sana dan kemudian, di mana kita akan - output ke, ia akan menjadi ketiga sel ini di sana. Dan kemudian kita klik OK. Baiklah, dan BAM! Begitulah pantas, pantasnya. Dan bagaimana jika kita mahukan tahun yang berbeza? Sekiranya kami menginginkan tahun yang berbeza, kami akan menghapus hasilnya, memasukkan tahun 2015, dan kemudian melakukan penapis lanjutan sekali lagi, Salin ke lokasi lain, klik OK dan ada semua catatan 2015. Sepantas kilat.
Baiklah sekarang, sementara saya peminat penapis lanjutan di Excel biasa, saya adalah peminat penapis canggih di VBA, baiklah, kerana VBA membuat penapis maju sangat, sangat, sangat mudah. Baiklah, jadi kita akan menulis beberapa kod di sini untuk Mort, dengan anggapan bahawa data Mort tidak mempunyai tajuk dan kita harus menambahkan tajuk untuk sementara waktu, baiklah? Oleh itu, saya akan beralih ke VBA, Alt + F11 dan kami akan menjalankannya dari lembaran kerja yang mempunyai data. Oleh itu: Dim WS Sebagai Lembaran Kerja, Tetapkan WS = ActiveSheet. Dan kemudian, masukkan Baris 1 dan tambahkan beberapa tajuk: A, B, Tahun, dan D. Cari tahu berapa baris data yang kita ada hari ini dan kemudian bermula dari sel A1 yang keluar 4 lajur hingga ke baris terakhir, namakan hingga julat input. Baiklah, dan inilah sebenarnya kod Mort di sini, di mana dia meminta InputBox,mendapat tahun yang mereka mahukan dan kemudian dia bertanya tahun apa atau apa yang mereka mahu namakan helaian baru, baiklah. Jadi ia sebenarnya akan memasukkan helaian pada Fly dan kemudian I- Dimension lembaran baru, WSN, sebagai ActiveSheet. Oleh itu, saya tahu bahawa WS adalah helaian asal, WSN adalah helaian baru yang baru sahaja ditambahkan. Pada lembaran baru, letakkan rentang kriteria sehingga di bawah Kolom E ada tajuk yang sesuai dengan tajuk ini di sini, dan kemudian, jawapan mana pun yang mereka berikan masuk dalam E2. Julat output akan menjadi tiga tajuk saya yang lain: A, B dan D. Dan sekali lagi, jika anda atau Mort mengubahnya menjadi tajuk sebenar yang mungkin merupakan perkara yang lebih baik daripada A, B, D, dan anda juga ubah ini menjadi tajuk sebenar, baiklah? Jadi semua ini hanyalah sedikit pra-kerja di sini. Baris kod yang hebat ini akan menjadikan keseluruhan penapis lanjutan. Jadi,dari InputRange kita melakukan AdvancedFilter, kita akan menyalin. Itu penapis pilihan kami di tempat atau salinan. CriteriaRange adalah E1 hingga E2, CopyToRange adalah A hingga C. Nilai unik -Tidak, kami mahukan semua nilai. Baiklah, satu baris kod di sana melakukan semua keajaiban gelung melalui semua rekod atau menggantikan gelung melalui semua rekod atau melakukan tatasusunan. Dan setelah selesai, kami akan membersihkan julat kriteria dan kemudian memadam Baris 1 kembali ke lembaran kerja asal.Dan setelah selesai, kami akan membersihkan julat kriteria dan kemudian memadam Baris 1 kembali ke lembaran kerja asal.Dan setelah selesai, kami akan membersihkan julat kriteria dan kemudian memadam Baris 1 kembali ke lembaran kerja asal.
Baiklah, jadi mari kita kembali ke sini untuk data kami. Kami akan memudahkan untuk menjalankannya, jadi: Masukkan, Bentuk dan panggil Penapis ini, Rumah, Pusat, Pusat, Lebih Besar, Lebih Besar, Lebih Besar, klik kanan, Tetapkan Makro, dan tetapkannya ke MacroForMort. Baiklah, ini dia. Kami akan melakukan ujian. Lihat kita ada di lembaran data, klik Tapisan, tahun apa yang kita mahukan? Kami mahu 2015. Apa yang saya mahu namakannya? Saya mahu menyebutnya 2015, baiklah. Dan BAM! Di sana ia selesai. Begitulah pantasnya, sebegitu cepatnya.
Sekarang, kerana data asli Mort tidak mempunyai tajuk, mungkin data ini tidak harus memiliki tajuk. Oleh itu, mari kita pergi Alt + F11, di sini kita ingin membersihkan julat kriteria. Kami juga akan Baris (1). Padam. Baiklah, jadi sekarang pada saat kita membahas ini, ia akan menyingkirkan tajuk tersebut. Dan mari kita - Daripada menjalankan semuanya dengan cepat, mari kita lihat di sini dengan tahun 2014. Oleh itu, saya akan memilih satu sel pada Data, Alt + F11, dan saya mahu berjalan hingga ke titik di mana kita melakukan penapis lanjutan. Oleh itu, kita dapat melihat dan melihat apa yang dilakukan oleh keseluruhan makro di sini. Oleh itu, kita akan klik Run, dan saya mahu mendapatkan 2014. 2014, baiklah. Oleh itu, tekan F8, kami akan melakukan penapis lanjutan. Kita boleh kembali ke Excel di sini dan melihat apa yang berlaku.
Perkara pertama yang berlaku- Sekarang, perkara pertama yang berlaku ialah kita menambah baris sementara baru dengan tajuk. Memasukkan lembaran kerja ini, membina rentang kriteria dengan tajuk dan tahun apa yang mereka masukkan, memilih bidang yang ingin kita lakukan dan kemudian kembali ke VBA, saya akan menjalankan baris kod seterusnya, iaitu F8 yang melakukan penapis lanjutan di sana . Ia sangat pantas dan anda akan melihat bahawa sekarang telah membawa kita semua rekod. Dari sana, hanya sedikit pembersihan, hapus ini, hapus ini. Saya akan kembali ke data dan memadam Baris 1 dan kami akan terus maju. Oleh itu, saya akan membiarkan yang lain berjalan, membuang titik putus itu, baiklah? Jadi ada VBA. Bagi saya, ini adalah cara terpantas, cara terpantas.
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, di sana anda memilikinya. Saya ingin mengucapkan terima kasih kepada Mort kerana telah menghantar soalan itu. Saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail contoh di sini: Podcast2060.xlsm
