Hàm tính thời gian thao tác (1 người xem)

[Gau_Bo]

Chào các bạn,
- Mình đang xây dựng công thức tính thời gian thao tác của nhân viên, trong quá trình làm việc xảy ra các yêu cầu như sau:

• Nghỉ giải lao 10ph từ 10h đến 10h10
• Nghỉ trưa 60ph từ 12h đến 13h
• Nghỉ giải lao 10ph từ 15h đến 15h10

- Vì thời gian thao tác của nhân viên khi nhập vào bị ngắt quãng liên tục không cố định nên mình cần xây dựng 1 công thức bao quát.



Lúc trước chỉ có nghỉ trưa từ 12h đến 13h nên công thức mình cũng bao quát sơ sơ được, giờ ra thêm cái giờ giải lao nên đang rối ạ.
Công thức lúc trước:
=(IF(AND(HOUR(A3)<12;HOUR(B3)>=13);B3-A3-"01:00";IF(AND(HOUR(A3)=12;HOUR(B3)=13);B3-A3-"00:15";B3-A3)))*1440
Mong mọi người cho ý kiến
Xin cám ơn!

 

[Hoàng Tuấn 868]

Mong mọi người cho ý kiến

Dùng máy chấm vân tay thôi bạn. Sau đó từ bảng dữ liệu của máy muốn tính gì chẳng được, chi tiết đến từng giây chắc là đủ.

 

[Gau_Bo]

Dùng máy chấm vân tay thôi bạn. Sau đó từ bảng dữ liệu của máy muốn tính gì chẳng được, chi tiết đến từng giây chắc là đủ.

Ý mình là dùng 1 công thức bao quát hết tất cả các trường hợp đó bác.
- Ví dụ:
7:00 đến 9:00 là 120 phút
9:00 đến 11:00 chỉ có 110 phút do trừ mất 10ph nghỉ giải lao
Vì đây là thời gian nhân viên thao tác theo từng công đoạn, không phải thời gian chấm công lúc vào buổi sáng và về buổi chiều nên nó có rất nhiều trường hợp.

 

[Hoàng Tuấn 868]

Ý mình

Nên có file dữ liệu chứ nghe chay thế này chắc khó hình dung.

 

[Gau_Bo]

Nên có file dữ liệu chứ nghe chay thế này chắc khó hình dung.

Gửi bác file nhé
Hiện tại mình tính chỉ trừ giờ nghỉ trưa 60phút, ai có làm trưa từ 12h đến 13h thì tính 45ph do có 15ph ăn cơm trưa.
Giờ ra thêm qui định giải lao 10ph lúc 10h và 15h nên không biết dùng công thức ra sao để bao quát các trường hợp

 

[Hoàng Tuấn 868]

Gửi bác file nhé
Hiện tại mình tính chỉ trừ giờ nghỉ trưa 60phút, ai có làm trưa từ 12h đến 13h thì tính 45ph do có 15ph ăn cơm trưa.
Giờ ra thêm qui định giải lao 10ph lúc 10h và 15h nên không biết dùng công thức ra sao để bao quát các trường hợp

Bạn nên đưa kết quả mong muốn và mô tả cụ thể cách tính thủ công xem sao.

 

[Gau_Bo]

Bạn nên đưa kết quả mong muốn và mô tả cụ thể cách tính thủ công xem sao.

Mình ví dụ:
- Cũ: làm từ 9h đến 11h là 120ph
- Mới: làm từ 9h đến 11h là 110ph do nghỉ giải lao từ 10h đến 10h10
Có 2 lần nghỉ giao lao trong ngày:
- 10h đến 10h10
- 15h đến 15h10
- Nghỉ trưa từ 12h đến 13h, ai có tăng ca buổi trưa thì nhập vào cột E là 12h và cột F là 13h thì công thức tự trừ ra 15ph

 

[Hoàng Tuấn 868]

Mình ví dụ:
- Cũ: làm từ 9h đến 11h là 120ph
- Mới: làm từ 9h đến 11h là 110ph do nghỉ giải lao từ 10h đến 10h10
Có 2 lần nghỉ giao lao trong ngày:
- 10h đến 10h10
- 15h đến 15h10
- Nghỉ trưa từ 12h đến 13h, ai có tăng ca buổi trưa thì nhập vào cột E là 12h và cột F là 13h thì công thức tự trừ ra 15ph

Bạn nên đưa kết quả mong muốn vào File và mô tả cụ thể cách tính thủ công xem sao.

 

[Gau_Bo]

Bạn nên đưa kết quả mong muốn vào File và mô tả cụ thể cách tính thủ công xem sao.

Gửi bác file mình có mô tả nhé.

 

[Hoàng Tuấn 868]

Gửi bác file mình có mô tả nhé.

Trong file chưa thấy ví dụ trường hợp tăng ca nhỉ.

 

[Phan Thế Hiệp]

Chào các bạn,
- Mình đang xây dựng công thức tính thời gian thao tác của nhân viên, trong quá trình làm việc xảy ra các yêu cầu như sau:

• Nghỉ giải lao 10ph từ 10h đến 10h10
• Nghỉ trưa 60ph từ 12h đến 13h
• Nghỉ giải lao 10ph từ 15h đến 15h10

- Vì thời gian thao tác của nhân viên khi nhập vào bị ngắt quãng liên tục không cố định nên mình cần xây dựng 1 công thức bao quát.

View attachment 284470

Lúc trước chỉ có nghỉ trưa từ 12h đến 13h nên công thức mình cũng bao quát sơ sơ được, giờ ra thêm cái giờ giải lao nên đang rối ạ.
Công thức lúc trước:
=(IF(AND(HOUR(A3)<12;HOUR(B3)>=13);B3-A3-"01:00";IF(AND(HOUR(A3)=12;HOUR(B3)=13);B3-A3-"00:15";B3-A3)))*1440
Mong mọi người cho ý kiến
Xin cám ơn!

Tôi làm cho bạn chỉ tính trong khoảng thời gian bắt đầu từ 7:00, cho đến 19:00, ngoài khoảng đó tôi không tính đến.

Tạo thêm các mốc giờ tại: L1: L9 (xem file kèm).

Công thức như sau:

I2=SUMPRODUCT(TEXT((E2:F2-$L$2:$L$8)*1440,"[<0]\0;[<"&{180;0;110;0;120;0;230}&"];\"&{"18\0";0;"11\0";0;"12\0";0;"23\0"})*{-1,1})

Enter, fill xuống.

Thân

 

[cantl]

Tôi làm cho bạn chỉ tính trong khoảng thời gian bắt đầu từ 7:00, cho đến 19:00, ngoài khoảng đó tôi không tính đến.

Tạo thêm các mốc giờ tại: L1: L9 (xem file kèm).

Công thức như sau:

I2=SUMPRODUCT(TEXT((E2:F2-$L$2:$L$8)*1440,"[<0]\0;[<"&{180;0;110;0;120;0;230}&"];\"&{"18\0";0;"11\0";0;"12\0";0;"23\0"})*{-1,1})

Enter, fill xuống.

Thân

Em cũng thích thời gian nên đang nghịch bài này mà không ra. Hàm bác chuẩn quá, nhưng em xem không hiểu cách hoạt động luôn. Hãi thật, tà đạo thật, rất phi lý thuyết.

 

[Hoàng Tuấn 868]

Hàm bác chuẩn quá, nhưng em xem không hiểu cách hoạt động luôn.

Cũng thử mãi chưa được, đang xem từng phần công thức của anh Hiệp hoạt động thế nào nhưng chưa nắm được, để hiểu được chắc còn lâu lắm.

 

[cantl]

Cũng thử mãi chưa được, đang xem từng phần công thức của anh Hiệp hoạt động thế nào nhưng chưa nắm được, chắc còn lâu.

Nhọ thật. Mấy cái ngoặc vuông, ngoặc nhọn, suỵt thế kia chưa có thầy cô nào dạy cả.
Google vẫn thiếu chức năng translate hàm sang tiếng Việt.

 

[Gau_Bo]

Tôi làm cho bạn chỉ tính trong khoảng thời gian bắt đầu từ 7:00, cho đến 19:00, ngoài khoảng đó tôi không tính đến.

Tạo thêm các mốc giờ tại: L1: L9 (xem file kèm).

Công thức như sau:

I2=SUMPRODUCT(TEXT((E2:F2-$L$2:$L$8)*1440,"[<0]\0;[<"&{180;0;110;0;120;0;230}&"];\"&{"18\0";0;"11\0";0;"12\0";0;"23\0"})*{-1,1})

Enter, fill xuống.

Thân

Cám ơn bác nhiều.
Em xin nói rõ thêm cho bác hiểu như sau:
- 1 ngày làm việc có thể bắt đầu từ 6h sáng đến 10h tối.
- Có 3 lần nghỉ giải lao trong ngày:
+ 10h đến 10h10
+ Nghỉ trưa từ 12h đến 13h
+ 15h đến 15h10
- Thời gian nhập vào nếu chứa 1 trong các mốc trên thì sẽ bị trừ ra.
- Nếu người đó làm 1 việc từ 6h sáng đến 6h tối thì tổng phải trừ là 10ph buổi sáng + 60ph trưa + 10ph chiều = 80ph
- Mốc thời gian nhập vào có thể là bất kỳ vì rất nhiều nhân viên, mốc thời gian nhập vào không giống nhau nên mình cần 1 công thức bao quát các trường hợp.
Cám ơn bác Hiệp đã bỏ thời gian giúp đỡ, nếu được nhờ bác hoàn thiện lại công thức giúp mình.
Mình xin số zalo để tiện liên hệ và mời bác 1 ly cafe cám ơn nhé.
Mình gửi lại file, trong file có dòng bôi đỏ bị sai.

 

[cantl]

.

 

[Haem]

Em cũng thích thời gian nên đang nghịch bài này mà không ra. Hàm bác chuẩn quá, nhưng em xem không hiểu cách hoạt động luôn. Hãi thật, tà đạo thật, rất phi lý thuyết.

Có hàm nào tính thời gian+-12 tiếng của bài này không bạn ơi

 

[cantl]

Có hàm nào tính thời gian+-12 tiếng của bài này không bạn ơi

có, nhưng không nên chen ngang gây lạc đề, đăng bài mới đi.

 

[tranchautrungduong]

Gợi ý cho bạn nè,

Bạn sẽ có các mốc thời gian để trừ như sau:

-10, -70, -80, -60

để trừ 10 thì A <= 10h và B >= 10h10 và B <= 12h, or A <= 15h và B >= 15h10 và B >= 13h

để trừ 60 thì A <12h và > 13h và > 10h10 và < 15h

để trừ 70 thì A <=10h và B >=13h và B < 15h or A < 12h và A > 10h10 và B >=13h và B < 15h

để trừ 80 thì A < 10h và B > 15h10

 

[Hoàng Tuấn 868]

Gợi ý cho bạn

Vấn đề là chuyển thành công thức cụ thể chứ mốc để trừ thì có rồi mà.

 

[tranchautrungduong]

Vấn đề là chuyển thành công thức cụ thể chứ mốc để trừ thì có rồi mà.

thì suy nghĩ mà viết, if or and thôi

 

[Hoàng Tuấn 868]

thì suy nghĩ mà viết, if or and thôi

Nếu bạn chủ bài viết được thì đã không phải nhờ.

 

[cantl]

thì suy nghĩ mà viết, if or and thôi

Nói thật mình thì chưa viết ra nổi, vấn đề cần giải quyết thì quá đơn giản, nếu lập chục cái cột phụ thì ra ngay. Quan trọng là hàm ngắn gọn mà chuẩn chỉnh luôn mới bá đạo.

 

[tranchautrungduong]

Chào các bạn,
- Mình đang xây dựng công thức tính thời gian thao tác của nhân viên, trong quá trình làm việc xảy ra các yêu cầu như sau:

• Nghỉ giải lao 10ph từ 10h đến 10h10
• Nghỉ trưa 60ph từ 12h đến 13h
• Nghỉ giải lao 10ph từ 15h đến 15h10

- Vì thời gian thao tác của nhân viên khi nhập vào bị ngắt quãng liên tục không cố định nên mình cần xây dựng 1 công thức bao quát.

View attachment 284470

Lúc trước chỉ có nghỉ trưa từ 12h đến 13h nên công thức mình cũng bao quát sơ sơ được, giờ ra thêm cái giờ giải lao nên đang rối ạ.
Công thức lúc trước:
=(IF(AND(HOUR(A3)<12;HOUR(B3)>=13);B3-A3-"01:00";IF(AND(HOUR(A3)=12;HOUR(B3)=13);B3-A3-"00:15";B3-A3)))*1440
Mong mọi người cho ý kiến
Xin cám ơn!

Bạn kiểm tra cột G nhé!


Công thức như sau:

=IF(AND($E2<$M$3,$F2>$M$7),($F2-$E2)*1440-80,
IF(AND($E2>=$M$5,$E2<=$M$3,$F2>=$M$6,$F2<=$M$4),($F2-$E2)*1440-60,
IF(OR(AND($E2<=$M$2,$F2>=$M$5,$F2<=$M$3),AND($E2<=$M$4,$F2>=$M$7,$F2>=$M$6)),($F2-$E2)*1440-10,
IF(OR(AND($E2<=$M$2,$F2>=$M$6,$F2<=$M$4),AND($E2>=$M$5,$F2>=$M$4,$E2<=$M$3,$F2>=$M$7)),($F2-$E2)*1440-70,(F2-E2)*1440))))

 

[Hoàng Tuấn 868]

Có vẻ nhiều dòng không khớp.

 

[tranchautrungduong]

Có vẻ nhiều dòng không khớp.

nào k khớp cứ check nhé, cái thủ công của bạn ấy tính sai đấy!

 

[cantl]

nào k khớp cứ check nhé, cái thủ công của bạn ấy tính sai đấy!

Nếu đúng thì chỉ có 15p thôi. Chắc phải thêm đoạn nào nữa mới được.
Đang xem bóng mà phải gác sang tét hàm.

 

[tranchautrungduong]

Nếu đúng thì chỉ có 15p thôi. Chắc phải thêm đoạn nào nữa mới được.
Đang xem bóng mà phải gác sang tét hàm.

quỷ thần ơi, 14h45 đến 15h là 15p rồi, ở đây 15h05p mà k chịu ra 20p. tại nó đâu làm qua 15h10 đâu mà dc nghỉ 10p nên 5p đo tính luôn k dc bỏ

 

[cantl]

quỷ thần ơi, 14h45 đến 15h là 15p rồi, ở đây 15h05p mà k chịu ra 20p

Chắc mình hiểu sai đề hả?

 

[tranchautrungduong]

Nếu đúng thì chỉ có 15p thôi. Chắc phải thêm đoạn nào nữa mới được.
Đang xem bóng mà phải gác sang tét hàm.

Chắc mình hiểu sai đề hả?

k sai đề. nhưng nó15h10 mới dc tính nghỉ 10p. nó chưa dc 15h10 sao dc tính nghri

 

[cantl]

k sai đề. nhưng nó15h10 mới dc tính nghỉ 10p. nó chưa dc 15h10 sao dc tính nghri

Chắc mình hiểu theo ý khác. Tức là làm việc chăm quá, lố 5p nghỉ, lúc tính lương chỉ được tính 15p, còn của bạn thì làm vào giờ nghỉ sẽ được tính thêm 5p. Do cách hiểu khác nhau.

 

[tranchautrungduong]

Chắc mình hiểu theo ý khác. Tức là làm việc chăm quá, lố 5p nghỉ, lúc tính lương chỉ được tính 15p, còn của bạn thì làm vào giờ nghỉ sẽ được tính thêm 5p. Do cách hiểu khác nhau.

vậy nếu ng ta làm

Chắc mình hiểu theo ý khác. Tức là làm việc chăm quá, lố 5p nghỉ, lúc tính lương chỉ được tính 15p, còn của bạn thì làm vào giờ nghỉ sẽ được tính thêm 5p. Do cách hiểu khác nhau.

tức là có làm qua mốc nghỉ thì mới trừ nghỉ, còn nếu k làm qua mốc thì tính luôn phần đã làm

VD: làm từ 11h đến 12h 55 k lẻ tính 60p à, 55p của ng ta đâu? khi nào làm đến 13h05 thì mới tính 65p

 

[cantl]

vậy nếu ng ta làm

tức là có làm qua mốc nghỉ thì mới trừ nghỉ, còn nếu k làm qua mốc thì tính luôn phần đã làm

VD: làm từ 11h đến 12h 55 k lẻ tính 60p à, 55p của ng ta đâu? khi nào làm đến 13h05 thì mới tính 65p

Theo mình thì 55p đấy phải tách riêng, đưa vào hệ số làm thêm khác hoặc trừ đi vì không khuyến khích làm vào giờ nghỉ.
Nói chung mình vẫn không vừa ý với các kết quả này.

 

[tranchautrungduong]

Theo mình thì 55p đấy phải tách riêng, đưa vào hệ số làm thêm khác hoặc trừ đi vì không khuyến khích làm vào giờ nghỉ.
Nói chung mình vẫn không vừa ý với các kết quả này.

nếu k khuyến khích sẽ k có thực tết giờ đó đâu. tạm đủ đáp ứng dc cho chủ topic

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

Theo mình thì 55p đấy phải tách riêng, đưa vào hệ số làm thêm khác hoặc trừ đi vì không khuyến khích làm vào giờ nghỉ.
Nói chung mình vẫn không vừa ý với các kết quả này.

cái bạn đang đưa ra là tìm lỗi của hàm

chứ thật sự thực tế ai là làm vào mốc đó làm gì, nếu có làm thì chấm thêm cho ng ta có sao đâu, thêm mỗỗi 5p rồi về hẳn chứ có làm nữa đâu, nếu còn làm thêm nữa thì đã trừ ra

 

[cantl]

nếu k khuyến khích sẽ k có thực tết giờ đó đâu. tạm đủ đáp ứng dc cho chủ topic

Chắc phù hợp với tác giả rồi. Xem bóng đê.

 

[tranchautrungduong]

Chắc phù hợp với tác giả rồi. Xem bóng đê.

anh 7 về nước rồi

 

[cantl]

VD: làm từ 11h đến 12h 55 k lẻ tính 60p à, 55p của ng ta đâu? khi nào làm đến 13h05 thì mới tính 65p

Với mấy cái ảnh mình chụp thì hàm bác Hiệp là quá chuẩn đấy. Dùng hàm if là phải bao quát rất nhiều khả năng xảy ra, không để ý tí là thiếu ngay, mà hàm dài khi sửa cũng khó lắm, chứ nếu sửa dễ thì bác Bill không khống chế số lượng hàm if lồng nhau làm gì (ý chủ quan).

 

[Phan Thế Hiệp]

Cám ơn bác nhiều.
Em xin nói rõ thêm cho bác hiểu như sau:
- 1 ngày làm việc có thể bắt đầu từ 6h sáng đến 10h tối.
- Có 3 lần nghỉ giải lao trong ngày:
+ 10h đến 10h10
+ Nghỉ trưa từ 12h đến 13h
+ 15h đến 15h10
- Thời gian nhập vào nếu chứa 1 trong các mốc trên thì sẽ bị trừ ra.
- Nếu người đó làm 1 việc từ 6h sáng đến 6h tối thì tổng phải trừ là 10ph buổi sáng + 60ph trưa + 10ph chiều = 80ph
- Mốc thời gian nhập vào có thể là bất kỳ vì rất nhiều nhân viên, mốc thời gian nhập vào không giống nhau nên mình cần 1 công thức bao quát các trường hợp.
Cám ơn bác Hiệp đã bỏ thời gian giúp đỡ, nếu được nhờ bác hoàn thiện lại công thức giúp mình.
Mình xin số zalo để tiện liên hệ và mời bác 1 ly cafe cám ơn nhé.
Mình gửi lại file, trong file có dòng bôi đỏ bị sai.

Mốc giờ khác nhau thì kết quả sẽ ra khác nhau. Do vậy tôi mới bảo bạn là tôi chỉ tính trong khung giờ từ 7:00->19:00. Nếu khung giờ của bạn tính trong khoảng từ 6:00->20:00, thì chỉnh lại L2= 6:00, và công thức như sau:

I2=SUMPRODUCT(TEXT((E2:F2-$L$2:$L$8)*1440,"[<0]\0;[<"&{240;0;110;0;120;0;290}&"];\"&{"24\0";0;"11\0";0;"12\0";0;"29\0"})*{-1,1})

Enter, fill xuống.

Thân

Gửi anh em: @Hoàng Tuấn 868, @cantailieu, @anh em khác đang tham khảo ...

Công thức này anh học từ 1 người bạn trên diễn đàn, tư duy người bạn này thật đáng nể phục, anh thật sự không bằng, và học được rất nhiều kiến thức hay từ người bạn đó.

Lúc nào rảnh thời gian chút, sẽ giải thích sơ công thức trên cho anh em tham khảo. Vậy nhen! Không khó lắm đâu.

Thân

 

[Hoàng Tuấn 868]

Mốc giờ khác nhau thì kết quả sẽ ra khác nhau. Do vậy tôi mới bảo bạn là tôi chỉ tính trong khung giờ từ 7:00->19:00. Nếu khung giờ của bạn tính trong khoảng từ 6:00->20:00, thì chỉnh lại L2= 6:00, và công thức như sau:

I2=SUMPRODUCT(TEXT((E2:F2-$L$2:$L$8)*1440,"[<0]\0;[<"&{240;0;110;0;120;0;290}&"];\"&{"24\0";0;"11\0";0;"12\0";0;"29\0"})*{-1,1})

Enter, fill xuống.

Thân

Gửi anh em: @Hoàng Tuấn 868, @cantailieu, @anh em khác đang tham khảo ...

Công thức này anh học từ 1 người bạn trên diễn đàn, tư duy người bạn này thật đáng nể phục, anh thật sự không bằng, và học được rất nhiều kiến thức hay từ người bạn đó.

Lúc nào rảnh thời gian chút, sẽ giải thích sơ công thức trên cho anh em tham khảo. Vậy nhen! Không khó lắm đâu.

Thân

Dạ, chân thành cảm ơn anh nhiều ạ.

 

[Nhattanktnn]

Mốc giờ khác nhau thì kết quả sẽ ra khác nhau. Do vậy tôi mới bảo bạn là tôi chỉ tính trong khung giờ từ 7:00->19:00. Nếu khung giờ của bạn tính trong khoảng từ 6:00->20:00, thì chỉnh lại L2= 6:00, và công thức như sau:

I2=SUMPRODUCT(TEXT((E2:F2-$L$2:$L$8)*1440,"[<0]\0;[<"&{240;0;110;0;120;0;290}&"];\"&{"24\0";0;"11\0";0;"12\0";0;"29\0"})*{-1,1})

Enter, fill xuống.

Thân

Gửi anh em: @Hoàng Tuấn 868, @cantailieu, @anh em khác đang tham khảo ...

Công thức này anh học từ 1 người bạn trên diễn đàn, tư duy người bạn này thật đáng nể phục, anh thật sự không bằng, và học được rất nhiều kiến thức hay từ người bạn đó.

Lúc nào rảnh thời gian chút, sẽ giải thích sơ công thức trên cho anh em tham khảo. Vậy nhen! Không khó lắm đâu.

Thân

Bác biến kiến thức người khác thành kiến thức của mình quá là tuyệt vời rồi. Còn bác 1.5 thì quá đáng nể hihi. Em vẫn biết bài đó còn có khúc trừ thứ 7 chủ nhật ra nữa cơ

 

[Gau_Bo]

k sai đề. nhưng nó15h10 mới dc tính nghỉ 10p. nó chưa dc 15h10 sao dc tính nghri

Đề thì không sai, nếu làm tới 15h5 thì nghỉ dc có 5ph do nhân viên ko chịu nghỉ thôi bác
Chỗ 15h5 mình sửa tay lại thành 15h10 hoặc 15h đều được vì chấm công là ko có trừ ra.
Cái này lập ra để tính thời gian thao tác của từng công đoạn cho chính xác để lên kế hoạch.
Nếu vậy thì ok rồi vì nhân viên ko nhập chi tiết tới mức đó đâu
Cám ơn các bác nhiều
Thân!

 

[Phan Thế Hiệp]

Bác biến kiến thức người khác thành kiến thức của mình quá là tuyệt vời rồi. Còn bác 1.5 thì quá đáng nể hihi. Em vẫn biết bài đó còn có khúc trừ thứ 7 chủ nhật ra nữa cơ

• Tư duy không bằng @leoheocon = @Ekerula = @excel_lv1.5
• Kiến thức quản lý dữ liệu, anh không bằng bác @SA_DQ
• Thông hiểu sâu căn cơ, và lý luận tận nguồn và quảng bác, anh không bằng anh @VetMini và 1 anh nữa
• Thông thạo cả VBA và các hàm, vận dụng công thức mảng đúng nơi đúng chỗ, cùng tìm ra các quy luật số, anh không bằng anh @HieuCD, anh @NDU, anh @huuthang_bd, anh @Phuocam
• Tính cẩn thận và tinh tế trong các kết quả, và ứng biến thích nghi với Google Sheet anh không bằng @befaint
• "Ham học, chọc khuấy" không bằng mấy em.
• Còn nhiều cái không bằng nữa v.v.

Chỉ mỗi một tính cách mà anh biết mình có là thích phân tích vấn đề, rồi tổng hợp để ghi nhớ những gì mình cho là có ích cho bản thân. Nên chả có gì đáng gọi là tuyệt vời cả em. Anh em mình "Đồng hội đồng thuyền".

Cũng thử mãi chưa được, đang xem từng phần công thức của anh Hiệp hoạt động thế nào nhưng chưa nắm được, để hiểu được chắc còn lâu lắm.

Như lời hứa, anh ghi giải thích dưới đây với hy vọng không gây nhàm chán với mấy em:
Có hai cách tính về đoạn thời gian:
Ví dụ: như bài cho đoạn đầu tiên theo mốc quy định là bắt đầu (BĐ) 7:00, Kết thúc (KT) 10:00

1/ Cách trực tiếp: Số giờ hoạt động (GHĐ) = (KT) trừ ((BĐ)
Cách này là cách được dùng thường xuyên vì dễ thấy ngay kết quả: 10:00 - 7:00 = 3 tiếng
Trường hợp nếu Giờ bắt đầu thực tế (BĐtt) hoặc/và Giờ kết thúc thực tế (KTtt) khác (tức <>) giờ (BĐ) (KT) quy định, thường ta dùng hàm MEDIAN() để tính số trung vị.

Ví dụ 1: Giờ (BĐtt) lúc 8h, (KTtt) lúc 10h

• (GHĐ) = MEDIAN( BĐ, KT, KTtt) - MEDIAN( BĐ, KT, BĐtt)
• (GHĐ) = MEDIAN( 7, 10, 10) - MEDIAN( 7, 10, 8)
• (GHĐ) = 10 - 8 = 2

Ví dụ 2: Giờ (BĐtt) lúc 7h30, (KTtt) lúc 9h

• (GHĐ) = MEDIAN( BĐ, KT, KTtt) - MEDIAN( BĐ, KT, BĐtt)
• (GHĐ) = MEDIAN( 7, 10, 9) - MEDIAN( 7, 10, 7.5 )
• (GHĐ) = 9 - 7.5 = 1.5

Với hai mốc cố định trong hàm MEDIAN() là (BĐ): 7h và (KT): 10h, nên nếu giờ BĐtt và KTtt khác với mốc giờ quy định:
- Nếu giờ BĐtt < BĐ thì lấy BĐ
- Nếu giờ BĐtt > BĐ thì lấy BĐtt
- Nếu giờ KTtt < KT thì lấy KTtt
- Nếu giờ KTtt > KT thì lấy KT

Cách này dễ sử dụng và công thức gọn gàng với trường hợp 1 đoạn thời gian, nhưng nếu phát sinh những đoạn thời gian nhỏ loại bỏ không cần tính đến, như bài trên đây, thì phải dùng công thức MEDIAN() nối dài cho từng đoạn vì phải xác định mốc cố định cho từng đoạn, hoặc dùng rất nhiều điều kiện để lọc, nên khi chỉnh sửa, thay đổi hoặc thêm bớt mốc quy định sẽ gặp không ít khó khăn.

2/ Cách gián tiếp: Số giờ hoạt động (GHĐ) = Lọc điều kiện [ (KTtt) - (BĐ) ] - Lọc điều kiện [ (BĐtt) - (BĐ) ]

Ví dụ 1: Giờ (BĐtt) lúc 8h, (KTtt) lúc 10h

• (GHĐ) = Lọc ĐK[ (KTtt) - (BĐ) ] - Lọc ĐK[ (BĐtt) - (BĐ) ]
• (GHĐ) = [ 10 - 7 ] - [ 8 - 7 ]
• (GHĐ) = 3 - 1 = 2

Ví dụ 2: Giờ (BĐtt) lúc 7h30, (KTtt) lúc 9h

• (GHĐ) = Lọc ĐK[ (KTtt) - (BĐ) ] - Lọc ĐK[ (BĐtt) - (BĐ) ]
• (GHĐ) = [ 9 - 7 ] - [ 7.5 - 7 ]
• (GHĐ) = 2 - 0.5 = 1.5

Cách này chỉ lấy giờ BĐ làm mốc để so cho cả hai giờ BĐtt và KTtt, cùng với "Điều kiện lọc" cho cả hai vế [ (KTtt) - (BĐ) ] và [ (BĐtt) - (BĐ) ] , như sau:

- Nếu kết quả là số âm: tức KTtt<BĐ hoặc BĐtt<BĐ, thì sẽ ĐIỀU CHỈNH bằng giá trị 0.​

- Nếu kết quả nhỏ hơn Lượng thời gian quy định (tức = (KT) - (BĐ)), và lớn hơn hoặc bằng 0: thì giữ nguyên giá trị của kết quả.​

- Nếu kết quả lớn hơn Lượng thời gian quy định (tức = (KT) - (BĐ)): thì sẽ ĐIỀU CHỈNH thành giá trị của "Lượng thời gian quy định".​

Thực ra, nó đã ẩn 1 bài toán là Lượng thời gian quy định = (KT) - (BĐ) trong điều kiện lọc bằng con số đã tính toán trước, như ví dụ trên ta có điều kiện:

1/ x<0: chỉnh thành 0​

2/ 0<= x < (10-7=3): giữ nguyên giá trị​

3/ x> 3: chỉnh thành 3​

VD1: cho BĐtt: 4h và KTtt 6h30, khi trừ cho (BĐ): 7h đều cho số âm, nên theo điều kiện lọc 1 nó sẽ bị chỉnh thành 0,

=> (GHĐ) = 0 - 0 = 0​

​

VD2: cho BĐtt: 4h và KTtt 9h30, nên => (GHĐ) = [9.5 - 7] - [4 - 7] = [2.5] - [-3] = 2.5 - 0 = 2.5

* [9.5 - 7] = 2.5, theo điều kiện lọc 2 ở trên => 2.5 < 3, nên nó giữ nguyên giá trị 2.5​

VD3: cho BĐtt: 9h và KTtt 13h, nên => (GHĐ) = [13 - 7] - [9 - 7] = [6] - 2

* [13 - 7] = 6, theo điều kiện lọc 3 ở trên=> 6 > 3, nên nó bị chỉnh thành giá trị 3​

* [9 - 7] = 2, theo điều kiện lọc 2 ở trên=> 2 < 3, nên nó giữ nguyên giá trị 2​

VD4: cho BĐtt: 4h và KTtt 13h, nên => (GHĐ) = [13 - 7] - [4 - 7] = [6] - [-3] = 3 - 0 = 3

VD5: cho BĐtt: 11h và KTtt 13h, nên => (GHĐ) = [13 - 7] - [11 - 7] = [6] - [4] = 3 - 3 = 0

Để ý ta thấy:
1/ Với điều kiện 1: chỉnh thành số 0, ta loại bỏ tất cả các giá trị dù là giờ BĐtt hay KTtt mà < giờ BĐ quy định, tức không cần quan tâm.
2/ Với điều kiện 2: giữ nguyên giá trị kết quả, tức giờ BĐtt hay KTtt nằm trong mốc quy định.
3/ Với điều kiện 3: chỉnh thành Lượng thời gian quy định (3h), ta loại bỏ tất cả các giá trị dù là giờ BĐtt hay KTtt mà > giờ KT quy định, tức quy về mốc chuẩn: (KT)-(BĐ).

Cái hay của cách này là tìm "Quy tắc tính toán chung" cho các đoạn thời gian nhỏ khác nhau cần tính, và không cần quan tâm đến các thời đoạn không cần tính đến (như thời gian nghỉ giải lao, thời gian ăn cơm trưa, cơm chiều v.v..). Tức chỉ cần xác định các Mốc đầu (BĐ) của các đoạn thời gian, và với 3 điều kiện lọc có định sẵn "Lượng thời gian quy định" cho từng đoạn, ta có thể tính toán ra Giờ hoạt động trên từng đoạn, rồi cộng các đoạn lại với nhau.


Xem thêm Sheet "Vidu Thuyetminh" file đính kèm.

Chúc anh em ngày vui vẻ và tốt lành

Thân

 

[cantl]

Cái hay của cách này là tìm "Quy tắc tính toán chung" cho các đoạn thời gian nhỏ khác nhau cần tính, và không cần quan tâm đến các thời đoạn không cần tính đến (như thời gian nghỉ giải lao, thời gian ăn cơm trưa, cơm chiều v.v..). Tức chỉ cần xác định các Mốc đầu (BĐ) của các đoạn thời gian, và với 3 điều kiện lọc có định sẵn "Lượng thời gian quy định" cho từng đoạn, ta có thể tính toán ra Giờ hoạt động trên từng đoạn, rồi cộng các đoạn lại với nhau.

Em cũng xem để mót ít công thức của bác, mặc dù cũng hiểu được sơ sơ nhưng vẫn chưa viết cho 1 bài tương tự được. Với công thức dạng này thì cả trăm mốc thời gian là không vấn đề. Có khi cả Microsoft cũng không nghĩ ra cái này (ý chủ quan).
Em đang luyện 1 bài đơn giản hơn mà chưa xong. Hại não quá.

 

[Hoàng Tuấn 868]

• Tư duy không bằng @leoheocon = @Ekerula = @excel_lv1.5
• Kiến thức quản lý dữ liệu, anh không bằng bác @SA_DQ
• Thông hiểu sâu căn cơ, và lý luận tận nguồn và quảng bác, anh không bằng anh @VetMini và 1 anh nữa
• Thông thạo cả VBA và các hàm, vận dụng công thức mảng đúng nơi đúng chỗ, cùng tìm ra các quy luật số, anh không bằng anh @HieuCD, anh @NDU, anh @huuthang_bd, anh @Phuocam
• Tính cẩn thận và tinh tế trong các kết quả, và ứng biến thích nghi với Google Sheet anh không bằng @befaint
• "Ham học, chọc khuấy" không bằng mấy em.
• Còn nhiều cái không bằng nữa v.v.

Chỉ mỗi một tính cách mà anh biết mình có là thích phân tích vấn đề, rồi tổng hợp để ghi nhớ những gì mình cho là có ích cho bản thân. Nên chả có gì đáng gọi là tuyệt vời cả em. Anh em mình "Đồng hội đồng thuyền".



Như lời hứa, anh ghi giải thích dưới đây với hy vọng không gây nhàm chán với mấy em:
Có hai cách tính về đoạn thời gian:
Ví dụ: như bài cho đoạn đầu tiên theo mốc quy định là bắt đầu (BĐ) 7:00, Kết thúc (KT) 10:00
View attachment 284527
1/ Cách trực tiếp: Số giờ hoạt động (GHĐ) = (KT) trừ ((BĐ)
Cách này là cách được dùng thường xuyên vì dễ thấy ngay kết quả: 10:00 - 7:00 = 3 tiếng
Trường hợp nếu Giờ bắt đầu thực tế (BĐtt) hoặc/và Giờ kết thúc thực tế (KTtt) khác (tức <>) giờ (BĐ) (KT) quy định, thường ta dùng hàm MEDIAN() để tính số trung vị.

Ví dụ 1: Giờ (BĐtt) lúc 8h, (KTtt) lúc 10h
View attachment 284529

• (GHĐ) = MEDIAN( BĐ, KT, KTtt) - MEDIAN( BĐ, KT, BĐtt)
• (GHĐ) = MEDIAN( 7, 10, 10) - MEDIAN( 7, 10, 8)
• (GHĐ) = 10 - 8 = 2

Ví dụ 2: Giờ (BĐtt) lúc 7h30, (KTtt) lúc 9h

• (GHĐ) = MEDIAN( BĐ, KT, KTtt) - MEDIAN( BĐ, KT, BĐtt)
• (GHĐ) = MEDIAN( 7, 10, 9) - MEDIAN( 7, 10, 7.5 )
• (GHĐ) = 9 - 7.5 = 1.5

Với hai mốc cố định trong hàm MEDIAN() là (BĐ): 7h và (KT): 10h, nên nếu giờ BĐtt và KTtt khác với mốc giờ quy định:
- Nếu giờ BĐtt < BĐ thì lấy BĐ
- Nếu giờ BĐtt > BĐ thì lấy BĐtt
- Nếu giờ KTtt < KT thì lấy KTtt
- Nếu giờ KTtt > KT thì lấy KT

Cách này dễ sử dụng và công thức gọn gàng với trường hợp 1 đoạn thời gian, nhưng nếu phát sinh những đoạn thời gian nhỏ loại bỏ không cần tính đến, như bài trên đây, thì phải dùng công thức MEDIAN() nối dài cho từng đoạn vì phải xác định mốc cố định cho từng đoạn, hoặc dùng rất nhiều điều kiện để lọc, nên khi chỉnh sửa, thay đổi hoặc thêm bớt mốc quy định sẽ gặp không ít khó khăn.

2/ Cách gián tiếp: Số giờ hoạt động (GHĐ) = Lọc điều kiện [ (KTtt) - (BĐ) ] - Lọc điều kiện [ (BĐtt) - (BĐ) ]
View attachment 284530
Ví dụ 1: Giờ (BĐtt) lúc 8h, (KTtt) lúc 10h

• (GHĐ) = Lọc ĐK[ (KTtt) - (BĐ) ] - Lọc ĐK[ (BĐtt) - (BĐ) ]
• (GHĐ) = [ 10 - 7 ] - [ 8 - 7 ]
• (GHĐ) = 3 - 1 = 2

Ví dụ 2: Giờ (BĐtt) lúc 7h30, (KTtt) lúc 9h

• (GHĐ) = Lọc ĐK[ (KTtt) - (BĐ) ] - Lọc ĐK[ (BĐtt) - (BĐ) ]
• (GHĐ) = [ 9 - 7 ] - [ 7.5 - 7 ]
• (GHĐ) = 2 - 0.5 = 1.5

Cách này chỉ lấy giờ BĐ làm mốc để so cho cả hai giờ BĐtt và KTtt, cùng với "Điều kiện lọc" cho cả hai vế [ (KTtt) - (BĐ) ] và [ (BĐtt) - (BĐ) ] , như sau:

- Nếu kết quả là số âm: tức KTtt<BĐ hoặc BĐtt<BĐ, thì sẽ ĐIỀU CHỈNH bằng giá trị 0.​

- Nếu kết quả nhỏ hơn Lượng thời gian quy định (tức = (KT) - (BĐ)), và lớn hơn hoặc bằng 0: thì giữ nguyên giá trị của kết quả.​

- Nếu kết quả lớn hơn Lượng thời gian quy định (tức = (KT) - (BĐ)): thì sẽ ĐIỀU CHỈNH thành giá trị của "Lượng thời gian quy định".​

Thực ra, nó đã ẩn 1 bài toán là Lượng thời gian quy định = (KT) - (BĐ) trong điều kiện lọc bằng con số đã tính toán trước, như ví dụ trên ta có điều kiện:

1/ x<0: chỉnh thành 0​

2/ 0<= x < (10-7=3): giữ nguyên giá trị​

3/ x> 3: chỉnh thành 3​

VD1: cho BĐtt: 4h và KTtt 6h30, khi trừ cho (BĐ): 7h đều cho số âm, nên theo điều kiện lọc 1 nó sẽ bị chỉnh thành 0,

=> (GHĐ) = 0 - 0 = 0​

​

VD2: cho BĐtt: 4h và KTtt 9h30, nên => (GHĐ) = [9.5 - 7] - [4 - 7] = [2.5] - [-3] = 2.5 - 0 = 2.5

* [9.5 - 7] = 2.5, theo điều kiện lọc 2 ở trên => 2.5 < 3, nên nó giữ nguyên giá trị 2.5​

VD3: cho BĐtt: 9h và KTtt 13h, nên => (GHĐ) = [13 - 7] - [9 - 7] = [6] - 2

* [13 - 7] = 6, theo điều kiện lọc 3 ở trên=> 6 > 3, nên nó bị chỉnh thành giá trị 3​

* [9 - 7] = 2, theo điều kiện lọc 2 ở trên=> 2 < 3, nên nó giữ nguyên giá trị 2​

VD4: cho BĐtt: 4h và KTtt 13h, nên => (GHĐ) = [13 - 7] - [4 - 7] = [6] - [-3] = 3 - 0 = 3

VD5: cho BĐtt: 11h và KTtt 13h, nên => (GHĐ) = [13 - 7] - [11 - 7] = [6] - [4] = 3 - 3 = 0

Để ý ta thấy:
1/ Với điều kiện 1: chỉnh thành số 0, ta loại bỏ tất cả các giá trị dù là giờ BĐtt hay KTtt mà < giờ BĐ quy định, tức không cần quan tâm.
2/ Với điều kiện 2: giữ nguyên giá trị kết quả, tức giờ BĐtt hay KTtt nằm trong mốc quy định.
3/ Với điều kiện 3: chỉnh thành Lượng thời gian quy định (3h), ta loại bỏ tất cả các giá trị dù là giờ BĐtt hay KTtt mà > giờ BĐ quy định, tức quy về mốc chuẩn: (KT)-(BĐ).

Cái hay của cách này là tìm "Quy tắc tính toán chung" cho các đoạn thời gian nhỏ khác nhau cần tính, và không cần quan tâm đến các thời đoạn không cần tính đến (như thời gian nghỉ giải lao, thời gian ăn cơm trưa, cơm chiều v.v..). Tức chỉ cần xác định các Mốc đầu (BĐ) của các đoạn thời gian, và với 3 điều kiện lọc có định sẵn "Lượng thời gian quy định" cho từng đoạn, ta có thể tính toán ra Giờ hoạt động trên từng đoạn, rồi cộng các đoạn lại với nhau.
View attachment 284531

Xem thêm Sheet "Vidu Thuyetminh" file đính kèm.

Chúc anh em ngày vui vẻ và tốt lành

Thân

Em vừa mở ra, chưa kịp đọc và suy ngẫm bài của anh, nhưng thấy riêng độ dài bài viết của anh cũng làm em nể phục vì sự chu đáo lắm rồi. Trân trọng cảm ơn anh rất nhiều ạ.

 

[Phan Thế Hiệp]

Em vừa mở ra, chưa kịp đọc và suy ngẫm bài của anh, nhưng thấy riêng độ dài bài viết của anh cũng làm em nể phục vì sự chu đáo lắm rồi. Trân trọng cảm ơn anh rất nhiều ạ.

Không có gì to tát đâu em!

Do bọn anh rất khổ sở khi chập chững tìm hiểu công thức mảng, vật lộn với nó quên ăn quên ngủ vì không có tài liệu nào giải thích tận cùng ngọn ngành những cái mình cần tìm, cho nên anh rất cảm thông khi mấy em cũng đang có câu hỏi mà anh đã từng hỏi, nên cố giải thích chi tiết để mấy em dễ nắm bắt được vấn đề một cách tường tận, rồi áp dụng vào công việc của mình cho tốt hơn. Chỉ với nguyện ý vậy thôi em.

Như anh đã từng nói: Giải thích những gì mình hiểu, tức là học, hiểu rõ và nhớ gấp đôi, anh cũng được lợi vậy!

Chúc em ngày thiệt vui.

 

[Hoàng Tuấn 868]

Không có gì to tát đâu em!

Do bọn anh rất khổ sở khi chập chững tìm hiểu công thức mảng, vật lộn với nó quên ăn quên ngủ vì không có tài liệu nào giải thích tận cùng ngọn ngành những cái mình cần tìm, cho nên anh rất cảm thông khi mấy em cũng đang có câu hỏi mà anh đã từng hỏi, nên cố giải thích chi tiết để mấy em dễ nắm bắt được vấn đề một cách tường tận, rồi áp dụng vào công việc của mình cho tốt hơn. Chỉ với nguyện ý vậy thôi em.

Như anh đã từng nói: Giải thích những gì mình hiểu, tức là học, hiểu rõ và nhớ gấp đôi, anh cũng được lợi vậy!

Chúc em ngày thiệt vui.

Dạ, em cảm ơn anh ạ.

 

[VetMini]

• Còn nhiều cái không bằng nữa v.v.

Không bằng vài cái nữa thì tiến đến không bằng cấp luôn.

Em cũng xem để mót ít công thức của bác, mặc dù cũng hiểu được sơ sơ nhưng vẫn chưa viết cho 1 bài tương tự được. ...

Chỉ xem mà hiểu nổi thì ỷ tài quá.
Mỗi lần tôi muốn hiểu công thức của y, tôi phải debug cả buổi. Debug của tôi không đơn giản F9 như mọi người đâu. Gặp công thức khủng quá, thì còn phải tách ra gồm cột phụ, ddooir dữ liệu thành table hoặc data models,...

Em vừa mở ra, chưa kịp đọc và suy ngẫm bài của anh, nhưng thấy riêng độ dài bài viết của anh cũng làm em nể phục vì sự chu đáo lắm rồi. Trân trọng cảm ơn anh rất nhiều ạ.

Chu cái gì? Bạn đọc sót câu chấm (dot points) thứ 5 trong bài #42 rồi.

 

[cantl]

F9

Thú thật, em chỉ biết mỗi cái này, và đoán kết quả nó sẽ thế này, thế này, ... nên muốn lập công thức mới nó phải lặp mấy lần.

 

[Hoàng Tuấn 868]

Chu cái gì? Bạn đọc sót câu chấm (dot points) thứ 5 trong bài #42 rồi.

Hì hì, em có nói là chưa kịp đọc và suy ngẫm mà anh.

 

[LmoovoenX]

Không có gì to tát đâu em!

Do bọn anh rất khổ sở khi chập chững tìm hiểu công thức mảng, vật lộn với nó quên ăn quên ngủ vì không có tài liệu nào giải thích tận cùng ngọn ngành những cái mình cần tìm, cho nên anh rất cảm thông khi mấy em cũng đang có câu hỏi mà anh đã từng hỏi, nên cố giải thích chi tiết để mấy em dễ nắm bắt được vấn đề một cách tường tận, rồi áp dụng vào công việc của mình cho tốt hơn. Chỉ với nguyện ý vậy thôi em.

Như anh đã từng nói: Giải thích những gì mình hiểu, tức là học, hiểu rõ và nhớ gấp đôi, anh cũng được lợi vậy!

Chúc em ngày thiệt vui.

Bác Hiệp cho em hỏi 1 tí.
Có phải là 1 số hàm nó không chấp nhận đối số của nó là kết quả của 1 công thức mảng. Ví dụ là hàm countif chẳng hạn.

góp vui 1 công thức cho bác chủ thớt nhé

=(SUM(IFERROR(TEXT($N$2:$N$4-E2,"hh:mm")+TEXT(F2-$O$2:$O$4,"hh:mm"),0))-(2+SUM(-TEXT($N$2:$N$4-E2,"[<0]\1;[>=0]\0"))+SUM(-TEXT(F2-$O$2:$O$4,"[<0]\1;[>=0]\0")))*(F2-E2))*1440

chỗ hàm trên, khúc sau tính sử dụng countif để đếm mà không được nên mới dùng hàm sum kết hợp với text.

Mình cũng chưa thử hết trường hợp, nhưng có lẽ hàm này áp dụng được cho mọi khung giờ bắt đầu và kết thúc trong ngày. Nếu có thêm khung giờ nghỉ gián đoạn thì chỉ cần sửa ở ô tham chiếu và thay số 2 bằng số ca nghỉ -1.

Xin ý kiến góp ý của các bác về công thức trên.

 

[Phan Thế Hiệp]

Bác Hiệp cho em hỏi 1 tí.
Có phải là 1 số hàm nó không chấp nhận đối số của nó là kết quả của 1 công thức mảng. Ví dụ là hàm countif chẳng hạn.

Đúng vậy, họ countif(s) và sumif(s) không nhận đối số của nó là 1 mảng (array), chỉ chấp nhận 1 vùng (range). Nên nó chỉ chấp nhận các vùng nguyên biến tấu thêm khi ta dùng hàm index(), hoặc offset() để chọn các vùng nguyên cần chọn.

Xin ý kiến góp ý của các bác về công thức trên.

1/ Dùng điều kiện trong TEXT() nếu chỉ lọc số âm (<0) và số dương thì không cần dùng 2 ngoặc "[" "]", mặc định vị trí của nó là:

TEXT(giá trị, "Số dương;Số âm;Số 0")

Nên có thể chỉnh điều kiện của bạn là:

TEXT(.....,"\0;\1") thế thôi.

2/ Công thức của tôi chỉ tính trong khung giờ hoạt động, nếu giờ kết thúc bị lấn thêm vài phút nó cũng không tính. Công thức của bạn khác chỗ đó.

Thân

 

[LmoovoenX]

Đúng vậy, họ countif(s) và sumif(s) không nhận đối số của nó là 1 mảng (array), chỉ chấp nhận 1 vùng (range). Nên nó chỉ chấp nhận các vùng nguyên biến tấu thêm khi ta dùng hàm index(), hoặc offset() để chọn các vùng nguyên cần chọn.

1/ Dùng điều kiện trong TEXT() nếu chỉ lọc số âm (<0) và số dương thì không cần dùng 2 ngoặc "[" "]", mặc định vị trí của nó là:

TEXT(giá trị, "Số dương;Số âm;Số 0")

Nên có thể chỉnh điều kiện của bạn là:

TEXT(.....,"\0;\1") thế thôi.

2/ Công thức của tôi chỉ tính trong khung giờ hoạt động, nếu giờ kết thúc bị lấn thêm vài phút nó cũng không tính. Công thức của bạn khác chỗ đó.

Thân

Cảm ơn bác. Thực sự là chưa nghĩ đến chỗ giờ làm nó lấn vô giờ giải lao hay nghỉ trưa. Để tham khảo thêm chỗ diễn giải của bác để hoàn thiện chỗ này.

 

[Gau_Bo]

Cám ơn các bác nhiều ạ

 

[cantl]

Đúng vậy, họ countif(s) và sumif(s) không nhận đối số của nó là 1 mảng (array), chỉ chấp nhận 1 vùng (range).

Em vào:
1 là: Ồ, cái này trước giờ em cứ tưởng cứ chọn nhiều hơn 1 ô thì nó là array (mảng), thảo nào cứ công thức nào em lập ra thì 90% nó là lỗi.
2 là: em mới được cấp giấy khai sinh đổi tên cho nó Tây tí, tên cũ nó hơi chuối.

 

[LmoovoenX]

em góp vui thêm mấy công thức nữa nhé
1:

=(MIN(F2,CHOOSE(SUM(--(F2>=$P$2:$P$5)),$O$3,$O$4,$O$5,$O$6))-MAX(E2,CHOOSE(SUM(--(E2>=$O$2:$O$5)),$P$2,$P$3,$P$4,$P$5)))*1440-CHOOSE(SUM(--(E2>=$O$2:$O$5)),80,70,10,0)+CHOOSE(SUM(--(F2>=$P$2:$P$5)),80,70,10,0)

2:

=(MIN(F2,MAX(IF(F2>$P$2:$P$5,$O$3:$O$6,0)))-MAX(E2,MAX(IF(E2>$O$2:$O$5,$P$2:$P$5,0))))*1440-MAX(IF(E2<=$O$3:$O$6,{80;70;10;0},0))+MAX(IF(F2<=$P$3:$P$6,{80;70;10;0},0))

3:

=SUM(IFERROR(--TEXT(IF(F2>=$O$3:$O$6,$O$3:$O$6,F2)-IF(E2<=$P$2:$P$5,$P$2:$P$5,E2),"hh:mm"),0))*1440

Bác @Phan Thế Hiệp cho em hỏi, tại sao chỗ cột K và L tương ứng với công thức 1 và 2 có 1 số chỗ nó không ra số nguyên mà ra số thập phân thé nhỉ? Em đã đánh dấu bằng bôi đỏ, đậm trong file.

 

[huuthang_bd]

Nếu bố trí bảng khung thời gian quy định theo 2 cột có lẽ công thức sẽ dễ hiểu hơn.