Skip to main content

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 項插入工具（插入QR码、根據路徑插入圖片等）｜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 天全功能試用 — 無需註冊、無需信用卡
  • 最超值 — 一次購買，節省單獨外掛費用