Excel(エクセル)関数の技：合計する関数(1/2)

• セルの値を合計するワークシート関数をまとめて説明しています

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

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

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

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

• 【例題】
  

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

1. 検索条件が文字列の場合
   • 商品名が「りんご」の金額を合計する
     • =SUMIF(B2:B12,"りんご",E2:E12)
       検索文字列を「"」(ダブルクォーテーション)で括ります
   • 商品名が「りんご」以外の金額を合計する
     • =SUMIF(B2:B12,"<>りんご",E2:E12)
       比較演算子(<>：等しくない)と組合せ、「"」で括ります。
   • 商品名が空欄(未入力)でない金額を合計する
     • =SUMIF(B2:B12,"<>"&"",E2:E12)
       比較演算子(<>：等しくない)と組合せ、「"」で括ります。
     • =SUMIF(B2:B12,"*",E2:E12)
       ワイルドカード「*」を利用しています。何か入力されていたら・・・という意味になります。
2. 検索条件が数値の場合
   • 単価が「200」の金額を合計する。
     • =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)
       ワイルドカード「*」を利用しています。

「統合」を利用する

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

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

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としています。
       
   • Excel2007ではSUMIFS関数が使えるようになりました。

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

     • =SUMIFS(E2:E12,A2:A12,"2007/4/1",B2:B12,"りんご")
       

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