การประกอบตารางจากไฟล์ Excel ต่างๆ ด้วย Power Query

การกำหนดปัญหา

มาดูโซลูชันที่สวยงามสำหรับสถานการณ์มาตรฐานที่ผู้ใช้ Excel ส่วนใหญ่เผชิญไม่ช้าก็เร็ว คุณจำเป็นต้องรวบรวมข้อมูลจากไฟล์จำนวนมากอย่างรวดเร็วและอัตโนมัติในตารางสุดท้าย 

สมมติว่าเรามีโฟลเดอร์ต่อไปนี้ ซึ่งมีไฟล์หลายไฟล์พร้อมข้อมูลจากเมืองสาขา:

จำนวนไฟล์ไม่สำคัญและอาจเปลี่ยนแปลงได้ในอนาคต แต่ละไฟล์มีชีตชื่อ การขายตำแหน่งของตารางข้อมูล:

แน่นอนว่าจำนวนแถว (คำสั่ง) ในตารางนั้นแตกต่างกัน แต่ชุดของคอลัมน์นั้นเป็นมาตรฐานทุกที่

ภารกิจ: เพื่อรวบรวมข้อมูลจากไฟล์ทั้งหมดไว้ในหนังสือเล่มเดียวโดยมีการอัปเดตอัตโนมัติในภายหลังเมื่อเพิ่มหรือลบไฟล์เมืองหรือแถวในตาราง ตามตารางรวมสุดท้าย จะสามารถสร้างรายงาน ตารางสาระสำคัญ ข้อมูลการจัดเรียงตัวกรอง ฯลฯ ได้ สิ่งสำคัญคือสามารถรวบรวมได้

เราเลือกอาวุธ

สำหรับวิธีแก้ปัญหา เราจำเป็นต้องมี Excel 2016 เวอร์ชันล่าสุด (มีฟังก์ชันการทำงานที่จำเป็นอยู่แล้วตามค่าเริ่มต้น) หรือ Excel 2010-2013 เวอร์ชันก่อนหน้าที่มีการติดตั้ง Add-in ฟรี Power Query จาก Microsoft (ดาวน์โหลดได้ที่นี่) Power Query เป็นเครื่องมือที่ยืดหยุ่นและทรงพลังเป็นพิเศษสำหรับการโหลดข้อมูลเข้าสู่ Excel จากโลกภายนอก จากนั้นจึงทำการลอกและประมวลผล Power Query รองรับแหล่งข้อมูลที่มีอยู่เกือบทั้งหมด ตั้งแต่ไฟล์ข้อความไปจนถึง SQL และแม้แต่ Facebook 🙂

หากคุณไม่มี Excel 2013 หรือ 2016 คุณจะไม่สามารถอ่านเพิ่มเติมได้ (ล้อเล่น) ใน Excel เวอร์ชันเก่า งานดังกล่าวสามารถทำได้โดยการเขียนโปรแกรมแมโครใน Visual Basic (ซึ่งเป็นเรื่องยากมากสำหรับผู้เริ่มต้น) หรือโดยการคัดลอกด้วยตนเองที่ซ้ำซากจำเจ (ซึ่งใช้เวลานานและทำให้เกิดข้อผิดพลาด)

ขั้นตอนที่ 1. นำเข้าไฟล์หนึ่งไฟล์เป็นตัวอย่าง

ขั้นแรก ให้นำเข้าข้อมูลจากสมุดงานหนึ่งเป็นตัวอย่าง เพื่อให้ Excel "หยิบแนวคิด" เมื่อต้องการทำสิ่งนี้ ให้สร้างเวิร์กบุ๊กเปล่าใหม่และ...

• หากคุณมี Excel 2016 ให้เปิดแท็บ ข้อมูล แล้วก็ สร้างแบบสอบถาม – จากไฟล์ – จากหนังสือ (ข้อมูล — แบบสอบถามใหม่- จากไฟล์ — จาก Excel)
• หากคุณมี Excel 2010-2013 ที่ติดตั้ง Add-in ของ Power Query ไว้ ให้เปิดแท็บ Power Query และเลือกบนมัน จากไฟล์ – จากหนังสือ (จากไฟล์ — จาก Excel)

จากนั้นในหน้าต่างที่เปิดขึ้น ให้ไปที่โฟลเดอร์ของเราที่มีรายงานและเลือกไฟล์ในเมืองใดก็ได้ (ไม่สำคัญว่าไฟล์ใดจะเป็นไฟล์ทั่วไป) หลังจากนั้นไม่กี่วินาที หน้าต่าง Navigator จะปรากฏขึ้น ซึ่งคุณต้องเลือกแผ่นงานที่เราต้องการ (Sales) ทางด้านซ้าย และเนื้อหาจะปรากฏทางด้านขวา:

หากคุณคลิกที่ปุ่มที่มุมล่างขวาของหน้าต่างนี้ ดาวน์โหลด (โหลด)จากนั้นตารางจะถูกนำเข้าไปยังแผ่นงานในรูปแบบเดิมทันที สำหรับไฟล์เดียวนี่ถือว่าดีแต่เราต้องโหลดไฟล์ดังกล่าวหลายๆ ไฟล์ ดังนั้นเราจะไปต่างออกไปเล็กน้อยแล้วคลิกปุ่ม การแก้ไข (แก้ไข). หลังจากนั้น ตัวแก้ไขแบบสอบถาม Power Query ควรแสดงในหน้าต่างแยกต่างหากพร้อมข้อมูลของเราจากหนังสือ:

นี้เป็นเครื่องมือที่มีประสิทธิภาพมากที่ช่วยให้คุณ "เสร็จสิ้น" ตารางในมุมมองที่เราต้องการ แม้แต่คำอธิบายแบบผิวเผินของฟังก์ชันทั้งหมดของมันก็ยังใช้เวลาประมาณร้อยหน้า แต่ถ้าใช้ช่วงเวลาสั้นๆ คุณสามารถใช้หน้าต่างนี้:

• กรองข้อมูลที่ไม่จำเป็น บรรทัดว่าง บรรทัดที่มีข้อผิดพลาด
• จัดเรียงข้อมูลตามคอลัมน์อย่างน้อยหนึ่งคอลัมน์
• กำจัดความซ้ำซากจำเจ
• แบ่งข้อความติดหนึบตามคอลัมน์ (ตามตัวคั่น จำนวนอักขระ ฯลฯ)
• ใส่ข้อความตามลำดับ (ลบช่องว่างเพิ่มเติม, ตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ ฯลฯ )
• แปลงประเภทข้อมูลในทุกวิถีทางที่เป็นไปได้ (เปลี่ยนตัวเลขเช่นข้อความเป็นตัวเลขปกติและในทางกลับกัน)
• ย้าย (หมุน) ตารางและขยายตารางไขว้สองมิติเป็นตารางแบน
• เพิ่มคอลัมน์เพิ่มเติมลงในตาราง และใช้สูตรและฟังก์ชันในคอลัมน์เหล่านั้นโดยใช้ภาษา M ที่สร้างไว้ใน Power Query
• ...

ตัวอย่างเช่น ให้เพิ่มคอลัมน์ที่มีชื่อข้อความของเดือนลงในตารางของเรา เพื่อให้ง่ายต่อการสร้างรายงานตารางสาระสำคัญในภายหลัง ในการดำเนินการนี้ ให้คลิกขวาที่ส่วนหัวของคอลัมน์ ข้อมูลแล้วเลือกคำสั่ง คอลัมน์ที่ซ้ำกัน (คอลัมน์ซ้ำ)จากนั้นคลิกขวาที่ส่วนหัวของคอลัมน์ที่ซ้ำกันที่ปรากฏขึ้นและเลือก Commands แปลง – เดือน – ชื่อเดือน:

คอลัมน์ใหม่ควรสร้างด้วยชื่อข้อความของเดือนสำหรับแต่ละแถว โดยดับเบิลคลิกที่ส่วนหัวของคอลัมน์ คุณสามารถเปลี่ยนชื่อจาก วันที่คัดลอก ให้สบายขึ้น เดือน, เช่น.

หากในบางคอลัมน์ โปรแกรมไม่รู้จักประเภทข้อมูลอย่างถูกต้อง คุณสามารถช่วยได้โดยคลิกที่ไอคอนรูปแบบทางด้านซ้ายของแต่ละคอลัมน์:

คุณสามารถยกเว้นบรรทัดที่มีข้อผิดพลาดหรือบรรทัดว่าง ตลอดจนผู้จัดการหรือลูกค้าที่ไม่จำเป็น โดยใช้ตัวกรองง่ายๆ:

นอกจากนี้ การแปลงที่ดำเนินการทั้งหมดจะได้รับการแก้ไขในแผงด้านขวา ซึ่งสามารถย้อนกลับ (ข้าม) หรือเปลี่ยนพารามิเตอร์ (เกียร์):

เบาและสง่างามใช่มั้ย?

ขั้นตอนที่ 2 มาแปลงคำขอของเราเป็นฟังก์ชันกันเถอะ

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

ในตัวแก้ไขแบบสอบถาม ไปที่แท็บมุมมองแล้วคลิกปุ่ม ตัวแก้ไขขั้นสูง (ดู — ตัวแก้ไขขั้นสูง). หน้าต่างควรเปิดขึ้นซึ่งการดำเนินการก่อนหน้าทั้งหมดของเราจะถูกเขียนในรูปแบบของโค้ดในภาษา M โปรดทราบว่าเส้นทางไปยังไฟล์ที่เรานำเข้าสำหรับตัวอย่างนั้นฮาร์ดโค้ดอยู่ในรหัส:

ตอนนี้มาทำการปรับเปลี่ยนสองสามอย่าง:

ความหมายง่ายๆ คือ บรรทัดแรก (เส้นทางไฟล์) => เปลี่ยนขั้นตอนของเราให้เป็นฟังก์ชันที่มีอาร์กิวเมนต์ ไฟล์พาธและด้านล่างเราเปลี่ยนเส้นทางคงที่เป็นค่าของตัวแปรนี้ 

ทั้งหมด. คลิกที่ เสร็จสิ้น และควรเห็นสิ่งนี้:

อย่ากลัวว่าข้อมูลจะหายไป อันที่จริง ทุกอย่างเรียบร้อย ทุกอย่างควรมีลักษณะเช่นนี้ 🙂 เราได้สร้างฟังก์ชันที่กำหนดเองของเราสำเร็จแล้ว โดยที่อัลกอริทึมทั้งหมดสำหรับการนำเข้าและประมวลผลข้อมูลจะถูกจดจำโดยไม่ต้องเชื่อมโยงกับไฟล์ใดไฟล์หนึ่ง . ยังคงให้ชื่อที่เข้าใจได้มากขึ้น (เช่น รับข้อมูล) ในแผงด้านขวาในสนาม ชื่อจริง และคุณสามารถเก็บเกี่ยว หน้าแรก — ปิดและดาวน์โหลด (หน้าแรก — ปิดและโหลด). โปรดทราบว่าเส้นทางไปยังไฟล์ที่เรานำเข้าสำหรับตัวอย่างนั้นถูกฮาร์ดโค้ดไว้ในโค้ด คุณจะกลับไปที่หน้าต่างหลักของ Microsoft Excel แต่แผงที่มีการเชื่อมต่อกับฟังก์ชันของเราจะปรากฏขึ้นทางด้านขวา:

ขั้นตอนที่ 3 รวบรวมไฟล์ทั้งหมด

ส่วนที่ยากที่สุดอยู่เบื้องหลัง ส่วนที่สบายและง่ายยังคงอยู่ ไปที่แท็บ ข้อมูล – สร้างแบบสอบถาม – จากไฟล์ – จากโฟลเดอร์ (ข้อมูล — แบบสอบถามใหม่ — จากไฟล์ — จากโฟลเดอร์) หรือถ้าคุณมี Excel 2010-2013 คล้ายกับแท็บ Power Query. ในหน้าต่างที่ปรากฏขึ้น ให้ระบุโฟลเดอร์ที่มีไฟล์เมืองต้นทางทั้งหมดของเรา และคลิก OK. ขั้นตอนต่อไปควรเปิดหน้าต่างที่ไฟล์ Excel ทั้งหมดที่พบในโฟลเดอร์นี้ (และโฟลเดอร์ย่อย) และรายละเอียดสำหรับแต่ละไฟล์จะแสดงอยู่ในรายการ:

คลิก เปลี่ยนแปลง (แก้ไข) และอีกครั้งเราเข้าสู่หน้าต่างตัวแก้ไขแบบสอบถามที่คุ้นเคย

ตอนนี้ เราต้องเพิ่มคอลัมน์อื่นในตารางของเราด้วยฟังก์ชันที่สร้างขึ้น ซึ่งจะ "ดึง" ข้อมูลจากแต่ละไฟล์ ในการดำเนินการนี้ ให้ไปที่แท็บ เพิ่มคอลัมน์ – คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — เพิ่มคอลัมน์กำหนดเอง) และในหน้าต่างที่ปรากฏขึ้น ให้เข้าสู่ฟังก์ชั่นของเรา รับข้อมูลโดยระบุเส้นทางแบบเต็มไปยังแต่ละไฟล์เป็นอาร์กิวเมนต์:

หลังจากคลิกที่ OK ควรเพิ่มคอลัมน์ที่สร้างขึ้นในตารางของเราทางด้านขวา

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

และตอนนี้ "ช่วงเวลาว้าว" - คลิกที่ไอคอนที่มีลูกศรของตัวเองที่มุมบนขวาของคอลัมน์ที่เพิ่มด้วยฟังก์ชันของเรา:

… ยกเลิกการเลือก ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า (ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า)และคลิก OK. และฟังก์ชันของเราจะโหลดและประมวลผลข้อมูลจากแต่ละไฟล์ตามอัลกอริธึมที่บันทึกไว้และรวบรวมทุกอย่างในตารางทั่วไป:

เพื่อความสวยงามที่สมบูรณ์ คุณสามารถลบนามสกุล .xlsx ออกจากคอลัมน์แรกที่มีชื่อไฟล์ได้ โดยการแทนที่แบบมาตรฐานด้วย "nothing" (คลิกขวาที่ส่วนหัวของคอลัมน์ – แทน) และเปลี่ยนชื่อคอลัมน์นี้เป็น เมือง. และยังแก้ไขรูปแบบข้อมูลในคอลัมน์ด้วยวันที่

ทั้งหมด! คลิกที่ หน้าแรก – ปิดและโหลด (หน้าแรก — ปิด & โหลด). ข้อมูลทั้งหมดที่รวบรวมโดยแบบสอบถามสำหรับทุกเมืองจะถูกอัปโหลดไปยังแผ่นงาน Excel ปัจจุบันในรูปแบบ "ตารางอัจฉริยะ":

การเชื่อมต่อที่สร้างขึ้นและฟังก์ชันการประกอบของเราไม่จำเป็นต้องบันทึกแยกกันแต่อย่างใด – จะถูกบันทึกพร้อมกับไฟล์ปัจจุบันในลักษณะปกติ

ในอนาคต หากมีการเปลี่ยนแปลงในโฟลเดอร์ (เพิ่มหรือลบเมือง) หรือในไฟล์ (เปลี่ยนจำนวนบรรทัด) ให้คลิกขวาบนโต๊ะหรือคิวรีในแผงด้านขวาและเลือก สั่งการ อัปเดตและบันทึก (รีเฟรช) – Power Query จะ "สร้าง" ข้อมูลทั้งหมดอีกครั้งในไม่กี่วินาที

PS

การแก้ไข หลังจากการอัปเดตในเดือนมกราคม 2017 Power Query ได้เรียนรู้วิธีรวบรวมเวิร์กบุ๊ก Excel ด้วยตัวเอง กล่าวคือ ไม่จำเป็นต้องสร้างฟังก์ชันแยกต่างหากอีกต่อไป ซึ่งจะเกิดขึ้นโดยอัตโนมัติ ดังนั้นขั้นตอนที่สองจากบทความนี้จึงไม่จำเป็นอีกต่อไปและกระบวนการทั้งหมดจะง่ายขึ้นอย่างเห็นได้ชัด:

1. Choose สร้างคำขอ – จากไฟล์ – จากโฟลเดอร์ – เลือกโฟลเดอร์ – OK
2. หลังจากรายการไฟล์ปรากฏขึ้น ให้กด เปลี่ยนแปลง
3. ในหน้าต่าง Query Editor ให้ขยายคอลัมน์ไบนารีด้วยลูกศรคู่และเลือกชื่อชีตที่จะนำมาจากแต่ละไฟล์

และนั่นคือทั้งหมด! เพลง!

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