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

Excelの腕試し問題308 混在する商品受払データから、計算式で商品別の受払データを抽出する!!


問題  「データ抽出」シートに、計算式で商品ごとに抽出してください。

一つのシートに複数の商品の受払データの中から、商品毎にデータを抽出するのは簡単なことではありません。
この問題では、敢えてその算出方法にチャレンジしてみてください。
データの抽出は、マクロ・VBAを使用しないと不可能であると諦めていたことが、計算式でも可能になる画期的な方法です。
    「Q307 混在する商品受払データに商品別の受払NO(連番)を付与する!!」の問題で取得した、受払Noを使用します。

        Excel 計算式でデータを抽出する方法

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

「在庫計算2」シートには、複数のインクの受払情報が入力されています。

 ・受払No(J3~):商品No(C3~)+連番(4桁)が入力されています。
  ・「在庫計算2」シートは、Q307で、作成したワークシートです。
 ・L3~M8セルの名前を、「商品マスタ」と名前がついている
        Excel 計算式でデータ抽出

「データ抽出」シートに以下の図を参考にして、データを抽出させてください。(計算式)

1 抽出したデータを集計させてください。

商品No(A4セル):インクの商品Noを選択できる様に入力規則(リスト)を設定してください。
    「在庫計算2」シートのL3~L8セルをリストとして表示
商品名(B4セル):商品No(A4セル)の値に応じて商品名を表示する様に計算式を設定してください。
   
範囲名の「商品マスタ」を参照し取得(VLOOKUP関数)
入庫数/払出数/在庫数を集計する計算式を入力してください。

       Excel 計算式で抽出

2 計算式で、データを抽出させてください。(計算式)

抽出するための検索値(払出No)を作成
 ・前置する商品No:A4セルの記号⇒「MG」
 ・連番(4桁):各行のNo(A6~)を4桁で表示⇒TEXT($A6,"0000")⇒「0001」
 ・抽出する払出No:前置する商品No+連番(4桁)(例:"MG"&"0001")
各行の払出しNoが、「データ抽出」シートの払出しNoの中の何番目のデータかを取得する。
 ・何番目のデータを取得するための関数:MATCH関数
各行の払出しNoのデータを抽出する。
 ・該当データを取得するための関数:INDEX関数
抽出受払Noに該当がない場合は、「""(空白)」表示にする。
 ・条件で処理を分岐する関数:IF関数

解法のコツ

Hint1:検査値が、検査範囲の何番目に位置するかを調べる関数はMATCH関数です。
Excel MATCH関数

Hint2:参照範囲の中の〇行目の〇列目の位置にある値を取得する関数は、INDEX関数です。
 INDEX関数は、MATCH関数と組み合わせて使用することが多い関数です。
Excel INDEX関数