คำนวณผลรวมการทำงาน (ผลรวมสะสม) ใน Excel - 5 วิธีง่ายๆ

ยอดรวม (เรียกอีกอย่างว่าผลรวมสะสม) นิยมใช้กันมากในหลายกรณีนี่คือตัวชี้วัดที่บอกคุณว่าผลรวมของค่าจนถึงตอนนี้คืออะไร

ตัวอย่างเช่น หากคุณมีข้อมูลยอดขายรายเดือน ยอดรวมจะบอกคุณว่ามียอดขายตั้งแต่วันแรกของเดือนจนถึงวันที่ระบุ

มีสถานการณ์อื่นๆ ที่มักใช้ยอดรวม เช่น การคำนวณยอดเงินสดในใบแจ้งยอดธนาคาร/บัญชีแยกประเภท การคำนวณแคลอรีในแผนมื้ออาหาร เป็นต้น

ใน Microsoft Excel มีหลายวิธีในการคำนวณผลรวมที่ทำงานอยู่

วิธีการที่คุณเลือกจะขึ้นอยู่กับโครงสร้างของข้อมูลด้วย

ตัวอย่างเช่น หากคุณมีข้อมูลแบบตารางอย่างง่าย คุณสามารถใช้สูตร SUM แบบง่ายได้ แต่ถ้าคุณมีตาราง Excel จะดีกว่าถ้าใช้การอ้างอิงแบบมีโครงสร้างคุณยังสามารถใช้ Power Query เพื่อทำสิ่งนี้ได้

ในบทช่วยสอนนี้ ฉันจะพูดถึงมันทั้งหมดคำนวณผลรวมใน Excelวิธีการต่างๆ

มาเริ่มกันเลย!

คำนวณยอดรวมที่ใช้ข้อมูลแบบตาราง

ถ้าคุณมีข้อมูลแบบตาราง (นั่นคือ ตารางใน Excel ที่ไม่ได้แปลงเป็นตาราง Excel) คุณสามารถใช้สูตรง่ายๆ ในการคำนวณผลรวมที่ทำงานอยู่ได้

ใช้ตัวดำเนินการเพิ่มเติม

สมมติว่าคุณมีข้อมูลการขายตามวันที่และต้องการ在 ในคอลัมน์ Cคำนวณรวมวิ่ง.

นี่คือขั้นตอนในการทำเช่นนี้

ขั้นตอนที่ 1 – ในเซลล์ C2 เซลล์แรกที่คุณต้องการเรียกใช้ผลรวม ให้ป้อน

= B2

นี่เพิ่งได้รับมูลค่าการขายเท่ากันในเซลล์ B2

ขั้นตอนที่ 2– ในเซลล์ C3 ให้ป้อนสูตรต่อไปนี้:

= C2 + B3

ขั้นตอนที่ 3– ใช้สูตรกับทั้งคอลัมน์คุณสามารถใช้จุดจับเติมเพื่อเลือกและลาก หรือเพียงแค่คัดลอกและวางเซลล์ C3 ลงในเซลล์ที่เหลือทั้งหมด (การดำเนินการนี้จะปรับการอ้างอิงโดยอัตโนมัติและให้ผลลัพธ์ที่ถูกต้อง)

สิ่งนี้จะให้ผลลัพธ์ที่แสดงด้านล่าง

นี่เป็นวิธีง่ายๆ ที่ได้ผลดีในกรณีส่วนใหญ่

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

มีข้อเสียเพียงอย่างเดียว - หากคุณลบแถวที่มีอยู่ในชุดข้อมูลนี้ เซลล์ทั้งหมดด้านล่างจะส่งคืนข้อผิดพลาดในการอ้างอิง (#REF!)

หากชุดข้อมูลของคุณมีความเป็นไปได้นี้ ให้ใช้วิธีการถัดไปโดยใช้สูตร SUM

ใช้ SUM กับการอ้างอิงเซลล์ที่ถูกล็อกบางส่วน

สมมติว่าคุณมีข้อมูลการขายตามวันที่ และคุณต้องการคำนวณผลรวมที่ทำงานอยู่ในคอลัมน์ C

ด้านล่างนี้คือสูตร SUM ที่จะให้ผลรวมการทำงานแก่คุณ

= SUM ($ B $ 2: B2)

ให้ฉันอธิบายว่าสูตรนี้ทำงานอย่างไร

ในสูตร SUM ด้านบน ฉันใช้การอ้างอิงเพื่อเพิ่มเป็น $B$2:B2

• $B$2 - นี่คือการอ้างอิงแบบสัมบูรณ์ ซึ่งหมายความว่าการอ้างอิงนี้จะไม่เปลี่ยนแปลงเมื่อฉันคัดลอกสูตรเดียวกันในเซลล์ด้านล่างดังนั้น เมื่อคุณคัดลอกสูตรในเซลล์ด้านล่าง สูตรจะเปลี่ยนเป็น SUM($B$2:B3)
• B2 - นี่คือส่วนที่สองของการอ้างอิง เป็นการอ้างอิงแบบสัมพัทธ์ ซึ่งหมายความว่าเมื่อฉันคัดลอกสูตรลงหรือไปทางขวา สิ่งนี้จะปรับดังนั้นเมื่อคัดลอกสูตรในเซลล์ด้านล่าง ค่าจะกลายเป็น B3

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

คำนวณยอดรวมในแผ่นงาน Excel

เมื่อทำงานกับข้อมูลแบบตารางใน Excel วิธีที่ดีที่สุดคือแปลงเป็นตาราง Excelทำให้จัดการข้อมูลได้ง่ายขึ้น และยังทำให้ง่ายต่อการใช้เครื่องมือ เช่น Power Query และ Power Pivot

การใช้ตาราง Excel มีประโยชน์หลายประการ เช่น ข้อมูลอ้างอิงแบบมีโครงสร้าง (ซึ่งทำให้ง่ายต่อการอ้างอิงข้อมูลในตารางและใช้ในสูตร) ​​และการปรับข้อมูลอ้างอิงโดยอัตโนมัติเมื่อคุณเพิ่มหรือลบข้อมูลออกจากตาราง

ในขณะที่คุณยังคงสามารถใช้สูตรข้างต้นที่ฉันแสดงให้คุณเห็นในแผ่นงาน Excel ให้ฉันแสดงวิธีการที่ดีกว่านี้ให้คุณดู

สมมติว่าคุณมีตาราง Excel ดังตัวอย่างด้านล่าง และคุณต้องการคำนวณผลรวมที่ทำงานอยู่ในคอลัมน์ C

นี่คือสูตรในการทำสิ่งนี้:

=SUM(ข้อมูลการขาย[[#Headers],[Sale]]:[@Sale])

สูตรข้างต้นอาจดูยาวหน่อย แต่คุณไม่จำเป็นต้องเขียนเองสิ่งที่คุณเห็นในสูตรบวกเรียกว่าการอ้างอิงแบบมีโครงสร้าง และเป็นวิธีที่มีประสิทธิภาพสำหรับ Excel ในการอ้างถึงจุดข้อมูลเฉพาะในตาราง Excel

ตัวอย่างเช่น SalesData[[#Headers],[Sale]] หมายถึงส่วนหัวของ Sales ในตาราง SalesData (SalesData คือชื่อของตาราง Excel ที่ฉันให้ไว้เมื่อสร้างตาราง)

โดยที่ [@Sale] หมายถึงค่าในเซลล์ในแถวเดียวกันของคอลัมน์ Sale

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

นี่คือขั้นตอนในการทำเช่นนี้:

1. ในเซลล์ C2 ให้ป้อน =SUM(
2. เลือกเซลล์ B1 ซึ่งเป็นส่วนหัวของคอลัมน์ที่มีมูลค่าการขายคุณสามารถใช้เมาส์หรือใช้ปุ่มลูกศรคุณจะสังเกตเห็นว่า Excel จะป้อนการอ้างอิงที่มีโครงสร้างไปยังเซลล์โดยอัตโนมัติ
3. เพิ่ม: (โคลอน)
4. เลือกเซลล์ B2Excel จะแทรกการอ้างอิงที่มีโครงสร้างไปยังเซลล์อีกครั้งโดยอัตโนมัติ
5. ปิดวงเล็บแล้วกด Enter

นอกจากนี้ คุณจะสังเกตเห็นว่าคุณไม่จำเป็นต้องคัดลอกสูตรในคอลัมน์ทั้งคอลัมน์ แผ่นงาน Excel จะคัดลอกสูตรให้คุณโดยอัตโนมัติ

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

แม้ว่าเราจะรวมส่วนหัวของคอลัมน์ไว้ในสูตรแล้ว แต่โปรดทราบว่าสูตรจะละเว้นข้อความส่วนหัวและพิจารณาเฉพาะข้อมูลในคอลัมน์เท่านั้น

คำนวณผลรวมการทำงานด้วย Power Query

Power Query เป็นเครื่องมือที่ยอดเยี่ยมเมื่อพูดถึงการเชื่อมต่อกับฐานข้อมูล การดึงข้อมูลจากหลายแหล่ง และการแปลงก่อนที่จะใส่ลงใน Excel

ถ้าคุณใช้ Power Query อยู่แล้ว การเพิ่มผลรวมที่ทำงานอยู่เมื่อแปลงข้อมูลในตัวแก้ไข Power Query จะมีประสิทธิภาพมากกว่า (แทนที่จะรับข้อมูลใน Excel ก่อนแล้วจึงเพิ่มผลรวมที่ทำงานอยู่โดยใช้วิธีการใดๆ ด้านบน)

ในขณะที่ไม่มีฟังก์ชันในตัวใน Power Query เพื่อเพิ่มยอดรวม (ฉันหวังว่าจะมี) คุณยังสามารถทำได้โดยใช้สูตรง่ายๆ

สมมติว่าคุณมีตาราง Excel ที่มีลักษณะดังนี้ และคุณต้องการเพิ่มผลรวมที่รันไปยังข้อมูลนี้:

นี่คือขั้นตอนในการทำเช่นนี้:

1. เลือกเซลล์ใดก็ได้ในตาราง Excel
2. คลิกข้อมูล
3. ในแท็บ รับและแปลง ให้คลิกไอคอน จากตาราง/ช่วงซึ่งจะเปิดตารางในตัวแก้ไข Power Query
4. [ไม่บังคับ] หากยังไม่ได้จัดเรียงคอลัมน์วันที่ ให้คลิกไอคอนตัวกรองในคอลัมน์วันที่ จากนั้นคลิกจัดเรียงจากน้อยไปมาก
5. คลิกแท็บเพิ่มคอลัมน์ในตัวแก้ไข Power Query
6. ในกลุ่ม ทั่วไป ให้คลิกเมนูดรอปดาวน์ คอลัมน์ดัชนี (อย่าคลิกไอคอนคอลัมน์ดัชนี แต่ให้ลูกศรเอียงสีดำเล็กๆ ข้างๆ เพื่อแสดงตัวเลือกเพิ่มเติม)
7. คลิกตัวเลือก "จาก 1"การทำเช่นนี้จะเพิ่มคอลัมน์ดัชนีใหม่ที่จะเริ่มที่ 1 และป้อนตัวเลขที่เพิ่มขึ้นทีละ 1 ตลอดทั้งคอลัมน์
8. คลิกไอคอนคอลัมน์ที่กำหนดเอง (รวมถึงในแท็บเพิ่มคอลัมน์ด้วย)
9. ในกล่องโต้ตอบคอลัมน์ที่กำหนดเองที่เปิดขึ้น ให้ป้อนชื่อสำหรับคอลัมน์ใหม่ในตัวอย่างนี้ ฉันจะใช้ชื่อ "Running Total"
10. ในฟิลด์สูตรคอลัมน์แบบกำหนดเอง ให้ป้อนสูตรต่อไปนี้:List.Sum(List.Range(#"เพิ่มดัชนี"[Sale],0,[Index]))
11. ตรวจสอบให้แน่ใจว่ามีช่องทำเครื่องหมายที่ด้านล่างของกล่องโต้ตอบที่ระบุว่า "ไม่พบข้อผิดพลาดทางไวยากรณ์"
12. คลิกตกลงสิ่งนี้จะเพิ่มคอลัมน์ผลรวมการทำงานใหม่
13. วางคอลัมน์ดัชนี
14. คลิกแท็บ ไฟล์ จากนั้นคลิก ปิดและโหลด

ขั้นตอนข้างต้นจะแทรกแผ่นงานใหม่ในสมุดงานของคุณด้วยตารางที่มีผลรวมทั้งหมด

ตอนนี้ ถ้าคุณคิดว่าขั้นตอนเหล่านี้มากเกินไปเมื่อเทียบกับวิธีการก่อนหน้าด้วยสูตรง่ายๆ คุณคิดถูก

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

การใช้ Power Query นั้นสมเหตุสมผลในสถานการณ์ที่คุณต้องดึงข้อมูลจากฐานข้อมูลหรือรวมข้อมูลจากเวิร์กบุ๊กต่างๆ หลายๆ อัน และยังเพิ่มยอดรวมที่กำลังทำงานอยู่ในกระบวนการ

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

มันทำงานอย่างไร?

ตอนนี้ให้ฉันอธิบายอย่างรวดเร็วว่าเกิดอะไรขึ้นกับแนวทางนี้

สิ่งแรกที่เราทำใน Power Query Editor คือการแทรกคอลัมน์ดัชนีที่เริ่มต้นที่ 1 และเพิ่มขึ้นตามเซลล์

เราทำสิ่งนี้เพราะเราต้องใช้คอลัมน์นี้ในการคำนวณยอดรวมในคอลัมน์อื่นที่แทรกในขั้นตอนต่อไป

จากนั้นเราแทรกคอลัมน์ที่กำหนดเองและใช้สูตรต่อไปนี้

List.Sum(List.Range(#"เพิ่มดัชนี"[Sale],0,[Index]))

นี่คือสูตร List.Sum ที่จะให้ผลรวมของช่วงที่ระบุในนั้น

ช่วงถูกระบุโดยใช้ฟังก์ชัน List.Range

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

ดังนั้นสำหรับเซลล์แรกList.Sum จะให้ผลรวมของมูลค่าการขายครั้งแรกเท่านั้น สำหรับเซลล์ที่สองจะให้ผลรวมของมูลค่าการขายสองค่าแรก และอื่นๆ

แม้ว่าวิธีนี้จะได้ผลดี แต่ก็อาจช้ามากสำหรับชุดข้อมูลขนาดใหญ่ (หลายพันแถว)

คำนวณยอดรวมตามเกณฑ์

จนถึงตอนนี้ เราได้เห็นตัวอย่างการคำนวณผลรวมของค่าทั้งหมดในคอลัมน์แล้ว

แต่ในบางกรณี คุณอาจต้องการคำนวณผลรวมรันสำหรับเรกคอร์ดเฉพาะ

ตัวอย่างเช่น ด้านล่าง ฉันมีชุดข้อมูลที่ฉันต้องการนับจำนวนเครื่องพิมพ์และสแกนเนอร์ที่ทำงานอยู่ในสองคอลัมน์ที่ต่างกัน

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

นี่คือสูตรสำหรับคอลัมน์เครื่องพิมพ์:

=SUMIF($C$2:C2,$D$1,$B$2:B2)

ในทำนองเดียวกัน ในการคำนวณผลรวมการทำงานของสแกนเนอร์ ให้ใช้สูตรต่อไปนี้:

=SUMIF($C$2:C2,$E$1,$B$2:B2)

ในสูตรข้างต้น ฉันได้ใช้ SUMIF ซึ่งจะให้ผลรวมในช่วงเมื่อตรงตามเงื่อนไขที่ระบุ

สูตรนี้ใช้พารามิเตอร์สามตัว:

1. พิสัย: นี่คือช่วงของเงื่อนไขที่จะตรวจสอบกับเงื่อนไขที่ระบุ
2. เกณฑ์: นี่คือเกณฑ์ที่จะตรวจสอบได้ก็ต่อเมื่อตรงตามเกณฑ์นี้ แล้วค่าในพารามิเตอร์ที่ XNUMX จะเพิ่มช่วงผลรวม
3. [ผลรวม_ช่วง]: นี่คือช่วงของผลรวมของค่าที่จะบวกเมื่อตรงตามเงื่อนไข

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

ในสูตรนี้ ฉันใช้คอลัมน์ส่วนหัว (เครื่องพิมพ์และสแกนเนอร์) เป็นเกณฑ์คุณยังสามารถฮาร์ดโค้ดมาตรฐานได้หากส่วนหัวของคอลัมน์ไม่เหมือนกับข้อความมาตรฐานทุกประการ

หากคุณต้องการตรวจสอบหลายเงื่อนไข คุณสามารถใช้สูตร SUMIFS ได้

สรุปผลรวมในตารางเดือย

ถ้าคุณต้องการเพิ่มผลรวมในผลลัพธ์ PivotTable ของคุณ คุณสามารถทำได้โดยใช้ฟังก์ชันที่มีอยู่ภายในใน PivotTable

สมมติว่าคุณมีตารางเดือยเหมือนด้านล่างที่มีวันที่ในคอลัมน์หนึ่งและมูลค่าการขายในอีกคอลัมน์หนึ่ง

ต่อไปนี้เป็นขั้นตอนในการเพิ่มคอลัมน์เพิ่มเติมที่จะแสดงยอดขายสะสมตามวันที่:

1. ลากช่อง Sale แล้ววางลงในพื้นที่ Value
2. นี่จะเพิ่มอีกหนึ่งคอลัมน์ที่มีมูลค่าการขาย
3. คลิกตัวเลือก Total Sales 2 ในพื้นที่ Value
4. คลิกที่ตัวเลือกการตั้งค่าฟิลด์ค่า
5. ในไดอะล็อกการตั้งค่าฟิลด์ค่า เปลี่ยนชื่อแบบกำหนดเองเป็นรัน Total
6. คลิกแท็บ "แสดงมูลค่าเป็น"
7. ในเมนูแบบเลื่อนลง แสดงค่าเป็น เลือกตัวเลือก "รันรวมใน"
8. ในตัวเลือกฟิลด์พื้นฐาน ตรวจสอบให้แน่ใจว่าได้เลือกวันที่แล้ว
9. คลิกตกลง

ขั้นตอนข้างต้นจะเปลี่ยนคอลัมน์ยอดขายที่สองเป็นคอลัมน์ "ยอดรวม"

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

ฉันยังกล่าวถึงวิธีการคำนวณยอดรวมที่ใช้ Power Query และ PivotTables

ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์