NokKrob นกกรอบ

Working with TEXT in Google Sheet

ปกติใน Google Sheet เรามีข้อมูลเป็นจำนวนมากไม่ว่าจะเป็นข้อมูลที่เป็น ข้อความ ตัวเลข หรือว่าวันที่ Google Sheet ก็มี Function ที่สามารถจัดการข้อความที่ผิดพลาดหรือไม่ถูกต้องได้ เช่น

LEFT, RIGHT, MID

Function LEFT, RIGHT, MID จะใช้ในการกรองข้อความโดยเริ่มจากทางซ้าย (LEFT) ทางขวา (RIGHT) ตรงกลาง (MID) จะเป็นประโยชน์มากในเวลาที่เราต้องการจะ Clean ข้อมูลของเรา
Example : มีรหัสลูกค้า เช่น ABC-1234-634236 เราอยากจะได้ 3 ตัวแรกของรหัส ใช้ function LEFT =LEFT(A1, 3)

ประยุกต์ใช้ LEFT ร่วมกับ FIND เพื่อหาตำแหน่งของตัวอักษรในที่นี้คือ ” ”

LEN, CONCATENATE, TRIM

Function LEN ใช้เมื่อเราต้องการให้ function return จำนวนตัวอักษรที่มีในเซลล์นั้นๆ ประยุกต์ใช้กับ Data Validation กำหนด Limit Character หรือหาค่า Error ที่มีจำนวนตัวอักษรต่ำหรือสูงกว่าที่เรากำหนด

Function CONCATNATE (or &) ใช้เพื่อเชื่อมข้อความให้เป็นข้อความเดียวกัน สามารถใช้เครื่องหมาย ampersand (&) ได้จะให้ผลลัพธ์เหมือนกับ CONCATNATE ตัว function นี้สามารถประยุกต์ใช้กับตัว Query Function ได้เพื่อให้สูตรของเราเป็น Dynamics Function

Function TRIM ใช้เพื่อจัดการพื้นที่ส่วนเกินที่เราไม่ต้องการที่คั่นตัว text ของเราอยู่

SUBSITITUDE, SPLIT

Function SUBSITITUDE จะแทนที่ข้อความเดิมด้วยข้อความอื่นแทนที่ได้หลายอย่างไม่ว่าจะเป็นเครื่องหมาย พื้นที่ว่าง ช่วยเป็นอย่างมากในการ Standardizing Data เช่น

Function SPLIT ใช้แยกคำต่างๆออกมาโดยใช้ delimiter เป็นตัวแบ่ง ” “, “/” สามารใช้ได้หลาย delimiter

UPPER, LOWER, PROPER

Function นี้ใช้เพื่อเปลี่ยนแบบตัวอักษรให้เป็นพิมพ์ใหญ่, พิมพ์เล็ก, เป็นตัวใหญ่แค่อักษรตัวแรก ตามลำดับ
Example : หากเซลล์ A1 มีข้อความ “hello world” ดังนั้น =UPPER(A1) จะส่งคืน “HELLO WORLD”
หากเซลล์ A1 มีข้อความ “HELLO WORLD” ดังนั้น =LOWER(A1) จะส่งคืน “hello world”
หากเซลล์ A1 มีข้อความ “HELLO WORLD” ดังนั้น =PROPER(A1) จะส่งคืน “Hello World”

Multiple Condition in Google Sheet

Function ที่มี s ต่อท้ายเราสามารถกำหนดตัว conditions ให้มันทำงานภายใต้เงื่อนไขต่างๆที่เรากำหนดขึ้นมาได้
ยกตัวอย่างฟังก์ชั่น IFS เราสามารถกรองข้อมูลได้มากกว่า 1 เงื่อนไข COUNTIFS นับจำนวนตามเงื่อนไขต่างๆที่เรากำหนดขึ้นได้ SUMIFS หาผลรวมตามเงื่อนไขต่างๆ ส่วนมาก Multiple Conditions IFs สามารถใช้ร่วมกับพวก Logical Operator (AND, OR, NOT) เพื่อเพิ่มเงื่อนไขที่ซับซ้อนมากขึ้นได้

Example :

นับจำนวน Transactions ที่มี Brand = Intel
=COUNTIF($C:$C, "Intel")

นับจำนวน Transactions ที่มี Brand = Intel และ Model = Core i5
=COUNTIFS($C:$C, "Intel", $D:$D, "Core i5")

นับจำนวน Transactions ที่มี Brand = Intel และ Quantity < 300
=COUNTIFS($C$2:$C$22, "Intel", $E$2:$E$22,"<300")

นับจำนวน Transactions ที่มี Brand = Intel ในวันที่ 2021-06-05
=COUNTIFS($C:$C, "Intel",$B:$B, "2021-06-05")

** สามารถใช้ได้หลายเงื่อนไขตามตัวอย่าง **

Sort & Filter Function

Filter เพื่อกรองหรือดึงเฉพาะข้อมูลที่เราต้องการมาทำงานด้วย สมมติลูกค้าที่ dtac มีทั้งหมด 20 ล้าน แต่โจทย์ของเราคือการวิเคราะห์ churn analysis ของ postpaid (ลูกค้าแบบรายเดือน) เวลาเราเขียน filter ก็จะหน้าตาประมาณ ( 1 )

ถ้าเกิดมีเงื่อนไขอื่นๆที่ต้องการเพิ่ม เช่น ลูกค้าต้องใช้ data มากกว่า 2GB ต่อเดือนด้วย เราสามารถเพิ่มเงื่อนไขในฟังก์ชันแบบ ( 2 )

หรือจะเขียนมากกว่าสองเงื่อนไขก็ได้ ยิ่งละเอียด เรายิ่งได้ data ที่ตรงกับโจทย์เรามากขึ้น ด้านล่างเราเพิ่มอีกหนึ่งเงื่อนไข คือต้องเปิดบริการก่อนวันที่ 1 ก.ค. 2022 ให้เราใช้ฟังก์ชัน DATE() เพื่อสร้าง date ได้เลย ( 3 )

churn คือศัพท์ทางการตลาด เป็นการวิเคราะห์ว่าลูกค้าจะใช้บริการเราต่อหรือเปล่า (yes, no) เวลาเราทำโมเดลวิเคราะห์ churn จะเรียกว่า binary classification model

( 1 ) =FILTER(customers, segment = "POSTPAID")
( 2 ) =FILTER(customers, segment = "POSTPAID", data_usage > 2)
( 3 ) =FILTER(customers, segment = "POSTPAID", data_usage > 2, registered_date < DATE(2022,7,1))

Tip – เวลาเขียน filter เราสามารถใช้ named range เพื่อตั้งชื่อ data table และ column ที่เราใช้บ่อยๆได้

** Filter หลายเงื่อนไขโดยการใช้ + แทน function AND จำเป็นที่จะต้องใส่วงเล็บลงในสูตรด้วย **

ในตัวอย่างข้างล่างคือ Filter Brand = AMD และ Model = Core i7

=FILTER(A3:E22,(C3:C22="AMD") + (D3:D22="Core i7"))

Sort ใช้เพื่อเรียงลำดับข้อมูลจาก A-Z หรือ น้อยไปมาก (Ascending Order) แทนค่าด้วย Parameter = True หรือ 1 / จาก Z-A หรือมากไปน้อย (Descending Order) แทนค่าด้วย Parameter = False หรือ 0

ตัวอย่างข้างล่างเป็นการเรียงตาม Columns ที่ 3 คือ Brand จาก A-Z และ Columns ที่ 5 คือ Quantity จากมากไปน้อย

=SORT(A3:E22, 3,true, 5,false)

👁️Lookup Function

VLOOKUP Function

Vlookup Function เป็น Function ที่จะะใช้เพื่อหาข้อมูลในแนวตั้ง (Vertical Lookup) ที่จะให้คุณหาข้อมูลใน Columns และส่งข้อมูลที่อยู่ใน Row เดียวกันแต่เป็น Columns อื่นส่วนใหญ่ใช้หาข้อมูลต่างๆที่ Releated กับข้อมูลที่เราจะหา

** VLOOKUP(ค่าที่ต้องการหา, ขอบเขตข้อมูลที่ต้องการเปรียบเทียบ, คอลัมธ์ที่ต้องการให้แสดงผล, ต้องการให้ข้อมูลตรงทุกตัวอักษร)

ในตัวอย่างข้างล่าง นี้ใช้ Vlookup เพื่อข้อมูลที่เกี่ยวข้องแบ่งตาม ID

=VLOOKUP(D3,PRODUCTS,2,False)
=VLOOKUP(D3,PRODUCTS,3,False)  

** ในที่นี้ใส่เป็น FALSE เพราะต้องการให้ตรงกันทุกตัว จะเรียกว่า Exact Match

อีก 1 ตัวอย่างของ Vlookup เช่น เราต้องการจะแยกประเภทของสิ่งต่างๆเดี๋ยว Range ของข้อมูลเช่นการตัดเกรดต่าง หรือจัดประเภทของ Range ราคา Chipset

=ArrayFormula(VLOOKUP(D3:D22,PRODUCTS,3,false))
=ArrayFormula(VLOOKUP(F3:F22, PRICE_BANDS , 2, True))

** ตัวนี้เป็น VLOOKUP ที่แบ่งตาม Range ของข้อมูลโดยจะนับข้อมูลที่ใกล้เคียงมากที่สุด ** ในที่นี้ใส่เป็น TRUE เพื่อให้เป็น Approximate Match

ข้อสำคัญของการใช้ Vlookup แบบ Approximate Match คือตัว Range ของข้อมูลเราจะ จำเป็น ที่จะต้อง Sort จากค่าน้อยไปค่ามาก (Ascending Order) เพราะ Function Vlookup จะกรองจากค่าบนสุดไล่ลงไปถึงล่างสุดเพื่อให้ Function หาอ่านค่าได้อย่างแม่นยำ

INDEX AND MATCH

Index Function เป็น Function ที่จะใช้หาข้อมูลในแนวตั้งและแนวนอนของ Range ที่เรากำหนด จะส่งคืนค่ากลับมาเป็นข้อมูลของช่องนั้นๆ

=INDEX(reference, [row], [column])

Match Function เป็น Function ที่จะหาค่าของข้อมูลที่เรากำหนดใน Range ที่เรากำหนด และจะส่งคืนค่าเป็นตำแหน่งของข้อมูลนั้นๆ

=MATCH(search_key, range, [search_type])

ตัวอย่างข้างล่างคือประยุกต์ใช้ Index และ Match นำตัว Dropdown List มาใช้ เพื่อทำการหาข้อมูลที่เป็น Dynamic Table นำ Match Function เข้าเพื่อให้ส่งคืนค่ากลับมาเป็นตำแหน่งของ Columns

Reference:
https://www.glideapps.com/blog/google-sheets-formulas