การเรียกใช้งานคำสั่ง Pivot table อยู่ที่แท็บใด

การทำ PivotTable จากหลายตารางนั้น ทำได้หลายวิธี แต่ละวิธีก็มีข้อดีข้อเสีย-ข้อจำกัดต่างกันดังนี้ครับ มาดูรายละเอียดกันดีกว่า

สารบัญ

• วิธีที่ 1 : ใช้ Data Model & PowerPivot
• วิธีที่ 2 : ใช้ Power Query (เหมาะกับ Excel 2013 ขึ้นไป)
  • 2.1 เตรียมตารางแยกหลายๆ ตารางที่มีความสัมพันธ์กัน แล้วส่งเข้าสู่ Data Model
  • 2.2 รวมข้อมูลทั้งหมดให้อยู่ในตารางเดียวกันก่อน แล้วค่อยส่งเข้า Pivot Table แบบปกติ
• วิธีที่ 3 : ใช้ Microsoft Query
• วิธีที่ 4 : ใช้ Multiple Consolidation Ranges (PivotTable Wizard เหมาะกับ Excel เก่า)
  • วิธีทำแบบสั้นๆ
• เพื่อนๆ ชอบแบบไหน หรือผสมแบบไหนดี?

วิธีที่ 1 : ใช้ Data Model & PowerPivot

วิธีนี้เป็นการสร้าง PivotTable จากข้อมูลหลายตารางที่มีความสัมพันธ์กัน (เรียกว่า Data Model) ซึ่งเป็น Concept เดียวกับโปรแกรม Power BI เลยครับ หากใช้วิธีนี้เราก็จะสามารถสร้าง Pivot Table แล้วเลือก Field ข้ามตารางได้ โดยที่ไม่จำเป็นต้องเอาข้อมูลมารวมเป็นตารางเดียวกันเลยด้วยซ้ำ

อย่างไรก็ตามวิธีนี้จะไม่สามารถเอาตารางมาต่อแถวกันได้นะครับ

นอกจานี้ วิธีนี้ต้องใช้ Excel version 2013 ขึ้นไปครับ และถ้าจะให้ดีควรจะ Enable Add-in Power Pivot ซะก่อนจึงจะใช้งานได้สะดวก ซึ่งทำตามดังนี้

แล้วเลือก PowerPivot Add-in ซะตามรูป

จากนั้นเราจะต้องเอาตารางแต่ละอัน Add เข้า Data Model ซะก่อน เช่น มีทั้งหมด 3 ตาราง เช่น
1. ตารางการขาย 2. ตารางรหัสพนักงาน 3. ตารางลูกค้า

เราต้องเอาตารางเข้า Data Model โดยเลือกข้อมูล แล้วแปลงแต่ละตารางให้เป็น Table โดย Insert -> Table ซะก่อน แล้วค่อยกด Add To Data Model

จากนั้นมันจะเอาข้อมูลเข้าสู่หน้าต่างของ PowerPivot

จากนั้นให้กด Switch to workbook เพื่อกลับออกมาใน Excel แล้วกด Add To Data Model ให้ครบทุกตารางที่มีความสัมพันธ์กัน

พอ add เข้าไปหลายตาราง ในหน้า Power Pivot จะมีหลายชีทด้วย

จากนั้นให้กดดู Diagram View เพื่อผูกความสัมพันธ์ตารางต่างๆ เข้าด้วยกัน

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

ทำให้ครบจะได้แบบนี้ จากนั้นก็กดสร้าง PivotTable ได้แล้ว

เราก็จะสามารถใช้ PivotTable Model Data Model แล้วยังสามารถลาก Field ข้ามตารางได้เลย โดยไม่ต้องเอาข้อมูลมารวมกันเป็นตารางเดียวกันอีก

สำหรับรายละเอียดว่า Concept ของ Data Model ทำงานยังไง ให้ไปอ่านใน Series Power BI ได้ครับ

หมายเหตุ : นอกจากวิธีนี้ เรายังกดสร้าง PivotTable โดยใช้ Data Model แบบไม่เปิด PowerPivot ก็ได้ โดยกดสร้าง PivotTable จากตารางโดยตรง แล้วติ๊ก Add to Data Model

แต่ข้อเสียของวิธีนี้ก็คือการกำหนด Relationship จะทำยากกว่า (เพราะไม่เห็นภาพ) โดยต้องใช้เมนูนี้

วิธีที่ 2 : ใช้ Power Query (เหมาะกับ Excel 2013 ขึ้นไป)

การใช้ Power Query นั้นสามารถทำได้ 2 ลักษณะ คือ

2.1 เตรียมตารางแยกหลายๆ ตารางที่มีความสัมพันธ์กัน แล้วส่งเข้าสู่ Data Model

ถ้าใช้แบบนี้ ก็เป็นการใช้ Power Query แค่จัดหน้าตาข้อมูลให้เหมาะสมเท่านั้น เพิ่มเติมแค่ตอนกด Close & Load To… ให้เลือกเป็น Connection Only (เพราะเราจะไม่ Load ผลลัพธ์ออกมาใน Excel ปกติ) แต่ให้ติ๊กเลือกว่า Add to Data Model เพื่อให้มันส่งผลลัพธ์เข้า Data Model เท่านั้นเอง

2.2 รวมข้อมูลทั้งหมดให้อยู่ในตารางเดียวกันก่อน แล้วค่อยส่งเข้า Pivot Table แบบปกติ

คำว่ารวมให้อยู่ในตารางเดียว จริงๆ แล้วก็มี 2 แบบใหญ่ๆ นั่นคือ รวมคอลัมน์ กับ รวมแถว

• การรวมข้อมูลหลายๆ ตารางมาต่อแถวกัน (คล้ายการที่เรา Copy Paste ) เรียกว่า Append
• การรวมข้อมูลหลายๆ ตารางมารวมคอลัมน์กัน (คล้ายๆ กับใช้ VLOOKUP) เรียกว่า Merge

จากนั้นค่อยส่งผลลัพธ์ที่รวมตารางเสร็จแล้วเข้าสู่ Pivot Table อีกทีครับ

Append เพื่อรวมตารางแบบเพิ่มแถว

วิธี Append ก็ให้สร้าง Query 2 ตารางขึ้นมาก่อน ตารางไหนยังไม่เอาผลลัพธ์ออกมาก็ทำแบบ Connection Only ไว้ เช่น อาจทำให้เป็น Connection Only ทั้งคู่เลย แล้วกดคลิ๊กขวาที่ Query ตัวแรก แล้วเลือก Append กับ Query ตัวอื่นๆ

แล้วเราก็จะได้ Query ใหม่ ที่เป็นผลลัพธ์จากการ Append 2 ตารางเข้าด้วยกัน (เอามาต่อแถวกัน)

จากนั้นก็เอาผลลัพธ์ออกไปใช้ใน Pivot Table ได้

ถ้ามีหลายตารางที่มีความสัมพันธ์กันจะติ๊ก Add this Data to the Data Model ด้วยก็ได้

Merge เพื่อรวมตารางแบบเพิ่มคอลัมน์

การจะ Merge ก็ให้สร้าง Query 2 ตารางขึ้นมาก่อน ตารางไหนยังไม่เอาผลลัพธ์ออกมาก็ทำแบบ Connection Only ไว้

จากนั้นกดที่ Query แรกแล้วคลิ๊กขวาเลือก Merge กับ Query ตัวที่สอง ที่สำคัญคือต้องเลือกด้วยว่าคอลัมน์ไหนคือตัวเชื่อมกัน และเลือก JoinKind ด้วย ซึ่งปกติก็จะเป็น Left Outer Join แบบนี้แหละ ok ได้เลย

ผลลัพธ์จะออกมาเป็น Table ก่อน

ให้เราคลิ๊กที่มุมขวาบนของคอลัมน์เพื่อ Expand เอาข้อมูลออกมา

แล้วเราก็จะได้ผลลัพธ์คล้ายๆ กับ VLOOKUP เลย (แต่ถ้าตารางอ้างอิงมีหลายบรรทัด มันจะมาทุกบรรทัดนะ ต่างจาก VLOOKUP ที่จะมาแค่อันบนสุด)

จากนั้นก็ Load เอาผลลัพธ์เข้า Pivot Table ได้เลย

วิธีที่ 3 : ใช้ Microsoft Query

วิธีสร้าง PivotTable จากข้อมูลหลายตารางด้วย Microsoft Query เหมาะกับคนที่ไม่มี Power Query ให้ใช้ เช่นคนที่มี Excel Version เก่ามากๆ นั่นเองครับ

วิธีการทำมีดังนี้

1. เรียกใช้ Microsoft Query โดยไปที่ Data => Get External Data => From Other Source => From Microsoft Query (Excel Version ใหม่จะไม่มีเมนูนี้แล้ว)
2. ดับเบิ้ลคลิ๊กที่ Excel File* แล้วเลือกไฟล์ Excel ที่ต้องการ
   • ปกติเลือกไฟล์ที่ทำงานอยู่ก็ได้ แต่ถ้าใครกดไม่ได้ให้เปิดจากไฟล์อื่น)
   • ถ้ามี Error บอกว่าหา Table ไม่เจอ ให้เลือก Option แล้วติ๊ก System Table ด้วย
     
     
3. เลือก Table ซักอัน แล้วกด > เพื่อ Add Field (ในที่นี้ของผมมีปัญหากับ field ภาษาไทย ผมเลยต้องเปลี่ยนชื่อ Field เป็นภาษาอังกฤษ)
   
   
4. กด Next ไปจนหน้าสุดท้าย ให้เปลี่ยนเป็นเลือกดู Query ก่อน
   
   
5. กด SQL แล้วแก้ Code ให้เป็นดังนี้
   SELECT *
   FROM ‘pathของไฟล์’.’ชื่อชีทแรก(ตามที่มันแสดง)’ ‘ชื่อชีทแรก(ตามที่มันแสดง)’
   UNION ALL
   SELECT *
   FROM ‘pathของไฟล์’.’ชื่อชีทสอง(ตามที่มันแสดง)’ ‘ชื่อชีทสอง(ตามที่มันแสดง)’
   
   
6. ของผมจะได้เป็นแบบนี้
   SELECT *
   FROM ‘D:\INWEXCEL\PIVOT\multiple-pivot-data2.xlsx’.’Month1$’ ‘Month1$’
   UNION ALL
   SELECT *
   FROM ‘D:\INWEXCEL\PIVOT\multiple-pivot-data2.xlsx’.’Month2$’ ‘Month2$’
7. แล้วกด OK แล้วมันจะบอกว่าไม่สามารถแสดงผลได้นะ ให้ OK อีกที
8. จากนั้นไปที่ file => return data to excel
   
   
9. เลือกให้ส่งเข้า PivotTable ไปเลยก็ได้ (ถ้าเลือกเป็น Table มันจะออกมาเป็นตารางก่อน)
   
   
10. จากนั้นก็หมุน PivotTable ทุกอย่างได้ตามปกติ
    
    

วิธีการใช้ Microsoft Query นี้ สามารถนำมาประยุกต์ได้เยอะแยะ เช่น มี 2 ตารางแยกกันอยู่ อยากเอามาผูกกันโดยไม่ต้องใช้ VLOOKUP ก็ยังได้เลยครับ โดยมีทั้งแบบลากเส้นเชื่อมเองใน Editor หรือจะเขียน SQL เอง ก็ได้ (ภาษา SQL ทำได้แทบทุกอย่าง แต่อาจต้องศึกษาเยอะหน่อย)

วิธีที่ 4 : ใช้ Multiple Consolidation Ranges (PivotTable Wizard เหมาะกับ Excel เก่า)

วิธีสร้าง PivotTable จากข้อมูลหลายตารางด้วยตัวเลือก Multiple Consolidation Ranges ในคำสั่ง PivotTable Wizard เป็นวิธีที่มีข้อจำกัดมากที่สุด ผมจึงไม่แนะนำครับ แค่จะเอาให้ดูเฉยๆ ว่าในอดีตมันทำแบบนี้ได้

วิธีทำแบบสั้นๆ

กด Alt, D, P เพื่อเรียก PivotTable Wizard แล้วเลือก Multiple Consolidation Ranges

เพื่อความง่ายเลือก Create a single page field for me ไปเลยก็ได้ครับ

เลือก Range ที่ต้องการ แล้วกด Add โดยทำทีละ source พอเสร็จแล้วก็กด Next จนจบกระบวนการ

คุณจะได้ PivotTable หน้าตาประหลาดมา 1 อัน ดังนี้

ผลลัพธ์ที่ได้ มีข้อจำกัดดังนี้ 

• Field แรกใน Data Source ต้องมาเป็น Row Item Label เสมอ
• Field ที่เหลืออันอื่นจะมาอยู่ใน Column Label เสมอ (มาเป็นชุดถ้าเอาออกต้องเอาออกหมดเลย)
• การสรุปผล เช่น SUM, COUNT เปลี่ยนได้ แต่เปลี่ยนแล้วเท่ากับเปลี่ยนให้กับทุก Field เลย ไม่สามารถทำให้แต่ละ Field ไม่เหมือนกันได้
• Field Page 1 แค่เป็นตัว Filter เลือกแต่ละ Data Source เฉยๆ เอาออกก็ได้

วิธีปรับแต่ง Data Source เพื่อทำให้แสดงผลดีขึ้นภายใต้ข้อจำกัดที่มี

• เอา Item ที่ต้องการไว้เป็น Row Item Label ไว้ Field แรกเสมอ
• ย้าย Field ที่ไม่ต้องการไปทางขวาๆ แล้วตอนเลือก Range ไม่ต้องเลือกมัน

ผมลองทำแล้ว ก็พอจะได้ผลลัพธ์ที่ ok ขึ้น ดังนี้ (ผมแยกแต่ละชีทเป็นคนละเดือน ดังนั้นเลยเอา Page1 มาใส่เป็นเดือนได้)

สรุปแล้วจะเห็นว่าวิธี Multiple Consolidation Ranges นั้นมีข้อจำกัดมากเลยนะครับ เห็นมะ อย่าใช้เลย 555

เพื่อนๆ ชอบแบบไหน หรือผสมแบบไหนดี?

ส่วนตัวผมชอบใช้ Power Query จัดข้อมูลให้เรียบร้อยก่อน แล้วค่อยส่งเข้า Data Model ไปทำต่อครับ พูดง่ายๆ คือ Step เหมือน Power BI เป๊ะเลย