2013年1月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(內容):找出含有「[右]」字元者的陣列。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

SUBSTITUTEhttp://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

 

沒有留言:

張貼留言

檢視其他文章

好康東東