วิธีรวมข้อมูล Excel จากหลายชีทเข้าด้วยกันเป็นตารางเดียวด้วย Power Query

by
Post Views: 13,400

Power Query เครื่องมือสำคัญของ Power BI และ Microsoft Excel 2016 | 2019 | 365 ที่จะช่วยจัดการข้อมูลให้ได้ตามต้องการ ไม่ว่าเป็นการรวบรวมข้อมูลจากหลายแหล่งเข้าด้วยกัน การเปลี่ยนประเภทข้อมูล การจัดตารางหรือคิวรี การจัดคอลัมน์ การจัดการข้อมูลที่บกพร่อง การรวมข้อมูล การแยกข้อมูล การปรับแต่งข้อมูล และการสร้างรูปแบบข้อมูล

จุดเด่นของ Power Query

  • รวบรวมข้อมูลได้จากหลายแหล่ง หลากหลายรูปแบบ
  • ใช้งานง่าย มีปุ่มเมนูให้ใช้งาน
  • ทุกการกระทำจะถูกบันทึกไว้เป็น Steps สามารถแก้ไข สลับลำดับ หรือลบ Steps ที่ทำไว้ได้อย่างง่ายดาย
  • รองรับการทำงานที่ Advance มากขึ้นด้วย M Code ซึ่งเป็นภาษาพิเศษของ Power Query

ในการใช้งาน Microsoft Excel เราอาจจะมีความจำเป็นที่จะรวมรวมข้อมูลจากหลาย ๆ ซีทเป็นตารางเดียว เพื่อให้สามารถนำไปวิเคราะห์ต่อ วันนี้จึงจะขอนำเสนอความสามารถของ Power Query เพื่อรวมข้อมูล Excel แต่ละชีทรวมเป็นตารางเดียวโดยเริ่มจาก

STEP 1 : ทำการแปลงข้อมูลในแต่ละชีทให้เป็นตาราง

  • เนื่องจาก Power Query จะทำงานกับข้อมูลในรูปแบบที่เป็นตาราง ดังนั้นต้องดำเนินการแปลงข้อมูลแต่ละชีทให้เป็นตาราง
  • ภายในชีท HATYAI คลิกเซลล์ A1
  • คลิกเมนู Insert –> Table
  • ระบบเลือกช่วงข้อมูลที่ต้องการให้อัตโนมัติ
  • เลือก My table has headers
  • คลิกปุ่ม OK
  • กำหนด Table Name : T_HATYAI
  • ดำเนินการเช่นเดียวกันในชีทที่เหลือโดยตั้งชื่อตาราง T_PATTANI, T_PHUKET, T_SURAT, T_TRANG ตามลำดับ

STEP 2 : รวมข้อมูลเป็นตารางเดียวด้วย Power Query

  • คลิกเมนู Data –> Get Data –> From Other Sources –>Blank Query
  • ระบบแสดงหน้าต่าง Power Query Editor

STEP 3 : กำหนดรายละเอียดของ Query

  • ตั้งชื่อ Query Name : ALL_CAMPUS
  • พิมพ์ฟังก์ชัน “=Excel.CurrentWorkbook()” ซึ่งเป็นภาษา M ที่อยู่เบื้องหลังของ Power Query เพื่อแสดงตารางข้อมูลทั้งหมดในไฟล์ Excel ของเรา
  • เราสามารถ Filter เพื่อเลือกรวมเฉพาะตารางข้อมูลที่ต้องการเท่านั้นได้
  • สมมุติเราต้องการรวมเฉพาะตารางเฉพาะที่ขึ้นต้นด้วยตัวอักษร ‘T’ เราสามารถทำได้โดยคลิกที่ Text Filters –> Begins With….
  • กรอกตัวอักษร T ลงไป คลิกปุ่ม OK

STEP 4 : กำหนดรายละเอียดของคอลัมน์

  • สามารถเลือกหรือไม่เลือกคอลัมน์ที่ต้องการหรือไม่ต้องการได้
  • กำหนดเงื่อนไขไม่เลือก Use original column name as prefix กรณีเลือกจะมีชื่อ Content นำหน้าชื่อคอลัมน์เช่น Content.Subject ID Content.Subject Code เป็นต้น
  • คลิกปุ่ม OK ระบบแสดงข้อมูลที่เกิดจากการรวมของตารางข้อมูลตามเงื่อนไข
  • คลิกปุ่ม Close & Load ระบบจะทำการรวมข้อมูลทั้ง 5 ชีทไว้ที่ชีท ALL_CAMPUS

STEP 4 : Power Query รวมข้อมูล Excel จากหลายชีทเข้าด้วยกันเป็นตารางเดียวเรียบร้อยแล้วค่ะ

^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ