Tag: REGEXEXTRACT

Google Sheets (หรือบางครั้งเรียกสั้น ๆ ว่า Sheets) เป็นเครื่องมือ spreadsheet ออนไลน์ สำหรับทำงานกับข้อมูลในรูปแบบตาราง (tabular data)

Google Sheets มีการทำงานเหมือนกับ Excel แต่มีจุดเด่น คือ:

• ใช้งานฟรี
• เข้าถึงจากที่ได้ก็ได้
• ใช้ทำงานร่วมกับคนอื่นแบบ real-time ได้
• รองรับข้อมูลจำนวนมากได้ (แม้อาจจะ lag บ้างก็ตาม)

ด้วยเหตุนี้ Google Sheets จึงได้รับความนิยมในกลุ่มคนทำงาน โดยเฉพะาะกับคนที่ใช้ Google Workspace ในการทำงาน

.

ในบทความนี้ เราจะมาทำความรู้จักกับ 7 กลุ่มสูตร Google Sheets ที่มักใช้ในการทำงาน data:

1. Filtering and sorting: กรองและจัดเรียงข้อมูล
2. Aggregating: สรุปข้อมูล
3. Searching: เรียกดูข้อมูล
4. Conditions: สร้างข้อมูลใหม่ด้วยเงื่อนไข
5. Working with dates: สูตรทำงานกับวันที่ (date)
6. Working with text: สูตรทำงานกับข้อความ (text)
7. Google: สูตรเฉพาะของ Google

.

ถ้าพร้อมแล้ว มาเริ่มกันเลย

1. 💳 Dataset ตัวอย่าง: Financial Transactions Dataset
2. 🏷️ Named Ranges
3. 🤔 Syntax Help
4. 🧑‍💼 Group #1 – Filtering & Sorting
   1. (1) FILTER()
   2. (2) SORT()
5. 🧑‍💼 Group #2 – Aggregating
6. 🧑‍💼 Group #3 – Searching
   1. (1) VLOOKUP()
   2. (2) INDEX()
   3. (3) MATCH()
   4. (4) QUERY()
7. 🧑‍💼 Group #4 – Conditions
   1. (1) IF()
   2. (2) IFS
   3. (3) IFERROR()
8. 🧑‍💼 Group #5 – Working With Date
   1. (1) TODAY()
   2. (2) DATEDIF()
   3. (3) NETWORKDAYS()
9. 🧑‍💼 Group #6 – Working With Text
   1. (1) Splitting Text
   2. (2) Joining Text
   3. (3) Extracting Text
   4. (3) Regular Expression
10. 🧑‍💼 Group #7 – Google
    1. (1) GOOGLEFINANCE()
    2. (2) GOOGLETRANSLATE()
11. 💪 Recap

💳 Dataset ตัวอย่าง: Financial Transactions Dataset

มาดู dataset ที่เราจะใช้เป็นตัวอย่างกัน: Financial Transactions Dataset

Financial Transactions Dataset เป็นข้อมูลสังเคราะห์ เลียนแบบข้อมูลทางธุรกรรมของสถาบันทางการเงิน

Dataset ประกอบด้วย 6 columns ได้แก่:

No.	Column	Description
1	transaction_id	รหัสการทำธุรกรรม
2	date	วันที่
3	customer_id	รหัสลูกค้า
4	amount	จำนวนเงิน
5	type	ประเภททางธุรกิจ เช่น credit, debit, transfer
6	description	คำอธิบายการทำธุรกรรม

สำหรับบทความนี้ เราจะใช้ข้อมูลแค่ 1,000 rows แรก เพื่อลดโหลดของ Google Sheets

โดยเราจะเก็บข้อมูลนี้ไว้ใน Sheet ชื่อ Data:

Note: สำหรับคนที่สนใจ สามารถดูตัวอย่างข้อมูลและสูตรได้ที่ Google Sheets

🏷️ Named Ranges

ก่อนไปดูการใช้งานสูตร Google Sheets เรามาทำความรู้จักกับ Named Ranges กันก่อน

Named Ranges เป็น function ใน Google Sheets ที่ใช้ตั้งชื่อ (ติด tag) ข้อมูล เพื่อให้ง่ายต่อการทำงาน

อย่างในกรณีของ Financial Transactions Dataset เราจะตั้งชื่อข้อมูลว่า transactions:

ข้อดีของการใช้ Named Ranges คือ:

เมื่อเราเรียกใช้สูตร เราสามารถใช้ชื่อที่เราตั้ง แทนช่วงข้อมูลได้เลย

เช่น:

• แทนที่จะเขียนว่า A1:F1001 หรือ Data!A1:F1001 ทุกครั้ง
• เราสามารถเขียน transactions แทนได้

วิธีใช้งาน:

1. เลือกชุดข้อมูลที่ต้องการ (ไม่รวม column headers)

2. ไปที่เมนู “Data” และ “Named Ranges”

3. ในแถบเมนูด้านขวามือ ตั้งชื่อชุดข้อมูล (เช่น transactions)

4. กด “Done” เพื่อบันทึก

🤔 Syntax Help

Google Sheets มีตัวช่วยในการเขียนสูตร

ทุกครั้งที่เราพิมพ์สูตร Google Sheets จะแสดงคู่มือการใช้งานขึ้นมา

เช่น:

เราสามารถเรียนวิธีการเขียนสูตรได้ จากเอกสารนี้ โดยไม่ต้องออกจาก Google Sheets เลย

🧑‍💼 Group #1 – Filtering & Sorting

เรามาเริ่มดูกลุ่มแรกของสูตร Google Sheets กัน

ในกลุ่มนี้ เราจะมาดู 2 สูตรสำหรับกรองและจัดเรียงข้อมูล:

1. FILTER()
2. SORT()

.

(1) FILTER()

Usage:

กรองข้อมูล

Syntax:

=FILTER(range, condition)

• range คือ ชุดข้อมูลต้นทาง
• condition คือ เงื่อนไขในการกรอง ซึ่งเราสามารถใส่ได้มากกว่า 1 เงื่อนไข

Example:

แสดงข้อมูลที่จำนวนเงินทางธุรกรรม มากกว่า 3,000:

=FILTER(transactions, Data!D2:D > 3000)

ผลลัพธ์:

เราจะได้ข้อมูลทั้งหมดที่มี amount มากกว่า 3,000 สังเกตได้จาก column D (highlight สีเขียว):

.

(2) SORT()

Usage:

เรียงลำดับข้อมูล

Syntax:

=SORT(range, sort_column, is_ascending)

• range คือ ชุดข้อมูลต้นทาง
• sort_column คือ column ที่ใช้ในการ sort
• is_ascending คือ ระบุว่า จัดเรียงแบบ ascending (A-Z) หรือ descending (Z-A):
  • เติม TRUE เพื่อ sort แบบ ascending
  • เติม FALSE เพื่อ sort แบบ descending

Example:

เรียงข้อมูลตามจำนวนเงิน จากมากไปน้อย:

=SORT(transactions, 4, FALSE)

ผลลัพธ์:

เราจะได้ข้อมูลทั้งหมดโดยเรียงตาม amount จากมากไปน้อย (column D, highlight สีเขียว):

Note:

เราสามารถใช้ FILTER() คู่กับ SORT() ได้

เช่น แสดงข้อมูลที่มีจำนวนเงินมากกว่า 5,000 โดยเรียงจากน้อยไปมาก:

=SORT(FILTER(transactions, Data!D2:D > 3000), 4, TRUE)

ผลลัพธ์:

เราจะได้ข้อมูลที่ amount มากกว่า 3,000 จัดเรียงจากน้อยที่สุดไปมากที่สุด (column D, highlight สีเขียว):

🧑‍💼 Group #2 – Aggregating

ในกลุ่มนี้ที่ 2 เรามาดูสูตรในการสรุปข้อมูล (aggregate) ที่มักใช้กัน:

Formula	Description
COUNTA()	นับจำนวนข้อมูล
SUM()	หาผลรวม
AVERAGE()	หาค่าเฉลี่ย (mean)
MEDIAN()	หาค่ากลาง
MODE()	หา value ที่ซ้ำเยอะที่สุด
MIN()	หา value ที่น้อยที่สุด
MAX()	หา value ที่มากที่สุด
QUARTILE()	หา quantile
STDEV()	หา standard deviation (SD)
VAR()	หา variance

ตัวอย่าง:

หาค่าสถิติของจำนวนเงินทางธุรกรรมทั้งหมด:

Note:

• เราจะเห็นว่า MODE() (row 9) แสดง error เนื่องจากไม่มีข้อมูลซ้ำมากกว่า 1 ครั้ง
• เดี๋ยวเราจะดูวิธีจัดการค่า error ในกลุ่มที่ 7 กัน

🧑‍💼 Group #3 – Searching

ในกลุ่มที่ 3 เรามาดู 4 สูตรสำหรับค้นหาข้อมูลกัน:

1. VLOOKUP()
2. INDEX()
3. MATCH()
4. QUERY()

.

(1) VLOOKUP()

Usage:

VLOOKUP ย่อมาจาก:

Vertical Lookup

ใช้ดึงข้อมูลที่อยู่ row เดียวกับ search key (คำค้นหา)

Syntax:

=VLOOKUP(search_key, range, index)

• search_key คือ value ที่เราใช้ค้นหา
• range คือ ชุดข้อมูลที่เราต้องการเข้าไปดึงข้อมูลมา
• index คือ column ใน range ที่เราต้องการไปดึงข้อมูลมา

Example:

สมมุติว่า เรามีรหัสการทำธุรกรรม 10 ตัว และเราอยากรู้ว่า:

• แต่ละรหัสเป็นธุรกรรมประเภทไหน
• มีจำนวนเงินเท่าไร

เราสามารถเขียนสูตรได้แบบนี้:

=ArrayFormula(VLOOKUP(A3:A12, transactions, 5))

เพื่อดึงข้อมูลประเภทธุรกรรมที่อยู่ใน column ที่ 5 จากรหัสการทำธุรกรรม

และ:

=ArrayFormula(VLOOKUP(A3:A12, transactions, 4))

เพื่อดึงข้อมูลจำนวนเงินที่อยู่ใน column ที่ 4 จากรหัสการทำธุรกรรม

ผลลัพธ์:

Note:

จากตัวอย่าง จะสังเกตเห็นว่า เราใช้ ArrayFormula ช่วยในการดึงข้อมูลทั้งชุดมาแสดง ด้วยการเขียนสูตรเพียงครั้งเดียว

การใช้ ArrayFormula มีข้อดี 2 อย่าง:

1. ประหยัดเวลาในการทำงาน
2. ช่วยในลดโหลดการทำงาน ทำให้ Google Sheets ทำงานได้เร็วขึ้น เนื่องจากลดการประมวลผลจากหลายสูตร เหลือสูตรเดียว

ทั้งนี้ เราสามารถเขียนสูตรให้ Google Sheets ทำงานเร็วขึ้นอีก ด้วยการดึงข้อมูลจาก 2 columns มาแสดงในสูตรเดียว:

=ArrayFormula(VLOOKUP(F3:F12, transactions, {5, 4}))

จะเห็นว่า เราใส่ {5, 4} แทน 5 หรือ 4 อย่างเดียว

ผลลัพธ์:

จะเห็นว่า ผลลัพธ์ของสูตรนี้เหมือนกับผลลัพธ์ของสูตรก่อนหน้า

.

(2) INDEX()

Usage:

แสดงข้อมูลจาก cell ที่ตรงกับ index ที่เรากำหนด

Syntax:

=INDEX(reference, row, column)

• reference คือ ชุดข้อมูลที่เราต้องการเข้าไปดึงข้อมูล
• row คือ เลข index ของ row
• column คือ เลข index ของ column

Example:

เราต้องการแสดงข้อมูลที่อยู่ใน:

• row ที่ 10 (transaction_id ที่ 10)
• column ที่ 6 (description)

เราสามารถเขียนสูตรได้ดังนี้:

=INDEX(transactions, 10, 6)

ผลลัพธ์:

Google Sheets จะแสดงคำว่า “Old see watch no.” ขึ้นมา

.

(3) MATCH()

Usage:

ระบุตำแหน่งของ value ที่เราต้องการค้นหา

Syntax:

=MATCH(search_key, range, search_type)

• search_key คือ value ที่เราใช้ค้นหา
• range คือ ชุดข้อมูลที่เราต้องการเข้าไปดึงข้อมูลมา
• search_type (optional) คือ กำหนดว่า เราต้องการค้นหาแบบตรงตัว หรือใกล้เคียง:
  • 0 คือ ตรงตัว
  • 1 คือ ใกล้เคียง

Example:

จากตัวอย่างของ INDEX() แทนที่จะดูว่า มีข้อมูลอะไรอยู่ใน row ที่ 10 และ column ที่ 6

เราถามคำถามกลับกัน คือ:

“Old see watch no.” อยู่ในตำแหน่งไหนของ column F

=MATCH("Old see watch no.", Data!F2:F, 0)

ผลลัพธ์:

Google Sheets จะแสดงเลข 10

ซึ่งหมายถึง “Old see watch no.” อยู่ในลำดับที่ 10 ของ column

Note:

เราสามารถใช้ INDEX() และ MATCH() เพื่อทำงานคล้ายกับ VLOOKUP() ได้

เช่น เราต้องการว่า จำนวนเงินของธุรกรรมที่เขียนว่า “Old see watch no.” มีจำนวนเท่าไร:

=INDEX(Data!D2:D, MATCH("Old see watch no.", Data!F2:F, 0))

ผลลัพธ์:

เราจะได้คำตอบที่ต้องการ: 1,008.62

.

(4) QUERY()

Usage:

QUERY() เป็นสูตรเพื่อดึงข้อมูลมาแสดงได้ในรูปแบบที่ต้องการ

Syntax:

=QUERY(data, query)

• data คือ ชุดข้อมูลต้นทางที่เราต้องการดึงข้อมูลมา
• query คือ การเขียนเงื่อนไขในการดึงข้อมูล ตาม syntax ของ SQL

Example #1:

เราต้องการดึงข้อมูลทั้งหมด จาก transactions มาแสดง:

=QUERY(transactions, "SELECT *")

ผลลัพธ์:

เราจะได้ข้อมูลทั้ง 1,000 rows และ 6 columns มาแสดง

.

Example #2:

เราสามารถตีกรอบข้อมูลลง โดย:

• ระบุเฉพาะ column ที่ต้องการ
• จำกัดจำนวน rows ที่ดึงมาแสดง

เช่น เลือกเฉพาะ รหัสลูกค้า และ จำนวนเงิน 10 ชุดแรกมาแสดง:

=QUERY(transactions, "SELECT C, D LIMIT 10")

ผลลัพธ์:

.

Example #3:

เราสามารถเขียน query เพื่อตอบโจทย์ที่ซับซ้อนขึ้นได้

เช่น แสดงรหัสลูกค้า 10 คนแรกที่มีจำนวนเงินทางธุรกรรมมากกว่า 5,000 ขึ้นไป พร้อมวันที่:

=QUERY(transactions, "SELECT C, D, B WHERE D >= 5000 ORDER BY D DESC LIMIT 10")

ผลลัพธ์:

Note: สำหรับใครที่สนใจวิธีเขียน query สามารถเรียนรู้เกี่ยวกับ SQL เบื้องต้นได้ที่ SQL Crash Course จาก DataRockie

🧑‍💼 Group #4 – Conditions

ในกลุ่มที่ 4 เรามาดู 4 สูตรสำหรับสร้างข้อมูลใหม่ตามเงื่อนไขกัน:

1. IF()
2. IFS()
3. IFERROR()

.

(1) IF()

Usage:

แสดงข้อมูลตามเงื่อนไขที่กำหนด (1 เงื่อนไข)

Syntax:

=IF(logical_expression, value_if_true, value_if_false)

• logical_expression คือ เงื่อนไขที่เรากำหนด
• value_if_true คือ สิ่งที่จะแสดง ถ้าข้อมูลตรงเงื่อนไข
• value_if_false คือ สิ่งที่จะแสดง ถ้าข้อมูลไม่ตรงเงื่อนไข

Example #1:

ต้องการจัดกลุ่มจำนวนเงิน โดย:

กลุ่ม	จำนวนเงิน
Large	ตั้งแต่ 5,000 ขึ้นไป
Small	น้อยกว่า 5,000

=ArrayFormula(IF(A3:A>=5000, "Large", "Small"))

ผลลัพธ์:

.

Example #2:

เราสามารถเขียน IF() ซ้อนกันไปเรื่อย ๆ (nested IFs) เพื่อเพิ่มจำนวนเงื่อนไขได้

เช่น แบ่งจำนวนเงินเป็น 3 กลุ่ม แทน 2 กลุ่ม:

กลุ่ม	จำนวนเงิน
Large	ตั้งแต่ 5,000 ขึ้นไป
Mid	ตั้งแต่ 2,500 แต่น้อยกว่า 5,000
Small	น้อยกว่า 2,500

=ArrayFormula(IF(A3:A>=5000, "Large", IF(A3:A>=2500, "Mid", "Low")))

ผลลัพธ์:

.

(2) IFS

Usage:

• แสดงข้อมูลตามเงื่อนไขที่กำหนด (มากกว่า 1 เงื่อนไข)
• มีค่าในการใช้งานเท่ากับการเขียน IF() แบบซ้อนกัน
• แต่มีข้อแตกต่างที่เขียนเงื่อนไขได้ง่ายกว่า

Example:

ต้องการแบ่งจำนวนเงินเป็น 3 กลุ่ม ดังนี้:

กลุ่ม	จำนวนเงิน
Large	ตั้งแต่ 5,000 ขึ้นไป
Mid	ตั้งแต่ 2,500 แต่น้อยกว่า 5,000
Small	น้อยกว่า 2,500

แทนที่จะเขียน IF() ซ้อน ๆ กัน เราสามารถใช้ IFS() ได้แบบนี้:

=ArrayFormula(IFS(A3:A>=5000, "Large", A3:A>=2500, "Mid", A3:A<2500, "Low"))

ผลลัพธ์:

จะสังเกตได้ว่า ผลลัพธ์ที่ได้เป็นอันเดียวกับ IF() ที่เขียนซ้อนกัน

.

(3) IFERROR()

Usage:

แสดงข้อมูลในกรณีที่สูตรเกิด error

Syntax:

=IFERROR(value, value_if_error)

• value คือ สูตรที่เราใช้ทำงาน และอาจจะเกิด error ได้
• value_if_error คือ ค่าที่จะแสดงในกรณีที่เกิด error

Example:

สมมุติเราใช้ IFS() เพื่อจัดกลุ่มจำนวนเงิน

แต่เราระบุแค่เงื่อนไขเดียว ทำให้ข้อมูลบางส่วนเกิด error เช่น:

• เราระบุว่า จำนวนเงินตั้งแต่ 5,000 จัดอยู่ในกลุ่ม “Wealthy”
• แต่เพราะเราไม่ได้กำหนดจำนวนที่น้อยกว่า 5,000 จะแสดงค่าอะไร

เราสามารถใช้ IFERROR() เพื่อแสดงค่าบางอย่างแทน ซึ่งจะช่วยให้ข้อมูลดูมีระเบียบขึ้นได้ เช่น “-”:

=ArrayFormula(IFERROR(IFS(A3:A>=5000, "Wealthy"), "-"))

ผลลัพธ์:

🧑‍💼 Group #5 – Working With Date

ในกลุ่มที่ 5 เรามาดู 3 สูตรที่ใช้ทำงานกับวันที่กัน:

1. TODAY()
2. DATEDIF()
3. NETWORKDAYS()

.

(1) TODAY()

Usage:

แสดงวันที่ของวันนี้

Example:

สมมุติว่า วันนี้เป็นที่ 10 ม.ค. 2025:

=TODAY()

ผลลัพธ์:

Google Sheets จะแสดง 01/10/2025

.

(2) DATEDIF()

Usage:

แสดงจำนวนวัน ระหว่าง 2 วันที่

Example:

หาจำนวนวัน ตั้งแต่วันที่ 1 ของปี 2025 จนถึง วันนี้:

=DATEDIF("01/01/2025", TODAY(), "D")

ผลลัพธ์:

Google Sheets จะแสดงจำนวนวันระหว่างวันนี้ และ วันที่ 1 ม.ค. 2025 เช่น 9

.

(3) NETWORKDAYS()

Usage:

แสดงจำนวนวันทำการ ระหว่าง 2 วันที่

Example:

หาจำนวนวันทำการ ตั้งแต่วันที่ 1 ของปี 2025 จนถึง วันนี้:

=NETWORKDAYS("01/01/2025", TODAY())

ผลลัพธ์:

Google Sheets จะแสดงจำนวนวันทำการระหว่างวันนี้ และ วันที่ 1 ม.ค. 2025 เช่น 8

🧑‍💼 Group #6 – Working With Text

ในกลุ่มที่ 6 เรามาดูสูตรที่ใช้ทำงานกับ text กัน:

1. Splitting text
   1. SPLIT()
2. Joining text
   1. &
   2. TEXTJOIN()
3. Extracting text
   1. LEFT()
   2. RIGHT()
   3. MID()
4. Regular expression
   1. REGEXMATCH()
   2. REGEXEXTRACT()

.

(1) Splitting Text

เราสามารถใช้ SPLIT() เพื่อแยก text ออกเป็นคำ ๆ ได้

Syntax:

=SPLIT(text, delimiter)

• text คือ ข้อความที่เราต้องการจะแยก
• delimiter คือ เครื่องหมายที่ใช้คั่นข้อความ เช่น:
  • Comma (,)
  • Dot (.)
  • Semi-colon (;)
  • Blank space
  • Tab

Example:

แยก description ออกเป็นคำ ๆ (โดยใช้ blank space เป็น delimiter):

=ArrayFormula(SPLIT(F3:F, " "))

Note: เราใช้ ArrayFormula ช่วยให้สูตรใช้งานได้กับทั้ง range

ผลลัพธ์:

.

(2) Joining Text

สูตร:

การเชื่อม text เข้าด้วยกัน ทำได้ 2 วิธี:

Formula	Description
&	เชื่อม text อย่างง่าย หรือไม่มีรูปแบบตายตัว
TEXTJOIN()	เชื่อม text อย่างมีรูปแบบ เชื่อม text อย่างมีรูปแบบ (เช่น เชื่อมโดยมี , คั่น)

Example:

เราต้องการเชื่อมข้อมูลให้กลายเป็นประโยคว่า:

ลูกค้าใช้เงินจำนวนเท่าไร + ประเภทอะไร + ไปกับอะไร

=TEXTJOIN(" ", TRUE, A2&" spent "&B2, "("&C2&")", "on", "'"&D2&"'")

ผลลัพธ์:

จะเห็นว่า ในตัวอย่าง เราใช้ & และ TEXTJOIN() คู่กัน:

• ใช้ & ที่เกิดขึ้นครั้งเดียว เช่น customer_id + “spent” + amount
• ใช้ TEXTJOIN() เพื่อใส่ blank space ระหว่าง text แต่ละชุด

Note: เราต้องเขียน TEXTJOIN() ทีละ row เอง เพราะเราไม่สามารถใช้ ArrayFormula กับ TEXTJOIN() ได้

.

(3) Extracting Text

สูตร:

เราสามารถดึง text ออกมา ได้ด้วย 3 วิธี:

Formula	Description
LEFT()	ดึง text โดยนับจากทางซ้าย
RIGHT()	ดึง text โดยนับจากทางขวา
MID()	ดึง text โดยเริ่มจากตรงกลาง

Syntax:

สำหรับ LEFT() และ RIGHT() เขียนเหมือนกัน:

=LR(string, characters)

• LR คือ เลือกสูตร LEFT หรือ RIGHT
• string คือ text ต้นฉบับที่เราต้องการดึงข้อมูลออกมา
• characters คือ จำนวนตัวอักษรที่ต้องการดึงออกมา โดยนับจากซ้ายหรือขวา ตามสูตรที่เลือก

ส่วน MID() มีการเขียนที่ต่างออกไป:

=MID(string, starts, characters)

• string คือ text ต้นฉบับที่เราต้องการดึงข้อมูลออกมา
• starts คือ ลำดับของตัวอักษรที่จะเริ่มดึง
• characters คือ จำนวนตัวอักษรที่ต้องการดึงออกมา

Example:

ใช้ 3 สูตรแยก วัน เดือน ปี ออกจาก date:

Data	Formula
Day	=ArrayFormula(LEFT(A3:A7, 2))
Month	=ArrayFormula(MID(A3:A7, 4, 2))
Year	=ArrayFormula(RIGHT(A3:A7, 4))

ผลลัพธ์:

.

(3) Regular Expression

สูตร:

Google Sheets รองรับการใช้งาน regular expression หรือ การเขียนเพื่อจับคู่รูปแบบ text

โดย มี 2 สูตรหลักที่มักใช้งาน คือ:

Formula	Description
REGEXMATCH()	เช็กว่า ในชุดข้อมูลไหม มี text ที่ต้องการ
REGEXEXTRACT()	ดึง text ออกจากชุดข้อมูล

Syntax:

=regex(text, regular_expression)

• regex คือ สูตร REGEXMATCH หรือ REGEXEXTRACT
• text คือ ชุดข้อมูลที่เราต้องการเข้าไปค้นหา
• regular_expression คือ รูปแบบ text ที่เราต้องการค้นหา

Example:

เราต้องการทำ 2 อย่าง:

1. เช็กว่า แต่ละ description มีคำว่า “she” ไหม (REGEXMATCH)
2. ดึงคำว่า “she” ออกจาก description (REGEXEXTRACT)

เราสามารถเขียนสูตรได้ดังนี้:

Problem	Formula
เช็กว่า แต่ละ description มีคำว่า “she” ไหม	=ArrayFormula(REGEXMATCH(A2:A, "(?i)\\bshe\\b"))
ดึงคำว่า “she” ออกจาก description	=ArrayFormula(IFERROR(REGEXEXTRACT(A2:A, "(?i)\\bshe\\b"), "NA"))

Note: สำหรับ REGEXEXTRACT() เราใช้ IFERROR() มาชวนแทนค่าในกรณีที่ข้อมูลต้นทางไม่มีคำว่า “she”

ผลลัพธ์:

จากผลลัพธ์ จะเห็นได้ว่า regular expression ที่เราใช้ จะทำให้สูตรของเราสามารถใช้ได้กับ “she” ที่เป็นพิมพ์เล็กและพิมพ์ใหญ่

Note: เราสามารถศึกษาการเขียน regular expression ทั้งหมดได้ที่ Syntax for Regular Expressions จาก Google

🧑‍💼 Group #7 – Google

ในกลุ่มสุดท้าย เรามาดู 2 สูตรเฉพาะของ Google กัน:

1. GOOGLEFINANCE()
2. GOOGLETRANSLATE()

.

(1) GOOGLEFINANCE()

Usage:

GOOGLEFINANCE() สามารถทำได้หลายอย่าง เช่น:

• แสดงราคาหุ้น
• แปลงสกุลเงิน
• วิเคราะห์เงินปันผล

Syntax:

การเขียน GOOGLEFINANCE() แตกต่างกันไปในแต่ละการใช้งาน

เราสามารถศึกษาการเขียน GOOGLEFINANCE() ได้ที่ GOOGLEFINANCE จาก Google

.

(2) GOOGLETRANSLATE()

Usage:

แปลภาษา

Syntax:

=GOOGLETRANSLATE(text, source_lang, target_lang)

• text คือ ข้อความที่เราต้องการแปลภาษา
• source_lang คือ ภาษาของข้อความต้นทาง
• target_lang คือ ภาษาของข้อความปลายทาง

Example:

เราสามารถใช้ทั้ง GOOGLEFINANCE() และ GOOGLETRANSLATE() เพื่อแปลข้อมูลจากอังกฤษเป็นไทยได้:

Formula	Description
=ArrayFormula(A3:A12 * GOOGLEFINANCE("CURRENCY:USDTHB"))	แปลงค่าเงินจากดอลล่าร์สหรัฐเป็นเงินบาท
=GOOGLETRANSLATE(B3:B12, "en", "th")	แปล text จากอังกฤษเป็นไทย

Note: ArrayFormula ไม่สามารถใช้คู่กับ GOOGLETRANSLATE() ได้

ผลลัพธ์:

💪 Recap

ในบทความนี้ เราทำความรู้จักกับ 7 กลุ่มสูตร Google Sheets สำหรับทำงานกับ data กัน:

กลุ่มที่ 1 – Filtering and sorting:

Formula	For
FILTER()	กรองข้อมูล
SORT()	จัดเรียงข้อมูล

กลุ่มที่ 2 – Aggregating:

Formula	Description
COUNTA()	นับข้อมูล
SUM()	หาผลรวม
AVERAGE()	หาค่าเฉลี่ย
MEDIAN()	หาค่ากลาง
MODE()	หา value ที่ซ้ำเยอะที่สุด
MIN()	หา value ที่น้อยที่สุด
MAX()	หา value ที่มากที่สุด
QUARTILE()	หา quantile
STDEV()	หา SD
VAR()	หา variance

กลุ่มที่ 3 – Searching:

Formula	For
VLOOKUP()	ดึงข้อมูลที่ตรงกับ index
INDEX()	ดึงข้อมูลที่ตรงกับ index
MATCH()	ระบุตัวแหน่งข้อมูล
QUERY()	ดึงข้อมูลตามเงื่อนไข

กลุ่มที่ 4 – Conditions:

Formula	For
IF()	สร้างข้อมูลใหม่ ตาม 1 เงื่อนไข
IFS()	สร้างข้อมูลใหม่ ตามมากกว่า 1 เงื่อนไข
IFERROR()	สร้างข้อมูลใหม่ ถ้าเกิด error

กลุ่มที่ 5 – Working with dates:

Formula	For
TODAY()	แสดงวันที่ของวันนี้
DATEFID()	แสดงจำนวนวันระหว่าง 2 วันที่
NETWORKDAYS()	แสดงจำนวนวันทำการระหว่าง 2 วันที่

กลุ่มที่ 6 – Working with text:

Formula	For
SPLIT()	แยก text
– & – TEXTJOIN()	เชื่อม text
– LEFT() – RIGHT() – MID()	ดึง text
– REGEXMATCH() – REGEXEXTRACT()	ทำงานกับ text โดยใช้ regular expression

กลุ่มที่ 7 – Google:

Formula	For
GOOGLEFINANCE()	แปลงสกุลเงิน
GOOGLETRANSLATE()	แปลภาษา