office --excel技巧

我想很多人都会遇到我曾经遇到过的问题，我总结了一些小技巧，和大家分享。
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.0558 E4
面 积 126.00 E5
单 价 2,600.00 E6
手 续 费 20,000.00 E7
年 限 10 E8
首 期 90,000.00 E9
总 房 价 347,600.00 E10
每月付款 2,805.86 E11
其中：付银行利息 ￥79,103.12 E12
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.2 average求平均值函数
函数功能：返回参数的平均值（算术平均值）
选定区域可以是用逗号隔开的30个以内的数值，也可以是一个数据区域
注意：如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内

8.3 rank 排序函数
函数功能：返回一个数字/单元格数值在多个数据中或者数字列表中的排位。数字/单元格数值的排位是该数字在多个数据或者数字列表中排名（次序）。
使用及技巧：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.4 VLOOKUP 函数 （财务人员非学不可的函数）
在表格数组的首列查找指定列的值，并由此返回表格数组满足行条件的指定列的值。 （非常不好描述，即使看不懂也没关系）

假设有一张成绩表，第一列为学号，第二列为姓名，第三列之后为各科成绩，第二行开始有学生的成绩信息，信息区域为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.5 EXCEL表格数据的横竖互换（转置）
选定表格需要互换的区域，选择性粘贴，选择“转置”，即可完成横竖互换。

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、利用数据透视表快速做库存报表

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

解决方法：利用数据透视表，选定需要的字段，设置号格式，将库位拉到页字段位置，在将该表页复制几份，每份选择不同的库位，则可生成各库位的报表。
以后每月只要将此文件复制一份，将基础数据（各库的汇总库存报表），覆盖，再刷新各个数据透视表即可快速得到仓库报表。