还剩15页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
电子表格公式技巧学习 2010-01-0201:09:18 阅读4080 评论2 字号大中小 订阅
1.公式技巧
1.1在单元格中显示工作表和工作簿的名称在单元格中显示工作表的名称,有两种要领
(1)建立如下自定义函数Functionbooknamebookname=ActiveSheet.NameEndFunction运用时在单元格中输入公式=bookname,即可返回当前工作簿的标签名字
(2)自定义名称的要领定义如下名称__[插入]à[名称]à[定义],名称的定义为“T_B”,引用位置输入“=repla__get.document11find]get.document1tnow”,在单元格输入“=T_B”就可以显示当前表名值得留心的是,返回的工作表名称随着工作表名称的变化而变化在此引用中,GET.DOCUMENT是宏表函数,当数据变动时不能自动计算,now是易失性函数,任何变动都会强制计算,宏表函数所以加上now就可以自动重算了,T用来将now产生的数值转化为空文本在单元格中显示工作簿的名称,运用系统函数__ll在单元格中输入公式=__llfilename ,就会返回该工作簿和工作表的名字(包括绝对路径名),然后根据自己的须要运用一些文本处理函数执行处理即可留心该函数必须在工作簿已经保存的情况下才生效
1.2基本判断单元格最后一位是数字还是字母在有些情况下,须要判断单元格的最后一位是数字还是字母,可以用下面三个公式之一
(2)=IFISNUMBER--RIGHTA11数字字母,直接返回数字或字母其中“--”的意思是将文本型数字转化为数值以便参与运算
(3)=IFISERRRIGHTA1*1字母数字,直接返回数字或字母
1.3如何求出一个人到某指定日期的周岁?=DATEDIF起始日期结束日期Y
1.4判断单元格中存在特定字符假如判断A栏里能不能存在$字符,有则等于1,没有则等于0,公式为=IFCOUNTIFA:A*$*
0101.5计算某单元格所在的列数通常情况下,A列为第1列,AA列为27列可以在A1单元格中输入列标,通过下列公式计算出任何列标的列数=COLUMNINDIRECTA11例如“FG”列为第163列
1.6DATEDIF函数的作用DATEDIF函数计算两个日期之间的天数、月数或年数提供此函数是为了与Lotus1-2-3兼容语法DATEDIFstart_dateend_dateunitStart_date为一个日期,它代表时间段内的第一个日期或起始日期日期有多种输入要领带引号的文本串(例如2001/1/30)、系列数(例如,如果运用1900日期系统则36921代表2001年1月30日)或其他公式或函数的结果(例如,DATEVALUE2001/1/30)End_date为一个日期,它代表时间段内的最后一个日期或结束日期Unit为所需信息的返回类型Unit返回Y时间段中的整年数M时间段中的整月数D时间段中的天数MDstart_date与end_date日期中天数的差忽略日期中的月和年YMstart_date与end_date日期中月数的差忽略日期中的日和年YDstart_date与end_date日期中天数的差忽略日期中的年说明MicrosoftEx__l按顺序的系列数保存日期,这样就可以对其执行计算如果工作簿运用1900日期系统,则Ex__l会将1900年1月1日保存为系列数1而如果工作簿运用1904日期系统,则Ex__l会将1904年1月1日保存为系列数0,(而将1904年1月2日保存为系列数1)例如,在1900日期系统中Ex__l将1998年1月1日保存为系列数35796,因为该日期距离1900年1月1日为35795天请查阅MicrosoftEx__l如何存储日期和时间Ex__lforWindows和Ex__lfor__cintosh运用不同的默认日期系统有关细致信息,请参阅NOW示例DATEDIF2001/1/12003/1/1Y等于2,即时间段中有两个整年DATEDIF2001/6/12002/8/15D等于440,即在2001年6月1日和2002年8月15日之间有440天DATEDIF2001/6/12002/8/15YD等于75,即在6月1日与8月15日之间有75天,忽略日期中的年DATEDIF2001/6/12002/8/15MD等于14,即开始日期1和结束日期15之间的差,忽略日期中的年和月
1.7在一个单元格中指定字符出现的次数例如在A1单元格中有“abcabca”字符串,求“a”在单元格A1内出现次数,用下列公式=LENA1-LENSUBSTITUTEA1a
1.8日期形式的转换我们在有些情况下写日期会用“20060404”表示,如何转换成“2006-04-04”的标准日期格式,用下面的两个公式之一(假定在A1单元格中有原始日期)=TEXTA10000-00-00=TEXTA1--也可以运用以下公式,转换成“2006-4-4”的格式=LEFTA14SUBSTITUTERIGHTA140-反之,如何把“2006年4月4日”转换成“20060404”?可以运用下面的公式之一(假定在A1单元格中有原始日期)=YEARA1TEXTMONTHA100TEXTDAYA100=YEARA1IFMONTHA1100MONTHA1MONTHA1IFDAYDAYA1100DAYA1DAYA1=TEXTA1yyyymmdd也可以直接自定义格式yyyymmdd
1.9用“定义名称”的要领突破IF函数的嵌套限定Ex__l中的IF函数的一个众所周知的限定是嵌套不能超过7层例如下面的公式是不正确的,因为嵌套层数超过了限定=IFSheet1!$A$4=111IFSheet1!$A$4=222IFSheet1!$A$4=333IFSheet1!$A$4=444IFSheet1!$A$4=555IFSheet1!$A$4=444IFSheet1!$A$4=555IFSheet1!$A$4=666IF$A$4=777FALSE通常的要领会考虑用VBA代替但是也可以可以通过对公式的一部分”定义名称”来处理这种限定定义一个名叫”OneToSix”的名称里面包括公式=IFSheet1!$A$4=111IFSheet1!$A$4=222IFSheet1!$A$4=333IFSheet1!$A$4=444IFSheet1!$A$4=555IFSheet1!$A$4=444IFSheet1!$A$4=555IFSheet1!$A$4=666FALSE再定义另一个名叫”SevenToThirteen”的名称,里面包括公式=IFSheet1!$A$4=777IFSheet1!$A$4=888IFSheet1!$A$4=999IFSheet1!$A$4=10100IFSheet1!$A$4=11110IFSheet1!$A$4=12120IFSheet1!$A$4=13130NotFound最后单元格中输入下面的公式=IFOneToSixOneToSixSevenToThirteen
1.10动态求和举一个基本例子例如对于A列,求出A1到当前单元格行标前面一行的单元格中的数值之和,更直接地说,如果当前单元格在B17,那么求A1:A16之和运用下面的公式=SUMINDIRECTA1:AROW-
11.11COUNTIF函数的16种公式配置(设DATA为区域名称)
(1)返加包含值12的单元格数量=COUNTIFDATA12
(2)返回包含负值的单元格数量=COUNTIFDATA0
(3)返回不等于0的单元格数量=COUNTIFDATA0
(4)返回大于5的单元格数量=COUNTIFDATA5
(5)返回等于单元格A1中内容的单元格数量=COUNTIFDATAA1
(6)返回大于单元格A1中内容的单元格数量=COUNTIFDATA“”A1
(7)返回包含文本内容的单元格数量=COUNTIFDATA“*”
(8)返回包含三个字符内容的单元格数量=COUNITFDATA“”
(9)返回包含单词GOOD不分大小写内容的单元格数量=COUNTIFDATA“GOOD”
(10)返回在文本中任何位置包含单词GOOD字符内容的单元格数量=COUNTIFDATA“*GOOD*”
(11)返回包含以单词AB不分大小写开头内容的单元格数量=COUNTIFDATA“AB*”
(12)返回包含当前日期的单元格数量=COUNTIFDATATODAY)
(13)返回大于平均值的单元格数量=COUNTIFDATA__ERAGEDATA
(14)返回平均值上面超过三个标准误差的值的单元格数量=COUNTIFDATA“__ERAGEDATA+STDEVDATA*3
(15)返回包含值为或-3的单元格数量=COUNTIFDATA3)+COUNIFDATA-3
(16)返回包含值逻辑值为TRUE的单元格数量=COUNTIFDATATRUE
1.12计算一个日期是一年中的第几天例如2006年7月29日是本年中的第几天?在一年中,显示是第几天用什么函数呢?假定A1中是日期,运用下列公式=A1-DATEYEARA110,将单元格格式配置为常规,返回210,即2006年7月29日是2006年的第210天
1.13如何用公式求出最大值所在的行?如A1A10中有10个数,如何求出最大的数在哪个单元格?=__TCHLARGEA1:A101A1:A100=ADDRESS__TCH__ALLA1:A10COUNTAA1:A10A1:A1001=ADDRESS__TCH__XA1:A101A1:A
10011.14在Ex__l中的绝对引用与相对引用之间切换在Ex__l中建立公式时,该公式可以运用相对引用,即相对于公式所在的位置引用单元;也可以运用绝对引用,即引用特定位置上的单元引用由所在单元格的“列的字母”和“行的数字”组成,绝对引用由在“列的字母”和“行的数字”前面加“$”表示,例如,$B$1是对第一行B列的绝对引用公式中还可以混合运用相对引用和绝对引用可以运用F4切换相对引用和绝对引用,选中包含公式的单元格,在公式栏中选择想要改动的引用,按F4键可以执行切换
1.15在Ex__l公式和结果之间高速切换在ex__l工作表中输入计算公式时,可以运用“Ctrl+`(中音号)”键来决定显示或潜藏公式,可让储存格显示计算的结果,还是公式本身
1.16如果某列中有大于0和小于0的数,将小于0数字所在的行自动删除假定在A1-A6中有大于0和小于0的数,可以用下面的VBA程序实现fori=6to1step-1if__llsi10thenrowsi.Deletenexti
1.17奇数行和偶数行求和有时候须要奇数行和偶数行单独求和,例如要求A列第1行至1000行中奇数行之和,运用公式=SUMPRODUCTA1:A1000*MODROWA1:A10002,要求这些行中偶数行之和,运用公式=SUMPRODUCTA1:A1000*NOTMODROWA1:A
100021.18用函数来获取单元格地址在复杂的计算中,往往要获知单元格的地址,可以用函数=ADDRESSROWCOLUMN获得当前单元格的地址
1.19求一列中某个特定的值对应的另外列的最大或最小值为了直观起见,举一个基本的例子例如在A1:A10中有若干台计算机、打印机、传真机等物品的名称,在B1:B10中有上述设备对应的__,求“计算机”对应的最低__可以用公式=minifa1:a10=计算机b1:b10,输入该公式后按Ctrl+Shift+Enter完成
1.20自动记录数据录入时间运用VBA实现,建立一个Time.xls文档,输入以下VBA代码PrivateSubWorksheet_ChangeByValTargetAsRangeIfTarget.Column1Then ExitSubElse Target.Offset01=NowEndIfEndSub
1.21如果一个单元格中既有数字又有字母,如何提取其中的数字呢FunctiongetnumberrngAsStringAsStringDimmylenAsIntegerDimmystrAsStringmylen=LenrngForI=1Tomylen mystr=MidrngI1 IfAscmystr=48AndAscmystr=57Then getnumber=getnumbermystr EndIfNextIEndFunction
1.22Ex__l数组的使用数组就是单元的__或是一组处理的值__可以写一个数组公式,即输入一个单个的公式,它执行多个输入的操作并产生多个结果——每个结果显示在一个单元中数组公式可以看成是有多重数值的公式与单值公式的不同之处在于它可以产生一个以上的结果一个数组公式可以占用一个或多个单元数组的元素可多达6500个
(1)了解数组首先我们通过多个例子来说明数组是如何工作的我们可以从图中看到,在“B”列中的数据为销售量,在“C”列中的数据是销售单价,要求计算出每种产品的销售额和总的销售金额,一般的做法是计算出每种产品的销售额,然后再计算出总的销售额但是如果我们改用数组,就可以只键入一个公式来完成这些运算输入数组公式的步骤为选定要存入公式的单元格,在本例中我们选择“D4”单元格输入公式=SUMB2:B4*C2:C4,但不要按下[Enter]键(输入公式的要领和输入普通的公式一样),按下[Shift]+[Ctrl]+[Enter]键我们就会看到在公式外面加上了一对大括号“{}”,如图7-36所示在单元格“D”中的公式“=SUMB2:B4*C2:C4”表示“B2:B4”范围内的每一个单元格和“C2C4”内相对应的单元格相乘,也就是把每个地区的销售量和销售单价相乘,相乘的结果共有3个数字,每个数字代表一个地区的销售额,而“SUM”函数将这些销售额相加,就得到了总的销售额下面我们再以运用数组计算3种产品的销售额为例,来说明如何产生多个计算结果其操作流程如下 1选择“D2D4”单元格区域,该区域中的每个单元格保存的销售金额如图7-37所示 2在“D2”单元格中输入公式“=B2:B4*C2:C4”(不按[Enter]键)按下[Shift]+[Ctrl]+[Enter]”键,我们就可以从图7-38中看到执行后的结果同时我们可以看到“D2”到“D4”的格中都会出现用大括弧“{}”框住的函数式,这表示“D2”到“D4”被当作一个单元格来处理,所以不能对“D2”到“D4”中的任一格作任何单独处理,必须针对整个数组来处理
(2)运用数组常数我们也可以在数组中运用常数值这些值可以放在数组公式中运用区域引用的地点要在数据公式中运用数组常数,直接将该值输入到公式中并将它们放在括号里例如,在图7-39中,就运用了数组常数执行计算常数数组可以是一维的也可以是二维的一维数组可以是垂直的也可以是水平的在一维水平数组中的元素用逗号分开下面是一个一维数组的例子例如数组{1020304050}在一维垂直数组中的元素用分号分开在下面的例子是一个6×1的数组,{100;200;300;400;500;600}对于二维数组,用逗号将一行内的元素分开,用分号将各行分开下一个例子是“4×4”的数组(由4行4列组成){100200300400;110……;130230330440}留心不可以在数组公式中运用列出常数的要领列出单元引用、名称或公式例如{2*33*34*3}因为列出了多个公式,是不能用的{A1B1C1}因为列出多个引用,也是不能用的不过可以运用一个区域,例如{A1:C1}对于数组常量的内容,可由下列准则构成数组常量可以是数字、文字、逻辑值或不正确值数组常量中的数字,也可以运用整数、小数或科学记数格式文字必须以双引号括住同一个数组常量中可以含有不同类型的值数组常量中的值必须是常量,不可以是公式数组常量不能含有货币符号、括号或百分比符号所输入的数组常量不得含有不同长度的行或列
(3)数组的编辑数组包含数个单元格,这些单元格形成一个整体,所以,数组里的某一单元格不能单独编辑在编辑数组前,必须先选取整个数组选取数组的步骤为1选取数组中的任一单元格 2在“编辑”菜单中选择“定位”命令或者按下[F5]键,出现一个“定位”对话框按下“定位条件”按钮,出现一个定位条件对话框,如图7-40所示选择“当前数组”选项,最后按下“确定”按钮,就可以看到数组被选定了编辑数组的步骤为:选定要编辑的数组,移到数据编辑栏上按[F2]键或单击左键,使代表数组的括号消散,之后就可以编辑公式了编辑完成后,按下[Shift]+[Ctrl]+[Enter]键若要删除数组,其步骤为选定要删除的数组,按[Ctrl]+[Delete]或选择编辑菜单中的“清理”
(4)数组的扩充在公式或函数中运用数组常量时,其它运算对象或参数应该和第一个数组具有相同的维数必要时,MicrosoftEx__l会将运算对象扩展,以符合操作须要的维数每一个运算对象的行数必须和含有最多行的运算对象的行数一样,而列数也必须和含有最多列数对象的列数一样例如:=SUM{123}+{456})内的第一个数组为1×3得到的结果为1+
4、2+5和3+6的和也就是21如果将公式写成=SUM{123}+4})则第二个数据并不是数组,而是一个数值,为了要和第一个数组相加,Ex__l会自动将数值扩充成1×3的数组运用=SUM{123}+{444}做计算,得到的结果为1+
4、2+4和3+4的和即18将数组公式输入单元格区域中时,所运用的维数应和这个公式计算所得数组维数相同这样,MicrosoftEx__l才能把计算所得的数组中的每一个数值放入数组区域的一个单元格内如果数组公式计算所得的数组比选定的数组区域还小,则MicrosoftEx__l会将这个数组扩展,以便将它填入整个数组区域内例如={12;34}*2扩充后的公式就会变为={12;34}*{22;22},则相应的计算结果为“2468”再如输入公式={12;34}*{23}扩充后的公式就会变为={12;34}*{23;23},则相应的计算结果为“26612”如果MicrosoftEx__l将一个数组扩展到可以填入比该数组公式大的区域内,而没有扩大值可用的单元格内,这样就会出现#N/A不正确值例如={12;34}={123}扩充后的公式就会变为={12#N/A;34#N/A}*{12#/A;
1.
2.#N/A},而相应的计算结果为“24#N/A46#N/A”如果数组公式计算所得的数组比选定的数组区域还要大,则超过的值不会出现在工作表上
1.23数组的使用
(1)数组公式的实现要领其实这些都是数组公式,数组公式的输入要领是将公式输入后,不要直接按回车键(Enter),而是要同时按Ctrl+Shift+Enter,这时计算机自动会为你添加“{}”的在__上,为了告诉大家这是数组公式,故在公式的头尾都加上了“{}”如果不注意按回车了,可以用鼠标点一下编辑栏中的公式,再按Ctrl+Shift+Enter编辑或删除数组公式编辑数组公式时,须选取数组区域并且激活编辑栏,公式两边的花括号将消散,然后编辑公式,最后按Ctrl+Shift+Enter键选取数组公式所占有的全部区域后,按Delete键即可删除数组公式数组常量的运用数组公式中还可运用数组常量,但必须自己键入花括号“{}”将数组常量括起来,并且用“,”和“;”分离元素其中“,”分离不同列的值,“;”分离不同行的值
2、数组公式的原理数组公式,说白了就是同时对一组或几组数同时处理,然后得到须要的答案运用数组公式的最主要的原理是数于数之间一一对应
1、假设要将A1A50区域中的所有数值舍入到2位小数位,然后对舍入的数值求和很自然地就会想到运用公式=ROUND(A1,2)+ROUND(A2,2)+…+ROUND(A50,2)或者添加ROUND辅助列(A1=ROUND(A1,2)),然后对辅助用SUM函数合计(=SUM(A1A50))如果用数组公式就不要这么麻烦,公式为{=SUM(ROUND(A1A50,2))},它的意思即为在数组A1A50用ROUND函数执行二位小数的四舍五入,然后执行合计
2、假设一题为A1A10区域中为商品单价,B1B10为对应的销售数量,须要统计总销售额,常规做法须要添加辅助列C列,在C列中计算出C1C10的每个单价的销售额(C1=A1*B1),然后执行SUM合计(C11=SUM(C1C10))而数组公式为{=SUM(A1A10*B1B10)}
3、留心关于常数项的数组可以直接手工添加{},如此公式=SUM{123}+{456}),这也是数组公式的一种形式须要统计如下图所示销量的频率分布,即分别统计销量在5000以下、5000到_____、_____到50000以及大于50000的销售点数量a2b2C2销售点销售额分段点城北00141005000城北00215__0_____城南001870050000城南00225900城南0035800城东00115300城东00238000城东0039800城西00156000城西00272050城中001130000城中00260400城中00348700步骤
1、打造如上图所示的表格
2、选中单元格G7G10,直接输入公式=FREQUENCY(B4B14,c4c6)
3、输入公式后,按CTRL+SHIFT+ENTER键结束 类型日期单价销售数量A2005-6-15100010B2005-6-20100015B2005-7-1400010C2005-7-10400011B2005-8-15900013C2005-8-20900015A2005-9-30100014A2005-10-10100020B2005-10-15400025类型从B1格开始 计算B产品8月份销量13{=SUMIF$B$2:$B$10=B*MONTH$C$2:$C$10=8$E$2:$E$100}13{=SUM$B$2:$B$10=B*MONTH$C$2:$C$10=8*$E$2:$E$10}计算A产品和B产品的销量107{=SUMIF$B$2:$B$10=A+$B$2:$B$10=B$E$2:$E$100}107{=SUM$B$2:$B$10=A+$B$2:$B$10=B*$E$2:$E$10}计算8月份前不包括B产品销量和8月后不包括C产品销量49{=SUMIFMONTH$C$2:$C$108$B$2:$B$10=B*MONTH$C$2:$C$10=8$B$2:$B$10=C$E$2:$E$10}49{=SUMIFMONTH$C$2:$C$108-$B$2:$B$10=B*MONTH$C$2:$C$10=8-$B$2:$B$10=C$E$2:$E$10}49{=SUMMONTH$C$2:$C$108$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中的数字为3__732,求其中最大的数字9,求这和6个数字之和为32
(1)求其中最大的数字,运用数组公式{=__XMIDA1ROWINDIRECT1:LENA11*1}先输入=__XMIDA1ROWINDIRECT1:LENA11*1,再按Ctrl+Shift+Enter
(2)求其中数字之和,运用下面的公式=SUMPRODUCTMIDA1ROWINDIRECT1:LENA11*
11.25逻辑函数的非逻辑表现例如,求取范围Data中小于0或大于5的数值之和: 正确用法 {=SUMIFData0+Data5Data}不正确用法 {=SUMIFORData0Data5Data}
1.26在EX__L的数组公式中ROW函数的用法在EX__L的数组公式中,ROW是一个非常有用的函数,现在举个例子来说明
(1)返回一列中最后一个数值{=INDEXA:A__XROWA1:A100*A1:A100}在这个公式中用ROW函数返回A1:A100即A1格到A100中不为空的单元格,它是一组数据,然后用__X确定最大的一个行号,即最后一格不为空的单元格,然后用INDEX,来返回A1到A100中A列最大行号的那个数据
(2)同理如果要返回一行中最后一个数值则为{=INDEX1:1__XCOLUMN1:1*1:1}
(3)下面出一个小题目,如果有兴趣想学数组的可以试一下,返回A列100行中最后一个有数值的行号的公式是什么?{=__XIFA1:A100ROWA1:A100}
1.27返回最大值的行号和地址返回最大值的行号{=minifA1:A100=__xA1:A100rowA1:A100}返回最大值的地址{=ADDRESSMINIFA1:A100=__xA1:A100ROWA1:A100COLUMNA1:A100}{=__XIFA1:A100*ISNUMBERA1:A100ROWA1:A100}
1.28Ex__l多见不正确及处理办法经常用Ex__l可能都会遇到一些不正确值信息,如#N/A!、#VALUE!、#DIV/O!等等,出现这些不正确的原由有很多种,如果公式不能计算正确结果,Ex__l将显示一个不正确值,例如,在须要数字的公式中运用文本、删除了被公式引用的单元格,或者运用了宽度不足以显示结果的单元格以下是几种多见的不正确及其处理要领
(1)#####!原由如果单元格所含的数字、日期或时间比单元格宽,或者单元格的日期时间公式产生了一个负值,就会产生#####!不正确处理要领如果单元格所含的数字、日期或时间比单元格宽,可以通过拖动列表之间的宽度来修改列宽如果运用的是1900年的日期系统,那么Ex__l中的日期和时间必须为正值,用较早的日期或者时间值减去较晚的日期或者时间值就会导致#####!不正确如果公式正确,也可以将单元格的格式改为非日期和时间型来显示该值
(2)#VALUE!当运用不正确的参数或运算对象类型时,或者当公式自动更正功能不能更正公式时,将产生不正确值#VALUE!原由一在须要数字或逻辑值时输入了文本,Ex__l不能将文本转换为正确的数据类型处理要领确认公式或函数所需的运算符或参数正确,并且公式引用的单元格中包含有效的数值例如如果单元格A1包含一个数字,单元格A2包含文本学籍,则公式=A1+A2将返回不正确值#VALUE!可以用SUM工作表函数将这两个值相加(SUM函数忽略文本)=SUM(A1:A2)原由二将单元格引用、公式或函数作为数组常量输入处理要领确认数组常量不是单元格引用、公式或函数原由三赋予须要单一数值的运算符或函数一个数值区域处理要领将数值区域改为单一数值修改数值区域,使其包含公式所在的数据行或列
(3)#DIV/O!当公式被零除时,将会产生不正确值#DIV/O!原由一在公式中,除数运用了指向空单元格或包含零值单元格的单元格引用(在Ex__l中如果运算对象是空白单元格,Ex__l将此空值当作零值)处理要领修改单元格引用,或者在用作除数的单元格中输入不为零的值原由二输入的公式中包含明显的除数零,例如=5/0处理要领将零改为非零值
(4)#NAME在公式中运用了Ex__l不能识别的文本时将产生不正确值#NAME原由一删除了公式中运用的名称,或者运用了不存在的名称处理要领确认运用的名称确实存在选择菜单插入名称定义命令,如果所需名称没有被列出,请运用定义命令添加相应的名称原由二名称的拼写不正确处理要领修改拼写不正确的名称原由三在公式中运用标志处理要领选择菜单中工具选项命令,打开选项对话框,然后单击重新计算标签,在工作薄选项下,选中接受公式标志复选框原由四在公式中输入文本时没有运用双引号处理要领Ex__l将其解释为名称,而不理会用户准备将其用作文本的想法,将公式中的文本括在双引号中例如下面的公式将一段文本总计和单元格B50中的数值合并在一起=总计B50原由五在区域的引用中缺少冒号处理要领确认公式中,运用的所有区域引用都运用冒号例如SUM(A2:B34)
(5)#N/A原由当在函数或公式中没有可用数值时,将产生不正确值#N/A处理要领如果工作表中某些单元格暂时没有数值,请在这些单元格中输入#N/A,公式在引用这些单元格时,将不执行数值计算,而是返回#N/A
(6)#REF!当单元格引用无效时将产生不正确值#REF!原由删除了由其他公式引用的单元格,或将__单元格粘贴到由其他公式引用的单元格中处理要领修改公式或者在删除或粘贴单元格之后,立即单击撤消按钮,以恢复工作表中的单元格
(7)#NUM!当公式或函数中某个数字有疑问时将产生不正确值#NUM!原由一在须要数字参数的函数中运用了不能接受的参数处理要领确认函数中运用的参数类型正确无误原由二运用了迭代计算的工作表函数,例如IRR或RATE,并且函数不能产生有效的结果处理要领为工作表函数运用不同的原始值原由三由公式产生的数字太大或太小,Ex__l不能表示处理要领修改公式,使其结果在有效数字范围之间
(8)#NULL!当试图为两个并不相交的区域指定交叉点时将产生不正确值#NULL!原由运用了不正确的区域运算符或不正确的单元格引用处理要领如果要引用两个不相交的区域,请运用联合运算符逗号(,)公式要对两个区域求和,请确认在引用这两个区域时,运用逗号如SUM(A1:A13,D12:D23)如果没有运用逗号,Ex__l将试图对同时属于两个区域的单元格求和,但是由于A1:A13和D12:D23并不相交,所以他们没有共同的单元格
1.29金额大写的转换假设A1单元格为原始数据,即小写数字公式法一=IFA1=0零元整IFA10负IFINTABSA1TEXTINTABSA1[dbnum2]元IFINTABSA1*10-INTABSA1*10TEXTINTABSA1*10-INTABSA1*10[dbnum2]角IFINTABSA1=ABSA1零IFROUNDABSA1*100-INTABSA1*10*10TEXTROUNDABSA1*100-INTABSA1*10*10[dbnum2]分整公式法二=IFA10负TEXTTRUNCABSA1[DBNum2]G/通用格式元IFROUNDA13=ROUNDA1整TEXTRIGHTTRUNCA1*101[DBNum2]G/通用格式角IFROUNDA13=ROUNDA11整TEXTRIGHTROUNDA1*1001[DBNum2]G/通用格式分公式法三=CONCATENATETEXTINTA1[DBNum2][$-804]G/通用格式元IFINTA1*10-INTA1*10=0TEXTIFAND__ILINGA1*1001-INTA1*10*10=10INTA1*1000-INTA1*100*10=5INTA1*10-INTA1*10+1INTA1*10-INTA1*10[DBNum2][$-804]G/通用格式角IFORINTA1*100-INTA1*10*10=0IFINTA1*1000-INTA1*100*10=5__ILINGA1*1001-INTA1*10*10=10FALSE整IFINTA1*1000-INTA1*100*10=5IF__ILINGA1*1001-INTA1*10*10=10TEXT__ILINGA1*1001-INTA1*10*10[DBNum2][$-804]G/通用格式分TEXTINTA1*100-INTA1*10*10[DBNum2][$-804]G/通用格式分¥FIXEDA12TRUE元
四、VBA代码法(自定义函数)PublicFunctionBigNumxiaoxieAsCurrencyApplication.VolatileDimfuhaoAsStringfuhao=Ifxiaoxie0Thenxiaoxie=-xiaoxiefuhao=负EndIfIfxiaoxie=0ThenBigNum=零元整ElseConstcNum=零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分ConstcCha=零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整BigNum=sNum=TrimStrIntRoundxiaoxie2*100Fori=1ToLensNumBigNum=BigNum+MidcNumMidsNumi1+11+MidcNum26-LensNum+i1NextiFori=0To11BigNum=Repla__BigNumMidcChai*2+12MidcChai+261NextiBigNum=fuhao+BigNum EndIfEndFunction上面四种办法中,要领一和要领四等价;要领
二、三和要领
一、四的区别只是整数部分为零时,要领
一、四不显示“零元”,而直接显示“角分”;要领三在要领2的基础上加上了数字显示,下表是不同数据在四种要领中的显示结果原始数据要领一要领二要领三要领四
0.12壹角贰分零元壹角贰分零元壹角贰分¥
0.12元壹角贰分
1.02壹元零贰分壹元零角贰分壹元贰分¥
1.02元壹元零贰分_____01壹佰万壹仟零壹元整壹佰万壹仟零壹元整壹佰万壹仟零壹元整¥_____
01.00元壹佰万壹仟零壹元整0零元整零元整零元整¥
0.00元零元整-25001负贰万伍仟零壹元整负贰万伍仟零壹元整-贰万伍仟零壹元整¥-
25001.00元负贰万伍仟零壹元整。