“IFERROR ต่างกับ ISERROR ยังไง?”

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

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

จุดประสงค์ของการใช้ทั้งสองฟังก์ชั่นคือ

ป้องกันความผิดพลาดจากการคำนวณ

ความผิดพลาด หมายถึงอะไร?

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

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

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

ถ้าไม่ต้องการ เรียกใช้บริการ ISERROR หรือ IFERROR ได้เลย

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

วิธีการใช้ฟังก์ชั่น IFERROR คือ

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

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

  1. ค่าที่ต้องการให้คำนวณ ถ้าค่านั้นไม่เกิดความผิดพลาด
  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)

IFERROR_Example_151121
ตัวอย่างการใช้ฟังก์ชั่น IFERROR

จากภาพ จะเห็นว่าราคาของ 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 ประเทศเท่านั้น ตามลิสต์นี้

FocusList_ISERROR_151121

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

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

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

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

ISERROR_Example_151121
ตัวอย่างการใช้ฟังก์ชั่น ISERROR

เรานำฟังก์ชั่น 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 ก็ได้นะครับ แต่ต้องปรับรูปแบบลิสต์โฟกัสเล็กน้อย เป็นแบบนี้

FocusList_IFERROR_151121

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

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

ChangeISERRORtoIFERROR_151121
ประยุกต์เพื่อใช้ IFERROR แทน ISERROR

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

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 เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

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