đếm số lượng trong khoảng thời gian

[DIEU DAT]

chào các bác, e có 1 file excel trong cột B là thời gian chấm vân tay của nhân viên, giờ e muốn đếm số lượng của các bữa ăn sáng từ 6:00-8:45, ăn trưa từ 10:30-13:00, chiều từ 16:30-19:00, ăn tối từ 22:00-00:30, ăn đêm tử 2:00-5:00 thì dùng công thức như thế nào a. mong các bác giải đáp giúp e

 

[nguyenthuy13388]

chào các bác, e có 1 file excel trong cột B là thời gian chấm vân tay của nhân viên, giờ e muốn đếm số lượng của các bữa ăn sáng từ 6:00-8:45, ăn trưa từ 10:30-13:00, chiều từ 16:30-19:00, ăn tối từ 22:00-00:30, ăn đêm tử 2:00-5:00 thì dùng công thức như thế nào a. mong các bác giải đáp giúp e

Thử xem đúng chưa?

 

[DIEU DAT]

Thử xem đúng chưa?

e thấy kết quả nó không đúng bác ạ, ví dụ e dùng tay tính ăn chiều của e là 224 mà bác tính dc 537

 

[nguyenthuy13388]

e thấy kết quả nó không đúng bác ạ, ví dụ e dùng tay tính ăn chiều của e là 224 mà bác tính dc 537

Srr mình gửi nhầm file. xem kết quả như bảng dưới đúng không?

 

[Phan Thế Hiệp]

e thấy kết quả nó không đúng bác ạ, ví dụ e dùng tay tính ăn chiều của e là 224 mà bác tính dc 537

Mượn file của bạn @nguyenthuy13388, chỉnh lại một vài chỗ cho gọn hơn:

C2=MOD(B2,1)
H4=SUMPRODUCT(COUNTIFS($C$1:$C$1500,">="&IF(G4<F4,CHOOSE({1,2},F4,0),F4),$C$1:$C$1500,"<="&IF(G4<F4,{24,0.5}/24,G4)))

Enter, fill xuống.

Thân

 

[DIEU DAT]

Srr mình gửi nhầm file. xem kết quả như bảng dưới đúng không?

View attachment 208196

Bài đã được tự động gộp: 24/11/18

Mượn file của bạn @nguyenthuy13388, chỉnh lại một vài chỗ cho gọn hơn:

C2=MOD(B2,1)
H4=SUMPRODUCT(COUNTIFS($C$1:$C$1500,">="&IF(G4<F4,CHOOSE({1,2},F4,0),F4),$C$1:$C$1500,"<="&IF(G4<F4,{24,0.5}/24,G4)))

Enter, fill xuống.

Thân

vâng, e cảm ơn bác nhiều ak, bác giúp e giải thích công thức của bác dc không ạ

 

[Phan Thế Hiệp]

bác giúp e giải thích công thức của bác dc không ạ

H4=SUMPRODUCT(COUNTIFS($C$1:$C$1500,">="&IF(G4<F4,CHOOSE({1,2},F4,0),F4),$C$1:$C$1500,"<="&IF(G4<F4,{24,0.5}/24,G4)))

Có thể hiểu đại khái như vầy:

1. Nếu Giờ bắt đầu (cột Fn) < Giờ kết thúc (cột Gn) (Vd: 6:00 < 8:45 ; 10:00 < 13:00 ; 02:00 < 05:00) thì dùng COUNTIFS() bình thường:
   • =COUNTIFS($C$1:$C$1500, ">="&F4 ,$C$1:$C$1500, "<="&G4 )
   • Cho dù có thêm SUMPRODUCT( COUNTIFS() ) bao bên ngoài COUNTIFS(), thì cũng không ảnh hưởng gì đến kết quả sau cùng.
2. Ngược lại, Nếu Giờ bắt đầu (cột Fn) > Giờ kết thúc (cột Gn) (Vd: 22:00 > 0:30) thì dùng COUNTIFS() mảng:
   • =COUNTIFS($C$1:$C$1500, ">="&CHOOSE({1,2},F4,0) ,$C$1:$C$1500, "<="&{24,0.5}/24 )
   • => COUNTIFS($C$1:$C$1500, ">="&{'22:00','0:00'} ,$C$1:$C$1500, "<="&{'24:00','0:30'} )
   • Sẽ cho ra kết quả là 1 Mảng, với phần tử 1 là kết quả đếm được cho khoảng thời gian từ 22:00 -> 24:00 (Vd: 150); và phần tử 2 là kết quả đếm được cho khoảng thời gian từ 00:00 -> 00:30 (Vd: 11)
3. Với hai ý nghĩa như vậy nên tôi dùng IF() để lọc:
   • ">="&IF(G4<F4 , CHOOSE({1,2},F4,0) , F4)
   • "<="&IF(G4<F4, {24,0.5}/24 , G4)
4. Cuối cùng SUMPRODUCT( {150,11} ) = 161

Thân

 

[nguyenthuy13388]

H4=SUMPRODUCT(COUNTIFS($C$1:$C$1500,">="&IF(G4<F4,CHOOSE({1,2},F4,0),F4),$C$1:$C$1500,"<="&IF(G4<F4,{24,0.5}/24,G4)))

Có thể hiểu đại khái như vầy:

1. Nếu Giờ bắt đầu (cột Fn) < Giờ kết thúc (cột Gn) (Vd: 6:00 < 8:45 ; 10:00 < 13:00 ; 02:00 < 05:00) thì dùng COUNTIFS() bình thường:
   • =COUNTIFS($C$1:$C$1500, ">="&F4 ,$C$1:$C$1500, "<="&G4 )
   • Cho dù có thêm SUMPRODUCT( COUNTIFS() ) bao bên ngoài COUNTIFS(), thì cũng không ảnh hưởng gì đến kết quả sau cùng.
2. Ngược lại, Nếu Giờ bắt đầu (cột Fn) > Giờ kết thúc (cột Gn) (Vd: 22:00 > 0:30) thì dùng COUNTIFS() mảng:
   • =COUNTIFS($C$1:$C$1500, ">="&CHOOSE({1,2},F4,0) ,$C$1:$C$1500, "<="&{24,0.5}/24 )
   • => COUNTIFS($C$1:$C$1500, ">="&{'22:00','0:00'} ,$C$1:$C$1500, "<="&{'24:00','0:30'} )
   • Sẽ cho ra kết quả là 1 Mảng, với phần tử 1 là kết quả đếm được cho khoảng thời gian từ 22:00 -> 24:00 (Vd: 150); và phần tử 2 là kết quả đếm được cho khoảng thời gian từ 00:00 -> 00:30 (Vd: 11)
3. Với hai ý nghĩa như vậy nên tôi dùng IF() để lọc:
   • ">="&IF(G4<F4 , CHOOSE({1,2},F4,0) , F4)
   • "<="&IF(G4<F4, {24,0.5}/24 , G4)
4. Cuối cùng SUMPRODUCT( {150,11} ) = 161

Thân

Cảm ơn bác đã cho em được mở rộng tầm mắt

 

[DIEU DAT]

H4=SUMPRODUCT(COUNTIFS($C$1:$C$1500,">="&IF(G4<F4,CHOOSE({1,2},F4,0),F4),$C$1:$C$1500,"<="&IF(G4<F4,{24,0.5}/24,G4)))

Có thể hiểu đại khái như vầy:

1. Nếu Giờ bắt đầu (cột Fn) < Giờ kết thúc (cột Gn) (Vd: 6:00 < 8:45 ; 10:00 < 13:00 ; 02:00 < 05:00) thì dùng COUNTIFS() bình thường:
   • =COUNTIFS($C$1:$C$1500, ">="&F4 ,$C$1:$C$1500, "<="&G4 )
   • Cho dù có thêm SUMPRODUCT( COUNTIFS() ) bao bên ngoài COUNTIFS(), thì cũng không ảnh hưởng gì đến kết quả sau cùng.
2. Ngược lại, Nếu Giờ bắt đầu (cột Fn) > Giờ kết thúc (cột Gn) (Vd: 22:00 > 0:30) thì dùng COUNTIFS() mảng:
   • =COUNTIFS($C$1:$C$1500, ">="&CHOOSE({1,2},F4,0) ,$C$1:$C$1500, "<="&{24,0.5}/24 )
   • => COUNTIFS($C$1:$C$1500, ">="&{'22:00','0:00'} ,$C$1:$C$1500, "<="&{'24:00','0:30'} )
   • Sẽ cho ra kết quả là 1 Mảng, với phần tử 1 là kết quả đếm được cho khoảng thời gian từ 22:00 -> 24:00 (Vd: 150); và phần tử 2 là kết quả đếm được cho khoảng thời gian từ 00:00 -> 00:30 (Vd: 11)
3. Với hai ý nghĩa như vậy nên tôi dùng IF() để lọc:
   • ">="&IF(G4<F4 , CHOOSE({1,2},F4,0) , F4)
   • "<="&IF(G4<F4, {24,0.5}/24 , G4)
4. Cuối cùng SUMPRODUCT( {150,11} ) = 161

Thân

thanks bác