贊助廠商

///文章列表///

在 Excel 中有一個資料表(如下圖),其中的項目由多類內容組合而成(例如:A、B、C、D等),其中以「,」分隔,現在要求取各個內容的個數和總和,該如何處理?

【準備工作】

選取儲存格B1:C22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、數值。

【輸入公式】

(1) 計算含有某內容的個數

儲存格F2:=SUMPRODUCT((NOT((SUBSTITUTE(項目,E2,"")=項目)))*1)

SUBSTITUTE(項目,E2,""):將項目欄位中的含有儲存格E2內容置換為空白("")。

SUBSTITUTE(項目,E2,"")=項目:判斷置換後的內容是否和原內容相同(表示儲存格中不含有該內容),若是則傳回 True,若不是則傳回 False,得到一組 True/False 的陣列。

NOT((SUBSTITUTE(項目,E2,"")=項目)):以 NOT 函數,將上式中的 True/False 的陣列,轉換為 Fasle/True 的陣列。

(NOT((SUBSTITUTE(項目,E2,"")=項目)))*1:將上式的 False/True 陣列,轉換為 0/1 陣列。

透過 SUMPRODUCT 函數,將上式的 1 和 0 加總,即為所求。

 

(2) 計算含有某內容的總和

儲存格G2:=SUMPRODUCT(NOT((SUBSTITUTE(項目,E2,"")=項目))*數值)

原理同(1),只要將(1)中的「*1」改成「數值」,即為所求。

 

【補充資料】

相關函數說明,請參考微軟網站:

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

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

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

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

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

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

 

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

在 Excel 中有一個含有日期、人員、數值欄位的工作表(如下圖,其中的星期欄位是一個輔助說明的欄位),想要求取某些條件下的數值的最大值,該如何處理?

【準備工作】

選取儲存格A1:D30,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、人員、數值。

【輸入公式】

(一) 各個人員的最大值

儲存格G2:{=MAX(IF(人員=F2,數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G2,貼至儲存格G2:G5。

IF(人員=F2,數值,):因為採用陣列公式,可以求得在人員欄位中符合儲存格F2(甲)的數值陣列。

IF 公式中的第三個參數為空白,可以讓求得的結果為「空白」;如果填入 0 或是 Fasle,則結果會顯示「0」。

最後藉由 MAX 函數,將求得的數值陣列中取最大值,即為所求。

 

(二) 各個星期幾的最大值

儲存格G8:{=MAX(IF(WEEKDAY(日期,1)=ROW(1:1),數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G8,貼至儲存格G8:G14。

原理同(一)。

WEEKDAY(日期,1):WEEKDAY 函數可以求得一個星期幾對應的數值。本例中選取「1」,所以對應ROW(1:1)=1,因此可以求得星期日的數值陣列。若往下複製公式時,ROW(1:1)→ROW(2:2)→ROW(3:3)→ …,如此可以求得各個星期幾對應的數值陣列。

image

 

(三) 各個月份的最大值

儲存格G17:{=MAX(IF(MONTH(日期)=ROW(4:4),數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格G17,貼至儲存格G17:G20。

原理同(一)和(二)。

IF(MONTH(日期)=ROW(4:4),數值,):透過 MONTH 函數取得日期中的月份,而 ROW(4:4)=4,即求得 4 月份的數值陣列。

 

(四) 各個月份中某個人員的最大值

儲存格G23:{=MAX(IF((MONTH(日期)=ROW(4:4))*(人員="甲"),數值,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。(以下亦同)

儲存格G24:{=MAX(IF((MONTH(日期)=ROW(5:5))*(人員="乙"),數值,))}

儲存格G25:{=MAX(IF((MONTH(日期)=ROW(6:6))*(人員="丙"),數值,))}

儲存格G26:{=MAX(IF((MONTH(日期)=ROW(7:7))*(人員="丁"),數值,))}

原理同(一)、(二)和(三)。

IF((MONTH(日期)=ROW(4:4))*(人員="甲"):在 IF 函數中使用雙條件運算,其中的「*」運算子,相當於將條件做 AND 運算。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼