Hàm tính thời gian thao tá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.