2022年EXCEL公式技巧汇总 .pdf

《2022年EXCEL公式技巧汇总 .pdf》由会员分享，可在线阅读，更多相关《2022年EXCEL公式技巧汇总 .pdf（14页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1.公式技巧1.1 在单元格中显示工作表和工作簿的名称在单元格中显示工作表的名称，有两种要领：（1）建立如下自定义函数：Function bookname()bookname=ActiveSheet.Name End Function 运用时在单元格中输入公式：=bookname()，即可返回当前工作簿的标签名字。（2）自定义名称的要领。定义如下名称：点击 插入名称定义，名称的定义为“T_B”，引用位置输入：“=replace(get.document(1),1,find(,get.document(1),)&t(now()”，在单元格输入“=T_B”就可以显示当前表名。值得留心的是，返回的工

2、作表名称随着工作表名称的变化而变化。在此引用中，GET.DOCUMENT()是宏表函数，当数据变动时不能自动计算，now()是易失性函数，任何变动都会强制计算，宏表函数所以加上now()就可以自动重算了，T()用来将 now()产生的数值转化为空文本。在单元格中显示工作簿的名称，运用系统函数Cell()：在单元格中输入公式：=Cell(filename)，就会返回该工作簿和工作表的名字（包括绝对路径名），然后根据自己的须要运用一些文本处理函数执行处理即可。留心：该函数必须在工作簿已经保存的情况下才生效。1.2 基本判断单元格最后一位是数字还是字母在有些情况下，须要判断单元格的最后一位是数字还是

3、字母，可以用下面三个公式之一：（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单元格中

4、输入列标，通过下列公式计算出任何列标的列数：=COLUMN(INDIRECT(A1&1)。例如：“FG”列为第163 列。1.6 DATEDIF 函数的作用名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 14 页 -DATEDIF函数计算两个日期之间的天数、月数或年数。提供此函数是为了与 Lotus 1-2-3 兼容。语法：DATEDIF(start_date,end_date,unit)Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。日期有多种输入要领：带引号的文本串（例如2001/1/30）、系列数（例如，如果运用 1900 日期系统则 36921 代

5、表 2001 年 1 月 30 日）或其他公式或函数的结果（例如，DATEVALUE(2001/1/30)）。End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。Unit 为所需信息的返回类型。Unit 返回 Y 时间段中的整年数。M时间段中的整月数。D时间段中的天数。MDstart_date 与 end_date 日期中天数的差。忽略日期中的月和年。YMstart_date 与 end_date 日期中月数的差。忽略日期中的日和年。YDstart_date 与 end_date 日期中天数的差。忽略日期中的年。说明：Microsoft Excel 按顺序的系列数保存日期，这

6、样就可以对其执行计算。如果工作簿运用 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 运用不同

7、的默认日期系统。有关细致信息，请参阅 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 在一个单元格

8、中指定字符出现的次数例如在 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 月

9、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)名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 14 页 -=TEXT(A1,yyyymmdd)。也可以直接自定义格式：yyyymmdd。1.9 用“定义名称”的要领突破IF 函数的嵌套限定Excel 中的 IF()函数的一个众所周知的限定

10、是嵌套不能超过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

11、$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,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(Sh

12、eet1!$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）返回

13、包含负值的单元格数量：=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”)（1

14、0）返回在文本中任何位置包含单词GOOD 字符内容的单元格数量：=COUNTIF(DATA,“*GOOD*”)（11）返回包含以单词AB(不分大小写)开头内容的单元格数量：=COUNTIF(DATA,“AB*”)名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 14 页 -（12）返回包含当前日期的单元格数量：=COUNTIF(DATA,TODAY()）（13）返回大于平均值的单元格数量：=COUNTIF(DATA,&AVERAGE(DATA)（14）返回平均值上面超过三个标准误差的值的单元格数量：=COUNTIF(DATA,“&AVERAGE(DATA)+STDEV(DATA)*

15、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 个数，如何求出最大的数

16、在哪个单元格？=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列的绝对引

17、用。公式中还可以混合运用相对引用和绝对引用。可以运用 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).De

18、lete 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 用函数来获取单元格地址名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 14 页 -在复杂的计算中，往往要获知单元格的地址，可以用函数=ADDRESS(ROW(),COLUMN()获得当前单元格的地址。1.19 求一列

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

20、 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.2

21、2 Excel数组的使用数组就是单元的集合或是一组处理的值集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果每个结果显示在一个单元中。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。数组的元素可多达6500 个。名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 14 页 -（1）了解数组首先我们通过多个例子来说明数组是如何工作的。我们可以从图中看到，在“B”列中的数据为销售量，在“C”列中的数据是销售单价，要求计算出每种产品的销售额和总的销售金额，一般的做法是计算出每种产品的销

22、售额，然后再计算出总的销售额。但是如果我们改用数组，就可以只键入一个公式来完成这些运算。输入数组公式的步骤为：选定要存入公式的单元格，在本例中我们选择“D4”单元格。输入公式=SUM(B2:B4*C2:C4)，但不要按下 Enter键（输入公式的要领和输入普通的公式一样），按下 Shift+Ctrl+Enter键。我们就会看到在公式外面加上了一对大括号“”，如图 7-36所示。在单元格“D”中的公式“=SUM(B2:B4*C2:C4)”,表示“B2:B4”范围内的每一个单元格和“C2：C4”内相对应的单元格相乘，也就是把每个地区的销售量和销售单价相乘，相乘的结果共有3 个数字，每个数字代表一个

23、地区的销售额，而“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”中的任一格作任何单独处理，必须

24、针对整个数组来处理。（2）运用数组常数我们也可以在数组中运用常数值。这些值可以放在数组公式中运用区域引用的地点。要在数据公式中运用数组常数，直接将该值输入到公式中并将它们放在括号里。例如，在图7-39 中，就运用了数组常数执行计算。常数数组可以是一维的也可以是二维的。一维数组可以是垂直的也可以是水平的。在一维水平数组中的元素用逗号分开。下面是一个一维数组的例子。例如数组：10,20,30,40,50。在一维垂直数组中的元素用分号分开。在下面的例子是一个61 的数组，100;200;300;400;500;600。对于二维数组，用逗号将一行内的元素分开，用分号将各行分开。下一个例子是“4 4”的

25、数组（由4 行 4 列组成）：100,200,300,400;110,;130,230,330,440。留心：不可以在数组公式中运用列出常数的要领列出单元引用、名称或公式。例如：2*3,3*3,4*3因为列出了多个公式，是不能用的。A1,B1,C1 因为列出多个引用，也是不能用的。不过可以运用一个区域，例如A1:C1。对于数组常量的内容，可由下列准则构成：数组常量可以是数字、文字、逻辑值或不正确值。数组常量中的数字，也可以运用整数、小数或科学记数格式。文字必须以双引号括住。同一个数组常量中可以含有不同类型的值。数组常量中的值必须是常量，不可以是公式。数组常量不能含有货币符号、括号或百分比符号。

26、所输入的数组常量不得含有不同长度的行或列。（3）数组的编辑名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 14 页 -数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能单独编辑。在编辑数组前，必须先选取整个数组。选取数组的步骤为：(1)选取数组中的任一单元格。(2)在“编辑”菜单中选择“定位”命令或者按下F5 键，出现一个“定位”对话框。按下“定位条件”按钮，出现一个定位条件对话框，如图7-40 所示。选择“当前数组”选项，最后按下“确定”按钮，就可以看到数组被选定了。编辑数组的步骤为:选定要编辑的数组，移到数据编辑栏上按F2 键或单击左键，使代表数组的括号

27、消散，之后就可以编辑公式了。编辑完成后，按下 Shift+Ctrl+Enter键。若要删除数组，其步骤为：选定要删除的数组，按Ctrl+Delete或选择编辑菜单中的“清理”。（4）数组的扩充在公式或函数中运用数组常量时，其它运算对象或参数应该和第一个数组具有相同的维数。必要时，Microsoft Excel 会将运算对象扩展，以符合操作须要的维数。每一个运算对象的行数必须和含有最多行的运算对象的行数一样，而列数也必须和含有最多列数对象的列数一样。例如:=SUM(1,2,3+4,5,6）内的第一个数组为13,得到的结果为1+4、2+5 和 3+6 的和,也就是 21。如果将公式写成=SUM(1

28、,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

29、,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.

30、23 数组的使用（1）数组公式的实现要领：其实这些都是数组公式，数组公式的输入要领是将公式输入后，不要直接按回车键（Enter），而是要同时按Ctrl+Shift+Enter，这时计算机自动会为你添加“”的。在论坛上，为了告诉大家这是数组公式，故在公式的头尾都加上了“”。如果不注意按回车了，可以用鼠标点一下编辑栏中的公式，再按Ctrl+Shift+Enter。编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消散，然后编辑公式，最后按Ctrl Shift Enter 键。选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。数组常量的运用数组公式中还

31、可运用数组常量，但必须自己键入花括号“”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值。名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 14 页 -2、数组公式的原理：数组公式，说白了就是同时对一组或几组数同时处理，然后得到须要的答案。运用数组公式的最主要的原理是数于数之间一一对应。1、假设要将A1：A50 区域中的所有数值舍入到2 位小数位，然后对舍入的数值求和。很自然地就会想到运用公式：=ROUND（A1，2）ROUND（A2，2）,ROUND（A50，2）。或者添加ROUND 辅助列（A1=ROUND（A1，2），然后对辅助用

32、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）

33、，这也是数组公式的一种形式。须要统计如下图所示销量的频率分布，即分别统计销量在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 步骤：名师资料总结-

34、精品资料欢迎下载-名师精心整理-第 8 页，共 14 页 -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

35、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

36、=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=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)名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 14 页 -以上公式中*的意思为 AND,+的意思为 OR,-的意思为

37、 不等于 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(INDI

38、RECT(1:&LEN(A1),1)*1)1.25 逻辑函数的非逻辑表现例如，求取范围Data 中小于 0 或大于 5 的数值之和:正确用法：=SUM(IF(Data5),Data)不正确用法：=SUM(IF(OR(Data5),Data)1.26 在 EXCEL的数组公式中ROW 函数的用法在 EXCEL的数组公式中，ROW()是一个非常有用的函数，现在举个例子来说明。（1）返回一列中最后一个数值=INDEX(A:A,MAX(ROW(A1:A100)*(A1:A100)在这个公式中用ROW 函数返回 A1:A100 即 A1格到 A100 中不为空的单元格，它是一组数据，然后用MAX确定最大

39、的一个行号，即最后一格不为空的单元格，然后用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 返回最大值的行号和地址返回最大值的行号：名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 14 页 -=min(if(A1:A100=max(A1:A100),row(A1:A100),)返

40、回最大值的地址：=ADDRESS(MIN(IF(A1:A100=max(A1:A100),ROW(A1:A100),),COLUMN(A1:A100)=MAX(IF(A1:A100)*ISNUMBER(A1:A100),ROW(A1:A100),)1.28 Excel多见不正确及处理办法经常用 Excel 可能都会遇到一些不正确值信息，如：#N/A！、#VALUE！、#DIV/O！等等，出现这些不正确的原由有很多种，如果公式不能计算正确结果，Excel 将显示一个不正确值，例如，在须要数字的公式中运用文本、删除了被公式引用的单元格，或者运用了宽度不足以显示结果的单元格。以下是几种多见的不正确及

41、其处理要领。（1）#！原由：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#！不正确。处理要领：如果单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。如果运用的是1900年的日期系统，那么Excel 中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#！不正确。如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。（2）#VALUE!当运用不正确的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生不正确值#VALUE!。原由一：在须要数字或逻辑值时输入了文本，Exc

42、el 不能将文本转换为正确的数据类型。处理要领：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如：如果单元格A1包含一个数字，单元格A2包含文本 学籍，则公式=A1+A2将返回不正确值#VALUE!。可以用 SUM 工作表函数将这两个值相加（SUM 函数忽略文本）：=SUM（A1:A2）。原由二：将单元格引用、公式或函数作为数组常量输入。处理要领：确认数组常量不是单元格引用、公式或函数。原由三：赋予须要单一数值的运算符或函数一个数值区域。处理要领：将数值区域改为单一数值。修改数值区域，使其包含公式所在的数据行或列。（3）#DIV/O!当公式被零除时，将会产生不正

43、确值#DIV/O!。原由一：在公式中，除数运用了指向空单元格或包含零值单元格的单元格引用（在 Excel 中如果运算对象是空白单元格，Excel将此空值当作零值）。处理要领：修改单元格引用，或者在用作除数的单元格中输入不为零的值。原由二：输入的公式中包含明显的除数零，例如：=5/0。处理要领：将零改为非零值。（4）#NAME?名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 14 页 -在公式中运用了Excel 不能识别的文本时将产生不正确值#NAME?。原由一：删除了公式中运用的名称，或者运用了不存在的名称。处理要领：确认运用的名称确实存在。选择菜单 插入 名称 定义 命令，如

44、果所需名称没有被列出，请运用 定义 命令添加相应的名称。原由二：名称的拼写不正确。处理要领：修改拼写不正确的名称。原由三：在公式中运用标志。处理要领：选择菜单中 工具 选项 命令，打开 选项 对话框，然后单击 重新计算 标签，在 工作薄选项 下，选中 接受公式标志 复选框。原由四：在公式中输入文本时没有运用双引号。处理要领：Excel 将其解释为名称，而不理会用户准备将其用作文本的想法，将公式中的文本括在双引号中。例如：下面的公式将一段文本总计：和单元格 B50中的数值合并在一起：=总计：&B50 原由五：在区域的引用中缺少冒号。处理要领：确认公式中，运用的所有区域引用都运用冒号。例如：SUM

45、（A2:B34）。（5）#N/A 原由：当在函数或公式中没有可用数值时，将产生不正确值#N/A。处理要领：如果工作表中某些单元格暂时没有数值，请在这些单元格中输入#N/A，公式在引用这些单元格时，将不执行数值计算，而是返回#N/A。（6）#REF!当单元格引用无效时将产生不正确值#REF！。原由：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。处理要领：修改公式或者在删除或粘贴单元格之后，立即单击撤消 按钮，以恢复工作表中的单元格。（7）#NUM！当公式或函数中某个数字有疑问时将产生不正确值#NUM！。原由一：在须要数字参数的函数中运用了不能接受的参数。处理要领：确

46、认函数中运用的参数类型正确无误。原由二：运用了迭代计算的工作表函数，例如：IRR 或 RATE，并且函数不能产生有效的结果。处理要领：为工作表函数运用不同的原始值。原由三：由公式产生的数字太大或太小，Excel 不能表示。处理要领：修改公式，使其结果在有效数字范围之间。名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 14 页 -（8）#NULL！当试图为两个并不相交的区域指定交叉点时将产生不正确值#NULL！。原由：运用了不正确的区域运算符或不正确的单元格引用。处理要领：如果要引用两个不相交的区域，请运用联合运算符逗号（，）。公式要对两个区域求和，请确认在引用这两个区域时，运用

47、逗号。如：SUM（A1:A13，D12:D23）。如果没有运用逗号，Excel 将试图对同时属于两个区域的单元格求和，但是由于A1:A13 和 D12:D23 并不相交，所以他们没有共同的单元格。1.29 金额大写的转换假设 A1 单元格为原始数据，即小写数字。公式法一：=IF(A1=0,零元整,IF(A10,负,)&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)=A

48、BS(A1),零)&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),dbnum2)&分,整)公式法二：=IF(A1=5),INT(A1*10)-INT(A1)*10+1,INT(A1*10)-INT(A1)*10),DBNum2$-804G/通用格式)&角)&IF(OR(INT(A1*100)-INT(A1*10)*10=0,(IF(INT(A1*1000)-INT(A1*100)*10=5,CEILING(A1*100,1)-INT(A1*10)*10=10,FALSE

49、),整,(IF(INT(A1*1000)-INT(A1*100)*10=5,(IF(CEILING(A1*100,1)-INT(A1*10)*10=10,(TEXT(CEILING(A1*100,1)-INT(A1*10)*10,DBNum2$-804G/通用格式)&分),(TEXT(INT(A1*100)-INT(A1*10)*10,DBNum2$-804G/通用格式)&分),(￥,FIXED(A1,2,TRUE),元)四、VBA代码法（自定义函数）Public Function BigNum(xiaoxie As Currency)Application.Volatile Dim fuha

50、o As String fuhao=If xiaoxie 0 Then xiaoxie=-xiaoxie fuhao=负 End If If xiaoxie=0 Then 名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 14 页 -BigNum=零元整 Else Const cNum=零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分 Const cCha=零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整 BigNum=sNum=Trim(Str(Int(Round(xiaoxie,2)*100)For i=1 To Len(sNum)BigNum=