Power Query ของ Excel เป็นเครื่องมืออันทรงพลังสำหรับเชื่อมต่อข้อมูล Transform และอัปเดตแบบ Real-time Power Query นำเข้าข้อมูลจากหลายแหล่งและ Refresh อัตโนมัติเมื่อข้อมูลต้นทางเปลี่ยนแปลง บทความนี้จะสอนสร้าง Dashboard ข้อมูล Real-time ด้วย Power Query
เราจะสร้าง Sales Dashboard แบบ Real-time โดยทำ Operations เพิ่มเติมใน Power Query บน Dataset ที่ขยายแล้ว หัวคอลัมน์มี Order Date, Region, Product, Salesperson, Units Sold, Revenue ($), Profit ($)
ขั้นตอนที่ 1: เชื่อมต่อข้อมูลด้วย Power Query
ดึงข้อมูลจากไฟล์ภายนอก:
- แท็บ Data >> Get Data >> From File >> From Workbook
- เลือกไฟล์ Dataset (เช่น SalesData.xlsx) >> Import
- ใน Navigator เลือกชีทที่มีข้อมูลขาย >> Load
เชื่อมต่อข้อมูลจากสมุดงานปัจจุบัน:
- แท็บ Data >> From Table/Range
Power Query จะโหลดข้อมูลเข้าสู่ Power Query Editor
ขั้นตอนที่ 2: Transform ข้อมูลด้วย Operations ขั้นสูง
ตอนข้อมูลอยู่ใน Power Query Editor แล้ว มาทำ Transform และคำนวณกัน
1. เปลี่ยน Data Type ของคอลัมน์
Power Query บางครั้งเปลี่ยน Format เอง ต้องตั้งค่าให้ตรงข้อมูลจริง
- Order Date: ต้องเป็น Date
- ถ้าไม่ใช่ เลือกคอลัมน์ Date >> แท็บ Transform >> DataType >> Date
- Units Sold: ต้องเป็น Whole Number
- ถ้าไม่ใช่ เลือกคอลัมน์ >> Transform >> DataType >> Whole Number
- Revenue ($) และ Profit ($): ต้องเป็น Currency
- ถ้าไม่ใช่ เลือกทั้งสองคอลัมน์ >> Transform >> DataType >> Currency
2. เพิ่มคอลัมน์ “Month”
- เลือกคอลัมน์ Order Date
- แท็บ Add Column >> Date >> Month >> Name of Month
- จะสร้างคอลัมน์ดึงชื่อเดือนจาก Order Date (เช่น January, February)
3. คำนวณ Revenue per Unit
คำนวณรายได้ต่อหน่วยที่ขายได้
- แท็บ Add Column >> Custom Column
- ใน Custom Column dialog:
- ตั้งชื่อ Revenue per Unit ใส่สูตร:
[#"Revenue ($)"]/[Units Sold]
-
- คลิก OK
คอลัมน์ใหม่นี้แสดงรายได้ต่อหน่วยที่ขายได้
4. คำนวณ Profit Margin
คำนวณ Profit Margin:
- แท็บ Add Column >> Custom Column
- ใน Custom Column dialog:
- ตั้งชื่อ Profit Margin (%) ใส่สูตร:
([#"Profit ($)"]/[#"Revenue ($)"])*100
-
- คลิก OK
คอลัมน์นี้แสดง Profit Margin ของแต่ละการขาย
5. จัดกลุ่มข้อมูล (Group By) ตาม Region และ Product
ดู Total Revenue, Units Sold, Profit แยกตาม Region และ Product
- แท็บ Home >> Group By
- ใน Group By dialog:
- เลือก Advanced เพื่อเพิ่ม Aggregation หลายตัว
- Group by: Region, Product
- ตั้งชื่อ New columns ให้ถูกต้อง
- Operation: Sum
- Column:
- Units Sold
- Revenue ($)
- Profit ($)
- คลิก OK
Group By จะสรุปข้อมูลตาม Region และ Product ให้ภาพรวมประสิทธิภาพการขาย
6. เรียงลำดับข้อมูล
เรียงตาม Revenue ($) จากมากไปน้อย เพื่อดูสินค้าและภูมิภาคที่ทำได้ดีที่สุด
- คลิก dropdown ที่หัวคอลัมน์ Revenue ($)
- เลือก Sort Descending >> OK
ข้อมูลจะเรียงจากสูงไปต่ำตาม Revenue
ขั้นตอนที่ 3: โหลดข้อมูลที่ Transform แล้วกลับ Excel
- แท็บ Home >> Close & Load >> Close & Load
ขั้นตอนที่ 4: สร้าง Dashboard
เมื่อข้อมูล Transform และโหลดกลับ Excel แล้ว สร้าง Dashboard พร้อม Visualization
1. สร้าง Pivot Table
- เลือกตารางข้อมูลที่ Transform แล้ว
- แท็บ Insert >> PivotTable
- ใน PivotTable Fields:
- Rows: Region, Product
- Values: Sum of Units Sold, Sum of Revenue, Sum of Profit
2. สร้าง Pivot Charts
Bar Chart: Revenue by Region:
- เลือก Pivot Table ที่มี Region และ Revenue
- แท็บ PivotTable Analyze >> Bar Chart >> Clustered Bar
Pie Chart: Sales Distribution by Product:
- เลือก Pivot Table ที่มี Product และ Revenue
- แท็บ PivotTable Analyze >> Pie Chart
ลองโต้ตอบ Dashboard: เลือก Region East จาก Pie Chart
เพิ่ม Slicers สำหรับ Salesperson และ Timeline สำหรับ Order Date เพื่อโต้ตอบเพิ่มเติม
ขั้นตอนที่ 5: ตั้งค่า Real-time Refresh
อัปเดตข้อมูล Real-time ใน Dashboard:
- แท็บ Data >> Refresh All เมื่อเพิ่มข้อมูลใหม่ในแหล่งต้นทาง
หรือตั้งให้ Refresh อัตโนมัติทุก 몇 นาที:
- แท็บ Data >> Queries and Connections >> คลิกขวา Queries and Connections >> Properties
- ใน Query Properties dialog:
- ติ๊ก Refresh every 5 minutes
- OK
สรุป
ทำตามขั้นตอนเหล่านี้จะได้ Dashboard ข้อมูลขายแบบ Dynamic Real-time ด้วย Power Query ทำ Operations ขั้นสูงใน Power Query Editor: Group, คำนวณ Custom Columns, เรียง, กรอง แล้วโหลดกลับ Excel สร้าง Dashboard Power Query จะอัปเดต Dashboard ด้วยข้อมูลใหม่ล่าสุดแบบ Real-time ได้อย่างรวดเร็ว
Get FREE Advanced Excel Exercises with Solutions!
