Excel-對含有文數字的儲存格執行小計(SUBSTITUTE)

在Excel 中有可能有些儲存格含有文字數，如何取出數字來執行小計呢？(參考下圖) 【準備工作】 選取儲存格A1:B25，按一下Ctrl+Shift+F3 鍵， ... 學不完．教不停．用不盡 跳到主文 分享個人電腦教學和回答網友提問 (Google,Windows,雲端應用) (Excel,Word,PowerPoint) 部落格全站分類：數位生活 相簿 部落格 留言 名片 贊助廠商 Jan17Thu201316:17 Excel-對含有文數字的儲存格執行小計(SUBSTITUTE) 在Excel中有可能有些儲存格含有文字數，如何取出數字來執行小計呢？(參考下圖) 【準備工作】 選取儲存格A1:B25，按一下Ctrl+Shift+F3鍵，定義名稱：內容、數字。

  【輸入公式】 (1)取出儲存格中的數字 儲存格B2：=VALUE(SUBSTITUTE(SUBSTITUTE(內容,"[左]",""),"[右]","")) 使用SUBSTITUTE函數將「[左]、[右]」字串以空白取代，再以VALUE函數將文字轉換為數字，便於後續計算。

選取儲存格B2，往下各列貼上。

(2)計算小計 儲存格E2：=SUMPRODUCT((LEN(SUBSTITUTE(內容,"[",""))=LEN(內容))*數字) LEN(SUBSTITUTE(內容,"[",""))=LEN(內容)：找出不含「[」字元者的陣列。

透過SUMPRODUCT函數將上式陣列對應的數字，透過「*」執行合計。

儲存格E3：=SUMPRODUCT((LEN(SUBSTITUTE(內容,"[左]",""))<>LEN(內容))*數字) LEN(SUBSTITUTE(內容,"[左]",""))<>LEN(內容)：找出含有「[左]」字元者的陣列。

儲存格E4：=SUMPRODUCT((LEN(SUBSTITUTE(內容,"[右]",""))<>LEN(內容))*數字) LEN(SUBSTITUTE(內容,"[右]",""))<>LEN(內容)：找出含有「[右]」字元者的陣列。

  【補充資料】 相關之詳細函數說明，請參閱微軟網站： SUBSTITUTE：http://office.microsoft.com/zh-tw/excel-help/HP010342927.aspx SUBSTITUTE：將文字字串中的old_text部分以新字串new_text取代。

語法：SUBSTITUTE(text,old_text,new_text,[instance_num]) text：要取代字元的文字，或含有該文字之儲存格的參照。

old_text：這是要取代的文字。

new_text：是要用來取代old_text的文字。

instance_num：指定要將第幾個old_text取代為new_text。

  全站熱搜 創作者介紹 vincent 學不完．教不停．用不盡 vincent發表在痞客邦留言(2)人氣() 全站分類：數位生活個人分類：講義資料此分類上一篇：Excel-指定組距計算小計(OFFSET) 此分類下一篇：Excel-計算不含六日的天數(NETWORKDAYS,WEEKDAY) 上一篇：Excel-指定組距計算小計(OFFSET) 下一篇：Excel-計算不含六日的天數(NETWORKDAYS,WEEKDAY) 歷史上的今天 2016:Excel-14個樞紐分析表應用練習 2015:Excel-自行調整小數位數的內容(INT) 2015:Excel-根據等第評比進步與否(MATCH,CHOOSE) 2013:Excel-計算不含六日的天數(NETWORKDAYS,WEEKDAY) 2011:Excel-表示面積大小的圖表(泡泡圖) 2010:Excel-計算文字筆劃 ▲top 留言列表 發表留言 到站人數 本日人氣： 累積人氣： 內建文章關鍵字搜尋 回到頁首 回到主文 免費註冊 客服中心 痞客邦首頁 ©2003-2021PIXNET 關閉視窗 PIXNET Facebook Yahoo! Google MSN {{guestName}} (登出) 您尚未登入，將以訪客身份留言。

亦可以上方服務帳號登入留言 請輸入暱稱(最多顯示6個中文字元) 請輸入標題(最多顯示9個中文字元) 請輸入內容(最多140個中文字元) 請輸入左方認證碼： 看不懂,換張圖 請輸入驗證碼 送出留言