Ron mahu melakukan banyak VLOOKUP dan menjumlahkan hasilnya. Terdapat penyelesaian satu formula untuk masalah ini.
Ron bertanya:
Bagaimana anda boleh menjumlahkan semua VLOOKUP tanpa melakukan setiap carian individu?
Ramai orang biasa dengan:
=VLOOKUP(B4,Table,2,True)
Sekiranya anda melakukan versi pencocokan anggaran VLOOKUP (di mana anda menyatakan Benar sebagai argumen keempat), anda juga boleh melakukan LOOKUP.
Pencarian adalah ganjil kerana mengembalikan lajur terakhir dalam jadual. Anda tidak menentukan nombor lajur. Sekiranya jadual anda berjalan dari E4 hingga J8 dan anda mahukan hasilnya dari lajur G, anda akan menentukan E4: G4 sebagai jadual carian.
Perbezaan lain: anda tidak menentukan Betul / Salah sebagai argumen keempat seperti yang anda lakukan dalam VLOOKUP: fungsi LOOKUP selalu melakukan versi Pencocokan Perkiraan VLOOKUP.
Mengapa bersusah payah dengan fungsi kuno ini? Kerana Lookup mempunyai helah khas: Anda dapat mencari semua nilai sekaligus dan itu akan menjumlahkannya. Daripada memberikan nilai tunggal seperti B4 sebagai argumen pertama, anda boleh menentukan semua nilai anda =LOOKUP(B4:B17,E4:F8). Oleh kerana ini akan mengembalikan 14 nilai yang berbeza, anda harus membungkus fungsi tersebut 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 memerlukan fungsi Wrapper, tetapi bukan fungsi rapper. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))tidak akan berjaya.
Terdapat kesilapan yang biasa anda ingin mengelakkan. Setelah menaip atau mengedit formula, jangan tekan Enter! Sebaliknya, lakukan silap mata papan kekunci tiga jari ini. Tahan Ctrl dan Shift. Sambil menekan Ctrl dan Shift, tekan Enter. Anda kini boleh melepaskan Ctrl dan Shift. Kami menyebutnya Ctrl + Shift + Enter, tetapi harus tepat waktunya: Tekan dan tahan Ctrl + Shift, Tekan Enter, Lepaskan Ctrl + Shift. Sekiranya anda melakukannya dengan betul, formula akan muncul di bar formula yang dikelilingi oleh pendakap kerinting:(=SUM(LOOKUP(B4:B17,E4:F8)))
Nota sampingan
LOOKUP juga dapat melakukan setara dengan HLOOKUP. Sekiranya jadual carian anda lebih lebar daripada yang tinggi, LOOKUP akan beralih ke HLOOKUP. Sekiranya dasi… meja berukuran 8x8 atau 10x10, LOOKUP akan memperlakukan meja sebagai menegak.
Tonton video di bawah atau pelajari tangkapan skrin ini.
Tonton Video
Transkrip Video
Belajar Excel dari Podcast, Episode 2184: Jumlahkan Semua Pencarian.
Hei, selamat datang kembali ke siaran net, saya Bill Jelen. Soalan hari ini, dari Ron, mengenai penggunaan program LOOKUP lama, lama. Dan ini dari buku saya, Excel 2016 In Depth.
Katakan bahawa kami mempunyai banyak produk di sini dan kami harus menggunakan jadual Carian. Dan untuk setiap produk, kami mesti mendapatkan nilai dari jadual Carian dan jumlahnya. Nah, cara yang biasa adalah, kita menggunakan VLOOKUP-- = VLOOKUP untuk produk ini dalam jadual ini. Saya akan tekan F4, kunci itu, dan dengan nilai kedua. Dan dalam kes ini, kerana setiap nilai yang kita cari ada dalam jadual, dan jadual disusun, selamat digunakan BENAR. Biasanya, saya tidak akan menggunakan BENAR, tetapi dalam episod ini kita akan menggunakan BENAR. Oleh itu, saya mendapat semua nilai itu dan kemudian di sini, Alt + =, kita mendapat jumlah itu, bukan? Tetapi bagaimana jika keseluruhan tujuan kita hanya untuk mendapatkan 1130? Kami tidak memerlukan semua nilai ini. Kami hanya memerlukan hasil ini.
Baiklah, baiklah, sekarang, ada fungsi lama yang sudah ada sejak zaman Visical, yang disebut LOOKUP. Bukan VLOOKUP. Bukan HLOOKUP, hanya LOOKUP. Dan nampaknya, pada mulanya, serupa dengan VLOOKUP - anda menentukan nilai yang anda cari dan jadual carian, tekan F4, tetapi kemudian kami selesai. Kami tidak perlu menentukan lajur mana kerana LOOKUP hanya menuju ke lajur terakhir dalam jadual. Sekiranya anda mempunyai jadual tujuh lajur dan anda ingin mencari nilai keempat, anda hanya perlu menentukan lajur satu hingga empat. Baiklah? Oleh itu, apa sahaja ruangan terakhir, itulah yang akan dicari. Dan kita tidak perlu menentukan, SALAH atau, BENAR kerana selalu menggunakan, BENAR; tidak ada, versi SALAH. Baiklah?
Oleh itu, anda harus faham, jika anda melakukan VLOOKUP, saya selalu menggunakan, SALAH pada akhirnya, tetapi dalam kes ini senarai pendeknya - kita tahu bahawa semua yang ada dalam senarai ada dalam jadual. Tidak ada yang hilang, dan jadual disusun. Baiklah? Jadi, ini akan memberi kita hasil yang sama seperti yang kita ada untuk VLOOKUP.
Hebat, saya mahu menyalinnya: Alt + =. Baiklah. Tetapi itu tidak membelikan kita kerana kita masih perlu memasukkan semua formula dan kemudian fungsi SUM. Perkara yang indah ialah LOOKUP dapat melakukan muslihat yang tidak dapat dilakukan oleh VLOOKUP, okey? Dan itu adalah untuk melakukan semua carian sekaligus. Oleh itu, di mana saya menghantar ini ke fungsi SUM, ketika saya mengatakan LOOKUP, Apa item carian? Kami ingin mencari semua perkara ini, koma, dan inilah jadualnya - dan kami tidak perlu menekan F4, kerana kami tidak akan menyalinnya di mana sahaja, hanya ada satu formula - tutup LOOKUP, tutup Jumlah.
Baiklah, sekarang, inilah tempat di mana perkara boleh diperbaiki: Sekiranya anda menekan Enter di sini, anda akan mendapat 60, baiklah? Kerana hanya akan melakukan yang pertama. Apa yang harus anda lakukan ialah menahan tiga ketukan kekunci ajaib, dan ini adalah Ctrl + Shift - Saya menahan shift Ctrl + dengan tangan kiri saya, saya terus menahannya, dan saya menekan ENTER dengan tangan kanan saya, dan ia akan melakukan semua matematik VLOOKUP. Bukankah itu hebat? Perhatikan di bar formula di sini, atau dalam teks formula, ia meletakkan pendakap keriting di sekelilingnya. Anda tidak menaip pendakap keriting itu, Excel memasukkan pendakap keriting itu, untuk mengatakan, "Hei, anda menekan Ctrl + Shift + Enter untuk ini."
Sekarang, hei, topik ini dan banyak topik lain ada dalam buku ini: Power Excel with, edisi 2017. Klik "Saya" di sana di penjuru kanan sebelah atas untuk membaca lebih lanjut mengenai buku ini.
Hari ini, soalan dari Ron: Bagaimana anda dapat menjumlahkan semua VLOOKUP? Sekarang, kebanyakan orang mengetahui VLOOKUP di mana anda menentukan nilai carian, jadual, lajur mana, dan kemudian, BENAR atau, SALAH. Dan jika anda melakukannya - jika anda memenuhi syarat - versi VLOOKUP BENAR. maka anda juga boleh melakukan LOOKUP lama ini. Ia ganjil, kerana mengembalikan lajur terakhir pada jadual, anda tidak menentukan nombor lajur, dan anda tidak mengatakan BENAR atau SALAH. Ia selalu BENAR. Mengapa kita menggunakan ini? Kerana mempunyai tipu muslihat khas: Anda dapat melakukan semua nilai pencarian sekaligus dan itu akan menjumlahkannya. Anda harus menekan Ctrl + Shift + Enter setelah menaip formula itu atau tidak akan berjaya. Dan kemudian dalam pelaksanaannya, saya akan menunjukkan trik lain untuk MENCARI.
Baiklah, saya ingin mengucapkan terima kasih kepada Ron kerana telah menghantar soalan itu, dan saya ingin mengucapkan terima kasih kerana berhenti. Sampai jumpa lagi untuk siaran net lain dari.
Baiklah, semasa kita di sini bercakap mengenai LOOKUP, perkara lain yang boleh dilakukan LOOKUP: Anda tahu, kami mempunyai VLOOKUP untuk jadual menegak seperti ini atau HLOOKUP untuk jadual mendatar seperti ini; LOOKUP boleh berjalan dengan baik. jadi kita boleh katakan hei kita ingin = LIHAT nilai ini, D, dalam jadual ini, dan kerana jadualnya lebih lebar daripada yang tinggi, LOOKUP secara automatik beralih ke versi HLOOKUP, bukan? Jadi dalam kes ini kerana kita menentukan 3 baris dengan 5 lajur, ia akan melakukan HLOOKUP. Dan kerana baris terakhir di sini adalah nombor, ia akan membawa kita nombor itu. Oleh itu, kita mempunyai D, Date, menjadikan kita 60. Baiklah. Sekiranya saya menentukan jadual yang hanya menuju ke baris 12, maka saya akan mendapat nama produk. Baiklah? Jadi ia adalah fungsi kecil yang menarik. Saya rasa Bantuan Excel biasa mengatakan, "Hei, jangan gunakan fungsi ini," tetapi ada 'masa-masa tertentu di mana anda boleh menggunakan fungsi ini.
Foto Tajuk: grandcanyonstate / pixabay
