[NEW] หามานาน! สูตร Excel คำนวณ % การเปลี่ยนแปลง (%Growth YoY) | เพอร์เฟค เขียนยังไง – NATAVIGUIDES

“อยากคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงของปีนี้กับปีที่แล้ว ต้องเขียนสูตรยังไงครับ” จู่ๆตั้มก็พุ่งพรวดเข้ามาถามที่โต๊ะแบบไม่ทันตั้งตัว
“ก็เอาตัวเลขปีนี้ลบปีที่แล้ว หารปีที่แล้วสิ”
“ลองแล้วครับพี่ มันได้แค่บางตัว แต่บางตัวไม่ได้ครับ”
“ไม่ได้ยังไงเหรอ?” ผมถามกลับ
“คือ ถ้าตัวเลขปีนี้หรือปีที่แล้วติดลบ ผลลัพธ์จะผิด”
“ยิ่งถ้าตัวเลขปีนี้หรือปีที่แล้วเป็นศูนย์ มันจะขึ้นเป็น #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 เท่านี้ ชีวิตดีขึ้นมากมาย” สนใจดูรายละเอียดได้จาก ลิงค์นี้ ครับ

Share this:

• Facebook

• Twitter

• LinkedIn

Like this:

Like

Loading…

“Perfect”

I might never be your knight in shining armour
I might never be the one you take home to mother
And I might never be the one who brings you flowers
But I can be the one, be the one tonight

ผมอาจะไม่ใช่อัศวินขี่ม้าขาวที่เปล่งประกายในชุดเกราะให้คุณได้

ผมอาจจะไม่ใช่คนที่จะให้คุณพาไปหาแม่ได้

และผมอาจจะไม่ใช่คนที่หอบดอกไม้ช่อโตมาให้คุณได้

แต่ผมเป็นคนคนนั้นได้ คนนั้นในใจคุณได้ในคืนนี้

When I first saw you
From across the room
I could tell that you were curious,
Oh, yeah
Girl, I hope you’re sure
What you’re looking for
‘Cause I’m not good at making promises

ครั้งแรกที่เห็นคุณ

จากมุมนี้ตรงข้ามห้องคุณ

บอกได้เลยว่าคุณค่อนข้างที่จะสงสัย

ผมหวังว่าคุณจะแน่ใจ

ว่าอะไรที่คุณกำลังมองหา

เพราะผมอาจจะรักษาสัญญาไม่ค่อยได้

But if you like causing trouble up in hotel rooms
And if you like having secret little rendezvous
If you like to do the things you know that we shouldn’t do
Then baby, I’m perfect
Baby, I’m perfect for you
And if you like midnight driving with the windows down
And if you like going places we can’t even pronounce
If you like to do whatever you’ve been dreaming about
Then baby, you’re perfect
Baby, you’re perfect
So let’s start right now

แต่ถ้าคุณชอบก่อปัญหาในโรงแรม

หรือว่าชอบที่จะมีที่ลับๆของเรา

ถ้าคุณชอบทำสิ่งที่เราแม่งไม่ควรทำมัน

นั่นแหละ ผมแม่งโคตรเพอร์เฟคอ่ะ

เพอร์เฟคสำหรับคุณสุดๆ

และถ้าชอบขับรถกลางดึกแล้วลดกระจกลงสักนิดนะ

หรือว่าชอบไปเที่ยวในที่ที่เราออกเสียงกันแม่งยังไม่ถูกอ่ะ

หรือว่าชอบทำอะไรก็ตามที่คุณเคยฝันเอาไว้

นี่ไง ผมใช่สำหรับคุณสุดๆอ่ะ

และเธอก็เพอร์เฟค

และเรามาเริ่มกันตอนนี้เลยแล้วกัน

I might never be the hands you put your heart in
Or the arms that hold you any time you want them
But that don’t mean that we can’t live here in the moment
‘Cause I can be the one you love from time to time

When I first saw you
From across the room
I could tell that you were curious,
Oh, yeah
Girl, I hope you’re sure
What you’re looking for
‘Cause I’m not good at making promises

ผมอาจจะยังไม่ใช่คนที่คุณจะวางหัวใจไว้ให้ในมือ

หรือว่าคนที่คอยกอดคุณตลอดเวลาทุกทีที่ต้องการ

แต่ไม่ได้หมายความว่าเราจะอยู่ที่นี่กันในตอนนี้ไม่ได้

เพราะว่าผมสามารถเป็นคนที่รักคุณตลอดไปได้นะสิ

ครั้งแรกที่ผมเห็นคุณ

จากห้องตรงข้าม

บอกได้เลยว่าหน้าตาคุณดูสงสัยมาก

และผมก็หวังว่าคุณจะแน่ใจ

กับสิ่งที่คุณกำลังตามหา

เพราะผมไม่ค่อยเก่งในเรื่องสัญญาสักเท่าไหร่

 

But if you like causing trouble up in hotel rooms
And if you like having secret little rendezvous
If you like to do the things you know that we shouldn’t do
Then baby, I’m perfect
Baby, I’m perfect for you
And if you like midnight driving with the windows down
And if you like going places we can’t even pronounce
If you like to do whatever you’ve been dreaming about
Then baby, you’re perfect
Baby, you’re perfect
So let’s start right now

แต่ถ้าคุณชอบก่อปัญหาในโรงแรม

หรือว่าชอบที่จะมีที่ลับๆของเรา

ถ้าคุณชอบทำสิ่งที่เราแม่งไม่ควรทำมัน

นั่นแหละ ผมแม่งโคตรเพอร์เฟคอ่ะ

เพอร์เฟคสำหรับคุณสุดๆ

และถ้าชอบขับรถกลางดึกแล้วลดกระจกลงสักนิดนะ

หรือว่าชอบไปเที่ยวในที่ที่เราออกเสียงกันแม่งยังไม่ถูกอ่ะ

หรือว่าชอบทำอะไรก็ตามที่คุณเคยฝันเอาไว้

นี่ไง ผมใช่สำหรับคุณสุดๆอ่ะ

และเธอก็เพอร์เฟค

และเรามาเริ่มกันตอนนี้เลยแล้วกัน

And if you like cameras flashing every time we go out,
Oh, yeah
And if you’re looking for someone to write your break-up songs about
Baby, I’m perfect
Baby, we’re perfect

แล้วถ้าคุณชอบแสงแฟลชสาดมาทุกครั้งเวลาที่เราออกไปข้างนอก

และถ้าคุณกำลังตามหาใครสักคนที่จะมาเขียนเพลงอกหักได้

ขอบอกเลยว่าผมนะ

ผมเพอร์เฟคมาก

If you like causing trouble up in hotel rooms
And if you like having secret little rendezvous
If you like to do the things you know that we shouldn’t do
Then baby, I’m perfect
Baby, I’m perfect for you
And if you like midnight driving with the windows down
And if you like going places we can’t even pronounce
If you like to do whatever you’ve been dreaming about
Then baby, you’re perfect
Baby, you’re perfect
So let’s start right now

แต่ถ้าคุณชอบก่อปัญหาในโรงแรม

หรือว่าชอบที่จะมีที่ลับๆของเรา

ถ้าคุณชอบทำสิ่งที่เราแม่งไม่ควรทำมัน

นั่นแหละ ผมแม่งโคตรเพอร์เฟคอ่ะ

เพอร์เฟคสำหรับคุณสุดๆ

และถ้าชอบขับรถกลางดึกแล้วลดกระจกลงสักนิดนะ

หรือว่าชอบไปเที่ยวในที่ที่เราออกเสียงกันแม่งยังไม่ถูกอ่ะ

หรือว่าชอบทำอะไรก็ตามที่คุณเคยฝันเอาไว้

นี่ไง ผมใช่สำหรับคุณสุดๆอ่ะ

และเธอก็เพอร์เฟค

และเรามาเริ่มกันตอนนี้เลยแล้วกัน