ทราบไหมครับว่า เทคนิคการบวกตัวเลขใน Excel มีไม่น้อยกว่า 10 วิธี!

คุณไม่ได้อ่านผิดหรอกครับ

10 วิธีจริงๆ !

(เท่าที่ผมทราบตอนนี้)

ผมเรียก 10 วิธีนั้นว่า

SUM Series

(อารมณ์คล้ายๆซีรีส์เกาหลี ^^)

มีวิธีอะไรบ้าง มาดูกันครับ…

1 บวกมือแบบโท่งๆ

หรือก็คือการเขียนสูตรแบบตรงๆ เช่น

=A2+B2

คงไม่ต้องอธิบายอะไรสำหรับวิธีนี้ เพราะเป็นวิธีที่ทราบกันดีทั่วราชอาณาจักร ^^

ข้อดี

• เขียนง่าย
• ใครแกะสูตรก็เข้าใจ

ข้อเสีย

• ไม่เหมาะกับการบวกตัวเลขหลายๆตัว เช่น

=A3+B3+C3+D3+E3+F3+G3+H3+I3+J3

แค่เห็นสูตรก็ตาลายแล้วใช่ไหมครับ ถ้าใครที่ใช้สูตรเก่งอาจๆเห็นแล้วหงุดหงิดด้วยซ้ำไป (อ้าว!)

• ไม่รองรับการบวกแบบมีเงื่อนไข

2. ใช้ฟังก์ชั่น SUM

สามารถใช้งานโดยการคลิกไอคอน AutoSum

หรือใช้คีย์บอร์ดชอร์ตคัต Alt+=

หรืออาจเขียนสูตรโดยตรงเลยก็ได้ เช่น

=SUM( A3:J3 )

ข้อดี

• ใช้งานง่าย
• ใช้กับข้อมูลที่มีลักษณะเป็นช่วง หรือมีหลายจำนวนได้ ลบข้อจำกัดของวิธีการบวกมือแบบโท่งๆ
• ใครแกะสูตรก็เข้าใจ

ข้อเสีย

• ไม่สามารถใช้กับการบวกแบบมีเงื่อนไขได้

.

3. ใช้ฟังก์ชั่น SUBTOTAL

ฟังก์ชั่น SUBTOTAL สามารถแสดงผลลัพธ์ได้หลายแบบ เช่น หาค่าเฉลี่ย หาค่าน้อยสุด หาค่ามากสุด

โครงสร้างของ SUBTOTAL คือ

=SUBTOTAL (function_num, ref1, [ref2], …)

เราสามารถใช้ SUBTOTAL หาผลรวมได้เช่นกัน นั่นคือใช้ในรูปแบบของ

=SUBTOTAL( 9,A4:A10000 )

หรือ

=SUBTOTAL( 109,A4:A10000 )

หรือพูดง่ายๆก็คือให้ function_num เป็น 9 หรือ 109 นั่นเอง

ต้องมี 9 หรือ 109 ด้วยเสมอนะครับ ไม่งั้นผลลัพธ์จะเป็นอย่างอื่น

ความแตกต่างระหว่าง 9 และ 109 คือ

9 ถ้ามีข้อมูลถูกซ่อนไว้ ข้อมูลที่ถูกซ่อนนั้นจะถูกรวมไปด้วย

109 ถ้ามีข้อมูลถูกซ่อนไว้ ข้อมูลที่ถูกซ่อนนั้นจะไม่ถูกรวมไปด้วย

แล้วถ้า function_num ไม่ใช่ 9 หรือ 109 ล่ะ ผลลัพธ์จะเป็นยังไง?

ถ้า function_num เป็นเลขอื่น ผลลัพธ์ที่ได้ของ SUBTOTAL เทียบเท่าผลลัพธ์ที่ได้จากฟังก์ชันอื่น 11 แบบ ดังนี้ครับ

ถ้าใช้ function_num เป็นเลข 1-11 ใช้แทนฟังก์ชัน AVERAGE, COUNT, …, VARP ได้เลย แต่ถ้ามีข้อมูลถูกซ่อน ข้อมูลที่ถูกซ่อนนั้นจะถูกนำไปคำนวณด้วย

แต่ถ้าใช้ function_num เป็นเลข 101-111 ใช้แทนฟังก์ชัน AVERAGE, COUNT, …, VARP ได้เลย ถ้ามีข้อมูลถูกซ่อน ข้อมูลที่ถูกซ่อนนั้นจะไม่ถูกนำไปคำนวณด้วย หรือพูดง่ายๆก็คือ คำนวณเฉพาะตัวเลขที่แสดงบนหน้าจอนั่นเอง

จริงๆแล้วผลลัพธ์ที่ได้จาก SUBTOTAL(9,…) และ SUBTOTAL(109, …) นี้คล้ายกับฟังก์ชั่น SUM เพียงแต่มีข้อดีกว่าในกรณีใช้ร่วมกับฟิลเตอร์

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

แต่ถ้าใช้ฟังก์ชั่น SUBTOTAL(9,…) หรือ SUBTOTAL(109,…)  ร่วมกับฟิลเตอร์ เมื่อฟิลเตอร์ข้อมูลอะไรบางอย่าง ผลลัพธ์ของฟังก์ชั่นนี้ จะแสดงเฉพาะผลรวมของตัวเลขที่ปรากฎบนหน้าจอเท่านั้น หรือก็คือตัวเลขที่ถูกฟิลเตอร์ไปจะไม่ถูกนำมารวมนั่นเอง!

ปกติแล้ว ผมใช้ฟังก์ชั่น SUBTOTAL(9,…) บนหัวตารางเสมอ และจะเขียนสูตรเผื่อข้อมูลอัพเดตไว้เลย

เช่น ตอนนี้ข้อมูลมีทั้งหมด 200 แถว ผมจะเขียนสูตรเผื่อไปเป็น 10,000 แถว ดังภาพ

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

เวลาใช้งานฟิลเตอร์ใดๆ ตัวเลขในสูตรก็จะเปลี่ยนตามไปด้วย สะดวกมาก

ใครไม่เคยลองใช้วิธีนี้ ผมแนะนำเลยครับ ^__^

ระวัง! ห้ามใส่สูตร SUM ไว้ที่ท้ายข้อมูลเด็ดขาด มิฉะนั้นตัวเลขในสูตร SUBTOTAL(9,..) จะถูกเบิ้ลเป็นสองเท่า

ข้อดี

• เขียนสูตรง่าย ใช้ง่าย
• เมื่อใช้ร่วมกับฟิลเตอร์ ผลลัพธ์ที่ได้คือผลรวมของข้อมูลที่ปรากฏบนหน้าจอเท่านั้น ข้อมูลที่ถูกฟิลเตอร์ออกไปจะไม่ถูกนำมารวมด้วย ซึ่งสะดวกมากสำหรับการใช้รวมตัวเลขบนหัวตาราง และต้องการความรวดเร็ว (ดังภาพ)
• ถ้ามีข้อมูลถูกซ่อน (แล้วใช้เป็นแบบ SUBTOTAL(109, …) ข้อมูลที่ถูกซ่อนจะไม่ถูกนำมารวมด้วย
• ถ้าชุดข้อมูลมีตัวเลขที่เกิดจาก SUBTOTAL เวลา SUBTOTAL ที่บรรทัดสุดท้าย ตัวเลขที่เกิดจาก SUBTOTAL นั้นจะไม่ถูกรวมที่บรรทัดสุดท้าย

ข้อเสีย

• เขียนสูตรยากกว่าฟังก์ชัน SUM เล็กน้อย
• ไม่รองรับการบวกแบบมีเงื่อนไข (เช่น ไม่รวมข้อมูลที่น้อยกว่า 200, รวมเฉพาะยอดขายของ Customer02)

.

4. ใช้ฟังก์ชั่น AGGREGATE

ฟังก์ชั่น AGGREGATE เพิ่งถูกใช้งานเวอร์ชัน 2010 นี่เอง (Excel 2003, Excel 2007 ใช้ไม่ได้)

ดูเผินๆ เหมือนเป็นฟังก์ชั่นที่ใช้ยาก เพราะมีโครงสร้าง 2 แบบ คือ

1 AGGREGATE(function_num, options, ref1, ref2,…)

2 AGGREGATE(function_num, options, array, k)

ที่ชื่อ AGGREGATE เพราะถูกออกแบบให้รวมการใช้งานแบบหลากหลายไว้ในฟังก์ชันเดียว ใช้งานแทนฟังก์ชันอื่นได้ถึง 19 ฟังก์ชั่น

(SUBTOTAL ยังแทนได้แค่ 11 ฟังก์ชั่น)

ใช้แทนได้ 19 ฟังก์ชั่นจริงๆ แถม “เจ๋ง” กว่าอีกต่างหาก !

เพราะฉะนั้นถึงได้ชื่อว่า AGGREGATE ไงครับ ^^

ฟังก์ชันที่ AGGREGATE สามารถใช้แทนได้มีอะไรบ้าง มาดูกัน

ดูแล้วงงนิดนึงใช่ไหมครับ

อธิบายได้ง่ายๆคือ จากโครงสร้าง

AGGREGATE(function_num, options,…..)

ถ้า function_num คือหมายเลข 1-13 (AVERAGE, COUNT,…, MODE.SNGL) ให้ใช้โครงสร้าง AGGREGATE(function_num, options, ref1, ref2,…)

แต่ถ้า function_num คือหมายเลข 14-19 (LARGE, SMALL,…, QUARTILE.EXC) ให้ใช้โครงสร้าง AGGREGATE(function_num, options, array, k)

options คืออะไร?

options คือสิ่งที่ทำให้ AGGREGATE “เจ๋ง” กว่าทั้ง 19 ฟังก์ชั่นนั้นครับ ^__^

เพราะ AGGREGATE มีออปชั่นให้เลือกได้ถึง 8 แบบ ในขณะที่ฟังก์ชันอื่นแทบไม่มีออปชงออปชั่นอะไรให้เลือกเลย

ออปชั่นทั้ง 8 แบบมีอะไรบ้าง มาดูกันครับ

ผมขออธิบายด้วย AGGREGATE ที่ใช้แทนฟังก์ชั่น SUM (function_num = 9) ละกันนะครับ จะได้เกี่ยวข้องกับบทความนี้ ^_^

(ไม่งั้นจะกลายเป็นบทความเรื่องฟังก์ชั่น AGGREGATE ^^)

ความ “เจ๋ง” ที่ผมชอบ AGGREGATE มากกว่าฟังก์ชัน SUM หรือ SUBTOTAL ก็คือ

ถ้าข้อมูลมีความผิดพลาด (error) ก็ยังสามารถรวมข้อมูลชุดนั้นได้

แต่ถ้าเป็น SUM หรือ SUBTOTAL จะขึ้น #N/A

ขอยกตัวอย่างง่ายๆแบบนี้ครับ

ถ้าเป็นเคสปกติ (ไม่มีซ่อนข้อมูล ข้อมูลไม่มี error) ผลลัพธ์จาก SUM, SUBTOTAL, AGGREGATE จะได้เท่ากัน

แต่ถ้ามีข้อมูลใดถูกซ่อนล่ะ?

เช่น จากข้อมูลเดิม ถ้าข้อมูล Product05 ถูกซ่อน

ค่าของ SUM ยังได้เท่าเดิม (เหมือนตอนข้อมูลไม่ถูกซ่อน ผลรวม = 55)

ถ้าใช้ SUBTOTAL แบบ SUBTOTAL(109,…) ค่าที่ถูกซ่อนจะไม่ถูกนำมารวม ผลรวม = 50

ถ้าใช้ AGGREGATE แบบ (AGGREGATE(9, 5, …) ค่าที่ถูกซ่อนจะไม่ถูกนำมารวม ผลรวม = 50 เหมือน SUBTOTAL

(9 คือ function_num หมายถึงใช้แทนฟังก์ชัน SUM)

(5 คือ option หมายถึง ไม่รวมค่าที่ถูกซ่อน (Ignore hidden rows))

(ถ้าเปลี่ยน option จาก 5 เป็น 1,3,7 ก็ได้คำตอบเหมือนกัน)

เริ่มเห็นความเจ๋งของ AGGREGATE แล้วใช่ไหมครับ ^__^

แต่ที่ AGGREGATE เจ๋งกว่า SUBTOTAL คือ ถ้าข้อมูลมีความผิดพลาด (error) ก็ยังสามารถรวมข้อมูลได้ ในขณะที่ SUM กับ SUBTOTAL เจ๊งไปแล้ว

ถ้าข้อมูลมี error ผลลัพธ์ของ SUM จะเป็น #N/A

ถ้าข้อมูลมี error ผลลัพธ์ของ SUBTOTAL(109,…) (และ SUBTOTAL(9, …) จะเป็น #N/A เหมือน SUM

แต่ถ้าใช้ AGGREGATE แบบ (AGGREGATE(9, 6, …) ค่า error จะไม่ถูกนำมารวม ผลรวม = 50 !

(9 คือ function_num หมายถึงใช้แทนฟังก์ชัน SUM)

(6 คือ option หมายถึง ไม่รวมค่าที่ error (Ignore error values))

(ถ้าเปลี่ยน option จาก 6 เป็น 2,3,7 ก็ได้คำตอบเหมือนกัน)

บอกแล้วว่า AGGREGATE เค้าเจ๋งจริง ^^

ข้อดี

• ถ้าข้อมูลถูกซ๋อน (แล้วใช้ option 1, 3, 5, 7) ข้อมูลที่ถูกซ่อนจะไม่ถูกนำมารวมด้วย (SUM ทำไม่ได้)
• ถ้าข้อมูลมีความผิดพลาด (error, แล้วใช้ option 2, 3, 6, 7) ข้อมูลนั้นจะไม่ถูกนำมารวมด้วย (SUM และ SUBTOTAL ทำไม่ได้)
• ถ้าใช้ fucntion_num แบบ 14-19 สามารถรองรับโครงสร้างข้อมูลที่เป็นอาร์เรย์ (Array) โดยไม่ต้องกด Ctrl+Shft+Enter (มีเพียง 4 ฟังก์ชั่นที่มีความสามารถนี้ ได้แก่ SUMPRODUCT, AGGREGATE (เฉพาะ function_num 14-19), INDEX, LOOKUP)

ข้อเสีย

• ต้องเป็นเวอร์ชัน 2010 ขึ้นไป (Excel 2010, Excel 2013, Excel 2016) หรือพูดอีกแง่ก็คือ เวอร์ชันต่ำกว่า 2010 (Excel 2003, Excel 2007) ใช้ไม่ได้
• โครงสร้างฟังก์ชันอาจดูซับซ้อนไปนิด เพราะมี 2 แบบ และต้องจำว่า ถ้าเป็น function_num 1-13 ให้ใช้แบบ AGGREGATE(function_num, options, ref1, ref2,…) แต่ถ้าเป็น function_num 14-10 ให้ใช้แบบ AGGREGATE(function_num, options, array, k)
• ไม่รองรับการบวกแบบมีเงื่อนไข (เช่น ไม่รวมข้อมูลที่น้อยกว่า 200, รวมเฉพาะยอดขายของ Customer02)

ใครสนใจไฟล์ตัวอย่างของ AGGREGATE ที่แสดงในภาพด้านบน ดาวน์โหลดได้จากลิงค์นี้เลยครับ ^__^

AGGREGATE_9_170722

อ้างอิงข้อมูลจาก

Credit: http://www.exceluser.com/excel_help/functions/function-aggregate.htm

Credit: https://www.youtube.com/watch?v=x9J2N31KTpE

.

5. ใช้ฟังก์ชั่น SUMIF

โครงสร้างของสูตรคือ (ขออธิบายโดยแทนค่าลงไปในสูตรเพื่อง่ายต่อการทำความเข้าใจนะครับ)

=SUM( ชื่อสินค้าทั้งหมด, ชื่อสินค้าที่ต้องการ, ยอดขายสินค้าทั้งหมด )

หรืออาจเขียนสูตรได้เป็น

=SUMIF(A2:A100,”iPAD3″,B2:B100)

ผลลัพธ์ที่ได้ก็คือยอดขาย iPAD3 จากข้อมูลการขายสินค้าทั้งหมดนั่นเอง

ในทางปฏิบัติ ควรเขียนสูตรเป็น

=SUMIF($A$2:$A$100,$J5,B$2:B$100)

ในที่นี้ J5 = iPAD3

เพราะข้อมูลที่เป็นข้อมูลอ้างอิง (เช่น ชื่อสินค้าทั้งหมด) มักไม่เปลี่ยนแปลง สิ่งที่เปลี่ยนแปลงมักเป็นเงื่อนไข (เช่น iPAD3) และตัวเลขที่ต้องการสรุป (เช่น ยอดขายสินค้าทั้งหมด) เราจึงไม่ควรล็อคทั้งแถวและคอลัมน์ เพื่อสะดวกต่อการก็อปปี้สูตรไปทางขวา หรือลงด้านล่างนั่นเอง

ข้อดี

• สามารถใช้สรุปข้อมูลแบบมีเงื่อนไขได้

ข้อเสีย

• ใช้สรุปข้อมูลได้เพียงเงื่อนไขเดียว
• ในกรณีลิงค์ข้อมูลข้ามไฟล์ และต้องเปิดทั้งสองไฟล์พร้อมกัน (ไฟล์ที่เก็บข้อมูล และไฟล์ที่คำนวณค่าสรุป) หากปิดไฟล์ที่เก็บข้อมูล ข้อมูลในไฟล์สรุปจะแสดงความผิดพลาด (#VALUE!)

.

6. ใช้ฟังก์ชั่น SUMIFS

สังเกตดีๆ มีตัว “S” ต่อท้ายด้วยนะครับ

ฟังก์ชันนี้เกิดในเวอร์ชั่น 2007 ถูกออกแบบมาเพื่อกลบข้อจำกัดของ SUMIF ที่สามารถสรุปข้อมูลได้เพียงเงื่อนไขเดียว

ด้วยเจ้าฟังก์ชั่น SUMIFS นี้ ต่อให้มี 5 เงื่อนไขก็ไม่หวั่น

โครงสร้างของสูตรคือ (ขออธิบายโดยการแทนค่าแบบ 3 เงื่อนไขนะครับ)

=SUMIFS( ยอดขายสินค้าทั้งหมด,
ชื่อสินค้าทั้งหมด,ชื่อสินค้าที่ต้องการ,
จังหวัดที่ขายสินค้า,จังหวัดที่ต้องการ,
รายชื่อห้างที่ขายสินค้า,ชื่อห้างที่ต้องการ)

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

=SUMIFS(E2:E100,
A2:A100,”iPAD3″,
B2:B100,”Bangkok”,
C2:C100,”Tesco”)

ในทางปฏิบัติควรเขียนสูตรเป็น

=SUMIFS(E$2:E$100,
$A$2:$A$100,$J5,
$B$2:$B$100,$K5,
$C$2:$C$100,$L5)

โดยในที่นี้

J5 = iPad3

K5 = Bangkok

L5 = Tesco

เพื่อสะดวกต่อการก็อปปี้สูตรทางด้านขวา หรือทางด้านล่าง นั่นเอง

ข้อดี

• สามารถสรุปข้อมูลได้หลายเงื่อนไข ลบข้อจำกัดของ SUMIF
• ในทางปฏิบัติสามารถนำไปใช้แทน SUMIF ได้อย่างสมบูรณ์ หรืออาจเลิกใช้ SUMIF ไปเลยก็ได้ (แต่ควรรู้โครงสร้างของ SUMIF เอาไว้ เผื่อต้องแกะสูตรของคนอื่น)
• คำนวณเร็วกว่าสูตร SUM Array

ข้อเสีย

• ในกรณีลิงค์ข้อมูลข้ามไฟล์ และต้องเปิดทั้งสองไฟล์พร้อมกัน (ไฟล์ที่เก็บข้อมูล และไฟล์ที่คำนวณค่าสรุป) หากปิดไฟล์ที่เก็บข้อมูล ข้อมูลในไฟล์สรุปจะแสดงความผิดพลาด (#VALUE!)
• เงื่อนไขทั้งหมดต้องเชื่อมกันด้วยความสัมพันธ์แบบ “และ” (AND) เท่านั้น ไม่สามารถใช้กับเงื่อนไขแบบ “หรือ” (OR) ได้ เช่น

ชื่อสินค้าที่ต้องการ   iPAD3

จังหวัดที่ต้องการ      Bangkok

ชื่อห้างที่ต้องการ      Tesco หรือ Central

กรณีเช่นนี้ มีห้างที่ต้องการ 2 ห้าง ไม่สามารถใช้ฟังก์ชั่น SUMIFS แบบตรงๆได้ แต่อาจประยุกต์โดยการใช้ฟังก์ชั่น SUMIFS 2 ตัว โดยตัวแรกให้ใส่เงื่อนไขว่าเป็น Tesco และใส่เงื่อนไขว่าเป็น Central ในตัวที่สอง หรืออาจเขียนได้เป็น

=SUMIFS(…..,”Tesco”)+SUMIFS(…….,”Central”)

อย่างไรก็ตาม วิธีการนี้ทำให้สูตรยาวและสร้างความงงงวยให้กับคนแกะสูตร ควรใช้วิธีอื่นดีกว่า

.

7. ใช้ฟังก์ชั่น SUMPRODUCT

ใช้ฟังก์ชั่นนี้บวกตัวเลขได้ด้วยเหรอ?

ได้ครับ แถมใช้ได้ถึง 2 วิธี

วิธีแรก

=SUMPRODUCT( (ชื่อสินค้าทั้งหมด=ชื่อสินค้าที่ต้องการ)
*(จังหวัดที่ขายสินค้า=จังหวัดที่ต้องการ)
*(รายชื่อห้างที่ขายสินค้า=ชื่อห้างที่ต้องการ)
*ยอดขายสินค้าทั้งหมด )

ไม่ต้องกด Ctrl+Shift+Enter เหมือนสูตร SUM Array นะครับ เพราะสูตรนี้มีโครงสร้างเหมือนอาร์เรย์อยู่แล้ว

ในทางปฏิบัติ อาจเขียนได้เป็น

=SUMPRODUCT( ($A$2:$A$100=”iPAD3″)
*($B$2:$B$100=”Bangkok”)
*($C$2:$C$100=”Tesco”)
*$E$2:$E$100 )

เพื่อสะดวกต่อการก็อปปี้สูตรไปทางด้านขวา หรือด้านล่าง

ดูเผินๆแล้วสูตรนี้คล้ายกับสูตร SUM Array มาก เพียงแค่เปลี่ยนชื่อฟังก์ชั่น จาก SUM เป็น SUMPRODUCT เท่านั้น

แถมไม่ต้องกด Ctrl+Shift+Enter ให้เปลืองหัวใจ

วิธีที่ 2 คือ

=SUMPRODUCT( – -(ชื่อสินค้าทั้งหมด=ชื่อสินค้าที่ต้องการ),
– -(จังหวัดที่ขายสินค้า=จังหวัดที่ต้องการ),
– -(รายชื่อห้างที่ขายสินค้า=ชื่อห้างที่ต้องการ),
ยอดขายสินค้าทั้งหมด )

การเขียนสูตรแบบนี้คำนวณเร็วกว่าแบบแรกเสียอีก!

การใส่เครื่องหมายลบสองครั้ง (- -) มีจุดประสงค์เพื่อเปลี่ยนสิ่งที่อยู่ในวงเล็บด้านหลัง (เช่น (ชื่อสินค้าทั้งหมด=ชื่อสินค้าที่ต้องการ) ) ให้กลายเป็น 1 หรือ 0

เพราะค่าที่ได้จากวงเล็บด้านหลังมี 2 อย่างคือ TRUE หรือ FALSE

การใส่เครื่องหมายลบสองครั้ง ลบคูณลบก็กลายเป็นบวก เสมือนว่าได้ค่าเดิม

เพียงแต่เปลี่ยน TRUE ให้เป็น 1 และเปลี่ยน FALSE ให้เป็น 0

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

• +0
• *1 (ผลลัพธ์ที่ได้จะช้ากว่า – – หรือ +0 เพราะเสมือนว่านำ 1 ไปคูณกับค่าทั้งหมด)

เพียงแต่การใช้ – – พิมพ์ได้เร็วกว่า จึงเป็นที่นิยม

แถมดูเท่ด้วยนะ ^^

การใช้ SUMPRODUCT ทั้ง 2 แบบนี้สามารถลบข้อจำกัดของ SUMIF และ SUMIFS ที่ว่าต้องเปิดสองไฟล์พร้อมกันกรณีลิงค์ข้ามไฟล์

แถมยังกลบข้อจำกัดของ SUMIFS ที่ว่าต้องเชื่อมเงื่อนไขในลักษณะ “และ” เท่านั้นด้วยได้

โดยอาจเขียนเป็น

=SUMPRODUCT( ($A$2:$A$100=”iPAD3″)
*($B$2:$B$100=”Bangkok”)
*(($C$2:$C$100=”Tesco”) + ($C$2:$C$100=”Central”))
*$E$2:$E$100 )

หรือ

=SUMPRODUCT( – -($A$2:$A$100=”iPAD3″),
– -($B$2:$B$100=”Bangkok”),
– –(($C$2:$C$100=”Tesco”) + ($C$2:$C$100=”Central”)),
$E$2:$E$100 )

เรียกว่า ใช้แทน Sum Array เลยก็ว่าได้

ข้อดี

• คำนวณเร็วกว่า Sum Array
• ลบข้อจำกัดของ SUMIFS ที่ต้องเชื่อมเงื่อนไขทั้งหมดแบบ “และ” (AND) เท่านั้น
• ลบข้อจำกัดกรณีลิงค์ข้ามไฟล์ ที่ต้องเปิดไฟล์เก็บข้อมูล และไฟล์สรุปพร้อมกัน ถ้าใช้ฟังก์ชั่น SUMPRODUCT เสร็จแล้ว สามารถปิดไฟล์ข้อมูลไปได้เลย ไฟล์สรุปจะไม่เกิดค่าความผิดพลาดใดๆ

ข้อเสีย

• เขียนสูตรยาก (สำหรับคนที่ไม่คุ้นเคย)
• คนส่วนใหญ่ไม่รู้จักวิธีนี้ อาจคิดว่าเราเขียนสูตรผิด (อีกแล้ว) T_T

8. ใช้สูตร SUM Array

วิธีนี้สามารถลบข้อจำกัดของ SUMIFS ที่ว่าเงื่อนไขทุกตัวต้องมีความสัมพันธ์แบบ “และ” เท่านั้น โดยจะใส่เงื่อนไขให้มีความซับซ้อนแค่ไหนก็ได้

โครงสร้างของสูตรคือ

=SUM((ชื่อสินค้าทั้งหมด=ชื่อสินค้าที่ต้องการ)
*(จังหวัดที่ขายสินค้า=จังหวัดที่ต้องการ)
*(รายชื่อห้างที่ขายสินค้า=ชื่อห้างที่ต้องการ)
*ยอดขายสินค้าทั้งหมด)

จากนั้นให้กด Ctrl+Shift+Enter พร้อมกัน เพื่อเปลี่ยนสูตรนี้ให้เป็นสูตรอาร์เรย์ หลังกดปุ่ม จะมีเครื่องหมายปีกกาคร่อมหน้าและหลังสูตรโดยอัตโนมัติ

{=SUM((ชื่อสินค้าทั้งหมด=ชื่อสินค้าที่ต้องการ)*(จังหวัดที่ขายสินค้า=จังหวัดที่ต้องการ)*(รายชื่อห้างที่ขายสินค้า=ชื่อห้างที่ต้องการ)*ยอดขายสินค้าทั้งหมด)}

!ห้ามพิมพ์เครื่องหมายปีกกาเองนะครับ เครื่องหมายปีกกาต้องเกิดจากการกด Ctrl+Shift+Enter เท่านั้น

ถ้าไม่มีเครื่องหมายปีกกาขึ้น แสดงว่ากดสูตรไม่ติด ^^ ลองกดใหม่อีกครั้งนะครับ

ในทางปฏิบัติควรเขียนสูตรเป็น

{=SUM(($A$2:$A$100=”iPAD3″)*($B$2:$B$100=”Bangkok”)*($C$2:$C$100=”Tesco”)*$E$2:$E$100)}

หรือ

{=SUM(($A$2:$A$100=$J5)*($B$2:$B$100=$K5)*($C$2:$C$100=$L5)*$E$2:$E$100)}

เพื่อสะดวกต่อการก็อปปี้สูตรไปทางด้านขวา หรือด้านล่าง นั่นเอง

หากต้องการใช้สูตรนี้เพื่อลบข้อจำกัดของ SUMIFS ก็สามารถทำได้ โดยเขียนสูตรเป็น

{=SUM(($A$2:$A$100=”iPAD3″)*($B$2:$B$100=”Bangkok”)*(($C$2:$C$100=”Tesco”)+($C$2:$C$100=”Central”))*$E$2:$E$100)}

ข้อดี

• ลบข้อจำกัดของ SUMIFS ที่ต้องเชื่อมเงื่อนไขทั้งหมดแบบ “และ” (AND) เท่านั้น
• ลบข้อจำกัดกรณีลิงค์ข้ามไฟล์ ที่ต้องเปิดไฟล์เก็บข้อมูล และไฟล์สรุปพร้อมกัน ถ้าใช้สูตร SUM Array เสร็จแล้ว สามารถปิดไฟล์ข้อมูลได้เลย ไฟล์สรุปจะไม่เกิดค่าความผิดพลาดใดๆ

ข้อเสีย

• เขียนสูตรยาก (สำหรับคนที่ไม่คุ้นเคย)
• คำนวณช้า ถ้าข้อมูลมีจำนวนมาก อาจใช้เวลาคำนวณเป็นนาที
• คนส่วนใหญ่ไม่รู้จักสูตรนี้ คนที่มาแกะสูตรเราอาจคิดว่าเราเขียนสูตรผิด (ซะงั้น!) T_T

.

9. ใช้ SUM ใน Pivot Table

หากข้อมูลถูกจัดเรียงเป็นพืด (Tabular) ในลักษณะที่เหมาะที่จะเป็นชุดข้อมูลของ pivot table เราอาจใช้ pivot table เพื่อบวกหรือสรุปข้อมูลไปเลยก็ได้

แน่นอนว่าประโยชน์ของ pivot table นั้นมีมากมายมหาศาล แถมยังใช้งานง่ายอีกด้วย

ข้อดี

• สามารถปรับเปลี่ยนเงื่อนไขง่ายเพียงแค่ปลายคลิก เลือกจากดร็อปดาวน์ลิสต์ได้เลย
• ใช้ประโยชน์จากฟีเจอร์อื่นๆของ pivot table

ข้อเสีย

• ข้อมูลต้องถูกจัดเรียงอยู่ในรูปแบบที่พร้อมใช้งาน หรือก็คือในลักษณะข้อมูลเต็มพืด (Tabular)
• ไม่สามารถใช้กับข้อมูลที่มีเงื่อนไขซับซ้อนได้ เช่น คอลัมน์เดียวกันแต่ใช้สูตรต่างกัน
  • อาจแก้ปัญหาโดยใข้ SUM Array หรือ SUMPRODUCT แทน
• ไม่สามารถปรับแต่งฟอร์แมตตามใจปรารถนาได้ เช่น เว้นคอลัมน์ เว้นสองบรรทัด
• หากต้องการสรุปข้อมูลเพียงไม่กี่เซลล์ ควรเขียนสูตรด้วยวิธีการอื่น เพราะใช้เวลาน้อยกว่าและสะดวกกว่า
• คนใช้งานจะติดใจ จนขอให้เราทำแบบนี้กับงานอื่นๆด้วย ซะงั้น!

.

10. ใช้ฟังก์ชั่น DSUM

ฟังก์ชั่น DSUM คือฟังก์ชั่นในตระกูลดาต้าเบส เหมาะกับข้อมูลที่ถูกจัดเรียงในลักษณะข้อมูลเต็มพืด (Tabular) หรือก็คือลักษณะเดียวกับข้อมูลที่เตรียมใช้ใน Pivot Table นั่นเอง

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

=DSUM(ข้อมูลทั้งหมดรวมถึงหัวคอลัมน์,
ชื่อคอลัมน์ที่ต้องการรวมตัวเลข,
เงื่อนไขที่ต้องการพร้อมชื่อหัวคอลัมน์ของข้อมูลนั้น)

ฟังก์ชั่นนี้อาจดูงงๆนิดนึงนะรับ เพื่อให้เห็นภาพมากขึ้น ขอยกตัวอย่างข้อมูลดังภาพ

จากภาพด้านบน นี่คือข้อมูลที่เตรียมใช้เป็น ข้อมูลทั้งหมดรวมถึงหัวคอลัมน์ ของสูตร DSUM หรือก็คือ

A3:J10000

สังเกตว่าต้องรวมแถว 3 ซึ่งเป็นแถวของหัวคอลัมน์เข้าไปในสูตรด้วยนะครับ

สำหรับ “เงื่อนไขที่ต้องการพร้อมชื่อหัวคอลัมน์ของข้อมูลนั้น” เราต้องกำหนดเงื่อนไข พร้อมใส่ชื่อของหัวคอลัมน์ลงไปในเงื่อนไขด้วย เช่น กำหนดในรูปแบบ

ในที่นี้ เงื่อนไขที่ต้องการพร้อมชื่อหัวคอลัมน์ของข้อมูลนั้น ถูกแสดงในเซลล์ L3:N5

ต้องรวมแถว 3 เข้าไปในชุดเงื่อนไขด้วยนะครับ

สมมติว่าเราต้องการสรุปข้อมูล Volume ของเงื่อนไขด้านบน เราจะเขียนสูตรว่า

=DSUM(A3:J10000,”Volume”,L3:N5)

ผลลัพธ์ที่ได้คือ Volume ของ Customer01 ทั้งหมด (ทุกสินค้า, ทุกประเทศ) รวมกับ Volume ของ Customer02 เฉพาะสินค้า 1002 และขายที่ประเทศ Japan เท่านั้น

หรือถ้าเราต้องการสรุปข้อมูล Sales ของเงื่อนไขเดียวกัน เราจะเขียนสูตรว่า

=DSUM(A3:J10000,”Sales”,L3:N5)

สิ่งที่ทำให้ฟังก์ชั่นนี้อาจดูงงๆ มี 2 เหตุผลด้วยกันคือ

A: ต้องรวมหัวคอลัมน์เข้าไปในอาร์กิวเมนต์ (องค์ประกอบของสูตร) “ข้อมูลทั้งหมดรวมถึงหัวคอลัมน์” ด้วย

B: ต้องใส่ชื่อหัวคอลัมน์ของข้อมูลลงไปในเงื่อนไขที่ต้องการด้วย และต้องใส่ในแถวบนสุดของชุดเงื่อนไข

อาจดูงงๆนะนิดนึงนะครับ เพราะโครงสร้างสูตรตระกูลดาต้าเบสค่อนข้างแตกต่างจากสูตรอื่นๆที่เราคุ้นเคย

แต่ถ้าใช้เป็นก็มีประโยชน์ไม่น้อย เพราะสามารถกำหนดเงื่อนไขได้หลากหลายมาก

ข้อดี

• กำหนดเงื่อนไขได้หลากหลาย
• เห็นเงื่อนไขทั้งหมดที่ใช้ในสูตรอย่างชัดเจน ปรับเปลี่ยนเงื่อนไขได้ง่าย
• เหมาะกับกรณีที่ต้องการเชื่อมเงื่อนไขแบบ “หรือ” (OR) มาก ซึ่งเป็นข้อจำกัดของ SUMIF และ SUMIFS
• เขียนสูตรไม่ซับซ้อน

ข้อเสีย

• ไวยากรณ์ของฟังก์ชั่นอาจทำให้งง
• ฟอร์แมตส่วนที่เป็นเงื่อนไข (พร้อมชื่อหัวตาราง) ต้องมีหัวตารางและส่วนของเงื่อนไขอยู่ติดกันเสมอ ในทางปฏิบัติ อาจไม่สะดวกต่อการจัดหาที่วางในตารางคำนวณ เพราะไม่สามารถก็อปปี้สูตรลงมาด้านล่างได้
• คนส่วนใหญ่ไม่ค่อยรู้จักฟังก์ชั่นนี้ ถ้ามีคนแกะสูตรที่เราเขียนไว้ อาจงงได้ (ความผิดเราไหมเนี่ย?)

สำหรับคนที่สนใจฟังก์ชั่น DSUM สามารถดาวน์โหลดโจทย์ตัวอย่าง โดยคลิกที่ไฟล์ด้านล่างครับ

DSUM_Example_151030

.

แล้วเราควรใช้ SUM Series ให้เป็นทั้งหมดกี่วิธี?

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

หากการสรุปนั้นไม่ต้องลิงค์ข้อมูลข้ามไฟล์ การใช้ SUMIFS ก็ดูง่าย และสะดวกดี แต่อาจมีข้อเสียที่เงื่อนไขทั้งหมดต้องเชื่อมกันแบบ “และ” เท่านั้น

โดยส่วนตัวแล้ว ผมมองว่าการสรุปตัวเลขโดยใช้สูตร SUMPRODUCT นั้นมีประโยชน์มากๆ สามารถกลบข้อจำกัดของ SUMIF, SUMIFS และ SUM Array ได้ทั้งหมด เพียงแต่อาจเขียนสูตรยาก และคนส่วนใหญ่ไม่คุ้นเคย

อย่างไรก็ตาม หากต้องการสรุปข้อมูลเป็นรีพอร์ต และต้องอัพเดตรีพอร์ตนี้เรื่อยๆ การใช้ pivot table น่าจะเป็นคำตอบที่ดีที่สุด

ขอให้สนุกกับ SUM Series ทั้ง 10 แบบนะครับ ^__^

.

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

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

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