データベースを扱う関数


 検索など、データベースを扱う関数は、基本をしっかり押さえると、大変便利に使えます。

必須データベース関数

関数(引数)

関数の値(返り値)・目的

VLOOKUP(検索値,範囲,列番号,型) 範囲の中の左の列から検索値を探し、その行の列データを返す
MATCH(検索値,範囲,型) 範囲の中の検索値を探し、その行番号を返す
INDEX(範囲,行番号,列番号) 範囲の中の指定された行・列の位置のセルの値を返す
ADDRESS(行番号,列番号) A3とかC15のような座標文字列を返す
INDIRECT(参照座標) A3とか、C15のような参照座標から、そのセルの値を返す
ROW()・COLUMN() セルの行番号・列番号を求める

検索の型指定

 検索値を指定する場合、見つからないときはエラーになります。これを防ぐために、VLOOKUPやMATCHでは、検索の型を指定できます。型に0を指定した場合は、完全に一致するものを探します。同じく1を指定すると、最も近いものを探しますが、この場合はデータがソートされて(小さい順に並んでいる)いる必要があります。0を指定する場合は、完全一致なので、ソートされていなくても大丈夫ですが、型指定を省略すると1になるので注意してください。

検索範囲の名前指定

 範囲は、B1:D50などのように、座標でも指定できますが、必ず範囲に名前指定をして使いましょう。座標での指定は、関数をコピーしたときに範囲も移動し、使いにくくなります。名前をつけておけば、別シートであっても、シート名の指定も不要です。名前は、日本語も使えます。右の図は、E2からE25の範囲を選択し、番号という名前をつけている例です。
範囲は、複数の行・列にまたがって指定することもできます。

上のサンプルで、同様にF2:F25は氏名、G2:G25は読みと名前をつけ、更にE2:G25には名簿と名前をつけました。本来は、このようなデータベースは別シートにA1から置くほうが良いのですが、関数の使い方が一目で分かるよう、同じシート上にD1からデータベースの表を置いています。このようなデータベースの表に、罫線は禁物です。

VLOOKUP関数

A1に検索値として、番号の値を置き、B1とB2にVLOOKUP関数を置きました。
B1には、=VLOOKUP(A1,名簿,,0)を、B2には=VLOOKUP(A1,名簿,,0)があります。

A1の34035を名簿の範囲で捜して、その列の2行目3行目のデータが返されます。検索の型は0で、完全一致を指定しているので、番号はソートされている必要はありません。A1に番号を入力すると、その行の氏名・読みをそれぞれB1・B2に表示します。

このように、VLOOKUP関数は、範囲の1列目をキィに検索し、2列目以降を指定して列データを返します。この並びだと、読みを指定して番号を返すことはできません。型指定を省略すると1になり、近似値を探し出すので、キィとなる列のデータでソートしておく必要があります。

MATCH関数

 MATCH関数は、VLOOKUPと似ていますが、指定された列の範囲を検索し、データのある行番号を出力します。この行番号は、指定された列の中の相対位置です。実際の、行番号ではないので注意してください。MATCHを使うと、任意の列から検索できます。しかし、直接セルの中身を表示しないので、次のINDEX関数と組み合わせて使います。

上の例では、氏名という範囲(B2:B25)からA6に入力した酒井久美を検索しています。B6に入れた関数は
=MATCH(A6,氏名,0)で、検索の型は0(完全一致)を指定しています。その結果、データの6行目に見つかったので、6が返されますが、実際はこのデータはF7にあり7行目です。6は、相対位置です。
B7には、=MATCH(A7,読み,0)で、読みの列からたけださちこを検索しています。

INDEX関数

範囲を指定し、その中の行番号・列番号を指定して、その位置のセルの値を返します。この場合も、行・列の番号は相対指定です。

上の例では、B10の関数で、名簿という範囲(E2:G25)から、10行・2列目のデータを表示しています。A9・A10に行番号・列番号を入れていますが、これは名簿という範囲の中の相対位置です。B13の関数は、同じように10行・3列のデータを表示しています。
MATCH関数とINDEX関数を組み合わせると、VLOOKUPよりも自由に検索できます。

ADDRESS関数

 ADDRESS関数は、行番号・列番号表示を、エクセルの座標形式に変換するものです。A1形式の他、R1C1形式も選べます。関数の引数に座標形式を使うものが多いので、MATCH関数などで求めた行番号・列番号を変換するために使います。この場合、範囲内の相対番号は、範囲の左上のスタート座標を加えて、絶対座標に変える必要があります。

上の例は、B16にADDRESS関数を置き、A12とB12にある行・列番号に、それぞれ1と3を加えて金坂聡美の氏名の位置を座標表示にしているものです。F17に$が付いていますが、これは関数が移動したときに座標が変わらないようにしているものです。A12に1を足すのは、名簿の範囲が2行目から始まっていること、B12に3を足すのは、実際には4を足しますが読みの位置から1列戻って氏名データを読みたかったからです。

INDIRECT関数

ADRESS関数は、座標の文字列を返しますが、これを実際のセルのデータに変換するものが、INDIRECT関数です。セルに、座標の文字列がある場合、これをその座標のセルの中身に変換します。ADDRESS関数と組み合わせて使うことが多いものです。

 

実際のファイルは ここから ダウンロードできます。(search.xls 21KB)

INDIRECT(ADDRESS(行1,列1)&”:”&ADDRESS(行2,列2))で範囲指定

MATCH関数などで、範囲をA1:D10のように指定しますが、これを変数の行・列の数字で指定したいときは、2つのADDRESS( ,)を&で”:”とつなぎ、これをINDIRECT関数で読ませます。具体的には、MATCH(検査値,INDIRECT( ),)のようにします。

ROW()・COLUMN()関数

ROWは、セルの行番号を、COLMNはセルの列番号を返す関数です。いずれも、()の中の引数を省略すると、自身のセル位置の数を返します。行や列の数に伴って変わる数を作りたいときに使います。ここでは、横に並んだデータを、INDEX関数を使って、縦に並べなおす例を使って説明します。

上のように、A1:D1に商品データが並んでいます。これを、B3に持ってくるには、INDEX関数で可能ですが、式をコピー・貼り付けで一度に書けるようにしたいと思います。先に、A1:D1に商品という名前を付けておきます。B3にA1の内容を持ってくるには、=A1でも良いのですが、これを縦に並べたい場合は、この書き方ではいちいち書かなければなりません。=INDEX(商品,1,1)と書いても、その下の式は =INDEX(商品、1,2)と書かなくてはなりません。

そこで、C3から縦に並べたい場合 =INDEX(商品,1,ROW()−2)とします。商品の範囲の列番号が、行の数字によって換わるようにします。C3は3行目なので1を、C4は4行目なので2ということは、列番号は、式が書かれるセルの行番号から2を引いた数字になります。

こうすれば、C3の式をフィルハンドル()でドラッグすれば、すべてに正しい式が入ります。

表紙へ