Microsoft Excel 词汇表及有关操作.docx

Microsoft Excel 词汇表及有关操作.docx

《Microsoft Excel 词汇表及有关操作.docx》由会员分享，可在线阅读，更多相关《Microsoft Excel 词汇表及有关操作.docx（15页珍藏版）》请在冰豆网上搜索。

MicrosoftExcel词汇表及有关操作

MicrosoftExcel词汇表及有关操作

假设已经建好了电子表格文件，其中“新生原始总表”数据表中有大学新生入学成绩数据，表格形式见下图。

从第16）~19）步开始，用该表做例子学习Excel的一些常用操作，并最终完成分班和成绩汇总的任务。

从20）步开始，对建立的“一班”工作表进行操作。

MicrosoftExcel：

是制作电子表格的软件，该软件具有强大的数据计算功能，而且具有强有力的图表、图形制作功能，广泛应用于办公自动化领域。

工作簿（bu）：

Execel软件将每一个工作簿作为一个文件保存，其扩展名为.XLS。

工作表：

工作表是用来存放数据的电子表格，一个工作簿由一个或多个工作表组成。

单元格：

工作表中所有行列交叉点处的方格称为单元格，它是工作表的基本成分。

单元格里可以添加的数据类型是文本、数字、日期、时间、公式。

活动单元格:

用鼠标选中单元格,该单元格成为活动单元格,鼠标双击活动单元格,可以写入数据，删除数据、查看数据操作。

.

0）若要输入分数形式，如1/4，正确的输入方法是：

01/4（0与分数之间有空格）。

1）若想将数字（如身份证号码）作为文本数据处理，需要加单引号，例如要输入001，则需要输入001。

2）年月日的输入格式：

年/月/日，或者年-月-日

3）时分秒的输入格式：

时：

分：

秒

4）在保存文件时候，可以设置文件的打开密码和修改密码，以防止非授权用户修改文件。

具体操作步骤：

在“另存为”对话框上选择“工具”按钮，在弹出的下拉菜单中选择“常规选项”，出现“保存选项”对话框，可以设置密码。

5）使用”插入”菜单,可以在选中的单元格的上方插入行、在选中的单元格的左方插入列、对选中的单元格加批注。

6）整行或整列单元格的选取：

用鼠标单击工作表相应的行号或列标。

7）在“编辑”菜单中，清除一行（或一列）里的内容：

选定行或列，“编辑”菜单→“清除”→“内容”。

8）在“编辑”菜单中，删除一行或一列：

选定行或列，“编辑”→“删除”。

9）智能填充编号序列：

相邻单元格中有相同的数据或按某种规律变化的数据时，可以利用智能填充功能实现编号序列的快速输入：

例如在单元格A3和A4输入数字1、2，用鼠标选定单元格A3和A4区域，然后移动鼠标到选定区域的右下角（右下角的小黑块称为填充柄），当鼠标光标变成“+”时，按住鼠标左键不放，向下拖动光标，进行数据自动填充。

10）多个连续行、列、单元格的选取：

按住键盘上的“Shift”键进行。

或用鼠标拖动相应的行或列。

11）多个不连续行、列、单元格的选取：

按住键盘上的“Ctrl”键进行。

12）数据有效性的设置：

当所录用的数据有一定的取舍范围，可以在录用数据前，考虑设置数据的有效性，可以降低录入数据的错误率。

例如，选择语文成绩列，选择“数据”菜单→“有效性”。

对整数限定范围是0~100

13）自动求和：

“自动求和”功能具有一定的智能分析功能，如果选定选区的右端或下端有一空列或空行，则能将自动求和结果放入相应的空列或空行中。

例如，选定数据列区域，以及空白列区域，选择自动求和按钮，则求和数据放到空百列中。

例如求出每个学生的各科成绩的总和。

也可在需要放求和数据的单元格内，直接输入求和函数。

例如在H3单元格输入“=SUM（E3:

G3）”,然后按回车键确认。

“E3:

G3”表示了E3到G3的所有单元格，是需要被求和的数据范围。

然后向下拖动H3单元格右下角的填充柄，可以自动实现公式复制，求出其它学生的各科成绩的总和。

例如在H4单元格中求和的公式会变为“=SUM（E4：

G4）”。

14）工作表的操作：

鼠标选取工作表，鼠标点击右键弹出快捷方式菜单，可以对工作表进行改名字、移动、复制、删除、插入等操作。

也可以直接用鼠标操作工作表，例如双击工作表修改名字、鼠标拖动工作表进行移动、按住键盘上的“Ctrl”键，并拖动工作表则可以复制该工作表。

15）工作表的“冻结”对于大的工作表，为了在浏览的过程中使表的标题一直处于可见状态，可以选择工作表的“冻结”操作。

“冻结”是以当前活动单元格的左上边框为准进行冻结的。

具体操作：

选一个单元格为当前活动单元格，“窗口”菜单→“冻结窗格”命令。

使用完毕冻结功能后，要取消冻结状态。

16）数据排序：

若对工作表的某一列或多个列的值进行排序时，应先单击表中的任意单元格，再选择“数据”菜单提供的排序命令。

例如数据先按所设置的“主关键字”排序，当“主关键字”相同时，再按“次关键字”排序。

例如对新生原始总表，设置主关键字为“性别”，次关键字为“总分”，则先按男女升序排序，而对全体男生及全体女生再按总分成绩降序排序。

若对某列排序，不要把整个列选上，应单击该列的某一单元格。

17）创建和填充自定义序列为Execel增加按某种规律变化的数据序列，例如一班、二班、三班、四班、四班、三班、二班、一班。

创建自定义序列操作方法：

“工具”菜单→“选项”→“自定义序列”。

在“自定义序列”列表中选择其中的新序列，然后在“输入序列”框中输入新序列名，如一班、二班、三班、四班、四班、三班、二班、一班。

等，数据用回车键换行，最后点击“添加”按钮。

自动填充已定义的序列数：

在Execel工作表中，可以增加一个新的名为“班级”的列，在该列名称下的8个单元格中依次输入“一班、二班、三班、四班、四班、三班、二班、一班”，然后按住shift键，选中8个单元格子，然后拖动该8个单元格子的最后面单元格右下角的填充柄进行填充，则序列数按已经定义的顺序循环填充。

经过第16和17步骤，可以把原始学生成绩表进行分班，分班采取了总分轮回制，例如总分第1~4名分到1~4班，第5~8名分到4~1班，依次类推。

注意：

如果创建的自定义序列是一班、二班、三班，则当某一单元格内输入“一班”时候，则可以拖动该单元格右下角的填充柄，则序列数按已定义的顺序循环填充。

18）数据筛选（shaixuan）：

如果工作表中的数据太多，可以用“数据筛选”功能将满足指定条件的数据记录显示出来，不满足条件的记录则被隐藏。

具体操作：

单击工作表中的任意单元格，单击“数据”菜单→“筛选”→“自动筛选”，则在工作表每个列标题右侧出现下拉箭头，可以显示要筛选的条件，例如“一班”，选择该条件，会筛选出一班的学生数据，可以把筛选出来的数据复制到另一个工作表中，例如建立“一班“工作表。

用相同的方法可以筛选出其它班的学生数据。

再次选择Excel“数据”菜单→“筛选”→“自动筛选”命令，将取消“筛选”状态，把原来隐藏的数据都显示出来。

19）分类汇总能够按照用户指定的要求进行数据汇总。

例如先将第17步分班的学生总表数据按照“班级”降序排序，然后选择Excel“数据”菜单→“分类汇总”对话框，对于“分类字段”，若选择“班级”，就可以对相同班级的学生数据进行汇总。

若选择的“汇总方式”为平均值，“选定汇总项目”为语文、数学和英语，则可以把该班级学生的语文、数学和英语成绩的平均分统计出来。

在“分类汇总”对话框中，单击“全部删除”按钮，可以取消分类汇总的结果。

注意：

分类汇总前，需要用数据排序功能对指定的分类字段，例如班级进行排序，然后才能进行“分类汇总”。

汇总的方式有求和、求平均值、统计个数等。

20）计算每个学生平均分：

在存放一班学生数据的“一班”工作表中，添加一列，在I2单元格内输入“平均分”，如果某个学生的总分数所在的单元格用H3来标记，则在该学生的平均分单元格I3中输入公式“=H3/3”，按回车键确认，就可以求出该学生的3科成绩的平均分。

求出第1个学生的平均分后，拖动其单元格右下角的填充柄，算出每个学生的平均分。

21）计算单科平均分：

在“一班”工作表中增加一行名为“单科平均分”，若要求出1班学生语文科目的平均分，选定放平均分的单元格，例如E44，单击“插入”菜单→“函数”，打开“插入函数”对话框，选择函数“AVERAGE”，按“确定”按钮，打开“函数参考”对话框，在Numner1的输入框里，输入函数所需要的参数。

例如输入E3：

E43，表明语文科目的第1个成绩所在的单元格是E3，语文科目的最后成绩所在的单元格是E43。

则可求出语文科目的平均分。

用鼠标向右拖动E44单元格右下角的填充柄，算出其它科目的平均分。

也可以直接在放平均分的单元格内输入求平均分的函数，例如“=AVERAGE（E3：

E43）”

输入完毕，按回车键确认输入。

注意：

公式和函数的输入以等号“=”开头。

在单元格内插入函数的方法一：

插入”菜单→“函数”，打开“插入函数”对话框。

在单元格内插入函数的方法二：

直接输入以等号开始的函数。

22）计算单科最高分、最低分：

按照第21步骤相似的操作，可以求出单科最高分和最低分，可以在相应的单元格内输入函数，例如求最高分“=MAX（E3：

E43）”，

求最低分：

“=MIN（E3：

E43）”。

23）总分排名在“一班”工作表中添加1列，在J2单元格内输入名为“总分排名”。

若放第1个学生的总分排名单元格为J3，在J3单元格内输入RANK函数，

例如“=RANK（H3，$H$3：

$H$43）”。

其中H3是第1个学生的总分数所在的单元格，$h$3:

$H$43表示H3到H43所有单元格，反映出排名范围。

用鼠标向下拖动J3单元格右下角的填充柄，算出其他学生的排名情况。

在该例子中，填充柄可以实现公式的复制，但函数中的第二个参数不变，即J4单元格中的公式是“=RANK（H4，$H$3:

$H$43）。

在单元格内插入RANK函数的方法：

“插入”菜单→“函数”，打开“插入函数”对话

框。

选中上图中的H3：

H43，然后按键盘上的F4键，则单元格中公式的参数H3：

H43将改变为$H$3:

$H$43，如下图所示：

24）相对引用和绝对引用的含义

*相对引用：

在引用单元格或单元区域的数据时，直接使用列号、行号来表示，称为

相对引用。

当公式中的参数是相对引用形式的参数时，复制该公式时，将

自动调整参数，以保证公式中单元格的相对关系保持不变。

例如在单元格F3中输入公式“=D3+E3”，则复制该公式到F4单元格，则公式变为“=D4+E4”，

即公式中单元格的相对关系是保持不变的。

*绝对引用：

如果在复制公式时，不希望参数发生变化，即不需要Excel调整引用，那么就需要使用绝对引用形式的参数。

绝对引用在引用单元格或单元区域的数据时，在列标和行标的前面加$。

如果选定相对引用H3：

H43，则按键盘上的F4键，可以把相对引用转换为绝对引用$H$3:

$H$43。

25）备注

在“一班”工作表中再添加1列，在K2单元格内输入名为“备注”。

选定单元格K3，插入函数IF，参数设置如下：

用鼠标向下拖动K3单元格右下角的填充柄，可以自动写出所有学生的学习情况备注。

也可在单元格内输入函数“=IF（I3>75,“好”,“一般”）”。

举例说明IF函数的功能：

IF函数对逻辑条件“I3>75”进行检查，如果条件成立，函数值为“好”,否则为“一般”。

经过上述步骤处理后，“一班”工作表内容形式如下：

26）函数COUNTIF的使用

函数COUNTIF的功能是计算区域中满足给定条件的单元格的个数。

语法:

COUNTIF（range,criteria）

Range :

为需要计算其中满足条件的单元格数目的单元格区域。

Criteria :

为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为32、"32"、">32"或"apples"。

下面显示一个例子:

27）统计各分数段人数

建立“一班平均分分布”工作表，表格框架如图所示。

在B3单元格内，插入函数COUNTIF，参数设置如下：

在B4单元格内，插入公式：

“=COUNTIF（一班!

I3:

I43,"<70"）-B3”

在B5单元格内，插入公式：

“=COUNTIF（一班!

I3:

I43,"<80"）-B3-B4”

在B6单元格内，插入公式：

“=COUNTIF（一班!

I3:

I43,"<90"）-B3-B4-B5”

在B7单元格内，插入公式：

“=COUNTIF（一班!

I3:

I43,">=90"）”

数据处理结果形式如图:

28）工作表格式的修饰美化

当工作表的数据处理完毕后，可进行格式的修饰。

打开“一班”工作表，选择前2行所在的区域，选择Excel的“格式”→“行”命令，设置行高度为25；

选定标题单元格区域A1：

K1，选择Excel的“格式”→“单元格”命令，出现下面对话框，选择“合并单元格”。

选择单元格区域A1：

K2，进行文字对齐的“居中”操作，结果如下：

利用Excel的“格式”→“单元格”命令，可以进行一系列的操作，如文字的对齐方式、字体的设置、边框的设置、图案和背景颜色设置，数字格式的设置（例如小数位数的设置）等操作。

选中单元格E3：

G43，选择Excel的“格式”→“条件格式”命令，可以做如下的设置：

单元格中的数值小于60时，则用“红色”显示该数值。

29）插入“图表”

在“一班平均分分布”工作表中插入图表，选定单元格区域A1：

B7，选择Excel的“插入”→“图表”命令。

进行如下步骤的选择操作：

①选择图表类型：

饼图

②设置图表源数据：

选中“一班平均分分布”工作表中的数据，会出现如下的公式

=一班平均分分布！

$A$1:

$B$7

③设置图表标题：

一班平均分分布图

④设置数据标志：

类别名称、百分比、显示引用线

⑤设置图表插入的地方：

把图表作为其中的对象插入到“一班平均分分布”工作表中。

选中饼图中某区域，选择Excel的“格式”→“数据点”命令，可以更改其颜色。

单击图表，利用出现的图表编辑工具条，可以对图表格式进行编辑，如改变文字的大小、等。

30）下面的EXCEL工作表中，显示出了单科成绩的优秀率和学生成绩的等级，如何利用EXCEL的函数实现呢？

&求英语成绩的优秀率

在B15单元格内，插入函数=COUNTIF（B4:

B11,">=90"）/COUNT（B4:

B11）

该函数计算出的值是小数类型的数据，改成以百分数形式显示单元格的值。

选中B15单元格，选择Excel的“格式”→“单元格”命令，设置如下：

&

&求学生成绩的等级

首先在当前工作表中做一个“查询数据表”，该表有2行数据组成。

第一行的数据是用数值表示的等级，这些数据必须由小到大排好序，其值分别是：

0、60、70、80、90。

第二行数据是用汉文字表示的等级，与第一行的数据对应，分别是：

不及格、及格、中、良、优秀。

在G4单元个内插入水平查表函数=HLOOKUP（F4,$B$19:

$F$20,2）

第1个参数表示在查询数据表的第1行范围内将要查找的数据，

第2个参数表示在查询数据表的哪些范围内查找，本例子是把查询数据表的所有数据选上。

注意该参数是绝对引用形式的参数（按盘上的F4键，可以把相对引用参数改成绝对引用参数）。

第3个参数是查询数据表的行号，表明若查找成功，返回第几行的数据。

例如行号2表示待匹配的数据在查询数据表第1行匹配成功，则返回查询数据表第2行对应的数据。

若匹配失败，则函数返回错误值。

第4个参数是指明查找时是精确匹配还是近似匹配。

如果参数是TRUE或省略了参数，表明是近似匹配，则匹配成功的值是比要查找的数据小的那些数据当中的最大数值。

如果参数是FALSE，则进行精确匹配，如果找不到，则返回错误。

拖动G4单元格的填充柄，复制该公式到G5：

G11单元格，可以自动求出其他学生成绩等级。

31）PMT财务函数的使用

该函数是根据固定贷款利率、贷款期数、贷款金额，按等额分期付款方式，计算出每期偿还多少本息。

函数形式为

PMT（rate，nper，pv，[fv]，[type]）

rate：

贷款利率。

若需要按月偿还，应该换算成月利率。

nper：

贷款期数，即该项贷款的付款期总数，与rate的单位要一致。

pv：

贷款金额，也称为本金。

fv：

未来值或在最后一次付款后希望得到的现金金额。

一般省略该参数，表明一笔贷款的未来值是0。

type：

数字0或1。

指定每期的还贷时间是期初还是期末，省略该参数，默认为0。

例如：

某职工用住房公积金贷款买房，假定贷款金额20万元，年利率是5.22%，贷款10年，按月等额还款，计算每月付款额。

首先在工作表中输入贷款金额、年利率、贷款期限等数据，如下图所示。

在B5单元格中输入函数“=PMT（B3/12,B4*12,B2）”，可以算出每月偿还贷款2142.88元。

如果贷款期限分别是8年、15年20年，则每月付款额是多少？

在E3单元格中，输入函数：

“=PMT（$B$3/12,D3*12,$B$2）”，复制该公式到E4：

E5。