Chayanonboo

10 Ideas from Google Sheets 101 เพื่อให้สามารถจัดการข้อมูลให้เป็นระเบียบมากขึ้น

Written by

Chayanon Boonthanom

in

,

Teached by Kasidis Satangmongkol (DataRockie Owner)

💕💕💕 หลังจากได้เรียน Google Sheets 101 จาก Data Science Bootcamp 11 จึงอยากสรุปความรู้เป็นบทความเพื่อพัฒนาความเข้าใจให้มากขึ้นสำหรับ Google Sheet คือโปรแกรม Spreadsheet ที่ใช้งานได้ฟรี ทำให้สามารถสร้าง แก้ไข และทำงานร่วมกับผู้อื่นได้แบบ Real-time

  1. Why Google Sheets?
  2. Data Types
    1. Type of Data
    2. Type of Category Data
    3. Punctuation
  3. Array Formula
    1. Horizontal Structure
    2. Vertical Structure
    3. Matrix Structure
  4. Cell References
  5. Function
  6. And or Not
  7. Conditions
    1. Condition term
    2. IF
    3. NESTED IF
    4. IFS
    5. SWITCH
  8. Aggregate Function
  9. Aggregate function with Conditions
    1. SUMIFS
  10. Filter and Sort
    1. Filter
      1. Filter by hand
      2. Filter with and, one condition, or
    2. SORT

Why Google Sheets?

ปกติควรใช้ Spreadsheets tool ในการเก็บ วิเคราะห์ และนำเสนอข้อมูล

Spreadsheet Method
Spreadsheet MethodDefinition
Storeเก็บรวมข้อมูล (Structure Data)
Analyzeทำความสะอาดและวิเคราะห์ข้อมูล
Presentนำเสนอ insights ที่มีประโยชน์ต่อแบรนด์/ เรื่องที่เราสนใจ

Data Types

Type of Data

Data Types
Data TypeDefinition
Numericตัวเลข
Stringตัวอักษร
BooleanTrue or False
Dateวันที่

Foundation Date : YYYY-MM-DD

Type of Category Data

TypeDefinition
Dimensionประเภทของข้อมูลที่เป็น Category
Measurementระเภทของข้อมูลที่เป็น Numeric

Explain the different between dimension and measurement in a table

dimension and measurement

Punctuation

Punctuation
TypePunctuationDefinition
Double quote” “use for message
Ampersand&to connect 2 message

Array Formula

มีไว้กรณีสามารถพิมพ์คำสั่งครั้งเดียวแล้วได้ผลลัพธ์ หลายๆค่าพร้อมกัน

Arrayformula Shortcut : CTRL + SHIFT + ENTER

TypeDefinitionPunctation
Horizontal Structureข้อมูลเรียงแนวนอน,
Vertical Structureข้อมูลเรียงแนวตั้ง;
Metrix Structureข้อมูลเรียงแนวนอนก่อน แล้วแสดงผลแนวตั้งต่อ, ;

Horizontal Structure

A7={1, 2, 3, 4, 5}

--------------------------------
จะแสดงผลในแนวนอนดังนี้ 1 2 3 4 5

Vertical Structure

A9={"Toy"; "Anna"; "John"; "David"}
--------------------------------
Toy แสดงผลในแนวตั้ง
Anna
John
David

Matrix Structure

={100, 200; 300, 400; 500, 600} 
----------------------- 
100 200
300 400
500 600 
แสดงผลในแนวนอนก่อน แล้วหลังจากจะแสดงผลแนวตั้งต่อจากแนวนอน
Results from Three Types of Array Formulas

Cell References

การ lock cell สามารถทำได้โดยการกด Press F4 to lock cell

Shortcut TypeDefinition
A1ไม่มีการ lock cell ใดๆ เลย
$A$1Absolute formula จะไม่มีการเลื่อนแถวเลย
A$1ปล่อยฟรี column A lock only row 1
$A1lock only column A ปล่อยฟรี row 1

Method by I2 = %percent ที่เงินเดือนของพนักงานแต่ละคนจะเพิ่มขึ้น

Sample of lock cell

Function

  • หน้าที่ของ Function การเปลี่ยน input ให้กลายเป็น output
Input to Output
function_name(arg1,arg2,[arg3],[arg4])
Required Argument
Optional Argument
TypeDefinition
Require Argumentตัวแปรที่จำเป็น ต้องมีสูตรถึงจะใช้ได้
Optional Argumentตัวแปรที่ไม่จำเป็นต้องมี เป็นแค่ ตัวแปรเสริม

And or Not

Array Function ไม่สามารถใช้กับ AND, OR, NOT

TypeDefinition
ANDจะแสดงค่าจริงต่อเมื่อ 2 เงื่อนไขเป็นจริง
ORจะแสดงค่าจริงต่อเมื่อ 1 เงื่อนไขเป็นจริง
NOTจะแสดงค่าไม่จริง
Prepare AND, OR, NOT

Conditions

TypeCondition
IFแสดงผลลัพธ์แบบ 1 เงือนไข
IFSแสดงผลลัพธ์ IF ซ้อน IF
NESTED IFSผลลัพธ์แบบมี IFS ตัวเดียวโดยทำอ่านง่าย
SWITCHใส่ Range Condition แค่ 1 รอบง่าย

Condition term

SalarySegment
≥100KHigh
≥50KMedium
ElseLow

IF

IF(F2:F6>=100000,"High","Low")

NESTED IF

ArrayFormula(IF(F2:F6>=100000,"High",IF(F2:F6>=50000,"Medium","Low")))

IFS

ArrayFormula(IFS(F2:F6>=100000,"High",
F2:F6>=50000,"Medium",
F2:F6<50000,"Low"))
NESTED IF VS IFS

NESTED IF vs IFS

QualityNESTED IFIFS
PatternIF ซ้อน IFสูตรสั้นกว่า
Approachสูตรอ่านยากเมื่อมีหลายเงื่อนไขอ่านสูตรง่ายกว่า
  1. SEGMENT : ถ้า column F ≥ 100,000 ให้แสดง “High”, Column F ≥ 50,000 ให้แสดง “Medium” นอกนั้นแสดง “Low”
  2. SEGMENT (IFS) : จะแยกเงื่อนไขพร้อมกันทีเดียวเลย

SWITCH

  • สามารถหาความสัมพันธ์ระหว่าง 2 ตาราง แล้วใส่ Range Condition แค่ 1 รอบทำให้สูตรสามารถอ่านได้ง่าย

SWITCH vs IFS

TypeRange
SWITCHมี Range Condition 1 รอบ Column G
IFSมี Range Condition หลายรอบ Column G
---SWITCH---
ArrayFormula(SWITCH(G2:G6,
1, "London",
2, "Madrid",
3, "Tokyo"))
---IFS----
ArrayFormula(IFS(G2:G6=1,"London",
G2:G6=2,"Madrid",
G2:G6=3,"Tokyo"))

SWITCH หาก column G = 1 show “London”, = 2 show “Madrid”,= 3 show “Tokyo”

Switch vs IFS

Aggregate Function

Aggregate FunctionDefinition
SUMผลรวม
AVGค่าเฉลี่ย
MINค่าต่ำสุด
MAXค่าสูงสุด
COUNTAนับจำนวน cell ที่มีข้อมูล
COUNTนับจำนวน cell ที่เป็นตัวเลข
MEDIANค่ากลาง
Aggregate Function

Aggregate function with Conditions

COUNTIFS

Countifs
TypeDefinition
Criteria_range1ช่วงที่ใช้ในการหานับค่า
Criterionตัวเลขหรือข้อความที่ต้องการหานับค่า

Requirement : เราสามารถสรุปผลของข้อมูลเบื้องต้นโดยการนับจำนวน Brand หรือ Model ที่เราต้องการ

G3=COUNTIFS(C:C,"AMD",D:D,"Ryzen 5")       
ค้นหา "Brand AMD" ที่อยู่ Column C และ "Model Ryzen 5" ที่อยู่ Column D
G4=COUNTIFS(C:C,"AMD",E:E,">300")         
ค้นหา "Brand AMD" ที่อยู่ Column C และ Column E ที่มี "Quantity > 300"
G5=COUNTIFS(C:C,"AMD",B:B,"<2021-06-05")  
ค้นหา "Brand AMD" ที่อยู่ Column C และ Column B ที่มี "Date < 2021-06-05"
G6=COUNTIFS(C:C,"AMD",B:B,">=2021-06-06") 
ค้นหา "Brand AMD" ที่อยู่ Column C และ Column B ที่มี "Date >= 2021-06-06"
G7=COUNTIFS(C:C,"AMD",B:B,"<2021-06-06")  
ค้นหา "Brand AMD" ที่อยู่ Column C และ Column B ที่มี "Date < 2021-06-06"
COUNTIFS Formula

SUMIFS

SUMIFS
TypeDefinition
Criteria_range1ช่วงที่ใช้ในการหารวมผลลัพธ์
Criterionตัวเลขหรือข้อความที่ต้องการหารวมผลลัพธ์

Requirement : เราสามารถ หาผลรวม Brand and Model ว่ามี Quantity เท่าไร

SUMIFS Formula
G3=SUMIFS(E:E, C:C, "AMD", D:D, "Ryzen 5")   
ค้นหาช่วง Column E ที่มี "Brand AMD" อยู่ Column C และ "Model Ryzen 5" ที่อยู่ Column D 
G4=SUMIFS(E:E, C:C, "inteL", D:D, "Core i5") 
ค้นหาช่วง Column E ที่มี "Brand Intel" อยู่ Column C และ "Model Core i5" ที่อยู่ Column D
G5=SUMIFS(E:E, C:C, "inteL", D:D, "Core i7") 
ค้นหาช่วง Column E ที่มี "Brand Intel" อยู่ Column C และ "Model RCore i7" ที่อยู่ Column D

ตัวอักษรตัวเล็กกับตัวใหญ่ สามารถค้นหาได้เหมือนกัน ต้องระวัง

Filter and Sort

Filter

Filter เพื่อดึงเฉพาะข้อมูลที่ต้องการนำมาใช้ในการทำงาน “โดยดึงมาทีเดียวพร้อมกันทุก Column”

Filter by hand

  • press filter in google sheet เลือก Filter by Condition —> Date is before —> Exact Date —> “2021-06-06”
Filter with hand

Filter with and, one condition, or

Filter with brand “AMD” only
=FILTER(A3:E22,C3:C22="AMD") 
เลือกช่วงทั้งหมด พร้อม Column Brand ที่มี "AMD"
Filter AND
=FILTER(A3:E22,C3:C22="AMD",D3:D22="Ryzen 5") 
เลือกช่วงทั้งหมด พร้อม Column Brand ที่มี "AMD" และ Column Model "Ryzen 5"
Filter OR
=FILTER(A3:E22, (C3:C22="AMD") + (D3:D22="Core i7")) 
เลือกช่วงทั้งหมด พร้อม Column Brand ที่มี "AMD" หรือ Column Model "Ryzen 5"

SORT

SORT FORMULA =SORT(range, sort_column, is_ascending)

is_ascending = True or False

TypeOrder
FALSEเรียงจาก มากไปน้อย
TRUEเรียงจาก น้อยไปมาก
Sort with one condition
=SORT(A3:E22,3,TRUE)
เรียงข้อมูล Column ที่ A3 ถึง E22 โดยที่ Column Model เป็น "Column ที่ 3" ซึ่งเรียงจาก"มากไปน้อย"
Sort with two condition
=SORT(A3:E22,3,TRUE,5,FALSE)
เรียงข้อมูล Column ที่ A3 ถึง E22 โดยที่ Column Brand เป็น Column ที่ 3 ซึ่งเรียงอักษรจาก"น้อยไปมาก" และ Column Quantity เป็น Column ที่ 5 ซึ่งเรียงจาก"มากไปน้อย"

หวังว่า บทความนี้จะเป็นประโยชน์ในการช่วยให้ทุกท่านสามารถนำความรู้จาก Google sheet 101 ในไปประยุกต์ใช้ในชีวิตส่วนตัวและชีวิตการทำงานที่สามารถทำให้ทุกท่านทำงานได้รวดเร็วและสะดวกยิ่งขึ้นครับ

Comments

Leave a comment

More posts