电子表格技术.docx
《电子表格技术.docx》由会员分享,可在线阅读,更多相关《电子表格技术.docx(22页珍藏版)》请在冰豆网上搜索。
![电子表格技术.docx](https://file1.bdocx.com/fileroot1/2023-8/13/715397d9-e43b-4473-872a-1688b884595f/715397d9-e43b-4473-872a-1688b884595f1.gif)
电子表格技术
电子表格的应用应用问题
1、如何用电子表格计算年龄?
方法一
假设A列存放出生年月,B列存放目前时间,C列存放计算结果.
你可以在C列输入公式:
=ROUNDDOWN(DAYS360(A1,B1)/365,0)
这个公式的含义:
DAYS360(A1,B1):
是用来计算两个时间之间相差的天数
/365:
把相差的天数换算成年数
ROUNDDOWN:
把小数点后面的数去掉变成整数,通常年龄是不会四舍五入,即使25岁零11个月都会说是25岁而不会说是26岁,因此直接把年后面的舍去算了。
方法二
以系统当前时间计算年龄的代码:
=datedif(A1,today(),"y")
以固定一时间计算年龄代码①:
=datedif(A1,"2009-6-30","Y")
以固定一时间计算年龄代码②:
=DATEDIF(A1,$K$2,"y")
说明:
①以上的A1是指要进行计算的单元格,$K$2是指计算截止时间所在的单元格,2009-6-30是可以根据需要进行变动。
其他的不要变动,复制粘贴到你的单元格计算就可以了。
②输入的数字格式为时间格式,如:
2009-06-30.
注:
用EXCEL电子表格计算年龄
假设b2为生日
=datedif(B2,today(),"y")
DATEDIF函数,除Excel2000中在帮助文档有描述外,其他版本的Excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。
但该函数在电子表格中确实存在,并且用来计算两个日期之间的天数、月数或年数很方便。
微软称,提供此函数是为了与Lotus1-2-3兼容。
该函数的用法为“DATEDIF(Start_date,End_date,Unit)”,其中Start_date为一个日期,它代表时间段内的第一个日期或起始日期。
End_date为一个日期,它代表时间段内的最后一个日期或结束日期。
Unit为所需信息的返回类型。
“Y”为时间段中的整年数,“M”为时间段中的整月数,“D”时间段中的天数。
“MD”为Start_date与End_date日期中天数的差,可忽略日期中的月和年。
“YM”为Start_date与End_date日期中月数的差,可忽略日期中的日和年。
“YD”为Start_date与End_date日期中天数的差,可忽略日期中的年。
比如,B2单元格中存放的是出生日期(输入年月日时,用斜线或短横线隔开),在C2单元格中输入“=datedif(B2,today(),"y")”(C2单元格的格式为常规),按回车键后,C2单元格中的数值就是计算后的年龄。
此函数在计算时,只有在两日期相差满12个月,才算为一年,假如生日是2004年2月27日,今天是2005年2月28日,用此函数计算的年龄则为0岁,这样算出的年龄其实是最公平的。
2、在电子表格中如何在很多数据中找出重复的数据
可以查找出来.
①.先选中你那列,比如是A1单元格,格式-条件格式,公式设置为:
=COUNTIF($A$1:
$A$6000,"="&A1)>1,格式设置为红色。
确定后将A1用格式刷将此列刷下,你要找的号码就会呈红色找到了
②.点"格式"->"条件格式",将条件设为:
公式=OR(A2=A1,A2=A3)
点"格式"->"字体"->"颜色",选中红色,点两次"确定"。
用格式刷将C2单元格的条件格式向下复制到其他单元格。
所有相同的数据都显示为红色,并且上下相邻。
2、在电子表格中如何排名次
答:
如:
在ABC列输入以下内容
A
B
C
1
姓名
考分
排名
2
张三
100
3
李四
300
4
王五
300
5
赵六
400
6
韩七
400
C2输入=RANK(B2,B$2:
B$6)
往下拖复制到C6
结果如下:
A
B
C
1
姓名
考分
排名
2
张三
100
3
3
李四
300
2
4
王五
300
2
5
赵六
400
1
6
韩七
400
1
OR:
下面是我们才进行的一次考试的成绩(只选择了一个班前10个学生的成绩)
在E2单元格的公式是“=RANK(E1,$E$1:
$E$10)”,向下复制即可生成全班学生的名次
这里的关键是用好RANK函数,注意其中的绝对引用区域你要根据你所统计的人数作相应的调整
A
B
C
F
G
H
D
E
序号
姓名
班级
语
数
英
总分
名次
1
张四
4
100
110
99
309
3
2
张五
4
102
113
90
305
2
3
张六
4
101
85
96
282
8
4
张七
4
105
108
97
310
4
5
张八
4
81
13
97
318
10
6
张九
4
101
114
99
314
1
7
李四
4
95
102
91
288
6
8
李五
4
82
76
83
241
9
9
李六
4
91
105
97
293
5
10
李七
4
102
99
99
300
7
用语法RANK(number,ref,order)
Number为需要找到排位的数字。
比如说H1
Ref为数字列表数组或对数字列表的引用。
H1:
H54
Ref中的非数值型参数将被忽略。
Order为一数字,指明排位的方式。
XX的显示很成问题,你可以将数据部分单独复制到你的一张空电子表格中试验一下
用Word2003制作试卷密封线
教师在制作试卷时,常常希望在试卷的最左端放置密封线和学生填写姓名的位置。
通过在Word2003中插入文本框可以轻松实现如图2007112306所示的试卷密封线。
图2007112306
试卷密封线使用Word2003制作试卷密封线的操作步骤如下所述:
第1步,打开一份已经编辑完成的Word试卷,依次单击“插入”→“文本框”→“横排”菜单命令,打开“在此处创建图形”编辑框。
鼠标指针呈现十字形,单击鼠标左键即可插入一个文本框。
将鼠标指向文本框的边框,鼠标指针处呈现十字形箭头。
将文本框拖动至“在此处创建图形”编辑框之外,然后将“在此处创建图形”编辑框删除,如图2007112307所示。
图2007112307
Word2003中插入文本框第2步,右键单击文本框边框,在打开的快捷菜单中选择“设置文本框格式”命令,如图2007112308所示。
图2007112308 选择“设置文本框格式”命令
第3步,打开“设置文本框格式”对话框,在“颜色与线条”选项卡中将线条颜色设置为无线条颜色,并单击“确定”按钮,如图2007112309所示。
图2007112309 “颜色与线条”选项卡
第4步,在文本框内部单击鼠标右键,在打开的快捷菜单中选择“文字方向”命令,如图2007112310所示。
图2007112310 选择“文字方向”命令
第5步,打开“文字方向-文本框”对话框,单击选择左侧的文字方向类型,并单击“确定”按钮,如图2007112311所示。
图2007112311 “文字方向-文本框”对话框
第6步,调整文本框大小,在文本框中输入“姓名、性别、考号”等内容。
按回车键,输入“密封线”三个字,并在三个字之间插入一定数量的空格,如图2007112312所示。
图2007112312 输入文本框内容
第7步,在“绘图”工具栏中选择直线工具,在文本框中插入一条直线。
右键单击插入的直线,在打开的快捷菜单中选择“设置自选图形格式”命令,如图2007112313所示。
图2007112313 选择“设置自选图形格式”命令
第8步,打开“设置自选图形格式”对话框,在“虚实”列表中选择“圆点”选项,并将“粗细”选项设置为1磅。
设置完毕单击“确定”按钮,如图2007112314所示。
图2007112314 选择“圆点”选项
通过上述设置,word试卷密封线的制作基本完成。
最后调整文本框的位置,使其居于试卷最左端即可。
用Excel统计各分数段学生数
教师常常要统计各学科相应分数段的学生人数,以方便对考试情况作全方位的对比分析。
在Excel中,有多种函数可以实现这种统计工作,笔者以图1所示的成绩表为例,给出多种统计方法。
方法一:
用COUNTIF函数统计
这是最常用、最容易理解的一种方法,我们用它来统计“语文”学科各分数段学生数。
如果某些学科(如体育),其成绩是不具体数值,而是字符等级(如“优秀、良好”等),我们也可以用COUNTIF函数来统计各等级的学生人数。
方法二:
用DCOUNT函数统计
这个函数不太常用,但用来统计分数段学生数效果很不错。
我们用它统计“数学”学科各分数段学生数。
方法三:
用FREQUENCY函数统计
这是一个专门用于统计某个区域中数据的频率分布函数,我们用它来统计“英语”学科各分数段学生数。
方法四:
用SUM函数统计
我们知道SUM函数通常是用来求和的,其实,他也可以用来进行多条件计数,我们用它来统计“政治”学科各分数段的学生数。
方法一:
用COUNTIF函数统计
这是最常用、最容易理解的一种方法,我们用它来统计“语文”学科各分数段学生数。
函数功能及用法介绍
①分别选中C63、C67单元格,输入公式:
=COUNTIF(C3:
C62,"<60")和=COUNTIF(C3:
C62,">=90"),即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。
②分别选中C64、C65和C66单元格,输入公式:
=COUNTIF(C3:
C62,">=60")-COUNTIF(C3:
C62,">=70")、=COUNTIF(C3:
C62,">=70")-COUNTIF(C3:
C62,">=80")和=COUNTIF(C3:
C62,">=80")-COUNTIF(C3:
C62,">=90"),即可统计出成绩在60-69分、70-79分、80-89分区间段的学生人数。
注意:
同时选中C63至C67单元格,将鼠标移至C67单元格右下角,成细十字线状时,按住左键向右拖拉至I列,就可以统计出其它学科各分数段的学生数。
如果某些学科(如体育),其成绩是不具体数值,而是字符等级(如“优秀、良好”等),我们可以用COUNTIF函数来统计各等级的学生人数。
如果某些学科(如体育),其成绩是不具体数值,而是字符等级(如“优秀、良好”等),我们可以用COUNTIF函数来统计各等级的学生人数。
①在K64至K67单元格中,分别输入成绩等级字符(参见图2)。
②选中L64单元格,输入公式:
=COUNTIF($L$3:
$L$62,K64),统计出“优秀”的学生人数。
③再次选中L64单元格,用“填充柄”将上述公式复制到L65至L67单元格中,统计出其它等级的学生人数。
上述全部统计结果参见图1。
方法二:
用DCOUNT函数统计
这个函数不太常用,但用来统计分数段学生数效果很不错。
我们用它统计“数学”学科各分数段学生数。
①分别选中M63至N72单元格区域(不一定非得不这个区域),输入学科名称(与统计学科名称一致,如“数学”等)及相应的分数段(如图2)。
②分别选中D63、D64……D67单元格,输入公式:
=DCOUNT(D2:
D62,"数学",M63:
N64)、=DCOUNT(D2:
D62,"数学",M65:
N66)、=DCOUNT(D2:
D62,"数学",M67:
N68)、=DCOUNT(D2:
D62,"数学",M69:
N70)、=DCOUNT($D$2:
$D$62,"数学",M71:
N72),确认即可。
注意:
将上述公式中的“DCOUNT”函数换成“DCOUNTA”函数,同样可以实现各分数段学生人数的统计。
方法三:
用FREQUENCY函数统计
这是一个专门用于统计某个区域中数据的频率分布函数,我们用它来统计“英语”学科各分数段学生数。
函数功能及用法介绍
① 分别选中O64至O67单元格,输入分数段的分隔数值(参见图2)。
②同时选中E63至E67单元格区域,在“编辑栏”中输入公式:
=FREQUENCY(E3:
E62,$O$64:
$O$67),输入完成后,按下“Ctrl+Shift+Enter”组合键进行确认,即可一次性统计出“英语”学科各分数段的学生人数。
注意:
①实际上此处输入的是一个数组公式,数组公式输入完成后,不能按“Enter”键进行确认,而是要按“Ctrl+Shift+Enter”组合键进行确认。
确认完成后,在公式两端出现一个数组公式的标志“{}”(该标志不能用键盘直接输入)。
②数组公式也支持用“填充柄”拖拉填充:
同时选中E63至E67单元格区域,将鼠标移至E67单元格右下角,成细十字线状时,按住左键向右拖拉,就可以统计出其它学科各分数段的学生数。
方法四:
用SUM函数统计
我们知道SUM函数通常是用来求和的,其实,他也可以用来进行多条件计数,我们用它来统计“政治”学科各分数段的学生数。
函数功能及用法介绍
①分别选中P64至P69单元格,输入分数段的分隔数值(参见图2)。
②选中F63单元格,输入公式:
=SUM(($F$3:
$F$62>=P64)*($F$3:
$F$62 ③再次选中F63单元格,用“填充柄”将上述公式复制到F64至F67单元格中,统计出其它各分数段的学生人数。
注意:
用此法统计时,可以不引用单元格,而直接采用分数值。
例如,在F64单元格中输入公式:
=SUM(($F$3:
$F$62>=60)*($F$3:
$F$62<70)),也可以统计出成绩在60-69分之间的学生人数。
注意:
①为了表格整体的美观,我们将M至P列隐藏起来:
同时选中M至P列,右击鼠标,在随后出现的快捷菜单中,选“隐藏”选项。
使用FREQUENCYCOUNTIF函数求出成绩分布频数
既然是在Excel中统计成绩,自然要充分利用Excel的强大功能,下面,我们介绍获取成绩分布频数的三种方法:
(一)利用FREQUENCY函数
FREQUENCY函数可以计算数值在某个区域内的出现频率,然后返回一个垂直数值,我们正好利用这个函数来统计考试成绩的频数分布。
不过,由于FREQUENCY函数返回的是一个数组,因此它必须以数组公式的形式输入。
语法:
FREQUENCY(data_array,bins_array)
Data_array:
是一个数组或对一组数值的引用,我们主要通过该值计算频率,如果此处不包含任何数值,那么函数FREQUENCY将返回一个零数组。
Bins_array:
是一个区间数组或对区间的引用,该区间用于对data_array中的数值进行分组,如果此处不包含任何数值,那么函数FREQUENCY返回的值与data_array中的元素个数相等。
第1步:
插入列
在适当位置手工插入区间分割点、人数、百分比这三列,注意请完全按照教务部门发下来的样表进行输入(见图3),这样做的好处当然是免得做无用功。
第2步:
设置区间分割点
区间分割点的设置是极为重要的一个步骤,这里以应该参考"分数段"进行设置,例如"29"表示考试成绩小于或等于29分的分数个数,"39"表示考试成绩介于30~39分的分数个数,其余的依次类推(见图4)。
第3步:
输入数组公式
选择准备放置各分数段人数的单元格区域,这里选择"F3:
F11",选择结束后按下F2键,如图5所示,在这里输入公式"=FREQUENCY(C3:
C44,E3:
E10)",为了避免出错,建议通过插入函数的方法进行,这样比较简单方便。
有些朋友可能会发现,这里选择了"F3:
F11"而非""F3:
F10",这是为什么呢?
也就是说,这里较左侧的区间分割点多选择了一个单元格,其实这个多出来的单元格主要用于返回大于99分的学生人数,原因是因为返回的数组中的元素个数(分布频数)比bins_array(分段点)中的元素个数多1,多出来的单元格将返回大于最末一个间隔值的数值个数,否则返回结果会出错。
第4步:
返回数组个数
前面已经说明,由于FREQUENCY返回的结果是数组形式,因此公式输入完成后必须按下"Ctrl+Shift+Enter"组合键进行确认,其标志是出现一对大括号"{}",计算结果如图6所示。
如果需要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在"工具"菜单上,指向"公式审核",再单击"公式审核模式"。
第5步:
计算百分比
接下来的工作就非常简单了,选中G3单元格,输入"=F3/42",将单元格数值设置为百分比格式,这一步工作可以在"单元格格式→数字"窗口中完成,最终结果如图7所示。
(二)利用COUNTIF函数
COUNTIF函数可以计算区域中满足给定条件的单元格的个数,命令语法是"COUNTIF(range,criteria)",这里的"range"是一个或多个要计数的单元格,期中包括数字或名称、数组或包含数字的引用,当然如果是空值或文本值将被忽略;而"criteria"的形式可以为数字、表达式、单元格引用或文本。
第1步:
设置区间分割点
首先,我们还是手工创建区间分割点、人数、百分比三个空白列,但在设置区间分割点时,注意与使用FREQUENCY函数时有一些区别,请按照图8所示进行设置,这里的"99.9"表示考试成绩为满分100的学生数,"89.9"表示考试成绩在90~99之间的学生数,其余的依次类推。
第2步:
计算满分的学生数
选中F3单元格,在这里输入公式"=COUNTIF(C$3:
C$44,">"&$E3)",这里的"C$3:
C$44"表示需要计数的单元格区域,而"">"&$E3"则表示大于99.9分,计算结果如图9所示。
第3步:
计算90~99分数区间段的学生数
选中F4单元格,在这里输入公式"=COUNTIF(C$3:
C$44,">"&$E4)-COUNTIF(C$3:
C$44,">"&$E3)",很快就可以得到图10所示处于90~99分数区间段的学生人数。
这个公式分成两个环节:
(1)COUNTIF(C$3:
C$44,">"&$E4):
计算出考试成绩大于89.9分的学生人数
(2)COUNTIF(C$3:
C$44,">"&$E3):
计算出考试成绩大于99.9分的学生人数
根据成绩分布频数样表的要求,我们需要的各个区间分割点的学生人数,而非累加的数字,因此需要对这两份数字进行减运行,否则最后所得到的结果就不是我们所需要的了。
接下来的工作就相当简单了,选中F3单元格,拖曳右下角的填充柄即可得到图11所示的成绩分布频数,至于各个区间学生数的百分比,可以按照前面的方法进行计算,这里就不再多作介绍。