Excel函数技巧.docx
- 文档编号:11823829
- 上传时间:2023-04-03
- 格式:DOCX
- 页数:7
- 大小:608.01KB
Excel函数技巧.docx
《Excel函数技巧.docx》由会员分享,可在线阅读,更多相关《Excel函数技巧.docx(7页珍藏版)》请在冰豆网上搜索。
Excel函数技巧
1、Subtotal函数(对过滤后的数据进行加总),如SUBTOTAL(9,B12:
B24)(统计过滤后的数值之和,9代表sum,B12:
B24代表要求和的列。
2、Round(公式,2)四舍五入(保留2位小数)。
3、Trim()去空格的。
4、clean()去非打印字符的。
5、&连接符。
6、value函数。
7、If系列函数,If(,,),If(or(,),,),Ifiserror(),Sumif(),If(Iserror(vlookup(),,vlookup())。
8、row()函数、
9、Concatenate()函数。
10、Month(),Date(),Year()注意学会相关的嵌套。
11、Index函数与Match函数的组合应用。
12、Vlookup函数等等
二、Excel技巧
1、ctrl+g定位空值填充。
2、ctrl+H替换替换过程巧用通配符“*”(星号)
3、数据分列技巧(工商银行导出的网银格式是csv格式,数据有的不能加总,运用数据分列,可以单独分离出能够用于加总的数据)、数据分列真的很好用,可以分列出日期格式,可以分列出单独的月份,等等,具体情况具体应用。
4、crtl+shift+enter输入数组,事半功倍。
5、Ctrl+Shift+End。
6、构造函数时,按F4可以迅速打出"$",绝对引用与相对引用切换。
7、数据透视表
实例1:
不规则文字与数字的分离(如果每行文字长度都一样,可以用数据分列分离出来),如B列名称与日期在同一单元格中现在我们要单独提出日期和相关文字,使之出现在不同列。
函数介绍:
1、B1为原始数据,C1=len(B1)为返回文本字符串的字符数 C2==lenb(B1)为返回文本字符串的字节数 。
2、C1运行出的结果是5+8=13,D1运行出的结果是5*2+8=18,大家看出区别了么?
Len函数与Lenb函数区别就在于1个汉字相当于2个字节(这就是为什么C2=5*2+8,乘以2)。
3、E1中(D1-C1),D1与C1之差就是汉字的个数(18-13=5),再套个Left函数,就成功的把汉字部分提取出来了。
4、F1,跟E1原理相同,2*C1-D1就是数字的个数(2*13-18=8),套个Right函数,成功提取出数字部分。
至此完毕。
运行结果如下
(实际上C1D1公式可以省略直接代入到E1,F1单元格。
如E1=Left(B1,lenb(B1)-Len(B1)))
实例2:
把需要补充的数据粘贴到筛选后的可见单元格中(注意不是把筛选后的单元格数据粘贴到别处,这个直接复制粘贴就好)实例:
(实例介绍,该实例是本人在实务工作中碰到的问题,当时审计的是一家香港公司,他们的序时账是按科目来的,一个科目从1月开始到12月结束,到最后有个期末余额,接着另一个科目又开始该科目的1-12月序时,然后期末余额,如此下去。
问题的关键在于 该序时账并没有提供任何相关科目的科目名称及科目代码,仅有一个期末余额。
在这种背景下,本人通过vlooup函数根据序时账中的期末余额,在科目余额表中倒查出科目名称科目代码。
现在我要做的就是给序时账补充对应的科目名称,科目代码。
)
原始数据如下
表一 简化的序时账
表二 简化的科目余额表(需要粘贴的数据)
步骤一,先插入两列AB列,A列编号序位
步骤二,筛选F列,筛选出本科目期末余额如
步骤三,选中B10到B20,按F5,定位条件,选择右边的可见单元格,在B10单元格中输入1,然后按住Ctrl+Enter,自动填充,效果如下
步骤四,点筛选选项框,本表取消筛选,效果如下
步骤五,对B列排序,选择降序排列,效果如下
步骤六,把科目余额表相对应的已有数据直接复制粘贴到D列,E列,效果如下
步骤七,对A列进行升序排列,效果如下
步骤八,填充科目名称及科目代码。
选中D1到D20,按F5,定位条件,空值,在D19单元格输入=D20,Ctrl+Enter自动填充。
E列同理。
效果如下
步骤九, 复制D列,E列,然后选择性粘贴为数值。
筛选G列,选中空白单元格,删除显示行
步骤十,对本表取消筛选(单击筛选选项框即可),效果如下,这样就把这份港式序时账,变成了中式序时账。
效果对比(下图为原始表)
以下一些Excel技能,须熟练掌握的:
∙格式刷
∙首行固定(视图->冻结窗格)
∙排序及筛选(数据->筛选)
∙单元格换行(Alt+回车)
∙批量生成序列(如选定两格,自动生成数字序列,或时间序列)
1.一定要善用冻结窗口,通常是固定头几行头几列,这样在一直往右拖的时候,也知道自己在看的是哪个项目。
2.善用颜色来分区表的不同功能区,让人一目了然,当然别用大红什么的当底色,不过颜色搭配和字体设计是一门大学问,多尝试,慢慢体会吧,面对一张赏心悦目的表也能提高工作效率啊。
几点个人的经验:
英文字体Arial当正文会比较好,10号。
中文字体,如果不是港台地区的话,就别用繁体了,放弃自己那点小癖好吧,这个在别人没装字体的电脑上是奇丑的。
居中还是左右对齐,要注意。
3.尝试取消网格线,alt加w,点vg。
尤其是给别人看的报告,是不是清爽很多。
必备技巧:
练熟ctrl加shift加上下左右键,简直可以升华人生,选定区域再简单不过了,再去拉滚轴是不是太单纯了点?
F4乃神器,除了楼上说的固定的作用外,还有重复操作的妙用,比如插入多行,你只需要插一次,然后猛击F4,想插几行就几行。
alt+4,追踪引用公式,alt+5,取消追踪。
这是我最常用的快捷键了。
ctrlz,撤销操作,返回上一步,又一神器。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 函数 技巧