Excel 小教室 – 當欄位中有文字時要如何加總,例如 1 元、2 元、3元

其實寫這一篇文章也是網友發問順手做筆記,需求是這樣的,他在 Excel 紀錄金額時,是用「元」為單位做結尾,但現在想要加總時利用 SUM 這函數或是全選都無法加總數字,這是因為只要有個文字在裡面,就無法用一般的方式來加總,所以今天阿湯要教大家的是,要怎麼做才能夠保留單位文字又可以進行加總,這邊我提供二種方法,有這樣需求的朋友趕緊來記一下吧。

欄位中有文字時要如何加總?

在開始之前先簡單列出二種文字格式加總後的結果,左邊的是有單位的狀況下使用 SUM 加總無法正確呈現,會得到 0,因為會被判斷為文字而不是數字,而右邊是僅有數字的情況下當然就可以正常使用 SUM。

01

阿湯提供二種方法,首先,如果你不想使用複雜的函數來達成的話,那麼我們一樣可以維持 SUM 這個函數不變,我們要修改的是欄位的儲存格格式,往後在記錄內容時,就不要再自己加上「元」這單位文字,我們利用儲存格格式來自動顯示就好,比如阿湯在數字呈現這些欄位選擇起來後右鍵進入儲存格格式。

02

選最下方的自訂,然後輸入「0元」或是其他單位文字做為結尾也可以,例如 0 天,0 個,第 0 個。

03

這麼一來當你輸入數字時,後方都會自動幫你顯示自訂單位文字,而且不影響 SUM 的加總,或是當你選取欄位時,Excel 最右下角也會正常顯示欄位數字加總。

04

方法二:

如果你的 Excel 已經記錄了好一段時間,你不想要重新去掉單位文字的話,希望可以直接加總,那麼就用阿湯提供的這個函數吧。

範例:=SUMPRODUCT(--LEFT(C1:C10,LEN(C1:C10)-1))

詳細用法就不說明,你只要修改 C1:C10 這個範圍區間,看你要加總的欄位區間是什麼就改一下,就能用了,最後面的 -1 是指加總時去掉最後的文字,所以如果你的單位是二個字就 -2,大概是這樣,大家去試看看吧,最建議還是用方法一,長期用來下來會比較方便一些。

05

更多 Office 相關教學:請點我

想看更多 Office 教學嗎?全新的影音頻道 Office 知識家上線囉,快點擊圖片前往訂閱。

office教學

你可能對這些內容也有興趣

作者:阿湯

對電腦 & 網路資訊充滿興趣與熱情、瘋了似的每日堅持發文,「阿湯」這個名字是由湯姆克魯斯而來的,雖然我沒有他帥氣,也不會演電影,但我會寫文章....

更多關於的文章》

7 個留言

  1. 翁先生表示:

    1 1元
    2 2元
    3 3元
    4 4元
    5 5元
    6 6元
    7 7元
    8 8元
    9 9元
    10 10元
    55 47
    依你公式,=SUMPRODUCT(–LEFT(C1:C10),LEN(C1:C10)-1),怎生如此

  2. Ming表示:

    太強了~

    SUMPRODUCT(–LEFT(C1:C10,LEN(C1:C10)-1))

    請問 中間的 — 是什麼用意~
    找不到 EXCEL 解譯 — 的用途~

    阿湯 說:

    這個我也有點難解釋,你一層一層分開看一下,大概會理解

  3. Steven表示:

    阿湯 你好
    請問要如何才能讓圖表的X軸指定的線變色

    阿湯 說:

    一般應該是可以直接調整線的顏色

  4. dnabym表示:

    會有這樣的疑問,是因為有類似這樣的需求:假設sheet1.2.3…有不同數值的定期更新表單,而總表的函數同欄都相同,只有滿足某條件後,自動抓取sheet”N”的數值。不知道湯哥有沒有相關頭緒可以幫忙研究看看,非常感謝

    阿湯 說:

    可能就要自己研究看看了..XDD

  5. dnabym表示:

    同樣是提取,請問要如何提取儲存格中的文字當公式的一部分使用呢?
    例如,A1格打上IF,A2格打上PRODUCT,甚至A1的IF兩個字可能來自X1&Y1&,然後B1~B10格公式想要根據需求做不同運算,如B1內容是=SUM””(C1:C10),B2內容是=SUM”A1″(C1:C10),B3內容是=SUM”A2″(C1:C10),其實這三格做的是SUM、SUMIF、SUMPRODUCT三種公式這樣呢?

    阿湯 說:

    我沒這樣試過,感覺應該不行

  6. ken表示:

    想請問一下,如果一格儲存格中有內容有金額與文字,例$230(高雄草衙道吃晚餐咖哩飯),要如何將金額與文字分別於不同儲存格中?

    阿湯 說:

    可以做,但公式會很複雜,原理大概就是找到數字位置後,再取出數字,文字也是差不多概念

  7. yk表示:

    太感人了 :'( 困擾已久的問題!

    阿湯 說:

    不客氣

發表留言 »

姓名(*)
信箱(*)