Câu hỏi cơ bản về Excel liên quan đến công thức

Tôi đang cố gắng tạo một lịch trình trong Excel. Tôi cần một ô để tính toán sự khác biệt giữa hai số trong cùng một ô... ví dụ: Nhân viên được lên lịch trong một ô là 11-2 hoặc 11:00AM-2:00PM. Tôi cần tính toán cho 3 giờ trong ô đó để có thể cộng tổng số giờ dự kiến ​​ở cuối bảng tính cho tổng số giờ làm việc theo lịch trình hàng tuần của mỗi nhân viên. Ví dụ

Lịch làm việc của nhân viên: 11-2, 11-2, 5-9.30. Tổng cộng cả tuần: 10.5

Trả lời:

Bạn đang gặp phải một vấn đề thiết kế cơ bản. Mỗi mục nhập cần có thời gian bắt đầu ở một cột và thời gian kết thúc ở một cột khác. Khi đó việc thực hiện sẽ rất dễ dàng, hoặc ít nhất là dễ hơn nhiều. Excel không được thiết kế để xử lý 2 giá trị trong cùng một ô. Bạn sẽ cần một công thức phức tạp để có được kết quả, hoặc một hàm do người dùng định nghĩa (UDF) tùy chỉnh để làm điều tương tự - UDF là một loại macro đặc biệt sử dụng mã VBA để lấy kết quả.

Ngoài ra, các ô bạn nhập thời gian bắt đầu/kết thúc cần được định dạng là ngày/giờ để tính toán chính xác, và ô cuối cùng nơi bạn muốn hiển thị tổng cộng cho cả tuần cần được định dạng là [giờ]:phút. Cuối cùng, nếu bất kỳ khoảng thời gian nào được lên lịch kéo dài qua nửa đêm, chẳng hạn như từ 11 giờ tối ngày hôm trước đến 2 giờ sáng ngày hôm sau, các mục nhập cần bao gồm cả ngày cùng với giờ, mặc dù chúng có thể được định dạng để chỉ hiển thị giờ và phút.

Đây là một ví dụ cơ bản:

Công thức trong cột Tổng số giờ của Bill (hàng 3) là:

=SUM(C3-B3,E3-D3,G3-F3,I3-H3,K3-J3,M3-L3,O3-N3)

với ô được định dạng tùy chỉnh là [h]:mm

Thời gian bắt đầu/kết thúc đều bao gồm ngày tháng, và lưu ý rằng các mục như mục của Bill vào thứ Hai - nửa đêm là vào ngày "tiếp theo": vì vậy 2 mục cho thứ Hai là:

17:00 ngày 30/12/2013 và 24:00 ngày 31/12/2013

Trả lời:

Nếu bạn NHẤT THIẾT phải nhập cả hai thời gian vào cùng một ô, và tôi thực sự khuyên bạn không nên làm vậy, thì hàm do người dùng định nghĩa (UDF) sau đây sẽ giải quyết vấn đề cho bạn. Bạn sẽ đặt công thức tham chiếu đến hàm này vào vị trí bạn muốn hiển thị tổng số của tuần. Các ô cần được tính toán phải liền kề nhau (mặc dù cũng có thể chỉ cần 1 ô). Hàm này không thực sự "mạnh mẽ" vì nó không kiểm tra các điều kiện lỗi có thể xảy ra, chẳng hạn như ô không có cặp số thích hợp, vì vậy nếu dữ liệu bạn nhập sai, nó có thể sẽ báo lỗi.

Ví dụ, giả sử 3 ô của bạn nằm ở vị trí B2, C2 và D2, bạn sẽ nhập công thức như sau:

=gettotaltime(B2:D2)

Đoạn mã này được đặt trong một mô-đun mã thông thường. Xem trang này để biết hướng dẫn cách đặt mã vào đó:

http://www.contextures.com/xlvba01.html#videoreg

Sau khi thêm mã vào bảng tính, bạn phải lưu nó dưới dạng bảng tính có hỗ trợ macro, loại .xlsm hoặc .xlsb.

Đây là đoạn mã. Hãy đọc phần chú thích ở đầu mã để xem cách nó xử lý số phút đã nhập.

Hàm GetTotalTime(scheduleDays As Range) As Single
'gọi từ bảng tính như
' =GetTotalTime(B2:H2)
' trong đó B2 là ô đầu tiên chứa các thời gian đã lên lịch.
' và H2 là ô cuối cùng chứa thời gian đã lên lịch.
LƯU Ý: Bạn có thể phân tách giờ và phút bằng dấu gạch ngang hoặc dấu gạch chéo.
' một dấu hai chấm ( : ) hoặc dấu thập phân ( . ), tuy nhiên
'Trong cả hai trường hợp, biên bản đều được xử lý như
' phút, chứ không phải phần thập phân của một giờ, vậy nên
' 11:30 và 11:3 là cùng một số.
'
Const hrSeparator = "-"
Const minSeparator = ":"
Dim currentDay As Range
Khai báo biến splitAt là số nguyên
Khai báo biến splitHM dưới dạng số nguyên
Dim totalHours As Single
Dim txtEHour As String
Dim txtEMin As String
Dim txtShour As String
Dim txtSMin As String
Dim endHour As Single
Dim startHour As Single

Đối với mỗi ngày hiện tại trong lịch trình ngày
Nếu không rỗng (ngày hiện tại) thì
splitAt = InStr(currentDay, hrSeparator)
Nếu splitAt > 0 thì
txtShour = Left(currentDay, splitAt - 1)
txtSHour = Replace(txtSHour, ".", minSeparator)
splitHM = InStr(txtShour, minSeparator)
Nếu splitHM > 0 thì
Nếu Right(txtShour, 1) = minSeparator thì
txtSHour = txtSHour & "0"
splitHM = InStr(txtShour, minSeparator)
Kết thúc nếu
startHour = Val(Left(txtShour, splitHM - 1)) _
+ (Val(Mid(txtSHour, splitHM + 1, Len(txtSHour) - splitHM)) / 60)
Khác
startHour = Val(txtSHour)
Kết thúc nếu

txtEHour = Mid(currentDay, splitAt + 1, Len(currentDay) - splitAt)
txtEHour = Replace(txtEHour, ".", minSeparator)
splitHM = InStr(txtEHour, minSeparator)
Nếu splitHM > 0 thì
Nếu Right(txtEHour, 1) = minSeparator thì
txtEHour = txtEHour & "0"
splitHM = InStr(txtEHour, minSeparator)
Kết thúc nếu
endHour = Val(Left(txtEHour, splitHM - 1)) _
+ (Val(Mid(txtEHour, splitHM + 1, Len(txtEHour) - splitHM)) / 60)
Khác
endHour = Val(txtEHour)
Kết thúc nếu
Nếu endHour < startHour thì
endHour = endHour + 24 ' cho phép vượt qua nửa đêm một lần
Kết thúc nếu
tổng số giờ = tổng số giờ + (giờ cuối - giờ bắt đầu)
Kết thúc nếu
Kết thúc nếu
Tiếp theo ' kết thúc Mỗi vòng lặp currentDay
GetTotalTime = totalHours
Kết thúc hàm