|
SUMPRODUCT関数は引数を指定するだけで配列計算できる便利な関数です。（SUMPRODUCT関数の使い方【基本編】はこちら）ここではSUMPRODUCT関数
で条件を付けて配列計算する方法をご紹介します。
=SUMPRODUCT((配列1の条件)*(配列2の条件),計算式)
★他の例はページ末尾の補足参照★
|
◎◎
SUMPRODUCT関数で条件を付ける際のポイント ◎◎
-
条件は(配列>=200)や
(配列="新宿")など、のように括弧で括り（文字は""で囲む）演算子が利用できます。
-
複数の条件は*で繋ぎます。（AND条件）※条件が１つなら*1
…… 例 SUMPRODUCT((配列1="りんご")*1,A1:A30)
-
条件と計算式はカンマ(,)で区切ります（,以降が計算式）…
例 SUMPRODUCT((A1:A3>=6)*(B1:B3="女"),C1:C3)
-
計算式がない場合はTrue(1)の個数を数えます
（カウント）…例
SUMPRODUCT((配列1="青森")*(配列2="りんご")) 青森のりんごを数える
-
条件範囲に合致した個数を数える（カウントする）なら計算式は不要です。（条件＝True(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、複数なら条件同士を掛け、カンマで区切って計算式を入力することを覚えておきましょう。
|