Excel的公式技巧

2024-08-23 19:48:14

字体：大中小

来源：转载

供稿：网友

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”就可以显示当前表名。值得注意的是，返回的工作表名称随着工作表名称的变化而变化。在此引用中，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_date,unit)

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!$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(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(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将显示一个错误值，例如，在需要数字的公式中使用文本、删除了被公式引用的单元格，或者使用了宽度不足以显示结果的单元格。以下是几种常见的错误及其解决方法。

（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

原因五：在区域的引用中缺少冒号。

解决方法：确认公式中，使用的所有区域引用都使用冒号。例如：SUM（A2: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 (ROUND(A1,3)=ROUND(A1,1),"整",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&"分"))

公式法三：

=CONCATENATE(TEXT(INT(A1),"[DBNum2][$-804]G/通用格式")&"元"&IF((INT(A1*10) -INT(A1)*10)=0,"",TEXT(IF(AND(CEILING(A1*100,1)-INT(A1*10)*10=10,INT(A1*1000)-INT(A1*100)*10>=5),INT(A1*10)-INT(A1)*10+1,INT(A1*10)-INT(A1)*10),"[DBNum2][$-804]G/通用格式")&"角")&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))),"整",(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][$-804]G/通用格式")&"分"))),(TEXT(INT(A1*100)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分")))),"(￥",FIXED(A1,2,TRUE),"元)")

四、VBA代码法（自定义函数）

Public Function BigNum(xiaoxie As Currency)

application.Volatile

Dim fuhao As String

fuhao = ""

If xiaoxie < 0 Then

xiaoxie = -xiaoxie

fuhao = "负"

End If

If xiaoxie = 0 Then

BigNum = "零元整"

Else