ดึงข้อมูลที่เลือกโดยใช้ Microsoft Excel Power Query

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

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

ดู: Google Workspace กับ Microsoft 365: การวิเคราะห์พร้อมกับรายการตรวจสอบ (TechRepublic Premium)

เรียนรู้วิธีใช้ฟีเจอร์แยกและแยกคอลัมน์ใน Power Query เพื่อแยกสตริงเฉพาะลงในส่วนประกอบ ฉันใช้ Microsoft 365 เดสก์ท็อปและ Power Query ใน . format Microsoft Excel. Power Query พร้อมใช้งานในเวอร์ชันเก่าผ่าน Excel 10 คุณสามารถดาวน์โหลดไฟล์สาธิต Microsoft Excel สำหรับบทช่วยสอนนี้

เหตุใดคุณจึงควรใช้ Power Query ของ Excel

คุณสามารถใช้ฟังก์ชันสตริงของ Excel, Text to Column หรือ Flash Fill ได้ แต่นี่คือสาเหตุบางประการที่คุณอาจไม่ใช้:

• Text to Columns จะเขียนทับข้อมูลเดิม
• ข้อมูลของคุณอาจไม่อยู่ใน Excel แม้ว่า Power Query จะพร้อมใช้งานใน Excel แต่ Power Query สามารถนำเข้าข้อมูลจากหลายแหล่งได้ ไม่ใช่แค่จาก Excel เท่านั้น
• ข้อมูลต้นฉบับประกอบด้วยแถวอื่นๆ ที่คุณสามารถนำเข้าไปยัง Excel ได้
• คุณอาจต้องใช้ Power Query สำหรับบางสิ่งที่ซับซ้อนกว่านั้น และการแยกเซ็ตย่อยของอินพุตดั้งเดิมเป็นเพียงขั้นตอนแรก

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

วิธีนำข้อมูลเข้าสู่ Power Query

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

สมมติว่าคุณมีรายการ CIN โดยแต่ละส่วนมีสามส่วน นอกจากนี้ อักขระยัติภังค์ยังทำหน้าที่เป็นตัวคั่นระหว่างสามส่วน (รูป A). คุณต้องการใช้องค์ประกอบตรงกลางของแต่ละสตริงเพราะเป็นส่วนที่กำหนดลูกค้าแต่ละรายจริงๆ อีกสององค์ประกอบกำหนดภูมิภาคที่ลูกค้าอาศัยอยู่และหมายเลขธุรกรรม

รูป A

ขั้นตอนแรกคือการโหลดข้อมูลลงใน Power Query ดังนี้:

1. คลิกที่ใดก็ได้ภายในตาราง

2. คลิกที่แท็บข้อมูล

3. ในกลุ่มข้อมูล รับและแปลง ให้คลิก จากตาราง/ช่วง

นี่ไง. ตารางอย่างง่ายที่แสดงใน รูป A ตอนนี้อยู่ใน Power Query

ด้วยข้อมูลใน Power Query คุณสามารถเริ่มแยกคลิปได้

วิธีแยกสตริงเฉพาะโดยใช้ตัวเลือกแยกใน Power Query

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

อย่างที่คุณเห็นใน รูป Bมีตัวเลือกมากมายและส่วนใหญ่จะอธิบายตนเองได้ เราจะดูที่ตัวเลือกต่างๆ เพื่อให้คุณเห็นว่าแต่ละตัวเลือกทำอะไรได้บ้าง

รูป B

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

รูป C

อย่างที่คุณเห็นใน รูป dตัวเลือกนี้จะส่งกลับเฉพาะอักขระตัวแรกก่อนตัวคั่น

รูป d

หากต้องการกู้คืนข้อมูลเดิม ให้ลบขั้นตอนที่ดึงออกมาจากข้อความก่อนระบุในส่วนของขั้นตอนที่ใช้ที่แสดงใน รูป E.

รูป E

ตอนนี้ ลองทำเช่นเดียวกันกับตัวเลือกถัดไป ข้อความหลังตัวเลือก เมื่อได้รับแจ้ง ให้ป้อนเครื่องหมายยัติภังค์แล้วคลิกตกลงเพื่อดูผลลัพธ์ที่แสดงใน รูปร่างและ. คราวนี้ Power Query จะลบอักขระสองตัวแรก ตัวเลขตัวแรกและยัติภังค์แรก

รูปร่างและ

กู้คืนข้อมูลเดิมอีกครั้งโดยลบขั้นตอนการแยกแล้วเลือกตัวเลือกข้อความจากตัวคั่น คราวนี้ Power Query จะพร้อมท์ให้ตัวเลือก ในกรณีนี้ ทั้งคู่เป็นอักขระยัติภังค์ (รูปร่าง g).

รูปร่าง g

คลิกตกลงเพื่อดูผลลัพธ์ที่แสดงใน รูปร่าง h.

รูปร่าง h

ตอนนี้เราจะพิจารณาอีกวิธีในการแบ่งสามส่วนของคอร์ดแต่ละคอร์ด แต่เราจะไม่แยกชิ้นส่วนออกจากคอร์ด เราจะแบ่งคอร์ด กู้คืนข้อมูลเดิมก่อนดำเนินการต่อ

วิธีแยกสตริงเฉพาะโดยใช้คอลัมน์แยกใน Power Query

คอลัมน์แยกใน Power Query ช่วยให้คุณส่งคืนสตริงได้มากกว่าหนึ่งส่วน ตัวอย่างเช่น สมมติว่าคุณต้องการข้อมูลสามคอลัมน์ หนึ่งคอลัมน์สำหรับแต่ละส่วน เพื่อให้บรรลุสิ่งนี้ ให้ใช้คอลัมน์แยกดังนี้:

1. หลังจากเลือกคอลัมน์แล้ว ให้คลิกที่แท็บ หน้าแรก

2. ในกลุ่ม Transform ให้คลิก Split Column

3. คลิกที่ตัวเลือกแรกตามตัวเลือก

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

รูปแรก

5. คลิกตกลงเพื่อดูผลลัพธ์ใน เจ . รูปร่าง.

เจ . รูปร่าง

ตัวเลือกนี้แยกแต่ละชุดข้อมูลออกเป็นสามคอลัมน์ โดยใช้อักขระที่ระบุเพื่อระบุว่าแต่ละส่วนเริ่มต้นและสิ้นสุดที่ใด

อย่างที่คุณเห็น Extract และ Split Column ช่วยให้คุณแยกข้อมูลได้อย่างรวดเร็ว คุณอาจพบการใช้งานสำหรับทั้งสองอย่าง