สร้างแดชบอร์ดยอดขายรายไตรมาสแบบโต้ตอบตามเขตพื้นที่ใน Excel

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

นี่คือ รายงาน ที่คุณจะสร้างหลังจากจบบทความนี้

สร้างรายงานใน Excel ที่แสดงยอดขายรายไตรมาสตามเขตพื้นที่

คุณสามารถดาวน์โหลดสมุดงานที่ใช้สำหรับสาธิต เพื่อลองทำตามขั้นตอนเหล่านี้ด้วยตัวเองขณะอ่านบทความนี้ได้ครับ

ขั้นตอนแบบทีละขั้นสำหรับการสร้างรายงานแสดงยอดขายรายไตรมาสตามเขตพื้นที่ใน Excel

สำหรับการสาธิต เราจะใช้ชุดข้อมูลต่อไปนี้

ข้อมูลนี้รวมยอดขายตามวันที่ ซึ่งเราจะจัดเรียงใหม่เป็นรูปแบบรายไตรมาสด้วยความช่วยเหลือจากฟีเจอร์ Table และ Pivot Table ของ Excel

ขั้นตอนที่ 1: แปลงชุดข้อมูลเป็นตาราง (Table)

หากข้อมูลไม่ได้อยู่ในรูปแบบตาราง ให้แปลงช่วงข้อมูลเป็นตาราง ตาราง Excel เป็นหนึ่งในฟีเจอร์ที่ดีที่สุดของ Excel ที่ทำให้งานหลายอย่างง่ายขึ้น เช่น การอ้างอิง การกรอง การจัดเรียง และการอัปเดต

• เลือกเซลล์ในช่วงที่ต้องการแปลงเป็นตาราง และกด Ctrl+T บนแป้นพิมพ์ หรือไปที่แท็บ Insert และจากกลุ่มคำสั่ง Tables คลิกที่ตัวเลือก Table

• ผลลัพธ์ กล่องโต้ตอบ Create Table จะปรากฏ ช่วงข้อมูลจะถูกเลือกโดยอัตโนมัติพร้อมกับติ๊กเลือก My table has headers เพื่อสร้างตาราง ให้คลิก OK

ผลลัพธ์ ชุดข้อมูลจะถูกแปลงเป็นตาราง

ขั้นตอนที่ 2: ตั้งชื่อช่วงตาราง

มาตั้งชื่อตารางในขณะนี้ ซึ่งจะช่วยให้บางส่วนของงานต่อไปง่ายขึ้น

คุณสามารถเปลี่ยนชื่อตารางได้จากแท็บ Design หรือใช้ Name Box เราได้ตั้งชื่อตารางของเราว่า Data

อ่านเพิ่มเติม: How to Make Monthly Report in Excel (with Quick Steps)

ขั้นตอนที่ 3: สร้าง Pivot Table จากข้อมูลที่ให้มา

เราจะใช้เครื่องมือที่ใช้มากที่สุดของ Excel สำหรับสร้างรายงานของเรา คือ Pivot Table ทำตามขั้นตอนเหล่านี้เพื่อสร้าง Pivot Table กับตาราง

• ก่อนอื่น เลือกเซลล์ในตาราง
• จากนั้นไปที่แท็บ Insert และคลิกคำสั่ง PivotTable จากกลุ่ม Tables

• ในขณะนี้ กล่องโต้ตอบ Create PivotTable จะปรากฏ เนื่องจากเราได้เลือกเซลล์ของตารางก่อนคลิกคำสั่ง PivotTable ชื่อตารางของเรา (Data) จะแสดงอัตโนมัติในฟิลด์ Table/Range ของกล่องโต้ตอบ
• เราต้องการสร้าง Pivot Table ในแผ่นงานใหม่ ดังนั้นเราเก็บตัวเลือกเริ่มต้น New Worksheet ภายใต้หัวข้อ Choose where you want the PivotTable report to be placed
• จากนั้นคลิก OK

แผ่นงานใหม่ถูกสร้างขึ้น และ PivotTable Fields task pane จะแสดงอัตโนมัติในแผ่นงาน

อ่านเพิ่มเติม: How to Create a Summary Report in Excel (2 Easy Methods)

ขั้นตอนที่ 4: เตรียม Pivot Table รายงานตามหมวดหมู่ (Category)

มาสร้าง รายงานยอดขาย Category แบบแยก และจากนั้นสร้างแผนภูมิพาย (Pie Chart) เพื่อทำรายงาน เราจัดระเบียบ Pivot Table Fields ดังนี้

สังเกตภาพด้านล่างอย่างละเอียด เราวางฟิลด์ Sales สองครั้งในพื้นที่ Values ด้วยเหตุผลนี้ ในพื้นที่ Columns จะมีฟิลด์ Values เพิ่มเติมแสดงอยู่ ในพื้นที่ Rows เราวางฟิลด์ Category

ด้านซ้ายของภาพ คุณเห็นตาราง Pivot Table สำหรับการตั้งค่าฟิลด์ข้างต้น

• ตอนนี้เราต้องการเปลี่ยนรูปแบบตัวเลขของยอดขายเป็นเปอร์เซ็นต์ (%) of Grand Total เพื่อทำเช่นนั้น ให้คลิกขวาที่เซลล์ในคอลัมน์
• จากนั้นเลือก Show Values As จากเมนูบริบท
• หลังจากนั้น คลิกคำสั่ง % of Grand Total

ดังนั้น ค่าของคอลัมน์จะแสดงเป็นเปอร์เซ็นต์ของ Grand Total

ขั้นตอนที่ 5: สร้างแผนภูมิพายสำหรับรายงานตามหมวดหมู่

เพื่อสร้างรายงานจากข้อมูล มาเพิ่มแผนภูมิพายเข้าไป ทำตามขั้นตอนเหล่านี้เพื่อเพิ่มแผนภูมิพายจากข้อมูล

• ก่อนอื่น เลือกเซลล์ใน Pivot Table
• จากนั้นไปที่แท็บ Insert และคลิกไอคอน Pie Chart จากกลุ่ม Charts
• หลังจากนั้น เลือกแผนภูมิ Pie จากรายการแบบเลื่อนลง

เราจะได้แผนภูมิพายปรากฏบนสเปรดชีตของเรา

หลังจากปรับแต่งบ้าง แผนภูมิจะมีลักษณะดังนี้

การแสดงชื่อหมวดหมู่และป้ายกำกับข้อมูลบนแผนภูมิพาย

คุณสามารถเพิ่มป้ายกำกับข้อมูลได้โดยทำตามขั้นตอนเหล่านี้

• ก่อนอื่น เลือกแผนภูมิพาย
• จากนั้นไปที่แท็บ Design และในกลุ่มคำสั่ง Chart Layouts คลิกที่ Quick Layout
• จากรายการแบบเลื่อนลง เลือกตัวเลือก Layout 1

วิธีทางเลือก:

อีกวิธีสร้างสรรค์ในการเพิ่มป้ายกำกับข้อมูลบนแผนภูมิคือการใช้ ฟังก์ชัน GETPIVOTDATA เราจะใช้ฟังก์ชันนี้เพื่อดึงข้อมูลจาก Pivot Table

คุณเห็น Pivot Table ด้านล่างที่สร้างจากข้อมูลของเรา

Pivot Table นี้แสดง Sum of Sales, State, และ Category แบบแยก

เราวางฟิลด์ State ในพื้นที่ Rows, ฟิลด์ Category ในพื้นที่ Columns, และฟิลด์ Sales ในพื้นที่ Values

ตอนนี้ มาดูฟังก์ชัน GETPIVOTDATA ของ Excel

ไวยากรณ์ GETPIVOTDATA: GETPIVOTDATA (data_field, pivot_table, [field1, item1], [field2, item2], …)

Pivot Table มีเพียงหนึ่ง data_field แต่สามารถมีฟิลด์อื่นๆ จำนวนเท่าใดก็ได้

สำหรับ Pivot Table ข้างต้น:

• data_field คือ ฟิลด์ Sales
• ฟิลด์อื่นๆ อีกสองคือ State และ Category.

ในภาพด้านล่าง คุณเห็นว่าผมใช้สูตร GETPIVOTDATA ในเซลล์ H9:

=GETPIVOTDATA("Sales", A3, "State", H7, "Category", H8)

สูตรนี้คืนค่า 950 ในเซลล์ H9

สูตรนี้ทำงานอย่างไร?

• อาร์กิวเมนต์ data_field คือ Sales ไม่มีข้อสงสัย
• A3 คือการอ้างอิงเซลล์ภายใน Pivot Table อาจเป็นการอ้างอิงเซลล์ใดก็ได้ภายใน Pivot Table
• field1, item1 = “State”, H7 คุณสามารถแปลได้ว่า Idaho (ค่าของเซลล์ H7 คือ Idaho) ไอเทมใน State
• field2, item2 = “Category”, H8 สามารถแปลได้ว่า Office Supplies (ค่าของเซลล์ H8 คือ Office Supplies) ไอเทมใน Category
• จุดตัดของค่า Idaho และค่า Office Supplies ให้ค่า 950

เพื่อแสดงป้ายกำกับ:

โดยใช้ฟังก์ชัน GETPIVOTDATA เราแสดงชื่อหมวดหมู่และค่ายอดขาย (% of Total) ในบางเซลล์ (ดูภาพด้านล่าง)

เพื่อความเข้าใจ ลองอธิบายสูตรในเซลล์ D4

=A4&" "&TEXT(GETPIVOTDATA("Sales", A3, "Category", A4), "0%")

• A4&” ” ส่วนนี้เข้าใจง่าย คือการอ้างอิงเซลล์แล้วทำช่องว่างในผลลัพธ์
• จากนั้นเราใช้ TEXT ของ Excel โดยอาร์กิวเมนต์ value ของฟังก์ชัน TEXT เราส่งผ่านฟังก์ชัน GETPIVOTDATA และอาร์กิวเมนต์ format_text เราใช้รูปแบบนี้: “0%”
• ส่วน GETPIVOTDATA เข้าใจง่าย จึงไม่ต้องอธิบายการทำงานของฟังก์ชัน GETPIVOTDATA ที่นี่

ตอนนี้ เราจะแสดงข้อมูลเหล่านี้บนแผนภูมิ

เราแทรก Text Box จากแท็บ Insert => กลุ่มคำสั่ง Illustrations => Shapes

ตอนนี้เราแทรก Text Box บนแผนภูมิ => วางเครื่องหมายเท่ากับบน Formula Bar แล้วเลือกเซลล์ D4

หากผมกด Enter Text Box จะแสดงค่าของเซลล์ D4

เช่นเดียวกัน ผมสร้าง Text Boxes อื่นๆ และอ้างอิงเซลล์ที่เกี่ยวข้อง

หมายเหตุ: เมื่อสร้าง Text Box หนึ่งแล้ว คุณสามารถสร้าง Text Boxes ใหม่จากตัวเดิมได้ วิธีทำมีดังนี้:

• วางเคอร์เซอร์เมาส์บนขอบของ Text Box ที่สร้างไว้ และกดปุ่ม Ctrl บนแป้นพิมพ์ เครื่องหมายบวกจะปรากฏ
• ลากเมาส์ คุณจะเห็น Text Box ใหม่ (วัตถุ) ถูกสร้างขึ้น วาง Text Box ใหม่นี้ที่ตำแหน่งที่คุณต้องการ

ดังนั้น เราเสร็จสิ้นการสร้างแผนภูมิพายที่แสดงยอดขายตามหมวดหมู่แบบไดนามิกแล้ว

ผมเพียงเปลี่ยนชื่อ Pivot Table นี้เป็น PT_CategorySales

อ่านเพิ่มเติม: How to Make Daily Sales Report in Excel (with Quick Steps)

บทความที่เกี่ยวข้อง

• How to Make Inventory Aging Report in Excel (Step by Step Guidelines)
• Generate PDF Reports from Excel Data (4 Easy Methods)
• How to Prepare MIS Report in Excel (2 Suitable Examples)
• Make MIS Report in Excel for Accounts (with Quick Steps)

ขั้นตอนที่ 6: เตรียม Pivot Table สำหรับยอดขายรายไตรมาส

บางครั้ง คุณอาจต้องการดูการเปลี่ยนแปลงของ Sales ในไตรมาสต่างๆ ตลอดหลายปี

เราจะสร้างรายงานแบบภาพด้านล่าง

ภาพแสดง 15 รัฐอันดับต้นๆ ของสหรัฐอเมริกาตามยอดขายรวมสำหรับไตรมาสต่างๆ เรายังเพิ่ม Sparklines เพื่อแสดงแนวโน้มในไตรมาสต่างๆ

ทำตามขั้นตอนเหล่านี้เพื่อเตรียม Pivot Table สำหรับยอดขายรายไตรมาส

• ก่อนอื่น เลือกเซลล์จากตารางข้อมูล
• จากนั้นเลือก PivotTable จากกลุ่ม Tables ของแท็บ Insert

• ต่อไป เลือกตำแหน่งที่ต้องการวาง Pivot Table และคลิก OK สำหรับการสาธิตนี้ เราเลือกแผ่นงานใหม่

• ตอนนี้ ทำดังนี้: เพิ่มฟิลด์ Order Date ในพื้นที่ Columns, ฟิลด์ State ในพื้นที่ Rows, และฟิลด์ Sales ในพื้นที่ Values

• เพื่อแสดงรายงานรายไตรมาส ให้คลิกขวาที่เซลล์ใดๆ ใน Column Labels และเลือก Group จากเมนูบริบท

• จากนั้นเลือก Quarters ภายใต้ส่วน By ของ Grouping

• หลังจากคลิก OK Pivot Table จะมีลักษณะประมาณนี้

ขั้นตอนที่ 7: แสดง 15 รัฐอันดับต้นๆ จากยอดขาย

ผลลัพธ์จากขั้นตอนก่อนหน้ามีรายงานรายไตรมาสของทุกรัฐจากชุดข้อมูล หากคุณต้องการทั้งหมด สามารถดำเนินต่อไปได้ แต่ในกรณีที่ต้องการการวิเคราะห์รายละเอียดมากขึ้นซึ่งต้องการรัฐอันดับต้นๆ นี่คือขั้นตอนที่สะดวก

• ก่อนอื่น คลิกขวาที่เซลล์ใดๆ ในคอลัมน์ State(หรือ Row Labels)
• จากนั้นชี้เมาส์ไปที่ Filter จากเมนูบริบท และเลือก Top 10

• ต่อไป เลือก 15 ในตัวเลือก Show จาก Top 10 Filter (State)

• เมื่อคลิก OK Pivot Table จะแสดง 15 รัฐอันดับต้นๆ ตามยอดขาย

ขั้นตอนที่ 8: เพิ่ม Sparklines ลงในตาราง

ก่อนเพิ่ม Sparklines ผมต้องการลบ Grand Totals ทั้งสอง ทำตามขั้นตอนเหล่านี้สำหรับคำแนะนำโดยละเอียด

• ก่อนอื่น เลือกเซลล์จาก Pivot Table
• จากนั้นไปที่แท็บ Design บนริบบอน
• ตอนนี้เลือก Grand Totals จาก Layout
• จากนั้นเลือก Off for Rows and Columns จากรายการแบบเลื่อนลง

ส่วน Grand Total จะถูกลบออก

• เพื่อเพิ่ม sparklines ให้เลือกเซลล์ F5 จากนั้นไปที่แท็บ Insert บนริบบอน
• ตอนนี้เลือก Line จาก Sparklines

• ในกล่อง Create Sparklines เลือกช่วง B5:E19 เป็น Data Range และช่วง F5:F19 เป็น Location Range

• คลิก OK Pivot Table จะมีลักษณะดังนี้สุดท้าย

• เพิ่มตัวชี้วัด (markers) ให้ดูน่าสนใจมากขึ้น เพื่อทำเช่นนั้น ไปที่แท็บ Sparkline บนริบบอน (จะปรากฏเมื่อคุณเลือกเซลล์ที่มี sparkline) จากนั้นเลือก Markers จาก Show

นี่คือผลลัพธ์สุดท้ายของ sparkline ของเรา

อ่านเพิ่มเติม: How to Make Monthly Sales Report in Excel (with Simple Steps)

ขั้นตอนที่ 9: เพิ่ม Slicer เพื่อกรองผลลัพธ์

ทำตามขั้นตอนง่ายๆ เหล่านี้เพื่อเพิ่ม Slicers ให้กับ Pivot Table

• ก่อนอื่น เลือก Pivot Table ที่คุณต้องการสร้าง Slicers
• จากนั้นไปที่แท็บ Insert และจากกลุ่มคำสั่ง Filters คลิกที่ Slicer

• ต่อไป กล่องโต้ตอบ Insert Slicers จะปรากฏพร้อมฟิลด์ทั้งหมดที่มีอยู่ของ Pivot Table เลือกฟิลด์ที่คุณต้องการสร้าง Slicers ที่นี่ เราเลือกฟิลด์ Customer Name, State, และ Category สำหรับการสาธิต

• หลังจากคลิก OK Slicers 3 ตัวจะปรากฏบนสเปรดชีต

อ่านเพิ่มเติม: How to Make MIS Report in Excel for Sales (with Easy Steps)

ขั้นตอนที่ 10: เตรียมรายงานสุดท้าย

ด้วยสิ่งต่างๆ ที่สร้างแยกกันแล้ว มารวมทั้งหมดเข้าด้วยกันในสเปรดชีตเดียวเพื่อสร้างรายงานสุดท้าย

ตอนนี้ หากคุณเลือก/ยกเลิกตัวเลือกจาก Slicer ผลลัพธ์จะเปลี่ยนไปตามลำดับในเวลาจริง ตัวอย่างเช่น มาเลือก Arizona จาก State slicers จะรายงานเฉพาะนั้น

คุณสามารถเลือกหลายรายการได้เช่นกัน ตัวอย่างเช่น เพิ่ม Alabama เข้าไป จะมีลักษณะดังนี้ และนี่คือวิธีที่คุณสามารถสร้างรายงานที่แสดงยอดขายรายไตรมาสตามเขตพื้นที่ได้

อ่านเพิ่มเติม: How to Automate Excel Reports Using Macros (3 Easy Ways)

สรุป

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

สำหรับคำแนะนำเพิ่มเติมแบบนี้ โปรดเยี่ยมชม Exceldemy.com

บทความที่เกี่ยวข้อง

• How to Create an Expense Report in Excel (With Easy Steps)
• Create an Income and Expense Report in Excel (3 Examples)
• How to Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)
• Make Production Report in Excel (2 Common Variants)
• How to Make Daily Activity Report in Excel (5 Easy Examples)
• Make Daily Production Report in Excel (Download Free Template)
• How to Make Report Card in Excel (Download Free Template)