Power Query สำหรับ Excel: ทำความสะอาดและจัดการไฟล์ CSV ที่ยุ่งเหยิงโดยอัตโนมัติได้ทันที

Power Query สำหรับ Excel: ทำความสะอาดและจัดการไฟล์ CSV ที่ยุ่งเหยิงโดยอัตโนมัติได้ทันที

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

บทความนี้ใช้ไฟล์ CSV ตัวอย่างสองไฟล์ ได้แก่ Sales_Report_January.csv และ Sales_Report_February.csv เมื่อคุณเปิดลิงก์ คุณสามารถดาวน์โหลดไฟล์ได้ฟรีโดยคลิกปุ่มดาวน์โหลดที่มุมบนขวาของหน้าจอ เมื่อดาวน์โหลดเสร็จแล้ว ให้ย้ายไฟล์ทั้งสองไปยังโฟลเดอร์ใหม่ที่จัดเตรียมไว้โดยเฉพาะบนคอมพิวเตอร์ของคุณ

Power Query ช่วยจัดการไฟล์ CSV ที่ยุ่งเหยิงได้อย่างรวดเร็ว

แก้ไขข้อมูลที่เสียหายโดยอัตโนมัติ

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

Laptop screen showing Microsoft Excel's Power Query Editor with a query.
Laptop screen showing Microsoft Excel's Power Query Editor with a query.

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

ขั้นตอนที่ 1: นำเข้าไฟล์ CSV ของคุณลงใน Excel อย่างชาญฉลาด

เริ่มต้นด้วยกระบวนการนำเข้าที่ถูกต้อง

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

เพื่อทำตามขั้นตอนในส่วนนี้ โปรดใช้ไฟล์ Sales_Report_January.csv ที่ลิงก์ไว้ด้านบน

ขั้นตอนการทำงานมีดังนี้:

  • เปิดเวิร์กบุ๊กเปล่าแล้วไปที่แท็บข้อมูล
    The Data tab is opened in a blank Excel workbook.
    The Data tab is opened in a blank Excel workbook.
  • คลิก รับข้อมูล > จากไฟล์ > จากไฟล์ข้อความ/CSV
    From Text/CSV is selected in the Get Data drop-down menu in Microsoft Excel.
    From Text/CSV is selected in the Get Data drop-down menu in Microsoft Excel.
  • ค้นหาและเลือกไฟล์ CSV จากนั้นคลิกนำเข้า
    A CSV file named Sales_Report_January is selected in Excel's Import Data window.
    A CSV file named Sales_Report_January is selected in Excel's Import Data window.
  • ตรวจสอบหน้าต่างแสดงตัวอย่างเพื่อให้แน่ใจว่า Excel ตรวจพบตัวคั่น (โดยปกติคือเครื่องหมายจุลภาค) อย่างถูกต้อง หากข้อมูลไม่ถูกแบ่งออกเป็นคอลัมน์อย่างถูกต้อง ให้เปลี่ยนตัวคั่น
    Comma is selected as the delimiter in the Excel import window.
    Comma is selected as the delimiter in the Excel import window.
  • คลิก แปลงข้อมูล
    Transform Data is selected in the Excel import workflow.
    Transform Data is selected in the Excel import workflow.

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

ขั้นตอนที่ 2: ปรับแต่งรูปแบบไฟล์ CSV ให้เรียบร้อยด้วยการคลิกเพียงไม่กี่ครั้ง

ขจัดสิ่งสกปรก

เมื่อโปรแกรมแก้ไข Power Query เปิดขึ้น คุณสามารถเริ่มแก้ไขปัญหาที่มักทำให้การทำงานกับไฟล์ CSV ดิบเป็นเรื่องยากได้

ส่วนนี้เป็นการต่อเนื่องจากขั้นตอนก่อนหน้า ดังนั้นคุณยังคงควรใช้งานไฟล์ Sales_Report_January.csv ที่นำเข้าอยู่

แยกชื่อเต็ม

  • คลิกขวาที่คอลัมน์ชื่อเต็ม
    A Full Name column is selected in Excel's Power Query Editor.
    A Full Name column is selected in Excel's Power Query Editor.
  • เลือก "แยกคอลัมน์" > "ตามตัวคั่น"
    By Delimiter is selected in the Split Column menu of Excel's Power Query Editor.
    By Delimiter is selected in the Split Column menu of Excel's Power Query Editor.
  • เลือกเว้นวรรคเพื่อแยกคอลัมน์ออกเป็นชื่อและนามสกุล แล้วคลิก ตกลง
    Space is selected as the delimiter when splitting columns in Power Query.
    Space is selected as the delimiter when splitting columns in Power Query.
  • ดับเบิ้ลคลิกที่ส่วนหัวคอลัมน์ใหม่เพื่อเปลี่ยนชื่อ
    Two columns, which are created in Power Query Editor by splitting a name column, are renamed First Name and Second Name.
    Two columns, which are created in Power Query Editor by splitting a name column, are renamed First Name and Second Name.

แก้ไขรูปแบบวันที่ที่ไม่สอดคล้องกัน

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

Date is selected in the number type drop-down menu of a column in Power Query Editor.
Date is selected in the number type drop-down menu of a column in Power Query Editor.

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

A column named Order Date in Power Query Editor displays two Error values.
A column named Order Date in Power Query Editor displays two Error values.

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

ข้อมูลจำเพาะของ Microsoft 365 Personal
คุณสมบัติ รายละเอียด
โอเอส วินโดวส์, มอสซาเรลล่า, ไอโฟน, ไอแพด, แอนดรอยด์
ทดลองใช้ฟรี 1 เดือน

Microsoft 365 ประกอบด้วยการเข้าถึงแอป Office เช่น Word, Excel และ PowerPoint บนอุปกรณ์ได้สูงสุดห้าเครื่อง พื้นที่เก็บข้อมูล OneDrive 1 TB และอื่นๆ อีกมากมาย

Microsoft 365 Personal.
Microsoft 365 Personal.

รักษาข้อมูลรหัสไปรษณีย์และรหัสสินค้าไว้

หากรหัสไปรษณีย์เช่น 02138 หรือรหัสสินค้าเช่น 000245 ขาดเลขศูนย์นำหน้า Power Query อาจอนุมานประเภทเป็นตัวเลขระหว่างขั้นตอนการเปลี่ยนประเภทอัตโนมัติ

The first Changed Type step is selected in the Power Query Editor Applied Steps pane.
The first Changed Type step is selected in the Power Query Editor Applied Steps pane.

วิธีแก้ไขปัญหานี้:

  • ในช่องขั้นตอนที่ใช้ ให้เลือกขั้นตอนประเภทที่เปลี่ยนแปลงขั้นตอนแรก
  • คลิกไอคอนประเภทข้อมูลในส่วนหัวคอลัมน์ที่เกี่ยวข้อง แล้วเลือก ข้อความ
    The ZIP Code column in Power Query Editor is changed to Text.
    The ZIP Code column in Power Query Editor is changed to Text.
  • หากมีข้อความแจ้ง ให้คลิกแทรกเพื่อเพิ่มขั้นตอนกลาง
    Insert is selected in Power Query's Insert Step warning box.
    Insert is selected in Power Query's Insert Step warning box.
  • สุดท้าย คลิก "แทนที่ขั้นตอนปัจจุบัน" เพื่ออัปเดตขั้นตอนที่มีอยู่แทนที่จะสร้างขั้นตอนใหม่
    Replace Current is selected in Power Query's Change Column Type warning box.
    Replace Current is selected in Power Query's Change Column Type warning box.

ตอนนี้เลขศูนย์นำหน้าจะถูกเก็บรักษาไว้อย่างถูกต้องแล้วเมื่อโหลดข้อมูลลงใน Excel

Two columns in Power Query Editor have their leading zeros preserved due to them being converted to text.
Two columns in Power Query Editor have their leading zeros preserved due to them being converted to text.

ลบคอลัมน์ที่ไม่จำเป็นออก

เพื่อให้ชุดข้อมูลเน้นเฉพาะข้อมูลที่มีประโยชน์ เช่น ข้อมูลลูกค้า คำสั่งซื้อ และผลิตภัณฑ์:

  • กดปุ่ม Ctrl ค้างไว้ขณะเลือกคอลัมน์ Region และ Internal Notes
    Two columns are selected in Excel's Power Query Editor.
    Two columns are selected in Excel's Power Query Editor.
  • คลิกขวาที่คอลัมน์ที่เลือกไว้ แล้วเลือก ลบคอลัมน์
    Remove Columns is selected in the right-click menu in Power Query Editor.
    Remove Columns is selected in the right-click menu in Power Query Editor.

โหลดชุดข้อมูลที่ทำความสะอาดแล้ว

เมื่อคุณพอใจกับลักษณะของข้อมูลแล้ว ให้คลิก ปิดและโหลด ในแท็บหน้าแรก เพื่อโหลดไฟล์ Sales_Report_January.csv ที่ทำความสะอาดแล้วลงใน Excel ในรูปแบบตารางที่มีโครงสร้าง

The top half of the Close and Load button in Excel's Power Query Editor is selected to load a sales report back to the Excel grid.
The top half of the Close and Load button in Excel's Power Query Editor is selected to load a sales report back to the Excel grid.

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

A cleaned-up sales report, converted from CSV to Excel via Power Query.
A cleaned-up sales report, converted from CSV to Excel via Power Query.

เคล็ดลับ: รวมรายงาน CSV หลายฉบับเข้าไว้ในชีตเดียว

การทำให้การรวมข้อมูลซ้ำๆ เป็นไปโดยอัตโนมัติ

แทนที่จะทำขั้นตอนการล้างข้อมูลซ้ำๆ Power Query สามารถใช้ตรรกะการแปลงข้อมูลเดียวกันกับไฟล์ CSV หลายไฟล์ในโฟลเดอร์ได้

ส่วนนี้จะเริ่มต้นเวิร์กโฟลว์ใหม่โดยใช้ไฟล์ Sales_Report_January.csv และ Sales_Report_February.csv เดิม ดังนั้นโปรดตรวจสอบให้แน่ใจว่าได้บันทึกไฟล์ทั้งสองไว้ในโฟลเดอร์เดียวกันก่อนเริ่มต้น เนื่องจากนี่เป็นเวิร์กบุ๊กใหม่และแบบสอบถามใหม่ งานทำความสะอาดไฟล์เดียวที่คุณทำไว้ก่อนหน้านี้จะไม่ถูกถ่ายโอนมาด้วย

ขั้นแรก ให้ทำตามขั้นตอนต่อไปนี้เพื่อเชื่อมต่อ Excel กับโฟลเดอร์ที่มีรายงานของคุณ:

  • ในเวิร์กบุ๊ก Excel ใหม่ ให้คลิก ข้อมูล > รับข้อมูล > จากไฟล์ > จากโฟลเดอร์
    From Folder is selected in Excel's Get Data drop-down menu.
    From Folder is selected in Excel's Get Data drop-down menu.
  • เลือกโฟลเดอร์เฉพาะที่มีไฟล์ CSV ทั้งสองไฟล์อยู่
    A folder named Sales CSVs is selected in Excel's Browse window.
    A folder named Sales CSVs is selected in Excel's Browse window.
  • คลิกเปิด
    A folder names Sales CSVs is selected and Open is highlighted in Excel's Browse window.
    A folder names Sales CSVs is selected and Open is highlighted in Excel's Browse window.

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

ขั้นตอนต่อไป ให้กำหนดโครงสร้างข้อมูลที่รวมกันแล้ว:

  • คลิก รวม > รวมและแปลงข้อมูล
    Combine and Transform Data is selected in the Combine drop-down menu of Excel's From Folder dialog.
    Combine and Transform Data is selected in the Combine drop-down menu of Excel's From Folder dialog.
  • เลือกไฟล์ที่คุณต้องการใช้เป็นไฟล์ตัวอย่าง ซึ่งโดยปกติจะเป็นรายงานฉบับแรกหรือฉบับที่สมบูรณ์ที่สุด และตรวจสอบให้แน่ใจว่าได้เลือกตัวคั่นที่ถูกต้อง (โดยปกติคือเครื่องหมายจุลภาค) เพื่อให้คอลัมน์ต่างๆ แยกออกจากกันอย่างถูกต้อง
    A sample file and comma delimiter are selected in Excel's Combine Files dialog.
    A sample file and comma delimiter are selected in Excel's Combine Files dialog.
  • คลิก ตกลง
    OK is selected in Excel's Combine Files dialog.
    OK is selected in Excel's Combine Files dialog.
  • ใน Power Query Editor ให้ตรวจสอบข้อมูลที่รวมกันแล้ว และทำการล้างข้อมูลเพิ่มเติมหากจำเป็น
    Various steps are seen in the Applied Steps pane in the Power Query Editor.
    Various steps are seen in the Applied Steps pane in the Power Query Editor.
  • คลิก ปิดและโหลด ในแท็บ หน้าแรก เพื่อส่งรายงานที่รวมและทำความสะอาดแล้วไปยังเวิร์กชีต Excel ใหม่
    The top half of the split Close and Load button in Excel's Power Query Editor is selected to load a combined sales report back to the Excel grid.
    The top half of the split Close and Load button in Excel's Power Query Editor is selected to load a combined sales report back to the Excel grid.

จากตรงนี้ เมื่อใดก็ตามที่คุณลากไฟล์ใหม่ลงในโฟลเดอร์ ให้คลิก ข้อมูล > รีเฟรชทั้งหมด เพื่ออัปเดตการค้นหา

A clean, combined sales report, converted from CSV to Excel via Power Query.
A clean, combined sales report, converted from CSV to Excel via Power Query.
The top half of the split Refresh All button in Excel's Data tab is selected.
The top half of the split Refresh All button in Excel's Data tab is selected.

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

ข้อได้เปรียบของการวิเคราะห์อัตโนมัติ

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

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

Power Query ใน Excel คืออะไร?

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

ฉันจะป้องกันไม่ให้ Excel ลบเลขศูนย์นำหน้าในรหัสไปรษณีย์ได้อย่างไร?

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

Power Query สามารถอัปเดตข้อมูลโดยอัตโนมัติเมื่อข้อมูลในไฟล์ CSV ของฉันเปลี่ยนแปลงได้หรือไม่?

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

ฉันจะรวมไฟล์ CSV หลายไฟล์เข้าไว้ในตาราง Excel เดียวได้อย่างไร?

คุณสามารถรวมไฟล์ CSV หลายไฟล์เข้าด้วยกันได้โดยไปที่ ข้อมูล > รับข้อมูล > จากไฟล์ > จากโฟลเดอร์ เลือกโฟลเดอร์เป้าหมาย และเลือก รวมและแปลงข้อมูล Power Query จะเรียงไฟล์เข้าด้วยกันตามรูปแบบตัวอย่าง

ฉันควรทำอย่างไรหาก Power Query ส่งค่าข้อผิดพลาดกลับมาในคอลัมน์วันที่?

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

ไฟล์ CSV ต้นฉบับของฉันจะเป็นอย่างไรเมื่อฉันใช้ Power Query?

ไฟล์ CSV ต้นฉบับของคุณยังคงไม่เปลี่ยนแปลง Power Query ทำงานโดยใช้สำเนาที่นำเข้าและการแปลงชั่วคราวภายในสภาพแวดล้อมของเวิร์กบุ๊ก Excel เท่านั้น

ฉันจะจัดการกับไฟล์ CSV ที่มีจำนวนแถวเกินขีดจำกัดของ Excel ได้อย่างไร?

หากไฟล์ CSV ของคุณมีจำนวนแถวนับล้านแถวซึ่งเกินขีดจำกัดของเวิร์กชีตมาตรฐาน คุณสามารถโหลดข้อมูลลงใน Data Model ของ Excel ได้โดยตรงแทนที่จะใช้ชีตปกติ ซึ่งจะช่วยให้คุณสร้าง PivotTable จากชุดข้อมูลขนาดใหญ่ได้

Power Query สำหรับ Excel: ทำความสะอาดและจัดการไฟล์ CSV ที่ยุ่งเหยิงโดยอัตโนมัติได้ทันที | WukiHow