Excel编制“花名册”小技巧
2001年2月26日22:31:5 赛迪网 赵斌
2001年2月26日22:31:5 赛迪网 赵斌
笔者在用Excel编制一个“学生花名册”表时，总结出一些简单方法，现整理出来，供大家参考。
■快速输入
1.如果要输入的内容在同一列已出现过，可直接单击右键，在弹出的快捷选单中单击最下面的“选择列表”项，出现一个下拉框，选择其中一项即可。
2.如果感觉这样输入还是繁琐，可利用Excel的“自动更正”功能更快速地进行输入。下面以“户籍所在地”为例说明具体操作方法。首先，确定各个派出所的简写，如朝阳派出所的简写为“CY”、人民路派出所简写为“RM”等。注意，各个简写不能重复，应该是表中惟一的字母组合。然后，选择“工具”选单中的“自动更正”功能，在出现的对话框中“替换（R）：”栏中输入“CY”，“替换为(W):”栏中输入“朝阳派出所”。如果“CY”在自动更正表中已存在，对话框右下角为“替换”按钮，这时，要重新确定简写；如果“CY”在自动更正表中不存在，对话框右下角为“添加”按钮，这时，单击“添加”按钮。注意，要选择“键入时自动替换”功能。这样，就可以用简写输入“户籍所在地”而让系统自动替换。
■快速输入
1.如果要输入的内容在同一列已出现过，可直接单击右键，在弹出的快捷选单中单击最下面的“选择列表”项，出现一个下拉框，选择其中一项即可。
2.如果感觉这样输入还是繁琐，可利用Excel的“自动更正”功能更快速地进行输入。下面以“户籍所在地”为例说明具体操作方法。首先，确定各个派出所的简写，如朝阳派出所的简写为“CY”、人民路派出所简写为“RM”等。注意，各个简写不能重复，应该是表中惟一的字母组合。然后，选择“工具”选单中的“自动更正”功能，在出现的对话框中“替换（R）：”栏中输入“CY”，“替换为(W):”栏中输入“朝阳派出所”。如果“CY”在自动更正表中已存在，对话框右下角为“替换”按钮，这时，要重新确定简写；如果“CY”在自动更正表中不存在，对话框右下角为“添加”按钮，这时，单击“添加”按钮。注意，要选择“键入时自动替换”功能。这样，就可以用简写输入“户籍所在地”而让系统自动替换。
■年龄计算
如何根据出生年月自动计算出年龄？进入新学年后如何快速更新年龄？笔者经过摸索，到了一个简单办法：
1.将“年龄”列的单元格式定义为“常规”类型；“出生年月”列的单元格式定义为“日期”“1995年3月”类型，该类型默认为每月的1日，即“95-3”等同于“95-3-1”。
2.使用DAYS360函数计算两个日期间相差的天数。
语法： DAYS360(start_date, end_date, method)
start_date 和 end_date 分别为起始日期和终止日期。参数既可以是用双引号括起来的字符串，其中用数字代表月、日、年（如“1/30/93”或“1-30-93”），也可以是代表日期的序列数。如果start_date 晚于 end_date ，则 DAYS360 函数将返回负数。method 是一个逻辑值，它规定了在计算中是采用何种日期格式。
3.使用ROUND函数按要求进行四舍五入。
语法：ROUND(number, num_digits)
Number 需要四舍五入的数字。
Num_digits 指定的位数，按此位数进行四舍五入。若Num-digits大于0，则四舍五入到指
如何根据出生年月自动计算出年龄？进入新学年后如何快速更新年龄？笔者经过摸索，到了一个简单办法：
1.将“年龄”列的单元格式定义为“常规”类型；“出生年月”列的单元格式定义为“日期”“1995年3月”类型，该类型默认为每月的1日，即“95-3”等同于“95-3-1”。
2.使用DAYS360函数计算两个日期间相差的天数。
语法： DAYS360(start_date, end_date, method)
start_date 和 end_date 分别为起始日期和终止日期。参数既可以是用双引号括起来的字符串，其中用数字代表月、日、年（如“1/30/93”或“1-30-93”），也可以是代表日期的序列数。如果start_date 晚于 end_date ，则 DAYS360 函数将返回负数。method 是一个逻辑值，它规定了在计算中是采用何种日期格式。
3.使用ROUND函数按要求进行四舍五入。
语法：ROUND(number, num_digits)
Number 需要四舍五入的数字。
Num_digits 指定的位数，按此位数进行四舍五入。若Num-digits大于0，则四舍五入到指
定的小数位。若Num-digits等于0，则四舍五入到最接近的整数。若Num-digits小于0，则在小数点左侧进行四舍五入。
4.在表Sheet1的F2单元格输入下面公式：=ROUND(DAYS360($E2,"00-10-1")/360,0)，其余进行复制粘贴即可。
5.前面由于将终止日期“00-10-1”直接写进了公式，日后更新很不方便。解决方法是任选一个空白单元格，设置为与“出生年月”相同的日期格式，输入“00-10-1”。选择“插入”选单中的“名称”项，单击“定义”，在“当前工作簿中的名称”一栏中输入“终止日期”，注意，“引用位置”应该是刚才输入“00-10-1”的单元格，单击“确定”结束。
这样，就给该单元格定义了名称。将公式改为： =ROUND(DAYS360($E2,终止日期)/360,0)。
以后，只需更改“终止日期”单元格的内容，就可以达到自动更新年龄的目的。
■条件统计
由于工作需要，每学期都要做一张统计表。
虽然利用Excel的“筛选”、“分类汇总”、“数据透视表”等功能也可以统计出有关数据，但每次都要进行一番操作，比较烦琐。笔者利用Excel的三个函数编制了一张固定的统计表，数
4.在表Sheet1的F2单元格输入下面公式：=ROUND(DAYS360($E2,"00-10-1")/360,0)，其余进行复制粘贴即可。
5.前面由于将终止日期“00-10-1”直接写进了公式，日后更新很不方便。解决方法是任选一个空白单元格，设置为与“出生年月”相同的日期格式，输入“00-10-1”。选择“插入”选单中的“名称”项，单击“定义”，在“当前工作簿中的名称”一栏中输入“终止日期”，注意，“引用位置”应该是刚才输入“00-10-1”的单元格，单击“确定”结束。
这样，就给该单元格定义了名称。将公式改为： =ROUND(DAYS360($E2,终止日期)/360,0)。
以后，只需更改“终止日期”单元格的内容，就可以达到自动更新年龄的目的。
■条件统计
由于工作需要，每学期都要做一张统计表。
虽然利用Excel的“筛选”、“分类汇总”、“数据透视表”等功能也可以统计出有关数据，但每次都要进行一番操作，比较烦琐。笔者利用Excel的三个函数编制了一张固定的统计表，数
据发生变化统计表也随之变化。具体方法是：
1.利用 COUNTA 函数可以计算一个区域中非空单元格的数目。语法：COUNTA(value1, value2, ...)。value1、value2为所要计数的值，参数个数为 1～30 个。这种情况下，参数值可以是任何类型，它们可以包括空字符（excel中round函数的使用方法“ ”）但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。该函数可以应用到本例中的“全班人数合计”项，公式为：=COUNTA（SHEET1！A2：A10），计算A2：A10这一区域中非空的单元格数目，即全班人数。
2.利用COUNTIF函数计算某区域内满足一个给定条件的单元格数目。语法：COUNTIF(range, criteria)。range 是将要计算其中单元格数目的区域，criteria 是确定哪些单元格将被计算在内的条件，其形式可以是数字、表达式或文本。例如，条件可以表示为32、“32”、“>32”等形式。本函数可以应用到本例中Sheet2的D1等单元格，公式为：=COUNTIF（Sheet1！B2：B10，“男”），计算出全班男生数目。
3.利用DCOUNTA函数计算给定区域中符合多个条件并且非空的单元格数。语法：DCOVNT(database,field,criteria)。database是组成数据库的单元格区域，field是将要用于函数计算的字段，criteria是包含条件的单元格区域。本函数应用到Sheet2的D2单元格的具
1.利用 COUNTA 函数可以计算一个区域中非空单元格的数目。语法：COUNTA(value1, value2, ...)。value1、value2为所要计数的值，参数个数为 1～30 个。这种情况下，参数值可以是任何类型，它们可以包括空字符（excel中round函数的使用方法“ ”）但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。该函数可以应用到本例中的“全班人数合计”项，公式为：=COUNTA（SHEET1！A2：A10），计算A2：A10这一区域中非空的单元格数目，即全班人数。
2.利用COUNTIF函数计算某区域内满足一个给定条件的单元格数目。语法：COUNTIF(range, criteria)。range 是将要计算其中单元格数目的区域，criteria 是确定哪些单元格将被计算在内的条件，其形式可以是数字、表达式或文本。例如，条件可以表示为32、“32”、“>32”等形式。本函数可以应用到本例中Sheet2的D1等单元格，公式为：=COUNTIF（Sheet1！B2：B10，“男”），计算出全班男生数目。
3.利用DCOUNTA函数计算给定区域中符合多个条件并且非空的单元格数。语法：DCOVNT(database,field,criteria)。database是组成数据库的单元格区域，field是将要用于函数计算的字段，criteria是包含条件的单元格区域。本函数应用到Sheet2的D2单元格的具
体方法是：在Sheet3中建立一个条件区域，如图3所示：
统计全班汉族男生的公式为：=DCOUNTA(Sheet1！A1：I10,,Sheet3！B1：C2)。同样，也可以为公式中单元格区域定义名称，方便以后维护。
4.为了保证统计表Sheet2的正确性，可以利用IF函数进行一致性验证。IF函数语法：IF(logical_test,value_if_true,value_if_false)
如果logical_test计算值为TRUE，则返回value_if_true；否则返回value_if_false。例如，全班人数、男生人数和女生人数都是自动统计出来的，为了验证其一致性，可任选一空白单元，输入公式：==IF(B1=D1+F1,"OK","ERROR")
若显示“ERROR”，则说明统计表有误。
统计全班汉族男生的公式为：=DCOUNTA(Sheet1！A1：I10,,Sheet3！B1：C2)。同样，也可以为公式中单元格区域定义名称，方便以后维护。
4.为了保证统计表Sheet2的正确性，可以利用IF函数进行一致性验证。IF函数语法：IF(logical_test,value_if_true,value_if_false)
如果logical_test计算值为TRUE，则返回value_if_true；否则返回value_if_false。例如，全班人数、男生人数和女生人数都是自动统计出来的，为了验证其一致性，可任选一空白单元，输入公式：==IF(B1=D1+F1,"OK","ERROR")
若显示“ERROR”，则说明统计表有误。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论