跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

如何在Excel中缩写数字？

Author Sun Last modified

在Excel中处理大型数据集时，经常会遇到大数字，这些数字可能会使您的工作表显得杂乱无章，并使数据更难阅读或分析。例如，像1,234,567或12,000,000这样的数值会让查看者感到不知所措，尤其是在仪表板或汇总报告中。在这种情况下，缩写数字（例如将1234567转换为1.23M或将12000转换为12K）有助于提高清晰度并保持紧凑的布局。本教程提供了几种在Excel中缩写数字的实用解决方案，包括内置格式、公式和VBA宏。每个选项都适用于不同的场景，具体取决于您的偏好和工作流程要求。

sample data arrow abbreviate numbers

缩写数字 | 使用Excel公式缩写数字 | 使用VBA宏缩写数字

arrow blue right bubble 缩写数字

一种在Excel中缩写数字的简单方法是通过“设置单元格格式”对话框自定义数字格式。此方法仅更改数字的显示方式，而不改变其基础值，这意味着使用这些单元格进行的计算仍然准确。对于快速报告或需要数字自动刷新而无需添加额外列的情况特别有用。但是，请记住，这种方式不会提供文本结果，也不允许在同一列中混合单位（如M和K）使用复杂逻辑——公式或宏更适合这种情况。

1. 选择要缩写的数字范围。然后，在选定范围内右键单击任意位置，并从上下文菜单中选择“设置单元格格式”。

click format cells from right click menu

2. 在“设置单元格格式”对话框中，转到“数字”选项卡，从左侧列表中选择“自定义”，并在“类型”字段中输入：[>999999]#,,"M";#,"K" 此自定义格式告诉Excel将大于999,999的数字显示为百万（带“M”），并将所有其他数字显示为千（带“K”）。请参见截图：

set data type

3. 单击“确定”以应用格式。现在，您选定范围内的数字将以缩写形式显示，这使得大型数据集更容易扫描和一目了然地解释。

result of abbreviating numbers

提示：如果您只需要将所有大数字显示为千 (“K”)，则在“类型”框中使用#,"K"。对于百万，使用#,,"M"。对于十亿或万亿，分别使用#,,,"B" 或 #,,,,"T"。如果要同时显示百万和十亿并附带正确的单位，请使用[>999999999.999]#,,,"B";[>999999.999]#,,"M";#,##0 _M。请注意，数字格式仅用于显示，不会改变用于计算的值。如果您需要更自定义的逻辑，例如四舍五入、显示小数位或处理负数，请考虑使用公式方法或VBA宏。

注意事项：如果您的数据包含小于1,000的值，它们将显示为原始数字，不进行缩写。此方法无法在同一单元格上下文中组合M和K，除非通过扩展的自定义格式，但这种方法有局限性。请注意，格式化的数字仍会在底层公式中使用原始值，这可能导致在复制或导出数据时产生混淆。

使用Excel公式缩写数字（将转换为文本）

如果您希望在一个新列中显示缩写结果并需要自定义逻辑，例如显示小数（1.25M）、处理混合单位（K、M、B、T）或控制结果作为文本，则可以使用Excel函数生成动态缩写。这也非常适合当您想要冻结或在其他公式或输出中操作显示的文本时。

适用场景：当您需要文本输出或高级缩写逻辑（不被自定义格式支持）时，此方法最佳。它适合摘要、仪表板或在共享导出/粘贴结果时使用。

1. 在目标单元格中输入以下公式（例如，如果您的数字在单元格A2中，请在单元格B2中输入）：

=IF(ABS(A2)>=1000000000,TEXT(A2/1000000000,"0.00")&"B",IF(ABS(A2)>=1000000,TEXT(A2/1000000,"0.00")&"M",IF(ABS(A2)>=1000,TEXT(A2/1000,"0.00")&"K",A2)))

该公式检查A2中的值，并适当使用B（十亿）、M（百万）或K（千）进行缩写，显示两位小数。支持负数，小于1,000的值按原样显示。

2. 按Enter键确认公式。如果要将其应用于更多行，请向下拖动填充柄或将公式复制到其他单元格。

实用技巧：要自定义小数位数，请调整TEXT函数中的“0.00”部分（例如，使用“0.0”表示一位小数）。- 如果处理非常大的数字（万亿或以上），进一步扩展IF模式。

注意事项：- 这会创建文本输出，因此除非转换回数字，否则SUM或其他数学公式中的数字将无法正确计算。如果要避免尾随零（例如，显示“12M”而不是“12.00M”），请在TEXT内使用自定义格式。

使用VBA宏缩写数字

对于希望在整个范围内完全自动缩写数字或应用不容易通过公式或数字格式处理的自定义规则的用户，您可以利用Excel VBA宏。这种方法允许就地替换或添加带有缩写结果的新列，适用于重复任务或处理大型表格。

最适合：在大型或动态范围内自动缩写；定制、可重复使用的流程；变量数据布局的批处理。

1. 首先，按Alt + F11打开Excel中的VBA编辑器。在VBA窗口中，点击“插入”>模块以创建一个新模块，并将以下代码复制到模块窗口中：

Sub AbbreviateNumbersAddColumn()
    Dim rng As Range
    Dim cell As Range
    Dim destCol As Range
    Dim abbrevValue As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range of numbers to abbreviate", xTitleId, rng.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    Set destCol = rng.Offset(0, 1).Columns(1)
    
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If Abs(cell.Value) >= 1000000000 Then
                abbrevValue = Format(cell.Value / 1000000000, "0.00") & "B"
            ElseIf Abs(cell.Value) >= 1000000 Then
                abbrevValue = Format(cell.Value / 1000000, "0.00") & "M"
            ElseIf Abs(cell.Value) >= 1000 Then
                abbrevValue = Format(cell.Value / 1000, "0.00") & "K"
            Else
                abbrevValue = cell.Value
            End If
        Else
            abbrevValue = cell.Value
        End If
        
        destCol.Cells(cell.Row - rng.Row + 1, 1).Value = abbrevValue
    Next
    
    MsgBox "Abbreviated values written to the column to the right of the selection."
End Sub

2. 要运行代码，请关闭VBA窗口。在Excel中，选择要缩写的数字范围，然后按F5键 或单击“运行”。宏将提示您选择一个范围，然后将缩写结果写入原始范围右侧的列中。

使用技巧：如果您想保留原始数据，结果将出现在新的相邻列中，这是安全的。- 您可以修改代码以覆盖原有内容，但不建议这样做，除非您有备份。

故障排除提醒：确保您的工作簿中启用了宏。如果您的数据包含文本或空单元格，宏将保持这些不变。对于自定义标签（例如，显示“T”代表万亿），相应调整If块。

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）……
15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言！

通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……

Office Tab为Office带来多标签界面，让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠