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

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

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

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

cell-reference

เมื่อรู้พิกัด เราก็จะอ้างอิง 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 นั้นแยกเรื่องของ “ข้อมูล” กับ “การแสดงผล” ออกจากกันครับ… บางทีสิ่งที่เราเห็นว่าเป็นแบบนึง ข้อมูลข้างในอาจเป็นอีกแบบก็ได้

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

basic1

ใน 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 จากเวลาเริ่มละจบงานจึงใช้การคูณธรรมดาก็ตอบได้แล้ว

basic2

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

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

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

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

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

triangle-area-formula

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

  • คำนวณปกติ เช่น =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

copy-paste-Relative

เช่น ในช่อง 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 ครั้ง

prevent-duplicate-entry2

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

dropdown3

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

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

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

data-source ==> pivot-sample-01

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

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

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

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

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

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

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

chart-animate-1

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

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

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

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

sudoku

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

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

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

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