---
title: 17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋
url: https://www.thepexcel.com/excel-booster-17-concepts/
type: post
date: 2017-01-02
updated: 2020-03-31
author: Sira Ekabut
categories: [Excel ทั่วไป, "Highlights : บทความแนะนำ"]
tags: [speed, tips, concepts]
---

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

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

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

 

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

 

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

 

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

 

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

 ![2](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-01.jpg) 

หลักการนี้ คือ สิ่งที่ผมใช้ในการแก้ปัญหาเกือบทุกๆ อย่างใน 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: การเขียนสูตรคือ อะไรกันแน่?

 

## ![3](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-02.jpg)

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

 

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

 ![4](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-03.jpg) 
- เรื่องของ 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: ประเภทของข้อมูลนั้นสำคัญยิ่ง!

 ![5](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-04.jpg) 
- ประเภทข้อมูลมี 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 ใช้แปลงประเภทข้อมูลได้

 ![6](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-05.jpg) 
- 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 ไปเท่านั้น

 ![7](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-06.jpg) 
- 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

 ![8](https://www.thepexcel.com/wp-content/uploads/2016/01/copy-paste-Relative.png) 
- เมื่อทำการ 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 ช่วยได้มาก

 ![9](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-08.jpg) 
- การทดข้อมูล คือ การประยุกต์เอาหลัก 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

 ![10](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-09.jpg) 
- ก่อนจะนำข้อมูลไป 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/](https://www.thepexcel.com/filtered-running-number/))
- ก่อนจะนำข้อมูลไปวิเคราะห์ใดๆ ควรเช็คความถูกต้องโดย Sort/Filter ดูความผิดปกติก่อน 
    - เช่น ไม่มีค่าน้อยไป/มากไป
    - ไม่มีข้อมูลขยะ
    - มีข้อมูล Blank ที่ไม่ต้องการหรือไม่
- หากข้อมูลไม่อยู่ในรูปแบบที่เหมาะสม อาจต้องเขียนสูตรเพื่อดัดแปลงข้อมูลก่อน
- หากข้อมูลแยกกันอยู่หลาย Sheet อาจพิจารณาใช้สูตรพวก Lookup เพื่อรวม Database ก่อน หรือ จะใช้พวก Data Model เพื่อสร้าง Relationship โดยไม่ต้องรวมตารางก่อนก็ได้

 

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

 ![11](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-10-2.jpg) 
- ข้อดีที่สุดอย่างหนึ่งของการใช้ Table ก็คือ ความสามารถในการทำ Dynamic Range อย่างง่ายที่สุด
- Table สามารถยืดพื้นที่การอ้างอิง ให้งอกตามการใส่ข้อมูลที่เพิ่มขึ้นได้เองโดยอัตโนมัติ
- การอ้างอิงค่าจาก Table สามารถอ้างอิงได้ทั้งการใช้ Cell Reference ปกติ และ Structure Reference เช่น Table1[สินค้า] ดังรูป
- นำไปประยุกต์เป็น Data Source ของเครื่องมืออื่นๆ ได้มากมาย เช่น Dropdown List (Data Validation), PivotTable, กราฟต่างๆ เป็นต้น

 

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

 ![12](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-11.jpg) 
- 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 เถอะ

 ![13](https://www.thepexcel.com/wp-content/uploads/2014/09/keyboard.png) 

ใช้ 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 ที่ต้องรู้จักมีไม่เยอะ

 ![14](https://www.thepexcel.com/wp-content/uploads/2015/04/80-20-pareto.png) 
- รู้จักฟังก์ชั่น 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](https://www.facebook.com/ExcelNaNa/) (ขอนำมาใช้เพราะชอบกราฟนี้มาก)*

 

[![15](https://www.thepexcel.com/wp-content/uploads/2017/01/Excel-NaNa.jpg)](https://www.facebook.com/453481854683772/videos/1311460038885945/)

 
- ต้องรู้จักประเภทกราฟหลักๆ ให้ดี เช่น 
    - 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/](https://www.facebook.com/ExcelNaNa/)

 

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

 ![เก่ง Excel](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-13.jpg) 
- นอกจากจะตั้งชื่อให้กับ 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*

 ![16](https://www.thepexcel.com/wp-content/uploads/2017/01/concept-12.png) 
- 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 ข้อมูล จะยิ่งเร็วแถมง่ายด้วย !!!
- จงเชื่อมั่นและเปิดใจเรียนรู้ เพราะ การเรียนรู้มันไม่สิ้นสุดจริงๆ ครับ ผมเองก็ยังไม่เรื่องที่ไม่รู้อีกมากมาย
- หากคุณรู้อะไรเจ๋งๆ **อย่าลืมมาแบ่งปันความรู้ให้คนอื่นได้รู้ด้วยนะครับ รับรองว่า “ยิ่งให้ยิ่งได้” จริงๆ ครับ**

---

_Source: [https://www.thepexcel.com/excel-booster-17-concepts/](https://www.thepexcel.com/excel-booster-17-concepts/)_