Excel培训教材数据处理篇(更新.ppt

李双林李双林 编制编制东菱凯琴集团 系列培训教程EXCEL培训培训之之数据处理篇数据处理篇EXCEL系列培训教程 之 数据处理篇EXCELEXCEL数据的基本运算数据的基本运算EXCELEXCEL数据的获取数据的获取EXCELEXCEL数据的高级处理数据的高级处理EXCEL系列培训教材 之 数据处理篇数据基本运算数据基本运算欢迎大家回来继续听我大放阙词。欢迎大家回来继续听我大放阙词。上一堂课学习的主要是上一堂课学习的主要是ExcelExcel的基本操作，下面我们来探讨一下的基本操作，下面我们来探讨一下ExcelExcel的的精髓：数据管理、统计及分析。精髓：数据管理、统计及分析。对于我们用来存放数据的原始工作表，强烈推荐采用类似数据库的格式，对于我们用来存放数据的原始工作表，强烈推荐采用类似数据库的格式，即不设任何表头、表尾及其他为了美观而设置的花哨格式，我们需要的是一即不设任何表头、表尾及其他为了美观而设置的花哨格式，我们需要的是一个干干净净的纯数据表格。如图个干干净净的纯数据表格。如图1 1：表表格格还还可可以以向向下下和和向向右右延延伸伸，不不过过为为了了数数据据处处理理的的方方便便，不不要要随随意意在在工工作作表表中中间间改改变变格格式式，以以免免给给自自己己的的工工作作带带来来麻麻烦烦。下下面面还还是是以以工工资资表表为为例向大家介绍数据的录入与处理。例向大家介绍数据的录入与处理。图1EXCEL系列培训教材 之 数据处理篇 下面我们以实例来演示一下数据的合并与分割吧。下面我们以实例来演示一下数据的合并与分割吧。EXCELEXCEL中中的的连连接接符符号号是是“&”“&”，如如图图2 2，我我们们只只要要在在部部门门与与职职位位中中间间输输入入连接符号连接符号“&”“&”，就可以将部门与职位连成一体了，就可以将部门与职位连成一体了，图2 将将鼠鼠标标移移到到单单元元格格的的右右下下角角，出出现现黑黑色色实实心心小小十十字字，按按住住不不放放向向下下拖拖动动复复制制公公式式，就就可可以以把把所所有有人人员员的的部部门门与与职职位位连连起起来来了。如图了。如图3 3：图3EXCEL系列培训教材 之 数据处理篇 如如果果我我们们对对处处理理结结果果满满意意，可可以以将将公公式式取取消消掉掉；先先选选择择范范围围，Ctrl+CCtrl+C，点右键点右键选择性粘贴选择性粘贴数值，数值，OKOK！同样，下面我们来学习一下如何将部门与职位分开。同样，下面我们来学习一下如何将部门与职位分开。首首先先，选选择择你你想想要要分分列列的的范范围围，点点击击菜菜单单栏栏上上的的“数数据据”“分分列列”，如图，如图3 3注意：选择的范围不能跨列操作！注意：选择的范围不能跨列操作！右右边边一一定定要要有有足足够够的的空空间间容容纳纳分列后的数据。分列后的数据。图3EXCEL系列培训教材 之 数据处理篇 在在“文本向导文本向导”对话框中选择对话框中选择“固定宽度固定宽度”，如图，如图4 4图图5 5图图6 6：图4图5图6EXCEL系列培训教材 之 数据处理篇 点点击击“完完成成”，处处理理结结果果如如图图7 7：我我们们可可以以看看到到，部部门门与与职职位位已已经经分分成成两两列列了了！（财财务务部部每每月月从从ERPERP里里面面导导出出的的数数据据，都都必必须须先先分分列列才才能能使使用用，但但由于当初的基本资料录入不规范，极大地妨碍了结算工作的效率！）由于当初的基本资料录入不规范，极大地妨碍了结算工作的效率！）图7EXCEL系列培训教材 之 数据处理篇 接着我们来讲一下接着我们来讲一下ExcelExcel中的公式运算，其实公式运算不外乎中的公式运算，其实公式运算不外乎“加减乘除加减乘除”，它与数学中的四则运算的规则是一样的，只不过用它与数学中的四则运算的规则是一样的，只不过用“*”“*”代表乘号，用代表乘号，用“/”“/”代表除代表除号；如图号；如图8 8：图8 注意，全勤天数注意，全勤天数“I2”“I2”请使用绝对引用格式，这样在向下复制时才不会出错。请使用绝对引用格式，这样在向下复制时才不会出错。图9EXCEL系列培训教材 之 数据处理篇基本函数基本函数 在刚才的表中，本月工资栏中的工资金额出现了在刚才的表中，本月工资栏中的工资金额出现了3 3位小数；大家都知道，工资位小数；大家都知道，工资计算都是精确到计算都是精确到2 2位小数的，所以我们必须对本月工资进行位小数的，所以我们必须对本月工资进行“四舍五入四舍五入”处理，这处理，这就要用到函数就要用到函数ROUND()ROUND()。ROUND(number,num_digits)Number需要进行四舍五入的对象。需要进行四舍五入的对象。Num_digits指定的位数，按此位数进行四舍五入指定的位数，按此位数进行四舍五入。如图如图1010：图10图11 处理完后的结果如图处理完后的结果如图1111所示，计算后的金额已所示，计算后的金额已经全部四舍五入了。经全部四舍五入了。EXCEL系列培训教材 之 数据处理篇 下面，有请大家最熟悉的求和函数下面，有请大家最熟悉的求和函数SUM()SUM()闪亮登场！闪亮登场！SUM(number1,number2,.)Number1,number2,.Number1,number2,.为为 1 1 到到 30 30 个需要求和的参数，可以是数值、单个需要求和的参数，可以是数值、单元格，也可以是区域。元格，也可以是区域。如图如图1212：图12EXCEL系列培训教材 之 数据处理篇 根据公司规定，当月无缺勤可享受根据公司规定，当月无缺勤可享受3030元的全勤奖，这就需要我们对出勤元的全勤奖，这就需要我们对出勤天数进行判断。下面我们来熟悉一下另一个应用广泛的函数：天数进行判断。下面我们来熟悉一下另一个应用广泛的函数：IF()IF()。IF(logical_test,value_if_true,value_if_false)logical_testlogical_test是一个条件判断式，如果条件为真是一个条件判断式，如果条件为真(True)(True)，则返回或者执行，则返回或者执行 value_if_truevalue_if_true，如果条件为假，如果条件为假(False)(False)，则返回或执行，则返回或执行value_if_falsevalue_if_false；这样说明白了吧？那就试试吧！这样说明白了吧？那就试试吧！图13图14 双击填充柄向下复制后，结果如图双击填充柄向下复制后，结果如图1414；大；大家可以看到，凡是不满勤的人员，都没有家可以看到，凡是不满勤的人员，都没有3030元元的全勤奖。的全勤奖。EXCEL系列培训教材 之 数据处理篇 现在我们要将工资计算资料里面的全勤奖添加到工资表中。现在我们要将工资计算资料里面的全勤奖添加到工资表中。在此先向大家泣血推荐一个妙用无穷的函数在此先向大家泣血推荐一个妙用无穷的函数VLOOKUP()VLOOKUP()（广告？）（广告？）。它在我。它在我个人的数据处理工作中立下了不可磨灭个人的数据处理工作中立下了不可磨灭andand载入史册的汗马功劳。载入史册的汗马功劳。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)（简直是天书嘛！喂喂！各位同学不要睡觉！起床罗！）（简直是天书嘛！喂喂！各位同学不要睡觉！起床罗！）其实，其实，VLOOKUPVLOOKUP是一个条件查找函数，是一个条件查找函数，lookup_valuelookup_value是查找条件，是查找条件，table_arraytable_array是查找的范围，是查找的范围，col_index_numcol_index_num是取值的列数，是取值的列数，range_lookuprange_lookup是指查找精度，要求是指查找精度，要求100%100%符合还是近似值符合还是近似值。为了更形象的说明，我们还是通过实例来演示吧。为了更形象的说明，我们还是通过实例来演示吧。（有人嘀（有人嘀咕：早该这样了！）咕：早该这样了！）我们在工作中经常遇到这样的情况：两个工作表中分别是仓库进料和发料的数我们在工作中经常遇到这样的情况：两个工作表中分别是仓库进料和发料的数据，但是材料顺序不一致，要想合并成一张进销存报表怎么办？据，但是材料顺序不一致，要想合并成一张进销存报表怎么办？（什么？排序？再（什么？排序？再一个一个找？愚公啊，一个一个找？愚公啊，I I 服了服了YOUYOU！我！我FTingFTing）（齐声高唱：）（齐声高唱：VLOOKUPVLOOKUP！）！）在工在工资计算中，要想直接调用人力资源部提供的考勤数据，怎么办？那可是资计算中，要想直接调用人力资源部提供的考勤数据，怎么办？那可是1500015000多人多人啊！啊！（齐声高唱：还是（齐声高唱：还是VLOOKUPVLOOKUP！）（啊！）（啊&%*%&*#&$%*&%*%&*#&$%*！TMD!TMD!谁扔的砖头？！谁扔的砖头？！）#h#h EXCEL系列培训教材 之 数据处理篇 如图如图1515所示，我们要在所示，我们要在Sheet2Sheet2工作表中，在工作表中，在“B:H”“B:H”列范围内，查找员工编列范围内，查找员工编号等于号等于“000001”“000001”的那一行，找到后将该行从的那一行，找到后将该行从“B”“B”列往后数第列往后数第7 7列的值取过来；列的值取过来；如果找不到则显示出错提示，如果找不到则显示出错提示，另外，我们还可以通过点击编辑栏旁边的另外，我们还可以通过点击编辑栏旁边的“fxfx”启动函数精灵向导来逐步完启动函数精灵向导来逐步完成（不推荐）。成（不推荐）。图15 如果我们将如果我们将IFIF（）与（）与VLOOKUPVLOOKUP（）嵌套使用，再加上一个（）嵌套使用，再加上一个ISERRORISERROR（），就可（），就可以去掉烦人的出错提示，完整公式如下：以去掉烦人的出错提示，完整公式如下：=IF(ISERROR(VLOOKUP(B4,Sheet2!B:H,7,FALSE),VLOOKUP(B4,Sheet2!B:H,7,F=IF(ISERROR(VLOOKUP(B4,Sheet2!B:H,7,FALSE),VLOOKUP(B4,Sheet2!B:H,7,FALSE)ALSE)EXCEL系列培训教材 之 数据处理篇 在我们的工作中，经常要对一大堆数据按给定的条件进行汇总，怎么办呢在我们的工作中，经常要对一大堆数据按给定的条件进行汇总，怎么办呢？没错，很多人都知道在？没错，很多人都知道在EXCELEXCEL中有个分类汇总的功能。但假如我不是按类别中有个分类汇总的功能。但假如我不是按类别呢？又或者我要将结果直接应用到其他地方去呢？呢？又或者我要将结果直接应用到其他地方去呢？这种情况下，分类汇总就已经无用武之地了。不过，好彩我们还有这种情况下，分类汇总就已经无用武之地了。不过，好彩我们还有SUMIFSUMIF()()这个宝贝，要不还真不知该咋办呢！这个宝贝，要不还真不知该咋办呢！图16 如图如图1616所示，我们要将所示，我们要将Sheet2Sheet2中的扣款汇总后录入到工资表的中的扣款汇总后录入到工资表的“扣款扣款”栏栏内，用内，用SUMIFSUMIF（）轻轻松松就可以搞定了！（）轻轻松松就可以搞定了！SUMIF(range,criteria,sum_range)Range Range 为用于条件判断的单元格区域。为用于条件判断的单元格区域。Criteria Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。字、表达式或文本。Sum_range Sum_range 是需要求和的实际单元格。是需要求和的实际单元格。EXCEL系列培训教材 之 数据处理篇 就这样，工资计算的基本工作就算初步完成了！就这样，工资计算的基本工作就算初步完成了！呵呵，别激动！财务部做事一向是十分严谨的，所以对工作进行复查也是呵呵，别激动！财务部做事一向是十分严谨的，所以对工作进行复查也是必不可少的一环！必不可少的一环！下面我们通过示例来说明一下如何对人事编号进行审核的！下面我们通过示例来说明一下如何对人事编号进行审核的！LEN(text)Text Text 是要查找其长度的文本。空格将作为字符进行计数。是要查找其长度的文本。空格将作为字符进行计数。公司的人事编号统一都是公司的人事编号统一都是6 6位数，所以我们先用位数，所以我们先用IENIEN（）判断一下工作表中（）判断一下工作表中编号的长度是否正确。如图编号的长度是否正确。如图1717：图17EXCEL系列培训教材 之 数据处理篇 点击填充柄向下复制，然后筛选出等于点击填充柄向下复制，然后筛选出等于6 6的数，如图的数，如图1818：所有人事编号长：所有人事编号长度有误的已经全部找到，下面就是如何去改正的问题了。度有误的已经全部找到，下面就是如何去改正的问题了。图18 很多同事交上来的电子表格中，自以为是地将人事编号设为很多同事交上来的电子表格中，自以为是地将人事编号设为“邮政编码邮政编码”格式，这样表面上看起来或者打印出来都是六位数，可是实际上还是数值格式，格式，这样表面上看起来或者打印出来都是六位数，可是实际上还是数值格式，真实的长度用真实的长度用LENLEN（）一测就知道了。（）一测就知道了。但是在进行取值运算时，但是在进行取值运算时，EXCELEXCEL会将会将“邮政编码邮政编码”格式的人事编号与文本格式的人事编号与文本格式的人事编号判断为截然不同的两个数据；所以如果某个月你因为没有考勤格式的人事编号判断为截然不同的两个数据；所以如果某个月你因为没有考勤收不到工资时，大可跑到考勤文员那里，拧住她的小耳朵问问她收不到工资时，大可跑到考勤文员那里，拧住她的小耳朵问问她 你为什么不来听李老师的课！你为什么不来听李老师的课！EXCEL系列培训教材 之 数据处理篇 在工资计算过程中，由于职位变动等原因，不可避免会存在一个人事编号在工资计算过程中，由于职位变动等原因，不可避免会存在一个人事编号有两份工资的状况，下面我们来把这些人纠出来再说。有两份工资的状况，下面我们来把这些人纠出来再说。COUNTIF(range,criteria)Range Range 为需要计算其中满足条件的单元格数目的单元格区域。为需要计算其中满足条件的单元格数目的单元格区域。Criteria Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。表达式或文本。图19 在这里在这里COUNTIFCOUNTIF（）函数的意思是：在（）函数的意思是：在“B”“B”列中，统计列中，统计“000001”“000001”这个员这个员工编号的个数。这样我们马上就知道谁将会收到几份工资。工编号的个数。这样我们马上就知道谁将会收到几份工资。是不是比火眼金睛是不是比火眼金睛还管用啊？呵呵，是你的就不会少你的，不是你的就别太贪心了。还管用啊？呵呵，是你的就不会少你的，不是你的就别太贪心了。EXCEL系列培训教材 之 数据处理篇 将工资表先按个数的降序，再按员工编号的升序排序，最后再筛选一下大将工资表先按个数的降序，再按员工编号的升序排序，最后再筛选一下大于于“1”“1”的个数，如果如图的个数，如果如图2020：剩下的工作就是好好核对这些人员的工资是否：剩下的工作就是好好核对这些人员的工资是否存在问题了。存在问题了。图20EXCEL系列培训教材 之 数据处理篇 在在EXCELEXCEL的学习中，一定要将学习与工作结合起来，这样才能达到事半功的学习中，一定要将学习与工作结合起来，这样才能达到事半功倍的效果。倍的效果。（想当年我学习那阵才叫一个苦呢，（想当年我学习那阵才叫一个苦呢，5555555555555555555555）另外一定要将动脑与动手相结合，多想想如何才能简化工作，可以让你少另外一定要将动脑与动手相结合，多想想如何才能简化工作，可以让你少走许多弯路。走许多弯路。（呵呵，最重要的是时刻记住怎样偷懒。）（呵呵，最重要的是时刻记住怎样偷懒。）当我们遇到疑问，无法在周围找到合适的解决方案时，不要忘记还有当我们遇到疑问，无法在周围找到合适的解决方案时，不要忘记还有internetinternet这座宝库还在等着你去挖掘呢！这座宝库还在等着你去挖掘呢！对不能上网的同事深表同情，口头安慰！对不能上网的同事深表同情，口头安慰！