บทสรุปผู้บริหาร

Power Pivot คืออะไร?

• แนะนำให้ใช้ Excel 2010 และ 2013 เป็นส่วนเสริม แต่ปัจจุบันเป็นแอปพลิเคชันที่มาพร้อมกับแอปพลิเคชันแล้ว Power Pivot เป็นส่วนหนึ่งของกลุ่มธุรกิจอัจฉริยะของ Microsoft ที่สามารถทำงานวิเคราะห์ข้อมูลขนาดใหญ่ (แต่ไม่ จำกัด เพียง) โดยไม่ต้องมีโครงสร้างพื้นฐานหรือซอฟต์แวร์พิเศษ
• ตามที่ Microsoft ' Power Pivot ช่วยให้คุณสามารถนำเข้าข้อมูลนับล้านแถวจากแหล่งข้อมูลหลายแหล่งลงในสมุดงาน Excel เดียวสร้างความสัมพันธ์ระหว่างข้อมูลที่แตกต่างกันสร้างคอลัมน์จากการคำนวณและการวัดโดยใช้สูตรสร้าง PivotTables และ PivotCharts และวิเคราะห์ข้อมูลเพิ่มเติมเพื่อให้คุณสามารถทำธุรกิจได้ทันเวลา ตัดสินใจโดยไม่ต้องการความช่วยเหลือด้านไอที '
• Power Pivot ถูกสร้างขึ้นเพื่อตอบสนองโดยตรงต่อความต้องการข้อมูลขนาดใหญ่ของความต้องการข่าวกรองธุรกิจร่วมสมัยซึ่ง Excel รุ่นก่อน ๆ มีขีด จำกัด 1,048,576 แถวหรือข้อบกพร่องด้านความเร็วในการประมวลผลซึ่งต้องดิ้นรนเพื่อรับมือ
• Power Pivot แสดงโดย Microsoft โดยใช้ DAX (Data Analysis Expressions) ซึ่งเป็นชุดของฟังก์ชันตัวดำเนินการและค่าคงที่ที่ใช้งานได้ในสูตรหรือนิพจน์เพื่อคำนวณ / ส่งคืนค่าอย่างน้อยหนึ่งค่า

Power Pivot เทียบกับ Excel พื้นฐานมีประโยชน์อย่างไร

• Power Pivot ช่วยให้คุณสามารถนำเข้าและจัดการข้อมูลหลายร้อยล้านแถวในขณะที่ Excel มีข้อ จำกัด ที่เข้มงวดเพียงมากกว่าหนึ่งล้านแถว
• Power Pivot ช่วยให้คุณสามารถนำเข้าข้อมูลจากหลายแหล่งลงในสมุดงานแหล่งเดียวโดยไม่ต้องสร้างแผ่นงานแหล่งที่มาหลายแผ่นและจัดการกับปัญหาการควบคุมเวอร์ชันและความสามารถในการถ่ายโอนที่เป็นไปได้
• Power Pivot ช่วยให้คุณจัดการข้อมูลที่นำเข้าวิเคราะห์และสรุปผลโดยไม่ทำให้ระบบคอมพิวเตอร์ของคุณช้าลงตามปกติของ Basic Excel
• Power Pivot ช่วยให้คุณเห็นภาพและจัดการชุดข้อมูลขนาดใหญ่ของคุณด้วย PivotCharts และ Power BI โดยที่ Excel พื้นฐานขาดความสามารถเหล่านี้

ผู้เชี่ยวชาญด้านการเงินหรือที่ปรึกษา Excel จะช่วยธุรกิจของคุณได้อย่างไร?

• โดยทำงานร่วมกับคุณในฐานะ พันธมิตรทางความคิด เพื่อออกแบบจัดโครงสร้างสร้างและส่งมอบแบบจำลองทางการเงินงบประมาณและการวิเคราะห์ข้อมูลขนาดใหญ่ / โครงการทั้งหมดนี้จะนำไปสู่การตัดสินใจเกี่ยวกับโครงการที่น่าสนใจการควบรวมและซื้อกิจการหรือการลงทุนเชิงกลยุทธ์
• ด้วยการสร้างโมเดลที่กำหนดเองเฉพาะสำหรับธุรกิจของคุณโดยใช้ Power Pivot และฟังก์ชัน excel พิเศษอื่น ๆ
• โดยยัง การสร้างเทมเพลตสำเร็จรูปแบบ go-to ที่สามารถปรับใช้ได้โดยไม่ซ้ำกันโดยเกือบทุกคนในองค์กรของคุณสำหรับเกือบทุกวัตถุประสงค์โดยใช้ Power Pivot และฟังก์ชัน Excel พิเศษอื่น ๆ
• โดยการฝึกอบรมบุคคลหรือกลุ่มภายในองค์กรของคุณในทุกเรื่องตั้งแต่พื้นฐานของ excel การสร้างแบบจำลองและการวิเคราะห์ไปจนถึงวิธีการเชิงปริมาณขั้นสูงโดยใช้ Power Pivot ตาราง Power Pivot แผนภูมิ Power Pivot และ PowerQuery
• โดยคำนึงถึงสิ่งเหล่านี้และอื่น ๆ ควบคู่ไปกับ การออกแบบการสร้างและการนำเสนองานนำเสนอ PowerPoint ที่สวยงามและเป็นมืออาชีพ ก่อนการตัดสินใจเชิงกลยุทธ์

ดาวน์โหลดชุดข้อมูล ที่นี่ เพื่อติดตามพร้อมกับบทช่วยสอน

เสื้อผ้าใหม่ของจักรพรรดิ: บทช่วยสอนเรื่อง Power Pivot

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

โดยเฉพาะอย่างยิ่งโครงสร้างพื้นฐานของ Excel รุ่นเก่าและข้อ จำกัด ในการประมวลผลเช่นขีด จำกัด แถวที่ 1,048,576 แถวหรือการประมวลผลที่ช้าลงอย่างหลีกเลี่ยงไม่ได้ซึ่งเกี่ยวข้องกับชุดข้อมูลขนาดใหญ่ตารางข้อมูลและสเปรดชีตที่เชื่อมต่อกันทำให้ความสามารถในการใช้งานลดลงในฐานะเครื่องมือข้อมูลขนาดใหญ่ที่มีประสิทธิภาพ อย่างไรก็ตามในปี 2010 Microsoft ได้เพิ่มมิติใหม่ให้กับ Excel ที่เรียกว่า Power Pivot . Power Pivot นำเสนอระบบธุรกิจอัจฉริยะรุ่นใหม่และฟังก์ชันการวิเคราะห์ทางธุรกิจให้กับ Excel ในความสามารถในการแยกรวมและวิเคราะห์ชุดข้อมูลที่แทบจะไร้ขีด จำกัด โดยไม่ทำให้ความเร็วในการประมวลผลลดลง แม้จะมีการเปิดตัวเมื่อแปดปีก่อน แต่นักวิเคราะห์ทางการเงินส่วนใหญ่ยังไม่ทราบวิธีใช้ Power Pivot และหลายคนไม่ทราบว่ามีอยู่จริง

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

Power Pivot คืออะไรและทำไมจึงมีประโยชน์

Power Pivot เป็นฟีเจอร์ของ Microsoft Excel ที่ถูกนำมาใช้เป็นส่วนเสริมของ Excel 2010 และ 2013 และตอนนี้เป็นฟีเจอร์ดั้งเดิมสำหรับ Excel 2016 และ 365 ในฐานะ Microsoft อธิบาย , Power Pivot สำหรับ Excel 'ช่วยให้คุณสามารถนำเข้าข้อมูลนับล้านแถวจากแหล่งข้อมูลหลายแหล่งลงในสมุดงาน Excel เดียวสร้างความสัมพันธ์ระหว่างข้อมูลที่แตกต่างกันสร้างคอลัมน์จากการคำนวณและการวัดโดยใช้สูตรสร้าง PivotTables และ PivotCharts จากนั้นวิเคราะห์ข้อมูลเพิ่มเติม เพื่อให้คุณสามารถตัดสินใจทางธุรกิจได้อย่างทันท่วงทีโดยไม่ต้องการความช่วยเหลือด้านไอที”

ภาษานิพจน์หลักที่ Microsoft ใช้ใน Power Pivot คือ DAX (Data Analysis Expressions) แม้ว่าภาษาอื่นจะสามารถใช้ได้ในสถานการณ์เฉพาะก็ตาม อีกครั้งดังที่ Microsoft อธิบายว่า“ DAX คือชุดของฟังก์ชันตัวดำเนินการและค่าคงที่ที่สามารถใช้ในสูตรหรือนิพจน์เพื่อคำนวณและส่งคืนค่าอย่างน้อยหนึ่งค่า ระบุให้ง่ายขึ้น DAX ช่วยให้คุณสร้างข้อมูลใหม่จากข้อมูลที่มีอยู่แล้วในแบบจำลองของคุณ” โชคดีสำหรับผู้ที่คุ้นเคยกับ Excel อยู่แล้วสูตร DAX จะดูคุ้นเคยเนื่องจากสูตรต่างๆมีไวยากรณ์ที่คล้ายกัน (เช่น SUM, AVERAGE, TRUNC)

เพื่อความชัดเจนประโยชน์หลักของการใช้ Power Pivot เทียบกับ Excel พื้นฐานสามารถสรุปได้ดังต่อไปนี้:

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

ในส่วนต่อไปนี้ฉันจะอธิบายแต่ละข้อข้างต้นและแสดงให้คุณเห็นว่า Power Pivot สำหรับ Excel มีประโยชน์อย่างไร

วิธีใช้ Power Pivot

1) การนำเข้าชุดข้อมูลขนาดใหญ่

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

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

ใช้ ข้อมูล บนริบบิ้นฉันสร้างไฟล์ แบบสอบถามใหม่ จากไฟล์ CSV (ดู การสร้างแบบสอบถามใหม่ ด้านล่าง) ฟังก์ชันนี้เคยเรียกว่า PowerQuery แต่ใน Excel 2016 และ 365 ถูกรวมเข้ากับแท็บข้อมูลของ Excel อย่างแน่นหนามากขึ้น

การสร้างแบบสอบถามใหม่

จากสมุดงานเปล่าใน Excel ไปจนถึงการโหลดทั้งสองล้านแถวลงใน Power Pivot ใช้เวลาประมาณหนึ่งนาที! สังเกตว่าฉันสามารถจัดรูปแบบข้อมูลแบบเบาบางได้โดยเลื่อนแถวแรกให้กลายเป็นชื่อคอลัมน์ ในช่วงไม่กี่ปีที่ผ่านมาฟังก์ชันการทำงานของ Power Query ได้รับการปรับปรุงอย่างมากจาก Add-in ของ Excel ไปเป็นส่วนที่รวมไว้อย่างแน่นหนาของแท็บข้อมูลบนแถบเครื่องมือ Power Query สามารถหมุนแบนล้างข้อมูลและกำหนดรูปแบบข้อมูลของคุณผ่านชุดตัวเลือกและภาษาของตัวเอง M.

2) การนำเข้าข้อมูลจากหลายแหล่ง

ประโยชน์หลักอีกประการหนึ่งของ Power Pivot สำหรับ Excel คือความสามารถในการนำเข้าข้อมูลจากหลายแหล่งได้อย่างง่ายดาย ก่อนหน้านี้พวกเราหลายคนสร้างแผ่นงานหลายแผ่นสำหรับแหล่งข้อมูลต่างๆของเรา บ่อยครั้งกระบวนการนี้เกี่ยวข้องกับการเขียนโค้ด VBA และคัดลอก / วางจากแหล่งข้อมูลที่แตกต่างกันเหล่านี้ โชคดีสำหรับเรา Power Pivot ช่วยให้คุณสามารถนำเข้าข้อมูลจากแหล่งข้อมูลต่าง ๆ ไปยัง Excel ได้โดยตรงโดยไม่ต้องประสบปัญหาดังกล่าวข้างต้น

การใช้ฟังก์ชัน Query ใน Exhibit 1 เราสามารถดึงข้อมูลจากแหล่งข้อมูลต่อไปนี้:

• Microsoft Azure
• เซิร์ฟเวอร์ SQL
• เทราดาทา
• เฟสบุ๊ค
• Salesforce
• ไฟล์ JSON
• สมุดงาน Excel
• …และอื่น ๆ อีกมากมาย

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

3) การทำงานกับชุดข้อมูลขนาดใหญ่

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

มาตรการ

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

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

ความแตกต่างระหว่างศิลปะกับการออกแบบ

Total Sales:=SUM('Accounting Data'[Amount])

สิ่งนี้จะสร้างการวัดใหม่ที่รวมในคอลัมน์จำนวนเงิน ในทำนองเดียวกันฉันสามารถพิมพ์หน่วยวัดอื่นในเซลล์ด้านล่าง

Average Sales:=AVERAGE('Accounting Data'[Amount])

การสร้างมาตรการ

จากนั้นดูว่าการสร้าง PivotTable ที่คุ้นเคยบนชุดข้อมูลขนาดใหญ่ทำได้เร็วเพียงใด

การสร้าง PivotTable

ตารางมิติ

ในฐานะนักวิเคราะห์ทางการเงินที่ใช้ Excel เรามีความเชี่ยวชาญในการใช้สูตรที่ซับซ้อนเพื่อทำให้เทคโนโลยีเป็นไปตามที่เราต้องการ เราเชี่ยวชาญ VLOOKUP, SUMIF และแม้จะหวั่น INDEX(MATCH()). อย่างไรก็ตามด้วยการใช้ Power Pivot เราสามารถโยนส่วนใหญ่ออกไปนอกหน้าต่างได้

การเพิ่มตารางที่ผู้ใช้สร้างขึ้นใน Power Pivot Model

เพื่อแสดงให้เห็นถึงฟังก์ชันนี้ฉันได้สร้างตารางอ้างอิงขนาดเล็กซึ่งฉันกำหนดให้แต่ละประเภทเป็นประเภท เมื่อเลือก“ เพิ่มในโมเดลข้อมูล” ตารางนี้จะโหลดลงใน Power Pivot (ดู การเพิ่มตารางที่ผู้ใช้สร้างขึ้นใน Power Pivot Model ข้างบน).

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

การสร้างตารางวันที่

คอลัมน์จากการคำนวณ

นอกจากการวัดแล้วยังมีการคำนวณอีกประเภทหนึ่ง ได้แก่ คอลัมน์จากการคำนวณ ผู้ใช้ Excel จะสบายใจในการเขียนสูตรเหล่านี้เนื่องจากคล้ายกับการเขียนสูตรในตารางข้อมูล ฉันได้สร้างคอลัมน์จากการคำนวณใหม่ด้านล่าง (ดู การสร้างคอลัมน์จากการคำนวณ ด้านล่าง) ซึ่งจัดเรียงตารางข้อมูลการบัญชีตามจำนวน การขายที่ต่ำกว่า $ 50 จะมีป้ายกำกับว่า 'เล็ก' และอื่น ๆ ทั้งหมดมีป้ายกำกับว่า 'ใหญ่' สูตรไม่รู้สึกง่าย?

การสร้างคอลัมน์จากการคำนวณ

ความสัมพันธ์

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

การกำหนดความสัมพันธ์

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

PivotTable โดยใช้ความสัมพันธ์

หรือเราสามารถสร้างแผนภูมิยอดขายเฉลี่ยสำหรับแต่ละวันในสัปดาห์โดยใช้ตารางปฏิทินใหม่

PivotChart โดยใช้ความสัมพันธ์

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

ในขณะที่สามารถดำเนินการในสถานการณ์จำลองการรายงานที่รวมทั้งหมดเหล่านี้ได้ แต่เรายังสามารถเจาะลึกลงไปในรายการโฆษณาแต่ละรายการได้เสมอ เราเก็บรักษาข้อมูลที่ละเอียดอ่อนของเรา

ฟังก์ชั่นขั้นสูง

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

ข่าวกรองเวลา

บ่อยครั้งเมื่อเราตรวจสอบผลลัพธ์ทางการเงินเราต้องการเปรียบเทียบกับกรอบเวลาที่เทียบเคียงจากปีที่แล้ว Power Pivot มีฟังก์ชันอัจฉริยะเวลาในตัว

Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

ตัวอย่างเช่นการเพิ่มเพียงสองมาตรการด้านบนในตารางข้อมูลการบัญชีใน Power Pivot ทำให้ฉันสามารถสร้าง PivotTable ต่อไปนี้ได้ในไม่กี่คลิก

เวลา PivotTable ข่าวกรอง

รายละเอียดไม่ตรงกัน

ในฐานะนักวิเคราะห์ทางการเงินปัญหาหนึ่งที่ฉันมักจะต้องแก้ไขคือรายละเอียดที่ไม่ตรงกัน ในตัวอย่างของเราข้อมูลการขายจริงจะแสดงในระดับหมวดหมู่ แต่เรามาเตรียมงบประมาณที่เป็นไปตามฤดูกาลเท่านั้น เราจะจัดเตรียมงบประมาณรายไตรมาสแม้ว่าข้อมูลการขายจะเป็นรายวันก็ตาม

รายละเอียดที่ไม่ตรงกัน - ตารางงบประมาณ

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

รายละเอียดที่ไม่ตรงกัน - ความสัมพันธ์

ใน Excel PivotTable ต่อไปนี้จะรวมเข้าด้วยกันอย่างรวดเร็ว

รายละเอียดที่ไม่ตรงกัน - งบประมาณเทียบกับผลลัพธ์จริง

นอกจากนี้เราสามารถกำหนดมาตรการใหม่ที่คำนวณผลต่างระหว่างยอดขายจริงและยอดขายตามงบประมาณดังต่อไปนี้:

Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

เมื่อใช้การวัดนี้เราสามารถแสดงความแปรปรวนบน PivotTable

รายละเอียดที่ไม่ตรงกัน - ผลลัพธ์ความแปรปรวน

เปอร์เซ็นต์ของทั้งหมด

สุดท้ายให้ตรวจสอบยอดขายในหมวดหมู่ใดหมวดหมู่หนึ่งเป็นเปอร์เซ็นต์ของยอดขายทั้งหมด (เช่นการมีส่วนร่วมในหมวดหมู่ต่อยอดขายโดยรวม) และยอดขายในหมวดหมู่ใดหมวดหมู่หนึ่งเป็นเปอร์เซ็นต์ของยอดขายประเภทเดียวกันทั้งหมด (เช่นการแบ่งหมวดหมู่ตามประเภทฤดูกาล การขาย). ฉันสร้างสองมาตรการด้านล่าง:

Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

ขณะนี้สามารถใช้มาตรการเหล่านี้ใน PivotTable ใหม่ได้:

เปอร์เซ็นต์ของทั้งหมด

สังเกตวิธีการคำนวณที่ ทั้งสองอย่าง หมวดหมู่และระดับประเภทตามฤดูกาล ฉันชอบความรวดเร็วและง่ายดายในการคำนวณเหล่านี้บนชุดข้อมูลขนาดใหญ่เช่นนี้ นี่เป็นเพียงตัวอย่างบางส่วนของความสง่างามและพลังการคำนวณที่แท้จริงของ Power Pivot

การบีบอัด

ประโยชน์อีกประการหนึ่งคือขนาดไฟล์จะหดลง ขนาดไฟล์ต้นฉบับคือ 91MB และตอนนี้มีขนาดไม่เกิน 4MB นั่นคือการบีบอัด 96% ของไฟล์ต้นฉบับ

ขนาดไฟล์

สิ่งนี้เกิดขึ้นได้อย่างไร? Power Pivot ใช้ไฟล์ xVelocity เครื่องยนต์เพื่อบีบอัดข้อมูล กล่าวง่ายๆคือข้อมูลจะถูกเก็บไว้ในคอลัมน์แทนที่จะเป็นแถว วิธีการจัดเก็บนี้ช่วยให้คอมพิวเตอร์สามารถบีบอัดค่าที่ซ้ำกันได้ ในชุดข้อมูลตัวอย่างของเรามีเพียงสี่ภูมิภาคที่ซ้ำกันในสองล้านแถวทั้งหมด Power Pivot สำหรับ Excel สามารถจัดเก็บข้อมูลนี้ได้อย่างมีประสิทธิภาพมากขึ้น ผลลัพธ์ก็คือสำหรับข้อมูลที่มีค่าการทำซ้ำจำนวนมากการจัดเก็บข้อมูลนี้มีค่าใช้จ่ายน้อยกว่ามาก

สิ่งหนึ่งที่ควรทราบคือฉันใช้เงินเต็มจำนวนในชุดข้อมูลตัวอย่างนี้ ถ้าฉันใส่จุดทศนิยมสองจุดเพื่อสะท้อนเซ็นต์เอฟเฟกต์การบีบอัดจะลดลงเหลือ 80% ของขนาดไฟล์ต้นฉบับ

SSAS Tabular

โมเดล Power Pivot ยังสามารถปรับขนาดให้ใช้ได้กับทั้งองค์กร สมมติว่าคุณสร้างโมเดล Power Pivot ที่เริ่มมีผู้ใช้จำนวนมากในองค์กรหรือข้อมูลเพิ่มขึ้นเป็น 10 ล้านแถวหรือทั้งสองอย่าง ณ จุดนี้คุณอาจไม่ต้องการให้ผู้ใช้สามสิบคนรีเฟรชโมเดลหรือทำการเปลี่ยนแปลง โมเดลสามารถแปลงเป็น SSAS Tabular ได้อย่างราบรื่น ตารางและความสัมพันธ์ทั้งหมดจะยังคงอยู่ แต่ตอนนี้คุณสามารถควบคุมความถี่ในการรีเฟรชกำหนดบทบาท (เช่นอ่านอย่างเดียวอ่านและประมวลผล) ให้กับผู้ใช้ต่างๆและปรับใช้เฉพาะส่วนหน้าของ Excel ขนาดเล็กซึ่งเชื่อมโยงไปยังโมเดลตาราง ผลลัพธ์คือผู้ใช้ของคุณสามารถเข้าถึงโมเดลตารางที่ปรับใช้แล้วด้วยสมุดงานขนาดเล็ก แต่ไม่มีสิทธิ์เข้าถึงสูตรและการวัดผล

4) การแสดงข้อมูลและการวิเคราะห์

สูตร CUBE

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

สูตร CUBE ที่ฝังอยู่

เราจะเติมตัวเลขยอดขายโดยไม่สร้าง PivotTables ได้อย่างไรหากยอดขายทั้งหมดของเราอยู่ใน Power Pivot for Excel ใช้สูตร CUBE! เราสามารถเขียนสูตร CUBE ภายในเซลล์ Excel ใดก็ได้และจะทำการคำนวณโดยใช้โมเดล Power Pivot ที่เราได้สร้างไว้แล้ว

ตัวอย่างเช่นพิมพ์สูตรต่อไปนี้ในเซลล์ภายใต้“ ยอดขายรวมปี 2016:”

SumExpPctTotal

ส่วนแรกของสูตรที่เน้นด้วยสีเหลืองหมายถึงชื่อของโมเดล Power Pivot โดยทั่วไปมักจะเป็น ThisWorkbookDataModel สำหรับ Power Pivot for Excel เวอร์ชันใหม่กว่า ส่วนที่เป็นสีเขียวกำหนดว่าเราต้องการใช้การวัดยอดขายทั้งหมด ส่วนที่เป็นสีน้ำเงินสั่งให้ Excel กรองเฉพาะแถวที่มี Sales Date ซึ่งมีปีเท่ากับ 2016

เบื้องหลัง Power Pivot ได้สร้างคิวบ์ Online Analytical Processing (OLAP) ด้วยข้อมูลคอลัมน์จากการคำนวณและหน่วยวัด การออกแบบนี้ช่วยให้ผู้ใช้ Excel สามารถเข้าถึงข้อมูลได้โดยดึงข้อมูลโดยตรงด้วยฟังก์ชัน CUBE ด้วยการใช้สูตร CUBE ฉันสามารถสร้างงบการเงินแบบเต็มซึ่งเป็นไปตามรูปแบบที่กำหนดไว้ล่วงหน้า ความสามารถนี้เป็นหนึ่งในจุดเด่นของการใช้ Power Pivot สำหรับ Excel สำหรับการวิเคราะห์ทางการเงิน

Power BI

ข้อดีอีกอย่างของ Power Pivot สำหรับ Excel คือคุณสามารถใช้สมุดงาน Power Pivot ที่คุณสร้างและแปลงเป็นแบบจำลอง Power BI ได้อย่างรวดเร็ว ด้วยการนำเข้าสมุดงาน Excel ลงในแอป Power BI Desktop หรือ Power BI Online โดยตรงคุณสามารถวิเคราะห์แสดงภาพและแชร์ข้อมูลของคุณกับใครก็ได้ในองค์กรของคุณ โดยพื้นฐานแล้ว Power BI คือ Power Pivot, PowerQuery และ SharePoint ทั้งหมดรวมกันเป็นหนึ่งเดียว ด้านล่างนี้ฉันได้สร้างแดชบอร์ดโดยการนำเข้าสมุดงาน Power Pivot for Excel ก่อนหน้านี้ไปยังแอปพลิเคชัน Power BI บนเดสก์ท็อป สังเกตว่าอินเทอร์เฟซแบบโต้ตอบเป็นอย่างไร:

Power BI

สิ่งที่ดีอย่างหนึ่งเกี่ยวกับ Power BI คือการถามตอบด้วยภาษาธรรมชาติ เพื่อสาธิตฉันอัปโหลดแบบจำลอง Power BI ไปยังบัญชี Power BI ออนไลน์ของฉัน จากเว็บไซต์ฉันสามารถถามคำถามและ Power BI จะสร้างการวิเคราะห์ที่เหมาะสมเมื่อฉันพิมพ์:

ค คอร์ปอเรชั่น หรือ ส คอร์ปอเรชั่น

ถาม - ตอบภาษาธรรมชาติ

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

ข้อดีอีกอย่างของ Power BI คือนักพัฒนาที่ Microsoft ปล่อยอัปเดตไปเรื่อย ๆ คุณลักษณะใหม่ที่ผู้ใช้ร้องขอจำนวนมากจะถูกผลักออกทุกเดือน เหนือสิ่งอื่นใดคือการเปลี่ยนจาก Power Pivot for Excel อย่างราบรื่น ดังนั้นเวลาที่คุณลงทุนในการเรียนรู้สูตร DAX สามารถปรับใช้ใน Power BI ได้! สำหรับนักวิเคราะห์ที่ต้องการแบ่งปันการวิเคราะห์ของเขากับผู้ใช้จำนวนมากบนอุปกรณ์ที่แตกต่างกัน Power BI อาจคุ้มค่ากับการสำรวจ

ปฏิบัติที่ดีที่สุด

เมื่อคุณเริ่มต้นแล้วมีแนวทางปฏิบัติที่ดีที่สุดบางประการที่คุณควรปฏิบัติตาม

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

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

สุดท้ายนี้ฉันขอแนะนำให้ใช้ชื่อภาษาอังกฤษธรรมดาสำหรับการวัด อันนี้ฉันใช้เวลานานในการรับเลี้ยงบุตรบุญธรรม ฉันใช้เวลาสองสามปีแรกในการสร้างชื่อเช่น Expense Line Item as Percent of Total Expenses แต่เมื่อคนอื่นเริ่มใช้สมุดงานเดียวกันฉันก็มีเรื่องที่ต้องทำมากมาย ตอนนี้เมื่อฉันเริ่มสมุดงานใหม่ฉันใช้ชื่อหน่วยวัดเช่น

|_+_|

แม้ว่าชื่อจะยาวกว่า แต่คนอื่นจะใช้ง่ายกว่ามาก

กรณีการใช้งานจริง

ในบทความนี้ฉันได้นำเสนอวิธีการเพียงไม่กี่วิธีที่ Power Pivot สำหรับ Excel ช่วยให้คุณก้าวไปอีกขั้นที่สำคัญนอกเหนือจาก Excel วานิลลาธรรมดา ฉันคิดว่ามันจะมีประโยชน์ในการเน้นกรณีการใช้งานในโลกแห่งความเป็นจริงซึ่งฉันพบว่า Power Pivot สำหรับ Excel มีประโยชน์อย่างยิ่ง

นี่คือบางส่วน:

• วิเคราะห์ประสิทธิภาพของสินทรัพย์จำนวนมากในช่วงเวลาต่างๆ: เนื่องจาก Power Pivot สำหรับ Excel ช่วยให้เราสามารถกำหนดมาตรการที่เปรียบเทียบช่วงเวลากับช่วงก่อนหน้าได้เราจึงสามารถมีประสิทธิภาพแบบไตรมาสต่อไตรมาสปีต่อปีและแบบเดือนต่อเดือนได้อย่างรวดเร็วโดย เขียนเพียงไม่กี่มาตรการ
• สรุปข้อมูลการบัญชีโดยใช้ระดับการรวมที่กำหนดเอง: ด้วยการระบุรายการโฆษณาบัญชีแยกประเภททั่วไปตามชื่อหมวดหมู่และงบการเงินทำให้สามารถสร้างรายงานที่มีรายการโฆษณาที่เหมาะสมได้อย่างรวดเร็ว
• เครือข่ายสามารถระบุยอดขายในสาขาเดียวกันได้: การใช้ตารางที่แสดงแผนที่เมื่อร้านค้าออนไลน์สามารถเปรียบเทียบผลลัพธ์ทางการเงินบนพื้นฐานของร้านค้าเดียวกันได้
• ระบุผู้ที่มีผลงานสูงและต่ำในการขาย: สามารถสร้าง PivotTables ที่เน้น SKU ห้าอันดับแรกและ SKU ห้าอันดับล่างตามยอดขาย, อัตรากำไรขั้นต้น, กรอบเวลาในการผลิต ฯลฯ
• ผู้ค้าปลีกสามารถกำหนดตารางปฏิทินที่ใช้การกำหนดค่า 4-4-5: ด้วยการใช้ตารางวันที่ที่กำหนดเองผู้ค้าปลีกสามารถกำหนดแต่ละวันให้กับเดือน 4-4-5 ที่เฉพาะเจาะจงได้อย่างง่ายดายจากนั้นสามารถรวมผลการขายรายวันเป็นเดือนที่เกี่ยวข้องได้

ตั้งแต่สเปรดชีต Clunky ไปจนถึงสมุดงานสมัยใหม่

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

ด้วยการใช้ฟังก์ชัน CUBE Power Pivot สำหรับ Excel จะผสมผสานเข้ากับเวิร์กบุ๊ก Excel ที่มีอยู่ของคุณได้อย่างราบรื่น การเพิ่มประสิทธิภาพการคำนวณไม่สามารถมองข้ามได้ สมมติว่าความเร็วในการประมวลผลเร็วขึ้น 20% ซึ่งเป็นแบบอนุรักษ์นิยมนักวิเคราะห์ทางการเงินที่ใช้เวลาหกชั่วโมงต่อวันใน Excel สามารถประหยัดเวลาได้ 300 ชั่วโมงต่อปี!

นอกจากนี้ตอนนี้เราสามารถวิเคราะห์ชุดข้อมูลที่มีขนาดใหญ่กว่าที่เคยทำได้ด้วย Excel แบบเดิมของเรา ด้วยโมเดลที่ออกแบบมาอย่างมีประสิทธิภาพเราสามารถมีข้อมูลได้มากถึง 10 เท่าจากที่ก่อนหน้านี้ได้รับอนุญาตใน Excel แบบเดิมในขณะที่ยังคงความคล่องตัวในการวิเคราะห์อย่างรวดเร็ว ด้วยความสามารถในการแปลงแบบจำลองจาก Power Pivot เป็น SSAS Tabular จำนวนข้อมูลที่สามารถประมวลผลได้คือ 100–1,000 เท่าของสิ่งที่เราทำได้ใน Excel

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

หากคุณสนใจที่จะทดลองใช้ Power Pivot สำหรับ Excel ด้านล่างนี้คือสื่อที่มีประโยชน์ในการเริ่มต้นใช้งาน

การอ้างอิงและแนวทางที่เป็นประโยชน์

Collie, R. , & Singh, A. (2016). Power Pivot และ Power BI: คู่มือผู้ใช้ Excel สำหรับ DAX, Power Query, Power BI & Power Pivot ใน Excel 2010-2016 สหรัฐอเมริกา: Holy Macro! หนังสือ.

Ferrari, A. , & Russo, M. (2015). คำแนะนำขั้นสุดท้ายสำหรับ DAX: ระบบธุรกิจอัจฉริยะด้วย Microsoft Excel, SQL Server Analysis Services และ Power BI สหรัฐอเมริกา: Microsoft Press, USA

ทำความเข้าใจพื้นฐาน

ตาราง Power Pivot คืออะไร?

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

ฉันจะสร้างตาราง Power Pivot ได้อย่างไร

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

ฉันจะสร้างแผนภูมิ Power Pivot ได้อย่างไร

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