เมื่อทำงานกับข้อมูลและสูตรใน Excel คุณจะต้องพบกับข้อผิดพลาด
เพื่อจัดการกับข้อผิดพลาด Excel มีฟังก์ชันที่มีประโยชน์ - ฟังก์ชัน IFERROR
ก่อนที่เราจะเข้าใจกลไกของการใช้ฟังก์ชัน IFERROR ใน Excel ก่อนอื่น มาทำความเข้าใจข้อผิดพลาดประเภทต่างๆ ที่คุณอาจพบเมื่อใช้สูตรก่อน
เนื้อหา
ประเภทของข้อผิดพลาดใน Excel
การทำความเข้าใจข้อผิดพลาดใน Excel จะช่วยให้คุณระบุสาเหตุที่เป็นไปได้และวิธีที่ดีที่สุดในการจัดการกับข้อผิดพลาดได้ดียิ่งขึ้น
ต่อไปนี้คือประเภทของข้อผิดพลาดที่คุณอาจพบใน Excel
#N/A ข้อผิดพลาด
สิ่งนี้เรียกว่าข้อผิดพลาด "ไม่มีค่า"
คุณเห็นสิ่งนี้เมื่อคุณใช้สูตรการค้นหาและไม่พบค่า (ดังนั้นจึงไม่พร้อมใช้งาน)
ด้านล่างนี้เป็นตัวอย่างที่ฉันใช้สูตร VLOOKUP เพื่อค้นหาราคาของสินค้า แต่จะส่งคืนข้อผิดพลาดเมื่อไม่พบรายการในอาร์เรย์ของตาราง
#DIV/0!ความผิดพลาด
คุณอาจเห็นข้อผิดพลาดนี้เมื่อตัวเลขหารด้วย 0
สิ่งนี้เรียกว่าข้อผิดพลาดในการหารในตัวอย่างด้านล่างจะให้ #DIV/0!เกิดข้อผิดพลาดเนื่องจากค่าปริมาณ (ตัวหารในสูตร) เป็น 0
#VALUE!ความผิดพลาด
ข้อผิดพลาดของค่าเกิดขึ้นเมื่อคุณใช้ชนิดข้อมูลที่ไม่ถูกต้องในสูตร
ตัวอย่างเช่น ในตัวอย่างด้านล่าง เมื่อฉันพยายามเพิ่มเซลล์ที่มีตัวเลขและอักขระ A จะทำให้ค่าผิดพลาด
สิ่งนี้เกิดขึ้นเพราะคุณสามารถเพิ่มค่าตัวเลขได้เท่านั้น แต่ฉันลองเพิ่มตัวเลขด้วยอักขระข้อความ
#REF! ความผิดพลาด
สิ่งนี้เรียกว่าข้อผิดพลาดในการอ้างอิง และคุณจะเห็นข้อผิดพลาดนี้เมื่อการอ้างอิงในสูตรใช้ไม่ได้อีกต่อไปกรณีนี้อาจเกิดขึ้นได้เมื่อสูตรอ้างอิงถึงการอ้างอิงเซลล์และไม่มีการอ้างอิงเซลล์ (เกิดขึ้นเมื่อคุณลบแถว/คอลัมน์หรือเวิร์กชีตที่อ้างอิงในสูตร)
ในตัวอย่างด้านล่าง แม้ว่าสูตรดั้งเดิมจะเป็น =A2/B2 เมื่อฉันลบคอลัมน์ B การอ้างอิงถึงมันจะกลายเป็น #REF!นอกจากนี้ยังให้ #REF!ข้อผิดพลาดอันเป็นผลมาจากสูตร
#NAME? ข้อผิดพลาด
ข้อผิดพลาดนี้อาจเกิดจากการสะกดผิดของฟังก์ชัน
ตัวอย่างเช่น หากคุณใช้ VLOKUP แทน VLOOKUP โดยไม่ได้ตั้งใจ คุณจะได้รับข้อผิดพลาดชื่อ
#NUM ข้อผิดพลาด
ข้อผิดพลาด Num อาจเกิดขึ้นหากคุณพยายามคำนวณค่าที่มีขนาดใหญ่มากใน Excelเช่น, = 187 ^ 549 จะส่งคืนข้อผิดพลาดที่เป็นตัวเลข
อีกสถานการณ์หนึ่งที่อาจเกิดข้อผิดพลาด NUM ขึ้นได้คือเมื่อคุณใส่สูตรที่มีอาร์กิวเมนต์ตัวเลขที่ไม่ถูกต้องตัวอย่างเช่น หากคุณกำลังคำนวณรากที่สอง มันจะส่งคืนข้อผิดพลาดที่เป็นตัวเลขหากเป็นตัวเลข และคุณให้ตัวเลขติดลบเป็นอาร์กิวเมนต์
ตัวอย่างเช่น ในกรณีของฟังก์ชันรากที่สอง ถ้าคุณให้ตัวเลขติดลบเป็นอาร์กิวเมนต์ ก็จะส่งคืนข้อผิดพลาดที่เป็นตัวเลข (แสดงด้านล่าง)
แม้ว่าฉันจะได้แสดงตัวอย่างบางส่วนที่นี่ แต่อาจมีสาเหตุอื่นๆ มากมายที่ทำให้เกิดข้อผิดพลาดใน Excelเมื่อคุณได้รับข้อผิดพลาดใน Excel คุณจะไม่สามารถทิ้งไว้ที่นั่นได้หากข้อมูลถูกใช้เพิ่มเติมในการคำนวณ คุณต้องแน่ใจว่ามีการจัดการข้อผิดพลาดอย่างถูกวิธี
ฟังก์ชัน Excel IFERROR เป็นวิธีที่ยอดเยี่ยมในการจัดการข้อผิดพลาดทุกประเภทใน Excel
ฟังก์ชัน Excel IFERROR - ภาพรวม
เมื่อใช้ฟังก์ชัน IFERROR คุณสามารถระบุสิ่งที่คุณต้องการให้สูตรส่งคืนแทนที่จะเป็นข้อผิดพลาดถ้าสูตรไม่ส่งกลับข้อผิดพลาด ก็จะส่งกลับผลลัพธ์ของตัวเอง
ไวยากรณ์ฟังก์ชัน IFERROR
=IFERROR(ค่า, value_if_error)
พารามิเตอร์อินพุต
- ค่า - นี่คือพารามิเตอร์เพื่อตรวจสอบข้อผิดพลาดในกรณีส่วนใหญ่จะเป็นสูตรหรือการอ้างอิงเซลล์
- ค่า_if_ข้อผิดพลาด – นี่คือค่าที่ส่งคืนเมื่อเกิดข้อผิดพลาดประเภทข้อผิดพลาดต่อไปนี้ได้รับการประเมินแล้ว: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? และ #NULL!
หมายเหตุเพิ่มเติม:
- ถ้าคุณใช้ "" เป็นพารามิเตอร์ value_if_error เซลล์จะไม่แสดงอะไรเลยเมื่อมีข้อผิดพลาดเกิดขึ้น
- ถ้าค่าหรือพารามิเตอร์ value_if_error อ้างถึงเซลล์ว่าง ฟังก์ชัน Excel IFERROR จะถือว่าเป็นค่าสตริงว่าง
- ถ้าอาร์กิวเมนต์ค่าเป็นสูตรอาร์เรย์ IFERROR จะส่งกลับอาร์เรย์ผลลัพธ์สำหรับแต่ละรายการในช่วงที่ระบุโดยค่า
ฟังก์ชัน Excel IFERROR - ตัวอย่าง
ด้านล่างนี้คือตัวอย่างการใช้ฟังก์ชัน IFERROR ใน Excel สามตัวอย่าง
ตัวอย่างที่ 1 - ส่งกลับเซลล์ว่างแทนที่จะเป็นข้อผิดพลาด
หากคุณมีฟังก์ชันที่อาจส่งคืนข้อผิดพลาด คุณสามารถรวมฟังก์ชัน IFERROR และระบุค่าว่างเป็นค่าที่จะส่งคืนเมื่อเกิดข้อผิดพลาด
ในตัวอย่างที่แสดงด้านล่าง ผลลัพธ์ใน D4 คือ #DIV/0!เกิดข้อผิดพลาดในการหารด้วย 0
ในกรณีนี้ คุณสามารถใช้สูตรต่อไปนี้เพื่อคืนค่าช่องว่างแทนที่จะเป็นข้อผิดพลาด DIV ที่น่าเกลียด
=IFERROR(A1/A2,"")
ฟังก์ชัน IFERROR นี้จะตรวจสอบว่าการคำนวณส่งผลให้เกิดข้อผิดพลาดหรือไม่ถ้าเป็นเช่นนั้น ก็จะคืนค่าช่องว่างที่ระบุในสูตร
คุณยังสามารถระบุสตริงหรือสูตรอื่นๆ ที่จะแสดงแทนค่าว่างได้ที่นี่
ตัวอย่างเช่น สูตรด้านล่างจะแสดงข้อความ "ข้อผิดพลาด" แทนที่จะเป็นเซลล์ว่าง
=IFERROR(A1/A2,"ข้อผิดพลาด")
หมายเหตุ: หากคุณใช้ Excel 2003 หรือเก่ากว่า คุณจะไม่พบฟังก์ชัน IFERROR ที่นั่นในกรณีนี้ คุณต้องใช้ฟังก์ชัน IF ร่วมกับฟังก์ชัน ISERROR
ตัวอย่างที่ 2 - ส่งกลับ "ไม่พบ" เมื่อ VLOOKUP ไม่พบค่า
เมื่อคุณใช้ฟังก์ชัน Excel VLOOKUP ข้อผิดพลาด #N/A จะถูกส่งกลับหากไม่พบค่าการค้นหาภายในช่วงที่ระบุ
ตัวอย่างเช่น ด้านล่างคือชุดข้อมูลชื่อนักเรียนและคะแนนของนักเรียนฉันใช้ฟังก์ชัน VLOOKUP เพื่อรับคะแนนของนักเรียนสามคน (ใน D2, D3 และ D4)
แม้ว่าสูตร VLOOKUP ในตัวอย่างด้านบนจะพบชื่อนักเรียนสองคนแรก แต่ไม่พบชื่อของ Josh ในรายการ ดังนั้นจึงแสดงข้อผิดพลาด #N/A
ในที่นี้ เราสามารถใช้ฟังก์ชัน IFERROR เพื่อส่งคืนข้อความว่างหรือข้อความที่มีความหมายแทนข้อผิดพลาด
ด้านล่างนี้เป็นสูตรที่ส่งคืน "ไม่พบ" แทนที่จะเป็นข้อผิดพลาด
=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),"ไม่พบ")
โปรดทราบว่าคุณสามารถใช้ IFNA แทน IFERROR ใน VLOOKUP ได้แม้ว่า IFERROR จะจัดการค่าความผิดพลาดทุกประเภท IFNA จะใช้กับข้อผิดพลาด #N/A เท่านั้น ไม่ใช่ข้อผิดพลาดอื่นๆ
ตัวอย่างที่ 3 - คืนค่า 0 เมื่อเกิดข้อผิดพลาด
หากคุณไม่ระบุค่าที่ IFERROR ส่งกลับในกรณีที่เกิดข้อผิดพลาด ค่านี้จะคืนค่าเป็น 0 โดยอัตโนมัติ
ตัวอย่างเช่น ถ้าฉันหาร 100 ด้วย 0 ดังที่แสดงด้านล่าง มันจะส่งคืนข้อผิดพลาด
อย่างไรก็ตาม หากฉันใช้ฟังก์ชัน IFERROR ด้านล่าง มันจะคืนค่า 0โปรดทราบว่าคุณยังต้องใช้เครื่องหมายจุลภาคต่อจากพารามิเตอร์แรก
ตัวอย่างที่ 4 - การใช้ IFERROR ที่ซ้อนกันกับ VLOOKUP
บางครั้งเมื่อใช้ VLOOKUP คุณอาจต้องดูตารางการกระจายตัวของอาร์เรย์ตัวอย่างเช่น สมมติว่าคุณมีธุรกรรมการขายใน 2 แผ่นงานแยกกัน และคุณต้องการค้นหาหมายเลขสินค้าและดูมูลค่าของรายการ
การทำเช่นนี้ต้องใช้ IFERROR ที่ซ้อนกันกับ VLOOKUP
สมมติว่าคุณมีชุดข้อมูลที่มีลักษณะดังนี้:
ในกรณีนี้ ในการหาคะแนนของ Grace คุณต้องใช้สูตร IFERROR ที่ซ้อนกันต่อไปนี้:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
การซ้อนสูตรนี้ช่วยให้แน่ใจว่าคุณได้รับค่าจากตารางใดตารางหนึ่งและจัดการกับข้อผิดพลาดที่ส่งคืน
โปรดทราบว่าหากแผ่นงานอยู่บนแผ่นเดียวกัน ในชีวิตจริง อาจเป็นแผ่นอื่น
