《excel培训》PPT课件 (2).ppt

EXCEL 基础知识,主要内容,EXCEL基础 EXCEL 数据管理 EXCEL 图表的创建 EXCEL公式与函数的应用 Excel使用技巧,EXCEL基础,EXCEL的启动和退出 EXCEL的组成 单元格的操作 工作簿的操作,EXCEL的工作环境,启动EXCEL程序,(1)通过WINDOWS开始菜单 (2)通过桌面快捷方式 (3)以安全模式启动EXCEL 如果（1），（2）两种方式均无法正常启动EXCEL，可以在键盘上按住CTRL键然后启动EXCEL进入安全模式，此时EXCEL只提供最基本的功能，而禁止使用部分可能产生问题的功能。,EXCEL的组成 默认情况下，打开一个工作簿时打开三个工作表，最多可建立255个。 行号自“1”到“65536”，列号由左到右采用字母编号从“A”增加到“IV”。,文件的概念,“文件”就是“存储在磁盘上的信息实体”。使用计算机，可以说用户几乎每时每刻都在与文件打交道。如果把计算机比做一个书橱，那么文件就好比是放在书橱里的书本。每本书都会在封面上印有书名，而文件同样也有“文件名”作为它们的标识。 对于书本来说，通常会有历史类、文学类、科技类、社会类等不同的种类划分。类似地，对于计算机文件来说，也有不同的类型之分。在WINDOWS操作系统中，不同类型的文件会显示为不同的图标，以帮助用户直观地进行区分。EXCEL文件的图标都会包括一个绿色的EXCEL程序标志。如下图：,文件的概念,除了图标以外，用户区别文件类型的另一个重要依据是文件的“扩展名”。“扩展名”也被称为“后缀名”或“后缀”，事实上是文件名的一部分。熟悉早期DOS操作系统的用户一定会清楚扩展名这个概念，但由于它在WINDOWS操作系统中并不是总是显示出来，所以可能容易被人忽视。 显示并查看文件扩展名的方法如下： 在键盘上按WIN+E组合键打开WINDOWS资源管理器，在窗口的菜单栏上依次单击“工具”“文件夹选项”，在弹出的“文件夹选项”对话框中选择“查看”选项卡，然后去掉“隐藏已知文件类型的扩展名“复选框的勾选即可。,文件的概念,文件的概念,取消扩展名隐藏之后，可以在文件夹窗口中看到所有的文件都显示出其完整名称。其文件名中”.“之后的”XLS“就是此文件的扩展名，标识了这个文件的类型。,其它不同类型的文件也会相应有不同的扩展名，例如WORD文档的扩展名默认为”.doc“，PowerPoint的演示文稿文件扩展名默认为”.ppt“等。,EXCEL的文件,通常情况下，EXCEL文件是指EXCEL的工作薄文件，即扩展名为XLS的文件，这是EXCEL最基本的电子表格文件类型。,EXCEL的文件,（2）加载宏文件（.XLA） 加载宏是一些包含了EXCEL扩展功能的程序，其中既包括EXCEL自带的加载宏程序（如分析工具库、规划求解等），也包括用户自己或第三方软件厂商所创建的加载宏程序（如自定义函数、命令等）。加载宏文件（.XLA）就是包含了这些程序的文件，通过移植加载宏文件，用户可以在不同的电脑上使用相同的加载宏程序。 （3）工作区文件（.XLW） 许多时候，当用户在处理一些较为复杂的EXCEL工作时，通常会在EXCEL工作窗口中同时打开多个工作薄。这样的工作也许并非一时之间就能处理完成，用户在下一次继续这项工作时往往还需要再次打开先前的这些工作薄。而通过保存工作区的功能可以很方便地为用户记忆下每个当前打开的工作薄名称，以备在下一次继续工作时能够快速地再次同时打开这些所有的EXCEL工作薄。这个能够保存用户在当前工作区打开工作薄状态的文件就是工作区文件（.XLW）。,理解EXCEL文件的概念,EXCEL的文件,（4）备份文件（.XLK） （5）网页文件（.MHT、.HTM） （6）其他类型的文件格式 .CSV（逗号分隔，EXCEL电子表格的另一种表现形式） 等,菜单命令操作的快捷键和组合键,除了使用鼠标，还可以使用键盘来选中菜单项及菜单命令。菜单项及菜单命令后面的括号中，都有一个英文大写字母，在键盘上按ALT+字母可以快速定位到相应的菜单项，继续按快捷字母键则可以选中相应菜单命令。例如：下图中的方法可以选中”编辑“菜单中的”复制“命令。,先按ALT后再按E,再按C,使用键盘选中菜单命令,组合快捷键,还有一类组合快捷键则标识在菜单命令的右侧，在键盘上同时按下这样的按键组合就可以快速执行菜单命令。,窗口元素的显示和隐藏,用户可以根据自己的使用习惯和需要进行一些设置，来控制显示或隐藏上面所提到的这些窗口元素。,1、视图菜单 在EXCEL窗口的菜单栏上单击”视图“菜单项，显示的下拉菜单如图：,下拉菜单的中间一栏中分别有”任务窗格“、”工具栏“、”编辑栏“和”状态栏“4个选项，其中”工具栏“还有二级下拉菜单显示。鼠标单击勾选这些选项，就可以在当前EXCEL的工作窗口中显示这些窗口元素；再次单击鼠标去除选项前的勾选，则可以隐藏当前工作窗口中的元素。其总的工具栏可以显示多组，如左图所示就是勾选显示了”常用“和”格式“、”绘图“三组工具栏。关闭EXCEL后，下次重新启动EXCEL时前面进行的视图设置依然有效。 通过右键单击菜单栏或现有工具栏，也可在快捷菜单中设置菜单栏和工具栏的显示方式。,窗口元素的显示和隐藏,2、工具选项设置 把菜单栏上依次单击菜单”工具“”选项“，在弹出的”选项“对话框中选择”视图“选项卡。 ”编辑栏“复选框和”状态栏“复选框控制着是否显示工作窗口中的编辑栏和状态栏。但勾选或去除勾选”启动任务窗格“并不能立即对工作窗口中的任务窗格产生作用，而是要在下一次启动EXCEL时才有相应的显示效果。,”窗口选项“区域中还有”水平滚动条“、”垂直滚动条“和”工作表标签“的复选框，勾选或去除勾选这些复选框可以控制滚动条和工作表标签的显示和隐藏，但此处的设置只对当前工作薄起作用。保存当前工作薄可以保留这些设置，并在下一次打开此工作薄时依然生效。,使用工具栏,在默认情况下，EXCEL的工作窗口中会显示两组工具栏，分别为”常用“和”格式“工具栏。除此以外，EXCEL还内置了”绘图“、”公式审核“、”控件工具箱“等数十种不同功能的工具栏供用户选择使用。用户可以根据自己的使用习惯和需要来调整工作窗口中的工具栏类型和布局。 1、隐藏和显示工具栏 前面已经介绍过显示和隐藏工具栏的方法，其中通过在菜单栏或工具栏上单击鼠标右键，在弹出的工具栏快捷菜单中选择所需要显示的内置工具栏。如下图，只要勾选相应的项目，即可显示此工具栏，而取消勾选则可以隐藏相应的工具栏。,使用工具栏,如果在快捷菜单上选择最后一项”自定义“，将弹出”自定义“对话框。选择”工具栏“选项卡，显示如下图：,可以发现，这里有比前面的快捷菜单中更多的工具栏类型选项，如”并排比较“、”全屏显示“等，通过勾选或取消勾选这些复选框，同样可以控制工作窗口中相应工具栏的显示和隐藏。 注意：”工作表菜单栏”这一项对应于工作窗口中的菜单栏,这个选项总是会在勾选状态,即显示菜单栏,取消勾选的操作将会无效。 注意：有些工具栏会在执行某些命令或在选定某些对象时自动显示，在结束操作或离开对象时自动隐藏，例如“图表”工具栏。,使用工具栏,2、移动和调整工具栏,设置为显示状态的工具栏有些会停靠在EXCEL工作窗口的上下左右4个边框上，如下图中的“停靠于底部的工具栏”，有些则会以浮动的形式出现，如下图中的“浮动工具栏”，浮动的工具栏上含有标题栏以显示工具栏的名称。,使用工具栏,2、移动和调整工具栏,将鼠标移至停靠的工具栏左侧，当鼠标指针显示为十字箭头时，可以按住鼠标左键移动工具栏。停靠在窗口边框上的工具栏可以移动到其他方向上的边框（例如从窗口底部移至窗口顶部），也可移动到窗口内部形成浮动工具栏。 类似地，浮动工具栏也可以移至窗口上的任意位置，或是移至窗口边沿形成停靠的工具栏。与前面的操作稍有不同，在浮动工具栏的标题栏或空白位置按下鼠标左键即可拖动工具栏，此时鼠标指针显示为十字箭头。 浮动工具栏还可以通过鼠标拉伸来改变其大小形状。将鼠标移至浮动工具栏的边沿，当鼠标指针显示为双向箭头时，可以按住鼠标左键进行拉伸变形，改变形状后的工具栏如上一张幻灯片中所示。,右键快捷菜单,除了在菜单栏和工具栏上选择执行EXCEL命令和功能外，还可以通过使用快捷菜单来选定命令，使用快捷菜单可以使命令的选择更加快速有效。在EXCEL中，通常只要单击鼠标右键就可以显示出快捷菜单，所显示的快捷菜单内容取决于鼠标所选定的对象。例如，对一个单元格进行单击鼠标右键的操作，则出现的快捷菜单会包含单元格操作命令。如右图：,单元格的操作 a)单元格的地址 表达法：工作表表名！列标行号。 例：D5( 当前工作表）、sheet2!e2(非当前工作表） 地址的引用：用：隔开表示引用，例b2:d3 用，隔开表示并列，例b2:c3,d5:e6 b)单元格的选定 选定一单元格：左键单击该单元格,选定连续区域： 拖动法：将鼠标指向要选定区域的左上角，拖动鼠标到选定区域的右 下角后松开左键。 快速法：选定区域的左上角单元格，按住shift键，单击选定区域的右下角单元格。 指定法：在名称框中输入单元格引用地址后回车。 选定数据区域：选定其中一个单元格，按ctrl+shift+8或ctrl+*。 选定行和列：单击行号、列号；按住“Ctrl”键可选多行和多列。 选定不连续区域：按住“Ctrl”键或按一次shift+F8。再分别选择区域。 全部选定：按一下工作表左上角的全选按钮或用快捷钮 ctrl+A. c)单元格的命名： 选取单元格区域，在名称框中输入名称后回车确定。,工作簿的操作 创建工作簿：单击文件/新建命令；在任务任务窗口中选择“空白工作簿”或“本机上的模板”，选择一种模板后确定。 保存工作簿：单击文件/保存或另存为命令 a)保存为模板：在“保存类型”中选择“模板”，保存位置自动为“Templates”文件夹。 b)加密保存：单击“工具”下拉菜单中“选项”，在保存选项对话框中输入打开密码或修改密码后确定。,EXCEL 数据管理,排序 筛选,排序 简单排序：选中要排序列中任一单元格，单击常用工具栏的“升序”或“降序”按钮。 多重排序：选定数据区域，单击数据/排序命令，在“排序”对话框中依此选择主要关键字及升降序和次要关键字及升降序；防止字段名参与排序可选中“有标题行”；单击“选项”可进一步设置排序方向和排序方法。 按行排序：只要在“选项”中设置排序方向为“按行”即可。,筛选 自动筛选: 选中数据清单中任一个单元格，单击数据/筛选/自动筛选命令，字段名右下角出现自动筛选箭头。 单击某字段的筛选箭头，在下拉列表中可选择排序、“前10个”、“全部”或具体数值。 如选择“自定义”则弹出“自定义自动筛选方式”对话框，输入筛选条件后确定。 取消筛选则重新单击数据/筛选/自动筛选命令，或显示“全部”。,高级筛选： 在数据清单外输入筛选条件，单击数据清单中任一个单元格，单击数据/筛选/高级筛选命令，出现高级筛选对话框。 在“方式”框中选择输出位置、确认“列表区域”、输入“条件区域”后确定。 取消高级筛选则单击数据/筛选/全部显示命令。,创建图表,图表用于将数据转化成图片。,创建图表,案例：根据下图表格建立学生成绩统计图,创建图表步骤,Step1：选择希望插入图表中的 数据，同时选择列标题和行标 题 。 Step2：点击菜单【插入图 表】，打开图表向导 Step3：当该向导打开时，会自 动选择柱形图类型。您可以方便 地选择其他图表类型 。 Step4：接下来，根据向导完成 对应设置 。,创建图表,第一步 选择创建图表的数据,选择A2:E6 必须包含行、列标题栏,创建图表,第二步 打开图表向导,创建图表,第三步 选择图表类型,可以在图表类型中选择各种图表，本案例我们用柱形图的第一个子图进行举例,点击此按钮，可以立即预览图表效果,设置完成后，点击下一步进入数据源设置,创建图表,第四步 图表向导图表数据源,默认显示第一步时选择的数据源,选择行，则表示X轴为成绩科目 选择列，则表示X轴为姓名,表示系列,创建图表,第四步 图表向导图表选项,填写图表的标题、X轴、Y轴的名称,注： 为图表添加标题是一种非常好的习惯 另外可以通过坐标轴、网格线、图例、数据标志、数据表页签来设置图表的其他属性,创建图表,第四步 图表向导图表位置,注： 作为新工作表插入表示独立图表为单独的一页，可以直接打印。 作为其中的对象插入是指该图表作为“对象”与数据放置在同一张工作表上。它可以移动和调整大小。它还可以与源数据一起打印。,创建图表,完成后的效果,注：完成后的图表，可以直接进行文字修改，字体设置，移动等操作,EXCEL公式与函数的应用,要点一：了解EXCEL函数与公式的概念 要点二：认识并掌握单元格引用 要点三：了解并掌握函数与公式的输入和编辑 要点四：掌握函数公式的检验方法,认识公式,公式的概念,什么是EXCEL的公式（Formula）？公式就是用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的模式。 用户使用公式是为了有目的地计算结果，因此EXCEL的公式必须（且只能）返回值。,公式的结构,下面表达式就是一个简单的公式实例。 =(C2+D2)*5 从公式结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，等号是不可或缺的。但在实际应用中，公式还可以使用树组、EXCEL函数或名称（命名公式）来进行运算。,公式的输入、编辑与复制,用户在输入公式时，通常以等号“=”作为开始（EXCEL的智能识别功能也允许使用加减号作为公式的开始，系统会自动前置等号），否则EXCEL只能将其识别为文本。 当单元格中首先输入“=”时，EXCEL就会识别其为公式输入的开始，按键结束公式的编辑。 如果用户希望对原有公式进行编辑，使用以下几种方法可以进入单元格编辑状态。 （1）选中公式所在单元格，并按下F2键 （2）双击公式所在单元格 （3）选中公式所在单元格，单击列标上方的编辑栏 如果在某个区域使用相同的计算方法，用户不必逐个编辑函数公式，这是因为公式具有可复制性。 如果希望在连续的区域中使用相同算法的公式，可以通过“双击”或“拖动”单元格右下角的填充柄进行公式的复制；如果公式所在单元格区域并不连续，还可以借助“复制”和“粘贴”功能来实现公式的复制。,使用简单公式计算产品销售利润,下图展示了某公式4月份所有产品的销售情况，通过下面的公式可以计算各产品的销售利润。,由于销售利润=产品单价*销量*利润率（已知为20%），因此E3公式为： =C3*D3*0.2 由于各个产品利润的计算方式相同，因此只需要拖动E3单元格右下角的填充柄，向下填充即可复制公式。 提示：使用快捷键在连续区域中填充公式与填充值的方法相同：当选择E3：E7区域后，再按下组合键可以实现公式的快速复制。,10EG001,利用“选择性粘贴”来复制公式,沿用上一个实例的销售情况表，如果5月份的销量表结构与4月份完全相同，使用“选择性粘贴”功能可以实现不连续区域的公式复制。如下图：,步骤1：选中E4单元格，按下组合键，复制公式； 步骤2：选择“H4:H8”单元格,单击鼠标右键,在弹出的右键菜单中选择”选择性粘贴”； 步骤3：在“选择性粘贴”对话框的“粘贴”类型下点选“公式”选项，单击“确定”按纽完成公式复制。如右图：,公式中的运算符,运算符是构成公式的基本元素之一,每个运算符分别代表一种运算.如下表所示,EXCEL包含4种类型的运算符算术运算符、比较运算符、文本运算符和引用运算符。 算术运算符：主要包含了加、减、乘、除以及乘幂等各种常规的算术运算； 比较运算符：用于比较数据的大小，包括对文本或数值的比较； 文本运算符：用于将文本字符或字符串进行连接、合并； 引用运算符：这是EXCEL特有的运算符，主要用于单元格引用。,数据的比较,在E中，除错误值以外，文本、数值、和逻辑值之间都存在着大小关系，了解这种关系在数据之间进行比较判断时显得非常重要。 各类数据的大小排列顺序为： 、-2、-1、0、1、2、A-Z、FALSE、TRUE 即数值小于文本，文本小于逻辑值，错误值不参与排序。,示例：判断A2单元格是否大于5且小于10 假设希望判断A2单元格的值是否大于5且小于10，如果使用下面的公式将永远无法得出正确结果。 =5A210 假设当A2单元格为数值8时，公式从左向右首先运算5A2，结果为True，再运算True10，结果只能为False；而且当A2为任意非错误值时，结果都只能为False，所以都无法得出正确结果，该公式的正确写法应为： =AND(5A2,A210),运算符的优先顺序,通常情况下，EXCEL按照从左向右的顺序进行公式运算，当公式中使用多个运算符时，EXCEL将根据各运算符的优先级进行运算，对于同一级次的运算符，则按自左而右的顺序运算。具体的优先顺序如下图：,运算符的优先顺序,括号的优先级次高于上图中所有运算符，因此可以在公式中使用括号来调整运算的优先级次。如果在公式中嵌套括号，其计算顺序是由最内层的括号逐级向外进行运算。 了解运算符的优先级非常重要，只有理解和掌握其处理原则，才能有效地避免在编写公式中出现运算错误。 例1 下面的公式将实现对A3，B3和C3单元格的销量求和后再乘以利润率D1得出总利润。 =A3+B3+C3*D1 （错误公式） =(A3+B3+C3)*D1 （正确公式） 例2 假设A1、B1和C1单元格分别为数值95，9和8，下面的公式将比较A1的数值与B1连接C1生成的文本型数值的大小，即是95-“98”为-3，正确结果应该为FALSE。 =A1-B1&C10 （错误公式） =A1-(B1&C1)0 （正确公式）,理解EXCEL函数,函数的概念,EXCEL的工作表函数（WorkSheet Function）通常被简称为EXCEL函数，它是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此，EXCEL函数也常被人们称为“特殊公式”。与公式一样，EXCEL函数的最终返回结果为值。 EXCEL函数只有唯一的名称且不区分大小写，它决定了函数的功能和用途。 EXCEL函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。如SUM(A1:A10,C1:C10)。,理解EXCEL函数,函数公式的结构,当EXCEL函数在公式中出现时，它通常由两个部分组成：一个是函数名称前面的等号，另一个则是函数本身。如果是以嵌套函数出现，则只包含函数本身。 例如常见IF条件判断函数公式结构，如下图：,对于函数的参数来说，可以由数值、日期和文本等元素组成，也可以使用常量、数组、单元格引用或其他函数。当函数的参数也是函数时，EXCEL称之为函数的嵌套。,理解EXCEL函数,为什么在公式中使用函数,虽然用户通过自行设计公式也可以实现某些计算，但EXCEL专门提供了大量功能强大的工作表函数来帮助用户解决实际问题，其除了能够简化公式书写外，还可实现许多自编公式无法实现的需求。 例如，计算单元格区域A1:A5数值的平均值，初学者通常利用下面的公式来处理： =(A1+A2+A3+A4+A5)/5 而使用函数表达方便得多 =AVERAGE(A1:A5),理解EXCEL函数,函数的易失性,某些时候，当用户打开一个工作薄但不做任何更改就关闭文档时，EXCEL却提示“是否保存对文档的更改？”。则有可能是打开的EXCEL文档中使用了某些具有Volatile特性的函数，即“易失性函数”。 这类函数的特征表现在：当用户激活一个单元格或在一个单元格输入数据后，甚至只是打开工作薄，具有易失性的函数都会自动重新计算。 易失性函数在以下情形下不会引发自动重新计算： 工作薄的重新计算模式设置为“手动重算”时； 当手工设置列宽、行高时，但隐藏行或设置行高值为0时除外； 当设置单元格格式或其他更改显示属性的设置时。,理解EXCEL函数,常用函数的分类,在EXCEL函数中，根据来源的不同，通常分为以下三种： 内置函数 只要启动了EXCEL就可以使用的函数 扩展函数 必须通过单击“工具加载宏”菜单命令加载，才能正常使用的函数 自定义函数 通过VBA代码实现特定功能的函数 在内置函数和扩展函数中，根据应用领域的不同，EXCEL函数一般分为：,在EXCEL的300多个函数中，用户实际经常使用的函数不足100个,理解EXCEL函数,函数输入和编辑,为了便于快速地输入函数公式，EXCEL提供了几种方法供用户选择 利用“自动求和”按纽输入函数； 利用“函数向导”插入函数； 直接手工输入函数。 使用工具栏“自动求和”按纽插入函数,单击自动求和“”按纽，即可得到汇总结果。,理解EXCEL函数,函数输入和编辑,使用插入函数向导 插入函数向导是一个交互式输入函数的对话框，选中任意单元格，按下组合键或者单击编辑栏左侧的Fx按纽，可以打开“插入函数”对话框。,如果用户对函数所属类别不太熟悉，可以在对话框的“搜索函数”文本框里输入 简单的描述寻找合适的函数。 如在“搜索函数”编辑框中输入“查找信息”，再单击“转到”按纽，系统会显示一个“推荐”列表，如下图，可以通过查看列表中的函数简介以确定使用具体的函数，最终单击“确定”按纽来输入函数。,理解EXCEL函数,函数输入和编辑,手工输入函数 用户也可以直接在单元格中输入函数公式，当输入等号、函数名和左括号，系统会自动出现当前函数的语法结构信息提示，如下图：,当用户对函数参数结构不太明确或希望进一步了解参数含义时，可以按下或组合键来打开”函数参数”对话框，如下图所示：,理解EXCEL函数,函数输入和编辑,在“插入函数”和“函数参数”对话框时，适时地使用函数帮助文件将对理解函数大有帮助。在任何时候都可以单击左下角的“有关该函数的帮助”或按下键来获取当前函数的帮助文档。,另外，爱输入函数名称后，如下图的提示信息中单击“函数名称”，同样可以打开当前函数的帮助文档。,输入公式的注意事项,有时候，用户已经按照正确的方式输入了函数公式，但结束编辑后，公式并未得到计算值，而是显示公式表达式本身。 以下几种方法将帮助用户查找并解决问题。,确认“选项”对话框中“公式”复选框是否被选中 （正常情况下，该复选框是没有被选中的） 检查单元格格式是否在输入公式前被设置为文本格式 注意：如果在公式输入之后变更单元格格式，则不会出现此现象；当用户将文本格式转为常规格式后，单元格依然显示为公式表达式，这是因