ทุกครั้งที่เราต้องดึงค่าจากแผ่นงานอื่นหรือภายในแผ่นงานเดียวกัน เราจะไม่อาจไม่คิดถึง ฟังก์ชัน VLOOKUP ใน Excel แต่ปัญหาหลักของฟังก์ชัน VLOOKUP คือ มันถูกออกแบบมาเพื่อคืนค่าเดียวเท่านั้นในครั้งเดียว ดังนั้น เราจึงต้องคิดนิดหน่อยนอกกรอบ เพื่อ vlookup หลายค่าด้วยฟังก์ชันนี้ ซึ่งเราจะอธิบายในบทความนี้
เรายังสามารถใช้ฟังก์ชัน Excel อื่นๆ เช่น INDEX, SMALL, MATCH, ROW, COLUMNS เป็นต้น รวมกันอย่างเหมาะสม เพื่อ vlookup และคืนค่าหลายค่า เรายังสามารถใช้คุณสมบัติ Excel หลายอย่าง เช่น Advanced Filter, AutoFilter และเครื่องมือ Format as Table ดังนั้น ในบทช่วยสอนนี้ เราจะอภิปรายรวมทั้งหมด 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 ไปจนถึงท้ายคอลัมน์ Employee
เสร็จแล้ว
2. Vlookup และดึงค่าหลายค่าแนวตั้งใน Excel
สมมติว่า คุณต้องการหาจำนวนพนักงานที่ทำงานภายใต้แผนก Engineering และจัดระเบียบชื่อเหล่านั้นในแนวตั้งในคอลัมน์ หากเช่นนั้น ให้ทำตามขั้นตอนด้านล่างเพื่อเรียนรู้วิธีการ ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ INDEX, SMALL, 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
❹ ตอนนี้ลากไอคอน Fill Handle ลงมาในคอลัมน์ Employee
␥ การแยกวิเคราะห์สูตร
- 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)), "") ▶ ส่งคืนหมายเลขแถวที่ตรงตามเงื่อนไข มิฉะนั้นส่งคืนค่าว่าง: {"";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
สมมติว่า ตอนนี้คุณต้องการหาจำนวนพนักงานที่ทำงานภายใต้แผนก Engineering และจัดระเบียบชื่อเหล่านั้นในแนวนอนในแถว ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ INDEX, SMALL, MIN, และ 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
❹ ตอนนี้ลากไอคอน Fill Handle ไปทางขวาของแถว Employee
␥ การแยกวิเคราะห์สูตร
- 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 พร้อมเกณฑ์
ในส่วนนี้ เราจะคืนชื่อพนักงานทั้งหมดในคอลัมน์ Employee ที่ทำงานภายใต้แผนก Engineering และทำงานกะ Morning ด้วย ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ INDEX, SMALL, 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
❹ ตอนนี้ลากไอคอน Fill Handle ลงมาในคอลัมน์ Employee
␥ การแยกวิเคราะห์สูตร
สูตรนี้ค่อนข้างคล้ายกับสูตรก่อนหน้าสองสูตร นี่คือข้อยกเว้น:
- ROW($C$5:$C$13)-4 ▶ ส่งคืนหมายเลขแถวภายในอาร์เรย์: {5;6;7;8;9;10;11;12;13}. ที่นี่ ตัวเลข 4 หมายถึงหมายเลขแถวที่หัวคอลัมน์ Employee อยู่.
- ROW()-4 ▶ ที่นี่ ตัวเลข 4 หมายถึงหมายเลขแถวก่อนหน้าก่อนที่แถวผลลัพธ์จะเริ่มต้น
- IFERROR ▶ ที่นี่ ฟังก์ชัน IFERROR ถูกใช้เพื่อสร้างผลลัพธ์ที่กำหนดเองหากเกิดข้อผิดพลาดใดๆ
5. Vlookup และแยกค่าหลายค่าใน Excel (ทั้งหมดในเซลล์เดียว)
ตอนนี้ เราจะรวมชื่อพนักงานทั้งหมดภายในเซลล์เดียวกัน ที่ทำงานภายใต้แผนก Engineering ฟังก์ชันที่เราจะใช้ในวิธีนี้คือ 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 โดยใช้ AutoFilter
หากคุณต้องการหลีกเลี่ยงการใช้สูตร Excel คุณสามารถ vlookup ผ่านตารางข้อมูลและดึงค่าหลายค่าออกมาได้โดยใช้คุณสมบัติ AutoFilter ที่นี่ เราจะพยายามคัดแยกชื่อพนักงานทั้งหมดที่ทำงานภายใต้แผนก Engineering
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก ตารางข้อมูลทั้งหมด
❷ จากนั้นไปที่ริบบิ้น Data
❸ หลังจากนั้น คลิกที่ตัวเลือก Filter
❹ ตอนนี้คลิกที่ไอคอนกรองใต้หัวคอลัมน์ Department
❺ ตอนนี้ ติ๊กถูก ที่ตัวเลือก Engineering จากเมนูป๊อปอัป
❻ จากนั้นกดปุ่ม OK
เยี่ยม! คุณทำเสร็จแล้ว ตอนนี้คุณจะได้ผลลัพธ์ดังนี้:
7. Vlookup และแยกค่าหลายค่าใน Excel โดยใช้ Advanced Filter
มีคุณสมบัติที่ชื่อว่า Advanced Filter ใน Excel ซึ่งคุณสามารถใช้เพื่อค้นหาผ่านตารางข้อมูลในแนวตั้งและแยกค่าหลายค่าพร้อมกันได้ ในตัวอย่างต่อไปนี้ เราเลือกแผนก Engineering ภายใต้หัวคอลัมน์ Department ซึ่งเราจะใช้ค้นหาตลอดตารางข้อมูล เพื่อหาว่าพนักงานใดทำงานภายใต้แผนก Engineering ตอนนี้ นี่คือขั้นตอนที่ต้องทำตาม:
🔗 ขั้นตอน:
❶ ก่อนอื่น เลือก ตารางข้อมูลทั้งหมด
❷ จากนั้นไปที่ริบบิ้น Data
❸ หลังจากนั้น คลิกที่ตัวเลือก Advanced
❹ เมื่อคุณทำขั้นตอนล่าสุดเสร็จแล้ว กล่องโต้ตอบชื่อ Advanced Filter จะปรากฏบนหน้าจอ
❺ ตอนนี้ ใส่
$B$4:$C$13ภายในแถบ List range: และ
'Advanced Filter'!$E$4:$E$5ภายในแถบ Criteria range:
❻ หลังจากนั้น คุณสามารถกดปุ่ม OK
เมื่อคุณทำตามขั้นตอนก่อนหน้าทั้งหมดเสร็จแล้ว คุณจะได้ผลลัพธ์ดังนี้:
8. Vlookup เพื่อดึงค่าหลายค่าใน Excel โดยใช้ Format as Table
คุณสามารถใช้วิธีนี้เป็นทางเลือกแทนตัวเลือก AutoFilter และ Advanced Filter ในวิธีนี้ เราจะใช้ตัวเลือก Format as Table เพื่อจัดเรียงชื่อพนักงานทั้งหมดภายใต้หมวดหมู่ Engineering นี่คือขั้นตอนที่ต้องทำตาม:
🔗 ขั้นตอน:
❶ ก่อน всего เลือก ตารางข้อมูลทั้งหมด
❷ จากนั้นไปที่ริบบิ้น Home
❸ หลังจากนั้นเลือกตัวเลือก Format as a Table
❹ จากนั้น เลือก สไตล์ตารางใดๆ ที่คุณชอบ
ตอนนี้ตารางข้อมูลของคุณจะมีลักษณะประมาณภาพต่อไปนี้
❺ ในขั้นตอนนี้ คลิกที่ ไอคอน Filter ภายใต้หัวคอลัมน์ Department
❻ จากเมนูป๊อปอัป ติ๊กถูก เฉพาะตัวเลือก Engineering
❼ จากนั้นคลิกที่ปุ่ม OK
เมื่อคุณทำตามขั้นตอนก่อนหน้าทั้งหมดเสร็จแล้ว ผลลัพธ์ของคุณควรจะมีลักษณะดังนี้:
สิ่งที่ควรจดจำ
📌 ฟังก์ชัน VLOOKUP ไม่สามารถดึงค่ามากกว่าหนึ่งค่าได้เพียงลำพัง
📌 ระวังเรื่องไวยากรณ์ของฟังก์ชัน
📌 ใส่ช่วงข้อมูลอย่างระมัดระวังลงในสูตร
สรุป
สรุปแล้ว เราได้อภิปราย 8 วิธีที่แตกต่างกัน ในการ vlookup และคืนค่าหลายค่าใน Excel แนะนำให้ดาวน์โหลดสมุดงานฝึกหัดที่แนบมาพร้อมกับบทความนี้ และฝึกทุกวิธีด้วยสมุดงานนั้น และอย่าลังเลที่จะถามคำถามในส่วนความคิดเห็นด้านล่าง เราจะพยายามตอบคำถามที่เกี่ยวข้องทั้งหมดโดยเร็วที่สุด
บทความที่เกี่ยวข้อง
- วิธีทำ VLOOKUP ด้วยหลายแถวใน Excel (5 วิธี)
