วิธีคำนวณผลรวมของคอลัมน์ใน excel วิธีคำนวณผลรวมของคอลัมน์ใน excel วิธีใส่ผลรวมย่อยใน excel

ฟังก์ชัน "ผลรวมย่อย ()"ใช้ในการคำนวณผลรวมย่อย (ผลรวม ค่าเฉลี่ย จำนวนค่า ฯลฯ) ในช่วงที่มีวันที่ซ่อนอยู่

ความไม่ชอบมาพากลของฟังก์ชันคือตั้งใจให้ใช้ร่วมกับเครื่องมือ Excel อื่นๆ (เช่น ตัวกรองอัตโนมัติ)

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

งาน: ตามพารามิเตอร์ที่เลือก ประเมินตัวบ่งชี้หลักโดยเปรียบเทียบกับสถานการณ์ในทุกพื้นที่

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

ในการทำเช่นนี้ คุณสามารถใช้ฟังก์ชัน Excel = ผลรวมย่อย () ฟังก์ชัน =Subtotal() ทำการคำนวณตามค่าที่กรอง

ในสูตร คุณสามารถตั้งค่าการคำนวณ:

• สื่อ - หมายเลข 1 หรือ 101 - สำหรับการสุ่มตัวอย่าง
• จำนวนค่า - หมายเลข 3 หรือ 103
• จำนวน - 9 หรือ 109
• เป็นต้น

• หากหมายเลขฟังก์ชันเป็นตัวเลขสามหลัก เช่น 109 ฟังก์ชันจะทำงานเป็นผลรวมย่อย เช่น คำนวณค่าตามพารามิเตอร์ที่กรอง
• หากตัวเลขเป็นตัวเลขหนึ่งหลักหรือสองหลัก 2 หรือ 11 สูตรผลรวมย่อย () จะทำงานเหมือนสูตรมาตรฐานสำหรับฟังก์ชันที่เลือก

ไปที่กิจกรรมของเรา:

เรามีการยกเลิกการโหลดจากฐานข้อมูลสำหรับคอลัมน์ต่อไปนี้:

• สถานการณ์
• การกระทำ
• จำนวนลูกค้า
• Volume_Sales_Before
• Volume_Sales_Plan
• Volume_Sales_Fact
• Costs_Plan
• ราคา_ข้อเท็จจริง

เพื่อประเมินสถานการณ์โดยรวม เราแนะนำสูตรผลรวมมาตรฐาน:

ในการคำนวณผลรวมย่อยสำหรับพารามิเตอร์ที่เลือก ให้ป้อน =SUBTOTALS(109;RC:RC) โดยที่

• 109 - ฟังก์ชันผลรวมสำหรับการคำนวณผลรวมย่อย
• RC:RC - อ้างอิงถึงช่วงผลรวม

ตอนนี้โดยการกรองคอลัมน์ "สถานการณ์" - "สถานการณ์ 4" และ "การดำเนินการ" - การดำเนินการ 2 และ 3 เราจะได้รับการคำนวณผลรวมย่อยใหม่สำหรับแต่ละตัวบ่งชี้ และเราสามารถเปรียบเทียบกับตัวบ่งชี้ "ทั้งหมด":

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

สูตร Excel =subtotal() เป็นเครื่องมือที่ยอดเยี่ยมสำหรับการคำนวณคะแนนตามพารามิเตอร์ที่กรอง

หากคุณมีคำถามใด ๆ - ติดต่อเรา!

เข้าร่วมกับเรา!

ดาวน์โหลดแอปพยากรณ์และข่าวกรองธุรกิจฟรี:

• โนโว ฟอร์คาสท์ ไลต์- อัตโนมัติ การคำนวณการคาดการณ์วี เก่ง.
• 4analytics- การวิเคราะห์ ABC-XYZและการวิเคราะห์การปล่อยมลพิษใน เอ็กเซล
• คิวลิคเซนส์เดสก์ทอป และ QlikViewPersonal Edition - ระบบ BI สำหรับการวิเคราะห์ข้อมูลและการสร้างภาพ

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

ตัวอย่างเช่น ลองสรุปผลรวมขั้นกลางและผลรวมทั่วไปของคอลัมน์ "หมายเลขคลังสินค้า" ในรายการ "รายการบัญชีสำหรับสินค้าในสต็อก"

ข้าว. 1.

หากต้องการแสดงผลรวมย่อย ให้ทำดังนี้

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

ข้าว. 2.

จากการดำเนินการคำสั่ง "Data" - "Totals" รายการจะปรากฏบนหน้าจอในรูปแบบของโครงสร้าง รูปภาพแสดงรายการที่มีการยุบรายการในกลุ่ม 2, 3 และ 4

ข้าว. 3.

ที่ด้านซ้ายของรายการมีปุ่มที่มีไอคอน "-" และ "+" ซึ่งระบุว่ารายการทั้งหมดในกลุ่มที่หนึ่งและห้าของรายการจะแสดงบนหน้าจอและในกลุ่ม 2, 3, 4 - รายการ กำลังพังทลายลง หากคุณคลิกที่ปุ่ม "-" รายการในกลุ่มจะยุบ และหากคุณคลิกที่ปุ่ม "+" รายการในกลุ่มจะขยายออก

หากต้องการจบคำสั่ง Totals ให้คลิกปุ่ม Clear All ในหน้าต่าง Subtotals

นี่คือบทจากหนังสือของ Bill Jelen

งาน: ฟังก์ชัน SUM จะรวมเซลล์ทั้งหมดในช่วง ไม่ว่าจะซ่อนไว้หรือไม่ก็ตาม คุณต้องการรวมเฉพาะแถวที่มองเห็นได้

วิธีแก้ไข: คุณสามารถใช้ฟังก์ชัน SUBTOTAL แทน SUM สูตรจะแตกต่างกันเล็กน้อยขึ้นอยู่กับว่าคุณซ่อนแถวอย่างไร หากคุณเลือกแถวแล้ว ให้คลิกขวา แล้วเลือกจากเมนูบริบท ซ่อนคุณสามารถใช้: =SUBTOTAL(109; พิสัย) (รูปที่ 1) การใช้ SUB-TOTALS เพื่อวัตถุประสงค์เหล่านี้ค่อนข้างผิดปกติ โดยปกติแล้ว ฟังก์ชันนี้จำเป็นต่อการทำให้ Excel ละเว้นผลรวมย่อยอื่นๆ ภายในช่วง

ดาวน์โหลดบันทึกในรูปแบบหรือตัวอย่างในรูปแบบ

SUB-TOTALS สามารถดำเนินการได้ 11 รายการ อาร์กิวเมนต์แรกของฟังก์ชันบอกให้ดำเนินการต่อไปนี้: (1) AVERAGE, (2) COUNT, (3) COUNT, (4) MAX, (5) MIN, (6) PRODUCT, (7) STDEV, ( 8) STDEV, ( 9) ผลรวม, (10) DISP, (11) DISP เมื่อเพิ่มหนึ่งร้อย การดำเนินการเดียวกันจะดำเนินการ แต่เฉพาะในเซลล์ที่มองเห็นเท่านั้น ตัวอย่างเช่น 104 จะพบค่าสูงสุดในเซลล์ที่มองเห็นได้ ภายใต้ มองเห็นได้ฉันหมายถึงไม่ปรากฏบนหน้าจอ (เช่น 120 บรรทัดจะไม่พอดีกับหน้าจอ) และไม่ถูกซ่อนโดยคำสั่ง ซ่อน.

เซลล์ E566 (ดูรูปที่ 1) ใช้สูตร =SUBTOTAL(109;E2:E564) Excel ส่งคืนผลรวมของเซลล์ที่มองเห็นได้ (ไม่ซ่อน) ในช่วง ได้แก่ - E2; E30; E72; E78; E564

ฟังก์ชัน SUBTOTAL ใช้กับชุดข้อมูลแนวตั้ง ไม่ได้มีไว้สำหรับชุดข้อมูลแนวนอน ดังนั้น เมื่อกำหนดผลรวมย่อยของชุดข้อมูลแนวนอนโดยใช้ค่าคงที่ function_numberตั้งแต่ 101 ขึ้นไป (เช่น SUBTOTALS (109; С2: F2) รูปที่ 2) การซ่อนคอลัมน์จะไม่ส่งผลต่อผลลัพธ์

ข้อมูลเพิ่มเติม: มีข้อยกเว้นที่ผิดปกติในลักษณะการทำงานของฟังก์ชัน SUBTOTAL เมื่อแถวถูกซ่อนโดยคำสั่งตัวกรองใดๆ (ตัวกรองขั้นสูง ตัวกรองอัตโนมัติ หรือตัวกรอง) Excel จะรวมเฉพาะแถวที่มองเห็น แม้ใน SUBTOTAL(9; พิสัย). ไม่จำเป็นต้องใช้เวอร์ชัน 109 (รูปที่ 3) ที่นี่ใช้ตัวกรองเพื่อค้นหาระเบียน เชฟรอน.

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

1. เลือกเซลล์ใดก็ได้ในชุดข้อมูลของคุณ
2. ผ่านเมนู ข้อมูล –> กรอง(หรือกด Alt + S จากนั้นกด F โดยไม่ต้องปล่อย Alt หรือกด Ctrl + Shift + L) Excel เพิ่มตัวกรอง (เมนูแบบเลื่อนลง) สำหรับส่วนหัวของคอลัมน์ทั้งหมด
3. เปิดหนึ่งในเมนูแบบเลื่อนลง เช่น ลูกค้า. ยกเลิกการเลือก เลือกทั้งหมดแล้วเลือกลูกค้าหนึ่งราย ในตัวอย่างของเรา - เชฟรอน.
4. เลือกเซลล์ด้านล่างข้อมูลที่กรองโดยตรง ในตัวอย่างของเรา เซลล์ E565:H565
5. กด Alt+= หรือคลิกไอคอน ผลรวมอัตโนมัติ(เมนู บ้าน). แทนที่จะใช้ SUM Excel จะใช้ SUBTOTAL(9; พิสัย) ซึ่งจะรวมเฉพาะแถวที่เลือกโดยตัวกรอง (ดูรูปที่ 3)

ใน Excel 2010 ฟังก์ชันอื่นที่คล้ายกันปรากฏขึ้น - AGGREGATE (สำหรับรายละเอียดเพิ่มเติม โปรดดู ; ส่วนฟังก์ชัน AGGREGATE) มันมีคุณสมบัติเพิ่มเติมใน "ละคร" และตัวเลือกเพิ่มเติมสำหรับบรรทัดที่จะไม่รวมและการประมวลผล ข้อได้เปรียบหลักคือการจัดการค่าที่ผิดพลาด (เช่น #DIV/0!) ขออภัย ฟังก์ชันนี้ใช้ไม่ได้กับผลรวมของคอลัมน์ที่มองเห็น

สรุป: คุณสามารถใช้ฟังก์ชัน SUBTOTAL เพื่อละเว้นแถวที่ซ่อนอยู่

มาคำนวณผลรวมย่อยในตาราง MS EXCEL กัน ตัวอย่างเช่น ในตารางที่มีข้อมูลการขายสำหรับสินค้าหลายประเภท เราจะคำนวณต้นทุนของแต่ละหมวดหมู่.

เรามีตารางขายสินค้า (สินค้าซ้ำ) ดูตัวอย่างไฟล์.

คำนวณต้นทุนของแต่ละผลิตภัณฑ์โดยใช้ MS EXCEL ผลรวมย่อย (ข้อมูล/ โครงสร้าง/ ผลรวมย่อย).

สำหรับสิ่งนี้คุณต้อง:

• ตรวจสอบให้แน่ใจว่าชื่อคอลัมน์มีส่วนหัว
• ข้อมูลคอลัมน์ สินค้า,ตัวอย่างเช่นกับ ;
• เลือกเซลล์ใดก็ได้ในตาราง โทร ผลรวมย่อย(ในเมนู โครงสร้างข้อมูล);

• ในสนาม" ทุกครั้งที่มีการเปลี่ยนแปลง:" เลือก ผลิตภัณฑ์;
• ในสนาม" การดำเนินการ" เลือก ผลรวม;
• ในสนาม" เพิ่มผลรวมสำหรับ» ทำเครื่องหมายที่ช่องถัดจากค่า ราคา;
• กดตกลง

ดังที่คุณเห็นจากภาพด้านบน หลังจากใช้เครื่องมือ ผลรวมย่อย MS EXCEL ได้สร้างการจัดระเบียบข้อมูลสามระดับ: การควบคุมโครงสร้างปรากฏทางด้านซ้ายของตาราง ระดับ 1: ผลรวมทั้งหมด (ต้นทุนของรายการทั้งหมดในตาราง); ระดับ 2: ต้นทุนสินค้าในแต่ละประเภท; ระดับ 3: แถวของตารางทั้งหมด คุณสามารถแสดงตารางในระดับรายละเอียดที่ต้องการได้โดยการกดปุ่มที่เหมาะสม ตัวเลขด้านล่างแสดงระดับ 1 และ 2

คัดลอกเฉพาะแถวที่มีผลรวมย่อย

การคัดลอกเฉพาะแถวที่มีผลรวมย่อยไปยังช่วงอื่นนั้นไม่ใช่เรื่องง่าย: แม้ว่าตารางจะถูกจัดกลุ่มในระดับที่ 2 (ดูรูปด้านบน) จากนั้นเลือกเซลล์ที่มีผลรวม (อันที่จริงคือช่วง A4:D92) และคัดลอกไปยังช่วงอื่น เราจะได้ทั้งตาราง เพื่อคัดลอกเฉพาะผลรวมที่เราใช้ (เราจะใช้ข้อเท็จจริงที่ว่า MS EXCEL เมื่อสร้างโครงสร้าง ผลรวมย่อยแทรกแถวทั้งหมดด้วยคำที่เพิ่ม ผลหรือในเวอร์ชั่นภาษาอังกฤษ - ทั้งหมด).

• สร้างในช่วง D5:D6 แผ่นเกณฑ์: ใน D5 วางส่วนหัวของคอลัมน์ที่มีคำว่า Total เช่น คำ ผลิตภัณฑ์; วี D6 ป้อน * รวม (ทุกแถวจะถูกเลือกที่มีค่าในคอลัมน์ผลิตภัณฑ์ที่ลงท้ายด้วยคำว่า ผล) เครื่องหมายดอกจัน หมายถึง *;

• เลือกเซลล์ใดก็ได้ในตาราง
• เรียก ( ข้อมูล/ เรียงลำดับและกรอง/ ขั้นสูง);
• ในฟิลด์ ช่วงเงื่อนไข ให้ป้อน D5:D6 ;
• ตั้งค่าตัวเลือก คัดลอกผลลัพธ์ไปยังตำแหน่งอื่น;
• ในสนาม ใส่ผลลัพธ์ในช่วงระบุเซลล์ว่าง เช่น A102 ;

การทำงาน ผลรวมย่อย() , เวอร์ชันภาษาอังกฤษของ SUBTOTAL(), ใช้ในการคำนวณผลรวมย่อย (ผลรวม ค่าเฉลี่ย จำนวนค่า ฯลฯ) ในช่วงที่มีแถวซ่อนอยู่

ความไม่ชอบมาพากลของฟังก์ชันนี้คือมีไว้เพื่อใช้ร่วมกับเครื่องมือ EXCEL อื่นๆ: และ . ดูตัวอย่างไฟล์.

ไวยากรณ์ของฟังก์ชัน

ผลรวมย่อย(function_number;ลิงค์1;link2;...))

Function_numberเป็นตัวเลขระหว่าง 1 ถึง 11 ที่ระบุฟังก์ชันที่จะใช้เมื่อคำนวณผลรวมภายในรายการ

Function_number (รวมถึงค่าที่ซ่อนอยู่)	Function_number (ไม่รวมค่าที่ซ่อนอยู่)	การทำงาน
1	101	เฉลี่ย
2	102	ตรวจสอบ
3	103	นับ
4	104	สูงสุด
5	105	นาที
6	106	สินค้า
7	107	สพฐ
8	108	สพฐ
9	109	ผลรวม
10	110	ดิสพ
11	111	ดิสพ

ตัวอย่างเช่น ฟังก์ชัน SUM() มีโค้ด 9 ฟังก์ชัน SUM() ก็มีโค้ด 109 เช่น คุณสามารถเขียนสูตร = ผลรวมย่อย (9;A2:A10)หรือ = INTERIM.TOTALS(109;A2:A10).อะไรคือความแตกต่าง - อ่านด้านล่าง มักใช้รหัสฟังก์ชันตั้งแต่ 1 ถึง 11

หากมีสูตรการบวกอยู่แล้วในอาร์กิวเมนต์ link1;link2;... (ผลรวมซ้อน) ระบบจะละเว้นผลรวมย่อยเหล่านี้เพื่อหลีกเลี่ยงการบวกซ้ำ

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

การทำงาน ผลรวมย่อย()และตัวกรองอัตโนมัติ

ให้มีตารางเริ่มต้น

การทำงาน ผลรวมย่อย()และเส้นที่ซ่อนอยู่

ให้มีตารางแหล่งที่มาเดียวกัน ซ่อนสายผลิตภัณฑ์ สินค้า2ผ่านเมนูหรือผ่านเมนูบริบท

ในกรณีนี้ มีความแตกต่างระหว่างการใช้รหัสฟังก์ชัน SUM(): 9 และ 109 ฟังก์ชันที่มีรหัส 109 "รู้สึก" ว่าแถวถูกซ่อนหรือไม่ กล่าวอีกนัยหนึ่งสำหรับช่วงของรหัส function_number 101 ถึง 111 ฟังก์ชัน ผลรวมย่อย()ไม่รวมค่าของแถวที่ซ่อนอยู่ด้วยคำสั่ง หน้าแรก/ เซลล์/ รูปแบบ/ ซ่อนหรือแสดง . รหัสเหล่านี้ใช้เพื่อรับผลรวมย่อยสำหรับตัวเลขที่ไม่ได้ซ่อนของรายการเท่านั้น

การทำงาน ผลรวมย่อย()และผลรวมย่อยของเครื่องมือ EXCEL

ให้มีตารางเริ่มต้นด้วย มาสร้างโครงสร้างโดยใช้ EXCEL - ในตัว

ซ่อนเส้นด้วย สินค้า2โดยคลิกที่ปุ่ม "ลบ" ที่เกี่ยวข้องในโครงสร้าง

การทำงาน ผลรวมย่อย()แยกบรรทัดที่ไม่แสดงทั้งหมดโดยโครงสร้างโดยไม่คำนึงถึงค่ารหัสที่ใช้ function_numberและในกรณีของเรา ให้คำนวณผลรวมของสินค้าเท่านั้น สินค้า1. ผลลัพธ์นี้คล้ายกับสถานการณ์ที่มีตัวกรองอัตโนมัติ

คุณสมบัติอื่นๆ

การทำงาน ผลรวมย่อย()สามารถคำนวณผลรวม จำนวน และค่าเฉลี่ยของค่าที่เลือก และยังมีฟังก์ชันอื่นๆ อีก 8 ฟังก์ชัน (ดูไวยากรณ์) ตามกฎแล้วฟังก์ชันเหล่านี้เพียงพอแล้ว แต่บางครั้งก็จำเป็นต้องขยายขีดความสามารถของฟังก์ชัน ผลรวมย่อย(). ลองพิจารณาตัวอย่างการคำนวณค่าเฉลี่ยทางเรขาคณิตสำหรับค่าที่เลือกโดยตัวกรองอัตโนมัติ ฟังก์ชัน SRGEOM() ไม่ได้อยู่ในรายการฟังก์ชันที่มีให้ใช้งานผ่านรหัสที่เกี่ยวข้อง แต่มีทางออก