“Excel คำนวณหาผลรวมค่า Top 3 ได้ไหม?” เพื่อนคนหนึ่งถาม
“ยังไงเหรอ อธิบายหน่อย” ผมถามกลับ เพราะไม่แน่ใจว่าเข้าใจถูกหรือเปล่า
“สมมติมียอดขายแต่ละประเทศ อยากได้เฉพาะยอดรวมของ 3 ประเทศที่มียอดขายสูงสุด เขียนสูตรยังไงดี“ เพื่อนอธิบาย
“เขียนได้สิ ไม่ยากด้วยนะ” ผมตอบ
“แล้วสมมติว่า มีเงื่อนไขเพิ่มเติม เช่นอยากได้เฉพาะยอดขายของประเทศในทวีปเอเชียล่ะ?” เพื่อนเริ่มแพลมความต้องการ
“ได้สิ ปรับสูตรนิดหน่อยก็ได้แล้ว” ผมตอบ
“แล้วถ้าอยากปรับเป็นผลรวมของ Top N ตามต้องการ เช่น Top5, Top 10 ล่ะ ทำได้ไหม?” เพื่อนเริ่มจินตนาการสร้างสรรค์
“ได้สิ แต่อาจเขียนสูตรซ้อนกันนิดหน่อย” ผมตอบ
“แล้วถ้าข้อมูลมีความผิดพลาดล่ะ เช่น ยอดขายควรเป็นตัวเลข แต่บางครั้งคีย์ข้อมูลเป็น “NoData” เขียนสูตรได้ไหม?” เพื่อนเริ่มสาธยาย
(แบบนี้เค้าเรียก “ได้คืบจะเอาศอก” ^^)
“ได้สิ Excel เขียนสูตรได้หมดแหละ“ ผมให้ความมั่นใจ
“แล้วสูตรที่ว่า เขียนยังไงอ่ะ?” ความต้องการสุดท้ายถูกประกาศแล้ว …
เขียนสูตรยังไงน่ะหรือครับ?
เพื่อให้เข้าใจทุกขั้นตอน มาดูแนวคิดทีละเต็ปแบบละเอียดกันเลยครับ ^__^
สมมติข้อมูลยอดขายแต่ละประเทศถูกเก็บในเซลล์ A4 ถึง B16 แบบนี้
ต้องการหาผลรวมของประเทศที่มียอดขายสูงสุด 3 อันดับแรก
จากภาพคือ China (180), Germany (170) และ Japan (140) หรือต้องได้ผลรวม
= 180+170+140
= 490
ถ้าคิดสูตรง่ายๆก็จะเป็น
=SUM(Top1, Top2, Top3)
ปัญหาคือจะหา Top1, Top2, Top3 ได้ยังไง?
หาได้ง่ายๆเลยครับ ด้วยฟังก์ชัน LARGE ^__^
ผลลัพธ์จากฟังก์ชัน LARGE คือค่าสูงสุดอันดับที่ต้องการ (k)
โครงสร้างฟังก์ชันคือ
=LARGE( array, k )
array = ชุดข้อมูล (ต้องเป็นต้วเลข) เช่นเคสนี้คือ B4:B16
k = อันดับที่ต้องการ เช่น ถ้าต้องการอันดับ 3 ก็ใส่ k เป็น 3
จากข้อมูลชุดนี้ ถ้าเขียนสูตรเป็น
=LARGE($B$4:$B$16,3)
(ขอล็อค B4:B16 เป็น $B$4:$B$16 เผื่อกรณีลากสูตรไปด้านล่างหรือด้านขวานะครับ)
จะได้ผลลัพธ์เป็น ค่าอันดับ 3
หรือผลลัพธ์ของเคสนี้ก็คือ
140 (Japan)
ดูเผินๆเหมือนจะโอเค
แต่.. ปัญหาคือ ถ้าเขียนสูตรแบบนี้ จะได้ค่าอันดับ 3 เพียงค่าเดียว !
แล้วถ้าอยากได้ค่าอันดับ 1, 2, 3 พร้อมกันล่ะ?
ไม่ยากครับ เราก็ระบุ k เป็น 1,2,3 ไปเลย แต่คงเขียนสูตรเป็น LARGE($B$4:$B$16, 1,2,3) ไม่ได้
Excel จะนึกว่าเราใส่องค์ประกอบ (Argument) ของ LARGE 4 ตัว ซึ่งผิดไวยากรณ์ของฟังก์ชัน
แล้วจะแก้ปัญหายังไงน่ะหรือครับ?
พิมพ์ตรงๆไม่ได้ แต่พิมพ์เป็นช่วงข้อมูลคงที่ (Array Constant) ได้ นั่นคือ เปลี่ยนจาก 1,2,3 เป็น
{1,2,3}
หรือถ้าพิมพ์เต็มๆใน LARGE คือ
=LARGE($B$4:$B$16,{1,2,3})
ผลลัพธ์จากสูตรคือ
={180,170,140}
ถ้าต้องการผลรวม ก็เพียงใส่ SUM ครอบ แบบนี้
=SUM(LARGE($B$4:$B$16,{1,2,3}))
=SUM({180,170,140})
= 490
เจ๋งป่ะล่ะ ^^
ถ้าไม่มีเงื่อนไขใด ใช้สูตรนี้ได้เลย สั้นและง่ายสุดๆ
แล้วถ้ามีเงื่อนไขล่ะ เช่น อยากได้ผลรวมของ Top 3 ของประเทศที่อยู่ในทวีปเอเชีย?
สมมติระบุทวีปลงในข้อมูลด้วย หรือระบุแล้วได้ข้อมูลหน้าตาแบบนี้
ถ้าเป็นแบบนี้ เราคงเขียนสูตร =SUM(LARGE($C$4:$C$16,{1,2,3})) ไม่ได้ เพราะสูตรไม่ได้ระบุเงื่อนไขเรื่องทวีป
ถ้าจะให้ถูก ต้องระบุเงื่อนไขลงไปใน LARGE ด้วย
สมมติเขียนเงื่อนไขไว้ในเซลล์ E4 (พร้อมทำ dropdown list เพื่อให้ปรับเปลี่ยนได้สะดวก)
เงื่อนไขของเคสนี้คือ $B$4:$B$16=E4
ต้องเพิ่มเงื่อนไขเข้าไปใน LARGE หรือจาก
LARGE($C$4:$C$16,{1,2,3})
ต้องเปลี่ยนสูตรเป็น
LARGE(($B$4:$B$16=E4)*($C$4:$C$16),{1,2,3})
เมื่อนำ ($B$4:$B$16=E4) คูณ (*) กับ ($C$4:$C$16) จะได้ผลลัพธ์เป็น
{TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}*{180;30;130;170;120;50;80;140;60;80;40;100;90}
เมื่อคูณกันแล้ว ผลลัพธ์ที่ได้คือ
{180;0;0;0;120;50;0;140;0;80;40;0;90}
ต้องการผลลัพธ์เป็นผลรวมของ Top3 ก็ใส่ LARGE และ SUM ครอบ หรือกลายเป็น
=SUM(LARGE(($B$4:$B$16=E4)*($C$4:$C$16),{1,2,3}))
ดูเผินๆเหมือนจะโอเค
ปัญหาคือ พอพิมพ์สูตรแล้วกด Enter เฉยๆไม่ได้
เพราะ array ของ LARGE ไม่ได้รองรับช่วงข้อมูลแบบมีการคำนวณ (โครงสร้างฟังก์ชันคือ LARGE(array, k)) จึงต้องเปลี่ยนจากสูตรธรรมดาให้กลายเป็นสูตรอาร์เรย์ (Array Formula) หรือต้องกด Ctrl+Shift+Enter แทนการกด Enter
พอกดแล้วจะได้เป็น
{=SUM(LARGE(($B$4:$B$16=E4)*($C$4:$C$16),{1,2,3}))}
(วงเล็บปีกกาข้างใน LARGE เกิดจากการพิมพ์ตรงๆ แต่วงเล็บปีกกาข้างนอกเกิดจากการกด Ctrl+Shift+Enter)
ปัญหาคือ อาจลืมกด Ctrl+Shift+Enter หรืออาจเผลอกด Enter อย่างเดียว
เพื่อป้องกันปัญหานี้ ให้ใช้ฟังก์ชัน SUMPRODUCT แทน SUM ได้เลย
หรือเขียนสูตรเป็น
=SUMPRODUCT(LARGE(($B$4:$B$16=E4)*($C$4:$C$16),{1,2,3}))
(ไม่ต้องกด Ctrl+Shft+Enter, กด Enter อย่างเดียว)
ผ่าง !!
เจ๋งไหมล่ะ ^^
“แล้วถ้าอยากปรับเป็นผลรวมของ Top N ตามต้องการล่ะ เช่น Top5, Top 10?”
ถ้าต้องการปรับเป็น Top 5 ก็เพียงแก้จาก {1,2,3} เป็น {1,2,3,4,5}
เช่น
=SUMPRODUCT(LARGE(($B$4:$B$16=E4)*($C$4:$C$16),{1,2,3,4,5}))
หรือถ้าต้องการปรับเป็น Top 10 ก็เพียงแก้จาก {1,2,3} เป็น {1,2,3,4,5,6,7,8,9,10}
“แล้วถ้าวันนี้อยากดูแค่ Top 5 พรุ่งนี้อยากดู Top 10 ล่ะ มีวิธีปรับให้เป็นอัตโนมัติโดยไม่ต้องแก้สูตรไหม?”
(เรื่องมากจริงเฟ้ย !)
(อุ๊บ…. ขอโทษที คิดดังไปหน่อย ^^)
ทำได้ครับ (กลับมาสุภาพแล้ว ^^) เพียงแค่ปรับวิธีการเขียน {1,2,3} ครับ
ถ้าต้องการผลลัพธ์เป็น {1,2,3,4,5,6,7,8,9,10} ให้ปรับสูตรเป็น
ROW(INDIRECT(“1:10”))
(อ่านรายละเอียดการเขียนสูตรลักษณะนี้จาก บทความนี้ ครับ)
ถ้าต้องการ Top3 ก็ไม่ต้องระบุเลข 3 ตรงๆในสูตร แต่ให้เก็บตัวเลขนั้น (3) ในเซลล์ใดเซลล์หนึ่ง เช่น F4
แทนค่า F4 ลงไปในสูตร ROW(INDIRECT(“1:10”)) หรือได้เป็น
ROW(INDIRECT(“1:”&F4))
นำ ROW(INDIRECT(“1:”&F4)) แทนที่ {1,2,3} ในสูตร
จาก
=SUMPRODUCT(LARGE(($B$4:$B$16=E4)*($C$4:$C$16),{1,2,3}))
กลายเป็น
=SUMPRODUCT(LARGE(($B$4:$B$16=E4)*($C$4:$C$16),ROW(INDIRECT(“1:”&F4))))
ผ่าง ผ่าง !!
เยส เยส !!
เป็นไงล่ะ ^^
สูตรนี้ใช้ได้จริงๆด้วย !
แต่.. เมื่อกี้ลองแก้ข้อมูลจากตัวเลขให้เป็น Text สูตรมันเจ๊งเลยอ่ะ ทำไงดี?
ก็อย่าไปแก้สิเฟ้ย !
(อุ๊บ.. เผลอคิดดังอีกแล้ว ^^)
เหตุผลที่ได้ผลลัพธ์เป็น #VALUE! เพราะ array ของ LARGE (โครงสร้างฟังก์ชันคือ LARGE(array, k)) รองรับข้อมูลที่เป็นตัวเลขเท่านั้น ไม่รองรับข้อมูลที่เป็นตัวหนังสือ (Text) ครับ
(กลับมาสุภาพแล้วนะ ^^)
ถ้าเป็นแบบนี้ ไม่สามารถเขียน LARGE เป็น
LARGE(($B$4:$B$16=E4)*($C$4:$C$16),ROW(INDIRECT(“1:”&F4))) ได้อีกต่อไป
ต้องเปลี่ยน array ใน LARGE หรือ ($B$4:$B$16=E4)*($C$4:$C$16) ให้เป็นตัวเลข (หรือ TRUE, FALSE)
ทำยังไงน่ะหรือครับ?
ใช้ IF ช่วยครับ
หรือก็คือเปลี่ยนจาก
LARGE(($B$4:$B$16=E4)*($C$4:$C$16),ROW(INDIRECT(“1:”&F4)))
เป็น
LARGE(IF($B$4:$B$16=E4,$C$4:$C$16),ROW(INDIRECT(“1:”&F4)))
การใช้ IF ในรูปแบบนี้ ถ้าค่าใดที่ไม่ใช่ Top N หรือค่าใดเป็นตัวหนังสือ (Text) หรืือแม้กระทั่งเป็น error จะถูกเปลี่ยนเป็น FALSE ทั้งหมด
ผลลัพธ์จาก IF($B$4:$B$16=E4,$C$4:$C$16) คือ
{180;FALSE;FALSE;FALSE;120;50;FALSE;140;FALSE;80;40;FALSE;90}
นำ LARGE และ SUMPRODUCT มาครอบ จะได้เป็น
=SUMPRODUCT(LARGE(IF($B$4:$B$16=E4,$C$4:$C$16),ROW(INDIRECT(“1:”&F4))))
แต่สูตรนี้ไม่ถูกต้อง!
ทำไมน่ะหรือครับ?
เพราะ IF เคสนี้เป็นสูตรอาร์เรย์ (Array Formula) ถ้าไม่กด Ctrl+Shift+Enter สูตรจะคำนวณค่าแรกแค่ค่าเดียว
หรือจาก $B$4:$B$16=E4 สูตรจะคำนวณแค่ $B$4=E4 (ไม่คำนวณ $B$5:$B$16=E4) ผลลัพธ์ที่ได้จะผิดพลาด (ถ้าถูกก็เป็นการถูกแบบบังเอิญ)
เคสนี้จึงควรกด Ctrl+Shift+Enter ด้วย หรือทำให้สูตรกลายเป็น
{=SUMPRODUCT(LARGE(IF($B$4:$B$16=E4,$C$4:$C$16),ROW(INDIRECT(“1:”&F4))))}
อย่างไรก็ตาม ถ้าใช้ SUMPRODUCT เราจะไม่กด Ctrl+Shift+Enter เพราะ SUMPRODUCT เป็นฟังก์ชันที่รองรับสูตรอาร์เรย์อยู่แล้ว
แต่เคสนี้ IF ซ้อนอยู่ใน LARGE และซ้อนอยู่ใน SUMPRODUCT อีกที จึงจำเป็นต้องกด Ctrl+Shift+Enter
แต่การใช้ SUMPRODUCT ก็ไม่ควรกด Ctrl+Shft+Enter
งั้นทำยังไงดี?
ทางแก้เคสนี้คือควรใช้ SUM แทน SUMPRODUCT และกด Ctrl+Shift+Enter ครับ
หรือสูตรก็คือ
{=SUM(LARGE(IF($B$4:$B$16=E4,$C$4:$C$16),ROW(INDIRECT(“1:”&F4))))}
(วงเล็บปีกกาเกิดจากการกด Ctrl+Shift+Enter)
ผ่าง ผ่าง ผ่าง !!!
เยส เยส เยส !!!
สูตรนี้น่าจะครอบคลุมทุกเคสครับ ^__^
จริงๆแล้ว คำถามที่ผมได้รับจากเพื่อนคือคำถามแบบที่สอง (SUM Top 3 แบบมีเงื่อนไข) ส่วนคำถามแบบที่ 3 (Top N), 4 (Error เพราะ Text) เป็นคำถามที่ผม “จินตนาการ” ต่อเองครับ
เหตุผลที่ต้อง “จินตนาการ” ต่อ เพราะเคสเหล่านี้มีโอกาสเกิดขึ้นได้
การเขียนสูตรที่ดี ไม่ใช่เพียงหาคำตอบได้แค่ “วันนี้” แต่ต้องหาคำตอบของ “พรุ่งนี้” ได้ด้วย
ซึ่งเคสของ “พรุ่งนี้” กับ “วันนี้” อาจไม่เหมือนกันก็ได้
“พรุ่งนี้” มักมี “อะไร” นอกเหนือจากที่คิดไว้ครั้งแรกเสมอ
จึงควรคาดเดา “อะไร” ให้ครบทุกความเป็นไปได้ และการคาดเดาต้องใช้ “จินตนาการ”
เมื่อใช้ “จินตนาการ” คาดเดาได้แล้ว ก็ไม่ยากเกินจะเขียนสูตร
เพราะ Excel สามารถเขียนสูตรทุกอย่างที่ “จินตนาการ” ได้
ปัญหาจึงไม่ใช่ว่า Excel เขียนสูตรได้หรือไม่ ปัญหาคือจะตีความ “จินตนาการ” ให้เป็นสูตรได้อย่างไร
มีเพียงหนทางเดียวเท่านั้นที่จะตีความ “จินตนาการ” ให้เป็นสูตรได้
หนทางนั้นคือ ฝึกฝน และ นำไปใช้จริง
ไม่มีทางลัดใดๆทั้งสิ้น
เมื่อเขียนสูตรตามจินตนาการได้แล้ว จะเขียนสูตรกับเคสใด เคสนั้นจะซับซ้อนแค่ไหน ก็เขียนได้ตามใจปรารถนา
เพราะ “กระบี่อยู่ในใจ” แล้วนั่นเอง ….
ขอให้สนุกกับการ ฝึกฝน และ นำไปใช้จริง ทุกคนครับ ^__^
สำหรับใครที่สนใจตัวอย่างของบทความนี้ สามารถดาวน์โหลดไฟล์ตัวอย่างเพื่อศึกษาได้เลยครับ
SUMTopNValue_WithCondition_180128
ขอบคุณเทคนิคดีๆจาก
https://exceljet.net/formula/sum-top-n-values
https://exceljet.net/formula/sum-top-n-values-with-criteria
https://exceljet.net/formula/create-array-of-numbers
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
ยอดเยี่ยมครับ ผมติดตามตลอด เป็นกำลังใจให้ครับ
ขอบคุณครับ