1.概要

商品名や品番などで検索し、一致したものの価格などの情報を探し出すには、INDEX と MATCH 関数を組み合わせて使う。
DLOOKUP関数でも可能だが、行に空白があったり、左側にIDがなかったりして、間違った結果が返ってくる恐れがある。
ここでは製品に使われている部品の一覧からそれぞれの部品の単価を調べて、使用されている個数を掛け算して合計して計算する。

2.単価表と計算するシートの準備

部品単価のシートを用意する。
A列に「部品番号」、B列に「部品名」、C列に「単価」を例にする。
タイトルは何でもいい。

次に製品金額を算出するシートを用意する。
A列に「部品番号」、B列に「部品名」、C列に「数量」、D列に「単価」、E列に「計」、タイトルは何でもいい。一応G列に「総合計」と四捨五入した結果も表示されるようにした。
レイアウトは下の図のような感じにした。

当然これだけでは計算できない。
D列に関数を入れなければならない。

3.MATCH関数

部品番号が部品単価の上から何番目にあるのか検索する関数は、MATCH関数を使う。
たとえば、下の画面のように、部品番号に「37040-00014」、部品名が「ジョイント」数量が1とする。

A列の部品番号「37040-00014」を「部品単価」のシートから探すとする。
まず、「=MATCH(」まで入力すると、「MATCH(検査値, 検査範囲, [照合の種類])」とヒントが表示されるので、「検査値」に相当するA2セルをクリックし、「,(カンマ)」を入力する。

次は「検索範囲」なので、「部品単価」シートを選択し、例えばA2セルからA15セルの範囲をドラッグして、「,(カンマ)」を入力する。

「0-完全一致」を選択して「Enter」キーを押す。

すると、D2セルに「=MATCH(A2,部品単価!A2:A15,0)」と関数が入力され、値は「4」になっている。

これは先ほど範囲指定したA2からA15の中で上から4番目に部品番号が見つかったということである。
一旦ここでまとめると、「=MATCH(A2,部品単価!A2:A15,0)」で、「部品単価」のA2セルからA15セルの範囲の中から、「製品金額算出」のA2の部品番号を探してそのデータが先ほど選択した範囲の中で上から何番目にあるのかがわかる。

4.INDEX関数

部品単価のシートの単価で上から4番目のデータを調べるには「=INDEX(」と入力して、

「部品単価」シートのC2~C15セルをドラッグする。

続けて、「,4)」を入力してEnterキーを押す。

すると、「112」という単価が表示される。

一旦ここでまとめると、「=INDEX(部品単価!C2:C15,4)」で、「部品単価」のC2セルからC15セルの範囲で、上から4番目のデータを調べることができる。

5.MATCH関数とINDEX関数を合体させる

「=MATCH(A2,部品単価!A2:A15,0)」で上から何番目に部品番号があるかわかる。
「=INDEX(部品単価!C2:C15,4)」で上から4番目の単価を調べることができる。

この二つの関数を合体させる。つまり、4のところにMATCHI関数を埋め込む。

「=INDEX(部品単価!C2:C15,MATCH(A2,部品単価!A2:A15,0))」

これをD2セルに書き込めばよい。

これをD列すべてにコピーすればいいが、
固定するセルの部分はF4キーで#を入れておく、また単価の一番下が100000行あるので、
15を100000に変更しておく。

「=INDEX(部品単価!$C$2:$C$100000,MATCH(A2,部品単価!$A$2:$A$100000,0))」

あとはD列の下までコピーすればよい。

6.使い方

部品番号と部品名と個数の部分に値を貼るだけでよい。
「計」のところに「数量」×「単価」を表示できるように式を入れ、
G2セルのところには、E列の総合計が表示されるようにした。
G4セルのところには、G2セルの値を切り上げするように関数を入れている。

また、「#N/A」が表示されないようにD列の関数は、
「=IFERROR(INDEX(部品単価!$C$2:部品単価!$C$100000,MATCH(A2,部品単価!$A$2:部品単価!$A$100000,0)),"")」
のようになっている。

部品単価の行数は、500000行にしたが素早く検索して結果を表示してくれる。エクセル関数のいいところはVBAよりも速いということだ。
VBAでこのINDEX、MATCH関数を記述して検索を早くする方法もあるが、これはまたいつか紹介しよう。

おすすめの記事