2017年5月20日 星期六

Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)

網友問到:在 Excel 的工作表中有一個資料表(如下圖),如何計算資料中的的每個項目之小數的和?
如下圖,資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。
Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)
【公式設計與解析】
選取資料欄位中有資料的所有儲存格(本例為儲存格A1:A22),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。
儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))
在 SUMPRODUCT 函數中利用二個陣列來運算:
LEFT(資料,1)=C2:在陣列公式中取出每個儲存格中的第一個字元,並判斷是否和儲存格 C2 的內容相同。傳回 TRUE/FALSE 陣列。
MID(資料,2,999):利用 MID 函數取出第一個字元以外的所有字元。(其中 999 只是一個較大的數,即使這個數字超過字串長度,仍可正常運作。)
公式中的『*』運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

沒有留言:

張貼留言

好康東東