สร้างตารางด้วยส่วนหัวที่แตกต่างจากหนังสือหลายเล่ม

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

เรามีไฟล์หลายไฟล์ (ในตัวอย่างของเรา - 4 ชิ้นในกรณีทั่วไป - มากเท่าที่คุณต้องการ) ในโฟลเดอร์เดียว รายงาน:

ภายในไฟล์เหล่านี้มีลักษณะดังนี้:

โดยที่:

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

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

ขั้นตอนที่ 1 การเตรียมไดเร็กทอรีของชื่อคอลัมน์

สิ่งแรกที่ต้องทำคือเตรียมหนังสืออ้างอิงพร้อมตัวเลือกที่เป็นไปได้ทั้งหมดสำหรับชื่อคอลัมน์และการตีความที่ถูกต้อง:

เราแปลงรายการนี้เป็นตาราง "อัจฉริยะ" แบบไดนามิกโดยใช้ปุ่มรูปแบบเป็นตารางบนแท็บ หน้าแรก (หน้าแรก — รูปแบบเป็นตาราง) หรือแป้นพิมพ์ลัด Ctrl+T และโหลดลงใน Power Query ด้วยคำสั่ง ข้อมูล – จากตาราง/ช่วง (ข้อมูล — จากตาราง/ช่วง). ใน Excel เวอร์ชันล่าสุด ได้เปลี่ยนชื่อเป็น มีใบ (จากแผ่นงาน).

ในหน้าต่างตัวแก้ไขคิวรี Power Query เรามักจะลบขั้นตอน เปลี่ยนประเภท และเพิ่มขั้นตอนใหม่แทนโดยคลิกที่ปุ่ม f_xในแถบสูตร (หากมองไม่เห็น คุณสามารถเปิดใช้งานได้บนแท็บ รีวิว) และป้อนสูตรในภาษา Power Query ที่มีอยู่แล้วภายใน M:

=Table.ToRows(ที่มา)

คำสั่งนี้จะแปลงคำสั่งที่โหลดในขั้นตอนก่อนหน้า แหล่ง ตารางอ้างอิงเป็นรายการที่ประกอบด้วยรายการที่ซ้อนกัน (List) ซึ่งแต่ละรายการจะเป็นคู่ของค่า มันกลายเป็น จากหนึ่งบรรทัด:

เราต้องการข้อมูลประเภทนี้ในภายหลังเมื่อทำการเปลี่ยนชื่อส่วนหัวจากตารางที่โหลดทั้งหมด

หลังจากแปลงเสร็จแล้ว ให้เลือกคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… และประเภทของการนำเข้า เพียงแค่สร้างการเชื่อมต่อ (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่… — สร้างการเชื่อมต่อเท่านั้น) และกลับไปที่ Excel

ขั้นตอนที่ 2 เราโหลดทุกอย่างจากไฟล์ทั้งหมดตามที่เป็นอยู่

ตอนนี้ มาโหลดเนื้อหาของไฟล์ทั้งหมดของเราจากโฟลเดอร์กัน อย่างที่เป็นอยู่ตอนนี้ การเลือกทีม ข้อมูล – รับข้อมูล – จากไฟล์ – จากโฟลเดอร์ (ข้อมูล — รับข้อมูล — จากไฟล์ — จากโฟลเดอร์) แล้วก็โฟลเดอร์ที่หนังสือต้นทางของเราอยู่

ในหน้าต่างแสดงตัวอย่าง คลิก แปลง (แปลง) or เปลี่ยนแปลง (แก้ไข):

แล้วขยายเนื้อหาของไฟล์ที่ดาวน์โหลดทั้งหมด (ไบนารี่) ปุ่มที่มีลูกศรคู่ในส่วนหัวของคอลัมน์ คอนเทนต์:

Power Query ในตัวอย่างของไฟล์แรก (วอสตอค.xlsx) จะถามชื่อชีทที่เราต้องการจะเอามาจากแต่ละเวิร์กบุ๊ก – เลือก ภาพถ่าย และกดตกลง:

หลังจากนั้น (ในความเป็นจริง) เหตุการณ์หลายอย่างที่ผู้ใช้ไม่ชัดเจนจะเกิดขึ้นซึ่งผลที่ตามมาจะมองเห็นได้ชัดเจนในแผงด้านซ้าย:

1. Power Query จะนำไฟล์แรกจากโฟลเดอร์ (เราจะมี Vostok.xlsx — เห็น ตัวอย่างไฟล์) เป็นตัวอย่างและนำเข้าเนื้อหาโดยการสร้างแบบสอบถาม แปลงไฟล์ตัวอย่าง. แบบสอบถามนี้จะมีขั้นตอนง่ายๆ เช่น แหล่ง (การเข้าถึงไฟล์) การเดินเรือ (การเลือกแผ่นงาน) และอาจเพิ่มชื่อเรื่อง คำขอนี้สามารถโหลดข้อมูลจากไฟล์ใดไฟล์หนึ่งเท่านั้น วอสตอค.xlsx.
   
2. ตามคำขอนี้ ฟังก์ชั่นที่เกี่ยวข้องจะถูกสร้างขึ้น แปลงไฟล์ (ระบุด้วยไอคอนลักษณะเฉพาะ f_x) โดยที่ไฟล์ต้นฉบับจะไม่เป็นค่าคงที่อีกต่อไป แต่เป็นค่าตัวแปร – พารามิเตอร์ ดังนั้น ฟังก์ชันนี้สามารถดึงข้อมูลจากหนังสือเล่มใดก็ได้ที่เราใส่เข้าไปเป็นอาร์กิวเมนต์
   
3. ฟังก์ชั่นจะถูกนำไปใช้กับแต่ละไฟล์ (ไบนารี) จากคอลัมน์ คอนเทนต์ – ขั้นตอนรับผิดชอบสิ่งนี้ เรียกใช้ฟังก์ชันกำหนดเอง ในแบบสอบถามของเราที่เพิ่มคอลัมน์ในรายการไฟล์ แปลงไฟล์ พร้อมผลลัพธ์การนำเข้าจากสมุดงานแต่ละเล่ม:

   

4. คอลัมน์พิเศษจะถูกลบออก
5. เนื้อหาของตารางที่ซ้อนกันถูกขยาย (ขั้นตอน คอลัมน์ตารางขยาย) – และเราเห็นผลลัพธ์สุดท้ายของการเก็บรวบรวมข้อมูลจากหนังสือทุกเล่ม:

   
   

ขั้นตอนที่ 3 ขัด

ภาพหน้าจอก่อนหน้านี้แสดงให้เห็นชัดเจนว่าการประกอบโดยตรง "ตามสภาพ" กลายเป็นว่ามีคุณภาพต่ำ:

• คอลัมน์จะกลับด้าน
• บรรทัดพิเศษมากมาย (ว่างและไม่เพียงเท่านั้น)
• ส่วนหัวของตารางจะไม่ถูกมองว่าเป็นส่วนหัวและผสมกับข้อมูล
  

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

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

เพื่อให้คอลัมน์จากไฟล์ต่างๆ เรียงกันโดยอัตโนมัติในภายหลัง จะต้องตั้งชื่อคอลัมน์เดียวกัน คุณสามารถทำการเปลี่ยนชื่อจำนวนมากตามไดเร็กทอรีที่สร้างไว้ก่อนหน้านี้ด้วย M-code หนึ่งบรรทัด มากดปุ่มกันอีกครั้ง f_x ในแถบสูตรและเพิ่มฟังก์ชันที่จะเปลี่ยน:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

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

อันที่จริงนั่นคือทั้งหมด

กลับมาตามคำเรียกร้อง รายงาน เราจะเห็นภาพที่แตกต่างไปจากเดิมอย่างสิ้นเชิง – ดีกว่าภาพก่อนหน้ามาก:

• Power Query, Power Pivot, Power BI คืออะไร และเหตุใดผู้ใช้ Excel จึงต้องการ
  
• รวบรวมข้อมูลจากไฟล์ทั้งหมดในโฟลเดอร์ที่กำหนด
• รวบรวมข้อมูลจากหนังสือทุกแผ่นในตารางเดียว