เล่าเกร็ดการใช้งาน Microsoft Excel

เล่าเกร็ดการใช้งาน Microsoft Excel

ผมเคยทำงานเป็น Data analyst มาก่อนฮะ

จริงๆ แล้วความแตกต่างระหว่าง Data analyst กับ Data engineer ที่ทำอยู่ตอนนี้ คือ Data engineer จะคุยกับคอมมากกว่าคุยกับคนนะฮะ และเนื้องานส่วนใหญ่จะเป็นเชิงเทคนิคมากกว่า ในขณะที่ Data analyst จะเน้นทำงานกับข้อมูลเพื่อไปตอบโจทย์ business และยุ่งเกี่ยวกับเชิงเทคนิคน้อยกว่า

เครื่องมือหลักๆ ของ Data analyst จึงเป็นโปรแกรมที่ใช้งานเร็วๆ ตอบคำตอบเร็วๆ ให้ทันกับเวลาที่ลูกค้าต้องการ และหนึ่งในนั้นก็คือ

Microsoft Excel

โปรแกรมที่คนรอบข้างผมมักจะทำหน้าแหยงๆ ใส่ แต่จริงๆ แล้วมันช่วยลดเวลาทำงานได้มากเลยนะฮะ ไม่ต้องเป็น Data analyst ก็ใช้งานคล่องๆ ได้ เพิ่มเวลาว่างให้เราไปนอน เอ๊ย เอาไปทำอย่างอื่นได้เงินเพิ่ม ดีไปอีกเนอะ

ดังนั้น บล็อกนี้เลยจะขอแปะเทคนิคง่ายๆ ใช้งานไม่ยากให้ไปลองเล่นกัน เผื่อจะช่วยให้งานเสร็จเร็วขึ้นฮะ

1. ต้องการพิมพ์ข้อความ ไม่เอา format อื่น

บางครั้ง เราต้องการพิมพ์ข้อความ อาจจะเป็นวันที่ เดือน หรือเลขไอดีอะไรสักอย่าง แต่ Excel มันฉลาดเกินไป แก้ format ให้เราอัตโนมัติ ให้เราเพิ่ม ' (Single quote) เข้าไปก่อนหน้าข้อความนั้นฮะ มันจะถือว่าเป็นข้อความทันที และไม่แก้ format ให้เราแล้ว

จะพิมพ์วันที่แบบนี้

แต่ Excel แก้ให้เป็นแบบนี้

เติม ' เข้าไป

ได้ค่าตามที่ต้องการแล้วล่ะ

2. เช็คค่า error

การเช็คค่า error ใน cell มีสามฟังก์ชันหลักๆ ฮะ ตามโค้ดด้านล่าง

=ISERR(cell)
=ISERROR(cell)
=ISNA(cell)

ความแตกต่างคือ =ISNA() จะเช็คเฉพาะ “ค่า” ซึ่ง “NA” มาจาก “Not Available” หมายถึงค่านี้มันใช้งานไม่ได้ฮะ ในขณะที่ =ISERROR() จะเช็คทุกอย่างที่เป็นความผิดพลาด ส่วน =ISERR() เช็คทุกอย่างที่ไม่ใช่ “NA” ฮะ

ดูจากรูปข้างล่างนี้ได้เลยฮะ คอลัมน์ B เป็นผลจากสูตรในคอลัมน์ A และคอลัมน์ D เป็นผลจากสูตรในคอลัมน์ C นะฮะ

  • =8/0 มันหารศูนย์ไม่ได้ จึงได้ผลลัพท์เป็น #DIV/0! ผิดตั้งแต่การหารแล้ว ดังนั้น =ISNA() เช็คไม่ได้นะฮะ
  • =NA() ให้ค่า #N/A เป็นฟังก์ชันที่ให้ค่า N/A ออกมาโดยเฉพาะ จริงๆ แล้วถ้าการคำนวณอื่นก็มีโอกาสได้ค่านี้ออกมา อาจจะมาจากค่าที่ป้อนเข้าฟังก์ชัน หรือทางอื่นฮะ อันนี้ =ISNA() เช็คได้ และกลับกัน =ISERR() เช็คไม่ได้แทนฮะ
  • =ASIN(2) ผิดเพราะฟังก์ชันนี้คำนวณค่า Arcsine โดยป้อนค่า -1 ถึง 1 เมื่อใส่ 2 เข้าไป ทำให้ได้ #NUM!ออกมาฮะ อันนี้ก็ใช้ =ISNA() เช็คไม่ได้เช่นกัน
  • =TEX() ผิดเพราะมันไม่มีฟังก์ชันนี้ ที่ถูกคือชื่อ =TEXT() และแน่นอน เราใช้ =ISNA() เช็คกรณีนี้ไม่ได้ฮะ
  • =VALUE("A") ฟังก์ชันนี้จะแปลงข้อความเป็นตัวเลข แต่ถ้ามันแปลงไม่ได้ อย่างค่า “A” มันก็จะตีเป็น #VALUE! และเราก็เช็คด้วย =ISNA() ไม่ได้ฮะ

3. แทนค่า error

=IFNA(cell, value)
=IFERROR(cell, value)

คล้ายกับข้างบนนั่นแหละฮะ ถ้ามันเช็ค =ISERROR() หรือ =ISNA() ได้ ก็สามารถแทนที่ค่าได้ กลับกัน ถ้าเช็คไม่ได้ มันก็จะแสดงผลเป็นข้อความแปลกๆ ออกมาฮะ

อ้อ ไม่มี =IFERR() นะฮะ

4. เปรียบเทียบค่า

ปกติแล้ว เราเทียบค่าสองค่าว่าเท่ากันมั้ย แค่ใช้เครื่องหมายเท่ากับก็พอแล้วถูกมั้ยฮะ แต่ว่านะ สิ่งที่เห็นอาจไม่ใช่สิ่งที่เป็นจริงๆ (คำคมต้องมา) เราจึงควรรู้จักสูตรการเทียบค่าสองตัวนี้ฮะ

=cell1=cell2
=IFERROR(VALUE(cell1),cell1)=IFERROR(VALUE(cell2),cell2)

มาดูตัวอย่างกัน

จากตัวอย่าง เราเห็นว่า value1 กับ value2 เหมือนกันทุกอย่าง แต่ทำไม =A5=B5 ถึงได้ FALSE สังเกตที่ fx ของ B5 ที่ผมแสดงให้ดูฮะ ว่ามันเป็นค่า '1 ซึ่งมันคือข้อความ ไม่ใช่ 1 ที่เป็นตัวเลข การเปรียบเทียบค่าถึงได้เป็นเท็จนั่นเองฮะ ซึ่งจากรูปก็มีผลลัพท์จากฟังก์ชัน =TYPE() ให้ดูด้วยนะฮะ ว่า 2 แปลว่าค่านั้นคือ Text

เราจึงใช้สองฟังก์ชันร่วมกัน นั่นคือ =IFERROR() และ =VALUE() โดยที่เราจะใช้ =VALUE() เพื่อเช็คว่าเป็นข้อความที่เป็นตัวเลขมั้ย ถ้าใช่ เราก็จะได้ตัวเลข แต่ถ้าไม่ใช่ เราก็ใช้ =IFERROR() ให้ได้ค่าของ cell นั้นมาแทน ผลลัพท์คือ ถ้า cell สามารถเป็นตัวเลขได้ เราจะได้ตัวเลข ถ้าไม่ใช่ เราจะได้ค่าต้นทางมา แล้วใช้เครื่องหมายเท่ากับตามปกติฮะ

5. แปลงตารางให้เป็นกราฟในคลิกเดียว

ที่ tab: Home มีเมนู Conditional Formatting ให้เราใช้งานดังนี้ฮะ

  • Data bar
    ใส่กราฟแท่งให้กับค่าใน cell ที่เราเลือก โดยค่าสูงสุดจะได้กราฟแท่งที่ยาวเต็ม cell ไปเลย
  • Color scale
    ใส่สีพื้นหลังให้ cell ทันที ขึ้นอยู่กับชุดสีที่เราเลือกฮะ
  • Icon set
    ใส่ไอคอนให้ cell ทันที เราเลือกได้ว่าจะให้เป็นไอคอนแบบไหน
  • Highlight cell rules
    เป็นแบบทำมือ เราใส่เงื่อนไขเข้าไป และกำหนดว่าถ้าเข้าเงื่อนไข ให้แสดงผลยังไงฮะ

6. ไม่เอาค่าซ้ำ

หลายครั้งที่เราต้องการค่าที่ไม่ซ้ำกัน จะทำยังไง

ไปที่ tab: Data กดที่เมนู Remove Duplicates จะมีตัวเลือกให้เราดังนี้ฮะ

6.1 Expanded Selection

เลือกข้อนี้ถ้าเราต้องการให้ทุกคอลัมน์ที่เราต้องการมีค่าไม่ซ้ำกันในแถวเดียวกัน จากรูป เจอค่าซ้ำหนึ่งค่า นั่นคือ (a, 1) ของแถวที่ 1 และแถวที่ 4 นะฮะ

6.2 Original selection

เลือกข้อนี้ ถ้าต้องการกำจัดค่าซ้ำในส่วนที่เลือกเอาไว้เท่านั้น ไม่ยุ่งเกี่ยวกับคอลัมน์อื่นฮะ

7. Lookup

Lookup หรือการค้นตาราง เป็นงานที่ค่อนข้างซับซ้อนสำหรับคนที่ใช้งาน Excel ไม่บ่อยนะฮะ ดังนั้น ผมจะแปะไว้สองสูตรที่เคยใช้ดังนี้ฮะ

=VLOOKUP(find, table, get, approx_match)
=INDEX(get, MATCH(find, table, match_type), column_no)

มาดูที่ฟังก์ชันแรก =VLOOKUP() กันก่อนฮะ ฟังก์ชันนี้มีข้อจำกัดคือ เราต้องให้คอลัมน์แรกของ “TABLE” เป็นคอลัมน์ที่เราจะหาค่า lookup ฮะ จากตัวอย่างข้างบน เราหา salary จาก name เราก็ต้องให้ “TABLE” มันคลุมตั้งแต่ name ไปจนถึง salary นั่นเองฮะ และผมกำหนด “APPROX_MATCH” เป็น FALSE เพราะไม่ต้องการ approximate matching ฮะ ผลคือมันจะหาแบบ exact match คือเทียบค่าเป๊ะๆ ออกมาฮะ

ส่วนวิธีที่สอง ใช้ =INDEX() กับ =MATCH() เป็นอีกวิธีนึงที่ผมใช้ตอนที่เราไม่สามารถกำหนดให้คอลัมน์แรกของ “TABLE” เป็นคอลัมน์ที่เราใช้หาค่า “FIND” ได้ ตัวอย่างดังข้างต้นฮะ เราต้องการหา id จาก name แต่ id อยู่ก่อนหน้า name จึงใช้วิธีนี้ฮะ โดย =MATCH() จะค้นหาว่าค่า “FIND” ที่ต้องการอยู่ลำดับที่เท่าไหร่ของ “TABLE” จากนั้นก็หาค่าที่ลำดับนั้นจาก “GET” ด้วย =INDEX() ฮะ

กำหนด “MATCH_TYPE” เป็น 0 เพราะต้องการ exact match และ “COLUMN_NO” เป็น 0 เพราะต้องการคอลัมน์แรกของ “GET” ฮะ

8. Count และ Sum

เป็นสูตรพื้นฐานที่สุด ที่เราใช้งานมันได้หลากหลายสถานการณ์มากฮะ

  • =SUM()
    หาผลรวมเฉยๆ
  • =SUMIF()
    หาผลรวมจาก 1 เงื่อนไข จากตัวอย่างคือหาผลรวม Salary (D2:D11) โดยมีเงื่อนไข Department (C2:C11) เท่ากับ “IT”
  • =SUMIFS()
    หาผลรวมจากหลายเงื่อนไข จากตัวอย่างคือหาผลรวม Salary (D2:D11) โดยที่ Department (C2:C11) เท่ากับ “IT” และ age (B2:B11) มากกว่า “30”
  • =COUNT()
    นับตัวเลขและ boolean เฉยๆ
  • =COUNTA()
    นับ cell ที่มีข้อมูล
  • =COUNTBLANK()
    นับ cell ที่ไม่มีข้อมูล
  • =COUNTIF()
    นับ cell จาก 1 เงื่อนไข จากตัวอย่างคือนับจาก Checked ที่มีค่าเท่ากับ “Yes”
  • =COUNTIFS()
    นับ cell จากหลายเงื่อนไข จากตัวอย่างคือนับจาก Checked ที่มีค่าเท่ากับ “Yes” และ Department เท่ากับ “Sales”

9. Dropdown

ถ้าเรามีข้อมูลชุดนึง และให้คนอื่นกรอกค่าตามที่เรากำหนด เราสามารถกำหนดค่าให้เป็น dropdown list ตามนี้เลยฮะ

เริ่มต้น ให้เราเตรียมค่าที่จะให้คนอื่นกรอก สมมติว่าเป็น Department นะฮะ จากนั้นก็เลือก cell ที่ต้องการให้ป้อนค่า แล้วไปที่ tab: Data เลือก Data Validation

จะมีกล่องขึ้นมา เลือก Allow: List และ Source: ก็ลากเมาส์คลุมค่าที่ต้องการฮะ

เสร็จแล้ว เวลาจะกรอกค่าก็จะมี dropdown ทางขวาให้กดเลือก หรือจะพิมพ์ก็ได้ แต่ถ้าพิมพ์ค่าที่ไม่อยู่ในตัวเลือกก็จะมีข้อความเด้งเตือนมาฮะ

10. Freeze panes

ข้อมูลมันเยอะมาก ไถดูแถวล่างๆ แต่ไม่ถนัดดูหัวคอลัมน์ แนะนำให้ Freeze panes ฮะ

ไปที่ tab: View เลือกเมนู Freeze Panes จะมีตัวเลือกอยู่นะฮะ Excel แต่ละเวอร์ชันอาจจะมีตัวเลือกไม่เหมือนกัน ครั้งนี้ผมเลือกเป็น “Freeze Panes” และก่อนกดอันนี้ผมก็เลือก cell B6 ที่จะเป็นจุดตัดฮะ ตามรูปเลย

ผลลัพท์คือ เราจะได้ Freeze panes ของ 5 แถวบน และ 1 คอลัมน์ด้านซ้าย เลื่อนไปมาสะดวกเลยฮะ

11. สูตรเยอะ เปิดไฟล์ใช้เวลาเยอะตาม

บ่อยครั้งมากที่ผมต้องจัดการข้อมูลใน Excel และต้องคำนวณใช้หลายสูตรกับหลายคอลัมน์ ประเด็นสำคัญคือ ถ้าเราบันทึกไฟล์ โดยมีสูตรพวกนั้นอยู่จำนวนมาก จะส่งผลเวลาเปิดไฟล์ครั้งต่อไป เพราะมันต้องคำนวณทั้งหมดอีกครั้งหนึ่ง ยิ่งถ้าคอมพิวเตอร์เราช้าๆ อยู่แล้วละก็ เตรียมเดินไปชงกาแฟตั้งแต่เนิ่นๆ ได้เลยฮะ

คำแนะนำคือ ถ้ามั่นใจว่าคำนวณค่าถูกต้องแล้ว และไม่ต้องมาคำนวณใหม่ในครั้งหน้า ให้ Copy แล้ว Paste as value จะทำให้สูตรหายไปแล้วกลายเป็นค่านิ่งๆ แทนฮะ วิธีนี้จะทำให้ไฟล์ Excel ใช้งานง่ายขึ้นในครั้งถัดไปฮะ

12. Pivot table

ความเป็นตารางของ Excel ก็ดีอยู่แล้วฮะ แต่ดีไปกว่าเดิมเมื่อเราใช้ Pivot table เป็น

Pivot table ถือเป็นจุดเด่นของ spreadsheet เลยฮะ เพราะมันสามารถออกแบบเป็นตารางที่ดูข้อมูลง่าย และสามารถคลิกเลือก/เปลี่ยน/กรองข้อมูลได้หลากหลายวิธีฮะ ซึ่งเราจะต้องสร้างผ่าน tab: Insert เลือกเมนู PivotTable

และนี่ก็เป็นตัวอย่างนึงของ Pivot table ที่ผมทำนะฮะ สร้างแบบง่ายๆ เร็วๆ เราก็ได้ตารางที่กดเลือกดู Salary ของแต่ละ Department แล้วล่ะ


Microsoft Excel ถือเป็นจุดขายของชุด Microsoft Office เลยนะฮะ ลองใช้งานดู แล้วจะติดใจ… จริงๆนะ

Reference links: https://blog.hubspot.com/marketing/how-to-use-excel-tips

Show Comments