最全EXCLE公式大全.doc

最全EXCLE公式大全.doc

《最全EXCLE公式大全.doc》由会员分享，可在线阅读，更多相关《最全EXCLE公式大全.doc（5页珍藏版）》请在冰点文库上搜索。

我想很多人都会遇到我曾经遇到过的问题，我总结了一些小技巧，和大家分享。

1、双击的妙用

A、对某一选定的单元格，依次双击该单元格的四个边（上下左右），可以迅速转到有连续数据的顶部、底部、最左和最右。

等同功能健为：

Ctrl+↑↓←→。

B、输出的数据一般情况都会有单元格宽度不够的情况。

要快速使所有的列数据都能完整地显示，可以做以下操作：

选定需要调整的列，比如选定A列到G列，在已选定区域的任意一列的右边沿双击。

C、公式纵向填充：

如F2=D2*E2，需要将公式从F2填充到F20，在F2单元格输入公式后，选定该单元格，鼠标指针停在该单元格的右下角时指针显示为一个小的十字，双击！

等同功能的操作有：

选定F2：

F20，在F2输入公式后按Ctrl+Enter，则公式往下填充（在F2：

F20区间的任意一格输入公式，都一样可以实现填充）。

另一等同功能健为：

选定F2：

F20，在F2输入公式后如果不小心敲了Enter，光标停留在F3，此时用Ctrl+Enter不能实现向下填充，此时按Ctrl+D一样可以实现公式快速填充。

D、不用双击：

如果需要改一个单元格的数据，一般情况都会双击该单元格转入编辑状态，或者将鼠标直接放在编辑栏的单元格内容处后修改。

快捷健为F2，停留在需要修改的单元格，按F2后直接可以转入编辑状态。

2、快速选定打印区域、设置打印

A、范围的选定：

光标停留在需要打印范围的左上角，先Ctrl+Shift+→，再Ctrl+Shift+↓，需要调整则用Shift+↓/→来增加没有文字区域的行或列。

B、将该范围定义为打印区域：

连续按ALT+F，T，S。

（免用鼠标）

C、设置打印页数和自动调整比例：

打印预览，设置、页面设置、设置为1页宽？

页高。

页边距，水平居中。

D、将表头设置为每页打印：

Alt+F，U，工作表，顶端标题行选表头的行标识。

E、宽度超宽的Excel文档的打印设置，可能遇到需将左边两列设置为固定格式的情况，可以在页面设置中将顶端行和左边列同时设定，将文档定义为多页宽，一页高，就可以打印出漂亮的宽文档了。

小知识：

如果数据区域中有公式得出得结果为：

#N/A、#DIV/0!

等信息，可以在页面设置的时候将错误信息定义为：

－－或者＃N/A，另一种处理方法是，用组合函数判断，如果出现错误信息则用零或者表示：

IF（ISERROR（公式）＝true，0，公式）。

3、U8数据导出EXCEL后不能运算，一些工资软件导出数据后不能自动重算

U8数据导出EXCEL表后格式为文本，切换成编辑状态后数据格式自动转换为数据型，简单的处理方法是利用“查找/替换”将所有的小数点替换成为小数点。

当数据是整数则需要通用整列乘1或者整列加0的方式，将计算的结果选择性粘贴成为数据就OK了。

从朗新工资系统导出的数据出现不能自动重算的情况，此时需要在工具－选项－重新计算－选择“自动重算”，之后我们就可以设公式了。

4、如何美化数据透视表获得的表格

刚开始使用数据透视表时，对其强大的功能我叹为用止，而它的格式却不能随心所欲，解决苦恼的方法当时用粘贴成为文本后再美化，后来学会了高招，与您共享：

行字段每增加一个项目会出现一个汇总，如果有些小计不需要，则可以选择改单元格，右键，字段设置－下方有“自动”，“自定义”、“无”，选择“无”，则同一类型的“小计”都会消失。

在列字段上会出现“求和项：

***”的格式，我不喜欢这样的，我想将“求和项：

”去掉，可以将求和项替换为，必须为，不能替换为空，否则透视表会提示数据表里字段已经存在。

以上的快捷键为：

选择其中一个将“求和项：

”ctrl－x，按键，CTRL-A选定整个工作表，ctrl－H（查找、替换），ctrl－v（粘贴“求和项：

”），按TAB键换到“替换为：

”，按，alt－a（全部替换）

你用熟悉了会觉得这些快捷键给你节省2/3的时间。

5、巧妙将数字转换成为货币大写假设F5单元格数值为18.21

按照以下步骤你可以成功将它转化为货币金额大写

在单元格E6输入公式"=int（F5）"

设置单元格E6,在“数字”下选择“特殊”－“中文大写数字”

设置单元格E6,在“数字”下选择“自定义”－[DBNum2][$-804]G/通用格式，在后加“圆”，即：

[DBNum2][$-804]G/通用格式圆，上方显示“壹拾捌圆”

在F6单元格录入“＝（F5-E6）*100”,得到值21，

设置单元格F6,第一步设置同E6,

设置单元格F6,在“数字”下选择“自定义”－修改为：

[DBNum2][$-804]0角0分，上方显示贰角壹分。

6、计算按揭买房

我不知道怎么将EXCEL上传，只好将数据和公式列出类罗

项目调整数据1单元格

银行利率0.0558E4

面积126.00E5

单价2,600.00E6

手续费20,000.00E7

年限10E8

首期90,000.00E9

总房价347,600.00E10

每月付款2,805.86E11

其中：

付银行利息￥79,103.12E12

E12=-PMT（E4/12,E8*12,E10-E9,,0）

7、如何计算员工上班的月份数？

计算年终奖的时候都会遇到要计算员工当年出勤的月份数，许多公司规定，入厂在15号以后则当月不计算，在15号前进厂则当月计算出勤月份。

说明：

DATEDIF（开始日期，结束日期，单位）函数用于计算两个日期相差的月份，公式：

=DATEDIF（B5,$C$2,$B$2）+IF（DAY（B5）<16,1,0）

"IF（DAY（B5）<16,0,-1）函数用于识别进厂日期是否在入厂月的15号前，15号前当月计算一个月，否则不算"；

如果超过12个月（员工进厂日期在以前年份），需要用IF（DATEDIF（B5,$C$2,$B$2）+IF（DAY（B5）<16,1,0）>12,12,DATEDIF（B5,$C$2,$B$2）+IF（DAY（B5）<16,1,0））将出勤月份大于12个月的调整为12个月。

8、常用函数sum，average，rank，search，iserror，if，vlookup

8.1、sum函数是一个求和函数，可以对一个区域、一列，一行进行求和，如SUM（A2:

B8）,SUM（A2:

A8），SUM（A2:

G2）也可以与其它函数组合起来用，如SUM（LARGE（A2:

A8，{1,2,3,4}））即对A2:

A8中最大的4个数求和；

注意事项：

如果数据区域中有文本型字段，sum函数不能对文本进行求和，必须将文本转换为数值才可以用函数，实践中有人遇到用＋号连加得到的结果与用sum函数求和结果不一致的情况就是因为数据区域有文本型单元格造成。

使用技巧：

如果要对A2:

A8进行求和，鼠标停留在A9单元格，按ALT+＝可以快速录入求和函数；将A2:

A8区域选定或者将A2:

A9区域选定按ALT+=快捷键也可以实现；直接按工具栏的∑当然也是一种快捷方式。

8.2average求平均值函数

函数功能：

返回参数的平均值（算术平均值）

选定区域可以是用逗号隔开的30个以内的数值，也可以是一个数据区域

注意：

如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内

8.3rank排序函数

函数功能：

返回一个数字/单元格数值在多个数据中或者数字列表中的排位。

数字/单元格数值的排位是该数字在多个数据或者数字列表中排名（次序）。

使用及技巧：

RANK（number,ref,order），number也可以是活动单元格，如将b2：

b40的数据进行排名，则公式为“＝rank（b2，$b$2:

$b$40，0）”如果是升序排列则“＝rank（b2，$b$2:

$b$40）”也是一样的效果。

*技巧：

快速将选定区域转换为绝对引用，选定b2:

b40区域，按F4键，可变为绝对引用，每多按一次会有不同，别忘了ctrl+向下键一起使用哟。

8.4VLOOKUP函数（财务人员非学不可的函数）

在表格数组的首列查找指定列的值，并由此返回表格数组满足行条件的指定列的值。

（非常不好描述，即使看不懂也没关系）

假设有一张成绩表，第一列为学号，第二列为姓名，第三列之后为各科成绩，第二行开始有学生的成绩信息，信息区域为A2:

A41我需要找出指定学号（放置在一个新工作表的A2:

A10）的几个人的数学成绩（假设在第5列）

语法

VLOOKUP（A2,成绩表的数据区域,5,0）

在新工作薄b2录入“＝vlookup（A2,成绩表$A$2:

$A$41,5,0）”,假设A2学号为S008,函数会将成绩表里的S008找到并将第5列的数学成绩返回

9.1替换公式中的一部分

财务人员和文员每月都要做一些相同的报表，每月都需要从不同的基础数据入手，需要花费较多的时间和精力。

技巧：

将公式中的一部分替换成为另外的字符串，就可以轻松完成公式（链接）的更新了

9.2、快速输入日期、时间

快速输入日期可用：

ctrl+；，快速输入当前时间可用ctrl+shift+；

9.3、打开文件提示的日期为当天（时间）

在单元格录入“＝today（）”则显示为当天的日期，在单元个录入“＝now（）”则显示当天的日期和时间

9.4需要删除字符串中的空格

可能遇到的情况：

录入的姓名中间有，或者字符串中的多余，影响数据的查找和透视等等

解决方法1：

用substitute函数实现

解决方法2：

ctrl+a，ctrl+h，查找替换为空（在查找栏录入一个，在替换为栏不用录入任何字符，alt+a完成全部替换。

（几秒钟搞定）

9.5EXCEL表格数据的横竖互换（转置）

选定表格需要互换的区域，选择性粘贴，选择“转置”，即可完成横竖互换。

9.6选定数据区域的可见单元格

有时候在筛选出来所需要的数据之后，copy到另外一个表里不需要的（已隐藏的行/列又出现了，有一个快捷的方式，在选择你想copy的区域的时候先按alt+；键，即实现了选定可见区域，再copy（ctrl+c）到另外的表页（ctrl+v）得到的数据就不会有你不想要的数据了。

10、ROUND四舍五入与以显示值为准的用法（排除计算出现的误差）

问题：

用EXCEL制作的工资表和其它一些表格往往会涉及到公式计算的结果，由于计算的结果一般显示为两位小数，而将单个数据相加与合计数相差1-2分。

对策1：

凡是涉及公式计算的数据，用round函数将其四舍五入，如函数“＝round（A2*B2,2）”就是将A2*B2的结果保留两位小数。

对策2：

将工作标设定为以显示值为准（即数据后面的小数位没有显示的系统默认为零）。

ALT+T（工具）－O（选项）－重新计算－工作薄选项－以显示值为准/以显示精度为准（高级版本excel的术语）

*建议使用round函数。

11、如何处理日期型数据不方便查询的问题

问题：

想通过日期列来筛选出来某个月内的数据。

对策1：

选定日期数据，点击工具栏的逗号，转换成为数值，可要记住你想要选择的起止日期转换成的数值，然后按照数值来筛选就可以了。

对策2：

插入一列，通过year（）&’—‘&month（）将日期字段转换成“年－月”格式，即可执行筛选。

12、ALT相关的快捷键用法一二

有很多快捷键需要用到ALT进行组合，如我们执行工具栏的编辑可用ALT+E，要打开工具用ALT+O，现在介绍两个绝对用的上用法：

用法1：

插入一张新的工作表：

ALT+SHIFT+F1或者ALT+F9

用法2：

将一块数据移到另外一个已经打开的表页或者工作薄：

用鼠标选定了需要移动的区域，按住ALT键，鼠标可以将该区域拖到你想要去的地方。

用法3：

参考9.6选定数据区域的可见单元格

13、CTRL相关的快捷键用法一二

用法1：

将上方公式的值复制成为数值：

CTRL+SHIFT+”（双引号）

类似功能键：

CTRL+C,ALT+E,S,ALT+V

用法2：

将显示公式切换为显示数值CTRL+’（单引号）

类似功能键：

选定公式部分，按F9

14、快速选定有连续文字区域用ctrl+shift+空格

当遇到需要打印一个很大的工作表时候，用鼠标选定有文字区域不是很方便，改用快捷键。

15、利用数据透视表快速做库存报表

问题：

材料仓每月需要分仓库库位打印材料仓库存报表，每月重复的工作量较大

解决方法：

利用数据透视表，选定需要的字段，设置号格式，将库位拉到页字段位置，在将该表页复制几份，每份选择不同的库位，则可生成各库位的报表。

以后每月只要将此文件复制一份，将基础数据（各库的汇总库存报表），覆盖，再刷新各个数据透视表即可快速得到仓库报表。

此例已经过检验。

大大提高了效率。