Tìm tổng số lượng từng sản phẩm dựa trên 20 điều kiện (1 điều kiện lớn bao gồm nhiều điều kiện nhỏ) (1 người xem)

[Yunakun]

Xin chào các anh chị trên diễn đàn,

Hiện tại em đang gặp vấn đề về cách tính tổng các loại sản phẩm dựa trên các điều kiện yêu cầu, nếu tính tất tần tật các loại điều kiện khoảng trên 20 điều kiện.
Em đang tính dựa theo cách thủ công, là lọc từng điều kiện lớn và nhỏ rồi cộng từng số lại. Như vậy khá mất thời gian và do yêu cầu công việc, em phải tính như vậy hằng ngày. Dữ liệu thay đổi mỗi ngày, từ 5000- 7000 dòng. Nên em nhờ anh/chị trên diễn đàn có cách nào tối ưu hơn không ạ? Xin nhờ các anh chị hỗ trợ ạ.

QUY TẮC TÍNH TỔNG:

 

[Ba Tê]

Xin chào các anh chị trên diễn đàn,

Hiện tại em đang gặp vấn đề về cách tính tổng các loại sản phẩm dựa trên các điều kiện yêu cầu, nếu tính tất tần tật các loại điều kiện khoảng trên 20 điều kiện.
Em đang tính dựa theo cách thủ công, là lọc từng điều kiện lớn và nhỏ rồi cộng từng số lại. Như vậy khá mất thời gian và do yêu cầu công việc, em phải tính như vậy hằng ngày. Dữ liệu thay đổi mỗi ngày, từ 5000- 7000 dòng. Nên em nhờ anh/chị trên diễn đàn có cách nào tối ưu hơn không ạ? Xin nhờ các anh chị hỗ trợ ạ.

QUY TẮC TÍNH TỔNG:

View attachment 215396

Bạn sửa lại tiêu đề, không viết HOA cả câu nhìn "chói mắt" lắm.

 

[Yunakun]

Bạn sửa lại tiêu đề, không viết HOA cả câu nhìn "chói mắt" lắm.

Dạ em sửa lại tiêu đề rồi, cám ơn anh đã góp ý.

 

[be_09]

Xin chào các anh chị trên diễn đàn,

Hiện tại em đang gặp vấn đề về cách tính tổng các loại sản phẩm dựa trên các điều kiện yêu cầu, nếu tính tất tần tật các loại điều kiện khoảng trên 20 điều kiện.
Em đang tính dựa theo cách thủ công, là lọc từng điều kiện lớn và nhỏ rồi cộng từng số lại. Như vậy khá mất thời gian và do yêu cầu công việc, em phải tính như vậy hằng ngày. Dữ liệu thay đổi mỗi ngày, từ 5000- 7000 dòng. Nên em nhờ anh/chị trên diễn đàn có cách nào tối ưu hơn không ạ? Xin nhờ các anh chị hỗ trợ ạ.

QUY TẮC TÍNH TỔNG:

View attachment 215396

Góp ý cho bạn:
1/ Sheet DATA nên cho ít dữ liệu.
2/ Nên có 1 sheet kết quả thì mọi người mới hiểu được nội dung bạn diễn giải.

 

[Yunakun]

Góp ý cho bạn:
1/ Sheet DATA nên cho ít dữ liệu.
2/ Nên có 1 sheet kết quả thì mọi người mới hiểu được nội dung bạn diễn giải.

Dạ em đã sửa file theo góp ý của anh. Nhờ mọi người hỗ trợ giúp em ạ.

 

[dazkangel]

Bạn giải thích quá khó hiểu, tôi chỉ hiểu mỗi cái 4bin,all còn lại bạn thử thêm điều kiện vào xem, riêng cái All không giống của bạn:

=SUMPRODUCT(SUMIFS(DATA!$R$3:$R$2208,DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$L$3:$L$2208,CHOOSE(MATCH($C5,{"4BIN","N","G","S","K","All"},0),"*"&{6,7,"A","B"},"DK2","DK3","DK4","DK5","<>="),DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$K$3:$K$2208,$E5))

 

[Yunakun]

Bạn giải thích quá khó hiểu, tôi chỉ hiểu mỗi cái 4bin,all còn lại bạn thử thêm điều kiện vào xem, riêng cái All không giống của bạn:

=SUMPRODUCT(SUMIFS(DATA!$R$3:$R$2208,DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$L$3:$L$2208,CHOOSE(MATCH($C5,{"4BIN","N","G","S","K","All"},0),"*"&{6,7,"A","B"},"DK2","DK3","DK4","DK5","<>="),DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$K$3:$K$2208,$E5))

Cảm ơn bạn, thật tế toàn bộ bảng điều kiện chỉ có điều kiện CIE là phức tạp nhất. Mình cũng đang kẹt để tìm công thức thích hợp cho phần này theo yêu cầu của từng điều kiện. Trong một cột CIE sẽ xuất hiện các giá trị sau: 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, G, H.

1. 4BIN: có nghĩa là sau khi lọc giá trị loại sản phẩm, vd là mã 4000K_561C, bạn phải lọc tiếp bên cột CIE các giá trị 6, 7, A, B. Rồi sau đó tính tổng số lượng bên cột số lượng.
2. N: Cũng tương tự khi tìm kiếm các giá trị 6, 7, A, B. Nhưng sẽ đi theo cặp: 6 đi với B, 7 đi với A. Số lần xuất hiện của 6 phải bằng số lần của B, cặp còn lại cũng vậy. Nếu khi lọc ra có 8 giá trị 6, 10 giá trị B thì bạn chỉ lấy 8 giá trị 6 và 8 giá trị B = 16 lần xuất hiện. Tiếp theo tính tổng bên cột số lượng.
3. G: Tương tự như N, nhưng không tìm các giá trị 6, 7, A, B. Tìm theo cặp 1 với G, 2 với F, 3 với E, 4 với D, 5 với C, 8 với 9.
4. All: lấy tất cả.

Còn lại kiểu S và K thì mình sẽ giải thích sau. Hoặc có thể bỏ ra khỏi điều kiện để mình tính tay cũng được.

 

[dazkangel]

Cảm ơn bạn, thật tế toàn bộ bảng điều kiện chỉ có điều kiện CIE là phức tạp nhất. Mình cũng đang kẹt để tìm công thức thích hợp cho phần này theo yêu cầu của từng điều kiện. Trong một cột CIE sẽ xuất hiện các giá trị sau: 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, G, H.

1. 4BIN: có nghĩa là sau khi lọc giá trị loại sản phẩm, vd là mã 4000K_561C, bạn phải lọc tiếp bên cột CIE các giá trị 6, 7, A, B. Rồi sau đó tính tổng số lượng bên cột số lượng.
2. N: Cũng tương tự khi tìm kiếm các giá trị 6, 7, A, B. Nhưng sẽ đi theo cặp: 6 đi với B, 7 đi với A. Số lần xuất hiện của 6 phải bằng số lần của B, cặp còn lại cũng vậy. Nếu khi lọc ra có 8 giá trị 6, 10 giá trị B thì bạn chỉ lấy 8 giá trị 6 và 8 giá trị B = 16 lần xuất hiện. Tiếp theo tính tổng bên cột số lượng.
3. G: Tương tự như N, nhưng không tìm các giá trị 6, 7, A, B. Tìm theo cặp 1 với G, 2 với F, 3 với E, 4 với D, 5 với C, 8 với 9.
4. All: lấy tất cả.

Còn lại kiểu S và K thì mình sẽ giải thích sau. Hoặc có thể bỏ ra khỏi điều kiện để mình tính tay cũng được.

Mới làm thử thêm cột N sao thấy khác kết quả của bạn, so sánh bên cột kết quả, kết quả 3 và cột Data đã lọc tô màu.
Bạn nghiên cứu thêm G thử xem thêm 12 cột phụ:

 

[Leojr28]

Mong mọi người giúp đỡ. Mình có file như ở dưới, tại sao một cái cho kết quả đúng mà một cái lại trả về giá trị 0.

 

[Yunakun]

Mới làm thử thêm cột N sao thấy khác kết quả của bạn, so sánh bên cột kết quả, kết quả 3 và cột Data đã lọc tô màu.
Bạn nghiên cứu thêm G thử xem thêm 12 cột phụ:

SP-010 LM561B+_RA80_5700K S4 N 45,000
Kết quả của thằng này phải là 45,000 chứ nhỉ ?

Kết qủa của bạn đúng rồi nha, do mình tính sai.

 

[Yunakun]

Công thức của bạn IFERORR mình chưa hiểu phần bạn chia /1.

Với lại nếu data mình ít với công thức mảng thì không sao. Thật tế dữ liệu của mình hiện tại khoảng 6000-8000 rows, nên không biết có cách nào tối ưu hơn không.

Cảm ơn bạn đã chia sẻ công thức, mình sẽ thử áp dụng nó với kiểu G, kiểu S, và kiểu K.

 

[dazkangel]

Công thức của bạn IFERORR mình chưa hiểu phần bạn chia /1.

Với lại nếu data mình ít với công thức mảng thì không sao. Thật tế dữ liệu của mình hiện tại khoảng 6000-8000 rows, nên không biết có cách nào tối ưu hơn không.

Cảm ơn bạn đã chia sẻ công thức, mình sẽ thử áp dụng nó với kiểu G, kiểu S, và kiểu K.

Đây là file có dữ liệu với kiểu G, 1/ vì không muốn lấy số 0 nha bạn, ví dụ 1/0 ra lỗi 1/(1/0) thì ra 1/(Div) = Div còn 1/(1/2) ra 1/(0.5) = 1
Chỉ còn cách bạn chạy công thức để lại 1 cái đầu tiên còn lại bạn copy paste thành value, hoặc nhờ ai viết VBA.
Đã cập nhật thêm điều kiện REEL:

 

[dazkangel]

Thêm công thức mới cho điều kiện S (Ưu tiên 1) mấy cái ưu tiên kia chưa giải thích nên chịu, nhấn Ctrl+Shift+Enter.

=SUM(COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,IF($C5="All","<>=",IF($C5="4BIN","?"&{6,"B",7,"A"}))),IF(COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,"?"&{6,7,1,2,3,4,5,8,"B","C",7,6})<MMULT({1,1,1},COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,"?"&{"B","A","G","F","E","D","C",9,1,3,9,"C";"","","","","","","","",2,4,"D","F";"","","","","","","","",5,8,"E","G"})),COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,"?"&{6,7,1,2,3,4,5,8,"B","C",7,6}),MMULT({1,1,1},COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,"?"&{"B","A","G","F","E","D","C",9,1,3,9,"C";"","","","","","","","",2,4,"D","F";"","","","","","","","",5,8,"E","G"})))*({"N","N","G","G","G","G","G","G","S","S","S","S"}=$C5)*2)*$F5

Công thức cho tất cả, không biết đúng không, siêu nặng:

=SUM(IF(IF(AND($C5<>{"4BIN","All"}),-1,1)*MMULT({1,1,1,1},COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,{"<>=","?6","?7","?A","?B","?6","?7","?1","?2","?3","?4","?5","?8","?B","?C","?7","?6","?6","?7","?A","?B","?6","?C","?6","?9";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?7","?F","?7","?D";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?A","?G","?A","?E";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?B",0,"?B",0}))>IF(AND($C5<>{"4BIN","All"}),-1,1)*MMULT({1,1,1,1},COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,{0,0,0,0,0,"?B","?A","?G","?F","?E","?D","?C","?9","?1","?3","?9","?C",0,0,0,0,"?6","?1",0,"?3";0,0,0,0,0,0,0,0,0,0,0,0,0,"?2","?4","?D","?F",0,0,0,0,"?7","?2",0,"?4";0,0,0,0,0,0,0,0,0,0,0,0,0,"?5","?8","?E","?G",0,0,0,0,"?A","?5",0,"?8";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?B",0,0,0})),MMULT({1,1,1,1},COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,{"<>=","?6","?7","?A","?B","?6","?7","?1","?2","?3","?4","?5","?8","?B","?C","?7","?6","?6","?7","?A","?B","?6","?C","?6","?9";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?7","?F","?7","?D";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?A","?G","?A","?E";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?B",0,"?B",0})),MMULT({1,1,1,1},COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","<>=",$D5),DATA!$L$3:$L$2208,{0,0,0,0,0,"?B","?A","?G","?F","?E","?D","?C","?9","?1","?3","?9","?C",0,0,0,0,"?6","?1",0,"?3";0,0,0,0,0,0,0,0,0,0,0,0,0,"?2","?4","?D","?F",0,0,0,0,"?7","?2",0,"?4";0,0,0,0,0,0,0,0,0,0,0,0,0,"?5","?8","?E","?G",0,0,0,0,"?A","?5",0,"?8";0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"?B",0,0,0})))*({"ALL","4BIN","4BIN","4BIN","4BIN","N","N","G","G","G","G","G","G","S","S","S","S","S","S","S","S","S","K","K","K"}=$C5)*{1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,1,1,1,1,2,2,1,2}*$F5)

 

[Phan Thế Hiệp]

Thêm công thức mới cho điều kiện S (Ưu tiên 1) mấy cái ưu tiên kia chưa giải thích nên chịu, nhấn Ctrl+Shift+Enter.

Thời gian gần đây anh thấy: em càng được thêm nhiều tính kiên trì, nhẫn nại và điềm tĩnh.

Chúc mừng em!

 

[dazkangel]

Thời gian gần đây anh thấy: em càng được thêm nhiều tính kiên trì, nhẫn nại và điềm tĩnh.

Chúc mừng em!

Haha bài nào hứng thì làm thôi anh.
@HieuCD @Phan Thế Hiệp 2 anh làm thử cho em học với, chứ công thức vừa dài vừa chậm em hơi thất vọng

 

[Phan Thế Hiệp]

Haha bài nào hứng thì làm thôi anh.
@HieuCD @Phan Thế Hiệp 2 anh làm thử cho em học với, chứ công thức vừa dài vừa chậm em hơi thất vọng

Bửa trước nhào đầu vô xử mấy bài tính toán giờ giấc, anh cũng hơi hơi dội, nay nên để thư thả chút mới dám dòm các bài có điều kiện lằng nhằng như thớt này.

Cứ mạnh dạn xử đi em, nhiều khi để thư thả em tự nhìn lại sẽ nảy ra ý có nhiều chỗ có thể làm gọn được.

Chúc em ngày vui

 

[dazkangel]

Công thức đến G, chắc nhẹ hơn 1 chút:

=SUM(RIGHT(AGGREGATE(15-($C5="4BIN")*{0;1},6,COUNTIFS(DATA!$B$3:$B$2208,G$4,DATA!$E$3:$E$2208,$B5,DATA!$K$3:$K$2208,$E5,DATA!$R$3:$R$2208,$F5,DATA!$M$3:$M$2208,IF($D5="A0","*",$D5),DATA!$L$3:$L$2208,IF($C5="All","*","?"&IF(OR($C5={"4BIN","N"}),{6,7;"B","A"},IF($C5="G",{1,2,3,4,5,8;"G","F","E","D","C",9}))))+IF($C5="N",{1,2},IF($C5="G",{1,2,3,4,5,6}))*10000,IF($C5="4BIN",{1,2},IF($C5="N",{1,3},IF($C5="G",{1,3,5,7,9,11},1)))),4)*$F5)/(1+($C5="All"))