คู่มือขั้นตอนสร้าง Calculated Fields ใน Pivot Table Data Models

หากคุณมองหาวิธีสร้าง Calculated Field ใน Pivot Table Data Model บทความนี้จะตอบโจทย์ เริ่มบทความหลักเพื่อรู้ขั้นตอนละเอียด

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

Calculated Field คืออะไร?

Calculated Field เป็นคุณสมบัติสำคัญของ PivotTable หรือ Pivot Chart เรียกอีกอย่างว่า Measure Calculated Field หรือ Measure คือฟิลด์ที่สร้างขึ้นผ่านสูตร DAX หากต้องการคำนวณคุณสมบัติเพิ่มเติมของชุดข้อมูลหลังทำ PivotTable เสร็จ สามารถใช้สูตรโดยสร้าง Calculated Field ที่นี่ Calculated Field โดยทั่วไปมี 2 ประเภท: Implicit Calculated Field และ Explicit Calculated Field Implicit Calculated Field สร้างด้วย PivotTable Field ส่วน Explicit Calculated Field สร้างด้วยแท็บ PivotTable Analyze

4 ตัวอย่างสร้าง Calculated Field ใน Pivot Table Data Model

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

ใช้ Microsoft Excel 365 สร้างบทความนี้ สามารถใช้เวอร์ชันอื่นตามสะดวก

ตัวอย่างที่ 1: สร้าง Implicit Calculated Field ใน Pivot Table Data Model

ตัวอย่างนี้จะแปลงชุดข้อมูลเป็น PivotTable แล้วแทรก Calculated Field คำนวณโบนัส 30% ของเงินเดือน

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

สร้าง PivotTable เพื่อแทรก Calculated Field ใน Data Model

• ไป Insert >> PivotTable dropdown >> From Table/Range

กล่องโต้ตอบ PivotTable from table or range จะปรากฏ

• เลือกชุดข้อมูลเป็น Table/Range คลิก New Worksheet
• ติ๊ก Add this data to the Data Model กด OK

จะไปที่แผ่นใหม่ มี 2 ส่วน: PivotTable1 และ PivotTable Fields

• ลาก Employee ไป Rows และ Salary ไป Values

PivotTable ต่อไปจะปรากฏด้านซ้าย

ขั้นตอนที่ 2: เพิ่ม Measure เพื่อแทรก Calculated Field

ขั้นตอนนี้เพิ่ม Measure หรือ Calculated Field ด้วยสูตร DAX

• คลิกขวา ที่ชื่อ PivotTable (ที่นี่คือ Range)
• เลือก Add Measure

กล่องโต้ตอบ Measure จะโผล่

• พิมพ์ Bonus (หรือชื่อที่ต้องการ) ใน Measure Name
• ในกล่อง Formula หลังเครื่องหมาย = พิมพ์สูตร ที่นี่ต้องการฟิลด์ Sum of Salary พิมพ์ s จะเห็นตัวเลือก เลือก [Sum of Salary]

• เขียนสูตรสมบูรณ์

คูณ 0.03 กับ [Sum of Salary] เพราะ โบนัส เป็น 30% ของเงินเดือนแต่ละคน

• กด OK

วิธีนี้ Calculated Field Bonus จะปรากฏในรายการฟิลด์

• ลากฟิลด์ Bonus ไป Values

คอลัมน์ใหม่ Bonus จะปรากฏพร้อมค่าที่คำนวณ

หลังเพิ่มสัญลักษณ์สกุลเงินในเงินเดือนและโบนัส จะได้ภาพดังนี้

อ่านเพิ่มเติม: วิธีสร้าง Data Model ใน Excel (3 วิธีที่ใช้ได้)

ตัวอย่างที่ 2: สร้าง Implicit Calculated Field ด้วยแท็บ Power Pivot

ตัวอย่างนี้สร้าง Calculated Field Bonus ด้วยสูตร DAX ช่วยด้วยแท็บ Power Pivot

ขั้นตอนที่ 1: เปิดใช้งาน Power Pivot

หากไม่เห็นแท็บ Power Pivot ทำตามขั้นตอนนี้เพื่อเปิดใช้งาน

• ไปที่ File

• เลือก Options

จะได้วิซาร์ด Excel Options

• ไปแท็บ Add-ins คลิกสัญลักษณ์ดรอปดาวน์ข้าง Manage

• เลือก COM Add-ins กด Go

วิซาร์ด COM Add-ins จะเปิด

• ติ๊ก Microsoft Power Pivot for Excel กด OK

ผลคือ จะเห็นแท็บ Power Pivot ในสมุดงาน

ขั้นตอนที่ 2: เพิ่ม Measure แทรก Calculated Field

• ทำตาม ขั้นตอนที่ 1 ของ ตัวอย่างที่ 1 เปิดแผ่นดังภาพ

• ลาก Employee ไป Rows และ Salary ไป Values

PivotTable จะปรากฏด้านซ้าย

• เลือกเซลล์ใดใน PivotTable ไป Power Pivot >> Measures dropdown >> New Measure

จะได้กล่องโต้ตอบ Measure

• ตั้ง Measure name เป็น Bonus 1
• เขียนสูตรในกล่อง Formula

สามารถเปลี่ยนรูปแบบที่นี่ได้

• เลือก Currency เป็น Category ระบุ Decimal places ไป 2 ตำแหน่ง
• กด OK

คอลัมน์ Bonus 1 พร้อมโบนัสพนักงาน จะเพิ่มเข้า PivotTable อัตโนมัติ

เพิ่มสัญลักษณ์ Currency ในเงินเดือนและขอบตาราง จะได้ลุกลักษณ์สุดท้าย

อ่านเพิ่มเติม: วิธีดึงข้อมูลจาก Data Model ใน Excel (2 วิธีง่าย)

ตัวอย่างที่ 3: สร้าง Explicit Calculated Field ใน Pivot Table Data Model

ใช้ Calculated Field โดยตรงสร้าง Explicit Calculated Field ด้วยสูตร คำนวณโบนัสพนักงาน

ขั้นตอน:

• ทำตาม ขั้นตอนที่ 1 ของ ตัวอย่างที่ 1 เปิดแผ่นดังภาพ

• ลาก Employee ไป Rows และ Salary ไป Values

PivotTable จะปรากฏด้านซ้าย

• เลือกเซลล์ใน PivotTable ไป PivotTable Analyze >> Calculations group >> Fields, Items & Sets dropdown >> Calculated Field

กล่องโต้ตอบ Insert Calculated Field จะโผล่

• ตั้ง Name เป็น Bonus 2
• ในกล่อง Formula หลังเครื่องหมาย = แทรกฟิลด์ที่ต้องการ

• คลิกฟิลด์ Salary กด Insert Field

Salary จะปรากฏในกล่อง Formula

• เขียนสูตรให้สมบูรณ์

• กด OK

สุดท้าย คอลัมน์ใหม่พร้อมค่าโบนัส จะเพิ่มใน Pivot Table

เพิ่มสัญลักษณ์ Currency ในเงินเดือนและขอบตาราง ได้ลุกลักษณ์สุดท้าย

อ่านเพิ่มเติม: ลบ Data Model จาก Pivot Table ใน Excel (ขั้นตอนง่าย)

ตัวอย่างที่ 4: ใช้ Explicit Calculated Field สำหรับสูตรซับซ้อนใน Pivot Table Data Model

คำนวณโบนัสด้วยเงื่อนไข: พนักงานที่วันทำงานมากกว่า 250 ได้โบนัส 50% ของเงินเดือน ส่วนที่เหลือได้ 30%** (ตรวจสอบ: ต้นฉบับเขียน 50% ทั้งคู่ แต่อาจเป็น typo ควรเป็น 30% ตามตัวอย่างก่อนหน้า) ใช้ IF function