เมื่อใดก็ตามที่เราต้องการดึงค่าจากแผ่นงานอื่นหรือภายในแผ่นงานเดียวกัน เราอดไม่ได้ที่จะนึกถึงฟังก์ชัน VLOOKUP ใน Excel แต่ปัญหาหลักของ VLOOKUP ฟังก์ชันคือออกแบบมาเพื่อคืนค่าเดียวเพียงครั้งเดียว ดังนั้น เราต้องคิดนอกกรอบเล็กน้อยเพื่อดูค่าหลายค่าด้วยฟังก์ชันนี้ ซึ่งเราจะอธิบายในบทความนี้
นอกจากนี้เรายังสามารถใช้ฟังก์ชัน Excel อื่นๆ เช่น INDEX , เล็ก , MATCH , ROW , คอลัมน์ ฯลฯ รวมกันอย่างเหมาะสมกับ vlookup และคืนค่าหลายค่า นอกจากนี้เรายังสามารถใช้คุณลักษณะต่างๆ ของ Excel เช่น ตัวกรองขั้นสูง , ตัวกรองอัตโนมัติ และ จัดรูปแบบเป็นตาราง เครื่องมือ. ดังนั้น ในบทช่วยสอนนี้ เราจะพูดถึงทั้งหมด 1 เมธอดโดยใช้ ฟังก์ชัน VLOOKUP , 4 เมธอดโดยใช้ฟังก์ชัน Excel อื่นๆ และ 3 โดยใช้เครื่องมือและตัวเลือกต่างๆ ของ Excel เพื่อ vlookup และส่งคืนค่าหลายค่าใน Excel ได้อย่างง่ายดาย
ขอแนะนำให้คุณดาวน์โหลดไฟล์ Excel และฝึกฝนไปพร้อมๆ กัน
8 วิธีในการ VLOOKUP และส่งกลับค่าหลายค่าใน Excel
ในบทช่วยสอนนี้ เราจะใช้ ฐานข้อมูลพนักงาน เพื่อสาธิตวิธีการทั้ง 8 วิธีตลอดทั้งบทความ
ดังนั้น โดยไม่ต้องมีการอภิปรายเพิ่มเติม เรามาทำความรู้จักกับทั้ง 8 วิธีกันเลย
1. ส่งคืนค่าหลายค่าโดยใช้ฟังก์ชัน VLOOKUP ใน Excel
เรารู้ว่า VLOOKUP ฟังก์ชั่นสามารถคืนค่าได้ครั้งละหนึ่งค่าเท่านั้น แต่เราต้องคืนค่าหลายค่า ใช่ มีตัวเลือกอื่นที่สามารถทำได้ แต่ถ้าคุณต้องการใช้เฉพาะ VLOOKUP ทำหน้าที่อย่าสิ้นหวัง มีทางออก ในส่วนนี้ คุณจะได้เรียนรู้วิธีใช้ VLOOKUP ฟังก์ชันเพื่อคืนค่าหลายค่าโดยปรับแต่งชุดข้อมูลของคุณเล็กน้อย ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ COUNTIF และ VLOOKUP . ทำตามขั้นตอนด้านล่าง:
🔗 ขั้นตอน:
ก่อนอื่น เราต้องทำให้ชื่อแผนกทั้งหมดไม่ซ้ำกัน ในการทำเช่นนั้น
❶ เลือก เซลล์ E5 .
❷ ตอนนี้ พิมพ์ สูตร
=B5&COUNTIF(B5:B$13,B5) ภายในเซลล์
❸ หลังจากนั้น กด ENTER ปุ่ม.
❹ การลาก Fill Handle ไอคอนลงไปที่ Dept._Unique คอลัมน์
❺ ตอนนี้เลือกเซลล์ C16 และ พิมพ์ สูตร
=VLOOKUP(B16,E5:F13,2,0) กับมัน
❻ หลังจากนั้น กด ENTER ปุ่ม.
❼ สุดท้าย ลาก Fill Handle ไอคอนต่อท้าย พนักงาน คอลัมน์
แค่นั้นแหละ.
2. Vlookup และดึงค่าหลายค่าในแนวตั้งใน Excel
สมมติว่าคุณต้องการหาจำนวนพนักงานที่ทำงานภายใต้วิศวกรรม แผนกและจัดระเบียบชื่อเหล่านั้นในแนวตั้งในคอลัมน์ หากเป็นเช่นนั้น ให้ทำตามขั้นตอนด้านล่างเพื่อเรียนรู้วิธีดำเนินการ ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ INDEX , เล็ก , MATCH และ ROW .
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก เซลล์ F5 ▶ เพื่อเก็บผลสูตร
❷ หลังจากนั้น พิมพ์ สูตร
=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1))) ภายในเซลล์
❸ จากนั้นกดปุ่ม ENTER ปุ่ม.
❹ ตอนนี้ลาก เติมแฮนเดิล ไอคอนลงใน พนักงาน คอลัมน์
␥ รายละเอียดสูตร
- ROW($B$5:$B$13) ▶ ส่งกลับหมายเลขแถวในรูปแบบอาร์เรย์:{5;6;7;8;9;10;11;12;13}
- MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ แปลงอาร์เรย์ก่อนหน้าเป็นค่าต่อไปนี้:{1;2;3;4;5;6;7;8;9}
- IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) ▶ ส่งกลับหมายเลขแถวที่ตรงกับเงื่อนไข มิฉะนั้นจะคืนค่า null:{“”;2;””;4;”;””;”;”;9}
- SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “ ”) ▶ ส่งกลับตัวเลขขนาดเล็กตัวแรกภายในอาร์เรย์:{“”;2;””;4;””;”;””;”;9}
- INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13),MATCH(ROW($B$5:$B$13), ROW($ B$5:$B$13)), “”) ▶ ส่งคืนชื่อพนักงานตามหมายเลขดัชนีแถวที่ส่งคืนโดย SMALL.
3. Vlookup และส่งกลับค่าหลายค่าในแนวนอนใน Excel
สมมติว่าตอนนี้คุณต้องการทราบจำนวนพนักงานที่ทำงานภายใต้วิศวกรรม แผนกและจัดระเบียบชื่อเหล่านั้นในแนวนอนในแถว ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ INDEX , เล็ก , นาที และ ROW .
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก เซลล์ C16 ▶ เพื่อเก็บผลสูตร
❷ หลังจากนั้น พิมพ์ สูตร
=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1))) ภายในเซลล์
❸ จากนั้นกดปุ่ม ENTER ปุ่ม.
❹ ตอนนี้ลาก เติมแฮนเดิล ไอคอนทางด้านขวาของพนักงาน แถว.
␥ รายละเอียดสูตร
- ROW($B$5:$B$13) ▶ ส่งกลับหมายเลขแถวในรูปแบบอาร์เรย์:{5;6;7;8;9;10;11;12;13}
- SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, “ ”) ▶ ส่งคืนตัวเลขขนาดเล็กตัวแรกภายในอาร์เรย์
- INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5) :$B$13))+1, “”) ▶ ส่งคืนชื่อพนักงานตามหมายเลขดัชนีแถวที่ส่งคืนโดย SMALL.
4. Vlookup และรับค่าหลายค่าใน Excel พร้อมเกณฑ์
ในส่วนนี้ เราจะส่งคืนชื่อพนักงานทั้งหมดใน พนักงาน คอลัมน์ที่ทำงานภายใต้ วิศวกรรม แผนกและยังทำงานใน เช้า กะ. ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ INDEX , เล็ก , IFERROR และ ROW .
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก เซลล์ H5 ▶ เพื่อเก็บผลสูตร
❷ หลังจากนั้น พิมพ์ สูตร
=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13)) *(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"") ภายในเซลล์
❸ จากนั้นกดปุ่ม ENTER ปุ่ม.
❹ ตอนนี้ลาก เติมแฮนเดิล ไอคอนลงใน พนักงาน คอลัมน์
␥ รายละเอียดสูตร
สูตรนี้ค่อนข้างคล้ายกับสองสูตรก่อนหน้า ข้อยกเว้นดังต่อไปนี้:
- ROW($C$5:$C$13)-4 ▶ ส่งกลับหมายเลขแถวภายในอาร์เรย์:{5;6;7;8;9;10;11;12;13} ในที่นี้ หมายเลข 4 หมายถึงหมายเลขแถวที่ส่วนหัวของคอลัมน์ พนักงาน อยู่
- ROW()-4 ▶ ในที่นี้หมายเลข 4 หมายถึงหมายเลขแถวก่อนหน้าก่อนที่แถวเอาต์พุตจะเริ่มขึ้น
- IFERROR ▶ ที่นี่ IFERROR ฟังก์ชันใช้เพื่อสังเคราะห์เอาต์พุตที่กำหนดเองหากมีข้อผิดพลาดเกิดขึ้น
5. Vlookup และแยกค่าหลายค่าใน Excel (ทั้งหมดในเซลล์เดียว)
ตอนนี้เราจะรวบรวมชื่อพนักงานทั้งหมดภายในเซลล์เดียวกันซึ่งทำงานภายใต้แผนกวิศวกรรม ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ TEXTJOIN และ IF
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก เซลล์ H5 ▶ เพื่อเก็บผลสูตร
❷ หลังจากนั้น พิมพ์ สูตร
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,"")) ภายในเซลล์
❸ จากนั้นกดปุ่ม ENTER ปุ่ม.
แค่นั้นแหละ.
␥ รายละเอียดสูตร
ที่นี่ IF ฟังก์ชั่นส่งคืนชื่อพนักงานทั้งหมดที่ตรงกับเงื่อนไขและ TEXTJOIN ฟังก์ชันรวมชื่อพนักงานทั้งหมดที่ส่งคืนโดย IF ฟังก์ชัน
อ่านเพิ่มเติม: วิธีการ VLOOKUP ค่าหลายค่าในเซลล์เดียวใน Excel (2 วิธีง่ายๆ)
6. Vlookup เพื่อคืนค่าหลายค่าใน Excel โดยใช้ตัวกรองอัตโนมัติ
ถ้าคุณต้องการหลีกเลี่ยงการใช้สูตร Excel คุณสามารถ vlookup ผ่านตารางข้อมูลและดึงค่าออกมาได้หลายค่าโดยใช้คุณลักษณะตัวกรองอัตโนมัติ ที่นี่เราจะพยายามแยกแยะชื่อพนักงานทั้งหมดที่ทำงานในแผนกวิศวกรรม
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก ตารางข้อมูลทั้งหมด
❷ จากนั้นไปที่ ข้อมูล ริบบิ้น
❸ หลังจากนั้น คลิกที่ ตัวกรอง ตัวเลือก
❹ ตอนนี้ คลิกที่ไอคอนตัวกรองด้านล่าง แผนก ส่วนหัวของคอลัมน์
❺ ตอนนี้ กาเครื่องหมาย ทำเครื่องหมายที่ วิศวกรรม จากเมนูป๊อปอัป
❻ จากนั้นกด ตกลง ปุ่ม.
ไชโย! คุณทำเสร็จแล้ว ตอนนี้คุณจะได้ผลลัพธ์ดังนี้:
7. Vlookup และแยกค่าหลายค่าใน Excel โดยใช้ตัวกรองขั้นสูง
มีคุณลักษณะที่เรียกว่าตัวกรองขั้นสูงใน Excel ซึ่งคุณสามารถใช้เพื่อค้นหาตารางข้อมูลในแนวตั้งและแยกค่าหลายค่าพร้อมกันได้ ในตัวอย่างต่อไปนี้ เราเลือกแผนกวิศวกรรมภายใต้ส่วนหัวของคอลัมน์แผนก ซึ่งเราจะใช้ค้นหาตลอดทั้งตารางข้อมูลเพื่อหาพนักงานที่ทำงานภายใต้แผนกวิศวกรรม ต่อไปนี้เป็นขั้นตอนที่ต้องปฏิบัติตาม:
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก ตารางข้อมูลทั้งหมด
❷ จากนั้นไปที่ ข้อมูล ริบบิ้น
❸ หลังจากนั้น คลิกที่ ขั้นสูง ตัวเลือก
❹ เมื่อคุณทำขั้นตอนสุดท้ายเสร็จแล้ว กล่องโต้ตอบชื่อ ตัวกรองขั้นสูง จะปรากฏบนหน้าจอ
❺ ตอนนี้ป้อน
$B$4:$C$13 ภายใน ช่วงรายการ: แถบและ
'Advanced Filter'!$E$4:$E$5 ภายใน ช่วงเกณฑ์: บาร์.
❻ หลังจากนั้นคุณสามารถกด ตกลง ปุ่ม.
เมื่อคุณทำขั้นตอนก่อนหน้านี้เสร็จแล้ว คุณจะได้ผลลัพธ์ดังนี้:
8. Vlookup เพื่อดึงค่าหลายค่าใน Excel โดยใช้รูปแบบเป็นตาราง
คุณสามารถใช้วิธีนี้เป็นทางเลือกแทน ตัวกรองอัตโนมัติ และ ตัวกรองขั้นสูง ตัวเลือก. ในวิธีนี้ เราจะใช้ รูปแบบเป็นตาราง ตัวเลือกในการจัดเรียงชื่อพนักงานทั้งหมดภายใต้ วิศวกรรม หมวดหมู่. นี่คือขั้นตอนที่ต้องปฏิบัติตาม:
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก ตารางข้อมูลทั้งหมด
❷ จากนั้นไปที่ หน้าแรก ริบบิ้น
❸ หลังจากนั้นเลือก จัดรูปแบบเป็นตาราง ตัวเลือก
❹ จากนั้น เลือก สไตล์ตารางใดก็ได้ตามต้องการ
ตอนนี้ตารางข้อมูลของคุณจะมีลักษณะเหมือนภาพต่อไปนี้
❺ ในขั้นตอนนี้ ให้คลิกที่ไอคอนตัวกรอง ภายใต้ แผนก ส่วนหัวของคอลัมน์
❻ จากเมนูป๊อปอัป ทำเครื่องหมาย ทำเครื่องหมายเฉพาะใน วิศวกรรม ตัวเลือก
❼ จากนั้นคลิกที่ ตกลง ปุ่ม.
เมื่อคุณทำตามขั้นตอนก่อนหน้าทั้งหมดเสร็จแล้ว ผลลัพธ์ของคุณควรมีลักษณะดังนี้:
สิ่งที่ควรจำ
📌 VLOOKUP ฟังก์ชันไม่สามารถนำมามากกว่าหนึ่งค่าได้
📌 โปรดระวังไวยากรณ์ของฟังก์ชัน
📌 ใส่ช่วงข้อมูลลงในสูตรอย่างระมัดระวัง
บทสรุป
เพื่อสรุป เราได้พูดถึง 8 วิธีที่แตกต่างกันใน vlookup และส่งกลับค่าหลายค่าใน Excel ขอแนะนำให้คุณดาวน์โหลดแบบฝึกหัดที่แนบมาพร้อมกับบทความนี้และฝึกฝนวิธีการทั้งหมดด้วย และอย่าลังเลที่จะถามคำถามใด ๆ ในส่วนความคิดเห็นด้านล่าง เราจะพยายามตอบคำถามที่เกี่ยวข้องทั้งหมดโดยเร็วที่สุด
บทความที่เกี่ยวข้อง
- วิธีการแสดง VLOOKUP ด้วยหลายแถวใน Excel (5 วิธี)
