工作表的公式和函数

【实训目的】

（1）掌握Excel工作表中公式的输入和技巧。

（2）掌握Excel工作表中函数的使用方法，熟练掌握常用函数的使用技巧。

【实训内容】

（1）用公式求平均值、自定义公式。

（2）常用函数（求和、平均值、最大值、最小值等）和复杂函数（RANK，COUNTA， VLOOKUP）的应用。

【实训要求】

（1）在“学生成绩表”工作簿中计算“原始成绩”工作表中所有学生的成绩平均分。

（2）在“全年产品销售情况表”工作簿中计算“2月”工作表中库存、入库余存和出库余存的各项数据值。

（3）按照边台1200元、中央台5000元、吊柜1000元、天平台2200元、试剂架300元、水槽500元、水龙头10元、水柜200元的单价计算各项余存的金额。将单元格设置为货币型、两位小数、符号为“$”、负数形式第五种。

（4）利用函数在“学生成绩表”工作簿中计算“原始成绩”工作表中所有学生的成绩平均分，总分的“最高分”和“最低分”；按总分进行排名。

（5）在“销售报表”工作簿“Sheet1”工作表中按合并单元格格式填充序号。

【实训操作步骤】

4.3.1 公式的应用

公式是利用运算符把单元格数值、单元格地址连接在一起的句子。要输入公式时需要在选中的单元格中先输入“＝”或者在“编辑栏”中输入“＝”，再输入公式，按Enter键完成公式的录入。

【知识小贴士】

所有运算符和标点符号必须在英文输入法状态下进行输入。

（1）计算平均分。打开“学生成绩表”工作簿，选定“原始成绩”工作表，在“性别”后插入一列“平均分”，选中D2单元格，输入“＝（E3＋F3＋G3＋H3＋I3＋J3）／6”，按Enter键完成公式的输入。再次选中D2单元格，拖动填充柄至D23单元格松开，完成全部计算，如图4-20所示。

图4-20 用公式求“平均分”

（2）自定义公式的使用。

1）打开“全年产品销售情况表”工作簿，选定“2月”工作表，清除E5：K12单元格内容。再次选中E5单元格，输入“＝1月′！K5”，按Enter键完成输入，拖动填充柄至E12单元格松开，完成全部计算，如图4-21所示。

图4-21 “自定义公式”的使用

2）选中H5单元格，输入“＝E5＋F5”，选中K5单元格，输入“＝E5＋F5－I5”，按上述1）中方法，用填充柄完成H6至H12，K6至K12的计算。

3）在“备注”前插入一列“金额”，按照边台1200元、中央台5000元、吊柜1000元、天平台2200元、试剂架300元、水槽500元、水龙头10元、水柜200元的单价，分别在L5至L12计算金额，如L5单元格输入“＝K5*1200”，依次完成L6至L12金额的计算。完成后如图4-22所示。

图4-22 “2月”工作表

4）选中L5至L12单元格，右击弹出菜单，选择“设置单元格格式”选项，在“数字”选项卡中，将单元格设置为货币型、两位小数，货币符号为“$”负数形式为第五种。单击“确定”按钮完成设置，如图4-23所示。

图4-23 设置单元格的货币格式

【知识小贴士】

除了可以用填充柄方式外，还可复制和移动公式。

例如，在“全年产品销售情况表”工作簿“1月”工作表中，当在H6单元格到H12单元格中还未输入公式时，先选中H5单元格，按Ctrl＋C组合键复制公式，在H6单元格用Ctrl＋V组合键粘贴公式，此时公式里的行列号也会自动更新为当前单元格的行列号；如要将E13数据移动到A14，可选中E13，移动光标至边框处，出现十字箭头“”时即可拖动该单元格内容到A14，也可用此方法将单元格内容移动到任意位置。

4.3.2 函数的应用

函数是一个预先定义好的内置公式，可以利用它进行更复杂的运算，一般通过“公式”选项卡的“”按钮实现函数的调用。也可在选中单元格后，单击编辑栏的“”按钮调用函数。

（1）用常用函数求最大值、最小值和平均值。

1）打开“学生成绩表”工作簿，选中“原始成绩”工作表，在“平均分”行前插入两行，分别为“最高分”和“最低分”，行高设为20，如图4-24所示。

图4-24 “原始成绩”中函数的使用

2）选择E24单元格，如图4-25所示，直接在“开始”选项卡中的“编辑”组功能区选择“自动求和”下拉菜单中的“最大值”一项，或单击编辑栏的“”按钮，在弹出的窗口中选择“MAX”，选择确定，接着选择默认函数参数，如图4-26和图4-27所示，单击“确定”按钮即完成计算。

图4-25 直接选择“最大值”函数

图4-26 “插入函数”对话框

图4-27 “函数参数”窗口

3）按照上述2）的方法，分别选择MIN函数和AVERAGE函数在E25计算最低分和在E26计算平均分。

（2）RANK函数的使用。

2）在K2单元格输入“名次”，选择K3单元格，单击编辑栏的“”按钮，在“插入函数”对话框中搜索“RANK”函数，单击“确定”按钮后，输入参数，在“Number”中输入“J2”，在“Ref”中输入绝对地址“＄J＄2：＄J＄24”，单击“确定”按钮，如图4-28所示。

3）选中K3单元格，用填充柄的方式，完成K3：K23的名次计算。

（3）COUNTA函数的使用。当遇到合并单元格填写序号数量较多时，逐条录入十分麻烦，可以使用COUNTA计算非空单元格的数量来完成这一计算过程，将序号自动填充。

1）打开“销售报表”工作簿，选中“Sheet1”工作表，选中“序号”这一列中B3到B21单元格，其呈反色显示。

图4-28 “RANK”函数设置

2）在编辑栏中输入“＝COUNTA（＄B＄3：B3）”，按Ctrl＋Enter组合键，所有合并和未合并单元格中自动填充序号，如图4-29所示。

图4-29 COUNTA函数的使用

【知识小贴士】

Ctrl＋Enter组合键的作用：可使用当前条目填充选定的单元格区域。

3）按照合并单元格进行小计求和。利用合并单元格只有左上角第一个单元格有值，其余均为空单元格的特点，可以做如下操作：选中H3：H21单元格，在编辑栏中输入公式“＝SUM（G3：G＄22）－SUM（H4：H＄22）”，按Ctrl＋Enter组合键即可。

【知识小贴士】

当最后一行的单元格是单数时，为避免循环引用的错误，公式中求和时用到G22和H22表格以外的区域，当最后一行为合并单元格时可不必这样引用。

（4）VLOOKUP函数的使用。

打开“商品信息”工作簿，选中“Sheet2”，用VLOOKUP函数在“商品单价”工作表中查询商品的品名和单价。

1）选中C2单元格，在编辑栏输入公式“＝VLOOKUP（B2，商品单价！＄A＄2：＄C＄8，2）”，再选中C2单元格，用拖动填充柄的方式完成C2：C11单元格的商品名称计算。

2）选中D2单元格，在编辑栏输入公式“＝VLOOKUP（B2，商品单价！＄A＄2：＄C＄8，3）”，再选中D2单元格，用拖动填充柄的方式完成D2：D11单元格的商品名称计算。

3）选中F2单元格，输入公式“＝E2*D2”，用拖动填充柄的方式完成F2：F11单元格金额的计算，如图4-30所示。

图4-30 直接输入VLOOKUP函数

也可以通过单击“”按钮，选择插入函数“VLOOKUP”，在弹出的函数参数窗口中第一行“Lookup＿value”中录入“B2”，在第二行“Table＿array”中录入“商品单价！＄A＄2：＄C＄8”，在第三行“Col＿index＿num”中录入“2”或“3”，单击“确定”按钮，完成计算，如图4-31所示。

图4-31 “VLOOKUP”函数设置

【巩固与提高练习】

（1）打开素材文件夹中的“奖金”工作簿，按下列要求进行操作。

1）根据“奖金比例”工作表中的数据，查询计算出对于不同业绩应发的奖金比例。

2）根据资金比例，计算“业绩奖金”。

3）计算员工的工资总所得。

（2）打开素材文件夹中的“采购预算表”工作簿，计算不同商品在不同数量下的折扣情况。