17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋

นี่คือของขวัญที่ผมตั้งใจเตรียมไว้ให้กับทุกคน ด้วยบทความ “17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋” ซึ่ง Concept คืออ่านบทความเดียวแล้วเก่ง Excel ขึ้นอย่างรวดเร็ว!!

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

หมายเหตุ : จุดประสงค์ในบทความนี้จะเน้นให้คุณไม่พลาด Concept เจ๋งๆ ซึ่งบางทีอาจไม่สามารถอธิบายโดยละเอียดได้ในบทความเดียว ดังนั้นถ้าอ่านแล้วยังสงสัยในวิธีทำ ก็สามารถ Google หาวิธีทำละเอียดจาก Keyword ได้เลยครับ

17 เคล็ดวิชา เก่ง Excel

เคล็ดวิชา 1 : หลักคิดพิชิตทุกปัญหา

หลักการนี้ คือ สิ่งที่ผมใช้ในการแก้ปัญหาเกือบทุกๆ อย่างใน Excel ซึ่งหลักการเหล่านี้เป็นแก่นที่แฝงอยู่ในเคล็ดลับข้ออื่นๆ ด้วย

• หาเป้าหมายหรือโปรเจคที่อยากจะทำให้ได้ การเรียนรู้แบบมีเป้าหมาย จะช่วยให้เรียนรู้ไวและจับต้องได้มากกว่าอ่านเฉยๆ
• คิดย้อนกลับจากเป้าหมาย (Backward Thinking)
  • เมื่อต้องการผลลัพธ์อะไรก็ตาม ให้คิดย้อนกลับว่าเราจะไปถึงเป้าหมายนั้นด้วยวิธีใด
  • เช่น หากอยากเขียนสูตรที่สามารถดึงค่าชื่อ Sheet ได้
    • เราอาจไปรู้มาว่า ฟังก์ชัน =CELL(“filename”,A1) จะให้ชื่อ Sheet กลับมาได้
    • แต่มันดันให้ค่ากลับมา ในรูปแบบของ –>  path[ชื่อไฟล์.สกุล]ชื่อsheet
    • ทีนี้ก็ต้องมาคิดต่อว่าจะเอาออกมาแต่ ชื่อSheet อย่างเดียวได้ยังไง?
• แบ่งงานใหญ่ให้เป็นงานย่อย (Divide & Conquer)
  • หากงานหนึ่งๆ ที่จะทำมันยากและซับซ้อนเกินไป ให้แบ่งมันเป็นงานย่อยๆ ที่เราเข้าใจและจัดการกับมันได้
  • เช่น เป้าหมาย คือ ดึง ชื่อSheet ออกมา จาก path[ชื่อไฟล์.สกุล]ชื่อsheet
    • เราก็ต้องคิดต่อว่า ตัวเราเองรู้ได้ไงว่าชื่อSheet อยู่ไหน? ซึ่งมันอยู่เครื่องหมาย ] นั่นเอง
    • ซึ่งอาจใช้ FIND หาตำแหน่ง ]
    • แล้วเอา LEN RIGHT มาช่วยตัดชื่อ Sheet ออกมา อีกที
  • ฟังก์ชันไหนใช้ไม่เป็น ก็ค่อยๆ หาทางเรียนรู้เพิ่มเติม
    • สมมติว่าคุณยังไม่รู้ฟังก์ชันใน Excel เท่าที่ควร
    • ลองตั้งเป้าในการเรียนรู้ฟังก์ชันใหม่ๆ เพิ่มขึ้นวันละ 1 ตัวดูมั้ยล่ะครับ?
    • แค่ 1 เดือน คุณก็รู้ฟังก์ชันมากมายมหาศาลแล้ว
    • การแบ่งทำวันละ 1 อัน มันดูเป็นไปได้มากกว่าการเรียนฟังก์ชัน 30 ตัวเยอะครับ!
• ใช้ Excel ภาษาอังกฤษ และเรียนรู้ศัพท์เทคนิคภาษาอังกฤษเอาไว้
  • Keyword ที่ผมจะให้ในบทความนี้ก็จะเป็นภาษาอังกฤษนะครับ
  • Google หาคำตอบ ด้วยภาษาอังกฤษ จะเจอคำตอบง่ายกว่าเยอะครับ

เคล็ดวิชา 2: การเขียนสูตรคือ อะไรกันแน่?

• การเขียนสูตรคือ “การดึง” ไม่ใช่การผลัก
  • อยากให้ผลลัพธ์แสดงในช่องไหน ต้องเขียนสูตรช่องนั้น
  • หากเราจะเขียนสูตรให้ช่อง B3 แสดงค่าอะไรซักอย่าง เราต้องเขียนสูตรที่ B3 เท่านั้น
  • ไม่สามารถไปเขียนสูตรที่อื่นแล้วผลักค่าไปที่ช่อง B3 โดยที่ B3 ไม่เขียนอะไรเลย (ถ้าจะทำต้องใช้ VBA สั่ง)
  • การดึงค่าแบบตรงๆ นี่แหละ ช่วยให้เราไม่ต้องพิมพ์ข้อมูลซ้ำๆ กันหลายที
  • เรา”ดึงค่า” จาก Cell ไหนก็ได้ โดยกด = แล้วจิ้ม Cell ที่ต้องการ ไม่ว่าจะอยู่ Sheet เดียวกัน/Sheet อื่น/ไฟล์อื่นก็ได้
• นอกจากจะดึงค่าตรงๆ แล้ว สิ่งที่ต้องทำก็ไม่พ้นการเอามาใส่สูตรเพื่อ “ผูกความสัมพันธ์” เพื่อสร้างผลลัพธ์บางอย่าง
  • ความสัมพันธ์ อาจเป็นสิ่งที่เป็นสากล เช่น การคำนวณทางคณิตศาสตร์/วิทยาศาสตร์ หรือ
  • ความสัมพันธ์ อาจเป็นสิ่งที่เราคิดหรือตั้งเกณฑ์ขึ้นมาเอง เช่น การประมาณการยอดขาย เป็นต้น
• เราไม่สามารถที่จะทำให้ Cell หนึ่งๆ เป็นทั้งสูตร และเป็นทั้งช่องกรอกข้อมูลพร้อมๆ กันได้ (ถ้ากรอกข้อมูลทับสูตรจะหายไป)
• สุดท้ายแล้ว จงมองให้เห็น Flow ของข้อมูล ที่ถูกเชื่อมด้วยการเขียนสูตรผูกความสัมพันธ์
• การแยกพื้นที่ระหว่าง Input และ Output ออกจากกันอย่างเป็นระเบียบ จะช่วยให้ Flow ของข้อมูลเป็นระเบียบและแก้ไขได้ง่าย

เคล็ดวิชา 3: แก้ Format ไม่เกี่ยวกับ Content

• เรื่องของ Format เป็นแค่การกำหนดรูปแบบการแสดงผลให้เราเห็น Content ในรูปแบบที่เปลี่ยนไปตามต้องการ
  • คล้ายกับการเปลี่ยนเคสของมือถือ หรือ เปลี่ยน Theme ของ App Line นั่นแหละ
• ตัวอย่างเช่น
  • หากเราเขียนใน A1 เป็น 3.5
  • แล้วเขียนใน A2 ว่า =A1 (จะได้ 3.5 เหมือน A1)
  • จากนั้นเปลี่ยน Number Format ลดทศนิยมใน A1 จะเห็นเป็นเลข 4 (เพราะ 3.5 ปัดขึ้นเป็น 4)
  • ค่าที่แท้จริงใน A1 ยังคงเป็น 3.5 อยู่ดี เช่นเดียวกับค่าใน A2 (ที่ดึงค่าใน A1 ไปแสดง)
• เรื่องของ Format กับ Content จะถูกพูดถึงอย่างเด่นชัดอีกทีในเคล็ดวิชาที่ 6 เรื่องวันที่และเวลาครับ

เคล็ดวิชา 4: ประเภทของข้อมูลนั้นสำคัญยิ่ง!

• ประเภทข้อมูลมี 4 แบบหลักๆ คือ Number, Text, Logic, Error
• เช็คประเภทข้อมูลด้วยตาเปล่าไม่ได้ ให้ใช้ฟังก์ชัน TYPE มาช่วย จะได้ผลลัพธ์เป็นตัวเลข
  • 1 = Number, 2=Text, 4= Logic, 16 = Error
• หากข้อมูลเป็นคนละประเภทกัน แม้จะดูเหมือนกัน แต่ Excel จะถือว่าไม่เท่ากัน เช่น พวกนี้จะได้ FALSE ทั้งหมด
  • “10”=10
  • 1 = TRUE
  • 0 = FALSE
• เช่นเดียวกับการใช้ Lookup Function อย่าง VLOOKUP และ MATCH ที่จะหาเจอเฉพาะข้อมูลประเภทเดียวกันเท่านั้น
• Input หรือ Argument ของฟังก์ชันต่างๆ ก็จะต้องการประเภทข้อมูลที่ค่อนข้างเฉพาะเจาะจง ต้องใส่ข้อมูลให้ถูกประเภท
• นอกจากนี้ ผลลัพธ์ของฟังก์ชันต่างๆ ก็จะให้ประเภทข้อมูลที่แตกต่างกันด้วย

เคล็ดวิชา 5: Operator ใช้แปลงประเภทข้อมูลได้

• Operator แต่ละประเภท เมื่อนำไปประกอบกับในสูตร จะให้ “ประเภทผลลัพธ์ของข้อมูล” ที่แตกต่างกัน
• Arithmetic Operator  เช่น +, -, *, /, ^, % เอาไว้คำนวณ
  • ทำให้ผลลัพธ์เป็น Number
  • ทำได้หลายแบบ ยังไงก็ได้ให้ผลลัพธ์ไม่เพี้ยน เช่น *1,+0,-0,/1
    • แต่ที่นิยมคือ ใช้การ *1 หรือ — นำหน้า
    • =”100″*1 จะได้ 100 (เป็น Number)
    • =–TRUE จะได้ 1 (เป็น Number)
• Text Operator เช่น & เอาไว้เชื่อมข้อความ
  • ทำให้ผลลัพธ์เป็น Text
  • เช่น 200&”” จะได้ “200” (เป็น Text)
• Comparison Operator เช่น >, =, <, >=, <=, <>
  • ทำให้ผลลัพธ์เป็น Logic
  • เช่น =500>1000 ได้ FALSE
• Range Operator เช่น , (comma) : (colon)  (space) เอาไว้เชื่อม Cell Reference
  • ทำให้ผลลัพธ์เป็น Cell Reference/Range
    
  • comma เชื่อมแบบไม่ต่อเนื่อง เช่น C10,D12
  • colon เชื่อมแบบต่อเนื่องกัน เช่น A1:B5
  • space เอาส่วนที่ทับซ้อนกัน เช่น A2:E3 C1:D5 จะได้ C2:D3
• Tips : ข้อมูลที่มี Error เช่น #DIV/0!, #N/A, #NAME?
  • ทำให้ผลลัพธ์ Error ตามไปด้วย
  • นอกจากจะใช้ฟังก์ชันพวก ISERROR, IFERROR เข้ามาช่วย

เคล็ดวิชา 6: วันที่และเวลา คือ ตัวเลข จำนวนเต็มและทศนิยม ที่เปลี่ยน Format ไปเท่านั้น

• Excel จะมองวันที่เป็นแค่เลขจำนวนเต็มธรรมดาๆ และมองเวลาเป็นทศนิยม (ส่วนหนึ่งของวัน)
  • วันที่ 1/1/1900 (ค.ศ.) เวลา 0:00 เป็นเลข 1
  • วันที่ 1/1/1900 (ค.ศ.) เวลา 12:00 เป็นเลข 1.5
  • วันที่ 2/1/1900 (ค.ศ.) เวลา 0:00 เป็นเลข 2
  • วันที่ 2/1/1900 (ค.ศ.) เวลา 12:00 เป็นเลข 2.5
• จะแปลงตัวเลข เป็นวันที่/เวลา หรือกลับกัน ทำได้ง่ายๆ โดยการเปลี่ยน Number Format
• ดังนั้นการหาระยะห่างของวัน 2 วัน จึงเอาวันที่มาลบกันตรงๆ ได้เลย เช่น
  • A1=28/11/2016 และ A2=4/12/2016
  • ระยะห่าง = A2-A1 = 6 วัน
  • เพราะค่าที่แท้จริง คือ A1=42702 และ A2 = 42708
• ดังนั้นถ้ามีวันที่+เวลาอยู่ใน Cell หนึ่งๆ เช่น A1
  • จะแยกวันออกมาใช้ INT เอาจำนวนเต็ม นั่นคือ =INT(A1)
  • จะแยกเวลาใช้ MOD หาร 1 เพื่อเอาทศนิยม นั่นคือ =MOD(A1,1)
• เวลา Input ข้อมูลวันที่ Excel จะตีความปีที่กรอกเป็น ค.ศ. (ยกเว้นไปตั้งค่า Format Cell นั้นๆ เป็นปฏิทินไทย แล้วเลือก Input dates according to selected calendar จะสามารถกรอกเป็น พ.ศ.ได้)

เคล็ดวิชา 7: การใส่ $ ใน Cell Reference

• เมื่อทำการ Copy สูตรที่มี Cell Reference อยู่ในสูตร จะทำให้ Cell Reference เลื่อนตำแหน่งตามทิศทางการ Copy/Paste โดยอัตโนมัติ
• หากไม่อยากให้เลื่อนต้องใส่เครื่องหมาย $ ลงไปใน Cell Reference (กด F4 ช่วยได้ สามารถกดวนรูปแบบได้ 4 อย่าง)
• หากมี $ หน้าอะไร ถือว่าตัวนั้นจะไม่เลื่อน เช่น
  • A1 –> Column เลื่อน, Row เลื่อน เรียกว่า Relative Cell Reference
  • $A$1 –> Column ไม่เลื่อน, Row ไม่เลื่อน เรียกว่า Absolute Cell Reference
  • A$1 –> Column เลื่อน, Row ไม่เลื่อน เรียกว่า Mixed Cell Reference
  • $A1 –> Column ไม่เลื่อน, Row เลื่อน เรียกว่า Mixed Cell Reference
• หลักคิดสำคัญเพื่อไม่ให้งง ว่า เมื่อไหร่ต้องใส่ $ ยังไง คือ ใช้หลักการ Divide & Conquer
  • โดยพิจารณามองทีละ Cell Reference
  • แล้วคิดทีละทิศคือ แนวนอน และ แนวตั้ง โดยถามคำถามว่า…
  • ถ้า Copy Cell นี้ไปทางขวา เจ้า Cell Reference ที่กำลังอ้างถึงอยู่ควรจะเลื่อนทางขวาด้วยมั้ย?
    • ถ้าไม่ควร ก็ใส่ $หน้ารหัสคอลัมน์
    • ถ้าควร ก็ไม่ใส่ $หน้ารหัสคอลัมน์
  • ถ้า Copy Cell นี้ลงข้างล่าง เจ้า Cell Reference ที่กำลังอ้างถึงอยู่ควรจะเลื่อนลงด้วยมั้ย?
    • ถ้าไม่ควร ก็ใส่ $หน้าเลขแถว
    • ถ้าควร ก็ไม่ใส่ $หน้าเลขแถว

เคล็ดวิชา 8: การทดข้อมูล / ทดสูตร / Helper Column ช่วยได้มาก

• การทดข้อมูล คือ การประยุกต์เอาหลัก Divide & Conquer มาใช้แบบหนึ่ง
  • การเขียนสูตรแบบซับซ้อน จริงๆ แล้วเราไม่จำเป็นต้องเขียนสูตรยาวๆ ในทีเดียว แต่เราเขียนสูตรทีละส่วน (โดยให้อ้างอิงถึงกัน) แล้วค่อยจับนำมารวมกันภายหลัง
  • เช่น อยากดึงค่าบางอย่างจากที่อื่นมาไว้ใน Cell ที่กำหนด
    • เราสามารถใช้ INDEX ได้ ในรูปแบบ =INDEX(array,row_num,column_num)
    • และใน row_num และ column_num เราต้องการให้มันมีความเป็น Dynamic โดย Link กับ Dropdown List ที่ User เลือก ดังนั้นเราจะใช้ MATCH มาช่วย
    • แต่แทนที่จะเขียน MATCH ลงไปใน INDEX เลย เราสามารถเขียนแยกไว้ในอีก Cell เพื่อลดความงง
    • พอเขียนเสร็จ ตรวจแล้วทำงานถูกต้อง ค่อยจับนำมารวบรวมกับ INDEX ทีหลัง
  • การทำแบบนี้ ฝรั่งเรียกว่า Mega Formula ซึ่งจะช่วยให้เราจะดูเทพขึ้นมาทันที !! 555
• Helper Column ก็เป็นหนึ่งในรูปแบบของการทดเหมือนกัน มันคือการสร้างคอลัมน์ที่ช่วยให้เราจัดการกับอะไรต่างๆ ง่ายขึ้น
  • สามารถเขียนเงื่อนไข AND OR ที่ซับซ้อน แทนที่จะใช้ Criteria ใน Filter/Pivot/SUMIFS/Advanced Filter ซึ่งอาจไม่ Flexible เท่าการเขียนสูตร
  • ลองดูตัวอย่าง การสร้าง Helper Column เพื่อรองรับการ Unsort ในเคล็ดวิชา 9 ได้

เคล็ดวิชา 9: การเตรียมข้อมูลเพื่อ การ Sort/Filter/Pivot

• ก่อนจะนำข้อมูลไป Sort/Filter หรือ PivotTable ควรจัดข้อมูลให้อยู่ในรูปแบบ Database ก่อนเสมอ
  • มีหัวตารางที่บอกว่าคอลัมน์นั้นๆ คือ อะไร แค่ 1 บรรทัด
  • ข้อมูลห้ามขาดหายไปทั้งบรรทัด
• ไม่ว่าจะ Sort หรือ Filter ผมแนะนำให้ใช้ คำสั่ง Filter เครื่องมือเดียวนี่แหละ
• การ Filter สามารถ Search ได้ และมีคำสั่ง Add to Current selection ด้วย เพื่อที่จะ Add สิ่งที่ Search เจอได้โดยไม่ต้อง Clear สิ่งที่เลือกไว้เดิม
• การ Filter สามารถ Clear Filter กลับมาเป็นแบบเก่าได้ แต่ Sort ไม่มีคำสั่ง Unsort
  • ดังนั้นถ้าจะอยากให้เรียงกลับเป็นเหมือนเดิมได้ ต้องใช้ Helper Column สร้างเลข Running เอาไว้ก่อนเลย
• หากอยากที่จะ Filter แล้วเลขสามารถ Run ใหม่ตามข้อมูลที่ Filter ให้ใช้ SUBTOTAL/AGGREGATE เข้าช่วย เพราะมีความสามารถนับข้อมูลเท่าที่มองเห็นได้ (รายละเอียดอ่านได้ที่ https://www.thepexcel.com/filtered-running-number/)
• ก่อนจะนำข้อมูลไปวิเคราะห์ใดๆ ควรเช็คความถูกต้องโดย Sort/Filter ดูความผิดปกติก่อน
  • เช่น ไม่มีค่าน้อยไป/มากไป
  • ไม่มีข้อมูลขยะ
  • มีข้อมูล Blank ที่ไม่ต้องการหรือไม่
• หากข้อมูลไม่อยู่ในรูปแบบที่เหมาะสม อาจต้องเขียนสูตรเพื่อดัดแปลงข้อมูลก่อน
• หากข้อมูลแยกกันอยู่หลาย Sheet อาจพิจารณาใช้สูตรพวก Lookup เพื่อรวม Database ก่อน หรือ จะใช้พวก Data Model เพื่อสร้าง Relationship โดยไม่ต้องรวมตารางก่อนก็ได้

เคล็ดวิชา 10: รู้จักการใช้ Table เพื่อสร้าง Dynamic Range

• ข้อดีที่สุดอย่างหนึ่งของการใช้ Table ก็คือ ความสามารถในการทำ Dynamic Range อย่างง่ายที่สุด
• Table สามารถยืดพื้นที่การอ้างอิง ให้งอกตามการใส่ข้อมูลที่เพิ่มขึ้นได้เองโดยอัตโนมัติ
• การอ้างอิงค่าจาก Table สามารถอ้างอิงได้ทั้งการใช้ Cell Reference ปกติ และ Structure Reference เช่น Table1[สินค้า] ดังรูป
• นำไปประยุกต์เป็น Data Source ของเครื่องมืออื่นๆ ได้มากมาย เช่น Dropdown List (Data Validation), PivotTable, กราฟต่างๆ เป็นต้น

เคล็ดวิชา 11: รู้จักใช้ความสามารถของ PivotTable

• PivotTable สามารถแปลงข้อมูลเชิง Database ให้เเป็นข้อมูลสรุปได้อย่างง่ายดาย
  • PivotTable ใช้งานง่ายมาก เขียนสูตรไม่เป็นเลยก็ยังใช้งานได้
• หัวตารางของ Data Source จะถูกสร้างให้กลายเป็น Field List
• เวลาใช้ ให้พิจารณาองค์ประกอบทีละส่วนว่า Field List แต่ละอันจะเอาไปไว้ในส่วนไหน ใน 4 ส่วนนี้
  • Filter : เอาไว้คัดกรองข้อมูลให้กับทั้งตาราง Pivot
    • มีลูกเล่น Generate Report Filter Page
    • ปกติแล้วมันจะเก็บข้อมูล Item List เอาไว้แม้ว่าใน Data Source จะไม่มีข้อมูลนั้นๆ แล้ว
    • เราสามารถทำให้ Item List มันยึดอันใหม่เสมอได้โดยไปที่ PivotTable Option –> Data –> Number of Items to retain per field –> None
  • Rows/Columns : เอาข้อมูลมาแสดงไว้คนละแถว/คอลัมน์ (แบบไม่ซ้ำกัน) เพื่อเป็น Caetgory ให้กับตารางสรุป
    • มีลูกเล่น Sort/Filter/Grouping
    • เราสามารถ Grouping ข้อมูลใน Row/Column Label ได้ ทั้งแบบ Manual และ แบบ Auto
      • Grouping แบบ Manual เหมาะกับข้อมูลที่เป็น Text และมันจะสร้าง Field ใหม่ขึ้นมาให้ด้วย
      • Grouping แบบ Auto ข้อมูล Field นั้นๆ จะต้องเป็นตัวเลข หรือ วันที่เท่านั้น และห้ามมีช่องว่าง จึงจะใช้ได้
    • ปกติแล้ว ข้อมูลใน Row/Column Label  ใดที่ไม่มีค่า มันจะไม่แสดง Row/Column Label  นั้นๆ
      • เราสามารถบังคับให้แสดงได้โดยไปที่ Field Setting -> Layout & Print -> Show items with no data
  • Values : เอาไว้คำนวณสรุปผลข้อมูล ซึ่งมีลูกเล่นสำคัญ 2 อย่าง คือ
    • Summarized Value by : เลือกวิธีในการคำนวณสรุปผล เช่น Sum, Count, Max, Min
      • ถ้าข้อมูลเป็นตัวเลข และไม่มี Blank เลย เวลาเอามาใส่จะเป็น Sum โดยอัตโนมัติ นอกนั้นเป็น Count
    • Show Value As : เลือกว่าจะโชว์ค่าตรงๆ หรือ จะแสดงเทียบกับช่องอื่น เช่น %ของ xxx
• ข้อเสียสำคัญ ของ PivotTable คือ เมื่อข้อมูลต้นทางเปลี่ยน จะต้องกดปุ่ม Refresh ค่าใน Pivot ถึงจะเปลี่ยน (ถ้าไม่อยากกดเอง สามารถใช้ VBA ช่วยได้)
• PivotTable ปกติจะเปิดโหมด Generate GetPivotData ไว้
  • ทำให้เมื่อเขียนสูตรแล้วจิ้มไปในบริเวณ PivotTable แล้วจะเกิดสูตรยาวๆ ที่ใช้งานยาก
  • แต่ข้อดีคือ สามารถอ้างอิงค่าโดยไม่ต้องกังวลว่า PivotTable จะพลิกหน้าตาหลายเป็นแบบใด
  • วิธีปิดโหมดนี้ คือไปที่ Ribbon ของ PivotTable แล้วติ๊กลูกศรใต้ PivotTable Option
• การใช้ Calculated Field ใน PivotTable เหมาะกับ การคำนวณค่า %Success Rate ของแต่ละกลุ่มข้อมูลมาก
  • การหาพวก % Success จะทำใน Data Source ด้วยการเขียนสูตรได้ลำบากกว่าการใช้ Calculated Field ใน PivotTable มาก
  • นอกจากนี้ การสร้าง PivotTable แล้วเขียนสูตรคำนวณเองข้างๆ ก็เสี่ยงต่ออารถูกหมุนข้อมูลไปทับ
  • เทคนิคการทำ % Success ง่ายๆ คือ ให้สร้าง Helper Column 2 อันใน Data Source
    • อันแรกเป็นตัวส่วน สมมติชื่อว่า AllCase (อาจให้เป็นเลข 1 เหมือนกันทุกแถว)
    • อีกอันเป็นตัวเศษ สมมติชื่อว่า SuccessCase ซึ่งถ้า Success จะให้เป็นเลข 1 ถ้าไม่ Success ก็ให้เป็นเลข 0
  • จากนั้นตอนสร้าง Calculated Field ก็จะสามารถใส่สูตรว่า SuccessCase/AllCase ได้เลย

เคล็ดวิชา 12: หัดใช้ Keyboard Shortcut เถอะ

ใช้ Keyboard Shortcut แล้วช่วยให้ทำงานเร็วขึ้นเยอะครับ ที่ต้องใช้บ่อยๆ มีไม่กี่ตัว ที่ผมใช้บ่อยก็มีตามนี้

• กด Alt แล้วตามด้วยอักษรที่ขึ้นมา เพื่อเรียกใช้งาน Ribbon และ Quick Access Toolbar
• Ctrl + ลูกศร = วิ่งไปสุดทาง
• Ctrl + Shift + ลูกศร = เลือกพื้นที่ไปสุดทาง
• Ctrl+c = copy
• Ctrl+x = cut
• Ctrl + v = paste
• Ctrl+z = undo
• Ctrl+Shift+L = Filter on/off
• Ctrl+1 = Format Cell / Format ส่วนประกอบของกราฟ
• นอกจากนี้ยังมี Set เอาไว้เปลี่ยน Number Format ซึ่งมีวิธีการจำที่น่าสนใจมาก
  • Ctrl+% เพื่อแปลงเป็นหน่วย % (จริงๆ ต้องกด Ctrl+Shift+5 เพราะ Shift +5 คือตัว % แต่ถ้าต้องจำว่า Ctrl+Shift+5 จะไม่มีทางจำได้เลย)
  • Ctrl+^ ก็เพื่อแปลงเป็นเลข Scientific E ยกกำลัง (เพราะ ^ เป็นเครื่องหมายยกกำลัง)
  • Ctrl+$ ก็เพื่อแปลงเป็นรูปแบบสกุลเงิน
  • Ctrl+# ก็เพื่อแปลงเป็นวันที่ (เพราะในโปรแกรม Access ก็ใส่วันที่ในเครื่องหมาย #)
  • Ctrl+@ ก็เพื่อแปลงเป็นเวลา เพราะ เครื่องหมาย@ ก็ดูเจาะจง คล้ายว่าจะระบุว่า ณ กี่โมง
  • Ctrl+: Stamp เวลาปัจจุบัน เพราะเหมือนเครื่องหมายคั่น ชม:นาที (ส่วน Ctrl+; = stamp ค่าวันที่ปัจจุบัน)
  • Ctrl+* เลือกข้อมูลใน Range ทั้งหมด เพราะ * แทนความหมายว่าทั้งหมด ในภาษาฐานข้อมูล
• F2 = Edit สูตร
• F3 = Paste ชื่อที่ตั้งไว้
• F4 = ใส่ $ ใน Cell Reference (เมื่อกำลัง edit สูตร) / Repeat Action ล่าสุด

เคล็ดวิชา 13: Function ที่ต้องรู้จักมีไม่เยอะ

• รู้จักฟังก์ชั่น 20% แต่ทำงานได้ 80% ตามกฎ 80/20
• ผมมั่นใจว่า ถ้าคุณรู้จักฟังก์ชันไม่กี่อันเหล่านี้เป็นอย่างดี รับรองว่าหากินได้เยอะมาก
• สิ่งที่ต้องจำคือ “หน้าที่ของมัน” ซึ่งสำคัญกว่าวิธีการเขียน (อันหลังเปิด Google/Help เอาก็ได้)
• กลุ่มคำนวณ
  • SUM = หาผลรวมของข้อมูลที่เป็นตัวเลข
  • COUNT = นับจำนวนข้อมูลที่เป็นตัวเลข
  • COUNTA = นับจำนวนข้อมูลที่ไม่ใช่ช่องว่าง
  • MAX/MIN = หาค่ามากสุด/น้อยสุด
  • LARGE/SMALL = หาค่ามากสุด/น้อยสุด เป็นลำดับที่ xx
  • AGGREGATE = ไว้สรุปผลข้อมูล มีหลาย option เช่น ไม่สนใจช่องที่ซ่อนอยู่ ไม่สนใจค่า error เป็นต้น
  • INT = ตัดทศนิยมทิ้ง ให้เหลือแต่จำนวนเต็ม
  • MOD = หาเศษจากการหาร
  • ROUND/ROUNDUP/ROUNDDOWN = ปัดทศนิยม
  • SUMPRODUCT = จับคู่คูณแล้วหาผลรวม
  • SUMIFS = หาผลรวมตามเงื่อนไข
• กลุ่มตรรกะ
  • AND/OR/NOT = เอาไว้เชื่อมตรรกะ TRUE/FALSE
  • IF = ตรวจสอบเงื่อนไข ถ้าจริงใช้สูตร 1 ถ้าไม่จริงใช้สูตร 2
  • ISERROR = เช็คว่าค่า Error หรือไม่
  • ISNUMBER = เช็คว่าค่าเป็นตัวเลขหรือไม่
  • CHOOSE = เลือกว่าจะใช้สูตรไหนคำนวณ
• กลุ่ม Lookup & Reference
  • VLOOKUP (ทั้งโหมด Exact และ Approximate Match) = Map ข้อมูลคำค้นหาดึงค่าจากตารางอ้างอิง
  • MATCH = หาว่าคำค้นหาอยู่ลำดับที่เท่าไหร่
  • INDEX = ให้ Cell Reference กลับมาเมื่อรู้พิกัดแถว/คอลัมน์ (ใช้คู่กับ MATCH จะเก่งมาก)
  • ADDRESS = แปลงลำดับแถว/คอลัมน์ ให้เป็น Text ที่หน้าตาเป็น Cell Reference
  • INDIRECT = แปลง Text ที่หน้าตาเป็น Cell Reference ให้เป็น Cell Reference จริงๆ
  • OFFSET = เลื่อน/ปรับขนาด Cell Reference
  • ROW/COLUMN = หาว่าอยู่แถว/คอลัมน์ที่เท่าไหร่
• กลุ่ม Text
  • LEN = นับจำนวนอักขระ
  • TRIM = ตัดช่องว่างส่วนเกิน
  • LEFT/MID/RIGHT = ตัดคำจากทิศทางต่างๆ
  • FIND/SEARCH = ค้นหาคำที่ต้องการว่าอยู่อักขระที่เท่าไหร่
  • SUBSTITUTE = แทนที่คำเมื่อรู้คำที่ต้องการแทนที่
  • REPLACE = แทนที่คำเมื่อรู้ตำแหน่งที่ต้องการแทนที่
  • TEXT = แปลงตัวเลข ให้กลายเป็น Text ตาม Custom Number Format ที่กำหนด
• กลุ่มวันที่และเวลา
  • TODAY = ให้ค่าวันที่ปัจจุบัน
  • DAY = ดึงค่าวัน จาก วันที่
  • MONTH = ดึงค่าเดือน จาก วันที่
  • YEAR = ดึงค่าปี จาก วันที่
  • DATE = สร้างวันที่ จาก วัน เดือน ปี
  • NETWORKDAYS = หาระยะเวลาวันทำงาน ไม่นับวันหยุด (นับวันเริ่ม)
  • WORKDAYS = หาวันสิ้นสุดงาน  เมื่อรู้ระยะเวลาวันทำงาน ไม่นับวันหยุด (ไม่นับวันเริ่ม)
  • NOW = ให้ค่าเวลาปัจจุบัน
  • HOUR = ดึงค่า ชั่วโมง จากเวลา
  • MINUTE = ดึงค่า นาที จากเวลา

เคล็ดวิชา 14: เทคนิคการทำกราฟสุดพลิกแพลง

รูปประกอบจากเพจ Excel Nana (ขอนำมาใช้เพราะชอบกราฟนี้มาก)

• ต้องรู้จักประเภทกราฟหลักๆ ให้ดี เช่น
  • Bar/Column Chart ทั้งแบบ Cluster และ แบบ Stacked
  • Line Chart : แกน x เป็นเพียง Category label (ข้อความ)
  • XY Scatter : แกน x เป็นตัวเลขจริงๆ
  • Pie Chart
• สามารถ Link ค่าจาก Cell มาที่ Label บนกราฟได้
  • เลือก Label ไปที่ Formula Bar กด = แล้วจิ้ม Cell mี่ต้องการ
• สามารถผสมกราฟหลายชนิดในกราฟเดียวได้ (Change series chart type)
• สามาารถ Plot ข้อมูลลงในแกน Y 2 แกนได้ (มักใช้กับกรณีที่ Scale ต่างกันมากๆ)
• เทคนิคทำให้กราฟพลิกแพลงได้มักจะใช้องค์ประกอบแบบนี้
  • นำกราฟมาซ้อนกัน
  • ใช้ข้อมูลหลาย Series มาต่อกันให้เหมือนเป็น Series เดียว
  • ปรับสีของกราฟให้มองไม่เห็นบางส่วน (เช่น เลือก No Color)
  • ใช้การ Fill (ถมสี) ด้วย Picture เพื่อสร้างกราฟที่สวยงามและมีลูกเล่นมากขึ้น
• หากสร้าง PivotChart จาก PivotTable เราสามารถเอาปุ่มสีเทาที่รกๆ ออกได้ โดยไปที่ Pivot Chart –> Field Buttons –> Hide All
• ใครสนใจการทำกราฟแบบสร้างสรรค์ลองศึกษาได้จากเพจ Excel Nana ได้เลยครับ https://www.facebook.com/ExcelNaNa/

เคล็ดวิชา 15: Defined Name นั้นมีดีกว่าแค่ชื่อ

• นอกจากจะตั้งชื่อให้กับ Cell/Range ได้แล้ว ยังตั้งชื่อให้กับสูตรหรือค่าคงที่ได้ด้วย
• การตั้งชื่อมีหลาย Scope คือ ระดับ Workbook  และ Worksheet (เป็นชื่อที่ฝังอยู่ที่ Sheet ใดชีทหนึ่ง)
• ใน 1 Scope ห้ามมีชื่อที่ซ้ำกัน
• เรามักประยุกต์เอา Defined Name ไปเป็น Data Source ของเครื่องมือต่างๆ เช่น Dropdown List (Data Validation), PivotTable, กราฟต่างๆ เป็นต้น
• การใช้ Defined Name ทำให้สามารถใช้ Array Formula ได้โดยไม่ต้องกดปุ่ม Control+Shift+Enter
• การใช้ Defined Name ทำให้สามารถสร้าง Dynamic Linked Picture เพื่อสร้าง Dynamic Chart ได้ (ร่วมกับสูตร INDEX)
• Cell Reference ใน Defined Name ปกติจะใส่ $ ไว้เป็น Absolute Cell Reference โดยอัตโนมัติ แต่เราสามารถปรับออก เพื่อพลิกแพลงได้ตามต้องการ

เคล็ดวิชา 16: เนรมิต Dashboard เจ๋งๆ

รูปประกอบจากเรื่อง iron man

• Dashboard คือ การแสดงผลข้อมูลโดยเอากราฟและข้อมูลตัวเลขสำคัญๆ มาแสดงรวมกันในหน้าเดียว
• สำคัญที่สุดคือการออกแบบ Dashboard โดยคำนึงถึงคนใช้งานเป็นสำคัญ ว่าเค้าต้องการเห็นอะไร?
• เทคนิคที่สำคัญในการทำ Dashboard คือ
  • การเขียนสูตร : คำนวณค่าต่างๆ ให้แสดงได้ตามต้องการ
  • PivotTable/ PivotChart : แสดงข้อมูลสรุป
  • Slicer : Slicer 1 ตัวจะควบคุม PivotTable/PivotChart ได้หลายๆ อันพร้อมกัน แบบ Interactive
  • Sparkline : สร้าง In-cell- Chart แบบง่ายๆ เพื่อเหมาะกับการแสดงผล
  • Conditional Format : ปรับ Format เพื่อเน้นสิ่งที่ต้องการนำเสนอ แบบ Interactive เช่น ทำไฟเขียวไฟแดง,  เปลี่ยนสีพื้นหลัง, เปลี่ยนสี/รูปแบบตัวอักษร
  • Linked Picture : สามารถดึง “สิ่งที่เราเห็น” จาก Range ในที่ต่างๆ มาแสดงในอีกที่ได้ แถมย่อ/ขยาย และวางตำแหน่งได้ดั่งใจ ถ้าข้อมูลต้นทางเปลี่ยน ปลายทางก็เปลี่ยนตาม เหมือนกับการตั้งกล้องถ่ายทอดสดไว้
  • Form Control : ทำให้ Dashboard Interactive มากขึ้น เช่น ทำ Scroll Bar / Radio Button แล้วให้ส่งค่าไปยัง Cell ที่กำหนด เพื่อผูกสูตรในการแสดงผลอีกที
  • ActiveX Control & VBA (Advanced) : ไม่มีอะไรที่ VBA ทำไม่ได้ แต่ต้องเรียนรู้ค่อนข้างเยอะ
• จะเห็นว่ากว่าจะทำ Dashboard เจ๋งๆ ได้ต้องใช้ทักษะ Excel มากมายเลยทีเดียว นี่แหละเป็นเป้าหมายที่ท้าทาย

เคล็ดวิชา 17: อย่าหยุดพัฒนาตัวเอง

• จงมุ่งมั่นหาวิธีที่ดีขึ้นเสมอๆ มันอาจมีวิธีที่ดีกว่าวิธีที่คุณใช้อยู่ แค่คุณยังไม่รู้ และยังไม่ลองค้นหาเท่านั้นเอง!!
• ตัวอย่างเช่น การ Map ข้อมูล จริงๆ แล้วทำได้หลายวิธีมากๆ ตั้งแต่วิธีที่แย่ จนไปถึงวิธีที่ดี
  • วิธีที่ถึกที่สุดคือดูด้วยตา กรอกด้วยมือ (ทั้งเหนื่อยและอาจผิดพลาดได้ง่ายๆ)
  • แทนที่จะดูด้วยตา กรอกด้วยมือ ก็ใช้สูตร เช่น IF หรือ VLOOKUP แทน
  • แทนที่จะใช้ IF หลายๆ ตัวซ้อนกัน ดูสิว่าใช้ VLOOKUP แทนได้หรือไม่
  • กรณีต้อง VLOOKUP ข้อมูลบรรทัดเดียวกันหลายรอบ ใช้ INDEX + ตำแหน่งแถวที่หามาแล้ว จะเร็วกว่าเยอะ
  • ใช้ VLOOKUP Approximate Match + IF แทน VLOOKUP แบบ Exact Match กรณีที่ข้อมูลเยอะมากๆ จะคำนวณเร็วขึ้น 100-1000 เท่า!
  • ใช้ INDEX + VLOOKUP Approximate Match แทน 2 วิธีข้างบน ก็จะยิ่งเร็วขึ้นไปอีก !!
  • ถ้าเรารู้จักใช้พวก Power Tool เช่น Power Query ในการ Map ข้อมูล จะยิ่งเร็วแถมง่ายด้วย !!!
• จงเชื่อมั่นและเปิดใจเรียนรู้ เพราะ การเรียนรู้มันไม่สิ้นสุดจริงๆ ครับ ผมเองก็ยังไม่เรื่องที่ไม่รู้อีกมากมาย
• หากคุณรู้อะไรเจ๋งๆ อย่าลืมมาแบ่งปันความรู้ให้คนอื่นได้รู้ด้วยนะครับ รับรองว่า “ยิ่งให้ยิ่งได้” จริงๆ ครับ