5.6 青年学科带头人初选表
案例背景
根据上级主管部门的指派，学校经常要根据不同要求上报有关全校教师队伍的整体结构情况的统计报表，有的时候不仅需要统计数字，还需要查看具体是那些教师满足要求，比如说教育局在2007年春季布置评选市、区级青年学科带头人，要求年龄条件为：男性教师在40岁以下，女性教师在35岁以下（截止到2007年8月31日以前，男性教师不超过40岁，女性教师不超过35岁）；要求学历条件在本科以上，情况基本相同的学历高的优先考虑，职称要求为“一级”或“高级”教师，其中，市级青年学科带头人要求具有高级职称。这时候负责这项工作的同志就需要在全校教师情况登记表中筛选中满足基本条件的教师作为候选教师参加初选。前面已经学习了数据的自动筛选，自动筛选可以实现在同一字段内的“与”和“或”的筛选，在不同字段间只能实现“与”的筛选，不能实现“或”的筛选，那么如何进行类似本案例中的多条件筛选呢？本案例以某中学根据教育局的部署对本校全体教师按照基本条件进行初选为例，应用Excel高级筛选技术来实现上述的多条件筛选。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 数字的日期格式
●函数应用 YEAR函数，MONTH函数 ，IF函数 ，
●综述 高级筛选
最终效果展示
5.6.1创建“市级青年学科带头人初选表”
Step 1创建工作簿、重命名工作表
创建工作簿“学科带头人初选表.xls”，然后将工作表“Sheet2“和”Sheet1“重命名为“区级“和”市级“。
Step 2输入表格字段名
单击“市级”工作表标签，在单元格区域A1:H1输入字段名：“编号“，”性别“，”出生年月日“，”职称“，”现学历“，”学科“，”参评年龄“。
Step 3输入教师原始信息
在单元格区域A2:G58输入全校教师的原始信息。
Step4由教师“出生年月日“换算“参评年龄“
①选中单元格H2，在编辑栏输入以下公式，然后按键确认，即可换算出第一个教师的“参评年龄“。
“=IF(MONTH(D2)<9,2007-YEAR(D2),2007-YEAR(D2)-1) “
②双击单元格H2右下角的填充柄即可完成其余56名教师 “参评年龄“的换算。
Step5修饰美化教师原始信息表
①选中单元格区域A1:H58，适当调整列宽，设置表格边框，文字居中对齐，字段名区域自动换行并添加灰色底纹。
②选中单元格区域D2:D58，按组合键弹出“单元格格式”对话框，切换到“数字”选项卡，在“分类”选项框中选择“日期”，在“类别”选项框中选择“*2001-3-14”
③单击“确定”按钮。
Step6输入“高级筛选“条件
①选中单元格区域E60:H62，首先为该区域设置边框及文本居中对齐，然后在E60:H60输入字段名“性别”，“职称”，“现学历”和“参评年龄”；
②在单元格区域E61:H61输入男教师参评市级青年学科带头人条件：“男”，“高级”，“<>"大专"”，“<=40”；
③在单元格区域E62:H62输入女教师参评市级青年学科带头人条件：“女”，“高级”，“<>"大专"”，“<=35”；
Step7高级筛选
①选中单元格区域A1:H58内任意单元格如D54，单击菜单“数据”→“筛选”→“高级筛选”,弹出“高级筛选”对话框。
②在“高级筛选”对话框，“方式”列表框中选择“将筛选结果复制到其他位置”，单击“列表区域”框右侧按钮，用鼠标选择单元格区域”$A$1: $H$58”, 单击“条件区域”框右侧按钮，用鼠标选择单元格区域$E$60: $H$62 , 单击“复制到”框右侧按钮，用鼠标选择单元格A64。
③单击“确定”按钮，即可筛选出符合市级青年学科带头人基本条件的教师初选名单
④在单元格A63中输入标题“市级青年学科带头人初选表”，选中单元格区域A63:H63，单击常用工具栏“合并及居中”。
5.6.2创建“区级青年学科带头人初选表”
Step 1输入教师原始信息
选中A1:H58单元格区域，按组合键复制，单击“区级”工作表标签，单击单元格A1，按组合键粘贴，然后适当调整列宽。
Step 2输入高级筛选条件
①选中单元格区域E60:H64，首先为该区域设置边框及文本居中对齐，然后在单元格区域E60:H60陆续输入字段名“性别”，“职称”，“现学历”和“参评年龄”；
②在单元格区域E61:H61和E62:H62，分别输入男教师参评市级青年学科带头人条件：“男”，“高级”，“<>"大专"”，“<=40”和“男”，“一级”，“<>"大专"”，“<=40”。
③在单元格区域E63:H63和E64:H64，输入女教师参评市级青年学科带头人条件：“女”，“高级”，“<>"大专"”，“<=40”和“女”，“一级”，“<>"大专"”，“<=40”。
Step 3
参照6.2.1筛选市级青年学科带头人的过程通过高级筛选得到“区级青年学科带头人”的初选名单
至此通过使用“高级筛选”技术进行初选，符合市级和区级青年学科带头人基本条件的教师名单全部已经列出，学校可根据这些教师的能力，业绩等因素综合考虑选出本校最合适的人选推荐到区里参评。
关键知识点讲解：
1.YEAR函数
函数名称：YEAR
主要功能：返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。
使用格式：YEAR(serial_number)
参数说明：
Serial_number 为一个日期值，其中包含要查找年份的日期。应使用 DATE 函数来输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本的形式输入，则会出现问题。
函数说明
Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一个默认日期系统。
由 YEAR、MONTH 和 DAY 函数返回的值是 Gregorian 值，不管所提供的日期值是怎样的显示格式。例如，如果所提供的日期值显示格式是 Hijri，YEAR、MONTH 和 DAY 函数返回的值将是对应 Gregorian 日期的值。
应用示例
2.MONTH函数
函数名称：MONTH
主要功能：返回以序列号表示的日期中的月份。月份是介于 1（一月）到 12（十二月）之间的整数。
使用格式：MONTH(serial_number)
参数说明：
Serial_number 表示一个日期值，其中包含要查找的月份。应使用 DATE 函数来输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本的形式输入，则会出现问题。
函数说明
Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一个默认日期系统。
由 YEAR、MONTH 和 DAY 函数返回的值为公历值，而不用考虑所给日期值的显示格式。例如，如果所给日期值的显示格式为 Hijri，则 YEAR、MONTH 和 DAY 函数的返回值将是与等价的公历日期相关的值。
应用示例
3.DAY函数
函数名称：DAY
主要功能：返回以序列号表示的某日期的天数，用整数 1 到 31 表示。
使用格式：DAY(serial_number)
参数说明：
Serial_number 为要查找的那一天的日期。应使用 DATE 函数来输入日期，或者将日期作为其他公式或函数的结果输入。例如，可使用函数 DATE(2008,5,23) 输入日期 2008 年 5 月 23 日。如果日期以文本的形式输入，则会出现问题。
函数说明
Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一个默认日期系统。
不论日期值以何种格式显示，YEAR，MONTH 和 DAY 函数返回的值都是 Gregorian 值。例如，如果日期的显示格式是回历，则 YEAR、MONTH 和 DAY 函数返回的值将是与相同的 Gregorian 日期相关联的值。
应用示例：
高级筛选：
使用高级条件筛选
1.在可用作条件区域的区域上方插入至少三个空白行。条件区域必须具有列标签。请确保在条件值与区域之间至少留了一个空白行。
外观示例
2.在列标志下面的一行中，键入所要匹配的条件。
条件示例
单列上具有多个条件
如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。例如，下面的条件区域显示“职称”列中包含“高级”或“一级”的行。
多列上具有单个条件
若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。例如，下面的条件区域将显示所有在“性别”列中包含“男”、在“职称”列中包含“高级”在“现学历”列中包含“高级”，在“参评年龄”小于等于 40 的数据行。
某一列或另一列上具有单个条件
若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，下面的条件区域将显示所有在“性别”列中为“男”、或“职称”列为“高级”或“参评年龄”<于 40的行。（准确的说，在同一行不同字段的条件是“与（AND）”的关系；不在同一行的条件是“或（OR）”的关系），根据下面的高级筛选条件筛选出来的结果是男性初级教师或具有高级职称的教师或年龄不足40岁的教师
两列上具有两组条件之一
若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。例如，下面的条件区域将显示所有在“性别”列中包含“男”且“职称”是“初级“或“高级”的教师。
一列有两组以上条件
若要找到满足两组以上条件的行，请用相同的列标包括多列。例如，下面条件区域显示年龄介于 30 和 40 之间以及年龄超过55的教师。
可以将公式的计算结果作为条件使用。用公式创建条件时，不要将列标签作为条件标签使用；应该将条件标签置空，或者使用区域中的非列标签。例如，下面的条件区域显示在列 C 中，其值大于单元格区域 C7:C10 平均值的行。
注释
●用作条件的公式必须使用相对引用来引用列标签（例如，“销售”），或者引用第一个记录的对应字段。公式中的其他所有引用都必须为绝对引用，并且公式的计算结果必须为 TRUE 或 FALSE。在本公式示例中，“C7”引用区域中第一个记录（行 7）的字段（列 C）。
●可在公式中使用列标签来代替相对单元格引用或区域名称。当 Microsoft Excel 在包含条件的单元格中显示错误值 #NAME? 或 #VALUE! 时，可忽略这些错误，因为它们不影响区域的筛选。
●Microsoft Excel 在计算数据时不区分大小写。
所有以该文本开始的项都将被筛选。例如，如果您键入文本“Dav”作为条件，Microsoft Excel 将查找“Davolio”、“David”和“Davis”。如果只匹配指定的文本，可键入下面的公式，其中“text”是需要查找的文本。 =''=text''
如果要查找某些字符相同但其他字符不一定相同的文本值，则可使用通配符。
通配符
以下通配符可作为筛选以及查找和替换内容时的比较条件。
3.单击区域中的单元格。
4.在“数据”菜单上，指向“筛选”，再单击“高级筛选”。
5.若要通过隐藏不符合条件的数据行来筛选区域，请单击“在原有区域显示筛选结果”。
若要通过将符合条件的数据行复制到工作表的其他位置来筛选区域，请单击“将筛选结果复制到其他位置”，然后在“复制到”编辑框中单击鼠标左键，再单击要在该处粘贴行的区域的左上角。
6.在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。
如果要在选择条件区域时暂时将“高级筛选”对话框移走，请单击“压缩对话框”。
7.若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。
提示
●您可以将某个区域命名为“Criteria”，此时“条件区域”框中就会自动出现对该区域的引用。您也可以将要筛选的数据区域命名为“Database”，并将要粘贴行的区域命名为“Extract”，这样，这些区域就会相应地自动出现在“数据区域”和“复制到”框中。
●将筛选所得的行复制到其他位置时，可以指定要复制的列。在筛选前，请将所需列的列标复制到粘贴区域的首行。而当筛选时，请在“复制到”框中输入对被复制列标的引用。这样，复制的行中将只包含已复制过列标的列。
案例公式解析:
因为“参评年龄“截止到2007年8月31日计算，因此由出生年月日换算出” 参评年龄“的公式设计是这样考虑的，如果一个教师出生的月份1-8月之间，就用2007减去该教师的出生年，否则就用2007减去该教师的出生年，然后再减去1年。单元格H2中的公式为
“=IF(MONTH(D2)<9,2007-YEAR(D2),2007-YEAR(D2)-1) “
单元格D2中的日期为“1953-12-12“，公式化简为
=IF（12<9,2007-YEAR(1953),2007-YEAR(1953)-1) “
公式继续化简为
“=IF（false,54,53) “
单元格H2返回“53”。
|
