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。
升级会员 