การรวมไฟล์ Excel หลายไฟล์ที่มีหลายชีท ด้วย Power Query
เคยหรือเปล่าที่ทุกวันในการใช้ Excel ทำรายงานสรุปข้อมูลรายได้จากการขาย, ค่าใช้จ่ายในการขายของแต่ละสาขาเป็นรายเดือน หรือรายไตรมาสให้หัวหน้างาน และผู้บริหาร โดยในแต่ละครั้งต้องทำการ Copy ข้อมูลที่ได้รับจากส่วนงานอื่น ๆ มารวมในไฟล์ Excel ที่จะทำรายงานดังกล่าว แน่นอนว่าจะต้องเสียเวลาทำแบบนี้พอสมควรในการรวมข้อมูล เพราะไฟล์ Excel ที่ได้มาแต่ละส่วนงานอาจมีการเก็บข้อมูล Excel เป็นหลายไฟล์ และในแต่ละไฟล์มีหลายชีท
จะดีกว่าไหม หากมีวิธีการที่ลดขั้นตอน และลดเวลาในการรวมไฟล์ Excel หลายไฟล์ที่มีหลายชีทได้ในคลิกเดียว
วันนี้ทางทีมงาน 9Experttraining จะมาแนะนำวิธีดังกล่าวนี้ ผ่านเครื่องมือที่เรียกว่า Power Query เป็นเครื่องมือที่มีอยู่ใน Microsoft Excel, Microsoft Power BI, SQL Server ที่ช่วยในการเตรียมข้อมูล ปรับแต่งข้อมูลให้พร้อม ซึ่งสามารถเชื่อมโยงกับข้อมูลจากหลายแหล่งได้ (อ่านบทความ Power Query คืออะไร ได้ที่ https://www.9experttraining.com/articles/power-query-excel-for-business-intelligence และบทความ Power Query ทำงานอย่างไร ได้ที่ https://www.9experttraining.com/articles/power-query-ทำงานอย่างไร)
การนำเข้าและรวมไฟล์ Excel หลายไฟล์ที่มีหลายชีท ด้วย Power Query
ตัวอย่างไฟล์ Excel เป็นรายการขายสินค้าเป็นรายวัน โดยถูกเก็บไว้ใน Folder เดียวกันที่ชื่อว่า Sales Data ซึ่งในแต่ละไฟล์ Excel จะเป็นยอดขายสินค้ารายวัน แบ่งตามชื่อไฟล์เป็นแต่ละไตรมาส เช่น Q1-2021 จะเป็นยอดขายของไตรมาส 1 ปี 2021
และในแต่ละไฟล์ จะแบ่งชีทออกเป็นแต่ละเดือน ตามแต่ละไตรมาสนั้น เช่น Jan-2021, Feb-2021, Mar-2021 ที่สำคัญโครงสร้างข้อมูลของแต่ละชีทจะเหมือนกัน คือ มีหัวตารางที่เหมือนกัน และในแต่ละคอลัมน์เป็นข้อมูลที่มีรูปแบบเหมือนกัน (โครงสร้างที่เหมือนกันทุกชีท)
ขั้นตอนในการนำเข้าและรวมไฟล์ Excel จากหลายไฟล์ ที่มีหลายชีทดังนี้
1. เปิด Microsoft Excel ไปที่ ป้าย Data > เลือก Get Data > เลือก Form File > เลือก From Folder
2. เลือกชื่อ Folder ที่เก็บไฟล์ Excel และคลิก Open จะมีหน้าต่างขึ้นมาแสดงรายละเอียดไฟล์ที่อยู่ใน Folder ให้คลิก Transform Data
3. เข้าสู่หน้าต่าง Power Query Editor เลือกคอลัมน์ Content กับ Name แล้วคลิกขวาเลือก Remove Other Columns
4. ที่แท็บเมนู Power Query Editor เลือก Add Column > เลือก Custom Column จะขึ้นหน้าต่าง Custom Column ในส่วน Custom column formula ให้ระบุสูตรตามรูป คือ = Excel.Workbook([Content]) จากนั้นคลิก OK
5. คลิกที่รูป 
ที่คอลัมน์ Custom ที่เพิ่มเข้ามา เพื่อต้องการจะขยายข้อมูลออกมา
จากนั้นคลิกปุ่ม OK
จะเห็นว่ามีคอลัมน์เพิ่มมาหลังจากทำการขยายข้อมูลออกมา โดยหากข้อมูลในไฟล์ Excel นั้นมีการสร้าง tables หรือสร้าง name ranges ไว้ สามารถกรองเลือกเอาแต่ sheet ได้ ที่คอลัมน์ Custom.Kind (แต่จากข้อมูลตัวอย่างนี้ มีแต่ข้อมูลที่เป็นประเภท sheet อยู่แล้ว) และหากกดเลือกข้อมูลในคอลัมน์ Custom.Data ก็จะพบกับตัวอย่างข้อมูล
6. เลือกคอลัมน์ที่ต้องการใช้ในลำดับต่อไป อย่างน้อยจะต้องมีคอลัมน์ Custom.Data ซึ่งในที่นี้จะเลือกคอลัมน์ Name, Custom.Name และ Custom.Data โดยใช้วิธีการเดิมเลือกคอลัมน์ที่ต้องการและคลิกขวาเลือก Remove Other Columns
7. ทำการขยายข้อมูลในคอลัมน์ Custom.Data โดยไม่ต้องเลือก Use original column name as prefix
8. คลิก Use First Row as Headers เพื่อต้องการให้แถวแรกเป็นหัวตารางแทน
โดยหลังจากใช้คำสั่ง Use First Row as Headers แล้วหากมีการกำหนด Data type ของแต่ละคอลัมน์ ให้ทำการลบขั้นตอนของคำสั่งนี้ออกก่อน (โดยปกติ step ที่เพิ่มมาจะชื่อว่า Changed Type) โดยจะได้หัวคอลัมน์ที่ไม่ได้มีการกำหนด Data type
9. เปลี่ยนชื่อคอลัมน์ที่ยังไม่ถูกต้อง 2 คอลัมน์
- ชื่อคอลัมน์ Q1-2021.xlsx เปลี่ยนเป็น File Name
- ชื่อคอลัมน์ Jan-2021 เปลี่ยนเป็น Sheet Name
10. ลบข้อมูลส่วนหัวจากชีทอื่นออก โดยการคลิกที่ปุ่มตัวกรองที่คอลัมน์ OrderID และเลือก OrderID ออก แล้วคลิก OK
11. ทำการกำหนด Data type ของทุกคอลัมน์ให้ถูกต้อง
12. ที่แท็บเมนู Home คลิก Close & Load > เลือก Close and Load To…
จากนั้นที่หน้าต่าง Import Data เลือก Table กับตำแหน่งที่ว่างข้อมูลเลือก New Worksheet คลิก OK
จะได้ข้อมูลตารางข้อมูลการขายสินค้ารายวัน จากไฟล์ Excel ทั้ง 3 ไฟล์ ที่มียอดขายของเดือนมกราคม 2021 จนถึงเดือนกันยายน 2021
ในที่สุดเราก็ได้ข้อมูลการขายออกมา จากการรวมไฟล์ Excel ทั้ง 3 ไฟล์ ที่มีหลายชีทในแต่ละไฟล์เป็นที่เรียบร้อย แต่ยังไม่จบแค่นั้น คือ หากได้ข้อมูลการขายเพิ่มขึ้นจากส่วนงานอื่นส่งมา เช่น ไฟล์ Q4-2021.xlsx เราเพียงนำไฟล์ดังกล่าวที่ได้มานี้ ไปใส่ไว้ใน Folder เดียวกับไฟล์ Excel 3 ไฟล์ (ตัวอย่างนี้ Folder Sales Data)
จากนั้นเปิดไฟล์ Excel ที่ทำการรวมไฟล์ หลายไฟล์ หลายชีทด้วย Power Query และไปที่ แท็บเมนู Data > คลิก Refresh All แล้วรอสักครู่ข้อมูลการขายรายวันจากไฟล์ Q4-2021.xlsx ได้ถูกนำมาเพิ่มต่อท้ายตารางเป็นที่เรียบร้อย ดังคำที่ได้กล่าวไว้ตอนต้นว่า ลดขั้นตอน และลดเวลาในการรวมไฟล์ Excel หลายไฟล์ที่มีหลายชีทได้ในคลิกเดียว
จะเห็นว่าหากรู้จักเครื่องมือ Power Query จะทำให้ประหยัดเวลา ลดขั้นตอนการทำงานได้อย่างมาก ๆ เหมาะกับงานในหลาย ๆ ส่วนที่ต้องการการจัดเตรียมข้อมูลให้พร้อมใช้ เพื่อนำไปใช้งานส่วนอื่น ๆ ต่อ ไม่ว่าจะเป็นการวิเคราะห์ข้อมูล สรุปผลข้อมูล เป็นต้น ขั้นตอนในการใช้ Power Query ในการรวมไฟล์ Excel หลายไฟล์ที่มีมีหลายชีทนั้น สามารถนำไปใช้ได้กับ Power BI Desktop ด้วยเช่นกัน
หวังว่าบทความนี้จะมีประโยชน์กับผู้อ่านทุกท่าน อย่างไรทางทีมงาน 9Experttraining ขอฝาก Facebook 9Expert Training กับ YouTube 9EXPERT ที่จะมีสิ่งใหม่ ๆ ด้านความรู้เกี่ยวกับการใช้งานโปรแกรมต่าง ๆ มากมาย
แนะนำหลักสูตรการอบรมด้าน Microsoft Excel ของ 9Expert Training
- Microsoft Excel Intermediate
- Microsoft Excel Advanced
- Microsoft Excel Advanced Pivot Table and Pivot Chart
- Microsoft Excel Macro and VBA
- Microsoft Excel Power BI Business Intelligence
- Microsoft Excel Power Query