甘苦人生2010 / execl文章 / EXCEL常用函数公式及技巧搜集之三

分享

   

EXCEL常用函数公式及技巧搜集之三

2017-01-29  甘苦人生2...
EXCEL常用函数公式及技巧搜集之三

打印表头

Excel中如何实现一个表头打印在多页上?

请选择文件-页面设置-工作表-打印标题-顶端标题行,然后选择你要打印的行。

打印表尾,通过Excel直接提供的功能应该是无法实现的,需要用vba编制才行。

Excel打印中如何不显示错误值符号

在“页面设置”-“工作表”-“错误单元格打印为”中,

将“显示值”改为“空白”即可。

对于一些不可打印的字符的处理

对于一些不可打印的字符(在Excel显示中类似空格),直接用替换方法不容易去掉。

可以这么做:

=SUBSTITUTE(CLEAN(A1)," ","")

用那个函数可将个位数前面的零值显示出来?

如果单元格A1的内容是5,在A2用那个函数可将A1的内容变为05?

(Text或value也可,总之个位数的零也显示,例:5变05,15则15)

可以用TEXT(A2,"00")

或将单元格格式自定义为00

如果你要在A3的前面插入100行

可以这样:在名称框输入   3:103-回车-ctrl+shift+"+"(大键盘)

请问如何每隔30行粘贴一新行

偶在班上负责统计企业进出口业务量,领导要求每30家做一合计数,偶只有每隔30行插入复制单元格的方法来添加的,很是麻烦,请教各位大虾有什么快捷的方法呀

在最后加一辅助列,输入=INT((ROW()-1)/31)+1 (假设一个标题行)

然后以该行分类字段汇总.

在工作表里有连续10行数据, 现在要每行间格2

解答:1:如sheet1!$A$1:$D$10中有连续10行资料,在sheet2中把sheet1中的数据每行间隔2行 ,sheet2!A1中公式可用:

=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$D$10,INT((ROW()-1)/2)+1,COLUMN()),""))

然后填充公式(注意公式在SHEET2中的填充范围,超过范围会出错!)

2:小修改

=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN()),"")

一个大表每一行下面需要加一行空行,怎么加最方便

方法一:增加辅助列,填充数据排序完成

方法二:增加辅助列,函数完成

=IF(MOD(ROW(),2),INDIRECT("a"&ROUNDUP(ROW()/2,0)),"")

Excel中插入空白行

如果想在某一行上面插入几行空白行,可以用鼠标拖动自此行开始选择相应的行数,然后单击右键,选择插入。如果在每一行上面均插入一空白行,按住Ctrl键,依次单击要插入新行的行标按钮,单击右键,选择插入即可。

快速删除工作表中的空行

如果用户想删除Excel工作表中的空行,一般的方法是需要将空行都找出来,然后逐行删除,但这样做操作量非常大,很不方便。下面提供二种快速删除工作表中的空行的方法:

1、首先打开要删除空行的工作表,在打开的工作表中单击“插入列”命令,从而插入一新的列X,在X列中顺序填入整数,然后根据其他任何一列将表中的行排序,使所有空行都集中到表的底部。删去所有空行中X列的数据,以X列重新排序,然后删去X列。

2、如批量删除空行,我们可以利用“自动筛选”功能,把空行全部找到,然后一次性删除。 做法:先在表中插入新的一个空行,然后按下Ctrl+A键,选择整个工作表,用鼠标单击“数据”菜单,选择“筛选”项中的“自动筛选”命令。这时在每一列的顶部,都出现一个下拉列表框,在典型列的下拉列表框中选择“空白”,直到页面内已看不到数据为止。 

在所有数据都被选中的情况下,单击“编辑”菜单,选择“删除行”命令,然后按“确定”按钮。这时所有的空行都已被删去,再单击“数据”菜单,选取“筛选”项中的“自动筛选”命令,工作表中的数据就全恢复了。插入一个空行是为了避免删除第一行数据。

如果想只删除某一列中的空白单元格,而其它列的数据和空白单元格都不受影响,可以先复制 此列,把它粘贴到空白工作表上,按上面的方法将空行全部删掉,然后再将此列复制,粘贴到原工作表的相应位置上。

快速删除空行

有时为了删除Excel工作簿中的空行,你可能会将空行一一找出然后删除,这样做非常不方便。你可以利用自动筛选功能来实现,方法是:先在表中插入新的一行(全空),然后选择表中所有的行,单击“数据筛选自动筛选”命令,在每一列的顶部,从下拉列表中选择“空白”。在所有数据都被选中的情况下,单击“编辑删除行”,然后按“确定”,所有的空行将被删去。 注意:插入一个空行是为了避免删除第一行数据。

一次删完Excel里面多出很多的空白行

1、用分面预览看看

2、用自动筛选然后删除

3、用自动筛选,选择一列用非空白,空白行就看不到了,打印也不会打出来。但是实际上还是在的,不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。

4、先插入一列,在这一列中输入自然数序列,然后以任一列排序,排序完后删除数据后面的空行,再以刚才输入的一列排序,排序后删除刚才插入的一列。

每30行为一页并加上一个标题如何实现

30行为一页,并加上一个标题,如何实现。

可以每30行加一个分页符,标题就用打印标题来设置。

1、        标题

文件-页面设置-工作表-打印标题-顶端标题行,设置一下就好了。

2、        每页30

也是在页面设置中,设置上下页边距的调整可以实现,打印预览看一下就可以看到是不是30行了,不到30行你可以将行距加宽,进行调整,以我的经验,加标题的30/页大概行距是20,这样连制表人的空间都留出来了。

每页30行-插入》分页符;然后每向下移动30行,点菜单插入》分页符

如何实现隔行都加上标题项

在excel中,每条记录都要加上标题(隔行都加),如何才能快速实现?(只要打印出来能实现就成)。

E列输入2 4,然后选中这两个单元格,拖住右下的点向下拉到底。

把第一行标题项复制,在有数据区域的下部选中与数据行数相同的空行,粘贴。

用同样的方法填上奇数(如上),按E列排序即可。

如何把标签页去掉的?

工具选项视图点击工作表标签去掉()勾确定。

恢复时也照此操作

工具选项视图点击工作表标签显示()勾确定。

去掉默认的表格线(网线)

单击“工具”菜单中的“选项”,再单击对话框中的“视图”,找到“网格线”,使之失效(将左边的“×”去掉)。

表格的框线

我们很喜欢为表格加上一道框线,不过这道框线又往往叫我们花掉很多时间来重画,例如在下方多加一列时,Excel并不会把新列加在下方框线之上。又例如将上方的数据拷到最后一列时,下方的框线就会给盖掉,变成穿了一个洞。

我的技巧就是在表格的最后一列留一列空列,并把它的列高定得很小,我就叫这一列「缓冲列」好了。把列高定小一点,除了美观之外,还可以用作提醒用户不要把数据打到缓冲列。你可以试试在缓冲列上加列或拷数据到缓冲列之上,框线并不会给弄乱。

列标的标识变了

通常EXCEL的列标都是用大写英文字母表示的,我的EXCEL的列标今天都变成了阿拉伯数字表示的了,请教这两种表示方法有什么不同,如果想恢复成字母表示的该怎么办。

这是EXCEL的R1C1样式。在这里改回来:工具/选项/常规:不选R1C1样式。

符号的意义

单元格自定义格式中"?" "#"代表的是什么意思。

“?” 一个字符,字符:可以是文本、也可以是数字;

“#” 一个数值字符,字符:只能是数字。

双击格式刷竟也能COPY文本(不是文本格式)

步骤:选中“单元格”→双击格式刷→按住Ctrl键选择需复制的不连续目标区域→按回车Enter

格式刷的作用其实没变,复制文本其实只是按Enter的结果。

分解动作分为三步:

1.定位在原数据上,双击格式刷:复制所有内容

2.在目标区域按格式刷:选择性粘贴-格式

3.按回车:粘贴所有内容。

你会发现如果原单元格上有批注或其实Shape对象的话,一样也复制了,跟原数据按Ctrl+C,选择区域.再按Enter这个意思是一样的。

查找+格式刷的妙用

通常在数据校对时要用到查找,找到之后就用不同格式区分(如字体为红色、底纹为黄色等等),如此重复。

例如:先找到第一个数据并将字体改为红色,然后双击格式刷,当查找到其它相同数据时,再按Ctrl+Aexcel就会将新找到的数据自动改为红色与之区分。

楼主能不能做一个动画演示?

具体操作为:Ctrl+F→在查找栏输入要查找的值→查找下一个→设置格式(不要退出查找对话框)→双击格式刷→查找全部→Ctrl+A→关闭

另外,通常的方法是:Ctrl+F→在查找栏输入要查找的值→查找全部→Ctrl+A,再设置格式。

光标移动

在一个Excel工作表中作业时,双击某单元格的四周(上、下、左、右),会迅速移动光标的位置,若是双击上方即刻回到单元格所在列的最顶端,双击下方则移动到最底端的编辑外,同样双击左右也是到相对应的地方,双击单元格中间则变为输入状态。大家可以试试,这样比移动工作表中的下拉图标快捷。

最后一行为文本

=offset($1,MATCH(CHAR(65535),b:b)-1,)

最后一行为数字

=offset($1,MATCH(9.9999E+307,b:b)-1,)

或者:=lookup(2,1/(b1:b1000<>""),b1:b1000)

如何在EXCEL中快速定位最后一行数据

如果定位就是选中的意思,可按CTRL+END键实现。

CTRL+↓       双击选取单元格之下框线

SUN函数快速求和

如何用sum函数快速求和

操作:将光标移到欲要求和的列或行,直按"Alt+"=",最后按一下“enter"键就可以。这样我们不用输入参数就可以快速求和。

Excel中快速查看所有工作表公式

  只需一次简单的键盘点击,即可可以显示出工作表中的所有公式,包括Excel用来存放日期的序列值。

要想在显示单元格值或单元格公式之间来回切换,只需按下CTRL+`(位于TAB键上方)。

Excel中设置行间距

    想必大家都知道Excel中是没有行间距设置功能的吧。利用拼音指南却可以让我们在Excel中轻松设置单元格中文字的行间距。

在Excel 2003中选中需要设置行间距的单元格,单击“格式”菜单,依次选择“拼音指南/显示或隐藏”,马上可以看到单元格中文字行间距变大了。

如果想再进一步调整行间距,可再单击“格式”菜单,选择“拼音指南/设置”打开“拼音属性”窗口,切换到“字体”选项卡下,把字号设置大一点,确定后行间距就会相应增大,反之则减小。

怎样同时改变多行行高

我们知道,通过拖动行或列间的分界线可以改变行高或列宽,但怎样同时改变向行或几列的高度或宽度呢?

  我们以改变行高为例,先选中要改变行高的列,按下Shift键再单击行标题头,可以选定连续的多行(如果要选中多个不连续行,可以按下Ctrl键)。选中多列后,拖动任意一个被选中的行标题间的分界线,到适当高度释放鼠标,所有被选中的行高都改变了。

我们也可以精确地改变行高:选中多行后,单击“格式”菜单,选择“行”中的“行高”命令,设置行高为20,单击“确定”,行高都被设置为20了。

快速换行

  在Excel单元格中输入数值后,按下Alt键不松开,再按下Enter键,即可快速换行。

让文本换行

  每次在Excel单元格中输入一个值,再按下Enter键,活动单元格均默认下移一个单元格,非常不方便。不过,这时,可以选择“工具”→“选项”→“编辑”,然后取消“按Enter键移动活动单元格标识框”复选框即可。

Excel中行列快速转换

如果需要要将Excel按行(列)排列的数据,转换为按列(行)排列,可以通过“选择性粘贴”来实现。

选中需要转换的数据区域,执行一下“复制”操作;选中保存数据的第一个单元格,执行“编辑选择性粘贴”命令,打开“选择性粘贴”对话框,选中其中的“转置”选项,确定返回即可。

将原有列中的内容倒置过来

1

5

2

4

3

3

4

2

5

1

B1 =OFFSET(A$1,COUNTA(A:A)-ROW(A1),)

快速回到A1单元格

按下Ctrl+Home组合键,快速选中A1单元格。

复制粘贴中回车键的妙用

1、先选要复制的目标单元格,复制后,直接选要粘贴的单元格,回车OK;

2、先选要复制的目标单元格,复制后,选定要粘贴的区域,回车OK;

3、先选要复制的目标单元格,复制后,选定要粘贴的不连续单元格,回车OK。

一次选中批注单元格

  按下Ctrl+Shift+O (字母 O)组合键,可以一次性选定所有带批注的单元格。

一次在所有单位格中插入批注

1选择你已经做批注的单元格

2复制

3选择你要做相同批注的所有单元格

4编辑〉选择性粘贴〉批注

在公式中插入批注

如果要在公式中插入批注信息,可以利用“NValue返回“0”的特点,因为文字属于其它值。

1.假如A1F1单元格中是个别统计数字,G1则是它们的总和,那么一般情况下其公式为“=SUM(A1:F1)”

2.如果要在公式中插入批注信息,可以将公式更改为“=SUM(A1:F1)+N("A1F1的总和")”

不连续单元格填充同一数据

选中一个单元格,按住Ctrl键,用鼠标单击其他单元格,就将这些单元格全部都选中了。在编辑区中输入数据,然后按住Ctrl键,同时敲一下回车,在所有选中的单元格中都出现了这一数据。

空白行的填充

各位,我一些同事喜欢在表格内使用合并单元格,而我的进行公式运算时需把单元格打散,但就出现了许多空白格,现在我想把空白格用临近的非空白填充。

EXA1格为张三A8李四A21王五之类,现在我要把A2A7填为张三,A9A20为李四,A22之后为王五。

B1输入=IF(A1<>"",A1,IF(ROW()>1,INDIRECT("b"&ROW()-1))),向下复制到合适位置。然后用选择性粘贴功能替换到A列中去。

怎样用函数向下实现自动填充

各位高手,怎样用函数实现如下的功能:把左边的空格,用上面的A0001代码填充,实现右边的格式,谢谢解答!!!

A0001        白色       300                 A0001        白色       300  

红色        500   --->         A0001        红色       500

黄色        300                A0001       黄色        300

如果你的第一个“A0001”[A1]A2=if(b2>0,a$1,"") 向下拖曳。

最好用附件的形式来提问,这样可以减少相互间猜题的麻烦。

用绝对值是不行的,假如,我下面还有别的编号,这个功能就实现不了啊

怎么设置自动保存

在“工具”菜单上,单击“选项”,再单击“保存”选项卡。选中“自动保存时间间隔”复选框。在“分钟”框中,指定希望 Microsoft Office 程序保存文件的频率。

避免输入网址和电子邮件地址时的超链接

在单元格中输入的网址或电子邮件地址,Excel在默认情况下会将其自动设为超级链接。如果想取消网址或电子邮件地址的超级链接,可以在单元格上单击鼠标右键,选择“超级链接/取消超级链接”即可。

此外,还有两个有效办法可以有效避免输入内容成为超级链接形式:

1、在单元格内的录入内容前加入一个空格;

2、单元格内容录入完毕后按下“Ctrl+z”组合键,撤消一次即可。

单元格前面自动加了等号

我的单元格怎么输入时间后前面自动加了等号,然后2005年就变成了1905年了呢?

工具-选项-1-2-3帮助-转换 lotus 123 公式

有无打勾?去掉

加盖公章

我们日常上送、下发的报表材料、通知等都要加盖公章,如果把这项工作交给Excel或Word来完成,我们的工作就轻松多了。

第一步:制作公章图案

首先我们要做出一个公章的图案,最简单的办法是把公章图案扫描到电脑中,然后处理成透明的GIF图像。我们也可以直接用Excel来制作:把绘图工具打开,选中“椭圆”工具,在按下“Shift”键的同时拖开鼠标,就可以得到一个正圆了。双击这个正圆打开“设置自选图形格式”对话框,在“颜色与线条”标签中,填充颜色选“无填充颜色”,线条颜色设为红色,选3磅粗的单线形(图)。公章的文字用艺术字来制作,填充颜色和线条颜色都用红色,并设成无阴影产。弧形文字和水平文字要分开来做,在做弧形文字时,把艺术字拖到圆形的上方,在艺术字工具中选“艺术字形状-细上弯弧”,按住黄色的四方块往下拉,再作适当的调整,就可以做出公章里的圆弧形的文字了。公章中间还有一个红五星,用“自选图形”的星形就可以做出来了,填充颜色和线条颜色用红色。最后,按住“Shift”键把组成公章的文字、图形全部选上,执行右键菜单中的“组合”命令,一个公章就做好了。

如何把做好的公章保存出来?这里有一方法:把工作表另存为Web页,然后到保存目录中找到*.files的文件夹,里面有一个GIF图片,这就是刚才做好的公章图案了,它的背景是透明的,我们把它改名为gongzhang.gif保存下来即可。

第二步:添加“盖章”按钮

接下来我们给Excel添加一个盖章按钮,当一个工作表做好后,点击这个盖章按钮,就可以为我们盖上公章了。

先把公章图形复制出来(用来粘贴作为按钮的图标),然后打开“工具-自定义”对话框,选中“命令”标签,在“类别”栏中找到“宏”,在右边的“命令”栏里就会出现一项“自定义按钮”。用鼠标把这个笑脸图标拖出到菜单栏或工具栏上放下,在笑脸图标上击右键,在弹出的菜单中把“命名”处的文字改为“加盖公章”。接下来点击“粘贴按钮图标”这个命令,就可以用刚和复制的公章图形来代替笑脸图标了。把鼠标移下来选中“分配超级链接-插入图片”,然后在“请键入文件名称或Web页名称”栏里输入公章图片gongzhang.gif的文件名及路径,然后按“确定”返回。

好了,看到“加盖公章”这个按钮了吧,点击一下看看,呵呵,页面上就盖上一个鲜红的公章了,用鼠标可以把它拖到任意的地方。在Word文档中加盖公章的方法与此大同小异,大家可以自己试一试。

查找+格式刷的妙用

通常在数据校对时要用到查找,找到之后就用不同格式区分(如字体为红色、底纹为黄色等等),如此重复。

例如:先找到第一个数据并将字体改为红色,然后双击格式刷,当查找到其它相同数据时,再按Ctrl+A,excel就会将新找到的数据自动改为红色与之区分。

具体操作为:Ctrl+F在查找栏输入要查找的值查找下一个设置格式(不要退出查找对话框)双击格式刷查找全部Ctrl+A关闭

另外,通常的方法是:Ctrl+F在查找栏输入要查找的值查找全部Ctrl+A,再设置格式。但再找下个值时,又要重新设置格式,如果要找的值很多的话就显得不是那么方便了。当然按照自己的习惯做最好的。

如果用格式刷+查找功能呢,就可以找到A并把A设成红色,再双击格式刷,然后再找B、找C、找D…excel就会把找到的B、C、D自动的设为红色以之区分,而不用找一个设一次字体了。

Excel中鼠标双击妙用

1、双击单元格,就可以编辑单元格的内容(对应用快捷键——F2)。 

2、在行/列边缘双击鼠标,则可以得到此列的最适合的行高/列宽。 

3、双击工作表标签,可以直接重命名工作表的名称。 

4、在填充的时候,选定单元格再移动到这个区域的右下角,这时鼠标会变成细十字。 

       (1)当选择区域当下方的单元格有内容时,双击会自动填充下方有数据的区域。 

       (2)当选择区域当下方的单元格为空时而左边有数据时,双击会自动填充到与左边有数据的区域齐。 

       (3)当选择区域当下方与左边的单元格为空时而右边有数据时,双击会自动填充到与右边有数据的区域齐。 

5、将鼠标移动到选定单元格的边上,这时鼠标会变成带箭头的十字。 

       (1)这时双击,可以移动到数据区域的边缘,相当于快捷键——Ctrl+方向键。 

       (2)如果按住Shift再双击,可以快速选择数据,相当于快捷键——Shift+Ctrl+方向键。 

6、双击工具栏的空白处,就可以调出自定义工具栏的对话框。 

7、双击左上角工具栏中工作簿的图标,可以关闭当前工作簿;双击标题栏中的Excel图标,可以关闭Excel。 

8、使用格式刷时,用双击而不是单击就可以多次使用,再单击一次格式刷结束。在使用绘图工具栏时,如果双击线、矩形、圆等图形时也可以连续绘图。

9、双击拆分窗格的分割条(上下滚动条的上方,左右滚动条的右边,没试过拆分窗口的朋友可以先从窗口/拆分里体验一下),可以按当前单元格上下左右拆分;拆分后再双击分割条的任意部分可以恢复。 

10.在菜单上双击,可将菜单中所有的菜单项(包括不常用的菜单项)全部展开。

11.如果工具条浮动在工作表区,在工具条的标题栏双击,则该工具条返回工作表区上部或下部位置。在选中行或选中列(当然也可以是某一行、列)的边缘双击,自动设置成合适的行高或列宽(偶常常在做完表后调整列宽、行高的时候把整个表选中,双击两下就OK了)。

12.双击还可以恢复被隐藏的行或列

如要恢复被隐藏的第3行,先将鼠标指针移动到第2行与第4行之间的位置,当鼠标的指针变成横向为“=”号,纵向为黑色实心双箭头的十字形状时,再双击操作,被隐藏的行就会显示出来了。变为"=""||"时直接拖动就可以了。

13.在标题栏上双击,由最大化窗口(原始状态)还原到原始状态(最大化)大小 。

14.双击EXCEL中的透视表中的数据,可在新的工作表中列出该数据的明细。

Excel中快速定位的技巧实例

在Excel中,我们需要到达某一单元格,一般是使用鼠标拖动滚动条来进行,但如果数据范围超出一屏幕显示范围或数据行数非常多时,想快速定位到某一单元格可要有点麻烦了。其实我们可以使用“定位”功能迅速到达想要的单元格。

例1:需要选中Y2008单元格(或快速移动到Y2008单元格),我们可以使用“编辑/定位”菜单,在引用位置里输入“Y2008”后按回车即可。

例2:需要选中Y列的2004~2008行的单元格,我们按照相同的方法,在引用位置里输入“Y2004:Y2008”按回车即可。

例3:需要选中2008行的单元格,我们可以在引用位置里输入“2008:2008”按回车即可。

例4:需要选中2004~2008行的单元格,我们可以在引用位置里输入“2004:2008”按回车即可。

Excel中插入Flash时钟的步骤

动态时钟不是用函数运算、自动化功能制作出來的,這只是簡单的插入Flash文挡的功能而已,而且只要你有Flash文件,任何人都可以轻松自行制作。

制作方法:

1 首先打开一个空白Excel文件,点击“视图” 然后点选【控件工具箱】,→点击“其他控件”。

2 然后再点击[Shockwave Flash Object]项目,表示要插入Flash物件。

3 接下来,鼠标会变成一个小十字,此時可以在Excel编辑区中画一个大小适中的方框,这个方框就是用来显示Flash时钟的內容的。

4 画好方框后,接着点击【属性】,准备设置属性。

5 出現「属性」对话框后,将DeviceFont设置成False;将Eebedmovie设置成True;将Enabled设置成True;将Locked设置成True;将Loop设置成True;将Menu设置成False;并在“Movie”右侧填入时钟的地址与名称(如:G\22006.swf)

6 如下图,退出设计模式,全部完成。

小写数字转换成人民币大写

方法1

=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整",TEXT(TRUNC(H16),"[DBNum2]G/通用格式"&""))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>"0"),TEXT(TRUNC(MOD(H16*10,10)),"[DBNum2]G/通用格式")&"","")&IF(AND(RIGHT(TRUNC(H16*10))="0",TRUNC(H16)<>H16),"","")&IF(TRUNC(H16*10)<>H16,TRUNC(H16*10)=H16*10), "","")

方法2

=IF(F10=0,"",CONCATENATE(IF(INT(F10)=0,"",TEXT(INT(F10),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"",IF(INT(F10)=0,"","")),TEXT(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"",TEXT(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))

方法3

人民币大写的函数公式,可正负,最多两位小数。

=IF(A1<0,"","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&""&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(A1),"[DBNum2]")&""&IF(ISNUMBER(FIND(".0",A1)),"",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"")&TEXT(RIGHT(A1),"[DBNum2]")&""))

方法4

修改一下4:根据剑魔兄的测试,发现有一个问题,如-100.05,现修正如下:

=IF(A1<0,"","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&""&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&""&IF(ISNUMBER(FIND(".0",A1)),"",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"")&TEXT(RIGHT(A1),"[DBNum2]")&""))

方法5

=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/通用格式")&""))

方法6

无条件舍去: =CONCATENATE(IF(A1<0,"",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]")&"",IF(OR(AND(ABS(A1)<0.1,TRUNC(A1)=A1),RIGHT(INT(ABS(A1)*100),2)="00"),"",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(A1*10),1)="0","",""),IF(OR(TRUNC(A1*10)-(A1*10)=0,RIGHT(TRUNC(A1*100),1)="0"),"",TEXT(RIGHT(TRUNC(A1*100),1),"[DBNum2]")&""))

小数点后两位四舍五入: =CONCATENATE(IF(A1<0,"",""),TEXT(IF(TRUNC(ROUND(A1,2))=0,"",TRUNC(ABS(ROUND(A1,2)))),"[DBNum2]")&"",IF(TRUNC(ROUND(A1,2))=ROUND(A1,2),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(ROUND(A1,2)*10),1)="0","",""),IF(OR(TRUNC(ROUND(A1,2)*10)-(ROUND(A1,2)*10)=0,RIGHT(ROUND(A1,2),1)="0",TRUNC(ROUND(A1,2))=ROUND(A1,2)),"",TEXT(RIGHT(ROUND(A1,2),1),"[DBNum2]")&""))

方法7

无条件舍去: =IF(A1<0,"","")&SUBSTITUTE(TEXT(TRUNC(A1),"[DBNum2]")&""&IF(ISNUMBER(FIND(".",TRUNC(A1,2))),TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",A1)),"",""),"")&IF(LEFT(RIGHT(TRUNC(A1,2),3),1)=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"",""),"-",)

小数点后两位四舍五入: =IF(A1<0,"","")&SUBSTITUTE(TEXT(TRUNC(ROUND(A1,2)),"[DBNum2]")&""&IF(ISNUMBER(FIND(".",ROUND(A1,2))),TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",ROUND(A1,2))),"",""),"")&IF(LEFT(RIGHT(TRUNC(ROUND(A1,2),2),3),1)=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"",""),"-",)

方法8

再简化如后,请大家试试。

无条件舍去:

=IF(A1<0,"","")&TEXT(TRUNC(ABS(A1)),"[DBNum2]")&""&IF(ISERR(FIND(".",TRUNC(A1,2))),"",TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]"))&IF(RIGHT(TRUNC(A1*10))="0","","")&IF(LEFT(RIGHT(TRUNC(A1,2),3))=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"","")

小数点后两位四舍五入:

=IF(A1<0,"","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&""&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"","")

方法9

=IF(ISTEXT(C2),"","人民币:"&TEXT(INT(C2),"[dbnum2]")&""&IF(INT(C2*10)-INT(C2)*10=0,"",TEXT(INT(C2*10)-INT(C2)*10,"[dbnum2]")&"")&IF(INT(C2*100)-INT(C2*10)*10=0,"",TEXT(INT(C2*100)-INT(C2*10)*10,"[dbnum2]")&""))

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多