สร้างแดชบอร์ด Excel ขั้นสูงด้วย Power Query, Power Pivot และ VBA – จากข้อมูลสู่ข้อมูลเชิงลึก

ในบทช่วยสอนนี้ เราจะแสดงวิธีสร้างแดชบอร์ด Excel ขั้นสูงโดยใช้ Power Query, Power Pivot และ VBA

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

• การขาย – มีข้อมูลธุรกรรม
• ผลิตภัณฑ์ – รายละเอียดผลิตภัณฑ์และหมวดหมู่
• ลูกค้า – ข้อมูลลูกค้า
• ภูมิภาค – ข้อมูลทางภูมิศาสตร์

ขั้นตอนที่ 1:ใช้ Power Query สำหรับการนำเข้าข้อมูล

นำเข้าข้อมูล:

• ไปที่ ข้อมูล แท็บ>> เลือก รับข้อมูล>> เลือก จากไฟล์>> เลือกจากข้อความ/CSV .
• ไปที่ sales_data.txt ไฟล์>> คลิก นำเข้า .

• เมื่อ Power Query Editor เปิดขึ้น ให้ตรวจสอบข้อมูลและทำการแปลงเหล่านี้:
• เปลี่ยนประเภทข้อมูล
• คลิกขวาที่คอลัมน์ใดก็ได้>> เลือก เปลี่ยนประเภทข้อมูล>> เลือก ประเภทข้อมูล .
  • วันที่สั่งซื้อ ถึง วันที่ .
  • ปริมาณ เป็น จำนวนเต็ม .
  • ราคาต่อหน่วย และ ส่วนลด เป็น เลขทศนิยม .

• ลบแถวที่ซ้ำกันโดยใช้ ลบรายการที่ซ้ำกัน .
• ทำซ้ำขั้นตอนการนำเข้าสำหรับ Products.csv, Customers.csv และ Dates.csv โดยใช้การแปลงประเภทข้อมูลที่เหมาะสม

แปลงข้อมูลด้วย Power Query:

มาปรับปรุงข้อมูลการขายของเราด้วยการทำงานโดยใช้ Power Query กันดีกว่า

เพิ่มคอลัมน์จากการคำนวณ:

• ในตัวแก้ไข Power Query เลือกข้อมูลการขาย
• ไปที่ เพิ่มคอลัมน์ แท็บ>> เลือก คอลัมน์ที่กำหนดเอง .
• ตั้งชื่อว่า:รายได้ .
• แทรกสูตรต่อไปนี้

= [Quantity] * [UnitPrice] * (1-[DiscountRate])

• คลิก ตกลง .

• เพิ่มคอลัมน์ที่กำหนดเองอื่น
• ตั้งชื่อว่า:กำไร .
• แทรกสูตรต่อไปนี้

Profit = [Revenue] - ([Quantity] * [UnitCost])

• คลิก ตกลง .
• เราจะต้องรวมกับตารางผลิตภัณฑ์เพื่อรับต้นทุน

รวมตารางเพื่อข้อมูลเชิงลึกเพิ่มเติม:

• ในตัวแก้ไข Power Query พร้อมข้อมูลการขายที่เปิดอยู่
• ไปที่หน้าแรก แท็บ>> คลิก รวมข้อความค้นหา จากริบบิ้น
  • เลือก รหัสผลิตภัณฑ์ จากตารางการขาย
  • เลือก ผลิตภัณฑ์ ตารางและเข้าร่วมบน ProductID .
  • คลิก ตกลง .

• คลิกที่ ขยายตาราง option>> เลือกเฉพาะ UnitCost คอลัมน์ที่จะนำเข้า
• คลิก ตกลง .

• ตอนนี้ ลาก กำไร คอลัมน์ใต้ ผลิตภัณฑ์ที่ขยาย ขั้นตอน
• คุณจะได้รับจำนวนกำไร

• เมื่อคุณแปลงข้อมูลเสร็จแล้ว
• คลิก ปิด &โหลดไปที่...

• ในส่วนนำเข้าข้อมูล กล่อง;
  • เลือก สร้างการเชื่อมต่อเท่านั้น .
  • เลือก เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล สำหรับทั้งสี่ตาราง
  • คลิก ตกลง .

ขั้นตอนที่ 2:สร้างโมเดลข้อมูลด้วย Power Pivot

เปิด Power Pivot:

หากไม่มี Power Pivot ใน Ribbon ให้เปิดใช้งาน

• ไปที่ไฟล์ แท็บ>> เลือก ตัวเลือก>> เลือก ส่วนเสริม .
• ในจัดการ กล่อง>> เลือก COM Add-in>> เลือก ไป .

• เลือก Microsoft Power Pivot สำหรับ Excel .
• คลิก ตกลง .

• ไปที่ Power Pivot แท็บ>> เลือก จัดการ .
• จะเปิดข้อมูลที่นำเข้าจาก Power Query

สร้างความสัมพันธ์ :

• ไปที่หน้าแรก แท็บ>> เลือก มุมมองไดอะแกรม .
  • ลาก การขาย[ProductID] ไปยัง ผลิตภัณฑ์[ProductID] .
  • ลาก การขาย[รหัสลูกค้า] ถึง ลูกค้า[รหัสลูกค้า] .
  • ลาก ลูกค้า[RegionID] ไปยัง ภูมิภาค [RegionID] .

• หรือไปที่ การออกแบบ แท็บ>> เลือก สร้างความสัมพันธ์ .
• จากนั้นเลือกคอลัมน์ที่ตรงกัน

สร้างการวัดจากการคำนวณ:

• ใน Power Pivot คลิกบนตารางการขาย
• ไปที่ หน้าแรก แท็บ>> เลือก การวัด >> คลิก การวัดใหม่ .
• หรือไปที่หน้าแรก แท็บ>> เลือก พื้นที่การคำนวณ .
• แทรกการวัดที่คำนวณแล้วที่นั่น:

• รายได้ทั้งหมด:

Total Revenue := SUM(Sales[Revenue])

• กำไรทั้งหมด:

Total Profit := SUM(Sales[Profit])

• อัตรากำไร:

Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)

• คำสั่งซื้อทั้งหมด:

Total Orders:=COUNTA(Sales[OrderID])

• มูลค่าการสั่งซื้อเฉลี่ย:

Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)

• รายได้ YTD:

YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))

• รายได้ของปีก่อน:

Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))

• การเติบโตแบบปีต่อปี:

YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)

ขั้นตอนที่ 3:ใช้ Pivot Table เพื่อสร้างส่วนประกอบแดชบอร์ด

สร้าง PivotTable

• ไปที่ แทรก แท็บ>> เลือก PivotTable >> เลือก จากแบบจำลองข้อมูล .
• ใน Power Pivot ไปที่ หน้าแรก แท็บ>> เลือก ตาราง Pivot .

• ในสร้าง PivotTable กล่อง;
  • เลือกแผ่นงานใหม่
  • คลิก ตกลง .

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

แผนภูมิและการแสดงภาพ

แผนภูมิแนวโน้มรายได้:

• สร้าง PivotTable จากตัวแบบข้อมูล
  • แถว:ยอดขาย[วันที่สั่งซื้อ[เดือน]]
  • ค่า: รายได้รวม
• ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก แผนภูมิคอลัมน์แบบกลุ่ม .

แผนภูมิผลิตภัณฑ์ยอดนิยม:

• สร้าง PivotTable จากตัวแบบข้อมูล
  • แถว:ผลิตภัณฑ์[ชื่อผลิตภัณฑ์]
  • ค่า:รายได้รวม
• จัดเรียงตาม รายได้รวม จากมากไปน้อย กรองไปที่ 10 อันดับแรก

• ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก แผนภูมิแท่งแบบกลุ่ม .

ประสิทธิภาพของหมวดหมู่ :

• สร้าง PivotTable จากตัวแบบข้อมูล
  • แถว:ผลิตภัณฑ์[หมวดหมู่]
  • มูลค่า:รายได้รวม, กำไรทั้งหมด, อัตรากำไร .
• ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก คอมโบ>> เลือก คอลัมน์แบบคลัสเตอร์ – เส้นบนแกนทุติยภูมิ .

ขั้นตอนที่ 4:แทรกองค์ประกอบเชิงโต้ตอบ

แทรกตัวแบ่งส่วนข้อมูลและไทม์ไลน์:

สร้างตัวแบ่งส่วนผลิตภัณฑ์และภูมิภาค:

• ไปที่ PivotAnalyze แท็บ>> เลือก แทรกตัวแบ่งส่วนข้อมูล .
• เลือก ผลิตภัณฑ์[หมวดหมู่] และ ลูกค้า[ภูมิภาค] .
• คลิก ตกลง .

• จัดรูปแบบให้ตรงกับการออกแบบแดชบอร์ดของคุณ

เพิ่มไทม์ไลน์สำหรับการกรองวันที่:

• ไปที่ PivotAnalyze แท็บ>> เลือก แทรกตัวแบ่งส่วนข้อมูล .
• เลือก ยอดขาย[วันที่สั่งซื้อ] .
• คลิก ตกลง .

• ตำแหน่งเหนือแผนภูมิของคุณ
• จัดรูปแบบให้ตรงกับสไตล์แดชบอร์ดของคุณ

เชื่อมต่อตัวแบ่งส่วนข้อมูลทั้งหมดกับ PivotTable :

• คลิกขวาที่ตัวแบ่งส่วนข้อมูลแต่ละตัว>> เลือก รายงานการเชื่อมต่อ .

• ตรวจสอบ PivotTable ทั้งหมด เพื่อให้แน่ใจว่าตัวกรองมีผลทั่วโลก
• คลิก ตกลง .

ขั้นตอนที่ 5:อัตโนมัติและปรับปรุงการโต้ตอบกับ VBA

เราจะใช้ VBA เพื่อทำให้แดชบอร์ดเป็นแบบไดนามิกและง่ายต่อการใช้งาน

ตัวอย่างที่ 1:ปุ่มรีเฟรชข้อมูล

• ไปที่ นักพัฒนา แท็บ>> เลือก แทรก >> เลือก ปุ่ม .
• เปลี่ยนชื่อปุ่มเป็น รีเฟรชข้อมูล .
• ปุ่มคลิกขวา>> เลือก กำหนดมาโคร>> เลือกใหม่ .

• คัดลอกและวางโค้ดต่อไปนี้

รหัส VBA:

Sub RefreshDashboard()
 ThisWorkbook.RefreshAll
 MsgBox "Dashboard data refreshed!", vbInformation
End Sub

ตัวอย่างที่ 2:ปุ่มรีเซ็ตแดชบอร์ด

• ไปที่ นักพัฒนา แท็บ>> เลือก แทรก >> เลือก ปุ่ม .
• เปลี่ยนชื่อปุ่มเป็น รีเซ็ตแดชบอร์ด .
• ปุ่มคลิกขวา>> เลือก กำหนดมาโคร>> เลือกใหม่ .
• คัดลอกและวางโค้ดต่อไปนี้

รหัส VBA:

Sub ResetDashboardFilter()
 Dim ws As Worksheet
 Dim slicer As slicerCache
 Dim pivotTable As pivotTable
 
 ' Clear all slicer caches
 For Each slicer In ActiveWorkbook.SlicerCaches
 slicer.ClearAllFilters
 Next slicer
 
 ' Reset any timelines (already using SlicerCaches)
 For Each slicer In ActiveWorkbook.SlicerCaches
 If slicer.SourceType = xlTimeline Then
 slicer.ClearAllFilters
 End If
 Next slicer
 
 ' Refresh pivot tables
 For Each ws In ActiveWorkbook.Worksheets
 For Each pivotTable In ws.PivotTables
 pivotTable.RefreshTable
 Next pivotTable
 Next ws
 
 MsgBox "Dashboard filters have been reset!", vbInformation, "Reset Filters"
End Sub

ขั้นตอนที่ 6:สร้างเค้าโครงแดชบอร์ด

สร้างแผ่นงานใหม่ชื่อแดชบอร์ด

• ตั้งค่าโครงสร้างแดชบอร์ด:
  • ชื่อแดชบอร์ดและตัวกรองวันที่
  • ส่วน KPI (รายได้ กำไร อัตรากำไรขั้นต้น การเติบโต)
  • แผนภูมิ (แนวโน้มการขาย ผลิตภัณฑ์ยอดนิยม ประสิทธิภาพระดับภูมิภาค)

• คุณสามารถสร้างตารางสรุปสำหรับตารางข้อมูลและการวิเคราะห์โดยละเอียดได้

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

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

ขั้นตอนที่ 7:ทดสอบและแก้ไขปัญหา

ทดสอบองค์ประกอบเชิงโต้ตอบทั้งหมด:

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

ทดสอบตัวกรองข้อมูลการรีเซ็ต:

• คลิกที่ รีเซ็ตแดชบอร์ด .
• ข้อความจะปรากฏขึ้น “ตัวกรองแดชบอร์ดถูกรีเซ็ตแล้ว” .
• คลิก ตกลง .
• ตัวกรองทั้งหมดจะถูกลบออก และคุณจะได้รับแดชบอร์ดใหม่

ทดสอบการรีเฟรชข้อมูล :

• แก้ไขแหล่งข้อมูล
• คลิก รีเฟรชข้อมูล .
• ตรวจสอบให้แน่ใจว่าการคำนวณทั้งหมดอัปเดตอย่างถูกต้อง
• ตรวจสอบการเชื่อมต่อหรือสูตรที่เสียหาย

ดาวน์โหลดแบบฝึกหัด

บทสรุป

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