Trong môi trường kỹ thuật, không có gì lạ khi cần phải “bình thường hóa”

số theo cách nào đó. Ví dụ: bạn có thể cần hiển thị các giá trị số được chuẩn hóa thành bội số của 10 ^ 3, chẳng hạn như 7340 được biểu thị bằng 7,34 và 73400 được biểu thị bằng 73,4.

Trong Excel có thể sử dụng định dạng số tùy chỉnh để thể hiện thông tin bằng ký hiệu khoa học sẽ chuẩn hóa việc hiển thị một số thành bội số của 10 ^ 3. Để làm điều này, bạn sẽ làm theo các bước sau:

  1. Chọn các ô bạn muốn định dạng.

  2. Chọn tùy chọn Ô từ menu Định dạng. Excel sẽ hiển thị hộp thoại Định dạng Ô.

  3. Đảm bảo rằng tab Số được chọn.

  4. Trong danh sách các danh mục định dạng, hãy chọn Tùy chỉnh. (Xem Hình 1.)

  5. Trong hộp Loại, nhập ## 0.0E + 0 làm định dạng của bạn. (Điều này chỉ cung cấp một số ở bên phải của vị trí thập phân. Nếu bạn muốn nhiều hơn, hãy tăng số số không sau vị trí thập phân.)

  6. Nhấp vào OK.

Bây giờ, khi bạn nhập một số chẳng hạn như 7340 vào ô, Excel sẽ hiển thị nó là 7,3E + 3. Do cách định dạng ô được nhập, phần sau chữ E sẽ luôn là bội số của 3.

Điều này là tốt và tốt, nhưng nếu bạn chỉ muốn 7,3 trong ô và sau đó là tiền tố chỉ số với một đơn vị trong ô liền kề, chẳng hạn như kilôgam? Điều này phức tạp hơn một chút, nhưng nó có thể được thực hiện bằng cách sử dụng công thức. Ví dụ: giả sử bạn có số ban đầu của mình trong ô A2, bạn muốn số chuẩn hóa trong ô B2, và tiền tố và tên đơn vị trong ô C2. Tất cả những gì bạn cần làm là nhập công thức sau vào ô B2:

=IF(OR(A2>=1,A2<=-1),SIGN(A2)(ABS(A2)/(10^(3INT(LOG(ABS(A2))/3)))), IF(A2=0,0,SIGN(A2)(ABS(A2)10^(-3*INT(LOG(ABS(A2))/3)))))

Giả sử các đơn vị bạn đang làm việc là một đơn vị tưởng tượng được gọi là foo, trong ô C2 bạn sẽ sử dụng một công thức khác, như sau:

=IF(OR(A2>=1, A2<=-1),CHOOSE(INT(LOG(ABS(A2))/3)+1, "Foos", "Kilofoos", "Megafoos", "Gigafoos", "Terafoos", "Petafoos", "Exafoos"), IF(A2=0,"",CHOOSE(INT(-LOG(ABS(A2))/3)+1, "Millifoos", "Microfoos", "Nanofoos", "Picofoos", "Femtofoos", "Attofoos")))

Những công thức này có vẻ hơi dài, và đúng như vậy. Tuy nhiên, chúng sẽ hoạt động với bất kỳ số nào trong khoảng từ -9.99999E-18 đến 9.99999E + 20.

Ví dụ: nếu bạn đặt số .000125 vào ô A2, thì ô B2 sẽ chứa 125 và ô C2 sẽ chứa Millifoos.

Nếu bạn không muốn sử dụng các công thức dài hơn như các công thức này trong sổ làm việc của mình, bạn có thể phát triển một vài hàm VBA để thực hiện thủ thuật.

Hàm sau, MySciNum, trả về một số chuẩn hóa. Vì vậy, bạn sẽ sử dụng = MySciNum (A2) trong ô B2 để nhận được kết quả tương tự như đã lưu ý ở trên:

Function MySciNum(BaseNum As Double) As Double     Select Case BaseNum         Case Is >= 1             While Abs(BaseNum) > 1000                 BaseNum = BaseNum / 1000             Wend         Case 0             'Do nothing         Case Else             While Abs(BaseNum) < 1                 BaseNum = BaseNum * 1000             Wend     End Select     MySciNum = BaseNum End Function

Hàm này chỉ trả về một số. Để trả về các đơn vị có tiền tố số liệu thích hợp, bạn sẽ sử dụng hàm sau. Tất cả những gì bạn cần làm là chuyển cho nó tham chiếu ô và tên của một đơn vị.

Ví dụ, bạn có thể sử dụng = MySciPre (A2, “foo”). Macro như sau:

Function MySciPre(BaseNum As Double, Unit As String) As String     Dim OrigNum As Double     Dim Pref As Integer     Dim Temp As String

Pref = 0     OrigNum = BaseNum     Select Case BaseNum         Case Is >= 1             While Abs(BaseNum) > 1000                 BaseNum = BaseNum / 1000                 Pref = Pref + 1             Wend         Case 0             Pref = 99         Case Else             While Abs(BaseNum) < 1                 BaseNum = BaseNum * 1000                 Pref = Pref - 1             Wend     End Select

Select Case Pref         Case -6             Temp = "atto" & Unit         Case -5             Temp = "femto" & Unit         Case -4             Temp = "pico" & Unit         Case -3             Temp = "nano" & Unit         Case -2             Temp = "micro" & Unit         Case -1             Temp = "milli" & Unit         Case 0             Temp = Unit         Case 1             Temp = "kilo" & Unit         Case 2             Temp = "mega" & Unit         Case 3             Temp = "giga" & Unit         Case 4             Temp = "tera" & Unit         Case 5             Temp = "peta" & Unit         Case 6             Temp = "exa" & Unit         Case Else             Temp = ""

End Select

If Len(Temp) > 0 Then         Temp = LCase(Temp)

Temp = UCase(Left(Temp, 1)) & Mid(Temp, 2)

If Abs(OrigNum) <> 1 Then Temp = Temp & "s"

End If

MySciPre = Temp End Function

_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 (2928) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: