谁能让我忘记 阅读答案:Excel学习 创建计算公式 - 一泓清水的日志 - 网易博客

Excel学习

               一、Excel中的快速计算

在窗口的右下角的“NUM”是快速计算工具。在“编辑状态”栏任意位置上的右键菜单上包括有“均值、计数、计数值、最大值、最小值、求和”六种计算方式的对话框（如E4-01：快速计算工具），你可根据不同的计算要求进行选取。如你要选择“求和”，就在菜单上点击一下“求和”，在“求和”前出现一个“b”号，此时，当你在工作表中选取一组有数据的单元格区域时，便会在“快速计算显示窗口”显示出“求和=xxx”。

注：快速计算工具是可扩展其用途的。如要求和，可把数据输入到一张空表中，进行快速求和。因为求和的数据是可见的，可随时校对，与使用计算器相比更为方便。

 （二）快速求和

选取求和的单元格区域后，在变量单元格按“Alt（副键盘上的）+”或用鼠标左键点工具栏的“∑”按钮，便可实现快速求和（E4-02：快速求和）。

 

自菜单栏选：工具→自定义→命令→工具→把像个计算器的自定义图标拖到菜单栏并用鼠标右击，改“自定义”为“计算器”即可。
        点击计算器按钮便出现一个“标准”计算器（如E4-03：标准型计算器），在计算器上点击“查看”，再点击对话框中的“科学型”，即可出现一个“科学型”计算器（如E4-04：科学型计算器）。用科学型计算器可以完成更为复杂的运算。
       用机内计算器有两大优点：其一，机内计算器可通过副键盘进行快速输入数据，并可用计算器上“编辑”中的下拉菜单（或计算机系统内复制、粘贴命令或用Ctrl+C、Ctrl+V快捷键）将计算结果“复制”和“粘贴”到工作表的任意单元格中，克服了普通计算器需由人工输入计算结果多余操作，还可克服输入中可能出现的失误。其二，用完计算器后，点击窗口的任意位置，计算器就自动回到“托盘”栏上，当离开Excel后使用其他的程序时，还可从“托盘”栏上随时调用这个计算器。

 
二、Excel公式设置中的有关问题

设置Excel公式必须由“=”号开始。一是在变量单元格输入“=”号后，在名称框即显示一个你前次最后使用过的函数，如果不是所需要的函数，可用鼠标键点击名称框右边的下三角，在出现的对话框内便列出最近常用的10个函数，可自其中查找函数（见E1-05：最近用过函数），如果对话框内还没有你需要的函数，可点击对话框中的“其他函数”此时又出现一个“插入函数”对话框（E1-06：插入函数对话框），选择需要的函数后，用鼠标左键点击函数输入到公式。也可在输入“=”号后直接键入函数；二是用鼠标左键点击工具栏上的粘贴函数按钮，变量单元格内会自动出现“=”号，并同时显示“插入函数”对话框（如E1-06：插入函数对话框），可在对话框中查找函数，并用鼠标左键点击所需函数输入到公式。

 

（二）公式引用中的相关问题

无论是数学公式还是函数公式，都涉及到对单元格的引用问题。对单元格引用方法分为“相对引用”和“绝对引用”。如：

“=A1”是对“A1”单元格的相对引用，在复制公式时，会随复制的位移而发生引用位置的改变；

“=$A$1”是对“A1”单元格的绝对引用，在复制公式时，不会因复制公式的位移而发生引用位置的改变；

“=$A1”是对A列的绝对引用，在复制公式时，其引用单元格的列号不会因复制公式而发生改变，但行号是会随复制公式的位移而发生改变的。

“=A$1”是对1行的绝对引用，在复制公式时，其引用单元格的行号不会因复制公式而发生改变，但列号会随复制公式的位移而发生改变的。

注：“相对引用”和“绝对引用”的转换方法是将光标停在公式中所引用单元格名称前，按“F4”键即可转换，反复按“F4”键可选择不同需求的引用方法。

在工作表中建立的计算公式，涉及对两个以上单元格区域的引用，称为二维公式。

例1、如对两个（或多个）不相邻且单元格域又不对称的数组求和，其二维公式的格式：

=SUM(A3:A7,C5:C17,E4:E18)

例2、如对矩形单元格区域（即两个以上相邻且对称的单元格区域）数组求和，其二维公式的格式：

相当于公式=SUM(A3:A10,B3:B10,C3:C10,D3:D10)

为使二维公式对单元格区域的引用操作更简捷，又不至发生单元格的引用位置差错，用鼠标点击的方法输入单元格（区域）名称是最实用的。

现就上两例公式中，如何用鼠标点击法引用单元格（域）分别予以说明：

按例1所列举的条件，如将“F3”作为变量单元格，选“F3”单元格，点击工具栏上“Σ”，出现公式：

=SUM()

有时在圆括号内会出现“求和”范围的提示，可不理采他。然后点击“A3”单元格，再按住“Shift”点击“A7”单元格，公式变为：

=SUM(A3:A7)

按住“Ctrl”点击C5单元格，接着再按住“Shift”点击“C17”单元格，公式变为：

= SUM(A3:A7,C5:C17)

再按住“Ctrl”点击E4单元格，接着再按住“Shift”点击“E18”单元格，公式变为：

=SUM(A3:A7,C5:C17,E4:E18)

按回车键完成求和。公式内的“，”号是系统自动加上的。

例2所列举的条件，如将E10作为变量单元格，选“E10”单元格，点击工具栏上“Σ”，出现公式：

=SUM()

点击“A3”单元格，再按住“Shift”点击“D10”单元格，公式变为：

=SUM(A3:D10)

按回车完成求和。

如果公式中对单元格（区域）引用范围仅在窗口的一屏之内，也可采用拖选的方法对连续单元格或对矩形区域进行引用。

上述方法对于求均值、计数、最大值、最小值等都是适用的。

注：如果我们选择一个变量单元格，预先建立一个Excel求和公式，其公式范围是当前窗口的一个矩形单元格域。这时如果在域内的任何一个单元格中输入数字，变量单元格都会立刻求出和来。用这种办法求和，你可以检查输入的数字是否有误，比用计算器更放心。

在工作表或工作簿之间，引用单元格（区域）建立的计算公式，称为三维公式。如对1月到12月工资表中D4单元求和，其三维公式的格式如：

=SUM(工资表1:工资表12！D4)

在同一工作簿的工作表之间引用单元格，可先用鼠标左键点击工作表标签、再点击工作表中的单元格进行引用操作。如在变量工作表的变量单元格中输入“=”号与函数后，用鼠标左键点击引用工作表标签，再点击引用单元格。

在引用其它工作簿中、工作表中的单元格，在变量工作簿、工作表中的变量单元格中输入“=”号与函数后，自菜单栏的窗口中点击已打开的待引用的工作簿、工作表标签及目标单元格进行引用。以求和为例：

如将汇总表的“D4”作为变量单元格，对“工资表1到工资表12”的“D4”单元格求和。

选汇总表“D4”单元格，用鼠标左键点击工具栏上“Σ”，在汇总表的“D4”单元格中出现公式：

=SUM()

然后点击标签栏上的“工资表1”标签，公式变为：

=SUM(工资表1！)

再按住“Shift”点击标签栏上的“工资表12”，公式变为：

=SUM(工资表1:工资表12！)

再点击 “工资表12上的D4”，公式变为：

=SUM（工资表1:工资表12！D4）

此时按回车键便完成12张工资表中“D4”单元格的求和。

下一步的工作就是将公式复制到汇总表中其他应当求和的单元格中去，完成工资表1到工资表12的全部求和。

注：对工作簿与工作簿之间的引用，因系统生成的引用是绝对引用，其公式不能直接复制。如必须进行复制公式的操作，需将公式中的绝对引用改写成相对引用。

1、用“名称框”定义名称
        如将B9：B12单元区定义为“应发工资”，选取该单元格（区域）后，在名称框显示为“B9”，可将B9改写为“应发工资”（如E4-07：定义名称）。

 

2、用插入法定义名称

自菜单栏选：插入→名称→定义→出现“定义名称”对话框（如E4-08：定义名称对话框），在对话框中的“在当前工作簿的名称”文字框内，定义选定单元格（区域）的名称。还可在其中进行添加、删除名称的操作。

 

对单元格（区域）定义名称后，可自名称栏点击右边的下三角标记，即显示出一个带有滚动条的名称对话框（见E4-08：定义名称），可用滚动条上下查找名称，当用鼠标左键点击所选的名称后，即可在工作表中标记出所定义的单元格（区域）。

注：对单元格(区)定义名称后，在公式的引用中使用名称时，等于是对单元格(区域)的绝对引用。

3、用名称设置公式

对工作表的各单元域定义名称后，便可用名称设置公式，其公式格式如：

=(基本工资+奖金+书报费+洗理费)

如果只对某一个定义名称后的单元格区域求和，其公式如：

=SUM（基本工资）

为检查公式中引用单元格（区域）是否正确，需要对公式进行审核。审核公式的方法有三：

一是光标进入变量单元格后，在编辑栏内会自动显示公式，可从中查看公式中对单元格的引用是否正确；

二是用鼠标左键双击变量单元格，在变量单元格内便会以不同颜色显示出公式的引用情况，同时以不同的边框颜色显示出所有引用单元格（区域）。
三是在光标进入变量（或常量）单元格后，自菜单栏选：工具→审核（Excel XP为“审核公式”）→此时出现一个对话框（如E4-09：审核对话框），框内有“追踪引用单元格、追踪从属单元格（用于常量单元格）、追踪错误、取消所有追踪箭头、显示审核工具栏”的提示。如用鼠标左键点击“追踪引用单元格”，所有引用单元格会出现兰色外框，并自框内发出兰色箭头指向变量单元格内。此时可根据兰色框和兰色箭头检查公式的引用是否正确（如E4-10：“审核”的运用）。取消时按E4-09图的提示中点击“取消所有追踪箭头”。

 

在Excel的公式设置中，无论使用什么运算符，都必须是半角字符，这一点十分重要。如果在公式设置中出现错误，首先要检查的就是使用的运算符是否是半角字符。现就公式中使用的运算符分述如下：

1、Excel中的运算符（总计16个）分别为：

（1）Excel公式中的算术运算符

Excel中的算术运算符（6个）：+、-、*、/、%、^，含义依次为：加、减、乘、除、百分数、乘幂。运算符在公式的应用如下表：

 

乘幂不仅可以运算整数形指数，还可运算包括零、正负整数和正负分数的指数运算。方法是在公式中先输入数字0和空格，再输入“分子/分母”，如果是负分数，应将负号放在0之前。如：要计算29的三分之一次幂，公式“=29^0 1/3”即可。例下表：

 

（2）Excel公式中的比较运算符

Excel公式中的比较运算符（6个），其作用是比较两个值，结果为逻辑是“TRUE”或不是“FALSE”。包括：=、>、<、>=、<=、<>，含义依次为：等于、大于、小于、大于等于、小于等于、不等于。

对比较运算符的运用，将在以后的实例中部分的加以说明。

（3）Excel公式中的文本连接符

Excel公式中的文本连接符（1个）,符号为“&”，可连接一个或更多字符串以产生一个文本串或数字串的连接。

如公式：=“2008年”&“北京的天更蓝”，“回车”确认，结果产生出“2008年北京的天更蓝”。

又如：在“A1”单元中有数字123，“A2”单元中有数字456，要在“A3”单元中用公式：=A1&A2，返回值为“123456”。

或产生一个数据的计量单位，如：同样在“A1”单元中有数字123，“A2”单元中有数字456，在“A3”单元中用公式：=A1&A2&“元”，返回值为“123456元”。

（4）Excel公式中的引用操作符

Excel公式中的引用操作符（3个）：冒号“：”用作运算区域的连接；逗号“，”用作区域与区域间的连接，“空格”用作多个引用的交集为一个引用。

对引用操作符的运用，将在以后的实例中说明。

（5）Excel公式中的条件设置符

Excel公式中的条件设置符是英文对称的双””号，如”优秀”、”良好”、”及格”、”到期”、”进货”等等格式的设置。

2、Excel公式中的计算顺序

Excel公式中的计算顺序为：引用操作符、负号、百分比、乘幂、乘除、加减、连接符、比较运算符。

三、Excel公式中的错误值

Excel公式中的错误值如下表：

 公式中除“####”错误值外，其余错误值不仅影响表格的美观，且作为数值为其它公式所引用后也影响其运算结果?。如何让它不显示错误值，请参阅ISERROR测试错误值函数的使用方法。

四、用“R、C”引用单元格建立公式

建立Excel公式，还可运用“R、C”引用单元格。“R”表示行，“C”表示列，如“R1”表示工作表的第一行、“C1”表示工作表A列，以此类推。如果使用“R1C1”引用单元格，其效果与“$A$1”等价，是对单元格的绝对引用。以下是“ R、C” 引用样式示例。

 注：用“R、C”对单元格的引用方法，个人觉得不是很大众化，仅作一般了解。

五、Excel的函数

Excel中的函数总计320个。现将部分常用函数及其主要用途列表如下。

 序号   常用函数名          用途

1     AND、OR和NOT     复合条件测试

2     AVERAGE               求平均值

3     COMBIN                   求一组数目的对象组合

4     CONCATENATE       连接函数

5     COUNT                    指定含有数字的区域

6     COUNTA                  统计个数

7     COUNTIF                 计算区域内满足条件的个数

8     DATE                      返回指定日期的序列数

9     FACT                       阶乘函数

10    IF                            判断

11    INT                          取整

12    ISERROR                测试错误值函数

13    Len()                      指定一段文字长度

14    MAX                        求最大值

15    Mid                         从一段文字中的指定位置起提取指定数量的字符。

16    MIN                         求最小值

17    MOD                       除余

18    NOW()                    时钟

19    PMT                       计算在固定利率下，贷款的等额分期偿还额

20    PRODUCT             乘积

21    RANK                    排位

22    REPT                     指定次数重复填充

23    ROUND                  四舍五入到指定小数

24    SQRT                    正数平方根

25    SUM                       求和

26    SUMIF                    条件求和

27    SUMPRODUCT      若干数组中彼此对应单元数的乘积和

28    SUMSQ                  求平方和

29    TEXT                      将指定数值转换成文本格式

30    TODAY()                返回计算机时钟当日的日期

31    VLOOKUP              查找结构信息

运用Excel函数设置公式，可以把复杂的公式变得十分的简单。下面就部分函数公式与算术公式用比较的方式进行一些说明：

1、AND、OR和NOT函数

见“10、IF判断函数”的说明中有关对嵌入AND、OR和NOT函数的说明。

2、AVERAGE平均值函数

如公式：=AVERAGE(A1:A5)返回A1:A5间的平均值

注：这里返回的平均值包括常数为“0”的单元格个数在内。

相当于算术式：=(A1+A2+A3+A4+A5)/5

3、COMBIN组合函数

公式格式：=COMBIN（数，组成数）返回组成方案

如从17人中抽出12人组成一个球队，求有多少个组成方案

公式：=COMBIN(17,12)返回6188个方案。

4、CONCATENATE连接函数

公式格式：=CONCATENATE(“字符串”,引用单元格或公式，”字符串”，…)返回一个文本段落。

如在一张《电话费公示表》之后写上一段文字，并且要求在文字中自动计算出数值。公式格式如：

=CONCATENATE("本月共支出电话费：",SUM(B3:C140)&"元","。"&"其中市话费:",SUM(B3:B140)&"元","，"&"长话费:",SUM(C3:C140)&"元","。")

在工作表还没有数字的情况下，所返回的文字段落如：

本月共支出电话费：0元。其中市话费：0元，长话费：0元。

公式设置完成后，只要在“B3:C140”单元格区域内输入数字，其文字段落中都会自动产生运算结果。

注：公式中用作单元格区域连接的“，”号必须用半角符号，且这个“，”号是不在文字段中落显示的。如需要在文字段落中显示标点符号，则应在“，”号之后紧接着输入“所需标点符号”。

5、COUNT指定含有数字的区域

如公式：=COUNT(B4:B15)返回B4:B15区域内含有数值的单元格数目（包括日期和变量公式）。

如在求平均值时，要使单元区域内不包括含有空白单元格，可在公式中套用COUNT函数。如要求出“B4:B15”单元域内不含空格的平均数，其公式：

=SUM(B4:B15)/COUNT(B4:B15)

6、COUNTA求非空白单元格函数

公式格式：=COUNTA（单元格区域）返回单元区域内非空格的单元格数目（也包含数值的单元格在内）。

如“A4:A67”单元格区域内是职工姓名记录，要统计单元格区域的记录中有多少个职工。其公式为：

=COUNTA(A4:A67)返回区域内的职工数，如因职工增加或减少，其统计数会随之变更。

7、COUNTIF求条件个数函数

公式格式：=COUNTIF(单元区域,”条件”)

如“B4:B40”单元格区域中分别记录有大学生、中专生、研究生等，求这个单元格区域内有大学生多少人？公式格式为：

=COUNTIF（B4:B40，“大学生”）返回单元格区域内大学生的人数。

还有性别、籍贯、职务、职称等等的统计，均类推。

又如“B4:B40”单元格区域中记录的学生考试成绩，求这个单元格区域内及格以上分数的学生有多少人？公式格式为：

=COUNTIF(B4:B40,”>=60”)返回单元格区域内及格以上学生的人数。

8、DATE返回指定日期的序列函数

如一工作表中分列在A5单元格中有“2001”、B5单元格中有“5”、C5单元格中有“21”，其指定日期序列的公式格式为：

=DATE(A5,B5,C5)返回2001-5-21

9、FACT阶乘函数

假如我们要求从1到8的阶乘积，公式格式：

=FACT（8）返回40320

相当于数学公式：

=1*2*3*4*5*6*7*8

10、IF判断函数

如在A1单元格中的数值小于20时返回5；否则返回10。公式格式为：

=IF（A1<20,5,10）

又如在A6单元格中的数值小于60时返回为“不及格”；否则返回为“及格”。公式格式为：

=IF（A6<60,”不及格”，”及格”）

再如在A6单元格有评比结果的记录。按规定评为“先进”者发给200元奖金、“合格”者发给100奖金、“一般”者发给50元奖金。公式格式为：

=IF(A6="先进",200,IF(A6="合格",100,IF(A6="一般",50)))

注：公式中的“()”号，其左“(”号与右“)”号必须对等。

可在IF函数中嵌入其他函数。如B3：G16单元区域中，分别记录有各项检查评比分，要求总分大于500分时为“合格”；否则为“不合格”公式格式为：

=IF(SUM(B3:G16)>500,”合格”,”不合格”)

可在IF函数中附加AND、OR符合条件测试函数。如在G4单元格中记录的是平均分数，而在F4单元格中记录的是旷课次数，规定平均分数大于80，且旷课次数少于5次时可评为“优等生”，否则只能评为“合格生”。

如果G4单元格中记录的平均分数为90，F4单元格中记录的旷课次数为5，在IF函数中分别嵌入AND和OR函数，其返回的结果是不一样的。

如IF函数中嵌入AND函数。公式格式如：

=IF(AND(G4>80,F4<5),”优等生”,”合格生”)返回的是“合格生”，是“且”（因两个条件中只有一个条件为真）的结果；

如IF函数中嵌入OR函数。公式格式如：

=IF(OR(G4>80,F4<5),”优等生”,”合格生”)返回的是“优等生”，是“或”（因两个条件中有一个条件为真）的结果。

NOT函数不宜在上例中嵌入，因为他常用于对条件求反。如参数为假，他返回的逻辑值为“真”，而参数为真时，则返回逻辑值为“假”。

如在A1单元格中有常数4，在IF函数中嵌入NOT函数。公式格式如：

=IF(NOT(A1=4),”是”,”不是”)返回的结果为“不是”。

IF函数是Excel诸函数中用途较广泛的一个函数。本章后面的一些应用实例中还将多处涉及到IF函数的使用方法。

也可用AND函数实现简单的条件测试。如果A1单元格的值大于A2单元格中的值，而A2单元格的值又小A3单元格的值时，返回逻辑值TRUE.否则返回为FALSE其公式如：

=AND(A1>A2,A2

11、INT取整函数

（1）单纯取整。即不管小数后数字大小，一律舍去。公式格式：

=INT(单元格)。

如“B3”单元格内有数值123.23，其取整公式格式：

=INT(B3)返回123

（2）条件取整。即设定小数的数值达到多少，符合其条件后取整。但公式需要与“IF”函数嵌套混合运用。如“B3”单元格中有数字1125.26，设置取整公式时，要求大于0.2时即向上收1。公式如：

=IF((B3-INT(B3))<0.2,INT(B3),INT(B3)+1)返回1126

（3）取整后存零。如应发工资有零头，不方便发放，需要取整后将其零头存下来留作以后集中处理。设“H4”单元格是应发工资1234.66，公式如：

=H4-INT（H4）返回值为0.66

12、ISERROR测试错误值函数

在公式中，如果所引用的单元格没有数值，或者查找条件不存在，都会在其变量单元格中返回一个错误值。这个有错误值的变量单元格，不仅影响表格的美观，而被其它公式所引用后又影响运算结果。因测试不同错误值有多种函数可选，如果只选用ISERROR函数也是可以取代其它的错误值测试函数的。因ISERROR测试错误值函数，若测试值为任何错误值(#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME?或NULL)都返回TRUE.

如有这样一个公式：=A2/B2当在A2或者B2（即除数）单元格中没有数字，则返回“＃DIV/0！”的错误值。详见下表：

 

如果把公式改写为：

=IF(ISERROR(A2/B2),0,(A2/B2))返回的是“0”值，而不是“＃DIV/0！

公式的含义是：用IF函数来断ISERROR函数的测试结果，如测试有错误时则返回为“0”，如测试没有错误时，则进入下一步运算。

如果再将整个工作表设置为不显示“0”值，这个工作表就显得很美观了。不显示“0”值的设置方法见“第三章、七”。

对ISERROR函数的应用，还可参阅VLOOKUP的应用举例。

13、Len(text)指定一段文字长度函数

如A2单元格中存有15位数的身份证号码，要指定A2单元格文字长度，其公式格式：

=LEN(A2)返回数15

LEN函数多数情况下是与其它函数镶嵌应用。详细应用情况请参阅“第四章、六、（五）”有关的公式设置。

14、MAX求最大值函数

如在F4：F40单元格区域内是学生考试成绩的记录，求该区域内最高分是多少？公式格式：

=MAX(F4:F40)返回最高分数值

15、MID从一段文字中的指定位置起提取指定数量的字符

公式格式：

=Mid(指定单元格,指定起始位置,指定提取数量)，其应用方法见“第四章、六、（五）”有关公式的设置。

16、MIN求最小值函数

如在F4：F40单元格区域内是学生考试成绩的记录，求该区域内最低分是多少？公式格式：

=MIN(F4:F40)返回最低分数值

17、MOD除余函数

应用于数值的公式格式：

=MOD（45,34）返回45被34除的余数11

应用于单元格的引用的公式格式：

=MOD（A10，B10）返回“A10”中数值被“B10”中数值除后的余数。

18、NOW()时钟函数

该函数不带任何参数，在单元格中引用该函数后，则在单元格返回计算机系统内部日期与时间。公式格式：

=NOW()

19、PMT函数

应用于数值的公式格式：

=PMT(贷款利率,偿还期限,贷款金额)

如一个人用住房公积金贷款200000元，年利率为3.60％，要求5年还清贷款，每月偿还金额是多少？

=PMT(3.6%/12,5*12,200000)=-3647.31

20、PRODUCT乘积函数

应用于数值的公式格式：

=PRODUCT（3,4,5）返回60

应用于单元格的引用的公式格式：

=PRODUCT（B2:B4）返回B2到B4间所有单元中数值的连乘积。

21、RANK排位函数

公式格式：=RANK(位置,范围,排位方向)。公式中的第三个参数若为“0”则为正向排位，若为“1”则为反向排位。如给（E4-11：排位）图中的总成绩正向排位，其公式如：=RANK(D2,$D$2:$D$10,0)

这种排位，相同数所排列的位数也相同，并自动跳过一个序位。如图中有两个第二就不再出现第三。

注：数组范围必须是绝对引用，否则将无法向下复制公式。

22、REPT指定次数重复填充函数

如要在一个单元格中一次填写15个“☆”号，公式格式：

=REPT(”☆”,15)返回☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

 23、ROUND四舍五入函数

格式：=ROUND(单元格,条件)

如“B3”单元格内有数字123.456，现按不同的舍取要求设置公式如下：

（1）不留小数四舍五入。公式如：

=ROUND(B3,0)返回123

（2）留一位小数四舍五入。公式如：

=ROUND(B3,1)返回123.5

（3）留两位小数四舍五入。公式如：

=ROUND(B3,2)返回123.46

（4）自个位四舍五入。公式如：

=ROUND(B3,-1)，返回120

因Excel的计算精确度达小数后14位。如果一个变量数只在格式上进行小数位设置，而没有在公式中进行四舍五入的小数位设置，这个变量数再次作为参数引用后，其运算结果的数值是不一样的。

24、SQRT平方根函数

应用于数值。公式格式=SQRT(8)返回2.828427

应用于单元格的引用。公式格式=SQRT(F3)返回F3单元格中数值的平方根。

25、SUM求和函数

如公式格式：=SUM(A1:A5)

相当于算术公式：=A1+A2+A3+A4+A5

用于多个单元格区域。如公式格式：=SUM(A1:A5,B1:B5)

相当于算术公式：=A1+A2+A3+A4+A5+B1+B2+B3+B4+B5

用于数值。如公式格式：=SUM（123,456,789）返回1368

相当于算术公式：=123+456+789

用于不连续单元格。如公式格式：=SUM(A1,B6,C9)返回这三单元格的和

相当于算术公式：=A1+B6+C9

26、SUMIF条件求和函数

其公式格式：=SUMIF（查找单元格区域，”条件”，计数单元格区域）或=SUMIF（查找单元格域，”条件”）

例1：如B4：B10单元格区域内有银行、现金、及邮件三种收款方式的注释，求C4：C10单元格域中不同进款来源的分类合计，三个公式格式分别为：

=SUMIF(B4:B10,”=银行”,C4:C10)返回银行汇入的款项合计；

=SUMIF(B4:B10,”=现金”,C4:C10)返回现金支付的款项合计；

=SUMIF(B4:B10,”=邮件”,C4:C10)返回邮件汇入的款项合计。

如果采用算术方式，先从B4:B10查找单元区域内，找到“银行”、“现金”或者“邮件”条件，然后再从C4:C10中去查找每一个对应的单元格，并逐一相加。

注：对查找条件的设置，可以不输入“=”号。

SUMIF条件求和函数，只能对一个条件进行查找并求和。但可以通过相加的方法，对两个以上的查找条件求和。如例1中，要同时对“现金”及“邮件”两种资金来源进行求和，可以用以下两种方法设置公式：

一是直接将两个查找公式相加，公式格式如：

=SUMIF(B4:B10,”=现金”,C4:C10)+SUMIF(B4:B10,”=邮件”,C4:C10)

二是用SUM求和函数将两个查找条件相加，公式格式如

=SUM(SUMIF(B4:B10,”=现金”,C4:C10),SUMIF(B4:B10,”=邮件”,C4:C10))

例2：在B3:B10单元格区域内求出大于100的数值和，公式格式

=SUMIF(B3:B10,”>100”)返回大于100的数值和。

例3：在G2：G32单元格区域记录的是职工年龄，而在F2：F32单元格区域记录的是职工工资，要求出小于30岁工人的工资和。公式格式

=SUMIF(G2:G32,”<30”,F2:F32)返回小于30岁工人的工资和。

例4：因为平均值是总和除以个数。如果将例3的工资和求出平均工资。可在SUMIF函数中套用COUNTIF函数。公式格式：

=SUMIF(G2:G32,”<30”,F2:F32)/COUNTIF(G2:G32,”<30”)返回小于30岁工人的平均工资。

27、SUMPRODUCT数组中彼此对应的乘积和函数

如算术公式=A1*A2+B1*B2+C1*C2+D1*D2+E1*E2+F1*F2

而函数公式则=SUMPRODUCT(A1:F1,A2:F2)

可见，用SUMPRODUCT函数设置以上公式，就简化多了。如（E4-12：彼此对应乘积和）图中所示。

用SUMPRODUCT函数与SUM函数镶嵌组成公式，求平均值数组的总平均值。如有一张电话费公布表，表中有已各单位的市话费、长话费均值，要分别求出市话费和长话费的总平均值。按照（E4-13：求总平均值例表）图中所示，在C11和D11单元格分别设置公式如下。

C11单元格的公式

 

=AVERAGE(D4:D10)则返回的平均值为283.71

两种计算方法相比较如下：

 可见两种不同的计算方法，其数值差别是很大的。而第二种计算方法，是对平均值数组再次平均，并不是对基数的平均。因此，第二种算法是错误的。

28、SUMSQ平方和函数

应用于数值。公式格式=SUMSQ(8,9)返回“8”和“9”平方和为145

应用于单元格的引用。如：A1、A2、A3、A4各单元格中分别有数字12、22、32、42，求各单元格的平方和。

其公式=SUMSQ(A1:A4)返回值为“A1:A4”单元格的平方和为3416

29、TEXT将指定数值转换成文本格式函数

格式=TEXT（指定数字或单元格，返回格式）如公式

=TEXT("195632","[DBNum2]G/通用格式")返回中文大写“壹拾玖万伍仟陆佰叁拾贰”。

如在B3单元格中有数字15698736，可在公式中直接引用“B3”，公式格式：

=TEXT(B3,"[DBNum1]G/通用格式")返回中文小写“一千五百六十九万八千七百三十六”。

30、TODAY()返回计算机时钟当日的日期函数

该函数不带任何参数，在单元格中引用该函数后，则在单元格返回计算机系统内部日期。

31、VLOOKUP查找结构信息函数

公式格式：=VLOOKUP(指定查找信息的单元格，查找单元格区域，指定显示符合查找信息的位置，匹配等级)

指定查找的信息一般是以升序排列的序号、编码及账号等；查找单元格区域引用为绝对引用，指定显示符合查找信息的位置，按符合查找信息的单元列为1，向右按2、3、4…的序列指定；匹配等级是：“TRUE”为近似匹配，“FALSE”为精确匹配。如公式中不指定匹配等级，则默认为近似匹配。现举两例：

（1）查找信息源固定不变，在工作表中输入查找信息完成查找输入。
第一步：建立信息源表。先将要使用搜索输入的代码、名称按顺序分别列出表来。

 

 

假如其中A列是部门代码，B列是部门名称；C列是类别代码，D列是类别名称（如E4-14：查找信息源表）。

第二步：建立查找输入工作表。在输入表中建立公式。假定在C9单元格中用VLOOKUP搜索函数定义公式，公式格式

=VLOOKU（B9,$A$2:$B$6,2）如（E4-15：输入查找公式）图的编辑栏所示。

        公式完成后，在B9单元中输入“’05”即可在C9单元格中自动显示出“劳资科”来。C9单元格的公式设置后，往下复制公式即可完成C10及以下单元格的公式设置。

公式中的“B9”为输入查找内容，“$A$2:$B$6”为查找的范围（必须用绝对引用），“2”是指从查找范围中返回数据的列（如是“1”则返回第一列的数据，如是“2”则返回第二列的数据），按上例，如将公式的“2”改为“1”，此时，在B9单元格输入05后，则在C9单元中返回的是“05”，而不是“劳资科”。（2）查找信息源可变，在信息源的表格中输入查找信息（数据）后，自动完成工作表中的信息（数据）的输入。

 为便于说明问题，我们建立一张信息源与查找输入表合一的工作表（见E4-16：查找输入的另一种格式）所示，工作表的A:C列是查找源，E:H列是查找输入表。

 在F2单元格中建立公式=VLOOKUP(E2,$A$2:$C$9,2,FALSE)

在G2单元格中建立公式=VLOOKUP(E2,$A$2:$C$9,3,FALSE)

同时再将F2及G2的公式向下复制，即可完成F3及G3及以下单元格的公式建立。

这时只要在B2或C2及以下单元格中输入数字，此时在F2或G2及以下单元中便会自动填入相同数字。

从图4-14中可以看出，在F2及G2中单元格中，因查找的92250电话号码不存在而显示出的#N/A错误值，且这个#N/A错误也影响到合计的运算。为了解决这一问题，需将F2及G2单元格中的公式改为：

=IF(ISERROR(VLOOKUP(E2,$A$2:$C$9,2,FALSE)),0,(VLOOKUP(E2,$A$2:$C$9,2,FALSE)))

=IF(ISERROR(VLOOKUP(E2,$A$2:$C$9,3,FALSE)),0,(VLOOKUP(E2,$A$2:$C$9,3,FALSE)))

注意：该公式涉及两个新的问题：

一是使用了“FALSE”精确匹配参数。如果在公式中不使用这个参数，尽管所查找的E2单元格中的“92250”在其查找范围内不存在，但它也会在F2和G2单元格中返回数字的，势必影响数字的准确性。

二是公式中用IF函数来断ISERROR函数的测试结果，如测试真有错误时则返回为“0”，如测试没有错误时，则进入下一步运算。解决了因错误值影响合计的运算。

数组公式是数组公式是数组区域共享的一种公式，可防止对公式的修改。

1、二维数组公式

数组分项求和公式横排如（E4-17：数组合横排）图所示，步骤是：

（1）选择区域B3：F3

（2）输入=B1:F1+B2:F2

（3）按“Ctrl+Shift+回车”其公式如（E4-17：数组合横排）辑栏所示。公式中的“{}”号是计算机系统自动加上的。

数组分项求和公式竖排如（E4-18：数组他竖排）图，步骤同上。

数组分项相乘公式横排如（E4-19：数组乘竖排）图，步骤同上。

2、三维数组公式

数组公式可以用镶嵌判断函数的方法实现三维运算。如：有“商品名称、单价、销售量”三项销售记录（见E4-20：商品销售记录），要求出每一种商品的销售金额，看起来比较麻烦，但如果用数组公式就方便多了。

 公式格式：

{=SUM(IF($A$3:$A$12="商品1",$B$3:$B$12*$C$3:$C$12))}

输入公式“=SUM(IF($A$3:$A$12="商品1",$B$3:$B$12*$C$3:$C$12))”，回车确认后，其计算的结果是局部范围的和，然后双击公式单格，显示出公式的引用单元格后，再用"Ctrl+Shift+Enter"组合键，此时在公式外自动加上“{}”括号，其公式变为“{=SUM(IF($A$3:$A$12="商品1",$B$3:$B$12*$C$3:$C$12))}”，完成“商品1”销售金额的计算。将公式向下复制，并将公式中的“商品1”改写为“商品2”、“商品3”，即可完成另两种商品销售金额的计算。

选需要省去公式的单元格或整张工作表，自菜单栏选：编辑→复制

自菜单栏选：编辑→选择性粘贴→自对话框中选“数值”→确定→最后按回车键，即可将工作表中的公式省去，只保留单元格或整张工作表中的数值。

注：对整张工作表省去公式保留数值，一但实施操作后是不可逆操作的。操作进行前，请将要省去公式的工作表复制一张后，再进行省去公式的操作。

六、部分函数应用实例

如果做储纳工作，建个现金收支账和银行往来账，不用算盘也不用计算器，使所管的账目日清月结。既方便了做账，又可从中学到一些计算机的操作技术，何乐而不为。以“现金收付”账和“现金月计账”为例：

1、现金收付账

按（E4-21：101现金表）的表样，做一张“现金收付”表，表中的公式：

（1）上年结转的借方和贷方：将上年度借方和贷方余额直接写入F4和H4单元格中。

（2）上年结转余额：在“J4”单元格设公式=F4-H4

（3）每一天的余额：首先在“J5”单元格设公式=J4+F5-H5，接着将H5单元格的公式向下复制，如果因插入或删除单元列，造成下面的余额值错误，可再将公式向下复制一次。

 

搞清现金收付账后，银行往来账也就自通了。

（4）自列标栏选“J”列，再自菜单选：“工具→选项→视图→窗口选项”，复选“零值”使它去掉前面钩，让整个“J”列不显示零值数。

2、现金月计账

按“E4-22：现金收付月计”的表栏做一张表，表中的公式：

 （1）自定义“X月合计”格式：因为“X月合计”作为常量值被公式引用中，公式只对“X”引用有效，而忽略“月合计”三个字的存在。首先在“A4单元格”自定义格式，方法是自菜单栏选：“格式→单元格（单元格格式）→数字→分类→自定义”，在“类型”文字框中输入“0"月合计"”（注意：“0"月合计"”外的大写“”号是不输入的，但”月合计”两边的””号必须是小写）确定退出。并将“A4单元格”的格式复制到“A6、A8、A10、A12、A14、A16、A18、A20、A22、A24、A26”单元格，并分别输入1至12，可见这些单元格只要输入月度数，确认后便自动出现后面的“月合计”。

（2）输入公式：B4单元格公式

=SUMIF('101现金'!$B$5:$B$981,A4,'101现金'!$F$5:$F$981)

C4单元格公式：

=SUMIF('101现金'!$B$5:$B$981,A4,'101现金'!$H$5:$H$981)

B5单元格公式：=B3+B4

C5单元格公式：=C3+C4

（3）选B4:C5单元格域，向下复制公式到C27单元格。

（4）余额公式：单元格公式：=B3-C3

选D3单元格后，向下复制到C27单元格。

       自2001年6月份开始，更换新的“活期储蓄”折了。新的“活期储蓄”折有三个特点：一是日期采用的是数字格式；二是科目采用的注释；三是收支采用的是正负数记账法。现按活期储蓄折的样式模拟一表（见E4-23：正副数记帐实例）。

 1、账本中的公式设置

《2001年存款账本》“结余”单元列中公式的设置。

D3单元格公式：

=C3

D4单元格公式：

=D3+C4

D5以下单元格公式，将D4单元格的公式向下复制即可。

《2001年存款月结表》“金额”单元列中的公式设置。

F3单元格（一月金额）公式：

=SUMIF($A$3:$A$101,"<=20010131",$C$3:$C$101)

F4单元格（二月金额）公式：

=SUMIF($A$3:$A$101,"<=20010231",$C$3:$C$101)-$F$3

F5单元格（三月金额）公式：

=SUMIF($A$3:$A$10,"<=20010331",$C$3:$C$101)-SUM($F$3:F4)

F5及以下单元格（四至十二月）的公式，参照以上公式修改参数即可。

2、对公式中有关问题说明

由于日期是采用的数字格式，使用SUMIF函数去查找月份，其难度较大，比较好的办法是采用比较运算的方式，不管设及对哪月的查找，都按“<=*****31”设置查找条件。

1、建一张存款小账本的表格
       运用三维表头的方式建立一个《银行存款小账本》把“存款总计”、“到期利息合计”及“到期本息合计”都放到表头位置（如E4-24：银行存款小帐本）。使之一在打开表格后，对自己存了多少钱，到期后有多少利息，到期后的本息有多少，一看便一目了然。建好表格后便可按以下说明输入公式。

 2、存款小账本的公式设置

（1）存款总计:公式建在“D2”单元格，其公式：=SUM(D5:H58)，公式中从“D5：H58”单元区是存款数值区。其中“H58”的58是预设的单元格数目，是可以根据存款数目的多少任意改变的。

（2）到期利息合计:公式建在“G2”单元格，其公式：=SUM(I5:I58)公式中从“I5：I58”是到期利息存放区。

（3）到期本息合计:公式建在“J2”单元格，其公式：=(D2+G2)

（4）到期利息：公式建在“I5”单元格并向下复制，其公式：

=IF(A5>0,(E5*0.0225+F5*0.0243*2+G5*0.027*3+H5*0.028*5)*0.8,"")公式中的存期利息，是按银行2001年的存款利率计算的。银行的存款利率如下表：

 

为了省篇幅，《银行存款小账本》中未列计三个月和半年的银行存款利率。银行的存款利率的比率是百分值，为简化公式设置，公式中的利率一律以小数位计算。如公式中的0.8，是利息税0.2的对应值。

（5）存款天数：公式建在“J5”单元格，并向下复制，其公式：

=TODAY()-DATE(A5,B5,C5)

对公式中的TODAY()函数和DATE函数，请参阅“第四章、五”。

（6）存款到期情况：公式建在“K5”单元格并向下复制，其公式：

=IF(OR(IF(E5>0,IF(J5>365,1,0),0)>0,IF(F5>0,IF(J5>730,1,0),0)>0,IF(G5>0,IF(J5>1095,1,0),0)>0,IF(H5>0,IF(J5>1825,1,0),0)>0),"有款到期"," ")

对年的计算按：一年365、二年730、三年1095、五年1825计算。

注：再次提醒你，Excel公式引用中只能使用园括号，但园括号必须是成对的。

至此，一张《存款小账本》就建成了，你只要在表中输入存款时间，存款类型，所有要计算的项目就可自动完成了。

 

因为1年有365天还要多出不足6个小时，每4年有一个润月，润月多出的一天实际上不足24小时，每40年又出现没有润月等情况。而“年”、“月”、“日”都分别有不同进制。准确的计算年龄和工龄是很困难的，只能计算到相差几天就很不错了。其计算的方法很多，这里介绍几种比较适用的方法。

如一个人的出生时间为“1969-10-12”（也可按“1969/10/12”填写）并记录在“B3”单元格中，这时要计算出这个人到你统计当日的年龄，其公式：

=ROUND((NOW()-B3)/365,0)返回统计当日的年龄。或者公式：

=ROUND((TODAY()-B3)/365,0)因为Excel提供的“NOW()”与“TODAY()”函数所返回的都是计算机系统时钟当日的日期与时间，这两个函数是可以直接与工作表中日期记录进行减运算的，而运算的结果是根据单元格数字格式的设置决定的。如单元格格式设置的是数值型，返回的数是相差天数；如设置格式是日期型，返回的是年月日。我们将减运算结果除以365天，再用“ROUND”函数舍取时，取小数后为“0”值，这个年龄计算公式设置就完成了。

 如果我们对出生年月日的格式分别记录在三个单元格内，假定“A2”单元格中记录“1946”年，“B2”单元格中记录“2月，“C2”单元格记录“7”日（如右表所示），并将年龄计算结果记录在“D2”单元格中，其公式

 

 =ROUND((TODAY()-DATE(A2,B2,C2))/365,0)该公式对“D2”单元格格式设置要求，必须设置为数值型。

要是在上例公式中直接写入出生年月日也是可以的。其公式：

=ROUND((TODAY()-DATE(1946,2,7))/365,0)该公式中对年月日的输入，必须用半角“，”号隔开。用上述方法设置公式计算出的年龄，是随计算机系统的时钟改变的，无论何日打印报表，都是当天的年龄。工龄计算公式可以如法炮制。对此例的计算公式，如遇有截止时间要求的年龄和工龄统计，要求统计到某年某月某日。现以统计到2000年7月1日为例，最简单方法是修改计算机时间系统，方法是用鼠标左键双击屏幕右下角的时间，在出现的“日期/时间  属性”展示板上（如E4-25：计算机时钟系统）修改时间即可。

现有15位数和18位数两种号码的身份证。

 15位数身份证号码：前6位为地区代码，7-8两位为出生年份，9-10两位为出生月份，11-12两位为出生日期，15位为性别（奇数为男，偶数为女）；

18位身份证号码：前6位为地区代码，7-10四位为出生年份，11-12两位为出生月份，13-14两位为出生日期，17位为性别（奇数为男，偶数为女），18位为效验位（如E4-26：用身份证统计1）。

 注：输入身份证号码时，前面必须加“’”号。

1、用身份证号码求出生年月日和性别

如A2单元格存有身份证号码“’130702760103092”，要在B2单元格中求出生年月日和在C2求出性别。

在B2单元中输入求出生年月日的公式：

=IF(LEN(A2)=15,CONCATENATE(19,MID(A2,7,2),"-",MID(A2,9,2),"-",MID(A2,11,2),),CONCATENATE(MID(A2,7,4),"-",MID(A2,11,2),"-",MID(A2,13,2),))

公式设置完成后，则在B2单元格中返回“1976-01-03”。该公式为15和18位两种身份证码的两个判断层次，是新旧两种身份证共用的公式。前一个判断条件中多了一个“19”是为产生四位数年份而特设的。公式输入无误后，向下复制公式即可。

在C2单元格中输入求性别的公式：

=IF(LEN(A2)=15,IF(MOD(MID(A2,15,1),2)=1,”男”,”女”),IF(MOD(MID(A2,17,1),2)=1,”男”,”女”))则在C2单元格返回“男”。

2、用身份证号码直接求年龄
能利用身份证号码求出生年月日，就可以利用身份证号码直接求出年龄来。方法是在原公式中加入求年龄的条件（如E4-27：用身份证统计2）。

 按照前节“1、”所提供的公式，加入求年龄的条件后，公式如：

=ROUND((NOW()-IF(LEN(A2)=15,CONCATENATE(19,MID(A2,7,2),"-",MID(A2,9,2),"-",MID(A2,11,2),),CONCATENATE(MID(A2,7,4),"-",MID(A2,11,2),"-",MID(A2,13,2),)))/365,0)

其性别公式同“1、”

1、作工资表

现虚拟了一张工资表（如E4-28：工资表）。工资表的应发工资及合计中的求和公式不再重复。本节主要说一说交纳所得税的公式设置。假定工资所得税的税率：应发工资>840元时，不交税；>=840<1200元时，交所得税1％；>=1200<2000元时，交所得税3％；>=2000<3000元时，交所得税4％； >=3000<4000元时，交所得税5％；>=4000元时，交所得税6％。

 

设置所得税公式

按照上列的税率系列，E4-29中“I4”是应交“所得税” 单元格，在“I4”设置公式如下：

=IF(H4<840,0,IF(H4<1200,H4*0.01,IF(H4<2000,H4*0.03,IF(H4<3000,H4*0.04,IF(H4<4000,H4*0.05,IF(H4>=4000,H4*0.06))))))

注：IF函数对一个公式判断也是按照一般数学公式的运算规律进行的，如上列公式，公式中只有左括号而没有右括号，说明公式中没有优先的运算条件，是一个从左至右的判断序列，到符合其条件时就产生一个运算结果，后面的条件就忽略了。

本期扣零公式

每次发工资都发零头，很不方便。可以先把零头钱存下到年终（或在下月计入“上期扣零”）处理。这种方法不一定可取，主要是多学一种公式的设置方法。在E4-28中“L4”单元格中设置公式如下：

=H4-I4-J4+K4-INT(H4-I4-J4+K4)

公式中的“INT”是取整函数。公式意为“应发-应扣-取整后的（应发-应扣）”留下零头钱。

2、准备现钞

现在一般发工资都用银行“工资卡”，如果不进“工资卡”而是发现金，就很有必要进行现钞的准备工作（如E4-29：现钞准备）。

 

假定实发工资的位置在“M4”单元格，在“N4、O4、P4、Q4、R4、S4、T4、单元格”中分别设置“100、50、20、10、5、2、1元”现金应发张数的公式：

“100元钞/张”的公式：=INT(M4/100)

“50元钞/张”的公式：=INT((M4-100*N4)/50)

“20元钞/张”的公式：=INT((M4-100*N4-50*O4)/20)

“10元钞/张”的公式：=INT((M4-100*N4-50*O4-20*P4)/10)

“5元钞/张”的公式：=INT((M4-100*N4-50*O4-20*P4-10*Q4)/5)

“2元钞/张”公式：=INT((M4-100*N4-50*O4-20*P4-Q4*10-5*R4)/2)

“1元钞/张”公式：=INT(M4-100*N4-50*O4-20*P4-10*Q4-5*R4-2*S4)

公式设置完成后，选取“N4-T4”使这个单元格区域变黑，再一起向下复制公式，就完成了所有人的各种现钞的应发张数。

各种现钞的应准备总张数，用前面讲过快速求和办法就可一次完成。

按E4-30中合计实发工资为11172元，其中：100元钞/107张、50元钞/6张、20元钞/5张、10元钞/2张、5元钞/7张、2元钞/6张、1元钞/5张。

这是为了说明问题才单独作了（E4-30：现钞准备）表。实际上在工资表之后就可直接进行现钞准备的公式设置，打印时将其列到打印区外就可。

3、工资年终汇总

一年12个月的工资发完后该年终汇总了。工作簿上有“一月到十二月”工作表标签，再增加一张“汇总表”，如基本工资在各月工作表的“D4”单元格，其汇总公式如下：

=SUM(一月:十二月!D4)

基本工资的汇总完成后，接着将公式复制到所有要汇总的项目单元格，即完成全部汇总工作。

注：这种汇总的三维公式设置方法，对任何一种工作簿中的工作表汇总都是适用的。

人民币的中文货币单位，整数为“元”，十分位、百分位为“角、分”，并加写“整”字结尾。可将这些计量单位用公式插入到数字中去。

1、中文货币单位写入

如在“B8”单元格中有数字“1234.56”要在“C8”单元格将这组数字变为“1234元5角6分整”，公式格式：

=TEXT(INT(B8),”G/通用格式”)&”元”&IF(INT(B8*10)-INT(B8)*10=0,””,TEXT(INT

(B8*10)-INT(B8)*10,”G/通用格式”)&”角”)&IF(INT(B8*100)-INT(B8*10)*10=0,””,

TEXT(INT(B8*100)-INT(B8*10)*10,”G/通用格式”)&”分”)&”整”

公式中“TEXT”是将指定数值转换成文本格式的函数。在公式中，小数点以前的整数，可方便地用“INT”函数取整，并在其后连接上“元”，而小数点后面的十分位和百分位数，只好用十分位“*10”和百分位“*100”将小数点向后移，再分别作为字符串来取整提取，以低位数是否为“0”，来判断是替换还是插入“元、角、分、整”，最后再把几个字符串都连接起来。

2、全中文小写

如将上述公式中“G/通用格式”改写为“[DBNum1]G/通用格式”，单元格中的这组数字则变为中文小写的“一千二百三十四元五角六分整”。公式格式：

=TEXT(INT(B8),”[DBNum1]G/通用格式”)&”元”&IF(INT(B8*10)-INT(B8)*10=0,””,

TEXT(INT(B8*10)-INT(B8)*10,”[DBNum1]G/通用格式”)&”角”)&IF(INT(B8*100)

-INT(B8*10)*10=0,””,TEXT(INT(B8*100)-INT(B8*10)*10,”[DBNum1]G/通用格式”)

&”分”)&”整”

3、全中文大写

如将上述公式中“G/通用格式”改写为“[DBNum2]G/通用格式”，单元格中的这组数字则变为中文大写的“壹仟贰佰叁拾肆元伍角陆分整”。公式格式：

=TEXT(INT(B7),”[DBNum2]G/通用格式”)&”元”&IF(INT(B7*10)-INT(B7)*10=0,””,

TEXT(INT(B7*10)-INT(B7)*10,”[DBNum2]G/通用格式”)&”角”)&IF(INT(B7*100)

-INT(B7*10)*10=0,””,TEXT(INT(B7*100)-INT(B7*10)*10,”[DBNum2]G/通用格式”)&”分”)&”整”

注：以上1、2、3例的公式，其变量后所产生的结果是文本而不是数值，不能用作常量数值再次被引用。以上公式及以下中的分散段落，是Word编辑系统造成的，实际情况应是字符紧靠的。

在会计的制单业务中，经常要用到全中文文字及数字大写的。虽然上例“3、”的公式已经接近会计业务专用了，但还应在数字前加上定语和会计专用符号。现举两例：

1、做记账凭证

记账凭证的式样很多。手工填写的记账凭证式样，有从市场采购的，也有各单位自行设计印制的。由计算机程序编制生成的记账凭证，不同计算机公司所做的记账凭证式样也不完全相同。但不管是什么样的记账凭证，必须满足记账所需的全部内容。（E4-30：记帐凭证式样）是一个假设的记账凭证式样。式样不一定正确，主要目的是为了介绍如何建立应用大写中文文字及数字的公式。

（1）自定义会计特殊格式

在A3单元格自定义“附单据0张”的格式：将E2与F2单元格合并，自定义格式”顺序第0号”。自定义方法见第二章、十四。

（2）设置日期格式

将C3与D3单元格合并，并在单元格中设置日期格式。方法是自菜单栏选：格式→单元格→分类→日期，再选→类型→选“1997年3月4日”格式后确定。以后输入日期时，按“2001/9/21”的格式输入后，便自动生成“2001年9月21日”。

 （3）设置会计专用符

选取F6到F10的金额单元格，自菜单栏选：格式→单元格→分类→货币，再从“货币符号”对话框中点击“”，出现菜单后选“￥”会计专用符号。以后在单元格中输入数字时，数字前会自动加上“￥”符号。

在F10单元格输入公式=SUM(F6:F9)

 （4）输入合计大写公式

将A10到E10单元格合并，合并后输入公式：

=”合计人民币（大写）：”&”￥”&TEXT(INT(F10),”[DBNum2]G/通用格式”)&”元”&

IF((INT(F10*10)-INT(F10)*10)=0,””,TEXT(INT(F10*10)-INT(F10)*10,”[DBNum2]G/

通用格式”)&”角”)&IF((INT(F10*100)-INT(F10*10)*10)=0,””,TEXT(INT(F10*100)

-INT(F10*10)*10,”[DBNum2]G/通用格式”)&”分”)&”整”公式设置完成后，在合并的A10到E10单元格内自动生成“合计人民币（大写）：￥壹万贰仟叁佰肆拾伍元陆角玖分整”。应用中，只要F6到F9单元格中任一单元格的数字发生改变，合计数中的大写及小写数字都会随之改变。

 

2、做支出凭证

单在没有报销单据的情况下发生的费用支出，一般采用支出凭单方式进行。支出凭单的实际作用等同于工资表之类的单据。在支出凭单中有领款人签字，且合计是采用的由计算机系统自动生成和大写，并有单位主管的签字，其支出凭单应当是有效的。（E4-31：支出证明样式）中的公式设置方法同（1、），只需按实际情况合并单元格的范围，并在公式中修改引用单元格即可。

（九）求标准差

某校对学生进行仰卧起坐的体育测试，并按年龄、参加人数及不同时间段所达到的次数汇总为频数表（E4-32：仰卧起坐频数表），其数学公式如（如E4-33：标准差的数学公式）。

按照数学公式的要求，如果用手工计算（即便是用计算器）是要用去很多时间的。对此，我们按频数在“C3：N3”单元格区域，参加人数在“B3”单元格，在“O3”单元格求标准差，其公式
为

=SQRT((SUMSQ(C3:N3)-(SUM(C3:N3)^2)/B3)/(B3-1))公式设置完成后，由“O3”向下复制公式即可。

 

（十）学生成绩的计算

下面虚拟了一张学生考试成绩表（如E4-34：学生成绩统计表），按表中的各种不同的运算要求，我们探讨其不同的公式设置。

我们前面已经学过“求和”和“排位”的公式设置方法，下面再对未涉及到的公式介绍如下：

 

1、录取与否统计

我们假定录取条件为>=500分，第一人的成绩合计在“I3”单元格，在“K3”单元格设置公式：

=IF(I3>=500,"录取","不录取")并将公式向下复制。

2、各科成绩等级统计

在“物理”的第一个记录即“L3”单元格设置公式：

=IF(C3<60,"不及格",IF(C3<75,"及格",IF(C3<90,"良好",IF(C3<150,"优秀",IF(C3="*","缺考")))))并将公式向右复制到数学的第一个记录即“Q3”单元格，然后再将公式一同向下复制，即完成所有人的各科成绩等级的统计。

3、不同等级数统计

在“不及格”的第一个记录即“R3”单元格设置公式：=COUNTIF(L3:Q3,"不及格")并将公式向右复制到S3、T3、U3及V3单元格，再对应地将公式中的第二个条件改写为“及格、良好、优秀、缺考”，这时完成从R3到V3的公式设置，再选R3到V3单元格将其变黑，向下复制公式，即完成所有人不同等级数的统计。

我们虚拟了一张《月亮弯（集团）干部花名册》（工作表标签为《干部花名册》如（E4-35：干部花名册），共涉及56个人的统计，图样仅是其中的一小部分。

 1、计算年龄及工龄

因该干部花名册中的年龄和工龄只有年和月的记录，不能使用以前介绍的计算方法。现就名册中有关年龄的工龄的计算探讨如下：

为计算年龄和工龄，在“A1、B1、C1”单元格中分别定义了“年月截止期、年、月”。如年月截止期有更改，干部花名册中的年龄和工龄会自动改变。需要将年龄和工龄统计到何时，只要更改年月截止期就可。

表中I5是统计年龄第一单元格，公式如：

=($B$1-G5)+ROUND(($C$1-H5)/12,0)公式设置后向下复制

表中L5是统计工龄第一单元格，公式如：

=($B$1-J5)+(INT(($C$1-K5)/12)>0.5)公式设置后向下复制

两个公式中，使用了ROUND四舍五入函数和INT取整函数。目的是为了多探索两种公式设置方法。

2、做干部实力统计表

现在另作一张工作表，把（E4-35干部花名册）中所涉及到的内容都统计出来（如E4-36：干部情况统计表）。

 下面就有关公式设置作一些介绍：

干部人数统计：建一张《弯月亮（集团）领导干部情况统计表》（简称“实力统计表），在“A5”单元格设置公式：

=COUNTA(干部花名册!B4:B199)返回干部总人数。

职务统计：其中总经理统计，在实力统计表“B5”单元格设置公式

=COUNTIF(干部花名册!$C$4:$C$199,"总经理")将公式向右复制到行政主管，并将公式中第三个条件逐一改为“副总经理”、“经理助理”、“财务主管”、“行政主管”即可。

性别、民族、政治面貌、学历、职称、及籍贯统计设置公式的方法，均与职务统计设置公式的方法相同。

平均年龄的统计：在“I13”单元格求平均年龄，公式如：

=AVERAGEA(干部花名册!H4:H199)

各种比例的计算：如在B6单元格计算总经理占总人数的比例，公式如：

=B5/$A$5

为使复制公式时引用单元格不变，公式中对干部人数所在的A5单元格，采用绝对引用法。

年龄段的统计：假定将年龄分为“<=45岁、>=46到<56岁、>=56到=60岁”的三个年龄段。

<=45岁，公式设在“F13”单元格，公式如

=COUNTIF(干部花名册!$H$4:$H$199,"<=45")

>=46到<56岁的统计，公式设在“G13”单元格，公式如

=COUNTIF(干部花名册!$H$4:$H$199,"<=56")-F13

>=56到=60岁的统计，公式设在“H13”单元格，公式如

=COUNTIF(干部花名册!$H$4:$H$199,"<=60")-SUM(F13:G13)

其工龄段的统计方法相同。

管理库房，并不是很复杂的工作，只要建立一个工作簿，并在这个工作簿中建立好“进货、发放及库存”三张工作表（如E4-37：管库三表），只要随时将进货及发放数量登记到表内，就可从库存工作表看出共进了不同品名的货是多少、共发不同品名的货是多少及库存的情况。

 1、建进货工作表

进货工作表只是一种推荐格式，表中无任何公式。对于品名单元列的填写，相同的货物可无限次重复出现，但对相同货物名称必须是一致的，否则会影响库存表运算（如E4-38:进货表）。

 2、建发放工作表

发放（或称“销售”）工作表也是一种推荐格式，表中无任何公式。其填写要求同进货工作表（如E4-39：发货表）。

 3、建库存工作表

库存工作表按（E4-40：库存表）的样表要求建立。建好工作表后头后，从工作表A列的A3单元格开始，向下输入所有的货物名称，再从B3单元格开始对应A列的货物名称建立进货数量的公式。公式格式如下：

 

（1）设进货数量公式

在B3单元格的公式“=SUMIF(进货!$B:$B,A3,进货!$D:$D)”

公式建立完成后，向下复制公式。如货物名称有增加，可随时向下复制公式。

公式中对“进货”工作表的B列和D列，采用的是“$B:$B”及“$D:$D”绝对引用格式。意为在对“进货”工作表的B列和D列单元格输入文字及数字时，向下输入到任一单元格，都会纳入公式的运算范围。

公式中的第二个查找条件A3，是按照“库存”工作表中“A3复印纸”的名称，去查找“进货”工作表中B列的“A3复印纸”，凡符合条件者即到“进货”工作中的D列去查找对应数据相加。从“库存”工作表的B3单元格中，可以看出，A3复印纸进货数量650是两次进货的和；从“库存”工作表的B6单元格中也可看出，墨水进货数量224也是两次进货的和。如果公式中使用的是加双引号的”A3”，就成了对“进货”工作表“$B:$B”范围中去查找”A3”的条件了。

公式中的第三个查找条件，也必须在“$D:$D”前加上“进货”工作表的标签名。

（2）设发放数量公式

在发放数量的C3单元格设公式“=SUMIF(发放!$C:$C,A3,发放!$E:$E)”

公式设置后，向下复制。如货物名称有增加，可随时向下复制公式。

对公式中要说明的问题，在（1）中都写清楚了。

（3）设当前库存量公式

在当前库存量的D3单元设公式“=B3-C3”

公式设置后，向下复制公式。

（4）规定库存量

E列是输入规定（或“最小”）库存量的单元格区域。在实际工作中，库存量多少最合适，是工作经验的估计数。也可以按领导规定的库存量去填写。

（5）设进货提示公式

在进货提示的F3单元格设公式“=IF(D3

公式设置后，向下复制公式。以后，哪些品种的货物该进货，都会自动提示。

1、去掉最高、最低分后求和

如果以《例表一》中田三七的得分为例，其去掉一个最高分、去掉一个最低分后的求和公式为：

=(SUM(B3:J3)-MAX(B3:J3)-MIN(B3:J3))

公式设置完成后，向下复制公式即可。
公式中的“SUM”函数，用来计算单元格区域中所有数值的和，“MAX”函数，用来返回一数组中的最大值，“MIN” 函数，用来返回一数组的最小值。即对单元格区域求和后，再减去一个最大数值及一个最小数值。

 

2、去掉最高、最低分后求平均值

如果以《例表二》中田三七的得分为例，其去掉一个最高分、去掉一个最低分后的平均值的公式为：

=(SUM(B3:J3)-MAX(B3:J3)-MIN(B3:J3))/(COUNT(B3:J3)-2)

公式设置完成后，向下复制公式即可。
公式的前一部份与上表相同，在公式后面增加一个除“COUNT”函数，用以计算包含数字的单元格以及参数列表中的数字的个数，并在总的个数中减2。

 

1、数据的限定性

我们知道，年龄不足18周岁不够招工条件，年满60岁就到了退休年龄，为此我们在工作表中设定数据有效条件，遇到限定数据条件时及时提醒注意。

假如在一个工作表的“E2：E20”单元域，进行限定年龄的设置，可点击菜单栏“数据→有效性”，请参照“数据有效性”的对话框（见E4-41：数据有效性菜单1）中的所有设置。接着点击“数据有效性”菜单上的“出错警告”命令，在“样式”中选“警告”，在“标题”栏中输入“请检查一下：”，在“错误信息”栏中输入“是否不够招工年龄或已到退休年龄！”，确定退出。

 

在定义过的单元格域中，如果输入了小于18或大于60的数字时，会立即出现图（E4-42：请检查的提示）的提示。

 

2、数据的唯一性

我们知道，身份证号码、车号、驾驶执照号等都具有唯一性，且身份证号码还有15位和18位两种版本。若在输入身份号码时，输入了相同的身份证号码、或输入的位数不足或超出身份证号码，就立即提出警告，不就可以防止输入时的错误吗。假定我们要在F列输入身份证号码，选定F2，并点击菜单栏“数据→有效性”，在出现的“数据有效性”（E4-43：数据有效性菜单2）对话框中选“设置”，在“允许”菜单中选“自定义”，在“公式”栏中输入公式:“=AND(COUNTIF(F:F,F2)=1,OR(LEN(F2)=15,LEN(F2)=18))”，接着点击“数据有效性”对话框中的“出错警告”，在“标题”文字框中输入“数据输入错误”，在“错误信息”文字框中输入“请检查输入数据的位数及唯一性是否错误！”确定退出。并将公式向F3以下复制。

输入身份证号码时，先输入一个半角“’”号，再输入身份证号码，如果输入的身份证号码位数不足或号码相同，就会出现一个提示，如（E4-44:数据输入错误提示）。