สูตร 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% แล้ว · ออกใบกำกับภาษีเต็มรูปแบบในนามบริษัทได้ · ดาวน์โหลดทันทีหลังชำระเงิน
Get this guide as a reference brief (PDF)
Summary + full section list + standards cited, Saha-branded for your memo/RFQ — emailed to you too.
Questions after reading? Talk to our engineers
Tell us what you need — our engineers help you spec it right, with a real quote. No charge.
Need help with this in your facility?
Our team handles full procurement and installation for the topics covered in this article. Free quote within 2 hours.
Frequently Asked Questions
1สูตร Excel คำนวณค่าคอมมิชชั่นขั้นบันไดจาก GM% เขียนยังไง?
+
2ทำไมสูตร MATCH ถึงคืนค่า #N/A เวลากำไรขั้นต้นติดลบ?
+
3ทำไมบิลขาดทุนใบเดียวทำให้ยอดรวมทั้งไฟล์พังได้?
+
4ตารางขั้นคอมมิชชั่นต้องเรียงลำดับยังไง?
+
5ทำไมต้องใช้ named range แทนการอ้างเซลล์ตรงๆ?
+
6จะกันไม่ให้ค่าคอมออกมาติดลบได้ยังไง?
+
7ควรใช้ VLOOKUP หรือ INDEX/MATCH สำหรับตารางขั้นคอม?
+
8สูตรนี้ใช้กับ Google Sheets และ LibreOffice ได้ไหม?
+
9ควรทดสอบไฟล์คำนวณค่าคอมด้วยเคสอะไรบ้าง?
+
Related content
Customers Paying Late? Chase Hard and Lose the Relationship, Don't Chase and Lose the Cash — a 4-Step Collection Ladder That Keeps Both
Effective B2B collection is not about harsher words — it is a predictable, escalating ladder tied to days overdue: polite reminder, firm date request, formal notice, delivery hold. With face-saving wording that fits Thai business culture, and the fact most SMEs miss: Thailand's 45-day credit-term guideline for large buyers.
DSO and "Idle Money" — the Credit Terms You Grant Have a Price in Baht per Year, and Most Businesses Never Compute It
Sell on 30-day terms but actually collect on day 70, and you are lending your customer money for 40 days while paying the overdraft interest yourself. How to measure your own DSO, how to price idle money in baht, and why outstanding balances must be computed from the cash you will actually receive after Thai withholding tax.
A Debtor Register with an Aging Table — Half of B2B Invoices Get Paid Late; the Businesses That Survive Know Who Owes What, Every Morning
Global B2B payment research says roughly half of invoices are paid late and bad debts average 6–8% of credit sales. How to build a debtor register and aging table that answers "who do we chase today" in ten seconds — including the parts international templates miss in Thailand: billing dates, cheque rounds, and withholding tax.
How Far Can You Discount? — The Four-Number Ladder to Know Before You Answer
Instead of guessing or giving way, use four figures worked out in advance: the price to quote, the lowest price clearing your minimum margin, break-even, and the cash floor. With the wording that works at each rung, and what to trade for every baht you concede.