[Nhờ giúp] Đếm ô chứa dữ liệu số thỏa mãn khoảng thời gian của vùng dữ liệu

[Blad01]

Các bạn giúp mình với ạ, mình muốn đếm số ô có chứa chữ số thỏa mãn điều kiện theo khoảng thời gian mong muốn. Mình đã thử với COUNTIFS và SUMPRODUCT mà không được. Mong các bạn giúp ạ.
Đề bài cụ thể có trong File đính kèm: Kết quả tại ô B1_TT01!D11 = tổng các Cell chứa dữ liệu số tại vùng B1_TT!N8:O16 sao cho thỏa mãn điều kiện trong khoảng thời gian từ ngày tại Cell B1_TT!D4 đến ngày tại Cell B1_TT!F4 (vùng dữ liệu thời gian là B1_TT!C8:C16).

 

[CHAOQUAY]

Các bạn giúp mình với ạ, mình muốn đếm số ô có chứa chữ số thỏa mãn điều kiện theo khoảng thời gian mong muốn. Mình đã thử với COUNTIFS và SUMPRODUCT mà không được. Mong các bạn giúp ạ.
Đề bài cụ thể có trong File đính kèm: Kết quả tại ô B1_TT01!D11 = tổng các Cell chứa dữ liệu số tại vùng B1_TT!N8:O16 sao cho thỏa mãn điều kiện trong khoảng thời gian từ ngày tại Cell B1_TT!D4 đến ngày tại Cell B1_TT!F4 (vùng dữ liệu thời gian là B1_TT!C8:C16).

Ví dụ Sumproduct() tại D11

=SUMPRODUCT(B1_TT!$N$8:$N$16,(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

 

[Blad01]

Ví dụ Sumproduct() tại D11

=SUMPRODUCT(B1_TT!$N$8:$N$16,(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

Công thức này là tính tổng rồi, em muốn chỉ đếm có bao nhiêu ô chứa số thôi ạ

 

[CHAOQUAY]

Công thức này là tính tổng rồi, em muốn chỉ đếm có bao nhiêu ô chứa số thôi ạ

Sửa lại như bên dưới

=SUMPRODUCT((B1_TT!$N$8:$N$16<>"")*(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

Có thể thay đổi điều kiện đếm tại biểu thức bên dưới trong công thức

(B1_TT!$N$8:$N$16<>"")

 

[dsafasfasf]

Bạn thử hàm này xem đúng ý chưa nhé :
=SUMPRODUCT((C8:C16>=D4)*(C8:C16<=F4)*(ISNUMBER(N8:N16))+(C8:C16>=D4)*(C8:C16<=F4)*(ISNUMBER(O8:O16)))

Giải thích trong công thức này:

• C8:C16>=D4 và C8:C16<=F4 là các điều kiện để kiểm tra xem ngày trong cột C có nằm trong khoảng từ ngày D4 đến F4 hay không.
• ISNUMBER(N8:N16) và ISNUMBER(O8:O16) kiểm tra xem các ô trong vùng N8:N16 và O8:O16 có phải là số hay không.
• SUMPRODUCT tính tổng số lần thỏa mãn cả hai điều kiện trên cho từng cột, từ đó cung cấp tổng số ô chứa số thỏa mãn cả về giá trị số và khoảng thời gian chỉ định.

 

[Blad01]

Sửa lại như bên dưới

=SUMPRODUCT((B1_TT!$N$8:$N$16<>"")*(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

Có thể thay đổi điều kiện đếm tại biểu thức bên dưới trong công thức

(B1_TT!$N$8:$N$16<>"")

Mình muốn cho thêm một điều kiện nữa nhưng tại sao hàm lại không hoạt động được nhỉ: =SUMPRODUCT((B1_TT!$N$8:$N$16<>"")*(B1_TT!$D$8:$D$16<>"")*(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

 

[Blad01]

Sửa lại như bên dưới

=SUMPRODUCT((B1_TT!$N$8:$N$16<>"")*(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

Có thể thay đổi điều kiện đếm tại biểu thức bên dưới trong công thức

(B1_TT!$N$8:$N$16<>"")[/code
[/QUOTE]

Với công thức như bạn, Mình muốn thêm điều kiện chỉ đếm cột có số tại B1_TT!$N$8:$N$16 khi B1_TT!$D$8:$H$16 có thông tin (là dạng Text) thì phải ghi công thức như nào ạ ?

 

[CHAOQUAY]

Với công thức như bạn, Mình muốn thêm điều kiện chỉ đếm cột có số tại B1_TT!$N$8:$N$16 khi B1_TT!$D$8:$H$16 có thông tin (là dạng Text) thì phải ghi công thức như nào ạ ?

Công thức hơi dài.
Điều kiện trong công thức tuần tự từ trái -> phải là theo như yêu cầu của bạn: N8:N16 = số; D8:H16 = text; ngày tháng nằm trong giới hạn

=SUMPRODUCT(AND(B1_TT!$N$8:$N$16<>"",ISNUMBER(B1_TT!$N$8:$N$16))*AND(B1_TT!$D$8:$H$16<>"",ISTEXT(B1_TT!$D$8:$H$16))*(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

Bài này để tiện kiểm tra có lẽ dùng cột phụ tốt hơn

 

[Phan Thế Hiệp]

Điều kiện trong công thức

Xem lại điều kiện: AND(B1_TT!$D$8:$H$16<>"",ISTEXT(B1_TT!$D$8:$H$16))

Vì phải xét theo từng dòng (5 ô) có thỏa hay không, còn điều kiện And() thì gom kết quả của cả mảng.

Thân

 

[Blad01]

Công thức hơi dài.
Điều kiện trong công thức tuần tự từ trái -> phải là theo như yêu cầu của bạn: N8:N16 = số; D8:H16 = text; ngày tháng nằm trong giới hạn

=SUMPRODUCT(AND(B1_TT!$N$8:$N$16<>"",ISNUMBER(B1_TT!$N$8:$N$16))*AND(B1_TT!$D$8:$H$16<>"",ISTEXT(B1_TT!$D$8:$H$16))*(B1_TT!$C$8:$C$16>=B1_TT!$D$4)*(B1_TT!$C$8:$C$16<=B1_TT!$F$4))

Bài này để tiện kiểm tra có lẽ dùng cột phụ tốt hơn

Mình làm như bạn hướng dẫn nhưng công thức không chạy. Mình gửi file lên mong bạn giúp đỡ.

 

[CHAOQUAY]

Mình làm như bạn hướng dẫn nhưng công thức không chạy. Mình gửi file lên mong bạn giúp đỡ.

Tạm tính từ dòng 11:19, các điều kiện theo nội dung trong file
Tại D11, công thức mảng kết thúc ctrl + shift + enter

=SUMPRODUCT(MMULT(TRANSPOSE((B1_TT!$AA$11:$AB$19>=1)*1),ISTEXT(B1_TT!$D$11:$D$19)*(B1_TT!$C$11:$C$19>=B1_TT!$D$3)*(B1_TT!$C$11:$C$19<=B1_TT!J3)))-SUM((B1_TT!$AC$11:$AC$19>=1)*1)

---
Trong công thức: điều kiện chức vụ tại B1_TT!D10:I30 được thay bằng điều kiện tên tại B1_TT!D10: D30. Cái này bạn kiểm tra lại xem có đáp ứng yêu cầu bài toán hay không rồi tính tiếp

Cột C trong sheet B1_TT có lẽ nên nhập là ngày tháng để tiện so sánh. Trong file đã sửa C11

 

[Blad01]

Tạm tính từ dòng 11:19, các điều kiện theo nội dung trong file
Tại D11, công thức mảng kết thúc ctrl + shift + enter

=SUMPRODUCT(MMULT(TRANSPOSE((B1_TT!$AA$11:$AB$19>=1)*1),ISTEXT(B1_TT!$D$11:$D$19)*(B1_TT!$C$11:$C$19>=B1_TT!$D$3)*(B1_TT!$C$11:$C$19<=B1_TT!J3)))-SUM((B1_TT!$AC$11:$AC$19>=1)*1)

---
Trong công thức: điều kiện chức vụ tại B1_TT!D10:I30 được thay bằng điều kiện tên tại B1_TT!D10: D30. Cái này bạn kiểm tra lại xem có đáp ứng yêu cầu bài toán hay không rồi tính tiếp

Cột C trong sheet B1_TT có lẽ nên nhập là ngày tháng để tiện so sánh. Trong file đã sửa C11

Cảm ơn bạn rất nhiều, công thức bạn hướng dẫn mình làm được rồi. Nhưng có vấn đề khi mình đặt điều kiện cho mảng có 5 cột thì nó không chạy (cột B1_TT! E11:E19). Mình đành phải làm riêng lẻ, nhưng công thức rất dài. Có cách nào em rút gọn được công thức này không ạ:

=SUMPRODUCT((B1_TT!$C$11:$C$20002>=B1_TT!$F$3)*(B1_TT!$C$11:$C$20002<=B1_TT!$J$3)*ISTEXT(B1_TT!$D$11:$D$20002)*ISTEXT(B1_TT!$E$11:$E$20002)*(B1_TT!AA11:AB20002<>""))+SUMPRODUCT((B1_TT!$C$11:$C$20002>=B1_TT!$F$3)*(B1_TT!$C$11:$C$20002<=B1_TT!$J$3)*ISTEXT(B1_TT!$D$11:$D$20002)*ISTEXT(B1_TT!$F$11:$F$20002)*(B1_TT!AA11:AB20002<>""))+SUMPRODUCT((B1_TT!$C$11:$C$20002>=B1_TT!$F$3)*(B1_TT!$C$11:$C$20002<=B1_TT!$J$3)*ISTEXT(B1_TT!$D$11:$D$20002)*ISTEXT(B1_TT!$G$11:$G$20002)*(B1_TT!AA11:AB20002<>""))++SUMPRODUCT((B1_TT!$C$11:$C$20002>=B1_TT!$F$3)*(B1_TT!$C$11:$C$20002<=B1_TT!$J$3)*ISTEXT(B1_TT!$D$11:$D$20002)*ISTEXT(B1_TT!$H$11:$H$20002)*(B1_TT!AA11:AB20002<>""))

 

[Phan Thế Hiệp]

B1_TT!$C$11:$C$20002

1/ Theo tôi, khi bạn dùng các hàm mảng như Sumproduct(), Mmult(), Transpose() thì bạn nên chọn số dòng dưới 1000 dòng, không nên chọn quá nhiều như số bạn định là 20002 dòng, vì máy sẽ chạy "ì ạch". Nếu chỉ tổng hợp ra kết quả chỉ mỗi 1 ô D11 thôi thì bạn có thể thử đến 5000 hoặc hơn chút. Làm thử bạn mới biết máy bạn có thể chạy ổn đến mức nào.

2/ Tôi có làm cho bạn công thức dưới đây và tóm tắt phân ra 3 điều kiện như bạn yêu cầu:

• ĐK.1: dữ liệu trong khoảng thời gian tại cell B1_TT!D3 và B1_TT!J3 (dữ liệu thời gian tại cột B1_TT!C10:C1000)
• ĐK.2: B1_TT!D10:I10 có chứa thông tin dạng Text
• ĐK.3: B1_TT!AA10:AB1000>=1 - (trừ) Số cell có chứa chữ số tại B1_TT!AC10:AC1000>=1

=SUMPRODUCT((B1_TT!$C$11:$C$100>=B1_TT!$D$3)*(B1_TT!$C$11:$C$100<=B1_TT!$J$3),(B1_TT!$D$11:$D$100<>"")*(MMULT(--(B1_TT!$E$11:$I$100<>""),{1;1;1;1;1})>0),MMULT(SIGN(B1_TT!$AA$11:$AC$100)*{1,1,-1},{1;1;1}))

Với:
ĐK.1: (B1_TT!$C$11:$C$100>=B1_TT!$D$3)*(B1_TT!$C$11:$C$100<=B1_TT!$J$3)

ĐK.2: (B1_TT!$D$11:$D$100<>"")*(MMULT(--(B1_TT!$E$11:$I$100<>""),{1;1;1;1;1})>0)

ĐK.3: MMULT(SIGN(B1_TT!$AA$11:$AC$100)*{1,1,-1},{1;1;1})

Xem dẫn giải công thức trong file kèm

Thân