Tính chi phí lưu kho dựa vào khung giá theo ngày lưu kho (1 người xem)

[MinhKhai]

Chào các anh chị

Em có bài toán tính phí lưu kho như file đính kèm. Lúc đầu tưởng làm được ngay, sau đó thì tắc hoàn toàn
Chi tiết em mô tả trong file, ở đây em mô tả sơ qua
Để tính CP lưu kho, em cần lấy SL x Đơn giá theo khung giá x số ngày trong khung giá đó
Vấn đề là nếu 1 ngày trong khung giá đó là ngày T7+CN+Lễ thì ngày trong khung giá đó được trượt lên 1 ngày.
Nhờ các anh chị em có cách tiếp cận khác để làm bài toán thành đơn giản ạ (ra số thành tiền lưu kho)
Xin cảm ơn

 

[Nguyễn Hồng Quang]

Cho hỏi thêm chút về điều kiện của bài toán
Ngày hàng xuất kho như trong ô D4 được hiểu là đến ngày đó hay là đến hết ngày đó
Ví dụ gửi kho 00h ngày 21/04 rút khỏi kho lúc 23h cùng ngày; D3= 21/04 ; D4 =21/04 được tính là 1 ngày hay 0 ngày

 

[Mộng Thường 2001]

Chào các anh chị

Em có bài toán tính phí lưu kho như file đính kèm. Lúc đầu tưởng làm được ngay, sau đó thì tắc hoàn toàn
Chi tiết em mô tả trong file, ở đây em mô tả sơ qua
Để tính CP lưu kho, em cần lấy SL x Đơn giá theo khung giá x số ngày trong khung giá đó
Vấn đề là nếu 1 ngày trong khung giá đó là ngày T7+CN+Lễ thì ngày trong khung giá đó được trượt lên 1 ngày.
Nhờ các anh chị em có cách tiếp cận khác để làm bài toán thành đơn giản ạ (ra số thành tiền lưu kho)
Xin cảm ơn

Thử:

=SUM(IFERROR((B4*LOOKUP(ROW(INDIRECT(C4&":"&D4)),WORKDAY.INTL(C4-1,{1,4,9},,$B$88:$B$90),E9:E11)*(COUNTIF(B19:B100,ROW(INDIRECT(C4&":"&D4)))=0)),))

Kết thúc bằng CTrl+Shift+Enter.
Xem file kèm.

hihi ^o^

 

[dazkangel]

Thử:

=SUM(IFERROR((B4*LOOKUP(ROW(INDIRECT(C4&":"&D4)),WORKDAY.INTL(C4-1,{1,4,9},,$B$88:$B$90),E9:E11)*(COUNTIF(B19:B100,ROW(INDIRECT(C4&":"&D4)))=0)),))

Kết thúc bằng CTrl+Shift+Enter.
Xem file kèm.

hihi ^o^

Em hơi kém tính thời gian ^^, Ctrl+Shift+Enter
Em theo anh hàm countif cho nó ngắn

=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&1+D4-C4-SUM(COUNTIF(B19:B94,ROW(INDIRECT(C4&":"&D4)))))),{1,4,9},1),)))*B4

Hoặc, nhìn công thức anh batman1 nên nghĩ tới hàm NETWORKDAYS, hi vọng đúng :

=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&NETWORKDAYS(C4,D4,B88:B90))),{1,4,9},1),)))*B4

 

[Mộng Thường 2001]

Em hơi kém tính thời gian ^^, Ctrl+Shift+Enter
Em theo anh hàm countif cho nó ngắn

=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&1+D4-C4-SUM(MMULT(N(ROW(INDIRECT(C4&":"&D4))=TRANSPOSE(B19:B94)),ROW(1:76)^0)))),{1,4,9},1),)))*B4

Hoặc:

=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&1+D4-C4-SUM(COUNTIF(B19:B94,ROW(INDIRECT(C4&":"&D4)))))),{1,4,9},1),)))*B4

Anh đang nghĩ dùng các hàm tính ngày để rút gọn khâu nhập liệu cho bạn @MinhKhai, không muốn dùng cột B19:B94 hơi bất tiện, và kéo dài dai dẳng từ năm này qua tháng nọ, không hay lắm. Nên:

=SUM(IFERROR((B4*LOOKUP(ROW(INDIRECT(C4&":"&D4)),WORKDAY.INTL(C4-1,{1,4,9},,$B$19:$B$21),E9:E11)*((WEEKDAY(ROW(INDIRECT(C4&":"&D4)),2)<6)*(1-COUNTIF(B19:B21,ROW(INDIRECT(C4&":"&D4)))))),))

Kết thúc bằng Ctrl+Shift+Enter.

Xem file kèm.
hihi ^o^

 

[dazkangel]

Anh đang nghĩ dùng các hàm tính ngày để rút gọn khâu nhập liệu cho bạn @MinhKhai, không muốn dùng cột B19:B94 hơi bất tiện, và kéo dài dai dẳng từ năm này qua tháng nọ, không hay lắm. Nên:

=SUM(IFERROR((B4*LOOKUP(ROW(INDIRECT(C4&":"&D4)),WORKDAY.INTL(C4-1,{1,4,9},,$B$19:$B$21),E9:E11)*((WEEKDAY(ROW(INDIRECT(C4&":"&D4)),2)<6)*(1-COUNTIF(B19:B21,ROW(INDIRECT(C4&":"&D4)))))),))

Kết thúc bằng Ctrl+Shift+Enter.

Xem file kèm.
hihi ^o^

Tại em kém về hàm thời gian quá anh ạ

 

[Mộng Thường 2001]

Tại em kém về hàm thời gian quá anh ạ

Em ngày càng tiến bộ vượt bậc!
Rất sáng tạo trong công thức Mảng, anh thấy vui lắm.
Anh thích tinh thần học hỏi cầu tiến của em.

Chúc em ngày vui.
hihi ^o^

 

[batman1]

Tôi không hiểu cách tính của chủ thớt lắm nhưng tôi nhập trong trường hợp tổng quát C4 = 2018-11-29, D4 = 2018-12-02 thì công thức bài #3 cho kết quả 520 000. Nhưng trong khoảng cho trước chỉ có 2 ngày trả phí vậy theo tôi kết quả phải là 200*2*650 = 260 000. Như vậy theo cách tính thì kết quả nào là đúng?
Hay tôi nhập C4 = 2018-05-09, D4 = 2018-12-02 thì công thức cho 43 134 000, mà theo tôi phải là 42 550 000
--------------
Tôi bất đắc dĩ mới dùng hàm mảng và các hàm volatile. Nếu dùng volatite thì khi nhập liệu ở những chỗ khác, không liên quan tới tham chiếu, thì công thức liên tục được tính lại. Nếu những công thức đó nhiều thì việc liên tục tính lại rất khó chịu.
Công thức bình thường

=B4*(NETWORKDAYS(C4;D4;$B$88:$B$90)*INDEX(E9:E11;MATCH(NETWORKDAYS(C4;D4;$B$88:$B$90);{0;3;8}))-CHOOSE(MATCH(NETWORKDAYS(C4;D4;$B$88:$B$90);{0;3;8});0;3*(E10-E9);3*(E11-E9)+5*(E11-E10)))

Hoặc đặt name songay

=NETWORKDAYS($C$4;$D$4;$B$88:$B$90)

Và công thức

=B4*(songay*INDEX(E9:E11;MATCH(songay;{0;3;8}))-CHOOSE(MATCH(songay;{0;3;8});0;3*(E10-E9);3*(E11-E9)+5*(E11-E10)))

Công thức tuy dài nhưng rất nhẹ, tính rất nhanh. Có thể kiểm tra bằng cách:
1. Cho C4:C1000 = 2018-04-02, D4: D1000 = 2018-04-16:2021-01-06
2. Nhập vào AA4 công thức mảng nhưng cố định B4, E9:E11 và B19:B100. Bây giờ copy công thức xuống tới AA1000. Phải sau nhiều nháy mắt Excel mới tính xong. Mỗi lần nhập gì đó vào vd. AB4 thì công thức lại được tính lại.
3. Xóa toàn bộ AA4:AA1000
4. Nhập vào AA4 công thức ở trên nhưng cố định B4, E9, E10, E11. Bây giờ copy công thức xuống tới AA1000. Sau một nháy mắt Excel đã tính xong. Nhập vào AB4 thoải mái, không có chuyện tính lại.
---------------
Hi vọng là nói nhiều nhưng không sai. Hi vọng là công thức chuẩn.

 

[HieuCD]

Anh đang nghĩ dùng các hàm tính ngày để rút gọn khâu nhập liệu cho bạn @MinhKhai, không muốn dùng cột B19:B94 hơi bất tiện, và kéo dài dai dẳng từ năm này qua tháng nọ, không hay lắm. Nên:

=SUM(IFERROR((B4*LOOKUP(ROW(INDIRECT(C4&":"&D4)),WORKDAY.INTL(C4-1,{1,4,9},,$B$19:$B$21),E9:E11)*((WEEKDAY(ROW(INDIRECT(C4&":"&D4)),2)<6)*(1-COUNTIF(B19:B21,ROW(INDIRECT(C4&":"&D4)))))),))

Kết thúc bằng Ctrl+Shift+Enter.

Xem file kèm.
hihi ^o^

Công thức tính tiền điện lũy kế đâu rồi

 

[Mộng Thường 2001]

Công thức tính tiền điện lũy kế đâu rồi

Đúng là "Thầy"! nên tường tận các bài toán. Tuy nói ít mà hiểu nhiều.
Cảm ơn anh @HieuCD!

=SUM((NETWORKDAYS(C4,D4,$B$19:$B$21)>{0,3,8})*(NETWORKDAYS(C4,D4,$B$19:$B$21)-{0,3,8})*{650,630,180})*B4

Chỉ Enter. Ngắn gọn không cầu kỳ dễ hiểu.
Công thức bậc thang lũy tiến của @eke_rula đúng là "độc cô cầu bại".
Khà khà khà.

Chủ thớt xem file kèm.
hihi ^o^

 

[HieuCD]

Đúng là "Thầy"! nên tường tận các bài toán. Tuy nói ít mà hiểu nhiều.
Cảm ơn anh @HieuCD!

=SUM((NETWORKDAYS(C4,D4,$B$19:$B$21)>{0,3,8})*(NETWORKDAYS(C4,D4,$B$19:$B$21)-{0,3,8})*{650,630,180})*B4

Chỉ Enter. Ngắn gọn không cầu kỳ dễ hiểu.
Công thức bậc thang lũy tiến của @eke_rula đúng là "độc cô cầu bại".
Khà khà khà.

Chủ thớt xem file kèm.
hihi ^o^

Công thức song kiếm hợp bích của hai bạn @eke_rula và QuocGiacan quá ấn tượng nên không quên được, nhìn dạng là nhớ ngay
Nhớ những ngày vui vẻ cùng bàn về công thức mảng

 

[Mộng Thường 2001]

Công thức song kiếm hợp bích của hai bạn @eke_rula và QuocGiacan quá ấn tượng nên không quên được, nhìn dạng là nhớ ngay
Nhớ những ngày vui vẻ cùng bàn về công thức mảng

Anh nói quá lời rồi!
Thành quả chung của anh em, trong đó có sự đóng góp không nhỏ của anh và còn em trai @befaint nữa, đâu dám nhận vơ về mình.
Cảm ơn anh @HieuCD, mãi mãi là một người "bạn hiền" của diễn đàn GPE.

Những ngày tháng kỹ niệm đó không bao giờ quên được.
Khà khà khà!

 

[Mộng Thường 2001]

Hoặc...:

=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&NETWORKDAYS(C4,D4,B88:B90))),{1,4,9},1),)))*B4

Thêm một công thức mới của "Hậu sinh khả úy" tại bài #4 ở trên, rất đáng khen ngợi!
Nhưng gặp một số người kỵ và e sợ dùng Mảng, thì khi gặp tính toán ví dụ như: từ 01/01/2018 đến 31/12/2018 thì công thức sẽ tạo 1 Mảng từ ROW(1:258), sẽ cho Mảng dài trong bộ nhớ khi tính toán, sẽ ảnh hưởng đến tốc độ máy.
Với anh thì công thức này cũng thuộc dạng tuyệt vời!
Chứng tỏ khả năng ứng biến nhanh nhạy về Mảng của em: @dazkangel

Công thức của em còn rút gọn thêm 2 ký tự nữa!
=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&NETWORKDAYS(C4,D4,B19:B21))),{1,4,9}),)))*B4

Chúc mừng em. hihi ^o^

 

[batman1]

Nếu thay cho dùng các hằng số mà dùng E9, E10, E11 thì công thức sẽ dài thêm chút.
Giá cả là cái hay thay đổi. Mỗi lần thay đổi thì sửa công thức?

Chẳng qua là mỗi người có một nguyên tắc khác mà thôi.

 

[Mộng Thường 2001]

Ở đời không có gì toàn vẹn mỹ mãn, được mặt này thì mất mặt khác.
Con người thì thích cầu toàn, há chẳng phải là phí sức hoài công lắm ru!?
Thôi thì! tùy cơ mà ứng biến: "Dĩ bất biến ứng vạn biến" vậy. Thêm chút công thức không là vấn đề quan trọng.

Quan trọng mục đích chính là: tìm giải pháp cho kết quả đúng.
Tùy nghi chủ thớt muốn chọn dùng công thức nào cho phù hợp: với ý thích của mình, và tình trạng xử lý file thực tế.

hihi ^o^

 

[batman1]

Bài này còn dễ lần nhưng có nhiều người hay dùng số thay cho tham chiếu. Cũng được nhưng lạm dụng quá không tốt. Ông nọ mà nghỉ việc và người khác tiếp quản code, công thức của ổng thì rất khó đọc, khó lần ra ổng lấy những con số đó ở đâu. Nói chung là rất mệt với những ổng có nguyên tắc kiểu đó. Cũng chính vì con số nó chả gợi ý gì cả, khó đọc, nên thay vì vd. ở chỗ nào đó dùng 1234 thì trong lập trình người ta hay dùng
Private Const TEN_DE_HIEU_GOI_Y_NGHIA_CUA_THAM_SO = 1234
rồi dùng TEN_DE_HIEU_GOI_Y_NGHIA_CUA_THAM_SO thay cho 1234

Nói chung mỗi người có nguyên tắc và phong cách của mình. Nhưng không phải là làm cho xong. Với phong cách như thế thì sau một thời gian chính "ổng" cũng có khó khăn, không biết mình bói những con số "câm nín" kia ở đâu ra. Còn về người tiếp quản những code, công thức kiểu đó thì mới nghĩ thôi đã thấy thương cho họ.

Mỗi ý kiến chỉ là ý kiến riêng thôi. Mỗi người tự chủ trong những quyết định của mình. Mỗi người tự chọn cho mình một nguyên tắc, phong cách.

 

[Mộng Thường 2001]

hihi ^o^
Thôi thì, "Dĩ bất biến ứng vạn biến", thương người thì thương cho trót, người không biết thì phải chỉ tới cùng vậy:

Công thức bài #4 thì không cần thêm bớt gì nữa, chủ thớt cứ vậy mà xài, không lo người sau vào nhận lại công thức hổng có hiểu:

=SUM(N(OFFSET(E8,MATCH(ROW(INDIRECT(1&":"&NETWORKDAYS(C4,D4,B19:B21))),{1,4,9}),)))*B4

Còn nếu có lo hảo sợ ảnh hưởng tốc độ máy thì xem tiếp công thức dưới đây:
Công thức bài #10, coi bộ hơi "khó"... chút nha, hihi ^o^
Hoặc:

=SUM((NETWORKDAYS(C4,D4,$B$19:$B$21)>{0;3;8})*(NETWORKDAYS(C4,D4,$B$19:$B$21)-{0;3;8})*(E9:E11-IFERROR(--E8:E10,)))*B4

Hoặc, cái này thì chắc người vô sau nhìn vào dễ hiểu hơn, hihi ^o^:

=SUM((NETWORKDAYS(C4,D4,$B$19:$B$21)>{0;3;8})*(NETWORKDAYS(C4,D4,$B$19:$B$21)-{0;3;8})*F9:F11)*B4

Cả ba công thức đều nhấn Ctrl+Shift+Enter.

Chủ thớt xem file kèm.
hihi ^o^

 

[ptm0412]

Tôi vừa xóa một số bài tranh luận. Đề nghị hai bên kềm chế.
Ý kiến riêng: Góp ý của anh batman1 ở bài 14 và 16 rất có giá trị, bạn mongthuong nên tiếp thu.

 

[MinhKhai]

Cảm ơn các anh,
Em đang mày mò tìm hiểu ý nghĩa của từng hàm mà các anh tạo ra. Hàm nào cũng "khủng" nên cần chút thời gian tìm hiểu ý nghĩa của nó. Trước mắt em xin cảm ơn mọi người đã giành thời gian gợi ý, giúp đỡ. Rất mong tiếp tục nhận được sự giúp đỡ

 

[linhpopo]

=IF(NETWORKDAYS(C4,D4,B88:B90)<=3,E9*NETWORKDAYS(C4,D4,B88:B90),IF(NETWORKDAYS(C4,D4,B88:B90)<=8,3*E9+(NETWORKDAYS(C4,D4,B88:B90)-3)*E10,3*E9+5*E10+(NETWORKDAYS(C4,D4,B88:B90)-8)*E11))*B4