Tổng giờ làm việc mỗi giờ theo phút

[Hung Duong]

Good morning Anh Em diễn đàn,
Rất cảm ơn những hổ trợ quý báu của Anh Em. Chúc cộng đồng GPE ngày càng lớn mạnh

Hôm nay, mình có một vấn đề nan giải rất cần anh em chia sẽ và hổ trợ. Vấn đề nằm trong file đính kèm với thông tin như sau:
1. A5:A14 là tên nhân viên, Cột C là giờ bắt đầu & Cột D là giờ kết thúc.
2. Màu xám là gantt chart theo giờ bắt đầu và giờ kết thúc
3. Các chữ trong ô màu xám là vị trí được xếp trong ca cho các nhân viên
4. E3:AF3 là tổng giờ làm việc của các nhân viên được xếp lịch trong từng giờ

*Vấn đề: Tính tổng thời gian làm việc trong từng giờ theo phút và dữ liệu hiện lên trong ô tương ứng trong E3:AF3
Ví dụ: Ô P3 là ô ghi tổng thời gian làm việc từ 15:00-16:00, theo lịch xếp thì tổng là 3.5 giờ = A (0.25)+D (0.25)+E (1)+H (1)+I (1)

 

[Hung Duong]

Nếu mình không dùng công thức được và mình rất mong sự chia sẽ, hổ trợ các hướng đi khác, ví dụ dùng sheet trung gian hoặc VBA

 

[bebo021999]

Nếu theo kết cấu bảng, theo mình hiểu thì cột P với tiêu đề 15:00, nghĩa là ứng với múi giờ từ 15:00-15:59
Do vậy, Nếu dòng nào có mốc bắt đầu trong khoảng này (vd như 15:15) phải điền nội dung vào cột này thì kết quả mới chính xác (mình đã điền tay vào)

Tại E3, copy sang phải

=SUM((IF($D$5:$D$14<=F$2,$D$5:$D$14,F$2)-IF($C$5:$C$14>=E$2,$C$5:$C$14,E$2)>0)*(IF($D$5:$D$14<=F$2,$D$5:$D$14,F$2)-IF($C$5:$C$14>=E$2,$C$5:$C$14,E$2))*(E$5:E$14<>"")*24)

Đây là công thức mảng, kết thúc bằng Ctrl-shift-enter (trên thanh công thức thấy cặp { } ở đầu và cuối là được)

 

[HieuCD]

Good morning Anh Em diễn đàn,
Rất cảm ơn những hổ trợ quý báu của Anh Em. Chúc cộng đồng GPE ngày càng lớn mạnh

Hôm nay, mình có một vấn đề nan giải rất cần anh em chia sẽ và hổ trợ. Vấn đề nằm trong file đính kèm với thông tin như sau:
1. A5:A14 là tên nhân viên, Cột C là giờ bắt đầu & Cột D là giờ kết thúc.
2. Màu xám là gantt chart theo giờ bắt đầu và giờ kết thúc
3. Các chữ trong ô màu xám là vị trí được xếp trong ca cho các nhân viên
4. E3:AF3 là tổng giờ làm việc của các nhân viên được xếp lịch trong từng giờ

*Vấn đề: Tính tổng thời gian làm việc trong từng giờ theo phút và dữ liệu hiện lên trong ô tương ứng trong E3:AF3
Ví dụ: Ô P3 là ô ghi tổng thời gian làm việc từ 15:00-16:00, theo lịch xếp thì tổng là 3.5 giờ = A (0.25)+D (0.25)+E (1)+H (1)+I (1)

E3 =SUM(--TEXT(IF($D$5:$D$14+($D$5:$D$14<$C$5:$C$14)>E2+1/24,E2+1/24,$D$5:$D$14+($D$5:$D$14<$C$5:$C$14))-IF($C$5:$C$14<E2,E2,$C$5:$C$14),"[<0]\0"))*24

Ctrl+Shift+Enter, copy ngang

 

[Hung Duong]

E3 =SUM(--TEXT(IF($D$5:$D$14+($D$5:$D$14<$C$5:$C$14)>E2+1/24,E2+1/24,$D$5:$D$14+($D$5:$D$14<$C$5:$C$14))-IF($C$5:$C$14<E2,E2,$C$5:$C$14),"[<0]\0"))*24

Ctrl+Shift+Enter, copy ngang

Nếu theo kết cấu bảng, theo mình hiểu thì cột P với tiêu đề 15:00, nghĩa là ứng với múi giờ từ 15:00-15:59
Do vậy, Nếu dòng nào có mốc bắt đầu trong khoảng này (vd như 15:15) phải điền nội dung vào cột này thì kết quả mới chính xác (mình đã điền tay vào)

Tại E3, copy sang phải

=SUM((IF($D$5:$D$14<=F$2,$D$5:$D$14,F$2)-IF($C$5:$C$14>=E$2,$C$5:$C$14,E$2)>0)*(IF($D$5:$D$14<=F$2,$D$5:$D$14,F$2)-IF($C$5:$C$14>=E$2,$C$5:$C$14,E$2))*(E$5:E$14<>"")*24)

Đây là công thức mảng, kết thúc bằng Ctrl-shift-enter (trên thanh công thức thấy cặp { } ở đầu và cuối là được)

Rất cảm ơn 2 bạn đã hổ trợ. Vấn đề của mình đã được hoàn thành.

Chúc một ngày tốt lành

 

[Hung Duong]

E3 =SUM(--TEXT(IF($D$5:$D$14+($D$5:$D$14<$C$5:$C$14)>E2+1/24,E2+1/24,$D$5:$D$14+($D$5:$D$14<$C$5:$C$14))-IF($C$5:$C$14<E2,E2,$C$5:$C$14),"[<0]\0"))*24

Ctrl+Shift+Enter, copy ngang

Xin chào Hieu,

Xin lỗi làm phiền bạn thêm lần nữa. Công thức tính tổng đã làm việc rất hiệu quả, tuy nhiên khi nhân viên có các ngày nghỉ theo code của nhân sự bao gồm "AL, COMP, OFF, UPL, PH" và mình để code trong cột giờ vào của những ngày đó thì công thức cộng không ra kết quả.

Mình gửi file đính kèm, mong nhận được sự hổ trợ chuyên môn từ bạn

Cảm ơn bạn

Hưng

 

[dazkangel]

E3 =SUM(--TEXT(IF($D$5:$D$14+($D$5:$D$14<$C$5:$C$14)>E2+1/24,E2+1/24,$D$5:$D$14+($D$5:$D$14<$C$5:$C$14))-IF($C$5:$C$14<E2,E2,$C$5:$C$14),"[<0]\0"))*24

Ctrl+Shift+Enter, copy ngang

Kiểu từa tựa nhấn Ctrl+Shift+Enter

=SUM(TEXT(E$2+{0,1,2,3}/96,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]1;\0")/4)

Thêm kiểu mới để loại những ô nghỉ:

=SUM(IF($D$5:$D$14="",,TEXT(E$2+{0,1,2,3}/96,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]1;\0"))/4)

 

[huonglien1901]

Kiểu từa tựa nhấn Ctrl+Shift+Enter

=SUM(TEXT(E$2+{0,1,2,3}/96,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]\1;\0")/4)

Anh có thể giải thích tí tẹo về công thức của anh được không? Em đọc mà chưa hiểu gì hết.

 

[dazkangel]

Anh có thể giải thích tí tẹo về công thức của anh được không? Em đọc mà chưa hiểu gì hết.

Anh hiểu thế này, họ lấy dữ liệu dựa vào các cột mốc 15 phút một nên lấy giờ cần tính cộng thêm các khoảng:
Ví dụ lấy 9:00 làm mốc thì ta ra {9:00,9:15,9:30,9:45} rồi nếu các số này lớn hơn thời gian kết thúc thì trả về 0
Tiếp theo nếu lớn hơn giờ bắt đầu thì bằng 1 còn các kết quả còn lại là 0 hết sau đó chia cho 4 vì mình tách 1 giờ ra 4 lần
Rồi cộng tất cả lại là ra số cần tìm, dĩ nhiên chỉ là tương đối vì nếu nhập giờ kết thúc là 9:46 thì kết quả vẫn ra nhưng thực tế là sai.

 

[HieuCD]

Kiểu từa tựa nhấn Ctrl+Shift+Enter

=SUM(TEXT(E$2+{0,1,2,3}/96,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]1;\0")/4)

Thêm kiểu mới để loại những ô nghỉ:

=SUM(IF($D$5:$D$14="",,TEXT(E$2+{0,1,2,3}/96,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]1;\0"))/4)

Nhìn công thức muốn xỉu

 

[HieuCD]

Xin chào Hieu,

Xin lỗi làm phiền bạn thêm lần nữa. Công thức tính tổng đã làm việc rất hiệu quả, tuy nhiên khi nhân viên có các ngày nghỉ theo code của nhân sự bao gồm "AL, COMP, OFF, UPL, PH" và mình để code trong cột giờ vào của những ngày đó thì công thức cộng không ra kết quả.

Mình gửi file đính kèm, mong nhận được sự hổ trợ chuyên môn từ bạn

Cảm ơn bạn

Hưng

E3 =SUM(IF(ISTEXT($C$5:$C$14),0,--TEXT(IF($D$5:$D$14+($D$5:$D$14<$C$5:$C$14)>E2+1/24,E2+1/24,$D$5:$D$14+($D$5:$D$14<$C$5:$C$14))-IF($C$5:$C$14<E2,E2,$C$5:$C$14),"[<0]\0")))*24

Nếu thời gian qui định làm tròn tới 15 phút dùng công thức của bạn @dazkangel có chỉnh tí tẹo (không biết tại sao luôn )

E3 =SUM(IF($D$5:$D$14="",,TEXT(E$2+{0,1,2,3}/95,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]1;\0"))/4)

Hai công thức đều nhấn Ctrl+Shift+Enter

 

[Hung Duong]

E3 =SUM(IF(ISTEXT($C$5:$C$14),0,--TEXT(IF($D$5:$D$14+($D$5:$D$14<$C$5:$C$14)>E2+1/24,E2+1/24,$D$5:$D$14+($D$5:$D$14<$C$5:$C$14))-IF($C$5:$C$14<E2,E2,$C$5:$C$14),"[<0]\0")))*24

Nếu thời gian qui định làm tròn tới 15 phút dùng công thức của bạn @dazkangel có chỉnh tí tẹo (không biết tại sao luôn )

E3 =SUM(IF($D$5:$D$14="",,TEXT(E$2+{0,1,2,3}/95,"[>"&$D$5:$D$14+($D$5:$D$14<$C$5:$C$14)-1/1440&"]\0;[>="&$C$5:$C$14&"]1;\0"))/4)

Hai công thức đều nhấn Ctrl+Shift+Enter

Mình đã hoàn thành xong file và rất cảm ơn sự chia sẽ, giúp đỡ của các anh em, đặc biệt la @HieuCD @dazkangel rất nhiều.
Chúc 2 bạn luôn thành công và diễn đàn GPE ngày càng lớn mạnh