“อยากเขียนสูตรให้ตัวเลขบวกกันต่อเนื่องจากค่าแรกจนถึงค่าสุดท้าย เขียนยังไงดี?” เพื่อนคนหนึ่งถาม

“ยังไงนะ?” ผมถามกลับด้วยความฉงน

“สมมติว่า ค่าแรกคือ 5 ค่าสุดท้ายคือ 10 อยากเขียนสูตรให้ตัวเลขบวกกันตั้งแต่ 5 จนถึง 10” เพื่อนพยายามอธิบาย

“ถ้าเขียนแบบตรงๆคือ 5+6+7+8+9+10 แต่ไม่อยากพิมพ์แบบแมนวล” เพื่อนอธิบายต่อ

“Excel เขียนสูตรแบบนี้ได้ไหม?” เพื่อนถามสรุป

ในชีวิตจริงเราไม่ค่อยเจอโจทย์แนวนี้ แต่คำถามนี้สะกิด “ต่อมอยากรู้” ของผมอย่างจัง

จากฟังก์ชันที่พอรู้จัก คิดว่าไม่น่ามีฟังก์ชันใดที่คำนวณค่าแบบนี้ได้ตรงๆ

เพื่อความชัวร์ ผมก็เลยลองค้นฟังก์ชันที่เกี่ยวกับการ SUM ทั้งหมด

พบว่าไม่มีฟังก์ชันที่ตอบปัญหานี้

แปลว่า ถ้าต้องการผลลัพธ์แบบนี้ ต้องเขียนสูตรขึ้นมาเอง

แล้วจะเขียนสูตรยังไงล่ะ?

ปัญหามีไว้ทาย เอ๊ย! มีไว้แก้ ^^

ผมมั่นใจว่า Excel ต้องเขียนสูตรได้แน่นอน ลองมาแก้ปัญหาทีละเปลาะกันครับ ^__^

เริ่มจากพิมพ์ค่าเริ่มต้น (5) ไว้ที่เซลล์ B3

พิมพ์ค่าสุดท้าย (10) ไว้ที่เซลล์ B4

ถ้าคิดแบบง่ายๆ สูตรน่าจะเป็น

=SUM(B3:B4)

แต่ถ้าเขียนแบบนี้ จะได้คำตอบ = 15

สูตรต้องเป็นประมาณ

=SUM(5:10)

แต่พิมพ์ตรงๆแบบนั้นไม่ได้ ต้องอ้างอิงเซลล์ที่เก็บค่าเริ่มต้น กับค่าสุดท้ายไว้ด้วย จึงใช้ฟังก์ชัน INDIRECT มาช่วย

สูตรจะกลายเป็นประมาณนี้

=SUM(INDIRECT(B3&”:”&B4))

จากสูตร B3&”:”&B4 จะได้ผลลัพธ์เป็น 5:10 หรือกลายเป็น

=SUM(INDIRECT(5:10))

ผลลัพธ์ของ INDIRECT(5:10) ก็คือข้อมูลทั้งหมดที่อยู่ในแถว (Row) ที่ 5 ถึง 10 (5:10)

พอรวมกับ SUM ก็จะกลายเป็น

=SUM($5:$10)

ผลลัพธ์ที่ได้คือ ผลรวมของข้อมูลทุกเซลล์ที่มีใน Row 5 – 10 (ทุกคอลัมน์)

แปลว่า ถ้าพิมพ์ค่าอะไรลงไปในบรรทัดที่ 5 – 10 จะถูกนำไปคิดหมดเลย เขียนสูตรแบบนี้มีโอกาสผิดพลาดสูงปรี๊ด

แต่เรามาถูกทางแล้ว !

จริงเหรอ?

จริงครับ

ผลลัพธ์ของ INDIRECT(5:10) คือบรรทัดที่ 5 – 10

ต้องการให้ได้เป็น 5, 6, 7, 8, 9, 10 หรือได้ค่าเลขแถวของแต่ละบรรทัด ต้องใช้ฟังก์ชัน ROW

เวลาใช้ก็คือครอบ INDIRECT(5:10) ด้วยฟังก์ชัน ROW นั่นเอง

หรือเขียนเป็น

=ROW(INDIRECT(B3&”:”&B4))

เอ็กเซลจะมองเป็น

=ROW($5:$10)

หรือก็คือใช้ฟังก์ชัน ROW หาเลขแถวของบรรทัดที่ 5 – 10

ฟังก์ชัน ROW ต้องทำงานแบบนี้ 6 ครั้ง (5, 6, 7, 8, 9, 10) แต่การทำงานหนึ่งครั้งจะคำนวณเพียงค่าเดียว ถ้าต้องการให้คำนวณทุกค่าในทีเดียว ต้องปรับให้เป็นสูตรอาร์เรย์ (Array Formula) หรือก็คือการกด Ctrl+Shft+Enter (แทนการกด Enter)

พอกด Ctrl+Shft+Enter สูตรจะกลายเป็น

{=ROW(INDIRECT(B3&”:”&B4))}

(วงเล็บปีกกาไม่ได้เกิดจากการพิมพ์ แต่เกิดจากการกด Ctrl+Shft+Enter)

ได้ผลลัพธ์เป็น

{5; 6; 7; 8; 9; 10}

ผลลัพธ์นี้เป็นช่วงข้อมูล เราต้องการผลบวกของช่วงข้อมูลนี้ จึงครอบด้วยฟังก์ชัน SUM

หรือสูตรก็คือ

=SUM(ROW(INDIRECT(B3&”:”&B4)))

เนื่องจาก ROW(INDIRECT(B3&”:”&B4)) เป็นสูตรอาร์เรย์ แม้ครอบด้วย SUM สูตรนี้ก็ยังเป็นสูตรอาร์เรย์ เวลาใช้งานจึงต้องกด Ctrl+Shft+Enter

สูตรกลายเป็น

{=SUM(ROW(INDIRECT(B3&”:”&B4)))}

(วงเล็บปีกกาไม่ได้เกิดจากการพิมพ์ แต่เกิดจากการกด Ctrl+Shft+Enter)

ได้คำตอบแว๊ว !!

นี่คือสูตรการหาผลรวมของตัวเลขบวกกันต่อเนื่อง ภาษาอังกฤษใช้คำว่า sum of a sequence, sum of series

หรือถ้าเป็นภาษาทางคณิตศาสตร์คือ Arithmethic Series (Arithmethic Sequence)

ถ้าลองค้นคำว่า Arithmethic Series ใน Google จะพบว่าโจทย์ข้อนี้คือปัญหาทางคณิตศาสตร์แบบหนึ่ง และมีคนคิดสูตรออกตั้งนานแล้ว !

สูตรที่ว่าคือ

n x (a1 + a2) / 2

(อ้างอิงจาก https://www.varsitytutors.com/hotmath/hotmath_help/topics/sum-of-the-first-n-terms-of-a-series )

โดย

n = จำนวนตัวเลขทั้งหมด (เคสนี้คือ 6 (5, 6, 7, 8, 9, 10))

a1 = ค่าเริ่มต้น (เคสนี้คือ 5)

a2 = ค่าสุดท้าย (เคสนี้คือ 10)

เข้าสูตรได้เป็น

6*(5+10)/2

(จากเคสนี้ เขียนสูตรได้เป็น =(B4-B3+1)*(B3+B4)/2 )

= 45

เท่ากันเป๊ะ!!

แล้วจะคิดสูตรมาตั้งนาน เพื่อ !!

T_T (ร้องไห้หนักมาก) …

ใจเย็นๆครับ

การเขียนสูตรด้วยเอ็กเซล บางครั้งมีความยืดหยุ่นมากกว่าสูตรทางคณิตศาสตร์ครับ

ยังไงอ่ะ?

สมมติเคสมีความซับซ้อนมากขึ้น โจทย์ระบุว่าถ้าค่าเริ่มต้นมากกว่าค่าสิ้นสุด การคำนวณต้องคิดจากค่าเริ่มต้นที่แท้จริงจนถึงค่าสิ้นสุด และค่าเริ่มต้นที่แท้จริงก็คือเศษจากการหารค่าเริ่มต้นด้วยค่าสิ้นสุด

งง?

ผมขออธิบายด้วยตัวอย่างนะครับ สมมติค่าเริ่มต้นเคสนี้คือ 26 ค่าสิ้นสุดคือ 10

ค่าเริ่มต้น (26) มากกว่าค่าสิ้นสุด (10) ดังนั้นจึงต้องหาค่าเริ่มต้นที่แท้จริงก่อน

ค่าเริ่มต้นที่แท้จริง (เศษจากการหารค่าเริ่มต้นด้วยค่าสิ้นสุด) นำ 26 หารด้วย 10

เศษจากการหารคือ 6

การคำนวณผลรวมของเคสนี้จึงกลายเป็น ผลรวมของ 6 ถึง 10

หรือก็คือ

= 6 + 7 + 8 + 9 + 10

= 40

เคสนี้ถ้าใช้สูตรทางคณิตศาสตร์ (n x (a1 + a2) / 2) อาจต้องปรับพอสมควร

(ใช้ (B4-B3+1)*(B3+B4)/2 ตรงๆไม่ได้)

แต่ถ้าใช้โครงสูตรเอ็กเซลเดิม

{=SUM(ROW(INDIRECT(B3&”:”&B4)))}

ปรับนิดเดียวเท่านั้นเอง

แค่ปรับ B3 ให้เป็นได้ทั้งค่าเริ่มต้น และค่าเริ่มต้นที่แท้จริง ที่เหลือเหมืือนเดิม

มาดูกันครับ

เคสนี้ ค่าเริ่มต้น (B3) มากกว่าค่าสิ้นสุด (B4) ต้องหาค่าเร่ิ่มต้นที่แท้จริงก่อน

ค่าเริ่มต้นที่แท้จริงคือ เศษจากการหารค่าเริ่มต้นด้วยค่าสิ้นสุด ประโยคนี้เปลี่ยนเป็นสูตรเอ็กเซลได้ว่า

=MOD(B3,B4)

ถ้า B3 = 26, B4 =10 ผลลัพธ์ของ MOD(B3,B4) ก็คือ 6

B3 มีโอกาสเป็นได้ทั้งน้อยและมากกว่า B4 จึงนำฟังก์ชัน IF มาช่วยเป็น

IF(B3<B4,B3,MOD(B3,B4))

หรือก็คือเปลี่ยนจาก B3 เป็น IF(B3<B4,B3,MOD(B3,B4))

จากสูตรเดิม

{=SUM(ROW(INDIRECT(B3&”:”&B4)))}

กลายเป็น

{=SUM(ROW(INDIRECT(IF(B3<B4,B3,MOD(B3,B4))&”:”&B4)))}

ง่ายไหมครับ ^__^

นอกจากนี้ ยังประยุกต์การคำนวณจากการบวกตัวเลข กลายเป็นคูณตัวเลขได้อีกต่างหาก

ยังไงอ่ะ?

สมมติจากที่นำตัวเลขมาบวกกัน ต้องการนำมาคูณกันแทน

หรือก็คือจาก 6+7+8+9+10

ต้องการเป็น

=6*7*8*9*10

=30,240

ทำไงดีอ่ะ?

ง่ายมากครับ แค่เปลี่ยนจากการบวกเป็นการคูณ หรือก็คือเปลี่ยนฟังก์ชันจาก SUM เป็น PRODUCT เท่านั้นเอง ที่เหลือเหมือนเดิมเป๊ะ

(โครงสร้างของฟังก์ชัน PRODUCT เหมือน SUM ทุกประการ)

นั่นคือจาก

{=SUM(ROW(INDIRECT(IF(B3<B4,B3,MOD(B3,B4))&”:”&B4)))}

เปลี่ยนเป็น

{=PRODUCT(ROW(INDIRECT(IF(B3<B4,B3,MOD(B3,B4))&”:”&B4)))}

โป๊ะเช๊ะเลย !!

ง่ายใช่ไหมล่ะ ^__^

…

เมื่อ 10 ปีก่อน ผมเรียกสูตรแบบนี้ว่า

“สูตรระดับเทพ”

เพราะมันดูคูล สั้น แถมเท่อีกต่างหาก

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

ไม่ว่าสูตรจะยาวยุ่บยั่บ มี IF ซ้อนกันหลายชั้น มีฟังก์ชันนู่นนั่นนี่เต็มไปหมด ขอให้คำนวณได้ผลลัพธ์ถูกต้อง ก็ถือเป็นสูตรระดับเทพแล้ว

คำว่า คำนวณได้ผลลัพธ์ถูกต้อง แปลว่าต้องคาดเดาความผิดพลาดจากกรณีต่างๆ

ไม่ว่าจะเป็นกรณีอินพุตเป็นศูนย์ กรณีอินพุตติดลบ หรือกรณีลืมคีย์อินพุต สูตรต้องคำนึงถึงความผิดพลาดทุกกรณี

เพราะสิ่งสำคัญที่สุดของการเขียนสูตรไม่ใช่วิธีการ แต่คือเป้าหมาย

และเป้าหมายก็คือผลลัพธ์ที่ถูกต้อง

วิธีการจะเป็นยังไงก็ได้ สูตรจะดูคูล เท่ สั้นหรือไม่ ไม่สำคัญเลย

ขอให้ผลลัพธ์ถูกต้องก็เพียงพอแล้ว

จริงไหมครับ ^__^

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

SumOfSequence_ArithmetricSeries_180108

.

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

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