เตรียมข้อมูลด้วย Google Sheets สำหรับมือใหม่ เริ่มได้ทันที

Introduction

Google Sheets เป็นเครื่องมือ Spreadsheets ที่สร้างโดย Google ที่มีลักษณะเป็นตาราง คล้ายๆ กับ Microsoft Excel ใช้ในการเก็บข้อมูลและทำงานต่างๆ ใน Data Analytics Workflow ได้อย่างรวดเร็ว

การเก็บข้อมูลด้วย Google Sheets มักเกิดจากการเก็บข้อมูลในปริมาณน้อยและไม่มีงบประมาณเพื่อใช้จัดการ Database โดยขั้นตอนนี้เราเรียกอีกอย่างว่า Store Data

และด้วยความที่ Google Sheets มีลักษณะเป็น Tabular Format คือ ตารางเก็บข้อมูลที่มีลักษณะเป็น Rows และ Columns เปรียบได้กับ Relational Database นั่นเอง

Data Analytics Workflow

Data Analytics Workflow คือ กระบวนการและขั้นตอนทำงานของ Data Analytics มีอยู่ 6 องค์ประกอบ

1. Business Question คือ ขั้นตอนการหาโจทย์จากฝั่ง Business หรือจุดประสงค์และเป้าหมายที่ต้องการ
2. Data Collection คือ ขั้นตอนการเก็บข้อมูลเพื่อตอบโจทย์ Business ให้ได้มากที่สุด
3. Data Preparation คือ ขั้นตอนการเตรียมข้อมูลเพื่อให้อยู่ในสภาพที่พร้อมใช้ในการวิเคราะห์ข้อมูล
4. Data Analysis คือ ขั้นตอนการสำรวจและวิเคราะห์ข้อมูลผ่านหลักสถิติในมุมต่างๆ
5. Data Visualization คือ ขั้นตอนการแสดงผลข้อมูลให้อยู่ในรูปแบบของ Chart และ Graph ที่เหมาะสมกับประเภทของข้อมูล เพื่อให้เข้าใจได้ง่าย
6. Data Storytelling คือ ขั้นตอนการนำข้อมูลมาเล่าเป็นเรื่องหัวใจสำคัญคือ Next step & Recommendation ที่ช่วยให้ผู้มีอำนาจในการตัดสินใจสามารถ Action ต่อได้ง่าย

โดยบทความนี้เราจะเน้นไปที่ขั้นตอนที่ 3 คือ Data Preparation

Data Collection - เก็บข้อมูลด้วย Google Sheets

1. สร้าง Google Sheets ไฟล์ใหม่

sheets.new -- พิมพ์ลงบน web browser

2. Store Data ลงไปใน Google Sheets

ตั้งชื่อไฟล์เป็น Example: Data Analytics Data เพื่อป้องกันการสับสนเพราะถ้าเราไม่ตั้งชื่อ ไฟล์จะเป็นชื่อ Untitled spreadsheet อาจทำให้หาไฟล์ยากในอนาคต จากนั้นลองเพิ่มข้อมูลลงไปตามตัวอย่างในรูป

Data Preparation - เตรียมข้อมูลด้วย Google Sheets

การเตรียมข้อมูลเปรียบเสมือนกับ Chef ที่นำวัตถุดิบมาทำให้พร้อมทาน หากจัดการวัตถุดิบได้ไม่ดีพอ อาหารที่ออกมาก็อาจมีรสชาติไม่อร่อยหรือเลวร้ายที่สุดคือไม่สามารถรับประทานได้เลย

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

1. Tidy Data

เปลี่ยนจากโครงสร้างตาราง Wide Format ที่มีลักษณะการเติมข้อมูลชุดใหม่เข้ามาทางด้านคอลัมน์ ให้กลายเป็น Long Format ที่มีลักษณะการเติมข้อมูลชุดใหม่เข้ามาทางด้านล่าง เช่นเดียวกับ Relational Database ทั่วไป

เปลี่ยนจาก Wide Format เป็น Long Format

ด้วยการลากคลุมที่ Data ทั้งหมดแล้วกด Ctrl+C เพื่อ Copy

จากนั้นคลิกที่ Cell A2 และคลิกขวา Paste special > Transposed

หลังจากนั้นเราก็จะได้ Long Format Table เรียบร้อย (อย่าลืมลบแถวแรกทิ้งไปด้วยนะ)

2. Remove Duplicate

ขั้นตอนนี้เป็นการกำจัดข้อมูลที่มีความซ้ำซ้อนกัน โดยใน Google Sheets มีวิธีทำอยู่หลายวิธีด้วยกัน โดยเราจะใช้ Built-in Feature: Remove duplicate (ตัวอย่างนี้เราจะใช้อีก Dataset นึงที่มีข้อมูลอยู่หลาย Column จะได้เห็นภาพชัดมากขึ้น)

ลากคลุมที่หัว Columns ทั้งหมดที่เรามี Data อยู่ แล้วเข้าไปที่เมนู

Data > Data cleanup > Remove duplicates

จะมีหน้าต่าง Remove duplicates ขึ้นมา ให้เรากดติ๊กถูกที่ Data has header row เพื่อป้องกัน Data ในหัวคอลัมน์ที่อาจถูกลบออกได้เช่นกัน จากนั้นคลิก Remove duplicates

สุดท้ายเราจะได้ผลลัพธ์ว่ามีการลบ Data ที่เป็น Duplicate ไปกี่แถว แต่ตารางที่เราเตรียมมาจะไม่มีข้อมูลซ้ำกันอยู่แล้ว

3. Unique Identify

เพื่อให้เราสามารถเชื่อมข้อมูลและ Refer ถึงข้อมูลได้อย่างเฉพาะเจาะจง เราจำเป็นต้องสร้าง Unique ID ให้กับข้อมูลทุกแถวเพื่อหลีกเลี่ยงความสับสนในการใช้งานข้อมูล

แต่ถ้าไม่มี Unique ID เราสามารถสร้างได้ง่ายๆ ได้ด้วยสูตรใน Google Sheets

• สร้าง Column Unique ID
• พิมพ์ ID แรกลงไปใน Data Row ที่ 2 ซึ่งในตัวอย่างคือ Cell A2
• ใช้สูตร =A2+1 ในช่อง A3 เพื่อสร้าง Unique ID ค่าต่อไปที่ไม่ซ้ำ

• ใช้สูตร IF เพื่อป้องกันการสร้าง ID โดยที่ไม่มี Data อยู่ใน Column B

SYNTAX

=IF(B3="","",A2+1)
###ถ้า(B3 เป็นค่าว่าง,ให้แสดงผลเป็นค่าว่าง,ถ้าไม่ใช่ให้แสดงค่าเป็น A2+1

4. Homogeneity

Homogeneity คือการเปลี่ยน Data ให้อยู่ใน Format เดียวกัน ยกตัวอย่าง Data ที่มีความหลอนที่สุดคือ Date มาลองดูตัวอย่างกันใน Column Date of Birth

Jame จะเป็น Date Format = YYYY/M/DD
Thomas จะเป็น Date Format = YYYY/DD/M
Andre จะเป็น Date Format = YYYY-MM-DD

เชื่อว่าหลายคนคงเคยเจอสภาพนี้กันอย่างแน่นอน ทางแก้ที่ดีที่สุดในการทำสิ่งนี้คือ เราต้องเลือกก่อนว่าต้องการ Format แบบไหน ส่วนใหญ่แล้วจะเป็น YYYY-MM-DD เหมือนกับของ Andre

แยกส่วน ปี เดือน วัน ด้วย Function SPLIT ใน Column D

SYNTAX

=SPLIT(C2,"/")
###แยกส่วนข้อมูล(C2,ด้วยเครื่องหมาย /)

จะเห็นว่าเมื่อเราใช้ Function นี้แล้ว ข้อมูลจะถูกแยกส่วนด้วย Delimiter "/" เป็น Cell สีแดงๆ ในรูป

แต่ความมั่วยังคงอยู่เพราะของ Thomas และ Jennie จะเห็นเลยว่ามันยังเป็น Data ที่ผิดเพราะเดือนควรมีค่าไม่เกิน 12 ดังนั้นเราจึงต้องทำการปรับ Format กันต่อ

สร้าง Column Month Final เพื่อปรับเดือนให้มีค่าที่ถูกต้อง และใช้ Function IF มาช่วยจัดการเรื่องนี้

=IF(E2>12,F2,E2)
###ถ้า E2 มีค่ามากกว่า 12,ให้ดึงค่าจาก F2 มาแทน,แต่ถ้าไม่ใช่ ให้ใช้ค่าใน E2 ได้เลย

สร้าง Column Date Final เพื่อปรับวันให้มีค่าที่ถูกต้องและใช้ Function IF มาช่วยจัดการเรื่องนี้

=IF(F2=G2,E2,F2) 
###ถ้าข้อมูลใน F2 มีค่าเท่ากับ G2,ให้ดึงข้อมูล E2 มาใช้,แต่ถ้าไม่ ให้ดึงข้อมูล F2 มาใช้

สร้าง Column Final Date เพื่อประกอบร่างกลับสู่ Date ด้วย Function DATE

=DATE(D2,G2,H2)
###ประกอบร่าง Date(ปี,เดือน,วัน)

เมื่อประกอบร่าง Date เสร็จเรียบร้อยเราจะเห็นว่ายังมีบาง Row ที่ยังเป็น Error อยู่เนื่องจาก Format ถูกต้องอยู่แล้วให้เราปล่อยมันไปก่อน

ปรับ Format ให้เป็น YYYY-MM-DD ด้วยการซ้อนสูตร TEXT

=TEXT(DATE(D2,G2,H2),"YYYY-MM-DD")
###ปรับ Format Date ที่ประกอบร่างให้กลายเป็น YYYY-MM-DD

เคลียร์ Row ที่ยัง Error อยู่เพราะมี Format ที่ถูกต้องด้วยการเพิ่ม IFERROR

=IFERROR(TEXT(DATE(D2,G2,H2),"YYYY-MM-DD"),C2)
###ถ้าทำทุกอย่างแล้วยัง Error ให้ดึงค่าใน C2 มาใช้ได้เลย

เอาสูตรทั้งหมดออก

ด้วยการลากคลุม Final Date แล้วกด Crtl+C เพื่อ Copy และคลิกขวาแล้วกด Paste special > Values only เพื่อให้เหลือแต่ Data เท่านั้น

Done! เท่านี้เราก็จัดการกับ Date เป็นที่เรียบร้อย

Join Data - เชื่อมข้อมูลด้วย Google Sheets

การเชื่อมช้อมูลเป็นอีกขั้นตอนที่ใช้เวลาค่อนข้างนาน หากข้อมูลไม่มี Unique ID ที่พร้อมใช้งาน โดยการเชื่อมข้อมูล หรือ การ Join Data มีหลายรูปแบบโดยมักจะใช้ตารางที่เป็นตัวตั้งคือ ตารางทางซ้าย (Left Table) และตารางที่เอามาเชื่อมข้อมูลคือ ตารางทางขวา (Right Table)

1. Inner Join: เป็นการเชื่อมข้อมูลโดยเอาเฉพาะสิ่งที่ตารางทั้งซ้ายและขวาเชื่อมด้วย Unique Key ได้ โดยส่วนที่เชื่อมกันไม่ได้ก็จะไม่แสดงผล โดยส่วนมากแล้วการ Join ประเภทนี้จะทำให้ข้อมูลลดลง (อาจมากขึ้นในบางกรณี)
2. Full Join: เป็นการเชื่อมข้อมูลโดยเอาทุกอย่างของทั้งสองตารางมาใช้งาน การเชื่อมข้อมูลประเภทนี้ทำให้ข้อมูลมีขนาดใหญ่มาก
3. Left Join: เป็นการเชื่อมข้อมูลโดยยึดจากตารางซ้ายเป็นหลัก หากข้อมูลแถวนั้นเชื่อมกันได้ด้วย Unique Key จะแสดงค่าของตารางขวาเข้ามาในตารางซ้าย แต่หากเชื่อมไม่ได้จะแสดงค่า NULL
4. Right Join: เป็นการเชื่อมข้อมูลโดยยึดจากตารางขวาเป็นหลัก หากข้อมูลแถวนั้นเชื่อมกันได้ด้วย Unique Key จะแสดงค่าของตารางซ้ายเข้ามาในตารางขวา แต่หากเชื่อมไม่ได้จะแสดงค่า NULL

การ Join Data ใน Google Sheets จะเป็นการใช้ Left Join เป็นหลัก โดยเราจะสร้างชุดข้อมูลขึ้นมาใหม่จาก Chat GPT เพื่อให้ข้อมูล Join กันได้อย่างเห็นภาพมากขึ้น

โดย Sample Data ทั้งหมด เราได้ไป Generate มาจาก Chat GPT เป็นที่เรียบร้อย เป็นข้อมูลการซื้อขายโทรศัพท์มือถือในปี 2024 ประกอบด้วย 3 Tables

Order Table: เก็บข้อมูลคำสั่งซื้อสินค้า

User Table: เก็บข้อมูลลูกค้า

Product Table: เก็บข้อมูลสินค้า

Join Data ด้วย VLOOKUP

VLOOKUP คือ Function ที่มีทั้งใน Google Sheets และ Excel ใช้เชื่อมข้อมูลแบบ Left Join ผ่าน Unique Key ของทั้งสองตาราง โดยองค์ประกอบของ Function เป็นแบบนี้

SYNTAX

=VLOOKUP(search_key, range, index, [is_sorted])

ตัวอย่างนี้เราจะเชื่อมข้อมูลโดยยึด Order Table เป็นตารางหลักทางซ้ายและให้ User Table เป็นตารางทางขวาและเชื่อมกันด้วย Unique Key ที่ชื่อว่า user_id

ขั้นตอนที่ 1: ให้ Copy หัวตารางของ User Table เข้ามาไว้ใน Order Table

• ขั้นตอนที่ 2: เริ่มเขียน Function VLOOKUP เพื่อดึงค่า Name จากตารางด้านขวา

มาทำความเข้าใจ Function VLOOKUP กันเถอะ

=VLOOKUP(search_key, range, index, [is_sorted])

search_key = Unique key ในตารางทางซ้ายที่ใช้เชื่อมข้อมูลทั้ง 2 ตาราง ซึ่งใน Order Table คือ C2

range = ช่วงข้อมูลที่ต้องการดึงข้อมูลจากตารางทางขวา ซึ่งใน User Table คือ Column A ถึง F

index = คอลัมน์ที่เท่าไหร่จากใน Range ที่ต้องการดึงข้อมูล ซึ่งใน Column Name คือ 2

is_sorted = ใส่เป็น FALSE เพื่อให้เป็น Exactly Match

สามารถศึกษา VLOOKUP เพิ่มเติมได้ ที่นี่

ลองประกอบร่าง VLOOKUP เพื่อดึงข้อมูล Name จาก User Table

=VLOOKUP(C2,user!A:F,2,FALSE)

ทีนี้ถ้าเราอยากจะดึงทุกคอลัมน์ทีเดียวทำยังไงดี? หลายคนก็คิดว่าเราแค่ Copy Function Vlookup แล้วเปลี่ยน Index ไปเรื่อย เช่น

=VLOOKUP(C2,user!A:F,3,FALSE) - สำหรับ email
=VLOOKUP(C2,user!A:F,4,FALSE) - สำหรับ city
=VLOOKUP(C2,user!A:F,5,FALSE) - สำหรับ country
=VLOOKUP(C2,user!A:F,6,FALSE) - สำหรับ registration_date

สูตรโกง VLOOKUP + ArrayFormula

SYNTAX

=ArrayFormula(VLOOKUP(C2,user!A:F,{2,3,4,5,6},FALSE))

ให้คลิกที่ช่องด้านบนที่มี Function อยู่แล้วกด Ctrl + Shift + Enter แล้วคำว่า ArrayFormula จะเติมเข้ามาใน Function VLOOKUP ทันที

จากนั้นเราก็มาเปิด { } เพื่อใส่ Index ตั้งแต่ 2 จนถึง 6 เพื่อดึงข้อมูลทุก Columns มาได้เลย ผลที่จะได้ก็จะเป็นแบบรูปด้านล่าง

เปลี่ยนตรง Search Key เป็น Array Format เพื่อให้ Function นี้ Apply ทุก Rows เท่านี้เราก็ดึงข้อมูลทุก Columns จาก User Table เข้ามายัง Order Table ได้ครบถ้วน!

Join Data ด้วย XLOOKUP

XLOOKUP คือ Function ใน Google Sheets และ Excel ที่ออกแบบมาเพื่อค้นหาข้อมูลในตาราง มีลักษณะคล้ายกับ Left Join เช่นเดียวกัน แต่ใช้งานง่ายและมีความยืดหยุ่นมากกว่า VLOOKUP

SYNTAX

=XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)

ตัวอย่างนี้เราจะเชื่อมข้อมูลโดยยึด Order Table เป็นตารางหลักทางซ้ายและให้ Product Table เป็นตารางทางขวาและเชื่อมกันด้วย Unique Key ที่ชื่อว่า product_id

ขั้นตอนที่ 1: ให้ Copy หัวตารางของ Product Table เข้ามาไว้ใน Order Table

ขั้นตอนที่ 2: เริ่มเขียน Function XLOOKUP เพื่อดึงค่า Product Name จากตารางด้านขวา

มาทำความเข้าใจ Function XLOOKUP กันเถอะ

=XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)

search_key = Unique key ในตารางทางซ้ายที่ใช้เชื่อมข้อมูลทั้ง 2 ตาราง ซึ่งใน Order Table คือ D2

lookup_range = ช่วงของ Unique key หรือ Columnในตารางทางขวาที่ใช้เชื่อมข้อมูลทั้ง 2 ตาราง ซึ่งใน Product Table คือ Column A2:A21

result_range = ช่วงของข้อมูล หรือ Column ที่มีคำตอบที่เราต้องการ ซึ่ง Product Table คือ Column B2:B21

missing_value = ใช้กำหนดค่าที่ต้องการแสดงผลหากไม่สามารถเชื่อมข้อมูลได้

match_mode = รูปแบบในการเชื่อมข้อมูลโดยส่วนมากจะใช้เป็น 0/FALSE ที่หมายถึง Exactly Match

สามารถศึกษา XLOOKUP เพิ่มเติมได้ ที่นี่

ลองประกอบร่าง XLOOKUP เพื่อดึงข้อมูล Product Name จาก Product Table

=XLOOKUP(D2,product!A2:A21,product!B2:B21,FALSE)

ใช้ ArrayFormula + ปรับ Search key ให้เป็น Array Format เพื่อให้สูตร Apply ทุก Rows ใน Column: Product Name

=ArrayFormula(XLOOKUP(D2:D21,product!A2:A21,product!B2:B21,FALSE))

เท่านี้เราก็สามารถดึงข้อมูล Product Name จากตารางทางขวาคือ Product Table เข้ามายังตารางทางซ้ายคือ Order Table ได้ครบทุกแถว ด้วยการใช้ XLOOKUP

ดึงข้อมูล Category, Price, Stock Quantity, Brand จาก Product Table ด้วยการแก้ Result Range จาก Function เดิมนิดหน่อย

SYNTAX

=ArrayFormula(XLOOKUP(D2:D21,product!A2:A21,product!C2:C21,FALSE))
=ArrayFormula(XLOOKUP(D2:D21,product!A2:A21,product!D2:D21,FALSE))
=ArrayFormula(XLOOKUP(D2:D21,product!A2:A21,product!E2:E21,FALSE))
=ArrayFormula(XLOOKUP(D2:D21,product!A2:A21,product!F2:F21,FALSE))

เท่านี้เราก็ดึงข้อมูลจาก Product Table ได้ครบถ้วน

สร้าง Calculated Field เพื่อคำนวน Total Sales

หลังจากการเชื่อมข้อมูลด้วยวิธี VLOOKUP จากตาราง User และ XLOOKUP จากตาราง Product เป็นที่เรียบร้อย แต่ข้อมูลเองก็ยังขาด Total Sales ทำให้ไม่ทราบว่าแต่ละ Order ขายได้เท่าไหร่

เท่านี้เราก็เตรียมข้อมูลเพื่อใช้ในการวิเคราะห์ในขั้นตอนต่อไปเสร็จเรียบร้อยแล้ว

Summary

ใครอ่านมาถึงตรงนี้ต้องบอกว่ามีความตั้งใจมาก ปรบมือให้ตัวเองด้วย ลองมาดูกันดีกว่าว่าเราเรียนรู้อะไรจากบทความนี้ไปแล้วบ้าง

• Data Analytic Workflow
  • เข้าใจกระบวนการและขั้นตอนในการทำงาน Data Analytics เบื้องต้น
• 4 Steps ในการเตรียมข้อมูลเบื้องต้นให้พร้อมใช้
  • Tidy Data (Wide Format to Long Format) ด้วยการใช้ Transposed
  • Remove Duplicates ด้วย Built-in Feature
  • Create Unique Key ด้วย Function IF แบบง่ายๆ
  • Homogeneity ปรับ Date Format สุดปวดหัว ให้อยู่ในรูปแบบ "YYYY-MM-DD""
• Join Data เชื่อมข้อมูลจากหลายแหล่งข้อมูล
  • รูปแบบการ Join Data
  • Join Data ด้วย VLOOKUP + ARRAYFORMULA
  • Join Data ด้วย XLOOKUP + ARRAYFORMULA

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

สร้างบัญชีรายรับ-รายจ่าย และวิเคราะห์ข้อมูลง่ายๆ ด้วย Google Sheets

เราสามารถฝึกเก็บออมเงิน วิเคราะห์รายรับรายจ่ายได้ด้วย Google Sheets เครื่องมือฟรีที่ใครๆ ก็เริ่มทำได้ แจกฟรี Template ไว้ใช้งานได้ทันที

มาลองเรียน - MalonglearnSorakrich Oanmanee

สร้างชีทคิดเงินค่าอาหารทุกงานปาร์ตี้ อย่างเสมอภาคด้วย GoogleSheets

ชีทคิดเงินอย่างเสมอภาคทุกงานเลี้ยง หมดปัญหาการโดนเก็บเงินแบบหารเท่า แจกฟรี Template เอาไปใช้ได้ทันที

มาลองเรียน - MalonglearnSorakrich Oanmanee