Spreadsheet – Chayanonboo

Teached by Kasidis Satangmongkol (DataRockie Owner)

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

Why Google Sheets?

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

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

Data Types

Type of Data

Data Type	Definition
Numeric	ตัวเลข
String	ตัวอักษร
Boolean	True or False
Date	วันที่

Foundation Date : YYYY-MM-DD

Type of Category Data

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

Explain the different between dimension and measurement in a table

Punctuation

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

Array Formula

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

Arrayformula Shortcut : CTRL + SHIFT + ENTER

Type	Definition	Punctation
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 Type	Definition
A1	ไม่มีการ lock cell ใดๆ เลย
$A$1	Absolute formula จะไม่มีการเลื่อนแถวเลย
A$1	ปล่อยฟรี column A lock only row 1
$A1	lock only column A ปล่อยฟรี row 1

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

Function

หน้าที่ของ Function การเปลี่ยน input ให้กลายเป็น output

function_name(arg1,arg2,[arg3],[arg4])
Required Argument
Optional Argument

Type	Definition
Require Argument	ตัวแปรที่จำเป็น ต้องมีสูตรถึงจะใช้ได้
Optional Argument	ตัวแปรที่ไม่จำเป็นต้องมี เป็นแค่ ตัวแปรเสริม

And or Not

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

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

Conditions

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

Condition term

Salary	Segment
≥100K	High
≥50K	Medium
Else	Low

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

Quality	NESTED IF	IFS
Pattern	IF ซ้อน IF	สูตรสั้นกว่า
Approach	สูตรอ่านยากเมื่อมีหลายเงื่อนไข	อ่านสูตรง่ายกว่า

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

SWITCH

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

SWITCH vs IFS

Type	Range
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”

Aggregate Function

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

Aggregate function with Conditions

COUNTIFS

Countifs

Type	Definition
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"

SUMIFS

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

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

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 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

Type	Order
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 ในไปประยุกต์ใช้ในชีวิตส่วนตัวและชีวิตการทำงานที่สามารถทำให้ทุกท่านทำงานได้รวดเร็วและสะดวกยิ่งขึ้นครับ

Tag: Spreadsheet

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