Mình cần trợ giúp Sumproduct liên quan đến điều kiện ngày tháng

[cuongeric]

Nhờ ACE tiên sinh giúp mình theo file đính kèm với, ở sheet Data OUT ô bôi vàng mình cần tính công thức vào đó thỏa mãn 3 điều kiện.
Tks ae

 

[Phan Thế Hiệp]

Nhờ ACE tiên sinh giúp mình theo file đính kèm với, ở sheet Data OUT ô bôi vàng mình cần tính công thức vào đó thỏa mãn 3 điều kiện.
Tks ae

Thử:

U8=COUNT(1/(MATCH(Thang!$G$2:$G$1000,IF((C8=Thang!$R$2:$R$1000)*($W$1=Thang!$I$2:$I$1000)*(MATCH(INT(Thang!$J$2:$J$1000),$W$2:$W$3+{0;1})=1),Thang!$G$2:$G$1000),)=ROW($1:$1000)))

Kết thúc bằng Ctrl+Shift+Enter, Fill xuống.

Thân

 

[vanthinh3101]

Nhờ ACE tiên sinh giúp mình theo file đính kèm với, ở sheet Data OUT ô bôi vàng mình cần tính công thức vào đó thỏa mãn 3 điều kiện.
Tks ae

Góp vui 1 chút:

U8=SUMPRODUCT(((Thang!$I$2:$I$215="TRUE_DISBDTL")*(Thang!F2:F215>=DATE(2019,3,1))*(Thang!$F$2:$F$215<=DATE(2019,3,7))*(Thang!$R$2:$R$215='Data OUT'!$C8))/COUNTIF(Thang!$G$2:$G$215,Thang!$G$2:$G$215))

Bài đã được tự động gộp: 10/3/19

Thử:

U8=COUNT(1/(MATCH(Thang!$G$2:$G$1000,IF((C8=Thang!$R$2:$R$1000)*($W$1=Thang!$I$2:$I$1000)*(MATCH(INT(Thang!$J$2:$J$1000),$W$2:$W$3+{0;1})=1),Thang!$G$2:$G$1000),)=ROW($1:$1000)))

Kết thúc bằng Ctrl+Shift+Enter, Fill xuống.

Thân

Em rất thích đoạn này anh ạ:

MATCH(INT(Thang!$J$2:$J$1000),$W$2:$W$3+{0;1})=1

 

[vanthinh3101]

Thử:

U8=COUNT(1/(MATCH(Thang!$G$2:$G$1000,IF((C8=Thang!$R$2:$R$1000)*($W$1=Thang!$I$2:$I$1000)*(MATCH(INT(Thang!$J$2:$J$1000),$W$2:$W$3+{0;1})=1),Thang!$G$2:$G$1000),)=ROW($1:$1000)))

Kết thúc bằng Ctrl+Shift+Enter, Fill xuống.

Thân

Em vừa phát hiện cái này anh ạ.
Công thức của anh bị nhầm cột điều kiện cột F với cột J ở sheet Thang
Nhưng quan trọng nhất là công thức của em sẽ bị sai trong trường hợp Customer_ID bị trùng và thời gian ở cột F không thỏa mãn điều kiện (ví dụ: dòng 27, em sửa cột F thành ngày 08/03/2019, cột G thành 044XXXXXX083 thì:
- Công thức của anh vẫn ra 3
- Công thức của em ra 2,5 --> bị sai

Ngoài ra, em còn dùng 1 công thức nữa thì lại thấy vẫn đúng là:

U8=SUM(IF((Thang!$I$2:$I$215="TRUE_DISBDTL")*(Thang!F2:F215>=DATE(2019,3,1))*(Thang!$F$2:$F$215<=DATE(2019,3,7))*(Thang!$R$2:$R$215='Data OUT'!$C8),1/COUNTIFS(Thang!$G$2:$G$215,Thang!$G$2:$G$215,Thang!$I$2:$I$215,"TRUE_DISBDTL",Thang!$F$2:$F$215,"<="&DATE(2019,3,7),Thang!$F$2:$F$215,">="&DATE(2019,3,1),Thang!$R$2:$R$215,'Data OUT'!$C8)))
Ctrl + Shift + Enter

Em chưa hiểu lắm trong trường hợp đếm loại trùng nhiều điệu kiện như thế này.
Anh có thể giải thích kỹ hơn về bài toán này không ạ?

 

[Phan Thế Hiệp]

Em vừa phát hiện cái này anh ạ.
Công thức của anh bị nhầm cột điều kiện cột F với cột J ở sheet Thang
Nhưng quan trọng nhất là công thức của em sẽ bị sai trong trường hợp Customer_ID bị trùng và thời gian ở cột F không thỏa mãn điều kiện (ví dụ: dòng 27, em sửa cột F thành ngày 08/03/2019, cột G thành 044XXXXXX083 thì:
- Công thức của anh vẫn ra 3
- Công thức của em ra 2,5 --> bị sai

Ngoài ra, em còn dùng 1 công thức nữa thì lại thấy vẫn đúng là:

U8=SUM(IF((Thang!$I$2:$I$215="TRUE_DISBDTL")*(Thang!F2:F215>=DATE(2019,3,1))*(Thang!$F$2:$F$215<=DATE(2019,3,7))*(Thang!$R$2:$R$215='Data OUT'!$C8),1/COUNTIFS(Thang!$G$2:$G$215,Thang!$G$2:$G$215,Thang!$I$2:$I$215,"TRUE_DISBDTL",Thang!$F$2:$F$215,"<="&DATE(2019,3,7),Thang!$F$2:$F$215,">="&DATE(2019,3,1),Thang!$R$2:$R$215,'Data OUT'!$C8)))
Ctrl + Shift + Enter

Em chưa hiểu lắm trong trường hợp đếm loại trùng nhiều điệu kiện như thế này.
Anh có thể giải thích kỹ hơn về bài toán này không ạ?

Cảm ơn em phát hiện chỗ anh lấy điều kiện chưa đúng cột (lẽ ra phải là cột F)!
Nhìn lại công thức mình có thể bỏ luôn INT() cũng được vì ngày tại W2 và W3 được lấy tại 00h00m và W3 luôn lớn hơn 1 ngày, nên công thức chỉ còn vầy:

U8=COUNT(1/(MATCH(Thang!$G$2:$G$1000,IF((C8=Thang!$R$2:$R$1000)*($W$1=Thang!$I$2:$I$1000)*(MATCH(Thang!$F$2:$F$1000,$W$2:$W$3+{0;1})=1),Thang!$G$2:$G$1000),)=ROW($1:$1000)))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống.

Công thức của em tại bài #4 với đoạn COUNTIFS() phía cuối cùng chính xác hơn COUNTIF() bài #3, vì COUNTIF() bài #3 sẽ đếm toàn bộ mà không phân biệt theo các điều kiện của yêu cầu bài toán, do đó khi dùng 1 chia (1/COUNTIF()) nó sẽ ra kết quả không chính xác. Ví dụ:

• COUNTIF(mã khách, mã khách)--> đếm luôn cả dù cùng 1 khách của các Vùng (Quảng Bình, Nghệ An 1-2-3,...), hoặc khác ngày-tháng, hoặc khác "True_Disbdtl"...., Giả sử ra 1 mảng: {2,3,5,3,4,2,5...}.
• Đoạn điều kiện theo yêu cầu giả sử ra: {0,1,0,0,1,1,0...}. Lấy hai mảng chia nhau rồi cộng lại sẽ ra kết quả lẻ.
• Vì vậy, để đếm đúng phải dùng COUNTIFS() như bài #4, tức phải có điều kiện thêm vào trong COUNTIFS().

Công thức của anh cách xử lý đơn giản hơn chút:

• Dùng công thức lõi: MATCH(Thang!$G$2:$G$1000,Thang!$G$2:$G$1000,)=ROW(1:1000) để tìm ra số vị trí xuất hiện của mã khách, và chỉ chọn những vị trí nào có thứ tự dòng khớp theo tuần tự của số thứ tự từ trên xuống, Ví dụ: sau khi Match() nó ra 1 mảng: {1,1,3,1,1,3,7,8,1,10,....} đem so với {1,2,3,4,5,6,7,8,9,10,....} thì kết quả sẽ là {TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,...}. Với việc so khớp tuần tự của số thứ tự, coi như nó loại những giá trị trùng phía sau, ví dụ: mặc dù có nhiều giá trị 1 trong mảng, nhưng chỉ có giá trị 1 đầu tiên so khớp với số thứ tự thì lấy, tương tự có hai giá trị 3, nhưng chỉ có số 3 khớp với stt tuần tự dòng 3 được lấy, giá trị sau bị loại. Sau đó dùng COUNT(1/Mảng KQ) sẽ ra.
• Để lọc theo điều kiện thì chỉ cần thêm hàm IF( Các điều kiện, Thang!$G$2:$G$1000 ) rồi đặt vào MATCH() ở trên là xong, vì lúc này "Vùng so khớp" của MATCH() đã được lược bớt bởi các điều kiện quy định, TRUE thì cho hiện ra giá trị "mã khách", FALSE thì báo FALSE, cho nên khi tìm vị trí của "Giá trị tìm": Thang!$G$2:$G$1000, nó sẽ lấy vị trí dòng của Mảng đã lọc theo điều kiện: IF( Các điều kiện, Thang!$G$2:$G$1000 ).
• Tóm gọn, dạng tổng quát của bài toán đếm theo nhiều điều kiện sẽ là:
  • =COUNT( 1/ ( MATCH( "Vùng Mã Khách" , IF( "Các điều kiện", "Vùng Mã Khách") ,) = ROW(1:1000) ) )

Em tham khảo thêm bài theo link này có ví dụ cụ thể: https://www.giaiphapexcel.com/diendan/threads/nhờ-tìm-lỗi-trong-công-thức-đếm-loại-bỏ-đếm-trùng.138038/#post-883771

Thân