电子表格公式技巧.doc

电子表格公式技巧
学习 2010-01-02 01:09:18 阅读4080 评论2  字号：大中小 订阅
1.公式技巧
1.1 在单元格中显示工作表和工作簿的名称
在单元格中显示工作表的名称，有两种要领：
（1）建立如下自定义函数：
Function bookname()
bookname = ActiveSheet.Name
End Function
运用时在单元格中输入公式：=bookname()，即可返回当前工作簿的标签名字。
（2）自定义名称的要领。定义如下名称：
点击[插入]à[名称]à[定义]，名称的定义为“T_B”，引用位置输入：“=replace(getdocument(1),1, find("]",get.document(1)),)&t(now())”，在单元格输入“=T_B”就可以显示当前表名。值得留心的是，返回的工作表名称随着工作表名称的变化而变化。在此引用中，GET.DOCUMENT()是宏表函数，当数据变动时不能自动计算，now()是易失性函数，任何变动都会强制计算，宏表函数所以加上now()就可以自动重算了，T()用来将now()产生的数值转化为空文本。
在单元格中显示工作簿的名称，运用系统函数Cell()：
在单元格中输入公式：=Cell("filename") ，就会返回该工作簿和工作表的名字（包括绝对路径名），然后根据自己的须要运用一些文本处理函数执行 处理即可。
留心：该函数必须在工作簿已经保存的情况下才生效。
1.2 基本判断单元格最后一位是数字还是字母
在有些情况下，须要判断单元格的最后一位是数字还是字母，可以用下面三个公式之一：
（2）=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母")，直接返回数字或字母。其中“--”的意思是将文本型数字转化为数值以便参与运算。
（3）=IF(ISERR(RIGHT(A1)*1),"字母","数字")，直接返回数字或字母。
1.3 如何 求出一个人到某指定日期的周岁？
=DATEDIF(起始日期,结束日期,"Y")
1.4 判断单元格中存在特定字符
假如判断A栏里能不能存在"$"字符，有则等于1，没有则等于0，公式为：
=IF(COUNTIF(A:A,"*$*")>0,1,0)。
1.5 计算某单元格所在的列数
通常情况下，A列为第1列，AA列为27列。可以在A1单元格中输入列标，通过下列公式计算出任何列标的列数：
=COLUMN(INDIRECT(A1&"1"))。例如：“FG”列为第163列。
1.6 DATEDIF函数的作用
DATEDIF函数计算两个日期之间的天数、月数或年数。提供此函数是为了与 Lotus 1-2-3 兼容。
语法：DATEDIF(start_date,end_dateunit)
Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。日期有多种输入要领：带引号的文本串（例如 "2001/1/30"）、系列数（例如，如果运用 1900 日期系统则 36921 代表 2001 年 1 月 30 日）或其他公式或函数的结果（例如，DATEVALUE("2001/1/30")）。
End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。
Unit 为所需信息的返回类型。
Unit返回"Y"时间段中的整年数。"M"时间段中的整月数。"D"时间段中的天数。"MD"start_date 与 end_date 日期中天数的差。忽略日期中的月和年。"YM"start_date 与 end_date 日期中月数的差。忽略日期中的日和年。"YD"start_date 与 end_date 日期中天数的差。忽略日期中的年。
说明：Microsoft Excel 按顺序的系列数保存日期，这样就可以对其执行 计算。如果工作簿运用 1900 日期系统，则 Excel 会将 1900 年 1 月 1 日保存为系列数 1。而如果工作簿运用 1904 日期系统，则 Excel 会将 1904 年 1 月 1 日保存为系列数 0，（而将 1904 年 1 月 2 日保存为系列数 1）。例如，在 1900 日期系统中 Excel 将 1998 年 1 月 1 日保存为系列数 35796，因为该日期距离 1900 年 1 月 1 日为 35795 天。请查阅 Microsoft Excel 如何 存储日期和时间。
Excel for Windows 和 Excel for Macintosh 运用不同的默认日期系统。有关细致信息，请参阅 NOW。示例
DATEDIF("2001/1/1","2003/1/1","Y") 等于 2，即时间段中有两个整年。
DATEDIF("2001/6/1","2002/8/15","D") 等于 440，即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之间有 440 天。

DATEDIF("2001/6/1","2002/8/15","YD") 等于 75，即在 6 月 1 日与 8 月 15 日之间有 75 天，忽略日期中的年。
DATEDIF("2001/6/1","2002/8/15","MD") 等于 14，即开始日期 1 和结束日期 15 之间的差，忽略日期中的年和月。
1.7 在一个单元格中指定字符出现的次数
例如在A1单元格中有“abcabca”字符串，求“a”在单元格A1内出现次数，用下列公式：
=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))。
1.8 日期形式的转换
我们在有些情况下写日期会用“20060404”表示，如何 转换成“2006-04-04”的标准日期格式，用下面的两个公式之一（假定在A1单元格中有原始日期）：
=TEXT(A1,"0000-00-00")
=TEXT(A1,"????-??-??")。
也可以运用以下公式，转换成“2006-4-4”的格式。
=LEFT(A1,4)&SUBSTITUTE(RIGHT(A1,4),0,"-")。
反之，如何 把“2006年4月4日”转换成“20060404”？可以运用 下面的公式之一（假定在A1单元格中有原始日期）：
=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00" )
YEAR(A1)&IF(MONTH(A1)<10,"0"&MONTH(A1),MONTH(A1))&IF(DAY(DAY(A1)<10),"0"&DAY(A1),DAY(A1))
=TEXT(A1,"yyyymmdd")。
也可以直接自定义格式：yyyymmdd。
1.9 用“定义名称”的要领突破IF函数的嵌套限定
Excel中的IF()函数的一个众所周知的限定是嵌套不能超过7层。例如下面的公式是不正确的，因为嵌套层数超过了限定。
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))
通常的要领会考虑用VBA代替。但是也可以可以通过对公式的一部分”定义名称”来处理这种限定定义一个名叫”OneToSix”的名称, 里面包括公式：
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))
再定义另一个名叫”SevenToThirteen”的名称，里面包括公式：
=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,IF(Sheet1!$A$4=13,130,"NotFound")))))))
最后单元格中输入下面的公式：
=IF(OneToSix,OneToSix,SevenToThirteen)
1.10 动态求和
举一个基本例子：例如对于A列，求出A1到当前单元格行标前面一行的单元格中的数值之和，更直接地说，如果当前单元格在B17，那么求A1:A16之和。运用 下面的公式：
=SUM(INDIRECT("A1:A"&ROW()-1))。
1.11 COUNTIF函数的16种公式配置（设DATA为区域名称）
（1）返加包含值12的单元格数量：=COUNTIF(DATA,12)
（2）返回包含负值的单元格数量：=COUNTIF(DATA,"<0")
（3）返回不等于0的单元格数量：=COUNTIF(DATA,"<>0")
（4）返回大于5的单元格数量：=COUNTIF(DATA,">5")
（5）返回等于单元格A1中内容的单元格数量：=COUNTIF(DATA,A1)
（6）返回大于单元格A1中内容的单元格数量：=COUNTIF(DATA,“>”&A1)
（7）返回包含文本内容的单元格数量：=COUNTIF(DATA,“*”)
（8）返回包含三个字符内容的单元格数量：=COUNITF(DATA,“???”)
（9）返回包含单词"GOOD"(不分大小写)内容的单元格数量：=COUNTIF(DATA,“GOOD”)
（10）返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量：=COUNTIF(DATA,“*GOOD*”)
（11）返回包含以单词"AB"(不分大小写)开头内容的单元格数量：=COUNTIF(DATA,“AB*”)
（12）返回包含当前日期的单元格数量：=COUNTIF(DATA,TODAY()）
（13）返回大于平均值的单元格数量：=COUNTIF(DATA,">"&AVERAGE(DATA))
（14）返回平均值上面超过三个标准误差的值的单元格数量：=COUNTIF(DATA,“>"&AVERAGE(DATA)+STDEV(DATA)*3)
（15）返回包含值为或-3的单元格数量：=COUNTIF(DATA,3）+COUNIF(DATA,-3)
（16）返回包含值逻辑值为TRUE的单元格数量：=COUNTIF(DATA,TRUE)
1.12 计算一个日期是一年中的第几天
例如2006年7月29日是本年中的第几天？在一年中，显示是第几天用什么函数呢？假定A1中是日期，运用 下列公式：
=A1-DATE(YEAR(A1),1,0)，将单元格格式配置为常规，返回210，即2006年7月29日是2006年的第210天。
1.13 如何 用公式求出最大值所在的行？
如A1：A10中有10个数，如何求出最大的数在哪个单元格？
=MATCH(LARGE(A1:A10,1),A1:A10,0)
=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)
=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)
1.14 在Excel中的绝对引用与相对引用之间切换
在Excel中建立公式时，该公式可以运用相对引用，即相对于公式所在的位置引用单元；也可以运用绝对引用，即引用特定位置上的单元。引用由所在单元格的“列的字母”和“行的数字”组成，绝对引用由在“列的字母”和“行的数字”前面加“$”表示，例如，$B$1是对第一行B列的绝对引用。公式中还可以混合运用相对引用和绝对引用。可以运用 F4切换相对引用和绝对引用，选中包含公式的单元格，在公式栏中选择想要改动的引用，按F4键可以执行 切换。
1.15 在Excel公式和结果之间高速切换
在excel工作表中输入计算公式时，可以运用 “Ctrl+`（中音号）”键来决定显示或潜藏公式，可让储存格显示计算的结果，还是公式本身。
1.16 如果某列中有大于0和小于0的数，将小于0数字所在的行自动删除
假定在A1-A6中有大于0和小于0的数，可以用下面的VBA程序实现：
for i=6 to 1 step -1
if cells(i,1)<0 then rows(i).Delete
next i
1.17 奇数行和偶数行求和
有时候须要奇数行和偶数行单独求和，例如要求A列第1行至1000行中奇数行之和，运用 公式=SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))，要求这些行中偶数行之和，运用 公式=SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))。
1.18 用函数来获取单元格地址
在复杂的计算中，往往要获知单元格的地址，可以用函数=ADDRESS(ROW(),COLUMN())获得当前单元格的地址。
1.19 求一列中某个特定的值对应的另外列的最大或最小值
为了直观起见，举一个基本的例子：例如在A1:A10中有若干台计算机、打印机、传真机等物品的名称，在B1:B10中有上述设备对应的价格，求“计算机”对应的最低价格。可以用公式：
=min(if(a1:a10="计算机",b1:b10))，输入该公式后按Ctrl+Shift+Enter完成。
1.20 自动记录数据录入时间
运用 VBA实现，建立一个Time.xls文档，输入以下VBA代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then
 Exit Sub
Else
 Target.Offset(0, 1) = Now
End If
End Sub
1.21 如果一个单元格中既有数字又有字母如何提取其中的数字呢
Function getnumber(rng As String) As String
Dim mylen As Integer
Dim mystr As String
mylen = Len(rng)
For I = 1 To mylen
 mystr = Mid(rng, I, 1)
 If Asc(mystr) >= 48 And Asc(mystr) <= 57 Then
 getnumber = getnumber & mystr
 End If
Next I
End Function
1.22 Excel数组的使用
数组就是单元的集合或是一组处理的值集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。数组的元素可多达6500个。
（1）了解数组
首先我们通过多个例子来说明数组是如何 工作的。我们可以从图中看到，在“B”列中的数据为销售量，在“C”列中的数据是销售单价，要求计算出每种产品的销售额和总的销售金额，一般的做法是计算出每种产品的销售额，然后再计算出总的销售额。但是如果我们改用数组，就可以只键入一个公式来完成这些运算。
输入数组公式的步骤为：
选定要存入公式的单元格，在本例中我们选择“D4”单元格。输入公式=SUM(B2:B4*C2:C4)，但不要按下[Enter]键（输入公式的要领和输入普通的公式一样），按下[Shift]+[Ctrl]+[Enter]键。我们就会看到在公式外面加上了一对大括号“{}”，如图 7-36所示。
在单元格“D”中的公式“=SUM(B2:B4*C2:C4)”, 表示“B2: B4”范围内的每一个单元格和“C2：C4”内相对应的单元格相乘，也就是把每个地区的销售量和销售单价相乘，相乘的结果共有3个数字，每个数字代表一个地区的销售额，而“SUM”函数将这些销售额相加，就得到了总的销售额。
下面我们再以运用数组计算3种产品的销售额为例，来说明如何 产生多个计算结果。其操作流程如下：
 (1) 选择“D2：D4”单元格区域，该区域中的每个单元格保存的销售金额。如图7-37所示。
 (2) 在“D2”单元格中输入公式“=B2:B4*C2:C4”（不按[Enter]键）按下[Shift]+[Ctrl]+[Enter]”键，我们就可以从图7-38中看到执行后的结果。同时我们可以看到“D2”到“D4”的格中都会出现用大括弧“{ }”框住的函数式，这表示“D2” 到“D4”被当作一个单元格来处理，所以不能对“D2”到“D4”中的任一格作任何单独处理，必须针对整个数组来处理。
 （2）运用数组常数
我们也可以在数组中运用常数值。这些值可以放在数组公式中运用区域引用的地点。要在数据公式中运用数组常数，直接将该值输入到公式中并将它们放在括号里。例如，在图7-39中，就运用了数组常数执行 计算。
常数数组可以是一维的也可以是二维的。一维数组可以是垂直的也可以是水平的。在一维水平数组中的元素用逗号分开。下面是一个一维数组的例子。例如数组：{10,20,30,40,50}。在一维垂直数组中的元素用分号分开。在下面的例子是一个6×1的数组，{100;200;300;400;500;600}。
对于二维数组，用逗号将一行内的元素分开，用分号将各行分开。下一个例子是“4 ×4”的数组（由4行4列组成）：{100,200,300,400;110, … … ;130,230,330,440}。
留心：不可以在数组公式中运用列出常数的要领列出单元引用、名称或公式。例如：{2*3,3*3,4*3}因为列出了多个公式，是不能用的。{A1,B1,C1}因为列出多个引用，也是不能用的。不过可以运用一个区域，例如{A1:C1}。
对于数组常量的内容，可由下列准则构成：
数组常量可以是数字、文字、逻辑值或不正确值。
数组常量中的数字，也可以运用整数、小数或科学记数格式。
文字必须以双引号括住。
同一个数组常量中可以含有不同类型的值。
数组常量中的值必须是常量，不可以是公式。
数组常量不能含有货币符号、括号或百分比符号。
所输入的数组常量不得含有不同长度的行或列。
（3）数组的编辑
数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能单独编辑。在编辑数组前，必须先选取整个数组。
选取数组的步骤为：
(1) 选取数组中的任一单元格。
 (2) 在“编辑”菜单中选择“定位”命令或者按下[F5]键，出现一个“定位”对话框。按下“定位条件”按钮，出现一个定位条件对话框，如图7-40所示。
选择“当前数组”选项，最后按下“确定”按钮，就可以看到数组被选定了。
编辑数组的步骤为:
选定要编辑的数组，移到数据编辑栏上按[F2]键或单击左键，使代表数组的括号消散，之后就可以编辑公式了。编辑完成后，按下[Shift] +[Ctrl] + [Enter]键。
若要删除数组，其步骤为：选定要删除的数组，按[Ctrl]+[Delete]或选择编辑菜单中的“清理 ”。
（4）数组的扩充
在公式或函数中运用数组常量时，其它运算对象或参数应该和第一个数组具有相同的维数。必要时，Microsoft Excel 会将运算对象扩展，以符合操作须要的维数。每一个运算对象的行数必须和含有最多行的运算对象的行数一样，而列数也必须和含有最多列数对象的列数一样。
例如: = SUM({1,2,3}+{4,5,6}）内的第一个数组为1×3,得到的结果为1+4、2+5和3+6的和,也就是21。如果将公式写成 = SUM({1,2,3}+4}）,则第二个数据并不是数组，而是一个数值，为了要和第一个数组相加，Excel 会自动将数值扩充成1 ×3 的数组。运用 =SUM({1,2,3}+{4,4,4})做计算，得到的结果为1+4、2+4和3+4的和, 即18。
将数组公式输入单元格区域中时，所运用的维数应和这个公式计算所得数组维数相同。这样，Microsoft Excel 才能把计算所得的数组中的每一个数值放入数组区域的一个单元格内。
如果数组公式计算所得的数组比选定的数组区域还小，则 Microsoft Excel会将这个数组扩展，以便将它填入整个数组区域内。例如：={1,2;3,4}*2扩充后的公式就会变为={1,2;3,4}*{2,2;2,2}，则相应的计算结果为“2,4,6,8”。再如：输入公式={1,2;3,4}*{2,3}扩充后的公式就会变为={1,2;3,4}*{2,3;2,3} ，则相应的计算结果为“2,6,6,12”。
如果 Microsoft Excel 将一个数组扩展到可以填入比该数组公式大的区域内，而没有扩大值可用的单元格内，这样就会出现#N/A不正确值。例如：={1,2;3,4}={1,2,3} 扩充后的公式就会变为={1,2,#N/A;3,4,#N/A}*{1,2,#/A;1.2.#N/A} ，而相应的计算结果为“2,4,#N/A,4,6,#N/A”。
如果数组公式计算所得的数组比选定的数组区域还要大，则超过的值不会出现在工作表上。
1.23 数组的使用
（1）数组公式的实现要领：
其实这些都是数组公式，数组公式的输入要领是将公式输入后，不要直接按回车键（Enter），而是要同时按Ctrl+Shift+Enter，这时计算机自动会为你添加“{ }”的。
在论坛上，为了告诉大家这是数组公式，故在公式的头尾都加上了“{ }”。如果不注意按回车了，可以用鼠标点一下编辑栏中的公式，再按Ctrl+Shift+Enter。
编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消散，然后编辑公式，最后按Ctrl＋Shift＋Enter键。选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。
数组常量的运用数组公式中还可运用数组常量，但必须自己键入花括号“{ }”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值。
2、数组公式的原理：
数组公式，说白了就是同时对一组或几组数同时处理，然后得到须要的答案。运用数组公式的最主要的原理是数于数之间一一对应。
1、假设要将A1：A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。很自然地就会想到运用公式：=ROUND（A1，2）＋ROUND（A2，2）＋…＋ROUND（A50，2）。或者添加ROUND辅助列（A1=ROUND（A1，2）），然后对辅助用SUM函数合计（=SUM（A1：A50））。
如果用数组公式就不要这么麻烦，公式为：
{=SUM（ROUND（A1：A50，2））}，它的意思即为在数组A1：A50用ROUND函数执行 二位小数的四舍五入，然后执行 合计。
2、假设一题为A1：A10区域中为商品单价，B1：B10为对应的销售数量，须要统计总销售额，常规做法须要添加辅助列C列，在C列中计算出C1：C10的每个单价的销售额（C1=A1*B1），然后执行 SUM合计（C11=SUM（C1：C10））。
而数组公式为：
{=SUM（A1：A10*B1：B10）}
3、留心：关于常数项的数组可以直接手工添加{ }，如此公式 = SUM({1,2,3}+{4,5,6}），这也是数组公式的一种形式。
须要统计如下图所示销量的频率分布，即分别统计销量在5000以下、5000到10000、10000到50000以及大于50000的销售点数量
a2b2C2
销售点 销售额 分段点
城北001 4100 5000
城北002 15890 10000
城南001 8700 50000
城南002 25900
城南003 5800
城东001 15300
城东002 38000
城东003 9800
城西001 56000
城西002 72050
城中001 130000
城中002 60400
城中003 48700
步骤：
"1、打造如上图所示的表格
2、选中单元格G7：G10，直接输入公式：=FREQUENCY（B4：B14，c4：c6）
3、输入公式后，按CTRL+SHIFT+ENTER键结束
"

类型 日期 单价 销售数量
A 2005-6-15 1000 10
B 2005-6-20 1000 15
B 2005-7-1 4000 10
C 2005-7-10 4000 11
B 2005-8-15 9000 13
C 2005-8-20 9000 15
A 2005-9-30 1000 14
A 2005-10-10 1000 20
B 2005-10-15 4000 25
类型从B1格开始

计算B产品8月份销量
13{=SUM(IF(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8),($E$2:$E$10),0))}
13 {=SUM(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8)*($E$2:$E$10))}
计算A产品和B产品的销量
107 {=SUM(IF(($B$2:$B$10="A")+($B$2:$B$10="B")E$2:$E$10),0))}
107 {=SUM((($B$2:$B$10="A")+($B$2:$B$10="B"))*($E$2:$E$10))}
计算8月份前不包括B产品销量和8月后不包括C产品销量
49 {=SUM(IF(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C")),$E$2:$E$10))}
49 {=SUM(IF(((MONTH($C$2:$C$10)<8)-($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)-(B$2:$B$10="C")),$E$2:$E$10))}
49 {=SUM(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C"))*$E$2:$E$10)}

以上公式中*的意思为AND,+的意思为OR,-的意思为<> "不等于"

1.24 求一个单元格数值中的最大数字和个数字之和
我们平时都是对不同单元格之间的数字执行 计算，但是在一个单元格内部，各数字之间有什么联系？这是一个很有创新意识的命题。例如A1中的数字为389732，求其中最大的数字9，求这和6个数字之和为32。
（1）求其中最大的数字，运用 数组公式：
{=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)}
先输入=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)，再按Ctrl+Shift+Enter。
（2）求其中数字之和，运用 下面的公式：
=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)
1.25 逻辑函数的非逻辑表现
例如，求取范围Data中小于0或大于5的数值之和:
 正确用法：
 {=SUM(IF((Data<0)+(Data>5),Data))}
不正确用法：
 {=SUM(IF(OR(Data<0,Data>5),Data))}
1.26 在EXCEL的数组公式中ROW函数的用法
在EXCEL的数组公式中，ROW()是一个非常有用的函数，现在举个例子来说明。
（1）返回一列中最后一个数值
{=INDEX(A:A,MAX(ROW(A1:A100)*(A1:A100<>"")))}
在这个公式中用ROW函数返回A1:A100<>""即A1格到A100中不为空的单元格，它是一组数据，然后用MAX确定最大的一个行号，即最后一格不为空的单元格，然后用INDEX，来返回A1到A100中A列最大行号的那个数据。
（2）同理如果要返回一行中最后一个数值则为
{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))}
（3）下面出一个小题目，如果有兴趣想学数组的可以试一下，返回A列100行中最后一个有数值的行号的公式是什么？
{=MAX(IF(A1:A100<>"",ROW(A1:A100),""))}
1.27 返回最大值的行号和地址
返回最大值的行号：
{=min(if(A1:A100=max(A1:A100),row(A1:A100),"")}
返回最大值的地址：
{=ADDRESS(MIN(IF(A1:A100=max(A1:A100),ROW(A1A100),"")),COLUMN(A1:A100))}
{=MAX(IF((A1:A100<>"")*ISNUMBER(A1:A100),ROW(A1:A100),""))}
1.28 Excel多见不正确及处理办法
经常用Excel可能都会遇到一些不正确值信息，如：# N/A！、#VALUE！、#DIV/O！等等，出现这些不正确的原由有很多种，如果公式不能计算正确结果，Excel将显示一个不正确值，例如，在须要数字的公式中运用文本、删除了被公式引用的单元格，或者运用了宽度不足以显示结果的单元格。以下是几种多见的不正确及其处理要领。
（1）#####！
原由：如果单元格所含的数字日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####！不正确。
处理要领：如果单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。如果运用的是1900年的日期系统，那么Excel中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#####！不正确。如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。
（2）#VALUE!
当运用不正确的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生不正确值#VALUE!。
原由一：在须要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
处理要领：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如：如果单元格A1包含一个数字，单元格A2包含文本"学籍"，则公式"=A1+A2"将返回不正确值#VALUE!。可以用SUM工作表函数将这两个值相加（SUM函数忽略文本）：=SUM（A1:A2）。
原由二：将单元格引用、公式或函数作为数组常量输入。
处理要领：确认数组常量不是单元格引用、公式或函数。
原由三：赋予须要单一数值的运算符或函数一个数值区域。
处理要领：将数值区域改为单一数值。修改数值区域，使其包含公式所在的数据行或列。
（3）#DIV/O!
当公式被零除时，将会产生不正确值#DIV/O!。
原由一：在公式中，除数运用了指向空单元格或包含零值单元格的单元格引用（在Excel中如果运算对象是空白单元格，Excel将此空值当作零值）。
处理要领：修改单元格引用，或者在用作除数的单元格中输入不为零的值。
原由二：输入的公式中包含明显的除数零，例如：=5/0。
处理要领：将零改为非零值。
（4）#NAME?
在公式中运用了Excel不能识别的文本时将产生不正确值#NAME?。
原由一：删除了公式中运用的名称，或者运用了不存在的名称。
处理要领：确认运用的名称确实存在。选择菜单"插入""名称""定义"命令，如果所需名称没有被列出，请运用 "定义"命令添加相应的名称。
原由二：名称的拼写不正确。
处理要领：修改拼写不正确的名称。
原由三：在公式中运用标志。
处理要领：选择菜单中"工具""选项"命令，打开"选项"对话框，然后单击"重新计算"标签，在"工作薄选项"下，选中"接受公式标志"复选框。
原由四：在公式中输入文本时没有运用双引号。
处理要领：Excel将其解释为名称，而不理会用户准备将其用作文本的想法，将公式中的文本括在双引号中。例如：下面的公式将一段文本"总计："和单元格B50中的数值合并在一起：="总计："&B50
原由五：在区域的引用中缺少冒号。
处理要领：确认公式中，运用的所有区域引用都运用冒号。例如：SUMA2:B34）。
（5）#N/A
原由：当在函数或公式中没有可用数值时，将产生不正确值#N/A。
处理要领：如果工作表中某些单元格暂时没有数值，请在这些单元格中输入"#N/A"，公式在引用这些单元格时，将不执行 数值计算，而是返回#N/A。
（6）#REF!
当单元格引用无效时将产生不正确值#REF！。
原由：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。
处理要领：修改公式或者在删除或粘贴单元格之后，立即单击"撤消"按钮，以恢复工作表中的单元格。
（7）#NUM！
当公式或函数中某个数字有疑问时将产生不正确值#NUM！。
原由一：在须要数字参数的函数中运用了不能接受的参数。
处理要领：确认函数中运用的参数类型正确无误。
原由二：运用了迭代计算的工作表函数，例如：IRR或RATE，并且函数不能产生有效的结果。
处理要领：为工作表函数运用不同的原始值。
原由三：由公式产生的数字太大或太小，Excel不能表示。
处理要领：修改公式，使其结果在有效数字范围之间。
（8）#NULL！
当试图为两个并不相交的区域指定交叉点时将产生不正确值#NULL！。
原由：运用了不正确的区域运算符或不正确的单元格引用。
处理要领：如果要引用两个不相交的区域，请运用联合运算符逗号（，）。公式要对两个区域求和，请确认在引用这两个区域时，运用逗号。如：SUM（A1:A13，D12:D23）。如果没有运用逗号，Excel将试图对同时属于两个区域的单元格求和，但是由于A1:A13和D12:D23并不相交，所以他们没有共同的单元格。
1.29 金额大写的转换
假设A1单元格为原始数据，即小写数字。
公式法一：
=IF(A1=0,"零元整",IF(A1<0,"负",)&IF(INT(ABS(A1)),TEXT(INT(ABS(A1)),"[dbnum2]")&"元",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[dbnum2]")&"角",IF(INT(ABS(A1))=ABS(A1),,"零"))&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)* 10,) , TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),"[dbnum2]")&"分","整"))
公式法二：
=IF(A1<0,"负",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"元"&IF(ROUND(A1,3) =ROUND(A1,),"整",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"角"&IF (RO