Jika Anda memiliki lembar kerja yang besar, banyak VLOOKUP dapat mulai memperlambat segalanya. Apakah Anda memiliki lembar kerja yang lambat karena VLOOKUP? Saya berbicara tentang lembar kerja yang membutuhkan 40 detik atau 4 menit untuk menghitung. Dalam artikel hari ini, rumus luar biasa dengan dua VLOOKUP menggunakan pencarian rentang akan menyelesaikan masalah.
VLOOKUP adalah fungsi yang relatif mahal. Saat Anda mencari pencocokan tepat, Excel harus melihat tabel pencarian satu per satu.
Buku kerja yang saya gunakan hari ini melakukan 7000 VLOOKUP menjadi tabel yang terdiri dari 116.000 item. Pada mesin 64-bit yang sangat cepat dengan 8 inti, waktu penghitungan ulang adalah 3,01 detik.
Salah satu cara untuk meningkatkan VLOOKUP adalah dengan memindahkan item terlaris ke atas tabel pencarian. Dapatkan laporan dari 100 barang terlaris teratas dan pindahkan barang-barang itu ke atas daftar. Mengurutkan berdasarkan popularitas meningkatkan waktu rekalc menjadi 0,369 detik. Ini delapan kali lebih cepat dari hasil pertama.
Tapi ada cara untuk lebih mempercepat. Saat Anda membangun VLOOKUP Anda, ketika Anda sampai pada argumen keempat untuk memilih Salah, ada opsi lain yang hampir tidak pernah digunakan. Excel mengatakan "Benar" melakukan "kecocokan perkiraan." Ini sama sekali tidak benar. Jika tim Excel jujur, mereka akan menjelaskan bahwa True “sering kali memberikan jawaban yang benar, tetapi di lain waktu, tanpa peringatan apa pun, kami akan menyelipkan jawaban yang salah di sana. Saya harap Anda tidak keberatan menyampaikan kembali nomor Anda ke Securities and Exchange Commission. ”
Tentu, ada waktu yang tepat untuk menggunakan True. Lihat artikel ini. Tetapi akan sangat buruk untuk menggunakan True saat Anda mencoba melakukan pencocokan persis.
Jika Anda mencoba menggunakan Benar untuk pencocokan tepat, Anda akan sering kali mendapatkan jawaban yang benar. Tetapi ketika item yang Anda cari tidak ada dalam tabel, Excel akan memberi Anda nilai dari baris yang berbeda. Ini adalah bagian yang membuat "True" menjadi non-starter untuk semua orang di Akuntansi. Tutup tidak pernah benar dalam Akuntansi.
Catatan
Saya mempelajari trik berikut dari Charles Williams. Dia adalah ahli kecepatan lembar kerja terkemuka di dunia. Jika Anda memiliki buku kerja yang lambat, pekerjakan Charles Williams untuk konsultasi setengah hari. Dia dapat menemukan hambatan dan membuat lembar kerja Anda lebih cepat. Temukan Charles di http://www.decisionmodels.com.
Sementara saya dan semua akuntan menolak argumen "Benar" dari VLOOKUP karena tidak dapat diprediksi, Charles Williams berpendapat Benar. Dia menunjukkan bahwa Benar jauh lebih cepat daripada Salah. Ratusan kali lebih cepat. Dia mengakui bahwa terkadang Anda mendapatkan jawaban yang salah. Tapi dia punya cara untuk menghadapi jawaban yang salah.
Charles sebenarnya ingin Anda melakukan dua VLOOKUP. Pertama, lakukan VLOOKUP dan kembalikan kolom 1 dari tabel. Lihat apakah hasil awalnya adalah yang Anda cari. Jika hasilnya cocok, maka Anda tahu aman untuk melakukan VLOOKUP yang sebenarnya untuk mengembalikan beberapa kolom lain dari tabel:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Sepintas lalu, ini tampak gila. Untuk menggunakan metode Charles, Anda harus melakukan VLOOKUP dua kali lebih banyak. Namun, saat Anda menghitung waktu untuk metode ini, ini 35 kali lebih cepat dari VLOOKUP normal.
Perhatikan bahwa meskipun sebagian besar tabel pencarian tidak harus diurutkan, saat Anda menggunakan True sebagai argumen keempat, tabel tersebut harus diurutkan. Untuk diskusi 7 menit tentang bagaimana versi True VLOOKUP melompat melalui tabel pencarian, lihat http://mrx.cl/TrueVLOOKUP.
Terima kasih kepada Charles Williams karena telah mengajari saya fitur ini dan kepada Scott St. Amant karena telah menominasikannya untuk tip 40 teratas.
Menonton video
- VLOOKUP saat digunakan dengan False adalah fungsi yang lambat
- Menyortir data AZ tidak mempercepat fungsinya
- Mengurutkan berdasarkan popularitas dapat mempercepat fungsi tersebut
- Beralih ke VLOOKUP dengan True lebih cepat, tetapi akan melaporkan jawaban yang salah jika item tidak ditemukan
- Untuk mengurangi masalah, lakukan VLOOKUP (A2, Tabel, 1, True) untuk melihat apakah hasilnya A2 terlebih dahulu
- 14000 VLOOKUP (True) dan 7000 IF berjalan lebih cepat dari 7000 VLOOKUP (False)
Transkrip yang Dibuat Otomatis
- Pelajari Excel dari Podcast
- episode 2031 lebih cepat vlookup I'm
- Podcasting semua tip dalam buku ini
- klik I di pojok kanan atas
- untuk masuk ke daftar pantauan
- hey selamat datang kembali ke mr. pemeran hutnik
- Saya Bill Jelen, saya telah melakukan ini
- video sebelum itu salah satu favorit saya
- trik jika Anda memiliki tampilan jika Anda
- memiliki bintang vlookup mengambil 30 40 50
- detik empat menit Anda tahu apa-apa
- Anda akan menyukai video ini jika Anda
- vlookup stick satu detik cukup klik Next
- dan lanjutkan ke video berikutnya Aku punya
- vlookup di sini melihat ke tabel
- dari 115.000 item melakukan 7000 vlookup
- kami akan menggunakan beberapa Charles Williams
- dari kode Excel cepat untuk melihat berapa panjangnya
- diperlukan untuk melakukan ini vlookup baik-baik saja empat
- koma nol sembilan detik itulah
- vlookup khas dengan tanda koma salah di
- akhir dan ini semua muncul karena lama
- dahulu kala saya dipancing oleh seorang pria
- Twitter yang mengatakan itu akan lebih baik jika
- Anda akan mengurutkan tabel pencarian Anda a
- mengirim saya mengatakan tidak, itu tidak benar sama sekali
- tidak masalah apakah kita pergi a
- mengirim atau turun atau seluruhnya
- acak vlookup hanya harus pergi melihat
- dari barang ke barang ke barang dan begitu juga saat kita
- urutkan tabel untuk melihat yang sebenarnya diperlukan
- lebih lama empat koma delapan empat detik jadi
- Anda tahu itu tidak benar bahwa menyortir file
- meja akan membuatnya lebih cepat tetapi
- benar-benar hal yang bisa membuatnya berjalan
- lebih cepat jika entah bagaimana Anda bisa mengurutkan
- popularitas jika Anda bisa mendapatkan yang terbaik
- menjual barang-barang di bagian atas daftar
- bahkan Anda tahu lima puluh teratas Anda tahu
- apa 50 item terlaris Anda
- bawa mereka ke bagian atas daftar dan
- perhatikan bahwa detik turun menjadi 0,36
- detik, peningkatan waktu sepuluh kali lipat
- menggunakan urutkan berdasarkan popularitas sekarang, hei beberapa
- bertahun-tahun yang lalu saya cukup beruntung untuk menjadi
- diundang ke Amsterdam untuk mempresentasikan di sebuah
- KTT Excel di sana dan itu tidak seperti
- sebagian besar seminar saya hanya saya
- di sana ada dua trek jadi kamar a
- dan kamar B dan aku berada di kamar be
- berbicara tentang vlookup dan di kamar
- tebak siapa yang duduk di ruangan itu
- apakah Charles Williams baik-baik saja dan Charles
- ini dia
- namanya disebutkan melalui
- dinding jadi dia datang untuk menontonnya dia
- menonton demo kecil saya di sana ke mana saya pergi
- dari empat detik menjadi 0,36 detik he
- mendatangi saya setelah itu dia bilang saya yakin
- Anda cukup senang dengan itu
- perbaikan
- Saya katakan ya itu tenda penuh
- perbaikan sekarang Charles Charles memiliki
- layanan Excel cepat model keputusan kami
- model keputusan membatasi tempat kami berada
- setengah hari dia akan menganalisis buku kerja Anda
- dan dia mengklaim membuatnya menjadi seratus
- kali lebih cepat, dia akan menemukan
- hambatan Annette dan Charles Charles
- comes from he says look at comma false
- that you and your accountant friends are
- doing it is the slowest thing in Excel
- if you would do a comma true it's a
- thousand times faster and then Charles
- says this next Clause is if it doesn't
- really matter he says now sometimes it's
- wrong oh wait Charles you don't
- understand an accountant sometimes is
- wrong is a non-starter we do not accept
- sometimes it's wrong and and the time
- that it's wrong the comma true when
- you're doing a comma true is we go look
- for a P 3 2 2 1 1 and it's not found
- they're gonna give you the item just
- less alright and they're not gonna tell
- you we couldn't find it they're just
- gonna they're just gonna give you Adam
- just less that that's unacceptable and
- Charles says well here's what we could
- do imagine if you did a vlookup of P 3 2
- 2 1 1 into just column G ask for the
- first column comma true and see if what
- you get back is what you were looking
- for if what you get back is what you're
- looking for then you know it's safe to
- go to the second vlookup if it's not
- what you were looking for then you have
- an if statement there that says not
- found alright so we do a vlookup of a2
- into the table comma 1 see if it's equal
- to 82 if it is then it's safe to go on
- and do the second vlookup otherwise they
- not found I said Charles do you realize
- I'm doing 7000 vlookups and now you're
- gonna be doing 7 14000 vlookups and 7000
- if statements I I said you really think
- this is gonna be faster sure I said well
- I'll bet you a pint alright so here we
- go remember the vlookup that all of us
- are doing with the comma falls 4.0 9
- seconds the sort by popularity which is
- kind of hard to do
- Oh point three seconds here we go here's
- Charles Williams are you ready look at
- that point zero four four one hundredths
- of a second from four seconds down to
- four one hundreds of a second imagine if
- you had a spreadsheet that was taking
- for 40 seconds to calculate and how much
- faster would be using this to vlookup
- method it's an amazing trick and yeah I
- guess I stole the trick and put it in
- the book although I put a great cartoon
- version of Charles Williams in there
- saying he's the fastest guy in all of
- Excel you can buy this tip and all of
- the other tips in this book click that I
- at the top right hand corner
- all right so recap vlookup when used
- with false it's a slow function sorting
- the data a disease does not speed up the
- function unless you sell a lot of things
- to begin with a and B sorting by
- popularity does it's about a tenfold
- Peningkatan, Anda dapat beralih ke vlookup
- dengan benar tetapi akan melaporkan yang salah
- jawab jika barang tidak ditemukan jadi kita
- sebenarnya akan melakukan dua pencarian vlookup a
- dua di kolom satu di tabel dan
- lihat apakah yang kita dapatkan adalah dua jika
- aman untuk melakukan vlookup
- kolom umum untuk memiliki
- jika pernyataan itu mengatakan tidak ditemukan baik-baik saja
- oh hei terima kasih kepada Charles Williams untuk
- mengajariku trik luar biasa itu dan
- terima kasih untuk mampir akan melihat
- Anda lain kali untuk pemain lain dari
- MrExcel
Unduh berkas
Unduh file contoh di sini: Podcast2031.xlsm
