Excel培训教材_数据处理篇

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

EXCEL培训之

数据处理篇

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

EXCEL数据的基本运算

EXCEL数据的获取EXCEL数据的高级处理

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

数据基本运算 欢迎大家回来继续听我大放阙词。 上一堂课学习的主要是Excel的基本操作，下面我们来探讨一下Excel 的精髓：数据管理、统计及分析。 对于我们用来存放数据的原始工作表，强烈推荐采用类似数据库的格 式，即不设任何表头、表尾及其他为了美观而设置的花哨格式，我们需要 的是一个干干净净的纯数据表格。如图1:

图1

表格还可以向下和向右延伸，不过为了数据处理的方便，不要随意在 工作表中间改变格式，以免给自己的工作带来麻烦。下面还是以工资表为 例向大家介绍数据的录入与处理。

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

下面我们以实例来演示一下数据的合并与分割吧。 EXCEL中的连接符号是“&”,如图2,我们只要在部门与职位中间输入 连接符号“&”,就可以将部门与职位连成一体了，图2

将鼠标移到单元格的 右下角，出现黑色实心小 十字，按住不放向下拖动 复制公式，就可以把所有 人员的部门与职位连起来 了。如图3:

图3

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

如果我们对处理结果满意，可以将公式取消掉；先选择范围，Ctrl+C, 点右键 选择性粘贴 数值，OK!~ 同样，下面我们来学习一下如何将部门与职位分开。首先，选择你想要分列的范围， 点击菜单栏上的 “ 数 据” “ 分 列”,如图3 注意：选择的范围不能跨列操作！ 右边一定要有足够的空间容纳 分列后的数据。

图3

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

在“文本向导”对话框中选择“固定宽度”,如图4 图5 图6:图4

图5

图6

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

点击“完成”,处理结果如图7:我们可以看到，部门与职位已经分成 两列了！(财务部每月从ERP里面导出的数据，都必须先分列才能使用，但 由于当初的基本资料录入不规范，极大地妨碍了结算工作的效率！)图7

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

接着我们来讲一下Excel中的公式运算，其实公式运算不外乎“加减乘除”, 它与数学中的四则运算的规则是一样的，只不过用“*”代表乘号，用“/”代表除 号；如图8: 图8

注意，全勤天数“I2”请使用绝对引用格式，这样在向下复制时才不会出错。

图9

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

基本函数 在刚才的表中，本月工资栏中的工资金额出现了3位小数；大家都知道，工资 计算都是精确到2位小数的，所以我们必须对本月工资进行“四舍五入”处理，这 就要用到函数ROUND()。 ROUND(number,num_digits) Number 需要进行四舍五入的对象。 Num_digits 指定的位数，按此位数进行四舍五入。如图10: 图10

处理完后的结果如图11所示，计算后的金额已 经全部四舍五入了。 图11

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

下面，有请大家最熟悉的求和函数SUM()闪亮登场！ SUM(number1,number2, ...) Number1, number2, ... 为 1

到 30 个需要求和的参数，可以是数值、单元 格，也可以是区域。如图12:

图12

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

根据公司规定，当月无缺勤可享受30元的全勤奖，这就需要我们对出 勤天数进行判断。下面我们来熟悉一下另一个应用广泛的函数：IF()。 IF(logical_test,value_if_true,value_if_false) logical_test是一个条件判断式，如果条件为真(True),则返回或者执行 value_if_true,如果条件为假(False),则返回或执行value_if_false; 这样说明白了吧？那就试试吧！图13

双击填充柄向下复制后，结果如图14;大 家可以看到，凡是不满勤的人员，都没有30元 的全勤奖。

图14

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

现在我们要将工资计算资料里面的全勤奖添加到工资表中。 在此先向大家泣血推荐一个妙用无穷的函数VLOOKUP() (广告？) 。它在我 个人的数据处理工作中立下了不可磨灭and载入史册的汗马功劳。 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) (简直是天书嘛！喂喂！各位同学不要睡觉！起床罗！) 其实，VLOOKUP是一个条件查找函数，lookup_value是查找条件， table_array是查找的范围，col_index_num是取值的列数，range_lookup是指查 找精度，要求100%符合还是近似值。为了更形象的说明，我们还是通过实例来演 示吧。(有人嘀咕：早该这样了！) 我们在工作中经常遇到这样的情况：两个工作表中分别是仓库进料和发料的 数据，但是材料顺序不一致，要想合并成一张进销存报表怎么办？(什么？排序？ 再一个一个找？愚公啊，I 服了YOU!我FTing~~~~~)(齐声高唱：VLOOKUP!) 在工资计算中，要想直接调用人力资源部提供的考勤数据，怎么办？那可是15000 多人啊！(齐声高唱：还是VLOOKUP!)(啊——&%*%&^*#…&$@%*!TMD!谁扔的 砖头？!)#h

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

如图15所示，我们要在Sheet2工作表中，在“B:H”列范围内，查找员工编 号等于“000001”的那一行，找到后将该行从“B”列往后数第7列的值取过来； 如果找不到则显示出错提示， 另外，我们还可以通过点击编辑栏旁边的“fx”启动函数精灵向导来逐步完 成(不推荐)。 图15

如果我们将IF()与VLOOKUP()嵌套使用，再加上一个ISERROR(),就 可以去掉烦人的出错提示，完整公式如下： =IF(ISERROR(VLOOKUP(B4,Sheet2!B:H,7,FALSE)),,VLOOKUP(B4,Sheet2!B:H,7, FALSE))

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

在我们的工作中，经常要对一大堆数据按给定的条件进行汇总，怎么办呢？ 没错，很多人都知道在EXCEL中有个分类汇总的功能。但假如我不是按类别呢？ 又或者我要将结果直接应用到其他地方去呢？ 这种情况下，分类汇总就已经无用武之地了。不过，好彩我们还有SUMIF() 这个宝贝，要不还真不知该咋办呢！

图16SUMIF(range,criteria,sum_range) Range 为用于条件判断的单

元格区域。 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、 表达式或文本。 Sum_range 是需要求和的实际单元格。 如图16所示，我们要将Sheet2中的扣款汇总后录入到工资表的“扣款”栏 内，用SUMIF()轻轻松松就可以搞定了！

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

就这样，工资计算的基本工作就算初步完成了！ 呵呵，别激动！财务部做事一向是十分严谨的，所以对工作进行复查也是 必不可少的一环！ 下面我们通过示例来说明一下如何对人事编号进行审核的！LEN(text) Text 是要查找其长度的文本。空格将作为字符进行计数。

图17 公司的人事编号统一都是6位数，所以我们先用IEN()判断一下工作表中 编号的长度是否正确。如图17:

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

点击填充柄向下复制，然后筛选出等于6的数，如图18:所有人事编号长 度有误的已经全部找到，下面就是如何去改正的问题了。

图18 很多同事交上来的电子表格中，自以为是地将人事编号设为“邮政编码” 格式，这样表面上看起来或者打印出来都是六位数，可是实际上还是数值格式， 真实的长度用LEN()一测就知道了。 但是在进行取值运算时，EXCEL会将“邮政编码”格式的人事编号与文本 格式的人事编号判断为截然不同的两个数据；所以如果某个月你因为没有考勤 收不到工资时，大可跑到考勤文员那里，拧住她的小耳朵问问她—— 你为什么不来听课！

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

在工资计算过程中，由于职位变动等原因，不可避免会存在一个人事编号 有两份工资的状况，下面我们来把这些人纠出来再说。 COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。 Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表 达式或文本。

图19 在这里COUNTIF()函数的意思是：在“B”列中，统计“000001”这个员工 编号的个数。这样我们马上就知道谁将会收到几份工资。是不是比火眼金睛还 管用啊？呵呵，是你的就不会少你的，不是你的就别太贪心了。

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

将工资表先按个数的降序，再按员工编号的升序排序，最后再筛选一下大 于“1”的个数，如果如图20:剩下的工作就是好好核对这些人员的工资是否存 在问题了。

图20

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

在EXCEL的学习中，一定要将学习与工作结合起来，这样才能达到事半功 倍的效果。(想当年我学习那阵才叫一个苦呢，55555555555) 另外一定要将动脑与动手相结合，多想想如何才能简化工作，可以让你少 走许多弯路。(呵呵，最重要的是时刻记住怎样偷懒。) 当我们遇到疑问，无法在周围找到合适的解决方案时，不要忘记还有 internet这座宝库还在等着你去挖掘呢！ 对不能上网的同事深表同情，口头安

慰！

能提升你100000倍工作效率的、很多人不知道的,非常实用的Excel技巧与技术讲解,excel,技巧,方法,分析,VBA,控件,单元格吗,密码,字符

完成

EXCEL培训之

数据处理篇