大学计算机基础 课件 6.4.3 总结与提高.pptx

大学计算机基础——基于计算思维（Windows10+Office2016）

6.4.3总结与提高

第6章Excel综合应用

6.4奖学金统计

1.SUMIFS函数

（1）将“北京”地区的所有数据过滤出来。

（2）在过滤出来的数据中，对积分=17000的数据进行再次过滤。

（3）统计最后过滤出来的积分=17000的和。

如图所示，要统计“北京”地区游戏积分在17000分以上（含）的总积分，需要：

1.SUMIFS函数

SUMIFS(Sum_range,Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)

可以使用SUMIFS函数来实现。SUMIF函数用来对区域中满足多个条件的单元格求和。

SUMIF函数的语法格式为

参数的含义是：

Criteria_range1和Criteria1：用Criteria1条件对criteria_range1区域进行过滤。Criteria_range2和Criteria2：用Criteria2条件对criteria_range2区域进行过滤。依次类推。

Sum_range：要求和的单元格区域。

1.SUMIFS函数

因此，统计“北京”地区游戏积分在17000分以上（含）的总积分的公式为“=SUMIFS(C3:C10,B3:B10,北京,C3:C10,=17000)”。

需要注意的是，SUMIFS和SUMIF函数的参数顺序有所不同。Sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。

2.双轴线图表制作

如图所示，要制作国内生产总值及增速比较图，由于数据差异大，采用统一的坐标轴来展现是不合适的。因此可以通过调整分别设置国内生产总值的坐标轴和增速的坐标轴，即用双轴线图表来实现数据差异过大的图表展现。

2.双轴线图表制作

①选择数据区域，插入二维簇状柱形图。

②将其布局方式设置为“布局4”。

③选中图表中代表增速的数据图形，单击鼠标右键，在弹出的快捷菜单中选择“设置数据系列格式”，将其设置为次坐标，如图所示。

操作步骤如下：

2.双轴线图表制作

④双击图表右边的百分比坐标轴数字标签，将其最大值设置为0.3，主要刻度线类型为“内部”，如图所示。

操作步骤如下：

2.双轴线图表制作

⑤选中图表中代表增速的数据图形，单击鼠标右键，在弹出的快捷菜单中选择“更改图标类型”，将其设置为折线图，如图所示。

操作步骤如下：

2.双轴线图表制作

⑥选中图表中代表增速的数据图形，单击鼠标右键，在弹出的快捷菜单中选择“设置数据系列格式”，将其“数据标记选项”的数据标记类型设为“内置”，大小为7，如图所示。

⑦选中图表中代表国内生产总值的数据图形，通过“图表工具—格式”选项卡，把柱状图填充的颜色去掉，换成框线。

操作步骤如下：

3.查找与替换高级应用

Excel2010的“查找与替换”功能与Word2010类似，除了可以在工作表中查找数据外，也可以进行特殊格式的查找和替换。通过“开始”选项卡/“编辑”组/“查找和选择”按钮，可以对选定的单元格区域、工作表或整个工作簿，查找出使用了公式、批注、条件格式或数据验证等特殊格式的单元格，也可以查找和替换含公式的自定义高级格式。

3.查找与替换高级应用

如左图所示，由于部分员工的奖金是用美元计算（货币格式为“-US$1,234.10”），因此在计发当月工资时，需要将奖金结算为人民币。因此，针对C列的奖金，需要找出货币格式为“-US$1,234.10”的单元格，并将其内容替换为原单元格数值×美元兑人民币汇率。

3.查找与替换高级应用

由于Excel不允许单元格的自引用，因此可以将C列中的数据复制到D列，然后对D3:D19中的数据进行查找与替换，如果其货币格式为“-US$1，234.10”，则用C列中对应的单元格数据×美元兑人民币汇率来替代当前单元格的内容即可。

①选择C13:C19单元格区域，将其复制到D3:D19单元格。

②如图所示，选择D13:D19单元格区域，单击“开始”选项卡/“编辑”组/“查找和选择”按钮的“替换”。单击“选项”按钮，展开对话框。

操作步骤如下：

3.查找与替换高级应用

③单击“查找内容”的“格式”，在“查找格式”对话框中选择“从单元格选择格式”按钮，用鼠标选择D7单元格，表示查找与D7单元格格式相同的数据。

④单击“替换为”的“格式”，在“替换格式”对话框中选择“从单元格选择格式”按钮，用鼠标选择D3单元格，表示将查找到的内容的格式替换为D3单元格格式。

⑤在“替换为”内容框中，输入“=INDEX(C:C,ROW(),0)*C20”。表示将查找到的内容替换为C列（C:C）当前行（ROW()）单元格乘以