Excel函数技巧_excel函数技巧

Excel函数技巧由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“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函数，成功提取出数字部分。至此 完毕。运行结果如下

（实际上 C1 D1公式可以省略直接代入到E1，F1单元格。如 E1=Left(B1,lenb(B1)-Len(B1)））

实例2：把需要补充的数据粘贴到筛选后的可见单元格中（注意不是把筛选后的单元格数据粘贴到别处，这个直接复制粘贴就好）实例：

（实例介绍，该实例是本人在实务工作中碰到的问题，当时审计的是一家香港公司，他们的序时账是按科目来的，一个科目从1月开始到12月结束，到最后有个期末余额，接着 另一个科目又开始该科目的1-12月序时，然后期末余额，如此下去。问题的关键在于 该序时账并没有提供任何相关科目的科目名称及科目代码，仅有一个期末余额。在这种背景下，本人通过vlooup函数根据序时账中的期末余额，在科目余额表中倒查出 科目名称 科目代码。现在我要做的就是给序时账补充对应的科目名称，科目代码。）

原始数据如下 表一 简化的序时账

表二 简化的科目余额表（需要粘贴的数据）

步骤一，先插入两列A B列，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，取消追踪。这是我最常用的快捷键了。ctrl z，撤销操作，返回上一步，又一神器。