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

Excelの腕試し問題306 商品が混在する商品受払データの商品別の累計計算


問題  商品が混在する受払データにおける、商品ごと累計の計算式を入力してください。

一つのシートに複数の商品の受払データの中から、商品毎の集計をするのは簡単なことではありません。
この問題では、敢えてその算出方法にチャレンジしてみてください。
この方法をマスターできれば、計算式で条件に一致するデータを抽出することも可能になります。
その方法は、また別の機会に紹介します。

        Excel 混在するデータから条件で累計(SUMIF関数)

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

インク払出しデータには、複数のインクの種類の受払情報が入力されています。

 ・商品No(C3~):アルファベット2文字が入力されています。商品の判別は、「商品No」で判別するものとします。
  ・入庫数(E3~):前年の繰越、入庫数などが入力されています。
 ・払出し数(F3~):インクの払出しの数が入力されています。

当該行のインクの累計及び在庫数を集計する計算式を入力してください。

計算式は、データが入力されていない行まで(約100行程度)まで入力するものとします。

入庫数の累計(G3セル~):入庫(E3セル~)の累計を集計する計算式を入力してください。
 ・開始行(E3セル)~当該行までの当該商品の入庫累計の計算式を入力してください。(SUMIF関数)
 ・計算する範囲の参照方法にある工夫をすれば簡単に計算できます。
 ・当該行の入庫数のセルが空白の場合は、「0(ゼロ)」になる様にしてください。
払出し数の累計(H3セル~):払出し数(F3セル~)の累計を集計する計算式を入力してください。
 ・開始行(F3セル)~当該行までの当該商品の払出し累計の計算式を入力してください。(SUMIF関数)
 ・計算する範囲の参照方法にある工夫をすれば簡単に計算できます。
 ・当該行の払出し数のセルが空白の場合は、「0(ゼロ)」になる様にしてください。
在庫数(I3セル~):在庫を計算する計算式を入力してください。
 ・開始行(I3セル)~当該行までの当該商品の在庫数を求める計算式を入力してください。
 ・計算する範囲の参照方法にある工夫をすれば簡単に計算できます。
 ・当該行の入庫数と払出し数が空白の場合は、「0(ゼロ)」になる様にしてください。
 ・もし、エラーが表示された場合はエラーが表示されない様に工夫してください。

解法のコツ

Hint1:条件に一致する合計を返す関数はSUMIF関数です。
Excel 条件で合計(SUMIF関数)

Hint2:売上日付が空白の場合は、「0」を表示させるのはIF関数です。
 エラー対策として、売上金額が「””(空白)」の場合、真の戻り値に、「””(空白)」が多様されます。
 この問題で、「0(ゼロ)」としたのは、この戻り値は計算式で再利用される可能性が高いことからです。
 「””(空白)」のまま計算式で再利用した場合、その再利用先で再びエラーが発生することが考えられます。
 それを防止するため、敢えて「0(ゼロ)」と返すように出題しています。
Excel IF関数で分岐