实验二　Excel公式、函数的使用方法

一、实验目的

1．熟练掌握公式和函数的使用方法。

2．掌握条件格式的使用方法。

3．掌握绝对引用和相对引用方法。

4．掌握数据排序方法。

5．掌握数据筛选方法。

二、实验内容

输入工资表和水电气费用表，使用公式和函数进行统计运算，并对数据进行排序。1．创建“实验二．xls”

启动Excel 2003，将空白工作簿保存为“实验二．xls”。

2．创建“工资表”和“水电气费用表”

将Sheet1命名为“工资表”，Sheet2命名为“水电气费用表”。

3．在“工资表”中输入数据并进行格式化

在“工资表”中输入数据并按照图9．16的效果对表格进行格式化。

图9．16　工资表

4．公式操作

单击J3单元格，在编辑栏中输入公式“＝E3＋F3＋G3”后按回车键或单击编辑栏左边的“√”按钮即可得到01234号员工应发的工资额，如图9．17所示。接着将鼠标移到J3单元格的填充柄处，当鼠标出现黑色“十”字时向下拖曳至J8单元格，则可以得到其他员工的应发工资额。

图9．17　工资表中员工“应发工资”项的设置

单击J4单元格，观察一下编辑栏就会发现该单元格的公式变成了“＝E4＋F4＋G4”，也就是说在进行向下填充操作时，公式中的单元格名称的行号部分也在随着当前行号的变化而变化，因此计算的结果跟着发生变化。用这种方法对数据进行计算是非常方便的，因为只需将第一个单元格的公式输入，然后采用自动填充方式就可以得到其他单元格的计算结果。用同样的方法计算每个员工的实发工资数。实发工资的计算公式为：实发工资＝应发工资－失业保险－公积金。请读者自己构造公式，并使用填充柄向下填充公式，然后将“实发工资”列的数据格式设置为货币，保留1位小数，货币符号为“￥”，如图9．18所示。

图9．18　工资表中员工“实发工资”项的设置

以上操作中，之所以将J3单元格的填充柄向下填充就可以得到其他单元格的计算结果，原因在于公式“＝E3＋F3＋G3”中的单元格引用方式为相对引用，其行号、列号可以随当前行号、列号的变化而变化。除相对引用外，还有绝对引用和混合引用。

以E3单元格为例，其相对引用方式为“E3”，绝对引用方式为“＄E＄3”，混合引用方式有两种：“＄E3”与“E＄3”。行号或列号前有“＄”符号，表示该行号或列号在被引用时是不会发生变化的；如果行号、列号前没有该符号，则表示该行号或列号在被引用时会发生递增或递减变化。例如，假设在J3单元格中输入的公式是“＝＄E＄3＋＄F＄3＋＄G＄3”，则进行填充时各个单元格的数据都变成J3单元格的数据。

关于绝对引用、相对引用和混合引用，读者可以自己做个测试。比如，在一个空单元格中输入公式“＝E3”（注意：输完公式后不能按回车键），然后按功能键F4，观察编辑栏中引用方式的变化情况。选定一种引用方式后按回车键，再使用填充柄向四个方向填充，观察数据的变化情况并归纳出数据的变化规律。

5．条件格式的使用

任务：将实发工资低于2 000元的单元格的文字颜色设置为红色。

操作步骤：先选中K3到K8之间的单元格区域，再单击“格式”菜单中的“条件格式”选项，在打开的“条件格式”对话框中按图9．19所示方法设置条件，然后单击“格式”按钮，在打开的“字体格式”对话框中将文字颜色设置为红色，这样所有实发工资低于2 000元的单元格都被标上了红颜色。

图9．19　“条件格式”对话框

6．函数的使用

分别在B10、D10、B11、D11单元格中输入“平均应发工资”、“应发工资最高额”、“实发工资总额”、“底薪为1 600的人数”。将C10、E10、C11单元格的数字格式设置为货币，保留1位小数。

选中C10单元格并单击编辑栏左边的“fx”按钮，或者单击“插入”菜单中的“函数”选项，即可打开“插入函数”对话框，如图9．20所示。

图9．20　“插入函数”对话框

在“或选择类别”栏中选择“常用函数”，在“选择函数”栏中选择“AVERAGE函数”，再点击“确定”按钮，会出现一个参数设置对话框（如图9．21所示）。如果所需的函数在列表中没有显示出来，而且也不知道该函数属于哪个类别，此时可以在“搜索函数”框中输入所需的函数名，再单击“转到”按钮，系统就会帮你找到该函数并显示在列表中。

图9．21　AVERAGE函数的参数设置对话框

在参数选择对话框中，单击“Number1”参数框中的按钮，将J3到J8之间的单元格选中（即将所有的“应发工资”单元格选中）后单击图9．22中的按钮，然后“Number1”中就会显示刚才所选择的单元格区域，如图9．23所示。最后单击“确定”按钮，C10单元格就会显示平均的应发工资额。

图9．22　AVERAGE函数的参数设置对话框中Number1的使用

图9．23　AVERAGE函数对平均应发工资额的计算

参照上述方法，用SUM函数计算实发工资总额，用MAX函数计算应发工资最高额。

计算“底薪为1 600的人数”的方法如下：

单击E11单元格，打开“插入函数”对话框，选择“统计”类函数中的“COUNTIF”函数，单击“确定”按钮后打开COUNTIF函数参数设置对话框，如图9．24所示。其中，Range参数用于设定将要统计的数据范围，Criteria参数用于设定统计的条件。单击“Range”参数框的按钮，选择E3到E8区域，再在“Criteria”参数框中输入“＝1600”后单击“确定”按钮即可得到底薪低于1 600元的人数。

图9．24　COUNTIF函数的参数设置对话框

7．对数据进行排序

选择A2到K8之间的单元格区域，单击“数据”菜单中的“排序”选项，打开“排序”对话框，如图9．25所示。将“主要关键字”设置为“实发工资”，选中“降序”单选按钮，再单击“我的数据区域”栏的“有标题行”单选按钮，最后单击“确定”按钮，则表中的数据即按“实发工资”由大到小的顺序排列。

图9．25　“排序”对话框

“排序”对话框中的“有标题行”选项是指如果所选择的单元格区域含有标题，则要选中该选项，这样Excel在排序时就会把标题行排除在外，否则将会把标题行和数据行一起进行排序，那样就会出错。

如果表中的数据要按照多个关键字排序，则可以在“排序”对话框中设置“次要关键字”、“第三关键字”选项，则表中的数据将先按主关键字排序；当主关键字的值相同时，则按次关键字排序；当主关键字、次关键字都相同时，则按第三关键字排序，最多允许有三个关键字。

8．数据筛选

任务：在表中查询应发工资高于2 500元而底薪低于2 000元的员工的信息。

步骤：选中A2至K8之间的单元格区域，单击“数据”菜单，将鼠标移至“筛选”菜单项，在打开的子菜单中选中“自动筛选”，此时选中区域的第一行中的每个单元格右下角都多了一个下三角形，如图9．26所示。

图9．26　“数据”菜单中“自动筛选”项的选择

单击“底薪”单元格右下角的三角形，在下拉列表中选择“自定义”选项，在打开的对话框中进行如图9．27的设置后单击“确定”按钮，则表中只显示所有底薪低于2 000元的员工的信息。

图9．27　“自定义自动筛选方式”对话框

同样，单击单元格“应发工资”右下角的三角形，在下拉列表中选择“自定义”选项，在打开的对话框中设定筛选条件为“应发工资大于2 500”，单击“确定”后，则表中只显示所有底薪低于2 000元且应发工资高于2 500元的员工的信息，如图9．28所示。

图9．28　底薪低于2 000元且应发工资高于2 500元的员工的信息

筛选后如果要显示全部数据，只要选择“数据”菜单中“筛选”子菜单中的“全部显示”命令即可。如果不想让数据处于筛选状态，即取消单元格右下角的三角形，可以再次选择“数据”菜单中“筛选”子菜单中的“自动筛选”命令，使它处于未选中状态即可。

以上操作全部完成后，单击“保存”按钮保存工作簿“实验二．xls”。（实际上，为了避免丢失修改，在操作过程中就应不时地保存文件。）

9．“水电气费用表”的制作

参照上述方法，在“水电气费用表”中录入以下数据（注意输入门牌号前先将A列数字格式设置为“文本”），按要求进行格式化，并进行公式、函数、排序和筛选操作。具体要求如下：

（1）标题行行高40，楷体，24号，加粗，蓝色。

（2）第2、第9、第10行行高25，楷体，14号，加粗，黑色。

（3）第3至第8行行高18，宋体，12号，黑色，第一列加粗，其余不加粗。

（4）A列的列宽为12，其余的列宽为15。

（5）E列、第9、第10行的数字格式为货币，保留1位小数，使用人民币符号“￥”。

（6）将E2至E8之间的单元格底纹设置为黄色，A9到E9的底纹设置为浅绿色，A10 到E10的设置为浅紫色，如图9．29所示。

图9．29　“水电气费用表”数据的录入和格式化

（7）在E3单元格中使用公式计算101用户的水电气费总和，再用填充柄向下填充E4 至E8之间的单元格。

（8）在B9单元格中使用函数计算所有用户的水费总和，用填充柄向右填充C9、D9、E9单元格。

（9）在B10单元格中用函数计算每户平均水费，再用填充柄向右填充C10、D10、E10单元格。

（10）选中A3至E8之间的单元格区域，按照“合计”列的数据升序排列，如图9．30所示。

图9．30　“水电气费用表”的公式、函数、排序和筛选操作

（11）对表中数据进行筛选，要求显示合计高于700、水费低于200的用户的信息，如图9．31所示。

图9．31　合计高于700、水费低于200的用户的信息

（12）仍以文件名“实验二．xls”保存工作簿。