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

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

Power Query ของ Excel

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

ฟังก์ชัน REGEX ของ Google ชีต

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

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

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

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

การลบอักขระที่ไม่ต้องการ

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

พาวเวอร์แบบสอบถาม

• เลือกช่วงข้อมูล
• ไปที่ข้อมูล แท็บ>> เลือก จากตาราง/ช่วง .
• พาวเวอร์คิวรี ตัวแก้ไขจะปรากฏขึ้น เลือกคอลัมน์ เราเลือกหมายเลขโทรศัพท์ คอลัมน์
• ไปที่การเปลี่ยนแปลง แท็บ>> เลือก แทนที่ค่า .
• แทนที่ค่า กล่องโต้ตอบจะปรากฏขึ้น;
  • ในส่วน มูลค่าที่ต้องการค้นหา กล่อง:ป้อนอักขระที่ไม่ต้องการ ((, ), -, ) ทีละตัว
  • ในส่วน แทนที่ด้วย box:เว้นว่างไว้หรือหากคุณมีค่าที่จะแทนที่ด้วย คุณสามารถแทรกค่านั้นได้
  • คลิก ตกลง .

• เลือก ปิดและโหลด เพื่อนำข้อมูลที่ล้างสะอาดแล้วกลับมายัง Excel

ฟังก์ชัน REGEXREPLACE ของ Google ชีต

มาล้างข้อมูลพร้อมกับการจัดรูปแบบใน Google ชีตโดยใช้ฟังก์ชัน REGREPLACE แทรกสูตรต่อไปนี้ในเซลล์ 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; มิฉะนั้นจะส่งคืน "ไม่ถูกต้อง"

ฟังก์ชัน REGREPLACE ของ Google ชีต

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

สูตร:

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

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

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

การกำหนดตัวพิมพ์ข้อความให้เป็นมาตรฐาน

ชุดข้อมูลอาจมีข้อความผสม คุณสามารถแปลงรายการข้อความทั้งหมดเป็นตัวพิมพ์เล็กหรือตัวพิมพ์เล็กได้

พาวเวอร์แบบสอบถาม

เปิด Power Query โดยเลือกช่วงข้อมูลและนำทางไปยัง ข้อมูล แท็บ>> เลือก จากตาราง/ช่วง .

• เลือกคอลัมน์ที่มีข้อความที่จะแปลง
• ไปที่การเปลี่ยนแปลง แท็บ>> เลือก ทำให้แต่ละคำเป็นตัวพิมพ์ใหญ่ .

ฟังก์ชันที่เหมาะสมของ Google ชีต

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

สูตรนี้ช่วยให้แน่ใจว่าแต่ละคำเป็นตัวพิมพ์ใหญ่ และตัวอักษรที่ไม่ใช่ตัวแรกจะถูกแปลงเป็นตัวพิมพ์เล็ก

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

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

พาวเวอร์แบบสอบถาม

เปิด Power Query โดยเลือกช่วงข้อมูลและนำทางไปยัง ข้อมูล แท็บ>> เลือก จากตาราง/ช่วง .

• เลือกคอลัมน์ที่มีชื่อ
• ไปที่ หน้าแรก แท็บ>> จาก แยกคอลัมน์> เลือก ตามตัวคั่น .

• ใน แบ่งคอลัมน์ตามตัวคั่น กล่องโต้ตอบ;
  • ใน เลือกและป้อนตัวคั่น: เลือก ช่องว่าง( ) .
  • คลิก ตกลง .

• เปลี่ยนชื่อคอลัมน์ผลลัพธ์เป็น "ชื่อ" และ "นามสกุล"

ฟังก์ชัน REGEXEXTRACT ของ Google ชีต

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

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

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

เมื่อใดควรใช้ Power Query กับ Google ชีต REGEX

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

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

บทสรุป

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