Excel(エクセル)基本講座：条件付き合計の関数(SUMIF,SUMIFS)

条件付き合計
　サムイフ
=SUMIF(検索範囲,検索条件,合計範囲)

• SUMPRODUCT関数を用いて、複数条件により合計を求めることもできます。→SUMPRODUCT関数

【問題】下の集計表で各項目名ごとの合計を計算しなさい。

• 	B	C	D	E	F
  2	月　日	項目名	適用	収入金額	支出金額
  3	4月15日	繰越金	3月分繰越金	10,000
  4	4月19日	会費	会費(19人分)	7,600
  5	4月20日	図書費	新聞代		3,000
  6	4月25日	会費	会費(63人分)	25,200
  7	4月26日	図書費	新聞代		5,000
  8	5月10日	諸費	コピー代		4,800
  9	5月12日	図書費	本代		12,000
  10	5月18日	諸費	工具代		14,200
  11	5月20日	会費	会費(2人分)	800
  12			合計	43,600	39,000
  13
  14	項目名	収入金額		支出金額
  15	会費	33,600		0
  16	図書費	0		20,000
  17	諸費	0		19,000

【解答例】

1. セルC15に入力する数式は、検索範囲C3:C11、検索条件は『会費』、合計範囲はE3:E11　となります。
   • =SUMIF(C3:C11,"会費",E3:E11）
     （注）文字列「会費」は「"」(ダブルクォーテーション)で囲みます。
     または
     =SUMIF(C3:C11,B15,E3:E11）
2. このままでは数式をコピーすると範囲の参照がずれてしまいますので、絶対参照に変更します。
   • 検索範囲：C3:C11は固定した範囲ですので、絶対参照で『$C$3:$C$11』とします。
   • 検索条件：『会費』はB15セルの値と同一であり、この列は変わらないので、列だけ絶対参照　にし、『$B15』とします。
   • 合計範囲：E3:E11ですが、この範囲も固定なので、絶対参照で『$E$3:$E$11』となります。
   • 支払金額は1列右に動きますので、行だけ絶対参照にし、『E$3:E$11』となります。
3. =SUMIF($C$3:$C$11,$B15,E$3:E$11)
   となります。
4. セルC15の数式をC16,C17,E15,E16,E17にコピーして完成です。
   

   	B	C	D	E	F
   2	月　日	項目名	適用	収入金額	支出金額
   3	4月15日	繰越金	3月分繰越金	10,000
   4	4月19日	会費	会費(19人分)	7,600
   5	4月20日	図書費	新聞代		3,000
   6	4月25日	会費	会費(63人分)	25,200
   7	4月26日	図書費	新聞代		5,000
   8	5月10日	諸費	コピー代		4,800
   9	5月12日	図書費	本代		12,000
   10	5月18日	諸費	工具代		14,200
   11	5月20日	会費	会費(2人分)	800
   12			合計	43,600	39,000
   13
   14	項目名	収入金額		支出金額
   15	会費	=SUMIF($C$3:$C$11,$B15,E$3:E$11)		=SUMIF($C$3:$C$11,$B15,F$3:F$11)
   16	図書費	=SUMIF($C$3:$C$11,$B16,E$3:E$11)		=SUMIF($C$3:$C$11,$B16,F$3:F$11)
   17	諸費	=SUMIF($C$3:$C$11,$B17,E$3:E$11)		=SUMIF($C$3:$C$11,$B17,F$3:F$11)
   18	相対参照では次のようになります。（$を取り除いた式になります)
   19	会費	=SUMIF(C3:C11,B15,E3:E11)		=SUMIF(C3:C11,B15,F3:F11)
   20	図書費	=SUMIF(C3:C11,B16,E3:E11)		=SUMIF(C3:C11,B16,F3:F11)
   21	諸費	=SUMIF(C3:C11,B17,E3:E11)		=SUMIF(C3:C11,B17,F3:F11)

(入力方法)　【関数の挿入】ボタンを使う方法　　　　topへ

1. C15セルをクリックしてアクティブにします。
2. 関数の挿入【fx】ボタンをクリックし、表示される関数の挿入ダイアログで【SUMIF】を選択します。
3. 【関数の引数】ダイアログで、
   • 【範囲】の欄を選択し、シートのC3:C11セルを選択します。絶対参照にするため【F4】キーを押して、$C$3:$C$11 とします。
   • 【検索条件】の欄を選択し、シートのB15セルを選択します。絶対参照にするため【F4】キーを押して　$B15 とします。
   • 【合計範囲】の欄を選択し、シートの　E3:E11セルを選択します。絶対参照にするため【F4】キーを押して、$E$3:$E$11 とします。
4. 【OK】ボタンをクリックします。
   

SUMIFS関数：複数の検索条件を満たすセルの値を合計します。　Topへ
サム　イフズ
SUMIFS(合計対象範囲,検索条件範囲1,検索条件1,検索条件範囲2,検索条件2...)
　　Excel2007で追加された関数です。

• 条件は「条件1AND条件2...」のANDでの結果になります。
• （例題）下表で身長170以上の男性の身長の合計値を求めなさい。
  

  	B	C	D	E
  2	名前	性別	身長	身長170以上の男性の身長の合計
  3	相沢	男	165	353
  4	井上	女	148
  5	池田	男	175
  6	石井	女	155
  7	上野	男	168
  8	内田	女	161
  9	内村	男	178

• （解答例）
  

  	B	C	D	E
  2	名前	性別	身長	身長170以上の男性の身長の合計
  3	相沢	男	165	=SUMIFS(D3:D9,C3:C9,"男",D3:D9,">=170")
  4	井上	女	148
  5	池田	男	175
  6	石井	女	155
  7	上野	男	168
  8	内田	女	161
  9	内村	男	178

• Excel2003以前では、SUMPRODUCT関数を利用して計算できます。
  • E2セル：=SUMPRODUCT((D3:D9>170)*(C3:C9="男"),D3:D9)

ワイルドカード文字　　　文字列の検索、置換時に使用します　　　　　topへ
　？は任意の 1 文字
　　例）鹿児島県や鹿児島市を指定したい場合1文字(県と市）だけ異なるので、「鹿児島？」とします。
　＊は任意の数の文字
　　例)鹿児島県　宮崎県　など　県を含む文字を指定したい場合、鹿児島（3文字）と宮崎（2文字）が異なるので「＊県」とします。
　　※　？や＊の検索には　~(チルダ)　を使用します。

【問題】下表の中の「PTA会費」、「児童会費」、「父兄会費」を集計表の【会費】欄に計算しなさい。

• 	B	C	D	E	F
  2	月　日	項目名	適用	収入金額	支出金額
  3	4月15日	繰越金	3月分繰越金	10,000
  4	4月19日	PTA会費	会費(19人分)	7,600
  5	4月20日	図書費	新聞代		3,000
  6	4月25日	児童会費	会費(63人分)	25,200
  7	4月26日	図書費	新聞代		5,000
  8	5月10日	諸費	コピー代		4,800
  9	5月12日	図書費	本代		12,000
  10	5月18日	諸費	工具代		14,200
  11	5月20日	父兄会費	会費(2人分)	800
  12			合計	43,600	39,000
  13
  14	項目名	収入金額
  15	会費	33,600

【解答例】

• 「PTA会費」、「児童会費」、「父兄会費」は全て【会費】という文字を最後に含みます。
  別な言い方をすれば、『「会費」で終わる文字列である』とも言えます。
• ワイルドカード文字「*」(アスタリスク:半角文字)を組み合わせて、『*会費』を検索条件とします。

• 	B	C	D	E	F
  2	月　日	項目名	適用	収入金額	支出金額
  3	4月15日	繰越金	3月分繰越金	10,000
  4	4月19日	PTA会費	会費(19人分)	7,600
  5	4月20日	図書費	新聞代		3,000
  6	4月25日	児童会費	会費(63人分)	25,200
  7	4月26日	図書費	新聞代		5,000
  8	5月10日	諸費	コピー代		4,800
  9	5月12日	図書費	本代		12,000
  10	5月18日	諸費	工具代		14,200
  11	5月20日	父兄会費	会費(2人分)	800
  12			合計	43,600	39,000
  13
  14	項目名	収入金額
  15	会費	=SUMIF(C3:C11,"*会費",E3:E11)

• 検索条件の書き方(例)
  
  • A1セルを参照する場合は「">"&A1」のように&でつなぎます。
• 【関連情報】
  • [XL2002]SUMIF、COUNTIF、COUNTBLANK 関数で#VALUE!エラー
    http://support.microsoft.com/kb/260415/JA/
  • [XL2000]32以上の数値とハイフンを含む文字でSUMIFの結果が正しくない
    http://support.microsoft.com/kb/416463/JA/

スポンサードリンク

PageViewCounter

Since2006/2/27