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

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

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

เคล็ดวิชา 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 เข้าช่วย เพราะมีความสามารถนับข้อมูลเท่าที่มองเห็นได้ (รายละเอียดอ่านได้ที่ http://www.inwexcel.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 ข้อมูล จะยิ่งเร็วแถมง่ายด้วย !!!
  • จงเชื่อมั่นและเปิดใจเรียนรู้ เพราะ การเรียนรู้มันไม่สิ้นสุดจริงๆ ครับ ผมเองก็ยังไม่เรื่องที่ไม่รู้อีกมากมาย
  • หากคุณรู้อะไรเจ๋งๆ อย่าลืมมาแบ่งปันความรู้ให้คนอื่นได้รู้ด้วยนะครับ รับรองว่า “ยิ่งให้ยิ่งได้” จริงๆ ครับ