Có vẻ như Excel không thể hiển thị giá trị thời gian có độ chính xác thứ hai thấp hơn mili giây, chẳng hạn như micro giây và nano giây
Nếu bạn đã có dấu thời gian được định dạng theo micro giây hoặc nano giây và bạn muốn hiển thị chúng trong Excel, thì mẹo là định dạng trước các ô dưới dạng văn bản trước khi bạn sao chép dấu thời gian vào chúng
Mili giây không phải là công dân hạng nhất trong Excel. Không có MILLISECOND tương đương với hàm SECOND và bạn sẽ không tìm thấy định dạng thời gian được xác định trước bao gồm giờ, phút, giây và mili giây
Điều này không có nghĩa là Excel không thể xử lý các giá trị thời gian bằng mili giây. Yêu cầu duy nhất là chúng ở định dạng mà Excel nhận ra. Bạn cũng nên gán định dạng tùy chỉnh cho các ô để bạn có thể dễ dàng đọc các giá trị. Tôi sẽ giải thích hai khía cạnh này trong bài viết này và trình bày các giải pháp cho các nhiệm vụ và sự cố phổ biến liên quan đến mili giây. Nếu bạn chỉ quan tâm đến một bản tóm tắt ngắn gọn, hãy chuyển đến TL; DR ở cuối
Ghi chú. Tôi đang sử dụng cài đặt khu vực Hoa Kỳ cho bài viết này. Các khu vực khác có thể yêu cầu các định dạng khác nhau, e. g. “hừ. mm. ss.000” cho Đức [bằng dấu phẩy thay vì dấu chấm]
Các định dạng thời gian với mili giây được Excel nhận dạng
Trong nội bộ, Excel lưu trữ các giá trị thời gian dưới dạng phân số của một ngày [24 giờ]. Điều này có nghĩa là 12h được biểu diễn bằng 12÷24 = ½ = 0. 5 [nửa ngày] và 3h là 3÷24 = 0. 125. Trong biểu diễn này, 1ms chỉ là một giá trị nhỏ. 1ms ÷ [24h × 60 phút/h × 60 giây/phút × 1000ms/s] = 1÷86400000 ≈ 0. 000000011574074 [so sánh giá trị này với độ chính xác số của Excel]
Điều này làm cho việc tính toán chênh lệch thời gian trở nên rất dễ dàng, nhưng việc nhập các giá trị theo cách này đặc biệt không thân thiện với người dùng. May mắn thay, Excel cũng nhận ra định dạng sau và các biến thể tương tự
hh:mm:ss.000
Thí dụ. “01. 23. 45. 678” là viết tắt của một giờ, 23 phút, 45 giây và 678 mili giây. Bạn không cần phải nhập các số 0 ở đầu [hoặc các số 0 ở cuối trong phần mili giây]
Các số được nhập như thế này hoặc đọc từ một tệp ở định dạng này được chuyển đổi thành phân số của một ngày. Tuy nhiên, chúng không được hiển thị chính xác cho đến khi bạn chỉ định một định dạng tùy chỉnh tương ứng cho các ô, như được giải thích bên dưới
Định dạng ô tùy chỉnh với mili giây
Ví dụ thực tế
Nhấp vào đây để tải xuống tệp CSV được tạo bởi phần mềm thu thập dữ liệu Simple Data Logger của chúng tôi trong quá trình cân định mức bằng cách sử dụng cân chính xác A&D GX-A. Phần mềm được cấu hình để ghi lại ngày, giờ [với mili giây] và trọng lượng
Sau khi mở tệp CSV trong Excel, bạn sẽ nhận thấy rằng thời gian không được hiển thị chính xác
Điều này có thể được khắc phục bằng cách gán định dạng tùy chỉnh sau cho các ô [chọn cột chứa thời gian, nhấp chuột phải và chọn “định dạng ô”]
[hh]:mm:ss.000
Dấu ngoặc vuông cho biết đây là định dạng "thời gian đã trôi qua" có thể hiển thị các giá trị hàng giờ bằng hoặc lớn hơn 24 giờ. Sau khi áp dụng, các giá trị thời gian được hiển thị ở định dạng mong muốn
Các tác vụ thường gặp khi sử dụng mili giây trong Excel
Tính chênh lệch thời gian
Nhờ định dạng số được Excel sử dụng nội bộ, bạn có thể thực hiện tất cả các loại phép tính với giá trị thời gian. Để tính chênh lệch múi giờ, chỉ cần lấy thời gian trước đó trừ đi thời gian sau
Nếu kết quả bao gồm “############” thay vì chênh lệch múi giờ dự kiến, có thể có một số lý do
- Bạn có thể cần phải áp dụng tùy chỉnh tương tự [hh]. mm. ss. 000 như trên vào kết quả
- Kết quả là giá trị âm. Excel không thích giá trị thời gian âm, nhưng có một số cách giải quyết
Hãy cẩn thận với những thay đổi ngày. Công thức chênh lệch múi giờ đơn giản được sử dụng ở trên sẽ thất bại vào lúc nửa đêm. May mắn thay, ngày được lưu trữ nội bộ dưới dạng số nguyên và do đó, bạn có thể chỉ cần thêm các giá trị ngày và giờ trước khi thực hiện phép trừ như tôi đã giải thích ở đây
Chỉ hiển thị mili giây
Excel không có hàm MILLISECOND để chỉ trả về mili giây của một giá trị thời gian. Bạn có thể sử dụng công thức sau để thay thế
=RIGHT[TEXT[D2, "hh:mm:ss.000"],3]*1
Điều này lấy 3 ký tự ngoài cùng bên phải [mili giây] từ ô D2 và nhân chúng với một để đảm bảo kết quả được coi là số [không phải văn bản]
Định dạng các ô trong cột kết quả dưới dạng số không có vị trí thập phân và bạn sẽ thấy rằng chỉ phần nghìn giây đã được trích xuất
Sau đó, bạn có thể dễ dàng thực hiện các phép tính như MIN, MAX, STDEV. P, v.v.
Chuyển đổi giá trị thời gian thành mili giây
Nếu bạn muốn chuyển đổi toàn bộ giá trị thời gian thành mili giây thay vì chỉ trích xuất phần mili giây, có hai cách tiếp cận sẽ dẫn đến cùng một kết quả
Bạn có thể sử dụng các hàm HOUR, MINUTE và SECOND tích hợp cùng với chức năng thay thế MILLISECOND của chúng tôi [xem bên trên] để chuyển đổi một giá trị thời gian [trong ô D2]
=HOUR[D2]*3600000+MINUTE[D2]*60000+SECOND[D2]*1000+RIGHT[TEXT[D2, "hh:mm:ss.000"],3]
Ngoài ra còn có một giải pháp thay thế tao nhã cho giải pháp này. Bạn chỉ cần nhân giá trị thời gian với 86400000 để chuyển đổi nó thành mili giây. Điều này hoạt động vì định dạng số nội bộ được Excel sử dụng trong đó 1 bằng 24h = 24h × 60 phút/h × 60 giây/phút × 1000 mili giây/giây = 86400000 mili giây
Sử dụng định dạng dễ dàng hơn để nhập thủ công thời gian bằng mili giây
Các giá trị thời gian bằng mili giây được sử dụng cho các ví dụ trên đã có sẵn trong phần “hh. mm. ss. 000” theo yêu cầu của Excel. Nhưng nếu bạn phải nhập nhiều giá trị thời gian theo cách thủ công thì sao? . g. 1. 25. 54. 010 thay vì 1. 25. 54. 010
Để Excel nhận dạng các giá trị này dưới dạng thời gian, chúng phải được chuyển đổi thành “hh. mm. ss. định dạng 000”. Tuy nhiên, nếu bạn sử dụng hàm SUBSTITUTE để thay thế tất cả các dấu chấm bằng dấu hai chấm, bạn sẽ không giữ nguyên dấu chấm cuối cùng. Giải pháp là giới hạn SUBSTITUTE chỉ thay thế lần xuất hiện đầu tiên của dấu chấm và sau đó áp dụng lại để thay thế dấu chấm thứ hai, giữ nguyên dấu chấm cuối cùng. Công thức bắt buộc trông có vẻ phức tạp nhưng thực ra rất đơn giản [nó giả sử giá trị bạn muốn chuyển đổi được chứa trong ô A2]
=SUBSTITUTE[SUBSTITUTE[A2,".",":",1],".",":",1]
Vui lòng dùng thử trước khi bạn nhập thủ công hàng trăm giá trị thời gian bằng định dạng dấu chấm. Nó làm việc tốt cho tôi