2011-11-01

VLOOKUP関数とMATCH関数を使って検索を効率的に行なう

OpenOffice

HLOOKUP関数とMATCH関数を使って効率的に表を検索するでHLOOKUP関数を使って得点を検索する方法を紹介しました。
今度は、VLOOKUP関数を使って行なう方法を紹介します。

このサンプルファイルは、SKYDRIVEからcalc131.odsをダウンロードしてください。
HLOOKUP関数は、指定した範囲の一番上の行の値を検索して、その下の、指定した行の値を返す関数でした。
VLOOKUP関数は、指定した範囲の左端の列の値を検索し、その右側の、指定した列の値を返す関数です。

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

HLOOKUP関数を使って検索したときにも紹介したように、こちらでもリストによる選択入力とMATCH関数による列に自動選択ができるようにします。
H1セルには$Sheet1.$A$2:$A$8の内容が選択入力出来るように設定しています。

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

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

これでOKをクリックすると、名前がH1セルに反映され選択できるようになります。
これで、H3セルの関数の数式は =VLOOKUP(H1;A1:E8;3;0)　になり、名前をその都度数式に入力する必要がなくなりました。

同様に科目もH2セルに上のプロセスを通してB1;E1の内容が反映されるようにします。

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

次にMATCH関数を使って、B1:E1のなかから、H2に選択した科目が表の中の何列目にあるか、自動的に選択できるようにします。
関数名：MATCH関数
書式：=MATCH(検索値;範囲;検索の型)
何ができる：検索したい値が範囲の中で何番目の行または列にあるかを返す。
平たく書くと：=MATCH(H2;A1:E1;0) でA1:E1の範囲から、H2に入力されている値（ここでは数学）が何番目にあるかを返します。ここでは3が返されます。
　＊検索の型で0を指定すると、検索値と完全に一致する値が入力されている行または列が返される値になります。

これでこれで、=VLOOKUP(H1;A1:E8;3;0) にMATCH(H2;A1:E1;0)をあてはめると、
=VLOOKUP(H1;A1:E8;MATCH(H2;A1:E1;0);0)
となり、名前、科目ともその都度入力する手間がかからなくなります。

2011-11-01

一覧表のデータに一律に50を加える、便利なコピー＆貼り付けの方法　形式を選択して貼り付け

OpenOffice

まず、加えたい数値50が入力されているセルをコピーします。

送信者 OpenOffice

50を加えたいセルをマウスで選択します。
ツールバーから形式を選択して貼り付けを選びます。

送信者 OpenOffice

右側の演算から足すを選択してOKをクリックます。

送信者 OpenOffice

このとおり、一気に50増えました。

送信者 OpenOffice

多分、28個のセルにいちいち50を加えていたら少なくとも3分はかかってしまうのではないでしょうか？それがたったの数秒でできてしまう便利技です。
足し算のほかにも引き算、掛け算、割り算も可能です。
この便利な機能でどのようなことができるか、いろいろ試してみてください。
サンプルファイルをSKYDRIVEに用意していますので、calc134.odsをダウンロードしてください。

形式を選択して貼り付けについて

も書きました。よかったら見てください。

以上、作業の効率化に役立てばうれしいです。

全然関係ない話ですが、一日三杯コーヒーを飲むと前立腺がんや皮膚がんの発症リスクが低くなる可能性があって、炭酸飲料をたくさん飲むと暴力的になるそうです。