「腕試し問題」関数応用編 

Excelの腕試し問題304 売上げ一覧表から商品別の集計する方法


問題  売上一覧表から、商品名別に集計するための計算式を入力してください。

商品名に応じた個数、金額、消費税、税込金額の合計をそれぞれ求める計算式を入力してください。

        Excel 条件別集計(SUMIF関数)

サンプルファイルのダウンロード

商品マスタ:J3~K8(商品のデータ領域)

 ・J3~K8セル:「商品マスタ」の名前が設定されています。
    J3~K8セル
には、商品名・単価のデータが入力されています。
 ・商品名のセル:入力規則でリストの参照範囲としても使用します。
  ・個数のセル:商品売上げリストに商品名に応じた単価を表示させることができます。

商品売上げデータ:A12~G28(集計のもとになる領域)

・商品名のセルは、入力規則でリスト(商品マスター)から入力された値が入力されています。
・個数のセルには、任意の数値が入力されています。
単価のセルには、単価を取得できる様に計算式を入力してください。
 ・商品名に応じた金額が商品マスターから、単価を取得できる様に計算式を入力してください。
 ・商品名が空白の行には、エラーが表示されない様に工夫してください。
金額~税込み金額のセルには、消費税・税込金額を求める計算式を入力してください。
 ・消費税率は、A3のセルの値を参照してください。
 ・税込み金額は、金額と消費税の和となる計算式を入力してください。

商品別の売上げ集計データ:B3~G8(商品名に応じた、集計を表示する領域)

・商品名のセルは、入力規則でリスト(商品マスター)から入力された値が入力されています。

単価のセルには、単価を取得できる様に計算式を入力してください。
 
・商品名に応じた金額が商品マスターから、単価を取得できる様に計算式を入力してください。
 ・商品名が空白の行には、エラーが表示されない様に工夫してください。
解答するデータ(計算式を入力する領域)

 ・個数(C3~C8):商品名に応じた個数の合計を集計してください。
 ・金額(E3~E8):商品名に応じた金額の合計を集計してください。
 ・消費税(F3~F8):商品名に応じた消費税の合計を集計してください。
 ・税込金額(G3~G8):商品名に応じた税込金額の合計を集計してください。
金額~税込み金額までは、SUMIF関数を用いるまでもなく集計は可能です。
しかし、練習を兼ねるという意味からも、 SUMIF関数で集計をする練習をしてみてください。

解法のコツ

HintSUMIF関数です。
Excel SUMIF関数

SUMIF関数の使用例
下の表で、商品名が「日本酒セット」の売上げ個数の合計を求める場合
    計算式:「=SUMIF(B2:B17,"日本酒セット",C2:C17)
    出力結果:「19
Excel SUMIF関数