Tính tổng có điều kiện trong khoảng thời gian

nguyentrieuphuc đã viết:

Chào anh chị,
Em có một khó khăn như này, mong anh chị giúp đỡ và giải đáp giúp em!
- Em có một bảng dữ liệu bao gồm thành phẩm (TP) nhập sản xuất liên tục trong tháng (năm)
- Em có bảng định mức thành phẩm được sản xuất bằng nguyên vật (NVL) liệu gì, trong bảng này có các thông tin định mức NVL là bao nhiêu, thời gian bắt đầu sử dụng NVL này, 1 TP có thể sản xuất bằng 1 hoặc nhiều hơn 1 mã NVL cùng lúc, ngày bắt đầu sử dụng đại diện cho NVL mới nhất đang sử dụng , các ngày bắt đầu sử dụng cũ hơn thì cho biết là NVL đó không sử dụng cho TP này nữa, nếu có 2 NVL cũng sử dụng sẽ có cùng ngày bắt đầu sử dụng.
=> bây giờ em muốn tổng kết số TP nhập sản xuất trong tháng theo từng NVL, ví dụ NVL028 sản xuất ra những thành phẩm nào, số lượng là bao nhiêu
- Em đã thử dùng sumifs và sumproduct nhưng kết quả chưa được hoàn thiện, em phải đánh công thức và chia trường hợp cho từng mã NVL rất lâu, em bị một số khó khắn này:
+ trong tháng mà thành phẩm đổi qua nhiều hơn 2 NVL trở lên thì phải chia khoảng điều kiện thời gian ( VD: TP0156)
+ nếu thành phẩm sản xuất cũng lúc bằng hoặc nhiều hơn 2 NVL ( VD: TP1450)
Em xin đính kèm file excel mẫu, anh chị nào đi qua có cách giải quyết thì cho em xin giải đáp !
Em cảm ơn trước ạ!

Nhấp chuột vào đây để mở rộng...

quocgiacan đã viết:

Gợi ý cho bạn một hướng theo dõi tiêu hao NVL.
Nếu Định mức phải quản theo thời gian áp dụng, thì chỉ cần thêm điều kiện trong LOOKUP()

Bạn xem file kèm.

Chúc bạn ngày vui.

Nhấp chuột vào đây để mở rộng...

nguyentrieuphuc đã viết:

Cảm ơn anh đã cho em một lời giải đáp, nhưng mà anh có thể xem thêm cho em mọt chút về điều kiện thời gian không
Vì ý của em là khi 1 thành phẩm có mã NVL có ngày áp dụng mới thì mã NVL với ngày áp dụng cũ sẽ ngừng sử dụng cho TP đó,
cụ thể ví dụ như TP0156 sản xuất bằng NVL028 từ 01/01/2017 đến ngày 18/6/2017 thì sản xuất bằng NVL302 và không sử dụng NVL028 nữa
hay TP1450 sản xuất bằng NVL028 từ 01/01/2017 đến ngày 18/6/2017 thì sản xuất bằng 2 mã NVL là NVL302 và NVL303

Bảng của anh đúng là trình bày rất dễ nhìn, em thử tính bằng tay thì sẽ ra kết quả như sau, anh thử xem đính kèm giúp em nhé, anh xem giúp em có cách nào lọc điều kiện theo mảng thời gian hiệu quả ko ạ?

Thanh kiu anh!

Nhấp chuột vào đây để mở rộng...

p/s: nếu có thể, thì ghi bài bằng tiếng Việt hết, hoặc toàn bộ bằng tiếng Anh đúng chuẩn

quocgiacan đã viết:

Cái khó nhất làm sao để máy phân biệt được Định mức áp dụng có cùng ngày như NVL302 và NVL303 ngày 18/06/2017, lấy cái gì để phân biệt được ngày 20/06/2017 thì mã TP1450 dùng cái nào trước cái nào sau?

Chúc bạn ngày vui.
p/s: nếu có thể, thì ghi bài bằng tiếng Việt hết, hoặc toàn bộ bằng tiếng Anh đúng chuẩn

Nhấp chuột vào đây để mở rộng...

nguyentrieuphuc đã viết:

Vậy em có thể hỏi anh một câu khác đi, ví dụ em có ô A1 là ngày 10/01/2017 và ô A2 là ngày 15/01/2017
thì em muốn lấy điều kiện (A1+1)=A2 , hoặc hiểu là có 1 ô excel chứa ngày tháng năm và muốn lấy điều kiện là ô excel cũng chứa ngày tháng năm và liền kề phía dưới hoặc bên trái phải thì làm như thế nào ạ?
Em cảm ơn!

Nhấp chuột vào đây để mở rộng...

E4=$D4*IFERROR(LOOKUP(2,1/($C4=DinhMuc!$A$4:$A$101)/(E$2=DinhMuc!$B$4:$B$101),DinhMuc!$E$4:$E$101)*(MATCH($B4,INDEX(DinhMuc!$C$4:$D$100,INDEX(MATCH($C4&E$2,DinhMuc!$A$4:$A$100&DinhMuc!$B$4:$B$100,0),),),1)=1),0)

quocgiacan đã viết:

Với câu hỏi này, tôi thấy bạn có chung ý tưởng với tôi đó!

Tôi đã làm thêm bên sheet 'Dinhmuc' cột 'ngày kết thúc' và làm sẳn công thức cho bạn luôn. Chính nhờ ngày này, nó sẽ phân biệt được mã NVL nào được đem vào SX từ ngày nào đến ngày nào.

Tuy nhiên với trường hợp đã nêu ở bài #4, bạn cần phân rõ ngày áp dụng của 2 NVL302 và 303, mặc dù ban hành ra cùng ngày 18/06, nhưng mã 303 đến ngày 26/06 áp dụng thì bạn điền (chỉnh lại cho phân biệt) ngày áp dụng là 26/06.

Chúc bạn ngày vui.

Nhấp chuột vào đây để mở rộng...

nguyentrieuphuc đã viết:

Cảm ơn anh đã đọc ý kiến của em, đúng là điều kiện thời gian là chìa khóa để giải quyết vấn đề này,
Em để NVL303 lúc thì ngày 18 lúc thì ngày 25 cũng nhằm để tạo ra trường hợp mở rộng bao quát hơn, vì thực tế em có nhiều mã NVL hơn, nhiều ngày áp dụng hơn, trước đây thì em vẫn theo dõi là nếu 1 mã TP mà thay đổi mã NVL thì sẽ đổi luôn mã TP tương ứng với NVL mới, nhưng do bây giờ có nhu cầu mới nên phải giải quyết bài toán này.
Ý của em ở điều kiện ngày có thể diễn đạt như này: if((A1+1)>A1,0,A1), vd:
NVL028 ngày áp dụng 01/01/2017: A1
NVL302 ngày áp dụng 20/01/2017: A2
NVL303 ngày áp dụng 20/01/2017: A3
=> nếu ngày A2>A1 thì NVL028 ngừng sử dụng, còn A2=0 hoặc =A1( cho trường hợp có từ 2 mã NVL cùng sản xuất ra 01 mã TP) thì vẫn sử dụng

** Thật ra em từ trước đến nay chỉ làm những công thức excel đơn giản , bây giờ mới bắt đầu tìm hiểu những công thức phức tạp hơn nên em xem công thức của anh nhưng vẫn chưa hiểu được lắm
Một lần nữa cảm ơn anh đã đọc ý kiến của em!

Nhấp chuột vào đây để mở rộng...

• Nó lấy mã TP và mã NVL (vd: TP1450NVL028) đem qua bảng 'Dinhmuc' để dò khớp dòng nào thì nó trả về cặp ngày: 'áp dụng ~ kết thúc' tương ứng (hàm Index() tô màu nâu ở trên), sau đó nó lấy ngày của dòng đang đứng mà xem thử có nằm trong khoảng đó không, nếu nằm trong khoảng nó trả về số 1, nếu không nằm trong khoảng trả về giá trị khác thì không cần phải lấy.
• Do vậy, việc xác định ngày áp dụng và ngày kết thúc của 1 mã NVL cho mã hàng nào đó chắc rằng bạn phải biết, thì việc cập nhật vào bảng không khó với bạn. Ngày kết thúc được xác định bằng công thức, hay nhập tay là tùy nơi bạn.
• Trường hợp hai mã NVL cùng xuất dùng cho 1 mã hàng, bạn cứ thử chỉnh lại bằng tay như: TP1450-NVL302->áp: 18/06 và kết: 23/06, NVL303->áp: 18/06 và kết: 23/08 thì bạn xem kết quả biết liền.
• Cách của bạn nghĩ nếu được thực hiện sẽ tốn rất nhiều công.

quocgiacan đã viết:

Công thức của tôi gửi dựa trên sự so khớp cặp ngày: 'áp dụng ~ kết thúc':
=(MATCH($B4, INDEX(DinhMuc!$C$4:$D$100,INDEX(MATCH($C4&E$2,DinhMuc!$A$4:$A$100&DinhMuc!$B$4:$B$100,0),),) ,1) =1)
Ý nghĩa:

• Nó lấy mã TP và mã NVL (vd: TP1450NVL028) đem qua bảng 'Dinhmuc' để dò khớp dòng nào thì nó trả về cặp ngày: 'áp dụng ~ kết thúc' tương ứng (hàm Index() tô màu nâu ở trên), sau đó nó lấy ngày của dòng đang đứng mà xem thử có nằm trong khoảng đó không, nếu nằm trong khoảng nó trả về số 1, nếu không nằm trong khoảng trả về giá trị khác thì không cần phải lấy.
• Do vậy, việc xác định ngày áp dụng và ngày kết thúc của 1 mã NVL cho mã hàng nào đó chắc rằng bạn phải biết, thì việc cập nhật vào bảng không khó với bạn. Ngày kết thúc được xác định bằng công thức, hay nhập tay là tùy nơi bạn.
• Trường hợp hai mã NVL cùng xuất dùng cho 1 mã hàng, bạn cứ thử chỉnh lại bằng tay như: TP1450-NVL302->áp: 18/06 và kết: 23/06, NVL303->áp: 18/06 và kết: 23/08 thì bạn xem kết quả biết liền.
• Cách của bạn nghĩ nếu được thực hiện sẽ tốn rất nhiều công.

Chúc bạn ngày vui.

Nhấp chuột vào đây để mở rộng...

nguyentrieuphuc đã viết:

Em cảm ơn anh, để em đọc và suy nghĩ thêm về công thức của anh, đây cũng là một hướng rất hay ạ
Cảm ơn anh đã dành thời gian giúp đỡ em!

Nhấp chuột vào đây để mở rộng...

nguyentrieuphuc đã viết:

Cảm ơn anh đã đọc ý kiến của em, đúng là điều kiện thời gian là chìa khóa để giải quyết vấn đề này,
Em để NVL303 lúc thì ngày 18 lúc thì ngày 25 cũng nhằm để tạo ra trường hợp mở rộng bao quát hơn, vì thực tế em có nhiều mã NVL hơn, nhiều ngày áp dụng hơn, trước đây thì em vẫn theo dõi là nếu 1 mã TP mà thay đổi mã NVL thì sẽ đổi luôn mã TP tương ứng với NVL mới, nhưng do bây giờ có nhu cầu mới nên phải giải quyết bài toán này.
Ý của em ở điều kiện ngày có thể diễn đạt như này: if((A1+1)>A1,0,A1), vd:
NVL028 ngày áp dụng 01/01/2017: A1
NVL302 ngày áp dụng 20/01/2017: A2
NVL303 ngày áp dụng 20/01/2017: A3
=> nếu ngày A2>A1 thì NVL028 ngừng sử dụng, còn A2=0 hoặc =A1( cho trường hợp có từ 2 mã NVL cùng sản xuất ra 01 mã TP) thì vẫn sử dụng

** Thật ra em từ trước đến nay chỉ làm những công thức excel đơn giản , bây giờ mới bắt đầu tìm hiểu những công thức phức tạp hơn nên em xem công thức của anh nhưng vẫn chưa hiểu được lắm
Một lần nữa cảm ơn anh đã đọc ý kiến của em!

Nhấp chuột vào đây để mở rộng...

Ba Tê đã viết:

"Hơi bị hiểu chút chút", tôi hiểu như vầy có đúng không nhé:
- 1 mã TP tùy thời điểm mà xài NVL này hay NVL khác hoặc nhiều NVL cùng lúc theo bảng Định mức.
- Bạn muốn tính tổng (SLượng x định mức) của từng NVL/ từng mã TP/ từng tháng ?
Thay đổi bảng định mức theo từng nhóm ngày, thứ tự từ nhỏ đến lớn của cột ngày.
Tôi làm thử với kiểu dữ liệu như trong file này, bạn thay đổi ô F1 sẽ có kết quả
Bạn kiểm tra kết quả bằng thủ công xem có đúng chưa. Nếu đúng thì ta bàn tiếp với dữ liệu giống thật (nhiều dòng, nhiều cột)

Nhấp chuột vào đây để mở rộng...

nguyentrieuphuc đã viết:

Em cảm ơn anh đã dành thời gian quan tâm đến thắc mắc của em, em có tính tay và kết quả tính ra nguyên vật liệu giống giá trị của anh đã tính
Có điều là từ đầu đề em đã trình bày là em muốn tính tổng số sản phẩm nhập sản xuất trong tháng theo từng mã NVL, còn các mục còn lại như điền định mức tiêu hao rồi nhân ra số lượng nguyên vật liệu tiêu hao thì cái đó chỉ là phụ thôi ạ
Như vậy đề bài của em là tính tổng TP nhập sản xuất, còn điều kiện là gom theo NVL, giữa NVL và TP có sự ràng buộc là ngày áp dụng, còn những chi tiết là định mức các thứ thì chúng ta gác sang một bên đã ạ

Nhấp chuột vào đây để mở rộng...

Ba Tê đã viết:

Vậy thì bạn xem lại file này, vẫn phải thay đổi bảng định mức lại như trong file này chứ không lấy bảng định mức của bạn được.
Nếu không đúng ý bạn thì là tôi chưa hiểu được yêu cầu.

Nhấp chuột vào đây để mở rộng...