เรามีรายการสั่งซื้อพร้อมหมายเลขและชื่อสินค้า ฉันต้องการยกตัวอย่างเช่นดึงออกจากโต๊ะตามหมายเลขคำสั่งซื้อของสินค้าทั้งหมดที่รวมอยู่ในนั้น มากหรือน้อยเช่นนี้:
คุณสมบัติที่ยอดเยี่ยม วีลุคอัพ (VLOOKUP) ในสถานการณ์เช่นนี้จะช่วยได้เพียงบางส่วนเท่านั้น เพราะสามารถดึงข้อมูลได้เฉพาะเมื่อพบการจับคู่ครั้งแรก กล่าวคือ จะให้เราเท่านั้น กระเช้าแอปเปิ้ล. หากต้องการค้นหาและแยกรายการทั้งหมดออกจากตาราง ควรใช้สูตรอาร์เรย์ ชอบอันนี้:
=ดัชนี($B$2:$B$16;น้อยที่สุด(IF($ E $ 2=A2: A16;สาย(B2:B16)-1;»»);สาย()-5))
จะต้องป้อนดังนี้:
- เลือกเซลล์ที่จะแสดงผลลัพธ์ (ในตัวอย่างของเราคือช่วง D6:D20)
- ป้อน (คัดลอกสูตรลงในเซลล์แรก) ของช่วง
- กด Ctrl + เปลี่ยน + เข้าสู่
การลบหน่วยเป็นส่วนๆ STRING(B2:B16)-1 เสร็จสิ้นเนื่องจากส่วนหัวของตาราง ด้วยเหตุผลเดียวกัน เพื่อชดเชยการเปลี่ยนแปลงในช่วงผลลัพธ์ที่สัมพันธ์กับค่าเดิม หมายเลขห้าในส่วนนั้นจะถูกลบ สตริง()-5
เพื่อซ่อน #NUM! ข้อผิดพลาดที่จะปรากฏในเซลล์ว่างในช่วงผลลัพธ์ D6:D20 คุณสามารถใช้ฟังก์ชันตรวจสอบข้อผิดพลาด IF และ EOSH โดยแทนที่สูตรของเราด้วยสูตรที่ซับซ้อนกว่าเล็กน้อย:
=ถ้า(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
ใน Excel 2007 ฟังก์ชัน IFERROR ที่สะดวกกว่าปรากฏขึ้น ซึ่งช่วยให้คุณแก้ปัญหาได้กระชับยิ่งขึ้น:
=ไอเฟอร์เรอร์(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5))-
PS
ใน Excel เวอร์ชันภาษาอังกฤษ ฟังก์ชันเหล่านี้จะมีลักษณะดังนี้:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- การใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาข้อมูลในตาราง
- เวอร์ชันปรับปรุงของฟังก์ชัน VLOOKUP2 ที่สามารถค้นหาในคอลัมน์ใดก็ได้ ไม่ใช่แค่ค่าแรกเท่านั้น
- ฟังก์ชัน VLOOKUP2 และ VLOOKUP3 จาก PLEX add-on
- สูตรอาร์เรย์คืออะไรและใช้ทำอะไร