EXCELで血統表を作ろう！レベル９

EXCELで血統表を作ろう！

レベル９エラーチェック　その３

－血統表中の生年のチェック－

【生年をチェックする】

ある馬とその親の生まれた年の関係を調べ，親子関係に矛盾がないかを調べてみましょう．もしここで矛盾が発見されれば，生年のデータに誤りがあるか，「同名異馬」（同じ名前の全く別の馬）の可能性があります．
馬の親子関係で矛盾がないと考えられるのは，

子は親より年下である．
親子の年齢差は３歳以上３０歳以下である．

という条件を満たした場合とすると，子の生年から親の生年を引き，その答が３以上３０以下なら問題ないという判定をします．

【生年を表示する】

レベル８ と同じように，血統表中の馬の生年を抜き出します．レベル８ で作成した性別を表示する表に，生年も追加して表示させてみましょう．

たとえば，“TABLE5”シートの L13セルには

=VLOOKUP(B13,HD,3,0)

という式が入力されていますが，この式を次のように変更します．

=VLOOKUP(B13,HD,3,0)&VLOOKUP(B13,HD,5,0)

B1セルに“テイエムオペラオー”，B13セルに“オペラハウス”が表示されていた場合，L13セルは“牡1988”となります．

ほかのセルも上のように式を書き換え，性別と生年が同時に表示されるようにしてください．

【チェック欄の式を変更する】

L3セル～P34セルの式を書き換えると，R3セル～V34セルのチェック欄が，すべて“X”になるはずです．
これは，「もし対象セルの内容が“牡”（または“牝”）でないなら“X”を表示しなさい」という判別式を用いているため，“牡1988”のような内容はすべて“牡”（または“牝”）ではないと見なされ，“X”が表示されているのです．

では，式をどのように変更すれば元のように正しく判定できるのでしょうか．
判定の対象を，セルの内容全体ではなく，最初の１文字だけにすれば，“牡”（または“牝”）を使って判定することができますね．
R13セルの式を次のように書き換えてみましょう．

  =IF(LEFT(L13,1)="牡","","X")

LEFT関数は，対象文字列のうち，左から指定した文字数だけ取り出す関数です．上の式では，「L13セル内容の，左から数えて１文字分を抜き出しなさい」という意味になります．

これで性別の判定は元通り行われることになりました．

では，次に生年の判定を行う式を追加します．
子の生年から親の生年を引き，その差が３以上３０以下でないときに“X”を表示させます．

まず，B1セルに入力した血統表本馬の年齢が表示されていないので，これを表示する式を入力しておきましょう．
L1セルに次の式を入力します．

  =VLOOKUP(B1,HD,5,0)

次に，R13セルの式を次のように変更します．

  =IF(LEFT(L13,1)="牡","","X")&IF(OR(L1-RIGHT(L13,4)<3,L1-RIGHT(L13,4)>30),"X","")

後半の式は，「もしL1セルの値からL13セルの後ろ４文字の値を引いた答が３未満，または３０より大きい場合は“X”を，それ以外の場合は空白を表示しなさい」という意味です．
RIGHT関数はLEFT関数のちょうど逆で，文字列の右から指定した文字数分を取り出す関数です．
また，IF関数の中で OR( A , B ) という表記がありますが，これは，「ＡまたはＢの場合」という２つの条件で場合分けすることを表しています．

性別，または生年のどちらか一方に誤りがあれが“X”が１つ，両方に誤りがあれば“X”が２つ表示されます．
“DATA”シートの“オペラハウス”の性別，生年を変更し，チェックが正しく行われているか確認してください．
チェック終了後，データを元に戻すのを忘れないようにしましょう．

R13以外のセルにも変更した式を入力します．たとえば，S8セルの式は次のようになります．

  =IF(LEFT(M8,1)="牡","","X")&IF(OR(RIGHT(L13,4)-RIGHT(M8,4)<3,RIGHT(L13,4)-RIGHT(M8,4)>30),"X","")

上の式を参考にして，他のセルにも式を入力してください．全部入力すると，次のようになります．

	Ｒ	Ｓ	Ｔ	Ｕ	Ｖ
1
2
3					=IF(LEFT(P3,1)="牡","","X")&IF(OR(RIGHT(O4,4)-RIGHT(P3,4)<3,RIGHT(O4,4)-RIGHT(P3,4)>30),"X","")
4				=IF(LEFT(O4,1)="牡","","X")&IF(OR(RIGHT(N5,4)-RIGHT(O4,4)<3,RIGHT(N5,4)-RIGHT(O4,4)>30),"X","")	=IF(LEFT(P4,1)="牝","","X")&IF(OR(RIGHT(O4,4)-RIGHT(P4,4)<3,RIGHT(O4,4)-RIGHT(P4,4)>30),"X","")
5			=IF(LEFT(N5,1)="牡","","X")&IF(OR(RIGHT(M8,4)-RIGHT(N5,4)<3,RIGHT(M8,4)-RIGHT(N5,4)>30),"X","")	=IF(LEFT(O5,1)="牝","","X")&IF(OR(RIGHT(N5,4)-RIGHT(O5,4)<3,RIGHT(N5,4)-RIGHT(O5,4)>30),"X","")	=IF(LEFT(P5,1)="牡","","X")&IF(OR(RIGHT(O5,4)-RIGHT(P5,4)<3,RIGHT(O5,4)-RIGHT(P5,4)>30),"X","")
6					=IF(LEFT(P4,1)="牝","","X")&IF(OR(RIGHT(O5,4)-RIGHT(P6,4)<3,RIGHT(O5,4)-RIGHT(P6,4)>30),"X","")
7					=IF(LEFT(P3,1)="牡","","X")&IF(OR(RIGHT(O8,4)-RIGHT(P7,4)<3,RIGHT(O8,4)-RIGHT(P7,4)>30),"X","")
8		=IF(LEFT(M8,1)="牡","","X")&IF(OR(RIGHT(L13,4)-RIGHT(M8,4)<3,RIGHT(L13,4)-RIGHT(M8,4)>30),"X","")	=IF(LEFT(N8,1)="牝","","X")&IF(OR(RIGHT(M8,4)-RIGHT(N8,4)<3,RIGHT(M8,4)-RIGHT(N8,4)>30),"X","")	=IF(LEFT(O8,1)="牡","","X")&IF(OR(RIGHT(N8,4)-RIGHT(O8,4)<3,RIGHT(N8,4)-RIGHT(O8,4)>30),"X","")	=IF(LEFT(P4,1)="牝","","X")&IF(OR(RIGHT(O8,4)-RIGHT(P8,4)<3,RIGHT(O8,4)-RIGHT(P8,4)>30),"X","")
9				=IF(LEFT(O9,1)="牝","","X")&IF(OR(RIGHT(N8,4)-RIGHT(O9,4)<3,RIGHT(N8,4)-RIGHT(O9,4)>30),"X","")	=IF(LEFT(P5,1)="牡","","X")&IF(OR(RIGHT(O9,4)-RIGHT(P9,4)<3,RIGHT(O9,4)-RIGHT(P9,4)>30),"X","")
10					=IF(LEFT(P4,1)="牝","","X")&IF(OR(RIGHT(O9,4)-RIGHT(P10,4)<3,RIGHT(O9,4)-RIGHT(P10,4)>30),"X","")
11					=IF(LEFT(P11,1)="牡","","X")&IF(OR(RIGHT(O12,4)-RIGHT(P11,4)<3,RIGHT(O12,4)-RIGHT(P11,4)>30),"X","")
12				=IF(LEFT(O12,1)="牡","","X")&IF(OR(RIGHT(N13,4)-RIGHT(O12,4)<3,RIGHT(N13,4)-RIGHT(O12,4)>30),"X","")	=IF(LEFT(P12,1)="牝","","X")&IF(OR(RIGHT(O12,4)-RIGHT(P12,4)<3,RIGHT(O12,4)-RIGHT(P12,4)>30),"X","")
13	=IF(LEFT(L13,1)="牡","","X")&IF(OR(L1-RIGHT(L13,4)<3,L1-RIGHT(L13,4)>30),"X","")	=IF(LEFT(M13,1)="牝","","X")&IF(OR(RIGHT(L13,4)-RIGHT(M13,4)<3,RIGHT(L13,4)-RIGHT(M13,4)>30),"X","")	=IF(LEFT(N13,1)="牡","","X")&IF(OR(RIGHT(M13,4)-RIGHT(N13,4)<3,RIGHT(M13,4)-RIGHT(N13,4)>30),"X","")	=IF(LEFT(O13,1)="牝","","X")&IF(OR(RIGHT(N13,4)-RIGHT(O13,4)<3,RIGHT(N13,4)-RIGHT(O13,4)>30),"X","")	=IF(LEFT(P13,1)="牡","","X")&IF(OR(RIGHT(O13,4)-RIGHT(P13,4)<3,RIGHT(O13,4)-RIGHT(P13,4)>30),"X","")
14					=IF(LEFT(P14,1)="牝","","X")&IF(OR(RIGHT(O13,4)-RIGHT(P14,4)<3,RIGHT(O13,4)-RIGHT(P14,4)>30),"X","")
15					=IF(LEFT(P15,1)="牡","","X")&IF(OR(RIGHT(O16,4)-RIGHT(P15,4)<3,RIGHT(O16,4)-RIGHT(P15,4)>30),"X","")
16			=IF(LEFT(N16,1)="牝","","X")&IF(OR(RIGHT(M13,4)-RIGHT(N16,4)<3,RIGHT(M13,4)-RIGHT(N16,4)>30),"X","")	=IF(LEFT(O16,1)="牡","","X")&IF(OR(RIGHT(N16,4)-RIGHT(O16,4)<3,RIGHT(N16,4)-RIGHT(O16,4)>30),"X","")	=IF(LEFT(P16,1)="牝","","X")&IF(OR(RIGHT(O16,4)-RIGHT(P16,4)<3,RIGHT(O16,4)-RIGHT(P16,4)>30),"X","")
17				=IF(LEFT(O17,1)="牝","","X")&IF(OR(RIGHT(N16,4)-RIGHT(O17,4)<3,RIGHT(N16,4)-RIGHT(O17,4)>30),"X","")	=IF(LEFT(P17,1)="牡","","X")&IF(OR(RIGHT(O17,4)-RIGHT(P17,4)<3,RIGHT(O17,4)-RIGHT(P17,4)>30),"X","")
18					=IF(LEFT(P18,1)="牝","","X")&IF(OR(RIGHT(O17,4)-RIGHT(P18,4)<3,RIGHT(O17,4)-RIGHT(P18,4)>30),"X","")
19					=IF(LEFT(P19,1)="牡","","X")&IF(OR(RIGHT(O20,4)-RIGHT(P19,4)<3,RIGHT(O20,4)-RIGHT(P19,4)>30),"X","")
20				=IF(LEFT(O20,1)="牡","","X")&IF(OR(RIGHT(N21,4)-RIGHT(O20,4)<3,RIGHT(N21,4)-RIGHT(O20,4)>30),"X","")	=IF(LEFT(P20,1)="牝","","X")&IF(OR(RIGHT(O20,4)-RIGHT(P20,4)<3,RIGHT(O20,4)-RIGHT(P20,4)>30),"X","")
21			=IF(LEFT(N21,1)="牡","","X")&IF(OR(RIGHT(M24,4)-RIGHT(N21,4)<3,RIGHT(M24,4)-RIGHT(N21,4)>30),"X","")	=IF(LEFT(O21,1)="牝","","X")&IF(OR(RIGHT(N21,4)-RIGHT(O21,4)<3,RIGHT(N21,4)-RIGHT(O21,4)>30),"X","")	=IF(LEFT(P21,1)="牡","","X")&IF(OR(RIGHT(O21,4)-RIGHT(P21,4)<3,RIGHT(O21,4)-RIGHT(P21,4)>30),"X","")
22					=IF(LEFT(P22,1)="牝","","X")&IF(OR(RIGHT(O21,4)-RIGHT(P22,4)<3,RIGHT(O21,4)-RIGHT(P22,4)>30),"X","")
23					=IF(LEFT(P23,1)="牡","","X")&IF(OR(RIGHT(O24,4)-RIGHT(P23,4)<3,RIGHT(O24,4)-RIGHT(P23,4)>30),"X","")
24	=IF(LEFT(L24,1)="牝","","X")&IF(OR(L1-RIGHT(L24,4)<3,L1-RIGHT(L24,4)>30),"X","")	=IF(LEFT(M24,1)="牡","","X")&IF(OR(RIGHT(L24,4)-RIGHT(M24,4)<3,RIGHT(L24,4)-RIGHT(M24,4)>30),"X","")	=IF(LEFT(N24,1)="牝","","X")&IF(OR(RIGHT(M24,4)-RIGHT(N24,4)<3,RIGHT(M24,4)-RIGHT(N24,4)>30),"X","")	=IF(LEFT(O24,1)="牡","","X")&IF(OR(RIGHT(N24,4)-RIGHT(O24,4)<3,RIGHT(N24,4)-RIGHT(O24,4)>30),"X","")	=IF(LEFT(P24,1)="牝","","X")&IF(OR(RIGHT(O24,4)-RIGHT(P24,4)<3,RIGHT(O24,4)-RIGHT(P24,4)>30),"X","")
25				=IF(LEFT(O25,1)="牝","","X")&IF(OR(RIGHT(N24,4)-RIGHT(O25,4)<3,RIGHT(N24,4)-RIGHT(O25,4)>30),"X","")	=IF(LEFT(P25,1)="牡","","X")&IF(OR(RIGHT(O25,4)-RIGHT(P25,4)<3,RIGHT(O25,4)-RIGHT(P25,4)>30),"X","")
26					=IF(LEFT(P26,1)="牝","","X")&IF(OR(RIGHT(O25,4)-RIGHT(P26,4)<3,RIGHT(O25,4)-RIGHT(P26,4)>30),"X","")
27					=IF(LEFT(P27,1)="牡","","X")&IF(OR(RIGHT(O28,4)-RIGHT(P27,4)<3,RIGHT(O28,4)-RIGHT(P27,4)>30),"X","")
28				=IF(LEFT(O28,1)="牡","","X")&IF(OR(RIGHT(N29,4)-RIGHT(O28,4)<3,RIGHT(N29,4)-RIGHT(O28,4)>30),"X","")	=IF(LEFT(P28,1)="牝","","X")&IF(OR(RIGHT(O28,4)-RIGHT(P28,4)<3,RIGHT(O28,4)-RIGHT(P28,4)>30),"X","")
29		=IF(LEFT(M29,1)="牝","","X")&IF(OR(RIGHT(L24,4)-RIGHT(M29,4)<3,RIGHT(L24,4)-RIGHT(M29,4)>30),"X","")	=IF(LEFT(N29,1)="牡","","X")&IF(OR(RIGHT(M29,4)-RIGHT(N29,4)<3,RIGHT(M29,4)-RIGHT(N29,4)>30),"X","")	=IF(LEFT(O29,1)="牝","","X")&IF(OR(RIGHT(N29,4)-RIGHT(O29,4)<3,RIGHT(N29,4)-RIGHT(O29,4)>30),"X","")	=IF(LEFT(P29,1)="牡","","X")&IF(OR(RIGHT(O29,4)-RIGHT(P29,4)<3,RIGHT(O29,4)-RIGHT(P29,4)>30),"X","")
30					=IF(LEFT(P30,1)="牝","","X")&IF(OR(RIGHT(O29,4)-RIGHT(P30,4)<3,RIGHT(O29,4)-RIGHT(P30,4)>30),"X","")
31					=IF(LEFT(P31,1)="牡","","X")&IF(OR(RIGHT(O32,4)-RIGHT(P31,4)<3,RIGHT(O32,4)-RIGHT(P31,4)>30),"X","")
32			=IF(LEFT(N32,1)="牝","","X")&IF(OR(RIGHT(M29,4)-RIGHT(N32,4)<3,RIGHT(M29,4)-RIGHT(N32,4)>30),"X","")	=IF(LEFT(O32,1)="牡","","X")&IF(OR(RIGHT(N32,4)-RIGHT(O32,4)<3,RIGHT(N32,4)-RIGHT(O32,4)>30),"X","")	=IF(LEFT(P32,1)="牝","","X")&IF(OR(RIGHT(O32,4)-RIGHT(P32,4)<3,RIGHT(O32,4)-RIGHT(P32,4)>30),"X","")
33				=IF(LEFT(O33,1)="牝","","X")&IF(OR(RIGHT(N32,4)-RIGHT(O33,4)<3,RIGHT(N32,4)-RIGHT(O33,4)>30),"X","")	=IF(LEFT(P33,1)="牡","","X")&IF(OR(RIGHT(O33,4)-RIGHT(P33,4)<3,RIGHT(O33,4)-RIGHT(P33,4)>30),"X","")
34					=IF(LEFT(P34,1)="牝","","X")&IF(OR(RIGHT(O33,4)-RIGHT(P34,4)<3,RIGHT(O33,4)-RIGHT(P34,4)>30),"X","")

【データを確認する】

式の書き換えはうまくできましたか．面倒な方は上の表をそのままコピーしてください．

チェックが正しく行われるか，データを一部書き換えて確認します．
“DATA”シートの“性別”や“生年”のデータを書き換え，チェックしてみてください．
チェック終了後，忘れずにデータを元に戻してください．
データを書き換える前に，“DATA”シートをそのままコピーしておくと，万が一元のデータがわからなくなったときに簡単に元に戻せます．

【おまけ】

どうしてもうまくいかなかった方にプレゼントです．
レベル６ までで作成した血統表に レベル７～レベル９のチェック部分をつけたファイルを用意しましたので，ダウンロードしてご利用ください．

圧縮ファイル（ＺＩＰ形式，１６ＫＢ）とEXCELファイル（５５ＫＢ）の２つがありますが，中身は全く同じものです．

圧縮ファイル Pedigree2.zip (16KB)

EXCELファイル Pedigree2.xls (55KB)
（右クリックしてファイルを保存してください）

次のレベル１０では，いよいよマクロを作ってみます．
とりあえず簡単に作れて役に立つものを考えましたので，ぜひこちらにも挑戦してくださいね．

レベル１０へ

EXCELで血統表を作ろう！目次へ

トップページへ