เนื้อหา
หากคุณได้เริ่มใช้เครื่องมือของ Add-in ของ Power Query ฟรีใน Microsoft Excel แล้ว ในไม่ช้าคุณจะพบปัญหาที่มีความเชี่ยวชาญสูง แต่บ่อยครั้งและน่ารำคาญที่เกี่ยวข้องกับการทำลายลิงก์ไปยังแหล่งข้อมูลอย่างต่อเนื่อง สาระสำคัญของปัญหาคือ ถ้าในคิวรีของคุณ คุณอ้างถึงไฟล์หรือโฟลเดอร์ภายนอก จากนั้น Power Query จะฮาร์ดโค้ดพาธสัมบูรณ์ไปยังไฟล์หรือโฟลเดอร์เหล่านั้นในข้อความคิวรี ทุกอย่างทำงานได้ดีบนคอมพิวเตอร์ของคุณ แต่ถ้าคุณตัดสินใจที่จะส่งไฟล์พร้อมกับคำขอไปยังเพื่อนร่วมงานของคุณ พวกเขาจะผิดหวังเพราะ พวกเขามีเส้นทางที่แตกต่างกันไปยังแหล่งข้อมูลบนคอมพิวเตอร์ของพวกเขา และการสืบค้นของเราจะใช้งานไม่ได้
จะทำอย่างไรในสถานการณ์เช่นนี้? ลองดูกรณีนี้โดยละเอียดยิ่งขึ้นด้วยตัวอย่างต่อไปนี้
การกำหนดปัญหา
สมมติว่าเรามีอยู่ในโฟลเดอร์ E:รายงานการขาย อยู่ไฟล์ สินค้า 100 อันดับแรก.xlsซึ่งเป็นการอัปโหลดจากฐานข้อมูลองค์กรหรือระบบ ERP (1C, SAP เป็นต้น) ไฟล์นี้มีข้อมูลเกี่ยวกับสินค้าโภคภัณฑ์ยอดนิยมและมีลักษณะดังนี้:
เห็นได้ชัดว่าแทบจะเป็นไปไม่ได้เลยที่จะทำงานกับมันใน Excel ในรูปแบบนี้: แถวว่างผ่านแถวที่มีข้อมูล เซลล์ที่ผสาน คอลัมน์พิเศษ ส่วนหัวหลายระดับ ฯลฯ จะรบกวน
ดังนั้น ถัดจากไฟล์นี้ในโฟลเดอร์เดียวกัน เราสร้างไฟล์ใหม่อีกไฟล์หนึ่ง ตัวจัดการ.xlsxซึ่งเราจะสร้างคิวรี Power Query ที่จะโหลดข้อมูลที่น่าเกลียดจากไฟล์อัปโหลดต้นทาง สินค้า 100 อันดับแรก.xlsและเรียงลำดับดังนี้
การร้องขอไปยังไฟล์ภายนอก
การเปิดไฟล์ ตัวจัดการ.xlsx, เลือกบนแท็บ ข้อมูล คำสั่ง รับข้อมูล – จากไฟล์ – จากสมุดงาน Excel (ข้อมูล — รับข้อมูล — จากไฟล์ — จาก Excel)จากนั้นระบุตำแหน่งของไฟล์ต้นฉบับและชีตที่เราต้องการ ข้อมูลที่เลือกจะถูกโหลดลงในตัวแก้ไข Power Query:
มาทำให้พวกเขากลับมาเป็นปกติ:
- ลบบรรทัดว่างด้วย หน้าแรก — ลบบรรทัด — ลบบรรทัดว่าง (หน้าแรก — ลบแถว — ลบแถวที่ว่างเปล่า).
- ลบ 4 บรรทัดบนสุดที่ไม่จำเป็นผ่าน หน้าแรก — ลบแถว — ลบแถวบนสุด (หน้าแรก — ลบแถว — ลบแถวบนสุด).
- ยกแถวแรกไปที่ส่วนหัวของตารางด้วยปุ่ม ใช้บรรทัดแรกเป็นส่วนหัว แถบ หน้าแรก (หน้าแรก — ใช้แถวแรกเป็นส่วนหัว).
- แยกบทความห้าหลักออกจากชื่อผลิตภัณฑ์ในคอลัมน์ที่สองโดยใช้คำสั่ง แยกคอลัมน์ แถบ การแปลง (แปลง — แยกคอลัมน์).
- ลบคอลัมน์ที่ไม่จำเป็นและเปลี่ยนชื่อส่วนหัวของคอลัมน์ที่เหลือเพื่อให้มองเห็นได้ดีขึ้น
เป็นผลให้เราควรได้ภาพที่น่ารื่นรมย์มากขึ้นดังต่อไปนี้:
ยังคงต้องอัปโหลดตารางที่สูงส่งนี้กลับไปที่แผ่นงานในไฟล์ของเรา ตัวจัดการ.xlsx ทีมงาน ปิดและดาวน์โหลด (หน้าแรก — ปิด&โหลด) แถบ หน้าแรก:
ค้นหาเส้นทางไปยังไฟล์ในคำขอ
ตอนนี้ มาดูกันว่าคิวรีของเรามีลักษณะอย่างไร "ภายใต้ฮูด" ในภาษาภายในที่สร้างไว้ใน Power Query ที่มีชื่อสั้นๆ ว่า "M" เมื่อต้องการทำเช่นนี้ กลับไปที่แบบสอบถามของเราโดยดับเบิลคลิกที่มันในบานหน้าต่างด้านขวา คำขอและการเชื่อมต่อ และบนแท็บ รีวิว เลือก ตัวแก้ไขขั้นสูง (ดู — ตัวแก้ไขขั้นสูง):
ในหน้าต่างที่เปิดขึ้น บรรทัดที่สองจะแสดงเส้นทางที่ฮาร์ดโค้ดไปยังไฟล์อัปโหลดดั้งเดิมของเราในทันที หากเราสามารถแทนที่สตริงข้อความนี้ด้วยพารามิเตอร์ ตัวแปร หรือลิงก์ไปยังเซลล์แผ่นงาน Excel ที่มีการเขียนเส้นทางนี้ไว้ล่วงหน้า เราก็สามารถเปลี่ยนในภายหลังได้อย่างง่ายดาย
เพิ่มตารางอัจฉริยะด้วยเส้นทางของไฟล์
มาปิด Power Query ก่อนแล้วกลับไปที่ไฟล์ของเรา ตัวจัดการ.xlsx. มาเพิ่มแผ่นงานเปล่าใหม่และสร้างตาราง "ฉลาด" ขนาดเล็กในเซลล์เดียวที่จะเขียนเส้นทางแบบเต็มไปยังไฟล์ข้อมูลต้นทางของเรา:
ในการสร้างตารางอัจฉริยะจากช่วงปกติ คุณสามารถใช้แป้นพิมพ์ลัด Ctrl+T หรือปุ่ม จัดรูปแบบเป็นตาราง แถบ หน้าแรก (หน้าแรก — รูปแบบเป็นตาราง). ส่วนหัวของคอลัมน์ (เซลล์ A1) สามารถเป็นอะไรก็ได้ โปรดทราบด้วยว่าเพื่อความชัดเจนฉันได้ให้ชื่อตาราง พารามิเตอร์ แถบ นวกรรมิก (ออกแบบ).
แน่นอนว่าการคัดลอกเส้นทางจาก Explorer หรือแม้แต่ป้อนด้วยตนเองนั้นไม่ยากเป็นพิเศษ แต่ควรลดปัจจัยมนุษย์ให้น้อยที่สุดและกำหนดเส้นทางโดยอัตโนมัติ หากเป็นไปได้ สามารถใช้งานได้โดยใช้ฟังก์ชันแผ่นงาน Excel มาตรฐาน เซลล์ (เซลล์)ซึ่งสามารถให้ข้อมูลที่เป็นประโยชน์มากมายเกี่ยวกับเซลล์ที่ระบุเป็นอาร์กิวเมนต์ ซึ่งรวมถึงเส้นทางไปยังไฟล์ปัจจุบัน:
หากเราคิดว่าไฟล์ข้อมูลต้นทางอยู่ในโฟลเดอร์เดียวกับตัวประมวลผลของเราเสมอ เส้นทางที่เราต้องการสามารถสร้างขึ้นได้ด้วยสูตรต่อไปนี้:
=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”
หรือในเวอร์ชันภาษาอังกฤษ:
=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»
… หน้าที่อยู่ที่ไหน เลฟซิมวี (ซ้าย) นำข้อความบางส่วนจากลิงก์แบบเต็มไปจนถึงวงเล็บเหลี่ยมเปิด (เช่น พาธไปยังโฟลเดอร์ปัจจุบัน) จากนั้นจึงแนบชื่อและนามสกุลของไฟล์ข้อมูลต้นทางของเรา
Parameterize เส้นทางในแบบสอบถาม
สัมผัสสุดท้ายและสำคัญที่สุดยังคงอยู่ - เพื่อเขียนเส้นทางไปยังไฟล์ต้นทางในคำขอ สินค้า 100 อันดับแรก.xlsอ้างถึงเซลล์ A2 ของตาราง "สมาร์ท" ที่เราสร้างขึ้น พารามิเตอร์.
เมื่อต้องการทำเช่นนี้ ให้กลับไปที่คิวรี Power Query แล้วเปิดใหม่อีกครั้ง ตัวแก้ไขขั้นสูง แถบ รีวิว (ดู — ตัวแก้ไขขั้นสูง). แทนที่จะเป็นเส้นทางสตริงข้อความในเครื่องหมายคำพูด “E:Sales รายงานผลิตภัณฑ์ 100 อันดับแรก.xlsx” มาแนะนำโครงสร้างต่อไปนี้:
Excel.สมุดงานปัจจุบัน(){[ชื่อ=”การตั้งค่า”]}[เนื้อหา]0 {}[เส้นทางไปยังแหล่งข้อมูล]
มาดูกันว่าประกอบด้วยอะไรบ้าง:
- Excel.สมุดงานปัจจุบัน() เป็นฟังก์ชันของภาษา M เพื่อเข้าถึงเนื้อหาของไฟล์ปัจจุบัน
- {[ชื่อ=”การตั้งค่า”]}[เนื้อหา] – นี่คือพารามิเตอร์การปรับแต่งสำหรับฟังก์ชั่นก่อนหน้าซึ่งระบุว่าเราต้องการรับเนื้อหาของตาราง "ฉลาด" พารามิเตอร์
- [เส้นทางไปยังแหล่งข้อมูล] เป็นชื่อคอลัมน์ในตาราง พารามิเตอร์ที่เราอ้างถึง
- 0 {} คือเลขแถวในตาราง พารามิเตอร์จากที่เราต้องการรับข้อมูล ฝาครอบไม่นับและการนับเริ่มต้นจากศูนย์ไม่ใช่จากหนึ่ง
นั่นคือทั้งหมดที่ในความเป็นจริง
มันยังคงคลิกที่ เสร็จสิ้น และตรวจสอบว่าคำขอของเราทำงานอย่างไร ในตอนนี้ เมื่อส่งทั้งโฟลเดอร์ที่มีไฟล์ทั้งสองไฟล์อยู่ภายในพีซีเครื่องอื่น คำขอจะยังคงทำงานและกำหนดเส้นทางไปยังข้อมูลโดยอัตโนมัติ
- Power Query คืออะไรและเหตุใดจึงจำเป็นเมื่อทำงานใน Microsoft Excel
- วิธีการนำเข้าตัวอย่างข้อความแบบลอยไปยัง Power Query
- การออกแบบ XNUMXD Crosstab ใหม่เป็น Flat Table ด้วย Power Query