สูตร INDEX/MATCH สำหรับคำนวณคอมขั้นบันไดจาก GM% แบบ copy ไปใช้ได้เลย ใช้ได้ทั้ง Excel / LibreOffice / Google Sheets พร้อมกับดัก #N/A ที่บิลขาดทุนใบเดียวทำให้ยอดรวมทั้งปีหายทั้งแดชบอร์ด และวิธีแก้ที่ถูกต้อง
บทความนี้เป็นคู่มือเชิงเทคนิค สูตรทุกสูตรในหน้านี้ copy ไปวางใช้ได้จริง และตั้งใจให้คุณสร้างไฟล์เองได้ตั้งแต่ต้นจนจบ
สิ่งที่คุณจะได้จากบทความนี้
- สูตรจริงสำหรับคำนวณคอมขั้นบันไดจาก GM% (อัตรากำไรขั้นต้น)
- กับดัก #N/A ที่ทำให้บิลขาดทุนใบเดียวลบยอดรวมทั้งปีของทั้งทีม — พร้อมวิธีแก้
- ชุดเคสทดสอบ 10 เคสที่ควรใช้ตรวจไฟล์ก่อนเอาไปคิดเงินจริง
เราเจอบั๊กในข้อ 2 กับไฟล์ที่เราทำเองและปล่อยใช้ไปแล้ว บทความนี้เขียนจากบั๊กตัวนั้น
1. วางตารางขั้นให้ถูกก่อน
หัวใจของคอมขั้นบันไดคือ ตารางขั้น ที่มีสองคอลัมน์เท่านั้น
| ขั้น | GM% ตั้งแต่ (ขอบล่าง) | อัตราคอม (% ของ GP) |
|---|---|---|
| 1 | 0% | 3% |
| 2 | 10% | 5% |
| 3 | 15% | 8% |
| 4 | 20% | 10% |
| 5 | 30% | 12% |
กฎเหล็กข้อเดียว: คอลัมน์ "GM% ตั้งแต่" ต้องเรียงจากน้อยไปมาก เสมอ
เอกสารของ Microsoft ระบุตรงตัวว่าเมื่อ match_type เป็น 1 ค่าใน lookup_array ต้องเรียงจากน้อยไปมาก และเอกสารของ Google Sheets ก็บอกเหมือนกันว่า search_type 1 จะ "ถือว่า" ช่วงข้อมูลเรียงจากน้อยไปมากแล้ว
⚠️ จุดที่อันตรายคือ: ถ้าเรียงผิด สูตรไม่ฟ้อง error แต่คืนอัตราผิดขั้นเงียบๆ — ซึ่งตรวจยากกว่า #N/A หลายเท่า
ตั้งชื่อช่วงข้อมูล (named range)
ตั้งชื่อสองคอลัมน์นี้เป็น RATE_LOW และ RATE_VAL
- Excel: Formulas → Name Manager → New
- Google Sheets: Data → Named ranges
- LibreOffice: Sheet → Named Ranges and Expressions → Define
ทำไมต้องตั้งชื่อ ไม่อ้างเซลล์ตรงๆ? เหตุผลไม่ใช่ความสวยงาม แต่เป็นการกันบั๊กหนึ่งชนิดที่เกิดบ่อยมาก:
ถ้าคุณเขียน MATCH(H2, B3:B7, 1) แล้วลืมใส่ $ แล้วลากสูตรลง 200 แถว ช่วง B3:B7 จะเลื่อนเป็น B4:B8, B5:B9, ... แถวล่างๆ จะคำนวณจากตารางขั้นที่ไม่ครบ และไฟล์จะให้ตัวเลขผิดโดยไม่ฟ้องอะไรเลย
named range เป็นการอ้างแบบสัมบูรณ์โดยธรรมชาติ บั๊กชนิดนี้จึงหายไปทั้งชนิด
2. สูตรหลัก
สมมติวางคอลัมน์แบบนี้
| คอลัมน์ | ความหมาย |
|---|---|
| E | ยอดขาย (ก่อน VAT) |
| F | ต้นทุน |
| G | กำไรขั้นต้น (GP) |
| H | GM% |
| I | อัตราคอมที่เข้าขั้น |
| J | ค่าคอมของบิลนั้น |
G2 =E2-F2
H2 =IF(E2=0, 0, G2/E2)
I2 =INDEX(RATE_VAL, MATCH(H2, RATE_LOW, 1))
J2 =G2*I2
สูตรชุดนี้ทำงานถูกต้องกับบิลที่มีกำไร — และนี่คือเวอร์ชันที่มีบั๊ก
3. กับดักที่ทำให้ทั้งไฟล์พัง
เกิดอะไรขึ้นเมื่อบิลใบหนึ่งขายต่ำกว่าทุน
ในธุรกิจซื้อมาขายไป การมีบิลที่ GM% ติดลบเป็นเรื่องปกติ ไม่ใช่ความผิดพลาด — ของค้างสต๊อกที่ต้องระบาย, งานที่ยอมขาดทุนเพื่อรักษาลูกค้า, ต้นทุนที่ขึ้นหลังเสนอราคาไปแล้ว, ค่าขนส่งบานปลาย
สมมติบิลใบหนึ่ง ยอดขาย ฿120,000 ต้นทุน ฿127,200
- G = -7,200
- H = -6.0%
- I =
MATCH(-6%, RATE_LOW, 1)→ ไม่มีแถวไหนในRATE_LOWที่ ≤ -6% เลย (ขั้นล่างสุดคือ 0%) →#N/A - J =
#N/A
แล้วมันลามยังไง
SUM() ส่งต่อ error ขึ้นไปทั้งหมด ผลคือ:
| ระดับ | ผลลัพธ์ |
|---|---|
| คอมของบิล INV-004 | #N/A |
| ยอดคอมรวมของพนักงานคนนั้น | #N/A |
| ยอดคอมรวมทั้งทีม | #N/A |
| แดชบอร์ดสรุปรายปี | #N/A ทั้งหน้า |
บิลใบเดียวลบทั้งไฟล์ และเพราะมันไม่ใช่ตัวเลขผิด แต่เป็นช่องว่างเปล่าที่เห็นชัด คนมักไปแก้ผิดที่
วิธี "แก้" ที่แย่กว่าเดิม
❌ =IFERROR(SUM(J2:J200), 0) ที่ยอดรวม → ยอดรวมกลายเป็น 0 ทั้งที่ทีมทำคอมได้จริงเป็นแสน
❌ =AGGREGATE(9,6,J2:J200) ที่ข้าม error → อันตรายที่สุด เพราะยอดรวมจะออกมาเป็นตัวเลขสวยงามที่ ขาดไปเท่ากับจำนวนบิลที่พัง โดยไม่มีสัญญาณเตือนใดๆ (และฟังก์ชันนี้ไม่มีใน Google Sheets ด้วย)
❌ =IFERROR(INDEX(...), 0) ที่ระดับบรรทัด → กลบปัญหาไว้ ทำให้ครั้งหน้าที่มี error ชนิดอื่นเกิดขึ้นจริง คุณจะไม่มีวันรู้
วิธีแก้ที่ถูก: บีบค่าที่ใช้ค้นหา
ปัญหาไม่ได้อยู่ที่ error — ปัญหาอยู่ที่เราถามคำถามที่ตารางตอบไม่ได้ วิธีแก้คือบีบไม่ให้ถามต่ำกว่าขั้นล่างสุด
I2 =INDEX(RATE_VAL, MATCH(MAX(H2,0), RATE_LOW, 1))
MAX(H2,0) ทำให้ GM% ที่ติดลบกลายเป็น 0 ก่อนเข้า MATCH → ตกลงมาอยู่ขั้นล่างสุด → ได้อัตรา 3% → ไม่มี error
แต่ยังไม่พอ — คอมยังติดลบได้
ถ้า GP = -7,200 และอัตรา = 3% ค่าคอมจะเป็น -216 บาท
นั่นแปลว่าไฟล์ของคุณกำลังหักเงินพนักงานอัตโนมัติจากบิลที่ผู้บริหารเป็นคนอนุมัติให้ขายขาดทุนเอง ซึ่งเป็นการตัดสินใจเชิงนโยบายที่ไม่ควรเกิดขึ้นโดยบังเอิญจากเลขคณิต
J2 =MAX(0, G2*I2)
สูตรเวอร์ชันสมบูรณ์ (รวมทุกกันชนไว้ในบรรทัดเดียว)
=IF(E2="", "", MAX(0, (E2-F2) * INDEX(RATE_VAL,
MATCH(MAX(ROUND(IF(E2=0, 0, (E2-F2)/E2), 4), 0), RATE_LOW, 1))))
ไล่ทีละชั้นจากในออกนอก:
| ชั้น | หน้าที่ |
|---|---|
IF(E2=0, 0, ...) |
กัน #DIV/0! ตอนยอดขายเป็นศูนย์ |
ROUND(..., 4) |
กันปัญหาทศนิยมลอยตัวตอน GM% ตกขอบขั้นพอดีเป๊ะ |
MAX(..., 0) |
กัน #N/A จาก GM% ติดลบ ← ตัวนี้คือหัวใจ |
INDEX/MATCH |
หาอัตราคอมของขั้น |
MAX(0, ...) |
กันคอมติดลบ |
IF(E2="", "", ...) |
แถวว่างไม่รบกวนยอดรวม |
หมายเหตุเรื่อง ROUND: สเปรดชีตเก็บทศนิยมแบบ binary floating point การหาร 10000/100000 อาจได้ค่าที่ห่างจาก 0.1 อยู่นิดเดียวจนบิลที่ควรเข้าขั้น 10% ตกลงไปขั้น 0% การปัดที่ทศนิยม 4 ตำแหน่ง (ละเอียดถึง 0.01%) แม่นเกินพอสำหรับขั้นที่ห่างกัน 5 จุด และตัดปัญหานี้ทิ้งทั้งหมด — อย่าลืมปัดค่าใน RATE_LOW ให้เป็น 4 ตำแหน่งด้วย
ทางเลือกที่สั้นกว่า ถ้าตารางขั้นอยู่ติดกันและคอลัมน์ GM% อยู่ซ้ายสุด:
=VLOOKUP(MAX(H2,0), TIER_TABLE, 2, TRUE)
ใช้ได้เหมือนกัน แต่ถ้ามีคนแทรกคอลัมน์กลางตาราง VLOOKUP จะดึงคอลัมน์ผิดโดยไม่ฟ้อง error ส่วน INDEX/MATCH ยังชี้ถูกช่อง — สำหรับไฟล์ที่หลายคนแก้ร่วมกัน INDEX/MATCH ปลอดภัยกว่า
4. ตัวอย่างเดินตัวเลขจริง 1 เดือน
พนักงานขาย 1 คน 5 บิล
| บิล | ยอดขาย | ต้นทุน | GP | GM% | เข้าขั้น | อัตรา | ค่าคอม |
|---|---|---|---|---|---|---|---|
| INV-001 | 250,000 | 212,500 | 37,500 | 15.0% | 3 | 8% | 3,000 |
| INV-002 | 180,000 | 153,000 | 27,000 | 15.0% | 3 | 8% | 2,160 |
| INV-003 | 96,000 | 91,200 | 4,800 | 5.0% | 1 | 3% | 144 |
| INV-004 | 120,000 | 127,200 | -7,200 | -6.0% | 1 (บีบแล้ว) | 3% | 0 |
| INV-005 | 400,000 | 280,000 | 120,000 | 30.0% | 5 | 12% | 14,400 |
| รวม | 1,046,000 | 863,900 | 182,100 | 19,704 |
ถ้าไม่ได้ใส่ MAX(H2,0) ช่อง INV-004 จะเป็น #N/A และช่อง "รวม" จะเป็น #N/A — คอม ฿19,704 ที่พนักงานทำได้จริงจะหายไปจากหน้าจอทั้งก้อน เพราะบิลใบเดียวที่ขาดทุน ฿7,200
5. ชีตทดสอบ 10 เคส
ชีตทดสอบที่มีแต่บิลกำไรสวยๆ จะรับรองว่าไฟล์ที่พังอยู่นั้นใช้งานได้ — ซึ่งแย่กว่าไม่มีชีตทดสอบเลย เพราะมันสร้างความมั่นใจปลอม
ทดสอบด้วยข้อมูลที่ตั้งใจให้พัง:
| # | เคส | ยอดขาย | ต้นทุน | ผลที่ควรได้ |
|---|---|---|---|---|
| 1 | กลางขั้นปกติ | 250,000 | 212,500 | 8% → ฿3,000 |
| 2 | ขั้นล่างสุด | 100,000 | 95,000 | 3% → ฿150 |
| 3 | เกินขั้นบนสุด | 1,000,000 | 100,000 | 12% → ฿108,000 |
| 4 | GP = 0 พอดี | 100,000 | 100,000 | 3% → ฿0 |
| 5 | GP ติดลบ | 100,000 | 106,000 | 3% → ฿0 ไม่ใช่ #N/A ไม่ใช่ติดลบ |
| 6 | ยอดขาย = 0 | 0 | 0 | ฿0 ไม่มี #DIV/0! |
| 7 | แถวว่าง | (ว่าง) | (ว่าง) | ว่าง และไม่ทำให้ยอดรวมพัง |
| 8 | ขอบขั้นพอดี 10.00% | 100,000 | 90,000 | 5% → ฿500 (ไม่ใช่ 3%) |
| 9 | ขอบขั้นบนพอดี 30.00% | 100,000 | 70,000 | 12% → ฿3,600 (ไม่ใช่ 10%) |
| 10 | ต้นทุนเป็นข้อความ/ว่าง | 100,000 | (ว่าง) | ต้องมีธงเตือน ไม่ใช่คิดว่ากำไร 100% |
เคส 5, 8, 9 คือสามเคสที่จับบั๊กได้จริง ถ้าจะทดสอบแค่สามเคส ให้เลือกสามเคสนี้
เคส 10 มีจุดที่ต้องคิดเอง: ถ้าต้นทุนว่าง สูตรจะมองเป็น 0 แล้วคิดว่ากำไร 100% → เข้าขั้นสูงสุด → จ่ายคอมเต็มจากบิลที่ยังไม่รู้ต้นทุน วิธีกันคือเพิ่มคอลัมน์ธงเตือนแยกออกมา
=IF(AND(E2<>"", F2=""), "⚠ ยังไม่มีต้นทุน", "")
ให้มันเตือน ไม่ใช่ให้มันเดา
6. สิ่งที่ควรทำหลังจากนี้
- ล็อกตารางขั้นให้เรียงจากน้อยไปมาก และป้องกันชีตนั้นไม่ให้แก้โดยไม่ตั้งใจ
- ใช้ named range ทุกที่ ที่สูตรอ้างตารางขั้น
- ครอบ
MAX(GM%, 0)ก่อนเข้า MATCH เสมอ — นี่คือบรรทัดเดียวที่กันบั๊กร้ายแรงที่สุด - ครอบ
MAX(0, ...)ที่ค่าคอม เพื่อประกาศให้ชัดว่าบริษัทไม่จ่ายคอมติดลบ - มีชีตทดสอบที่โหดกับตัวเอง และรันทุกครั้งก่อนแก้สูตรขึ้นใช้จริง
- อย่าใส่ IFERROR ที่ยอดรวม — ให้ error โผล่ที่บรรทัด แล้วไปแก้ที่ต้นเหตุ
เรื่องการออกแบบขั้นให้ไม่เกิดข้อพิพาทตอนสิ้นเดือน (ปัญหาหน้าผาระหว่างขั้น) เป็นคนละเรื่องกับการเขียนสูตร อ่านต่อได้ที่ ออกแบบคอมขั้นบันไดยังไงไม่ให้ทะเลาะกันสิ้นเดือน
เครื่องมือที่ใช้ทำเรื่องนี้ได้ทันที
พูดกันตรงๆ ก่อน: บทความนี้ให้สูตรครบแล้ว คุณทำไฟล์เองได้จริง ทุกสูตรอยู่ข้างบนหมด ไม่มีอะไรถูกกั๊กไว้ ถ้าคุณมีเวลาสุดสัปดาห์หนึ่งกับความอดทนกับชีตทดสอบ คุณไม่ต้องซื้ออะไรเลย
ระบบคำนวณค่าคอมมิชชั่นจากกำไรขั้นต้น (GP) มีไว้สำหรับคนที่ไม่อยากเสียสุดสัปดาห์ไปกับมัน และอยากได้เคสขอบๆ ที่จัดการเรียบร้อยแล้ว:
- ตารางขั้น + named range + สูตร
INDEX/MATCHที่บีบค่าและกันคอมติดลบไว้แล้ว - ชีตทดสอบระบบ 10 เคส ตามที่อธิบายในบทความนี้ พร้อมคำตอบที่ถูกกำกับไว้ทุกช่อง
- ธงเตือนสำหรับแถวที่ข้อมูลไม่ครบ แทนที่จะเดาแล้วจ่ายเงินผิด
- ไม่มีมาโคร เปิดได้ทั้ง Excel / LibreOffice / Google Sheets
- ทุกสูตรเปิดดูและแก้ได้ ไม่มีส่วนที่ถูกซ่อน
จ่ายครั้งเดียว ฿999 — ราคาเปิดร้าน ถึง 31 ธ.ค. 2569 (ราคาเต็ม ฿1,490) · รวมภาษีมูลค่าเพิ่ม 7% แล้ว · ออกใบกำกับภาษีเต็มรูปแบบในนามบริษัทได้ · ดาวน์โหลดทันทีหลังชำระเงิน
รับเอกสารสรุปหัวข้อนี้เป็น PDF
บทสรุป + หัวข้อครบ + มาตรฐานอ้างอิง มีโลโก้ Saha แนบ memo/TOR ได้ทันที — ส่งเข้าอีเมลให้ด้วย
อ่านแล้วมีคำถาม? ให้วิศวกรช่วย
บอกสิ่งที่อยากรู้สั้นๆ — วิศวกรสหวัฒนกิจช่วยเลือกสเปกที่เหมาะ พร้อมใบเสนอราคาจริง ไม่มีค่าบริการ
ต้องการให้ทีมช่วยเหลือเรื่องนี้?
ทีมงานรับเสนอราคา + จัดส่ง + ติดตั้งครบวงจรในหัวข้อที่บทความนี้พูดถึง — ใบเสนอราคาฟรี อย่างรวดเร็วในเวลาทำการ
คำถามที่พบบ่อย
1สูตร Excel คำนวณค่าคอมมิชชั่นขั้นบันไดจาก GM% เขียนยังไง?
+
2ทำไมสูตร MATCH ถึงคืนค่า #N/A เวลากำไรขั้นต้นติดลบ?
+
3ทำไมบิลขาดทุนใบเดียวทำให้ยอดรวมทั้งไฟล์พังได้?
+
4ตารางขั้นคอมมิชชั่นต้องเรียงลำดับยังไง?
+
5ทำไมต้องใช้ named range แทนการอ้างเซลล์ตรงๆ?
+
6จะกันไม่ให้ค่าคอมออกมาติดลบได้ยังไง?
+
7ควรใช้ VLOOKUP หรือ INDEX/MATCH สำหรับตารางขั้นคอม?
+
8สูตรนี้ใช้กับ Google Sheets และ LibreOffice ได้ไหม?
+
9ควรทดสอบไฟล์คำนวณค่าคอมด้วยเคสอะไรบ้าง?
+
เนื้อหาที่เกี่ยวข้อง
ภาษีหัก ณ ที่จ่ายค่าคอมมิชชั่น — 40(1) หรือ 40(2)? อย่าหักผิดเป็น 3%
คอมที่จ่ายให้พนักงานของตัวเองเป็นเงินได้ 40(1) ต้องรวมกับเงินเดือนแล้วหักแบบอัตราก้าวหน้า ยื่น ภ.ง.ด.1 — ส่วน 3% ที่หลายบริษัทหักกันจนติดเป็นนิสัย ใช้ได้เฉพาะบางกรณีเท่านั้น พร้อมตรวจสอบกับแบบฟอร์มจริงของกรมสรรพากร
ออกแบบคอมขั้นบันไดยังไงไม่ให้ทะเลาะกันสิ้นเดือน
ขั้นบันไดแบบคิดย้อนทั้งเดือนสร้าง 'หน้าผา' ที่ทำให้เงินหลักหมื่นแกว่งเพราะยอดบาทสุดท้าย ซึ่งเป็นต้นตอของการเลื่อนวันวางบิลและการเถียงเรื่องปัดทศนิยม — วิธีแก้คือขั้นบันไดแบบส่วนเพิ่ม พร้อมสูตร SUMPRODUCT ที่ใช้ได้จริง
พนักงานขายทุบราคาเพื่อปั่นยอด — สาเหตุจริงและวิธีแก้ที่ระบบ
ส่วนลด 5% กินกำไรขั้นต้นได้ถึง 62.5% ในงานมาร์จิ้นบาง แต่พนักงานเสียคอมแค่ 5% — บทความนี้แสดงเลขให้เห็นว่าทำไมการทุบราคาแทบไม่เคยเป็นปัญหาที่ตัวคน แต่เป็นปัญหาที่แผนค่าตอบแทน และวิธีแก้ 4 ข้อที่แก้ที่ระบบ
ควรตั้งเพดานค่าคอมมิชชั่นไหม? — ข้อดี ข้อเสีย และทางสายกลาง
งานวิจัย Marketing Science และ HBR ชี้ตรงกันว่าเพดานค่าคอมทำร้ายยอดขาย แต่ SME ที่ใช้วงเงิน OD ก็มีเหตุผลเรื่องกระแสเงินสดที่ปฏิเสธไม่ได้ บทความนี้เสนอทั้งสองฝั่งอย่างตรงไปตรงมา พร้อมทางสายกลางที่ trigger ที่ 'ขนาดดีล' ไม่ใช่ที่ยอดคอมสะสม