這次要來練習 COUNTIF 和 SUMIF 公式。

【準備工作】

將各欄位資料設定名稱以方便在公中使用。

選取資料範圍(本例為儲存格A1:A25)，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項。定義四個名稱：「序號、姓名、日期、數量」。

選取儲存格F6，選取［資料/資料工具］中的「資料驗證」按鈕。在［儲存格允許］中選取「清單」，在來源中輸入：「=INDIRECT(“日期")」。

儲存格F6即可以下拉式清單方式選取日期：

 

【公式輸入】

(1) 儲存格F2：=COUNT(D2:D10)

計算儲存格D2:D10中的數字個數。

(2) 儲存格F3：=COUNTIF(數量,">200")

計算「數量」欄位中大於200的個數。

(3) 儲存格F4：=COUNTIF(姓名,"陳*")

計算所有姓「陳」者的數量。

COUNTIF 和 SUMIF 函數中可以使用「?、*」萬用字元。

(4) 儲存格F5：=COUNTIF(日期,">2011/3/1")

計算「大於 2011/3/1」 的日期個數。將一個固定日期置於「" "」中來運算。

(5) 儲存格F7：=COUNTIF(日期,">"&F6)

將日期置於一個儲存格中再取用，必須以「&」來串接條件運算符號和儲存格位址。

改用陣列公式(輸入完成，要按 Ctrl +Shift+Enter 鍵)：

儲存格F6：{=COUNT(IF(日期>F6,數量,Fasle))}

(6) 儲存格F8：=SUMIF(數量,">250")

計算「數量」欄位中超過 250 者的總和。

(7) 儲存格F9：=SUMIF(日期,">"&F6,數量)

計算大於某個日期之後的數量總和。

改用陣列公式(輸入完成，要按 Ctrl +Shift+Enter 鍵)：

儲存格F6：{=SUM(IF(日期>F6,數量,FALSE))}

【延伸學習】

參考：本部落格中其他關於 Excel SUMIF 函數的應用

參考：本部落格中其他關於 Excel SUMIFS 函數的應用

參考：本部落格中其他關於 Excel COUNTIF 函數的應用

參考：本部落格中其他關於 Excel COUNTIFS 函數的應用

​​

【補充說明】

COUNTIF：http://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx

COUNTIF：計算範圍內符合您所指定單一條件準則的儲存格總數。

語法：COUNTIF(range, criteria)

range：要列入計算的一個或多個儲存格，包括數字或包含數字的名稱、陣列或參照；空白或文字值會忽略。

criteria：定義要將哪些儲存格列入計算的準則，可以是數字、表示式、儲存格參照或文字字串。

可以在準則中使用萬用字元：問號 (?) 及星號 (*)；問號可以替代任何單一字元；星號可以替代任何一系列的字元。如果確實要尋找實際的問號或星號，請在該字元前輸入波狀符號 (~)。

 

SUMIF：http://office.microsoft.com/zh-tw/excel-help/HP010342932.aspx

SUMIF：將準則套用到將進行加總的相同值上。

Range：依據準則進行評估的儲存格範圍。每個範圍中的儲存格都必須是數字，或包含數字的名稱、陣列或參照位址。空白或文字值會被忽略。

Criteria：用以定義要加總之儲存格的準則，可以是數字、表示式、儲存格參照、文字或函數。例如，criteria 可以由 32、">32"、B5、32、"32"、"apples" 或 TODAY() 來表示。

重要事項：任何文字準則或包含邏輯符號或數學符號的準則都必須使用雙引號 (") 括住。如果準則為數值，則不需要使用雙引號。

sum_range：想要加總 range 引數內指定範圍之外的儲存格，這個引數可指定實際要加總的儲存格。如果省略 sum_range 引數，則 Excel 會加總 range 引數內指定的儲存格 (即與套用準則相同的儲存格)。

 

文章標籤
全站熱搜
創作者介紹
創作者 vincent 的頭像
vincent

學不完．教不停．用不盡

vincent 發表在 痞客邦 留言(30) 人氣(540,013)