10 ข้อต้องรู้ เมื่อต้องบู๊ด้วย Excel

ถ้าหากคุณมีความรู้สึกที่ว่า “อยากจะใช้ Excel ให้เก่งขึ้น” แต่ดูเหมือนมีอะไรที่ต้องเรียนรู้เยอะแยะไปหมด และ ไม่รู้ว่าอะไรบ้างที่จำเป็นจริงๆ กันแน่?

บทความนี้มีคำตอบ เพราะผมได้ทำการสรุปเป็น “10 ข้อต้องรู้ เมื่อต้องบู๊ด้วย Excel” เพื่อให้คุณหรือเพื่อนของคุณที่อาจกำลังอยากพัฒนาทักษะการใช้ Excel ได้อ่านกันครับ

1. อ้างอิงช่องต่างๆ ใน Excel

ใน Excel นั้นมีช่องตารางเต็มไปหมด ซึ่งแต่ละช่องมีการระบุตำแหน่งด้วยรหัสคอลัมน์ (ที่ขึ้นด้วยตัวอักษรภาษาอังกฤษ) ตามด้วยรหัสแถว (ที่เป็นตัวเลข)

เมื่อรู้พิกัด เราก็จะอ้างอิง Cell นั้นๆ ได้ เช่น ถ้าจะเรียก Cell ที่อยู่ตำแหน่งคอลัมน์ D ตัดกับ แถวที่ 3 เราก็จะเรียกว่า D3 นั่นเอง

นอกจากจะอ้างอิงทีละช่องแล้ว เรายังอ้างอิงหลายๆ Cell หรือเป็่นช่วง (Range) ได้ด้วยการใช้เครื่องหมายต่างๆ มาเชื่อม เช่น

• Comma ใช้เชื่อม Cell ที่ไม่ได้อยู่ติดกัน เช่น A2,B5 หมายถึง 2 ช่อง คือ A2 และ B5
• Colon ใช้เชื่อม Cell ที่อยู่ติดกัน เช่น A2:B5 หมายถึง 8 ช่อง คือ A2,A3,A4,A5,B2,B3,B4,B5

แต่นอกจากการอ้างอิงด้วยรหัส เช่น A1 แล้ว เรายังอ้างอิงไปยังช่องต่างๆ ได้ด้วยการตั้งชื่อ (Define Name) ได้ด้วย ซึ่งสามารถใช้แทนรหัส A1 พวกนี้ได้ทุกประการ เช่น ตั้งชื่อช่อง A1 ว่า ยอดขาย ก็ได้  แถมเรายังตั้งชื่อ Range ได้ด้วยเช่นกัน

รายละเอียดเพิ่มเติม ดูได้ที่ การเลือก/กรอก/แก้ไขข้อมูล และ การอ้างอิงด้วยการตั้งชื่อ (Define Name)

2. สิ่งที่เห็นภายนอก vs ค่าที่แท้จริง

Excel นั้นแยกเรื่องของ “ข้อมูล” กับ “การแสดงผล” ออกจากกันครับ… บางทีสิ่งที่เราเห็นว่าเป็นแบบนึง ข้อมูลข้างในอาจเป็นอีกแบบก็ได้

เช่นตัวอย่างเลขทศนิยมอันนี้ก็ได้ครับ ชัดเจนดี

ใน A1 มีค่าที่แท้จริงเป็น 2.5 แต่แสดงผลเป็น 3 เพราะปรับ Format ให้ไม่มีทศนิยม แต่เวลานำไปคำนวณ Excel ก็ยังเอาค่าที่แท้จริงคือ 2.5 ไปคำนวณอยู่ดี

เช่น =A1*A2 แปลว่าให้เอาค่าใน A1 คูณด้วยค่าใน A2 ซึ่งในที่นี้ได้ 5 ไม่ใช่ 6 เพราะเอา 2.5*2 ต่างหาก

ซึ่งเราสามารถทำให้ข้อมูลน่าสนใจได้มากขึ้น ด้วยการปรับ Format หรือรูปแบบการแสดงผลนั่นเอง ซึ่งมีทั้งเราปรับเองทีละอัน หรือ จะให้ Excel ปรับให้หากข้อมูลใน Cell ตรงกับเงื่อนไขที่เรากำหนดก็ได้

รายละเอียดเพิ่มเติม ดูได้ที่ การเปลี่ยนรูปแบบ (Formatting) , Conditional Formatting เพื่อปรับรูปแบบตามเงื่อนไข, การกำหนด Custom Number Format

3. Excel มองวันเวลาเป็นแค่ตัวเลขธรรมดา

วันที่และเวลา ที่เราเห็นแสดงผลอยู่ใน Cell ต่างๆ นั้น จริงๆ แล้วค่าที่แท้จริงเป็นแค่ตัวเลขธรรมดาๆ แค่นั้นเอง

โดยที่เลข 1 หน่วย แทน 1 วันเต็มๆ 
ถ้า 1.5 ก็แทน 1 วันครึ่ง…

แต่การที่เรามองเห็นเป็นวันที่ เป็นเวลา นั้นเป็นเพราะมีการกำหนด Format หรือรูปแบบการแสดงผลให้เป็นวันที่และเวลาเท่านั้นเอง

ดังนั้น การคำนวณหาค่า OT จากเวลาเริ่มละจบงานจึงใช้การคูณธรรมดาก็ตอบได้แล้ว

แนวคิดคือ พอเอาเวลาจบลบเวลาเริ่ม จะได้เวลาหน่วยเป็นวัน จึงต้องเอาไป *24*60 ให้หน่วยเป็นนาที แล้วคูณค่า OT ต่อนาที จุงจะได้ค่า OT นั่นเอง

จริงๆ แล้วเป็นเพราะว่าข้อมูลใน Excel นั้นมีอยู่แค่ 5 ประเภท นั่นคือ ตัวเลข, ตัวหนังสือ, ตรรกะ TRUE/FALSE, ค่า Error, Array เท่านั้น จะเห็นว่าไม่ได้มีข้อมูลประเภทวันที่/เวลา เลย แปลว่า Excel มองวันที่/เวลา เป็นแค่ตัวเลขบบนึงเท่านั้นเองครับ

ถ้าอยากเรียนรู้เพิ่มเติม อ่านได้ที่ ประเภทของข้อมูลบน Excel และ การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel

4. สูตรคำนวณคือหัวใจของ Excel

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

โดยเราจะต้องเขียนสูตรไว้หลังเครื่องหมาย = เสมอ และผลลัพธ์ของการคำนวณจะถูกแสดงในช่องที่เรากำลังเขียนสูตรนั่นเอง ซึ่งเราเขียนสูตรได้หลายแบบ

• คำนวณปกติ เช่น =5+300 จะได้ผลลัพธ์เป็น 305  หรือเขียน =4*6 (4 คูณด้วย 6) จะได้ 24
• มีการอ้างถึงช่องอื่นๆ (Cell Reference) เช่น =B3+10 แปลว่าให้เอาค่าในช่อง B3 มาบวกด้วย 10 ซึ่งถ้าค่าใน B3 เปลี่ยนไป ผลลัพธ์การคำนวณก็จะเปลี่ยนไปด้วยเช่นกัน
• มีการอ้างด้วย Define Name เช่น =ยอดขาย*5 แปลว่าให้เอาจาก Range ที่ Define Name ชื่อว่า ยอดขาย คูณด้วย 5
• มีการใช้ตัวเชื่อม (Operator) เช่น เครื่องหมายเปรียบเทียบ > < เพื่อให้ได้ค่า TRUE/FALSE , เครื่องหมาย & เอาไว้เชื่อมข้อความเข้าด้วยกัน เป็นต้น
• มีการเรียกใช้ฟังก์ชั่น (สูตรสำเร็จรูป) เช่น  =SUM(A1:A10) แปลว่าให้เอาค่าในช่อง A1 ถึง A10 (10 ช่อง) มาบวกกันให้หมด

อย่างไรก็ตามคำว่า “คำนวณ” ที่ผมพูดถึงนั้น ไม่จำเป็นที่ต้องเป็นการคำนวณทางคณิตศาสตร์เท่านั้น แต่การจัดการข้อมูล แทนที่คำ ตัดคำ หรือแม้แต่การค้นหาข้อมูล หรือ Map ข้อมูล เช่น VLOOKUP เพื่อหาชื่อสินค้าจากรหัสสินค้าก็ถือว่าเป็นการคำนวณแบบหนึ่งเช่นกัน

สิ่งที่ต้องระวังในการเขียนสูตรคือ เวลาเราจะเขียนข้อความลงในสูตร จะต้องใส่อยู่ในเครื่องหมายคำพูด ไม่เช่นนั้น Excel จะไปสับสนว่าเป็น Define Name ซึ่งจะทำให้ Error เช่น จะเอาคำว่า บาท ไปต่อท้ายตัวเลขในช่อง B1 เราก็ต้องเขียนว่า =B1&” บาท” เป็ยต้น

ถ้าอยากเรียนรู้เพิ่มเติม อ่านได้ที่ การเขียนสูตรผูกความสัมพันธ์ (Formula)

5. เครื่องหมาย $ ต้องใช้ให้เป็น

เวลา Copy สูตรที่มีการใช้ Cell Reference อยู่ เวลา Paste แล้ว Cell Reference จะเลื่อนไปตามทิศทางของการ Copy

เช่น ในช่อง B1 เขียนว่า =E1+3 เมื่อทำการ Copy สตรแล้วมา Paste ที่ A7 จะได้ว่า =D7+3 (Cell Reference E1 ในสูตรเลื่อนลงมาเป็น D7 ตามทิศทางของการ Copy/Paste)

หากว่าต้องการจะ Fix ตำแหน่งไม่ให้ Cell Reference เลื่อน ก็ต้องใช้เครื่องหมาย $ มาช่วย (กด F4 ก็ได้) โดยหลักการคือ หาก $ อยู่หน้าตัวไหน ตัวนั้นจะถูกตรึงไว้ไม่ให้เลื่อนไปไหน (ในหนังสือ Excel Level Up! ผมใช้คำว่าใช้เงิน $ ฟาดหัวให้อยู่นิ่งๆ)

ดังนั้น…

• ถ้าเขียนว่า =$E$1+3 มันก็จะไม่เลื่อนไปไหนเลย
• ถ้าเขียนว่า =$E1+3 มันก็จะไม่เลื่อนคอลัมน์ แต่เลื่อนแถวได้
• ถ้าเขียนว่า =E$1+3 มันก็จะเลื่อนคอลัมน์ได้ แต่เลื่อนแถวไม่ได้
• ถ้าเขียนว่า =E1+3 มันก็จะเลื่อนได้ทั้งคอลัมน์และแถว

ซึ่งทั้ง 4 แบบนี้กดปุ่ม F4 เพื่อเปลี่ยนรูปแบบไปมาได้ครับ

ถ้าอยากเรียนรู้เพิ่มเติม อ่านได้ที่ การคัดลอก ตัด แปะ แทรก

6. ฟังก์ชั่นที่ใช้บ่อยจริงๆ มีไม่เยอะ

ฟังก์ชั่นที่ใช้บ่อยจริงๆ ใน Excel มีแค่ 20% ของฟังก์ชั่นทั้งหมด (ตามกฎ 80/20 เลย) ดังนั้นมันไม่ใช่เรื่องยากเกินไปที่จะเรียนรู้แน่นอน ถ้าเรียนรู้แค่วันละ 1 ฟังก์ชั่น เพียง 2 เดือนก็เรียนครบแล้วครับ

ผมอยากให้หลักคิดบางอย่าง กับฟังก์ชั่นบางตัวที่ Hot Hit สุดๆ ดังนี้ครับ

• เมื่อนึกถึงคำว่า “ถ้า” ให้ใช้ ฟังก์ชั่น IF โดย 1 IF แตกกิ่งก้านได้ 2 กิ่ง คือ จริง และ เท็จ (หากมีหลายๆ “ถ้า” ก็ต้องใช้ IF ซ้อนกันหลายตัว)
• เมื่อต้องการ “Map ข้อมูล” เช่น หาชื่อสินค้าจากรหัสสินค้า ให้ใช้ฟังก์ชั่น VLOOKUP
• เมื่อต้องการหาว่าข้อมูลที่สนใจอยู่ลำดับที่เท่าไหร่ ให้ใช้ ฟังก์ชั่น MATCH
• หากรู้พิกัด แล้วอยากได้ค่าในพิกัดนั้นกลับมา ใช้ ฟังก์ชั่น INDEX
• หากต้องการทำงานกับข้อความ เช่น นับอักขระ ตัดคำ แทยที่คำ ให้ใช้ฟังก์ชั่นกลุ่ม Text
• หากต้องการทำงานกับวันที่และเวลา เช่น ดึงค่า ปี เดือน วัน ให้ใช้ฟังก์ชั่นกลุ่ม Date/Time ผสมกับความรู้เรื่องที่ว่า Excel มองวันที่/เวลา เป็นแค่ตัวเลข

7.บังคับให้กรอกข้อมูลตามที่กำหนดด้วย Data Validation

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

ปัญหานี้สามารถลดให้เหลือน้อยลงได้โดยการใช้เครื่องมือที่ชื่อว่า Data Validation ซึ่งกำหนดได้ว่าในแต่ละช่องจะยอมให้กรอกข้อมูลอะไรได้บ้าง เช่น ตัวหนังสือ ตัวเลข วันที่

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

เช่นรูปข้างล่าง ผมใส่เงื่อนไขว่าต้องนับแต่ละคำได้ไม่เกิน 1 ครั้ง

แต่ที่เจ๋งสุดคือเครื่องมือที่ชื่อว่า List ซึ่งสามารถทำเป็น Dropdown List เพื่อช่วยให้ผู้ใช้งานทำงานได้สะดวกมากขึ้น ซึ่งเป็นเครื่องมือ Data Validation ที่ใช้บ่อยที่สุดเลย

ถ้าอยากเรียนรู้เพิ่มเติม อ่านได้ที่ Data Validation

8. การสร้าง PivotTable เพื่อสรุปผลข้อมูลจากฐานข้อมูลที่เตรียมไว้

PivotTable (ฝรั่งอ่านว่า พิ-วอท-เท-เบิ้ล) เป็นเครื่องมือบน Excel ที่สามารถสรุปผลข้อมูลตามเงื่อนไขที่กำหนดได้อย่างง่ายดายและรวดเร็ว

 ==> 

เช่น สามารถสรุปได้ว่าข้อมูลแต่ละประเภท มีผลสรุปที่เราสนใจเป็นเท่าไหร่ เช่น ผลรวม/จำนวนนับ/ค่าเฉลี่ย/ค่ามาก/น้อยสุด เป็นต้น

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

ถ้าอยากเรียนรู้เพิ่มเติม อ่านได้ที่ Pivot Table ภาค 1 – การสรุปผลข้อมูลขั้นพื้นฐาน

9. การสร้างกราฟ จากข้อมูลที่ทำการสรุปผลไว้แล้ว

กราฟหรือแผนภูมิที่ดีนั้นเป็นสิ่งที่ช่วยให้เราสามารถแสดงผลลัพธ์ของข้อมูลในรูปแบบที่เข้าใจง่ายขึ้น แบบว่า คนดูมองเแล้วเห็นภาพ และรู้ได้ทันทีว่าข้อมูลที่เรากำลังแสดงอยู่หมายถึงอะไร ดังคำกล่าวที่ว่า “รูปภาพ 1 รูปแทนคำนับพัน” นั่นเอง

ดังนั้นกราฟหรือแผนภูมิจึงเป็นสิ่งที่จำเป็นต่อการนำเสนองานเป็นอย่างยิ่ง เพราะช่วยประหยัดเวลาในการอธิบาย และทำให้การนำเสนอน่าสนใจ และเป็นที่จดจำมากขึ้น

การจะทำกราฟได้นั้น เราต้องมีการสรุปผลข้อมูลเสียก่อน ไม่ว่าจะใช้ PivotTable หรือพวก SUMIFS/COUNTIFS ก็ตาม

ถ้าอยากเรียนรู้เพิ่มเติม อ่านได้ที่ การสร้างกราฟแผนภูมิใน Excel เบื้องต้น

10 : อย่าเพิ่งคิดว่า Excel ทำไม่ได้

ปัญหาหลายๆ อย่างนั้นดูยาก และดูเหมือนไม่น่าจะใช้ Excel แก้ไขปัญหาได้ แต่เอาเข้าจริงๆ แล้วมันก็มีทางแก้ไขปัญหาอยู่ดี เพียงแต่เรายังไม่รู้เท่านั้นเอง…

ตัวอย่างที่ Extreme มากๆ คือ มีคนใช้ Excel แก้ปัญหา Sudoku ได้โดยไม่ต้องใช้ VBA หรือ Solver เลยด้วยซ้ำ!! ถ้าสนใจอ่านได้ในนี้ (ยากมากๆ หน่อยนะครับ)

• Building a Basic, Understandable Sudoku Solver Using Excel Iterative Calculation – Part 1/2
• Building a Basic, Understandable Sudoku Solver Using Excel Iterative Calculation – Part 2/2

ดังนั้น ถ้าเจอปัญหาอะไรก็ตาม ผมอยากให้ลองแก้ปัญหาอย่างสุดความสามารถก่อน เพราะมันจะทำให้คุณเก่งขึ้นอีกมาก

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

หากแก้ปัญหาด้วยตัวเองไม่ได้ ก็อย่าลืมพึ่งกาอากู๋ Google หรือจะถามทางผู้รู้ Excel ทั้งหลายก็ได้ คนไทยเก่งๆ ก็มีเยอะครับ ตามนี้เลย ==> 4 จักรพรรดิ และ 7 เทพโจรสลัด แห่งวงการเอ็กเซล (จากเว็บ วิศวกรรีพอร์ต)

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