贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在 Excel 的一個資料表中,每個儲存格的內容是由字串和數字構成(參考下圖),有網友問到想要取出所有的字串來計算總和,該如何處理呢?

假設我們不使用輔助欄位來練習如何在一個儲存格中使用公式直接計算總和。

(1) 所有儲存格不含空白儲存格

觀察上圖,其中每個儲存格有共同的字串「Windows」。

儲存格A15:=SUMPRODUCT(MID(A2:A12,8,999)*1)

MID(A2:A12,8,999):取出儲存格A2:A12中的內容,由第8個字取999個字。其中,因為「Windows」占了7個字,所以由第8個字取。而參數999,是指取一個很大的數,因為每個儲存格中的數字位元數不固定。

MID(A2:A12,8,999)*1:因為 MID 函數傳回數字其實是文字型式,「*1」的運算目的是為了將文字轉換為數字。

最後使用 SUMPRODUCT 函數利用「乘積和」的概念將每個儲存格中的每個數字予以相加。

 

(2) 儲存格中部分含有空白儲存格

因為在儲存格C2:C12範圍中有一個空白的儲存格,如果使用(1)方法的公式將會產生錯誤訊息,所以要修改公式。

儲存格C15:=SUMPRODUCT(MID(C2:C12&"00000000",8,999)/100000000)

MID(C2:C12&"00000000",8,999):為了避開因為空白儲存格造成的 MID 函數傳回錯誤訊息,所以將每個儲存格中的內容右側串接「00000000」(8個0),相當於乘以100000000。經過 MID 函數處理後,空白儲存格會傳回「0」。

MID(C2:C12&"00000000",8,999)/100000000:再將取得的數字除以「100000000」,即可還原為原來的數字。

最後使用 SUMPRODUCT 函數利用「乘積和」的概念將每個儲存格中的每個數字予以相加。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • argenteagle
  • 感謝版主的教學!!!!立刻來試試看新的公式
  • Jessie
  • 請問版主:
    1.如果字串中中文,即無法使用此公式,是否有其他處理方式?
    2.如果字串中有小數點的數字,依上述方式無法被計算到,請問應如何調整公式?

    感謝您的解惑!

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼