办公软件Excel高级操作使用技巧函数教程Word文档格式.docx
《办公软件Excel高级操作使用技巧函数教程Word文档格式.docx》由会员分享，可在线阅读，更多相关《办公软件Excel高级操作使用技巧函数教程Word文档格式.docx（26页珍藏版）》请在冰豆网上搜索。
任务3-4应用控件控制工作表数据21
单元4保护和共享数据22
任务4-1加密工作簿22
任务4-2保护工作表以限制输入22
任务4-3为工作簿设定属性23
任务4-4共享工作簿24
任务4-5将工作表中数据导出为XML文件25
任务4-6显示共享工作簿中的修订26
单元1Excel公式与函数的高级应用
任务1-1应用COUNTIF函数进行条件统计
在工作表“图书销售统计”的单元格M2中，添加一个函数以对“仓库2”中的图书种类进行计数。
素材文档：
E01-01.xlsx
结果文档：
E01-01-R.xlsx
任务解析：
COUNTIF函数用于对区域中满足单个指定条件的单元格进行计数。
例如，可以对以某一字母开头的所有的单元格进行计数，也可以对大于或小于某一指定数字的所有单元格进行计数。
COUNTIF函数的语法为：
COUNTIF（range,criteria）。
其中，range为要进行计数的单元格区域，criteria是用于定义对哪些单元格进行计数的条件。
解题步骤
1.选定单元格“M2”；
2.单击“公式”选项卡/“其他函数”下拉按钮；
3.在下拉菜单中单击“统计”函数；
4.在下一级菜单中单击“COUNTIF”；
5.打开“函数参数”对话框，在“Range”文本框中输入“C2:
C199”，在“Criteria”文本框中输入“C2”；
6.单击“确定”按钮；
在使用COUNTIF函数进行计数的时候，如果使用者对函数的参数还不熟悉，最好的方法是使用“函数参数”对话框输入函数，在逐渐对该函数的各个参数熟悉之后，直接在Excel的编辑栏中输入函数，效率会更高。
例如本任务，可以在选定M2单元格后，直接在编辑栏输入“=COUNTIF（C2:
C199,C2）”，然后按Enter键，即可完成任务的解答。
其他函数的输入方法与本任务相同。
任务1-2应用COUNTIFS函数对符合条件的数据计数
在工作表“ABC电脑销售统计”的单元格P2中，使用COUNTIFS函数，计算在区域3中，有多少销售人员的年度总销售量超过了25000。
E01-02.xlsx
E01-02-R.xlsx
COUNTIFS函数用于对区域中满足多个指定条件的单元格进行计数。
COUNTIFS函数的语法为COUNTIFS（criteria_range1,criteria1,[criteria_range2,criteria2]...）。
其中criteria_range1为计算关联条件的第一个区域，criteria1为第一个关联条件。
该函数总共可以对127个关联条件区域进行多条件计数。
1.选定单元格“P2”；
4.在下一级菜单中单击“COUNTIFS”；
5.打开“函数参数”对话框，在“criteria_range1”文本框中输入“C3:
C56”,在“criteria1”文本框中输入“C6”，在“criteria_range2”文本框中输入“N3:
N56”，在“criteria2”文本框中输入“"
>
25000"
”；
任务1-3应用SUMIFS函数对符合条件的数据求和
在工作表“图书销售统计”的单元格M2中，插入SUMIFS函数，计算仓库2中以“酒”开头的图书中，销往东北的总金额。
E01-03.xlsx
E01-03-R.xlsx
SUMIFS函数用于对区域中满足多个条件的单元格求和。
例如，以某个字母开头的单元格或者大于某个数值的单元格等。
该函数的语法为“SUMIFS（sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…）”。
其中，sum_range为包含要进行求和的数值的单元格区域，criteria_range1为第一个关联条件区域，criteria1为第一个关联条件。
SUMIFS函数最多允许附加127个关联区域。
2.单击“公式”选项卡/“数学和三角函数”下拉按钮；
3.在下拉菜单中单击“SUMIFS”函数；
4.打开“函数参数”对话框，在“sum_range”文本框中输入“D2:
D199”，在“Criteria_range1”文本框中输入“C2:
C199”，在“Criteria1”文本框中输入“C2”，在“Criteria_range2”文本框中输入“A2:
A199”，在“Criteria2”文本框中输入“"
酒*"
5.单击“确定”按钮；
任务1-4应用AVERAGEIFS函数统计符合条件的数据平均值
在工作表“图书销售统计”的单元格M2中，使用AVERAGEIFS函数，计算仓库2中的销往华中的平均值。
（剔除值为0的情况）
E01-04.xlsx
E01-04-R.xlsx
与SUMIFS函数用法类似，AVERAGEIFS函数用于对区域内满足多个条件的单元格求平均值。
该函数的语法为“AVERAGEIFS（average_range,criteria,[criteria_range2,criteria2],…）”。
其中，average_range为包含要进行求平均值的数值的单元格区域，criteria_range1为第一个关联条件区域，criteria1为第一个关联条件。
AVERAGEIFS函数最多允许附加127个关联区域。
4.在下一级菜单中单击“AVERAGEIFS”；
5.打开“函数参数”对话框，在“average_range”文本框中输入“I2：
I199”，在“average_range1”文本框中输入“C2：
C199”，在“criteria1”文本框中输入“C2”，在“average_range2”文本框中输入“I2：
I199”，在“criteria2”文本框中输入“"
<
0"
任务1-5应用HLOOKUP函数进行数据查询（F1:
HLOOKUP）
在工作表“ABC电脑销售量统计”的单元格C10中，使用HLOOKUP函数，查找西南区域的销售经理的总销售量。
E01-05.xlsx
E01-05-R.xlsx
HLOOKUP函数用于在表格的首行查找指定的数值，并返回该数值同一列中指定行的值。
HLOOKUP中的H代表“行”。
该函数的语法为“HLOOKUP（lookup_value,table_array,row_index_num,[range_lookup]）”。
其中，lookup_value为需要在表格的第一行中查找的数值，table_array为需要在其中查找信息的表格，row_index_num为在首行查找到的数值后，所要返回的值所在的行的序列号，[range_lookup]为逻辑值，可选，如果填写1，则进行近似匹配，如果填写0,则进行精确查询。
1.选定单元格“C10”；
2.单击“公式”选项卡/“查找与引用”下拉按钮；
3.在下拉菜单中单击“HLOOKUP”函数；
4.打开“函数参数”对话框，在“lookup_value”文本框中输入“G3”，在“table_array”文本框中输入“D3:
J8”，在“row_index_num”文本框中输入“6”，在“range_lookup”文本框中输入“0”；
任务1-6应用VLOOKUP函数进行数据查询
在工作表“ABC电脑销售量统计”的单元格B12中，使用VLOOKUP函数，查找华东区域的销售经理的总销售量。
E01-06.xlsx
E01-06-R.xlsx
VLOOKUP函数用于在表格的首列查找指定的数值，并返回该数值同一行中指定列的值。
VLOOKUP中的V代表“列”。
该函数的语法为“VLOOKUP（lookup_value,table_array,col_index_num,[range_lookup]）”。
其中，lookup_value为需要在表格的第一列中查找的数值，table_array为需要在其中查找信息的表格，col_index_num为在首列查找到数值后，所要返回的值所在的列的序列号，[range_lookup]为逻辑值，可选，如果填写1，则进行近似匹配，如果填写0，则进行精确查询。
如果表格是横向的，也就是说所要查询的数值在表格的第一行，则使用HLOOKUP函数；
相反，如果表格是纵向的，即所要查询的数值在表格的第一列，那么应当使用VLOOKUP函数。
我们日常所建立的表格，大多为纵向的表格，因此VLOOKUP函数在工作中应用更为广泛。
1.选定单元格“B12”；
3.在下拉菜单中单击“VLOOKUP”函数；
4.打开“函数参数”对话框，在“Lookup_value”文本框中输入“B9”，在“Table_array”文本框中输入“B3:
G9”，在“Col_index_num”文本框中输入“6”，在“Range_lookup”文本框中输入“0”；
任务1-7设置EXCEL选项——更改公式错误标识
配置Excel，以使用红色标识检测到的公式错误。
E01-07.xlsx
E01-07-R.xlsx
在安装完毕，首次使用的时候，Excel2010已经包含了各种默认的设置，如显示界面、保存方式等。
如果出于特殊需求，要对这些设置进行修改，那么需要在Excel选项中更改设置。
在函数与公式方面，Excel2010也有其默认的设置，比如，如果一个单元格中的公式包含错误，那么会在这个单元格的左上角出现相应的标记，有时为了避免标记的颜色和单元格的底色混淆，可以修改Excel2010所默认的单元格错误标识颜色为任意的其他颜色。
1.单击“文件”选项卡/“选项”按钮；
2.在打开的“Excel”选项对话框中，单击“公式”选项卡；
3.单击“错误检查”组中的“使用此颜色标识错误”下拉按钮，在下拉；
4.单击“确定”按钮，完成设置。
任务1-8设置Excel选项——启用迭代计算
启用迭代计算，并将最多迭代次数设置为50。
E01-08.xlsx
E01-08-R.xlsx
当某个单元格中的公式直接或间接引用了该单元格自身时，就构成循环引用。
例如，单元格A1中的公式为“=A1+A2”，这个公式引用了单元格A1本身。
Excel可能无法处理这一状况。
要想使Excel能处理循环引用，需要启用迭代计算。
所谓迭代：
是指在满足特定数值条件之前重复计算工作表。
迭代计算可对性能产生重要影响。
因此在默认情况下，Excel中关闭了迭代计算。
启用迭代计算的同时，还必须确定迭代次数，也就是重新计算公式的次数。
2.在打开的“Excel选项”对话框中，单击“公式”选项卡；
3.选中“计算选项”组的“启用迭代计算”复选框；
4.在“最多迭代次数”文本框中输入“50”；
5.单击“确定”按钮，完成设置。
任务1-9追踪单元格的公式引用
在工作表“ABC电脑销售统计”中，追踪单元格N57的所有直接和间接的公式引用。
E01-09.xlsx
E01-09-R.xlsx
引用单元格是指被其他单元格中的公式引用的单元格。
例如，如果单元格D10的公式为“=B5”，那么单元格B5就是单元格D10的引用单元格。
在一个复杂的工作表中，某个单元格中的公式可能会引用多个其他的单元格，而这些被引用的单元格常常又引用了另外的单元格，由此形成多个级别的直接和间接引用关系，这使得检查公式是否准确或者查找错误根源变得十分困难。
为了帮助检查公式，Excel提供了“追踪引用单元格”命令，以图形方式显示或追踪某个单元格与其引用单元格之间的关系。
1.选定单元格“N57”；
2.单击“公式”选项卡/“追踪引用单元格”按钮；
3.可以看到通过箭头的形式指示了单元格N57的所有直接引用的单元格，再次单击“追踪引用单元格”按钮；
4.可以看到显示出了部分单元格N57的间接引用单元格，第三次单击“追踪引用单元格”按钮，单元格N57的全部直接和间接引用单元格都被显示出来；
与引用单元格类似的是从属单元格，二者正好相反。
例如，如果单元格D10中的公式为“=B5”，那么单元格D10就是单元格B5的从属单元格。
使用同样的方法，在Excel中也可以追踪某个单元格的从属单元格。
下图为追踪单元格H5的从属单元格的完成效果。
任务1-10查找表格中不一致的公式引用
在工作表“ABC电脑销售统计”中，追踪不一致公式的所有公式引用。
E01-10.xlsx
E01-10-R.xlsx
当某个单元格中的公式与其相邻单元格中的公式的模式不匹配时，Excel会将这个单元做出错误标记。
例如，要使C列的值为A列中的数值乘以B列中的数值，则单元格C1中的公式为“=A1*B1”,单元格C2中的公式为“=A2*B2”，单元格C3中的公式为“=A3*B3”，以此类推。
如果在单元格C4中的公式为“=A4*B2”，则Excel就会将其识别为不一致的公式，因为要继续之前模式，公式应该是“=A3*B4”。
Excel提供了“错误检查”工具，可以帮助使用者快速找到工作表中存在不一致公式的单元格。
但需要注意的是，不一致的公式并不一定意味着该公式必然是错误的。
如果公式确实是错误的，使单元格引用保持一致通常会解决问题。
1.单击“公式”选项卡/“错误检查”按钮；
2.在打开的“错误检查”对话框中，会显示出找到的第一个公式不一致的单元格，继续单击“下一个”按钮；
3.在提示完成查找的对话框中单击“确定”按钮，此时被找到的公式不一致的单元格N13处于选中状态；
4.单击“公式”选项卡/“追踪引用单元格”按钮，可以看到通过箭头的形式指示出了单元格N13的所有直接引用的单元格；
5.再次单击“追踪引用单元格”按钮，可以看到单元格N13的全部直接和间接引用单元格都被指示了出来；
任务1-11应用公式求值工具更正公式错误
在工作表“课酬统计”中，使用“公式求值”工具，更正单元格G5中的错误。
E01-11.xlsx
E01-11-R.xlsx
在Excel中，对于比较复杂的计算公式，比如包含多层嵌套函数的公式，当计算结果为错误值时，如何快速检查计算公式的错误呢？
Excel所提供的“公式求值”工具，可以帮助使用者检查公式每一步的计算结果，从而找出错误所在。
需要注意的是，公式求值工具仅仅通过分步计算复杂公式，来帮助找到错误所在，其本身不能自动更正错误。
1.选定单元格“G5”；
2.单击“公式”选项卡/“公式求值”按钮；
3.在打开的“公式求值”对话框中，在上方的文本框中可以看到单元格G5的公式，反复单击“求值”按钮，可以看到分步运算的结果；
4.在出现“#VALUE!
”时，仔细观察该部分的有问题的公式，可以发现是单元格引用出现了错误，将F3单元格误引用为了F4单元格，观察完毕后，继续反复单击“求值”按钮；
5.在全部公式运算完成后，单击“关闭”按钮，结束公式求值；
6.在编辑栏，将G5单元格的公式中的“$F$4”更改为“$F$3”，然后按Enter键；
（凡有$符号的为绝对引用，没有$符号的为相对引用）
单元2应用Excel分析和展示数据
任务2-1合并多个区域中的数据
将工作簿中名称为“_2009年”、“_2010年”和“_2011年”的区域合并到新工作表，并对其求和，起始单元格为A1，在首行和最左列显示标签，并将新工作表命名为“三年汇总”。
E02-01.xlsx
E02-01-R.xlsx
要汇总单独工作表中数据的结果，可将各个单独工作表中的数据合并到一个工作表（主工作表）。
例如，每个地区分支机构，都有一张计算收支数据的工作表，则可以使用数据合并功能将这些数据合并到一张汇总的主工作表上。
这张主工作表可包含整个企业的销售总额和平均值等指标。
需要注意的是，要进行合并计算的多个数据区域中的数据应当使用相同的行标签和列标签，这样才能才能得到正确的结果。
本任务重要求进行合并的是3个命名的单元格区域，这3个区域中的数据以相同的顺序排列，并有着相同行标签和列标签。
为了使Excel中的公式更加容易维护和理解，可以为某个单元格区域、函数和常量定义名称，定义后的名称可以如同某个数值一样，参加计算。
1.单击“插入工作表”按钮，建立新工作表“Sheet1”；
2.单击“数据”选项卡/“合并计算”按钮；
3.在打开的“合并计算”对话框中，在“引用位置”文本框输入“_2009年”；
4.单击“添加”按钮；
5.继续在“引用位置”文本框输入“_2010年”；
6.单击“添加”按钮；
7.继续在“引用位置”文本框输入“_2011年”；
8.单击“添加”按钮；
9.选中“首行”和“最左列”复选框；
10.单击“确定”按钮，完成合并；
11.双击“Sheet1”工作表标签，使其处于编辑状态；
12.将“Sheet1”替换为“三年汇总”，按Enter键。
单击“公式”选项卡/“名称管理器”按钮，会打开“名称管理器”对话框，在其中可以看到本任务中合并计算所引用的3个名称，以及每个名称所包含的的单元格区域范围。
单击对话框中的“新建”按钮，可以建立新的名称；
单击“编辑”按钮可以修改已经存在的名称；
单击“删除”按钮，可以删除名称。
任务2-2创建方案模拟分析数据
创建并显示名为“发展预测”的方案，通过该方案可以将“去年销售金额（元）”的值更改为“300000”。
E02-02.xlsx
E02-02-R.xlsx
预测未来值是决策制定过程的重要组成部分。
有效的方法之一是规划多组值以查看它们对结果的影响。
Excel提供的“方案管理器”工具可以轻松的达成此目的，该工具的基本思想是在工作表中自动替换可变的参数，例如银行的利率，查看结果（例如利息收入）的变化情况。
方案管理器可以帮助使用者分析的典型问题包含：
单位成本发生变化后如何影响净利润？
气温的升高会导致冰川的融化程度如何变化？
如果利率降低，还贷情况如何变化？
使用者可以在工作表中创建不同的方案并加以保存，然后切换方案以查看不同的结果。
还可以创建摘要来比较各种不同方案的结果。
1.单击“数据”选项卡/“模拟分析”下拉按钮；
2.在下拉菜单中单击“方案管理器”；
3.在打开的“方案管理器”对话框中，单击“添加”按钮；
4.在打开的“添加方案”对话框的“方案名”文本框中输入“发展预测”；
5.在“可变单元格”文本框中输入“B3”；
6.单击“确定”按钮，此时会打开“方案变量值”对话框；
7.在“方案变量值”对话框的“$B$3”文本框中输入“300000”；
8.单击“确定”按钮；
9.回到“方案管理器”对话框后，选中刚刚建立的方案“发展预测”（在只有一个方案的情况下，该方案会默认被选中）；
10.单击“显示”按钮；
11.单击“关闭”按钮；
如果在方案管理器中建立了多个方案，可以通过单击“摘要”按钮，创建方案摘要来比较各个方案之间的差别，具体操作方法和完成效果如下图所示，单击“数据”选项卡/“模拟分析”下拉按钮，在下拉菜单中单击“方案管理器”，此时会打开“方案管理器”对话框，单击“摘要”按钮，会在新的工作表中建立包含所有方案的方案摘要。
任务2-3应用数据透视表分类汇总数据
在新工作表中创建数据透视表，该数据透视表的行标签为“产品”，列标签为“发货城市”，最大值项为“订单金额”。
E02-03.xlsx
E02-03-R.xlsx
使用数据透视表可以高效地分类、汇总和分析大量的数据。
数据透视表是工作中进行决策分析的有力工具。
需要注意的是，创建数据透视表的基础是规范的源数据，源数据应当采取列表格式，即列标签应位于第一行，后续行中的每个单元格都应包含与其列标题相对应的数据，且源数据中不得出现任何空行或空列。
建立后的数据透视表，会将原来数据源中列标签作为新建立的报表的行标签和列标签，并加以分类和汇总。
1.选定工作表“10月订单统计”中的数据区域的任意一个单元格；
2.单击“插入”选项卡/“数据透视表”下拉按钮；
3.在下拉菜单中单击“数据透视表”；
4.在打开的“创建数据透视表”对话框中，确认“表/区域”文本框中所选择的单元格范围为“10月订单统计'
!
$A$1:
$F$126”；
5.确认数据透视表存放的位置为“新工作表”；
7.在打开的“数据透视表字段列表”任务窗格中，将“选择要添加到报表的字段”复选框列表中的“产品”字段拖动到下方的“行标签”区域；
8.用同样的方法，将“发货城市”字段拖动到“列标签”区域；
9.再将“订单金额”字段拖动到“数值”区域；
10.单击“数值”文本框中的“求和项：
订单金额”；
11.在向上开启的菜单中，单击“值字段设置”；
12.在打开的“值字段设置”对话框中，在“值汇总方式”选项卡的“计算类型”列表框中，选择“最大值”；
13.单击“确定”按钮；
数据透视表创建完成后，可以在“数据透视表工具：
设计”选项卡中，对其进行进一步修饰，例如更改报表的布局和样式，显示或者取消分类汇总，以及总计行和列。
任务2-4应用切片器筛选数据
在工作表“销售汇总”中，插入切片器，以便数据透视表显示“发货城市”和“订单编号”。
E02-04.xlsx
E02-04-R.xlsx
在
升级会员 