祝!100記事達成!!テーマにしてほしい内容等ありましたらお問合せフォームからメッセージください(^^)

Excel関数大全!~SUMPRODUCT関数~

どうもasです。

 

本日はExcelのSUMPRODUCT関数について解説していきます。

 

それではいってみましょう~。

 

SUMPRODUCT関数

SUMPRODUCT関数は組み合わせ方によって用途が変わります。

 

用途

  1. 数値を掛けて合計する
  2. 条件に合う数値を合計する

 

基本形

  1. =SUMPRODUCT(配列1,配列2,[配列3…,配列N])
  2. =SUMPRODUCT((配列理論式M)*1,配列N)※MとNの合計255

 

ワンポイント

  • 配列の範囲は1:1に指定します。

 

 

使用例

売上合計を求める

SUMPRODUCT関数を使えば売上の小計を計算せずに合計を求められます。

 

 

  1. 配列1にB2~B5セル(単価)を指定します。
  2. 配列2にC2~B5セル(販売数)を指定します。

 

このように配列1と配列2を1:1で指定すると配列1と配列2を掛け合わせて、小計を計算せずに合計を集計できます。

 

ワンポイント

  • 配列内では文字、空白、論理値は『0』としてし計算されます。

 

消費税を計算する

消費税入力用の列を追加すれば、軽減税率の計算もできます。

 

 

  1. 配列1にB2~B5セル(単価)を指定します。
  2. 配列2にC2~C5セル(販売数)を指定します。
  3. 配列3にD2~D5セル(税率)を指定し1を足します。(例:1+10%=1.1)

 

このように関数を入力すれば、①~③を掛け合わせて、複数の消費税が混在した計算を合計する事ができます。

 

条件に合う数値のみ集計する①

下図のような使い方をすれば条件の合う数値のみを集計することができます。

 

  1. 配列論理式をE2~E6セルに指定し、『”〇”』をイコールで結ぶことで、『〇』の有無で合計対象範囲を判定します。最終的に①~④を掛け合わせるので、1を掛け合わせることによって配列論理式の結果を数値化しています。
  2. 配列1をB2~B6セル(単価)に指定します。
  3. 配列2をC2~C6セル(販売数)に指定します。
  4. 配列3にD2~D6セル(割引率)に指定し1から引きます。(例:1-30%=0.7)

 

このように関数を入力すると①の条件に合う集計範囲内の②~③を掛け合わせて集計する事ができます。

 

ワンポイント

SUMPRODUCT関数では数値以外の文字、空白、論理値は全て0としてみなされます。

よって通常、関数において『TRUE』は1、『FALSE』は0としてみなされますが、SUMPRODUCT関数においては『TRUE』も0としてみなされてしまします。

なので前述の①の配列論理式『E2:E6=”〇”』の結果に1を掛け合わせることにより、結果を数値化しています。

 

条件に合うデータの個数を数える

SUMPRODUCT関数の配列の組み方によってはCOUNTIFSのような使い方ができます。

 

  1. 配列論理式でB2~B8セル(部署)を範囲指定してE2セル(営業部)に入力した条件の有無を判定します。1を掛けることにより数値化します。
  2. 配列論理式でC2~C8セル(性別)を範囲指定してE2セル(男性)に入力した条件の有無を判定します。1を掛けることにより数値化します。

 

このように関数を入力すると①~②の条件に合うデータの個数を求めることができます。

 

 

条件に合う数値のみ集計する②

基本的な使い方ではありませんが、下図のような使い方もできます。

 

 

  1. 配列論理式でA2~A8セル(商品名)を範囲指定してE2セル(商品B)に入力した条件の行数を検索します。
  2. 配列論理式でB1~C1セル(商品名)を範囲指定してF1セル(仕入値)に入力した条件の列数を検索します。
  3. 配列1をB2~C8セル(仕入値~販売価格)に指定します。①~②の行数と列数が交差するセルの数値を求めます。

 

少し特殊な使い方なので、あまり難しく考えず数学の方程式のように見本の形で覚えて使ってください。

 

 

まとめ記事

Excel関数のまとめ記事です。

 

是非参考にしてください(^-^)

 

     

asblogの最新情報をチェックしよう!