[Fundamental Data Analytics & Data Scientist EP.03] รวมสูตร Excel ที่ใช้โคตรบ่อย!!!
“เรียนรู้สูตร Excel ด้วยการลองเป็น Data Analyst ที่ IBM กันดีกว่า”
มาถึงบทความที่สามกันแล้วสำหรับซีรี่ย์ Fundamental Data Analytics & Data Scientist ในสองตอนที่แล้วเราได้อธิบายถึงอาชีพที่มีความสำคัญและมีความเกี่ยวข้องกับข้อมูลนั้นคือ Data Scientist และ Data Analytics ซึ่งถ้าได้อ่านบทความก่อนหน้านี้แล้ว จะเห็นว่า “การทำความสะอาดข้อมูล (Clean Data)” เป็นขั้นตอนที่สำคัญและเป็นขั้นตอนที่ใช้เวลานาน ดังนั้นเรื่องของ “การจัดการข้อมูล (Data Manipulation)” เพื่อให้ข้อมูลดังกล่าวสามารถนำไปทำความสะอาดหรือนำไปใช้งานได้ง่ายก็เป็นสิ่งสำคัญ
ตอนที่แล้ว —
[Fundamental Data Analytics & Data Scientist EP.01] Data Scientist ในไทยมัน Sexy ขนาดนั้นเลยหรอ!!!
โปรแกรมที่ใช้สำหรับการจัดการข้อมูลที่ง่ายที่สุดนั้นก็คือ Microsoft Excel ของเรานั้นเอง !!!
ดังนั้น วันนี้เราจะมาแนะนำสูตร Excel ที่ใช้บ่อยมากสำหรับการจัดการข้อมูลกัน
Briefly
Function ที่ใช้บ่อยๆในการจัดการข้อมูลด้วย Excel นั้นคือ
ข้อมูลที่ใช้
ใช้ข้อมูล IBM HR Analytics Employee Attrition & Performance ซึ่งเป็นชุดข้อมูลเดียวกันกับตอนที่แล้วนะครับ จะได้ไม่เสียเวลามากในการทำความเข้าใจนะครับ
เอาหล่ะ…ถ้าพร้อมแล้ว “เรามาสวมบทบาทเป็น Data Analyst ที่ IBM กันดีกว่า”
UNIQUE — แสดงผลข้อมูลที่ไม่ซ้ำกันเลย
Boss: “เอ…ข้อมูลดูยากจังเลย…ทำสรุปให้หน่อยว่าบริษัทเรามีกี่ตำแหน่ง (JobRole) ?”
We: “ได้ครับบอส”
หากต้องการดูว่าข้อมูลมีทั้งหมดกี่ประเภท หรือมีข้อมูลที่ไม่ซ้ำกันเราจะใช้สูตร
=UNIQUE(เซลล์ที่ต้องการเช็คข้อมูลซ้ำ)
ซึ่งในกรณีนี้เราใช้สูตร =UNIQUE(M2:M1471) จะได้ตำแหน่งงานทั้งหมดที่มีในบริษัท เท่ากับ 9 ตำแหน่งนั้นเอง
COUNTIF — นับจำนวนของข้อมูลตามเกณฑ์ที่เราตั้งไว้
Boss: “โอเค…แล้วแต่ละตำแหน่ง (JobRole) มีกี่คนหล่ะ ?”
เนื่องจากคราวที่แล้วเราใช้ UNIQUE ในการหาตำแหน่งที่มีทั้งหมดของบริษัทไปแล้ว ขั้นตอนต่อไปก็คือเราต้องเช็คจำนวนพนักงานของแต่ละตำแหน่ง สูตรต่อไปที่เราจะใช้ก็คือ
=COUNTIF(เซลล์ที่ต้องการเช็คข้อมูล, เซลล์ที่ใช้เป็นเกณฑ์)
เราก็ใช้สูตร =COUNTIF(M2:M1471, AD2:AD10) จะได้พนักงานในแต่ละตำแหน่ง ซึ่งจะสามารถเช็คความถูกต้องได้ด้วยการดูผลรวมของพนักงานทุกตำแหน่งจะต้องเท่ากับ 1,470 คน ซึ่งก็คือพนักงานทั้งหมดของบริษัทที่มีใน Dataset
ตระกูล LOOKUP — ใช้สำหรับดูข้อมูลที่เกี่ยวข้อง
Boss: “เช็ครายชื่อให้หน่อย ว่าพนักงาน10คนนี้ ได้ออกจากบริษัท(Attrition)ไปแล้วหรือยัง ? //แนบเอกสารรายชื่อทั้ง 10 ”
สูตร LOOKUP ใน Excel ตอนนี้มีหลายประเภทด้วยกัน แต่ในตัวอย่างนี้จะใช้ VLOOKUP ในการหาข้อมูลที่เกี่ยวข้องกับแต่ละ ID
=VLOOKUP(ตัวข้อมูลที่ต้องการหา, ตารางที่ต้องการหาข้อมูล, เลขคอลัมน์ที่จะโชว์ค่า, [0: Exact Match 1: Aproximate Match])
*โดยตารางที่ต้องการหาข้อมูลนั้นจะต้องมีคอลลัมน์แรกที่ตรงกันกับตัวเลขที่ต้องการหา(Ex. คอลัมน์ EmployeeNumber = คอลัมน์ ID)
เราจะหาสถานะของพนักงานเหล่านี้ได้จากสูตร =VLOOKUP(AD7, $A$2:$C$1471, 3, 0) จะเห็นว่า “พนักงานที่ลาออกจากบริษัทไปแล้วคือพนักงานหมายเลขที่ 283 และ 1433”
จะเห็นได้ว่าสูตร VLOOKUP นั้นมีไว้สำหรับดึงข้อมูลที่เกี่ยวข้องในแนวนอน (Column Based) แต่ถ้าหากต้องการจะดึงข้อมูลในแนวตั้ง (Index Based) จะต้องใช้ HLOOKUP แทน
=HLOOKUP(ตัวข้อมูลที่ต้องการหา, ตารางที่ต้องการหาข้อมูล, เลขแถวที่จะโชว์ค่า, [0: Exact Match 1: Aproximate Match])
จริงๆแล้วสูตรที่ใช้ในการดูข้อมูลที่เกี่ยวข้องไม่ได้มีแค่ VLOOK UP และ HLOOKUP แต่ยังมี XLOOKUP (สูตร XLOOKUP ตอนนี้มีเฉพาะใน Microsoft 365 เท่านั้น)ที่เพิ่มความสะดวกในการดึงข้อมูลโดยจุดเด่นในการใช้ XLOOKUP ที่แตกต่างจาก VLOOKUP เลยก็คือ“ตารางที่ต้องการหาข้อมูลไม่จำเป็นจะต้องมีคอลลัมน์แรกที่ตรงกันกับตัวเลขที่ต้องการหา”
=XLOOKUP(ตัวข้อมูลที่ต้องการหา, ตารางที่ต้องการหาข้อมูล, ***แถวของตารางที่จะโชว์ค่า***, [0: Exact Match 1: Aproximate Match])
IF/IFS — ฟังก์ชันสำหรับการเปรียบเทียบข้อมูลกับเงื่อนไขที่กำหนด
Boss: “ข้อมูลอายุ(Age)มันดูยากไปนะ จัดระเบียบให้ข้อมูลอายุเป็นช่วงวัย(Generation) ตามที่แนบในเอกสารนี้นะ อยากจะดูว่าที่บริษัทมีพนักงานที่เป็นเด็กใหม่(Gen Z)กี่คน ?//แนบเอกสารที่สอง”
ถ้าหากต้องการจะจัดข้อมูลให้อยู่ในกลุ่มเดียวกัน ฟังก์ชันในการตรวจสอบข้อมูลไปยังกลุ่มต่างๆก็คือ IF
=IF(เงื่อนไขที่ต้องการตรวจสอบ, กรณีเงื่อนไขดังกล่าวเป็นจริง, กรณีเงื่อนไขเป็นเท็จ)
เช่น =IF(Age=22, “Gen Z”, “Other”) หมายความว่า หากข้อมูลดังกล่าวมีอายุ 22 ปี ก็จะต้องไปอยู่ใน “Gen Z” หากไปใช่จะเป็น “Other”
กรณีนี้ หากพิมสูตร =IF(B2<24, $AE$6, $AE$7) ก็จะเป็นการหาว่าหาก B2 (ก็คือ ID1) มีอายุน้อยกว่า 24 ปี จะถือว่าเป็น “Gen Z” ถ้าหากอายุมากกว่านั้นก็จะเป็น “Other” ถ้าหากต้องการนับว่ามี Gen Z ทั้งหมดกี่คนก็ใช้สูตร COUNTIF จากตัวอย่างก่อนหน้านี้ก็จะพบว่า “มีพนักงานที่เป็น Gen Z ทั้งหมด 71 คน” เท่านี้ก็เรียบ…
Boss: “เอ่อ…เปลี่ยนใจแล้วนะ อยากได้ช่วงอายุทั้ง 4 แบบเลยคือ Gen Z, Gen Y, Gen X และ Baby Boomers”
โจทย์นี้ท้าทายขึ้น แต่ไม่ยากเลย เพราะ “IF สามารถใช้สูตรซ้อนกันได้”
โดยสูตรนี้จะมีฟังก์ชันเพิ่มเข้ามาก็คือ AND ซึ่งจะเป็นฟังก์ชันสำหรับการเช็คตรรกะ (Logic)
=AND(เงื่อนไขที่ 1, เงื่อนไขที่ 2)
ถ้าทั้ง 2 เงื่อนไขที่กำหนดให้เป็นจริง จะคืนค่าเป็นจริง แต่ถ้าหากค่าใดค่าหนึ่งเป็นเท็จ ฟังก์ชันนี้ก็จะคืนค่าเป็นเท็จ (เหมือนกับ “และ” ในวิชาตรรกศาสตร์นั้นเอง)
แต่สังเกตดีๆว่าถ้าหากเขียนโดยใช้สูตรนี้ จะต้องใช้สูตร IF ตามจำนวนของประเภทข้อมูลที่ต้องการแบ่งเลย
=IF(B2<24, $AE$3, IF(AND(B2>=24, B2<41), $AE$4, IF(AND(B2>=41, B2<57), $AE$5, $AE$6)))
เพื่อไม่ให้เป็นการงง ทาง Microsoft 2016 ก็ได้มีฟังก์ชัน IFS ไว้ใช้สำหรับการเปรียบเทียบข้อมูลในกรณีที่มีหลายเงื่อนไขนั้นเอง
=IFS(เงื่อนไขที่ต้องการตรวจสอบที่ 1, กรณีเงื่อนไขที่ 1 เป็นจริง, [เงื่อนไขที่ต้องการตรวจสอบที่ 2], [กรณีเงื่อนไขที่ 2เป็นจริง], …)
สูตรของเราก็ไม่มีการใช้ IF ซ้อนๆกันอีกต่อไป และจะลดรูปสูตรลงเหลือแค่
=IFS(B2<25, $AE$3, AND(B2>=25, B2<41), $AE$4, AND(B2>=41, B2<57), $AE$5, B2>=57, $AE$6)
เป็นยังไงบ้าง? หวังว่าบทความนี้จะเป็นประโยชน์สำหรับผู้ที่ต้องการใช้ Excel เพื่อการจัดการข้อมูลด้วยสูตรเบื้องต้นที่ใช้บ่อยมาก
ถ้าต้องการสอบถามหรือพูดคุยเพิ่มเติมเกี่ยวกับสายงาน Data หรือเรื่องอื่นก็ได้ (ถ้าตอบได้นะ😁) ตาม LinkedIn ข้างล่างเลยจ้า แล้วเรื่องถัดไปจะเป็นเรื่องอะไรก็ขอให้กดติดตามกันไว้ด้วยนะคร้าบ😍