电子表格技术.docx

资源描述

电子表格技术.docx

《电子表格技术.docx》由会员分享，可在线阅读，更多相关《电子表格技术.docx（22页珍藏版）》请在冰豆网上搜索。

电子表格技术.docx

电子表格技术

电子表格的应用应用问题

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列输入以下内容

姓名

考分

排名

张三

100

李四

300

王五

300

赵六

400

韩七

400

C2输入=RANK（B2,B$2:

B$6）

往下拖复制到C6

结果如下：

姓名

考分

排名

张三

100

李四

300

王五

300

赵六

400

韩七

400

OR:

下面是我们才进行的一次考试的成绩（只选择了一个班前10个学生的成绩）

在E2单元格的公式是“=RANK（E1,$E$1:

$E$10）”，向下复制即可生成全班学生的名次

这里的关键是用好RANK函数，注意其中的绝对引用区域你要根据你所统计的人数作相应的调整

序号

姓名

班级

语

数

英

总分

名次

张四

100

110

309

张五

102

113

305

张六

101

282

张七

105

108

310

张八

318

张九

101

114

314

李四

102

288

李五

241

李六

105

293

李七

102

300

用语法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函数来统计各等级的学生人数。

　　①在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所示的成绩分布频数，至于各个区间学生数的百分比，可以按照前面的方法进行计算，这里就不再多作介绍。

展开阅读全文