教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010:SUMPRODUCT関数の使い方【応用編】

 

 

 

 


 

 

 

 


 

 

 

SUMPRODUCT関数は引数を指定するだけで配列計算できる便利な関数です。(SUMPRODUCT関数の使い方【基本編】はこちら)ここではSUMPRODUCT関数 で条件を付けて配列計算する方法をご紹介します。

=SUMPRODUCT((配列1の条件)*(配列2の条件),計算式)   ★他の例はページ末尾の補足参照★

◎◎ SUMPRODUCT関数で条件を付ける際のポイント ◎◎

  • 条件は(配列>=200) (配列="新宿")など、のように括弧で括り(文字は""で囲む)演算子が利用できます。

  • 複数の条件は*で繋ぎます。(AND条件)※条件が1つなら*1 …… 例 SUMPRODUCT((配列1="りんご")*1,A1:A30)

  • 条件と計算式はカンマ(,)で区切ります(,以降が計算式)… 例 SUMPRODUCT((A1:A3>=6)*(B1:B3="女"),C1:C3)

  • 計算式がない場合はTrue(1)の個数を数えます (カウント)…例 SUMPRODUCT((配列1="青森")*(配列2="りんご")) 青森のりんごを数える

  • 条件範囲に合致した個数を数える(カウントする)なら計算式は不要です。(条件=True(1)を合計するので)

 

 

◆シナリオ1:【りんご】で【現金】払いの売上金額(価格×個数の合計)を出すには…?

SUMPRODUCT((A2:A11=”りんご”)*(D2:D11="現金"),B2:B11*C2:C11)

 

※条件が1つなら*1とします。例:りんごの売上金額を出す… SUMPRODUCT((A2:A11="りんご")*1,B2:B11*C2:C11)

 

 

◆ シナリオ2:各部署にランチ代(補助費)が出ました。弁当1個の値段と人数を計算し、残高を返す必要があります。

  • 全体の残高を出すには … SUMPRODUCT(B2:B11-(C2:C11*D2:D11))

  • システム部だけの残高を出すには … SUMPRODUCT((A2:A11="システム")*1,B2:B11-(C2:C11*D2:D11))

 

◆ SUMPRODUCT関数の概念 ◆

  • 配列数式にしなくても引数を配列として扱うことができます。

  • 配列を1つ指定しただけなら合計が返されます。複数の配列なら各要素の積が合計されます。数値以外の要素は0とみなします(3,1,りんご,2の配列なら6が返る)配列に条件を付けると各要素にTrue(1)かFalse(0)が割当られます。(1,0,0,1…のような状態)これだけでは結果は0なので条件を括弧で括って*1とすると各要素から0か1が返り(0*1=0、1*1=1)それを合計するので結果として個数が返ります。複数の条件なら他の配列でも0か1が返るのでこれらを掛ければ(=True(1)*True(1)の場合のみ1が返る(0*0も1*0も0))合致したものだけを対象に計算ができます。(AND条件)

 ◆ SUMPRODUCT関数の例 ◆

  • SUMPRODUCT(配列1,配列2) …… 配列の各要素の積を合計

  • SUMPRODUCT(配列1) …… 配列1の合計

  • SUMPRODUCT((配列1の条件)*1,配列) …… 条件に合致したものを合計(※SUMIF(範囲,条件,合計範囲)と同じ)

  • SUMPRODUCT((配列1の条件)*(配列2の条件))…… 複数条件に合致したものを数える(COUNTIFSと同じ。2003で使える)

  • SUMPRODUCT((配列1の条件)*1,計算式)……例:Sumproduct((A1:A3="kids")*1,b1:b3/c1:c3)  kidsのb列÷c列を合計する

  • SUMPRODUCT((配列1の条件)*(配列2の条件),配列3) …… 配列1の条件と配列2の条件に合致したもの(AND条件)を配列3の値で合計

  • SUMPRODUCT((配列1の条件)*(配列2の条件),配列3*配列4) …… 配列1の条件と配列2の条件に合致したもので、配列3×配列4の各要素を合計

初心者の方はとりあえず条件は( )で指定し、条件が1つなら*1、複数なら条件同士を掛け、カンマで区切って計算式を入力することを覚えておきましょう。

 

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows