Ini adalah contoh kecepatan rumus lainnya. Katakanlah Anda harus melakukan 12 kolom VLOOKUP. Anda dapat membuatnya lebih cepat dengan menggunakan satu fungsi MATCH dan 12 INDEX.
Pada gambar berikut, Anda harus melakukan 12 fungsi VLOOKUP untuk setiap nomor akun. VLOOKUP sangat berguna, tetapi membutuhkan banyak waktu untuk melakukan perhitungan.
Plus, rumusnya harus diedit di setiap sel saat Anda menyalin. Argumen ketiga harus diubah dari 2 menjadi 3 untuk Februari, lalu 4 untuk Maret, dan seterusnya.
Salah satu solusinya adalah menambahkan baris dengan nomor kolom. Kemudian, argumen ke-3 VLOOKUP dapat mengarah ke baris ini. Setidaknya Anda dapat menyalin rumus yang sama dari B4 dan menempelkannya ke C4: M4 sebelum menyalin seluruh set.
Tapi di sini ada pendekatan yang lebih cepat. Tambahkan kolom B baru dengan Where? sebagai tajuk. Kolom B berisi fungsi MATCH. Fungsi ini sangat mirip dengan VLOOKUP: Anda mencari nilai di A4 di kolom P4: P227. Angka 0 di akhir adalah seperti False di akhir VLOOKUP. Ini menentukan bahwa Anda menginginkan pencocokan tepat. Inilah perbedaan besar: MATCH mengembalikan tempat nilai ditemukan. Jawaban dari 208 mengatakan bahwa A308 adalah sel ke 208 dalam rentang P4: P227. Dari perspektif recalc time, MATCH dan VLOOKUP hampir sama.
Saya dapat mendengar apa yang Anda pikirkan. “Apa gunanya mengetahui di mana lokasinya? Saya tidak pernah memiliki manajer yang menelepon dan bertanya, 'Di baris mana piutang itu?' ”
Meskipun manusia jarang bertanya di baris mana ada sesuatu, fungsi INDEX dapat menggunakan posisi itu. Rumus berikut memberitahu Excel untuk mengembalikan item ke 208 dari Q4: Q227.
Saat Anda menyalin rumus ini, larik nilai berpindah di seluruh tabel pencarian. Untuk setiap baris, Anda melakukan satu fungsi MATCH dan 12 INDEX. Fungsi INDEX sangat cepat dibandingkan dengan VLOOKUP. Seluruh rangkaian rumus akan menghitung 85% lebih cepat dari 12 kolom VLOOKUP.
Menonton video
- Katakanlah Anda harus melakukan 12 kolom VLOOKUP
- Hati-hati gunakan tanda dolar tunggal sebelum kolom nilai pencarian
- Hati-hati gunakan tanda empat dolar untuk tabel pencarian
- Anda masih melakukan hard-coding untuk argumen kolom ketiga.
- Salah satu solusi umum adalah menambahkan deretan sel pembantu dengan nomor kolom.
- Solusi lain yang kurang efisien adalah menggunakan COLUMN (B2) di dalam rumus VLOOKUP.
- Namun, melakukan 12 VLOOKUP untuk setiap baris sangat tidak efisien
- Sebagai gantinya, tambahkan kolom pembantu dengan judul WHERE dan lakukan satu Pencocokan.
- PERTANDINGAN berlangsung selama VLOOKUP untuk Januari.
- Anda kemudian dapat menggunakan 12 fungsi INDEX. Ini sangat cepat dibandingkan dengan VLOOKUP.
- INDEX akan menunjuk ke satu kolom jawaban dengan $ sebelum baris.
- INDEX akan menunjuk ke kolom pembantu dengan $ sebelum kolom.
Transkrip Video
Pelajari Excel dari podcast, episode 2028 - Mengganti Banyak VLOOKUP dengan satu MATCH!
Klik "i" di pojok kanan atas untuk masuk ke playlist, saya akan membuat podcasting seluruh buku ini!
Hai, selamat datang kembali di netcast, saya Bill Jelen! Nah itu masalah klasik, kita harus melakukan VLOOKUP setiap bulan sekali kan? Dan Anda bisa sangat berhati-hati di sini tentang menekan F4 3 kali untuk menguncinya ke kolom, dan kemudian menekan F4 setelah mengunci seluruh baris. Tetapi ketika Anda sampai pada titik ini, 2, FALSE bahwa 2 adalah hard-code, dan saat Anda menyalinnya, Anda harus mengedit 2 menjadi 3, bukan? Sekarang, satu cara yang tidak efisien untuk melakukan ini, cara yang tidak saya sukai adalah menggunakan kolom B1. Kolom B1 tentu saja 2, tetapi saat Anda menyalinnya, lihat bahwa itu akan berubah menjadi kolom C1, yaitu 3, tetapi pikirkan tentang ini, ini terus-menerus mencari nomor kolom berulang kali. Jadi yang saya lihat dilakukan orang dan mengapa, Anda tahu, lebih memilih daripada kolom, adalah kita akan Ctrl-seret itu,letakkan nomor 2-13 di atas sana di sel pembantu, dan kemudian, ketika kita sampai ke titik ini, kita naik dan menentukan nomor kolom itu. Tekan F4 2 kali untuk menguncinya ke baris,, FALSE dan seterusnya. Tetapi bahkan dengan metode itu, VLOOKUP sangat tidak efisien, karena harus mencari semua item ini di sini sampai menemukan A308 dan itulah angka B4. Ketika kemudian berpindah ke C4, ia lupa bahwa ia hanya pergi dan melihat, dan itu dimulai dari awal lagi, baiklah. Jadi Anda memiliki salah satu fungsi paling lambat di semua Excel, VLOOKUP, FALSE yang dikerjakan berulang kali untuk item yang sama.karena harus mencari melalui semua item ini di sini sampai menemukan A308 dan itulah angka keluarnya B4. Ketika kemudian berpindah ke C4, ia lupa bahwa ia hanya pergi dan melihat, dan itu dimulai dari awal lagi, baiklah. Jadi Anda memiliki salah satu fungsi paling lambat di semua Excel, VLOOKUP, FALSE yang dikerjakan berulang kali untuk item yang sama.karena harus mencari melalui semua item ini di sini sampai menemukan A308 dan itulah angka keluarnya B4. Ketika kemudian berpindah ke C4, ia lupa bahwa ia hanya pergi dan melihat, dan itu dimulai dari awal lagi, baiklah. Jadi Anda memiliki salah satu fungsi paling lambat di semua Excel, VLOOKUP, FALSE yang dikerjakan berulang kali untuk item yang sama.
Jadi inilah cara yang jauh lebih cepat untuk pergi, kita akan memasukkan kolom pembantu, dan kolom pembantu ini saya menyebutnya Dimana? Seperti di mana sih A308 itu? Kita akan menggunakan a = MATCH, cari A308 di baris pertama tabel, tekan F4 di sana,, 0 untuk pencocokan persis, baiklah, ini memberi tahu kita bahwa "Hei, lihat itu, di baris, 6, bagaimana luar biasa apakah itu? ” Tapi seperti yang kita salin, lihat, itu di tempat yang berbeda sepanjang waktu. Baiklah, sekarang pertandingan ini memakan waktu selama VLOOKUP Januari berlangsung, itu saja mereka mati, tapi inilah hal yang luar biasa. Dari sana kami tidak perlu melakukan VLOOKUP untuk sisa baris lainnya, kami hanya dapat melakukan = INDEX, INDEX mengatakan "Ini adalah serangkaian jawaban." Saya akan pergi ke sel Januari, dan saya akan sangat hati-hati di sini menekan F4 2 kali jadi saya menguncinya ke 4: 227,tetapi Q diizinkan untuk berubah saat saya bergerak. Koma, dan kemudian ingin tahu baris apa, nah itu akan menjadi jawaban di B4, saya akan menekan F4 3 kali untuk mendapatkan $ sebelum B, baiklah, salin itu.
Rumus ini, rumus INDEX ini, 12 ini akan terjadi dalam waktu kurang dari waktu yang dibutuhkan untuk melakukan VLOOKUP Februari, oke. Jika kita meletakkan timer Charles Williams pada ini, semua ini akan menghitung sekitar 14% dari waktu 12 VLOOKUP. Manajer Anda tidak ingin melihat Di mana? Baiklah, sembunyikan saja kolom itu, semuanya tetap berfungsi, baiklah, ini cara yang bagus untuk mempercepat 12 bulan atau 52 minggu VLOOKUP. Baiklah, tip ini, dan banyak tip lainnya, ada di buku ini. Klik "i" di pojok kanan atas sana, Anda dapat membeli buku, e-book $ 10, $ 25 untuk buku cetak, oke.
Jadi hari ini kami memiliki masalah di mana 12 kolom VLOOKUP, Anda dapat memasukkan $ dengan hati-hati, tetapi argumen ketiga itu masih harus di-hardcode. Anda bisa menggunakan kolom (B2), saya bukan penggemar itu, karena ada ratusan baris * 12 kolom yang menghitungnya berulang kali. Cukup gunakan sel pembantu berturut-turut, letakkan angka 2-12 dan tunjukkan, itu masih tidak efisien, karena VLOOKUP setelah keluar bulan Januari, itu harus dimulai kembali pada awal Februari. Jadi saya sarankan menambahkan kolom dengan tajuk "Di mana?" dan melakukan satu MATCH di sana. MATCH itu membutuhkan waktu selama VLOOKUP untuk Januari, tapi kemudian 12 fungsi INDEX akan memakan waktu lebih sedikit daripada VLOOKUP untuk bulan Februari, dan Anda telah memangkas banyak waktu. Sekali lagi, hati-hati dengan $ dalam fungsi INDEX di kedua tempat, tepat sebelum baris,dan yang lainnya sebelum kolom, referensi campuran di keduanya.
Hei, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2028.xlsx
