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

Excelの腕試し問題307 混在する商品受払データに商品別の受払NO(連番)を付与する!!


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

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

       

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

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

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

インクの別の連番を(商品No+連番(4桁))を付与

J3~J100のセル:インクの別の連番を(商品No+連番(4桁))を付与する計算式を入力してください。
前置する商品Noは、各行のC列の値を使用してください。

払出し数の累計(H3セル~):払出し数(F3セル~)の累計を集計する計算式を入力してください。
    ・
当該行より上にする、当該行の商品Noの個数が連番になります。
    利用する関数は、COUNTIF関数です。
前置する商品Noと連番を連結するのは、以下の何れかの方法になります。
    「&」の記号で連結する⇒商品No&連番(4桁)
    CONCATENATE関数を使用⇒CONCATENATE(商品No,連番(4桁))
当該行の払出し数のセルが空白の場合は、「""(空白)」になる様にしてください。

解法のコツ

Hint1:条件に一致する件数を返す関数はCOUNTIF関数です。
Excel COUNTIF関数

Hint2:商品Noが空白の場合は、「””(空白)」を表示させるのはIF関数です。
 この問題で、「””(空白)」としたのは、この戻り値は数値として、で再利用されることがないのが理由です。
Excel IF関数で分岐