Master PivotTables:5 เทคนิคขั้นสูงเพื่อข้อมูลเชิงลึกอันทรงพลัง

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

ในบทช่วยสอนนี้ เราจะสำรวจเคล็ดลับสำคัญ 5 ข้อของ PivotTable เพื่อปลดล็อกข้อมูลเชิงลึกในข้อมูลของคุณ

ชุดข้อมูลตัวอย่าง (ตารางการขาย)

ลองนึกภาพคุณมีข้อมูลการขายใน Excel เราจะใช้ชุดข้อมูลนี้สำหรับตัวอย่างทั้งหมดด้านล่าง

แปลงข้อมูลเป็นตาราง:

• เลือกชุดข้อมูลทั้งหมด
• ไปที่ ส่วนแทรก แท็บ>> เลือก ตาราง .
• ทำเครื่องหมายที่ “ตารางของฉันมีส่วนหัว” .
• คลิก ตกลง .
• ไปที่ การออกแบบตาราง>> ตั้งชื่อตาราง “SalesData”.

สร้าง PivotTable พื้นฐาน:

1. คลิกเซลล์ใดก็ได้ใน SalesData
2. ไปที่ ส่วนแทรก แท็บ>> เลือก PivotTable .
3. เลือก แผ่นงานใหม่ และคลิก ตกลง .

จากฟิลด์ PivotTable รายการ:

• ลาก ภูมิภาค เป็น แถว .
• ลาก หมวดหมู่ ไปที่ คอลัมน์ .
• ลากรายได้ เป็นค่า .

ตอนนี้คุณมีรายงานรายได้มาตรฐานตามภูมิภาคและหมวดหมู่แล้ว เราจะเปลี่ยนมันให้เป็นสิ่งที่ชาญฉลาดยิ่งขึ้น

เคล็ดลับที่ 1:แทรกตัวแบ่งส่วนข้อมูลสำหรับการกรองเชิงโต้ตอบ

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

ขั้นตอน:

1. คลิกที่ใดก็ได้ภายใน PivotTable
2. ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก แทรกตัวแบ่งส่วนข้อมูล .
3. เลือกพนักงานขาย , ลูกค้า และ หมวดหมู่ จากนั้นคลิก ตกลง .

Excel จะแสดงกล่องตัวแบ่งส่วนข้อมูลสามกล่องพร้อมปุ่ม

• คลิกพนักงานขายเพื่อกรอง PivotTable เพื่อแสดงเฉพาะข้อมูลของบุคคลนั้น

• กด Ctrl ค้างไว้ ขณะที่คลิกเพื่อเลือกหลายชื่อ
• คลิกไอคอนช่องทางในตัวแบ่งส่วนข้อมูล (ด้านบนขวา) เพื่อล้างตัวกรอง

การเชื่อมต่อตัวแบ่งส่วนข้อมูลกับหลาย PivotTable:
หนึ่งในคุณสมบัติที่ทรงพลังที่สุดคือความสามารถในการเชื่อมต่อตัวแบ่งส่วนข้อมูลตัวเดียวกับ PivotTable หลายตัว

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

• เลือก PivotTable ทั้งหมด>> คลิก ตกลง .

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

การปรับแต่งลักษณะตัวแบ่งส่วนข้อมูล:

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

เคล็ดลับที่ 2:วิเคราะห์แนวโน้มเวลาทันทีด้วยไทม์ไลน์

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

ขั้นตอน:

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

คุณจะเห็นแถบแนวนอนพร้อมช่วงวันที่ของคุณ

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

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

เคล็ดลับ 3:จัดกลุ่มข้อมูลตามอายุหรือกลุ่มการขาย

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

จัดกลุ่มลูกค้าตามกลุ่มอายุ:

• สร้าง PivotTable ใหม่
• ลาก ลูกค้า_อายุ เป็น แถว .
• ลากรายได้ เป็นค่า .
• คลิกขวา อายุใดก็ได้ในป้ายกำกับแถวและเลือก กลุ่ม .

• ในกล่องโต้ตอบการจัดกลุ่ม ให้ตั้งค่า:
  • เริ่มต้นที่: 20
  • สิ้นสุดที่: 70
  • โดย: 10
• คลิก ตกลง . PivotTable จะแสดงกลุ่มอายุ

ตอนนี้คุณสามารถสังเกตได้ว่ากลุ่มอายุใดสร้างรายได้มากที่สุด คำสั่งซื้อที่ใหญ่ที่สุดมาจากลูกค้าที่มีอายุมากกว่าหรือน้อยกว่าใช่หรือไม่

จัดกลุ่มรายได้เป็นช่วงยอดขาย:

• สร้าง PivotTable อื่น
• ลากรายได้ เป็น แถว .
• ลากรายได้ เป็นค่า .
• ลาก ภูมิภาค ไปที่ คอลัมน์ .
• คลิกขวาที่มูลค่ารายได้ใดๆ ในป้ายกำกับแถว และเลือก กลุ่ม... .
• กำหนดขอบเขตที่เหมาะสม:
  • เริ่มต้นที่: 0
  • สิ้นสุดที่: 40000
  • โดย: 2000
• คลิก ตกลง .

คุณจะเห็นวงดนตรีดังต่อไปนี้

ระดับราคาผลิตภัณฑ์:

• เริ่มต้นที่: 0
• สิ้นสุดที่: 500
• โดย: 100

ตัวอย่างเช่น 0–100 (งบประมาณ), 100–200 (ประหยัด), 200–300 (มาตรฐาน), 300–400 (พรีเมียม), 400–500 (หรูหรา)

เคล็ดลับที่ 4:สร้างการวัดแบบกำหนดเองด้วยฟิลด์จากการคำนวณ

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

ขั้นตอน:

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

• ใน แทรกฟิลด์ที่คำนวณได้ กล่องโต้ตอบ:
  • ป้อน ชื่อ สำหรับสาขาของคุณ (เช่น “ส่วนต่างกำไร”)
  • คลิกในสูตร และสร้างสูตรของคุณ
  • ดับเบิลคลิกชื่อฟิลด์จากรายการฟิลด์เพื่อแทรก
  • ใช้ตัวดำเนินการมาตรฐาน:+ (บวก), − (ลบ), * (คูณ), / (หาร)
• คลิก ตกลง .

กำไร:

• ชื่อ: กำไร
• สูตร:

เปอร์เซ็นต์อัตรากำไร:

• ชื่อ: อัตรากำไรขั้นต้น
• สูตร:

= (Revenue - Cost) / Revenue

• จัดรูปแบบเป็น เปอร์เซ็นต์ .

สร้าง "กำไร" เป็น 30% ของรายได้:

• ชื่อ: กำไร 30% ของรายได้
• สูตร:

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

เคล็ดลับ 5:ดูเปอร์เซ็นต์และผลรวมรันด้วย “แสดงค่าเป็น”

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

เปอร์เซ็นต์ของรายได้ทั้งหมดตามภูมิภาค:

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

ตอนนี้แต่ละภูมิภาคจะแสดงเปอร์เซ็นต์ของรายได้โดยรวมที่มาจากภูมิภาคนี้ ซึ่งตีความได้ง่ายกว่าตัวเลขดิบเมื่อเปรียบเทียบกลุ่มต่างๆ

คุณยังสามารถเลือก:

• % ของผลรวมคอลัมน์ เพื่อดูแต่ละภูมิภาคเป็นเปอร์เซ็นต์ของคอลัมน์
• % ของผลรวมแถว เพื่อดูแต่ละหมวดหมู่เป็นเปอร์เซ็นต์ภายในแต่ละภูมิภาค

เคล็ดลับ:ใช้การจัดรูปแบบ PivotTable เพื่อความเข้าใจที่ชัดเจน

PivotTable ที่มีรูปแบบที่ดีทำให้เข้าใจข้อมูลเชิงลึกได้ง่ายขึ้น

เพิ่มรูปแบบตัวเลข:

• คลิกขวาที่ค่าใดก็ได้>> รูปแบบตัวเลข>> เลือกสกุลเงิน เปอร์เซ็นต์ ฯลฯ

แสดงผู้มีส่วนร่วม 5 อันดับแรก:

1. คลิกเมนูแบบเลื่อนลงถัดจากป้ายกำกับแถว
2. เลือก ตัวกรองค่า>> เลือก 10 อันดับแรก>> เปลี่ยนเป็น 5 อันดับแรก
3. เลือก ผลรวมของรายได้ .

ใช้การจัดรูปแบบตามเงื่อนไข:

1. เลือกค่า PivotTable
2. ไปที่หน้าแรก แท็บ>> เลือก การจัดรูปแบบตามเงื่อนไข .
3. เลือกระดับสี แถบข้อมูล หรือชุดไอคอน

ข้อมูลเชิงลึก: การเน้นประสิทธิภาพสูงสุดช่วยให้ระบุสินค้าขายดีหรือภูมิภาคที่แข็งแกร่งที่สุดได้อย่างรวดเร็ว

นำทุกอย่างมารวมกัน:แดชบอร์ดการขายเชิงโต้ตอบที่เรียบง่าย

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

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

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

บทสรุป

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