วิธีสร้าง Dynamic Search บน Google Sheet

หลาย ๆ คนคงเคยเจอปัญหาที่ต้องการค้นหาข้อมูลบน Google Sheet ที่มีจำนวนหลายคอลัมน์ แล้วก็ไม่รู้ว่าค่าที่เราต้องการจะอยู่ที่คอลัมน์ไหน

บางคนอาจจะอยากค้นหาข้อความบางอย่าง โดยตัดเงื่อนไขบางอย่างออกไปด้วยพร้อมๆกันในเวลาเดียวเลย คำถามคือ เราจะทำมันได้อย่างไร

ถ้าเป็นเมื่อก่อนคำตอบนี้อาจจะยากนิดนึง ผมใช้เวลาค่อนข้างนานในการทำงานที่มีความซับซ้อนสูง บางงานต้องไปอ่านที่ Stackoverflow หรือบางงานต้องมาหนังดูวิธีการผูกสูตรมากมายจาก Youtuber ชาวอินเดียผู้มอบวิธีคิดอันล้ำลึกมาให้ แต่สมัยนี้เราสามารถใช้เครื่องมือที่มีมากมาย เช่น ChatGPT , Gemini ในการช่วยไกด์คำตอบให้เราได้

บทความนี้ผมจะแนะนำทุกคนให้เข้าใจ ถึงวิธีการทำงานแบบง่าย ๆ เอง การนำ multiple text search โดยปกติจะใช้ในการ clean data , exploration data เป็นส่วนมาก แต่มันจะดีถ้าเนื้อหาเหล่านี้ช่วยลดเวลาการทำงานของพวกคุณได้ มาเริ่มกันเลย

1.การนำเข้าข้อมูล

หากเรามี account gmail อยู่แล้วเราสามารถเริ่มต้น ได้ด้วยการพิมพ์ คำสั่งนี้ในแถบเบราเซอร์เพิ่มสร้างชีทใหม่

sheet.new

จากนั้นให้นำข้อมูล imdb.csv สามารถโหลด dataset จาก kaggle อันนี้มาใช้ได้เหมือนกัน เว็บนี้ผมแนะนำ เป็นเว็บไซต์ที่มี dataset ให้เล่นมากมาย แล้วเรายังสามารถหา use case ได้มากมายครับ

Netflix Prize Shows Information (9000 Shows)

Scrapped IMDB data For Netfilx Prize Dataset Titles for Recommendation Systems.

Kaggle

เมื่อเราโหลดข้อมูลสำเร็จแล้วให้เราลบคอลัมน์แรกทิ้ง โดยให้ Column A ตรงกับ title ความสำคัญของการทำงานเกี่ยวกับข้อมูล คือตารางของเราจะต้องสมบูรณ์มีทั้งส่วนที่เป็นชื่อคอลัมน์และเนื้อหาของข้อมูล

เมื่อนำข้อมูลเข้าเรียบร้อยให้จัดการข้อมูล แล้วสร้างเป็น Table ขึ้นมา เพื่อความสะดวกในการเรียกใช้งานใน function จะเขียนขึ้นมาต่อไป ผมจะเปลี่ยนชื่อตารางเป็น IMDB ตามตัวอย่างที่ขึ้นในรูป

2.การเลือกวิธีค้นหาข้อมูล

สำหรับการเลือกวิธีการค้นหาข้อมูลครั้งนี้ ผมจะตั้งเงื่อนไขดังต่อไปนี้

1. สามารถค้นหาข้อมูลไม่ว่า คำที่เราค้นหาจะอยู่ส่วนใดก็ตามใน ตาราง
2. สามารถไฮไลท์สีได้ว่า คำที่เราค้นหา อยู่ในช่องไหน
3. สามารถใช้คำค้นหาได้มากกว่า 1 คำ
4. สามารถเลือกตัดคำค้นหาที่ไม่ต้องการได้ มากกว่า 1 คำ

2.1 Regular Expression

สำหรับการจัดการข้อมูลผมจะใช้ Regular Expression ในการจัดการ เนื่องจากมีความยืดหยุ่นในการใช้งานมากกว่ากรณีที่เราต้องการหา pattern matching และสามารถค้นหาได้มากกว่า 1 คำ ในการใส่ input ในช่องค้นหา ซึ่งเราจะให้ผู้ใช้งานกรอกในช่อง B2 สำหรับคำที่ต้องการค้นหา และ B3 สำหรับคำที่ไม่ต้องการให้อยู่ในผลลัพธ์การค้นหาครับ

REGEXREPLACE(B3, ",", "|")

ก่อนจะทำการค้นหา ผมจะตั้งเป้าหมายว่าเราจะให้ผู้ใช้งานค้นหาข้อมูลด้วยการพิมพ์ตัวอักษรแบบใดมาก็ได้ แต่เราจะแปลงให้เป็นพิมพ์เล็กทั้งหมด (กรณีพิมพ์มาเป็นภาษาอังกฤษ) โดยใช้วิธีการคั่นด้วยเครื่องหมาย , แล้วเราจะแปลงเป็น | เพื่อใช้คู่กับ Function REGEXMATCH ในอนาคตได้ ตอนนี้ช่องที่ให้ผู้ใช้งานกรอกคือ B2 และ B3 แต่ cell ที่เราจะเอาไปใช้สำหรับการค้นหาคือ C2 และ C3 คือหลังจากเปลี่ยนเครื่องหมายด้วย REGEXREPLACE แล้วนั่นเอง

ขออธิบายตรงนี้เพิ่มนิดนึงครับ สำหรับใครที่ไม่เคยใช้ REGEXMATCH ฟังก์ชั่นนี้เกิดมาเพื่อกู้โลกโดยแท้ สำหรับคนที่ต้องการค้นหา pattern matching หรือค้นหาคำหลายข้อความ แน่นอนว่าเรื่องของความเร็วอาจจะช้ากว่าการเลือกแบบ Filter Equal match แต่ใครจะสน ในเมื่อมันใช้งานง่ายกว่า และ Google Sheet เองก็เหมาะกับงานขนาดเล็ก - กลาง อยู่แล้ว แต่ข้อจำกัดของ Function นี้มันจะเป็น case sensitive แปลว่าตัวพิมพ์เล็ก หรือพิมพ์ใหญ่ ที่เราพิมพ์ไม่เหมือนกัน จะส่งผลต่อการค้นหาทำให้ค้นหาไม่เจอด้วยเช่นกัน

ทีนี้เรามาอธิบายการก่อน โดยทั่วไปแล้ว Function REGEXMATCH จะต้องการ Input สองตัว

1. ข้อความที่ต้องการค้นหา
2. แพทเทิร์นที่ต้องการค้นหา

=REGEXMATCH(A1,"Data")

2.2 สูตรการค้นหาด้วย REGEXMATCH + FILTER

=IFERROR(IF(len(C2)>=3,
   VSTACK(IMDB[#HEADERS],
     FILTER(IMDB,BYROW(IMDB,
     LAMBDA(row, OR(REGEXMATCH(lower(row), C2)))),
     IF(C3="", BYROW(IMDB, LAMBDA(r, OR(REGEXMATCH(lower(r), C3)))),NOT(BYROW(IMDB,LAMBDA(r,OR(REGEXMATCH(lower(r), C3))))))
    )
   )
,VSTACK(IMDB[#HEADERS],"")))

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

LAMBDA(row, OR(REGEXMATCH(lower(row), C2))))

 IF(C3="", BYROW(IMDB, LAMBDA(r, OR(REGEXMATCH(lower(r), C3)))),
     NOT(BYROW(IMDB,LAMBDA(r,OR(REGEXMATCH(lower(r), C3))))))

FILTER(IMDB , BYROW(IMDB) , [condition1] , [condition2],...)

VSTACK(IMDB[#HEADERS] , [Data])

IFERROR( do something , "")

IF(len(c2)>=3, [funciton IF TRUE] , [function IF FALSE])

2.3 ไฮไลท์ข้อความที่ตรงกับคำค้นหา

เมื่อเราใส่สูตรที่ช่อง A5 แล้วจะได้ผลลัพธ์การค้นหาตามที่เราต้องการเลย สังเกตว่าจะมี title ที่มีคำว่า star trek และ มี pirate ด้วย จริงๆ มันจะอยู่คอลัมน์ไหนก็ได้ เพราะสูตรเราจะหาทั้งหมด แต่เดี๋ยวก่อน แบบนี้มันก็มองยากใช่ไหม ผมว่าเราต้องทำ Conditional Formating ให้มันมองง่ายสักนิดนึง

ทีนี้เรามาลองทดสอบกันด้วยการใส่ exclude keyword ผมจะใส่คำว่า movie เนื่องจากไม่ต้องการให้ผลลัพธ์ที่เป็น movie แสดงออกมา

ทีนี้เหมือนจะดีแล้ว แต่สูตรที่เราวางไว้ก็เหมือนจะใช้ได้แค่กับ TABLE เดียวใช่ไหม มันดูไม่ยืดหยุ่นเอาซะเลย ใครจะทำ Copy สูตรยาวๆ ก็ทำไปครับ แต่สำหรับผมจะเอาไปเขียน Name Function คราวหน้าเวลาผม Import ข้อมูลมาจะได้ใช้ชีทนี้เป็นเครื่องมือซะเลย ลองมาดูก่อน

3.การสร้าง Function สำหรับใช้งาน

Named Functions เป็นการเขียน Function ของเราเพื่อให้สามารถกับงานที่ลักษณะแบบเดียวกัน โดยเราไม่จำเป็นจะต้อง copy สูตรยาว ๆ อย่างที่บอกไปด้านบน เราแค่ต้องคิดว่า function ของเราจะรับ argument แบบไหนบ้าง อย่างเคสนี้ เราจะรับแค่ หัวตาราง (headers) , ข้อมูลในตาราง (body) , คำค้นหาที่ต้องการ (include keyword) และ คำที่ไม่ต้องการให้อยู่ในผลลัพธ์ (exclude keyword)

=IFERROR(IF(len(include)>=3,
VSTACK(header,
FILTER(body,BYROW(body,
LAMBDA(row, OR(REGEXMATCH(lower(row), include)))),
IF(exclude="", BYROW(body, LAMBDA(r, OR(REGEXMATCH(lower(r), exclude)))),NOT(BYROW(body,LAMBDA(r,OR(REGEXMATCH(lower(r), exclude))))))
)),VSTACK(header,"")))

ขั้นตอนแรก ให้เราใส่ชื่อ Function Name ไปก่อน ในกรณีนี้ผมตั้งชื่อเป็น SEARCH_BY_KEYWORD และให้รายละเอียดเป็นการ "ค้นหา แบบหลายคอลัมน์" เพื่อบอกว่า ฟังก์ชั่นนี้เอาไว้ทำอะไร

หัวข้อ Argument placeholders จะหมายถึงข้อมูลเราต้อง Input เข้าไปเพื่อให้ Function สามารถทำงานได้ถูกต้อง แล้ว Copy สูตรด้านบนไปว่างต้อง Formula definition แล้วกด Next ได้เลย

เมื่อกรอกข้อมูลเสร็จเรียบร้อยแล้ว ให้เรากดปุ่ม Create จากนั้น Duplicated Sheet ออกมาอีกแถบนึง เพื่อทดสอบสูตร โดยผมจะตั้งชื่อว่า Used Function เพื่อทำการเรียกใช้งาน function ที่เราสร้างขึ้นมาแบบสั้นๆ

ผมลองเปลี่ยน Data ดูบ้าง โดยใช้ fixtures.csv

2025 Premier League: Stats, Matches, Salaries

Fixtures, Salaries, Standings, Stats, and More

Kaggle

ทีนี้ถ้าจะให้ Perfect ก็ไปปรับ Cell C2:C3 ให้มี text เป็นสีขาวแล้ว Protect Range C2:C3 และ A5:M เป็นต้นไป ก็จะดูไม่รกมากขึ้น แต่ในกรณีที่มีจำนวน Column มากกว่าเดิม อย่าลืมไปปรับช่วงของ Condition formatting และปรับให้เหมาะสมกับงานของตัวเองครับ

สามารถ Make a copy ได้ที่นี่ครับ สำหรับสายใช้งาน

IMDB Multiple Text Search

Google Docs