Power Query: นำเข้าและแปลงข้อมูลอย่างมือโปร

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

Power Query มักถูกเรียกกันว่า "รับและแปลงข้อมูล (Get & Transform Data)" ซึ่งช่วยให้คุณสามารถเชื่อมต่อกับแหล่งข้อมูลได้เกือบทุกประเภท ทำความสะอาดและปรับรูปแบบข้อมูลใหม่ และโหลดลงในสเปรดชีตของคุณได้ และที่ดีที่สุดคืออะไร? มันจะบันทึกขั้นตอนที่คุณทำไว้ ในครั้งถัดไปที่คุณได้รับข้อมูลใหม่ คุณไม่จำเป็นต้องทำซ้ำขั้นตอนเดิมแบบแมนนวลอีก เพียงแค่คลิก รีเฟรช (Refresh)

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

Power Query คืออะไร?

Power Query คือกลไกเชื่อมต่อและเตรียมข้อมูล ในโลกของการจัดการฐานข้อมูล กระบวนการนี้เป็นที่รู้จักกันในชื่อ ETL: Extract (สกัด), Transform (แปลง) และ Load (โหลด)

• สกัด (Extract): ดึงข้อมูลจากแหล่งต่างๆ (สมุดงาน Excel, ไฟล์ CSV และข้อความ, หน้าเว็บ, ฐานข้อมูล SQL หรือแม้แต่บริการคลาวด์)
• แปลง (Transform): ทำความสะอาดและปรับรูปแบบข้อมูลใหม่ รวมถึงการกรองแถว เปลี่ยนชนิดข้อมูล แยกข้อความ ผสานตาราง และการสร้างคอลัมน์จากการคำนวณ
• โหลด (Load): ส่งออกข้อมูลที่ปรับปรุงแล้วไปยังตาราง Excel (Excel Table), PivotTable หรือ Power Pivot Data Model ได้โดยตรง

ตามปกติแล้ว ผู้ใช้ Excel จะพึ่งพาการใช้ฟังก์ชันร่วมกัน เช่น TRIM, PROPER, SUBSTITUTE และ VLOOKUP ควบคู่ไปกับการคัดลอกและวางแบบแมนนวลเพื่อจัดการงานเหล่านี้ แต่ Power Query ได้เข้ามาแทนที่เวิร์กโฟลว์ที่น่าเบื่อนั้นด้วยอินเทอร์เฟซแบบภาพที่ใช้งานง่าย

ทำไมคุณถึงควรเรียนรู้ Power Query

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

• ไม่ต้องเขียนโค้ด: แม้ว่า Power Query จะทำงานด้วยภาษาของสูตรที่เรียกว่า "M" แต่คุณสามารถทำการแปลงข้อมูลได้ถึง 95% โดยใช้เพียงแค่ปุ่มบนริบบอนและเมนูคลิกขวาเท่านั้น
• ระบบอัตโนมัติโดยไม่ต้องใช้ VBA: คุณไม่จำเป็นต้องรู้วิธีเขียนมาโคร (Macro) เพื่อสร้างระบบอัตโนมัติสำหรับงานที่ต้องทำซ้ำๆ Power Query จะจดจำ "ขั้นตอนที่นำไปใช้ (Applied Steps)" ของคุณ และนำไปใช้กับข้อมูลใหม่โดยอัตโนมัติ
• จัดการข้อมูลขนาดใหญ่ได้ (Big Data): Excel มีขีดจำกัดทางกายภาพอยู่ที่ 1,048,576 แถว แต่ Power Query สามารถเชื่อมต่อกับชุดข้อมูลที่มีหลายล้านแถว สรุปข้อมูลเบื้องหลัง และโหลดเฉพาะผลลัพธ์ที่สรุปแล้วลงในสเปรดชีตของคุณได้
• การแก้ไขแบบไม่ทำลายข้อมูลต้นฉบับ (Non-Destructive Editing): Power Query จะไม่เปลี่ยนแปลงแหล่งข้อมูลต้นฉบับของคุณ มันเพียงแค่สร้างการเชื่อมต่อ อ่านข้อมูล และส่งออกเป็นสำเนาข้อมูลที่ทำความสะอาดใหม่

เริ่มต้นใช้งาน: ตัวแก้ไข Power Query (Power Query Editor)

หากต้องการเข้าถึง Power Query ให้เปิดสมุดงาน Excel เปล่าขึ้นมาและไปที่แท็บ ข้อมูล (Data) บนริบบอน (Ribbon) มองหากลุ่ม รับและแปลงข้อมูล (Get & Transform Data) ที่อยู่ทางด้านซ้ายสุด

จากตรงนี้ คุณสามารถคลิก รับข้อมูล (Get Data) เพื่อดูเมนูแบบเลื่อนลงของแหล่งข้อมูลที่ใช้งานได้ เมื่อคุณเลือกไฟล์แล้วคลิก "แปลงข้อมูล (Transform Data)" Excel จะเปิด ตัวแก้ไข Power Query (Power Query Editor) ในหน้าต่างใหม่ ซึ่งอินเทอร์เฟซนี้ประกอบด้วย 4 ส่วนหลัก:

1. ริบบอน (The Ribbon): อยู่ที่ด้านบนสุด ประกอบด้วยแท็บต่างๆ เช่น หน้าแรก (Home), แปลง (Transform), เพิ่มคอลัมน์ (Add Column) และ มุมมอง (View) นี่คือที่รวมเครื่องมือทั้งหมดของคุณ
2. บานหน้าต่างคิวรี (The Queries Pane): อยู่ทางด้านซ้าย แสดงรายการการเชื่อมต่อข้อมูลทั้งหมดที่ทำงานอยู่ในสมุดงานของคุณ
3. การแสดงตัวอย่างข้อมูล (The Data Preview): ตารางตรงกลางจะแสดงภาพตัวอย่างของข้อมูลตามสถานะจริงในขั้นตอนที่คุณกำลังเลือกอยู่
4. บานหน้าต่างการตั้งค่าคิวรี / ขั้นตอนที่นำไปใช้ (The Query Settings Pane / Applied Steps): อยู่ทางด้านขวา นี่คือส่วนที่สำคัญที่สุดของตัวแก้ไข ทุกครั้งที่คุณเปลี่ยนบางอย่าง Power Query จะบันทึกสิ่งนั้นไว้ที่นี่เป็นลำดับขั้นตอน คุณสามารถลบ จัดเรียงใหม่ หรือแก้ไขขั้นตอนเหล่านี้ได้ทุกเมื่อ

ตัวอย่างแบบทีละขั้นตอน: การทำความสะอาดข้อมูลยอดขายที่ยุ่งเหยิง

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

นี่คือตัวอย่างของข้อมูลดิบที่ยุ่งเหยิงของเรา:

System Export: Q3 Sales Report	Column2	Column3
Generated on: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

หากเราใช้สูตรแบบดั้งเดิม เราคงต้องใช้ฟังก์ชัน LEFT, RIGHT, FIND และ VALUE เพื่อแยกชื่อพนักงานขายและแก้ไขตัวเลข แทนที่จะเป็นเช่นนั้น เรามาลองใช้ Power Query กันดีกว่า

ขั้นตอนที่ 1: นำเข้าข้อมูล

บันทึกข้อมูลที่ยุ่งเหยิงนั้นเป็นไฟล์ CSV หรือ Excel เปิดสมุดงาน Excel ใหม่ ไปที่ ข้อมูล (Data) > รับข้อมูล (Get Data) > จากไฟล์ (From File) และเลือกไฟล์ของคุณ เมื่อหน้าต่างแสดงตัวอย่างปรากฏขึ้น ให้คลิก แปลงข้อมูล (Transform Data) ตัวแก้ไข Power Query จะเปิดขึ้น

ขั้นตอนที่ 2: ลบแถวบนสุดและเลื่อนส่วนหัวขึ้น

สองแถวแรกในข้อมูลของเราคือข้อมูลเมตา (Metadata) ที่ระบบส่งออกมา ไม่ใช่บันทึกข้อมูลจริงๆ เราต้องกำจัดมันทิ้งไป

• ที่แท็บหน้าแรก (Home) ให้คลิก เอาแถวออก (Remove Rows) > เอาแถวบนสุดออก (Remove Top Rows) พิมพ์เลข "2" และคลิกตกลง (OK)
• ตอนนี้ ส่วนหัวคอลัมน์จริงๆ ของเราจะมาอยู่ที่แถวที่ 1 ให้คลิกปุ่ม ใช้แถวแรกเป็นส่วนหัว (Use First Row as Headers) บนแท็บหน้าแรก ทันใดนั้น "Rep_ID_Name", "Order_Date" และ "Revenue" ก็จะกลายเป็นชื่อคอลัมน์ทางการของคุณ

ขั้นตอนที่ 3: แยกคอลัมน์พนักงานขาย (Rep)

คอลัมน์ "Rep_ID_Name" มีทั้งหมายเลข ID และชื่อพนักงานรวมอยู่ด้วยกันโดยถูกคั่นด้วยเครื่องหมายยัติภังค์ ( - )

• คลิกขวาที่ส่วนหัวคอลัมน์ "Rep_ID_Name"
• เลือก แยกคอลัมน์ (Split Column) > ตามตัวคั่น (By Delimiter)
• เลือก "กำหนดเอง (Custom)" แล้วพิมพ์เครื่องหมายยัติภังค์ ( - ) คลิกลงตกลง (OK)
• Power Query จะแยกข้อมูลออกเป็นสองคอลัมน์ ตอนนี้คุณสามารถดับเบิลคลิกที่ส่วนหัวคอลัมน์ใหม่เพื่อเปลี่ยนชื่อเป็น "Rep_ID" และ "Rep_Name" ได้เลย

หากต้องการทำความสะอาดเครื่องหมายขีดล่างในชื่อของ Bob (Bob_Jones) ให้คลิกขวาที่คอลัมน์ Rep_Name เลือก แทนที่ค่า (Replace Values) พิมพ์เครื่องหมายขีดล่าง (_) ในช่อง "ค่าที่จะค้นหา (Value to Find)" แล้วเว้นว่างช่อง "แทนที่ด้วย (Replace With)" ไว้หรือเพิ่มช่องว่างเข้าไปแทน คลิกตกลง (OK)

ขั้นตอนที่ 4: ปรับชนิดข้อมูลให้เป็นมาตรฐาน

สังเกตไหมว่าวันที่และรายได้ของเราอยู่ในรูปแบบที่แตกต่างกันโดยสิ้นเชิง? Power Query ช่วยให้การปรับรูปแบบให้เป็นมาตรฐานนั้นเป็นเรื่องง่าย

• คลิกไอคอนเล็กๆ ทางด้านซ้ายของส่วนหัวคอลัมน์ "Order_Date" (ซึ่งอาจจะแสดงเป็น "ABC" หรือ "Any") ให้เปลี่ยนเป็น วันที่ (Date) Power Query จะแปลรูปแบบวันที่ต่างๆ ให้กลายเป็นมาตรฐานวันที่แบบเดียวกันโดยอัตโนมัติ
• คลิกไอคอนถัดจาก "Revenue" แล้วเปลี่ยนเป็น สกุลเงิน (Currency) (หรือ ทศนิยม / Decimal Number) มันจะตัดเครื่องหมายดอลลาร์ออกและจัดรูปแบบทุกอย่างให้ดูสะอาดตา

ขั้นตอนที่ 5: เพิ่มคอลัมน์แบบมีเงื่อนไข

สมมติว่าเราต้องการจัดหมวดหมู่ยอดขายที่มีมูลค่าเกิน 1,000 ดอลลาร์เป็น "High Value" (มูลค่าสูง) แทนที่จะเขียนฟังก์ชัน IF ที่ซับซ้อน เช่น =IF(C2>=1000, "High Value", "Standard") ใน Excel เราก็สามารถใช้ UI ของ Power Query แทนได้เลย

ไปที่แท็บ เพิ่มคอลัมน์ (Add Column) แล้วคลิก คอลัมน์แบบมีเงื่อนไข (Conditional Column) กำหนดกฎว่า: ถ้า (If) [Revenue] มากกว่าหรือเท่ากับ 1000 ให้ส่งค่าออกเป็น "High Value" มิฉะนั้น (Else) เป็น "Standard" ในเบื้องหลังแล้ว Power Query จะสร้างโค้ด M ต่อไปนี้สำหรับขั้นตอนนี้:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

ผสานคิวรี (Merging Queries): ทางเลือกใหม่ที่ทันสมัยแทน VLOOKUP

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

อย่างไรก็ตาม การรันสูตร VLOOKUP หรือ INDEX และ MATCH นับพันๆ แถวสามารถทำให้สมุดงานของคุณช้าลงอย่างมาก แต่ใน Power Query คุณสามารถใช้ฟีเจอร์ ผสานคิวรี (Merge Queries) แทนได้

เพียงนำเข้าทั้งสองตารางไปยัง Power Query เลือกตารางยอดขายหลักของคุณ แล้วคลิก ผสานคิวรี (Merge Queries) ในแท็บหน้าแรก เลือกตารางที่สอง (ตาราง Regions) จากนั้นคลิกที่คอลัมน์ที่ตรงกันในทั้งสองตาราง (เช่น "Rep_ID") แล้วคลิกตกลง Power Query จะทำงานเทียบเท่ากับ VLOOKUP ที่รวดเร็วสุดๆ ในไม่กี่วินาที ไม่ว่าคุณจะมีข้อมูลสิบแถวหรือสิบล้านแถวก็ตาม

ยกเลิกการสรุปข้อมูล (Unpivoting Data): อาวุธลับของ Power Query

บ่อยครั้งที่คุณมักจะได้รับข้อมูลที่จัดกลุ่มไว้ในโครงสร้างแบบ Pivot แล้ว (เช่น เอาเดือนไปเรียงต่อกันเป็นคอลัมน์: Jan, Feb, Mar, Apr) แม้ว่ารูปแบบนี้จะง่ายต่อการอ่านด้วยตาเปล่า แต่มันก็เลวร้ายมากสำหรับการนำไปสร้างแผนภูมิหรือ PivotTable

เลือกคอลัมน์ตัวระบุข้อมูล (เช่น Rep Name) คลิกขวาที่ส่วนหัวคอลัมน์ แล้วเลือก ยกเลิกการสรุปคอลัมน์อื่นๆ (Unpivot Other Columns) Power Query จะเปลี่ยนข้อมูลแบบกว้างหรือตารางไขว้ (Cross-tabular) ของคุณให้กลายเป็นตารางแบบแบนที่มีเลย์เอาต์คอลัมน์ใหม่สำหรับ "แอตทริบิวต์ (Attribute)" (เดือน) และ "ค่า (Value)" (ยอดขาย) ในทันที การทำเช่นนี้ด้วยสูตร Excel มาตรฐานแทบจะเป็นไปไม่ได้เลย นี่จึงเป็นสิ่งที่ทำให้การ Unpivot กลายเป็นหนึ่งในฟีเจอร์ที่ได้รับการยกย่องมากที่สุดของ Power Query

โหลดข้อมูลที่ผ่านการแปลงแล้ว

เมื่อข้อมูลของคุณสะอาดเรียบร้อยสมบูรณ์แล้ว ก็ถึงเวลาส่งกลับไปที่ Excel

ที่แท็บหน้าแรก คลิก ปิดและโหลด (Close & Load) โดยค่าเริ่มต้น ระบบจะโหลดข้อมูลที่ถูกแปลงแล้วใส่ในตาราง Excel ตารางใหม่สีเขียวบนแผ่นงานใหม่ หากคุณต้องการส่งข้อมูลตรงเข้าสู่ขั้นตอนการวิเคราะห์เลย คุณสามารถคลิกที่ลูกศรชี้ลง เลือก ปิดและโหลดไปยัง... (Close & Load To...) แล้วเลือกรายงาน PivotTable แทน หากคุณต้องการทบทวนวิธีสร้างการสรุปข้อมูลเหล่านี้ ลองดูบทช่วยสอนในการสร้าง Pivot Table สำหรับผู้เริ่มต้น

ความมหัศจรรย์ของปุ่ม "รีเฟรช (Refresh)"

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

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

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

ทำให้เส้นทางข้อมูลของคุณง่ายขึ้นด้วย AI

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

หากคุณพบว่าตัวเองกำลังปวดหัวกับการเขียนสูตรคำนวณคอลัมน์แบบกำหนดเอง GPTExcel คือเพื่อนคู่คิดที่สมบูรณ์แบบของคุณ เพียงแค่อธิบายสิ่งที่คุณต้องการจะทำเป็นภาษาพูดง่ายๆ—เช่น "ฉันต้องการสูตรที่สกัดเฉพาะตัวเลขออกจากข้อความที่ปนกันอยู่"—แล้ว GPTExcel จะสร้างสูตรหรือโค้ด M ที่ถูกต้องให้ในพริบตา การผสานการใช้งาน Power Query ร่วมกับเครื่องมือ AI สำหรับการทำความสะอาดข้อมูล จะมอบชุดเครื่องมือวิเคราะห์ข้อมูลที่ไร้ขีดจำกัดให้กับคุณ

คำถามที่พบบ่อย (FAQ)

Power Query จะเปลี่ยนแปลงไฟล์ข้อมูลต้นฉบับของฉันหรือไม่?

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

ฉันสามารถใช้ Power Query บน Mac ได้หรือไม่?

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

Merge และ Append ใน Power Query แตกต่างกันอย่างไร?

ผสาน (Merge) นั้นเทียบเท่ากับการใช้ VLOOKUP หรือ INDEX/MATCH คุณใช้มันเพื่อเพิ่มคอลัมน์ใหม่ของข้อมูลโดยการจับคู่ ID ที่เหมือนกันระหว่างตารางสองตาราง ส่วน เพิ่ม (Append) จะเหมือนกับการคัดลอกและวางข้อมูลต่อท้ายที่ด้านล่างของแผ่นงาน คุณใช้มันเพื่อซ้อนตารางเข้าด้วยกัน ซึ่งก็คือการเพิ่มแถวใหม่ (เช่น การนำยอดขายเดือนมกราคมมารวมกับยอดขายเดือนกุมภาพันธ์)

ทำไมการรีเฟรช Power Query ของฉันถึงล้มเหลว?

สาเหตุที่พบบ่อยที่สุดที่ทำให้การรีเฟรชคิวรีล้มเหลวคือ ไฟล์ต้นทางถูกย้าย ถูกเปลี่ยนชื่อ หรือถูกลบ อีกหนึ่งปัญหาที่เจอบ่อยคือ ส่วนหัวของคอลัมน์ในข้อมูลดิบมีการเปลี่ยนแปลง (เช่น ระบบอาจเปลี่ยนชื่อจาก "Revenue" เป็น "Total Revenue") คุณสามารถแก้ไขปัญหานี้ได้โดยการเปิดตัวแก้ไข Power Query ไปที่บานหน้าต่างขั้นตอนที่นำไปใช้ (Applied Steps) แล้วอัปเดตตรงขั้นตอนแหล่งข้อมูล (Source) หรือทำการเปลี่ยนชื่อคอลัมน์ในตรรกะขั้นตอนของคุณใหม่