Công thức chung
=LEFT(A1,LEN(A1)-2)/10^((MATCH(RIGHT(A1,2),("PB","TB","GB","MB","KB"),0)-3)*3)
Tóm lược
Để chuẩn hóa các đơn vị thành Gigabyte (hoặc megabyte, kilobyte, v.v.), bạn có thể sử dụng một công thức thông minh dựa trên các hàm MATCH, LEFT và RIGHT. Trong ví dụ được hiển thị, công thức trong C5 là:
=LEFT(B5,LEN(B5)-2)/10^((MATCH(RIGHT(B5,2),("PB","TB","GB","MB","KB"),0)-3)*3)
Lưu ý: để đơn giản, chúng tôi đang sử dụng các giá trị thập phân (cơ số 10), nhưng cũng có một tiêu chuẩn nhị phân. Xem bên dưới.
Giải trình
Quan trọng: công thức này giả định rằng đơn vị là 2 ký tự cuối cùng của chuỗi bao gồm cả số và đơn vị đo lường.
Công thức này hoạt động vì các đơn vị kỹ thuật số có mối quan hệ "sức mạnh của 10".
Về cơ bản, công thức này tách phần số của kích thước khỏi đơn vị, sau đó chia số bằng ước số thích hợp để chuẩn hóa thành Gigabyte. Số chia được tính dưới dạng lũy thừa của 10, vì vậy công thức rút gọn thành:
=number/10^power
Để lấy số, công thức trích xuất tất cả các ký tự từ trái lên đến nhưng không bao gồm các đơn vị:
LEFT(B5,LEN(B5)-2)
Để nhận được "power", công thức khớp với đơn vị trong một hằng số mảng được mã hóa cứng:
MATCH(RIGHT(B5,2),("PB","TB","GB","MB","KB"),0)
Trả về vị trí của đơn vị trong hằng số mảng. Ví dụ, đối với công thức trong C5, đơn vị là "KB", do đó, vị trí là 5. Kết quả này được điều chỉnh bằng cách trừ 3, sau đó nhân kết quả với 3, thu được 6 là lũy thừa, được sử dụng làm số mũ để tính toán kết quả chính xác theo gigabyte:
=900/10^6 =900/1000000 =0.0009
Công thức chuẩn nhị phân
Máy tính sử dụng hệ thống số nhị phân để lưu trữ và báo cáo kích thước dữ liệu, nhưng các tiền tố như "kilo", "mega", "giga", v.v. dựa trên hệ mét. Đó là một chủ đề khó hiểu, nhưng việc sử dụng đơn vị kích thước thập phân để lưu trữ trên máy tính không thực sự chính xác và sự khác biệt tăng lên khi các đơn vị ngày càng lớn. Công thức dưới đây sẽ chuẩn hóa thành đơn vị nhị phân.
=LEFT(A1,LEN(A1)-2)/2^((MATCH(RIGHT(A1,2),("PB","TB","GB","MB","KB"),0)-3)*10)
Với công thức này, về mặt kỹ thuật, bạn đang nhận được Gibibyte (GiB), không phải Gigabyte. Thêm thông tin ở đây và ở đây.
