Excel培训资料.ppt

《Excel培训资料.ppt》由会员分享，可在线阅读，更多相关《Excel培训资料.ppt（61页珍藏版）》请在课桌文档上搜索。

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移动到工作簿中前一个工作表CTRL+F6 或 CTRL+TAB移动到下

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

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

5、单元格中公式复制到当前单元格ALT+下箭头键显示“记忆式键入”列表,设置数据格式的按键,按 键 完 成 功 能ALT+（撇号）显示“样式”对话框CTRL+1显示“单元格格式”对话框CTRL+SHIFT+应用“常规”数字格式CTRL+SHIFT+$应用带两个小数位的“贷币”格式,括号为负数CTRL+SHIFT+%应用不带小数位的“百分比”格式 CTRL+SHIFT+应用带两个小数位的“科学记数”数字格式CTRL+SHIFT+#应用年月日“日期”格式CTRL+SHIFT+应用小时和分钟“时间”格式，标明上午下午CTRL+SHIFT+!应用这样的数字格式：-3,000,000.00CTRL+SHIF

6、T+&应用外边框CTRL+SHIFT+_删除外边框CTRL+B应用或取消字体加粗格式CTRL+I应用或取消字体倾斜格式CTRL+U应用或取消下划线格式CTRL+5应用或取消删除线格式CTRL+9隐藏行CTRL+SHIFT+(（左括号）取消隐藏行CTRL+0（零）隐藏列CTRL+SHIFT+)（右括号）取消隐藏列,编辑数据,按 键 完 成 功 能F2编辑活动单元格并将插入点放置到线条末尾F3将定义的名称粘贴到公式中CTRL+SHIFT+ENTER将公式作为数组公式输入CTRL+A在公式中键入函数名之后，显示公式选项板 CTRL+SHIFT+A在公式中键入函数名之后，为该函数插入变量名和括号CTR

7、L+C复制选定区域CTRL+X剪切选定区域CTRL+V粘贴选定区域CTRL+连字符删除选定区域CTRL+Z撤消最后一次操作CTRL+SHIFT+加号插入空白单元格ENTER（SHIFT+ENTER）在选定区域内由上往下移动TAB（SHIFT+TAB）在选定区域内由左往右移动CTRL+PERIOD按顺时针方向移动到选定区域的下一个角。CTRL+ALT+右箭头键右移到非相邻的选定区域CTRL+ALT+左箭头键左移到非相邻的选定区域,选择单元格、列或行的按键(1),按 键 完 成 功 能CTRL+SHIFT+*（星号）选定当前单元格周围的区域SHIFT+箭头键将选定区域扩展一个单元格宽度CTRL+S

8、HIFT+箭头键将选定区域扩展到与活动单元格同一行或同一列的最后一个非空白单元格SHIFT+HOME将选定区域扩展到行首CTRL+SHIFT+HOME将选定区域扩展到工作表的开始CTRL+SHIFT+END将选定区域扩展到表的最后一个使用单元格CTRL+SPACEBAR选定整列SHIFT+SPACEBAR选定整行CTRL+A选定整个工作表SHIFT+BACKSPACE如果已经选定了多个单元格，则只选定其中的活动单元格SHIFT+PAGE DOWN将选定区域向下扩展一屏SHIFT+PAGE UP将选定区域向上扩展一屏CTRL+SHIFT+SPACEBAR在选定了一个对象的情况下，选定工作表上的所

9、有对象CTRL+6在隐藏对象,显示对象与对象占位符之间切换CTRL+7显示或隐藏“常用”工具栏F8使用箭头键启动扩展选中区域的功能,选择单元格、列或行的按键(2),处于 End 模式时展开选中区域的按键END打开或关闭 END 模式END,SHIFT+HOME将选定区域扩展到工作表上包含数据的最后一个单元格（右下角）END,SHIFT+ENTER将选定区域扩展到当前行中的最后一个单元格。CTRL+/选中当前数组，此数组是活动单元格所属的数组CTRL+SHIFT+O(字母 O)选定所有带批注的单元格CTRL+选择行中不与该行内活动单元格的值相匹配的单元格。必须选择起始于活动单元格的行。CTRL+

10、SHIFT+|选中列中不与该列内活动单元格的值相匹配的单元格。必须选择起始于活动单元格的列。CTRL+（左方括号）选定当前选定区域中公式的直接引用单元格CTRL+SHIFT+（左大括号）选定当前选定区域中公式直接或间接引用的所有单元格CTRL+（右方括号）只选定直接引用当前单元格公式所在的单元格CTRL+SHIFT+（右大括号）选定所有带有公式的单元格，这些公式直接或间接引用当前单元格ALT+;（分号）只选定当前选定区域中的可视单元格,分级显示数据的按键,按 键 完 成 功 能ALT+SHIFT+右箭头键对行或列分组ALT+SHIFT+左箭头键取消行或列分组CTRL+8显示或隐藏分级显示符号C

11、TRL+9隐藏选定行CTRL+SHIFT+(（左括号）取消隐藏选定行CTRL+0（零）隐藏选定列CTRL+SHIFT+)（右括号）取消隐藏选定列,窗口、对话框和编辑框中的按键（1）,按 键 完 成 功 能ALT+TAB切换到下一个程序ALT+SHIFT+TAB切换到上一个程序CTRL+ESC显示 Windows“开始”菜单CTRL+W 或 CTRL+F4关闭活动工作簿窗口CTRL+F5恢复活动工作簿窗口的大小F6移动到已拆分工作簿的下一窗格SHIFT+F6移动到已拆分工作簿的上一窗格CTRL+F6切换到下一个工作簿窗口CTRL+SHIFT+F6切换到上一个工作簿窗口CTRL+F7 执行“移动”

12、命令（菜单栏中的工作簿图标菜单），或使用箭头键移动窗口CTRL+F8执行“大小”命令（菜单栏中的工作簿图标菜单），或使用箭头键调整窗口的大小CTRL+F9将工作簿窗口最小化为图标CTRL+F10最大化或恢复工作簿窗口PRTSCR将屏幕图像复制到剪贴板中ALT+PRINT SCREEN将活动窗口的图像复制到剪贴板中,窗口、对话框和编辑框中的按键（2）,按 键 完 成 功 能TAB移动到下一个选项或选项组SHIFT+TAB移动到上一个选项或选项组CTRL+TAB 或 CTRL+PAGE DOWN切换到对话框中的下一个选项卡CTRL+SHIFT+TAB 或 CTRL+PAGE UP切换到对话框中的上

13、一个选项卡ALT+下箭头键打开选定的下拉列表框 对话框中编辑框的按键 在编辑框中按下完成HOME移动到内容的开始处END移动到内容的末尾CTRL+左箭头键向左移动一个字CTRL+右箭头键向右移动一个字SHIFT+左箭头键选定或取消选定左面的一个字符SHIFT+右箭头键选定或取消选定右面的一个字符CTRL+SHIFT+左箭头键选定或取消选定左面的一个字CTRL+SHIFT+右箭头键选定或取消选定右面的一个字SHIFT+HOME选定从插入点到开始处的内容SHIFT+END选定从插入点到末尾的内容,用于工作表、图表和宏的按键,按 键 完 成 功 能SHIFT+F11 或 ALT+SHIFT+F1插入

14、新工作表F11 或 ALT+F1创建使用当前区域的图表ALT+F8显示“宏”对话框ALT+F11显示“Visual Basic 编辑器”CTRL+F11插入 Microsoft Excel 4.0 宏工作表CTRL+PAGE DOWN移动到工作簿中的下一个工作表CTRL+PAGE UP移动到工作簿中的上一个工作表SHIFT+CTRL+PAGE DOWN选择工作簿中当前和下一个工作表SHIFT+CTRL+PAGE UP选择工作簿中的当前工作簿或上一个工作簿,返回目录,格式设计,页 面 设 置,数据有效性,条 件 格 式,单元格格式,单元格格式,单元格格式概述单元格格式数字单元格格式对齐单元格格式

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

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

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

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

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

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

21、：对选定范围进行分类汇总2、如何删除分类汇总 选中已分类汇总的数列数据分类汇总左下角的“全部删除”,完成。3、如何将分类汇总的结果复制到一个新表中“定位可见单元格”4、将汇总结果中的“汇总”样全部删除“替换”,合并计算(案例讲解),一是通过位置合并计算二是通过分类合并计算按列合并按行合并同时按行和按列合并,数据透视表(案例讲解),1、数据透视表一定要有全部的列名称2、关于页、行、列选择3、展开其中某项数据4、关于时间的组合5、关于数据更新：从数据源更新6、如果仅仅是调整透视表结构，不涉及数据源,使用名称,1、使用名称的好处：一次改全部公式，如管理费用表2、名称可以代替公式一部分或者区域一部分，

22、甚至可以移动的区域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+Enter4、可以对数组定义一个名称，然后对名称引用 例：数据“有效性”设置中可以使用。,分 列,1、分列有两种方式：分隔符号，固定宽度2、使用“分隔符号”分列，可以使用Tal键分列，加空格、

23、逗号等3、使用“固定宽度”分列，即划线确定分列宽度，注意数据细节：对文本列前封顶，数字列由于一般靠右，前面可以截去一部分，后面应留有余地4、数据“分列”在导入外部数数据时使用方式相同，可以混合使用，对容易分列的地方按“固定宽度”分列，在数据有错开的地方再按“分隔符号”分列,获取外部数据,1、获取外部数据方式：可以入的数据包括文本、DBF等，对MDB数据应从ACCESS中以EXCEL格式导出2、处理ERP导出的报表数据(文本文件)关于分列：如前述，可以使用两种方式分别处理关于错行：如客户名称在客户代码的下一行，排序、错列、提升即可3、处理营销系统导出的.SLK文本文件导入的数据可以同时进行数据透

24、视表的汇总，并进行时间的组合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、计算两个时间单元格之间的天数差，直接相减3、计算A1和A2两个时间单元格之间的月份差：MONTH(A1)-MONTH(A2)+(YE

25、AR(A1)-YEAR(A2)*124、在数据透视表中，时间可以按月和按年进行组合,文本处理,1、测试长度：=len(b2)2、截取左边三个字：=left(b2,3)3、截取右边三个字：=right(b2,3)4、截取第X个字开始起N个字：=MID(b2,X,N)5、连接三个单元格：=b2&c2&e2,工具栏调整,修改常用工具按钮：鼠标在工具栏按右键，选“自定义命令”即可修改常用按钮，包括修改所有菜单条：增加、减少、重命名,关于安全,(工具)保护工作表(工具)保护工作簿(工具)保护并共享工作簿(另存为)保护文件,共享工作簿,作用：允许多人在网上同时编辑同一工作簿缺点：可能存在太多修订冲突，可在

26、修订记录时，文件会变得很大在菜单“工具共享工作簿”下按单选按钮即可，如下图,图表生成,MS Excel进行图表生成，其步骤为：在工作表中选择欲生成图表的数据区域单击工具栏上“图表向导”的按钮在图表向导窗口中选择图表类型确定生成图表的数据源,选择A2:E7,单击“图表向导”,选定类型后单击“下一步”,确认数据后单击“下一步”,输入相关说明,单击“下一步”,确定图表摆放位置,单击“完成”,最终图表,图表工具栏,MS Excel图表结构示例,MS Excel图表结构示例,MS Excel图表结构示例,MS Excel图表结构示例,MS Excel图表结构示例,MS Excel图表结构示例,谢谢！再见,