“อยากคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงของปีนี้กับปีที่แล้ว ต้องเขียนสูตรยังไงครับ” จู่ๆตั้มก็พุ่งพรวดเข้ามาถามที่โต๊ะแบบไม่ทันตั้งตัว
“ก็เอาตัวเลขปีนี้ลบปีที่แล้ว หารปีที่แล้วสิ”
“ลองแล้วครับพี่ มันได้แค่บางตัว แต่บางตัวไม่ได้ครับ”
“ไม่ได้ยังไงเหรอ?” ผมถามกลับ
“คือ ถ้าตัวเลขปีนี้หรือปีที่แล้วติดลบ ผลลัพธ์จะผิด”
“ยิ่งถ้าตัวเลขปีนี้หรือปีที่แล้วเป็นศูนย์ มันจะขึ้นเป็น #DIV/0! ครับ”
“นั่งงมตั้งนานก็ยังคิดไม่ออก พี่ช่วยผมหน่อยครับ” ตั้มเขย่าตัวผมพร้อมส่งสายตาวิงวอน

เคสที่ตั้มเล่า หน้าตาเป็นแบบนี้ครับ

อันที่จริงแล้ว การคิดเปอร์เซ็นต์การเปลี่ยนแปลง คำนวณแบบตรงไปตรงมาได้เลย นั่นคือ
=(Actual – LastYear) / LastYear

เช่น ถ้าเขียนสูตรที่เซลล์ E4 จะได้เป็น
=(B4-C4)/C4

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

ถ้าลองวิเคราะห์ปัญหา จะพบว่าเกิดจากสาเหตุ 3 ประการ นั่นคือ
1. ตัวเลขปีนี้หรือปีที่แล้วติดลบ
2. ตัวเลขปีที่แล้วเป็นศูนย์
3. ตัวเลขปีนี้และปีที่แล้วเป็นศูนย์

ถ้าวิเคราะห์สาเหตุได้แล้ว มาหาทางแก้กันทีละเปลาะครับ ^__^

1. ตัวเลขปีนี้หรือปีที่แล้วติดลบ

สาเหตุนี้แก้ได้ด้วยการปรับสูตรนิดเดียวครับ
นั่นคือ ถ้าทำให้ตัวหารกลายเป็นบวก ผลลัพธ์จะถูกทันที !
(ง่ายกว่าที่คิดใช่ไหมล่ะ ^^)
หรือปรับจาก
=(Actual – LastYear) / LastYear
เป็น
=(Actual – LastYear) / Absolute(LastYear)

การทำให้ตัวหารเป็นบวก ก็คือการใส่ฟังก์ชัน Absolute หรือครอบ LastYear ด้วย ABS นั่นเอง
ถ้าเขียนสูตรที่เซลล์ E4 จะได้เป็น
=(B4-C4) / ABS(C4)

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

งั้นมาวิเคราะห์กันต่อว่า ถ้าตัวเลขปีที่แล้วเป็นศูนย์จะแก้ไขยังไงดี

2. ตัวเลขปีที่แล้วเป็นศูนย์

กรณีตัวเลขปีที่แล้วเป็นศูนย์ ถ้าวิเคราะห์ให้ลึกขึ้น จะแบ่งได้เป็น 2 กรณีย่อยคือ
A: ตัวเลขปีนี้เป็นบวก แต่ปีที่แล้วเป็นศูนย์
B: ตัวเลขปีนี้เป็นลบ แต่ปีที่แล้วเป็นศูนย์
มาวิเคราะห์กันทีละกรณีครับ ^__^

A: ตัวเลขปีนี้เป็นบวก แต่ปีที่แล้วเป็นศูนย์

เช่น Actual = 20, LastYear = 0
เคสนี้ถ้าคิดตามสูตรเดิมจะได้
=(20-0) / ABS(0)
= 20/ 0
= #DIV/0!
ได้ผลลัพธ์เป็น #DIV/0! (divided by zero) เพราะตัวหารเป็นศูนย์
การได้ผลลัพธ์เป็น #DIV/0! ถือว่าถูกในแง่คณิตศาสตร์
แต่ในแง่ของการทำรีพอร์ตอาจถือว่าไม่ถูก
เพราะเคสแบบนี้ คนอ่านรีพอร์ตมักต้องการให้ผลลัพธ์เป็น 100%
คิดง่ายๆว่า ปีที่แล้วไม่มี ปีนี้มี มันก็ต้องถือว่าเพิ่มขึ้น 100% จริงไหม

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

ใช่ครับ แปลว่าต้องขอความช่วยเหลือจากฟังก์ชัน IF
โครงสร้างของฟังก์ชัน IF คือ
=IF( logical_test, value_if_true, [value_if_false] )

เคสนี้มี 2 เงื่อนไข ดังนั้นต้องเชื่อมเงื่อนไขด้วยฟังก์ชัน AND หรือเขียนเงื่อนไขเป็น
AND( B4>0, C4=0 )

เมื่อนำ IF มารวมกับ AND เคสนี้เขียนสูตรได้เป็น
=IF( AND( B4>0, C4=0 ), 100%, (B4-C4)/ABS(C4) )

ได้ผลลัพธ์ตามต้องการ
แต่… อาจมีบางเคสที่ไม่ได้

B: ตัวเลขปีนี้เป็นลบ แต่ปีที่แล้วเป็นศูนย์

เช่น Actual = -10, LastYear = 0
เคสนี้ถ้าคิดตามสูตรเดิมจะได้
=(-10-0)/ ABS(0)
= -10/ 0
= #DIV/0!
ได้ผลลัพธ์เป็น #DIV/0! เพราะตัวหารเป็นศูนย์
เคสแบบนี้ คนอ่านรีพอร์ตมักต้องการให้ผลลัพธ์แสดงเป็น -100%
เพราะคิดง่ายๆว่า ปีที่แล้วไม่มี ปีนี้ติดลบ มันก็ต้องถือว่าลดลง 100% จริงไหม?

ปรับสูตรยังไงดีน่ะหรือครับ?
ต้องมองเพิ่มเติมด้วยว่า ถ้าตัวเลขปีนี้เป็นลบ และปีที่แล้วเป็นศูนย์ ให้แสดงผลลัพธ์เป็น -100%

ใช่ครับ แปลว่าต้องเขียน IF ซ้อนกันสองชั้น

เคสนี้มี 2 เงื่อนไข เชื่อมเงื่อนไขด้วยฟังก์ชัน AND หรือเขียนเงื่อนไขเป็น
AND( B4<0, C4=0 )
หรือปรับสูตรใหม่เป็น

=IF(AND( B4>0, C4=0 ), 100%,
IF(AND( B4<0, C4=0 ), -100%, (B4-C4)/ABS(C4) ) )

เจ๋งใช่ไหมล่ะ ^__^

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

3. ตัวเลขปีนี้และปีที่แล้วเป็นศูนย์

จากสูตรเดิม ถ้า Actual = 0, LastYear = 0 จะหลุดเงื่อนไขทั้ง AND(B4>0,C4=0) และ AND(B4<0,C4=0) จึงถูกคำนวณด้วยกรณีที่เป็น false หรือ (B4-C4)/ABS(C4)
= (0-0)/ ABS(0)
= 0/0
= #DIV/0!

เคสแบบนี้ คนอ่านรีพอร์ตมักต้องการให้แสดงผลลัพธ์เป็น 0%
เพราะคิดง่ายๆว่า ปีที่แล้วเป็นศูนย์ ปีนี้ก็เป็นศูนย์ นั่นคือไม่มีการเปลี่ยนแปลง งั้นก็ต้องแสดงผลเป็น 0%

แล้วจะปรับสูตรยังไงดีน่ะหรือครับ?
ต้องมองเพิ่มเติมว่า ถ้าตัวเลขปีนี้เป็นศูนย์ และปีที่แล้วเป็นศูนย์ ให้แสดงผลลัพธ์เป็น 0%

ใช่ครับ แปลว่าต้องเขียน IF เพิ่มขึ้นอีกหนึ่งชั้น

เขียนเงื่อนไขเป็น AND( B4=0, C4=0 )
หรือปรับสูตรใหม่เป็น

=IF( AND( B4>0, C4=0 ),100%,
IF( AND( B4<0, C4=0 ),-100%,
IF( AND( B4=0, C4=0 ),0, (B4-C4)/ABS(C4) ) ) )

ฮัดช่า !!
ได้ตามต้องการแล้ว ^__^

หืมม์! สูตรดูยากหรือครับ?
ที่สูตรดูยากเพราะเคสนี้ดูเผินๆเหมือนจะง่าย แต่เอาเข้าจริงแล้วมีเงื่อนไขยิบย่อยมาก
ลองมองง่ายๆว่า เคสนี้แสดงผลไดัถึง 4 แบบ นั่นคือ
1. 100%
2. -100%
3. 0%
4. (B4-C4)/ABS(C4)
ถ้ามีผลลัพธ์ 4 แบบ แปลว่าต้องใช้ IF ซ้อนกัน 3 ชั้น
หรืออธิบายง่ายๆด้วยภาพนี้ครับ

แม้สูตรอาจดูยาวเล็กน้อย แต่ถ้าเราเข้าใจ ก็ไม่ยากจริงไหมครับ ^__^

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

บทความนี้มี ep2 ด้วย สูตรสั้นกว่าเดิมอีก สนใจคลิก ลิงค์นี้ ได้เลย

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

อ้อ! ตอนนี้ผมมีคอร์สออนไลน์ชื่อ “Excel In Essence: รู้ Excel เท่านี้ ชีวิตดีขึ้นมากมาย” สนใจดูรายละเอียดได้จาก ลิงค์นี้ ครับ