Ron ingin melakukan banyak VLOOKUP dan menjumlahkan hasilnya. Ada solusi formula tunggal untuk masalah ini.
Ron bertanya:
Bagaimana Anda bisa menjumlahkan semua VLOOKUP tanpa melakukan pencarian individual?
Banyak orang yang familiar dengan:
=VLOOKUP(B4,Table,2,True)
Jika Anda melakukan versi pencocokan perkiraan VLOOKUP (di mana Anda menentukan True sebagai argumen keempat), Anda juga bisa melakukan LOOKUP.
Pencarian ganjil karena mengembalikan kolom terakhir dalam tabel. Anda tidak menentukan nomor kolom. Jika tabel Anda berjalan dari E4 ke J8 dan Anda menginginkan hasil dari kolom G, Anda harus menentukan E4: G4 sebagai tabel pencarian.
Perbedaan lainnya: Anda tidak menentukan True / False sebagai argumen keempat seperti yang Anda lakukan di VLOOKUP: fungsi LOOKUP selalu melakukan versi Approximate Match dari VLOOKUP.
Mengapa repot-repot dengan fungsi kuno ini? Karena Lookup memiliki trik khusus: Anda dapat mencari semua nilai sekaligus dan itu akan menjumlahkannya. Alih-alih meneruskan satu nilai seperti B4 sebagai argumen pertama, Anda dapat menentukan semua nilai Anda =LOOKUP(B4:B17,E4:F8). Karena ini akan mengembalikan 14 nilai berbeda, Anda harus membungkus fungsi dalam fungsi pembungkus seperti =SUM( LOOKUP(B4:B17,E4:F8))atau =COUNT(LOOKUP(B4:B17,E4:F8))atau =AVERAGE( LOOKUP(B4:B17,E4:F8)). Ingat, Anda membutuhkan fungsi Wrapper, tetapi bukan fungsi rapper. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))tidak akan berhasil.
Ada kesalahan umum yang ingin Anda hindari. Setelah mengetik atau mengedit rumus, jangan tekan Enter! Sebagai gantinya, lakukan trik keyboard tiga jari ini. Tahan Ctrl dan Shift. Sambil menahan Ctrl dan Shift, tekan Enter. Anda sekarang dapat melepaskan Ctrl dan Shift. Kami menyebutnya Ctrl + Shift + Enter, tetapi itu benar-benar harus diatur waktunya dengan benar: Tekan dan tahan Ctrl + Shift, Tekan Enter, Lepaskan Ctrl + Shift. Jika Anda melakukannya dengan benar, rumus akan muncul di bilah rumus yang dikelilingi oleh tanda kurung kurawal:(=SUM(LOOKUP(B4:B17,E4:F8)))
Catatan Samping
LOOKUP juga dapat melakukan hal yang setara dengan HLOOKUP. Jika tabel pencarian Anda lebih lebar dari tingginya, LOOKUP akan beralih ke HLOOKUP. Dalam kasus seri… tabel berukuran 8x8 atau 10x10, LOOKUP akan memperlakukan tabel sebagai vertikal.
Tonton video di bawah atau pelajari tangkapan layar ini.
Menonton video
Transkrip Video
Belajar Excel dari Podcast, Episode 2184: Jumlahkan Semua Pencarian.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini, dari Ron, tentang penggunaan program LOOKUP yang lama. Dan ini dari buku saya, Excel 2016 In Depth.
Katakanlah kita memiliki banyak produk di sini dan kita harus menggunakan tabel Pencarian. Dan untuk setiap produk, kami harus, Anda tahu, mendapatkan nilai dari tabel Pencarian dan menjumlahkannya. Nah, cara tipikal adalah, kami menggunakan VLOOKUP-- = VLOOKUP untuk produk ini di tabel ini. Saya akan menekan F4, menguncinya, dan dengan nilai kedua. Dan dalam kasus khusus ini, karena setiap nilai yang kita cari ada di tabel, dan tabel diurutkan, aman untuk menggunakan TRUE. Biasanya, saya tidak akan pernah menggunakan TRUE, tetapi dalam episode ini kita akan menggunakan TRUE. Jadi saya mendapatkan semua nilai itu dan kemudian di sini, Alt + =, kita mendapatkan totalnya, bukan? Tetapi bagaimana jika seluruh tujuan kita hanya untuk mendapatkan 1130? Kami tidak membutuhkan semua nilai ini. Kami hanya membutuhkan hasil ini.
Oke, sekarang, ada fungsi lama yang sudah ada sejak masa Visical, disebut LOOKUP. Bukan VLOOKUP. Bukan HLOOKUP, hanya LOOKUP. Dan tampaknya, pada awalnya, mirip dengan VLOOKUP-- Anda menentukan nilai yang Anda cari dan tabel pencarian, tekan F4, tapi kemudian kita selesai. Kami tidak harus menentukan kolom mana karena LOOKUP hanya pergi ke kolom terakhir dalam tabel. Jika Anda memiliki tabel tujuh kolom dan ingin mencari nilai keempat, Anda cukup menentukan kolom satu sampai empat. Baik? Dan, jadi, apa pun kolom terakhirnya, itulah yang akan dicari. Dan kita tidak harus menentukan, FALSE atau, TRUE karena selalu menggunakan TRUE; tidak ada, versi SALAH. Baik?
Jadi Anda harus mengerti, jika Anda melakukan VLOOKUP, saya selalu menggunakan, FALSE di akhir, tetapi dalam hal ini adalah daftar pendek - kita tahu bahwa semua yang ada di daftar ada di tabel. Tidak ada yang hilang, dan tabel diurutkan. Baik? Jadi, ini akan memberi kita hasil yang sama persis dengan yang kita miliki untuk VLOOKUP.
Luar biasa, saya ingin menyalin ini: Alt + =. Baik. Tapi itu tidak membeli apa-apa karena kita masih harus memasukkan semua rumus dan kemudian fungsi SUM. Hebatnya LOOKUP bisa melakukan trik yang tidak bisa dilakukan VLOOKUP, oke? Dan itu untuk melakukan semua pencarian sekaligus. Jadi, di mana saya mengirim ini ke fungsi SUM, ketika saya mengatakan LOOKUP, Apa item pencariannya? Kami ingin mencari semua hal ini, koma, lalu inilah tabelnya-- dan kami tidak perlu menekan F4, karena kami tidak akan menyalinnya di mana pun, hanya ada satu rumus-- tutup LOOKUP, tutup jumlah.
Baiklah, sekarang, di sinilah hal-hal dapat menjadi kacau: Jika Anda cukup menekan Enter di sini, Anda akan mendapatkan 60, oke? Karena itu hanya akan pergi melakukan yang pertama. Yang harus Anda lakukan adalah menahan tiga tombol ajaib, dan ini Ctrl + Shift - Saya menahan Ctrl + shift dengan tangan kiri saya, saya tetap menahannya, dan saya menekan ENTER dengan tangan kanan saya, dan itu akan melakukan semua matematika VLOOKUP. Bukankah itu luar biasa? Perhatikan di bilah rumus di sini, atau di teks rumus, ada tanda kurung kurawal di sekitarnya. Anda tidak mengetikkan tanda kurung kurawal, Excel memasukkan tanda kurung kurawal tersebut, untuk mengatakan, "Hei, Anda menekan Ctrl + Shift + Enter untuk ini."
Nah, hei, topik ini dan banyak topik lainnya ada di buku ini: Power Excel with, edisi 2017. Klik "Saya" di pojok kanan atas untuk membaca lebih lanjut tentang buku itu.
Hari ini, pertanyaan dari Ron: Bagaimana Anda bisa menjumlahkan semua VLOOKUP? Sekarang, kebanyakan orang tahu VLOOKUP tempat Anda menentukan nilai pencarian, tabel, kolom mana, lalu, TRUE atau, FALSE. Dan jika Anda melakukan-- jika Anda memenuhi syarat untuk-- versi VLOOKUP YANG BENAR. maka Anda juga dapat melakukan LOOKUP lama ini. Aneh, karena mengembalikan kolom terakhir tabel, Anda tidak menentukan nomor kolom, dan Anda tidak mengatakan TRUE atau FALSE. Itu selalu BENAR. Mengapa kami menggunakan ini? Karena ia memiliki trik khusus: Anda dapat melakukan semua nilai pencarian sekaligus dan itu akan menjumlahkannya. Anda harus menekan Ctrl + Shift + Enter setelah mengetik rumus itu atau tidak akan berhasil. Dan kemudian, saya akan menunjukkan trik lain untuk LOOKUP.
Baiklah, hei, saya ingin berterima kasih kepada Ron karena telah mengirimkan pertanyaan itu, dan saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Baiklah, saat kita berbicara tentang LOOKUP, hal lain yang dapat dilakukan LOOKUP: Anda tahu, kami memiliki VLOOKUP untuk tabel vertikal seperti ini atau HLOOKUP untuk tabel horizontal seperti ini; LOOKUP bisa berjalan baik. jadi kita bisa bilang hei kita mau = LOOKUP nilai ini, D, di tabel ini, dan karena tabel lebih lebar daripada tingginya, LOOKUP secara otomatis beralih ke versi HLOOKUP, bukan? Jadi dalam kasus ini karena kami menentukan 3 baris kali 5 kolom, itu akan melakukan HLOOKUP. Dan karena baris terakhir di sini adalah angka-angka, itu akan memberi kita angka itu. Jadi kita punya D, Date, dapatkan 60. Baiklah. Jika saya akan menentukan tabel yang hanya menuju ke baris 12, maka saya akan mendapatkan nama produknya. Baik? Jadi ini semacam fungsi kecil yang menarik. Saya pikir Bantuan Excel dulu mengatakan, "Hei, jangan gunakan fungsi ini," tapi ada 'Pada waktu tertentu Anda dapat menggunakan fungsi ini.
Judul Foto: grandcanyonstate / pixabay
