Full Screen Home

Cumulative Sum

สูตรซึ่งทำหน้าที่บวกเลขใน Excel ซึ่งเรามักชอบใช้กันเป็นประจำ คงหนีไม่พ้นสูตร Sum หากไม่ใช้สูตรนี้ก็ต้องนำเซลล์ หรือตัวเลขมาบวกต่อๆกันด้วยเครื่องหมายบวก เช่น

=SUM(C3:G3) ให้ผลลัพธ์เท่ากับ
=C3+D3+E3+F3+G3

  C D E F G
3 10 20 2 10 6
4 10 30 32 42 48

ซึ่งคืนผลลัพธ์เป็นยอดรวมเท่ากับ 48

หากต้องการคำนวณยอดรวมสะสมเพิ่มทีละเซลล์ ต้องสร้างสูตร =SUM($C3:C3) แล้ว copy สูตรไปใส่ในเซลล์ด้านขวามือ จะได้ยอดรวมสะสมตามที่แสดงไว้ใน Row 4

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

เมื่อเปลืองเซลล์ จึงทำให้ file ขนาดใหญ่ขึ้น คำนวณช้าลง

ศึกษาข้อมูลเพิ่มเติมจาก Excel Expert Training http://xls.i.am

ถ้าต้องการสร้างสูตรคำนวณยอดรวมสะสม (Cumulative Sum) สูตรเดียวในเซลล์เดียว โดยใช้ตัวเลขตามตัวอย่างข้างต้นนี้ ให้ได้ตัวเลขรวมสะสมเป็นเลขลำดับใน array ขอให้ลองสร้างสูตรต่อไปนี้

=SUBTOTAL(9, INDIRECT( ADDRESS( ROW(C3), COLUMN(C3) ) & ":" & ADDRESS(  ROW(C3), ROW( INDIRECT( COLUMN(C3) & ":" & COLUMN(G3))) )))

สูตรนี้จะสร้างแบบ array หรือไม่ก็ได้ ทำให้เกิดเลขลำดับ {10;30;32;42;48} ซึ่งเราสามารถใช้ในการคำนวณอื่นๆต่อไป

สูตรนี้ดัดแปลงจากสูตรของ Aladin Akyurek
=SUBTOTAL(9, (INDIRECT("C3" & ":" & ADDRESS(ROW(C3), ROW(INDIRECT("1:" & COUNT(C3:G3)))+3)))) จึงขอขอบคุณ ณ ที่นี้ด้วย

รูปแบบสูตร

=SUBTOTAL(FunctionNum, TotalRange)

FunctionNum เป็นตัวเลข 1 - 11
หากต้องการทำหน้าที่แบบสูตร Sum ให้ใช้เลข 9

FunctionNum Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

ROW() COLUMN() คืนค่าเป็นเลขที่ของ Row / Column

ROWS(Range) COLUMNS(Range)
คืนค่าเป็นจำนวน Row / Column ใน range นั้น

ADDRESS(Row#,Column#) คืนค่าเป็นตำแหน่งของเซลล์

13/05/2544

http://xls.i.am