HOME > モーグ学習室 > Excel 関数 > 第6回 複数条件や数値などでデータを抽出する 5/5

第6回 複数条件や数値などでデータを抽出する 5/5

第6回 複数条件や数値などでデータを抽出する| 1/5  2/5  3/5  4/5  5/5 

SUMPRODUCT関数

最後に、対応する要素同士の積を合計する『SUMPRODUCT関数』を紹介します。
文章での説明は、少し難しいので実際に数式を作成しながら解説をしていきます。

“オーダー表”シートの右下にある「オーダー内容」を参照し、「オーダー表」に入力した“仕上サイズ”と“刷色”から料金を表示させます。

  1. セルC8を選択する。
  2. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
    ▼ 図05-01 セルC8を選択し、[関数の挿入]ボタンをクリック
    図05-01 セルC8を選択し、[関数の挿入]ボタンをクリック
  3. [関数の分類]ボックスから[数学/三角]を選択する。
  4. [関数名]ボックスから[SUMPRODUCT]を選択する。
  5. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
    ▼ 図05-02 [関数の挿入]ダイアログボックス
    図05-02 [関数の挿入]ダイアログボックス
  6. [配列1]ボックスに「($F$12:$F$19=A8)*($G$12:$G$19=B8)」と入力する。

    ※ この数式は、それぞれ絶対参照の範囲の値が=の後ろのセルと同じなら「1」、それ以外は「2」を返すという数式です。これらの数式結果を乗算した値が”配列1”の値となるため、セルA8、セルB8と一致した行のみが「1」を返す形となります。

  7. [配列2]ボックスにセルH12からH19を選択し、[F4]キーを押す。
  8. [OK]ボタンをクリックする。
    ▼ 図05-03 [関数の引数]ダイアログボックス
    図05-03 [関数の引数]ダイアログボックスス

    オートフィル機能を使用して、他のセルにも同じように関数を挿入します。

    ▼ 図05-04 オートフィル機能を使ってコピー
    図05-04 オートフィル機能を使ってコピー

    “配列1”と”配列2”を乗算した結果が値として返されます。”配列1”でセルA8とセルB8に該当する行のみが「1」を返すため、それ以外の値は[配列2の値×0]となり、「0」が返されます。そのため、セルA8とセルB8に該当する料金が数式の結果となります。

    最後に、もう一度SUMPRODUCT関数を使用し、“料金”と“枚数”の値を使用して“オーダー料金”を算出しましょう。

  1. セルD13を選択する。
  2. [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する。
    ▼ 図05-05 セルD13を選択し、[関数の挿入]ボタンをクリック
     図05-05 セルD13を選択し、[関数の挿入]ボタンをクリック
  3. [関数の分類]ボックスから[数学/三角]を選択する。
  4. [関数名]ボックスから[SUMPRODUCT]を選択する。
  5. [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する。
    ▼ 図05-06 [関数の挿入]ダイアログボックス
    図05-06 [関数の挿入]ダイアログボックス
  6. [配列1]ボックスにセルC8からC12を選択し、[C8:C12]と表示されたことを確認する。
  7. [配列2]ボックスにセルD8からD12を選択し、[D8:D12]と表示されたことを確認する。
  8. [OK]ボタンをクリックする。
    ▼ 図05-07 [関数の引数]ダイアログボックス
    図05-07 [関数の引数]ダイアログボックス

これで数式は完成しました。枚数を入力すると、「料金×枚数」の合計値が表示されます。枚数を入力し、オーダー料金が計算されるかどうか確認してみましょう。
料金×枚数の合計が“オーダー料金”に反映されたでしょうか?

▼ 図05-08 枚数を入力すると、オーダー料金が表示
図05-08 枚数を入力すると、オーダー料金が表示

今回は、台帳の管理などデータを扱う場合に活用できる関数をいくつかご紹介しました。用途に応じてこれらの関数を組み合わせて使いこなすことで、Excelでの処理をどんどん楽にできます。そのためには、今回使用したサンプルの引数や条件などを自分なりに変更し、関数の使い方やどの値が表示されるのかなどをしっかりマスターしておきましょう。

第6回 複数条件や数値などでデータを抽出する| 1/5  2/5  3/5  4/5  5/5