คู่มือผู้เชี่ยวชาญ: Power Query ต่อ Google Sheets REGEX สำหรับการทำความสะอาดข้อมูลขั้นสูง

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

Power Query ของ Excel

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

ฟังก์ชัน REGEX ของ Google Sheets

ฟังก์ชัน REGEX ของ Google Sheets ใช้นิพจน์ปกติ (Regular Expressions) เพื่อค้นหารูปแบบภายในข้อมูลข้อความ ทำให้เหมาะสำหรับการแยกวิเคราะห์ จัดรูปแบบ หรือตรวจสอบความถูกต้องของรายการข้อความ

1. REGEXREPLACE: แทนที่ข้อความที่ตรงกับนิพจน์ปกติด้วยข้อความที่ระบุ
   • =REGEXREPLACE(text, regular_expression, replacement)
2. REGEXMATCH: ตรวจสอบว่าสตริงข้อความตรงกับนิพจน์ปกติที่ระบุหรือไม่
   • =REGEXMATCH(text, regular_expression)
3. REGEXEXTRACT: ดึงสตริงย่อยที่ตรงกันจากข้อความตามนิพจน์ปกติ
   • =REGEXEXTRACT(text, regular_expression)

ฟังก์ชันเหล่านี้มีประโยชน์สำหรับการทำความสะอาดข้อมูล การจดจำรูปแบบ และการจัดการข้อความแบบไดนามิก

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

การกำจัดตัวอักษรที่ไม่ต้องการ

ทำความสะอาดชุดข้อมูลหมายเลขโทรศัพท์ที่มีตัวอักษรที่ไม่ต้องการ เช่น วงเล็บ ขีด หรือช่องว่าง

Power Query

• เลือกช่วงข้อมูล
• ไปที่แท็บ ข้อมูล (Data) >> เลือก จากตาราง/ช่วง (From Table/Range)
• โปรแกรมแก้ไข Power Query จะปรากฏขึ้น เลือกคอลัมน์ที่ต้องการ เราเลือกคอลัมน์ หมายเลขโทรศัพท์ (Phone Number)
• ไปที่แท็บ แปลง (Transform) >> เลือก แทนที่ค่า (Replace Values)
• กล่องโต้ตอบ แทนที่ค่า จะปรากฏขึ้น:
  • ในกล่อง ค่าที่ต้องการค้นหา (Value to Find): ป้อนตัวอักษรที่ไม่ต้องการ ((, ), -, ) ทีละตัว
  • ในกล่อง แทนที่ด้วย (Replace with): ทิ้งว่างไว้ หรือใส่ค่าที่ต้องการแทนที่ได้
  • คลิก ตกลง (OK)

• เลือก ปิดและโหลด (Close & Load) เพื่อนำข้อมูลที่ทำความสะอาดแล้วกลับไปที่ Excel

ฟังก์ชัน REGEXREPLACE ของ Google Sheets

ลองทำความสะอาดข้อมูลพร้อมจัดรูปแบบใน Google Sheets โดยใช้ฟังก์ชัน REGEXREPLACE ใส่สูตรต่อไปนี้ในเซลล์ G2

=ARRAYFORMULA(IF(LEN(REGEXREPLACE(D2:D6, "[^0-9]", ""))=10, "(" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 1, 3) & ") " & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 4, 3) & "-" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 7, 4), "Invalid"))

สูตรนี้จะลบตัวอักษรที่ไม่ใช่ตัวเลขทั้งหมด จากนั้นตรวจสอบว่าหมายเลขที่ทำความสะอาดแล้วมี 10 หลักพอดีหรือไม่ หากถูกต้อง จะจัดรูปแบบเป็น (XXX) XXX-XXXX มิฉะนั้นจะคืนค่า "Invalid"

ฟังก์ชัน REGEXREPLACE ของ Google Sheets (กรณีทั่วไป)

คุณสามารถใช้สูตรต่อไปนี้เพื่อทำความสะอาดตัวอักษรพิเศษที่ไม่จำเป็น

สูตร:

=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")

รูปแบบ [^a-zA-Z0-9] นี้จะลบตัวอักษรใดๆ ที่ไม่ใช่ตัวอักษรหรือตัวเลข

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

การมาตรฐานการ์สข้อความ (Text Case)

ชุดข้อมูลอาจมีข้อความผสมกัน คุณสามารถแปลงรายการข้อความทั้งหมดเป็นตัวพิมพ์เล็กหรือตัวพิมพ์ใหญ่ตัวแรกของแต่ละคำ (Title Case)

Power Query

เปิด Power Query โดยเลือกช่วงข้อมูลและไปที่แท็บ ข้อมูล (Data) >> เลือก จากตาราง/ช่วง (From Table/Range)

• เลือกคอลัมน์ที่มีข้อความที่ต้องการแปลง
• ไปที่แท็บ แปลง (Transform) >> เลือก ตัวพิมพ์ใหญ่แต่ละคำ (Capitalize Each Word)

ฟังก์ชัน PROPER ของ Google Sheets

ใน Google Sheets ไม่มีวิธีทำให้ตัวพิมพ์ใหญ่แต่ละคำได้โดยใช้เพียง REGEX เพราะ REGEXREPLACE ไม่รองรับฟังก์ชัน UPPER และ LOWER โดยตรงในรูปแบบหรือการแทนที่ คุณสามารถใช้ฟังก์ชันภายนอก เช่น PROPER, UPPER หรือ LOWER

สูตรนี้จะทำให้แต่ละคำขึ้นต้นด้วยตัวพิมพ์ใหญ่ และตัวอักษรที่เหลือเป็นตัวพิมพ์เล็ก

การแยกข้อมูลด้วยตัวคั่น (Delimiter)

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

Power Query

เปิด Power Query โดยเลือกช่วงข้อมูลและไปที่แท็บ ข้อมูล (Data) >> เลือก จากตาราง/ช่วง (From Table/Range)

• เลือกคอลัมน์ที่มีชื่อ
• ไปที่แท็บ หน้าแรก (Home) >> จาก แยกคอลัมน์ (Split Column) > เลือก ตามตัวคั่น (By Delimiter)

• ในกล่องโต้ตอบ แยกคอลัมน์ตามตัวคั่น (Split Column by Delimiter):
  • ใน เลือกและป้อนตัวคั่น (Select and enter delimiter): เลือก ช่องว่าง (Space)
  • คลิก ตกลง (OK)

• เปลี่ยนชื่อคอลัมน์ที่ได้เป็น "ชื่อ (First Name)" และ "นามสกุล (Last Name)"

ฟังก์ชัน REGEXEXTRACT ของ Google Sheets

คุณสามารถใช้ฟังก์ชัน REGEXEXTRACT เพื่อแยกข้อมูลด้วยตัวคั่น ใส่สูตรต่อไปนี้ในเซลล์ที่เลือก

=REGEXEXTRACT(E9, "^([^ ]+) (.+)$")

สูตรนี้จะแยกนามสกุลและชื่อเป็นสองเซลล์ ใช้คอลัมน์เพิ่มเติมตามต้องการ

เมื่อไหร่ควรใช้ Power Query หรือ Google Sheets REGEX

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

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

สรุป

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

รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!