Excel培训资料.ppt

1、培 训 目 的,如何准确而高效地工作 学习使用更为复杂的公式及函数 设计简洁美观的数据表格 提高数据处理技能 发挥个人创造能力,概述,工作簿与工作表,关系：可以把工作簿看成是一个文件，把工作表看成是一个表。 工作表的构成元素：单元格、行与列。一个工作表有65536行、256列。,工作表个数的设定,可以直接插入或删除工作表，主要是右键单击工作表标签选择相应的命令实现。 “工具”菜单“选项” “常规”选项卡，在“新工作簿中的工作表数”后设定（如下图）,目 录,提高效率快捷键 格式设计 公式和函数 数据处理 其他：时间和文本处理、工具栏调整等 图表生成,提高效率快捷键,在工作表或工作簿中移动和滚动的

2、按键 用于输入数据的按键 设置数据格式的按键 编 辑 数 据 选择单元格、列或行的按键(1)、(2) 分级显示数据的按键 窗口、对话框和编辑框中的按键(1) 、(2) 用于工作表、图表和宏的按键,在工作表或工作簿中移动和滚动的按键,按 键完 成 功 能 箭头键 向上、下、左或右移动单元格 CTRL+ 箭头键移动到当前数据区域的边缘 CTRL+HOME移动到工作表的开头 CTRL+END移动到工作表的最后一个单元格（右下角） ALT+PAGE DOWN 向右移动一屏 ALT+PAGE UP向左移动一屏 CTRL+PAGE DOWN 移动到工作簿中下一个工作表 CTRL+PAGE UP移动到工作簿

3、中前一个工作表 CTRL+F6 或 CTRL+TAB移动到下一工作簿或窗口 CTRL+SHIFT+F6 或 移动到前一工作簿或窗口 CTRL+SHIFT+TAB F5显示“定位”对话框 SHIFT+F5或CTRL+F 显示“查找”对话框 SHIFT+F4重复上一次“查找”操作（等同于“查找下一个”） TAB在保护工作表中的非锁定单元格之间移动,用于输入数据的按键,按 键 完 成 功 能 ALT+ENTER在单元格中折行 CTRL+ENTER用当前输入项填充选定的单元格区域 TAB完成单元格输入并在选定区域中右移 CTRL+DELETE删除插入点到行末的文本 F4 或 CTRL+Y重复最后一次操

4、作 F2 (SHIFT+F2)编辑单元格 (编辑单元格批注) CTRL+D (CTRL+R)向下填充 (向右填充) CTRL+F3 (F3)定义名称 (将定义的名称粘贴到公式中) CTRL+SHIFT+ENTER以数组公式形式输入公式 CTRL+A在公式中键入函数名后显示公式选项板 CTRL+K插入超级链接 SHIFT+F3将函数粘贴到公式中 F9 (CTRL+ALT+F9)计算所有打开(活动)工作簿中的所有工作表 SHIFT+F9 计算活动工作表 ALT+= （等号）插入“自动求和”公式 CTRL+; （分号） 输入日期 CTRL+SHIFT+: （冒号）输入时间 CTRL+SHIFT+“

5、（双引号）将上方单元格中数值复制到当前单元格 CTRL+ （左单引号）在显示单元格值和显示单元格公式间转换 CTRL+ （撇号）将上方单元格中公式复制到当前单元格 ALT+ 下箭头键显示“记忆式键入”列表,设置数据格式的按键,按 键 完 成 功 能 ALT+ （撇号）显示“样式”对话框 CTRL+1显示“单元格格式”对话框 CTRL+SHIFT+应用“常规”数字格式 CTRL+SHIFT+$应用带两个小数位的“贷币”格式,括号为负数 CTRL+SHIFT+%应用不带小数位的“百分比”格式 CTRL+SHIFT+应用带两个小数位的“科学记数”数字格式 CTRL+SHIFT+#应用年月日“日期”格

6、式 CTRL+SHIFT+应用小时和分钟“时间”格式，标明上午下午 CTRL+SHIFT+!应用这样的数字格式：-3,000,000.00 CTRL+SHIFT+ （分号）只选定当前选定区域中的可视单元格,分级显示数据的按键,按 键 完 成 功 能 ALT + SHIFT + 右箭头键对行或列分组 ALT + SHIFT + 左箭头键取消行或列分组 CTRL+8显示或隐藏分级显示符号 CTRL+9隐藏选定行 CTRL+SHIFT+( （左括号）取消隐藏选定行 CTRL+0（零）隐藏选定列 CTRL+SHIFT+)（右括号）取消隐藏选定列,窗口、对话框和编辑框中的按键（1）,按 键 完 成 功

7、能 ALT+TAB切换到下一个程序 ALT+SHIFT+TAB切换到上一个程序 CTRL+ESC显示 Windows“开始”菜单 CTRL+W 或 CTRL+F4关闭活动工作簿窗口 CTRL+F5恢复活动工作簿窗口的大小 F6移动到已拆分工作簿的下一窗格 SHIFT+F6移动到已拆分工作簿的上一窗格 CTRL+F6切换到下一个工作簿窗口 CTRL+SHIFT+F6切换到上一个工作簿窗口 CTRL+F7 执行“移动”命令（菜单栏中的工作簿图标菜单），或使用箭头键移动窗口 CTRL+F8执行“大小”命令（菜单栏中的工作簿图标菜单），或使用箭头键调整窗口的大小 CTRL+F9将工作簿窗口最小化为图标

8、 CTRL+F10最大化或恢复工作簿窗口 PRTSCR将屏幕图像复制到剪贴板中 ALT+PRINT SCREEN将活动窗口的图像复制到剪贴板中,窗口、对话框和编辑框中的按键（2）,按 键 完 成 功 能 TAB移动到下一个选项或选项组 SHIFT+TAB移动到上一个选项或选项组 CTRL+TAB 或 CTRL+PAGE DOWN切换到对话框中的下一个选项卡 CTRL+SHIFT+TAB 或 CTRL+PAGE UP切换到对话框中的上一个选项卡 ALT+ 下箭头键打开选定的下拉列表框 对话框中编辑框的按键 在编辑框中按下完成 HOME移动到内容的开始处 END移动到内容的末尾 CTRL+ 左箭头

9、键向左移动一个字 CTRL+ 右箭头键向右移动一个字 SHIFT+ 左箭头键选定或取消选定左面的一个字符 SHIFT+ 右箭头键选定或取消选定右面的一个字符 CTRL+SHIFT+ 左箭头键选定或取消选定左面的一个字 CTRL+SHIFT+ 右箭头键选定或取消选定右面的一个字 SHIFT+HOME选定从插入点到开始处的内容 SHIFT+END选定从插入点到末尾的内容,用于工作表、图表和宏的按键,按 键 完 成 功 能 SHIFT+F11 或 ALT+SHIFT+F1插入新工作表 F11 或 ALT+F1创建使用当前区域的图表 ALT+F8显示“宏”对话框 ALT+F11显示“Visual Ba

10、sic 编辑器” CTRL+F11插入 Microsoft Excel 4.0 宏工作表 CTRL+PAGE DOWN移动到工作簿中的下一个工作表 CTRL+PAGE UP移动到工作簿中的上一个工作表 SHIFT+CTRL+PAGE DOWN选择工作簿中当前和下一个工作表 SHIFT+CTRL+PAGE UP选择工作簿中的当前工作簿或上一个工作簿,返回目录,格式设计,页 面 设 置,数据有效性,条 件 格 式,单元格格式,单元格格式,单元格格式概述 单元格格式数字 单元格格式对齐 单元格格式字体 单元格格式边框 单元格格式图案 单元格格式保护,对任何选定的单元格区域设置格式，只需对选定区域按鼠

11、标右键或键盘快捷菜单：设置单元格格式，包括：,单元格格式概述,如右图 所有设置单元格格式只能对选定的范围起作用，即Excel的任何操作都必须有批定的对象 对选定的区域按鼠标右键或键盘菜单键 设置单元格格式(F)即可设置单元格格式 记住：不要选定：整个表、整行、整列来设置格式，否则会无谓加大文件长度,数据格式数字,此处只对“自定义数字格式”进行说明： 设定常用数字格式：Crtl+Shift+! 如：-12,345 对于七位发票号码654可以设置自定义格式：0000000显示：0000654 自定义格式顺序：正数格式;负数格式;零格式;文本格式 如：#,#0;红色-#,#0;黄色0;蓝色 和0为数

12、字占位符，为文本占位符，“，”（逗号）为千分位符号 d天，m 月，y 年，如：将2003-3-15显示成：今天是3月15日，用函数：=TEXT(单元格,“今天是m月d日”)或者自定义格式：今天是m月d日 *表示重复，如：*#,#0.00将数字前加满*号 为在不同工作表中使用自定义格式，只需把含自定义格式的单元格复制到其他工作表中就可以了。,数据格式对齐,对横向合并，一般不用“合并单元格”，用“水平对齐”中的“跨列居中” 对竖向合并，可以改变文字方向 对备注单元格，可以使用“缩小字体填充” 对大单元格应在“垂直对齐”上选“居中”,数据格式字体,建议 对表格正文部分，大多数时候都不需要改变字体大小

13、，只需要按缩放打印即可实现,数据格式边框、图案,表格框内线一般用单线，外框一般用双线， 当然可以选择任意一种线型 图案：设置单元格的背景颜色及图案。,数据格式保护,锁定即对单元格内容进行锁定，不允许修改（只有工作表被保护时才会生效） 1.默认单元格都是为“锁定”状态。格式单元格保护锁定。 隐藏即在保护工作表时，隐藏工作表中的公式 “锁定”和“隐藏”跟“工作表保护”有不可分割的关系。,页面设置,可以在工具栏添加页面设置快捷键 任何页面设置，首先必须确定打印机和纸张类型 页边距：一般应选择水平居中，左右边距大多数情况下相等 垂直居中视情况选择 页眉/页脚：可以自定义页眉/页脚 工作表：顶端标题行和

14、左端标题列 选项：视打印机是否支持 如：每张纸打印页数双面打印等,数据有效性 （案例介绍）,可以任意定义序列或范围并通过菜单选择,条件格式,可以输入公式或数值 最多可以设三种条件格式，加无条件时的格式，即根据一个单元格的内容最多可自动设置四种格式,返回目录,公式函数,基本公式 工作表函数 错误跟踪,基本公式,1、原则上输入任何公式，仅使用键盘，才能提高速度 2、在单元格开始输入，按“+”、“-”、“=”时，EXCEL视同输入公式 3、对任一单元格，按 F2进入编辑状态，再按 F2 进入选择单元格 4、例如：输入 VLOOKUP 或者连接两个单元格 5、公司中可以使用名称 6、单元格的引用：绝对

15、引用和相对引用，使用 F4 切换 7、对同一表、不同表、不同工作簿均以键盘方式引用,工作表函数,举例：,错误跟踪,对公式或者函数的循环引用可能需要错误追踪，在“自定义工具栏”下选“循环引用”，即打开“循环引用”错误追踪的工具条 单元格错误类型： # 错误 宽度不够或者负日期 #VALUE! 错误 参数不正确，不能返回结果值 #DIV/O! 错误 不能被零整除（除数为零） #NAME? 错误 引用名称错误或引用不存在的名称 #N/A 错误 找不到值 #REF! 错误 引用不存在的单元格或区域 #NUM! 错误 参数错误，或者数值过大、过小 #NULL! 错误 不正确的单元格运算或引用,返回目录,

16、数据处理,排 序 筛 选 模拟运算表 分类汇总 合并计算 数据透视表 使用名称 使用数组 循环引用 分 列 获取外部数据,排序,1、建议：仅当单一表格，表头连续时，才能用工具条快速排序 2、对于有较复杂表头的表格，一定要选定范围后才能排序 3、用选定范围后排序，可针对任一局部排序，如华南区域客户 4、可以按行排序 5、对于不想改变最终顺序的表格，可以加入序号列后排序,筛选,1、选定一定范围后，可在相应的地方加入筛选按钮 2、使用自动筛选； 3、关于高级筛选： 数据区、 条件区与条件、 或条件 4、使用高级筛选获取不重复记录,模拟运算表 （案例讲解）,单输入模拟运算表 1.引用行的 2.引用列的

17、 双输入模拟运算表,分类汇总 （案例讲解）,1、坚持习惯：对选定范围进行分类汇总 2、如何删除分类汇总 选中已分类汇总的数列数据分类汇总左下角的“全部删除”,完成。 3、如何将分类汇总的结果复制到一个新表中 “定位可见单元格” 4、将汇总结果中的“汇总”样全部删除 “替换”,合并计算 (案例讲解),一是通过位置合并计算 二是通过分类合并计算 按列合并 按行合并 同时按行和按列合并,数据透视表 (案例讲解),1、数据透视表一定要有全部的列名称 2、关于页、行、列选择 3、展开其中某项数据 4、关于时间的组合 5、关于数据更新：从数据源更新 6、如果仅仅是调整透视表结构，不涉及数据源,使用名称,1

18、、使用名称的好处：一次改全部公式，如管理费用表 2、名称可以代替公式一部分或者区域一部分，甚至可以移动的区域 3、如：在集团会计报表中的管理费用-本月数 本月数=offset(!F5,0,MONTH(管理费用!$A$2)-1) 4、用名称代替区域： 总收入=sum(b5:c100) 5、如何在名称中使用相对和绝对引用：使用 F2 功能键,使用数组,1、数组举例：=sum(b2:b100)*(c2:c100) 2、如前述，对绝对值求和：=sum(abs(b2:b300) 3、输入数组按：Ctrl + Shift +Enter 4、可以对数组定义一个名称，然后对名称引用 例：数据“有效性”设置中可

19、以使用。,分 列,1、分列有两种方式：分隔符号，固定宽度 2、使用“分隔符号”分列，可以使用Tal键分列，加空格、逗号等 3、使用“固定宽度”分列，即划线确定分列宽度，注意数据细节： 对文本列前封顶，数字列由于一般靠右，前面可以截去一部分，后面应留有余地 4、数据“分列”在导入外部数数据时使用方式相同，可以混合使用，对容易分列的地方按“固定宽度”分列，在数据有错开的地方再按“分隔符号”分列,获取外部数据,1、获取外部数据方式：可以入的数据包括文本、DBF等，对MDB数据应从ACCESS中以EXCEL格式导出 2、处理ERP导出的报表数据(文本文件) 关于分列：如前述，可以使用两种方式分别处理

20、关于错行：如客户名称在客户代码的下一行，排序、错列、提升即可 3、处理营销系统导出的.SLK文本文件 导入的数据可以同时进行数据透视表的汇总，并进行时间的组合 4、以建立查询的方式导入外部数据，如：DBF、MDB等,返回目录,其他,时间处理 文本处理 工具栏调整 关于安全 共享工作簿,时间处理,对于时间，系统保存的实际上是数字，只是格式不同罢了 比如昨天和今天的时间差就是1，1900年1月1日是系统的第1天 1、定义时间格式：yeary,month m,day d，如： 使用函数定义格式，b2单元格内容是：2003年5月31日 text(b2,”今天是yy年mm月dd日”）， 显示：今天是03年05月31日 2、计算两个时间单元格之间的天数差，