レベル1 まず3代血統表を作ってみよう −VLOOKUP関数を使って血統表を作る− |
EXCELで血統表を作ろう! 目次へ | トップページへ |
はじめにEXCELの「新規作成」で新しいブックを用意してください. 空のシートに,次のように罫線を引きます. |
次に,データベースの部分を作成します. E1からG16のセル範囲に,以下のデータを入力してください. 画面をそのままコピーする場合は,表の範囲を選択し,EXCELのE1セルの位置にコピー&ペーストしてください. たぶん表の形のまま貼り付けられると思います. |
馬 名 | 父 | 母 |
Blushing Groom | Red God | Runaway Bride |
Colorspin | High Top | Reprocolor |
Fairy Bridge | Bold Reason | Special |
High Top | Derring-Do | Camenae |
Key to the Kingdom | Bold Ruler | Key Bridge |
Northern Dancer | Nearctic | Natalma |
Noura | Key to the Kingdom | River Guide |
Red God | Nasrullah | Spring Run |
Reprocolor | Jimmy Reppin | Blue Queen |
River Guide | Drone | Blue Canoe |
Runaway Bride | Wild Risk | Aimee |
Sadler's Wells | Northern Dancer | Fairy Bridge |
オペラハウス | Sadler's Wells | Colorspin |
テイエムオペラオー | オペラハウス | ワンスウェド |
ワンスウェド | Blushing Groom | Noura |
空の表とデータベースができたら,A1セルに“テイエムオペラオー”と全角で入力します. ここは血統表を作りたい馬の名前を入力するセルです. 次に,A4セルに =VLOOKUP(A1,E2:G16,2,0) と入力してください. ここは血統表の中の“父”を表示するセルになります. セルの値は“オペラハウス”になりましたか? |
VLOOKUP関数は,指定した範囲のうち左端の列の値で検索し,条件にあったセルの値を取り出す関数です.
VLOOKUP関数の最後の引数(上の例では“0”)ですが,血統表内で使用するときは必ず“0”か“FALSE”にします. 試しに,A1セルの値を“ステイゴールド”,A4セルの数式を =VLOOKUP(A1,E2:G16,2,1) と書き換えてみましょう.セルの値はどうなりますか? 実際には“ステイゴールド”はデータベースに登録されていません.本当ならエラーが表示されるはずですが,A4セルの値は“Sadler's Wells”となっています.これは,キーとなる値が見つからなかったので,その値未満でもっとも大きい値を探し,それが“オペラハウス”だったため,その父の“Sadler's Wells”を表示してしまったのです.勝手に違う馬の血統表を作られては困るので,最後の引数は必ず“0”または“FALSE”にしてください. A4セルの数式を =VLOOKUP(A1,E2:G16,2,0) に戻してみましょう. 今度は“#N/A”と表示されました.これは,該当する値が見つからなかったことを示しています. |
A1セルを“テイエムオペラオー”に戻し,次にA8セルに =VLOOKUP(A1,E2:G16,3,0) と入力します.A4セルとほとんど同じ式ですが,3つ目の引数が“2”から“3”になっています. 今度のセルには“母”を表示させたいので,データベースの中で“母”の名前が入力されている3つ目の列を指定します. A8セルの値は“ワンスウェド”になりましたか? |
血統表の基本は,ある馬の父親と母親の名前を探して表示し,さらに,その父親の父と母を探して表示,母親の父と母を探して表示,...というように,同じことを次々に繰り返しているだけです. では,B3セルに“オペラハウス”の父を表示させるにはどうすればいいでしょうか. 今度はキーとなる値はA4セルに入力されています.あとは同じように =VLOOKUP(A4,E2:G16,2,0) と入力すればOKです. |
データを探す範囲を指定するのに,毎回“E2:G16”を入力するのは面倒ですね. 決まった範囲に名前(範囲名)をつけ,この名前を使うこともできます. E2からG16の範囲を選択します. メニューから 挿入→名前→定義 を選んで,選択した範囲に“HD”という名前をつけます. (またはメニューバーの左下にある「名前ボックス」をクリックし,“HD”と入力) “HD”は“HORSE DATA”の略のつもりでつけました. 範囲名は「HorseData」でも「馬血統」でも「ほにゃらら」でも何でもかまいませんが,範囲の内容がわかりやすく,あまり長くない名前をつけておきましょう. 範囲名をつけたら,B3セルの式のうち,“E2:G16”を“HD”(またはあなたがつけた範囲名)に変更して,正しく検索できることを確認します. なお,範囲名を入力するときに," "(ダブルクォーテーション)などをつけないでください. オペラハウスの父である“Sadler's Wells”の名前が正しく表示されていますか. A4セルとA8セルの式も同じように範囲名を変更しておきましょう. |
次はB5セルにオペラハウスの母親,B7セルにワンスウェドの父親,B9セルにワンスウェドの母親を表示させてみてください. ここまでできれば,あとは同じ作業の繰り返しです.C3セルからC10セルにもVLOOKUP関数の式を入力し,3代血統表を完成させてみましょう. 次の表のように入力すれば,正しく血統表が表示されるはずです. |
A | B | C | |
1 | (馬名を入力するセル) | ||
2 | |||
3 | =VLOOKUP(A4,HD,2,0) | =VLOOKUP(B3,HD,2,0) | |
4 | =VLOOKUP(A1,HD,2,0) | =VLOOKUP(B3,HD,3,0) | |
5 | =VLOOKUP(A4,HD,3,0) | =VLOOKUP(B5,HD,2,0) | |
6 | =VLOOKUP(B5,HD,3,0) | ||
7 | =VLOOKUP(A8,HD,2,0) | =VLOOKUP(B7,HD,2,0) | |
8 | =VLOOKUP(A1,HD,3,0) | =VLOOKUP(B7,HD,3,0) | |
9 | =VLOOKUP(A8,HD,3,0) | =VLOOKUP(B9,HD,2,0) | |
10 | =VLOOKUP(B9,HD,3,0) |
表が完成すると,テイエムオペラオーの血統表が表示されます. |
A | B | C | |
1 | テイエムオペラオー | ||
2 | |||
3 | オペラハウス | Sadler's Wells | Northern Dancer |
4 | Fairy Bridge | ||
5 | Colorspin | High Top | |
6 | Reprocolor | ||
7 | ワンスウェド | Blushing Groom | Red God |
8 | Runaway Bride | ||
9 | Noura | Key to the Kingdom | |
10 | River Guide |
この血統表が,テイエムオペラオー以外でも正しく表示されるかどうか確認してみましょう. A1セルに“オペラハウス”と入力します.血統表はどうなりましたか. 下のような血統表が表示されたら大成功です. データベース部分に馬のデータが登録されてさえいれば,どんな馬でも血統表を表示させることができます. |
A | B | C | |
1 | オペラハウス | ||
2 | |||
3 | Sadler's Wells | Northern Dancer | Nearctic |
4 | Natalma | ||
5 | Fairy Bridge | Bold Reason | |
6 | Special | ||
7 | Colorspin | High Top | Derring-Do |
8 | Camenae | ||
9 | Reprocolor | Jimmy Reppin | |
10 | Blue Queen |
A1セルに“Colorspin”と入力してみてください. |
A | B | C | |
1 | Colorspin | ||
2 | |||
3 | High Top | Derring-Do | #N/A |
4 | #N/A | ||
5 | Camenae | #N/A | |
6 | #N/A | ||
7 | Reprocolor | Jimmy Reppin | #N/A |
8 | #N/A | ||
9 | Blue Queen | #N/A | |
10 | #N/A |
2代目までは正しく表示されていますが,3代目の列は“#N/A”になっています. これは,2代目の馬(Derring-DoやCamenae)のデータが登録されていなかったので,検索値を表示することができなかったという意味です. データが存在しないのですから,これは仕方ないのですが,表の中にエラーが出るよりは,何も表示されない方が見た目がいいですよね. レベル2 では IF関数を使ってこのようなエラーを表示させない方法について説明します. また,レベル3 では,馬の名前だけでなく性別・毛色・生年なども表示させて,より本格的な血統表を作っていきます. レベル1 で作った血統表は レベル2 でも使いますので,ファイル名をつけて保存しておいてください. |
レベル2へ | EXCELで血統表を作ろう! 目次へ | トップページへ |