中国移动EXCEL技巧高手培训

玩转 剑客 享受高效办公新生活 中国移动广东公司品牌课程系列第 3页 /共 108页 Microsoft Excel 分享篇 中国移动广东公司品牌课程系列第 4页 /共 108页 目 录 1.界面简介 2.数据输入技巧 3.数据处理 4.工作表操作 5.常用函数 6.图表呈现 7.Excel打印设置 中国移动广东公司品牌课程系列第 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页 目 录 1.界面简介 2.数据输入技巧 3.数据处理 4.工作表操作 5.常用函数 6.图表呈现 7.Excel打印设置 中国移动广东公司品牌课程系列第 17页 /共 108页 排 序 简单排序 复杂排序 中国移动广东公司品牌课程系列第 18页 /共 108页 筛 选 自动筛选 对包含特定文本的行进行区域筛选 快速筛选（见图一） 高级筛选 筛选出不重复数据 （见图二） 图一： 图二： 中国移动广东公司品牌课程系列第 19页 /共 108页 条件格式 条件格式的设置可以根据公式设定， 也可以按单元格的数字进行设定。 公式设定可以做比较复杂的条件格式。 中国移动广东公司品牌课程系列第 20页 /共 108页 目 录 1.界面简介 2.数据输入技巧 3.数据处理 4.工作表操作 5.常用函数 6.图表呈现 7.Excel打印设置 中国移动广东公司品牌课程系列第 21页 /共 108页 数据选择技巧 方法一： Shift键 +单元格边框双击 方法二： Shift+Ctrl+ 选择同列数据； Shift+Ctrl+ 选择同行数据。 中国移动广东公司品牌课程系列第 22页 /共 108页 插入与隐藏行列 插入 隐藏行列 取消隐藏 中国移动广东公司品牌课程系列第 23页 /共 108页 数据表的转置 使用“选择性粘贴” 实现 中国移动广东公司品牌课程系列第 24页 /共 108页 工作表操作 工作表标签重命名 插入、删除、 移动复制工作表 选择工作簿 复制工作表 中国移动广东公司品牌课程系列第 25页 /共 108页 工作表操作 设置工作表默认属性 中国移动广东公司品牌课程系列第 26页 /共 108页 窗格操作 拆分窗格 中国移动广东公司品牌课程系列第 27页 /共 108页 窗格操作 拆分窗格 中国移动广东公司品牌课程系列第 28页 /共 108页 1.界面简介 2.数据输入技巧 3.数据处理 4.工作表操作 5.常用函数 6.图表呈现 目 录 7.Excel打印设置 中国移动广东公司品牌课程系列第 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页 目 录 1.界面简介 2.数据输入技巧 3.数据处理 4.工作表操作 5.常用函数 6.图表呈现 7.Excel打印设置 中国移动广东公司品牌课程系列第 48页 /共 108页 创建图表三口诀 选中数据直接完成 想改哪就双击哪 想删哪就点哪 你喜欢哪种表格？ 中国移动广东公司品牌课程系列第 49页 /共 108页 口诀应用 调整图表 图表元素的增减 图表的美化 自定义图表类型 图表数据区域的操作 中国移动广东公司品牌课程系列第 50页 /共 108页 图表制作技术 (甘特图 ) 任务要求 ： 按照数据源的 制作出工作进展图。 挑战难度： 数据源 处理结果 中国移动广东公司品牌课程系列第 51页 /共 108页 图表制作技术 (动态图表 ) 任务要求 ： 按照数据源的 制作出可以选择月份销售量的柱图。 挑战难度： 中国移动广东公司品牌课程系列第 52页