Level 9 错误检查 其3
－检查血统表中的生年－

【检查生年】

通过检查某匹马与其父母的出生年份关系，判断是否存在矛盾。如果发现矛盾，可能是生年数据有误，或者存在“同名异马”（名字相同但完全不同的马）。
在马匹的亲子关系中，以下条件被认为是合理的：

1. 子女的出生年份必须晚于父母。
2. 父母与子女的年龄差应在3岁到30岁之间。

基于此，我们可以通过计算子女的出生年份减去父母的出生年份，如果结果在3到30之间，则没有问题。

【显示生年】

与Level 8类似，提取血统表中马的出生年份。在Level 8中创建的性别显示表中，添加生年显示。

例如，“TABLE5”工作表的L13单元格原本输入了以下公式：
=VLOOKUP(B13,HD,3,0)
现在将其修改为：
=VLOOKUP(B13,HD,3,0)&VLOOKUP(B13,HD,5,0)
如果B1单元格显示“T M Opera O”，B13单元格显示“Opera House”，则L13单元格将显示“公1988”。

请将其他单元格的公式也按此修改，使性别和生年同时显示。

【修改检查栏的公式】

修改L3到P34单元格的公式后，R3到V34单元格的检查栏应全部显示“X”。
这是因为检查公式是基于“如果目标单元格的内容不是‘公’（或‘母’），则显示‘X’”，而“公1988”这样的内容被视为不符合条件，因此显示“X”。

那么，如何修改公式才能正确判断呢？
只需将判断对象从单元格的完整内容改为第一个字符即可。将R13单元格的公式修改为：
=IF(LEFT(L13,1)="公","","X")
LEFT函数用于从字符串的左侧提取指定数量的字符。上述公式的意思是：“提取L13单元格内容的第一个字符，如果是‘公’，则显示空白，否则显示‘X’。”

这样，性别的判断功能就恢复正常了。

接下来，添加生年判断的公式。
如果子女的出生年份减去父母的出生年份不在3到30之间，则显示“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单元格的最后4个字符的值小于3，或大于30，则显示‘X’，否则显示空白。” RIGHT函数与LEFT函数相反，用于从字符串的右侧提取指定数量的字符。
此外，OR(A, B)表示“如果A或B成立”，用于处理多个条件。

如果性别或生年中的任意一个存在错误，则显示一个“X”；如果两者都有错误，则显示两个“X”。
请修改“DATA”工作表中“Opera House”的性别和生年，确认检查功能是否正常工作。
检查完成后，请记得将数据恢复原状。

将修改后的公式输入到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”工作表，以便在需要时轻松恢复原始数据。

【额外提示】

如果无论如何都无法顺利完成，这里有一个礼物送给你。
我准备了一个文件，包含Level 6中完成的血统表，并添加了Level 7～Level 9的检查功能，你可以下载并使用。

提供压缩文件（ZIP格式，16KB）和EXCEL文件（55KB），内容完全相同。

压缩文件 Pedigree2.zip (16KB)

EXCEL文件 Pedigree2.xls (55KB)

在接下来的Level 10中，我们将开始制作宏。
我们设计了一些简单且实用的宏，请务必继续挑战！