รู้หรือไม่ว่า PivotTable สามารถสร้างมาจาก Data Source หลายตารางได้! ในบทความนี้เราจะมาดูกันว่าทำยังไง?

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

วิธีที่ 1 : ใช้ Multiple Consolidation Ranges (PivotTable Wizard)

  • ข้อดี ใช้ได้กับ Excel Version ไหนก็ได้
  • ข้อดี ทำไม่ยากมากนัก
  • ข้อเสีย ทั้งสองตารางต้องมี Field เหมือนกัน
  • ข้อเสีย ผลลัพธ์ใน Pivot นั้นจำกัดมาก ปรับแต่งแทบไม่ได้

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

  • กด Alt, D, P เพื่อเรียก PivotTable Wizard แล้วเลือก Multiple Consolidation Ranges
    consolidate-range
  • เพื่อความง่ายเลือก Create a single page field for me ไปเลยก็ได้ครับ
  • เลือก Range ที่ต้องการ แล้วกด Add โดยทำทีละ source พอเสร็จแล้วก็กด Next จนจบกระบวนการ
    select-range
  • คุณจะได้ PivotTable หน้าตาประหลาดมา 1 อัน ดังนี้
    first-result2
  • ผลลัพธ์ที่ได้ มีข้อจำกัดดังนี้ 
    • 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 มาใส่เป็นเดือนได้)
      first-result3

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

วิธีที่ 2 : ใช้การสร้าง Query มาช่วย

Query คือภาษาด้าน Database ซึ่งเราสามารถใช้ Excel เพื่อเรียกใช้ภาษา Query นี้ได้ด้วย ซึ่งสามารถทำได้ 2 แบบ คือ

  1. เขียนภาษา Query เอง ใน Microsoft Query  (Excel ทำได้ทุก Version)
  2. ใช้ Power Query ช่วยสร้าง
    รองรับกับ Excel บาง version
    – สำหรับ Office 2016 มี PowerQuery ฝังมาให้เลย อยู่ใน Data=>Query
    – สำหรับ version อื่นๆ ลองโหลดได้ที่นี่ https://www.microsoft.com/en-us/download/details.aspx?id=39379)

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

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

  1. เรียกใช้ Microsoft Query โดยไปที่ Data => Get External Data => From Other Source => From Microsoft Query
  2. ดับเบิ้ลคลิ๊กที่ Excel File* แล้วเลือกไฟล์ Excel ที่ต้องการ
    • ปกติเลือกไฟล์ที่ทำงานอยู่ก็ได้ แต่ถ้าใครกดไม่ได้ให้เปิดจากไฟล์อื่น)
    • ถ้ามี Error บอกว่าหา Table ไม่เจอ ให้เลือก Option แล้วติ๊ก System Table ด้วย
      table-option
  3. เลือก Table ซักอัน แล้วกด > เพื่อ Add Field (ในที่นี้ของผมมีปัญหากับ field ภาษาไทย ผมเลยต้องเปลี่ยนชื่อ Field เป็นภาษาอังกฤษ)
    Microsoft Query-01
  4. กด Next ไปจนหน้าสุดท้าย ให้เปลี่ยนเป็นเลือกดู Query ก่อน
    Microsoft Query-02
  5. กด SQL แล้วแก้ Code ให้เป็นดังนี้
    SELECT *
    FROM `pathของไฟล์`.`ชื่อชีทแรก(ตามที่มันแสดง)` `ชื่อชีทแรก(ตามที่มันแสดง)`
    UNION ALL
    SELECT *
    FROM `pathของไฟล์`.`ชื่อชีทสอง(ตามที่มันแสดง)` `ชื่อชีทสอง(ตามที่มันแสดง)`
    Microsoft Query-03
  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
    Microsoft Query-04
  9. เลือกให้ส่งเข้า PivotTable ไปเลยก็ได้ (ถ้าเลือกเป็น Table มันจะออกมาเป็นตารางก่อน)
    Microsoft Query-05
  10. จากนั้นก็หมุน PivotTable ทุกอย่างได้ตามปกติ “แบบไร้ข้อจำกัด” ครับ!
    Microsoft Query-06

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

ซึ่งเครื่องมือ PowerQuery ก็ทำงานคล้ายๆ แบบ Microsoft Query แต่ดูง่ายกว่าครับ (มีปุ่มสั่งต่อไฟล์ได้เลย โดยไม่ต้องเขียนภาษา SQL) ลองไปโหลดเล่นดูได้เลยครับ
Power Query Editor

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

วิธีนี้ก็ทำได้ทุกอย่างตามต้องการเช่นกัน แถมใช้งานง่ายกว่า Microsoft Query ด้วย แต่ วิธีนี้ผมจะยังไม่ขอพูดถึงมากแล้วกันครับ เพราะยังมีคนใช้ Excel ใหม่ๆ ที่รองรับ PowerPivot น้อยอยู่

ใครสนใจลองไปหาตามนี้ได้ครับ

http://www.contextures.com/excelpowerpivotresources.html