VLOOKUP adalah fungsi yang kuat. Tetapi saya sering mendapat soalan dalam salah satu seminar Power Excel saya dari seseorang yang ingin mengetahui sama ada VLOOKUP dapat mengembalikan semua nilai yang sepadan. Seperti yang anda ketahui, VLOOKUP dengan False sebagai argumen keempat akan selalu mengembalikan perlawanan pertama yang dijumpainya. Dalam tangkapan skrin berikut, sel F2 mengembalikan 3623 kerana ini adalah perlawanan pertama yang dijumpai untuk pekerjaan J1199.
Maka, persoalannya, dapatkah VLOOKUP mengembalikan semua perlawanan?
VLOOKUP tidak akan. Tetapi fungsi lain boleh.
Sekiranya anda ingin menjumlahkan semua kos dari pekerjaan J1199, anda akan menggunakan =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Sekiranya anda mempunyai nilai teks dan ingin menggabungkan semua hasil menjadi satu nilai, anda boleh menggunakannya =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Formula ini hanya berfungsi di Office 365 dan Excel 2019.
Atau, anda mungkin perlu mengembalikan semua hasil untuk satu pekerjaan ke dalam lembaran kerja baru. =FILTER(B2:C53,A2:A53=K1,"None Found")Fungsi baru yang akan datang ke Office 365 pada tahun 2019 akan menyelesaikan masalah:
Kadang kala, orang mahu melakukan semua VLOOKUP dan menjumlahkannya. Sekiranya jadual carian anda disusun, anda boleh menggunakan =SUM(LOOKUP(B2:B53,M3:N5)).
Sekiranya anda perlu menjumlahkan semua VLOOKUP dengan versi Exact Match dari VLOOKUP, anda perlu mempunyai akses ke Dynamic Arrays agar dapat digunakan =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Untuk mengetahui lebih lanjut mengenai Dynamic Arrays, lihat Excel Dynamic Arrays Straight To The Point.
Tonton Video
Transkrip Video
Belajar Excel Dari, Podcast Episode 2247: Bolehkah Anda Mengembalikan Semua Nilai VLookUp?
Hey. Selamat kembali ke netcast. Saya Bill Jelen. Dua soalan muncul dalam seminar saya di Appleton, Wisconsin minggu lalu - keduanya berkaitan. Mereka berkata, hei, bagaimana kita mengembalikan semua VLOOKUP, baiklah? Dalam kes ini, seperti J1199 mempunyai banyak perlawanan dan mereka, anda tahu, ingin mengembalikan semuanya, dan soalan pertama saya setiap kali ada yang bertanya kepada saya ini, baik, apa yang anda mahu lakukan dengan perlawanan itu? Adakah itu nombor yang ingin anda tambah atau teks yang anda mahu gabungkan? Dan memang melucukan. Dua soalan dalam seminar yang sama, satu orang ingin menambahkannya dan yang lain ingin menggabungkan hasilnya.
Oleh itu, mari kita lihat kedua-duanya. Lihat keterangan YouTube untuk melihat senarai kandungan di mana anda boleh beralih ke yang lain jika anda ingin melihat hasil teksnya.
Baiklah, jadi, pertama, jika kita mahu menambahkan semuanya, kita sama sekali tidak akan menggunakan VLOOKUP. Kami akan menggunakan fungsi yang disebut SUMIF atau SUMIFS yang akan menjumlahkan semua yang sesuai dengan item ini. Jadi, SUMIFS. Inilah nilai berangka yang ingin dijumlahkan dan saya akan menekan F4 untuk menguncinya. Dengan cara itu, semasa saya menyalinnya, ia akan terus menunjukkan jarak yang sama, dan kemudian kami ingin memeriksa dan melihat apakah nombor JOB di lajur A, sekali lagi F4 di sana, adalah = ke nilai di sebelah kiri kita - dalam kes ini E2 - dan semasa kami menyalinnya, kami akan melihat JUMLAH untuk setiap item. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Mari kita buat sedikit pemeriksaan di sini. J1199. Jumlahnya ialah 25365. Baiklah. Jadi, ini berfungsi. Sekiranya ia nombor dan anda ingin mendapatkan semua nombor dan menambahkannya, beralih ke SUMIF atau SUMIFS, tetapi jika teks, baiklah, sekarang, fungsi ini baru di Office 365 pada Februari 2017. Jadi, jika anda mempunyai Excel 2016 atau Excel 2013 atau Excel 2010 atau yang lama, anda tidak akan mempunyai fungsi ini. Ia adalah fungsi yang dipanggil TEXTJOIN. TEXTJOIN. Ini adalah fungsi lain dari (Joe McDade - 01:50) yang baru saja membawa kami semua formula larik dinamik yang hebat di Ignite pada tahun 2018, dan Joe memastikan bahawa TEXTJOIN akan berfungsi dengan tatasusunan, yang sangat hebat.
Jadi, pembatas di sini akan menjadi, RUANG, mengabaikan KOSONG pasti. Kami ingin mengabaikan KOSONG di sini kerana kami akan menghasilkan banyak kekosongan di bahagian seterusnya, pernyataan IF. JIKA item di atas A2, F4, adalah = ke nombor JOB ini di sini, maka saya mahu item yang sesuai dari lajur C, F4, jika tidak, saya mahu "" seperti itu. Tutup pernyataan IF itu. Tutup TEXTJOIN. Adakah saya perlu menekan CONTROL + SHIFT + ENTER? Tidak, saya tidak. Ini membawa saya semua produk yang sepadan dengan itu, ok? Jadi, mengembalikan semua VLOOKUP, jika kita ingin menjumlahkannya, ya, jika kita mahu menggabungkannya, ya. (= TEXTJOIN (“,”, Benar, JIKA ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Baiklah, Sekarang, ada satu kemungkinan lain di sini apabila orang bertanya kepada saya jika mereka dapat mengembalikan semua VLOOKUP. Mungkin menjadi masalah di mana kita ingin mencari setiap kos ini di sini dan mengetahui KOS PENANGANAN dan kemudian menjumlahkan semuanya. Seperti, saya tidak mahu meletakkan VLOOKUP di sini dan VLOOKUP di sini dan VLOOKUP di sini dan VLOOKUP di sini. Saya hanya mahu melakukannya sama sekali dan, dalam kes ini, kita akan menggunakan fungsi SUM dan kemudian fungsi LOOKUP lama yang lama. LOOKUP mengatakan bahawa kita akan mencari semua nilai ini di lajur B. Saya tidak memerlukan F4 di sini kerana saya tidak menyalinnya di mana sahaja. ,. Inilah jadual carian kami. ), tutup SUM, dan ia keluar dan melakukan setiap individu VLOOKUP dan kemudian jumlah mereka semua seperti itu. (= SUM (MENCARI (B2: B53, K3: L5)))
Baiklah, hei. Semua topik ini adalah buku saya LIV: 54 Petua Paling Hebat Sepanjang Masa. Klik i di sudut kanan atas untuk mengetahui lebih lanjut.
Jadi, persoalannya adakah anda boleh mengembalikan semua VLOOKUP? Nah, semacam, tetapi sebenarnya tidak menggunakan VLOOKUP. Kita akan menggunakan SUMIF, TEXTJOIN, atau SUM atau MENCARI untuk menyelesaikannya.
Baiklah, hei. Saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Anda tahu, baiklah, saya telah bercakap mengenai susunan dinamik ini selama seminggu. Saya mahu membuat satu video di mana saya tidak menyentuh tatasusunan dinamik kerana saya tahu banyak orang belum memilikinya, tetapi inilah dia. Ini adalah jalan keluar. Anda tahu, ini bukan mengikut abjad. Ini akan menjadi jauh lebih baik jika kita dapat menyusunnya, dan jika anda mempunyai susunan dinamik baru, anda boleh menghantarnya ke fungsi SORT, SORT seperti ini, dan tekan ENTER, dan sekarang hasilnya akan disusun seperti itu.
Anda tahu, walaupun formula ini boleh menjadi lebih baik dengan tatasusunan dinamik. Pencarian memerlukan anda menggunakan, BENAR. Bagaimana jika anda mahu menggunakan, SALAH? Kita boleh mengubahnya menjadi VLOOKUP, mencari semua teks ini ke dalam jadual itu, 2,. Dalam kes ini, saya akan menggunakan BENAR tetapi, dalam kes lain, anda mungkin menggunakan SALAH. KAWALAN + SHIFT + ENTER. Tidak. Ia akan berfungsi, baiklah? (= SUM (VLOOKUP (B2: B53, K3: L5,2, Betul)))
Dynamic Arrays yang keluar pada awal 2019 akan menyelesaikan begitu banyak masalah.
Terima kasih kerana meluangkan masa di sini. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: can-you-return-all-vlookup-Values.xlsx
Apabila seseorang bertanya "Bolehkah VLOOKUP mengembalikan semua pertandingan, jawapannya adalah Tidak. Tetapi, terdapat banyak fungsi lain yang dapat melakukan perkara yang sama.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Menormalkan data anda seperti yang anda minta orang lain menormalkan data mereka untuk anda"
Kevin Lehrbass
