计算机应用基础实训项目五-补助金额查询表课件.pptx

1、项目五项目五 补助金额查询表补助金额查询表本项目我们主要介绍的是countif和sumif这两种函数来计算表格数据值，此外再使用数据有效性来控制单元格内容的输入。使用查找与引用的vlookup函数完成补助金各字段的查询任务。Countif和sumif函数计算数据有效性的设置Vlookup函数完成数据查询打开素材文件夹中的“补助金查询.xlsx”工作簿，在补助金额那栏的G2单元格中输入计算公式“=$J$5*E2-F2”，拖动填充柄，完成补助金额的计算。1、在“汇总”工作表中，单击D5单元格，运用函数计算各街道补助金额发放人数的总和。打开【公式】选项卡，选择“其他函数”“统计”下的“countif

2、”函数，计算“街道”所在列中各个街道人员的数量总和。2、在打开的countif函数参数设置中，第一个空格引用了“数据”工作表中的B2：B101单元格区域，在第二个单元格中，引用条件即C5单元格，计算出凤凰街道的人数总和。3、双击D5单元格，打开函数计算表达式，将表达式中的“$B$2:$B$101”改成绝对引用地址，即把函数改成“=COUNTIF(数据!$B$2:$B$101,C5)”。4、拖动填充柄，完成其他数据的计算，并用求和函数计算人数总和。1、用鼠标选中E5单元格，打开【公式】选项卡下的“数字和三角函数”，选择“SUMIF”函数，计算各个街道补助金额的总和。2、在弹出的SUMIF函数的参

3、数设置对话框中，依次在三个空格中输入或者引用单元格区域“数据!B2:B101”、“C5”和“数据!G2:G101”，完成第一个凤凰街道的补助金额的数值求和。3、双击D5单元格，将其中“数据”工作表的B列和G列区域设置为绝对引用地址，锁定住这两块区域，函数表达式修改为“=SUMIF(数据!$B$2:$B$101,C5,数据!$G$2:$G$101)”。4、再用拖动填充柄复制单元格函数的方法求出其他街道的金额总数，最后使用求和函数求出E12单元格的总金额。1、打开“查询”工作表，选中E5单元格，打开【数据】选项卡中的“数据有效性”。2、在“数据有效性”对话框中，单击“设置”选项卡，在“允许”下拉列

4、表中选择“序列”选项。单击“来源”编辑框右侧的“折叠”按钮，然后用鼠标选取“数据”工作表中的A2：A101单元格区域，再单击“展开”按钮返回“数据有效性”对话框，“数据”工作表中的A2：A101就成为了“序列”的来源。3、单击“确定”按钮后，返回工作表，在E5单元格右侧会出现下拉列表，可以从中选择所有的序号，方便快捷且不容易出错。3、单击“确定”按钮后，返回工作表，在E5单元格右侧会出现下拉列表，可以从中选择所有的序号，方便快捷且不容易出错。1、回到“数据”工作表，在表格中选中A2：G102单元格，在表格的左上方的位置输入“数据”字样，命名这块单元格区域的名字为“数据”。2、再返回“查询”工作

5、表，单击E8单元格，打开【公式】选项卡中的“查找与引用”下的VLOOKUP函数，通过这个函数的查找引用的功能，利用序号，找到每一个人员所在的街道、居委会、户主姓名、人口、救助金额等内容。3、在VLOOKUP函数的参数设置对话框中，共有4个空格。第一个表示需要查找与引用的关键字，我们用序号来查找，因此，此处填入“E5”单元格。第二个空格表示的是需要查找信息的原始表格区域名称，我们在前面已经将A2：G101单元格命名为“数据”，因此，此处输入“数据”即可。第三个空格指的是我们查找的E8单元格需要显示“街道”的这个内容，在“数据”区域中是第几列，根据数据所在区域可以看出“街道”所在的位置是表格的第2

6、列。同样的“居委会”是第3列，“户主姓名”是第4列，“人口”是第5列，“补助金额”是第7列。第四个空格如果需要精确查找的话，可以忽略不计。根据我们的要求，依次在4个空格中填入内容。单击“确定”按钮返回表格。4、当我们在E5单元格中，在下拉列表下选择任意一个序号，在E8单元格就会出现所对应的街道名称，顺利的完成了数据的查找与引用。5、双击E8单元格，将VLOOKUP函数表达式修改成“=VLOOKUP($E$5,数据,2)”。根据查询要求，我们再依次在E9、E10、E11、E12等单元格中复制或者输入“=VLOOKUP($E$5,数据,3)”、“=VLOOKUP($E$5,数据,4)”、“=VLO

7、OKUP($E$5,数据,5)”、“=VLOOKUP($E$5,数据,7)”等函数表达式。完成“居委会”、“户主姓名”、“人口”、“补助金额”等数据的查询与引用。6、在E5单元格中任意选择几个序号，查看VLOOKUP函数所制作出来的查询引用效果。本项目是对补助金数据进行查询。先使用countif函数和sumif函数对金额进行计算，然后设定“序号”字段的数据有效性，最后使用vlookup函数进行各种字段数据的查询。打开素材文件夹中的“家电销售情况查询表.xlsx”工作簿，按照要求完成销售情况查询。1、根据原始数据，统计各个产品的销售数量，并计算销售额，设置数据格式。2、在H4单元格中设置数据有效性，数据来源为表格中的A3：A17单元格。3、利用函数完成如图所示的销售情况查询表。