用Excel制作的表格可用来完成复杂的数据分析，在表格中公式和数组起着举足轻重的作用。本章主要介绍公式、数据及引用操作技巧相关的知识，让用户了解与掌握公式和数据的更多内容，从而更加熟练地使用Excel。
在使用数组公式时，有可能返回的是一个结果，也有可能返回的是一个集合。
STEP01：打开“人员统计.xlsx”工作簿，选中A1:D4单元格区域，输入公式“={“编号”,”姓名”,”性别”,”年龄”;”001″,”张三”,”男”,”22″;”002″,”张五”,”男”,”24″;”004″,”丁一”,”女”,”23″}”，如果按“Ctrl+Enter”组合键返回，会显示如图10-27所示的结果。
STEP02：如果按“Ctrl+Shift+Enter”组合键则会返回一组集合，结果如图10-28所示。
图10-27 返回结果
图10-28 返回一组集合
在工作表中经常可以看到许多在头尾带有“{}”的公式，有的用户把这些公式直接复制粘贴到单元格中，却没有出现正确的结果，这是为什么呢？其实这些都是数组公式，数组公式的输入方法是将公式输入后，不直接按“Enter”键，而是按“Ctrl+Shift+Enter”组合键，这时电脑自动为公式添加“{}”。
用户如果不小心按了“Enter”键，也不用紧张，用鼠标点一下编辑栏中的公式，再按“Ctrl+Shift+Enter”组合键即可。
数组公式是相对于普通公式而言的，普通公式只占用一个单元格，且返回一个结果，而数组公式则可以占用一个单元格也可以占用多个单元格，它对一组数或多组数进行计算，并返回一个或多个结果。
数组公式用一对大括号“{}”来括住，以区别普通公式，且以按“Ctrl+Shift+Enter”组合键结束。
数组公式主要用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。数组公式最大的特点就是可以执行多重计算，它返回的是一组数据结果。数组公式最大的特征就是所引用的参数是数组参数，包括区域数组和常量数组。区域数组是一个矩形的单元格区域，如$A$1:$D$5；常量数组是一组给定的常量，例如{1,2,3}、{1;2;3}或{1,2,3;1,2,3}。
数组公式中的参数必须为“矩形”，如{1,2,3;1,2}就无法引用了。输入后同时按“Ctrl+Shift+Enter”组合键，数组公式的外面会自动加上大括号{}予以区分。有的时候，看上去是一般应用的公式也应该属于数组公式，只是它所引用的是数组常量。对于参数为常量数组的公式，则在参数外有大括号{}，在公式外则没有，输入时也不必按“Ctrl+Shift+Enter”组合键。
如果不希望工作表中的公式被其他用户看到或者修改，可以对其进行保护与隐藏。下面是保护和隐藏工作表中的函数公式的具体操作步骤。
STEP01：打开“总收入统计.xlsx”工作簿，在工作表页面按“F5”键打开如图10-18所示的“定位”对话框。在对话框中单击“定位”条件按钮，打开“定位条件”对话框。
STEP02：打开“定位条件”对话框后，在“选择”列表框中单击选择“公式”单选按钮，然后单击“确定”按钮返回工作表，如图10-19所示。此时，工作表中会自动选择所有包含公式的单元格。
图10-18 “定位”对话框
图10-19 设置定位条件
STEP03：在选择的单元格处单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，如图10-20所示。
STEP04：随后会打开“设置单元格格式”对话框，切换至“保护”选项卡，依次勾选“锁定”和“隐藏”复选框，然后单击“确定”按钮返回工作表，如图10-21所示。
图10-20 设置单元格保护属性
图10-21 设置保护选项
STEP05：如图10-22所示，切换至“审阅”选项卡，在“保护”组中单击“保护工作表”按钮，会打开如图10-23所示的“保护工作表”对话框。在“取消工作表保护时使用的密码”文本框中输入“123456”，然后单击“确定”按钮。
STEP06：此时，会弹出“确认密码”对话框，在“重新输入密码”文本框中再次输入密码“123456”，然后单击“确定”按钮即可完成保护设置，如图10-24所示。
STEP07：若用户想要修改工作表中被保护的公式数据，会弹出如图10-25所示的对话框。
图10-22 单击“保护工作表”按钮
图10-23 设置密码
图10-24 确认密码
图10-25 提示框
STEP08：如果用户想要取消对工作表的保护，在主页将功能区切换至“审阅”选项卡，单击“保护”组中的“撤销工作表保护”按钮，打开如图10-26所示的“撤销工作表保护”对话框。然后在“密码”文本框中输入“123456”，单击“确定”按钮即可。
图10-26 撤销工作表保护
在函数的实际使用过程中，并非总是需要把一个函数的所有参数都写完整才可以计算，可以根据需要对参数进行省略和简化，以达到缩短公式长度或者减少计算步骤的目的。本节将具体讲解如何省略、简写及简化函数参数。
函数的帮助文件会将其各个参数表达的意思和要求罗列出来，仔细看看就会发现，有很多参数的描述中包括“忽略”“省略”“默认”等词，而且会注明，如果省略该参数，则表示默认该参数代表某个值。参数的省略是指该参数连同该参数存在所需的逗号间隔都不出现在函数中。
例：判断B2是否与A2的值相等，如果是则返回TRUE，否则返回FALSE。
=if（b2=a2,true,false）
可以省略为：
=if（b2=a2,true）
部分函数中的参数为TRUE或者FALSE，比如HLOOKUP函数的参数range_lookup。当为其指定为FALSE的时候，可以用0来替代。甚至连0也不写，而只是用逗号占据参数位置。下面3个公式是等价的：
= VLOOKUP(A1,B1:C10,2,FALSE) = VLOOKUP(A1,B1:C10,2,0) = VLOOKUP(A1,B1:C10,2,)
此外，有些针对数值的逻辑判断，可利用“0=false”和“非0数值=true”的规则来简化。比如，在已知A1单元格的数据只可能是数值的前提下，可以将公式=if(A1<>0，B1/A1,””)简化为=if(A1,B1/A1,””)。
在使用函数的过程中，经常会遇到一些不可预知的错误，这些不同类型的错误，对于普通用户来说，往往是不容易理解的，而且不容易掌握。本节就Excel中错误的类型以及查错与监视功能展开综合论述。
在使用Excel公式进行计算的时候，可能会因为某种原因而无法得到正确结果，从而返回一个错误值。表10-1列出了常见的错误值及其含义。
表10-1 常见Excel公式错误值说明
当公式的结果返回错误值的时候，可以使用Excel的错误检查工具，快速查找错误原因。
在工作表中切换至“文件”选项卡，在左侧导航栏中单击“选项”标签，打开“Excel选项”对话框。单击“公式”标签，在对应的右侧窗格中向下滑动滑块至“错误检查”列表框下，勾选“允许后台错误检查”复选框，并设置使用“绿色”标识错误，然后单击“确定”按钮完成设置，如图10-14所示。
如此，当单元格内的公式出现错误的时候，单元格左上角会自动出现一个绿色小三角形，即Excel的智能按钮标记，如图10-15所示。
图10-14 设置“错误检查”选项
图10-15 计算错误的单元格中出现智能标记
选定包含错误的单元格，单击出现的下三角按钮，会显示如图10-16所示的下拉列表。菜单中包含错误的类型，关于此错误的帮助链接，显示计算步骤，忽略错误，以及在公式编辑栏中编辑等选项，以便用户选择下一步操作。
如果用户创建了链接到其他工作簿的数据的电子表格，可以利用监视窗口随时查看工作表、单元格和公式函数在改动的时候是如何影响当前数据的。
单击“公式”选项卡下的“监视窗口”按钮，打开如图10-17所示的“监视窗口”对话框，通过它可以观察单元格及其中的公式。该对话框可以监视单元格的下列属性：所属工作簿、所属工作表、名称、单元格、值及公式。每个单元格只能有一个监视窗口。
图10-16 错误检查智能标记选项
图10-17 监视窗口
可以先选择工作表上的一个或多个包含公式的单元格，然后单击“监视窗口”对话框中的“添加监视”按钮，即可监视所选的单元格。监视窗口可以移动并改变窗口边界来获取最佳视图。
利用函数工具提示，可以轻松快速掌握函数的使用方法。函数工具提示主要包括以下几种操作。
STEP01：切换至“文件”选项卡，在左侧导航栏中单击“选项”标签，打开“Excel选项”对话框，如图10-11所示。
STEP02：单击“公式”标签，在对应的右侧窗格中对更改与公式计算、性能和错误处理相关的选项进行相关设置。这里在“计算选项”列表框下选择“自动重算”单选按钮，在“使用公式”列表框下取消勾选“R1C1引用样式”复选框，在“错误检查”列表框下勾选“允许后台错误检查”复选框，并设置使用“绿色”标识错误，然后在“错误检查规则”列表框下取消勾选“引用空单元格的公式”复选框。完成设置后，单击“确定”按钮即可返回工作表，如图10-12所示。
图10-11 单击“选项”标签
图10-12 设置公式属性
在单元格中输入一个函数公式的时候，按“Ctrl+Shift+A”组合键可以得到包含该函数完整语法的公式。例如输入“=IF”，然后按“Ctrl+Shift+A”组合键，则可以在单元格中得到如图10-13所示结果。
图10-13 利用组合键查询语法
Excel内置函数多数都有相应的帮助文件，单击“插入函数”对话框或者“函数参数”对话框左下角的“有关该函数的帮助”的链接，或者单击“函数提示工具”左边的函数名称，都可以调出相关函数的帮助窗口。
输入与编辑函数公式的时候，有许多技巧。下面分别进行详细介绍。
一、使用工具栏按钮输入函数
许多读者接触Excel公式计算都是从求和开始的，所以对功能区上的“自动求和”按钮应该不会陌生。在工作表页面切换至“公式”选项卡，在“函数库”组中单击“自动求和”下三角按钮将出现求和、平均值、计数、最大值、最小值、其他函数6个选项（默认为求和），如图10-7所示。选择其中一项，就可以在单元格中快捷地插入相对应的常用函数。
二、使用插入函数向导
插入函数向导是一个交互式输入函数的对话框，选中任意单元格，按“Shift+F3”组合键或者直接单击“公式”选项卡下的“插入函数”按钮，都可以打开如图10-8所示的“插入函数”对话框。
如图10-9所示，如果对函数所属类别不是很熟悉，可以在此对话框的“搜索函数”文本框里输入简单描述来寻找合适的函数。比如，在文本框中输入“返回两数相除的余数”，然后单击“转到”按钮，则Excel会在“选择函数”列表框中“推荐”供用户选择的函数，如MOD函数。
如果知道所需函数的类别，可以先在“或选择类别”下拉列表中选择分类，然后从“选择函数”列表框中选择函数。当类别中的函数数量较多的时候，可以移动滚动条或者输入函数开头字母来快速定位函数。
图10-7 函数选项
图10-8 插入函数向导
选定函数后，在“插入函数”对话框中单击“确定”按钮，Excel会将函数写入编辑栏中，同时会打开“函数参数”对话框，利用此对话框，用户可以方便地输入函数所需的各项参数，每个参数框右边会显示该参数的当前值。对话框下方有关于所选函数的一些简单描述文字，以及对各个参数的相关说明，如图10-10所示。
图10-9 使用“搜索函数”文本框
图10-10 函数参数对话框
三、手工输入函数
熟悉函数的用户可以直接在单元格中输入函数公式。输入函数公式的方法与输入其他数据没有差别，只要保证输入的内容符合函数公式的结构即可。
四、公式的编辑
当需要修改公式的时候，可以在编辑栏中移动光标到相应的地方直接修改，或者单击“公式”选项卡下的“插入函数”按钮，在打开的“函数参数”对话框中进行修改。
在移动公式时，公式内单元格引用不会更改。当复制公式时，单元格引用将根据所引用类型而变化。
以“总收入统计.xlsx”工作簿中的数据为例，移动公式的具体操作步骤如下。
STEP01：选择包含公式的单元格，这里选择D2单元格，单击鼠标右键，在弹出的隐藏菜单中选择“剪切”选项，如图10-1所示。
STEP02：在工作表中选择目标放置位置，如E2单元格，单击鼠标右键，在弹出的隐藏菜单中选择“粘贴”选项，如图10-2所示。移动公式后的效果如图10-3所示。
图10-1 剪切公式
图10-2 粘贴公式
复制公式的具体操作步骤如下。
STEP01：选择包含公式的单元格，这里选择D3单元格，单击鼠标右键，在弹出的隐藏菜单中选择“复制”选项，如图10-4所示。
图10-3 移动公式效果
图10-4 复制公式
STEP02：在工作表中选择目标放置位置，如E3单元格，单击鼠标右键，在弹出的隐藏菜单中选择“粘贴选项”列表下的“粘贴公式”选项，如图10-5所示。复制公式后的效果如图10-6所示。
图10-5 “粘贴公式”选项
图10-6 复制公式效果
当单元格引用无效时，会出现此错误。
可能的原因和解决方法如下。
1）可能删除了其他公式所引用的单元格，或者可能将单元格粘贴到其他公式所引用的其他单元格上。
解决方法：如果在Excel中启用了错误检查，则单击显示在错误的单元格旁边的按钮，并单击“显示计算步骤”（如果显示），然后单击适合所用数据的解决方案。
2）可能存在指向当前未运行的程序的对象链接和嵌入（OLE）链接。
解决方法：更改公式，或者在删除或粘贴单元格之后立即单击快速访问工具栏上的“撤消”按钮以恢复工作表中的单元格。
3）可能链接到了不可用的动态数据交换（DDE）主题（客户端/服务器应用程序的服务器部分中的一组或一类数据），如“系统”。
解决方法：启动对象链接和嵌入（OLE）链接调用的程序。使用正确的动态数据交换（DDE）主题。
4）工作簿中可能有个宏在工作表中输入了返回值为“#REF！”错误的函数。
解决方法：检查函数以确定是否引用了无效的单元格或单元格区域。例如，如果宏在工作表中输入的函数引用函数上面的单元格，而含有该函数的单元格位于第1行中，这时函数将返回“#REF!”，因为第1行上面再没有单元格了。
如果公式无法正确计算结果，Excel将会显示错误值，例如“#####、#DIV/0！、#N/A、#NAME？、#NULL！、#NUM！、#REF！和#VALUE！”，每种错误类型都有不同的原因和不同的解决方法。
如果公式所包含的单元格具有不同的数据类型，则Microsoft Excel将显示“#VALUE！”错误。如果启用了错误检查且将鼠标指针定位在错误指示器上，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。
可能的原因和解决方法如下。
1）公式中所含的一个或多个单元格包含文本，并且公式使用标准算术运算符（+、-、*和/）对这些单元格执行数学运算。例如，公式=A1+B1（其中A1包含字符串“happy”，而B1包含数字1314）将返回“#VALUE！”错误。
解决方法：不要使用算术运算符，而是使用函数（例如SUM、PRODUCT或QUOTIENT）对可能包含文本的单元格执行算术运算，并避免在函数中使用算术运算符，而使用逗号来分隔参数。
2）使用了数学函数（例如SUM、PRODUCT或QUOTIENT）的公式包含的参数是文本字符串，而不是数字。例如，公式PRODUCT(3,”happy”)将返回“#VALUE!”错误，因为PRODUCT函数要求使用数字作为参数。
解决方法：确保数学函数（例如SUM、PRODUCT或QUOTIENT）中没有直接使用文本作为参数。如果公式使用了某个函数，而该函数引用的单元格包含文本，则会忽略该单元格且不会显示错误。
3）工作簿使用了数据连接，而该连接不可用。
解决方法：如果工作簿使用了数据连接，执行必要步骤以恢复该数据连接，或者，如果可能，可以考虑导入数据。