Excel强大功能介绍.ppt

办公自动化之-Excel,2008年7月,主要内容,主要内容,Excel概述及常用功能介绍,Excel图表应用,常用Excel函数及其应用,Excel数据透视表的应用,主要内容,主要内容,Excel概述及常用功能介绍,Excel图表应用,常用Excel函数及其应用,Excel数据透视表的应用,Excel概述,Excel 2,于1987年问世，为Windows Excel的原始版本。根据现在的标准看，这个版本过于粗糙；,Excel 3,Excel 4,1990年底，微软公司发布了Excel 3 for windows，这个版本无论在外观和性能上都作了很大的改进，增加了工具栏、绘图功能、工作表大纲、加载宏支持、3D图表、工作组编辑等；,1992年投入市场。这个版本增加了很多新功能，且可用性也增强了，使初学者在很短的时间内能够掌握。,1994年初投入市场。这个版本推出了更多的新功能，包括多页面工作簿和VBA宏语言。,Excel 5,Excel 发展,Excel概述,Excel 发展,Excel 95,也被称为Excel 7，是在1995年夏天推出的，因为它是第一个使用更高级的32位代码技术的Excel版本，在当时产生了巨大的影响；,Excel 97,也被称为Excel 8，它提供了前所未有的升级功能，工具栏和菜单的样式作了重大的改进，工作表可见行的数量增加了3倍；,Excel 2000,也被称为Excel 9，在1999年6月发布，它的最大改进就是将HTML作为可选的文件格式；,Excel 2002,也被称为Excel 10，在2001年6月份发布，最重要的新功能就是可以恢复被破坏的的工作簿文件，还增加了背景公式错误检查功能和新公式调试工具。,Excel 2003,Excel常用功能介绍,文件加密保护,文档保护功能,路径：文件 另存为 工具 常规选项 设置密码,Excel常用功能介绍,工作表保护,文档保护功能,路径：工具保护保护工作表选择密码,Excel常用功能介绍,单元格定位填充,下表中空白处的内容均为上一行的内容，这种情况在我们做数据透视表时经常会遇到，如果采用“复制粘贴”的方法填充空白处的内容，会很繁索，浪费时间。,如果采用“单元格定位填充”的功能，复制粘贴可能花上几个小时的一张表格，几分钟时间就可能解决了。,Excel常用功能介绍,1、选择需要填充空白单元格的列；,2、启动“编辑”菜单中的定位功能(快捷键：Ctrl+G)；,3、在对话框中选择“定位条件”中的“空值”，点击确定；,Excel常用功能介绍,4、录入公式：等于上一单元格；然后按“Ctrl+Enter”就可以了。,Excel常用功能介绍,单元格有效性设置,1、在工作表空白处将要选择的信息先录入进去；然后选择要设置的列；,一、设置单元格下拉菜单，快速录入数据：,Excel常用功能介绍,2、启动“数据”菜单的“有效性”选项，在“设置”中选择“序列”功能；,3、选择数据来源为预先定义的选项，确认完成，这样在列中就可以选择了；,Excel常用功能介绍,选中列，开启“数据”菜单“有效性”，选择“输入法模式”下的“打开”选项，点击确定，这样在录入资料信息时，列与列之间就能够自动转换“中文英文”录入模式了。,二、中文录入模式开启：,Excel常用功能介绍,2、录入类型后点击确定，这样就要以在单元格中录入数据了；在录入时，如果不够格式设置的位数，系统会自动以“0”补齐位数。,Excel常用功能介绍,选中区域，开启“数据”菜单“有效性”，选择“设置”下的“整数”选项，并录入最小值及最大值，点击确定，这样如果录入区域外的数据，就会自动报错。,如果我们要在单元格区域内录入“0100”的整数，如何做到自我检测呢？,三、数据录入检测：,Excel常用功能介绍,单元格自定义格式,我们要输入员工编号如：“000100”，怎样能做到更快捷方便呢？,1、选中列，打开“单元格格式”设置，选择“自定义”，并在类型中录入“000000”；,Excel常用功能介绍,路径：数据分列,步骤,单元格分列,1、选择要分列的数据列；,2、点击分列项，并选择合适的类型；,3、调整适合宽度；,4、选择存放的目标区域，并确认完成；,Excel常用功能介绍,5,Excel常用功能介绍,表格数据行列互换,1、选择数据区域，复制；,2、点击右键，选择“选择性粘贴”，选中“转置”，确认后返回就可；,Excel常用功能介绍,条件格式设置,例：对入职的新员工(假定试用期为1个月)，在1个月试用期满后，表单自动提示，并用颜色标注，以示区别。,Excel常用功能介绍,1、选定要设置“条件格式”的列；,2、打开“格式”菜单中的“条件格式”；,Excel常用功能介绍,3、根据需要设置条件格式；,Excel常用功能介绍,4、设置好后格式后，点击确认。(如果需要设置多个条件，点击“添加”就可以了)；,Excel常用功能介绍,在Excel 中导入文本文档(.txt),1,2,Excel常用功能介绍,3,4,5,6,Excel常用功能介绍,超链接功能,1、选择要插入超链接的单元格，打开“插入”菜单中“超级链接”，或点击右键选择“超级链接”(快捷键：Ctrl+K),并选择要链接引用的位置；,Excel常用功能介绍,超链接功能,2、在引用链接的文档中，建立链接返回，并键入要返回的单元格所在的位置，如返回“印刷部”在“A2”位置；,Excel常用功能介绍,打印表头设置,我们在打印一个长工作表时，往往第二页后就显示不到标题，这样打印出来的工作表比较不直观也不太美观。,我们可以通过“页面设置”来解决这个问题!,Excel常用功能介绍,1、执行“文件”菜单“页面设置”命令，在对话框中点击进入“工作表”标签。,2、点击“顶端标题行”按钮，后在工作表中选择好需要固定的标题行，点 击“确定”按钮,Excel常用功能介绍,“页面设置”后的结果：每页都可以打印出标题栏了。,Excel概述,四、常用快捷功能介绍：,Ctrl组合快捷键,【Ctrl】+【N】：创建一个新的空白文件。【Ctrl】+【S】：使用其当前文件名、位置和文件格式保存活动文件。【Ctrl】+【W】：关闭选定的工作簿窗口。【Ctrl】+【+】：显示用于插入空白单元格的【插入】对话框。【Ctrl】+【9】：隐藏选定的行。【Ctrl】+【(】：取消隐藏选定范围内所有隐藏的行。【Ctrl】+【0】：隐藏选定的列。【Ctrl】+【)】：取消隐藏选定范围内所有隐藏的列。【Ctrl】+【C】：复制选定的单元格。【Ctrl】+【D】：复制上一层单元格。【Ctrl】+【X】：剪切选定的单元格。【Ctrl】+【V】：粘贴复制或剪切的内容。,Excel概述,【Ctrl】+【A】：选择整个工作表。【Ctrl】+【G】：显示【定位】对话框，【F5】功能。【Ctrl】+【F】：显示【查找】对话框，同【Shift】+【F5】功能。【Ctrl】+【P】：显示【打印】对话框。【Ctrl】+【H】：显示【查找和替换】对话框。【Ctrl】+【Z】：撤消上一个命令或删除最后键入的条目。,Ctrl组合快捷键,Excel概述,功能键,【Alt】+【Shift】+【F1】：插入新的工作表。【Shift】+【F11】：插入一个新工作表【F2】：编辑活动单元格并将插入点放在单元格内容的结尾。【Shift】+【F2】：编辑单元格批注【Ctrl】+【F5】：恢复选定工作簿窗口的窗口大小【Ctrl】+【F6】：切换到另一个工作簿窗口【Ctrl】+【F9】：将工作簿窗口最小化为图标【Ctrl】+【F10】：最大化或还原选定的工作簿窗口【Shift】+【F10】：显示选定项目的快捷菜单【F12】：显示【另存为】对话框。,Excel概述,其他快捷键,【Ctrl】+【箭头键】：移动到工作表中当前数据区域的边缘。【Shift】+【箭头键】：将单元格的选定范围扩大一个单元格【Ctrl】+【Shift】+【箭头键】：可将单元格的选定范围扩展到与活动 单元格同一列或同一行中的最后一个非空白单元格【Ctrl】+【End】：移动到工作表上的最后一个单元格(非空白)【Alt】+【Enter】：在同一单元格中另起一个新行【Home】：移到工作表中某一行的开头【End】：当【Scroll Lock】处于开启状态时，移动到窗口右下角的单元格【Ctrl】+【Shift】+【End】：将单元格的选定范围扩展到工作表上所 使用的最后一个单元格（右下角）【Ctrl】+【Enter】：使用当前条目填充选定的单元格区域【Shift】+【Enter】：完成单元格输入并选择上面的单元格【Ctrl】+【Home】：移到工作表的开头【Ctrl】+【Shift+Home】：将单元格的选定范围扩展到工作表的开头,Excel概述,其他快捷键,【Alt】+【Page Down】：在工作表中向右移动一个屏幕【Ctrl】+【Page Down】：移到工作簿中的下一个工作表【Ctrl】+【Page Up】：移到工作簿中的上一个工作表【Tab】：在工作表中向右移动一个单元格【Shift】+【Tab】：移到前一个单元格（在工作表中）或前一个选项（在对话框中）【Ctrl】+【Shift+Page Down】：选择工作簿中的当前和下一个工作表【Alt】+【Page Up】：在工作表中向左移动一个屏幕【Ctrl】+【Shift+Page Up】：选择工作簿中的当前和上一个工作表【Ctrl】+【空格键】：选择工作表中的整列。【Shift】+【空格键】：选择工作表中的整行。【Ctrl】+【Shift】+【空格键】：选择整个工作表【Ctrl】+【Tab】：切换到下一个选项卡【Ctrl】+【Shift】+【Tab】：切换到前一个选项卡,主要内容,主要内容,Excel概述及常用功能介绍,Excel图表应用,常用Excel函数及其应用,Excel数据透视表的应用,Excel图表制作,Excel 图表分类,常用标准图表,其他标准图表,柱形图、折线图、饼图,条形图、圆锥图、棱锥图气泡图、股价图等,Excel图表制作,Excel 图表应用,柱形图,显示或比较多个数据组 如：分析7月份各部门加班费用,折线图,饼 图,比较每一数值相较于总数值的大小如：分析每月机台大废品中各废品类型所占的比例,对数据一段时期内的变化进行趋势分析如：分析07年度整体加班工资趋势和与 06年对比工资分析,Excel图表制作,Excel 图表使用步骤,1、选定待显示于图表中的数据所在的单元格。如果希望新数据的行列标志也显示在图表中，则选定区域还应包括含有标志的单元格。,2、单击“图表向导”按钮,3、选择自己需要的图表类型,4、根据“图表向导”中的指示进行操作,Excel图表制作,Excel 图表选项设置,1、标题及坐标轴标识；,Excel图表制作,2、数据标志显示；,Excel图表制作,3、数据表显示；,Excel图表制作,利用“图表”工具栏，可以快速准确地选择图表中的相应项目,一、准确地选择图表中的项目,Excel 图表个性化设置,1、选择菜单“视图工具栏图表”命令，显示“图表”工具栏；,2、单击工具栏上左侧“图表对象”下拉列表框的下拉箭头，就可以选中图表中相应的项目,Excel图表制作,3、我们也可以通过鼠标单击的方式来选择项目，不管用哪种方法，都可以通过左上方的名称框来查看，当前选中 的项目是什么；,Excel图表制作,二、修改各种标题的字体,如果想每次修改一种标题字体，比如修改“图表标题”的字体为“黑体”，则选中“图表标题”单击鼠标右键，在弹出的菜单中选择“图表标题格式”命令，通过打开的“图表标题格式”对话框进行修改,如果想同时修改图表上的所有字体，则单击图表的边框，选中图表区，然后直接在“格式”工具栏上修改字体的类型和大小即可。,Excel图表制作,三、使用次坐标轴,有时候，一个图表中包含两个数据系列，但是它们之间的值相差很大，如果使用一个数值轴，那么另一个数据系列的差别情况可能显示得很不明显，在这种情况下，我们可以通过使用次坐标轴来改善另一个数据系列的显示情况,下图中，因为“人数”与“金额”的差异较大，导致“人数”系列显示不是很明显，可以利用“次坐标轴”来解决这个问题。,Excel图表制作,1、选中系列“人数”，单击鼠标右键，打开“数据系列格式”命令；,Excel图表制作,2、进入“坐标轴”选项，点选“次坐标轴”选框，单击“确定”按钮；,Excel图表制作,3、返回编辑窗口后，我们发现图表中有两个坐标轴，左边原坐标轴 表示“用餐金额”，右边次坐标轴表示“人数”。,Excel图表制作,四、多种图表类型并存,组合图表指的是在一个图表中包含两种或两种以上的图表类型。Excel“图形类型”中提供了两种组合图表：“柱状-面积图”和“线-柱图”,Excel图表制作,假设要创建一个同时显示柱形、折线和面积图的图表，可以按照如下步骤进行操作,1、在工作表中输入数据并创建一个柱形工作表；,Excel图表制作,2、单击第2个即5月的数据系列将其选中，然后选择“图形图形类型”，打开“图形类型”对话框，选择“折线图”并单击“确定”按钮,Excel图表制作,3、用同样的方法将第3个和第4个数据系列的类型分别改为面积图和饼图，这样我们就得到了一个同时具有4种类型的组合图表。,Excel与其他软件应用,在PPT及Word中插入Excel文档,1、打开“插入”菜单中“对象”命令，并选择“由文件创建”；,PPT,Word,Excel与其他软件应用,2、通过“浏览”键选择Excel文档所有位置，选择文档，并选择“显示为 图标”，后按确定键；,3、确定后，文档中会出现一个Excel图标，如果要打开文档，直接双击 图标就可以了。,PPT,Word,Excel与其他软件应用,在PPT及Word中插入Excel表格,方法1、从Excel工作表中直接复制表格数据，然后粘贴在相应的文档中；,方法2、在相应文档中，打开“插入”菜单中“表格”功能，录入表格行、列数，点击确定；所生成的表格编辑方式同Excel差不多。,Word,PPT,Excel与其他软件应用,在PPT及Word中插入Excel图表,方法1、从Excel工作表中直接复制图表，然后粘贴在相应的文档中；,方法2、在相应文档中，打开“插入”菜单中“图表”功能，在弹出的表 格中录入相应数据，便会自动生成图表。,如果要修改图表格式的话，直接双击进入图表编辑，编辑方法同在Excel中图表编辑。,Excel与其他软件应用,以下Excel文档要填入Word表格中，你会用什么办法？,Excel与其他软件应用,“邮件合并”功能,路径：Word菜单工具 邮件合并,1,2,可以选择“建立数据源”直接从Word文档中建立,Excel与其他软件应用,3,选择并打开需要合并的Excel文档，Word文档会自动关联文档数据，建立连接。,Excel与其他软件应用,4,在文档空白处插入对应的合并域：,Excel与其他软件应用,5,主要内容,主要内容,Excel概述及常用功能介绍,Excel图表应用,常用Excel函数及其应用,Excel数据透视表的应用,Excel函数应用,一、定义,Excel所提到的函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列的运算。,参数：指的是函数括号中的数值，可以是常数、公式或其它函数，给定的参数必须能产生有效的值。,Excel函数应用,二、函数的结构,1、函数的结构以函数名称开始，后面是左圆括号、以逗 号分隔的参数和右括号。,Excel函数应用,2、嵌套函数：即多重函数，指在某些情况下，根据需要将某函数作另一函数的参数使用。,Excel函数应用,数据库函数,日期与时间函数,工程函数,财务函数,信息函数,逻辑函数,查询与引用函数,数学与三角函数,统计函数,文本函数,自定义函数,三、函数的分类(分11类),Excel函数应用,四、常用函数应用介绍：,1、与求和有关的函数的应用：,Excel提供的求和函数主要有：SUM、SUMIF、SUBTOTAL、SUMPRODUCT等；,我们主要应用的有：SUM(计算一组参数之和)、SUMIF(对满足某一条件的单元格区域求和)。,Excel函数应用,SUM函数,a、行或列求和：对行或列内若干单元格求和。b、区域求和：常用于对一张工作表中的所有数据求总和。c、多个条件求和：,注意：1、多个条件求和中，各个参数要用括号括起，并且参数与 参数值之间要用“*”号或“，”相连；2、各个参数起始的区间要一致：即起始行和终止行要一致；3、公式录入后，将光标移至“”号的左边，同时按“Ctrl+Shift+Enter”键，即可出来结果。,Excel函数应用,SUMIF函数,SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。,例中：“A2：A5”为提供逻辑判断依据的单元格区域；“150000”为判断条件，即只统计“150000”的单元格；“B2：B5”为实际求和的单元格区域。,Excel函数应用,2、四舍五入及取整函数的应用,在实际工作的数学运算中，特别是财务计算中常常遇到四舍五入的问题。虽然Excel也允许你定义小数位数，但实际操作中并未做到真正的四舍五入，只是显示结果按照定义的小数位显示，这样会导致汇总的结果与实际四舍五入后数字的结果有差异，而这种差异在财务运算上是不允许的。,Excel在这方面提供的函数有：Round函数：按指定的位数对数值进行四舍五入Roundup函数：按指定的位数向上舍入数字Rounddown函数：按指定的位数向下舍入数字Int函数：将数值向下取整为最接近的整数,Excel函数应用,Round函数,它的功能就是根据指定的位数，将数字四舍五入,公式：round(number,num_digits)其中：number 为将要四舍五入的数字；num_digits是希望得到的数字的小数点后的位数,Roundup函数及Rounddown函数的公式与Round函数一样，只是保留数时向前一位入数不同。,Excel函数应用,INT函数,将余数直接舍去取整的函数,公式：INT(number),注：因INT函数是直接舍去余数后取整的函数，如果是要将数值四舍五入取整的话，可以将数值+0.5后取整，即函数应用可以改为：INT（number+0.5)这样就可以达到四舍五入的效果了。,Excel函数应用,3、逻辑函数的应用,定义：用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。,Excel提供了六种逻辑函数：AND、OR、NOT、FALSE、IF、TRUE函数,我们常用的主要是IF函数。,Excel函数应用,IF函数,IF函数用于执行真假值判定后，根据逻辑测试的真假值返回不同的结果，因此IF函数也称为条件函数。,简单的IF函数由三个参数组成，上表中参数表示：如果第一个参数“A2A3”成立，则执行第二个参数“TRUE”或“A2A3”的结果，否则执行第三个参数“FALSE”或“A2A3”的结果。,Excel函数应用,IF函数应用,1、输出带有公式的空白表格,公式1=sum(K4:O4)，只是一个单纯的求和函数，如果表单中未填入数据时，结果就会为“0”。,公式2中加入了一个判定，如果sum(K4:O4)0，显示求和结果，反之显示为字符“”,式中表示不显示内容。,1,2,Excel函数应用,IF函数应用,2、多层嵌套函数应用,上例中，以下两个函数都为嵌套函数：IF(AND(B380),“优秀”,“良好”)、IF(AND(B375”成立，就执行第二个函数，否则执行第三个函数。,Excel函数应用,4、文本函数的应用,定义：所谓文本函数，就是可以在公式中处理文字串的函数。例如：可以改变大小写或确定文字串的长度；可以替换某些字符或者去除某些字符等；,主要分类：1、大小写转换函数：LOWER、UPPER、PROPER；2、取出字符串中的部分字符：LEFT、RIGHT、MID函数；3、去除字符串中的空白：TRIM函数,Excel函数应用,取出字符串中的部分字符,Excel函数应用,5、查询与引用函数的应用,VLOOKUP 函数的应用,VLOOKUP 函数可以根据搜索区域内最左列的值，去查找区域内其他列的数据，并返回该列的数据。对于字母来说，搜索时不分大小写。,查找达到的目的：一是精确的查找；二是近似的查找。,Excel函数应用,VLOOKUP 函数的应用,Excel函数应用,6、统计函数的应用,RANK 函数的应用,定义：返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小,公式中：“B2”为要排序的数值；“$B$2：$B$9”为排序的数值组；排名结果“6”表示：该数据在此数组中排位为第六位,Excel函数应用,五、公式应用错误信息：,#DIV/0！,这个公式试图使用0作为除数（运算中不允许这种操作）。如果公式中使用一个空单元格时，也可能会出现这种情况。,#NAME？,公式使用了一个Excel不能识别的名称。如果删除公式使用的名称或误输入某个函数，也会出现这个信息。,#N/A,公式中引用的（直接或间接）单元格中使用NA函数所标识的不能使用的数据。这种错误信息也常出现在查找函数没有找到匹配数据的时候。,#NULL！,公式使用了一种不允许出现交叉在一起的两个区域,#NUM!,这个问题与值有关。例如，在应该使用正值的情况下使用了负值,#REF!,公式引用了一个无效单元格。如果单元格从工作表中删除，经常会出现这种信息,#VALUE!,公式包括了错误形式的变量或运算对象。一个运算对象引用一个值或单元格引用，但是它们中却需要使用公式计算结果。,主要内容,主要内容,Excel概述及常用功能介绍,Excel图表应用,常用Excel函数及其应用,Excel数据透视表的应用,Excel数据透视表应用,定义,数据透视表是用于快速汇总大量数据的交互式表格。可以旋转行或列查看对数据的不同汇总，还可以通过显示不同的页来筛选数据，或者也可以显示所关心区域的明细数据。,路径：数据 数据透视表和图表报告,数据透视表包括两项：“字段项”及“数据项,Excel数据透视表应用,步骤,1、创建数据透视表原始数据,2、删除所有的空行和空列、自动小计，并确保各列的标题无空值,1,保证所有行、列及标题非空,Excel数据透视表应用,2,3、启动数据透视表向导，选择“数据透视表”，单击“完成”,Excel数据透视表应用,4、拖动相应的“字段项”及“数据项”，构建数据透视表,3,Excel数据透视表应用,5、根据需求设置数据“字段格式”,4,Excel数据透视表应用,6、使用“设置报表格式”来选择所需要的报表格式,5,Excel数据透视表应用,数据透视表的其他功能,1、数据行、列隐藏,将光标移至想要隐藏的行或列前，当光标变成由左至右或由上至下的黑色箭标时，单击鼠标，就可以选择需隐藏的所有同类型的行或列，然后单击鼠标右键，选择“隐藏”就可以了。,Excel数据透视表应用,数据透视表的其他功能,2、添加字段计算,根据需要对数据透视表中的单元格进行计算。,Excel数据透视表应用,数据透视表的其他功能,3、自动筛选及分页显示,A、我们可以根据标题栏的下拉箭头来自动选择，如果不需要的项目，直接在前面将勾去掉，数据透视表中便不会包含这一项了。,B、对于分类项目，我们可以利用分页显示功能，在各个工作表中显 示每个明细项，如下例中，我们可以将各个部门直接在不同的工作 表中显示。,Excel数据透视表应用,数据透视表的其他功能,4、多个数据库合并透视,数据透视表还可以将多个明细工作表中的数据合并在一个数据透视表中汇总显示。这种功能在我们实际工作中应用不多,谢谢大家!,