การสร้างตารางหลายรูปแบบจากแผ่นงานเดียวใน Power Query

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

เป็นข้อมูลป้อนเข้า เรามีไฟล์ Excel โดยที่แผ่นงานหนึ่งประกอบด้วยตารางหลายตารางพร้อมข้อมูลการขายในรูปแบบต่อไปนี้:

โปรดทราบว่า:

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

สมมติฐานที่สำคัญสองประการ สันนิษฐานว่า:

• เหนือแต่ละตาราง ในคอลัมน์แรก มีชื่อของผู้จัดการที่มียอดขายตามตารางที่แสดงไว้ (Ivanov, Petrov, Sidorov เป็นต้น)
  
• ชื่อของสินค้าและภูมิภาคในตารางทั้งหมดถูกเขียนในลักษณะเดียวกัน – ด้วยความถูกต้องของกรณี
  

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

ขั้นตอนที่ 1. เชื่อมต่อกับไฟล์

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

ด้วยเหตุนี้ ข้อมูลทั้งหมดจากข้อมูลดังกล่าวจึงควรโหลดลงในตัวแก้ไข Power Query:

ขั้นตอนที่ 2. ทำความสะอาดถังขยะ

ลบขั้นตอนที่สร้างขึ้นโดยอัตโนมัติ แก้ไขประเภท (เปลี่ยนประเภท) и ส่วนหัวสูง (ส่วนหัวที่ได้รับการส่งเสริม) และกำจัดบรรทัดว่างและบรรทัดที่มีผลรวมโดยใช้ตัวกรอง โมฆะ и รวม โดยคอลัมน์แรก เป็นผลให้เราได้ภาพต่อไปนี้:

ขั้นตอนที่ 3 การเพิ่มผู้จัดการ

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

1. มาเพิ่มคอลัมน์เสริมที่มีหมายเลขบรรทัดโดยใช้คำสั่ง เพิ่มคอลัมน์ – คอลัมน์ดัชนี – ตั้งแต่ 0 (เพิ่มคอลัมน์ — คอลัมน์ดัชนี — จาก 0).

2. เพิ่มคอลัมน์ที่มีสูตรด้วยคำสั่ง การเพิ่มคอลัมน์ – คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง) และแนะนำสิ่งก่อสร้างต่อไปนี้ที่นั่น:

ตรรกะของสูตรนี้เรียบง่าย หากค่าของเซลล์ถัดไปในคอลัมน์แรกคือ "ผลิตภัณฑ์" แสดงว่าเราสะดุดกับจุดเริ่มต้นของตารางใหม่ เราจึงแสดงค่าของเซลล์ก่อนหน้าด้วย ชื่อผู้จัดการ มิฉะนั้น เราจะไม่แสดงอะไรเลย กล่าวคือเป็นโมฆะ

ในการรับเซลล์หลักที่มีนามสกุล ก่อนอื่นเราอ้างอิงถึงตารางจากขั้นตอนก่อนหน้า #”เพิ่มดัชนี”แล้วระบุชื่อคอลัมน์ที่เราต้องการ [คอลัมน์1] ในวงเล็บเหลี่ยมและหมายเลขเซลล์ในคอลัมน์นั้นในวงเล็บปีกกา หมายเลขเซลล์จะน้อยกว่าหมายเลขปัจจุบันซึ่งเรานำมาจากคอลัมน์ ดัชนีตามลำดับ

3. มันยังคงเติมในเซลล์ว่างด้วย โมฆะ ชื่อจากเซลล์ที่สูงกว่าด้วยคำสั่ง แปลงร่าง-เติม-ลง (แปลง — เติม — ลง) และลบคอลัมน์ที่ไม่ต้องการอีกต่อไปด้วยดัชนีและแถวที่มีนามสกุลในคอลัมน์แรก เป็นผลให้เราได้รับ:

ขั้นตอนที่ 4 จัดกลุ่มตามตารางโดยผู้จัดการ

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

เป็นผลให้เราได้รับตารางแยกสำหรับผู้จัดการแต่ละคน:

ขั้นตอนที่ 5: แปลงตารางที่ซ้อนกัน

ตอนนี้เราให้ตารางที่อยู่ในแต่ละเซลล์ของคอลัมน์ผลลัพธ์ ข้อมูลทั้งหมด ในรูปทรงที่เหมาะสม

ขั้นแรก ให้ลบคอลัมน์ที่ไม่ต้องการในแต่ละตารางอีกต่อไป ผู้จัดการ. เราใช้อีกครั้ง คอลัมน์ที่กำหนดเอง แถบ การแปลง (แปลง — คอลัมน์กำหนดเอง) และสูตรต่อไปนี้:

จากนั้น ด้วยคอลัมน์จากการคำนวณอื่น เราจะเพิ่มแถวแรกในแต่ละตารางเป็นส่วนหัว:

และสุดท้าย เราทำการเปลี่ยนแปลงหลัก – แฉแต่ละตารางโดยใช้ฟังก์ชัน M Table.UnpivotOtherColumns:

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

การกำจัดคอลัมน์กลางที่ไม่จำเป็น เรามี:

ขั้นตอนที่ 6 ขยายตารางที่ซ้อนกัน

มันยังคงขยายตารางที่ซ้อนกันที่ทำให้เป็นมาตรฐานทั้งหมดเป็นรายการเดียวโดยใช้ปุ่มที่มีลูกศรคู่ในส่วนหัวของคอลัมน์:

… และในที่สุดเราก็ได้สิ่งที่เราต้องการ:

คุณสามารถส่งออกตารางผลลัพธ์กลับไปที่ Excel โดยใช้คำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…).

• สร้างตารางด้วยส่วนหัวที่แตกต่างจากหนังสือหลายเล่ม
• รวบรวมข้อมูลจากไฟล์ทั้งหมดในโฟลเดอร์ที่กำหนด
• รวบรวมข้อมูลจากหนังสือทุกแผ่นในตารางเดียว