Hướng dẫn tính tổng tiền thưởng theo cấp

[saobekhonglac]

Chào Anh/Chị.

Mình có file tiền thưởng theo cấp nhân viên. Mình muốn tỉnh tổng tiền thưởng từng nhiên viên theo cấp nhỏ nhất.

Ví dụ: Nếu nhân viên xuất hiện từ cấp 1 đến cấp 7 thì tình tổng theo cấp 1
Nếu nhân viên xuất hiện từ code 1 đến cấp 6 thì vãn tính tổng theo cấp 1
.....
Nếu nhân viên xuất hiện từ code 2 đến cấp 3 thì tính tổng theo cấp 2
Nếu nhân viên xuất hiện từ code 4 đến cấp 7 thì tính tổng theo cấp 4
Nếu nhân viên chỉ xuất hiện ở cấp 7 thì tính tổng theo cấp 7
....
Mình có tính thử từ cấp 1 đến cấp 3 trong file từ cột K đến L

 

[Phan Thế Hiệp]

Chào Anh/Chị.

Mình có file tiền thưởng theo cấp nhân viên. Mình muốn tỉnh tổng tiền thưởng từng nhiên viên theo cấp nhỏ nhất.

Ví dụ: Nếu nhân viên xuất hiện từ cấp 1 đến cấp 7 thì tình tổng theo cấp 1
Nếu nhân viên xuất hiện từ code 1 đến cấp 6 thì vãn tính tổng theo cấp 1
.....
Nếu nhân viên xuất hiện từ code 2 đến cấp 3 thì tính tổng theo cấp 2
Nếu nhân viên xuất hiện từ code 4 đến cấp 7 thì tính tổng theo cấp 4
Nếu nhân viên chỉ xuất hiện ở cấp 7 thì tính tổng theo cấp 7
....
Mình có tính thử từ cấp 1 đến cấp 3 trong file từ cột K đến L

Thử:

J2=IFERROR(INDIRECT(MID(REPT(TEXT(AGGREGATE(15,6,(ROW($A$2:$A$66)+COLUMN($A$1:$G$1)*10^3)/(COUNTIF($J$1:$J1,$A$2:$G$66)=0),1),"C000R000"),2),5,8),),"")

Enter fill xuống.

K2=IF(J2<>"",SUMIF(INDEX($A$2:$G$66,,MATCH(1,SIGN(MMULT(TRANSPOSE(ROW($A$2:$A$66)^0),COUNTIF(J2,$A$2:$G$66))),)),J2,$H$2:$H$66),)
L2=IFERROR("cấp " &MATCH(1,SIGN(MMULT(TRANSPOSE(ROW($A$2:$A$66)^0),COUNTIF(J2,$A$2:$G$66))),),"")

Ctrl+Shift+Enter fill xuống.
Xem file kèm.
Thân

 

[saobekhonglac]

Thử:

J2=IFERROR(INDIRECT(MID(REPT(TEXT(AGGREGATE(15,6,(ROW($A$2:$A$66)+COLUMN($A$1:$G$1)*10^3)/(COUNTIF($J$1:$J1,$A$2:$G$66)=0),1),"C000R000"),2),5,8),),"")

Enter fill xuống.

K2=IF(J2<>"",SUMIF(INDEX($A$2:$G$66,,MATCH(1,SIGN(MMULT(TRANSPOSE(ROW($A$2:$A$66)^0),COUNTIF(J2,$A$2:$G$66))),)),J2,$H$2:$H$66),)
L2=IFERROR("cấp " &MATCH(1,SIGN(MMULT(TRANSPOSE(ROW($A$2:$A$66)^0),COUNTIF(J2,$A$2:$G$66))),),"")

Ctrl+Shift+Enter fill xuống.
Xem file kèm.
Thân

Cám ơn anh nhiều. Nhìn công thức hại não quá không hiểu gì hết.

 

[Phan Thế Hiệp]

Cám ơn anh nhiều. Nhìn công thức hại não quá không hiểu gì hết.

Qua bài tương tự có giải thích.
https://www.giaiphapexcel.com/diend...ùng-từ-2-cột-và-tính-tổng.136566/#post-872185
Thân

 

[saobekhonglac]

Qua bài tương tự có giải thích.
https://www.giaiphapexcel.com/diend...ùng-từ-2-cột-và-tính-tổng.136566/#post-872185
Thân

Làm mới có 6000 dòng mà nó chạy thấy thương luôn. Dữ liệu em hơn 65,000 dòng

 

[SA_DQ]

Làm mới có 6000 dòng mà nó chạy thấy thương luôn. Dữ liệu em hơn 65,000 dòng

Vậy thử trãi nghiệm với cái niL

Sub TinhThuong()
 Dim Rng As Range, Rg0 As Range, sRng As Range, Cls As Range, Arr()
 Dim Rws As Long, J As Integer, W As Long
 
 Rws = [B1].CurrentRegion.Rows.Count
 For Each Cls In Range([k2], [k2].End(xlDown))
    For J = 1 To 7
        Arr() = Cells(2, J).Resize(Rws).Value
        For W = 1 To UBound(Arr())
            If Arr(W, 1) = Cls.Value Then
                Cls.Offset(, 1).Value = "C" & Str(J)
                GoTo GPE
            End If
        Next W
    Next J
GPE:
 Next Cls
End Sub

 

[antamhn]

Bạn thử giải quyết bằng Solver xem sao
- 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

 

[saobekhonglac]

Vậy thử trãi nghiệm với cái niL

Sub TinhThuong()
Dim Rng As Range, Rg0 As Range, sRng As Range, Cls As Range, Arr()
Dim Rws As Long, J As Integer, W As Long

Rws = [B1].CurrentRegion.Rows.Count
For Each Cls In Range([k2], [k2].End(xlDown))
    For J = 1 To 7
        Arr() = Cells(2, J).Resize(Rws).Value
        For W = 1 To UBound(Arr())
            If Arr(W, 1) = Cls.Value Then
                Cls.Offset(, 1).Value = "C" & Str(J)
                GoTo GPE
            End If
        Next W
    Next J
GPE:
Next Cls
End Sub

Nó chỉ chạy ra chữ C1 thôi chứ không có dữ liệu gì nữa anh ơi.

Bài đã được tự động gộp: 3/8/18

Bạn thử giải quyết bằng Solver xem sao
- 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

Cột D đang là dữ liệu chi tiết mà Anh. Làm vậy mất dữ liệu rồi sao có kết quả.

 

[SA_DQ]

Nó chỉ chạy ra chữ C1 thôi chứ không có dữ liệu gì nữa anh ơi.

Vậy bạn muốn chúng thể hiện cái gì?
Chứ thể hiện cột số liệu cuối cùng thì cần gì fải tìm cho tổn hao sức khỏe làm vậy?

 

[congnt92]

Làm mới có 6000 dòng mà nó chạy thấy thương luôn. Dữ liệu em hơn 65,000 dòng

Phương pháp lựa chọn dựa trên khối lượng vấn đề cần giải quyết mà bạn.
Bởi vậy nên M$ phát triển excel vừa hàm, vừa có 1 vài tính năng thay thế cho hàm tại ribbon, vừa có vba.
Hàm của a Hiệp đưa ra vì căn cứ vào mẫu bạn đưa. Nếu ngay từ đầu bạn ghi chú khối lượng dữ liệu lớn và đăng bài ở box lập trình thì sẽ khác rồi .
Vì hàm cao siêu không rành, vba mù tịt nên nếu gặp công việc như bạn, mình chọn cách hơi "tay chân" xíu
Kết hợp pivot table, nó sẽ cộng lũy kế cho bạn từng mã nhân viên theo từng cấp, sau đó mình copy lần lượt 7 cấp này (theo thứ tự từ cấp nhỏ đến lớn) nối liên tục và dùng công cụ remove duplicate có sẵn ở ribbon. Nó sẽ gỡ bỏ các giá trị trùng từ dưới lên do đó sẽ đảm bảo việc lấy giá trị tổng tiền ở cấp thấp nhất.
Làm cũng chỉ độ 2-3' là ra kết quả nhưng cách này hơi thủ công và nếu chỉ 7 cấp thì được chứ 70 cấp mà copy nối liên tục vậy cũng oải. Mình góp vui thôi

 

[saobekhonglac]

Vậy bạn muốn chúng thể hiện cái gì?
Chứ thể hiện cột số liệu cuối cùng thì cần gì fải tìm cho tổn hao sức khỏe làm vậy?

Chạy ra kết quả tiền của từng nhân viên theo số bên dưới:

Đối chiếu
A10165 6,969,905
A10166 1,082,081
A10168 2,588,755
A10171 2,324,775
A10180 782,548
A10198 2,279,245
A10380 409,875
A10655 1,450,041
A10948 104,500
A10174 181,872
A10658 399,651
A10770 288,340
A10853 1,082,081
A10181 112,925
A10657 352,113
A10659 1,950
A10661 123,245
A10717 870,655
A10837 147,415
A11059 98,446
A11219 2,324,775
A11313 1,159,705
A11411 1,082,081
A12351 323,395
A12503 16,320
A10169 128,970
A10175 459,455
A10186 109,200
A10228 505,645
A10246 707,950
A10919 465,265
A11244 488,103
A10187 130,055
A10189 397,705
A10195 185,248
A10200 223,580
A10204 197,525
A10219 300,085
A10220 64,245
A10241 93,690
A10269 132,660
A10271 281,900
A10283 147,120
A10288 291,575
A10392 44,110
A10663 76,280
A10664 123,245
A10690 139,157
A10694 97,211
A10867 192,760
A10924 1,082,081
A10993 125,450
A11062 49,257
A11063 49,189
A11269 73,650
A11271 60,380
A11303 70,375
A11350 48,305
A11368 775
A11577 234,950
A11723 83,426
A11973 2,050
A12067 100,212
A12086 190,119
A12436 10,270
A12536 92,620
A12738 70,860
A10185 185,245
A10190 192,760
A10208 128,970
A10209 108,750
A10213 109,200
A10234 208,090
A10239 256,300
A10257 86,190
A10386 302,315
A10508 208,965
A10707 363,135
A10830 112,150
A10878 130,715
A10905 83,345
A10990 99,790
A11129 64,070
A11141 459,455
A11169 23,975
A11238 4,710
A11353 91,820
A11399 104,235
A11759 63,450
A12084 1,950
A12150 10,270
A12226 30,006
A12543 14,270
A10578 48,235
A10918 86,190
A10942 291,575
A10991 92,650
A11001 76,280
A11110 281,900
A11142 234,950
A11148 125,450
A11150 256,300
A11213 49,189
A11311 44,110
A11334 73,650
A11339 223,580
A11444 49,257
A11502 64,245
A11521 300,085
A11582 139,157
A11638 60,380
A11639 4,710
A11686 185,248
A11689 208,090
A11729 208,965
A11736 197,525
A11781 293,470
A11802 63,450
A11931 363,135
A11957 70,860
A12071 104,235
A12116 75,010
A12136 108,750
A12220 130,715
A12244 130,055
A12320 30,006
A12421 302,315
A12496 94,765
A12505 23,975
A12527 7,140
A12545 192,760
A12546 125,120
A12571 70,375
A12601 1,950
A12616 64,070
A12673 132,660
A12691 185,245
A12692 147,120
A12724 14,270
A12795 2,050
A12796 100,212
A12840 800

 

[congnt92]

Chạy ra kết quả tiền của từng nhân viên theo số bên dưới:

Spoiler

Đối chiếu
A10165 6,969,905
A10166 1,082,081
A10168 2,588,755
A10171 2,324,775
A10180 782,548
A10198 2,279,245
A10380 409,875
A10655 1,450,041
A10948 104,500
A10174 181,872
A10658 399,651
A10770 288,340
A10853 1,082,081
A10181 112,925
A10657 352,113
A10659 1,950
A10661 123,245
A10717 870,655
A10837 147,415
A11059 98,446
A11219 2,324,775
A11313 1,159,705
A11411 1,082,081
A12351 323,395
A12503 16,320
A10169 128,970
A10175 459,455
A10186 109,200
A10228 505,645
A10246 707,950
A10919 465,265
A11244 488,103
A10187 130,055
A10189 397,705
A10195 185,248
A10200 223,580
A10204 197,525
A10219 300,085
A10220 64,245
A10241 93,690
A10269 132,660
A10271 281,900
A10283 147,120
A10288 291,575
A10392 44,110
A10663 76,280
A10664 123,245
A10690 139,157
A10694 97,211
A10867 192,760
A10924 1,082,081
A10993 125,450
A11062 49,257
A11063 49,189
A11269 73,650
A11271 60,380
A11303 70,375
A11350 48,305
A11368 775
A11577 234,950
A11723 83,426
A11973 2,050
A12067 100,212
A12086 190,119
A12436 10,270
A12536 92,620
A12738 70,860
A10185 185,245
A10190 192,760
A10208 128,970
A10209 108,750
A10213 109,200
A10234 208,090
A10239 256,300
A10257 86,190
A10386 302,315
A10508 208,965
A10707 363,135
A10830 112,150
A10878 130,715
A10905 83,345
A10990 99,790
A11129 64,070
A11141 459,455
A11169 23,975
A11238 4,710
A11353 91,820
A11399 104,235
A11759 63,450
A12084 1,950
A12150 10,270
A12226 30,006
A12543 14,270
A10578 48,235
A10918 86,190
A10942 291,575
A10991 92,650
A11001 76,280
A11110 281,900
A11142 234,950
A11148 125,450
A11150 256,300
A11213 49,189
A11311 44,110
A11334 73,650
A11339 223,580
A11444 49,257
A11502 64,245
A11521 300,085
A11582 139,157
A11638 60,380
A11639 4,710
A11686 185,248
A11689 208,090
A11729 208,965
A11736 197,525
A11781 293,470
A11802 63,450
A11931 363,135
A11957 70,860
A12071 104,235
A12116 75,010
A12136 108,750
A12220 130,715
A12244 130,055
A12320 30,006
A12421 302,315
A12496 94,765
A12505 23,975
A12527 7,140
A12545 192,760
A12546 125,120
A12571 70,375
A12601 1,950
A12616 64,070
A12673 132,660
A12691 185,245
A12692 147,120
A12724 14,270
A12795 2,050
A12796 100,212
A12840 800

Xác định được cấp mấy thì đã giải quyết 90% công việc rồi mà bạn. Giờ biết số cấp thì dùng Sumif theo số cột. Có thể dùng kết hợp hàm indirect để cơ động hơn.
Sửa dòng code này
Cls.Offset(, 1).Value = "C" & Str(J)
thành
Cls.Offset(, 1).Value = Str(J)
khi đó cột L sẽ trả về số cấp (tức số cột)
Tại cột M sử dụng hàm
=SUMIF(INDIRECT(LEFT(ADDRESS(1,L2,4,1),1)&":"&LEFT(ADDRESS(1,L2,4,1),1)),K2,H:H)
để tính tổng tiền.
P/S1: Mình không biết cách lồng hàm trên vào code luôn
P/S2: Bạn xem hình tránh loãng thớt.

 

[SA_DQ]

À, hiểu rồi:

Sub TinhThuong()
Dim Rng As Range, Cls As Range, Arr(), WF As Object, Rg0 As Range
Dim Rws As Long, J As Integer, W As Long

Rws = [B1].CurrentRegion.Rows.Count
Set WF = Application.WorksheetFunction
Set Rg0 = [H1].Resize(Rws)
For Each Cls In Range([k2], [k2].End(xlDown))
    For J = 1 To 7
        Set Rng = Cells(1, J).Resize(Rws)
        Arr() = Cells(2, J).Resize(Rws).Value
        For W = 1 To UBound(Arr())
            If Arr(W, 1) = Cls.Value Then
                Cls.Offset(, 1).Value = WF.SumIf(Rng, Cls, Rg0)
                GoTo GPE
            End If
        Next W
    Next J
GPE:            Next Cls
End Sub

 

[Phan Thế Hiệp]

À, hiểu rồi:

Cho em góp thêm khúc đầu "Lọc trùng dữ liệu trong Mảng" theo yêu cầu chủ thớt, khúc tính thưởng em bê nguyên xi của anh đem vào.
Cho em út cơ hội tập dượt "sao chép" VBA nhen anh. Có chỗ nào sai anh góp ý dùm em.
Cảm ơn anh nhiều.
Chúc anh cuối tuần vui cùng gia đình.