2013年1月23日 星期三

Excel-計算項目個數(區隔數字和文字)(SUMPRODUCT)

有網友問到如下圖的數值儲存格和文字儲存格,如何分別計算其符合的個數?

【準備工作】

選取儲存格A1:A26,按一下 Ctrl+Shift+F3 鍵,定義名稱:數值。這是由數字組成的儲存格。

選取儲存格F1:F26,按一下 Ctrl+Shift+F3 鍵,定義名稱:文字。這是由文字組成的儲存格。

【輸入公式】

(1) 儲存格I2:=SUMPRODUCT((LEFT(文字,3)=LEFT(H2,3))*1)

LEFT(文字,3)=LEFT(H2,3):判斷文字陣列儲存格中取出前3個字和儲存格H2的前3個字比較是否相符。

使用 SUMPRODUCT 函數計算上式判斷為 True 的數量。(True=1、Fasle=0)

複製儲存格I2,往下各列貼上。

(2) 儲存格D2:=SUMPRODUCT((TEXT(LEFT(數值,3),"######")=LEFT(C2,3))*1)

原理同上式,因為「數值」名稱中的儲存格都是數值內容,所以在使用時先以 TEXT 函數轉換為文字,否則其結果為 0。

複製儲存格D2,往下各列貼上。

 

【補充資料】

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

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

好康東東