ทำไมฉันถึงกำลังเปลี่ยนจาก PivotTables เป็นฟังก์ชัน PIVOTBY ใน Excel

เป็นเวลาหลายสิบปี PivotTables ได้กลายเป็นสุดยอดเครื่องมือสรุปข้อมูลใน Excel อย่างไม่ต้องสงสัย แต่แม้ว่ามันจะมีประโยชน์ แต่ก็รู้สึกเสมอเหมือนกับว่าเป็น "แอปภายในแอป" มันอยู่ในชั้นของตัวเอง มีเมนูที่แปลกของตัวเอง และบางครั้งก็ไม่เข้ากับสูตรอื่น ๆ ของคุณ

นั่นคือเหตุผลที่ฉันเปลี่ยนไปใช้ฟังก์ชัน PIVOTBY ที่เรียบง่ายและเหมาะกับกริดมากกว่า

รายการอาร์กิวเมนต์เชิงลึกของ PIVOTBY คืออาวุธลับของมัน

สูตรเดียว ควบคุมได้ไม่จำกัด

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

=PIVOTBY(row_fields, col_fields, values, function, [other options...])

แม้ว่าตอนแรกจะดูซับซ้อนกว่าการตั้งค่า PivotTable ฟังก์ชันนี้มีความสามารถที่แท้จริงอยู่ที่ลักษณะเป็นโมดูล: คุณกำหนดสิ่งจำเป็น "สี่หลัก" ได้แก่ แถว คอลัมน์ ค่า และคณิตศาสตร์ จากนั้นรายงานจะสร้างขึ้นทันที จากนั้นคุณสามารถสลับแสดงผลรวมย่อย ผลรวมใหญ่ และตัวกรองได้โดยไม่ต้องออกจากกริด

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

จุดสิ้นสุดของตรรกะสเปรดชีตที่ 'ซ่อนอยู่'

ความโปร่งใสคือเครื่องมือการตรวจสอบสูงสุด

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

ด้วย PIVOTBY ตรรกะจะปรากฏอยู่ในสูตรเอง:

=PIVOTBY(T_Sales[Category], T_Sales[Region], T_Sales[Revenue], SUM)

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

การรวม LAMBDA นำคณิตศาสตร์กลับเข้าสู่กริด

การคำนวณโดยไม่ต้องใช้เมนูโมดอลทำให้เหนื่อยน้อยลง

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

ด้านอื่น ๆ PIVOTBY ช่วยให้คุณรวมการคำนวณโดยตรงโดยใช้ LAMBDA และฟังก์ชันช่วยเหลือของมัน:

=PIVOTBY(T_Sales[Category], T_Sales[Region], T_Sales[Revenue], LAMBDA(x, SUM(x) * 1.05))

การปรับภาษีเพียง 5% แบบง่ายๆ? ทำได้โดยไม่ต้องออกจากกริด มันเน้นคีย์บอร์ดเป็นหลักและสูตรเป็นหลัก ทำให้การทำงานของคุณรวดเร็วและมีสมาธิ ฉันยอมใช้เวลาในการสร้างตรรกะที่ชาญฉลาดมากกว่าการค้นหาฟังก์ชันพื้นฐานผ่านเมนู

การรวมเนทีฟแบบกริดกับตัวดำเนินการช่วงสปิล

เครื่องหมาย # คือความตายของ GETPIVOTDATA

ตารางสรุป (PivotTables) เป็นวัตถุแบบคงที่ที่อยู่บนเลเยอร์แยกเหนือกริด มักจะต้องใช้ฟังก์ชัน GETPIVOTDATA ที่ยุ่งยากเพื่ออ้างอิงถึงค่าของพวกมัน PIVOTBY จะส่งออกอาร์เรย์แบบไดนามิก ดังนั้นคุณสามารถอ้างอิงรายงานทั้งหมดได้ด้วยเครื่องหมาย # เพียงตัวเดียว (ตัวดำเนินการช่วง spill):

=G2#

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

การออกแบบที่ควบคุมด้วยคีย์บอร์ดช่วยป้องกันการทำรูปแบบผิดพลาดโดยไม่ตั้งใจ

เลย์เอาต์ที่ถูกเขียนโค้ดไว้เพื่อความทนทาน

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

=PIVOTBY(T_Sales[[#All],[Category]], T_Sales[[#All],[Region]], T_Sales[[#All],[Revenue]], SUM, 1, 1)

รายงานยังคงสอดคล้องกัน ไม่ว่าคนใดจะเปิดสมุดงานหรือกดปุ่มใด สำหรับใครก็ตามที่เคยใช้เวลาหลายชั่วโมงในการแก้ไขเลย์เอาต์ที่เสียหลังจากเพื่อนร่วมงานรีเฟรช PivotTable โดยบังเอิญ เพียงเรื่องนี้เรื่องเดียวก็คุ้มกับการเปลี่ยนไปใช้งานแล้ว

การกรองแบบรวมช่วยสร้างประสบการณ์ผู้ใช้ที่เรียบเนียนมากขึ้น

แดชบอร์ดมืออาชีพโดยไม่มีตัวกรอง

Slicers ดีสำหรับการกรอง แต่พวกมันลอยอยู่บนแผ่นงานเหมือนสติกเกอร์ พวกมันถูกย้ายหรือลบได้ง่ายโดยคนอื่น ซึ่งสามารถทำลายความสมบูรณ์ของภาพของแดชบอร์ดที่ออกแบบอย่างพิถีพิถันได้อย่างรวดเร็ว

อาร์กิวเมนต์ filter_array ของ PIVOTBY ฝังเกณฑ์ลงในสูตร:

=PIVOTBY(T_Sales[Category], T_Sales[Region], T_Sales[Revenue], SUM, , , , , , T_Sales[Region]=G2)

เปลี่ยน G2 เป็น "West" และรายงานของคุณจะอัปเดตทันที ไม่มีวัตถุลอย ไม่มีการคลิกโดยไม่ได้ตั้งใจ—มีเพียงแดชบอร์ดที่สะอาดและตอบสนองได้ดี รู้สึกเหมือนแอปพลิเคชันจริง

อย่าเข้าใจฉันผิด—PivotTables ยังคงมีบทบาทในระบบนิเวศของ Excel อยู่ หากคุณกำลังวิเคราะห์ข้อมูลจำนวนหลายล้านแถว ทำงานกับฐานข้อมูลภายนอกหรือโมเดลข้อมูลหลายตาราง ต้องการอินเทอร์เฟซที่มีการโต้ตอบสูง หรืออยากจัดกลุ่มวันที่ เครื่องมือ PivotTable ยังคงเป็นมาตรฐานทองคำ นอกจากนี้ ยังสามารถใช้ได้ง่ายในเวอร์ชันเก่าของ Excel ในขณะที่คุณต้องใช้ Microsoft 365, Excel 2021 หรือใหม่กว่า หรือ Excel สำหรับเว็บเพื่อใช้ฟังก์ชัน PIVOTBY

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