贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

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

【準備工作】

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

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

image[3]

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

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

image[7]

 

【公式輸入】

(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))}

 

【補充說明】

COUNTIFhttp://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx

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

語法:COUNTIF(range, criteria)

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

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

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

 

SUMIFhttp://office.microsoft.com/zh-tw/excel-help/HP010342932.aspx

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

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

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

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

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

創作者介紹

學不完.教不停.用不盡

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


留言列表 (12)

發表留言
  • 訪客
  • 請問老師
    如果要計算區間內的總合
    還需要配合什麼函數才能達成

    例如:一份銷售報表
    要算0~500,501~1000,1001~1500 個區間的銷售總額~
  • 請參考文章:http://isvincent.pixnet.net/blog/post/43046200

    vincent 於 2014/03/10 21:07 回覆

  • 巫小柔
  • 請問老師:
    我的excel表格內容是用驗證-清單,篩選出來的,那之些篩選出來的可以使用COUNTIF函數計算,其內容文字數量嗎?我怎麼用都是等於0~~請教老師怎麼解?
  • verna0072001
  • 好像是按ctrl+shift +F3
  • 很感謝您,已更正。

    vincent 於 2015/11/25 14:08 回覆

  • 訪客
  • 請問sumif,計算加總的是數字,條件是1或2或3或空白,但請問空白" "要怎算設定才可計只sumif,條件是空白 的總數?謝謝
  • 訪客
  • 如果數值是1~200,每區間是25為一範圍( 例如1~25,26~50)若其中沒有1的數值(顯示2-26)要如何使用函數設定
  • 訪客
  • 您好,請問sum_range,可以計算(選取)兩欄(含以上)的值嗎?
    因為現在我有一資料是要分別計算男女的年資(小於1年、1年、2年...10年),其年資已分欄位用if函數分別以0或1表示了,現在則是要用另張表表示男生小於1年有幾人?1年-2年有幾人..大於10年有幾人? 那 小於1年者,用sumif函數是OK的,至於1年-2年,就發現在sum_range這邊無法計算兩欄位,目前是用sumif(....)+sumif(...)這樣處理,想問加總範圍能否有更簡短的方式能學習。謝謝
  • 請參考:http://isvincent.pixnet.net/blog/post/47011954

    vincent 於 2017/02/03 14:18 回覆

  • 悄悄話
  • wkf
  • 請問一下副座標軸的問題

    我有四組資料分別是A,a B,b
    四個資料要一起做成圖表,AB做直條圖,ab做折線圖
    因為AB和ab的數值差異較大,所以ab做副座標軸的折線圖
    但是我現在AB沒問題,但是ab的折線圖卻是「合計」
    比如說a是5,b是3,a的位置是5沒錯,但是b卻會變成「8」(應該是3才正確)
    請問要如何修正?
  • 一頁書
  • 站長請教:
    如要做成如下畫面,編號欄,該如何設公式

    同一個名字,編號(流水號)相同,
    一旦不同人時,編號由上面+1,

    我印象中書上曾用COUNTIF,
    但忘了該怎麼設定。

    謝謝!

    編號 姓名
    1 范群賢
    1 范群賢
    1 范群賢
    1 范群賢
    1 范群賢
    1 范群賢
    2 陳名人
    2 陳名人
    2 陳名人
    2 陳名人
    2 陳名人
    2 陳名人

  • 儲存格A1:1
    儲存格A2:=IF(B2<>B1,A1+1,A1)

    vincent 於 2017/06/08 22:07 回覆

  • 一頁書
  • 感謝解答!

    竟然只用IF即可!不需用到COUNTIF。
    看來是我記錯了,謝謝!

    A1→編號
    B1→姓名
    A2→1
    A3→=IF(B3<>B2,A2+1,A2)
  • Ashley
  • 請問老師
    如果想要回傳儲存格內的數值,該用哪個函數
    例如:在儲存格範圍G2:M20中搜尋大於A3的數值
    (G2:M20範圍內是隨機亂數)
    想要在這個範圍內搜尋大於A3的最小值
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼