中国移动EXCEL技巧高手培训.ppt
享受高效办公新生活,中国移动广东公司品牌课程系列第2页/共108页,如何玩,游戏形式课程以”分享”与”辅导”相结合，老师会在课堂里提出问题,大家可以选择对问题的解决“分享”或者“辅导”；游戏规则主动分享的个人可以获得任务相应星级难度的奖励印花；主动回答老师问题,并且回答正确也可获得1个印花；奖品兑换课程结束后进行礼品竞投，价高者得；竞投过程中不允许炒卖，叫价者若炒卖，需要按实上缴；注意事项：正在分享的同事在处理问题过程中,大家需要保持安静,分享完成后才可以补充；,HOW TO PLAY,中国移动广东公司品牌课程系列第3页/共108页,Microsoft Excel 分享篇,中国移动广东公司品牌课程系列第4页/共108页,目 录,中国移动广东公司品牌课程系列第5页/共108页,标题栏,鼠标移至Excel顶部，查看文件的标题。当我们只选定一个工作表时，就如图中标识所示：,当我们同时选定多个工作 表时，就如图中标识所示：,中国移动广东公司品牌课程系列第6页/共108页,工具栏的显示与隐藏,图一为快速调用工具栏图二为自定义显示工具栏图三为如何移动工具栏,图二：,图一：,图三：,中国移动广东公司品牌课程系列第7页/共108页,自定义工具栏,自定义工具栏：工具自定义“工具栏”选项，选择“新建”，给工具栏命名。左图为已设置好的，右图为设置界面。,中国移动广东公司品牌课程系列第8页/共108页,其他设置,任务栏设置公式编辑栏的调出,中国移动广东公司品牌课程系列第9页/共108页,目 录,中国移动广东公司品牌课程系列第10页/共108页,快速输入技巧,录入快捷键（常用）F2 反向选中单元格TAB 复制上一单元格内容Shift+TAB 显示当前时间Ctrl+D 选中单元格输入内容Ctrl+S 选中下一单元格Ctrl+R 显示当前日期Ctrl+；复制左边单元格内容Ctrl+Shift+；保存,中国移动广东公司品牌课程系列第11页/共108页,快速输入技巧,录入快捷键（常用）F2 反向选中单元格 TAB 复制上一单元格内容Shift+TAB 显示当前时间 Ctrl+D 选中单元格输入内容 Ctrl+S 选中下一单元格 Ctrl+R 显示当前日期 Ctrl+；复制左边单元格内容Ctrl+Shift+；保存,中国移动广东公司品牌课程系列第12页/共108页,填充柄,自然序列填充 等差等比序列填充 文本序列填充,中国移动广东公司品牌课程系列第13页/共108页,自定义序列,自定义序列的用法,修改自定义序列,中国移动广东公司品牌课程系列第14页/共108页,工作组输入,如何将工作表成组 成组工作表的数据录入操作,中国移动广东公司品牌课程系列第15页/共108页,特殊文本的输入,手动录入,预设格式录入,中国移动广东公司品牌课程系列第16页/共108页,目 录,中国移动广东公司品牌课程系列第17页/共108页,排 序,简单排序,复杂排序,中国移动广东公司品牌课程系列第18页/共108页,筛 选,自动筛选 对包含特定文本的行进行区域筛选 快速筛选（见图一）高级筛选 筛选出不重复数据（见图二）,图一：,图二：,中国移动广东公司品牌课程系列第19页/共108页,条件格式,条件格式的设置可以根据公式设定，也可以按单元格的数字进行设定。公式设定可以做比较复杂的条件格式。,中国移动广东公司品牌课程系列第20页/共108页,目 录,中国移动广东公司品牌课程系列第21页/共108页,数据选择技巧,方法一：Shift键+单元格边框双击方法二：Shift+Ctrl+选择同列数据；Shift+Ctrl+选择同行数据。,中国移动广东公司品牌课程系列第22页/共108页,插入与隐藏行列,插入 隐藏行列 取消隐藏,中国移动广东公司品牌课程系列第23页/共108页,数据表的转置,使用“选择性粘贴”实现,中国移动广东公司品牌课程系列第24页/共108页,工作表操作,工作表标签重命名 插入、删除、移动复制工作表,中国移动广东公司品牌课程系列第25页/共108页,工作表操作,设置工作表默认属性,中国移动广东公司品牌课程系列第26页/共108页,窗格操作,拆分窗格,中国移动广东公司品牌课程系列第27页/共108页,窗格操作,拆分窗格,中国移动广东公司品牌课程系列第28页/共108页,目 录,中国移动广东公司品牌课程系列第29页/共108页,Excel函数简介,插入函数,中国移动广东公司品牌课程系列第30页/共108页,绝对引用与相对引用,绝对引用使用方法 相对引用使用方法,中国移动广东公司品牌课程系列第31页/共108页,函数,常用查找函数,Vlookup()函数。=VLOOKUP（找什么，在哪个区域找，要显示哪个值，0）,中国移动广东公司品牌课程系列第32页/共108页,常用数学函数,Sum（）函数 Average（）函数 Min（）与Max（）函数 Count（）函数与Counta（）函数,中国移动广东公司品牌课程系列第33页/共108页,常用文本函数,Left()函数Right()函数Mid()函数Len()函数文本连接符&,中国移动广东公司品牌课程系列第34页/共108页,文本处理技术(精确查找字符位置),任务要求：要求在B列判断A列的商品型号是否包含大写字母“CS“，若包含则显示“包含”。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第35页/共108页,文本处理技术(精确查找字符位置),Explain:FIND(“CS”,A3),表示在A3单元格里查找带有大写字母CS字符然后返回要查找文本所在字符串的起始位置编号,若找不到则显示#Value。,Explain:ISNUMBER(FIND(“CS”,A3),若FIND函数找到值时,则显示数值,否则显示#Value。ISNUMBER根据FIND的结果进行判断，若为数值则返回true，否则返回false,Explain:若ISNUMBER返回为TRUE时，IF函数则返回“包含”，否则返回“不包含”,FIND,ISNUMBER,IF,公式：=IF(ISNUMBER(FIND(CS,A3),包含,不包含),注意：FIND仅适用于精确查找，并区分字母的大小写,中国移动广东公司品牌课程系列第36页/共108页,文本处理技术(模糊查找字符位置),任务要求：根据A列的卡号编号，判断是贵宾卡或是普通卡，以A结尾(卡号尾位不区分大小写)。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第37页/共108页,文本处理技术(模糊查找字符位置),Explain:*号表示所有字符，？表示单个字符。,Explain:SEARCH(“*A”,A3),“表示在A3单元格里查找字符位数为8位，且最后字符为大写的A。然后返回要查找文本所在字符串的起始位置编号,若找不到则显示#Value。,Explain:若ISNUMBER返回为TRUE时，IF函数则返回“贵宾卡”，否则返回“普通卡”,通配符,SEARCH,IF,公式：=IF(ISNUMBER(SEARCH(“*A,A3),贵宾卡,普通卡),注意：Search适用于模糊查找，且可不区分字母的大小写。若查找真正的*号和?号，可以使用*和?进行查找。,中国移动广东公司品牌课程系列第38页/共108页,文本处理技术(截取右边字符),任务要求：根据A列的班级和姓名，截取右边字符，注意姓名字数可能是三个，也可能是四个。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第39页/共108页,文本处理技术(截取右边字符),Think Road:班级和姓名列，以冒号相分隔，所以若先找出冒号前有多少个字符，然后再计出总共有多少字符，相减后即可得出姓名占的字符数，用RIGHT函数即可读取。,Explain:RIGHT函数表示从右向左读取字符，=RIGHT(A3,2)表示在A3单元格里从右向左读取2个字符。,Explain:Len函数是用来测量字符的位数，返回结果是位数的数值。,思路,RIGHT,LEN,公式：=RIGHT(A3,LEN(A3)-FIND(:,A3),中国移动广东公司品牌课程系列第40页/共108页,文本处理技术(截取中间字符),任务要求：从客户地址列中截取出城市名称，显示在B列中。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第41页/共108页,文本处理技术(截取中间字符),Think Road:客户地址列中有以下规律：XXX省YYY市，所以只要拿到“省”和“市”作为标识符，借助MID函数，即可将城市分离出来。,Explain:读取单元格中间值，使用方法是：=MID(对象单元格，开始字符编号，取数范围）,思路,MID,公式：=MID(A3,FIND(省,A3)+1,FIND(市,A3)-FIND(省,A3),中国移动广东公司品牌课程系列第42页/共108页,文本处理技术(截取中间字符),任务要求：从科目名称列中截取出车型名称，显示在B列中。挑战难度：,数据源,处理结果,数据源,处理结果,中国移动广东公司品牌课程系列第43页/共108页,文本处理技术(截取中间字符),Think Road:科目名称列中有以下规律：XXX-YYY-ZZZ，所以只要以“-”作为标识符，借助MID函数，也可将车型分离出来。,Explain:FIND(“-”,D3,6)表示在D3单元格的第6个字符开始里找标识符“-”，返回找到字符编号。,思路,FIND,公式：=MID(D3,6,FIND(-,D3,6)-6),库存商品-宝来-1.9L,FIND(“-”,D3,6)返回第2个”-”所在的编号为8.,字符“库存商品-”和第二个“-”是固定字符，所以固定位数为6，因此用找到的编号与找到的固定编号相减，得出车型字符的位数2,中国移动广东公司品牌课程系列第44页/共108页,文本处理技术(绩效分行),任务要求：将每位员工分行显示绩效明细，并在每行附上表头。并插入一新行以作分隔。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第45页/共108页,文本处理技术(绩效分行),Think Road:借助VLOOKUP函数引用工号所对应的各考核字段的数值，然后再利用COLUMN函数向右拖动时自动递增相应的列数，来查找出相应的数值，然后利用拖动的方法填充剩余数值。,Explain:Vlookup表示在列中查找，使用的方法是：vlookup(查找对象，对象所在范围，范围中对象所在的列数，返回精确/模糊值）,Explain:Column()表示当前单元格所在的列数,思路,VLOOKUP,COLUMN,公式：=VLOOKUP($A3,绩效数据!$A:$W,COLUMN(),0),中国移动广东公司品牌课程系列第46页/共108页,文本处理技术(隔行填充),任务要求：如何快速对已合并的单元格进行拆解，并进行隔行填充相对应的类别值。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第47页/共108页,目 录,中国移动广东公司品牌课程系列第48页/共108页,创建图表三口诀,选中数据直接完成 想改哪就双击哪想删哪就点哪,你喜欢哪种表格？,VS,中国移动广东公司品牌课程系列第49页/共108页,口诀应用,调整图表图表元素的增减图表的美化自定义图表类型图表数据区域的操作,中国移动广东公司品牌课程系列第50页/共108页,图表制作技术(甘特图),任务要求：按照数据源的制作出工作进展图。挑战难度：,数据源,处理结果,中国移动广东公司品牌课程系列第51页/共108页,图表制作技术(动态图表),任务要求：按照数据源的制作出可以选择月份销售量的柱图。挑战难度：,中国移动广东公司品牌课程系列第52页/共108页,图表制作技术(动态图表),OFFSET 函数,中国移动广东公司品牌课程系列第53页/共108页,目 录,中国移动广东公司品牌课程系列第54页/共108页,Excel打印设置,打印预览表格缩印首行重复打印,中国移动广东公司品牌课程系列第55页/共108页,共享站点：,中国移动广东公司品牌课程系列第56页/共108页,Thank You!,
