Tag: Google Sheets

วิธีโหลดข้อมูล Google Sheets มาวิเคราะห์ใน Python บน Google Colab ใน 3 ขั้นตอน–ตัวอย่างจาก Harry Potter transaction dataset

Google Sheets เป็นเครื่องมือเก็บข้อมูลที่ทุกคนสามารถเข้าถึงได้ฟรี และมักเป็นที่เก็บข้อมูลทั้งส่วนตัว (เช่น รายรับรายจ่าย) และธุรกิจ (เช่น ข้อมูลการขาย ข้อมูลลูกค้า)

แม้ว่า Google Sheet จะวิเคราะห์ข้อมูลได้ แต่การวิเคราะห์จะมีประสิทธิภาพมากกว่า เมื่อเราใช้ programming language อย่าง Python เข้ามาช่วย

นอกจากความรวดเร็วในการประมวลผล และรองรับข้อมูลปริมาณมาก Python ยังสามารถวิเคราะห์แบบอัตโนมัติได้ด้วย เพียงแค่เราเขียน code รอเอาไว้

ในบทความนี้ เราจะมาดูวิธีการโหลดข้อมูลจาก Google Sheet เข้ามาวิเคราะห์ใน Python บน Google Colab กัน

บทความนี้แบ่งเป็น 3 ส่วน:

Load spreadsheet
Load worksheet
Load data

สำหรับคนที่ต้องการทำตาม สามารถดูไฟล์ตัวอย่างได้ตาม link:

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

1️⃣ Step 1. Load Spreadsheet

เริ่มแรก เราจะโหลด spreadsheet ที่ต้องการ ใน 2 ขั้นตอน:

Authorise: ให้สิทธิ์การเข้าถึง Google Drive กับ Colab
Open spreadsheet: เชื่อมต่อ Google Sheet ที่ต้องการ

✅ 1.1 Authorise

เราเปิดสิทธิ์การเข้าถึง Google Drive ให้กับ Colab ได้แบบนี้:

			
# Grant Colab access to Google services
# Import package
from google.colab import auth
# Enable access to Google services
auth.authenticate_user()

		

เมื่อกด “Run”, Google จะพาเราไปที่หน้า Sign In ให้เรากด “Continue”:

จากนั้น ติ๊ก checkbox เพื่อให้สิทธิ์กับ Colab แล้วกด “Continue”

หลังเปิดสิทธิ์ ให้เราสร้าง client เพื่อเข้าถึง Google Drive:

			
# Connect to Google Drive
# Import packages
import gspread
from google.auth import default
# Get credentials and Google Cloud project ID
creds, _ = default()
# Create Google Sheet client
gc = gspread.authorize(creds)

		

Note:

default() จะคืนค่าให้ 2 อย่าง คือ credentials และ Google Cloud project ID
เราจะใช้เฉพาะ credentials
ส่วน Google Cloud project ID เราจะปล่อยทิ้งไป โดยเก็บไว้ใน _

📖 1.2 Open Spreadsheet

หลังจากสร้าง client แล้ว เราจะเชื่อมต่อกับ spreadsheet ซึ่งเราจะต้องเอา ID ของ spreadsheet มาจาก URL ตามตัวอย่างในรูป:

ให้เรา copy ID มาใช้แบบนี้:

			
# Load spreadsheet
# Define spreadsheet ID
spreadsheet_id = "12MglU8pFc_7XAylqANyqm8aLQNwvL98fXObjHEjrRvQ"
# Open spreadsheet
spreadsheet = gc.open_by_key(spreadsheet_id)
# Print spreadsheet title
print(spreadsheet.title)

		

ผลลัพธ์:

Diagon Alley Artefacts

ตอนนี้ เราก็โหลด spreadsheet สำเร็จแล้ว

ตัวอย่าง spreadsheet:

2️⃣ Step 2. Load Worksheet

หลังจากโหลด Google Sheet แล้ว เราจะเชื่อมต่อกับ worksheet ที่ต้องการ ใน 2 ขั้นตอน:

List: ดูรายชื่อ worksheet ทั้งหมดใน Google Sheet
Select: เลือก worksheet

📋 2.1 List

เราดูรายชื่อ worksheet ทั้งหมดได้แบบนี้:

			
# List worksheets
# Get all worksheet names
worksheets = spreadsheet.worksheets()
# Print them
for ws in worksheets:
    print(ws.title)

		

ผลลัพธ์:

			
transactions
Sheet2
Sheet3

🫳 2.2 Select

จากนั้น ให้เราโหลด worksheet ที่ต้องการ (เช่น transactions):

			
# Select worksheet
worksheet = spreadsheet.worksheet("transactions")

ตอนนี้ เราก็เชื่อมต่อกับ worksheet สำเร็จแล้ว

3️⃣ Step 3. Load Data

สุดท้าย เราจะโหลดข้อมูลจาก worksheet ใน 3 ขั้นตอน:

Read data: โหลดข้อมูลจาก worksheet
Convert to DataFrame: เปลี่ยนข้อมูลให้เป็น DataFrame
Analyse: วิเคราะห์ข้อมูลตามต้องการ

👓 3.1 Read Data

เราจะโหลดข้อมูลจาก worksheet แบบนี้:

			
# Get all data from worksheet
data = worksheet.get_all_values()
# Print result
data

โดยข้อมูลที่ได้จะเป็น list of lists (1 list = 1 row):

🐼 3.2 Convert to DataFrame

เพื่อช่วยให้เราวิเคราะห์ข้อมูลได้ง่าย เราจะเปลี่ยนข้อมูลให้เป็น DataFrame ด้วย pandas:

			
# Convert data to df
# Import package
import pandas as pd
# Convert
df = pd.DataFrame(
    data=data[1:],
    columns=data[0]
)
# Print result
df

		

ผลลัพธ์:

📈 3.3 Analyse

จากนั้น เราสามารถวิเคราะห์ข้อมูลได้ตามต้องการ เช่น คำนวณยอดขายทั้งหมด:

			
# Find total sales per category
# Convert column types to numeric
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
df["unit_price"] = pd.to_numeric(df["unit_price"], errors="coerce")
# Calculate total sales per row
df["revenue"] = df["quantity"] * df["unit_price"]
# Calculate sum sales
total_sales = df["revenue"].sum()
# Print result
print(total_sales)

		

ผลลัพธ์:

11600.0

Note:

ข้อมูลที่โหลดจะเป็น string ดังนั้น เราต้องเปลี่ยนตัวเลขให้กลายเป็น numeric หรือ float ก่อนเอาไปวิเคราะห์ต่อ
ดูวิธีวิเคราะห์ข้อมูลด้วย pandas

💪 Summary

ในบทความนี้ เราดูวิธีโหลดข้อมูล Google Sheet เข้ามาใน Python บน Google Colab ใน 3 ขั้นตอน:

Step 1. Load spreadsheet:

Code	For
`auth.authenticate_user()`	เปิดสิทธิ์เข้าถึง Google Drive
`default()`	รับ credentials
`gspread.authorize(creds)`	สร้าง client
`gc.open_by_key(spreadsheet_id)`	เชื่อมต่อ Google Sheet

Step 2. Load worksheet:

Code	For
`spreadsheet.worksheets()`	ดูรายชื่อ worksheet ทั้งหมด
`spreadsheet.worksheet("worksheet_name")`	เลือก worksheet

Step 3. Load data:

Code	For
`worksheet.get_all_values()`	โหลดข้อมูลใน worksheet
`pd.DataFrame(data=data[1:], columns=data[0])`	แปลงข้อมูลให้เป็น DataFrame

📃 References

2026-06-18

Regular Expressions: เครื่องมือถอดรหัส เพื่อทำงานกับ Text ใน Google Sheets — แนะนำวิธีใช้สูตร REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Regular expression หรือ การเขียน pattern เพื่อทำงานกับ text เป็นเหมือนกับเครื่องถอดรหัสที่ช่วยถอดรูปแบบของข้อความ และช่วยให้เราทำงานกับข้อมูลได้ง่ายขึ้น

ยกตัวอย่างเช่น เรามีข้อมูลลูกค้าที่เก็บค่าไว้ใน cell เดียวกัน:

แต่เราต้องการแยกข้อมูลเป็น columns เพื่อความสะดวกในการใช้งานต่อ เช่น:

ID
Name
Address

แทนที่เรา copy-paste ข้อมูลลงทีละ column ด้วยตัวเอง เราสามารถใช้สูตร regular expression ของ Google Sheets ช่วยได้:

ในบทความนี้ เราจะทำความรู้จักกับ:

3 สูตร Google Sheets ที่ใช้ทำงานกับ regular expression
การเขียน regular expression เพื่อใช้งาน 3 สูตรนี้

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

👨‍🔬 Intro to REGEX Formulas

Google Sheets มี 3 สูตรสำหรับใช้ regular expression ซึ่งได้แก่:

No.	Formula	For
1	`REGEXMATCH()`	เช็กหาคำที่ตรงกับ regular expression
2	`REGEXEXTRACT()`	ดึงคำที่ตรงกับ regular expression ออกมา
3	`REGEXREPLACE()`	แทนที่คำที่ตรงกับ regular expression

โดยมีการเขียนดังนี้:

No.	Formula	Syntax
1	`REGEXMATCH()`	`REGEXMATCH(text, regular_expression)`
2	`REGEXEXTRACT()`	`REGEXEXTRACT(text, regular_expression)`
3	`REGEXREPLACE()`	`REGEXREPLACE(text, regular_expression, replacement)`

text คือ ข้อมูล text ที่เราจะใช้ทำงาน
regular_expression คือ regular expression ที่เราใช้กำหนดเงื่อนไข
replacement คือ คำที่จะแทนที่คำที่ตรงกับ regular expression

ก่อนไปดูการใช้งานจริง เรามาดูหลักการเขียน regular expression เพื่อให้สูตรเหล่านี้ทำงานได้กัน

⌨️ Regular Expression Basics

การเขียน regular expression แบ่งออกได้เป็น 4 กลุ่มหลัก ได้แก่:

Special characters
Character classes
Quantifiers
Others

🔣 (1) Special Characters

Regular expression ในกลุ่มนี้ เป็นอักขระพิเศษ โดยมี usage ต่างกันดังนี้:

No.	Character	For	Example
1	`^…`	เริ่มต้นด้วย …	`^J` หมายถึง คำที่ขึ้นต้นด้วย J
2	`…$`	ลงท้ายด้วย …	`s$` หมายถึง คำที่ลงท้ายด้วย s
3	`.`	แทนอักขระใด ๆ 1 ตัว (ยกเว้นการขึ้นบรรทัดใหม่)	`c.t` ใช้หาคำเช่น cat, cmt, cPt, c7t
5	`\\…`	ใช้บอกว่า … ไม่ใช่ regular expression	`\\.` หมายถึง ให้หาคำที่มี . แทนคำที่มีอักขระใด ๆ

🔣 (2) Character Classes

Regular expression ในกลุ่มนี้ ใช้แทนตัวอักษรและตัวเลข:

No.	Character	For	Example
1	`[…]`	จับคู่ตัวอักษร ใน `[]`	`[ABC]` หมายถึง จับคู่คำที่มี A, B, หรือ C
2	`[A-Z]`	จับคู่ตัวอักษร A-Z พิมพ์ใหญ่	จะได้คำ เช่น ANT, FGH, QPD, …
3	`[a-z]`	จับคู่ตัวอักษร a-z พิมพ์เล็ก	จะได้คำ เช่น ant, fgh, qpd, …
4	`[A-Za-z]`	จับคู่ตัวอักษร A-Z พิมพ์เล็กหรือใหญ่ก็ได้	จะได้คำ เช่น aNt, Fgh, qpD, …
5	`[A-z]`	จับคู่ตัวอักษร a-z พิมพ์เล็กหรือใหญ่ก็ได้	จะได้คำ เช่น aNt, Fgh, qpD, …
6	`[0-9]`	จับคู่ตัวตัวเลข 0-9	จะได้คำ เช่น 012, 438, 507, …

🔣 (3) Quantifiers

Regular expression ในกลุ่มนี้ ใช้แทนจำนวนตัวอักขระที่ต้องการค้นหา:

No.	Character	For	Example
1	`…*`	จับคู่อักขระ … จำนวนตั้งแต่ 0 ขึ้นไป	`A*` จับคู่คำ เช่น A, An, Ant, ANts, …
2	`…+`	จับคู่อักขระ … จำนวนตั้งแต่ 1 ขึ้นไป	`A+` จับคู่คำ เช่น An, Ant, ANts, …
3	`…?`	จับคู่อักขระ … จำนวน 0 หรือ 1	`A?` จับคู่คำ เช่น A, An, AT, …
5	`…{…}`	จับคู่อักขระ … ตามจำนวนใน `{}`	`.{5}` หมายถึง จับคู่อักขระใด ๆ ที่มีความยาว 5 ตัว
4	`…{…, …}`	จับคู่อักขระ … ตามช่วงใน `{}`	`.{3, 5}` หมายถึง จับคู่อักขระใด ๆ ที่มีความยาว 3 ถึง 5 ตัว

🔣 (4) Others

Regular expression อื่น ๆ นอกเหนือ 3 กลุ่มแรก:

No.	Character	For	Example
1	`\\w`	จับคู่ A-Z ทั้งพิมพ์เล็กและใหญ่ หรือตัวเลขก็ได้	–
2	`\\d`	จับคู่ตัวตัวเลข 0-9	–
3	`\\b`	จับคู่ word boundary	–
4	`\\n`	จับคู่ new line	–
5	`\\s`	จับคู่ blank space	–
6	`(…)`	จับกลุ่ม regular expression	–

📃 Learn More

เราสามารถ regular expression อื่น ๆ ได้ที่ Syntax for Regular Expressions จาก Google

💪 Put It All Together

ตอนนี้ เรารู้จัก 3 สูตร REGEX และการเขียน regular expression แล้ว

เรามาดูตัวอย่างการใช้งานสูตร REGEX ในการทำงานกัน

ในตัวอย่าง เรามีข้อมูลพนักงาน 3 columns ได้แก่:

Name
Email
Comment

Note: สามารถดูต้นฉบับได้ที่ Google Sheets

🗝️ (1) REGEXMATCH

สมมุติว่า เราต้องการเช็กว่า ใน comment มีคำว่า “comment” ไหม

เราสามารถใช้ REGEXMATCH ได้ดังนี้:

=REGEXMATCH(C3, "[Cc]omment")

ผลลัพธ์:

จะเห็นว่า เราได้ค่า TRUE และ FALSE กลับมา:

TRUE แสดงว่า text มีคำว่า “comment”
FALSE แสดงว่า text ไม่มีคำว่า “comment”

🗝️ (2) REGEXEXTRACT

เราต้องการดึงนามสกุลออกจากชื่อ

เราสามารถใช้ REGEXEXTRACT ช่วยได้:

=REGEXEXTRACT(A3, "\s([A-z]*)")

ผลลัพธ์:

🗝️ (3) REGEXREPLACE

เราต้องการเปลี่ยนอีเมลของทุกคนให้มี domain (เช่น @example.com และ @adams-mail.net) เป็น @email.com

เราสามารถใช้ REGEXREPLACE ช่วยได้:

=REGEXREPLACE(B3, "@[A-z.-]+\.[A-z]{2,}", "@email.com")

ผลลัพธ์:

📚 References

2025-08-14

Google Sheets Essentials: วิธีเขียน 7 กลุ่มสูตรสำคัญใน Google Sheets สำหรับงาน Data พร้อมตัวอย่างการทำงานกับข้อมูลการเงิน

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

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

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

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

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

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

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

💳 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 สูตรสำหรับกรองและจัดเรียงข้อมูล:

FILTER()
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 สูตรสำหรับค้นหาข้อมูลกัน:

VLOOKUP()
INDEX()
MATCH()
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 อย่าง:

ประหยัดเวลาในการทำงาน
ช่วยในลดโหลดการทำงาน ทำให้ 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 สูตรสำหรับสร้างข้อมูลใหม่ตามเงื่อนไขกัน:

IF()
IFS()
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 สูตรที่ใช้ทำงานกับวันที่กัน:

TODAY()
DATEDIF()
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 กัน:

Splitting text
1. SPLIT()
Joining text
1. &
2. TEXTJOIN()
Extracting text
1. LEFT()
2. RIGHT()
3. MID()
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 อย่าง:

เช็กว่า แต่ละ description มีคำว่า “she” ไหม (REGEXMATCH)
ดึงคำว่า “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 กัน:

GOOGLEFINANCE()
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()`	แปลภาษา

2025-01-16

Tag: Google Sheets

วิธีโหลดข้อมูล Google Sheets มาวิเคราะห์ใน Python บน Google Colab ใน 3 ขั้นตอน–ตัวอย่างจาก Harry Potter transaction dataset

1️⃣ Step 1. Load Spreadsheet

✅ 1.1 Authorise

📖 1.2 Open Spreadsheet

2️⃣ Step 2. Load Worksheet

📋 2.1 List

🫳 2.2 Select

3️⃣ Step 3. Load Data

👓 3.1 Read Data

🐼 3.2 Convert to DataFrame

📈 3.3 Analyse

💪 Summary

📃 References

Share this:

Regular Expressions: เครื่องมือถอดรหัส เพื่อทำงานกับ Text ใน Google Sheets — แนะนำวิธีใช้สูตร REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

👨‍🔬 Intro to REGEX Formulas

⌨️ Regular Expression Basics

🔣 (1) Special Characters

🔣 (2) Character Classes

🔣 (3) Quantifiers

🔣 (4) Others

📃 Learn More

💪 Put It All Together

🗝️ (1) REGEXMATCH

🗝️ (2) REGEXEXTRACT

🗝️ (3) REGEXREPLACE

📚 References

Share this:

Google Sheets Essentials: วิธีเขียน 7 กลุ่มสูตรสำคัญใน Google Sheets สำหรับงาน Data พร้อมตัวอย่างการทำงานกับข้อมูลการเงิน

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

🏷️ Named Ranges

🤔 Syntax Help

🧑‍💼 Group #1 – Filtering & Sorting

(1) FILTER()

(2) SORT()

🧑‍💼 Group #2 – Aggregating

🧑‍💼 Group #3 – Searching

(1) VLOOKUP()

(2) INDEX()

(3) MATCH()

(4) QUERY()

🧑‍💼 Group #4 – Conditions

(1) IF()

(2) IFS

(3) IFERROR()

🧑‍💼 Group #5 – Working With Date

(1) TODAY()

(2) DATEDIF()

(3) NETWORKDAYS()

🧑‍💼 Group #6 – Working With Text

(1) Splitting Text

(2) Joining Text

(3) Extracting Text

(3) Regular Expression

🧑‍💼 Group #7 – Google

(1) GOOGLEFINANCE()

(2) GOOGLETRANSLATE()

💪 Recap

Share this: