นี่คือฟีเจอร์ของ Excel ที่ฉันรอมานานถึง 10 ปี

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

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

การค้นหารูปแบบตอนนี้สะดวกขึ้นด้วย REGEXTEST

ฉันไม่จำเป็นต้องใช้ตรรกะซับซ้อนในการตรวจสอบข้อมูลอีกต่อไป

REGEXTEST ตรวจสอบว่าข้อความตรงกับรูปแบบที่กำหนดหรือไม่ มันจะส่งค่ากลับเป็น TRUE หรือ FALSE — ไม่มีมากหรือน้อยไปกว่านี้ ซึ่งทำให้มันเหมาะสำหรับการตรวจสอบความถูกต้องของข้อมูล การกรองข้อมูล และการจัดรูปแบบตามเงื่อนไข

ไวยากรณ์นั้นเข้าใจง่าย:

=REGEXTEST(text, pattern, [case_sensitivity])

• text: สตริงที่คุณต้องการทดสอบกับรูปแบบ
• รูปแบบ: รูปแบบนิพจน์ปกติที่ต้องการจับคู่.
• case_sensitivity: นี่คือตัวแปรทางเลือก ตั้งค่าเป็น 0 (ค่าเริ่มต้น) สำหรับการจับคู่ที่คำนึงถึงตัวพิมพ์ใหญ่-เล็ก หรือ 1 สำหรับการไม่คำนึงถึงตัวพิมพ์

ก่อน REGEXTEST การตรวจสอบว่ารหัสสินค้าตรงตามรูปแบบที่กำหนดหรือไม่นั้นหมายถึงการต่อฟังก์ชัน LEN, ISNUMBER และ MID เข้าด้วยกันจนแทบอ่านไม่ออก ตอนนี้สามารถทำได้ด้วยสูตรเดียว

ในสเปรดชีต คอลัมน์รหัสสินค้า (Product Code) มีรายการเช่น "PRD-12345-X," "ITEM-987," และ "A12B34" เพื่อตรวจสอบว่ารหัสใดมีตัวเลขต่อเนื่องอย่างน้อยสี่หลัก สูตรต่อไปนี้สามารถทำงานนี้ได้

=REGEXTEST(A2, "\d{4}")

รหัสเช่น "PRD-12345-X" จะคืนค่า TRUE ในขณะที่ "ITEM-987" จะคืนค่า FALSE เพราะว่า "987" มีเพียงสามหลัก เช่นเดียวกัน "A12B34" ไม่มีตัวเลขต่อเนื่องกันสี่หลัก จึงคืนค่า FALSE เช่นกัน

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

การดึงข้อมูลเฉพาะเป็นกระบวนการแบบขั้นตอนเดียวในที่สุด

REGEXEXTRACT คือเครื่องมือที่ Excel ขาดหายไป

REGEXEXTRACT ดึงข้อความที่ตรงกับรูปแบบจากสตริง แทนที่จะส่งค่ากลับเป็น TRUE หรือ FALSE มันจะให้เนื้อหาจริงที่ตรงกัน ซึ่งมีประโยชน์มากกว่าเมื่อคุณต้องการแยกข้อมูลที่สะอาดออกจากรายการที่ยุ่งเหยิง

ไวยากรณ์เป็นไปตามโครงสร้างเดียวกัน:

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

• text: สตริงที่มีข้อมูลที่คุณต้องการดึงออกมา
• รูปแบบ: นิพจน์ปกติที่ใช้กำหนดสิ่งที่จะจับ
• return_mode: นี่เป็นพารามิเตอร์ที่ไม่ต้องใส่ก็ได้ ตั้งค่าเป็น 0 (ค่าเริ่มต้น) เพื่อส่งคืนเฉพาะผลลัพธ์แรกที่ตรงกัน หรือ 1 เพื่อส่งคืนผลลัพธ์ทั้งหมดเป็นอาร์เรย์แบบกระจาย
• case_sensitivity: พารามิเตอร์เสริม; ใช้ 0 (ค่าปริยาย) สำหรับต้องแยกตัวพิมพ์ใหญ่เล็ก หรือ 1 สำหรับไม่ต้องแยกตัวพิมพ์ใหญ่เล็ก.

คอลัมน์อีเมลลูกค้าในสเปรดชีตเป็นตัวอย่างที่ชัดเจนว่าทำไมฟังก์ชันนี้ถึงสำคัญ รายการเช่น "ติดต่อ: jane.doe@email.com" และ "สั่งซื้อโดย john_smith@domain.org" ซ่อนที่อยู่อีเมลจริงไว้ในข้อความรอบ ๆ การดึงข้อมูลเหล่านี้ด้วยตนเองจะเป็นงานที่น่าเบื่อ

สูตรต่อไปนี้ดึงเฉพาะที่อยู่อีเมลออกมา:

=REGEXEXTRACT(B2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

สำหรับรายการแรก มันจะคืนค่า "jane.doe@email.com" — ไม่มีข้อความเพิ่มเติม ไม่มีการทำความสะอาดด้วยตนเอง

คอลัมน์คำอธิบายธุรกรรมแสดงการใช้งานที่เป็นประโยชน์อีกอย่าง รายการเช่น "ชำระเงิน 250 ดอลลาร์ได้รับแล้ว" และ "ชำระใบแจ้งหนี้ 1,200 ดอลลาร์แล้ว" มีจำนวนเงินดอลลาร์ฝังอยู่ในประโยค สูตรต่อไปนี้สามารถดึง "$250" และ "$1,200" ออกมาโดยตรงในคอลัมน์แยกต่างหาก

=REGEXEXTRACT(E2, "\$[\d,]+\.?\d*")

ฉันเคยทำสิ่งนี้โดยใช้การรวมกันของ FIND, MID, และ LEN — สูตรเหล่านี้ค่อนข้างซับซ้อนและพังได้ทันทีเมื่อรูปแบบต้นฉบับเปลี่ยนไปเล็กน้อย REGEXEXTRACT จัดการกับความแตกต่างได้อย่างราบรื่นเพราะรูปแบบสามารถปรับให้เข้ากับโครงสร้างที่ต่างกันได้

การแก้ไขอย่างแม่นยำทำได้เร็วขึ้นมากด้วย REGEXREPLACE

คุณสามารถสลับรูปแบบแทนที่จะเป็นแค่ตัวอักษรได้แล้ว

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

ไวยากรณ์นี้เพิ่มพารามิเตอร์อีกหนึ่งตัว:

=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

• ข้อความ: สตริงที่คุณต้องการแก้ไข
• รูปแบบ: นิพจน์ปกติที่ใช้ระบุว่าต้องแทนที่อะไร
• การแทนที่: ข้อความที่จะใช้แทนส่วนที่ตรงกันแต่ละส่วน ใช้กลุ่มจับ ($1, $2) เพื่อเก็บส่วนของต้นฉบับ
• occurrence: ไม่จำเป็น ระบุว่าต้องการแทนที่ผลการจับคู่ใด — 0 (ค่าเริ่มต้น) แทนที่ทุกผลการจับคู่, 1 แทนที่ผลการจับคู่แรก, 2 แทนที่ผลการจับคู่ที่สอง, และต่อไปตามลำดับ
• case_sensitivity: ทางเลือก ตั้งค่าเป็น 0 (ค่าเริ่มต้น) สำหรับตรวจสอบตัวพิมพ์เล็ก-ใหญ่ หรือ 1 สำหรับไม่ตรวจสอบตัวพิมพ์เล็ก-ใหญ่

คอลัมน์หมายเลขโทรศัพท์ในสเปรดชีตแสดงให้เห็นชัดเจนว่าฟังก์ชันนี้มีประโยชน์อย่างไร รายการต่าง ๆ ปรากฏในรูปแบบที่แตกต่างกันอย่างมาก: “(123) 456-7890,” “123-456-7890,” “+1 123 456 7890,” และ “123.456.7890.” การวิเคราะห์หรือการค้นหาข้อมูลกับข้อมูลที่ไม่สอดคล้องกันแบบนี้ถือเป็นการเชิญชวนให้เกิดข้อผิดพลาด.

สูตรต่อไปนี้จะลบทุกอย่างยกเว้นตัวเลข ทำให้ตัวแปรทุกแบบกลายเป็น "1234567890" ที่สะอาด

=REGEXREPLACE(D2, "[^\d]", "")

หากคุณต้องการผลลัพธ์ที่จัดรูปแบบแล้ว ให้ปรับทุกรายการให้อยู่ในรูปแบบ “(123) 456-7890” โดยใช้สูตรดังต่อไปนี้:

=REGEXREPLACE(REGEXREPLACE(D2, "[^\d]", ""), "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")

SUBSTITUTE สามารถจัดการเรื่องนี้ได้ถ้าคุณรู้ตัวคั่นทุกตัวล่วงหน้า — แต่คุณจะต้องมีฟังก์ชันซ้อนแยกกันสำหรับวงเล็บ ขีดกลาง จุด และช่องว่าง REGEXREPLACE จัดการทั้งหมดได้ในรูปแบบเดียวเลย

ฟังก์ชันเหล่านี้เชื่อมช่องว่างระหว่าง Excel กับการเขียนโค้ด

คุณไม่จำเป็นต้องเป็นโปรแกรมเมอร์ก็สามารถใช้เครื่องมือมืออาชีพได้

เป็นเวลาหลายปีที่ผู้ใช้ Excel ได้ยินเกี่ยวกับ regex แต่ไม่สามารถใช้งานได้หากไม่เปลี่ยนไปใช้ Python, VBA หรือ Power Query หากคุณต้องการจับคู่รูปแบบ คุณก็ต้องเรียนรู้ภาษาสคริปต์หรือสร้างสูตรซับซ้อนที่แทบจะใช้งานไม่ได้ ซึ่งอุปสรรคนี้ไม่อยู่แล้ว

ฟังก์ชันทั้งสามนี้นำแนวคิดหลักของการเขียนโปรแกรมมาสู่สภาพแวดล้อมของสเปรดชีตโดยตรง ไวยากรณ์คล้ายกับที่นักพัฒนาใช้ใน JavaScript, Python และภาษาอื่น ๆ ดังนั้นรูปแบบ regex ใด ๆ ที่คุณพบออนไลน์สามารถใช้ใน Excel ได้โดยไม่ต้องแก้ไขมาก คำตอบบน Stack Overflow ที่เคยต้องแปลงโค้ด ตอนนี้สามารถใช้ได้โดยตรง

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

เส้นโค้งการเรียนรู้นั้นก็ไม่ชันมาก รูปแบบพื้นฐานเช่น \d+ สำหรับตัวเลขหรือ [A-Za-z]+ สำหรับตัวอักษรสามารถจัดการงานทั่วไปได้ส่วนใหญ่ คุณไม่จำเป็นต้องเชี่ยวชาญทุกฟีเจอร์ของ regex — เพียงพอที่จะแก้ปัญหาที่อยู่ตรงหน้าคุณเท่านั้น การรู้วิธีใช้ Power Query สำหรับการแปลงข้อมูลยังคงมีประโยชน์สำหรับงานที่ซับซ้อนหลายขั้นตอน แต่การเปิดตัวแก้ไขแยกต่างหากสำหรับการจับคู่รูปแบบแบบง่ายมักจะรู้สึกเกินจำเป็น ตอนนี้คุณสามารถอยู่ในเวิร์กชีตและได้รับผลลัพธ์เดียวกันด้วยสูตรเพียงสูตรเดียว