1.2 公式基础概念
本节从初学者对公式容易产生的疑问入手，阐述公式、函数、数据类型、运算符、引用、单元格格式等基本概念，引导读者掌握构建正确公式的方法，了解Excel计算的相关规范和限制，避免出现计算错误等意外。
疑难11 为什么此“公式”不能根据圆的直径求面积
如图1-25所示，为什么在Excel中插入以下“公式”后不能自动得出计算结果：
■ 图1-25 求已知直径的圆的面积
是否可以根据在单元格中输入的圆的直径（比如86.6cm），用公式求出这个圆的面积（m2）？要求计算结果保留3位小数。
→ 解决方案
重新编制公式，求算圆的面积。
↙ 操作方法
在B2单元格输入直径的值86.6，在B3单元格中输入以下公式：
=IF(B2="","",ROUND(PI()＊(B2/100)^2/4,3))
可得到计算结果为0.589m2，当B2单元格中未输入数据时，面积显示为空文本。
↙ 知识扩展
※ 什么是公式 ※
本例提问者用了Excel 2010版“插入”选项卡中“符号”组的“公式”，是编辑和排版数学公式的一项功能，仅仅作为一个文本框对象插入到Excel中。本书所研究的“公式”，是指以“=”号开始，通过使用运算符将数据、函数等元素按一定顺序连接在一起，从而实现对工作表中的数值执行计算的等式。
※ 什么是函数 ※
简而言之，函数就是预先定义的公式。例如SUM（A1:B4）可以执行与下面公式相同的计算效果：
=A1+A2+A3+A4+B1+B2+B2+B3+B4
使用函数不仅可以简化公式，而且具有仅用运算符连接的一般公式所不能代替的功能。例如查找引用、逻辑判断等。函数是由函数的名称、左括号、以半角逗号相隔的参数和右括号组成，有的函数也可以不使用参数，例如PI、NOW、RAND等。函数的参数位置是固定的，如图1-26所示，IF函数的3个参数从左到右依次排列，不能跳过中间任何一个参数而直接使用右边的参数。
■ 图1-26 公式结构图
※ 公式的组成结构 ※
公式的组成要素为等号“=”、运算符和常量、单元格引用、函数、名称等。如图1-26所示，本例中的公式使用了IF、ROUND和PI 3个函数，运算符=、＊、/、^，以及对B2单元格的引用和常量""、100、2、4、3等。其中，ROUND函数是作为IF函数的一个参数使用的，称为嵌套函数；同理，PI函数嵌套于IF和ROUND函数中，称为二级嵌套函数。
疑难12 为什么IF函数只用两个参数也能计算
如图1-27所示，在B列使用公式对A列数据进行筛选，如果A列的数据是正数则返回其本身，如果是负数或0则返回0。在B3中输入下面公式并向下复制后，为什么当A列数据为负数或0时公式返回的是FALSE，如何让其返回0？
■ 图1-27 IF函数第3参数的省略和简写
=IF(A3>0,A3)
→ 解决方案
理解省略的函数参数默认值，正确设置参数值。
↙ 操作方法
在C3单元格中输入公式1或公式2，并向下复制：
公式1 =IF(A3>0,A3,0)
公式2 =IF(A3>0,A3,)
※ 必须参数与可选参数 ※
↙ 原理分析
IF函数的语法：
IF(logical_test, value_if_true, [value_if_false])
其中，参数logical_test与value_if_true是必须参数，value_if_false是可选参数。本例中，原公式“=IF（A3>0,A3）”仅使用了2个参数，当省略第3参数时，相当于使用默认值FALSE作为第3参数，因而当条件不满足时则返回FALSE。在语法中，可选参数用方括号包含起来。
↙ 知识扩展
※ 省略参数与省略参数的值 ※
本例中，原公式IF函数仅使用两个参数，这种用法称为“省略第3参数”；而公式3中IF函数的第2参数后面有一个逗号，这种用法称为“省略了第3参数的值”。
只有函数的参数为可选参数时，才可以将整个参数连同其前面的逗号一同省略。如果一个函数有多个可选参数，可以从右往左依次省略。常用函数参数省略的等同用法如表1-2所示。
表1-2 常用函数省略参数的等同用法
一般可用于代替参数值的有逻辑值FALSE、数值0或者空文本等，省略参数的值的示例表如表1-3所示。
表1-3 省略参数的值的示例表
疑难13 为什么公式=A3×B3返回#NAME?错误
如图1-28所示，想在C3单元格中用公式计算得到A3和B3单元格的乘积，为什么输入公式“=A3×B3”却返回#NAME?错误？
■ 图1-28 返回#NAME？错误
→ 解决方案
在公式中使用正确的运算符。
↙ 操作方法
在C3单元格中输入以下公式：
=A3＊B3
↙ 知识扩展
※ 公式中运算符的类型及含义 ※
Excel中公式使用的运算符与数学公式计算有所不同，如本例中的乘法运算应使用＊（星号）而不是×（乘号）。根据使用功能，Excel中的运算符可分为4类：算术运算、比较运算、文本连接和引用运算，如表1-4所示。
表1-4 运算符的类型及含义
疑难14 为什么开奖号末位判断总是“大”
如图1-29所示，B列用RIGHT函数提取排列三开奖号码的末位号码，为什么在C3单元格中输入公式“=IF(B3>4,"大","小")”，返回的结果都是“大”，是公式有问题吗？
■ 图1-29 为什么开奖号码末位判断总是大
→ 解决方案
用公式将文本形式的数字转为数值后再进行比较，即可得出正确结果。
↙ 操作方法
在C3单元格输入以下公式并向下复制：
=IF(B3-4>0,"大","小")
↙ 原理分析
B列中使用RIGHT函数得到的数字为文本形式，因文本数据在排序上比数值靠后，所以判别文本是否大于数字时都会返回TRUE。使用B3-4将文本形式的数字参与到算术运算后，可转为数值，并与零比较，结果就是该数字与4的大小比较。
↙ 知识扩展
※ Excel的数据类型 ※
在Excel中，数据可分为文本、数值、逻辑值、错误值等几种类型。文本用一对半角引号包含来表示，例如"exceltip.net"、"中国"等都是文本；数值是由负数、零和正数组成的，例如-34、87；逻辑值只有TRUE和FALSE两个；错误值有#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!和#NULL! 7种类型。
※ 数据排列顺序的规则 ※
在单元格中分别输入各种数据，单击“数据”选项卡进行“升序”排序时，数据按下面顺序排列：
…、-2、-1、0、1、2、…、A-Z、FALSE、TRUE
即：在排序时，数值小于文本，文本小于逻辑值，错误值不参与排序。
※ 文本形式数字如何转换为数值 ※
文本形式数字产生的原因主要有三种，即：单元格格式为“文本”、在输入数据前加了（' 撇号）、文本函数或文本运算符&计算结果。在公式中，要将其转换为数值，可以使用VALUE函数进行转换，当文本形式数字直接参与加、减、乘、除以及%、乘幂算术运算时也会被转为数值。例如以下7个公式均可将A1中的文本形式数字转为数值：
公式1 =A1+0
公式2 =A1-0
公式3 =A1＊1
公式4 =A1/1
公式5 =0--A1
公式6 =--A1
公式7 =VALUE(A1)
其中，公式6实质是公式5的简化（即0减去负A1），是输入最为方便的方法。
疑难15 为什么数字与“空”单元格相加出错
如图1-30所示，在计算退休年龄时，C列根据B列的性别判断延迟年数，女性职员不延迟显示为“空”，公式为“=IF(B3="男",5,"")”，为什么D5单元格=C5+50得到#VALUE！错误？
■ 图1-30 数字与“空”相加得到#VALUE!错误
→ 解决方案
了解空文本与空单元格的区别，使用N函数将空文本转换为0参与加法计算或使用SUM函数求和。
↙ 操作方法
在D3单元格输入公式1或公式2并向下复制：
公式1 =50+N(C3)
公式2 =SUM(C3,50)
↙ 原理分析
C5单元格得到的计算结果为""，即“空文本”而非“空单元格”，而D5单元格的公式需要使用数字相加，空文本没有转换为正确的数据类型，所以返回#VALUE!错误。
使用N函数，可以将文本转换为0，数值返回其本身的值；使用SUM函数，可以忽略文本求和因此可以得出正确结果。
其中，N函数语法请参阅：疑难200。
SUM函数语法为：
SUM(number1,[number2],...))
该函数最多可以有255个参数number，第2～255个参数为可选参数。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果任意参数为错误值或为不能转换为数字的文本，Excel将会显示错误。
↙ 知识扩展
※ 空文本与空单元格的区别 ※
文本在Excel中的表示方法是用一对半角双引号包含，空文本是指双引号""中间什么内容也没有的文本，其字符长度为0。在公式中，经常使用空文本作为一个计算结果以代替用户不希望出现的FALSE、0等结果。例如本案例中，不想显示女性职工延迟年数为0，用空文本""即可达到目的。
空单元格是指一个单元格中未曾输入任何数据，或者数据已被清空。
因为设置公式时，经常用类似A1=""来判断A1单元格是否含有数据，初学者容易混淆""与空单元格，事实上两者不是在什么情况下都画等号的。如表1-5所示，A5、A7单元格中输入=""来返回空文本，A3、A4、A6都是空单元格，在B列输入几个公式后，可以从返回的不同结果说明空文本与空单元格的区别。
此外，“空格”也是经常与空文本、空单元格混淆的一个概念，空格是指按下键盘上的【Space】键得到的具有字符长度的文本，比如" "由两个空格组成，字符长度为2。
※ 公式中的&""有什么作用 ※
如表1-5所示，空单元格在参与公式运算时，默认返回0，但与文本进行比较、合并运算时则视为空文本。利用这个特性，在较为复杂的查找引用公式中，引用一个空单元格并与空文本合并，返回空文本，以此屏蔽引用空单元格返回的0，从而减少条件判断而缩短公式。
表1-5 空文本与空单元格的区别
如图1-31所示，要求列出A列姓名为张三的对应的B列的订单号。
■ 图1-31 公式中&""的妙用
在D3单元格输入公式1并按【Ctrl+Shift+Enter】组合键结束，形成数组公式后向下复制：
=IF(ROW(1:1)>COUNTIF(A:A,"张三"),"",INDEX(B:B,SMALL(IF(A$3:A$10="张三
",ROW($3:$10)),ROW(1:1))))
或者用同样的方法输入公式2：
=INDEX(B:B,SMALL(IF(A$3:A$10="张三",ROW($3:$10),2^20),ROW(1:1)))&""
其中，公式2对不满足IF条件的返回2^20（即Excel 2010的最大行数1048576），因而当罗列完满足条件的记录之后，全部引用B列最后一行空单元格，并与空文本进行合并得到空文本，比公式1更简洁实用。
↘ 注意
使用&""后公式计算结果为文本。
疑难16 为什么两个时间相减得到长串#####号
如图1-32所示，A3单元格为晚班的上班时间，B3单元格为晚班的下班时间，为什么C3单元格中用公式=B3-A3求时间2减去时间1得到的结果是#填满单元格呢？
■ 图1-32 时间相减得到#号填满单元格
→ 解决方案
了解产生长串#号的原因，并使用正确的时间计算公式。
↙ 操作方法
在C2单元格输入以下公式：
=B3+IF(B3<A3,1,0) -A3
可得到正确结果。
↙ 原理分析
在Excel中，日期和时间是数值的特殊格式，可以直接进行加减运算，但是“负”的时间是没有意义的，如果时间差为负数，会返回#号填满单元格。
本例使用IF函数判断时间2早于时间1，就加上1表示时间2为第2天的时间。
↙ 知识扩展
※ 产生#####的原因分析及解决方法 ※
严格来说，#号填满单元格不是错误值，使用ISERROR、ERROR.TYPE函数不能判别为错误。一般有三种情况会导致此问题。
原因1 单元格列宽不足。
解决方法：可以通过调整单元格列宽、设置自动换行或缩小字体等方法解决。
原因2 返回的时间、日期为负数。
解决方法：可以设置单元格为其他格式，如果需要用日期、时间格式表示，则应该用较晚的时间、日期减去较早的时间、日期。
原因3 单元格格式中设置了条件，但输入数据未满足条件。例如设置单元格格式，自定义格式为“[=1]男；[=2]女”，然后输入了除1、2之外的数字。Excel 2003版本不会有此问题。
解决方法：自定义格式时使用完整代码。例如自定义格式代码设置为“[=1]男；[=2]女；G/通用格式”，则输入1、2之外的数字会返回通用格式。
疑难17 如何输入编号1-2、1/2以及比分1:3
编写图书时，经常用到以负号或斜杠连接的章节编号，例如第1章第2节表示为1-2或者1/2，但在Excel的单元格中这样输入后会自动变为日期。此外，输入球赛的比分1:2、1:3时，也会自动变为时间，有没有办法解决这个问题？
→ 解决方案
使用文本格式输入带负号、斜杠或冒号的数据。
↙ 操作方法
方法1 如图1-33所示，选择B列单元格，打开“设置单元格格式”对话框，在“数字”选项卡中选择“文本”格式，单击“确定”按钮，再输入上述数据。
■ 图1-33 设置单元格格式为文本
方法2 在输入上述数据时，先输入半角单引号（又称为撇号'），强制以文本格式输入数据。如图1-34所示，C3单元格在编辑栏显示为“'1-2”。
■ 图1-34 输入编号1-2、1/2及比分1:3
↙ 原理分析
※ 日期、时间数据的表示方法 ※
在Excel中，日期和时间是数值的一种特殊格式，默认使用1900日期系统，即数值1表示1900年1月1日，2010年1月1日的日期序列值为40179，表示从1900年1月1日起的第40179天。时间不足1天的小数部分，根据时间的进制，1天共分为24小时，因而1小时的数值大小为1/24。同理，1分钟的数值为1/1440，1秒的数值为1/86 400。
默认状态下，日期的格式一般用负号或斜杠将年月日依次相隔，时间则用冒号将时分秒依次相隔。例如：输入“1-1”～“1-31”、“2-1”～“2-28”等数据，Excel将默认识别为日期数据，表示系统日期当年的1月1日至1月31日、2月1日至2月28日等。输入“1:1”～“23:59”时，Excel将默认识别为时间数据。
此外，在中文系统中输入“1月1日”、“1时3分5秒”等规范的日期或时间时，也会自动识别为数值。
↙ 知识扩展
※ 打开“设置单元格格式”对话框的3种方法 ※
方法1 使用功能区的“对话框启动器”：单击“开始”选项卡，在“数字”组右下角单击“设置单元格格式”对话框启动器，如图1-35所示。
■ 图1-35 “设置单元格格式”对话框启动器
方法2 使用右键快捷菜单功能：用鼠标右键单击单元格区域，在弹出的快捷菜单中单击“设置单元格格式”命令，如图1-36所示。
■ 图1-36 在单元格中使用右键快捷菜单
方法3 使用快捷键：选择单元格区域后，按【Ctrl+1】组合键，即可打开“设置单元格格式”对话框。
疑难18 如何快速规范以小数点间隔的伪日期
在输入职员出生年月时，工作人员习惯用小数点间隔来表示日期，例如用“1987.7.25”表示1987年7月25日，但1987.7.25实际上无法直接作为日期数据参与计算，如何快速将其转换为规范的日期数据？
→ 解决方案1
使用查找替换法实现快速转换。
↙ 操作方法
※ 查找替换法规范“伪日期”数据 ※
步骤1 如图1-37所示，选择包含小数点间隔的“伪日期”数据的A列单元格区域；
■ 图1-37 查找替换法规范“伪日期”数据
步骤2 按【Ctrl+H】组合键，或者单击“开始”选项卡“编辑”组的“查找和选择”按钮，并在下拉菜单中选择“替换”命令，弹出“查找与替换”对话框；
步骤3 在“查找内容”编辑框中输入小数点“.”，在“替换为”编辑框中输入负号“-”或斜杠“/”，单击“全部替换”。
→ 解决方案2
使用“数据分列”功能转换“伪日期”数据。
↙ 操作方法
※ 数据分列法规范“伪日期”数据 ※
步骤1 如图1-38所示，选择包含小数点间隔的“伪日期”数据的A列单元格区域；
■ 图1-38 用数据分列法规范“伪日期”数据
步骤2 单击“数据”选项卡，在“数据工具”组中单击“分列”按钮，弹出“文本分列向导”对话框；
步骤3 单击“文本分列向导”对话框中“下一步”按钮两次，选择“列数据格式”中的“日期”选项，单击“完成”按钮。
↙ 知识扩展
※ 根据使用习惯改变默认日期与时间格式 ※
日期作为数值的一种特殊格式，有着非常广泛的计算用途，例如计算年龄、工龄、工作日等，在本书后续的章节将详细讲解。许多用户习惯使用小数点间隔表示日期，但本例中的“伪日期”只能被识别为文本数据，而无法直接用于日期的各种计算。
用户可以根据日常习惯，设置系统中的“时钟、语言和区域”选项，改变默认表示方式。操作方法如下：
步骤1 单击Windows系统“开始”→“控制面板”，选择“时钟、语言和区域”，如图1-39所示。
■ 图1-39 Windows控制面板
步骤2 单击“区域和语言”中的“更改日期、时间或数字格式”链接，如图1-40所示，可以设置默认的短日期、长日期和短时间、长时间的格式。
■ 图1-40 设置日期和时间的默认格式
步骤3 单击“区域和语言”对话框右下角的“其他设置”按钮，弹出“自定义格式”对话框，如图1-41中位置❶所示，可以将短日期更改为以小数点间隔的形式“yyyy.m.d”，这时再在工作表单元格中输入2010.1.1就会被识别为规范的日期格式。
■ 图1-41 修改短日期间隔符号和两位数字年份
※ Excel中两位数字的年份 ※
在Windows XP操作系统下，两位数字的年份使用2029短年份规则表示，即年份为00～29表示2000～2029年，年份30～99表示1930～1999年。例如，在Excel单元格中输入29-1-1，是表示2029年1月1日；输入30-1-1则表示1930年1月1日。
如图1-41的位置❷所示，Windows Vista和Windows 7对于两位数字表示的年份可以由用户设定，图中设置表示输入两位数字的年份在1930～2029之间，用户可以通过调节2029的值来改变范围，以便更适应用户需求。
疑难19 如何拯救小数表示的伪出生年月
出生年月是一个很常用的信息，在输入此类数据时，习惯使用小数点间隔的用户必须注意，例如“1978.10”、“1978.1”、“1978.01”等数据，因为只有一个小数点，不管是否设置了“控制面板”中的短日期格式，Excel都将其识别为数值数据。如果单元格格式为常规，则当输入“1978.10”时会自动变为“1978.1”，表示数值一千九百七十八点一。有的用户会“自作聪明”地把单元格格式小数位数增加到两位，以达到显示为“1978.10”的效果。如何才能拯救这样的伪日期数据？
→ 解决方案
多次使用“剪贴板”等操作技巧将其转换为规范日期。
↙ 操作方法
※ 用剪贴板规范含有两位小数的“伪日期”数据 ※
步骤1 如图1-42所示，选择包含“伪出生年月”的A2:A4单元格区域，单击“开始”选项卡→“复制”按钮→“剪贴板”组右下角的对话框启动器（斜向下45°箭头）→选择B2单元格，单击左侧“剪贴板”对话框中的数据，则在B2单元格得到换行显示的全部“伪出生年月”；
■ 图1-42 拯救“伪出生年月”步骤1
步骤2 使用“查找替换”功能，将B2单元格中的小数点替换为负号，双击激活B2单元格或在编辑栏中选择B2单元格全部数据，单击“开始”选项卡→“复制”按钮，如图1-43所示，在左侧的“剪贴板”对话框中得到“1978-10”等结果；
■ 图1-43 拯救“伪出生年月”步骤2
步骤3 选择C2单元格，单击左侧“剪贴板”中上一步复制的结果，如图1-44所示，在C2:C4得到规范的日期如“1978/10/1”等，自动将出生年月补充日期为该月1日。
■ 图1-44 拯救“伪出生年月”步骤3
↘ 注意
类似“1978.10”这种“伪出生年月”的本质是带有小数的数值，无法使用“数据分列”法进行转换，而使用“查找替换”法时，也不能区分10月和1月。因此处理较为麻烦，建议用户养成规范的日期输入习惯，避免使用带小数点的出生年月，或者在输入时在月份后面添加日期，例如输入“1978.10.1”等，则可以参考疑难19中的操作方法，快速实现日期规范化。
疑难20 如何实现输入数字1、2后自动将其显示为性别
如图1-45所示，有大量的职工信息需要输入，希望能够输入数字1表示男性、数字2表示女性来简化数据录入，并且当输入数字1、2之外的内容时提醒“输入有误”，该如何实现？
■ 图1-45 将输入的数字自动显示为性别
→ 解决方案
通过自定义单元格格式实现“条件”显示。
↙ 操作方法
※ 为单元格自定义数字格式 ※
步骤1 选择性别数据所在的C3:C10单元格区域，打开“设置单元格格式”对话框，如图1-46所示：
■ 图1-46 设置自定义格式
步骤2 在“数字”选项卡中，选择“分类”为“自定义”，并在右侧“类型”编辑框中输入以下自定义格式代码后单击“确定”按钮：
[=1]"男";[=2]"女";输入有误;输入有误
步骤3 在C3:C10单元格区域输入数字1、2，用以表示性别为男、女。
↙ 原理分析
本例通过设置自定义数字格式，当单元格的数据满足条件1（即等于1）时，显示为“男”，满足条件2（即等于2）时，显示为“女”，输入其他非负数字或者文本时，显示为“输入有误”。
↙ 知识扩展
※ Excel的数字格式种类 ※
如图1-47所示，Excel内置数字格式有常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本和特殊11个种类，用户还可以根据需要进行“自定义”设置。
■ 图1-47 数字格式代码中的颜色和条件
数值格式主要用于设置小数位数及千分位、负数的显示方式；货币格式主要用于设置货币符号、千分位的显示方式；会计专用格式是在货币格式基础上，增加了对齐货币符号和小数点的功能。设置百分比格式时，单元格中的数字将自动乘以100并以百分比格式显示。
日期与时间格式中，以＊（星号）开头的格式受“控制面板”中短日期、长日期和短时间等格式设置的影响。
分数格式一般可设置分母为1位、2位、3位数字以及常用分母格式，在输入分数时，整数部分与分数部分加入一个“空格”，输入不足1的分数需要先输入数字0，例如八分之三的输入方法为“0 3/8”，一又四分之三的输入方法为“1 3/4”。
科学记数法采用E+方式表示，例如“1.23E+02”表示1.23乘以10的2次方，即123。
中文版Excel 2010特殊格式中，包含常用的邮政编码、中文大写数字和中文小写数字三种格式。
※ 常用数字格式代码组成部分 ※
完整的数字格式可包含4个代码部分，各个部分用分号分隔，从左到右依次表示正数、负数、零值和文本的格式，如下所示：
<正数>;<负数>;<零>;<文本>
如果仅指定两个代码部分，则第1部分用于正数和零，第2部分用于负数；如果仅指定一个代码部分，则该部分将用于所有数字；如果要跳过某一代码部分，然后在其后面包含一个代码部分，则必须为要跳过的部分包含结束分号；如果要设置文本值的显示格式，则必须在第4部分设置需要显示的内容。
在数字格式中，可以使用方括号、比较运算符和数字结合作为指定条件，4个代码部分中最多可以设置两个条件，例如本例中“[=1]"男"；[=2]"女"；"输入有误"；"输入有误"”。
此外，数字格式还可以设置数字显示的颜色，例如自定义格式代码为
[蓝色]+0;[红色]-0
正数和0将显示为蓝色且带+号，负数显示为红色且带-号。如果自定义格式代码为
[>=60][蓝色]合格;[红色]不合格
则当单元格数值超过60时，显示蓝色的“合格”，否则显示为红色“不合格”。显示效果如图1-47所示。
疑难21 如何让单位为元的金额显示为万元
如图1-48所示，数据表中B列的销售金额以元为单位输入。为了显示简洁而又不影响计算的精度，能否将销售金额以万元为单位显示？
■ 图1-48 如何以万元为单位显示金额
→ 解决方案
通过设置自定义数字格式实现。
步骤1 在C3单元格输入以下公式并向下复制：
=B3
↙ 操作方法
步骤2 选择C3:C10单元格区域，打开“设置单元格格式”对话框，设置自定义格式代码为
0!.0,"万元"
或者
0\.0,"万元"
↙ 原理分析
本例中，千分位符号（逗号）的右侧没有_"0"_或者_"#"_、_"?"_用以显示数字，因此不显示千元以下的部分，再使用占位符!（叹号）或\（反斜杠）与句点结合，在千元前面显示句点本身而不是作为数值自身小数点使用，从而得到以“万元”为单位的显示效果。
↘ 注意
通过设置单元格的数字格式，仅仅是改变数字显示的效果，而单元格中数据的实质仍然是该数值本身。例如本例C3单元格显示为“135.3万元”，与在单元格中输入“135.3万元”不同的是，前者实质为数值“1353185”，可以参与计算，而后者为字符串，不能参与数值计算。
↙ 知识扩展
※ 数字格式代码及含义 ※
单元格格式代码的符号和字母有特定的含义和规定，具体如表1-6所示，用户可以根据需要设置自定义数字格式。在需要使用B、D、E、H、M等作为字母本身时，应在其前面加占位符\或!，避免与所代表的佛历年份、日期等信息冲突。
表1-6 单元格格式代码含义及示例
※ 日期相关格式代码 ※
※ 时间相关格式代码 ※
※ 数字格式代码中的日历和语言区域 ※
如图1-49所示，在设置日期格式时，在“区域设置（国家/地区）”编辑框中选择“泰语”，在下方的“日历类型”中选择“泰国佛历”后，选择“分类”类型为“自定义”，可以看到其格式代码为
■ 图1-49 选择日历类型
[$-1070000]d/m/yy;@
该代码的方括号中为一串以“$-”开头的字符串，用于指定数字显示的语言和日历类型等，一般由“2位数字外观代码+2位日历类型代码+4位区域设置代码”共3段十六进制字符组成，含义如下：
1.数字外观代码，用于指定单元格将以何种语言显示数字，其对应的含义如表1-7所示，例如使用1E、1F、20将分别显示为简体中文的小写、大写和全角阿拉伯数字，可以不指定该代码。
表1-7 数字外观格式代码及含义
2.日历类型代码，用于指定所使用的日历类型，其对应关系如表1-8所示。
表1-8 日历类型代码及含义
此外，仍有部分微软未公布代码，经测试：“04”为台湾日历，“08”为犹太日历，“0E”、“11”、“12”、“13”为阴历（可能因操作系统不同而存在差异）。
3.区域设置代码，用于将区域设置规范添加到数字格式后，在格式单元格中输入的数字将被使用适当的特有区域设置信息进行格式化，其对应关系如表1-9所示。
表1-9 语言代码及含义
续表
如果使用0作为其中一个代码段的值，Excel将使用“控制面板”中设置的系统设置。例如：在A1单元格中输入2009年12月17日，设置数字格式代码为
[$-1F130000]yyyy年m月d日
单元格将显示为“贰零零玖年壹拾贰月壹拾柒日”。代码中“1F”表示使用中文大写数字，“13”表示使用阴历，“0000”表示使用“控制面板”中的区域设置。此外，首个代码段前的数字0可以省略，例如代码“[$-0804]yyyy-m-d”可以写为“[$-804]yyyy-m-d”。
↘ 注意
阴历与农历都是大月30天、小月29天，但不同的是，阴历大、小月交替出现，农历则可能是连续两个大月或小月，其推算方法较为复杂。因而并不能直接使用阴历代替农历。
疑难22 如何让累计求和引用的区域随公式向下复制变化
如图1-50所示，从C4单元格开始需要将B列的每月收入进行累计，有没有办法让求和区域一直从B4单元格开始引用并随公式下拉复制自动变为B4:B5、B4:B6等，D4应缴税收的公式为“=C4*B2”，能否让B2单元格始终不变呢？
■ 图1-50 让B列的每月收入进行累计
→ 解决方案
根据需要使用相对、绝对或混合引用单元格的方式。
↙ 操作方法
步骤1 在C4单元格输入以下公式并向下复制：
=SUM(B$4:B4）
步骤2 在D4单元格输入以下公式并向下复制：
=C4＊B$2
↙ 原理分析
本例中，累计收入需要始终引用从B4单元格开始到公式所在行的B列的单元格，且公式仅向下复制，不需考虑列标的变化，因此使用了混合引用。
应缴税收需要固定引用B2单元格的税率，且公式仅向下复制，因此使用行绝对列相对引用；而对C列的引用则需要随着公式位置的不同而变化，因此使用相对引用。
↙ 知识扩展
※ 相对引用、绝对引用、混合引用 ※
复制公式可以在不同单元格实现相似的计算，公式中使用的单元格地址在复制公式时是否随着公式所在行、列位置的变化而自动改变，可以通过使用相对引用、绝对引用和混合引用方式来实现。
1.相对引用。
公式复制时单元格地址的行号、列号都发生变化，称为“相对引用”，如本例公式中的"B4"，当公式向下复制到第5行时，会变为"B5"；公式向右复制时，会变为"C4"。简言之，即“行列皆可变”。
2.绝对引用。
公式复制时，单元格地址的行号、列号都不发生变化，称为“绝对引用”。例如在任意单元格输入公式：
=$A$1
则不论公式向哪个方向复制，始终都不会发生变化。简言之，即“行列皆不变”。
3.混合引用。
公式复制时，单元格地址的行号或列号之一发生变化，称为“混合引用”。如本例公式中的"B$2"，为行绝对列相对引用，即复制时“行不变列可变”。如果使用行相对列绝对引用"$B2"，则复制时“行可变列不变”。
以上为A1引用样式中的表示方法。可以将“$”号理解为一把锁，放在行号或者列号前面就会将其锁定，从而不随复制公式的位置变化而发生变化。
在R1C1引用样式中，用方括号中的数字来表示单元格相对于公式所在的单元格偏移的距离，负数表示被引用单元格在当前单元格的左侧、上方。如图1-51所示，在R7C4单元格引用R3C2单元格（即A1引用样式中“在D7单元格引用B3单元格”的意思），相对引用的公式为
■ 图1-51 R1C1与A1引用样式中的相对引用
=R[-4]C[-2]
表示引用了从当前单元格向上第4行、向左第2列的位置。与A1引用样式的表示方法区别如表1-10所示。
表1-10 A1与R1C1引用样式中不同引用方式对照表
此外，R1C1引用样式还允许不标明行号或列标来表示相对引用。例如公式：
=R3C
表示始终引用公式所在列的第3行单元格，类似于A1引用样式中在A列输入公式：
=A$3
4.快速切换引用相对性。
在编辑栏中选中单元格地址，按【F4】键可以依次将单元格引用切换为绝对引用、行绝对列相对引用、行相对列绝对引用和相对引用，例如公式中的“A1”将依次变为“$A$1”、“A$1”、“$A1”、“A1”。
疑难23 不同表格相同表头能否只填一处
如图1-52所示，工程施工中经常要进行筛分法、甲种比重计法、移液管法等颗粒分析试验，这些试验表格有很多处内容都是一样的，比如项目名称、承包单位、监理单位等信息。能否做到多个重复表头只填一次？
■ 图1-52 多个重复表头只填一次
→ 解决方案
建立常用信息表，使用跨表引用实现表头一次填。
↙ 操作方法
步骤1 新建一个名为“常用信息”的工作表，输入各表可通用的内容，如图1-53所示。
■ 图1-53 通用表头信息
步骤2 在各分表需要填写信息的单元格输入公式引用“常用信息”表的内容，例如表头的建设项目名称为
=常用信息!C3
输入步骤为：输入“=”号，用鼠标单击“常用信息”工作表标签，并单击选中C2单元格，按【Enter】键结束。
↙ 知识扩展
※ 跨表引用单元格的表示方法 ※
当公式引用的单元格与公式本身不在同一个工作表中时，称为跨表引用单元格，其表示方法为：工作表名!单元格，例如引用当前工作簿中Sheet1表的B3单元格公式为
= Sheet1!B3
当公式引用其他工作簿上的单元格时，称为跨工作簿引用单元格，其表示方法为：[工作簿名]工作表名!单元格，例如引用工作簿3的Sheet1工作表的B3单元格公式为
=[工作簿3]Sheet1!B3
当公式引用了已关闭的工作簿上的单元格时，其表示方法为跨工作簿引用单元格前面加上工作簿所在的路径，例如引用D盘根目录下工作簿3的Sheet1工作表的B3单元格公式为
='D:\ [工作簿3.xlsx]Sheet1'!B3
通过对这3种表示方法的对比不难看出，表示单元格的方法与被引用单元格所处的位置有关，就像写信时“地址”的详细度变化一样，一般省内寄信只需写某市某区某街道，而跨省寄信则必须写明省份，国际邮递则需要写明国家。
↘ 注意
跨表引用完整的表示方法，应该是将感叹号之前的路径、工作簿名和工作表名信息用一对英文单引号包含起来，如
='Sheet1'!B3
如果跨表引用的工作表名称以数字开头、包含空格或以下字符，则单引号不能省略：$ % ` ~ ! @ # ^ & ( ) + - = , | " ; { }
疑难24 为什么身份证号码15位以后的数字都显示为0
如图1-54所示，在B列单元格输入身份证号码信息时，15位数字的身份证号码显示正常，但18位数字的身份证号码后面的3位数字都是0，这是为什么，有什么方法可以解决吗？
■ 图1-54 身份证号码15位之后的3位数字都显示为0
→ 解决方案
设置单元格数字格式为文本或者强制以文本格式输入。
↙ 操作方法
方法1 选择身份证号码所在单元格，打开“设置单元格格式”对话框，在“数字”选项卡中选择“文本”格式，单击“确定”按钮，然后再输入身份证号码。
方法2 在输入身份证号码时，首先输入半角单引号'（也称为“撇号”），强制以文本格式输入数字。
方法1可以先批量设置单元格数字格式后再输入，方法2需要每个单元格都先输入半角单引号，如果数据量大，建议使用方法1。
↘ 注意
Excel的计算精度仅为15位数字（含小数，即从数字左侧第一个不为0的数字算起，第16位数字及之后的数字均为0），如果先输入18位数字再设置为文本格式，则无法恢复后3位数字。
↙ 知识扩展
※ Excel的计算限制 ※
使用Excel进行计算时，公式允许的最大数字为±1.7976931348623158e+308，但数字的计算精度为15位数字，因此进行超长数字串输入和统计计算等操作时，需要考虑此特性并采取相应的措施。
如图1-54所示，本例中C列单元格是以文本形式存储的18位身份证号码，C5、C6单元格的号码仅后3位数字不同，但使用以下公式：
=COUNTIF(C:C,C5)
计算结果为2，这是因为COUNTIF函数默认将数字视为数值型的结果。使用以下公式计算可得到正确结果：
=SUMPRODUCT(--(C3:C6=C5))
此外，Excel 2010中的函数参数个数限制为255个，公式允许的嵌套层数为64个；在Excel 2003版中仅为30个参数、7层嵌套。其他计算限制如表1-11所示。
表1-11 Excel 2010计算限制
疑难25 为什么有时候汇总金额会比实际差1分钱
如图1-55所示，某公司工资表中，C3单元格公式为
■ 图1-55 汇总金额与实际差1分钱
=1700/30＊B3
即按固定工资1700元除以30天再乘以出勤天数计算，经常出现手工汇总每个员工工资总额与使用C11单元格中用SUM函数得到的和差1分钱，有什么办法让合计金额计算正确呢？
→ 解决方案1
设置计算精度，以显示精度为准。
↙ 操作方法
如图1-56所示，单击“文件”选项卡→“选项”→“高级”，在“计算此工作簿时”区域勾选“将精度设为所显示的精度”复选框，单击“确定”按钮两次。
■ 图1-56 将精度设为所显示的精度
↘ 注意
设置完毕后，该工作簿内所有的公式计算都将受到影响，按照“所见即所得”的模式计算。例如设置单元格数字格式为0位小数后，数据将以整数部分进行计算，需慎用。
→ 解决方案2
使用舍入函数确保计算精度。
↙ 操作方法
如图1-55所示，在D3单元格输入如下公式：
=ROUND(1700/30＊B3,2)
对每个人的计时工资按照“四舍五入”保留两位小数，则D11单元格用SUM函数求和可得到正确结果。
↙ 原理分析
本例中，由于使用除法运算产生了无限不循环小数，例如杨光辉的工资额为1700/30*13=736.666666666667，并非显示的736.67元，从而造成求和结果的差额。
↙ 知识扩展
※ 浮点运算导致意外计算误差 ※
由于Excel执行美国电气和电子工程师协会（IEEE）制定的754浮点算法规范，采用二进制数字存储，因而可能会导致意外的计算误差。
如图1-57所示，在借方、贷方试算平衡表中，B8、B9单元格分别使用=SUM（B3:B6）、=SUM （C3:C6）公式进行合计后显示的金额是相同的，但B10单元格输入以下公式后返回的结果为“借贷不等”：
■ 图1-57 浮点运算导致的误差
=IF(B8-B9=0,"","借贷不等")
如图1-58所示，对B10单元格进行“公式求值”可发现B8-B9的结果不为0，而是一个极小的数：-3.72529029846191E-09。
■ 图1-58 浮点运算产生一个极小的数
采取“将精度设为所显示的精度”或使用ROUND函数进行修正，可以解决浮点运算问题。
↘ 注意
部分函数如MOD、INT、TRUNC等，在计算过程中也可能产生浮点运算误差。
疑难26 为什么即使没有修改内容关闭表格时也会提示保存
如图1-59所示，打开报表后没有修改任何内容就关闭工作簿，为什么也会提示保存？是否数据被修改了？
■ 图1-59 关闭工作簿时提示保存
→ 解决方案
判别是否修改和理解函数“易失性”的特性。
↙ 操作方法
查看“快速访问”工具栏中“撤销”按钮，该按钮为灰色（即不可用状态），因此可以判别未对报表数据进行修改。如果要修改报表，可多次单击“撤销”按钮或单击该按钮右侧下拉菜单后一次选择“撤销”多步骤操作。
↙ 原理分析
※ 哪些函数具有易失性 ※
本例报表中的填报日期利用的TODAY函数具有一种特性，即无论何时在工作表的任意单元格中进行计算都会进行重新计算的特性，即“易失性”。打开或关闭工作簿、编辑单元格中的数据、双击调整自动列宽等动作，都会引起“易失性”函数重新计算，而非易失性函数仅当：函数的参数或者是引用的区域、数据有变更时才会进行重算。
以下函数具有“易失性”：获取随机数的RAND函数和RANDBETWEEN函数、获取当前日期的TODAY函数、获取当前时间的NOW函数、获取单元格信息的CELL函数和INFO函数以及返回引用的OFFSET函数、INDIRECT函数等。
此外，SUMIF函数的第3参数sum_range使用简写方式时，公式在计算时需要根据range参数重新定位sum_range参数所对应的单元格区域，因而表现出“易失性”现象。
关于SUMIF函数第3参数的简写方式，请参阅：疑难79。
INDEX函数在使用A1:INDEX等引用结构时，也因需重新定位单元格区域而表现出“易失性”现象。例如第4章第1节疑难128：INDEX（H:H,ROW()-1）不具有易失性，但SUM（H$3:INDEX （H:H,ROW()-1））则具有易失性。
↙ 知识扩展
※ 增加“撤销”次数 ※
Excel 2010默认可撤销最后100次操作，如果需要增加或减少撤销次数，可通过修改注册表的方法实现。步骤如下：
步骤1 单击Windows“开始”→“运行”命令，在“打开”框中输入“regedit”，单击“确定”按钮，打开“注册表编辑器”窗口。
步骤2 在左侧窗口展开“HKEY_CURRENT_USER\Software\Microsoft\ Office14.0\Excel\Options”分支，在右侧窗口空白处，单击右键，指向“新建”，单击“DWORD”，将新DWORD值命名为“UndoHistory”。
步骤3 双击“UndoHistory”，打开“编辑DWORD值”对话框，单击“十进制”，然后在“数值数据”框中输入200或其他数字，单击“确定”按钮。
步骤4 重新启动Excel 2010，可撤销的操作步骤次数将变为设定的值，如图1-60所示。
■ 图1-60 改变后的可撤销次数
疑难27 为什么在Excel 2003中打开公式会显示_xlfn. 前缀
如图1-61所示，使用Excel 2003版打开Excel 2010版工作簿时，公式中SUMIFS函数为什么会显示为_xlfn.SUMIFS？
■ 图1-61 Excel 2003版函数显示_xlfn.前缀
→ 解决方案
了解Excel 2010中的新增函数。
↙ 操作方法
如果仅需要计算结果，在Excel 2003中打开包含高版本新增函数的工作簿时，不重新编辑公式直接采用复制和选择性粘贴为“数值”的方式，保留计算结果。
如果需要更新计算结果，则必须使用Excel 2003支持的函数重新设置公式，否则将显示为#NAME?错误值。
↙ 知识扩展
※ Excel 2003不支持的新增函数有哪些 ※
Excel 2007新增的函数主要有：条件统计类函数AVERAGEIF、AVERAGEIFS、SUMIFS、COUNTIFS，逻辑判断函数IFERROR和多维数据集函数CUBEKPIMEMBER、CUBEMEMBER、CUBEMEMBERPROPERTY、CUBERANKEDMEMBER、CUBESET、CUBESETCOUNT、CUBEVALUE等。
在此基础上，Excel 2010新增了返回列表和数据库的合计函数AGGREGATE，并且针对MOD、RAND、BETADIST等函数使用了新的算法，提高了计算结果的准确性和运算速度；对RANK、MODE等函数也做了改进，增加了RANK.AVG、MODE.MULT等函数，以更符合统计惯例的需求，以及采用更为准确的函数命名。
如果需要兼顾考虑Excel 2003用户，则在使用新增、改进后的函数时，应选择“兼容性”函数。方法如图1-62所示，单击编辑栏左侧的“插入函数”按钮，在弹出的“插入函数”对话框中选择类别为“兼容性”。
■ 图1-62 插入兼容性函数
疑难28 为什么用Excel 2010打开早期版本的文件后只有65 536行
如图1-63所示，在Excel 2010中打开早期版本的兼容文件后，为什么总行数只有65536行，而不是1048576行？
■ 图1-63 Excel 2010打开早期版本工作簿仅有65 536行
→ 解决方案
转换低版本文件为当前文件格式。
↙ 操作方法
步骤1 单击“文件”选项卡→“信息”，弹出该工作簿的有关信息；
步骤2 在信息视图中单击“转换”按钮；
步骤3 在弹出的提示对话框中单击“是”按钮，关闭并重新打开工作簿，如图1-64所示。
■ 图1-64 将兼容模式改为当前文件格式
此外，也可以单击“文件”选项卡中的“另存为”按钮，将低版本文件保存为当前文件格式。
↙ 知识扩展
※ Excel 2010支持哪些文件格式 ※
Excel 2007、Excel 2010引入了新的简洁而可靠的Excel XML Format格式，提高了Excel与外部数据源和系统的集成程度。新格式存储的文档文件大小比Excel 97-2003等早期版本的文件格式（.xls、.xlt）要小很多，而且恢复毁坏或受损文件的功能得到了极大改善。
此外，在Excel 2007、Excel 2010版中存储VBA宏代码或使用Excel 4.0宏表函数时，需保存为“Excel启用宏的工作簿”或“Excel启用宏的模板”。Excel 2010支持的Excel文件格式及相关说明如表1-12所示。
表1-12 Excel 2010支持的Excel文件格式及说明
※ 如何在早期Excel版本中打开、另存为高版本文件 ※
除了在Excel 2007、Excel 2010中将文件另存为“Excel 97-2003工作簿”外，用户还可以在微软官方网站下载并安装“Microsoft Office Word、Excel和PowerPoint 2007文件格式兼容包”，实现在Excel 2003中直接打开、另存为高版本文件。