1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. 条件を満たす値のカウントや合計をFILTER関数で計算する

条件を満たす値のカウントや合計をFILTER関数で計算する:Excel関数

• このページではFILTER関数を使って条件付きの合計や条件付きでカウントする方法を書いてみます。
  FILTER関数はデータを抽出して書き出すだけではなく、計算にも利用できます。従来のSUMIFやSUMIFS、COUNTIFやCOUNTIFS関数の代わりになりえると思っています。

2021/10/13

FILTER関数が利用できるExcelの種類(バージョンについて)

• FILTER関数がExcel for Microsoft 365 (サブスクリプション型)で使えるようになりました。
  (Office 365は2020/4/22からは Microsoft 365となりました)
  ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
• なお、2021/10/5頃から市販されている Excel2021(永続ライセンス版)では使用できるようです。
• FILTER関数の詳細な使い方は　FILTER関数　をご覧ください。

定義した条件に基づいてデータの範囲をフィルター処理できます。　　　topへ
　フィルター
=FILTER(配列,含む,[空の場合])

• =FILTER(配列,含む,[空の場合])
  • 引数の表現(日本語)がわかりにくいのでよく見てください。
    

    配列	フィルターするデータの範囲または配列
    含む	フィルターする条件を書きます(フィルターで残すデータの条件を書きます)
    空の場合	(省略可)フィルターした後にデータがない場合に表示するテキスト (#CALC! エラーとなるときに表示する文字列を指定します)

FILTER関数で条件付きの合計を求める

• 【問題1】性別が「男」で血液型が「A」の得点の合計を求めなさい。　　問題1の解答例
  【問題2】年齢が30歳代の得点の合計を求めなさい。　　　問題2の解答例
  【問題3】「女」の「B」または「男」の「A」の得点の合計を求めなさい。　　問題3の解答例
  

  	B	C	D	E	F	G
  2	番号	氏名	性別	年齢	血液型	得点
  3	1	上原嘉男	男	30	O	91
  4	2	森永彩芽	女	28	A	98
  5	3	古田恵	女	26	A	61
  6	4	太田千恵子	女	21	B	46
  7	5	豊田啓一	男	38	B	78
  8	6	新村遥奈	女	39	A	76
  9	7	坂元彩香	女	29	B	68
  10	8	坪井尚生	男	33	A	77
  11	9	西原舞	女	35	B	41
  12	10	中野野乃花	女	23	A	93
  13	11	岩渕佳代	女	39	A	61
  14	12	市村将文	男	24	A	66
  15	13	芦田公平	男	31	A	90
  16	14	高見美姫	女	27	A	81
  17	15	高山晴彦	男	23	B	76
  18	16	高島嘉子	女	24	A	85
  19	17	蛭田功一	男	32	A	60
  20	18	北奈那	女	32	B	84
  21	19	本田明莉	女	29	A	66
  22	20	東海林真依	女	30	A	92

【問題1】性別が「男」で血液型が「A」の得点の合計を求めなさい。

• 条件が一つの場合、性別が「男」の場合は　=SUM(FILTER(G3:G22,D3:D22="男"))　で求めることができます。
  従来のSUMIF関数では　=SUMIF(D3:D22,"男",G3:G22)　とします。
  
• 条件が二つの場合、性別が「男」And　血液型が「A」の場合は　=SUM(FILTER(G3:G22,(D3:D22="男")*(G3:G22="A")))　で求めることができます。
  AND条件なので、条件は論理積 (D3:D22="男")*(G3:G22="A")　とします。
  従来のSUMIFS関数では　=SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A")　とします。
  

【問題2】年齢が30歳代の得点の合計を求めなさい。

• 合計の対象範囲は　G3:G22　、条件は30歳代ということですので、30以上　40未満を条件とします。
  数式は　=SUM(FILTER(G3:G22,(E3:E22>=30)*(E3:E22<40)))　となります。
  SUMIFS関数の場合は　=SUMIFS(G3:G22,E3:E22,">=30",E3:E22,"<40")　とします。
  

【問題3】「女」の「B」または「男」の「A」の得点の合計を求めなさい。

• 「女」の「B」または「男」の「A」という条件は OR条件なので論理和　+　で表現することができます。
  ((D3:D22="女")*(F3:F22="B"))+((D3:D22="男")*(F3:F22="A"))
  数式は　=SUM(FILTER(G3:G22,((D3:D22="女")*(F3:F22="B"))+((D3:D22="男")*(F3:F22="A"))))　としました。
• SUMIFS関数の場合は
  「女」の「B」または「男」の「A」の条件を一つにまとめることができませんので、それぞれの条件で求めてから合計します。
  「女」の「B」は　SUMIFS(G3:G22,D3:D22,"女",F3:F22,"B")　
  「男」の「A」は　SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A")
  でそれぞれ求めることができます。
  よって数式は　=SUMIFS(G3:G22,D3:D22,"女",F3:F22,"B")+SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A")　としました。
  合計はSUM関数で求めることができますので、　=SUM(SUMIFS(G3:G22,D3:D22,"女",F3:F22,"B"),SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A"))　とすることもできます。
• 
• Excel for Microsoft365 では動的配列数式を使えます。これを使うと FILTER関数も不要です。(;^_^A
  I3セルの数式を　=SUM((D3:D22="女")*(F3:F22="B")*G3:G22+(D3:D22="男")*(F3:F22="A")*G3:G22)　とすることができます。
  

FILTER関数で条件付きでカウントする

• 上記のデータリストを使って以下の計算をしてください。
  【問題4】性別が「男」で血液型が「A」の人数をカウントしなさい。　　問題4の解答例
  【問題5】年齢が30歳代の人数をカウントしなさい。　　　問題5の解答例
  【問題6】「女」の「B」または「A」の人数をカウントしなさい。　　問題6の解答例

【問題4】性別が「男」で血液型が「A」の人数をカウントしなさい。

• 条件が一つの場合、性別が「男」の場合は　=COUNTA(FILTER(D3:D22,D3:D22="男"))　で求めることができます。
  FILTER関数のフィルターする配列を D3:D22 と性別(文字列)にしているので、フィルターされた性別を　COUNTA関数でカウントします。
  従来のSUMIF関数では　=COUNTIF(D3:D22,"男")　とします。
  
• 条件が二つの場合、性別が「男」And　血液型が「A」の場合は　=COUNTA(FILTER(C3:C22,(D3:D22="男")*(G3:G22="A")))　で求めることができます。
  AND条件なので、条件は論理積 (D3:D22="男")*(G3:G22="A")　とします。
  COUNTIFS関数では　=COUNTIFS(D3:D22,"男",F3:F22,"A")　とします。
  

【問題5】年齢が30歳代の人数をカウントしなさい

• 抽出する対象範囲は　E3:E22　としました。抽出条件は30歳代ということですので、30以上　40未満を条件とします。
  数式は　=COUNT(FILTER(E3:E22,(E3:E22>=30)*(E3:E22<40)))　となります。
  ここでは、抽出する(フィルターする)データは年齢(数値)なので COUNT関数でカウントします。
  COUNTIFS関数の場合は　=COUNTIFS(E3:E22,">=30",E3:E22,"<40")　とします。
  

【問題6】「女」の「B」または「A」の人数をカウントしなさい。

• 「女」の「B」という条件は AND条件なので 論理積　(D3:D22="女")*(F3:F22="B")
  「女」の「A」という条件は AND条件なので 論理積　(D3:D22="女")*(F3:F22="A")
  この2つの条件のOR条件が求めるものになるので論理和　((D3:D22="女")*(F3:F22="B"))+((D3:D22="女")*(F3:F22="A"))
  これをまとめると　(D3:D22="女")*((F3:F22="B")+(F3:F22="A"))　とすることができます。
• COUNTIFS関数の場合は
  「女」の「B」または「女」の「A」の条件を一つにまとめることができませんので、それぞれの条件で求めてから合計します。
  「女」の「B」は　COUNTIFS(D3:D22,"女",F3:F22,"B")
  「女」の「A」は　COUNTIFS(D3:D22,"女",F3:F22,"A")
  でそれぞれ求めることができます。
  よって数式は　=COUNTIFS(D3:D22,"女",F3:F22,"B")+COUNTIFS(D3:D22,"女",F3:F22,"A")　としました。
• 

スポンサードリンク

PageViewCounter

Since2006/2/27