Ruby's Duckie

hello ! สวัสดีค่าบ Ruby’s Duckie คนดีคนเดิมค่า ✨ วันนี้มาเคาะ Skill ที่รวมการใช้งาน Google sheet แบบ Basic to Advance ที่เราใช่ในชีวิตในการทำงานมาแจกทุกวัน (จริงๆ เหมือนเป็น Second Brain ของเราเองด้วย ลืมเมื่อไรก็เปิดอ่านได้เลย เย่) โดยบทความนี้จะแบ่งออกเป็นทั้งหมด 4 sprint 🍃 ด้วยกัน เราจะเริ่มจากพื้นฐานง่ายๆ เพื่อให้เข้าใจพื้นฐานของการใช่งานก่อน จากนั้นเราจะเริ่มก้าวไปด้วยกันจนถึง Data visaulization กันเลย ดังนั้นบทความนี้ยาวแน่นอน แต่เพื่อที่จะเวลาเรากลับมาอ่าน เราไม่จำเป็นต้องเลื่อนเยอะๆ ให้เหนื่อยเลย สามารถเลือกจากหัวข้อได้เลยว่าจะไปรื่อฟื้นจากหัวข้อไหน ถ้าพร้อมแล้ว ! เรามาลุยไปด้วยกันเลย เย้ 🙌🏻

🔎 Sprint 1: Foundation & Data Entry

google sheet เป็นเครื่องมือสำหรับการเก็บ + วิเคราะห์ + นำเสนอข้อมูล สามารถทำได้ครบ จบในคนๆ เดียวได้เลย (ข้อแค่มีบัญชี Gmail ก็สามารถใช้ได้เลย ฟรีๆ) เห้ย !

หลายๆคนก็อาจจะมีใช้งานมาแล้วบ้าง เช่น Microsoft Excel etc. ซึ่งเป็นเครื่องมือที่ทำหน้าที่คล้ายกัน แต่ function ที่ใช้จะไม่เหมือนกัน ค่อนข้างต่างกัน แต่จุดเด่นที่ทำให้ Google Sheets เหนือกว่าในยุคนี้เลยก็คือ มันทำงานบน Cloud และแน่นอน !! เราควรใช้งานได้ทั้ง 2 tools เพราะเราเป็น Generalism ! อย่าให้เครื่องมือมาทำให้เราไปต่อไม่ได้ ลุย

🔍 เราสามารถเปิด google sheets ได้เลยแบบไวๆ โดยพิมพ์ sheets.new ในช่อง URL 🌸

User Interface ของ Google sheet จะเห็นว่าเหมือน excel เลย 🔥

เมื่อเปิดหน้าต่าง Google Sheets ขึ้นมา สิ่งสำคัญคือการรู้จักกับ 5 ส่วนหลัก บนหน้าจอ

• Menu Bar เป็นแหล่งรวมคำสั่งทุกอย่าง เช่น File, Edit, View, Insert และ Format ต่างๆ
• Tools bar: ปุ่มลัดสำหรับสั่งงานบ่อยๆ เช่น เปลี่ยนฟอนต์, ใส่สีเซลล์, ปรับเส้นขอบ, จัดกึ่งกลาง หรือใส่สูตร
• Formula Bar : ช่องสีขาวยาวๆ ที่อยู่บนตาราง (fx………) ช่องนี้มีความสำคัญมาก เพราะเป็นจุดที่เราจะใช้พิมพ์สูตรคำนวณและดูข้อความที่ซ่อนอยู่ในเซลล์นั้นๆ
• Workspace : ช่องตารางสี่เหลี่ยมจำนวนมากที่เราเห็น โดยเราจะเรียกแต่ละช่องว่า Cell
  • Column : แนวตั้ง แทนด้วยตัวอักษรภาษาอังกฤษ (A, B, C, …)
  • Row : แนวนอน แทนด้วยตัวเลข (1, 2, 3, …)
• Sheet Tabs : อยู่ล่างสุดของหน้าจอ ใน 1 ไฟล์เราสามารถสร้างแผ่นงานย่อยๆ เพิ่มได้เหมือนสมุดโน้ต โดยกดปุ่ม + ที่มุมล่างซ้าย

ถ้าเราคลิกช่องแรกซ้ายบนสุด เซลล์นั้นจะชื่อว่า A1

Formatting in Google Sheets 🔑

เอาละ หลังจากที่เราได้แผ่น sheet มาแล้วเรามาลองเกี่ยวกับ Formatting กันเลยดีกว่า ให้เราจำไว้ว่าในแต่ละ cell เราจะรู้ว่าข้อมูลที่เราใส่ลงไปเป็น Text หรือ Number ดูได้จากการจัดการข้อความนั้นๆ

• Text จะอยู่ชิดซ้าย
• Number จะอยู่ชิดขวา

💡 ตัวอย่างข้อมูลที่เราสามารถเห็นได้ประจำวัน แล้วเรามีวิธีการตรวจสอบยังไงว่าเป็น Text หรือ Number

เราสามารถใช้สูตร =ISTEXT() เพื่อตรวจสอบว่าเป็น Text หรือไม่ และใช้ =ISNUMBER() เพื่อตรวจสอบว่าเป็น Number ใช่ไหม หากใช้ใน Cell นั้นจะคืนค่าเป็น TRUE กลับมา

💡 โจทย์ง่ายๆ ลองไปตรวจสอบดูสิ้ ว่า Inspection Time เป็น text หรือ number ?

นอกจากนี้เรายังสามารถเอา Text หรือ Number มาต่อกันได้ด้วย & แต่ๆ จำไว้ว่าจะพิมพ์ Text ลงไปตรงๆ จะต้องมี ” “ มาครอบอีกที 🐳

Note : Cell Reference ล็อกยังไง มีวิธีการล็อกแบบไหนบ้าง ?

การอ้างอิง cell (F4) ใน google sheet มีการ lock cell อยู่ 4 แบบ

• A1 คือ ไม่มีการล็อคใดๆ 🙅🏻‍♂️
• $A$1 : ล็อคทั้ง Column + Row 🔒
• A$1 : ล็อกเฉพาะ Row 🔒
• $A1 : ล็อกเฉพาะ Column 🔒

Short Keybord : fn + F4 or F4 ⌨️

สัญลักษณ์ $ อยู่หน้าตัวไหน ตัวนั้นโดนล็อคอยู่กับที่ทันที!
🐻 อยู่หน้า A (ตัวอักษร) = ล็อคคอลัมน์แนวตั้ง
🐻 อยู่หน้า 1 (ตัวเลข) = ล็อคแถวแนวนอน

Data input Array คืออะไร 🌸

ปกติแล้ว เวลาที่ User กรอกข้อมูล แล้วเอามาใช้ต่อบางครั้ง data structure จะเป็น array มาซึ่งก็คือชุด data ที่ถูกป้อนเข้ามาในระบบ และถูกคั่นด้วย (,) หรือเรียกว่าเป็นรุปแบบ array ซึ่งหากถูกแบ่งด้วยคอมม่า = เป็นตัวแบ่ง Column

และหากต้องการให้แบ่งเป็น Column ให้ใช้ (;) เป็นตัวแบ่ง และข้างล่างตัวอย่างแบบ Matrix Array ที่ใช้กันแบบเต็มๆ ข้อสังเกตจะเห็นว่าถ้าเป็น text จะเอา ” ” มาครอบอีกที เย้ 🚀

💡 นักสถิติชอบ data ที่มี Structure มาก เพราะสามารถคำนวนได้ แต่ถ้าเป็น Dimension จะทำได้แค่ Count เท่านั้น 😎

นอกจากนี้เมื่อเรามีข้อมูลแล้ว เรายังสามารถตั้งชื่อให้ table นั้นๆ ได้ด้วย เวลาที่เราจะเรียกใช้เราสามารถ mention ชื่อตารางได้เลย โดยชื่อที่ตั้งจะต้อง..

• ห้ามขึ้นต้นด้วยตัวเลข 🙅🏻
• ห้ามมี Space 🙅🏻
• ควรตั้งชื่อเป็นตัวพิมพ์ใหญ่ดีที่สุด 🆗

💡 ตัวอย่างการเรียกใช้ EMPLOYEE_NAME เพื่อทำการ COUNT ไวๆ ให้เราลองตอบดูว่าถ้าต้องการคำนวน Salary ของ Employee มีเท่าไร ใช่สูตรอะไรมาตอบกันได้ เย่ !

ถ้าหากข้อมูลของเราเป็น Table เราสามารถเรียก column ด้วย [] ได้เลย และยังสามารถเลือกได้หลาย column แต่ต้องเอา Array fomular มาครอบอีกที

Function ง่ายๆ เหมือนเครื่องคิดเลขขนาดใหญ่ 📖

เราใช้ Function หลาย ๆ ตัวมารวมกัน
เพื่อทำการ Data Aggregation เพื่อสรุปภาพรวมข้อมูลให้กลายเป็นรายงาน 🌸

ข้อมูลตัวเลขของเราจริงๆ เราสามารถ บวก ลบ คูณ หารได้ปกติเหมือนเราใช้เครื่องคิดเลขเลย แต่เราสามารถบวก ลบ คูณ หรือหาร ด้วย Function ใน Google sheet ได้เลยไวๆ โดยไม่ต้องบวกทีละตัว เอ้อออ ไวกว่า มีตัวไหนบ้างที่เป็น Basic มาดูกันว่ามีตัวไหนบ้าง

• SUM
• AVERAGE
• COUNT
• COUNTA
• MIN
• MAX
• SUMIF
• COUNTIF

ตัวอย่างข้อมูลที่เราใช้ในวันนี้ เป็นข้อมูล Sales Transection Dataset มาให้ลองทำกันได้ง่ายๆ

สังเกตว่าเวลาที่เราเริ่มพิมพ์ = จะมี formular suggestion ขึ้นมา แนะนำว่าไม่รู้ว่าต้องใช่ยังไง อย่าเพิ่งให้ ai ช่วย ให้เราลองอ่านและดูตัวอย่างการใช่งานก่อน (คงคอนเซบต้องทำได้เองก่อน ค่อยให้ ai ช่วย ! 🔥)

💡 SUM ก็คือการบวกที่เราบวกเลขกันปกติ แต่จะสามารถใช้สูตรนี้แทนการที่เรานั่งกด 1 + 2 + 3 ทีละตัวได้ ป้องกันการลืม และคำนวนผิดด้วย ถ้า data ที่เรากรอกมาไม่ผิดนะ 555+ อะมาดูตัวอย่าง ‘จงรวมยอด Tatal Amount ให้หมด’

-- แทนที่เราจะเอาเลขบวกทีละตัวให้เรา
--ใส่ range ตัวเลขที่เราจะรวมได้เลย
=SUM(H4:H13)

ได้คำตอบเป็น ฿ 6,850.00 ได้เท่ากันไหม ? ง่ายเลยใช่ไหม 🤣 อ่ะไปกันต่อเลยนะ !

💡SUMIF ง่ายๆ ก็คือเป็นการหาผลรวมตามเงือนไขที่เรากำหนด เช่น จงหาผลรวมของยอดขายที่ status complete แล้วว่ามีทั้งหมดกี่บาท ? สังเกตไหมว่าเราตั้งเงือนไขว่า status ที่ completed แล้วเท่านั้น อันอื่นไม่ต้องเอามา !

=SUMIF(I4:I13,"Completed",H4:H13)

จะเห็นว่าตาม syntax คือ ช่วงที่ต้องการตรวจสอบ + เงือนไข + ช่วงตัวเลขที่ต้องการเอามารวม มาต่อที่อีกตัวอย่างที่จะให้ sumif เหมือนกันแต่ให้ไปรวม total amount ของเครื่องใช้ไฟฟ้า Electronics มา

=SUMIF(E4:E13, "Electronics", H4:H13)

ได้คำตอบยังไงเอามาแชร์กันมั้งนะ อิอิ ลุยกันต่อเลยย เย่

💡 SUMIFS เห็นไหมว่า IF มีเติม S แปลว่าเราสามารถรวมได้มากกว่า 1 เงือนไข เห้ย ! ไม่ต้องกด SUM ที่ละหลายๆ ครั้งแล้ว (แอบกระซิบว่า formula อื่นๆก็เหมือนกัน เช่น IFs , COUNTIFS, MAXIFS หรือ MINIFS เป็นต้น สังเกตง่ายๆ เติม s แต่ๆ ถ้าไม่รู้จริงๆ ก็อ่าน ducument เลยว่าใช่ยังไง เย่)

=SUMIFS(H4:H13, E4:E13, "Electronics", I4:I13, "Completed")

จากตัวอย่างข้างบน ตีความง่ายๆคือ จงวิ่งไปหาผลรวม Total Amount ของ Electronics ที่ status completed แล้ว เห็นไหมว่าเรามีมากกว่า 1 เงือนไข คือ Electronic & Completed

• AVERAGE จะคำนวณเฉพาะ number , Interger
• AVERAGEA จะคำนวณ Text + True/False + number/int

การหาเฉลี่ยปกติ หรือ =AVERAGE() หลักการคือการเอาผลรวมทั้งหมดหารด้วยจำนวนทั้งหมด เช่นที่เราได้ 26.67 นั่นหมายถึงเอา 10 + 20 +50 แล้วหารด้วย 3 นั่นเอง แต่ๆ =AVERAGEA() จะเห็นว่า text + true/false ถูกเอาไปคำนวนด้วย เห้ยยย แล้วมันคำนวนยังไง มีประโยชน์อะไร มาดูกัน

1. ค่า TRUE / FALSE จะถูกนับเป็น 1 และ 0
2. text จะคิดเป็น 0 (แต่จะเอาไปนับรวมกับตัวหารด้วย)

จากตัวอย่างก็คืออธิบายได้ว่า =AVERAGEA() เนี่ยเอา 10+20+50+0+0+0+1 ผลลัพธ์ก็เลยจะออกมาเป็น 11.57 ‼ แล้วมันใช่ตอนไหนละ ทำไมต้องมี formula อันนี้ด้วย?

ส่วนใหญ่เราจะใช้ AVERAGEA ร่วมกับค่า TRUE/FALSE ในการ หาเปอร์เซ็นต์ที่สำเร็จ หรือการผ่านของคะแนนสอบ เช่น ตารางเช็คชื่อเข้างาน (ถ้ามาติ๊กถูก = TRUE, ถ้าขาด = FALSE) เป็นต้น ⛰️

COUNT เป็นการนับ n เฉพาะที่เป็น Number แต่ COUNTA นับหมดทั้งตัวเลขทั้งตัวหนังสือ

สังเกตไหมว่า ถ้าเป็น COUNT ที่ไม่มี A จะนับเฉพาะที่เป็นตัวเลข (เพราะเลขสามารถนับได้) เว้นแต่เราสร้างเงือนไขด้วย IF นับหมด ไม่สน Text หรือ Number 🤯 แถมยังตั้งเงือนไขได้อีกว่าต้องการให้นับจำนวนที่มากกว่า น้อยกว่า หรือเท่ากับที่เราต้องการนับ !!!

แต่ๆ ในส่วนของ COUNTIFS นั้น สังเกตว่ามีเครื่องหมาย + เป็นตัวแทนของ OR ซึ่งตามตรรกศาสตร์ ถ้า 2 เงือนไขจะสามารถ return ค่าออกมาให้ได้ (TRUE) นั้นหมายความว่าโจทย์ของข้อนั้นคือ จงไปนับว่าใน Column มีคำว่า Pizza OR Dog กี่คำ เป็นต้น

Min และ Max คือการหาจำนวนที่น้อยที่สุด หรือมากที่สุดตามชื่อของ function เลย ใช้เวลาที่อยากรู้ว่าเร็วๆ ว่า เอ้ะ ! ยอดขายที่มากที่สุดคือเท่าไร หรือ ยอดขายต่ำสุดเท่าไร โดยไม่ต้องไปเขียนสูตรมากมาย

การใช้งานก็สามารถทำได้ง่ายๆเลย โดยเราพิมพ์ =Max(.. จากนั้นตามด้วย Range หรือ Column ที่เราต้องการหาค่าที่มากที่สุด

นอกจากนี้ยังมี =MaxIFs() สังเกตไหมมี IFs อีกแล้ว แสดงว่าเราสามารถกำหนดเงือนไขได้ และแน่นอนว่าเรากำหนดได้มากกว่า 1 Condition

จากตัวอย่างจะเห็นว่า 1,240.00 เป็นการหาค่าที่มากที่สุดที่เป็น Category = ‘Electronics’ อันนี้ในกรณี 1 Condition ในส่วนของ 2 condition ถัดมาเป็นการหาค่า Max ที่หมวด Category = ‘Electronics’ และ Status = “Complete” และแน่นอนในส่วนของ Min ก็สามารถทำได้เช่นกันให้เพื่อนๆ ลองทำกันแล้วมาบอกว่าได้ผลลัพธ์เท่าไรด้วยนะ เย้🥳

📊 Sprint 2: Data Manipulation & Logic

เดินทางมาถึง Sprint2 เก่งขึ้นอีกขั้นแล้ว เนื้อหาใน sprint นี้จะเป็นการจัดการข้อมูล การค้นหาข้อมูลแบบเบื้องต้น (ใช้ในชิวิตประจำวัน) และการจัดการกับวันที่โดยไม่ต้องไปนั่งบวก ลบ ให้ยากอีกต่อไป !!

🔑 Simple IF & IFs function with Array formula !

ข้อดีของการใช้ Array formula มีอะไรบ้าง ทำไมอยากให้เราใช้ให้ติดนิสัย 😆 ?

• ไม่ต้องลากสูตรซ้ำ -> เขียนสูตรแค่เซลล์เดียว ผลลัพธ์จะกระจายเต็มคอลัมน์ ไม่ต้องลาก
• คำนวณข้อมูลใหม่ให้อัตโนมัติ -> มีข้อมูลใหม่ สูตรจะทำงานให้เองทันทีโดยอัตโนมัติ
• ไฟล์ทำงานเร็วขึ้น ไม่หน่วง -> การใช้สูตรเดียวควบคุมทั้งคอลัมน์

Array formula Keys = control + shift + Enter / mac = cmd + shift + enter 🌸

ตารางที่เราจะใช้สำหรับการ practics ! สามารถเลื่อนไปแกะสูตรได้ที่ sheet : Array Formula – IF/IFs

จาก syntax ด้านบนมุมซ้ายที่เราได้ให้ไว้ เรามาลองหาว่าใครที่มี Perfomance Score >= 90 คนนั้นจะได้ Bonus Eligible

=ArrayFormula(IF(F6:F25>=70, "Eligible", "Not Eligible"))

อธิบายง่ายๆ F6:F25 คือช่วงข้อมูลที่เราจะเอามาสร้างเงือนไข โดยเงือนไขคือถ้าช่วงนั้นๆ มีค่ามากกว่าหรือเท่ากับ 70 (>= 70) ให้คืนค่าที่เป็นจริงออกมากนั้นก็ถือ ‘Eligible’ แต่ถ้าไม่ หรือ False ให้ Return คือ ‘Not Eligible’ ออกมา

สังเกตว่าถ้าเราลองไปเปลี่ยนเลข SCORE <90 จะทำให้ Bonus Eligoble เปลี่ยนไปด้วยโดยที่เราไม่ต้องไปเขียนสูตรใหม่อีกรอบ ง่ายมั้ยย ? ถ้าลอทำแล้ว เรามาต่อที่ IFs กันเลย จะเห็นว่ามี s แปลว่ามีหลาย condition ได้ !

โจทย์ก็คือให้จัด Tier A, B, และ C ตาม Perfomance score 🌸 โดย logic คือ ถ้าเงือนไขแรกไม่เป็นจริง ก็ให้เริ่มตรวจสอบเงือนไขที่ 2, 3 ไปเรื่อยๆ

=ARRAYFORMULA(IFS(F6:F25>=80, "Tier A", 
                  F6:F25>=70, "Tier B", 
                  F6:F25<70, "Tier C"))

นอกจากนี้สิ่งที่ Ruby ชอบทำไวๆ หลังจากที่จัด Tier แล้วจะ Filter ต่อทันที เพราะอยากจะเห็นข้อมูลที่เราได้ทำการจัดการ ด้วยฟังก์ชั่น Filter & Sort มาดูกันเลยว่าทำได้ยังไง

🚀 Filter & Sort data !!

Filter function : =FILTER(range, condition1, [condition2, …])
Sort function : =SORT(range, sort_column, is_ascending)

จาก Syntax ด้านบนสามารถเข้าใจได้ง่ายเลย เช่น Filter คือการที่เราไปเลือกช่วงข้อมูลที่เราต้องการ Filter (range) จากนั้นให้ใส่ condition ที่ต้องการ Filter จากตัวอย่างนี้คือเราจะเลือกเฉพาะ Bonus = Eligilble ผลที่ได้คือเราจะได้ช่วงข้อมูลที่เราต้องการมาให้เราได้เลย เหมือนการ filter ปกติแบบไม่เขียนสูตร จากนั้นหากเราต้องการให้มันเรียงลำดับ Monthly Salary ที่เรา filter มาให้เรียงจากมากไปน้อย ก็สามารถทำได้โดยการ Sort ข้อมูลที่เรา Filter มา !

=SORT(FILTER(EMPLOYEE_DATA,
             BONUS_ELIGIBLE = "Eligible")
      ,6,TRUE)

* ข้อควรระวัง : ช่วงที่เรา Filter ต้องมี Raw ที่เท่ากัน ไม่งั้นผลไม่ออก ! 🐻

สูตรด้านบนจะเห็นว่าเราได้ไป Filter = EMPOYEE_DATA คือชื่อของข้อมูลที่เราตั้งเอาไว้ นอกจากนี้เรายังสามารถตั้งชื่อ Colomn ได้เหมือนกัน ซึ่ง Ruby ก็ได้ตั้งเป็นชื่อ BONUS_ELIGIBLE หลังจาก Filter มาแล้วก็จะโดน SORT() แถวที่ 6 ซึ่งก็คือแถวของ Monthly Salary

• TRUE = Defult ของ Function โดยจะเรียงจากน้อยไปมาก (⬆️ Ascending)
• FALSE = มากไปน้อย (⬇️ Descending)

🐻 UPPER, LOWER, CONCATENATE

เป็นการปรับ Format ของ Text ใน Cell ว่าอยากจะให้ออกมาในรูปแบบไหน มาเริ่มทำไปด้วยกันที่ UPPER&LOWER CASE

• URRER() = ปรับให้เป็นตัวพิมพ์ใหญ่ทั้งหมด
• LOWER() = ปรับให้เป็นตัวพิมพ์เล็กทั้งหมด

🟢 =ArrayFormula(UPPER(B2:B11)) 
🟡 =ArrayFormula(LOWER(D2:D11)) 

ข้อสังเกต : เห็นไหมว่าเราใช้ ArrayFormula มาช่วยให้รันออกไวๆ ภายใน cell เดียว ไม่ต้อง copy วาง !😎🔥

• CONCATENATE() = เลือก range มาต่อกันเลยดื้อๆ
• LEFT() = ดึงเฉพาะอักษรทางด้านซ้าย กี่ตัวก็สามารถกำหนดได้
• RIGHT() = เหมือน LEFT() แต่ดึงขวา

=CONCATENATE(F2," ",G2) # ใช้ " " มาเพื่อเว้นระยะห่าง
=ArrayFormula(LEFT(F2:F11,1)) # เลข 1 หมายถึงดึงมาแค่ 1 ตัวแรก
=ArrayFormula(RIGHT(G2:G11,2)) # เลข 2 หมายถึงดึงมา 2 ตัวหลัง

🌸 easy concate with ‘&’ = ต่อได้หลากหลายทั้ง Text & number

เราสามารถเอาข้อความหรือตัวเลขมาต่อกับข้อมูลใน cell ได้เลย เพียงแต่ว่าText ต้องโดนครอบด้วย (“TEXT”) ถึงจะมาสามารถเชือมกับข้อมูลในเซลล์ได้

="My name is "&B2&", i am "&D2&" at " &+ UPPER(E2)

🐳 AND , OR, NOT like a Logic

เราจะใช้ AND, OR, NOT เป็นตัวดำเนินการ หรือ Logical Operators โดยจะใช้ TRUE/FALSE สำหรับเป็นตัวตั้งเงือนไข

ตาราง Truth Table 📖

*หมายเหตุ : ใน ( ) ให้ใส่ช่วงข้อมูลที่เราต้องการตรวจสอบ

วิธีการใช้งานจะเห็นว่าได้เขียน formula ไว้ให้ในตารางแล้ว อยากให้เพื่อนๆลองตรวจสอบดูว่าแต่ละอันได้คำตอบยังไงบ้าง เช่น =AND(A2:B2) ได้คำตอบเป็น TRUE หรือ FALSE

🐶 SWICH() when you found something?

  =SWITCH(expression, value1, result1, [Optional])

รู้หรือไม่? =SWICH( ) สามารถใช้แทน =IFs() ได้เลย ซึ่ง syntax คล้ายกับ IFs แหละ ประมาณว่าถ้าเจอข้อมูล A ในตาราง A1 ให้ Switch เป็นอะไรก็ตามที่เราตั้งเอาไว้ เดียวเรามาลองดูตัวอย่างกันดีกว่าว่ามันมีความคล้ายหรือต่างกันยังไง

โดยเราจะมี 2 table อันแรกจะบอกว่าแต่ละคนมี Contries code อะไร และอีกอันบอกว่าแต่ละ code มีความหมายว่าอะไร

เปรียบเทียบการ switch vs IFs สำหรับแปลความหมายว่ารายชื่อ 10 รายนั้นทำงานที่ประเทศอะไรบ้าง โดยตามเงื่อนไขของ IFs นั้นจะเป็นการใช้ TRUE / FALSE ในการแปลข้อมูล ง่ายๆ ถ้าข้อมูลที่ไปหามาเป็นจริง ก็ให้ Return ค่านั้นออกมา

ข้อสังเกต จะเห็นว่าถ้า IFs จะต้องกวาดช่วงข้อมูลที่ต้องการหาหลายรอบ ในขณะที่ SWITCH() เป็นการระบุช่วงที่ต้องการรอบเดียว จากนั้นถ้าเจอก็ SWITCH เป็นอะไรก็ตามที่เราตั้งไว้ แต่ยังไงทั้งคู่ก็ได้ผลลัพธ์ที่เหมือนกัน แล้วแต่ว่าเราเลือกใช้แบบไหน เย่

⛰️ How to Manage DATE(), DateDIF() and DAY()

วิธีการจัดการกับวัน (date)ง่ายๆ ไม่ต้อง copy วางทีละช่อง ข้อมูลมาเป็นแบบไหน Google Sheet ก็สามารถ จัดการได้ง่ายๆเลยยย เย้

• Date() เป็นตัวช่วยประกอบร่างวันที่แบบไวๆ ได้เลย มาดูตัวอย่างๆ

syntax คือ Date(year, month, day) จากตารางหลังจากที่เราลองใส่สูตรเพื่อประกอบร่างของวันจะได้ 1990-02-30, 1982-09-31 และ 1995-06-01 ถ้าต้องการแยกออกจากกันก็สามารถใช้ =DAY() สำหรับแยกวันที่ออกมาได้เลย ลองเดากันดูว่าถ้าอยากจะแยกเดือน หรือปีล่ะ ? ต้องใช้ function อะไร 🐶

=ArrayFormula(DATE(A2:A3,A2:A3,A2:A3))

• DATEDIF() ตามชื่อเลย คือการหาส่วนต่างวันที่ เดือน หรือปี ขึ้นอยู่กับว่าจะตั้งค่า unit เป็นอะไร และนี่คือ syntax =DATEDIF(start_date, end_date, unit) ตัวอย่างการหา Dif ของ YEAR ว่าต่างกันเท่าไร ( แม้ format จะต่างกัน แต่ก็ยังสามารถใช้ function นี้ได้เหมือนกัน เฉียบบบบบ 💡)

=ArrayFormula(DATEDIF(A2:A3,A2:A3,"Y"))

Tips : ถ้าอยากให้นับ Month = “M”, DAY = “D” 🌸

📈 Sprint 3: Advanced Functions & Data Linking

🔥 Vlookup / Xlookup function สกิลที่มีติดตัวแล้วจะงานไวแบบปุปปับ

เป็นการค้นหาข้อมูลจากหลายตารางในแนวตั้ง แล้วดึงข้อมูลมาแสดงผลได้เลย โดยนำค่าที่เทียบหรือ Ref ไปเทียบกับคอลัมน์แรกสุดของตาราง ! ส่วนของ Xlookup() เป็นอันใหม่ที่ใช้ง่ายว่าและสะดวกกว่ามาก อะมาดู syntax กันว่ามีอะไรบ้าง

=VLOOKUP(lookup_value, table_array, col_index_num,
         [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, 
         [if_not_found])

ทั้ง 2 function คิดง่ายๆเลยว่า จงไปหาข้อมูลที่เลือก (lookup_value) ในอีกตาราง แล้ว return col (vlookup) หรือ return_array (xlookup) มาให้เราถ้าเจอ ถ้าไม่เจอจะขึ้น N/A ข้อดีของ vlookup คือสามารถเลือกให้ return มาให้เราได้หลาย column แต่ xlookup return ให้ได้แค่ค่าเดียว หรือ column เดียว ! แต่ xlookup ใช้ง่ายและไวกว่ามากๆๆๆ มาดูตัวอย่างกัน

💡 ข้อสังเกต : Vlookup จะเห็นว่าเป็นคลุมทั้งตารางเพื่อหา B12 แล้วให้ return column index ที่ 2 มาแต่ๆๆๆ มีง่ายกว่านั้นคือ Xlookup คือการหา C12 ใน Column $A$3:$A$7 แล้ว Return ค่า $D$3:$D$7 มาให้

ใช้ Index and Match สำหรับเป็นเครื่องมือค้นหาแบบ Dynamic ง่ายๆ 🔑

Index() สำหรับหาว่า raw ที่ x และ Column ที่ y จุดตัดมันคือค่าอะไร เช่นๆ ดูจากตัวอย่างตารางด้านล่างนี้

เราอยากจะหาว่าแถมที่ 4 ใน column ที่ C มันคือค่าอะไรนะ เราจะสามารถหาได้ง่ายๆ โดยใช้สูตร =INDEX(range, [raw], [column])💡

/* นับแถว column จากซ้ายไปขวา */
= INDEX(A1:D6, 4,3) 

Match() เป็นเครื่องมือที่เราสามารถใช้สำหรับหาตำแหน่งของข้อมูลว่าอยู่ Index ที่เท่าไร เช่น เราอยากรู้ว่า Unit Price อยู่ตำแน่งไหนก็สามารถหาได้จาก syntax =MATCH("search", range)

= MATCH("Unit Price", A1:D6) /* คำตอบที่ได้คือ Index ที่ 4 */

⛰️ ลองมาสร้าง Mini search 🔍 โดยการใช้ index + match กัน เย่ โดย tab bar ไว้สำหรับหาว่า ID นี้ คืออะไรและราคาเท่าไร โดยเราสามารถเอาทั้ง 2 function มา combind เข้าด้วยกัน Insert drop-down ไว้สำหรับว่า สินค้าแต่ละ ID มีราคาเท่าไร หรือสามารถเลือกชื่อ column ใหม่ได้ว่าเป็นสินค้าประเภทไหน เป็นไง ง่ายไหม

🐑 Query function ที่เหมือน SQL ยังกับแกะ แทบไม่ต้องเรียนรู้เพิ่ม แถมง่าย !!!

อันดับแรก ให้เราตั้งชื่อให้กับข้อมูลของเราก่อน สามารถทำได้ไวๆ โดยการกด cmd + A ให้คลุมทั้งข้อมูลแล้วตั้งชื่อด้วย Data > Named ranges

• Select คล้ายๆ กับการ Filter ข้อมูลเอาขึ้นมาดู โดยการเลือก column A, B, C, D หรือตามที่เราที่ต้องการได้เลย มาดูตัวอย่างกัน (เดียวเราจะใช้ตัวอย่างข้อมูลนี้ในการฝึกหัวข้ออื่นๆด้วยเลย ชื่อตารางคือ IMDB)

ให้เราลอง Select ข้อมูล Genre & IMDb Rating ซึ่งอยู่ในตาราง D และ F และการเพิ่ม limit เป็นการบอกว่าให้เอามาแค่ 3 แถวพอ !

=QUERY(IMDB,"SELECT D, F limit 3", true) 
/* True คือให้ดึงหัวชื่อ column มาด้วย*/

• Filter data with Query เป็นการเลือก Filter มาเฉพาะบางอย่างที่เราต้องการโดยเราใช้ where ในการ filter เหมือนใน SQL เลยไหม ง่ายจนงง 😮 ซึ่งๆ จากตัวอย่างเนี่ย ให้ไปหาว่าหนังเรื่องอะไรบ้างที่ Release ในปี 2000 ขึ้นไป 👍🏻

=QUERY(IMDB,"SELECT B, D, where D > 2000, true)

• Filter Null จะเหมือนกับก่อนหน้าเลย ไว้สำหรับ Clean Data ก่อนที่เราจะเอามาใช้ง่ายโดยปรับ where D is NOT null -> เพิ่ม NOT null เข้าไปได้เลย หรืออยากเห็นค่าว่างตรงไหนจะลบ NOT ออกก็ได้ 👍🏻
• Filter Date -> ถ้าจำได้ DATE = isNUMBER ใช่ไหม ? แสดงว่าเราก็สามารถ filter หา DATE ได้เหมือนกัน ในตัวอย่างไม่มีข้อมูล date แต่ลองดูจากสูตรข้างล่างว่าเข้าใจไหม คือเราสามารถใส่ date ที่ต้องการหาไปได้เลย เฉียบบบบบ !

=QUERY(IMDB, "SELECT B, D WHERE D > date '2026-11-11'", true)

• LIKE – Pattern Matching การหาข้อมูลโดยใช้ pattern ในการหาโดยใช้ LIKE operator ใน WHERE เพื่อค้นหา
  • % ใช้ match หลายๆ character
  • _ ใช้ match character ที่เฉพาะเจาะจงไปเลย

=QUERY(IMDB, "SELECT C, D WHERE D LIKE 'Drama')
=QUERY(IMDB, "SELECT C, D WHERE D LIKE 'Dra_a')
=QUERY(IMDB, "SELECT C, D WHERE D LIKE '%m%')

• Aggregate Function with Query จะเป็นฟีลเหมือนเครื่องคิดเลขขนาดเล็กๆ (ที่ทำได้เยอะมาก) มีอะไรบ้างไปดูกัน 🌸
  • avg()
  • Sum()
  • count()
  • max()
  • Min()

=QUERY(IMDB, "SELECT avg(F), avg(G) 
LABEL avg(F) 'Avg Rating', avg(G) 'Avg Runtime'", 1)

=QUERY(IMDB, "SELECT min(C), max(C) 
LABEL min(C) 'Earliest Year', max(C) 'Latest Year'", 1)

เพื่อนๆ ลองทำกันดูนะ เดียวแปะโจทย์ไว้ให้ ๆๆๆ คือ ให้หาผลรวม Total Vote ทั้งหมดมาสิ้ ว่ามีเท่าไร ได้คำตอบแล้วอย่าลืมมาคอมเม้นต์กันด้วยนะ !!

• Group by & Order by เป็นการ Group อะไรก็ตามที่อยู่ใน Category เดียวกัน และ Order by คือการจัดการข้อมูลให้เรียงจากมากไปน้อย (Asc) หรือน้อยไปมาก (Desc) นั้นเอง

=QUERY(IMDB, "SELECT E, count(B), avg(F), sum(H) 
GROUP BY E ORDER BY avg(F) DESC LABEL count(B) 'Movies',
avg(F) 'Avg Rating', sum(H) 'Total Votes'", 1)

• Pivot table เป็นการหมุนข้อมูลแหละ เพื่อจัดกลุ่มข้อมูลพร้อมกับคำนวณแบบไวๆ ได้

=QUERY(IMDB, "SELECT D, sum(H) 
       WHERE D = 'Crime, Drama' 
GROUP BY D", 1)

=QUERY(IMDB, "SELECT D, sum(H) 
       WHERE D = 'Crime, Drama' 
GROUP BY D PIVOT C", 1)

🎨 Sprint 4: Data Visualization & Reporting

ข้อมูลที่เราจะเอามาลองทำอยู่ใน tab ที่มีชื่อว่า Retail Sales Data

🌸 Basic Charts & Graph

มาดูใน Google sheets เราสามารถทำ graph แบบไหนได้บ้าง ค่อนข้างเยอะ และเพียงพอมากๆ ในการนำไปนำเสนอออ เย่

ป้ายบอกทาง ๆ ให้เราคลุมตาราง -> Insert -> Chart 📊 ง่ายมากกกกกก งงเลย

⛰️ Line Chart เหมาะกับข้อมูลที่เป็นตัวเลขคู่กับเวลามากๆๆ ไว้ดูการเปลี่ยนแปลงแบบต่อเนื่อง มาลองดูตัวอย่างแรก จะเป็น Monthly Revenue and Profit Trends (2024) เราสามารถทำได้โดย Step = Ctrl + A and insert Pivot table มันจะเด้งว่าเราจะ pivot ตรง working sheets เลย หรือจะ new sheets ไปเลยก็ได้ 🤓

หลังจากที่เราได้ตารางมาแล้ว ให้เราครอบตารางที่เรา pivot มาแล้ว Insert > Chart แล้วเลือก Line Chart จะเห็นว่าเมื่อรายได้เพิ่มขึ้น กำไรก็จะเพิ่มขึ้นตาม และเมื่อรายได้ลดลง กำไรก็ปรับตัวลดลงตามเช่นกัน

• รายได้และกำไรทำจุดสูงสุดครั้งแรกในเดือน กุมภาพันธ์ (Feb)
• เกิดการดิ่งลงอย่างหนักสองช่วงหลัก คือเดือน เมษายน (Apr)

มาตัวอย่างต่อไปแบบไวๆ คือการดู Profit ที่ได้ของแต่ละ Product กัน ! ขั้นตอนเหมือนกันเลอออ แต่ๆ การเลือก data มาลงในการ pivot อาจจะต้องคิดนิดนึ่งว่าเราอยากได้อะไรร !

⛰️ Bar Chart จะไว้สำหรับ number ที่ถูกแยกๆ เพื่อเปรียบเทียบของแต่ละ Category เช่นในตัวอย่าง Total Profit by Product Category เราจะเลือก Product Category ใส่ใน Rows และ Profit ใส่ใน Values

ในส่วนของกราฟนั้นเราสามารถแก้ไขได้ หรือเปลี่ยนแปลง style ได้ตามความเหมาะสม โดยจำไว้ว่ากราฟที่ดีคือ กราฟที่ user มองแล้วสามารถเข้าใจได้เลย ง่ายๆไม่ต้องอธิบายเยอะ อยากให้เพื่อนๆ ลองไปทำ Data Visualization ของตัวเองดูโดยใช้ข้อมูล Retail Sale Data แล้วอย่าลืมส่งมาอวดๆๆ กันบ้างนะค่าบบบ 😆

🫵🏻 Fast DashBoard on One Click 🚀

ตอนนี้ใน google sheets มี feature ใหม่ๆๆๆ เย่ เป็น Sheets Canvas ที่ใช้ Gemini AI เข้ามาช่วยเปลี่ยนข้อมูลใน Google Sheets ให้กลายเป็นแดชบอร์ดคล้ายๆ ในเว็บเลยย เห่ยยยย นี่ทำให้ google sheets จบได้เลยยนะเนี่ย ! 🤯

Promt ที่ใช้ ถ้าเอาง่ายแบบไม่คิดอะไรเลยก็ ‘Create dashboard of Retail Sale data’ แต่ๆ เราควรที่จะคิดนิดนึ่งว่าเราต้องการอะไรบ้าง เพื่อให้ออกมาใน one shot โดยที่เราไม่ต้อง burn credit เพิ่ม 🔥

ตัวอย่างคร่าวๆ ที่ได้มา ลองไปเล่นๆๆ

สามารถไปดู Dashboard เต็มๆ ได้ที่ Retail Sales Executive Dashboard

copy แล้วไปฝึกทำได้เลย : https://docs.google.com/spreadsheets/d/1QS7VnBUfSqKP3crgcAdr-uIIdX_GPFnrIUjUuRGL__A/copy

เป็นยังไงกันบ้างทุกคนนนน long form มากกก อยากให้ทุกคนลองไปเล่นเยอะๆ จริงๆ google sheets ทำอะไรได้เยอะมากจริงๆๆๆ วันนี้ Ruby’s duckie ของจบเท่านี้ก่อน ถ้ามี part 2 มาเดียวสะกิดๆๆๆๆๆ เย้ ขอบคุณมากๆเลยที่อ่านจนจบ หวังว่าบทความนี้จะเป็นประโยชน์ต่อเพื่อนๆ พี่ๆ น้องๆ สามารถ support กาแฟเพื่อเป้นกำลังใจให้กันได้น๊าาา อิอิ วันนี้ Ruby’s Duckie ขอลาไปก่อน ไว้เจอกันใหม่ เย๊ ! 🌸🤚🏻