เมื่อใช้สูตร VLOOKUP ใน Excel บางครั้งคุณอาจพบข้อผิดพลาด #N/A ที่น่าเกลียดสิ่งนี้จะเกิดขึ้นเมื่อสูตรของคุณไม่พบค่าการค้นหา
ในบทช่วยสอนนี้ ฉันจะแสดงวิธีต่างๆ ในการใช้ IFERROR และ VLOOKUP เพื่อจัดการกับข้อผิดพลาด #N/A ในเวิร์กชีต
ใช้ IFERROR ร่วมกับ VLOOKUP เพื่อแสดงสิ่งที่มีความหมายแทนข้อผิดพลาด #N/A (หรือข้อผิดพลาดอื่นๆ)
ก่อนที่เราจะลงรายละเอียดเกี่ยวกับวิธีใช้ชุดค่าผสมนี้ มาดูฟังก์ชัน IFERROR คร่าวๆ เพื่อดูว่ามันทำงานอย่างไร
เนื้อหา
คำอธิบายฟังก์ชัน IFERROR
เมื่อใช้ฟังก์ชัน IFERROR คุณสามารถระบุสิ่งที่ควรเกิดขึ้นเมื่อการอ้างอิงสูตรหรือเซลล์ส่งกลับข้อผิดพลาด
นี่คือไวยากรณ์ของฟังก์ชัน IFERROR
=IFERROR(ค่า, value_if_error)
- ค่า - นี่คือพารามิเตอร์เพื่อตรวจสอบข้อผิดพลาดในกรณีส่วนใหญ่จะเป็นสูตรหรือการอ้างอิงเซลล์เมื่อใช้ VLOOKUP กับ IFERROR สูตร VLOOKUP จะเป็นพารามิเตอร์นี้
- ค่า_if_ข้อผิดพลาด – นี่คือค่าที่ส่งคืนเมื่อเกิดข้อผิดพลาดประเภทข้อผิดพลาดต่อไปนี้ได้รับการประเมินแล้ว: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? และ #NULL!
สาเหตุที่เป็นไปได้สำหรับ VLOOKUP ที่ส่งคืนข้อผิดพลาด #N/A
ฟังก์ชัน VLOOKUP อาจส่งคืนข้อผิดพลาด #N/A ด้วยเหตุผลใดๆ ต่อไปนี้:
- ไม่พบค่าการค้นหาในอาร์เรย์การค้นหา
- มีช่องว่างนำหน้า ต่อท้าย หรือสองเท่าในค่าการค้นหา (หรืออาร์เรย์ตาราง)
- มีการพิมพ์ผิดในค่าการค้นหาหรือค่าการค้นหาในอาร์เรย์
คุณสามารถใช้ IFERROR ร่วมกับ VLOOKUP เพื่อจัดการกับสาเหตุของข้อผิดพลาดเหล่านี้ทั้งหมดอย่างไรก็ตาม คุณควรคำนึงถึงเหตุผลที่ #2 และ #3 และแก้ไขในแหล่งข้อมูล แทนที่จะปล่อยให้ IFERROR จัดการ
หมายเหตุ: IFERROR จะจัดการข้อผิดพลาดโดยไม่คำนึงถึงสาเหตุหากคุณต้องการจัดการเฉพาะข้อผิดพลาดที่เกิดจาก VLOOKUP ไม่พบค่าการค้นหา ให้ใช้ IFNA แทนวิธีนี้จะช่วยให้แน่ใจว่าข้อผิดพลาดอื่นที่ไม่ใช่ #N/A ไม่ได้รับการจัดการ และคุณสามารถตรวจสอบข้อผิดพลาดอื่นๆ เหล่านี้ได้
คุณสามารถใช้ฟังก์ชัน TRIM เพื่อจัดการกับช่องว่างนำหน้า ต่อท้าย และสองเท่า
แทนที่ข้อผิดพลาด VLOOKUP #N/A ด้วยข้อความที่สื่อความหมาย
สมมติว่าคุณมีชุดข้อมูลที่มีลักษณะดังนี้:
ดังที่คุณเห็น สูตร VLOOKUP ส่งกลับข้อผิดพลาดเนื่องจากค่าการค้นหาไม่อยู่ในรายการเรากำลังหาสกอร์ของเกล็น มันไม่อยู่ในตารางคะแนน
แม้ว่าชุดข้อมูลนี้จะมีขนาดเล็กมาก แต่คุณอาจได้ชุดข้อมูลขนาดใหญ่ที่คุณต้องตรวจสอบรายการต่างๆ ที่เกิดขึ้นคุณจะได้รับข้อผิดพลาด #N/A สำหรับแต่ละกรณีที่ไม่พบค่า
นี่คือสูตรที่คุณสามารถใช้เพื่อให้ได้สิ่งที่มีความหมาย ไม่ใช่ #N/A ผิด
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,0),"ไม่พบ")
สูตรด้านบนส่งคืนข้อความ "ไม่พบ" แทนที่จะเป็นข้อผิดพลาด #N/Aคุณยังสามารถใช้สูตรเดียวกันเพื่อแสดงค่าว่าง ศูนย์ หรือข้อความที่มีความหมายอื่นๆ
VLOOKUP ที่ซ้อนกันด้วยฟังก์ชัน IFERROR
หากคุณกำลังใช้ VLOOKUP และตารางค้นหาของคุณกระจายอยู่ในเวิร์กชีตเดียวกันหรือเวิร์กชีตอื่น คุณต้องตรวจสอบค่า VLOOKUP ผ่านตารางเหล่านี้ทั้งหมด
ตัวอย่างเช่น ในชุดข้อมูลที่แสดงด้านล่าง มีตารางชื่อนักเรียนและคะแนนแยกกันสองตาราง
หากฉันต้องค้นหาคะแนนของ Grace ในชุดข้อมูลนี้ ฉันต้องใช้ฟังก์ชัน VLOOKUP เพื่อตรวจสอบตารางแรก และหากไม่พบค่าในชุดข้อมูล ให้ตรวจสอบตารางที่สอง
นี่คือสูตร IFERROR ที่ซ้อนกันที่ฉันสามารถใช้เพื่อค้นหาค่า:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
การใช้ VLOOKUP กับ IF และ ISERROR (ก่อน Excel 2007)
ฟังก์ชัน IFERROR ถูกนำมาใช้ใน Excel 2007 สำหรับ Windows และ Excel 2016 ใน Mac
หากคุณกำลังใช้เวอร์ชันก่อนหน้า ฟังก์ชัน IFERROR จะไม่ทำงานบนระบบของคุณ
คุณสามารถจำลองการทำงานของฟังก์ชัน IFERROR ได้โดยการรวมฟังก์ชัน IF เข้ากับฟังก์ชัน ISERROR
ให้ฉันแสดงให้คุณเห็นอย่างรวดเร็วถึงวิธีการใช้ IF และ ISERROR แทน IFERROR
ในตัวอย่างข้างต้น แทนที่จะใช้ IFERROR คุณสามารถใช้สูตรที่แสดงในเซลล์ B3:
=IF(ISERROR(A3),"ไม่พบ",A3)
ส่วน ISERROR ของสูตรจะตรวจสอบข้อผิดพลาด (รวมถึงข้อผิดพลาด #N/A) และส่งกลับค่า TRUE หากพบข้อผิดพลาด มิฉะนั้น FALSE
- ถ้า TRUE (ระบุข้อผิดพลาด) ฟังก์ชัน IF จะส่งกลับค่าที่ระบุ ("ไม่พบ" ในกรณีนี้)
- หากเป็น FALSE (ซึ่งหมายความว่าไม่มีข้อผิดพลาด) ฟังก์ชัน IF จะคืนค่านี้ (A3 ในตัวอย่างด้านบน)
IFERROR และ IFNA
IFERROR จัดการข้อผิดพลาดทุกประเภท ในขณะที่ IFNA จัดการเฉพาะข้อผิดพลาด #N/A
เมื่อจัดการกับข้อผิดพลาดที่เกิดจาก VLOOKUP คุณต้องแน่ใจว่าคุณใช้สูตรที่ถูกต้อง
เมื่อต้องการจัดการกับข้อผิดพลาดต่างๆ ได้โปรดใช้ IFERRORขณะนี้ ข้อผิดพลาดสามารถเกิดจากปัจจัยต่างๆ ได้ (เช่น สูตรที่ไม่ถูกต้อง, ช่วงที่ชื่อที่สะกดผิด, ไม่พบค่าการค้นหา และการคืนค่าที่ไม่ถูกต้องจากตารางค้นหา)IFERROR ไม่สำคัญ มันจะแทนที่ข้อผิดพลาดทั้งหมดเหล่านี้ด้วยค่าที่ระบุ
ใช้ IFNA เมื่อคุณต้องการจัดการข้อผิดพลาด #N/A เท่านั้นซึ่งน่าจะเกิดจากสูตร VLOOKUP ไม่พบค่าการค้นหา
