การผนวกหลายแผ่นงานใน Excel เข้ากับ LuckyTemplates

บทช่วยสอนนี้จะหารือเกี่ยวกับวิธีการนำเข้าและเปิดไฟล์ Excel ที่มีหลายแผ่นไปยังหนึ่งตาราง LuckyTemplates

ไฟล์ Excel ข้อมูลนี้จะใช้สำหรับบทช่วยสอนนี้

ไฟล์มีข้อมูล 60 แผ่นที่มีเค้าโครงเดียวกัน

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

สารบัญ

• การนำเข้าแผ่นงาน Excel ไปยัง LuckyTemplates
• การนำ Security Ticker จาก Excel มาสู่ LuckyTemplates
• การลบแถวว่าง
• การทำสำเนาแบบสอบถาม
• การกำหนดประเภทข้อมูลให้กับคอลัมน์
• การเพิ่มคอลัมน์ Security Ticker จาก Excel ไปยัง LuckyTemplates
• การสร้างคอลัมน์ที่กำหนดเองสำหรับการค้นหาพันธบัตร
• บทสรุป

การนำเข้าแผ่นงาน Excel ไปยัง LuckyTemplates

สิ่งแรกที่ต้องทำคือเปิดPower Query Editorและสร้างพารามิเตอร์เพื่อจัดเก็บตำแหน่งไฟล์โดยใช้ตัวเลือกManage Parameters

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

หลังจากกด OK ให้คลิกNew Sourceแล้วเลือกExcel Workbook

จากนั้น เลือกและเปิดไฟล์ Excel เมื่อเสร็จแล้ว หน้าจอ Navigator จะเปิดขึ้นและแสดงรายการแผ่นงานทั้งหมดภายในไฟล์ Excel

เลือกแผ่นแรกแล้วกดตกลง หลังจากนั้น คุณจะเห็นตารางภายใน Power Query Editor

สิ่งต่อไปที่ต้องทำคือเปลี่ยนพาธไฟล์แบบฮาร์ดโค้ดเป็นพารามิเตอร์ไฟล์ เปิดหน้าต่าง Advanced Editor และเปลี่ยนพา ธของไฟล์ในซอร์สโค้ดเป็นFileLocation

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

การนำ Security Ticker จาก Excel มาสู่ LuckyTemplates

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

ส่วนต่อประสานกับผู้ใช้สามารถช่วยคุณแยกมูลค่าของทิกเกอร์ได้ คลิกขวาที่เซลล์แล้วเลือกAdd As New Query

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

ด้วยรหัสเหล่านี้ คุณสามารถแยกค่าได้แล้ว กลับไปที่ตารางพันธบัตรและรับข้อมูลพันธบัตร ก่อนอื่นให้ลบ 8 แปดแถวบนสุดออก คลิกRemove RowsและเลือกRemove Top Rows

จากนั้นป้อน8ในช่อง Number Of Rows แล้วกด OK

จากนั้นคลิก Use First Row As Headers เพื่อตั้งค่าส่วนหัว

เมื่อเสร็จแล้ว ตารางพันธบัตรจะมีลักษณะดังนี้

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

การลบแถวว่าง

หากต้องการลบแถวว่าง ให้คลิก ลบแถว แล้วเลือกลบแถวว่าง

การแปลงนั้นสร้างไวยากรณ์นี้:

Record.FieldValues ​​รับค่าทั้งหมดจากแถวปัจจุบันในตารางเป็นรายการ List.RemoveMatchingItemsจะลบค่าทั้งหมดในรายการแรกที่ตรงกันในรายการที่สอง รายการที่สองประกอบด้วยสตริงข้อความว่างหรือค่าว่างเท่านั้น ค่าเหล่านี้คือค่าที่จะไม่รวมอยู่ในรายการแรก

ถ้าสตริงข้อความว่างและ null ทั้งหมดถูกลบออกจากรายการที่มีค่าฟิลด์เรกคอร์ด รายการควรว่างเปล่าและ List.IsEmpty จะประเมินค่าเป็น True จากนั้นTable.SelectRowsจะเก็บ Trues ไว้

คุณไม่ควรลงเอยด้วยตารางที่มีแต่แถวว่าง นั่นเป็นสาเหตุที่คำหลักไม่ถูกเพิ่มก่อนList.IsEmpty จากนั้นจะส่งคืนตารางที่มีแถวที่ไม่ว่าง

นอกจากแถวว่างแล้ว คุณต้องลบคอลัมน์ว่างด้วย แต่ก่อนหน้านั้น ลองดูสิ่งที่ Power Query สร้างเมื่อคุณเอาคอลัมน์ออก เลือกคอลัมน์ที่สี่แล้วคลิกลบคอลัมน์

หลังจากดำเนินการแปลงแล้ว คุณจะเห็นไวยากรณ์นี้ในแถบสูตร

เรียกใช้ ฟังก์ชัน Table.RemoveColumnsจากนั้นอ้างอิงและผ่านขั้นตอนก่อนหน้าในบานหน้าต่างขั้นตอนที่ใช้เป็นอาร์กิวเมนต์แรก การแปลงเพิ่มเติมผ่านรายการที่มีชื่อคอลัมน์ของคอลัมน์ที่คุณต้องการลบ

การทำสำเนาแบบสอบถาม

ตอนนี้ ทำซ้ำแบบสอบถามและเลือกขั้นตอนเลื่อนระดับส่วนหัวในบานหน้าต่างขั้นตอนที่ใช้ จากนั้น คลิกขวาที่ขั้นตอนนั้นแล้วเลือกลบจนกว่าจะสิ้นสุด

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

เมื่อเสร็จแล้ว ตาราง Bond จะมีลักษณะดังนี้

จากนั้น นำส่วนหนึ่งของตรรกะที่สร้างขึ้นโดยขั้นตอน Removed Blank Rows มาใช้ใหม่ เพื่อเปลี่ยนเรกคอร์ดให้เป็นรายการและนำค่า Null ออก กลับไปที่แบบสอบถาม Bond และเลือกขั้นตอน Remove Blank Rows ถัดไป คัดลอกรหัส M นี้

กลับไปที่คิวรีที่ซ้ำกัน แล้ววางโค้ดในแถบสูตร จากนั้นจัดเรียงสตริงเพื่อทำให้สูตรมีลักษณะดังนี้

ตอนนี้คุณได้สร้างรายการที่มีชื่อคอลัมน์ที่คุณต้องการเก็บไว้ เปลี่ยน ชื่อ แบบสอบถามชื่อคอลัมน์

จากนั้น กลับไปที่แบบสอบถามพันธบัตร เนื่องจากคุณได้สร้างคิวรีที่มีคอลัมน์ทั้งหมดที่คุณต้องการเก็บไว้ คุณจึงต้องแสดงรายการคอลัมน์ทั้งหมดที่คุณต้องการยกเว้นในฟังก์ชันTable.RemoveColumns

เปลี่ยน{Column4}ด้วยไวยากรณ์เดียวกับที่คัดลอกมาจากขั้นตอน Removed Blank Rows เปลี่ยน {“ ” , null}เป็นColumnNamesด้วย

จากนั้น คุณต้องส่งรายการที่มีชื่อคอลัมน์จริงจากตารางพันธบัตร เปลี่ยนRecord.FieldValues(_)เป็น Table.ColumnNames ( ) ป้อน#”Removed Blank Rows”ภายในวงเล็บเพื่อส่งอาร์กิวเมนต์อ้างอิงตาราง

การกำหนดประเภทข้อมูลให้กับคอลัมน์

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

สำหรับคอลัมน์ PX_LAST และ YLD_YTM_MID ให้เลือกประเภทข้อมูลตัวเลขทศนิยม

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

การเพิ่มคอลัมน์ Security Ticker จาก Excel ไปยัง LuckyTemplates

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

ขั้นแรก ให้เพิ่มเครื่องหมายความปลอดภัย หากคุณคลิกพื้นที่สีขาวภายในเซลล์ของตารางใดๆ คุณจะเห็นตัวอย่างเนื้อหาของตารางนั้น

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

จากนั้น กลับไปที่แบบสอบถาม Bond และเพิ่มคอลัมน์ที่กำหนดเอง

ตั้งชื่อคอลัมน์ Security Ticker และเขียนรหัส M ต่อไปนี้

สูตรมี คำสั่ง IFที่บอกว่าหากพบคำว่า Security ใน Column1 ก็จะให้ค่าของเซลล์จาก Column2 ซึ่งอยู่ติดกัน มิฉะนั้น จะมีการระบุเป็นโมฆะ

หลังจากกดตกลง คอลัมน์ใหม่ที่มีเครื่องหมายความปลอดภัยจะถูกเพิ่มลงในตาราง

คลิกปุ่มดรอปดาวน์ของคอลัมน์ Security Ticker และยกเลิกการเลือกค่า Null เพื่อลบค่า Null ทั้งหมดในคอลัมน์

หลังจากนั้น คุณจะเหลือข้อมูลพันธบัตรทั้งหมดจากชีตทั้งหมด สิ่งที่คุณต้องทำคือทำซ้ำการแปลงที่ทำในแบบสอบถาม Bond(2) และนำไปใช้กับตารางที่ซ้อนกันในคอลัมน์ Data

การสร้างคอลัมน์ที่กำหนดเองสำหรับการค้นหาพันธบัตร

ไปที่แบบสอบถาม Bond(2) เปิดหน้าต่าง Advanced Editor และคัดลอกโค้ดต่อไปนี้:

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

จากนั้น เปลี่ยนBond_Sheetเป็นDataเพื่อแปลงตารางในคอลัมน์ Data

หลังจากกด OK คอลัมน์ใหม่จะถูกเพิ่มในตาราง

หากคุณคลิกที่ช่องว่างภายในเซลล์ใดๆ ของคอลัมน์นั้น คุณจะเห็นข้อมูลจากแบบสอบถาม Bond(2)

สิ่งที่คุณต้องมีคือในสองคอลัมน์สุดท้ายของข้อความค้นหาพันธบัตร ดังนั้น เลือกทั้งคอลัมน์ Security Ticker และ Custom แล้วคลิกRemove Other Columnsจาก Ribbon Remove Columns

ขยายคอลัมน์ Custom และยกเลิกการเลือกช่อง Use The Original Name As Prefix

เมื่อเสร็จแล้ว ตรวจสอบตาราง ด้านล่างตาราง เลือกตัวเลือกการทำโปรไฟล์คอลัมน์ตามชุดข้อมูลทั้งหมด

หลังจากการแปลงนั้น ข้อผิดพลาดจะเกิดขึ้นในคอลัมน์ข้อมูล

ดังนั้น ตรวจสอบข้อผิดพลาดโดยคลิกที่คอลัมน์ Data และเลือก Keep Errors ในแถบ Keep Rows

หากคุณคลิกค่า Error ภายในคอลัมน์ Data คุณสามารถอ่านข้อความแสดงข้อผิดพลาดด้านล่าง:

ในการแก้ปัญหา ให้ลบขั้นตอน Kept Errors ออกก่อน คลิกคอลัมน์ Data แล้วเลือกRemove Errorsจาก Ribbon Remove Rows

จากนั้น ตั้งค่าตัวเลือกการทำโปรไฟล์คอลัมน์กลับเป็น 1,000 แถวบนสุด และนั่นแหล่ะ!

บทสรุป

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

เมลิสซ่า