Power Query คือฟีเจอร์เปลี่ยนเกมของ Excel ที่คุณยังไม่ได้ใช้—นี่คือ 5 วิธีในการนำมันไปใช้งาน

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

ฉันจะแนะนำคุณผ่านห้าขั้นตอนการแปลงโดยใช้ชุดข้อมูลเดียวกัน—เริ่มจากการสกัดข้อความและทำความสะอาด จากนั้นไปสู่การสรุปชุดข้อมูลขนาดใหญ่โดยไม่ใช้ PivotTable

ปรับปรุงและแก้ไขข้อมูลในเวิร์กชีตปัจจุบันของคุณ

แปลงข้อความที่ยุ่งเหยิงและสรุปค่าสำคัญ

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

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

ก่อนอื่น ให้โหลดตารางของคุณเข้าสู่ Power Query Editor เลือกเซลล์ใดก็ได้ภายในข้อมูล และในแท็บ Data ให้คลิก From Table/Range

คุณสามารถดูโต๊ะของคุณได้แล้วในหน้าต่างใหม่

เวิร์กโฟลว์ 1: การแยกและจัดระเบียบจุดข้อมูลเฉพาะ

สกัดและทำความสะอาดข้อมูลเพื่อการรายงานที่ดียิ่งขึ้น

เป้าหมายของคุณ: เพื่อแปลงสตริงดิบ เช่น TSHIRT_XL_RED_001 ให้กลายเป็นสามคอลัมน์แยกที่สะอาด: หนึ่งสำหรับชื่อไอเท็มที่สะกดถูกต้อง ("T-shirt"), หนึ่งสำหรับขนาด ("XL"), และหนึ่งสำหรับสี ("Red")

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

ขั้นตอนที่ 1: แยกและเปลี่ยนชื่อคอลัมน์ของคุณ

ขั้นตอนแรกคือการแยกคอลัมน์ SKU ออกเป็น รายการ ขนาด สี และรหัส และตั้งชื่อให้ชัดเจน:

1. เลือกคอลัมน์ SKU
2. ในแท็บ หน้าแรก คลิก แยกคอลัมน์ > ตามตัวคั่น
3. เลือก กำหนดเอง เป็นประเภทตัวคั่น, ใส่ อันเดอร์สกอร์ (_), เลือก ทุกครั้งที่ตัวคั่นปรากฏ, และคลิก ตกลง.
4. ดับเบิลคลิกที่ หัวเรื่อง ของ คอลัมน์ใหม่ แต่ละคอลัมน์ และเปลี่ยนชื่อเป็น รายการ, ขนาด, สี, และ รหัส ตามลำดับ

ขั้นตอนที่ 2: ลบคอลัมน์ ID ที่ไม่จำเป็น

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

1. กด Ctrl และคลิกที่หัวคอลัมน์ทั้งหมด ยกเว้น ID
2. คลิกขวาที่ หัวข้อที่เลือก หนึ่งรายการ แล้วคลิก ลบคอลัมน์อื่น

ขั้นตอนที่ 3: แก้ไขรูปแบบของคุณ

ตอนนี้โครงสร้างถูกตั้งค่าเรียบร้อยแล้ว คุณสามารถแก้ไขรูปแบบของคอลัมน์ Item ได้ ขั้นตอนนี้มีสองเฟส: เฟสแรกคือการแปลงง่าย ๆ ที่เปลี่ยนอักษรตัวพิมพ์ใหญ่ทั้งหมดให้เป็นการใช้ตัวพิมพ์ปกติ (ตัวอย่างเช่น "TSHIRT" เป็น "Tshirt") และเฟสที่สองคือการเปลี่ยน "Tshirt" เป็น "T-shirt"

1. เลือกคอลัมน์ รายการ
2. ในแท็บ Transform ให้คลิก Format > Capitalize Each Word
3. คลิกขวาที่หัวคอลัมน์ Item และเลือก Replace Values
4. เปลี่ยน Tshirt เป็น T-shirt จากนั้นคลิก ตกลง.

ขั้นตอนที่ 4: แปลรหัสสี

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

1. ในแท็บ เพิ่มคอลัมน์ คลิก คอลัมน์ตามเงื่อนไข
2. ตั้งชื่อคอลัมน์ใหม่ว่า สีที่เหมาะสม
3. เพิ่มกฎแรกของคุณ: ถ้า Color เท่ากับ WHT ให้แสดงผล White.
4. คลิก เพิ่มข้อตกลง ทำซ้ำขั้นตอนนี้สำหรับสีอื่น ๆ และคลิก ตกลง
5. คลิก และ ลาก คอลัมน์ใหม่ไปทางซ้ายเพื่อให้เป็นคอลัมน์ที่สาม
6. คลิกขวาที่หัวคอลัมน์ สี เดิมที่รก แล้วเลือก ลบ

ข้อมูลของคุณตอนนี้สะอาด สอดคล้อง และเป็นมาตรฐานแล้ว และคุณพร้อมที่จะไปยังเวิร์กโฟลว์ 2

เวิร์กโฟลว์ 2: สรุปชุดข้อมูลขนาดใหญ่โดยไม่ใช้ PivotTable

รวมผลรวมธุรกรรมเพื่อการรายงานระดับสูง

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

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

1. ในบานหน้าต่าง Queries ทางด้านซ้าย คลิกขวาที่ query ที่มีอยู่ ของคุณแล้วเลือก Reference
2. ดับเบิลคลิกที่ การสอบถามใหม่ นี้และเปลี่ยนชื่อเป็น สรุปรายงาน
3. เมื่อเลือกคำค้นหาใหม่นี้แล้ว คุณก็พร้อมที่จะดำเนินการย้ายครั้งสุดท้ายที่ 5

ขั้นตอนที่ 5: จัดกลุ่มตามหมวดหมู่

คุณสมบัติ Group By ของ Power Query ช่วยให้คุณทำการรวมข้อมูลซึ่งคุณมักจะใช้ใน PivotTables แม้ว่า PivotTables จะเหมาะสำหรับการวิเคราะห์แบบโต้ตอบ Power Query นั้นเหมาะสมกว่าสำหรับกระบวนงานรายงานที่ทำซ้ำได้และสามารถรีเฟรชได้ รวมถึงการหลีกเลี่ยงโครงสร้างข้อมูลซ้ำส่วนตัวแล้ว ผมพบว่า Power Query Editor ใช้งานง่ายกว่าหน้าต่าง PivotTable Fields

1. เลือกคอลัมน์ หมวดหมู่
2. ในแท็บ Transform (หรือแท็บ Home ในบางเวอร์ชัน) คลิก Group By
3. ในกล่องโต้ตอบ ให้แน่ใจว่าได้เลือก พื้นฐาน และตั้งค่า ชื่อคอลัมน์ใหม่ เป็น จำนวนหน่วยที่ขายทั้งหมด
4. ตั้งค่า Operation เป็น Sum และ Column เป็น Units Sold.
5. คลิก ตกลง.

ตอนนี้ ข้อมูลของคุณถูกสรุปอย่างกระชับเป็นตารางที่อ่านง่าย

สุดท้าย ให้คลิกครึ่งบนของปุ่ม ปิด & โหลด ที่แยกออกในแท็บ หน้าแรก เพื่อโหลดตารางใหม่ของคุณไปยังแท็บเวิร์กชีตแยกต่างหาก หากข้อมูลต้นฉบับของคุณมีการเปลี่ยนแปลง ให้คลิก ข้อมูล > รีเฟรชทั้งหมด เพื่ออัปเดตการสืบค้นทั้งสอง.

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