คำนวณ Weighted Average ใน Pivot Table: เทคนิค Helper Column แบบละเอียด

การคำนวณ Weighted Average (ค่าเฉลี่ยน้ำหนัก) ใน Pivot Table ของ Excel ค่อนข้างซับซ้อน เพราะไม่สามารถใช้ฟังก์ชัน Excel ธรรมดา (เช่น SUMPRODUCT/SUM) ได้โดยตรงใน Pivot Table บทความนี้จะแนะนำ วิธีง่ายๆ ด้วย Helper Column (คอลัมน์ช่วย) ที่แก้ปัญหาได้อย่างมีประสิทธิภาพ

ดาวน์โหลดไฟล์ฝึกปฏิบัติ

ดาวน์โหลดไฟล์ตัวอย่างที่ใช้ในบทความนี้ได้ที่นี่

วิธีง่าย: เพิ่ม Helper Column คำนวณ Weighted Average ใน Pivot Table

หลักการ: ทำไมต้อง Helper Column?

Weighted Average คือค่าเฉลี่ยที่แต่ละค่ามีน้ำหนัก (Weight) ต่างกัน ช่วยให้ได้ค่าเฉลี่ยที่สะท้อนความสำคัญสัมพัทธ์ของแต่ละจำนวนได้แม่นยำกว่าค่าเฉลี่ยธรรมดา ปกติคำนวณด้วย SUMPRODUCT / SUM แต่ใน Pivot Table ใช้ฟังก์ชันไม่ได้ → วิธีแก้คือ เพิ่มคอลัมน์คำนวณไว้ในข้อมูลต้นทาง (Source Data) ก่อนสร้าง Pivot Table

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

มีข้อมูลการขายสินค้าค้าขายรายวัน ต้องการหา ราคาเฉลี่ยน้ำหนัก (Weighted Average Price) ของแต่ละสินค้าใน Pivot Table

ขั้นตอนที่ 1: เพิ่ม Helper Column (Sales Amount)

• เพิ่มคอลัมน์ใหม่ชื่อ 'Sales Amount' ในตารางข้อมูล
• ที่เซลล์แรกของคอลัมน์ใหม่ ใส่สูตร: =D5*E5 (ราคา × น้ำหนัก/ปริมาณ)

• ลาก Fill Handle (+) คัดลอกสูตรลงทั้งคอลัมน์

• ได้คอลัมน์ Sales Amount คำนวณครบแล้ว

ขั้นตอนที่ 2: สร้าง Pivot Table

• คลิกที่เซลล์ใดๆ ในข้อมูล (B4:F14)
• ไป Insert > Pivot Table > From Table/Range

• ในหน้าต่าง 'PivotTable from table or range' ตรวจสอบ Table/Range ให้ถูกต้อง > OK

• Pivot Table สร้างขึ้นที่ Sheet ใหม่ ตั้งค่า PivotTable Fields ตามภาพ

• ได้ Pivot Table พร้อมคอลัมน์ Sales Amount

ขั้นตอนที่ 3: สร้าง Calculated Field สำหรับ Weighted Average

• คลิกที่ Pivot Table
• ไป Pivot Table Analyze > Fields, Items, & Sets > Calculated Field

• ในหน้าต่าง Insert Calculated Field:

• Name: ใส่ 'Weighted Average'
• Formula: ใส่ Sales Amount / Weight (หาร Helper Column ด้วยน้ำหนัก)
• กด OK

• ได้ Weighted Average Price ของแต่ละสินค้าแสดงที่แถว Subtotal ของ Pivot Table

อ่านเพิ่ม: Calculate Conditional Weighted Average with Multiple Conditions in Excel

สรุป

บทความนี้อธิบายวิธีคำนวณ Weighted Average ใน Pivot Table ด้วยเทคนิค Helper Column ซึ่งเป็นวิธีที่เรียบง่ายและทำงานได้จริง เพียงเพิ่มคอลัมน์คำนวณ (ราคา × น้ำหนัก) ในข้อมูลต้นทาง แล้วสร้าง Calculated Field หาร Sales Amount ด้วย Weight ก็ได้ค่าเฉลี่ยน้ำหนักที่ถูกต้องทันที หวังว่าจะช่วยแก้ปัญหาของคุณได้ครับ

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

• How to Create a Weighted Scoring Model in Excel (4 Suitable Examples)
• Assigning Weights to Variables in Excel (3 Useful Examples)
• Calculate Weighted Moving Average in Excel (3 Methods)
• How to calculate weighted average in Excel with percentages (2 ways)