1. Home » 
2. エクセル関数の技 » 
3. 合計する関数の技(1/2)

合計する関数の技(1/2):Excel関数の技

• セルの値を合計するワークシート関数や機能をまとめて説明しています 。合計ができる関数はいろいろありますので、それらの違いや特徴を説明しています。

数値やセルの値を合計する　　topへ

SUM関数：=SUM(数値1,数値2,・・・)

1. 引数に数値、論理値、数値を表す文字列を指定した場合
   • =SUM(1,TRUE,"1")　は「3」になります。
     TRUEは数値の1として、文字列「"1"」は数値の1として計算されますので、=SUM(1,1,1)=3といった感じです。
   • ただし、計算できない文字列が入るとエラーが返されます。
     =SUM(1,TRUE,"A")　は #VALUE! となります。
     
2. 引数にセル範囲を指定した場合
   数値セルが計算され、空白セル、論理値、文字列のセルは無視されます。
   SUM(B1:B5)　は以下の例では「1」となります。
   B1セルの数値「1」のみが計算されますので、=SUM(B1:B5)=1 となります。
   
3. 四則演算の足し算との違い
   • 足し算では数値として扱える文字列は計算されます。
     文字列の数字を計算した例です。=B1+B2　とすると15となります。
     ちなみに、=SUM(B1:B2)は0となります。
     
   • なお、=B1+B2で数値と見なされない文字列のセルがある場合にはエラーとなります。
     ちなみに、=SUM(B1:B2) では文字列のセルは無視されますので、0となります。
     
   • 論理値のTRUEは1、FALSEは0として計算されます。
     =B1+B2　とすると、1となります。
     ちなみに、=SUM(B1:B2) では論理値のセルは無視されますので 0となります。
     

条件に合うセルを合計する　　topへ

• 【例題】
  
• データが利用できるように表を書いておきます。
  一応、日付は2007年当時ですので、2007/4/1 といったデータにする必要があります。
  

  日付	商品名	単価	数量	金額
  2007/4/1	りんご	100	203	20,300
  2007/4/2	みかん	80	157	12,560
  2007/4/3	なし	150	308	46,200
  2007/4/1	もも	280	179	50,120
  2007/4/2	バナナ	110	221	24,310
  2007/4/3	ぶどう	360	230	82,800
  2007/4/1	りんご	100	126	12,600
  2007/4/2	みかん	80	253	20,240
  2007/4/3	なし	150	84	12,600
  2007/4/1	バナナ	110	97	10,670
  2007/4/2	ぶどう	360	141	50,760
  			合計	343,160

SUMIF関数：=SUMIF(範囲,検索条件,合計範囲)

1. 検索条件が文字列の場合
   • 商品名が「りんご」の金額を合計する
     =SUMIF(B2:B12,"りんご",E2:E12)
     検索文字列を「"」(ダブルクォーテーション)で括ります
     
   • 商品名が「りんご」以外の金額を合計する
     =SUMIF(B2:B12,"<>りんご",E2:E12)
     比較演算子(<>：等しくない)と組合せ、「"」で括ります。
     
   • 商品名が空欄(未入力)でない金額を合計する
     • =SUMIF(B2:B12,"<>"&"",E2:E12)
       比較演算子(<>：等しくない)と組合せ、「"」で括ります。
       
     • =SUMIF(B2:B12,"*",E2:E12)
       ワイルドカード「*」を利用しています。何か入力されていたら・・・という意味になります。
2. 検索条件が数値の場合
   • 単価が「100」の金額を合計する。
     =SUMIF(C2:C12,100,E2:E12)
     検索条件が数値の場合は「"」で括る必要はありません。
     
   • 数量が200未満の金額を合計する
     =SUMIF(D2:D12,"<200",E2:E12)
     比較演算子(<：未満)と組合せる時は「"」で括ります。
     
   • 数量が200以上の金額を合計する
     • =SUMIF(D2:D12,">=200",E2:E12)
       比較演算子(>=：以上)と組合せる時は「"」で括ります。
3. 検索条件がシリアル値の場合
   • 日付が4月1日(2007/4/1)の金額を合計する
     =SUMIF(A2:A12,"2007/4/1",E2:E12)
     シリアル値を「"」で括ります。
     
   • 日付の期間が4月1日(2007/4/1)〜4月2日(2007/4/2)の金額を合計する
     2007/4/1以上の合計から2007/4/3以上の合計を差し引きます。
     =SUMIF(A2:A12,">=2007/4/1",E2:E12)-SUMIF(A2:A12,">=2007/4/3",E2:E12)
     比較演算子(>=：以上)を使っています。
     
4. 検索条件をセル参照する場合
   • G2セルに「りんご」と入力されている場合
     =SUMIF(B2:B12,G2,E2:E12)
     検索条件に参照セルのセル番地を入れます。
     
   • G2セルに「りんご」と入力されていて、りんご以外の金額を合計する
     =SUMIF(B2:B12,"<>"&G2,E2:E12)
     比較演算子(<>:等しくない)は「"」で括り、参照セル番地とを文字列演算子「&」でつなぎます。
     
5. ワイルドカードと組み合わせる場合
   • 商品名に「ん」を含むもの(この例ではりんごとみかん)の金額を合計する
     =SUMIF(B2:B12,"*ん*",E2:E12)
     ワイルドカード「*」を利用しています。
     

Spill(スピル)が利用できる場合の集計方法

• 金額の計算はE2セルに　=C2:C12*D2:D12 と入力しています。
  商品名はG2セルに　=UNIQUE(B2:B12)　と入力しています。
  金額はH2セルに　=SUMIF(B2:B12,G2#,E2#)　と入力しています。
  

「統合」を利用して集計する

• 商品名毎に金額の合計を計算します。

1. G1セルに「商品名」H1セルに「金額」とリスト(A1:E12)と同じ列見出しを入力します。
   
2. G1:H1セルを選択して、[データ]タブのデータツール グループにある[統合]を実行します。
   
3. 統合の設定で集計の方法を「合計」を選択します。
   統合元範囲でセル範囲「Sheet1!$B$1:$E$12」を選択して、[追加]ボタンをクリックします。
   統合元に「Sheet1!$B$1:$E$12」を登録します。
   統合の基準の上端行と左端列の両方にチェックを入れます。
   [OK]ボタンをクリックします。
   
4. 商品名毎に金額の合計が計算されました。
   

複数条件に合うセルを合計する　　topへ

SUMIFS関数：=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2...)

• And条件で合計する
  Excel2007以降ではSUMIFS関数が使えるようになりました。
  2007/4/1 and　りんご　の金額の合計を求めます。
  =SUMIFS(E2:E12,A2:A12,"2007/4/1",B2:B12,"りんご")
  セル参照にすると、=SUMIFS(E2:E12,A2:A12,G3,B2:B12,G4)　となります。
  

SUMIF関数：=SUMIF(範囲,検索条件,合計範囲)

1. OR条件
   • 同一列でのOR条件
     商品名が「りんご」または「みかん」の金額を合計する。
     • =SUM(SUMIF(B2:B12,{"りんご","みかん"},E2:E12))
       (注)この形式ではセル参照はできません
     • =SUMIF(B2:B12,"りんご",E2:E12)+SUMIF(B2:B12,"みかん",E2:E12)
     
2. AND条件
   • 複数列でのAND条件
     日付が4月1日(2007/4/1)で商品名が「りんご」の金額
     • そのままでは計算できないので作業列を使います。
       F2セルに=A2&"_"&B2　と入力してF12セルまでコピーします。
       =SUMIF(F2:F12,"2007/4/1"*1&"_"&"りんご",E2:E12)
       (注)日付のシリアル値にするため"2007/4/1"*1としています。
     

SUMPRODUCT関数：=SUMPRODUCT(配列1,配列2,配列3,...)

• [SUMPRODUCT関数の基本]
  • 単価と数量を掛け合わせたものを合計する
    =SUMPRODUCT(C2:C12,D2:D12)
    
• [SUMPRODUCT関数の応用]
• OR条件
  • 商品名が「りんご」または「みかん」の金額を合計する。
    =SUMPRODUCT((B2:B12="りんご")+(B2:B12="みかん"),E2:E12)
    条件式は「+」(プラス)でつなぎます。(論理和)
    
• AND条件
  • 日付が4月1日(2007/4/1)で商品名が「りんご」の金額を合計する
    =SUMPRODUCT((A2:A12=DATE(2007,4,1))*(B2:B12="りんご"),E2:E12)
    条件式は「*」(アスタリスク)でつなぎます。(論理積)
    
• 金額の計算欄がないケースではちょっと複雑になります。
  日付が4月1日(2007/4/1)で商品名が「りんご」の単価と数量を掛け合わせて合計する
  • =SUMPRODUCT((A2:A12=DATE(2007,4,1))*(B2:B12="りんご")*(C2:C12),D2:D12)
    =SUMPRODUCT((A2:A12="2007/4/1"*1)*(B2:B12="りんご")*(C2:C12),D2:D12)
    日付がシリアル値なので(A2:A12=DATE(2007,4,1))や(A2:A12="2007/4/1"*1)とします。
    

DSUM関数:=DSUM(Database,フィールド,Criteria)

• 条件をセル範囲に書き出しておいて該当するフィールドの合計を計算します。
• 日付が4月1日(2007/4/1)で商品名が「りんご」の金額を合計する。
  • 条件をG1:H2へ書き出します。同一行に条件を書いているのでAND条件になります。
  • =DSUM(A1:E12,G4,G1:H2)　←フィールドをセル参照しています
  • =DSUM(A1:E12,"金額",G1:H2)　←フィールドに列見出しを使用しています
  • =DSUM(A1:E12,5,G1:H2)　　←フィールドに列の位置(金額はデータリストの左から5番目の列)を使用しています
  • 
• 【AND条件】
  日付が4月1日(2007/4/1)〜4月2日(2007/4/2)で商品名が「りんご」の金額を合計する。
  • 条件をG1:I2へ書き出します。
    2007/4/1以上 かつ　2007/4/2以下　のAND条件ですので同一行に条件を書きます。
  • =DSUM(A1:E12,E1,G1:I2)
    
• 【OR条件】
  日付が4月1日(2007/4/1)または4月3日(2007/4/3)で商品名が「りんご」の金額を合計する。
  • 条件をG1:H3へ書き出します。
    2007/4/1でりんご　または　2007/4/3でりんご　のOR条件ですので同一行にAND条件を書き、2行に分けてOR条件を書きます。
  • =DSUM(A1:E12,E1,G1:H3)
  • 

上位/下位のセルを合計する　　topへ

• 上位3つのセル値を合計します。
  • LARGE(B2:B7,{1,2,3})と配列を使って取り出し、SUM関数で合計します。
    • =SUM(LARGE(B2:B7,{1,2,3}))
      =SUM({950,900,800})
      =2650
      となります。
    
• 下位3つのセル値を合計します。
  • SMALL(B2:B7,{1,2,3})と配列を使って取り出し、SUM関数で合計します。
    • =SUM(SMALL(B2:B7,{1,2,3}))
      =SUM({500,550,720})
      =1770
      となります。
  

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27