[Fundamental Data Analytics & Data Scientist EP.03] รวมสูตร Excel ที่ใช้โคตรบ่อย!!!

Pasith Thanapatpisarn
4 min readFeb 7, 2021

--

“เรียนรู้สูตร Excel ด้วยการลองเป็น Data Analyst ที่ IBM กันดีกว่า”

EP.03 Opening memes — Source

มาถึงบทความที่สามกันแล้วสำหรับซีรี่ย์ Fundamental Data Analytics & Data Scientist ในสองตอนที่แล้วเราได้อธิบายถึงอาชีพที่มีความสำคัญและมีความเกี่ยวข้องกับข้อมูลนั้นคือ Data Scientist และ Data Analytics ซึ่งถ้าได้อ่านบทความก่อนหน้านี้แล้ว จะเห็นว่า “การทำความสะอาดข้อมูล (Clean Data)” เป็นขั้นตอนที่สำคัญและเป็นขั้นตอนที่ใช้เวลานาน ดังนั้นเรื่องของ “การจัดการข้อมูล (Data Manipulation)” เพื่อให้ข้อมูลดังกล่าวสามารถนำไปทำความสะอาดหรือนำไปใช้งานได้ง่ายก็เป็นสิ่งสำคัญ

ตอนที่แล้ว —

[Fundamental Data Analytics & Data Scientist EP.01] Data Scientist ในไทยมัน Sexy ขนาดนั้นเลยหรอ!!!

[Fundamental Data Analytics & Data Scientist EP.02] Data Analytics Step by step อาชีพที่แบกความหวังของ Business

โปรแกรมที่ใช้สำหรับการจัดการข้อมูลที่ง่ายที่สุดนั้นก็คือ Microsoft Excel ของเรานั้นเอง !!!

ดังนั้น วันนี้เราจะมาแนะนำสูตร Excel ที่ใช้บ่อยมากสำหรับการจัดการข้อมูลกัน

Briefly

Function ที่ใช้บ่อยๆในการจัดการข้อมูลด้วย Excel นั้นคือ

  1. UNIQUE
  2. COUNTIF
  3. ตระกูล LOOKUP
  4. IF/IFS

ข้อมูลที่ใช้

ใช้ข้อมูล IBM HR Analytics Employee Attrition & Performance ซึ่งเป็นชุดข้อมูลเดียวกันกับตอนที่แล้วนะครับ จะได้ไม่เสียเวลามากในการทำความเข้าใจนะครับ

IBM HR Analytics Employee Attrition & Performance — Kaggle
Dataset

เอาหล่ะ…ถ้าพร้อมแล้ว เรามาสวมบทบาทเป็น Data Analyst ที่ IBM กันดีกว่า

UNIQUE — แสดงผลข้อมูลที่ไม่ซ้ำกันเลย

Boss: “เอ…ข้อมูลดูยากจังเลย…ทำสรุปให้หน่อยว่าบริษัทเรามีกี่ตำแหน่ง (JobRole) ?”
We: “ได้ครับบอส”

หากต้องการดูว่าข้อมูลมีทั้งหมดกี่ประเภท หรือมีข้อมูลที่ไม่ซ้ำกันเราจะใช้สูตร

=UNIQUE(เซลล์ที่ต้องการเช็คข้อมูลซ้ำ)

UNIQUE Example

ซึ่งในกรณีนี้เราใช้สูตร =UNIQUE(M2:M1471) จะได้ตำแหน่งงานทั้งหมดที่มีในบริษัท เท่ากับ 9 ตำแหน่งนั้นเอง

COUNTIF — นับจำนวนของข้อมูลตามเกณฑ์ที่เราตั้งไว้

Boss: “โอเค…แล้วแต่ละตำแหน่ง (JobRole) มีกี่คนหล่ะ ?”

เนื่องจากคราวที่แล้วเราใช้ UNIQUE ในการหาตำแหน่งที่มีทั้งหมดของบริษัทไปแล้ว ขั้นตอนต่อไปก็คือเราต้องเช็คจำนวนพนักงานของแต่ละตำแหน่ง สูตรต่อไปที่เราจะใช้ก็คือ

=COUNTIF(เซลล์ที่ต้องการเช็คข้อมูล, เซลล์ที่ใช้เป็นเกณฑ์)

COUNTIF Example

เราก็ใช้สูตร =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 Example

เราจะหาสถานะของพนักงานเหล่านี้ได้จากสูตร =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])

XLOOKUP Example — EmployeeNumber อยู่มุมไหนก็ไม่หวั่น

IF/IFS — ฟังก์ชันสำหรับการเปรียบเทียบข้อมูลกับเงื่อนไขที่กำหนด

Boss: “ข้อมูลอายุ(Age)มันดูยากไปนะ จัดระเบียบให้ข้อมูลอายุเป็นช่วงวัย(Generation) ตามที่แนบในเอกสารนี้นะ อยากจะดูว่าที่บริษัทมีพนักงานที่เป็นเด็กใหม่(Gen Z)กี่คน ?//แนบเอกสารที่สอง”

รายละเอียดของ Generation

ถ้าหากต้องการจะจัดข้อมูลให้อยู่ในกลุ่มเดียวกัน ฟังก์ชันในการตรวจสอบข้อมูลไปยังกลุ่มต่างๆก็คือ IF

=IF(เงื่อนไขที่ต้องการตรวจสอบ, กรณีเงื่อนไขดังกล่าวเป็นจริง, กรณีเงื่อนไขเป็นเท็จ)

เช่น =IF(Age=22, “Gen Z”, “Other”) หมายความว่า หากข้อมูลดังกล่าวมีอายุ 22 ปี ก็จะต้องไปอยู่ใน “Gen Z” หากไปใช่จะเป็น “Other”

IF Example

กรณีนี้ หากพิมสูตร =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”

รายละเอียดของ Generation ชุดใหม่

โจทย์นี้ท้าทายขึ้น แต่ไม่ยากเลย เพราะ “IF สามารถใช้สูตรซ้อนกันได้

IF Example (2)

โดยสูตรนี้จะมีฟังก์ชันเพิ่มเข้ามาก็คือ 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เป็นจริง], …)

IFS Example

สูตรของเราก็ไม่มีการใช้ 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 ข้างล่างเลยจ้า แล้วเรื่องถัดไปจะเป็นเรื่องอะไรก็ขอให้กดติดตามกันไว้ด้วยนะคร้าบ😍

--

--