Tính tổng theo điều kiện

[yeudoi]

Nhờ mọi người giúp tạo công thức để tính tổng theo điều kiện trong file đính kèm ( Có thể viết macro cũng được). Xin cảm ơn trước.

 

[haituan]

Bạn đưa dữ liệu chỉ có 3 biến A, B, C thì m giải theo cách cổ điển một chút. Bạn tham khảo nhé! Xem có tìm ra cách mới nào ko!
Công thức bạn copy vào ô

I14=SUM(($B$11:$B$19=$H$3)*($C$11:$C$19=$H14)*($D$11:$D$19))*I$3+SUM(($B$11:$B$19=$H$4)*($C$11:$C$19=$H14)*($D$11:$D$19))*I$4+SUM(($B$11:$B$19=$H$5)*($C$11:$C$19=$H14)*($D$11:$D$19))*I$5

sau đó sử dụng ctrl+Alt+Enter
Và kéo dài đến cuối bảng. Bạn tham khảo file:
http://www.4shared.com/file/134971467/5bfae7e7/_2__Gui_bai1.html

 

[huuthang_bd]

Nhờ mọi người giúp tạo công thức để tính tổng theo điều kiện trong file đính kèm ( Có thể viết macro cũng được). Xin cảm ơn trước.

Dùng tạm UDF nhé.

Function TongNguyenLieu(TriDo, BangNguyenLieu As Range, BangKhoiLuong As Range)
Dim Rng As Range, MyAdd As String
    Set Rng = BangNguyenLieu.Offset(, 1).Resize(, 1).Find(What:=TriDo, LookAt:=xlWhole)
        If Not Rng Is Nothing Then
            MyAdd = Rng.Address
            Do
                TongNguyenLieu = TongNguyenLieu + Rng.Offset(, 1) * Application.WorksheetFunction.VLookup(Rng.Offset(, -1), BangKhoiLuong, BangKhoiLuong.Columns.Count, 0)
                Set Rng = BangNguyenLieu.Offset(, 1).Resize(, 1).Find(What:=TriDo, After:=Rng, LookAt:=xlWhole)
            Loop While Rng.Address <> MyAdd
        End If
End Function

 

[ptlong04x1]

Nhờ mọi người giúp tạo công thức để tính tổng theo điều kiện trong file đính kèm ( Có thể viết macro cũng được). Xin cảm ơn trước.

Cách làm của mình :
+ Thêm vào 1 vùng 1 phụ.
+ Dùng SumProduct.

 

[DOSNET]

Dùng tạm UDF nhé.

Function TongNguyenLieu(TriDo, BangNguyenLieu As Range, BangKhoiLuong As Range)
Dim Rng As Range, MyAdd As String
    Set Rng = BangNguyenLieu.Offset(, 1).Resize(, 1).Find(What:=TriDo, LookAt:=xlWhole)
        If Not Rng Is Nothing Then
            MyAdd = Rng.Address
            Do
                TongNguyenLieu = TongNguyenLieu + Rng.Offset(, 1) * Application.WorksheetFunction.VLookup(Rng.Offset(, -1), BangKhoiLuong, BangKhoiLuong.Columns.Count, 0)
                Set Rng = BangNguyenLieu.Offset(, 1).Resize(, 1).Find(What:=TriDo, After:=Rng, LookAt:=xlWhole)
            Loop While Rng.Address <> MyAdd
        End If
End Function

Nếu mở rộng vùng nguyên liệu và sản phẩm thì sửa lại UDF nhừ nào bác, chắc thực tế sẽ còn nhiều hơn 3 loại sản phẩm?

 

[huuthang_bd]

Nếu mở rộng vùng nguyên liệu và sản phẩm thì sửa lại UDF nhừ nào bác, chắc thực tế sẽ còn nhiều hơn 3 loại sản phẩm?

Không cần sửa Code đâu bạn. Bạn chỉ cần mở rộng vùng tham chiếu trong công thức và tuân thủ một số nguyên tắc sau khi sử dụng công thức:
- BangNguyenLieu là bảng có: Cột 1 là cột Tên sản phẩm, Cột 2 là cột Dày, Cột 3 là cột KL
- BangKhoiLuong là bảng có cột đầu tiên là cột Tên sản phẩm, cột cuối cùng là cột khối lượng muốn tính.

Nếu áp dụng mà phát sinh lỗi thì bạn gửi file lỗi lên tôi xem lại.

 

[yeudoi]

Xin cảm ơn tất cả mọi người. Không ngờ quá nhanh và quá đúng.
Sao mình chèn thêm cột tại bảng nguyên liệu thì số liệu không nhảy nhỉ

 

[haituan]

@HuuThang: Cho m hỏi một chút: Tại sao khi ta thay đổi tên sản phẩm C thành D chẳn hạn trong bảng kế hoạch sản xuất mà trong bảng nguyên liệu không có (hoặc có tên). Tại sao vẫn thống kê có số lượng tại chiều dày =25....

 

[yeudoi]

@HuuThang: Cho m hỏi một chút: Tại sao khi ta thay đổi tên sản phẩm C thành D chẳn hạn trong bảng kế hoạch sản xuất mà trong bảng nguyên liệu không có (hoặc có tên). Tại sao vẫn thống kê có số lượng tại chiều dày =25....

Đúng có lẽ công thức này vẫn chưa hoàn chỉnh mong nhờ bạn HuuThang xem lại giúp. Nếu mở rộng vùng nguyên liệu thì số không hiển thị được và nếu thay đổi tên sản phẩm trong bảng kế hoạch thì vẫn tính toán và báo lỗi.

 

[huuthang_bd]

@HuuThang: Cho m hỏi một chút: Tại sao khi ta thay đổi tên sản phẩm C thành D chẳn hạn trong bảng kế hoạch sản xuất mà trong bảng nguyên liệu không có (hoặc có tên). Tại sao vẫn thống kê có số lượng tại chiều dày =25....

Nguyên liệu có Dày 25 là nguyên liệu của SP B. Thay đổi tên sản phẩm C thì có gì liên quan?

Đúng có lẽ công thức này vẫn chưa hoàn chỉnh mong nhờ bạn HuuThang xem lại giúp. Nếu mở rộng vùng nguyên liệu thì số không hiển thị được và nếu thay đổi tên sản phẩm trong bảng kế hoạch thì vẫn tính toán và báo lỗi.

Tôi sửa lại như thế này:

Function TongNguyenLieu(TriDo, BangNguyenLieu As Range, BangKhoiLuong As Range)
Dim Rng As Range, MyAdd As String
    Set Rng = BangNguyenLieu.Offset(, 1).Resize(, 1).Find(What:=TriDo, LookAt:=xlWhole)
        If Not Rng Is Nothing Then
            MyAdd = Rng.Address
            Do
                If Application.WorksheetFunction.CountIf(BangKhoiLuong.Resize(, 1), Rng.Offset(, -1)) > 0 Then
                TongNguyenLieu = TongNguyenLieu + Rng.Offset(, 1) * Application.WorksheetFunction.VLookup(Rng.Offset(, -1), BangKhoiLuong, BangKhoiLuong.Columns.Count, 0)
                End If
                Set Rng = BangNguyenLieu.Offset(, 1).Resize(, 1).Find(What:=TriDo, After:=Rng, LookAt:=xlWhole)
            Loop While Rng.Address <> MyAdd
        End If
End Function

 

[DOSNET]

Đúng có lẽ công thức này vẫn chưa hoàn chỉnh mong nhờ bạn HuuThang xem lại giúp. Nếu mở rộng vùng nguyên liệu thì số không hiển thị được và nếu thay đổi tên sản phẩm trong bảng kế hoạch thì vẫn tính toán và báo lỗi.

Mình đã test kỹ rồi, UDF của Huuthang_bd rất ổn

 

[yeudoi]

Mình đã test kỹ rồi, UDF của Huuthang_bd rất ổn

Nhờ anh làm hộ giúp file nếu chèn thêm cột tại bảng định mức nguyên liệu thử.

 

[huuthang_bd]

Mình đã test kỹ rồi, UDF của Huuthang_bd rất ổn

Chắc mọi người đang xét trường hợp sản phẩm không có trong bảng kế hoạch nhưng có trong bảng tiêu hao nguyên vật liệu. Trường hợp này cũng có thể xảy ra do bảng định mức tiêu hao nguyên vật liệu là chung cho toàn bộ sản phẩm, còn bảng kế hoạch thì cần sản phẩm nào mới làm sản phẩm đó. Tôi đã khắc phục ở bài #10.

Còn trường hợp sản phẩm có trong bảng kế hoạch nhưng không có trong bảng định mức tiêu hao nguyên vật liệu thì sản phẩm chỉ sản phẩm nào có định mức mới được tính vào tổng, không có định mức thì bỏ qua.

Nhờ anh làm hộ giúp file nếu chèn thêm cột tại bảng định mức nguyên liệu thử.

Bạn xem lại đoạn tôi lưu ý ở bài #6 nhé:

- BangNguyenLieu là bảng có: Cột 1 là cột Tên sản phẩm, Cột 2 là cột Dày, Cột 3 là cột KL
- BangKhoiLuong là bảng có cột đầu tiên là cột Tên sản phẩm, cột cuối cùng là cột khối lượng muốn tính.

Nếu bạn muốn linh hoạt các cột này thì tôi sẽ sửa code lại.

 

[yeudoi]

Nếu làm được linh hoạt hơn thì tốt quá, cảm phiền bạn. Còn khi thay đổi thì mình vẫn thấy chưa được, nhờ bạn xem trong file

 

[huuthang_bd]

Nếu làm được linh hoạt hơn thì tốt quá, cảm phiền bạn. Còn khi thay đổi thì mình vẫn thấy chưa được, nhờ bạn xem trong file

Sửa lại như thế này cho linh hoạt.

Function TongNguyenLieu(TriDo, BangNguyenLieu As Range, DayRow As Integer, KLRow As Integer, BangKhoiLuong As Range)
Dim Rng As Range, MyAdd As String
    Set Rng = BangNguyenLieu.Offset(, DayRow - 1).Resize(, 1).Find(What:=TriDo, LookAt:=xlWhole)
        If Not Rng Is Nothing Then
            MyAdd = Rng.Address
            Do
                If Application.WorksheetFunction.CountIf(BangKhoiLuong.Resize(, 1), Rng.Offset(, 1 - DayRow)) > 0 Then
                TongNguyenLieu = TongNguyenLieu + Rng.Offset(, KLRow - DayRow) * Application.WorksheetFunction.VLookup(Rng.Offset(, 1 - DayRow), BangKhoiLuong, BangKhoiLuong.Columns.Count, 0)
                End If
                Set Rng = BangNguyenLieu.Offset(, DayRow - 1).Resize(, 1).Find(What:=TriDo, After:=Rng, LookAt:=xlWhole)
            Loop While Rng.Address <> MyAdd
        End If
End Function

Cú pháp:

=TongNguyenLieu(LoaiNguyenLieu,BangDoDinhMuc,STT_CotLoaiNguyenLieu_TrongBangDinhMuc,STT_CotKhoiLuong_TrongBangDinhMuc,BangKeHoachSanXuat)

 

[yeudoi]

Nhờ mọi người giúp tiếp. Nếu như mình thêm một điều kiện nữa thì phải sửa như thế nào. Xem trong file đính kèm.

 

[huuthang_bd]

Nhờ mọi người giúp tiếp. Nếu như mình thêm một điều kiện nữa thì phải sửa như thế nào. Xem trong file đính kèm.

Nếu vậy cần phải thêm tham số:

Function TongNguyenLieu(TriDo, LoaiNguyenLieu As String, BangNguyenLieu As Range, DayCol As Byte, LoaiNLCol As Byte, KLCol As Byte, BangKhoiLuong As Range)
Dim Rng As Range, MyAdd As String
    Set Rng = BangNguyenLieu.Offset(, DayCol - 1).Resize(, 1).Find(What:=TriDo, LookAt:=xlWhole)
        If Not Rng Is Nothing Then
            MyAdd = Rng.Address
            Do
                If Application.WorksheetFunction.CountIf(BangKhoiLuong.Resize(, 1), Rng.Offset(, 1 - DayCol)) > 0 Then
                TongNguyenLieu = TongNguyenLieu + IIf(Rng.Offset(, LoaiNLCol - DayCol).Value = LoaiNguyenLieu, Rng.Offset(, KLCol - DayCol) * Application.WorksheetFunction.VLookup(Rng.Offset(, 1 - DayCol), BangKhoiLuong, BangKhoiLuong.Columns.Count, 0), 0)
                End If
                Set Rng = BangNguyenLieu.Offset(, DayCol - 1).Resize(, 1).Find(What:=TriDo, After:=Rng, LookAt:=xlWhole)
            Loop While Rng.Address <> MyAdd
        End If
End Function

Cú pháp:

=TongNguyenLieu(QuiCach,LoaiNguyenLieu,BangDinhMuc,Cot_QuyCach,Cot_LoaiNguyenLieu,Cot_SoLuong,BangKHSX)

Trong đó:

Quy cách: Là quy cách của nguyên liệu cần tính
LoaiNguyenLieu: Là loại nguyên liệu (Trong file của bạn là A1, A2)
BangDinhMuc: Là bảng dò định mức, bắt đầu bằng cột Tên sản phẩm.
Cot_QuyCach: Là vị trí cột Quy cách trong BangDinhMuc
Cot_LoaiNguyenLieu: Là vị trí cột Loại nguyên liệu trong BangDinhMuc
Cot_QuyCach: Là vị trí cột Số lượng trong BangDinhMuc
BangKHSX: Là Bảng kế hoạch sản xuất.

Bạn xem trong file sẽ thấy rõ hơn.