การจัดการข้อความบน Google Sheet

ข้อมูลที่เราใช้กันอยู่ทุกวันนี้ จริงๆ แล้วมีข้อมูลหลายรูปแบบ ทั้งที่เป็นตัวอักษร ตัวเลข เปอร์เซ็นต์ และวันที่ หลายคนใช้ความพยายามในการแก้ไขไฟล์ที่ยาก โดยอาศัยการพิมพ์ทีละบรรทัดมาก่อน จึงจะรู้ว่าทริคในการจัดการข้อมูลมีได้หลายรูปแบบมาก ๆ ผมจะยกตัวอย่างที่ละรูปแบบ โดยบทความนี้จะเป็นบทความแรกที่ใช้สอนในการจัดการข้อความบน Google Sheet ถ้าพร้อมแล้วเราไปเริ่มกันได้เลย

การปรับข้อความ

การเปลี่ยนแปลงรูปแบบวิธีการแสดงข้อความมีทั้งหมด 3 วิธี ดังนี้

• ปรับข้อความเป็น ตัวอักษรพิมพ์เล็ก ทั้งหมด ใช้คำสั่ง =LOWERCASE( Cell )
• ปรับข้อความเป็น ตัวอักษรพิมพ์ใหญ่ ทั้งหมด = UPPERCASE( Cell )
• ปรับข้อความเป็น ตัวอักษรพิมพ์ใหญ่ เฉพาะตัวแรกของคำเท่านั้น ใช้คำสั่ง PROPER ( Cell)

การเชื่อมข้อความ

วิธีการเชื่อมระหว่าง ข้อความ 2 ข้อความเราสามารถใช้คำสังง่ายๆ อย่างเช่น CONCATNATE() โดยที่ฟังก์ชั่นนี้จะทำหน้าที่ในการเชื่อมข้อความ 2 ข้อความเข้าหากัน ให้อยู่ภายในเซลเดียวกัน แต่ในกรณีที่เรามีการทำงานกับหลาย ๆ ข้อความ เราสามารถเลือกใช้วิธีอื่น ๆ ได้ เช่น การใช้เครื่องหมาย & ในการเชื่อมข้อความระหว่างข้อความ 2 ข้อความ หรือมากกว่านั้น

ข้อสังเกต การเชื่อมข้อความ

กรณีเราใช้ Concatnate เราจะสามารถใช้ข้อความมากกว่า 2 ข้อความได้ แต่จะไม่สามารถใช้ร่วมกับ Arrayformula ได้ กรณีที่เราต้องการใช้ข้อความเพียง 2 ข้อความ หรือ 2 เซลล์เท่านั้น เราสามารถใช้ Concat ในการสร้าง ArrayFormula ครอบสูตรได้ไว้ได้

แต่ในกรณีที่มีข้อความมากกว่า 2 ข้อความที่ต้องการเชื่อมกัน เราสามารถทำได้เช่นกัน โดยการใช้ function map + lambda ในการส่งตัวแปรข้อความที่กำหนดวนไปเรื่อย ๆ วิธีนี้จะให้ผลลัพธ์ที่ดีกว่าเขียน nested function ที่เขียนซ้อนกันไปเรื่อย ๆ

การเชื่อมคำโดยมีสัญลักษณ์คั่นกลาง

โดยปกติแล้ว ถ้าเรามีข้อความที่ต้องการให้อยู่ในเซลล์เดียวกัน โดยมีเครื่องหมายแบ่งชุดของข้อมูล อันดับแรกที่เรามักจะนึกถึงคือการเก็บข้อมูลแบบ Comma seperate value หรือที่เราเรียกว่าไฟล์ csv นั่นเอง สมมติว่าเรามี Cell ทั้งหมด 3 cell โดยที่ค่าที่อยู่ในแต่ละแถว อาจจะมีจำนวนไม่เท่ากัน บางแถวมี 1 columns บางแถวมี 2 และ 3 คอลัมน์

วิธีการเชื่อมข้อความด้วย concat , concatenate หรือ การใช้ & เชื่อมข้อความจะไม่ค่อยเหมาะสมสักเท่าไร เนื่องจากเราอาจจะต้องมีการจัดการค่าว่าง ไม่ให้แสดงผลของเครื่องหมายคั่นกลางหลังจากค่าว่างของเซลล์

วิธีที่แนะนำคือการใช้ฟังก์ชั่น 2 ตัวดังนี้

1. TEXTJOIN วิธีการใช้งานคือเราจะประกาศตัวแปรเครื่องหมายก่อน หลังจากนั้นถึงจะเลือก cell ที่ต้องการทีละ cell ไปใส่ใน argument ของฟังก์ชั่นตามที่ต้องการ
2. JOIN วิธีการใช้งานจะคล้าย ๆ กับ TEXTJOIN แต่เราจะใช้วิธีการป้อนข้อมูลของเซลล์ แบบในรูปแบบช่วง (range) แบบนี้จะเหมาะกับข้อมูลที่มีคอลัมน์ติดกันเท่านั้น

แน่นอนว่าทั้ง 2 วิธีไม่สามารถรับรองการใช้งานกับ Arrayformula แต่เราสามารถใช้ function map + lambda ควบคู่กันได้สำหรับการใช้งานทั่วไป

การตัดข้อความ

ตรงข้ามกับการเชื่อมข้อความนั้น การตัดข้อความก็เป็นทักษะที่เราจะต้องพบเจอบ่อย ๆ แต่ก่อนจะตัดข้อความ ให้เราลองนึกว่าการตัดข้อความที่จะเอาไปใช้ควรจะมีหลักการอย่างไร ถ้าเป็นการตัดข้อความที่มีเครื่องหมายคั่นปกติ ไม่ซับซ้อน เราสามารถเลือกใช้ Function Split พร้อมระบุ เครื่องหมายที่เป็นตัวคั่นข้อความ (Delimiter) เพื่อให้ระบุตำแหน่งได้ถูกต้อง

การตัดข้อความแบบพิเศษ

โดยทั่วไปเรามักจะพบเห็นการตัดชื่อนามสกุล แบบทั่วไป เครื่องการแบ่งข้อความออกด้วยเครื่องหมายเว้นวรรค แต่เน้นนอนว่าโดยทั่วไป ถ้าชื่อนามสกุลมีแค่ 2 ส่วน ทุกคนก็คงจะเลือกใช้วิธีการข้างบนได้ แต่ในกรณีที่เราต้องจัดการกับชื่อนามสกุลจริงๆ อาจจะไม่ได้เจอแบบนั้น

1. เครื่องหมายเว้นวรรคมีเกินหรือเปล่า
2. ชื่อนามสกุล ที่กรอกจะมีได้กี่ส่วน (ชื่อ ชื่อกลาง? นามสกุล?) ถ้ามีจะแบ่งยังไง

SPLIT First Last Name

Google Docs

การตรวจข้อความและดึงข้อความที่ต้องการ

แน่นอนว่าการทำงานกับข้อความ บางทีเราจะต้องการค้นหาแค่บางส่วนของข้อความว่ามันมีความเหมือนหรือแตกต่างกันยังไงบ้าง

ก่อนอื่น เรามาเข้าใจสิ่งที่เรียกว่า Regular Expression กันก่อน โดย Regular Expression เป็นวิธีที่ใช้หาข้อความที่มีรูปแบบคล้ายคลึงกัน โดยจะมี syntax เฉพาะในการเลือกรูปแบบ

นอกจากนี้ยังต้องอธิบายเพิ่มเติมว่า Google Sheet มี regular expression ทั้งหมด 3 ฟังก์ชั่นซึ่งจะใช้วิธีการแตกต่างกันออกไป

1. REGEXP_MATCH จะให้ผลลัพธ์เป็น TRUE , FALSE เมื่อเงื่อนไขใน syntax ตรงกับข้อความ หรือไม่
2. REGEXP_EXTRACT จะให้ผลลัพธ์ในการดึงข้อความที่ตรงกับ syntax กำหนดไว้
3. REGEXP_REPLACE จะให้ผลลัพธ์เป็นการแทนที่ข้อความที่ตรงกับ syntax ด้วยข้อความที่เรากำหนดไว้

ตัวอย่างที่ 1 การดึงเบอร์โทรศัพท์

การดึงเบอร์โทรศัพท์โดยใช้ Regular Expression เราจะใช้หลักการในการทำนาย รูปแบบของเบอร์โทรศัพท์ เช่น สามารถขึ้นต้นด้วย +66 หรือ 08x, 09x ได้โดยที่จะมีชุดตัวเลขทั้งหมด 10 ตัวหรือ 9 ตัวสำหรับกรณีเบอร์โทรศัพท์บ้าน / สำนักงาน

นอกจากนี้ยังสังเกตได้ว่า ชุดตัวเลขอาจจะมีรูปแบบที่เหมือนกัน เช่น มี - และ มีการแสดงผลในตำแหน่งที่แตกต่างกันของข้อความ Regular Expression ก็สามารถจัดการในส่วนนี้ได้ดีเช่นกัน

• เงื่อนไข สามารถเปลี่ยนแปลงได้ตามความต้องการของผู้ใช้งาน การใช้งาน Regular Expression จะช่วยลดเวลาในการจัดการเงื่อนไขที่ซับซ้อน ให้ยืดหยุ่นมากขึ้น

ตัวอย่างที่ 2 การดึงอีเมล

การดึงข้อมูลอีเมล จะทำได้โดยที่เราใช้รูปแบบของอีเมลในการค้นหาด้วย Regular Expression เช่น การที่มีเครื่องหมาย @ เป็นสัญลักษณ์ในการแบ่งชื่อ และโดเมนของผู้ใช้งาน และมีการกำกับว่าภายหลังโดเมนจะต้องมีเครื่องหมาย . ไม่ว่าจะใช้โดเมนรูปแบบใด ก็ตาม

ผมมีตัวอย่างการใช้ Regular Expression + Let + Map สำหรับการ label ข้อความให้อยู่ในหมวดหมู่ที่เราต้องการ

ขั้นตอนที่ 1 สร้างตาราง โดยมีชื่อประเภทที่ต้องการให้แบ่งข้อมูลอยู่ด้านบนสุด และ คำที่เกี่ยวข้องกับประเภทข้อมูลอยู่ด้านล่าง

ขั้นตอนที่ 2 สร้างตารางโดยใช้สูตร เพื่อเปลี่ยนรูปแบบข้อความ ให้สามารถใช้งานร่วมกับ Regular expression ได้

ขั้นตอนที่ 3 สร้างตารางที่มีข้อมูลจริง และใช้ LET + MAP + REGEXP ในการดึงข้อความที่เกี่ยวข้องออกมาให้อยู่ในรูปแบบของการ label ประเภทข้อความ

กรณีที่มีข้อความมากกว่า 1 ข้อความ เราจะทำการรวมแล้วคั่นด้วยเครื่องหมาย , เพื่อให้ผู้ใช้งานสามารถอ่านได้อย่างรวดเร็ว โดยไม่ต้องไปดูที่คอลัมน์อื่น ๆ

ตัวอย่างการจัดการข้อมูลทั้งหมด สามารถดูได้ที่ลิ้งค์นี้เลยครับ

Google Sheet Edit Text

Google Docs

ข้อสังเกต
1.วิธีนี้จะสามารถใช้งานได้ในกรณีที่ข้อมูลมีน้อยกว่า 5,000 แถว ถ้ามีข้อมูลเยอะกว่านั้นเราอาจจะทำการแบ่งตามชีทก่อน แล้วค่อยนำไปรวมกันภายหลัง

2.วิธีนี้จะเรียงลำดับการแมพ ตามข้อความแถวแรกที่เราใส่เข้าไป ไม่ใช่ข้อความที่ปรากฎในข้อความดิบ เช่น แถวที่ 2 เราจะเจอ ซิเมโอเน่ ที่เป็นชื่อ โค้ช ก่อน แต่ในเงื่อนไข เราใส่ หงส์ เข้าไปในประเภท ลิเวอร์พูล ก่อน คำตอบจะถูกเรียงลำดับตามประเภทที่เราใส่เข้าไปก่อนหลัง

3.คุณสามารถใช้วิธีที่ง่ายกว่านั้นได้ กรณีที่คุณมี workspace สำหรับ AI บน Google Sheet ด้วยการพิมพ์คำสั่ง =AI ( 'prompt' , Range of cell ) วิธีนี้จะช่วยให้คุณได้คำตอบที่กว้างมากขึ้น และอาจจะระบุคำที่เกี่ยวข้องได้ดีกว่าการมานั่งใส่ คำที่เกี่ยวข้องของแต่ละประเภทด้วยตนเอง

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