Phân bổ chi phí theo điều kiện bằng VBA

[thinhnx22]

Chào anh chị và các bạn,
Trong file 3 có 3 sheet: Bang1,Bang2 và sheet ketqua. Bang1 là có chi phí chung theo từng tài khoản, bộ phận và khoản mục chi phí. Bang2 là chi phí chi tiết tương ứng với tỷ lệ của bộ phận và khoản mục chi phí. Sheet kết quả = chi phí chung * tỷ lệ chi tiết tương ứng. Em có đính kèm file, mô tả cách làm và kết quả mong muốn. Nhờ anh chị và các bạn code để được kết quả . Em chân thành cảm ơn.

 

[HUONGHCKT]

Chào anh chị và các bạn,
Trong file 3 có 3 sheet: Bang1,Bang2 và sheet ketqua. Bang1 là có chi phí chung theo từng tài khoản, bộ phận và khoản mục chi phí. Bang2 là chi phí chi tiết tương ứng với tỷ lệ của bộ phận và khoản mục chi phí. Sheet kết quả = chi phí chung * tỷ lệ chi tiết tương ứng. Em có đính kèm file, mô tả cách làm và kết quả mong muốn. Nhờ anh chị và các bạn code để được kết quả . Em chân thành cảm ơn.

Bạn xem file. Nhấn vào mặt cười để xem kết quả.
Hãy thử lại bằng cách thêm bớt, thay đổi dữ liệu.

 

[thinhnx22]

Bạn xem file. Nhấn vào mặt cười để xem kết quả.
Hãy thử lại bằng cách thêm bớt, thay đổi dữ liệu.

Dạ, em cảm ơn nhé.

 

[thinhnx22]

Dạ, em cảm ơn nhé.

Trong trường hợp dữ liệu dòng của bảng 2 nhiều hơn dòng bảng 1, dữ liệu sẽ chạy ra chưa đúng. Nhờ anh và các bạn sửa lại giúp em nhé. Lúc đầu, để mọi người dễ hình dung cách làm, nên em đã cắt bớt dữ liệu đi ạ.

 

[bebo021999]

Bài này phải dân kế toán tổng hợp mới hiểu và làm được nhé.
Nếu đây là bài toán tính Z sản phẩm, thì bạn đang mô tả quá sơ sài.
Theo bảng kết quả, bạn đang tập hợp Z theo tài khoản 622, chi tiết theo từng SP .
Câu hỏi đặt ra là:
1) Theo kết quả, với từng tài khoản, thì 1 SP tương ứng với 1 bộ phận và 1 mã KMCP (Quan hệ 1-1) . Trong khi trên thực tế bảng 1, một SP có nhiều loại BP và mã KMCP (1- nhiều)
2) Ngoài kết quả theo TK, còn có yêu cầu tổng hợp theo từng SP không? Làm luôn 1 thể.

Với file mới cập nhật, bạn làm lại sheet kết quả nhé.

 

[thinhnx22]

Bài này phải dân kế toán tổng hợp mới hiểu và làm được nhé.
Nếu đây là bài toán tính Z sản phẩm, thì bạn đang mô tả quá sơ sài.
Theo bảng kết quả, bạn đang tập hợp Z theo tài khoản 622, chi tiết theo từng SP .
Câu hỏi đặt ra là:
1) Theo kết quả, với từng tài khoản, thì 1 SP tương ứng với 1 bộ phận và 1 mã KMCP (Quan hệ 1-1) . Trong khi trên thực tế bảng 1, một SP có nhiều loại BP và mã KMCP (1- nhiều)
2) Ngoài kết quả theo TK, còn có yêu cầu tổng hợp theo từng SP không? Làm luôn 1 thể.

Với file mới cập nhật, bạn làm lại sheet kết quả nhé.

Em chào anh,
1)Đây đúng là bài toán tính giá thành. Bảng 1 em có chi phí chung như điện nước, thuê nhà (Cột E bảng 1). Bây giờ em cần phân bổ chi phí chung từ SPC (sản phẩm chung) theo tài khoản, bộ phận, khoản mục chi phí. Bảng 2 em có tỷ lệ chi tiết theo từng sản phẩm. Nếu bảng 1 và bảng 2 chung nhau, bộ phận và khoản mục chi phí, thì từ chi phí chung bảng 1 * tỷ lệ chi tiết theo từng sản phẩm của Bảng 2.
2).Vì đây là bài toán đặc thù liên quan đến kế toán, em sợ giải thích nhiều thì mọi người không hiểu vì không phải ai trên diễn đàn cũng cùng ngành với mình nên cố gắng mô tả bằng file và kết quả mẫu bằng công thức đơn giản nhất để minh họa.
3) Em có đính kèm lại kết quả mẫu của 3 trường hợp cụ thể, trường hợp còn lại đều làm tương tự. Kết quả mẫu này cũng giống bài 1 nhưng em cơ sở dữ liệu của bảng 2 thì đang nhiều hơn bảng 1.
Nhờ anh và các bạn hỗ trợ ạ.

 

[kelacloi]

Em chào anh,
1)Đây đúng là bài toán tính giá thành. Bảng 1 em có chi phí chung như điện nước, thuê nhà (Cột E bảng 1). Bây giờ em cần phân bổ chi phí chung từ SPC (sản phẩm chung) theo tài khoản, bộ phận, khoản mục chi phí. Bảng 2 em có tỷ lệ chi tiết theo từng sản phẩm. Nếu bảng 1 và bảng 2 chung nhau, bộ phận và khoản mục chi phí, thì từ chi phí chung bảng 1 * tỷ lệ chi tiết theo từng sản phẩm của Bảng 2.
2).Vì đây là bài toán đặc thù liên quan đến kế toán, em sợ giải thích nhiều thì mọi người không hiểu vì không phải ai trên diễn đàn cũng cùng ngành với mình nên cố gắng mô tả bằng file và kết quả mẫu bằng công thức đơn giản nhất để minh họa.
3) Em có đính kèm lại kết quả mẫu của 3 trường hợp cụ thể, trường hợp còn lại đều làm tương tự. Kết quả mẫu này cũng giống bài 1 nhưng em cơ sở dữ liệu của bảng 2 thì đang nhiều hơn bảng 1.
Nhờ anh và các bạn hỗ trợ ạ.

Dùng công thức rồi dùng pivot table để tổng hợp là được.
Sao phải code trong trường hợp này nhỉ?

 

[thinhnx22]

Dùng công thức rồi dùng pivot table để tổng hợp là được.
Sao phải code trong trường hợp này nhỉ?

Mình chưa nghĩ ra cách làm công thức rồi sau đó pivot table (còn dữ liệu nguồn bảng 1 mình đang dùng pivot). Bạn làm thử kết quả giúp mình, để mình học hỏi thêm nhé.

 

[HUONGHCKT]

Em chào anh,
1)Đây đúng là bài toán tính giá thành. Bảng 1 em có chi phí chung như điện nước, thuê nhà (Cột E bảng 1). Bây giờ em cần phân bổ chi phí chung từ SPC (sản phẩm chung) theo tài khoản, bộ phận, khoản mục chi phí. Bảng 2 em có tỷ lệ chi tiết theo từng sản phẩm. Nếu bảng 1 và bảng 2 chung nhau, bộ phận và khoản mục chi phí, thì từ chi phí chung bảng 1 * tỷ lệ chi tiết theo từng sản phẩm của Bảng 2.
2).Vì đây là bài toán đặc thù liên quan đến kế toán, em sợ giải thích nhiều thì mọi người không hiểu vì không phải ai trên diễn đàn cũng cùng ngành với mình nên cố gắng mô tả bằng file và kết quả mẫu bằng công thức đơn giản nhất để minh họa.
3) Em có đính kèm lại kết quả mẫu của 3 trường hợp cụ thể, trường hợp còn lại đều làm tương tự. Kết quả mẫu này cũng giống bài 1 nhưng em cơ sở dữ liệu của bảng 2 thì đang nhiều hơn bảng 1.
Nhờ anh và các bạn hỗ trợ ạ.

Bạn kiểm tra lại dữ liệu của mình. Giải thuật của bài toán bạn cũng đã phân tích rất đúng trong code rồi. Tôi thấy Tài khoản 622 trong Sh2 không có đồng thời 2 mã (Mã Bộ phận & Mã KMCP ) trùng với đồng thời 2 mã (Mã Bộ phận & mã KMCP) nào cả. bạn có thể kiểm tra nói bằng cách gán mảng Data xuống Sh nào đó. Code cũng dò mảng này ra sau khi đã cộng dồn các mã có tiêu chí giống nhau.

 

[thinhnx22]

Bạn kiểm tra lại dữ liệu của mình. Giải thuật của bài toán bạn cũng đã phân tích rất đúng trong code rồi. Tôi thấy Tài khoản 622 trong Sh2 không có đồng thời 2 mã (Mã Bộ phận & Mã KMCP ) trùng với đồng thời 2 mã (Mã Bộ phận & mã KMCP) nào cả. bạn có thể kiểm tra nói bằng cách gán mảng Data xuống Sh nào đó. Code cũng dò mảng này ra sau khi đã cộng dồn các mã có tiêu chí giống nhau.

Em xem giải thuật thì rất đúng yêu cầu của em rồi, hướng làm ban đầu của em cũng là như vậy nhưng em không biết cách code. Em chỉ lấy tài khoản ở bảng 1 thôi anh ạ. Còn bảng 2 em không dùng đến tài khoản. Bảng 1 sau khi cộng dồn theo tài khoản, bộ phận và khoản mục chi phí ra được số tiền là A. Nếu bảng 1 và bảng 2 chung nhau bộ phận và khoản mục chi phí thì sẽ lấy A*tỷ lệ chi tiết. Sheet kết quả thì tài khoản (được lấy trong bảng 1) lập lại số dòng đúng bằng số dòng của sản phẩm chi tiết trong sheet bảng 2. Anh xem giúp em file kết quả em updated bài 6 nhé.

 

[bebo021999]

Làm theo file tại bài 4.

Option Explicit
Sub test()
Dim lr1&, lr2&, i&, k&, cell As Range, key, id As String, rng, arr(), s, arrKQ(1 To 1000000, 1 To 8)
Dim dic As Object
Set dic = CreateObject("Scripting.dictionary")
With Worksheets("Bang2")
    lr2 = .Cells(Rows.Count, "A").End(xlUp).Row
    ReDim arr(1 To lr2 - 1, 1 To 2)
    rng = .Range("A2:H" & lr2).Value2
    For i = 1 To lr2 - 1
        arr(i, 1) = rng(i, 1) & "-" & rng(i, 2) & "-" & rng(i, 3) & "-" & rng(i, 5)
        arr(i, 2) = rng(i, 7)
    Next
    .Range("L2").Resize(lr2 - 1, 2).Value = arr
End With
rng = 0
With Worksheets("Bang1")
    lr1 = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:H" & lr1).Value
        For i = 1 To lr1 - 1
            id = rng(i, 1) & "-" & rng(i, 2) & "-" & rng(i, 4)
            If Not dic.exists(id) Then
                dic.Add id, rng(i, 8) ' tao danh sach duy nhat theo id
                Else
                    dic(id) = dic(id) + rng(i, 8) ' cong don so tien cho tung id
            End If
        Next
End With
        For Each key In dic.keys
            For i = 1 To UBound(arr)
                s = Split(arr(i, 1), "-")
                If arr(i, 1) Like key & "*" Then
                    k = k + 1
                    arrKQ(k, 1) = s(0)
                    arrKQ(k, 2) = s(1): arrKQ(k, 3) = s(1)
                    arrKQ(k, 4) = s(2): arrKQ(k, 5) = s(2)
                    arrKQ(k, 6) = s(3): arrKQ(k, 7) = s(3)
                    arrKQ(k, 8) = arr(i, 2) * dic(key)
                End If
            Next
        Next
Worksheets("Ketqua").Range("A2").Resize(UBound(arrKQ), 8).Value = arrKQ
End Sub

 

[thinhnx22]

Làm theo file tại bài 4.

Option Explicit
Sub test()
Dim lr1&, lr2&, i&, k&, cell As Range, key, id As String, rng, arr(), s, arrKQ(1 To 1000000, 1 To 8)
Dim dic As Object
Set dic = CreateObject("Scripting.dictionary")
With Worksheets("Bang2")
    lr2 = .Cells(Rows.Count, "A").End(xlUp).Row
    ReDim arr(1 To lr2 - 1, 1 To 2)
    rng = .Range("A2:H" & lr2).Value2
    For i = 1 To lr2 - 1
        arr(i, 1) = rng(i, 1) & "-" & rng(i, 2) & "-" & rng(i, 3) & "-" & rng(i, 5)
        arr(i, 2) = rng(i, 7)
    Next
    .Range("L2").Resize(lr2 - 1, 2).Value = arr
End With
rng = 0
With Worksheets("Bang1")
    lr1 = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A2:H" & lr1).Value
        For i = 1 To lr1 - 1
            id = rng(i, 1) & "-" & rng(i, 2) & "-" & rng(i, 4)
            If Not dic.exists(id) Then
                dic.Add id, rng(i, 8) ' tao danh sach duy nhat theo id
                Else
                    dic(id) = dic(id) + rng(i, 8) ' cong don so tien cho tung id
            End If
        Next
End With
        For Each key In dic.keys
            For i = 1 To UBound(arr)
                s = Split(arr(i, 1), "-")
                If arr(i, 1) Like key & "*" Then
                    k = k + 1
                    arrKQ(k, 1) = s(0)
                    arrKQ(k, 2) = s(1): arrKQ(k, 3) = s(1)
                    arrKQ(k, 4) = s(2): arrKQ(k, 5) = s(2)
                    arrKQ(k, 6) = s(3): arrKQ(k, 7) = s(3)
                    arrKQ(k, 8) = arr(i, 2) * dic(key)
                End If
            Next
        Next
Worksheets("Ketqua").Range("A2").Resize(UBound(arrKQ), 8).Value = arrKQ
End Sub

Em cảm ơn anh. Kết quả thì đúng rồi anh ạ, nhưng em muốn tài khoản lấy từ sheet bảng 1 anh ạ. Theo em hiểu thì tài khoản đang lấy từ sheet bảng 2 đúng không ạ?

 

[bebo021999]

Em cảm ơn anh. Kết quả thì đúng rồi anh ạ, nhưng em muốn tài khoản lấy từ sheet bảng 1 anh ạ. Theo em hiểu thì tài khoản đang lấy từ sheet bảng 2 đúng không ạ?

Lấy từ bảng 1 nhé (vì là bảng phát sinh thực tế). Trong code, nó là dic. Phần cuối của code, bạn sẽ thấy mình duyệt qua từng key của dic để nạp vào Kết quả.
Còn bảng 2 chỉ là bảng đăng ký định mức thôi, chỉ có giá trị tham chiếu.

 

[kelacloi]

Em cảm ơn anh. Kết quả thì đúng rồi anh ạ, nhưng em muốn tài khoản lấy từ sheet bảng 1 anh ạ. Theo em hiểu thì tài khoản đang lấy từ sheet bảng 2 đúng không ạ?

Có phải thế này không bạn?
Phần tài khoản thì bạn lấy từ sheet1 sang xem.

 

[thinhnx22]

Lấy từ bảng 1 nhé (vì là bảng phát sinh thực tế). Trong code, nó là dic. Phần cuối của code, bạn sẽ thấy mình duyệt qua từng key của dic để nạp vào Kết quả.
Còn bảng 2 chỉ là bảng đăng ký định mức thôi, chỉ có giá trị tham chiếu.

Đúng rồi anh ạ, chắc em đính kèm bị nhầm file, em cảm ơn anh và mọi người nhé.

 

[bebo021999]

SPC11: 98%
SPC1111: 2%

Có vẻ 2 SP này là 1, vì tổng = 100%

Có phải mã bi sai không? Sửa lại thành SPC11 xem thử?

 

[thinhnx22]

SPC11: 98%
SPC1111: 2%

Có vẻ 2 SP này là 1, vì tổng = 100%

Có phải mã bi sai không? Sửa lại thành SPC11 xem thử?

dạ đúng anh ạ, tỷ lệ các bên gửi chưa chuẩn, khi sang bảng 2 đáng nhẽ không còn SPC (3 chữ cái đầu tiên) nữa, mà phải SP12,SP13,SP14.... .

 

[bebo021999]

dạ đúng anh ạ, tỷ lệ các bên gửi chưa chuẩn, khi sang bảng 2 đáng nhẽ không còn SPC (3 chữ cái đầu tiên) nữa, mà phải SP12,SP13,SP14.... .

"Đúng" có nghĩa là data gốc không sai, hay là xác nhận câu hỏi "Có phải mã bị sai?" là "đúng mã bị sai" ?
Vì "tỷ lệ chưa chuẩn", vậy bạn sửa cho chuẩn rồi kiểm ra lại còn sai không?

 

[thinhnx22]

"Đúng" có nghĩa là data gốc không sai, hay là xác nhận câu hỏi "Có phải mã bị sai?" là "đúng mã bị sai" ?
Vì "tỷ lệ chưa chuẩn", vậy bạn sửa cho chuẩn rồi kiểm ra lại còn sai không?

Dạ, mã bị sai anh nhé, và em sẽ báo các bên kiểm tra lại tỷ lệ ạ.