Sử dụng công thức để tính tiền thưởng theo phân loại ? (1 người xem)

[Chuotdong]

Mình có bài toán sau mời các bạn xem, giải dùm, hạn chế tối đa dùng cột phụ

Có 1 số tiền nào đó, sau đó tùy theo xếp loại của từng cá nhân (là A, B, hay C,D ..) mà tính số tiền người đó được hưởng, biết rằng chênh lệch giữa các mức xếp loại là cố định từ trước, ví dụ chênh loai A và B luôn là 500.000đ .v.v...
Chỉ có tổng số tiền và xếp loại của từng cá nhân có thể thay đổi.
Cụ thể xem file đính kèm

Mình định dùng countif() mà thấy hơi rối

 

[Lê Duy Thương]

mình có bài toán sau mời các bạn xem, giải dùm, hạn chế tối đa dùng cột phụ

có 1 số tiền nào đó, sau đó tùy theo xếp loại của từng cá nhân (là a, b, hay c,d ..) mà tính số tiền người đó được hưởng, biết rằng chênh lệch giữa các mức xếp loại là cố định từ trước, ví dụ chênh loai a và b luôn là 500.000đ .v.v...
Chỉ có tổng số tiền và xếp loại của từng cá nhân có thể thay đổi.
Cụ thể xem file đính kèm

mình định dùng countif() mà thấy hơi rối

thử dùng công thức này xem sao

=lookup(c3,{"a","b","c","d",0},{1.4,0.9,0.7,0.1,0})*10^6

 

[nghiaphuc]

thử dùng công thức này xem sao

=lookup(c3,{"a","b","c","d",0},{1.4,0.9,0.7,0.1,0})*10^6

Nếu tổng tiền thưởng thay đổi thì công thức trên không đúng đâu anh ơi. Vấn đề của tác giả là từ tổng số tiền thưởng đã có, làm sao để chia cho các nhân viên theo loại cho phù hợp, có nghĩa là nếu thay đổi tổng tiền thì số tiền thưởng của mỗi người cũng tự thay đổi theo.
Em sử dụng giải pháp sau, giải quyết được vấn đề nhưng không biết đã tối ưu chưa:
1. Sử dụng các name:

Rng=OFFSET(Sheet1!$C$3,,,COUNTA(Sheet1!$C:$C)-2)
T=LOOKUP(10^14,Sheet1!$D:$D)
D=(T-1300000*COUNTIF(Rng,"A")-800000*COUNTIF(Rng,"B")-600000*COUNTIF(Rng,"C"))/COUNTA(Rng)

(Rng: Vùng xếp loại, T: Tổng số tiền thưởng, D: Số tiền thưởng cho loại D)
2. Sử dụng công thức sau cho D3 và fill cho toàn vùng:

=D+IF(C3="A",1300000,IF(C3="B",800000,IF(C3="C",600000,0)))

 

[ChanhTQ@]

Thêm 1 tham khảo cho bạn bằng hàm người dùng sau

Option Explicit
Function ThuongABC(Loai As String, CSDL As Range, TTien As Double)
 ReDim Mang(1 To 3) As Byte:           Const Bb As Double = 500000
 Dim WF As Object, Cls As Range:       Const Cc As Double = 700000
 Dim jJ As Byte, LA As Double:         Const Dd As Double = 1300000
 
 Set WF = Application.WorksheetFunction
 For jJ = 1 To 3
   Mang(jJ) = WF.CountIf(CSDL, Chr(65 + jJ))
 Next jJ
 LA = (TTien + Bb * Mang(1) + Cc * Mang(2) + Dd * Mang(3)) / CSDL.Cells.Count
 ThuongABC = Switch(Loai = "A", LA, Loai = "B", LA - Bb, Loai = "C", LA - Cc, Loai = "D", LA - Dd)
 
End Function

/(hi đó cú fáp tại [D3] sẽ là =ThuongABC(C3,C$3:C$16,D$2)
Ở đây D$2 đang chứa tổng lượng tiền thưởng của toàn đơn vị.

 

[ndu96081631]

Mình có bài toán sau mời các bạn xem, giải dùm, hạn chế tối đa dùng cột phụ

Có 1 số tiền nào đó, sau đó tùy theo xếp loại của từng cá nhân (là A, B, hay C,D ..) mà tính số tiền người đó được hưởng, biết rằng chênh lệch giữa các mức xếp loại là cố định từ trước, ví dụ chênh loai A và B luôn là 500.000đ .v.v...
Chỉ có tổng số tiền và xếp loại của từng cá nhân có thể thay đổi.
Cụ thể xem file đính kèm

Mình định dùng countif() mà thấy hơi rối

Tôi nghĩ bài toán này giải quyết bằng Solver là hay nhất
- Gõ vào cell D4 công thức =LOOKUP(C4,{"a","b","c","d"},{0,-5,-7,-13})*10^5+$D$3
- Kéo fill D4 xuống đến D16
- Cell D3 bỏ trống
- Gõ vào cell D17 công thức =SUM(D316)
- Giờ sẽ tìm xem D3 bằng bao nhiêu để thỏa điều kiện D17 = 17,100,000
Ta tiến hành như sau:
- Gọi Solver (trong menu Tools) ---> Nếu chưa có Solver thì cài nó vào (trong Tools\Add-Ins)
- Mục Set Target Cell, chọn $D$17
- Mục Equal To, check mục Value of và gõ vào Textbox số 17100000
- Mục By Changing Cells, chọn $D$3
- Bấm nút Add, thêm điều kiện $D$3 >=0



- Bấm nút Solver để máy tính ra giá trị cell D3 ---> Các giá trị khác trong cột D sẽ bị "kéo theo" nhờ công thức liên kết với D3

 

[ndu96081631]

Nghiên cứu thêm cách dùng công thức xem!
Phân tích bài toán:
- Gọi a là số người xếp loại A và A là tiền thưởng người A sẽ lảnh
- Gọi b là số người xếp loại B và B là tiền thưởng người B sẽ lảnh
- Gọi c là số người xếp loại C và C là tiền thưởng người C sẽ lảnh
- Gọi d là số người xếp loại D và D là tiền thưởng người D sẽ lảnh
- Gọi T là tổng số tiền
Ta có:
a*A + b*B + c*C + d*D = T
Trong đó B = A - 500000, C = A - 700000 và D = A - 1300000
===>
a*A + b*(A - 500000) + c*(A - 700000) + d*(A - 1300000) = T
===>
a*A + b*A + c*A + d*A - (5*b + 7*c + 13*d)*10^5 = T
===>
(a + b + c + d)*A - (5*b + 7*c + 13*d)*10^5 = T
===>
A = (T + (5*b + 7*c + 13*d)*10^5)/(a + b + c + d)
Trong đó
a + b + c + d = COUNTA(Cột xếp loại)
b = COUNTIF(Cột xếp loại, "B")
c = COUNTIF(Cột xếp loại, "C")
d = COUNTIF(Cột xếp loại, "D")
===>
5*b + 7*c + 13*d = 5*COUNTIF(Cột xếp loại, "B") + 7*COUNTIF(Cột xếp loại, "C") + 13*COUNTIF(Cột xếp loại, "D")
===>
5*b + 7*c + 13*d = SUMPRODUCT(COUNTIF(Cột xếp loại,{"B","C","D"})*({5,7,13}))
Vậy:
A = (T + SUMPRODUCT(COUNTIF(Cột xếp loại,{"B","C","D"})*({5,7,13}))*10^5)/COUNTA(Cột xếp loại)
Chuyển thành công thức tại cell D3:

=($D$18+SUMPRODUCT(COUNTIF($C$3:$C$16,{"B","C","D"})*({5,7,13}))*10^5)/COUNTA($C$3:$C$16)-LOOKUP($C3,{"A","B","C","D"},{0,5,7,13})*10^5

----------------------
Ta có thể tạo 1 bảng tra ghi lại mối tương quan giửa xếp loại và số tiền như sau



và công thức tính tiền cho từng loại liên kết với bảng tra này là:

=($D$18+SUMPRODUCT(COUNTIF($C$3:$C$16,$I$2:$I$5)*($J$2:$J$5)))/COUNTA($C$3:$C$16)-LOOKUP($C3,$I$2:$J$5)

 

[Chuotdong]

Cám ơn các phương án của các bạn đưa ra. Như ban đầu đã nói mình đã nghĩ đến phương án Countif() nên sẽ chọn phương án của anh NDU, tuy nhiên cho mình hỏi thêm trong trường hợp phát sinh một mức thưởng không có mức chênh cố đinh với mức A (trong file Excel mình có ghi "Loại khác kém loại A: tùy chọn") thì nên xử lý công thức như thế nào. Ví dụ có 2 người bị xếp loại E ứng với mức tiền cố định là 200.000 đồng dù tổng tiền có thể thay đổi

Các phương án dùng Solver, name và VBA .... cũng hay nhưng do yêu cầu thực tế mình cần dùng công thức hơn

 

[ndu96081631]

Cám ơn các phương án của các bạn đưa ra. Như ban đầu đã nói mình đã nghĩ đến phương án Countif() nên sẽ chọn phương án của anh NDU, tuy nhiên cho mình hỏi thêm trong trường hợp phát sinh một mức thưởng không có mức chênh cố đinh với mức A (trong file Excel mình có ghi "Loại khác kém loại A: tùy chọn") thì nên xử lý công thức như thế nào. Ví dụ có 2 người bị xếp loại E ứng với mức tiền cố định là 200.000 đồng dù tổng tiền có thể thay đổi

Các phương án dùng Solver, name và VBA .... cũng hay nhưng do yêu cầu thực tế mình cần dùng công thức hơn

Cái này cũng dễ!
Nếu loại E luôn cố định 200000 thì trước tiên điền số tiền thưởng cho tất cả những người xếp loại E ---> Tổng tiền sẽ bị trừ bớt (vì số tiền đã điền vào)... Và với tổng tiền mới này thì ta cứ tính toán bình thường cho các loại A, B, C, D như trước thôi

 

[Chuotdong]

Cám ơn anh, nhưng cũng không đơn giản lắm vì sẽ phải lọc ra tất cả các ô không có xếp loại thuộc vào A,B,C,D

Công thức lọc sẽ như thế nào vậy ?

 

[ndu96081631]

Cám ơn anh, nhưng cũng không đơn giản lắm vì sẽ phải lọc ra tất cả các ô không có xếp loại thuộc vào A,B,C,D

Công thức lọc sẽ như thế nào vậy ?

Đầu tiên xin đề xuất bạn cần xem lại việc đặt tên xếp loại... Đa phần hàm Excel không phân biệt HOA thường nên giữa loại x và X rất khó phân biệt ---> Chỉ khiến vấn đề phức tạp thêm
Vậy liệu có thể sửa cho chúng khác nhau không, chẳng hạn x và X sửa thành X và Y
Nếu được như vậy thì ta làm như sau:
- Tạo 1 bảng tra các mức thưởng cố định



- Cell D17, tính xem sau khi trừ đi các loại cố định, ta còn lại bao nhiêu tiền:

=$D$18-SUMPRODUCT(COUNTIF($C$3:$C$16,$I$12:$I$22),$J$12:$J$22)

- Cell D3, tính toán mức thưởng cụ thể cho các loại A, B, C, D:

=IF(C3="","",IF(COUNTIF($I$12:$I$22,C3),VLOOKUP(C3,$I$12:$J$22,2,0),($D$17+SUMPRODUCT(COUNTIF($C$3:$C$16,$I$2:$I$5)*($J$2:$J$5)))/SUMPRODUCT(COUNTIF($C$3:$C$16,$I$2:$I$5))-LOOKUP($C3,$I$2:$J$5)))

 

[thang314]

thử nhé:
đặt định mức A= bao nhiêu đó ở d2
sau đó bạn thử gõ công thức trên vào các ô còn lại nhé.
=IF(C3="b",$D$2-500000,IF(C3="c",$D$2-700000,IF(C3="d",$D$2-1300000,IF(C3="a",$D$2,"tuy chon"))))

 

[ndu96081631]

thử nhé:
đặt định mức A= bao nhiêu đó ở d2
sau đó bạn thử gõ công thức trên vào các ô còn lại nhé.
=IF(C3="b",$D$2-500000,IF(C3="c",$D$2-700000,IF(C3="d",$D$2-1300000,IF(C3="a",$D$2,"tuy chon"))))

Trời! Ngắn gọn nhỉ?
Thế ta sẽ gõ số bi nhiêu vào D2 vậy đồng chí?

 

[thang314]

đó sẽ là số mà mức A được hưởng. nếu như vd thì là 1400000. vậy thôi.

 

[nghiaphuc]

đó sẽ là số mà mức A được hưởng. nếu như vd thì là 1400000. vậy thôi.

Vấn đề là bạn tính như thế nào để ra con số 1400000 đó? Lưu ý là với tổng tiền thay đổi thì con số này cũng thay đổi theo.

 

[ndu96081631]

đó sẽ là số mà mức A được hưởng. nếu như vd thì là 1400000. vậy thôi.

Đúng là cái ông này chẳng hiểu người ta hỏi gì cả
Hỏi bạn nha: Số 1400000 ấy được tính từ đâu ra hay bạn tự mình tưởng tượng rồi gõ vào?
Ẹc... Ẹc...

 

[thang314]

các bác có hay được chia tiền thưởng của công ty cho người lao động không. em thì thường xuyên chia lại cho chính nhân viên của mình thôi. nguyên tắc chính:
định mức đầu tiên được tính cho người loại cao nhất: cụ thể: A
thứ 2: không bao giờ chia mà có loại khác kém loại A: tuỳ chọn.

 

[ndu96081631]

các bác có hay được chia tiền thưởng của công ty cho người lao động không. em thì thường xuyên chia lại cho chính nhân viên của mình thôi. nguyên tắc chính:
định mức đầu tiên được tính cho người loại cao nhất: cụ thể: A
thứ 2: không bao giờ chia mà có loại khác kém loại A: tuỳ chọn.

Nếu là bạn quản lý thì bạn chia thế nào là việc của bạn, ở đây là giải quyết bài toán chia tiền của người khác bạn à!
Tức người khác yêu cầu thế nào ta làm thế nấy ---> Có phải tiền của bạn đâu mà cứ đòi chia theo ý mình thế nhỉ?
Hỏi tiếp nhé: Nếu bạn cứ chia loại A = 1,400,000 thì chẳng liên quan gì đến tổng tiền à? Nếu tổng tiền = 100,000,000 hoặc tổng tiền = 10,000,000 thì loại A = bi nhiêu hay vẫn cứ = 1,400,000
Ẹc... Ẹc...

 

[thang314]

bạn giải quyết chỗ " Loại khác kém loại A: tuỳ chọn" thế nào

 

[ndu96081631]

bạn giải quyết chỗ " Loại khác kém loại A: tuỳ chọn" thế nào

Bạn cứ đọc lại từ bài 1 đến cuối tự nhiên sẽ rõ người ta muốn gì và việc giải quyết vụ KHÁC ấy nằm ở bài 10

 

[ChanhTQ@]

các bác có hay được chia tiền thưởng của công ty cho người lao động không. em thì thường xuyên chia lại cho chính nhân viên của mình thôi. nguyên tắc chính: . . .

Mình thì liên quan tới cái này từ khi nó mới ra đời lựng.

& thường thì như sau: Có 4 mức, A, B, C & KK (khuyến khích)

KK cho ~ nhân viên mới đến CQ dưới vài 3 tháng & ~ NV bị kỹ luật, vi fạm trong năm;

Loại A chỉ 70% của đơn vị/bộ fận/Ca,tổ

Tiền thưởng thì loại B=.8 loại A & C = .6 loại A

(ách chia này cũng tiện đó chứ?

Giả dụ có 1 tỉ chia cho 700 con người với con số tương ứng A-B-C-KK là 500,100, 60, 40
thì êxcel sẽ giúp ta trong chớp mắt mà thôi!

Thân!

 

[tranminhduong1592]

làm e bài này vs. e đăng lên đây cả tháng nay mà k ai làm hộ. chỉ e những cách làm bài này vs a. e cảm ơn

 

[SA_DQ]

=VLOOKUP(G7,BTra,IF(E7="CC",2,IF(E7="DH",3,4)),FALSE)
​

 

[comet_1701]

làm e bài này vs. E đăng lên đây cả tháng nay mà k ai làm hộ. Chỉ e những cách làm bài này vs a. E cảm ơn

=index($d$25:$f$28,match(g8,$c$25:$c$28,0),match(e8,$d$24:$f$24,0))

=VLOOKUP(G7,$C$25:$F$28,MATCH(E7,$C$24:$F$24,0),0)

=HLOOKUP(E7,$D$24:$F$28,MATCH(G7,$C$24:$C$28,0),0)

thêm mấy cách nữa cho bạn