Power Query ของ Excel เป็นหนึ่งในเครื่องมือที่ทรงพลังที่สุดสำหรับการเชื่อมต่อข้อมูล การแปลง และการอัปเดตแบบเรียลไทม์ Power Query สามารถนำเข้าข้อมูลจากแหล่งต่างๆ และรีเฟรชแบบเรียลไทม์ทุกครั้งที่มีการเปลี่ยนแปลงในข้อมูลต้นฉบับ ในบทความนี้ เราจะแสดงวิธีสร้างแดชบอร์ดข้อมูลแบบเรียลไทม์ด้วย Power Query
มาสร้างแดชบอร์ดการขายแบบเรียลไทม์โดยดำเนินการเพิ่มเติมใน Power Query บนชุดข้อมูลที่ขยาย ส่วนหัวในชุดข้อมูลการขายประกอบด้วยวันที่สั่งซื้อ ภูมิภาค ผลิตภัณฑ์ พนักงานขาย หน่วยที่ขาย รายได้ ($) และกำไร ($)
ขั้นตอนที่ 1:เชื่อมต่อข้อมูลด้วย Power Query
หากต้องการรับข้อมูลจากแหล่งภายนอก:
- ไปที่ข้อมูล แท็บ>> เลือก รับข้อมูล>> จากไฟล์>> จากสมุดงาน .
- เลือกไฟล์ชุดข้อมูลของคุณ (เช่น SalesData.xlsx) แล้วคลิกนำเข้า .
- ในนาวิเกเตอร์ ให้เลือกชีตที่มีข้อมูลการขายแล้วคลิก โหลด .
หากต้องการเชื่อมต่อข้อมูลจากสมุดงานที่มีอยู่:
- ไปที่ข้อมูล แท็บ>> เลือก จากตาราง/ช่วง .
Power Query จะโหลดข้อมูลลงใน ตัวแก้ไข Power Query .
ขั้นตอนที่ 2:แปลงข้อมูลด้วยการดำเนินการขั้นสูง
ตอนนี้ข้อมูลถูกโหลดลงใน Power Query Editor แล้ว มาทำการแปลงข้อมูลและการคำนวณกัน
1. เปลี่ยนประเภทข้อมูลคอลัมน์
บางครั้ง Power Query จะเปลี่ยนรูปแบบข้อมูลเป็นรูปแบบเริ่มต้น ขึ้นอยู่กับประเภทข้อมูลของคุณ ให้เปลี่ยนทั้งคอลัมน์
- วันที่สั่งซื้อ :ตรวจสอบให้แน่ใจว่าอยู่ใน วันที่ รูปแบบ
- หากไม่ใช่ ให้เลือกคอลัมน์วันที่
- ไปที่การเปลี่ยนแปลง แท็บ>> จาก ประเภทข้อมูล>> เลือกเฉพาะ วันที่ .
- หน่วยที่ขาย: ตรวจสอบว่าอยู่ใน จำนวนเต็ม หรือไม่ รูปแบบ
- หากไม่ใช่ ให้เลือกคอลัมน์หน่วยที่ขาย
- ไปที่การเปลี่ยนแปลง แท็บ>> จาก ประเภทข้อมูล>> เลือกเฉพาะ จำนวนเต็ม .
- รายได้ ($) และ กำไร ($) :ตรวจสอบให้แน่ใจว่าคอลัมน์เหล่านี้เป็น สกุลเงิน รูปแบบ
- หากไม่ใช่ ให้เลือกคอลัมน์รายได้ ($) และกำไร ($)
- ไปที่การเปลี่ยนแปลง แท็บ>> จาก ประเภทข้อมูล>> เลือกเฉพาะ สกุลเงิน .
2. เพิ่มคอลัมน์ "เดือน"
- เลือกคอลัมน์วันที่สั่งซื้อ
- ไปที่ เพิ่มคอลัมน์ แท็บ>> คลิก วันที่>> เลือก เดือน>> เลือก ชื่อเดือน .
- สิ่งนี้จะสร้างคอลัมน์ที่แยกชื่อของเดือนจาก วันที่สั่งซื้อ (เช่น มกราคม กุมภาพันธ์)
3. คำนวณรายได้ต่อหน่วย
มาคำนวณรายได้ที่สร้างขึ้นสำหรับการขายแต่ละหน่วยกัน
- ไปที่ เพิ่มคอลัมน์ แท็บ>> เลือก คอลัมน์ที่กำหนดเอง .
- ใน คอลัมน์ที่กำหนดเอง กล่องโต้ตอบ;
- ตั้งชื่อคอลัมน์ รายได้ต่อหน่วย และใช้สูตรต่อไปนี้ในกล่องสูตร:
[#"Revenue ($)"]/[Units Sold]
- คลิก ตกลง .
คอลัมน์ที่กำหนดเองใหม่นี้จะแสดงจำนวนเงินที่สร้างรายได้จากการขายแต่ละหน่วย
4. คำนวณอัตรากำไร
ต่อไป มาคำนวณอัตรากำไรกัน อัตรากำไรจะคำนวณเป็น:
- ไปที่ เพิ่มคอลัมน์ แท็บ>> เลือก คอลัมน์ที่กำหนดเอง .
- ใน คอลัมน์ที่กำหนดเอง กล่องโต้ตอบ;
- ตั้งชื่อคอลัมน์ อัตรากำไร (%) และใช้สูตรต่อไปนี้ในกล่องสูตร:
([#"Profit ($)"]/[#"Revenue ($)"])*100)
- คลิก ตกลง .
คอลัมน์ที่กำหนดเองนี้จะแสดงอัตรากำไรสำหรับการขายแต่ละครั้ง
5. จัดกลุ่มข้อมูลตามภูมิภาคและผลิตภัณฑ์เพื่อข้อมูลเชิงลึกแบบรวม
หากต้องการดูรายได้ทั้งหมด , หน่วยที่ขาย และ กำไร ตามภูมิภาค และผลิตภัณฑ์ เราจะจัดกลุ่มข้อมูล
- ไปที่หน้าแรก แท็บ>> เลือก จัดกลุ่มตาม .
- ใน จัดกลุ่มตาม กล่องโต้ตอบ ให้ตั้งค่าดังต่อไปนี้:
- เลือก ขั้นสูง เพื่อเพิ่มการรวมหลายรายการ
- จัดกลุ่มตาม :ภูมิภาค, ผลิตภัณฑ์
- แทรกที่ถูกต้อง ชื่อคอลัมน์ใหม่ .
- การทำงาน :ผลรวม
- คอลัมน์:
- หน่วยที่ขาย
- รายได้ ($)
- กำไร ($)
- คลิก ตกลง .
จัดกลุ่มตาม คุณลักษณะจะสรุปข้อมูลตามภูมิภาคและผลิตภัณฑ์เพื่อให้ภาพรวมของประสิทธิภาพการขาย
6. จัดเรียงข้อมูล
จัดเรียงข้อมูลตามรายได้ ($) จากมากไปน้อยเพื่อดูผลิตภัณฑ์และภูมิภาคที่มีประสิทธิภาพสูงสุด
- คลิกเมนูแบบเลื่อนลงบนรายได้ ($) ส่วนหัวของคอลัมน์
- เลือก เรียงลำดับจากมากไปน้อย>> คลิก ตกลง .
วิธีนี้จะจัดเรียงข้อมูลของคุณเพื่อแสดงข้อมูลจากสูงสุดไปต่ำสุดตามรายได้
ขั้นตอนที่ 3:โหลดข้อมูลที่แปลงแล้วลงใน Excel
เพื่อโหลดข้อมูลที่ล้างและแปลงแล้วลงในแผ่นงาน Excel
- ไปที่หน้าแรก แท็บ>> จาก ปิดและโหลด>> เลือก ปิดและโหลด .
ขั้นตอนที่ 4:การสร้างแดชบอร์ด
เมื่อข้อมูลของคุณถูกแปลงและโหลดลงใน Excel คุณสามารถสร้างแดชบอร์ดพร้อมการแสดงภาพได้
1. สร้างตารางสาระสำคัญ
- เลือกตารางข้อมูลที่แปลงแล้ว
- ไปที่ แทรก แท็บ>> เลือก PivotTable .
- ในฟิลด์ PivotTable รายการ:
- แถว :ภูมิภาค, ผลิตภัณฑ์
- ค่านิยม :ผลรวมของหน่วยที่ขาย ผลรวมของรายได้ ผลรวมของกำไร
2. สร้างแผนภูมิสาระสำคัญ
แผนภูมิแท่งสำหรับรายได้ตามภูมิภาค :
- เน้นตาราง Pivot ด้วยภูมิภาคและรายได้
- ไปที่ การวิเคราะห์ PivotTable แท็บ>> เลือก แผนภูมิแท่ง>> เลือก แถบคลัสเตอร์ .
แผนภูมิวงกลมสำหรับการกระจายการขายตามผลิตภัณฑ์ :
- เลือก Pivot Table พร้อมผลิตภัณฑ์และรายได้
- ไปที่ การวิเคราะห์ PivotTable แท็บ>> เลือก แผนภูมิวงกลม .
มาสำรวจการโต้ตอบของแดชบอร์ดกันดีกว่า เลือกภูมิภาคตะวันออกจากแผนภูมิวงกลม
คุณสามารถเพิ่มตัวแบ่งส่วนข้อมูลสำหรับพนักงานขายและไทม์ไลน์สำหรับวันที่สั่งซื้อสำหรับการโต้ตอบได้
ขั้นตอนที่ 5:ตั้งค่าการรีเฟรชแบบเรียลไทม์
หากต้องการอัปเดตข้อมูลแบบเรียลไทม์ในแดชบอร์ด:
- ไปที่ข้อมูล แท็บ>> คลิก รีเฟรชทั้งหมด เมื่อใดก็ตามที่คุณเพิ่มข้อมูลใหม่ลงในแหล่งข้อมูลของคุณ
หรือคุณสามารถตั้งค่าสมุดงานให้รีเฟรชอัตโนมัติทุกๆ สองสามนาที:
- ไปที่ข้อมูล แท็บ>> เลือก ข้อความค้นหาและการเชื่อมต่อ>> คลิกขวาที่ ข้อความค้นหาและการเชื่อมต่อ>> เลือก คุณสมบัติ .
- ใน คุณสมบัติการค้นหา กล่องโต้ตอบ;
- เปิดใช้งาน รีเฟรชทุก 5 นาที .
- คลิก ตกลง .
บทสรุป
ด้วยการทำตามขั้นตอนเหล่านี้ คุณสามารถสร้างแดชบอร์ดข้อมูลการขายแบบเรียลไทม์แบบไดนามิกได้โดยใช้ Power Query คุณสามารถดำเนินการขั้นสูงในตัวแก้ไข Power Query เพื่อจัดกลุ่ม คำนวณคอลัมน์ที่กำหนดเอง เรียงลำดับ กรอง ฯลฯ จากนั้นโหลดข้อมูลที่แปลงแล้วลงใน Excel เพื่อสร้างแดชบอร์ดแบบเรียลไทม์ การเชื่อมต่อข้อมูลและฟีเจอร์การแปลงของ Power Query จะอัปเดตแดชบอร์ดของคุณด้วยข้อมูลใหม่แบบเรียลไทม์อย่างรวดเร็ว
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!