home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> ซอฟต์แวร์ >> Office

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

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

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

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

  • ไปที่แท็บ Data >> เลือก Get Data >> From File >> From Text/CSV
  • ไปที่ไฟล์ sales_data.txt >> คลิก Import
  • เมื่อ Power Query Editor เปิด ให้ตรวจสอบข้อมูลและทำการแปลง:
    • เปลี่ยนชนิดข้อมูล: คลิกขวาที่คอลัมน์ >> Change Data Type
    • OrderDate เป็น Date
    • Quantity เป็น Whole Number
    • UnitPrice และ Discount เป็น Decimal Number
  • ลบแถวซ้ำด้วย Remove Duplicates
  • ทำซ้ำกระบวนการนำเข้าสำหรับ Products.csv, Customers.csv, และ Dates.csv พร้อมการแปลงชนิดข้อมูลที่เหมาะสม

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

เพิ่มคอลัมน์คำนวณ:

  • ใน Power Query Editor เลือกข้อมูล Sales
  • ไปที่แท็บ Add Column >> Custom Column
  • ตั้งชื่อ: Revenue
  • สูตร: = [Quantity] * [UnitPrice] * (1-[DiscountRate])
  • เพิ่ม Custom Column อีกอัน: Profit
  • สูตร: = [Revenue] - ([Quantity] * [UnitCost])
  • ต้อง Merge กับตาราง Products เพื่อดึง Cost มา

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

  • ใน Power Query Editor พร้อมข้อมูล Sales
  • แท็บ Home >> Merge Queries
  • เลือก ProductID จากตาราง Sales
  • เลือกตาราง Products และ join ที่ ProductID
  • คลิก Expand Table เลือกเฉพาะคอลัมน์ UnitCost
  • ย้ายคอลัมน์ Profit ใต้ขั้นตอน Expanded Products

เมื่อเสร็จสิ้นการแปลงข้อมูล:

  • คลิก Close & Load To…
  • ในกล่อง Import Data: เลือก Only Create Connection และ Add this data to Data Model สำหรับตารางทั้ง 4 ตาราง

ขั้นตอนที่ 2: สร้าง Data Model ด้วย Power Pivot

เปิด Power Pivot:

  • ถ้าไม่มีใน Ribbon ให้เปิดใช้งาน: File > Options > Add-ins > COM Add-ins > Go
  • เลือก Microsoft Power Pivot for Excel
  • ไปที่แท็บ Power Pivot >> Manage

สร้าง Relationships:

  • ไปที่แท็บ Home >> Diagram View
  • ลาก Sales[ProductID] ไป Products[ProductID]
  • ลาก Sales[CustomerID] ไป Customers[CustomerID]
  • ลาก Customers[RegionID] ไป Regions[RegionID]

สร้าง Calculated Measures (DAX):

  • ใน Power Pivot คลิกที่ตาราง Sales
  • แท็บ Home >> Measures >> New Measure
  • หรือใช้ Calculation Area

Measures ที่สำคัญ:

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 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: ใช้ PivotTable สร้างส่วนประกอบแดชบอร์ด

สร้าง PivotTables:

  • แท็บ Insert >> PivotTable >> From Data Model
  • สร้าง PivotTable แยกสำหรับ Total Revenue, Total Profit, Profit Margin, Growth
  • วางในเซลล์ที่ตรงกับโครงสร้างแดชบอร์ด
  • จัดรูปแบบเป็นสกุลเงินหรือเปอร์เซ็นต์ตามเหมาะสม

กราฟและการแสดงภาพ:

กราฟแนวโน้มรายได้:

  • PivotTable จาก Data Model: Rows = Sales[OrderDate[Month]], Values = Total Revenue
  • PivotTable Analyze >> Clustered Column Chart

กราฟสินค้าขายดี Top Products:

  • PivotTable: Rows = Products[ProductName], Values = Total Revenue
  • เรียงตาม Total Revenue descending, กรอง Top 10
  • PivotTable Analyze >> Clustered Bar Chart

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

  • PivotTable: Rows = Products[Category], Values = Total Revenue, Total Profit, Profit Margin
  • PivotTable Analyze >> Combo Chart (Clustered Column – Line on Secondary Axis)

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

แทรก Slicers และ Timeline:

  • สร้าง Slicer สำหรับ Products[Category] และ Customers[Region]
  • เพิ่ม Timeline สำหรับ Sales[OrderDate]
  • จัดรูปแบบให้เข้ากับการออกแบบแดชบอร์ด

เชื่อม Slicers ทั้งหมดกับ PivotTables ทุกตัว:

  • คลิกขวา Slicer แต่ละตัว >> Report Connections
  • ติ๊ก all PivotTables เพื่อให้ฟิลเตอร์ทำงานทั่วทั้งแดชบอร์ด

ขั้นตอนที่ 5: ทำให้เป็นอัตโนมัติและเพิ่ม互动ด้วย VBA

ตัวอย่าง 1: ปุ่ม Refresh Data

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

ตัวอย่าง 2: ปุ่ม Reset Dashboard

Sub ResetDashboardFilter()
    Dim ws As Worksheet
    Dim slicer As SlicerCache
    Dim pivotTable As PivotTable
    
    ' ล้างฟิลเตอร์ Slicer ทั้งหมด
    For Each slicer In ActiveWorkbook.SlicerCaches
        slicer.ClearAllFilters
    Next slicer
    
    ' รีเฟรช 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: สร้างเลเอาต์แดชบอร์ด

  • สร้างชีตใหม่ชื่อ Dashboard
  • โครงสร้างแดชบอร์ด: ชื่อแดชบอร์ดและตัวกรองวันที่, ส่วน KPI (Revenue, Profit, Margin, Growth), กราฟ (Sales Trend, Top Products, Regional Performance)
  • จัดรูปแบบสอดคล้อง: ใช้配色สีเดียวกัน จัดตำแหน่งองค์ประกอบ ใส่ขอบแยกส่วน
  • Conditional Formatting: Data bars, color scales, KPI icons
  • สร้างแผ่นคำแนะนำ: อธิบายวิธีใช้แดชบอร์ด การรีเฟรชข้อมูล ฟีเจอร์ต่างๆ

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

  • ทดสอบทุกองค์ประกอบโต้ตอบ: Slicers กรองได้ทั้งหมด, ปุ่มทำงาน, Timeline ควบคุมช่วงวันที่
  • ทดสอบ Reset: คลิก Reset Dashboard > ฟิลเตอร์ทั้งหมดถูกล้าง
  • ทดสอบ Data Refresh: แก้ไขข้อมูลต้นทาง > คลิก Refresh Data > ตรวจสอบการคำนวณอัปเดต

สรุป

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