John làm việc trong ngành công nghiệp truyền hình, nơi mà thời gian được thực hiện ở độ phân giải nhỏ hơn một giây. Video truyền hình phải tính đến giờ, phút, giây và khung hình. (Có ba mươi khung hình mỗi giây.) John đang tự hỏi liệu có cách nào để xử lý khung hình trong Excel.

Không có cách nào để xử lý khung như một phần của giá trị thời gian gốc trong Excel. (Trong ngành truyền hình, giá trị thời gian bao gồm các khung hình thường được gọi là “mã thời gian” hoặc “mã thời gian”.) Tuy nhiên, có một số điều bạn có thể làm để xử lý các khung hình. Có lẽ gợi ý rõ ràng nhất là giữ giờ, phút và giây làm giá trị thời gian thông thường, sau đó đặt các khung vào một ô riêng biệt. Hạn chế ngay lập tức của cách tiếp cận này là các phép tính cho “thời gian truyền hình” không dễ dàng như nếu chúng được biểu diễn bằng một giá trị duy nhất.

Một cách giải quyết vấn đề này là cố gắng thực hiện các phép tính của riêng bạn trong một macro.

Excel trải qua một quy trình nội bộ để chuyển đổi thời gian thành giá trị thập phân có thể làm việc rất dễ dàng. Bạn có thể mô phỏng quá trình chuyển đổi tương tự này, chuyển đổi giá trị thời gian (bao gồm cả khung hình) thành giá trị thập phân. Giờ TV, ở định dạng 00: 29: 10: 10, có thể được lưu trữ trong một ô (nơi Excel sẽ coi nó như một chuỗi) và sau đó được chuyển đổi thành giá trị bằng macro.

Tất nhiên, có một vấn đề ở đây: Bạn không thể chuyển đổi thời gian thành giá trị thập phân thực giữa 0 và 1 giống như Excel làm với thời gian. Lý do là do các giới hạn về chữ số có nghĩa của Excel. Để đạt được một giá trị, bạn sẽ chia giờ cho 24, phút cho 1440 (24 60), giây cho 86400 (24 60 60) và số khung cho 2592000 (24 60 60 30, giả sử bạn đang làm việc ở 30 khung hình môi giây). Khi bạn bắt đầu nhận được các giá trị nhỏ, nó vượt quá giới hạn của Excel trong việc duy trì mọi thứ ở mức mười lăm chữ số có nghĩa. Do đó, bạn sẽ gặp phải lỗi làm tròn không thể tránh khỏi trên giá trị khung.

Một giải pháp cho vấn đề này là không cố gắng làm việc với các giá trị thập phân từ 0 đến 1, mà thay vào đó làm việc với các số nguyên. Nếu bạn chuyển đổi chuỗi thời gian thành một giá trị số nguyên đại diện cho tổng số khung hình trong thời gian đó, thì bạn có thể dễ dàng thực hiện phép toán trên giá trị kết quả.

Macro sau sẽ thực hiện chuyển đổi một chuỗi ở định dạng đã được đề cập:

Function Time2Num(Raw) As Long     Dim FirstColon As Integer     Dim SecondColon As Integer     Dim ThirdColon As Integer     Dim NumHours As Integer     Dim NumMinutes As Integer     Dim NumSeconds As Integer     Dim NumFrames As Integer     Dim FrameRate As Integer     Dim T2D As Long

' Change the following to the number of frames     ' per second with which you are working     FrameRate = 30

FirstColon = InStr(Raw, ":")

SecondColon = InStr(FirstColon + 1, Raw, ":")

ThirdColon = InStr(SecondColon + 1, Raw, ":")



NumHours = Val(Mid(Raw, 1, FirstColon - 1))

NumMinutes = Val(Mid(Raw, FirstColon + 1, SecondColon - 1))

NumSeconds = Val(Mid(Raw, SecondColon + 1, ThirdColon - 1))

NumFrames = Val(Mid(Raw, ThirdColon + 1, Len(Raw)))



T2D = CLng(NumHours)

T2D = T2D  60 + NumMinutes     T2D = T2D  60 + NumSeconds     T2D = T2D * FrameRate + NumFrames

Time2Num = T2D End Function

Để xem cách này hoạt động như thế nào, nếu bạn có một chuỗi chẳng hạn như 37: 15: 42: 06 trong ô A4 và bạn sử dụng công thức = Time2Num (A4), kết quả là giá trị 4024266, là số khung trong 37 giờ, 15 phút, 42 giây và 6 khung hình. Để chuyển đổi các giá trị đó về thời gian dễ hiểu, bạn có thể sử dụng hàm sau:

Function Num2Time(Raw) As String     Dim NumHours As Integer     Dim NumMinutes As Integer     Dim NumSeconds As Integer     Dim NumFrames As Integer     Dim FrameRate As Integer     Dim RemainingTime As Long

' Change the following to the number of frames     ' per second with which you are working     FrameRate = 30

NumHours = Raw \ (CLng(FrameRate  60)  60)

RemainingTime = Raw Mod (CLng(FrameRate  60)  60)



NumMinutes = RemainingTime \ (60 * FrameRate)

RemainingTime = RemainingTime Mod (60 * FrameRate)



NumSeconds = RemainingTime \ FrameRate     RemainingTime = RemainingTime Mod FrameRate

NumFrames = RemainingTime

Num2Time = Format(NumHours, "00") & ":" & _       Format(NumMinutes, "00") & ":" & _       Format(NumSeconds, "00") & ":" & _       Format(NumFrames, "00")

End Function

Bằng cách kết hợp hai hàm, bạn có thể thực hiện một số phép toán với thời gian. Ví dụ: giả sử bạn có thời gian 00: 29: 10: 10 trong ô A4 và thời gian 00: 16: 12: 23 trong ô A5. Nếu bạn đặt công thức sau vào một ô, bạn có thể tìm ra sự khác biệt giữa hai thời điểm:

=Num2Time(Time2Num(A4)-Time2Num(A5))

Kết quả là 00: 12: 57: 17.

Các ví dụ được trình bày ở đây là thô sơ; họ không tính đến việc xử lý lỗi hoặc kiểm tra giới hạn thời gian sử dụng. Bạn có thể mở rộng các ví dụ để phù hợp với nhu cầu của mình hoặc bạn có thể tìm đến nguồn của bên thứ ba. Ví dụ: bạn có thể tìm thấy lời giải thích (với sổ làm việc mẫu) cho thời gian NTSC và PAL tại URL sau:

http://www.kenstone.net/fcp_homepage/timecode_spreadsheet.html

Ngoài ra còn có các giải pháp dựa trên vĩ mô khác trôi nổi trên Internet.

Cách tốt nhất là sử dụng công cụ tìm kiếm yêu thích của bạn và tìm kiếm “timecode excel” hoặc “time code excel” (không có dấu ngoặc kép). Bạn sẽ tìm thấy rất nhiều ví dụ về mã mà bạn có thể bắt đầu.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (8353) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: