ขอคำแนะนำการเขียนสูตรคำนวณคอมมิชชั่นค่ะ

[Jajabonat]

เรียนอาจารย์ทุกๆท่าน ดิฉันอยากขอคำแนะนำในการเขียนสูตรคำนวณค่าคอมมิชชั่นค่ะ

เงื่อนไขการคำนวณค่าคอมมิชชั่นมีดังนี้
1.กำหนดเป้าการขาย (เป้าหมาย) ของแต่ละบุคคล เอาฐานเงินเดือนของแต่ละบุคคลตั้ง x 45

2.ยอดขายไม่ถึง 50%ของเป้าหมาย จะไม่ได้รับค่าคอมมิชชั่น

3.ยอดขายตั้งแต่ 50.1% ขึ้นไป ถึง 75% ของเป้าหมาย จะได้รับค่าคอมมิชชั่นในอัตรา 1%
โดยคำนวณตั้งแต่ยอด 0% จนถึง 75%

4.ยอดขายตั้งแต่ 75.1% ขึ้นไป ถึง 99% ของเป้าหมาย จะได้รับค่าคอมมิชชั่นในอัตรา 2%
โดยคำนวณตั้งแต่ยอด 75.1% จนถึง 99%

5.ยอดขายเกิน 100% ขึ้นไป ของเป้าหมาย จะได้รับค่าคอมมิชชั่นในอัตรา 1%
โดยคำนวณตั้งแต่ยอด 100% ขึ้นไป

ดิฉันต้องจัดวางข้อมูลต่างๆ ลงในตาราง Excel อย่างไรบ้างค่ะ เพราะมีพนักงานขายหลายคน แต่ละคนตั้งเป้ายอดขายไว้ไม่เท่ากัน
รบกวนช่วยแนะนำด้วยนะค่ะ

[Supachok]

E4
=IF(D4/C4>100%,(D4-C4)*0.01,IF(75.1%>D4/C4<99%,D4*0.02,IF(50.1>D4/C4<75%,D4*0.01,IF(D4/C4<50%,0))))

Copy down
เงื่อนไขเป็นไปตาม excel file

[Jajabonat]

ขออนุญาติเรียกพี่นะคะ
รบกวนสอบถามค่ะ เอาไปวางในช่อง E4 ช่องเดียวหรอค่ะพี่
แล้วช่องอื่นต้องนำไปวางด้วยไม่ค่ะ

ต้องขอโทษด้วยนะคะที่ถาม มือใหม่มากค่ะ
ขอบคุณค่ะ

[Jajabonat]

E4
=IF(D4/C4>100%,(D4-C4)*0.01,IF(75.1%>D4/C4<99%,D4*0.02,IF(50.1>D4/C4<75%,D4*0.01,IF(D4/C4<50%,0))))

Copy down
เงื่อนไขเป็นไปตาม excel file

ขออนุญาติเรียกพี่นะคะ
รบกวนสอบถามค่ะ เอาไปวางในช่อง E4 ช่องเดียวหรอค่ะพี่
แล้วช่องอื่นต้องนำไปวางด้วยไม่ค่ะ

ต้องขอโทษด้วยนะคะที่ถาม มือใหม่มากค่ะ
ขอบคุณค่ะ

[Jajabonat]

แต่ทำไหมนำไปใช้แล้ว มันมีช่อง FALSE ด้วยนะคะ

แล้วถ้าเป็นการนำผลค่าคอมฯที่ได้ไปรวมกับค่าคอมที่คำนวณได้ก่อนหน้า ต้องใช้สูตรยังไงค่ะ

[Supachok]

ที่ว่า false ได้copy ไป cell อื่นหรือยัง
เงื่อนไขที่ตาม excel ถูกต้องไหม

ลองแนบไฟล์มาดูครับ

[Bo_ry]

P2 ใส่ Target
Q2 ใส่ Commission

P3:P7 0%;50%;75%;100%
Q3:Q7 => 0%;1%;2%;1%

N4
=SUMPRODUCT((D4>C4*$P$3:$P$6)*(D4-C4*$P$3:$P$6),$Q$3:$Q$6-N(+$Q$2:$Q$5))

[Jajabonat]

ที่ว่า false ได้copy ไป cell อื่นหรือยัง
เงื่อนไขที่ตาม excel ถูกต้องไหม

ลองแนบไฟล์มาดูครับ

พี่ค่ะ
1.ได้ copy วางแล้วลากลงค่ะ และได้เพิ่มตัวเลขจริงเข้าไป 1 รายการ มันขึ้นว่า false ค่ะ
2.ถ้าหนูเพิ่มเงื่อนไขเข้าไปเพิ่มสูตรต้องคำนวณอย่างไรค่ะ และถ้ามีค่าคอมสะสมด้วยต้องคิดยังไงค่ะ
รบกวนด้วยนะคะพี่

[Jajabonat]

P2 ใส่ Target
Q2 ใส่ Commission

P3:P7 0%;50%;75%;100%
Q3:Q7 => 0%;1%;2%;1%

N4
=SUMPRODUCT((D4>C4*$P$3:$P$6)*(D4-C4*$P$3:$P$6),$Q$3:$Q$6-N(+$Q$2:$Q$5))

พี่ค่ะถ้าเพิ่มเงื่อนไขเข้าไปด้วยจะคิดสูตรเพิ่มเข้าไปอย่างไรค่ะ
1.ยอดขายระหว่าง 100.1% ขึ้นไปถึง 125% จะถูกคำนวณด้วยอัตรา 3%
2.ยอดขายระหว่าง 125.1% ขึ้นไปถึง 150 จะถูกคำนวณด้วยอัตรา 4%
3.ยอดขายที่เกิน 150% ขึ้นไป ของเป้าหมาย จะถูกคำนวณด้วยอัตรา 1%

รบกวนด้วยค่ะ

[Bo_ry]

T2 ใส่ Target
U2 ใส่ Commission

T3:T7 0%;50%;75%;100%;125%;150%
U3:U7 => 0%;1%;2%;3%;4%;1%

Q4
=SUMPRODUCT((D4>C4*$T$3:$T$8)*(D4-C4*$T$3:$T$8),$U$3:$U$8-N(+$U$2:$U$7))

[Jajabonat]

T2 ใส่ Target
U2 ใส่ Commission

T3:T7 0%;50%;75%;100%;125%;150%
U3:U7 => 0%;1%;2%;3%;4%;1%

Q4
=SUMPRODUCT((D4>C4*$T$3:$T$8)*(D4-C4*$T$3:$T$8),$U$3:$U$8-N(+$U$2:$U$7))

พี่ค่ะลองใส่แล้วค่ะ
แต่ยอดรวมแล้วไม่เท่ากันค่ะ รบกวนดูในไฟล์แนบให้หน่อยค่ะ

[Bo_ry]

Column O คำนวณผิด
O6
=D6-MIN(C6*1.5,D6)

[Jajabonat]

Column O คำนวณผิด
O6
=D6-MIN(C6*1.5,D6)

พี่ค่ะได้เท่ากันแล้วค่ะ ขอรบกวนอีกรอบ รบกวนช่วยอธิบายสูตรให้ฟังหน่อยค่ะพี่
ทั้งนี้แนบไฟล์มาให้พี่ดูด้วยค่ะ ว่าทำได้เท่ากันแล้วค่ะ

[Bo_ry]

=SUMPRODUCT((D6>C6*$T$3:$T$8)*(D6-C6*$T$3:$T$8),$U$3:$U$8-N(+$U$2:$U$7))

a. (D6>C6*$T$3:$T$8) เช็คว่า Invoice มากกว่า Target แต่ละขั้น
b. (D6-C6*$T$3:$T$8) Invoice - Target แต่ละขั้น

a* b Invoice - Target เฉพาะที่มากว่า Target แต่ละขั้น
ที่ R6 ได้
(D6>C6*$T$3:$T$8)*(D6-C6*$T$3:$T$8)
___0%___;__50%__;_100%__;_125%__;_150%_;>150%; จำนวนที่คิด com
{2370700;1853200;1594450;1335700;1076950;818200}
___0%___;__1%___;___1%__;___1%_;___1%__;__-3%__; % commission ที่คิด = $U$3:$U$8-N(+$U$2:$U$7)
___0%___;__1%___;___2%__;___3%_;___4%__;___1%__; % commission สะสม = $U$3:$U$8

2370700 ทั้งหมดไม่คิดคอมเลย 0%
1853200 ส่วนที่เลย 50% คิด 1%
1594450 ส่วนที่เลย 75% คิดอีก 1% สวนที่เลย 75% +เพิ่มอีก 1% = 1+1=2%
1335700 ส่วนที่เลย 100% คิดอีก 1% สวนที่เลย 100% +เพิ่มอีก 1% = 2+1=3%
1076950 ส่วนที่เลย 125% คิดอีก 1% สวนที่เลย 125% +เพิ่มอีก 1% = 3+1=4%
818200 ส่วนที่เลย 150% หักไป 3% สวนที่เลย 150% -ลบไป 3% = 4-3 = 1%

[Jajabonat]

=SUMPRODUCT((D6>C6*$T$3:$T$8)*(D6-C6*$T$3:$T$8),$U$3:$U$8-N(+$U$2:$U$7))

a. (D6>C6*$T$3:$T$8) เช็คว่า Invoice มากกว่า Target แต่ละขั้น
b. (D6-C6*$T$3:$T$8) Invoice - Target แต่ละขั้น

a* b Invoice - Target เฉพาะที่มากว่า Target แต่ละขั้น
ที่ R6 ได้
(D6>C6*$T$3:$T$8)*(D6-C6*$T$3:$T$8)
___0%___;__50%__;_100%__;_125%__;_150%_;>150%; จำนวนที่คิด com
{2370700;1853200;1594450;1335700;1076950;818200}
___0%___;__1%___;___1%__;___1%_;___1%__;__-3%__; % commission ที่คิด = $U$3:$U$8-N(+$U$2:$U$7)
___0%___;__1%___;___2%__;___3%_;___4%__;___1%__; % commission สะสม = $U$3:$U$8

2370700 ทั้งหมดไม่คิดคอมเลย 0%
1853200 ส่วนที่เลย 50% คิด 1%
1594450 ส่วนที่เลย 75% คิดอีก 1% สวนที่เลย 75% +เพิ่มอีก 1% = 1+1=2%
1335700 ส่วนที่เลย 100% คิดอีก 1% สวนที่เลย 100% +เพิ่มอีก 1% = 2+1=3%
1076950 ส่วนที่เลย 125% คิดอีก 1% สวนที่เลย 125% +เพิ่มอีก 1% = 3+1=4%
818200 ส่วนที่เลย 150% หักไป 3% สวนที่เลย 150% -ลบไป 3% = 4-3 = 1%

ขอบคุณมากเลยค่ะ เข้าใจเพิ่มขึ้นแล้วค่ะ