ใน EP.2 เราสร้างตาราง KPI ใน Excel ได้แล้ว แต่ยังมีปัญหาอยู่ — Status ต้องพิมพ์เอง ทุกครั้ง
ในตอนนี้เราจะแก้ปัญหานั้นด้วย สูตรคำนวณ KPI ที่จำเป็น ทั้ง Achievement %, Inverse KPI สำหรับ KPI ที่ยิ่งน้อยยิ่งดี, สูตร IF สำหรับ Status อัตโนมัติ, Data Validation เพื่อป้องกันข้อมูลผิด และการ Protect สูตรไม่ให้ถูกลบ
ทุกสูตรมีตัวอย่างจริง — ทำตามได้เลย
TL;DR: ใช้
=Actual/Targetสำหรับ KPI ปกติ,=2-Actual/Targetสำหรับ Inverse KPI, และ=IF(D2>=1,"On Track","At Risk")เพื่อให้ Status แสดงอัตโนมัติ ดาวน์โหลด template พร้อมสูตรได้ท้ายบทความ
สิ่งที่ต้องเตรียม
- ไฟล์ Excel จาก EP.2 (หรือ ดาวน์โหลด EP2-KPI-Basic-Template.xlsx)
- ถ้ายังไม่ได้ทำ EP.2 แนะนำให้กลับไปดู EP.2: เริ่มต้นสร้าง KPI ใน Excel ก่อน
หรือ ดาวน์โหลด EP3-KPI-Formulas.xlsx ฟรี ที่ท้ายบทความ เพื่อทำตามไปพร้อมกัน
Step 1: สูตร Achievement % (KPI ปกติ)
นี่คือสูตรพื้นฐานที่สุดของการคำนวณ KPI — เปรียบเทียบผลจริงกับเป้าหมาย
สูตร:
=Actual/Target
หรือในตาราง Excel ของเรา:
=C2/B2
หลักการ: ถ้า Actual ≥ Target → ผลลัพธ์ ≥ 100% (ถึงเป้า)
ตัวอย่าง:
| KPI Name | Target (B) | Actual (C) | Achievement % (D) |
|---|---|---|---|
| Sales Revenue | 10,000,000 | 9,500,000 | 95% |
| Customer Satisfaction | 4.5 | 4.7 | 104% |
| Employee Retention | 90% | 92% | 102% |
สูตรนี้ใช้ได้กับ KPI ที่ยิ่งมากยิ่งดี เช่น ยอดขาย, คะแนนความพึงพอใจ, อัตราการรักษาพนักงาน
สิ่งที่ต้องระวัง:
ถ้า Target เป็น 0 สูตรจะ Error (#DIV/0!) วิธีแก้:
=IF(B2=0,"N/A",C2/B2)
สูตรนี้เช็คก่อนว่า Target เป็น 0 หรือไม่ — ถ้าใช่จะแสดง “N/A” แทนที่จะ Error
Step 2: สูตร Inverse KPI (ยิ่งน้อยยิ่งดี)
KPI บางตัว ยิ่งน้อยยิ่งดี เช่น:
– Defect Rate (อัตราของเสีย)
– Customer Complaints (จำนวนข้อร้องเรียน)
– Employee Turnover (อัตราการลาออก)
– Cost per Unit (ต้นทุนต่อหน่วย)
ถ้าใช้สูตร =Actual/Target ตรงๆ ผลจะกลับด้าน — Defect Rate ต่ำกว่าเป้า (ดี) กลับได้ Achievement ต่ำกว่า 100%
สูตร Inverse KPI:
=2-(Actual/Target)
ในตาราง Excel:
=2-(C2/B2)
ทำไมต้องสูตรนี้?
| สถานการณ์ | Actual/Target | สูตร 2-(A/T) | ผลลัพธ์ |
|---|---|---|---|
| Actual = Target (พอดีเป้า) | 1.00 | 2-1.00 = 100% | ถึงเป้า |
| Actual < Target (ดี) | 0.67 | 2-0.67 = 133% | เกินเป้า |
| Actual > Target (ไม่ดี) | 1.50 | 2-1.50 = 50% | ต่ำกว่าเป้า |
ตัวอย่างจริง:
| KPI Name | Type | Target | Actual | สูตร | Achievement % |
|---|---|---|---|---|---|
| Defect Rate | Inverse | 3% | 2% | =2-(2%/3%) | 133% |
| Customer Complaints | Inverse | 10 ครั้ง | 15 ครั้ง | =2-(15/10) | 50% |
| Cost per Unit | Inverse | 500 บาท | 450 บาท | =2-(450/500) | 110% |
ผลลัพธ์สื่อความหมายถูกต้อง — Defect Rate ต่ำกว่าเป้า = Achievement สูงกว่า 100%
Step 3: สูตร IF สำหรับ Status อัตโนมัติ
ใน EP.2 เราพิมพ์ Status ด้วยมือ ตอนนี้ถึงเวลาให้ Excel คำนวณ Status เอง
สูตร IF แบบง่าย (2 สถานะ):
=IF(D2>=1,"On Track","At Risk")
อ่านสูตร: ถ้า Achievement % (D2) ≥ 100% → แสดง “On Track” ถ้าไม่ → แสดง “At Risk”
วิธีทำ:
- คลิกที่ Cell E2
- พิมพ์
=IF(D2>=1,"On Track","At Risk") - กด Enter
- ลาก Fill Handle (มุมขวาล่าง) ลงไปถึง E4
ผลลัพธ์:
| KPI Name | Achievement % | Status |
|---|---|---|
| Sales Revenue | 95% | At Risk |
| Customer Satisfaction | 104% | On Track |
| Employee Retention | 102% | On Track |
ตอนนี้ไม่ต้องพิมพ์ Status เองอีกแล้ว — เปลี่ยนตัวเลข Actual เมื่อไหร่ Status ก็อัพเดทอัตโนมัติ
Step 4: Nested IF สำหรับ 3 ระดับ
2 สถานะอาจไม่ละเอียดพอ ลองเพิ่มเป็น 3 ระดับ:
- On Track — ≥ 100% (ถึงเป้า)
- At Risk — 80-99% (ใกล้เป้า แต่ยังไม่ถึง)
- Below Target — < 80% (ต่ำกว่าเป้ามาก)
สูตร Nested IF:
=IF(D2>=1,"On Track",IF(D2>=0.8,"At Risk","Below Target"))
อ่านสูตรทีละส่วน:
=IF(D2>=1, ← เช็คก่อน: ≥ 100% ไหม?
"On Track", ← ถ้าใช่ → On Track
IF(D2>=0.8, ← ถ้าไม่ เช็คต่อ: ≥ 80% ไหม?
"At Risk", ← ถ้าใช่ → At Risk
"Below Target" ← ถ้าไม่ → Below Target
)
)
ตัวอย่างผลลัพธ์:
| KPI Name | Achievement % | Status |
|---|---|---|
| Sales Revenue | 95% | At Risk |
| Customer Satisfaction | 104% | On Track |
| Defect Rate | 50% | Below Target |
| Employee Retention | 102% | On Track |
| Cost per Unit | 110% | On Track |
Tip: เพิ่มสีให้ Status
เพื่อให้อ่านง่ายขึ้น ใช้ Conditional Formatting กับ Column E:
- เลือก E2:E10
- Home → Conditional Formatting → New Rule
- เลือก Format only cells that contain
- สร้าง 3 rules:
| Rule | Condition | Fill Color |
|---|---|---|
| 1 | Cell Value = “On Track” | เขียวอ่อน |
| 2 | Cell Value = “At Risk” | เหลือง |
| 3 | Cell Value = “Below Target” | แดงอ่อน |
ตอนนี้ Status จะ เปลี่ยนสีอัตโนมัติ ตามสถานะ
Step 5: Data Validation สำหรับ Dropdown
ถ้าบาง Column ต้องการให้เลือกจากตัวเลือกที่กำหนดไว้ (เช่น ประเภท KPI, ชื่อแผนก) ใช้ Data Validation เพื่อสร้าง Dropdown list
สร้าง Reference Sheet:
- สร้าง Sheet ใหม่ ตั้งชื่อ “Reference”
- ใส่ข้อมูลใน Column A:
| Cell | ค่า |
|---|---|
| A1 | On Track |
| A2 | At Risk |
| A3 | Below Target |
สร้าง Dropdown:
- กลับไปที่ Sheet หลัก
- เลือก E2:E10 (Column Status)
- ไปที่ Data → Data Validation
- ตั้งค่า:
- Allow: List
- Source:
=Reference!$A$1:$A$3 - กด OK
ตอนนี้ Column E จะมี ลูกศร dropdown ให้เลือก — ไม่ต้องพิมพ์เอง ลดโอกาสพิมพ์ผิด
เมื่อไหร่ใช้ Dropdown vs สูตร IF?
| วิธี | ใช้เมื่อ |
|---|---|
| สูตร IF | Status คำนวณจากตัวเลข Achievement % ได้เลย |
| Dropdown | ข้อมูลที่ต้องเลือกเอง เช่น ประเภท KPI, ชื่อแผนก, ความถี่การวัด |
ใน Template ของ EP.3 เราจะใช้ ทั้งสองแบบ — สูตร IF สำหรับ Status, Dropdown สำหรับ KPI Type (Regular/Inverse)
Step 6: Protect สูตรไม่ให้ถูกลบ
ปัญหาที่พบบ่อย — คนอื่นมาลบสูตรโดยไม่ตั้งใจ แล้วตาราง KPI ก็พัง
วิธีแก้: ล็อค Cell ที่มีสูตร แต่ปล่อยให้กรอกข้อมูลได้ตามปกติ
วิธีทำ:
ขั้นที่ 1: ปลดล็อคทุก Cell ก่อน
- กด Ctrl+A (เลือกทั้ง Sheet)
- คลิกขวา → Format Cells → Tab Protection
- เอาเครื่องหมายถูกออก จาก “Locked”
- กด OK
ขั้นที่ 2: ล็อคเฉพาะ Cell ที่มีสูตร
- เลือก D2:E10 (Achievement % และ Status — Cell ที่มีสูตร)
- คลิกขวา → Format Cells → Tab Protection
- ติ๊ก “Locked”
- กด OK
ขั้นที่ 3: เปิด Sheet Protection
- ไปที่ Review → Protect Sheet
- ตั้ง Password (ถ้าต้องการ — ไม่บังคับ)
- ติ๊ก “Select unlocked cells”
- กด OK
ผลลัพธ์:
| Cell | สถานะ | คนอื่นทำได้ |
|---|---|---|
| A2:A10 (KPI Name) | Unlocked | แก้ไขได้ |
| B2:C10 (Target, Actual) | Unlocked | กรอกข้อมูลได้ |
| D2:D10 (Achievement %) | Locked | แก้ไขไม่ได้ |
| E2:E10 (Status) | Locked | แก้ไขไม่ได้ |
ตอนนี้ถ้าใครพยายามแก้ Cell สูตร Excel จะแจ้งเตือนว่า “This cell is protected and therefore read-only”
ตารางที่เสร็จแล้ว
หลังจากทำครบ 6 Steps ตาราง KPI ของคุณจะมีครบ:
| KPI Name | Type | Target | Actual | Achievement % | Status |
|---|---|---|---|---|---|
| Sales Revenue | Regular | 10,000,000 | 9,500,000 | 95% | At Risk |
| Customer Satisfaction | Regular | 4.5 | 4.7 | 104% | On Track |
| Employee Retention | Regular | 90% | 92% | 102% | On Track |
| Defect Rate | Inverse | 3% | 5% | 33% | Below Target |
| Cost per Unit | Inverse | 500 | 450 | 110% | On Track |
สูตรทำงานอัตโนมัติ — แค่เปลี่ยนตัวเลข Actual ทุกอย่างอัพเดทเอง
ข้อผิดพลาดที่พบบ่อย
1. ลืมเปลี่ยนสูตรสำหรับ Inverse KPI
ใช้ =C/B กับ KPI ที่ยิ่งน้อยยิ่งดี → ผลลัพธ์กลับด้าน ต้องใช้ =2-(C/B) แทน
2. สูตร IF ใส่ % ผิด
เขียน =IF(D2>=100,...) แทน =IF(D2>=1,...) — ถ้า Format เป็น % แล้ว ค่าจริงใน Cell คือ 0.95 ไม่ใช่ 95
3. ลืม $ ใน Data Validation
ใส่ Source เป็น =Reference!A1:A3 แทน =Reference!$A$1:$A$3 — ถ้าไม่มี $ พอ copy ไป Cell อื่นจะเลื่อนผิดแถว
4. Protect Sheet ก่อนปลดล็อค Data Cells
ถ้า Protect Sheet ตอนที่ทุก Cell ยังเป็น Locked → ไม่มีใครกรอกข้อมูลได้เลย ต้องปลดล็อค Data Cells ก่อน Protect
5. สูตร #DIV/0! Error
Target เป็น 0 → หารด้วย 0 ไม่ได้ ใช้ =IF(B2=0,"N/A",C2/B2) เพื่อป้องกัน
สิ่งที่ได้เรียนรู้ใน EP.3
| สิ่งที่ทำ | Excel Skill ที่ได้ |
|---|---|
| สูตร Achievement % | Basic division formula |
| สูตร Inverse KPI | =2-(Actual/Target) |
| สูตร IF (2 สถานะ) | IF function |
| Nested IF (3 สถานะ) | Nested IF function |
| สี Status อัตโนมัติ | Conditional Formatting with text rules |
| Data Validation dropdown | Data Validation, Reference sheet |
| Protect สูตร | Cell protection, Sheet protection |
ลองทำเอง: ปรับ KPI ขององค์กรคุณ
ลองเพิ่ม KPI ขององค์กรคุณเอง แล้วตอบคำถามนี้:
- KPI ตัวไหนเป็น Regular (ยิ่งมากยิ่งดี)?
- KPI ตัวไหนเป็น Inverse (ยิ่งน้อยยิ่งดี)?
- ใช้สูตรไหนกับ KPI แต่ละตัว?
ตัวอย่าง KPI แยกตามประเภท:
| Regular (ยิ่งมากยิ่งดี) | Inverse (ยิ่งน้อยยิ่งดี) |
|---|---|
| ยอดขาย | อัตราของเสีย |
| คะแนนความพึงพอใจ | จำนวนข้อร้องเรียน |
| อัตราการรักษาพนักงาน | ต้นทุนต่อหน่วย |
| จำนวนลูกค้าใหม่ | ระยะเวลาการผลิต |
ข้อจำกัดของตารางนี้ (แก้ใน EP ถัดไป)
| ข้อจำกัด | แก้ใน EP ไหน |
|---|---|
| สีแค่ Status text | EP.4 — Conditional Formatting เต็มรูปแบบ |
| ไม่มีมิติเวลา (เดือน) | EP.5 — Monthly Tracking |
| ไม่มีเจ้าของ KPI | EP.6 — Multiple Owners |
| ไม่มีน้ำหนัก KPI | EP.7 — KPI Weighting |
| ยังไม่เป็น Dashboard | EP.8 — Complete Dashboard |
ทุกตอน ต่อยอดจากตอนก่อนหน้า — สูตรที่เรียนวันนี้จะถูกใช้ไปตลอดทั้ง Series
คำถามที่พบบ่อย (FAQ)
สูตร Inverse KPI ทำไมต้องใช้ 2-Actual/Target?
สูตร =2-(Actual/Target) ทำให้ผลลัพธ์อยู่ใน scale เดียวกับ KPI ปกติ คือ 100% = ถึงเป้า ถ้า Actual เท่ากับ Target จะได้ 2-1 = 100% ถ้า Actual น้อยกว่า Target (ดี) จะได้มากกว่า 100% ทำให้เปรียบเทียบ KPI ทุกตัวด้วยมาตรฐานเดียวกันได้
มีสูตรอื่นที่ใช้ได้เหมือนกัน เช่น =1-(Actual-Target)/Target แต่สูตร =2-(Actual/Target) เข้าใจง่ายกว่าและใช้กันแพร่หลายที่สุด
ใช้ Google Sheets แทน Excel ได้ไหม?
ได้ สูตรทั้งหมดในบทเรียนนี้ใช้ได้ใน Google Sheets เหมือนกัน ทั้ง IF, Nested IF และ Data Validation ทำงานเหมือนกัน แต่เมนูอาจอยู่คนละที่ เช่น Data Validation ใน Google Sheets อยู่ที่ Data → Data validation
ทำไม IF function แสดงผลผิด?
สาเหตุที่พบบ่อยที่สุดคือ ใช้ค่า 100 แทน 1 ในเงื่อนไข ถ้า Cell D2 Format เป็น Percentage ค่าจริงใน Cell คือ 0.95 (ไม่ใช่ 95) ดังนั้นเงื่อนไขต้องเป็น D2>=1 ไม่ใช่ D2>=100 ลองคลิกที่ Cell แล้วดูค่าใน Formula Bar
KPI ที่มีทั้งขั้นต่ำและขั้นสูง ใช้สูตรไหน?
บาง KPI ต้องอยู่ในช่วงที่กำหนด เช่น “อุณหภูมิห้องเก็บสินค้า 18-22 องศา” กรณีนี้ใช้สูตร IF ซ้อนกัน:
=IF(AND(C2>=18,C2<=22),"On Track","At Risk") — ในตอนต่อๆ ไปจะมีสูตรที่ซับซ้อนกว่านี้
ควรตั้ง Password ตอน Protect Sheet ไหม?
สำหรับไฟล์ที่ใช้ภายในทีม ไม่จำเป็นต้องใส่ Password เพราะจุดประสงค์คือป้องกันการลบสูตร “โดยไม่ตั้งใจ” ไม่ใช่ป้องกันคนที่ต้องการแก้ไขจริงๆ ถ้าใส่ Password แล้วลืม จะยุ่งยากในการแก้ไขภายหลัง
Nested IF ซ้อนได้สูงสุดกี่ชั้น?
Excel 2019/365 รองรับ IF ซ้อนได้สูงสุด 64 ชั้น แต่ในทางปฏิบัติ ถ้าเกิน 3-4 ชั้น แนะนำให้ใช้ VLOOKUP, IFS, หรือ SWITCH แทน เพราะอ่านง่ายกว่ามาก (จะสอนใน EP.18)
📥 ดาวน์โหลดฟรี: EP3-KPI-Formulas.xlsx
ในไฟล์จะมี 4 Sheets:
– Instructions — คำแนะนำการใช้งานภาษาไทย
– Example — ตัวอย่างเต็มรูปแบบ พร้อมสูตรทุกแบบ (Regular + Inverse + IF + Nested IF)
– Template — ว่างเปล่า พร้อมใช้งานทันที (มี Header + สูตร + Protection พร้อม)
– Formulas — รวมสูตรทั้งหมดที่ใช้ใน EP.3 พร้อมคำอธิบาย
ตอนต่อไป
Episode 4: Conditional Formatting & Status Colors
ในตอนหน้า เราจะ:
– เปลี่ยนสีตัวเลข Achievement % ด้วย Color Scales แบบละเอียด
– เพิ่ม Icon Sets (ลูกศร, ไฟจราจร, ดาว) เพื่อดูสถานะแบบเร็ว
– สร้าง Custom Rules สำหรับเงื่อนไขเฉพาะ
– ทำให้ตาราง KPI ดูเป็นมืออาชีพ ด้วย Formatting ที่เหมาะสม
[Link to EP 4 →]
Tags: #KPI #Excel #PerformanceManagement #ExcelTutorial #KPIFormula #ExcelIF
Series: Excel Performance Management (EP.3/30)