วิธีที่ 1 สูตร
เริ่มกันเลยสำหรับการอุ่นเครื่องด้วยตัวเลือกที่ง่ายที่สุด - สูตร ถ้าเรามีตารางขนาดเล็กที่จัดเรียงตามวันที่เป็นข้อมูลเข้า เพื่อที่จะคำนวณผลรวมในคอลัมน์ที่แยกจากกัน เราจำเป็นต้องมีสูตรพื้นฐาน:
คุณลักษณะหลักที่นี่คือการแก้ไขช่วงที่ยุ่งยากภายในฟังก์ชัน SUM - การอ้างอิงไปยังจุดเริ่มต้นของช่วงนั้นสมบูรณ์ (ด้วยเครื่องหมายดอลลาร์) และส่วนท้าย - สัมพันธ์กัน (ไม่มีดอลลาร์) ดังนั้น เมื่อคัดลอกสูตรลงไปทั้งคอลัมน์ เราจะได้ช่วงขยาย ซึ่งเป็นผลรวมที่เราคำนวณ
ข้อเสียของวิธีนี้ชัดเจน:
- ตารางจะต้องจัดเรียงตามวันที่
- เมื่อเพิ่มแถวใหม่ด้วยข้อมูล จะต้องขยายสูตรด้วยตนเอง
วิธีที่ 2 ตารางเดือย
วิธีนี้ซับซ้อนกว่าเล็กน้อย แต่น่าพอใจกว่ามาก และเพื่อให้รุนแรงขึ้น ให้พิจารณาปัญหาที่ร้ายแรงกว่านั้น - ตารางข้อมูล 2000 แถว ซึ่งไม่มีการจัดเรียงตามคอลัมน์วันที่ แต่มีการซ้ำซ้อน (เช่น เราสามารถขายได้หลายครั้งในวันเดียวกัน):
เราแปลงตารางเดิมให้เป็นแป้นพิมพ์ลัด "อัจฉริยะ" (ไดนามิก) Ctrl+T หรือทีม หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง)แล้วเราก็สร้าง pivot table ด้วยคำสั่ง แทรก – PivotTable (แทรก — ตารางหมุน). เราใส่วันที่ในพื้นที่แถวในการสรุปและจำนวนสินค้าที่ขายในพื้นที่มูลค่า:
โปรดทราบว่าหากคุณมี Excel เวอร์ชันไม่เก่าเลย วันที่จะถูกจัดกลุ่มตามปี ไตรมาส และเดือนโดยอัตโนมัติ หากคุณต้องการการจัดกลุ่มอื่น (หรือไม่ต้องการเลย) คุณสามารถแก้ไขได้โดยคลิกขวาที่วันที่ใดก็ได้และเลือกคำสั่ง กลุ่ม / เลิกจัดกลุ่ม (กลุ่ม / เลิกจัดกลุ่ม).
หากคุณต้องการดูทั้งผลรวมที่เป็นผลลัพธ์ตามช่วงเวลาและผลรวมที่รันอยู่ในคอลัมน์ที่แยกจากกัน คุณควรโยนฟิลด์ลงในพื้นที่ของค่า ขาย อีกครั้งเพื่อให้ได้สนามที่ซ้ำกัน – ในนั้นเราจะเปิดการแสดงผลรวมการวิ่ง ให้คลิกขวาที่ฟิลด์และเลือกคำสั่ง การคำนวณเพิ่มเติม – ยอดรวมสะสม (แสดงค่าเป็น — รันยอดรวม):
คุณสามารถเลือกตัวเลือกในการเพิ่มยอดรวมเป็นเปอร์เซ็นต์ และในหน้าต่างถัดไป คุณต้องเลือกฟิลด์ที่จะสะสม ในกรณีของเรา นี่คือฟิลด์วันที่:
ข้อดีของวิธีนี้:
- อ่านข้อมูลจำนวนมากได้อย่างรวดเร็ว
- ไม่จำเป็นต้องป้อนสูตรด้วยตนเอง
- เมื่อเปลี่ยนแปลงข้อมูลต้นฉบับ การอัปเดตข้อมูลสรุปโดยใช้ปุ่มเมาส์ขวาหรือด้วยคำสั่ง Data – Refresh All ก็เพียงพอแล้ว
ข้อเสียตามมาจากการที่นี่คือบทสรุป ซึ่งหมายความว่าคุณไม่สามารถทำอะไรก็ได้ที่คุณต้องการในนั้น (แทรกบรรทัด เขียนสูตร สร้างไดอะแกรม ฯลฯ) จะไม่ทำงานอีกต่อไป
วิธีที่ 3: Power Query
มาโหลดตาราง "อัจฉริยะ" ของเราที่มีแหล่งข้อมูลลงในตัวแก้ไขคิวรี Power Query โดยใช้คำสั่ง ข้อมูล – จากตาราง/ช่วง (ข้อมูล — จากตาราง/ช่วง). ในเวอร์ชันล่าสุดของ Excel ได้มีการเปลี่ยนชื่อ - ตอนนี้เรียกว่า มีใบ (จากแผ่นงาน):
จากนั้นเราจะทำตามขั้นตอนต่อไปนี้:
1. เรียงลำดับตารางจากน้อยไปมากตามคอลัมน์วันที่ด้วยคำสั่ง เรียงลำดับจากน้อยไปมาก ในรายการดรอปดาวน์ตัวกรองในส่วนหัวของตาราง
2. หลังจากนั้นเล็กน้อย ในการคำนวณยอดรวม เราจำเป็นต้องมีคอลัมน์เสริมที่มีหมายเลขแถวลำดับ มาเพิ่มด้วยคำสั่ง เพิ่มคอลัมน์ – คอลัมน์ดัชนี – ตั้งแต่ 1 (เพิ่มคอลัมน์ — คอลัมน์ดัชนี — จาก 1).
3. นอกจากนี้ ในการคำนวณยอดรวม เราจำเป็นต้องมีการอ้างอิงไปยังคอลัมน์ ขายที่ข้อมูลสรุปของเราอยู่ ใน Power Query คอลัมน์จะเรียกอีกอย่างว่ารายการ (รายการ) และหากต้องการรับลิงก์ ให้คลิกขวาที่ส่วนหัวของคอลัมน์และเลือกคำสั่ง รายละเอียด (แสดงรายละเอียด). นิพจน์ที่เราต้องการจะปรากฏในแถบสูตรซึ่งประกอบด้วยชื่อของขั้นตอนก่อนหน้า #”เพิ่มดัชนี”, จากที่เราเอาตารางและชื่อคอลัมน์มา [ฝ่ายขาย] จากตารางนี้ในวงเล็บเหลี่ยม:
คัดลอกนิพจน์นี้ไปยังคลิปบอร์ดเพื่อใช้งานต่อไป
4. ลบขั้นตอนสุดท้ายที่ไม่จำเป็นออกไป ขาย และเพิ่มคอลัมน์จากการคำนวณแทนการคำนวณผลรวมที่รันด้วยคำสั่ง การเพิ่มคอลัมน์ – คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง). สูตรที่เราต้องการจะมีลักษณะดังนี้:
ที่นี่ฟังก์ชั่น รายการช่วง นำรายการเดิม (คอลัมน์ [ฝ่ายขาย]) และแยกองค์ประกอบออกจากองค์ประกอบโดยเริ่มจากองค์ประกอบแรก (ในสูตร นี่คือ 0 เนื่องจากการกำหนดหมายเลขใน Power Query เริ่มจากศูนย์) จำนวนองค์ประกอบที่จะดึงคือหมายเลขแถวที่เรานำมาจากคอลัมน์ [ดัชนี]. ดังนั้นฟังก์ชันนี้สำหรับแถวแรกจะคืนค่าเพียงหนึ่งเซลล์แรกของคอลัมน์ ขาย. สำหรับบรรทัดที่สอง - สองเซลล์แรกอยู่แล้ว สำหรับบรรทัดที่สาม - สามเซลล์แรก ฯลฯ
ถ้าอย่างนั้นฟังก์ชั่น รายการผลรวม รวมค่าที่แยกออกมาและเราได้รับผลรวมขององค์ประกอบก่อนหน้าทั้งหมดในแต่ละแถวนั่นคือผลรวมสะสม:
ยังคงต้องลบคอลัมน์ดัชนีที่เราไม่ต้องการอีกต่อไปและอัปโหลดผลลัพธ์กลับไปที่ Excel ด้วยคำสั่ง Home – Close & Load to
ปัญหาได้รับการแก้ไข
รวดเร็วและโกรธ
โดยหลักการแล้วสิ่งนี้สามารถหยุดได้ แต่มีแมลงวันตัวเล็ก ๆ อยู่ในครีม - คำขอที่เราสร้างขึ้นนั้นทำงานด้วยความเร็วของเต่า ตัวอย่างเช่น บนพีซีของฉันไม่ใช่พีซีที่อ่อนแอที่สุด ตารางที่มีเพียง 2000 แถวเท่านั้นที่ประมวลผลใน 17 วินาที เกิดอะไรขึ้นถ้ามีข้อมูลเพิ่มเติม?
ในการเร่งความเร็ว คุณสามารถใช้การบัฟเฟอร์โดยใช้ฟังก์ชัน List.Buffer พิเศษ ซึ่งจะโหลดรายการ (รายการ) ที่กำหนดให้เป็นอาร์กิวเมนต์ใน RAM ซึ่งจะทำให้เข้าถึงได้เร็วขึ้นอย่างมากในอนาคต ในกรณีของเรา ควรบัฟเฟอร์รายการ #”ดัชนีที่เพิ่ม” [ขายแล้ว] ซึ่ง Power Query ต้องเข้าถึงเมื่อคำนวณผลรวมที่ทำงานอยู่ในแต่ละแถวของตาราง 2000 แถวของเรา
เมื่อต้องการทำเช่นนี้ ในตัวแก้ไข Power Query บนแท็บหลัก ให้คลิกปุ่มตัวแก้ไขขั้นสูง (หน้าแรก - ตัวแก้ไขขั้นสูง) เพื่อเปิดซอร์สโค้ดของคิวรีของเราในภาษา M ที่สร้างขึ้นใน Power Query:
แล้วเพิ่มบรรทัดที่มีตัวแปรตรงนั้น รายการของฉันค่าที่ส่งคืนโดยฟังก์ชันบัฟเฟอร์ และในขั้นตอนต่อไป เราจะแทนที่การเรียกรายการด้วยตัวแปรนี้:
หลังจากทำการเปลี่ยนแปลงเหล่านี้ แบบสอบถามของเราจะเร็วขึ้นอย่างมาก และจะจัดการกับตาราง 2000 แถวในเวลาเพียง 0.3 วินาที!
อย่างอื่นใช่ไหม 🙂
- แผนภูมิ Pareto (80/20) และวิธีสร้างใน Excel
- การค้นหาคำสำคัญในข้อความและการบัฟเฟอร์แบบสอบถามใน Power Query