VLOOKUP luar biasa dan fungsi favorit saya
Tabel ini tidak hanya mempermudah penyegaran data, tetapi juga membuat membaca rumus menjadi lebih mudah! Satu-satunya hal yang perlu Anda lakukan adalah menekan Ctrl + T sebelum menulis rumus.
Mari kembali ke rumus VLOOKUP dari atas. Kali ini, konversikan tabel item dan tabel pembelian Anda ke tabel Excel dengan Ctrl + T sejak awal! Untuk mempermudah, berikan setiap tabel nama yang ramah menggunakan tab Alat Tabel:
Sekarang ketik VLOOKUP lagi tanpa melakukan sesuatu yang berbeda dari yang biasanya Anda lakukan, rumus Anda di C2 sekarang =VLOOKUP((@Item),Items,2,0)bukan =VLOOKUP(B2,$E$5:$F$10,2,0)!
Meskipun tabel Item berada di lembar kerja yang berbeda, rumusnya tetap sama, bukan yang kurang mudah dibaca =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
(@Item) dalam rumus merujuk ke sel di kolom Item dari tabel ini (di baris yang sama dengan rumus) dan oleh karena itu sama di seluruh kolom. Dan Item merujuk ke seluruh tabel item (tanpa header). Yang terbaik dari semuanya, Anda tidak perlu mengetik semua ini. Setelah ini menjadi tabel, Excel akan menempatkan nama-nama ini di rumus Anda saat Anda memilih sel / rentang!
Mari kita selangkah lebih maju. Tambahkan kolom lain ke tabel Penjualan untuk menghitung pendapatan dengan rumus =(@Price)*(@Qty). Jika sekarang Anda ingin menghitung pendapatan total, rumusnya adalah =SUM(Sales(Revenue)); yang sangat mudah dipahami, di mana pun datanya atau berapa banyak baris yang dicakupnya!
Menonton video
- VLOOKUP luar biasa dan fungsi favorit saya
- Pembenci VLOOKUP mengeluh bahwa ini rapuh karena argumen ke-3
- Jika bentuk tabel pencarian Anda berubah, jawabannya bisa berubah
- Salah satu solusinya adalah mengganti argumen ketiga dengan MATCH
- Tapi bayangkan melakukan MATCH untuk 1000 baris VLOOKUP
- Buat tabel pencarian Anda menjadi tabel sebelum melakukan VLOOKUP
- Referensi tabel terstruktur akan menangani jika bentuk tabel berubah
- Selain itu, tidak perlu melakukan MATCH berulang kali
- Peter Albert mengirimkan tip ini
Transkrip Video
Pelajari Excel untuk Podcast, Episode 2003 - Referensi yang Dapat Dibaca
Jangan lupa subscribe playlist XL. Saya akan membuat podcasting seluruh buku ini.
Baiklah, tip hari ini dari Peter Albert. Peter Albert. Sekarang mari kita bicara tentang VLOOKUP. Saya adalah penggemar berat VLOOKUP. Bagi saya VLOOKUP adalah garis pemisah. Jika Anda dapat melakukan VLOOKUP, semua hal lain di Excel akan menjadi mudah bagi Anda. Jadi VLOOKUP memungkinkan kita mencari harga dari tabel itu dan kita akan membicarakan VLOOKUP lebih lanjut nanti.
Jadi salin ini dan semuanya bekerja dengan baik tetapi saya harus memberi tahu Anda. Saya telah melihat mereka. Saya sudah berbicara dengan mereka. Saya sudah bertemu mereka. Ada banyak pembenci VLOOKUP di luar sana. Orang yang benci jika Anda melihat ke atas dan apa keluhan lainnya adalah bahwa itu sangat rapuh, argumen ketiga itu, di mana kami mengatakan kami ingin kolom ketiga, bahwa jika seseorang akan memutuskan nanti bahwa kami membutuhkan bidang baru di sini, mungkin seperti, ukuran . Baiklah, pertama, tampaknya ada semacam bug yang tidak dihitung ulang oleh Excel. Biarkan saya mengurungkan, mengurungkan, dan kemudian mengulang. Ini dia. Aneh, saya harus melaporkannya ke tim Excel, tetapi Anda melihat bahwa di mana kami mendapatkan harga, sekarang mendapatkan warna, karena sulit untuk mengatakan mereka menginginkan kolom ketiga. Baiklah dan apa yang orang lakukan untuk mengatasi ini adalah hal gila ini dengan = MATCH.Cari kata Price di baris pertama tabel, F4,0 dan itu akan memberi tahu kita harga saat ini adalah kolom keempat. Jadi mereka akan benar-benar melakukan = VLOOKUP. Kami mencari A104, di tabel ini. F4 dan kemudian alih-alih melakukan hard-coding nomor empat mereka melakukan MATCH dan MATCH akan dikunci ke harga. Jadi F4, dua kali untuk meletakkan $ sebelum 1 dan itu akan melihat melalui baris pertama tabel. Ups, F4 dua kali, koma, ketinggalan koma. Baiklah tekan F4 di sini koma 0 untuk kecocokan persis dengan pertandingan lalu koma untuk kecocokan persis dengan VLOOKUP. Ya dan hei ini bekerja dengan baik dan di sini saya hanya memiliki enam jadi itu bukan masalah besar.di tabel ini. F4 dan kemudian alih-alih melakukan hard-coding nomor empat mereka melakukan MATCH dan MATCH akan dikunci ke harga. Jadi F4, dua kali untuk meletakkan $ sebelum 1 dan itu akan melihat melalui baris pertama tabel. Ups, F4 dua kali, koma, ketinggalan koma. Baiklah tekan F4 di sini koma 0 untuk kecocokan persis dengan pertandingan lalu koma untuk kecocokan persis dengan VLOOKUP. Ya dan hei ini bekerja dengan baik dan di sini saya hanya memiliki enam jadi itu bukan masalah besar.di tabel ini. F4 dan kemudian alih-alih melakukan hard-coding nomor empat mereka melakukan MATCH dan MATCH akan dikunci ke harga. Jadi F4, dua kali untuk meletakkan $ sebelum 1 dan itu akan melihat melalui baris pertama tabel. Ups, F4 dua kali, koma, ketinggalan koma. Baiklah tekan F4 di sini koma 0 untuk kecocokan persis dengan pertandingan lalu koma untuk kecocokan persis dengan VLOOKUP. Ya dan hei ini berfungsi dengan baik dan di sini saya hanya memiliki enam dari mereka jadi itu bukan masalah besar.Baiklah tekan F4 di sini koma 0 untuk kecocokan persis dengan pertandingan lalu koma untuk kecocokan persis dengan VLOOKUP. Ya dan hei ini berfungsi dengan baik dan di sini saya hanya memiliki enam dari mereka jadi itu bukan masalah besar.Baiklah tekan F4 di sini koma 0 untuk kecocokan persis dengan pertandingan lalu koma untuk kecocokan persis dengan VLOOKUP. Ya dan hei ini berfungsi dengan baik dan di sini saya hanya memiliki enam dari mereka jadi itu bukan masalah besar.
Lihat jika saya memasukkan yang baru, itu akan secara otomatis menyesuaikan dan terus mendapatkan harga tetapi bayangkan jika Anda memiliki seribu VLOOKUP dan setiap VLOOKUP akan mengulang pertandingan itu untuk mengetahui harga di kolom kelima atau kolom keempat. Ini mengerikan. Tabel hanya memecahkan masalah ini. Jadi inilah tabel VLOOKUP saya, jauh sebelum saya melakukan sesuatu, saya akan pergi ke sini dan CTRL T untuk membuatnya menjadi tabel nyata. Mereka akan menyebutnya tabel 1 tapi saya akan menyebutnya ProductTable, semuanya satu kata, tanpa spasi: ProductTable. Jadi sekarang sudah namanya. Baiklah, jadi sekarang kita punya tabel bernama ProductTable. Kemudian kami datang ke sini dan berkata kami akan melakukan = INDEX dari harga tersebut. Harga berapa yang kita inginkan? Kami ingin hasil dari pertandingan A104 menjadi item ini. Cocok persis, tanda kurung tutup untuk INDEX.Ini hanya melakukan satu pertandingan. Itu tidak melakukan pertandingan dan VLOOKUP. Jenis, akan jauh lebih cepat. Salin itu. Baiklah dan nanti jika kita memasukkan ukuran, jadi masukkan kolom, ukuran semuanya terus berfungsi karena mencari kolom yang disebut Harga dan katakanlah jika kita mengubahnya menjadi Harga Jual, rumus itu akan ditulis ulang. Benar, jauh lebih aman, cara yang lebih aman untuk pergi.
Baiklah, banyak trik keren di tabel. Lihat buku ini dari Kevin Jones dan Zach Barresse di Tabel Excel. Semua jenis trik di sana dan semua yang kami podcasting di bulan Agustus dan September ada di buku yang penuh sesak ini. Ditambah banyak kesenangan. Lelucon Excel. Koktail Excel. Tweet Excel. Petualangan Excel. Penuh warna. Lihat, beli buku ini. Aku akan sangat menghargainya.
Oke episode hari ini. VLOOKUP luar biasa dan ini adalah fungsi favorit saya tetapi ada pembenci VLOOKUP di luar sana yang mengeluh bahwa itu rapuh karena argumen ketiga itu, jika bentuk tabel Anda VLOOKUP berubah, jawabannya akan berubah. Salah satu solusinya adalah mengganti argumen ketiga itu dengan MATCH, tapi ya ampun, bayangkan melakukan MATCH untuk seribu baris VLOOKUP. Jadi buatlah VLOOKUP Anda menjadi tabel sebelum melakukan VLOOKUP. Referensi Tabel Struktur akan menangani jika Bentuk Tabel berubah. Ditambah Anda tidak sedang melakukan VLOOKUP dan pertandingan. Hanya satu pertandingan bersama dengan INDEX dan INDEX yang secepat kilat.
Terima kasih kepada Peter Robert atas tip ini dan terima kasih telah mampir. Sampai jumpa di lain waktu, untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2003.xlsx
