Ini adalah satu lagi contoh kelajuan formula. Katakan bahawa anda perlu melakukan 12 lajur VLOOKUP. Anda boleh mempercepatnya dengan menggunakan satu fungsi MATCH dan 12 INDEX.
Dalam gambar berikut, anda perlu melakukan 12 fungsi VLOOKUP untuk setiap nombor akaun. VLOOKUP hebat, tetapi memerlukan banyak masa untuk melakukan pengiraan.
Selain itu, formula harus diedit di setiap sel semasa anda menyalinnya. Hujah ketiga harus berubah dari 2 hingga 3 untuk Februari, kemudian 4 untuk bulan Mac, dan seterusnya.
Satu penyelesaian adalah dengan menambahkan baris dengan nombor lajur. Kemudian, argumen ke-3 VLOOKUP dapat menunjukkan baris ini. Sekurang-kurangnya anda boleh menyalin formula yang sama dari B4 dan tampal ke C4: M4 sebelum menyalin keseluruhan susunan.
Tetapi inilah pendekatan yang lebih pantas. Tambahkan lajur B baru dengan Di mana? sebagai tajuk. Lajur B mengandungi fungsi MATCH. Fungsi ini sangat serupa dengan VLOOKUP: Anda mencari nilai di A4 pada lajur P4: P227. 0 di hujungnya seperti Palsu di akhir VLOOKUP. Ini menentukan bahawa anda mahukan padanan yang tepat. Inilah perbezaan besar: MATCH kembali di mana nilainya dijumpai. Jawapan 208 mengatakan bahawa A308 adalah sel ke-208 dalam julat P4: P227. Dari perspektif masa recalc, MATCH dan VLOOKUP hampir sama.
Saya dapat mendengar apa yang anda fikirkan. "Apa gunanya mengetahui di mana sesuatu berada? Saya tidak pernah memanggil pengurus dan bertanya, 'Baris apa yang boleh diterima?' "
Walaupun manusia jarang bertanya pada baris apa, fungsi INDEX dapat menggunakan kedudukan itu. Formula berikut memberitahu Excel untuk mengembalikan item ke-208 dari Q4: Q227.
Semasa anda menyalin formula ini, susunan nilai bergerak di seluruh jadual carian. Untuk setiap baris, anda melakukan satu fungsi MATCH dan 12 INDEX. Fungsi INDEX sangat pantas berbanding dengan VLOOKUP. Keseluruhan rangkaian formula akan mengira 85% lebih pantas daripada 12 lajur VLOOKUP.
Tonton Video
- Katakan bahawa anda perlu melakukan 12 lajur VLOOKUP
- Gunakan tanda dolar dengan berhati-hati sebelum lajur nilai carian
- Gunakan tanda-tanda empat dolar dengan berhati-hati untuk jadual carian
- Anda masih menyusun hujah lajur ketiga.
- Salah satu penyelesaian yang biasa adalah dengan menambahkan barisan sel pembantu dengan nombor lajur.
- Penyelesaian lain yang kurang cekap adalah menggunakan COLUMN (B2) di dalam formula VLOOKUP.
- Tetapi, melakukan 12 VLOOKUP untuk setiap baris sangat tidak cekap
- Sebagai gantinya, tambahkan kolum pembantu dengan tajuk DI SANA dan lakukan satu Pertandingan.
- PERTANDINGAN mengambil masa selama VLOOKUP untuk bulan Januari.
- Anda kemudian boleh menggunakan 12 fungsi INDEX. Ini sangat pantas berbanding dengan VLOOKUP.
- INDEX akan menunjukkan satu ruangan jawapan dengan $ sebelum baris.
- INDEX akan menunjuk ke ruang penolong dengan $ sebelum lajur.
Transkrip Video
Belajar Excel dari podcast, episod 2028 - Mengganti Banyak VLOOKUP dengan satu PERTANDINGAN!
Klik “i” di sudut kanan atas untuk masuk ke senarai main, saya akan menyiarkan keseluruhan buku ini!
Hei, selamat datang kembali ke netcast, saya Bill Jelen! Ini masalah klasik, kita mesti buat VLOOKUP sekali setiap bulan, bukan? Dan anda boleh sangat berhati-hati di sini mengenai menekan F4 sebanyak 3 kali untuk menguncinya ke lajur, dan kemudian menekan F4 sekali kunci ke seluruh baris. Tetapi apabila anda sampai ke tahap ini, angka 2, PALSU yang 2 itu dikodkan keras, dan semasa anda menyalinnya, anda perlu mengedit 2 hingga 3, bukan? Sekarang, satu cara yang tidak cekap untuk melakukan ini, cara yang saya tidak suka adalah dengan menggunakan lajur B1. Lajur B1 tentu saja 2, tetapi semasa anda menyalinnya, lihat bahawa ia akan berubah menjadi lajur C1, yang merupakan 3, tetapi fikirkanlah, ini terus menerus mencari nombor lajur berulang-ulang. Oleh itu, apa yang saya lihat orang lakukan dan mengapa, anda tahu, lebih memilih daripada lajur, adakah kita akan Ctrl-drag itu,letakkan angka 2-13 di atas di sel pembantu, dan kemudian, ketika sampai ke titik ini, kita naik dan menentukan nombor lajur itu. Tekan F4 2 kali untuk menguncinya ke baris,, SALAH dan sebagainya. Tetapi walaupun dengan kaedah itu, VLOOKUP sangat tidak cekap, kerana ia harus mencari semua item ini di sini sehingga ia menemui A308 dan itulah yang diketahui B4. Apabila ia kemudian bergerak ke C4, ia lupa bahawa ia hanya berjalan dan kelihatan, dan ia bermula dari awal lagi, baiklah. Oleh itu, anda mempunyai salah satu fungsi paling lambat di seluruh Excel, VLOOKUP, PALSU dilakukan berulang-ulang untuk item yang sama.kerana ia mesti mencari semua item ini di sini sehingga ia menemui A308 dan itulah B4. Apabila ia kemudian bergerak ke C4, ia lupa bahawa ia hanya berjalan dan kelihatan, dan ia bermula lagi, baiklah. Oleh itu, anda mempunyai salah satu fungsi paling lambat di seluruh Excel, VLOOKUP, PALSU dilakukan berulang-ulang untuk item yang sama.kerana ia mesti mencari semua item ini di sini sehingga ia menemui A308 dan itulah B4. Apabila ia kemudian bergerak ke C4, ia lupa bahawa ia hanya berjalan dan kelihatan, dan ia bermula dari awal lagi, baiklah. Oleh itu, anda mempunyai salah satu fungsi paling lambat di seluruh Excel, VLOOKUP, PALSU dilakukan berulang-ulang untuk item yang sama.
Jadi inilah cara yang jauh lebih cepat, kita akan memasukkan ruangan penolong, dan ruangan penolong ini saya namakan Di mana? Seperti di mana sih A308? Kami akan menggunakan = MATCH, cari A308 di baris pertama meja, tekan F4 di sana, 0 untuk pencocokan tepat, baiklah, ia memberitahu kita bahawa "Hei, lihat itu, itu berturut-turut, 6, bagaimana hebat kan? " Tetapi semasa kami menyalin, lihat, ia berada di tempat yang berlainan sepanjang masa. Baiklah, sekarang perlawanan ini berlangsung selagi VLOOKUP Januari berlangsung, di sana mereka mati walaupun, tetapi inilah yang mengagumkan. Dari sana kita tidak perlu melakukan VLOOKUP untuk sisa baris, kita hanya boleh melakukan = INDEX, INDEX mengatakan "Inilah pelbagai jawapan." Saya akan pergi ke sel Januari, dan saya akan berhati-hati di sini tekan F4 2 kali sehingga saya menguncinya hingga 4: 227,tetapi Q dibenarkan untuk berubah semasa saya bergerak. Koma, dan kemudian ingin tahu baris apa, baik itu akan menjadi jawapannya di B4, saya akan menekan F4 3 kali untuk mendapatkan $ sebelum B, baiklah, salinnya.
Formula ini, formula INDEX, 12 ini akan berlaku dalam masa yang lebih sedikit daripada masa yang diperlukan untuk melakukan VLOOKUP Februari, baiklah. Sekiranya kita meletakkan pemasa Charles Williams, ini akan mengira kira-kira 14% masa 12 VLOOKUP. Pengurus anda tidak mahu melihat di mana? Baiklah, sembunyikan lajur itu, semuanya tetap berfungsi, baiklah, ini adalah cara yang baik untuk mempercepat 12 bulan atau 52 minggu VLOOKUP Baiklah, petua ini dan banyak lagi petua terdapat dalam buku ini. Klik "i" di sudut kanan atas di sana, anda boleh membeli buku itu, e-buku $ 10, $ 25 untuk buku cetak, baiklah.
Jadi hari ini kami mempunyai masalah di mana 12 lajur VLOOKUP, anda boleh memasukkan $ dengan berhati-hati, tetapi kemudian argumen ke-3 itu masih harus dikodkan dengan keras. Anda boleh menggunakan lajur (B2), saya bukan peminatnya, kerana terdapat ratusan baris * 12 lajur yang menghitungnya berulang-ulang. Cukup gunakan sel pembantu berturut-turut, letakkan angka 2-12 dan tunjuk pada itu, ia masih tidak cekap, kerana VLOOKUP setelah keluar Januari, ia mesti bermula pada awal bulan Februari. Oleh itu, saya cadangkan untuk menambahkan lajur dengan tajuk "Di mana?" dan melakukan PERTANDINGAN tunggal di sana. PERTANDINGAN itu memakan masa selama VLOOKUP untuk bulan Januari, tetapi kemudian 12 fungsi INDEX akan memakan masa yang lebih sedikit daripada VLOOKUP untuk bulan Februari, dan anda telah menghabiskan banyak masa. Sekali lagi, berhati-hati dengan fungsi $ di INDEX di kedua tempat, satu tepat sebelum baris,dan yang lain sebelum lajur, rujukan campuran di kedua-dua mereka.
Hei, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa anda di lain kali untuk siaran net lain!
Muat turun fail
Muat turun fail contoh di sini: Podcast2028.xlsx
