“ฟังก์ชัน ISERROR ต่างกับ IFERROR ยังไงครับ?”

ผมได้รับคำถามนี้จากแฟนเพจหลายท่าน แต่ยังไม่ได้ตอบซักที

วันนี้ฤกษ์งามยามดี ขอตอบในบทความนี้ละกันครับ

ฟังก์ชันทั้งสองใช้ป้องกันความผิดพลาดจากการคำนวณ

ความผิดพลาดจากการคำนวณ คืออะไร?

ความผิดพลาดจากการคำนวณ (Error) ก็คือ คำนวณแล้วได้ผลลัพธ์ 1 ใน 7 อย่างนี้

1. #VALUE!
2. #REF!
3. #DIV/0!
4. #NUM!
5. #NAME?
6. #NULL!
7. #N/A

โดยทั่วไป เราไม่ต้องการให้แสดงค่าเหล่านี้ในรีพอร์ตใช่ไหมครับ

มาเจาะลึกกันทีละตัวครับ เริ่มจาก IFERROR ก่อน

โครงสร้างฟังก์ชัน IFERROR คือ

=IFERROR(ค่าที่ต้องการให้คำนวณ, ค่าที่ต้องการให้แสดงถ้าเกิดความผิดพลาด)

ผลลัพธ์ที่ได้จาก IFERROR มีความเป็นไปได้ 2 อย่างคือ

• ค่าที่ต้องการให้คำนวณ ถ้าค่านั้นไม่เกิดความผิดพลาด
• ค่าที่ต้องการให้แสดงถ้าเกิดความผิดพลาด เช่น 0, “No Data”, “Not Assigned”

สมมติเราต้องการคำนวณราค่าเฉลี่ย

สูตรคำนวณราคาเฉลี่ยคือ

=ยอดขาย/ปริมาณ

เช่น ถ้ายอดขาย = 2,000 บาท ปริมาณ = 10 ชิ้น

ราคาเฉลี่ย

= 2,000/10

= 20

แต่ในชีวิตจริง อะไรๆมันไม่ราบลื่นเหมือนลานสเก็ตน้ำแข็ง มันมักจะมีเคสที่เกิดความผิดพลาดจากการคำนวณทุกทีสิ!

เช่น ยอดขาย = 300 แต่ ปริมาณ = 0 !!

มีเคสแบบนี้ด้วยเหรอ?

มีสิครับ เช่น การออกใบเพิ่มหนี้ ใบลดหนี้ ยังไงล่ะครับ ^__^

ถ้าเราเขียนสูตรแค่ว่า

ราคาเฉลี่ย = ยอดขาย/ปริมาณ

แล้วไปเจอกับข้อมูลที่เป็นใบลดหนี้ ผลลัพธ์จากการคำนวณคือ

#DIV/0!

ถ้าเราอยากเปลี่ยนจาก #DIV/0! เป็นค่าอื่นๆที่ดูสบายตา เช่น 0

เรานำ IFERROR มาช่วยแบบนี้ครับ

=IFERROR(D2/C2,0)

จากภาพ จะเห็นว่าราคาของ Product 4 ในแถวที่ 5 เท่ากับ 0

เพราะเราใส่ฟังก์ชัน IFERROR ครอบไว้นั่นเอง!

ส่วนค่าอื่นๆได้เท่ากับ ยอดขาย/ปริมาณ

(Sales/Volume, D2/C2)

เพราะไม่เกิดความผิดพลาดจากการคำนวณ

ก็ดูใช้ง่ายดีนะ แล้ว ISERROR ล่ะ?

โครงสร้างฟังก์ชัน ISERROR คือ

=ISERROR(ค่าอะไรซักค่านึง)

ผลลัพธ์ที่ได้จาก ISERROR มี 2 แบบเท่านั้นคือ

TRUE หรือ FALSE 

ถ้า “ค่าอะไรซักค่านึง”นั้นผิดพลาด ค่าที่ได้คือ TRUE

ถ้า “ค่าอะไรซักค่านึง” นั้นไม่มีความผิดพลาด ค่าที่ได้คือ FALSE

มันดูแหม่งๆยังไงชอบกลอ่ะ..

อาจขัดแย้งกับความรู้สึกแว่บแรกนิดนึงนะครับ

เพราะแว่บแรก เรามักเข้าใจว่า ถ้า “ค่าอะไรซักค่านึง” ผิดพลาด ผลลัพธ์ที่ได้คือ FALSE แต่ความจริงกลับตรงข้าม

จริงๆแล้ว ISERROR คือการตรวจสอบความผิดพลาด

ถ้าค่านั้นผิดพลาด มันจะแสดงค่า TRUE (ก็มันผิดนี่)

ถ้าค่านั้นไม่มีอะไรผิดพลาด มันจะแสดงค่า FALSE (ก็มันไม่ผิดพลาดนี่นา)

ในทางปฏิบัติ ฟังก์ชัน ISERROR มักใช้คู่กับฟังก์ชัน IF เสมอ ในรูปแบบนี้ครับ

=IF(ISERROR(การคำนวณอะไรซักอย่าง), ถ้า “การคำนวณอะไรซักอย่าง” ผิดพลาดจะทำไง, ถ้า “การคำนวณอะไรซักอย่าง” ไม่ผิดพลาดจะทำไง)

ขอยกตัวอย่างการใช้ดังนี้ครับ

สมมติว่า เรามีข้อมูลยอดขายของประเทศต่างๆทั่วโลก แต่เราต้องการโฟกัสแค่ 3 ประเทศเท่านั้น ตามลิสต์นี้

นั่นคือ ถ้าประเทศนั้นอยู่ในลิสต์นี้ ให้แสดงคำว่า “Focus”

แต่ถ้าประเทศนั้นไม่อยู่ในลิสต์นี้ ให้แสดงคำว่า “Normal”

เราจะเขียนสูตรในลักษณะนี้ครับ

=IF(ISERROR(MATCH(A2,$E$2:$E$4,0)),”Normal”,”Focus”)

เรานำฟังก์ชัน MATCH มาช่วย เพื่อหาว่า ประเทศนั้นอยู่ในลิสต์ที่เราโฟกัสหรือเปล่า

แล้วครอบด้วย ISERROR อีกที เพื่อตรวจสอบว่า ค่าที่ได้จาก MATCH เกิดความผิดพลาดหรือไม่

ถ้าประเทศนั้นอยู่ในลิสต์โฟกัส ค่าที่ได้จาก MATCH หรือ

MATCH(A2,$E$2:$E$4,0)

คือ ลำดับ ของประเทศในลิสต์

เช่น 1,2,3

แต่ถ้าไม่อยู่ ก็จะแสดงค่าความผิดพลาด (#N/A) แทน

USA ไม่อยู่ในลิสต์โฟกัสของเรา ค่าที่ได้จาก

MATCH(A3,$E$2:$E$4,0)

คือ #N/A

ดังนั้น ค่าที่ได้จาก ISERROR(MATCH(A3,$E$2:$E$4,0)) ก็คือ

ISERROR(#N/A)

= TRUE

(TRUE เพราะ #N/A ผิดพลาด)

ค่าที่ได้จาก IF หรือก็คือ

=IF(ISERROR(…..),”Normal”,”Focus”)

=IF(TRUE,”Normal”,”Focus”)

=”Normal”

มาลองดู Thailand กันบ้าง

Thailand อยู่ในลิสต์โฟกัสของเราลำดับที่ 1 ค่าที่ได้จาก

MATCH(A3,$E$2:$E$4,0)

= 1

ดังนั้น ค่าที่ได้จาก

ISERROR(MATCH(A4,$E$2:$E$4,0)) ก็คือ

ISERROR(1)

= FALSE

(FALSE เพราะ 1 ไม่ใช่ความผิดพลาด)

ค่าที่ได้จาก IF หรือก็คือ

=IF(ISERROR(…..),”Normal”,”Focus”)

=IF(FALSE,”Normal”,”Focus”)

=”Focus”

สำหรับคนที่สนใจ สามารถดาวน์โหลดไฟล์ตัวอย่างได้ที่ลิงค์นี้ครับ

ISERRORvsIFERROR_151121

พอจะเห็นความแตกต่างระหว่าง IFERROR และ ISERRORไหมครับ?

เจ้าสองฟังก์ชันนี้ สะกดคล้ายกัน แต่ให้ผลลัพธ์ไม่เหมือนกัน

ผลลัพธ์ที่ได้จาก ISERROR คือ TRUE หรือ FALSE เท่านั้น

เราจึงแทบไม่ใช้ ISERROR เดี่ยวๆเลย ส่วนใหญ่มักใช้ร่วมกับ IF เสมอ

ISERROR เป็นฟังก์ชันที่เกิดขึ้นมาก่อน แต่รูปแบบการใช้งานค่อนข้างซับซ้อน ใช้เดี่ยวๆไม่ค่อยได้ ขัดกับความรู้สึกแว่บแรก

เอ็กเซลจึงพัฒนาฟังก์ชันใหม่ให้ใช้ง่ายขึ้น คือ

IFERROR นั่นเอง!

จากเคสเรื่องลิสต์โฟกัสเมื่อสักครู่ เราอาจใช้ IFERROR ก็ได้ แต่ต้องปรับรูปแบบลิสต์โฟกัสเล็กน้อย เป็นแบบนี้

แล้วนำฟังก์ชัน VLOOKUP มาช่วย ในลักษณะนี้

=IFERROR(VLOOKUP(A2,$E$2:$F$4,2,0),”Normal”)

จากตัวอย่างเดิม

USA ไม่อยู่ในลิสต์โฟกัส VLOOKUP จึงหาไม่เจอ ค่าที่ได้จาก VLOOKUP จึงเป็น

#N/A

เมื่อ #N/A อยู่ใน IFERROR  หรือก็คือ

=IFERROR(#N/A,”Normal”)

=”Normal”

ลองมาดู Thailand กันบ้าง

Thailand อยู่ในลิสต์โฟกัส ดังนั้น ค่าที่ได้จาก VLOOKUP คือ”Focus”

เมื่อ “Focus” อยู่ใน IFERROR  หรือก็คือ

=IFERROR(VLOOKUP(A3,$E$2:$F$4,2,0),”Normal”)

=IFERROR(“Focus”,”Normal”)

=”Focus”

จะเห็นได้ว่า ถ้าปรับเล็กน้อย ก็สามารถใช้ IFERROR แทน ISERROR ได้

โดยส่วนตัวแล้ว ผมไม่ค่อยชอบปรับเปลี่ยนตารางอ้างอิง ยิ่งถ้าต้องเพิ่มอะไรบางอย่างเข้าไป มักจะไม่ทำ

ถ้าเป็นเคสนี้ ผมเลือกใช้ ISERROR คู่กับ IF แม้ว่าอาจเขียนสูตรยากกว่านิดหน่อยก็ตาม

แต่ถ้ารู้สึกฝึนๆกับ ISERROR จะปรับตารางอ้างอิงสักนิด เพื่อใช้ IFERROR ก็ไม่ผิดอะไร ต่างคนต่างมี “สไตล์” เป็นของตัวเอง

ขออย่างเดียว อย่าพิมพ์ชื่อฟังก์ชันผิดเป็น IFISERROR ก็พอครับ ^_^

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่าน เพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^