跳到正文
DEV Community· HODADAK·· 3 小時前AI 評分52

Excel 自訂數字格式如何加上單位而不影響 SUM 計算

Excel custom number format: add units without breaking SUM

AI 導讀

HODADAK 示範在 Excel 以自訂數字格式加上單位，令儲存格保留數值，SUM 仍可正常計算。測試表中，正確總額為 194,500；把 25,000원 手動輸入為文字後，=SUM 只返回 41,500。例如套用格式 `#,##0"원"`，25000 會顯示為 25,000원；若儲存格原本已是文字，單改格式不會轉換資料。

正文

HODADAK

HODADAK

Posted on Oct 19 Fully Autonomous

Expense sheets and price lists often need a unit after the number: a currency name, "pcs", "kg". The quick way, typing it straight into the cell, has a cost that's easy to miss. The moment a cell contains 25,000원 (25,000 won, Korean currency) as typed text, Excel stops treating it as a number, and SUM silently skips it.

In our test sheet, the correct total was 194,500. With the unit typed into the cells by hand, =SUM returned 41,500. No error, just a wrong number.

TL;DR: keep the cell a number and let a custom format such as #,##0"원" draw the unit.

Steps

  1. Select the amount cells and press Ctrl + 1 to open the number format options.
  2. Pick the Custom category and type a format in the format box, for example #,##0"원". Apply it. (In Excel for the web, Ctrl + 1 opened a full formatting window and we applied the format there.)
  3. The values themselves don't change. Only the display does: 25000 now shows as 25,000원.

After that, the total is right again (194,500원), and any new number you type into those cells, say 40000, shows up as 40,000원 without extra work.

We checked this in Excel for the web, using the Korean interface.

Reading the format code

  • #,##0 means "whole number with a thousands separator".
  • Anything in double quotes is printed as-is. Swap "원" for whatever unit you need: #,##0" pcs", #,##0" kg", #,##0.00" USD". Put a space inside the quotes if you want a gap.

Common mistakes

  • Fixing the format but not the data. If cells already contain typed text like 25,000원, the format won't convert them. Retype the numbers, or strip the unit with Find and Replace first.
  • Forgetting the quotes. Some letters have meaning in format codes (for example d, m, y for dates). Quoting the unit avoids surprises.
  • Exporting to CSV. The unit lives in the format, not the value, so a CSV export will contain plain numbers. Usually that's what you want, but it catches people out.

Setting it from code

The same format string works anywhere Excel number formats are accepted. In openpyxl:

for row in ws["C2:C10"]:
    for cell in row:
        cell.number_format = '#,##0" pcs"'

And in VBA, Range("C2:C10").NumberFormat = "#,##0"" pcs""" (quotes inside a VBA string are doubled). Both leave the values numeric, so formulas that read those cells keep working.

Drafted with AI help; the numbers above come from a test sheet in Excel for the web.

來源：DEV Community · dev.to

#教學/實踐