Excel 单元格转换教程 – 转换单位、文本和数字、货币、时区等

ms excelmicrosoft technologiesadvanced excel function更新于 2025/4/10 17:48:17

在 Excel 中处理数据时，有时您可能需要对单元格进行某些数据转换，例如转换单位、转换货币、转换时区等等。

转换单位

大多数情况下，当我们需要将数字从一个计量单位转换为另一个计量单位时，我们会使用 CONVERT 函数。但是，对于您尝试的某些单位转换，CONVERT 函数可能无法正常运行。

步骤 1

您可以使用 CONVERT 函数将范围内指定的英寸分别转换为英尺、厘米和毫米的对应值。

步骤 2

测量单位多种多样。要查看属于每个类别的单位以及可与 CONVERT 函数的"from unit"和"to unit"参数一起使用的单位，请分别输入"from unit"和"to unit"。

=CONVERT(number, from_unit, to_unit)

其中，

number − 需要转换的数值。
from_unit − 数字的初始测量单位。
to_unit − "number"将转换为的单位

英寸到毫米的转换

选择一个单元格(本例中为 C2)，然后将下面提供的公式粘贴或输入到该单元格中，然后按 Enter 键。首先，选择包含此结果的单元格，然后使用自动填充手柄将其向下移动。这将显示剩余的结果。

=CONVERT(A2,"in","mm")

步骤 3

选择一个单元格(本例中假设为 D2)，然后将下面提供的公式粘贴或输入到该单元格中，然后按 Enter 键。首先，选择包含此结果的单元格，然后使用自动填充手柄将其向下移动。这将显示剩余的结果。

=CONVERT(A2,"in","cm")

步骤 4

选择一个单元格(本例中假设为 E2)，然后将下方提供的公式粘贴或输入到该单元格中，然后按 Enter 键。首先，选择包含此结果的单元格，然后使用自动填充手柄将其向下移动。这将显示剩余的结果。

=CONVERT(A2,"in","ft")

文本和数字

如果您想在 Excel 中将数字显示为英语单词，以下用户定义函数可能会对您有所帮助。

步骤 1

要启动 Microsoft Visual Basic for Applications 窗口，请按住 Alt 键并同时按下 F11 键。或者前往开发者菜单，选择Visual Basic。

之后，会打开Microsoft Visual Basic for Applications。然后前往插入，选择模块。

复制并粘贴以下 VBA 代码。

Function NumberstoWords(ByVal MyNumber)
  'Update by Extendoffice 20220516
   Dim xStr As String
   Dim xFNum As Integer
   Dim xStrPoint
   Dim xStrNumber
   Dim xPoint As String
   Dim xNumber As String
   Dim xP() As Variant
   Dim xDP
   Dim xCnt As Integer
   Dim xResult, xT As String
   Dim xLen As Integer
   On Error Resume Next
   xP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
   xNumber = Trim(Str(MyNumber))
   xDP = InStr(xNumber, ".")
   xPoint = ""
   xStrNumber = ""
   If xDP > 0 Then
      xPoint = " point "
      xStr = Mid(xNumber, xDP + 1)
      xStrPoint = Left(xStr, Len(xNumber) - xDP)
      For xFNum = 1 To Len(xStrPoint)
         xStr = Mid(xStrPoint, xFNum, 1)
         xPoint = xPoint & GetDigits(xStr) & " "
         Next xFNum
         xNumber = Trim(Left(xNumber, xDP - 1))
   End If
   xCnt = 0
   xResult = ""
   xT = ""
   xLen = 0
   xLen = Int(Len(Str(xNumber)) / 3)
   If (Len(Str(xNumber)) Mod 3) = 0 Then xLen = xLen - 1
      Do While xNumber <> ""
      If xLen = xCnt Then
         xT = GetHundredsDigits(Right(xNumber, 3), False)
      Else
         If xCnt = 0 Then
            xT = GetHundredsDigits(Right(xNumber, 3), True)
         Else
            xT = GetHundredsDigits(Right(xNumber, 3), False)
         End If
      End If
      If xT <> "" Then
         xResult = xT & xP(xCnt) & xResult
      End If
      If Len(xNumber) > 3 Then
         xNumber = Left(xNumber, Len(xNumber) - 3)
      Else
         xNumber = ""
      End If
   xCnt = xCnt + 1
   Loop
   xResult = xResult & xPoint
   NumberstoWords = xResult
End Function
Function GetHundredsDigits(xHDgt, xB As Boolean)
   Dim xRStr As String
   Dim xStrNum As String
   Dim xStr As String
   Dim xI As Integer
   Dim xBB As Boolean
   xStrNum = xHDgt
   xRStr = ""
   On Error Resume Next
   xBB = True
   If Val(xStrNum) = 0 Then Exit Function
      xStrNum = Right("000" & xStrNum, 3)
      xStr = Mid(xStrNum, 1, 1)
      If xStr <> "0" Then
         xRStr = GetDigits(Mid(xStrNum, 1, 1)) & "Hundred "
      Else
         If xB Then
            xRStr = "and "
            xBB = False
         Else
            xRStr = " "
            xBB = False
         End If
      End If
   If Mid(xStrNum, 2, 2) <> "00" Then
      xRStr = xRStr & GetTenDigits(Mid(xStrNum, 2, 2), xBB)
   End If
   GetHundredsDigits = xRStr
End Function
Function GetTenDigits(xTDgt, xB As Boolean)
   Dim xStr As String
   Dim xI As Integer
   Dim xArr_1() As Variant
   Dim xArr_2() As Variant
   Dim xT As Boolean
   xArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen
   ", "Nineteen ")
   xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
   xStr = ""
   xT = True
   On Error Resume Next
   If Val(Left(xTDgt, 1)) = 1 Then
      xI = Val(Right(xTDgt, 1))
      If xB Then xStr = "and "
         xStr = xStr & xArr_1(xI)
      Else
         xI = Val(Left(xTDgt, 1))
         If Val(Left(xTDgt, 1)) > 1 Then
            If xB Then xStr = "and "
               xStr = xStr & xArr_2(Val(Left(xTDgt, 1)))
               xT = False
            End If
            If xStr = "" Then
               If xB Then
                  xStr = "and "
               End If
            End If
            If Right(xTDgt, 1) <> "0" Then
               xStr = xStr & GetDigits(Right(xTDgt, 1))
            End If
         End If
      GetTenDigits = xStr
End Function
Function GetDigits(xDgt)
   Dim xStr As String
   Dim xArr_1() As Variant
   xArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
   xStr = ""
   On Error Resume Next
   xStr = xArr_1(Val(xDgt))
   GetDigits = xStr
End Function

请参阅下方截图。