สร้างแดชบอร์ดเชิงโต้ตอบแบบเรียลไทม์ใน Excel โดยใช้ PivotTable และตัวแบ่งส่วนข้อมูล

ในบทความนี้ เราจะแสดงวิธีสร้างแดชบอร์ดแบบไดนามิกด้วย PivotTable และตัวแบ่งส่วนข้อมูลใน Excel

ขั้นตอนที่ 1:เตรียมข้อมูลของคุณ

ก่อนที่จะสร้างแดชบอร์ด สิ่งสำคัญคือต้องจัดโครงสร้างข้อมูลของคุณอย่างเหมาะสม

ข้อกำหนดสำหรับชุดข้อมูลของคุณ:

• ต้องอยู่ในรูปแบบตาราง (ไม่มีเซลล์ที่ผสาน)
• แต่ละคอลัมน์ควรมีส่วนหัวที่ชัดเจนและไม่ซ้ำกัน
• ไม่มีแถวหรือคอลัมน์ว่าง
• ตรวจสอบให้แน่ใจว่าคอลัมน์ทั้งหมดมีรูปแบบที่เหมาะสม:
  • วันที่ คอลัมน์เป็น วันที่แบบสั้น .
  • ราคาต่อหน่วย และ ยอดขายรวม เป็น สกุลเงิน .
  • หน่วยที่ขาย เป็น ตัวเลข โดยไม่มี ทศนิยม .
• แปลงเป็นตาราง Excel:
  • เลือกข้อมูลทั้งหมดหรือกด Ctrl+A .
  • กด Ctrl+T หรือไปที่ แทรก แท็บ>> เลือก ตาราง .
  • ตรวจสอบที่ ตารางของฉันมีส่วนหัว .
  • คลิก ตกลง .

• ตั้งชื่อตารางของคุณ:
  • เมื่อเลือกเซลล์ใดๆ ในตาราง
  • ไปที่การออกแบบตาราง แท็บ>> เปลี่ยน ชื่อตาราง ไปที่ ข้อมูลการขาย .

ขั้นตอนที่ 2:สร้าง PivotTable แรกของคุณ

มาสร้าง PivotTable ที่แสดงยอดขายตามภูมิภาคและหมวดหมู่ผลิตภัณฑ์กันดีกว่า

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

• ในบานหน้าต่างเขตข้อมูล PivotTable:
  • ลาก ภูมิภาค ลงใน แถว พื้นที่
  • ลาก หมวดหมู่ผลิตภัณฑ์ ลงใน คอลัมน์ พื้นที่
  • ลาก ยอดขายรวม ลงใน ค่า พื้นที่
• ตอนนี้ PivotTable แรกจะแสดงรายได้ที่แต่ละหมวดหมู่ผลิตภัณฑ์สร้างขึ้นในแต่ละภูมิภาค

ขั้นตอนที่ 3:เพิ่มตัวแบ่งส่วนข้อมูลและไทม์ไลน์

ตอนนี้ ให้เพิ่มตัวแบ่งส่วนข้อมูลและไทม์ไลน์เพื่อกรองข้อมูลนี้แบบโต้ตอบได้

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

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

แทรกไทม์ไลน์:

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

• วางตำแหน่งตัวแบ่งส่วนข้อมูลและไทม์ไลน์ไว้ข้าง PivotTable ของคุณ

ทดสอบตัวแบ่งส่วนข้อมูลและไทม์ไลน์ของคุณโดยคลิกตัวแทนฝ่ายขายที่แตกต่างกัน และดูว่า PivotTable อัปเดตโดยอัตโนมัติอย่างไร!

ขั้นตอนที่ 4:สร้าง PivotTable เพิ่มเติม

มาเพิ่ม PivotTable อีกสองรายการในแดชบอร์ดของเรา

PivotTable 2:แนวโน้มยอดขายรายเดือน

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

• ในบานหน้าต่างเขตข้อมูล PivotTable:
  • ลาก วันที่ ไปที่ แถว พื้นที่ มันจะแสดง
    • เดือน (วันที่)
    • วัน (วันที่)
    • วันที่
  • ลาก ยอดขายรวม ไปที่ ค่า พื้นที่

PivotTable 3:ผลิตภัณฑ์ยอดนิยมตามหน่วยที่ขาย

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

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

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

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

• ตรวจสอบ PivotTable ทั้งหมดในรายการ
• คลิก ตกลง .

• ทำซ้ำสำหรับ วันที่ ตัวแบ่งส่วนข้อมูล

ตอนนี้ เมื่อคุณคลิกที่ตัวแทนฝ่ายขายหรือช่วงวันที่ที่เฉพาะเจาะจง PivotTable ทั้งสามจะอัปเดตพร้อมกัน!

ขั้นตอนที่ 6:สร้าง PivotChart

แปลง PivotTable ของคุณให้เป็นแผนภูมิภาพ

สำหรับภูมิภาค/หมวดหมู่ PivotTable:

• คลิกที่ใดก็ได้ใน PivotTable
• ไปที่ การวิเคราะห์ PivotTable แท็บ>> เลือก PivotChart .
• เลือก คอลัมน์แบบคลัสเตอร์ แผนภูมิ
• คลิก ตกลง .
• จัดรูปแบบแผนภูมิ:
  • เพิ่มชื่อแผนภูมิ
  • ปรับแต่งแกน
  • ลบองค์ประกอบที่ไม่จำเป็นออก (เช่น เส้นตาราง คำอธิบาย หากไม่จำเป็น)

สำหรับแนวโน้มยอดขายรายเดือน:

• คลิกที่ใดก็ได้ใน PivotTable
• ไปที่ การวิเคราะห์ PivotTable แท็บ>> เลือก PivotChart .
• เลือก เส้นที่มีเครื่องหมาย แผนภูมิ
• คลิก ตกลง .

สำหรับผลิตภัณฑ์ยอดนิยม:

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

การเปลี่ยนแปลงใดๆ ใน PivotTable ของคุณจะปรากฏใน PivotChart ของคุณโดยอัตโนมัติ

ขั้นตอนที่ 7:ออกแบบเค้าโครงแดชบอร์ดของคุณ

ตอนนี้เรามาจัดระเบียบทุกอย่างให้เป็นแดชบอร์ดที่เชื่อมโยงกัน

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

ขั้นตอนที่ 8:ปรับปรุงด้วยการจัดรูปแบบ

มาทำให้แดชบอร์ดดูน่าดึงดูดยิ่งขึ้นกันเถอะ:

• ใช้การจัดรูปแบบตามเงื่อนไขกับ PivotTable ภูมิภาค/หมวดหมู่ :
  • เลือกเซลล์ข้อมูล
  • ไปที่ หน้าแรก แท็บ>> จาก การจัดรูปแบบตามเงื่อนไข>> จาก ระดับสี>> เลือก เขียว-เหลือง-แดง .
• จัดรูปแบบแนวโน้มรายเดือน แผนภูมิ:
  • คลิกที่แผนภูมิ
  • ไปที่การออกแบบแผนภูมิ>> เลือก สไตล์ 5 (หรือสไตล์ใดก็ได้ที่คุณต้องการ)
  • เพิ่มชื่อแผนภูมิ:แนวโน้มยอดขายรายเดือน .
• จัดรูปแบบ ผลิตภัณฑ์ยอดนิยม แผนภูมิ:
  • เพิ่มป้ายกำกับข้อมูล
  • ไปที่การออกแบบแผนภูมิ>> เลือก เพิ่มองค์ประกอบแผนภูมิ>> เลือก ป้ายกำกับข้อมูล .
  • เรียงลำดับจากมากไปน้อย
  • เพิ่มชื่อแผนภูมิ:ผลิตภัณฑ์ยอดนิยม .

ตรวจสอบการโต้ตอบ:

เคล็ดลับการแก้ปัญหา

• หากไม่ได้จัดกลุ่มวันที่อย่างถูกต้อง ตรวจสอบให้แน่ใจว่าวันที่เหล่านั้นอยู่ในรูปแบบวันที่ในข้อมูลต้นฉบับ
• หากตัวแบ่งส่วนข้อมูลไม่ได้อัปเดต PivotTable ทั้งหมด ให้ตรวจสอบการเชื่อมต่อรายงานอีกครั้ง
• ลองใช้ "Defer Layout Update" ในตัวเลือก PivotTable สำหรับปัญหาด้านประสิทธิภาพกับชุดข้อมูลขนาดใหญ่

เทคนิคขั้นสูง

สร้างฟิลด์จากการคำนวณที่แสดงอัตรากำไร:

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

• คลิก เพิ่ม และ ตกลง .

เพิ่มชื่อแบบไดนามิกที่เปลี่ยนแปลงด้วยการเลือก:

• ใช้ GETPIVOTDATA() เพื่อดึงผลรวมที่กรองในปัจจุบัน
• แทรกสูตรต่อไปนี้

="Sales Dashboard: "&TEXT(GETPIVOTDATA("Total Sales",$A$3),"$#,##0")

ดาวน์โหลดสมุดงาน

บทสรุป

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