สอนสร้าง Habit Tracker ด้วย Excel 365
สวัสดีค่ะทุกคน ขอแนะนำตัวก่อนนะคะ ชื่อ วิลาวัลย์ ผดุงผล ชื่อเล่น ปังปอนด์ หรือเรียกสั้น ๆ ว่าปอนด์ก็ได้ค่ะ ปอนด์ได้มีโอกาสเข้าร่วมแข่งขัน MOS Olympic Thailand Competition 2024 จนได้เป็นตัวแทนเยาวชนไทยเข้าร่วมการแข่งขัน Microsoft Excel 365 จากเวที MOS World Championship 2024 ที่ประเทศสหรัฐอเมริกา และได้รับรางวัลอันดับที่ 5 กลับมาค่ะ
ประกาศนียบัตรรางวัลอันดับ 5 การแข่งขัน Microsoft Excel 365
ที่จริงแล้วก่อนที่ปอนด์จะได้เริ่มต้นการเดินทางกับ Excel อย่างจริงจัง ก็เคยมองว่า Excel เป็นแค่โปรแกรมสเปรดชีตโปรแกรมนึงเท่านั้น แต่หลังจากเรียนรู้ไปเรื่อย ๆ ตอนนี้ปอนด์สามารถใช้ Excel VBA ในการสร้างโปรแกรมอัตโนมัติสำหรับงานที่ซับซ้อน ซึ่งช่วยทำให้ประหยัดเวลาการทำงานไปได้หลายชั่วโมงเลยค่ะ และปอนด์เชื่อว่าทุกคนที่กำลังอ่านบทความนี้อยู่ก็สามารถทำเหมือนปอนด์ได้เช่นกันนะคะ!
มาลองดูกันดีกว่าว่าเราจะเริ่มต้นเปลี่ยนโปรแกรมที่ดูยากและน่าเบื่อนี้ ให้กลายเป็นเรื่องสนุกและนำมาใช้ประโยชน์กับงานของเราได้ยังไงบ้าง
เรื่องราวและประสบการณ์กับ Excel
ปอนด์ขอเริ่มด้วยประสบการณ์ส่วนตัวก่อนนะคะ
จุดเริ่มต้นของการได้ใช้ Excel จริง ๆ ก็เริ่มมาตั้งแต่เด็ก ๆ ในวิชาคอมพิวเตอร์แล้วค่ะ แต่ปอนด์รู้สึกว่าเป็นโปรแกรมที่น่าเบื่อจริง ๆ ทั้งดูยากและซับซ้อนเลยไม่ค่อยได้ใช้งานเท่าไหร่ แต่ก็...หนีไม่พ้นค่ะ เพราะพอเข้ามหาวิทยาลัย ปอนด์เลือกเรียนสายคอมพิวเตอร์ซะด้วยสิ ยังไงก็หนีไม่พ้นโปรแกรม basic พวกนี้หรอกค่ะ ไม่ว่าจะเป็น Word หรือ PowerPoint หรือ Excel
ตอนแรกที่ได้มาเจอ Excel อีกครั้งก็ยังรู้สึกไม่ชอบเหมือนเดิมนั่นแหละค่ะ ฮ่า ๆ แต่จุดเปลี่ยนที่ทำให้เริ่มรู้สึกชอบและสนุกกับโปรแกรมนี้คือตอนที่อาจารย์ในคณะแนะนำให้รู้จักกับโปรแกรมการแข่งขันเกี่ยวกับ Microsoft Office ที่รู้จักกันในชื่อ MOS Olympic Thailand Competition 2024 ที่ทางบริษัทเออาร์ไอที ได้จัดขึ้น อาจารย์ให้ปอนด์เลือกว่าอยากแข่งโปรแกรมไหน ปอนด์เห็นว่าเพื่อนคนอื่นเลือก Word กับ PowerPoint ไปแล้ว ปอนด์เลยทำการเลือก Excel ซะเลยค่ะ มา! ลองดูกันอีกสักตั้ง
จุดนี้นี่แหละที่ทำให้ปอนด์ได้เห็นความสามารถของ Excel แบบเต็ม ๆ เพราะหลังจากนั้นมาก็ตั้งใจเรียนรู้ใหม่ทั้งหมดเลยค่ะ และด้วยความพยายามอย่างเต็มที่เลยทำให้สามารถคว้าแชมป์เวทีประเทศไทย และคว้าที่ 5 ระดับโลกในโปรแกรม Microsoft Excel 365 มาได้
The Power of Excel in Everyday Life
พอมาถึงตรงนี้แล้ว ทุกคนคงอยากรู้แล้วใช่ไหมคะว่า Excel ทำอะไรได้บ้าง จะหยิบมาใช้ยังไงให้เกิดประโยชน์มากที่สุดในชีวิตประจำวัน และนี่คือ Habit Tracker ที่สร้างขึ้นด้วย Excel นำมาออกแบบและตกแต่งให้ดูน่ารักมากขึ้น ซึ่งนอกจากจะดูน่ารักแล้วยังใช้ติดตามกิจวัตรประจำวันของเราได้จริง ๆ
ตัวอย่าง Habit Tracker ที่สร้างขึ้นด้วย Excel
พอเห็นแบบนี้แล้วบางคนอาจจะคิดว่าต้องยากแน่ ๆ เลย แต่จริง ๆ แล้ว นี่คือการรวมตัวกันของสูตรพื้นฐานใน Excelเลย (ตัวอย่างนี้อ้างอิงการใช้งานบน Excel 365 สำหรับใครที่ใช้เวอร์ชันอื่น ๆ อาจจะใช้งานสูตรบางสูตรไม่ได้นะคะ) และก่อนที่เราจะไปดูกันว่าใช้สูตรอะไรบ้าง เรามาดูวิธีการ ตั้งชื่อช่วงของเซลล์ให้ทำงานง่ายขึ้นกันก่อนค่ะ
ข้อดีของการตั้งชื่อช่วงของเซลล์มีอะไรบ้าง แล้วทำไมต้องทำด้วย?
- ทำให้สูตรอ่านง่าย และเข้าใจได้ง่ายขึ้น
- ต้องมาคอยกังวลว่าต้องล็อกเซลล์หรือเปล่า เพราะการตั้งชื่อช่วงของเซลล์ทำให้ที่อยู่ที่จะอ้างอิงเป็น Absolute หรือเทียบเท่ากับการใส่เครื่องหมาย $ ก่อนชื่อคอลัมน์และแถวนั่นเอง (จะเห็นได้ว่าช่วยลดความสับสนได้เยอะเลย แถมอ่านง่ายขึ้นอีกด้วยนะ)
ปอนด์จะเพิ่ม Sheet เปล่าขึ้นมาหนึ่งอัน แล้วตั้งชื่อว่า Icons นะคะ ในชีทนี้จะนำไอคอนที่เราใช้เป็นตัววัดระดับแบบน่ารัก ๆ มาใส่ไว้ (ปอนด์ได้ inspiration มาจากเกม Stardew Valley ค่ะ ติดงอมแงมมาก) ถ้าดูจากคอลัมน์ B จะเห็นได้ว่าใน Cell สีเขียวฝั่งซ้ายสุดจะกำหนดให้เป็นชื่อดอกไม้ และ Cell ฝั่งขวาเป็นต้นไป จะไล่ลำดับของดอกไม้ให้โตตามเปอร์เซ็นต์ไปเลยค่ะ
เมื่อวางภาพเสร็จแล้วให้เราตั้งชื่อช่วงของเซลล์ โดยจะตั้งชื่อเป็น “Stage 1” ไปจนถึง “Stage 5” และส่วนที่เป็นชื่อของดอกไม้ เราจะเลือกชื่อดอกไม้ทั้งหมดแล้วตั้งชื่อกลุ่มนี้ว่า “Plants_Name” ค่ะ
ตั้งชื่อช่วงของเซลล์เพื่อให้เข้าใจง่ายขึ้น
วิธีการตั้งชื่อช่วงของเซลล์ง่าย ๆ เลยคือ ให้เราเลือกช่วงของเซลล์ด้วยการลากคลุมส่วนที่ต้องการก่อน หลังจากนั้นให้ไปจิ้มที่ Name Box แล้วทำการเปลี่ยนชื่อได้เลยค่ะ เพียงเท่านี้ข้อมูลของเราก็พร้อมที่จะนำไปใช้ในสูตรแล้วค่ะ
เตรียมพร้อมสำหรับการประกอบร่าง!
กลับมาที่ชีท Sheet1 กันค่ะ เริ่มแรกเลยให้วาง layout หรือ สามารถออกแบบได้ตามใจชอบเลยค่ะว่าอยากได้แบบไหน (การใส่ check box จะมีให้ใช้งานใน version 365 นะคะ)
1. ฟังก์ชันแรกที่จะใช้คือ SEQUENCE (ใช้ได้ตั้งแต่ version 2021 ขึ้นไปนะคะ) เราจะใช้ฟังก์ชันนี้ในการสร้างลำดับของตัวเลขที่อยู่ข้างหน้า Habits และใช้สร้างวันที่ในเดือนค่ะ
Syntax ของฟังก์ชัน: =SEQUENCE(rows,[columns],[start],[step])
เข้าใจแบบง่าย ๆ คือ: =SEQUENCE(จำนวนแถวที่ต้องการสร้างลำดับ,จำนวนคอลัมน์ที่ต้องการสร้างลำดับ,ตัวเลขเริ่มต้น,ระยะห่างระหว่างตัวเลขแต่ละตัว)
อันแรกคือลำดับตัวเลขที่อยู่หน้า Habits ซึ่งจะเรียงลำดับลงไปในแนวดิ่ง หรือ rows เราจะทำการสร้างลำดับ 10 ลำดับ ตามที่ได้กำหนด Habits เอาไว้
- ตำแหน่ง rows ในสูตรจะใส่เป็นเลข 10 (เพราะมี 10 แถว)
- ตำแหน่ง columns ในสูตรให้เว้นว่างเอาไว้ (เพราะไม่ต้องการ run ตัวเลขที่คอลัมน์)
- ตำแหน่ง start ในสูตรจะใส่เป็นเลข 1 (เพราะเราต้องการที่จะเริ่มจากเลข 1 ค่ะ)
ดังนั้น สูตรที่เราจะได้ คือ =SEQUENCE(10,,1)
ต่อมาเป็นการรัน “วันที่” ในเดือน ซึ่งเราจะไม่ใส่ตัวเลขที่ตำแหน่ง rows นะคะ เพราะว่าเราต้องการจะสร้างลำดับตัวเลขไล่เป็นแนวนอน หรือไล่ลำดับไปตาม columns
- ตำแหน่ง rows ในสูตรให้เว้นว่างเอาไว้ (เพราะไม่ต้องการ run ตัวเลขที่แถว)
- ตำแหน่ง columns ในสูตรจะใส่เป็นเลข 31 (ตามจำนวนวันของหนึ่งเดือน)
- ตำแหน่ง start ในสูตรจะใส่เป็นเลข 1 (เพราะต้องการเริ่มนับจากวันที่ 1 ของเดือน)
ดังนั้น สูตรที่เราจะได้ คือ =SEQUENCE(,31,1)
2. ฟังก์ชันต่อมาคือ COUNTIF ค่ะ เราจะใช้สำหรับนับจำนวนที่ตรงตามเงื่อนไขที่ตั้งไว้ กรณีนี้คือใช้นับจำนวนว่า Habit แต่ละอัน ใน 1 เดือน ทำสำเร็จไปทั้งหมดกี่ครั้ง ถ้ามีการติ๊กถูก ผลลัพธ์ของเซลล์นั้นจะเป็น TRUE และค่าพื้นฐานของช่อง Check box ที่ไม่ถูกติ๊ก จะถูกนับเป็น FALSE เพราะฉะนั้นเราจะทำการนับจำนวน TRUE เท่านั้นค่ะ
Syntax ของฟังก์ชัน: =COUNTIF(range, criteria)
เข้าใจแบบง่าย ๆ คือ: =COUNTIF(ช่วงของเซลล์ที่ต้องการตรวจสอบ, เงื่อนไข)
ส่วนแรกที่จะใช้ฟังก์ชันนี้คือ “คอลัมน์ Completed” เพื่อนับว่าทำ Habit นี้ไปกี่ครั้งใน 1 เดือน
ดังนั้น สูตรที่เราจะได้ คือ =COUNTIF(D4:AH4, TRUE)
หมายความว่า เราจะทำการนับข้อมูลในคอลัมน์ D ไปจนถึงคอลัมน์ AH ที่ได้ใส่ check box เอาไว้ ว่ามีการติ๊กถูก (ค่า TRUE) เท่านั้น เป็นจำนวนกี่ครั้ง
3. ตำแหน่งต่อมาที่จะใช้ฟังก์ชัน คือ ใช้หาเปอร์เซ็นต์ว่าในจำนวน Habit ทั้งหมดที่ตั้งไว้ เราได้ทำไปทั้งหมดกี่ Habit ภายใน 1 วัน ซึ่งจะมีส่วนเกี่ยวข้องกับการโตของดอกไม้ แต่ก่อนจะทำการหาเปอร์เซ็นต์ เราต้องทำการนับก่อนนะคะว่าจำนวนของ Habit ทั้งหมดมีกี่อย่าง โดยใช้ฟังก์ชัน COUNTA ค่ะ เอาไว้ใช้นับทุกอย่างรวมทั้งตัวอักษรและตัวเลขทั้งหมดที่ไม่ใช่ช่องว่าง และใส่ค่าไว้ในเซลล์ C14
ดังนั้น สูตรที่เราจะได้ คือ =COUNTA(C4:C13)
วิธีการหาเปอร์เซ็นต์ให้ใช้หลักการเดียวกับการนับจำนวนที่ตรงตามเงื่อนไขเลยค่ะ คือ เราจะทำการนับ check box ที่มีการติ๊กถูกเท่านั้น ซึ่งมีค่าเป็น TRUE หลังจากนั้นให้หารด้วยจำนวนของ Habit ทั้งหมด (เซลล์ C14 ที่ใช้สูตร COUNTA นับเอาไว้) แล้วทำการเปลี่ยนช่วงเซลล์ที่แสดงผลลัพธ์ให้อยู่ในรูปแบบของเปอร์เซ็นต์
ดังนั้น สูตรที่เราจะได้ คือ =COUNTIF(D4:D13,TRUE)/$C$14
เปลี่ยนรูปแบบแสดงผลลัพธ์ : ไปที่แถบเมนู Home ในกรอบคำสั่ง Number เลือกรูปแบบเป็น Percentage
4. ต่อมาเราจะแสดงรูปหัวใจที่อยู่ในคอลัมน์ Progress กันค่ะ โดยการใช้ฟังก์ชัน IFS ซึ่งจะกำหนดกฎไว้ว่า ถ้าทำได้มากกว่า 80% จะได้ไปเลย 5 หัวใจ แสดงว่าเราทำได้ดีมากค่ะ
Syntax ของฟังก์ชัน: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)
เข้าใจแบบง่าย ๆ คือ: =IFS(เงื่อนไข 1, ค่าถ้าเงื่อนไข 1 เป็นจริง, เงื่อนไข 2, ค่าถ้าเงื่อนไข 2 เป็นจริง, …)
เราจะทำการอ้างอิงไปยังเปอร์เซ็นต์ของ Completed นะคะ ส่วนการหาเปอร์เซ็นต์คือเอาตัวเลขที่ได้ในคอลัมน์ Completed หารด้วยจำนวนวันในเดือนนั้น ๆ แล้วทำการเปลี่ยนช่วงเซลล์ที่แสดงผลลัพธ์เป็นรูปแบบเปอร์เซ็นต์
มาดูสูตรของเรากันค่ะว่าจะทำให้มีหัวใจแสดงขึ้นได้ยังไง ก่อนอื่นต้องตั้งเกณฑ์ของเราขึ้นมาก่อนค่ะ ปอนด์จะกำหนดให้เป็น ดังนี้
- กำหนดค่า 0% = ไม่มีหัวใจ
- กำหนดค่า 20% = หัวใจ 1 ดวง
- กำหนดค่า 40% = หัวใจ 2 ดวง
- กำหนดค่า 60% = หัวใจ 3 ดวง
- กำหนดค่า 80% = หัวใจ 4 ดวง
- นอกเหนือจากเกณฑ์นี้ (หรือก็คือ มากกว่า 80% นั่นเอง) = หัวใจ 5 ดวง
ดังนั้น สูตรที่เราจะได้ คือ =IFS(AJ4=0%, "",AJ4<=20%, "♡", AJ4<=40%, "♡♡", AJ4<=60%, "♡♡♡", AJ4<=80%, "♡♡♡♡", TRUE, "♡♡♡♡♡")
5. และส่วนสำคัญที่สุดใน Habit Tracker นี้เลยก็คือ การเติบโตของดอกไม้นั่นเอง! หลังจากที่เราได้ทำการคิดเปอร์เซ็นต์ไว้เรียบร้อยแล้วว่าภายใน 1 วัน เราได้ทำไปกี่ Habit และจำนวนที่ทำได้เหล่านั้นคิดเป็นกี่เปอร์เซ็นต์ ทีนี้ก็ถึงเวลานำรูปดอกไม้ที่อยู่ในชีท Icons มาใช้งานกันค่ะ โดยใช้ฟังก์ชันนี้มีชื่อว่า XLOOKUP เราจะหยิบมาทำงานร่วมกับฟังก์ชัน IFS เพื่อให้ได้ผลลัพธ์ออกมาตามรูปนี้ค่ะ
Syntax ของฟังก์ชัน: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
เข้าใจแบบง่าย ๆ คือ: =XLOOKUP(ค่าที่ต้องการค้นหา, ช่วงที่จะค้นหา, ช่วงที่จะส่งคืนค่า, ค่าที่จะส่งคืนถ้าไม่พบผลลัพธ์, โหมดการจับคู่, โหมดการค้นหา)
ทำไมต้องใช้ XLOOKUP แค่ใช้ IFS ก็น่าจะพอแล้วนะ?
สาเหตุที่เราต้องใช้ XLOOKUP ก็เพราะว่าเรามีดอกไม้อยู่หลายชนิด แต่เราอยากให้แสดงผลตามชนิดของดอกไม้ที่เราเลือกเท่านั้น ซึ่งปอนด์จะกำหนดเซลล์ 1 เซลล์ขึ้นมา สำหรับใช้เลือกชื่อดอกไม้จากทั้ง 5 ชนิด ที่เคยใสไว้ในชีท Icon ด้วยวิธี Drop-Down List ค่ะ
สำหรับคนที่ไม่รู้วิธีการสร้าง Drop-Down List วิธีการทำง่ายมาก ๆ เลยค่ะ มาลองดูกัน
ให้ทำการเลือกเซลล์ก่อนค่ะว่าอยากให้ผลลัพธ์แสดงตรงเซลล์ไหน สมมติให้เป็นเซลล์ C15 แล้วกันค่ะ หลังจากนั้นให้ไปที่แถบเมนู Data แล้วเลือกคำสั่งที่มีชื่อว่า Data Validation และทำการตั้งค่าตามนี้ได้เลยค่ะ
- ช่อง Allow เลือกเป็น List
- ติ๊กเลือกที่ Ignore blank และ In-cell dropdown
- ช่อง Source เลือกเซลล์ที่ต้องการ ซึ่งเราได้ทำการตั้งชื่อไว้ในขั้นตอนแรกแล้ว ในช่องนี้จึงจะแสดงผลเป็น =Plants_Name
ต่อไป มาดูไส้ในของสูตรกันก่อนนะคะ เดี๋ยวเราค่อยเอาไปประกอบร่างกันทีหลัง ไส้ในของเราคือ IFS ค่ะ เพื่อที่จะหาว่าตอนนี้ต้นไม้ของเราอยู่ Stage ไหน
พอรู้แล้วว่าดอกไม้ของเราอยู่ Stage ไหน เราจะมาทำการหาว่าชนิดดอกไม้ของเราคือชนิดไหน โดยใช้ฟังก์ชัน XLOOKUP
เราจะทำการหาชื่อดอกไม้ใน Drop-Down List ในช่วงเซลล์ที่มีชื่อว่า Plants_Name หรือก็คือช่วงชื่อของดอกไม้ที่เราได้ตั้งไว้ตอนแรกนั่นเองค่ะ ส่วนช่วงที่จะคืนค่าก็คือ Stage ของดอกไม้ตามเปอร์เซ็นต์ และนี่คือสูตรแบบเต็ม ๆ ค่ะ
ของแถมเรื่อง Excel VBA เอาไว้ใช้เคลียร์ check box
เราสามารถเขียนโค้ดเพื่อใช้เคลียร์ check box ให้เอาเครื่องหมายติ๊กถูกออก ตรงนี้ไม่จำเป็นต้องเข้าใจแบบ 100% ก็ได้นะคะ เดี๋ยวเราจะมาพูดคุยกันเรื่องนี้อีกทีค่ะ
อธิบายโค้ดแบบง่าย ๆ ก็คือ เราจะทำการตั้งชื่อ subroutine ของเราว่า ClearCheckBox ส่วนความหมายของ ActiveSheet หมายความว่าชีทที่เรากำลังเปิดอยู่ และ Range คือช่วงของเซลล์นั่นเองค่ะ
ในที่นี้ปอนด์ได้ทำการคลุมช่วงของเซลล์ที่มี check box แล้วตั้งชื่อว่า Checkbox ที่อยู่ถัดมาจาก Range คือ .Value คือการเข้าถึงค่าของเซลล์ในช่วง ๆ นั้น และหลังจากนั้นมีเครื่องหมายเท่ากับ ที่จะมีความหมายโดยรวมว่า เราต้องการจะตั้งค่าช่วงเซลล์ที่มีชื่อว่า “Checkbox” ให้เป็น FALSE หรือก็คือการเอาเครื่องหมายติ๊กถูกออกทั้งหมดนั่นเองค่ะ
นี่คือไฟล์ Habit Tracker ให้ทุกคนไปลองเล่นดูนะคะ
(ต้องเปิดบน Excel 365 นะคะ จึงจะเห็นข้อมูลครบทั้งหมด)
เป็นยังไงบ้างคะ จาก Microsoft Excel ที่ดูเป็นโปรแกรม spreadsheet ธรรมดา เราสามารถออกแบบทำอะไรได้หลายแบบจริง ๆ ค่ะ ปอนด์หวังว่าทุกคนจะนำสิ่งที่ได้รู้นี้ไปประยุกต์ใช้ในด้านอื่น ๆ นะคะ ยังมีสิ่งที่น่าสนใจอื่น ๆ ที่ Excel ทำได้อีกนะคะ ในบทความต่อไปเราจะมาดูกันค่ะว่า Excel จะเอาไปใช้ในการทำงานยังไงให้เราทำงานได้เร็วและง่ายขึ้นมากกว่าเดิม