HLOOKUP関数とMATCH関数を使って効率的に表を検索する

OpenOffice

送信者 OpenOffice
この表のサンプルファイルは、SKYDRIVEからcalc130.odsをダウンロードしてください。

上の表には、7人の生徒の4科目のテストの点数が入力されています。
この表から、指定した生徒の指定した科目の点数を検索した結果がK3セルに返されています。
ここでは、指定した名前の下にある科目の点数を表示するので、HLOOKUP関数を使っています。

関数名：HLOOKUP関数
書式：=HLOOKUP(検索値;範囲;行番号;検索の型)
何ができる：指定した範囲の一番上の行の値を検索して範囲内の対応するセルの値を返します。
平たく書くと：=HLOOKUP("屋久島ひろ子";A1:H5;3;0) でA1:H5の表の一行目から、屋久島ひろ子を検索し、3行目にある点数を返します。

と、平たく書くとこれだけのことですが、この表を実際に使うとなると、かなり不便を感じます。
１：検索するたびに名前を数式の中に入力しなければいけない
２：表示したい科目が何行目にあるかを調べて、その行番号を数式の中に入力しなければいけない
１度や２度ならともかく、日常業務の中でこんなことはやってられません。

上に書いた不便を解消する方法が２つあります。


検索したい名前と科目をリスト形式ににして選択入力できるようにする

  1. K1セルを選択し、ツールバーから、データ＞入力規則をたどります。
  2. 条件のタブをクリックします。
  3. 入力値の種類からセルの範囲を選択すると、ソースを入力する枠が現れます。
  4. ここで一度クリックした後、表からB1:H1をマウスで選択してマウスを放します。
  5. ソースに$Sheet1.$B$1:$H$1が自動的に入力されます。

これでOKをクリックすると、名前がK1セルに反映され選択できるようになります。

同様に、K2セルの科目も、上のプロセスを通してA2:A5セルをマウスで選択できるようにします。

これで、入力が効率的になりました。


MATCH関数で行番号が、HLOOKUP関数に自動的に反映されるようにする

上のプロセスで科目が選択してK2セルに入力できるようになりました。選択した科目名が数式に反映されるようにするためにMATCH関数を用います。

関数名：MATCH関数
書式：=MATCH(検索値;範囲;検索の型)
何ができる：検索したい値が範囲の中で何番目の行または列にあるかを返す。
平たく書くと：=MATCH(K2;A1:A5;0) でA1:A5の範囲から、K2に入力されている値（ここでは数学）が何番目にあるかを返します。ここでは3が返されます。
　＊検索の型で0を指定すると、検索値と完全に一致する値が入力されている行または列が返される値になります。


これで、=HLOOKUP(K1;A1:H5;3;0) にMATCH(K2;A1:A5;0)をあてはめると、=HLOOKUP(K1;A1:H5;MATCH(K2;A1:A5;0);0)
となり、名前、科目ともその都度入力する手間がかからなくなります。
ここでは、行を検索するのでHLOOKUP関数を用いました。列を検索する場合はVLOOKUP関数が使えます。こちらの記事をお読みください。

この記事をクリップ！