วิธีการซ่อนข้อผิดพลาดของ Excel ด้วยฟังก์ชั่น if และ iserror

Microsoft Excel เป็นแอปพลิเคชั่นสเปรดชีตอเนกประสงค์ที่มีประสิทธิภาพและยอดเยี่ยมสำหรับการติดตามและจัดการทุกอย่างตั้งแต่สินค้าคงคลังขององค์กรไปจนถึงงบประมาณของธุรกิจขนาดเล็ก ข้อดีอย่างหนึ่งของ Excel คือคุณสามารถตั้งค่าสูตรล่วงหน้าซึ่งจะอัปเดตโดยอัตโนมัติเมื่อคุณป้อนข้อมูลใหม่ น่าเสียดายที่สูตรบางสูตรเป็นไปไม่ได้ในทางคณิตศาสตร์หากไม่มีข้อมูลที่จำเป็นทำให้เกิดข้อผิดพลาดในตารางของคุณเช่น # DIV / 0 !, #VALUE !, #REF !, และ #NAME? แม้ว่าจะไม่เป็นอันตราย แต่อย่างใดข้อผิดพลาดเหล่านี้จะปรากฏในสเปรดชีตของคุณจนกว่าจะได้รับการแก้ไขหรือจนกว่าจะมีการป้อนข้อมูลที่จำเป็นซึ่งอาจทำให้ตารางโดยรวมน่าดึงดูดน้อยลงและเข้าใจยากขึ้น โชคดีที่อย่างน้อยในกรณีของข้อมูลที่หายไปคุณสามารถซ่อนข้อผิดพลาดของ Excel ด้วยความช่วยเหลือจากฟังก์ชัน IF และ ISERROR นี่คือวิธีที่จะทำ
เรากำลังใช้สเปรดชีตติดตามการลดน้ำหนักขนาดเล็กเป็นตัวอย่างของตารางที่จะทำให้เกิดข้อผิดพลาดในการคำนวณ (การคำนวณเปอร์เซ็นต์การสูญเสียน้ำหนัก) ในขณะที่รอข้อมูลใหม่ (การชั่งน้ำหนักตามมา)

ตัวอย่างสเปรดชีตของเรารอการป้อนข้อมูลในคอลัมน์ น้ำหนัก จากนั้นอัปเดตคอลัมน์อื่น ๆ ทั้งหมดโดยอัตโนมัติตามข้อมูลใหม่ ปัญหาคือคอลัมน์ Percent Lost ขึ้นอยู่กับค่าการ เปลี่ยนแปลง ที่ยังไม่ได้อัปเดตสำหรับสัปดาห์ที่ยังไม่ได้ใส่น้ำหนักส่งผลให้ # DIV / 0! ข้อผิดพลาดซึ่งบ่งชี้ว่าสูตรพยายามหารด้วยศูนย์ เราสามารถแก้ไขข้อผิดพลาดนี้ได้สามวิธี:

1. เราสามารถลบสูตรออกจากสัปดาห์ที่ไม่มีการป้อนน้ำหนักจากนั้นเพิ่มสูตรด้วยตนเองในแต่ละสัปดาห์ สิ่งนี้จะได้ผลในตัวอย่างของเราเนื่องจากสเปรดชีตมีขนาดค่อนข้างเล็ก แต่ไม่เหมาะสำหรับสเปรดชีตขนาดใหญ่และซับซ้อนกว่า
2. เราสามารถคำนวณเปอร์เซ็นต์ที่สูญเสียไปโดยใช้สูตรอื่นที่ไม่หารด้วยศูนย์ อีกครั้งเป็นไปได้ในตัวอย่างของเรา แต่อาจไม่ได้ขึ้นอยู่กับสเปรดชีตและชุดข้อมูลเสมอไป
3. เราสามารถใช้ฟังก์ชัน ISERROR ซึ่งเมื่อรวมกับคำสั่ง IF ให้เรากำหนดค่าทางเลือกหรือการคำนวณหากผลลัพธ์เริ่มต้นส่งกลับข้อผิดพลาด นี่คือทางออกที่เราจะแสดงให้คุณเห็นในวันนี้

ฟังก์ชั่น ISERROR

ด้วยตัวเอง ISERROR ทดสอบเซลล์หรือสูตรที่กำหนดและส่งกลับ“ จริง” ถ้าผลลัพธ์ของการคำนวณหรือค่าของเซลล์นั้นเป็นข้อผิดพลาดและ "false" หากไม่ใช่ คุณสามารถใช้ ISERROR ได้ง่ายๆเพียงป้อนการคำนวณหรือเซลล์ในวงเล็บที่ตามด้วยฟังก์ชัน ตัวอย่างเช่น:

ISERROR ((B5-B4) / C5)

หากการคำนวณ (B5-B4) / C5 ส่งคืนข้อผิดพลาด ISERROR จะส่งคืน“ จริง” เมื่อจับคู่กับสูตรที่มีเงื่อนไข ในขณะที่สิ่งนี้สามารถนำไปใช้ประโยชน์ได้หลายวิธี แต่บทบาทที่มีประโยชน์ที่สุดคือเมื่อจับคู่กับฟังก์ชั่น IF

ฟังก์ชั่น IF

ฟังก์ชัน IF ใช้โดยการวางการทดสอบหรือค่าสามค่าในวงเล็บคั่นด้วยเครื่องหมายจุลภาค: IF (ค่าที่จะทดสอบค่าถ้าจริงค่าถ้าเท็จ) ตัวอย่างเช่น:

IF (B5> 100, 0, B5)

ในตัวอย่างด้านบนหากค่าในเซลล์ B5 มากกว่า 100 (ซึ่งหมายความว่าการทดสอบเป็นจริง) จะมีการแสดงค่าศูนย์เป็นค่าเซลล์ แต่ถ้า B5 น้อยกว่าหรือเท่ากับ 100 (ซึ่งหมายความว่าการทดสอบเป็นเท็จ) ค่าจริงของ B5 จะปรากฏขึ้น

IF และ ISERROR รวมอยู่ด้วย

วิธีที่เรารวมฟังก์ชัน IF และ ISERROR คือการใช้ ISERROR เป็นการทดสอบสำหรับคำสั่ง IF ลองมาที่สเปรดชีตลดน้ำหนักของเราเป็นตัวอย่าง สาเหตุที่เซลล์ E6 ส่งคืน # DIV / 0! ข้อผิดพลาดเป็นเพราะสูตรกำลังพยายามแบ่งน้ำหนักรวมที่สูญเสียไปจากน้ำหนักของสัปดาห์ก่อนหน้าซึ่งยังไม่สามารถใช้ได้ในทุกสัปดาห์และทำหน้าที่แบ่งการเป็นศูนย์อย่างมีประสิทธิภาพ
แต่ถ้าเราใช้การรวมกันระหว่าง IF และ ISERROR เราสามารถบอกให้ Excel เพิกเฉยข้อผิดพลาดและเพียงแค่ป้อน 0% (หรือค่าใด ๆ ที่เราต้องการ) หรือเพียงแค่ทำการคำนวณให้เสร็จสมบูรณ์หากไม่มีข้อผิดพลาดเกิดขึ้น ในตัวอย่างของเราสามารถทำได้โดยใช้สูตรต่อไปนี้:

IF (ISERROR (D6 / B5), 0 (D6 / D5))

หากต้องการย้ำสูตรข้างต้นบอกว่าถ้าคำตอบของ D6 / D5 ส่งผลให้เกิดข้อผิดพลาดให้ส่งกลับค่าศูนย์ แต่ถ้า D6 / B5 ไม่ ส่งผลให้เกิดข้อผิดพลาดให้แสดงวิธีการแก้ปัญหานั้น

ด้วยฟังก์ชั่นนั้นคุณสามารถคัดลอกมันไปยังเซลล์ที่เหลือและข้อผิดพลาดใด ๆ จะถูกแทนที่ด้วยศูนย์ อย่างไรก็ตามเมื่อคุณป้อนข้อมูลใหม่ในอนาคตเซลล์ที่ได้รับผลกระทบจะอัปเดตเป็นค่าที่ถูกต้องโดยอัตโนมัติเนื่องจากเงื่อนไขข้อผิดพลาดจะไม่เป็นจริงอีกต่อไป

โปรดทราบว่าเมื่อพยายามซ่อนข้อผิดพลาดของ Excel คุณสามารถใช้ค่าหรือสูตรใดก็ได้สำหรับตัวแปรทั้งสามตัวในคำสั่ง IF มันไม่จำเป็นต้องเป็นศูนย์หรือจำนวนเต็มเหมือนในตัวอย่างของเรา ทางเลือกอื่นรวมถึงการอ้างอิงสูตรที่แยกจากกันอย่างสิ้นเชิงหรือแทรกช่องว่างโดยใช้เครื่องหมายคำพูดสองคำ (“”) เป็นค่า“ จริง” ของคุณ เพื่อแสดงสูตรต่อไปนี้จะแสดงพื้นที่ว่างในกรณีที่เกิดข้อผิดพลาดแทนที่จะเป็นศูนย์:

IF (ISERROR (D6 / B5), "" (D6 / D5))

เพียงจำไว้ว่าคำสั่ง IF อาจกลายเป็นความยาวและซับซ้อนได้อย่างรวดเร็วโดยเฉพาะเมื่อจับคู่กับ ISERROR และง่ายต่อการใส่วงเล็บหรือเครื่องหมายจุลภาคในสถานการณ์เช่นนี้ สูตรรหัสสี Excel รุ่นล่าสุดเมื่อคุณป้อนเพื่อช่วยคุณติดตามค่าเซลล์และวงเล็บ