Pivot Tables ทรงพลังสำหรับ Data Analysis ด้วย Advanced Techniques เช่น Calculated Fields และ Multiple Data Sources จะปลดล็อก Insights เพิ่มเติม บทความนี้อธิบายและสาธิต Advanced Pivot Table Techniques โฟกัส Calculated Fields และ Multiple Data Sources ใน Pivot Table
สร้าง Calculated Fields ใน Pivot Tables
Calculated Fields สร้าง Custom Calculations ภายใน Pivot Table จากข้อมูลที่มีอยู่ เพิ่มคอลัมน์ใหม่ใน Pivot Table ค่ามาจาก Existing Fields ใช้เมื่อต้องการคำนวณบนข้อมูลโดยไม่แก้ไข Dataset ต้นฉบับ
คำนวณ Commission 10% จาก Sales โดย Calculated Field ใน Pivot Table ต้องสร้าง Pivot Table ก่อน
Step 1: Create a Pivot Table.
- เลือก Data Range
- แท็บ Insert >> Pivot Table
- เลือกที่วาง Pivot Table (New/Existing Worksheet)
ลาก Fields ที่จำเป็นไป Rows, Columns, Values, Filters
Step 2: Insert a Calculated Field.
- คลิกที่ Pivot Table ใดๆ
- แท็บ PivotTable Analyze >> Fields, Items & Sets >> Calculated Field
- ใน Insert Calculated Field dialog:
- Name: ใส่ชื่อ Calculated Field: Commission on Sales (10%)
- Formula: ใส่สูตร:
- = 0.01 * ‘Total Sales’
- คลิก Add >> OK เพิ่ม Field ใน Pivot Table
คอลัมน์ Calculated Field ใหม่ Commission on Sales (10%) จะเพิ่มใน Pivot Table
Tip: Calculated Fields เหมาะกับ Additive Calculations แต่ Non-additive หรือ Complex Calculations ควรใช้ Power Pivot
สร้าง Pivot Tables จาก Multiple Data Sources
รวมข้อมูลจากหลายตาราง (Sources) ใน Pivot Table เดียว วิเคราะห์ Dataset ครอบคลุมมากขึ้นโดยไม่ Merge Tables ด้วยมือ
สมมติมี Multiple Data Sources: Sales Data (ข้อมูลขาย) และ Product Data (ข้อมูลสินค้า) จะแสดงการใช้ Multiple Sources ใน Pivot Table
Method 1: ใช้ Excel Built-in Data Model
ใช้ Data Model Feature ใช้ Multiple Sources ใน Pivot Table Excel 2013+ มี Built-in Data Model
Step 1: Set Up Each Table in Excel.
- เลือก Cell Range >> Insert >> Table
- ตั้งชื่อตารางแต่ละอันเพื่อเข้าใจง่าย (เช่น Sales_Data สำหรับ Sales, Products_Table สำหรับ Products)
Step 2: Insert Pivot Table ใช้ Data Model.
- เลือก Table >> Insert >> PivotTable
- ใน Create PivotTable dialog:
- เลือกที่วาง Pivot Table
- ติ๊ก Add this data to the Data Model
- OK
Step 3: Create Relationships.
- ไป Data >> Data Tools >> Relationships
- ใน Manage Relationships dialog:
- คลิก New เลือก Product_ID จาก Sales และ Products สร้าง Relationship
- OK บันทึก Relationship
Step 4: Build the Pivot Table.
- ใน PivotTable Fields จะเห็น Fields จากทั้ง 2 ตาราง
- ลาก Category และ Product จาก Product_Table ไป Rows
- ลาก Unit sold จาก Sales_Data ไป Columns
- ลาก Total Sales จาก Sales_Data ไป Values ดู Quantity และ Sales โดย Product
ด้วย Relationships ที่ตั้งค่า Pivot Table ดึง Fields จากทั้ง 2 ตาราง แสดง Sales Summary, Product Names, Categories โดยไม่ Merge ด้วยมือ
Method 2: ใช้ Power Pivot สำหรับ Multiple Data Sources
Power Pivot Add-in (Excel 2016, 2019, Office 365 Pro/Enterprise) สร้าง Relationships ระหว่างตารางจาก Sources ต่างๆ ทำให้สร้าง Pivot Table จากหลายตารางและ Analyze ข้ามตารางได้
Step 1: Enable Power Pivot (ถ้ายังไม่ Enable)
- File >> Options >> Add-ins
- ด้านล่าง Manage Dropdown >> COM Add-ins >> Go
- ติ๊ก Microsoft Power Pivot for Excel >> OK
Step 2: Load Data into Power Pivot.
- Data >> Get Data Import จาก Sources ต่างๆ (Multiple Sheets, External DB, CSV)
- เลือก Data Source (Excel Workbook, SQL Server, Text/CSV ฯลฯ) คลิก Load To
- ใน Import Data dialog:
- เลือก Table
- New Worksheet
- ติ๊ก Add this data to the Data Model
Note: แต่ละตารางที่ Load ต้องมี Unique Identifier (Product ID, Customer ID) เพื่อสร้าง Relationships
Step 3: Create Relationships between Tables.
- Power Pivot >> Manage เปิด Power Pivot Editor
- ไป Diagram View ดู Tables แบบ Visual
- Drag-and-drop Fields ระหว่าง Tables สร้าง Relationships เชื่อม Product ID จาก Sales Table ไป Product ID ใน Product Table
- Home ใน Power Pivot >> PivotTable
- เลือกที่วาง Pivot Table (เช่น New Worksheet) OK
- ใน Pivot Table Field List จะเห็น Fields จาก Tables ทั้งหมด ใช้สร้าง Report ดึงข้อมูลจาก Multiple Sources
- ลาก Product จาก Product_Table ไป Rows
- ลาก Unit Sold จาก Sales_Data ไป Columns
- ลาก Total Sales จาก Sales_Data ไป Values
สรุป
Advanced Techniques นี้ให้ Customize Analysis และ Integrate Multiple Data Sources ทำให้ Pivot Tables มี Insight มากขึ้น Mastering Calculated Fields และ Consolidating Multiple Sources ใน Pivot Tables ช่วยวิเคราะห์ Complex Datasets ได้มีประสิทธิภาพ ได้ Insights เร็วขึ้น จัดการข้อมูลดีขึ้น
Image by councilcle from Pixabay
Get FREE Advanced Excel Exercises with Solutions!
