ยอดรวม (เรียกอีกอย่างว่าผลรวมสะสม) นิยมใช้กันมากในหลายกรณีนี่คือตัวชี้วัดที่บอกคุณว่าผลรวมของค่าจนถึงตอนนี้คืออะไร
ตัวอย่างเช่น หากคุณมีข้อมูลยอดขายรายเดือน ยอดรวมจะบอกคุณว่ามียอดขายตั้งแต่วันแรกของเดือนจนถึงวันที่ระบุ
มีสถานการณ์อื่นๆ ที่มักใช้ยอดรวม เช่น การคำนวณยอดเงินสดในใบแจ้งยอดธนาคาร/บัญชีแยกประเภท การคำนวณแคลอรีในแผนมื้ออาหาร เป็นต้น
ใน 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
ฉันเพิ่งจะอธิบายสิ่งนี้ที่นี่เพื่อความเข้าใจของคุณ แต่แม้ว่าคุณจะไม่รู้อะไรเกี่ยวกับการอ้างอิงที่มีแบบแผน คุณก็ยังสามารถสร้างสูตรนี้ได้อย่างง่ายดาย
นี่คือขั้นตอนในการทำเช่นนี้:
- ในเซลล์ C2 ให้ป้อน =SUM(
- เลือกเซลล์ B1 ซึ่งเป็นส่วนหัวของคอลัมน์ที่มีมูลค่าการขายคุณสามารถใช้เมาส์หรือใช้ปุ่มลูกศรคุณจะสังเกตเห็นว่า Excel จะป้อนการอ้างอิงที่มีโครงสร้างไปยังเซลล์โดยอัตโนมัติ
- เพิ่ม: (โคลอน)
- เลือกเซลล์ B2Excel จะแทรกการอ้างอิงที่มีโครงสร้างไปยังเซลล์อีกครั้งโดยอัตโนมัติ
- ปิดวงเล็บแล้วกด Enter
นอกจากนี้ คุณจะสังเกตเห็นว่าคุณไม่จำเป็นต้องคัดลอกสูตรในคอลัมน์ทั้งคอลัมน์ แผ่นงาน Excel จะคัดลอกสูตรให้คุณโดยอัตโนมัติ
ประโยชน์อีกประการของแนวทางนี้คือ ถ้าคุณเพิ่มระเบียนใหม่ในชุดข้อมูลนี้ แผ่นงาน Excel จะคำนวณผลรวมของระเบียนใหม่ทั้งหมดโดยอัตโนมัติ
แม้ว่าเราจะรวมส่วนหัวของคอลัมน์ไว้ในสูตรแล้ว แต่โปรดทราบว่าสูตรจะละเว้นข้อความส่วนหัวและพิจารณาเฉพาะข้อมูลในคอลัมน์เท่านั้น
คำนวณผลรวมการทำงานด้วย Power Query
Power Query เป็นเครื่องมือที่ยอดเยี่ยมเมื่อพูดถึงการเชื่อมต่อกับฐานข้อมูล การดึงข้อมูลจากหลายแหล่ง และการแปลงก่อนที่จะใส่ลงใน Excel
ถ้าคุณใช้ Power Query อยู่แล้ว การเพิ่มผลรวมที่ทำงานอยู่เมื่อแปลงข้อมูลในตัวแก้ไข Power Query จะมีประสิทธิภาพมากกว่า (แทนที่จะรับข้อมูลใน Excel ก่อนแล้วจึงเพิ่มผลรวมที่ทำงานอยู่โดยใช้วิธีการใดๆ ด้านบน)
ในขณะที่ไม่มีฟังก์ชันในตัวใน Power Query เพื่อเพิ่มยอดรวม (ฉันหวังว่าจะมี) คุณยังสามารถทำได้โดยใช้สูตรง่ายๆ
สมมติว่าคุณมีตาราง Excel ที่มีลักษณะดังนี้ และคุณต้องการเพิ่มผลรวมที่รันไปยังข้อมูลนี้:
นี่คือขั้นตอนในการทำเช่นนี้:
- เลือกเซลล์ใดก็ได้ในตาราง Excel
- คลิกข้อมูล
- ในแท็บ รับและแปลง ให้คลิกไอคอน จากตาราง/ช่วงซึ่งจะเปิดตารางในตัวแก้ไข Power Query
- [ไม่บังคับ] หากยังไม่ได้จัดเรียงคอลัมน์วันที่ ให้คลิกไอคอนตัวกรองในคอลัมน์วันที่ จากนั้นคลิกจัดเรียงจากน้อยไปมาก
- คลิกแท็บเพิ่มคอลัมน์ในตัวแก้ไข Power Query
- ในกลุ่ม ทั่วไป ให้คลิกเมนูดรอปดาวน์ คอลัมน์ดัชนี (อย่าคลิกไอคอนคอลัมน์ดัชนี แต่ให้ลูกศรเอียงสีดำเล็กๆ ข้างๆ เพื่อแสดงตัวเลือกเพิ่มเติม)
- คลิกตัวเลือก "จาก 1"การทำเช่นนี้จะเพิ่มคอลัมน์ดัชนีใหม่ที่จะเริ่มที่ 1 และป้อนตัวเลขที่เพิ่มขึ้นทีละ 1 ตลอดทั้งคอลัมน์
- คลิกไอคอนคอลัมน์ที่กำหนดเอง (รวมถึงในแท็บเพิ่มคอลัมน์ด้วย)
- ในกล่องโต้ตอบคอลัมน์ที่กำหนดเองที่เปิดขึ้น ให้ป้อนชื่อสำหรับคอลัมน์ใหม่ในตัวอย่างนี้ ฉันจะใช้ชื่อ "Running Total"
- ในฟิลด์สูตรคอลัมน์แบบกำหนดเอง ให้ป้อนสูตรต่อไปนี้:List.Sum(List.Range(#"เพิ่มดัชนี"[Sale],0,[Index]))
- ตรวจสอบให้แน่ใจว่ามีช่องทำเครื่องหมายที่ด้านล่างของกล่องโต้ตอบที่ระบุว่า "ไม่พบข้อผิดพลาดทางไวยากรณ์"
- คลิกตกลงสิ่งนี้จะเพิ่มคอลัมน์ผลรวมการทำงานใหม่
- วางคอลัมน์ดัชนี
- คลิกแท็บ ไฟล์ จากนั้นคลิก ปิดและโหลด
ขั้นตอนข้างต้นจะแทรกแผ่นงานใหม่ในสมุดงานของคุณด้วยตารางที่มีผลรวมทั้งหมด
ตอนนี้ ถ้าคุณคิดว่าขั้นตอนเหล่านี้มากเกินไปเมื่อเทียบกับวิธีการก่อนหน้าด้วยสูตรง่ายๆ คุณคิดถูก
หากคุณมีชุดข้อมูลอยู่แล้วและสิ่งที่คุณต้องทำคือเพิ่มผลรวมที่กำลังรันอยู่ ไม่ควรใช้ 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 ซึ่งจะให้ผลรวมในช่วงเมื่อตรงตามเงื่อนไขที่ระบุ
สูตรนี้ใช้พารามิเตอร์สามตัว:
- พิสัย: นี่คือช่วงของเงื่อนไขที่จะตรวจสอบกับเงื่อนไขที่ระบุ
- เกณฑ์: นี่คือเกณฑ์ที่จะตรวจสอบได้ก็ต่อเมื่อตรงตามเกณฑ์นี้ แล้วค่าในพารามิเตอร์ที่ XNUMX จะเพิ่มช่วงผลรวม
- [ผลรวม_ช่วง]: นี่คือช่วงของผลรวมของค่าที่จะบวกเมื่อตรงตามเงื่อนไข
นอกจากนี้ในพิสัยและผลรวม_ช่วงฉันได้ล็อกส่วนที่สองของข้อมูลอ้างอิงเพื่อที่เมื่อเราเลื่อนลงมาในเซลล์ ช่วงจะขยายเพิ่มขึ้นเรื่อยๆสิ่งนี้ทำให้เราสามารถพิจารณาและเพิ่มค่าได้จนถึงช่วงนั้นเท่านั้น
ในสูตรนี้ ฉันใช้คอลัมน์ส่วนหัว (เครื่องพิมพ์และสแกนเนอร์) เป็นเกณฑ์คุณยังสามารถฮาร์ดโค้ดมาตรฐานได้หากส่วนหัวของคอลัมน์ไม่เหมือนกับข้อความมาตรฐานทุกประการ
หากคุณต้องการตรวจสอบหลายเงื่อนไข คุณสามารถใช้สูตร SUMIFS ได้
สรุปผลรวมในตารางเดือย
ถ้าคุณต้องการเพิ่มผลรวมในผลลัพธ์ PivotTable ของคุณ คุณสามารถทำได้โดยใช้ฟังก์ชันที่มีอยู่ภายในใน PivotTable
สมมติว่าคุณมีตารางเดือยเหมือนด้านล่างที่มีวันที่ในคอลัมน์หนึ่งและมูลค่าการขายในอีกคอลัมน์หนึ่ง
ต่อไปนี้เป็นขั้นตอนในการเพิ่มคอลัมน์เพิ่มเติมที่จะแสดงยอดขายสะสมตามวันที่:
- ลากช่อง Sale แล้ววางลงในพื้นที่ Value
- นี่จะเพิ่มอีกหนึ่งคอลัมน์ที่มีมูลค่าการขาย
- คลิกตัวเลือก Total Sales 2 ในพื้นที่ Value
- คลิกที่ตัวเลือกการตั้งค่าฟิลด์ค่า
- ในไดอะล็อกการตั้งค่าฟิลด์ค่า เปลี่ยนชื่อแบบกำหนดเองเป็นรัน Total
- คลิกแท็บ "แสดงมูลค่าเป็น"
- ในเมนูแบบเลื่อนลง แสดงค่าเป็น เลือกตัวเลือก "รันรวมใน"
- ในตัวเลือกฟิลด์พื้นฐาน ตรวจสอบให้แน่ใจว่าได้เลือกวันที่แล้ว
- คลิกตกลง
ขั้นตอนข้างต้นจะเปลี่ยนคอลัมน์ยอดขายที่สองเป็นคอลัมน์ "ยอดรวม"
นี่คือวิธีการบางส่วนที่คุณสามารถใช้เพื่อคำนวณผลรวมที่ทำงานอยู่ใน Excelถ้าคุณมีข้อมูลแบบตาราง คุณสามารถใช้สูตรอย่างง่าย และถ้าคุณมีตาราง Excel คุณสามารถใช้สูตรที่ใช้การอ้างอิงแบบมีโครงสร้างได้
ฉันยังกล่าวถึงวิธีการคำนวณยอดรวมที่ใช้ Power Query และ PivotTables
ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์
