贊助廠商

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

搜尋本部落格文章資料

參考下圖,網友想要在 Excel 的工作表中找出同時是會員也是理事的人有幾個。這是常見的找出符合多條件計算個數的例子,只要使用 SUMPRODUCT 函數即可搞定。以下分別使用二種不同的資料呈現方式,再透過 SUMPRODUCT 函數來求解。

選取儲存格B1:C27,按 Ctrl+Shift +F3 鍵,勾選「頂端列」,定義名稱:會員別、職務。

根據上圖計算個數

儲存格E5:=SUMPRODUCT((會員別="會員")*(職務="理事"))

若儲存格內容,改成下圖。

選取儲存格B1:B27,按 Ctrl+Shift +F3 鍵,勾選「頂端列」,定義名稱:會員職務。

儲存格D5:=SUMPRODUCT((會員職務="會員,理事")*1)

試比較以上二個圖中的資料呈現方式,下圖如果不是使用「會員,理事」,而是使用「理事,會員」,則上述公式將會出錯。可以稍加修改公式:

儲存格D5:=SUMPRODUCT((會員職務="會員,理事")*1)+
SUMPRODUCT((會員職務="理事,會員")*1)

 

【思考一下】

(1) 若是條件變為三個、四個,你是否也會處理公式?

(2) 公式中(會員別="會員")*(職務="理事"),其中的「*」意義為何?

(3) 公式中的「*1」意義為何?

(4) 公式:=SUMPRODUCT((會員職務="會員,理事")*1)+SUMPRODUCT((會員職務="理事,會員")*1),改成=SUMPRODUCT((會員職務="會員,理事")*1+(會員職務="理事,會員")*1),結果是否一樣?

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (8)

發表留言
  • Blue
  • 謝謝達人的指導
  • Blue
  • office 公式說明對 sumproduct 的說明很簡短.....所以只看得懂對數值計算的使用...
    所以也搞不懂這個函數為何能計算非數字的內容。只知道會將所有非數值資料的陣列項目當成 0 來處理。
    而 SUMPRODUCT((會員別="會員")*(職務="理事")) 中..
    也很不解它為何不需要 IF 來判別 (會員別="會員") , 也不懂 * 是代表 乘 或是 and 的意思。
    微軟對公式的用法不是很大眾化......我想這是很多使用者的困難點...
  • Blue
  • 因此 胡亂猜測公式的意義 如下:
    在 SUMPRODUCT((會員別="會員")*(職務="理事")) 中:
    (會員別="會員") 中 ()的作用就是判斷 True 或 False . 當 兩個條件都是 true 時, 就會產生 1*1 的結果 ( 因為猜它 ()內是True時就傳回 1) , 然後 公式會循列加總出 所有1*1的結果 , 也就等於是加總了 所有列 同時符合 會員別="會員" 與 職務="理事" 出現的次數.
    而*1 就是把 符合 ture 的結果數值化 而能讓公式加總符合條件的次數.
  • 您的想法是對的。(會員別="會員")*(職務="理事")其中的「*」在邏輯運算中相當於執行AND運算。

    vincent 於 2015/05/05 09:48 回覆

  • Blue
  • 我猜 :思考一下 4 的答案是不一樣 因為改變過後的 ()內引數的寫法應該不符EXCEL的規定
  • (1)公式:=SUMPRODUCT((會員職務="會員,理事")*1)+SUMPRODUCT((會員職務="理事,會員")*1)
    改成
    (2)公式:=SUMPRODUCT((會員職務="會員,理事")*1+(會員職務="理事,會員")*1)
    結果會一樣。
    其中(2)內的「+」,相當於執行邏輯OR運算。

    vincent 於 2015/05/05 09:55 回覆

  • Blue
  • 報告達人 : 我發覺以我的作法 ,若使用 sumproduct , 也是要寫長串公式.

    (原來的留言版 主題編號是 339) 我想我沒把問題寫清楚
    我把實際的檔案放在估狗雲端 . https://drive.google.com/file/d/0By5CPqOTbbKeb212MWxUMkYwam8/view?usp=sharing
    ----------------------------------------------------
    今把問題整理好...
    我有一個 名為 職稱 的欄位 , 欄位內容共有 十幾個不同的文字值.....
    如 :
    第一二屆理事長
    第三四屆理事長
    第五屆理事長
    第六屆理事長
    第七八屆理事長
    榮譽會員
    總教練
    常務理事
    理事
    副理事長
    會員
    理事
    榮譽理事
    顧問
    後備理事
    .......
    .......
    ------------
    今想 在欄位的最後 做一個統計.... 而統計是希望要 加總符合條件的儲存格個數...
    原本是用 countif 來做... 但是當我要的符合條件很多時 , 如 10 個 , 就需要 寫10個 countif 來相加... 這樣公式會變很長 也容易錯. 能不能有其他不要這麼笨的方法?
  • 我了解了你的意思,依你的需求改寫了公式,請參考:http://isvincent.pixnet.net/blog/post/44810614

    vincent 於 2015/05/05 11:03 回覆

  • 訪客
  • 請問若是多個兩行要進行加總可否 問卷結果共10題同時符合4和1的條件
  • 因為已先定義名稱,所以不管原始資料有幾欄,都是可以的。
    如果你要增加符合的條件,則修改類似這樣:
    =SUMPRODUCT((會員別="會員")*(職務="理事")*(YY="XX")*(PP="QQ"))

    vincent 於 2017/03/28 09:05 回覆

  • 訪客
  • 我想請問一下 假設我有一份整年的資料 假設時間那欄有(年/月/日/小時/分)
    而我只要符合某月某日就可以了 該怎麼修改呢?
    就是只要年/月/日符合就算符合條件的意思
  • 悄悄話

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼