ประโยชน์ของ Pivot ตามตัวแบบข้อมูล

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

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

อย่างไรก็ตาม ก่อนที่จะพิจารณา “ขนมปัง” เหล่านี้อย่างใกล้ชิด ก่อนอื่นเรามาทำความเข้าใจว่าที่จริงแล้ว Data Model นี้คืออะไร?

ตัวแบบข้อมูลคืออะไร

ตัวแบบข้อมูล (ย่อมาจาก MD หรือ DM = Data Model) เป็นพื้นที่พิเศษภายในไฟล์ Excel ที่คุณสามารถจัดเก็บข้อมูลแบบตารางได้ - ตารางอย่างน้อยหนึ่งตารางที่เชื่อมโยงถึงกัน หากต้องการ อันที่จริง นี่คือฐานข้อมูลขนาดเล็ก (OLAP cube) ที่ฝังอยู่ภายในเวิร์กบุ๊ก Excel เมื่อเทียบกับการจัดเก็บข้อมูลแบบคลาสสิกในรูปแบบของตารางปกติ (หรือสมาร์ท) บนแผ่นงานของ Excel ตัวแบบข้อมูลมีข้อดีที่สำคัญหลายประการ:

• ตารางได้ถึง 2 พันล้านเส้นและแผ่นงาน Excel สามารถใส่ได้มากกว่า 1 ล้านเล็กน้อย
• แม้จะมีขนาดมหึมา การประมวลผลของตารางดังกล่าว (การกรอง การเรียงลำดับ การคำนวณ ข้อมูลสรุปสิ่งปลูกสร้าง ฯลฯ) ก็ยังดำเนินการได้ เร็วมาก เร็วกว่า Excel เองมาก
• ด้วยข้อมูลในแบบจำลอง คุณสามารถทำการคำนวณเพิ่มเติม (หากต้องการ ซับซ้อนมาก) โดยใช้ ภาษา DAX ในตัว.
  
• ข้อมูลทั้งหมดที่โหลดลงใน Data Model นั้นดีมาก อัดแน่น โดยใช้ตัวเก็บถาวรในตัวแบบพิเศษและเพิ่มขนาดของไฟล์ Excel ดั้งเดิมในระดับปานกลาง
  

โมเดลได้รับการจัดการและคำนวณโดย Add-in พิเศษที่มีอยู่ใน Microsoft Excel – PowerPivotที่ฉันเขียนไปแล้ว หากต้องการเปิดใช้งานบนแท็บ ผู้พัฒนา คลิก ส่วนเสริม COM (ผู้พัฒนา — ส่วนเสริม COM) และทำเครื่องหมายในช่องที่เหมาะสม:

ถ้าแท็บ ผู้พัฒนา (ผู้พัฒนา)มองไม่เห็นบนริบบิ้น คุณสามารถเปิดผ่าน ไฟล์ – ตัวเลือก – การตั้งค่าริบบอน (ไฟล์ — ตัวเลือก — กำหนด Ribbon เอง). หากในหน้าต่างที่แสดงด้านบนในรายการ Add-in ของ COM คุณไม่มี Power Pivot แสดงว่าไม่มี Power Pivot อยู่ในเวอร์ชันของ Microsoft Office 🙁

บนแท็บ Power Pivot ที่ปรากฏขึ้น จะมีปุ่มสีเขียวอ่อนขนาดใหญ่ การจัดการ (จัดการ)การคลิกที่จะเปิดหน้าต่าง Power Pivot ที่ด้านบนของ Excel ซึ่งเราจะเห็นเนื้อหาของตัวแบบข้อมูลของหนังสือเล่มปัจจุบัน:

หมายเหตุสำคัญระหว่างทาง: เวิร์กบุ๊ก Excel สามารถมีตัวแบบข้อมูลได้เพียงตัวเดียว

โหลดตารางลงใน Data Model

ในการโหลดข้อมูลลงในโมเดล ขั้นแรกเราจะเปลี่ยนตารางให้เป็นแป้นพิมพ์ลัด "อัจฉริยะ" แบบไดนามิก Ctrl+T และตั้งชื่อที่เป็นมิตรบนแท็บ นวกรรมิก (ออกแบบ). นี่เป็นขั้นตอนที่จำเป็น

จากนั้น คุณสามารถใช้วิธีใดก็ได้จากสามวิธีในการเลือก:

• กดปุ่ม เพิ่มไปยังรุ่น (เพิ่มไปยังโมเดลข้อมูล) แถบ PowerPivot แถบ หน้าแรก (บ้าน).
  
• การเลือกทีม แทรก – PivotTable (แทรก — ตารางหมุน) และเปิดกล่องกาเครื่องหมาย เพิ่มข้อมูลนี้ในโมเดลข้อมูล (เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล). ในกรณีนี้ ตามข้อมูลที่โหลดเข้าสู่โมเดล ตารางเดือยจะถูกสร้างขึ้นทันทีเช่นกัน
  
• บนแท็บขั้นสูง ข้อมูล (วันที่) คลิกที่ปุ่ม จากตาราง/ช่วง (จากตาราง/ช่วง)เพื่อโหลดตารางของเราลงในตัวแก้ไข Power Query เส้นทางนี้ยาวที่สุด แต่หากต้องการ คุณสามารถดำเนินการล้างข้อมูลเพิ่มเติม การแก้ไข และการแปลงทุกประเภทได้ที่นี่ ซึ่ง Power Query มีความแข็งแกร่งมาก

  จากนั้นข้อมูลที่หวีจะถูกอัปโหลดไปยัง Model โดยคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…). ในหน้าต่างที่เปิดขึ้น ให้เลือกตัวเลือก เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น) และที่สำคัญ ติ๊ก เพิ่มข้อมูลนี้ในโมเดลข้อมูล (เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล).
  

เราสร้างบทสรุปของ Data Model

ในการสร้างแบบจำลองข้อมูลสรุป คุณสามารถใช้วิธีใดก็ได้จากสามวิธี:

• กดปุ่ม ตารางสรุป (ตารางเดือย) ในหน้าต่าง Power Pivot
• เลือกคำสั่งใน Excel แทรก – PivotTable และเปลี่ยนเป็นโหมด ใช้แบบจำลองข้อมูลของหนังสือเล่มนี้ (แทรก — Pivot Table — ใช้ตัวแบบข้อมูลของเวิร์กบุ๊กนี้).
• การเลือกทีม แทรก – PivotTable (แทรก — ตารางหมุน) และเปิดกล่องกาเครื่องหมาย เพิ่มข้อมูลนี้ในโมเดลข้อมูล (เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล). ตาราง "อัจฉริยะ" ปัจจุบันจะถูกโหลดลงในโมเดล และตารางสรุปจะถูกสร้างขึ้นสำหรับโมเดลทั้งหมด

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

ประโยชน์ 1: ความสัมพันธ์ระหว่างตารางโดยไม่ต้องใช้สูตร

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

ในกรณีของบทสรุปของ Data Model ทุกอย่างง่ายกว่ามาก แค่ตั้งค่าความสัมพันธ์ระหว่างตารางในหน้าต่าง Power Pivot ครั้งเดียวก็เพียงพอแล้ว เท่านี้ก็เสร็จเรียบร้อย ในการทำเช่นนี้บนแท็บ PowerPivot กดปุ่ม การจัดการ (จัดการ) จากนั้นในหน้าต่างที่ปรากฏขึ้น – ปุ่ม มุมมองแผนภูมิ (มุมมองแผนภาพ). มันยังคงลากชื่อคอลัมน์ (คีย์) ทั่วไป (ฟิลด์) ระหว่างตารางเพื่อสร้างลิงก์:

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

ประโยชน์ 2: นับค่าที่ไม่ซ้ำกัน

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

คลิกขวาที่ฟิลด์ – command ตัวเลือกช่องค่า และบนแท็บ การดำเนินการ Choose จำนวนองค์ประกอบที่แตกต่างกัน (จำนวนที่แตกต่าง):

ประโยชน์ 3: สูตร DAX แบบกำหนดเอง

บางครั้งคุณต้องทำการคำนวณเพิ่มเติมหลายอย่างในตารางสาระสำคัญ ในการสรุปทั่วไป ทำได้โดยใช้ฟิลด์และออบเจ็กต์จากการคำนวณ ในขณะที่ข้อมูลสรุปโมเดลข้อมูลใช้การวัดในภาษา DAX พิเศษ (DAX = Data Analysis Expressions)

ในการสร้างการวัด ให้เลือกบนแท็บ PowerPivot คำสั่ง มาตรการ – สร้างการวัด (มาตรการ — มาตรการใหม่) หรือเพียงคลิกขวาที่ตารางในรายการ Pivot Fields แล้วเลือก เพิ่มหน่วยวัด (เพิ่มหน่วยวัด) ในเมนูบริบท:

ในหน้าต่างที่เปิดขึ้น ให้ตั้งค่า:

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

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

ประโยชน์ 4: ลำดับชั้นของฟิลด์ที่กำหนดเอง

บ่อยครั้ง เมื่อสร้างรายงานมาตรฐาน คุณจะต้องรวมเขตข้อมูลเดียวกันลงในตารางสาระสำคัญตามลำดับที่กำหนด ตัวอย่างเช่น ปี-ไตรมาส-เดือน-วัน,หรือ Category-สินค้า,หรือ ประเทศ-เมือง-ลูกค้า ฯลฯ ในการสรุปแบบจำลองข้อมูล ปัญหานี้แก้ไขได้ง่ายโดยการสร้างของคุณเอง ลำดับชั้น — ชุดฟิลด์ที่กำหนดเอง

ในหน้าต่าง Power Pivot ให้สลับไปที่โหมดแผนภูมิด้วยปุ่ม มุมมองแผนภูมิ แถบ หน้าแรก (หน้าแรก — มุมมองไดอะแกรม), เลือกด้วย Ctrl ช่องที่ต้องการและคลิกขวาที่ช่องเหล่านั้น เมนูบริบทจะมีคำสั่ง สร้างลำดับชั้น (สร้างลำดับชั้น):

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

ประโยชน์ 5: ลายฉลุที่กำหนดเอง

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

ในการทำเช่นนี้บนแท็บ การวิเคราะห์ตารางสาระสำคัญ ในรายการดรอปดาวน์ ฟิลด์ ไอเท็ม และเซ็ต มีคำสั่งที่สอดคล้องกัน (วิเคราะห์ — ฟิลด์, ฉันtems & Sets — สร้างชุดตามรายการแถว/คอลัมน์):

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

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

ประโยชน์ 6: เลือกซ่อนตารางและคอลัมน์

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

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

ประโยชน์ที่ 7 การเจาะลึกขั้นสูง

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

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

หลังจากนั้น ค่าปัจจุบัน (รุ่น = Explorer) จะเข้าสู่พื้นที่ตัวกรอง และข้อมูลสรุปจะถูกสร้างขึ้นโดยสำนักงาน:

แน่นอน ขั้นตอนดังกล่าวสามารถทำซ้ำได้หลายครั้ง โดยเจาะลึกข้อมูลของคุณไปในทิศทางที่คุณสนใจอย่างสม่ำเสมอ

ประโยชน์ 8: แปลงฟังก์ชัน Pivot เป็น Cube

หากคุณเลือกเซลล์ใดๆ ในการสรุปสำหรับตัวแบบข้อมูล จากนั้นเลือกบนแท็บ การวิเคราะห์ตารางสาระสำคัญ คำสั่ง เครื่องมือ OLAP – แปลงเป็นสูตร (วิเคราะห์ — เครื่องมือ OLAP — แปลงเป็นสูตร)จากนั้นข้อมูลสรุปทั้งหมดจะถูกแปลงเป็นสูตรโดยอัตโนมัติ ตอนนี้ ค่าของฟิลด์ในพื้นที่แถว-คอลัมน์และผลลัพธ์ในพื้นที่ค่าจะถูกดึงมาจาก Data Model โดยใช้ฟังก์ชันคิวบ์พิเศษ: CUBEVALUE และ CUBEMEMBER:

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

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

• การวิเคราะห์ตามแผนในตารางสาระสำคัญด้วย Power Pivot และ Power Query
• ตาราง Pivot พร้อมส่วนหัวแบบหลายบรรทัด
• สร้างฐานข้อมูลใน Excel โดยใช้ Power Pivot