Category: Spreadsheets
-
เขียน Nested Conditions แบบคูลๆด้วยฟังชั่น IF IFS และ XLOOKUP
บทความนี้แอดมาสอนเขียนฟังชั่น IF IFS และ XLOOKUP ใน Excel เพื่อสร้างโปรแกรมตัดเกรดนักเรียนง่ายๆ (nested conditions) มีทั้งหมด 6 เกรดตั้งแต่ A-F โดยเกณฑ์การตัดเกรดนักเรียนเป็นดังนี้ ดาวน์โหลดไฟล์ตัวอย่าง Excel สำหรับบทความนี้ได้ที่นี่ ข้อมูลคะแนนนักเรียนอยู่ในคอลัมน์ A ส่วนเกรดที่คำนวณจากฟังชั่น IF IFS และ XLOOKUP อยู่ในคอลัมน์ B C D ตามลำดับ IF (column B) มาเริ่มกันที่ฟังชั่นที่มีมานานที่สุดใน Excel คือฟังชั่น IF ที่มี syntax การเขียนง่ายๆแบบนี้ =IF(condition, TRUE, FALSE) ถ้าเราต้องการเขียนเงื่อนไขซ้อนกันหลายๆชั้น ก็สามารถทำได้ด้วยการเขียน nested IF แบบนี้ IF(IF(IF … ))) ซึ่งเป็นวิธีที่เขียนสูตรที่ยุ่งยากและมีโอกาสผิดสูง (i.e. error prone) […]
-
XLOOKUP ฟังชั่นใหม่ของ Excel ที่เกิดมาแทน VLOOKUP
XLOOKUP คือฟังชั่นใหม่ของ Excel เปิดตัวเมื่อเดือนสิงหาคม 2019 เพื่อแทนที่ฟังชั่น VLOOKUP และ HLOOKUP เขียนง่ายกว่าเดิม แถมมีลูกเล่นมากขึ้น เช่น การดึงข้อมูลจากหลายๆคอลัมน์พร้อมกัน และ search_mode เพื่อให้ฟังชั่นทำงานเร็วขึ้น บทความนี้แอดมารีวิววิธีการใช้งานฟังชั่นนี้ด้วย Excel Office 365 ดาวน์โหลดตัวอย่าง EPL table สำหรับบทความนี้ได้ที่นี่ VLOOKUP สมัยก่อนถ้าเรารู้ชื่อทีมฟุตบอล (Team = “Manchester United”) และต้องการ lookup คะแนนล่าสุดของทีมที่เราต้องการ (Points = ?) เราต้องเขียนฟังชั่น VLOOKUP ตามตัวอย่างด้านล่าง เลข 9 ในสูตรคือตำแหน่งของคอลัมน์ที่เราต้องการดึงค่าออกมา (column index) ส่วน FALSE คือการดึงค่าแบบ exact match แปลว่าในตาราง B2:J21 คอลัมน์ B ต้องมีคำว่า “Manchester […]
-
วิธีจัดการ Missing Value ง่ายๆด้วยโปรแกรม Excel
บทความที่แล้ว แอดอธิบายปัญหา outliers และการทำ outlier detection ไปแล้ว วันนี้เราจะมาลองดูการทำความสะอาด missing values ใน Excel กันบ้าง ดาวน์โหลดไฟล์ตัวอย่างได้ที่นี่ ฟังชั่นหลักที่ data analyst ใช้นับจำนวน/ ตรวจหา missing values ใน Excel คือ COUNTBLANK() และ ISBLANK() ตามลำดับ สังเกตในสูตรคำว่า “blank” หมายถึง cell ว่างใน worksheet นั้นๆ ตรวจสอบ Missing Value เปิดไฟล์ตัวอย่างขึ้นมา ข้อมูลที่มี missing values จะอยู่ที่ cell A3:E12 เราสามารถใช้ฟังชั่น COUNTBLANK() เพื่อนับจำนวน missing values ในแต่ละคอลัมน์ ลองดูสูตรที่แอดเขียนไว้ใน cell A15:E15 รู้จักกับค่า […]
-
วิธีตรวจจับ Outliers ในข้อมูลง่ายๆด้วย Boxplot และ IQR
หน้าที่สำคัญของ data analyst คือการทำความสะอาดข้อมูลให้พร้อมสำหรับทำงาน โดยปัญหาหลักๆของ data ที่เราเจอบ่อยๆคือเรื่อง missing values และ outliers บทความนี้เราจะอธิบายว่า outliers คืออะไร และแนะนำเทคนิคในการ flag และ remove ค่า outliers ด้วยโปรแกรม Excel ง่ายๆใน 4 ขั้นตอน ดาวน์โหลดไฟล์ตัวอย่าง Excel สำหรับบทความนี้ได้ที่นี่ Outlier คืออะไร Outlier (มีหลายชื่อ เช่น anomaly, extreme value) คือ data points ที่มีค่าสูงหรือต่ำกว่า data points ส่วนใหญ่ในชุดข้อมูลหนึ่งๆอย่างมาก เช่น ส่วนสูง 210 cm คือค่า outlier สำหรับคนไทย เพราะเราแทบไม่เคยเห็นคนไทยสูงเกิน 2 เมตรเลย เป็นต้น อ้างอิงสถิติจากเว็บไซต์ […]
-
ภาษา R พื้นฐานสำหรับ Excel Users
R เป็นหนึ่งในภาษาที่ได้รับความนิยมสูงมากสำหรับงาน data science เรียนรู้ง่าย ทำงานได้รวดเร็ว เกิดมาพร้อมกับความสามารถด้านสถิติ (ภาษาอังกฤษ เราเรียก R ว่า fast data crunching language) R เหมาะสำหรับคนที่ใช้ Excel ในการทำงานอยู่แล้ว ถ้าอยากจะเริ่มเขียนโปรแกรม แอดแนะนำ R เป็นภาษาแรกเพราะ logic ในการทำงานของเครื่องมือสองตัวนี้คล้ายกันมาก (การเรียกใช้งานฟังชั่นเหมือนกันเกือบ 100%) บทความนี้จะเปรียบเทียบความเหมือน/ แตกต่างของทั้งสองโปรแกรม และอธิบายการทำงานเบื้องต้นของ R ให้กับ Excel users ได้เข้าใจ ไม่ต้องเคยเขียนโปรแกรมมาก่อน ก็อ่านรู้เรื่อง ทำตามได้เลย 😛 Getting Software สำหรับเพื่อนๆที่อยากทำตาม tutorial วันนี้ต้องติดตั้งโปรแกรมฟรีสองตัวคือ R และ RStudio Desktop ติดตั้งเสร็จแล้วเปิดโปรแกรม RStudio ขึ้นมา และเริ่มเขียนโค้ดในหน้าต่าง console ตามตัวอย่างด้านล่างได้เลย นักเรียนสามารถดาวน์โหลดไฟล์ […]
-
รู้จักกับ Analysis Toolpak เครื่องมือลับสำหรับงานสถิติใน Excel
ถ้าบอกว่า Excel มี add-in ที่ช่วยเราวิเคราะห์ผลสถิติได้ถึง 15 โมเดลแบบไม่ต้องเขียนสูตรแถมใช้งานฟรีด้วย มีอยู่จริงหรอเครื่องมือดีๆแบบนี้? ตอบเลยว่ามี รู้งี้ใช้นานแล้ว !! วันนี้เราจะแนะนำวิธีใช้งาน Analysis Toolpak เบื้องต้น ลองสร้าง correlation matrix และ linear regression ทำนายราคาบ้านง่ายๆ สำหรับเพื่อนๆที่อยากทำตาม tutorial สามารถโหลดไฟล์ตัวอย่างได้ที่นี่ Activate Add-in วิธีเรียกใช้งาน Analysis Toolpak ให้ไปที่ File -> Options แล้วเลือก Add-ins ตามรูปด้านล่าง เสร็จแล้วจะมีไอคอน <Data Analysis> โผล่ขึ้นมาที่แท๊บ Data พร้อมให้เราใช้งานทางด้านขวาสุดของหน้าจอ Know Your Dataset ขั้นตอนแรกก่อนที่เราจะเริ่มทำ data analysis คือการทำความเข้าใจข้อมูลของเราก่อน สิ่งที่เราควรรู้เกี่ยวกับ dataset มี 4 ข้อ […]