วิธีดึงข้อมูลจาก Excel ไฟล์ย่อยมาทำรายงานสรุปใน Excel หลัก (อีกไฟล์) /Power BI

ผมเองก็ได้นำเสนอข้อมูลเกี่ยวกับ Power Query มาเยอะพอสมควรว่ามันสามารถรวบรวมข้อมูลจากหลายแหล่ง และยังสามารถดัดแปลงหน้าตาข้อมูลให้อยู่ในรูปแบบที่เหมาะสมมากขึ้นได้

แต่เหมือนว่ายังมีเพื่อนๆ หลายท่านอาจยังไม่เห็นภาพว่ามันดึงข้อมูลจาก Excel มารวมกันได้ยังไง ดังนั้นในบทความนี้ผมจะสรุปวิธีดึงข้อมูลจากไฟล์ Excel ให้ดูครับ

การดึงข้อมูลจาก Excel นั้นจริงๆ มีหลากหลายรูปแบบ มีความซับซ้อนหลายระดับ (แบบยากสุดจริงๆ จะรองรับแบบสถานการณ์แบบง่ายสุดได้ด้วยครับ)

ดึงข้อมูลจาก Excel 1 ไฟล์

ต้องการแค่ 1 Table จาก Excel 1 ไฟล์

การดึงข้อมูลจาก Table เดียวจากไฟล์ Excel ที่ต้องการนั้นง่ายที่สุดเลย นั่นก็คือ กด Get Data -> From File -> From Workbook แล้วเลือกไฟล์ Excel ที่ต้องการ และเลือก Table ที่ต้องการจากหน้าต่างด้านซ้ายมือ (สังเกตว่าสัญลักษณ์ของ Table กับ Sheet ไม่เหมือนกันนะ)

Tips : การดึงข้อมูลจาก Table จะดีตรงที่ Power Query จะรู้จักหัวตารางโดยอัตโนมัติ เราจึงไม่ต้องมาปวดหัวกับเรื่องการจัดการหัวตารางเลย และหากบริเวณอื่นใน Sheet มีข้อมูลปนอยู่ ก็ไม่เป็นไรด้วย เพราะมันอ้างอิงข้อมูลจาก Table ที่เลือกเท่านั้น

Excel 1 ไฟล์ 1 Sheet

วิธีนี้เราก็เลือก Get Data -> From File -> From Workbook แล้วเลือกไฟล์ Excel ที่ต้องการ และเลือก Sheet ที่ต้องการจากหน้าต่างด้านซ้ายมือ

การที่เราดึงข้อมูลที่เป็น Sheet (ไม่ได้เป็น Table) จะทำให้ Power Query อาจระบุหัวตารางไม่ถูกต้อง โดยมันจะดึงข้อมูลบรรทัดบนสุดที่ใช้งานมา แล้วพยายาม Promote Header ให้โดยอัตโนมัติ โดยถ้าช่องเป็นช่องว่างมันจะออกมาเป็นคำว่า Column1, Column2,…. ไปเรื่อยๆ ก่อน โดยจะดึงข้อมูลจนถึงช่องสุดท้ายที่มีการใช้งานใน Sheet นั้น

นั่นคือถ้าข้อมูลบรรทัดแรกเป็นหัวตารางพอดี Power Query จะมีการ Promote Header ให้เองเลย นั่นแปลว่าถ้าข้อมูลใน Sheet มีแค่ข้อมูลที่เราต้องการตั้งแต่บรรทัดแรก ก็จะไม่มีปัญหาอะไรเลย

ในทางกลับกัน หากว่าใน Sheet บรรทัดบนสุดไม่ใช่หัวตาราง แต่มีข้อมูลอื่นอยู่ดังนี้

ข้อมูลที่ดึงมาก็จะเน่ามาก เราก็ควรจะต้องหาทางกด Remove Top Row (เช่น เอาบรรทัดบนออก 3 บรรทัดได้) แล้วค่อยกด Use First Row as Header อีกที

และหากช่องสุดท้ายที่ใช้งานของ sheet นั้นดันไม่ใช่ข้อมูลที่อยากได้อีก เช่น

เราก็ต้องหาทางตัด Column, Row ที่ไม่ใช้ออก ซึ่งอาจต้องหาทาง Filter ด้วยเงื่อนไขที่เหมาะสมเป็นต้น (ซึ่งบางทีมันก็ยุ่งยากมากๆ เลย)

ดังนั้นถ้าจะดึงข้อมูลจาก Sheet ถ้าให้ง่าย ใน Sheet นั้นก็ควรมีแต่ข้อมูลที่อยากได้ ไม่ต้องมีอย่างอื่นปนมา มันก็จะง่ายที่สุดครับ (ถ้าจะมีข้อมูลปน ทำเป็น Table ง่ายกว่าเยอะ)

Excel 1 ไฟล์ หลาย Table/หลาย Sheet

ให้เราสั่ง Get Data จาก Excel 1 ไฟล์ตามปกติ ซึ่ง กรณีที่ใน Excel 1 ไฟล์ มีหลาย Table นั้นจริงๆ ก็สามารถจัดการได้ 2 แบบ คือ

1. Table/Sheet แต่ละอันไม่ใช่ข้อมูลแบบเดียวกัน ดังนั้นเราก็ควรเอาไว้แยกคนละ Query กัน
2. Table/Sheet แต่ละอันคือข้อมูลแบบเดียวกัน ดังนั้นเราควรเอามาต่อรวมใน Query เดียวกัน

เรามาดูกรณีแรกกันก่อน ถ้าแบบนั้นคือให้เราติ๊กเลือก Multiple Items ก่อน จากนั้นค่อย Table/Sheet ที่ต้องการ

พอกด Transform ปุ๊ป มันจะดึงแต่ละ Table/Sheet มาแยกเป็นคนละ Query กันเลย (สังเกตที่ list ของ query ที่อยู่ด้านซ้ายมือ)

แต่ถ้าเป็นกรณีที่สอง (ซึ่งคงเป็นแบบที่หลายคนอยากรู้) ให้เรากดเลือกที่ตัวไฟล์หลักเลย (อย่าเลือกแต่ละ table/ แต่ละ sheet) เพื่อให้มันอ่านข้อมูลทั้งไฟล์มาว่ามี Table และ Sheet อะไรบ้าง

สมมติว่าเราอยากได้เฉพาะ Table เท่านั้น ตรง Kind ก็ให้ Filter ให้เหลือแค่ Table ถ้าเราจะเอาเฉพาะ Sheet ก็ให้ Filter เหลือแค่ Sheet

ซึ่งถ้าอยากเจาะจงชื่อ Table หรือชื่อ Sheet ก็ไป Filter ตรง Name เพิ่มได้ ว่าจะให้ขึ้นด้วยคำว่าอะไร หรือมีคำอะไรอยู่?

พอเลือกสิ่งที่ต้องการได้ ก็สามารถกด Expand ข้อมูลตรงคอลัมน์ Data ออกมาได้เลย

ถ้าข้อมูลเป็น Table ก็จะไม่มีปัญหาเรื่องหัวตาราง (จะจบเลย) ถ้าข้อมูลเป็น Sheet ก็จะต้องหาทางกำจัดหัวตารางที่ขึ้นมาซ้ำๆ อีกที

รวม Excelหลายไฟล์ใน Folder

รวม Excel หลายไฟล์ใน Folder แต่ละไฟล์มี Table ที่ต้องการชื่อเดียวกันเป๊ะ

แบบนี้ก็จะจัดการค่อนข้างง่ายกว่าที่หลายคนคาดคิด นั่นคือแค่กด Get Data from File -> From Folder แล้วเลือก Folder ที่ต้องการ จากนั้นกด Transform Data ก่อน

จากนั้นก็กด Expand ที่คอลัมน์ Content ได้เลย

จากนั้นมันจะอ่านข้อมูลจากไฟล์ sample ที่เลือก (ปกติจะเลือกไฟล์แรก) ว่ามีอะไรบ้าง? ให้เราก็เลือก Table ที่ต้องการ (เช่น เราต้องการ Table ชื่อว่า SalesTableจาก Excel ทุกไฟล์)

มันจะเอาข้อมูลจาก Table ใน Excel แต่ะไฟล์มารวมกันให้โดยอัตโนมัติเลย

Tips: หากเราต้องการจะดัดแปลงไฟล์ก่อนจะเอามา Combine ด้วยการ Append ให้ไปทำการแก้ไขที่ Transform Sample File ก่อนครับ (เช่น พยายามทำให้คอลัมน์เหมือนๆ กัน)

ที่แบบนี้มันง่ายเพราะว่าข้อมูลต้นทางเป็น Table แถมชื่อเดียวกันทุกไฟล์ เลยง่ายมาก ดังนั้นถ้าจะรวมข้อมูลจาก Excel หลายๆ ไฟล์ หากเราทำให้เป็น Table ชื่อเดียวกันได้ มันจะง่ายสุดๆ เลยครับ

รวม Excel หลายไฟล์ใน Folder แต่ละไฟล์มี Table/Sheet ที่ต้องการเอามารวมกันหลายชื่อ (แต่ให้รวมเป็น Query เดียว)

แบบนี้ให้กด Get File from Folder แล้วกด Transform ตามปกติ (ถ้ามีไฟล์อื่นปนมาก็ Filter ทิ้งออกไปได้) แล้วก็กด Expand ที่ Content

เนื่องจากเราต้องการข้อมูลหลาย Table ให้เลือกที่ Folder แทนที่จะเลือกที่ Table แต่ละอัน แบบนี้มันจะ List ทั้ง Table/Sheet ที่มีออกมาทั้งหมด

หน้าที่ของเราแค่ Filter เลือกให้เหลือเฉพาะสิ่งที่เราต้องการเอามารวมกัน เช่น Filter Kind เป็น Table ส่วน Name ก็อาจจะ Filter เฉพาะชื่อที่มีคำที่ต้องการ

จากนั้นค่อยกด Expand ข้อมูลที่คอลัมน์ Data ออกมาอีกที

ข้อมูลจากทุก Table ในทุกไฟล์ใน Folder ก็จะถูกนำมาต่อกันทั้งหมดทันที (ถ้ามีข้อมูลซ้ำ สามารถสั่ง Remove Duplicates อีกทีได้)

สรุป สิ่งที่อยากบอกทุกท่าน

เพื่อนๆ คงเห็นว่า กรณีถ้าข้อมูลต้นทางเป็น Table จะทำให้การรวมข้อมูลง่ายขึ้นมาก… ดังนั้น ถ้าทำเป็น Table ได้ก็ทำเถอะครับ ^^