EXCELで血統表を作ろう!

レベル1  まず3代血統表を作ってみよう

−VLOOKUP関数を使って血統表を作る−

EXCELで血統表を作ろう!  目次へ
トップページへ


【表の枠を作る】

はじめにEXCELの「新規作成」で新しいブックを用意してください.
空のシートに,次のように罫線を引きます.

EXCEL_3TABLE


【データベースを作る】

次に,データベースの部分を作成します.
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関数は,指定した範囲のうち左端の列の値で検索し,条件にあったセルの値を取り出す関数です.

=VLOOKUP( A1 , E2:G16 , 2 , 0 )

A1 検索するためのキーとなる値.この場合はA1に入力された値をキーとして,指定範囲内の左端の列を探します.
E2:G16 値を探すセル範囲.
2 指定した範囲のうち,左から数えて“2”列目のセルの値を取り出せという指示.ここでは“父”の馬を取り出すことを指示しています.
0 キーとなる値が範囲の左端列に存在しなかったときにどうするかを指定.
“0”または“FALSE”を指定すると,キーとなる値がなかった場合はエラーを返します.
“1”または“TRUE”を指定するか,あるいは省略すると,キーの値未満でもっとも大きい値を返します.

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


【レベル2に挑戦!】

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で血統表を作ろう!  目次へ
トップページへ


address=http://cosmarr.com