Excel应用技巧.ppt

《Excel应用技巧.ppt》由会员分享，可在线阅读，更多相关《Excel应用技巧.ppt（51页珍藏版）》请在三一办公上搜索。

1、EXCEL常用公式及使用技巧,第一章 数据的整理和分析,1.1 数据的排序1.2 数据的筛选1.3 数据的分类汇总与分级显示1.4 条件格式1.5 数据透视表1.6 冻结和拆分窗口1.7 选择性粘贴,第二章 Excel 中的图表2.1 图表的建立2.2 图表的修改第三章 公式和函数3.1 绝对地址与相对地址3.2 公式创建3.3 函数使用第四章 高级技巧4.1 单元格内容的合并4.2 如何避免错误信息4.3 批量删除空行,1.1数据的排序,Excel 提供了多种方法对工作表区域进行排序，用户可以根据需要按行或列、按升序或降序已经使用自 定义排序命令。当用户按行进行排序时，数据列表中的列将被重新

2、排列，但行保持不变，如果按列进行排 序，行将被重新排列而列保持不变。没有经过排序的数据列表看上去杂乱无章，不利于我们对数据进行查找和分析，所以此时我们需要按 照对数据表进行整理。我们可以将数据列表按“出生年月”进行排序。如下图：,1.1数据的排序,首先我们需要单击数据列表中的任意一个单元格，然后单击数据标签中的排序按钮，此时会出现排序 的对话框。,看到弹出的排序对话框中，在“主要关键字”下拉列表框中选择“出生年月”，在设置好主要关键字后，可 以对排序依据进行设置，例如，数值、单元格颜色、字体颜色、单元格图标，在这样我们可以选择默认的 数据作为排序依据。最后我们可以对数据排序次序进行设置，在次序

3、下拉菜单中选择升序、降序或自定义 排序。在这里我们选择升序，设置完成后，单击确定即可。如下图：,1.1数据的排序,我们除了可以对数据表进行单一列的排序之外，如果用户希望对列表中的数据按“性别”的升序来排 序、性别相同的数据按“文化程度”升序排序、“性别”和“文化程度”都相同的记录按照“基本工资”从 小到大的顺序来排序，此时我们就要对 3 个不同的列进行排序才能达到用户的要求。首先我们需要单击数据列表中的任意一个单元格，然后单击数据标签中的排序按钮，此时会出现排序的对话框。看到弹出的排序对话框中，在“主要关键字”下拉列表框中选择“性别”，在添加好主要关键字后，单击“添加条件”按钮，此时在对话框中

4、显示“次要关键字”，同设置“主要关键字”方法相同，在下 拉菜单中选择“文化程度”，然后再点击“添加条件”添加第三个排序条件，选择“基本工资”。在设置好 多列排序的条件后，单击确定即可看到多列排序后的数据表。如下图：,在 Excel 2007 中，排序条件最多可以支持 64 个关键字。,1.1数据的排序,如果你要将员工按其所在的部门进行排序，这些部门名称既的有关信息不是按拼音顺序，也不是按笔画顺序，怎么办?可采用自定义序列来排序。1.执行“格式选项”命令，打开“选项”对话框，进入“自定义序列”标签中，在“输入序列”下面的方框中输入部门排序的序列(如“机关,车队,一车间,二车间,三车间”等)，单击

5、“添加”和“确定”按钮退出。2.选中“部门”列中任意一个单元格，执行“数据排序”命令，打开“排序”对话框，单击“选项”按钮，弹出“排序选项”对话框，按其中的下拉按钮，选中刚才自定义的序列，按两次“确定”按钮返回，所有数据就按要求进行了排序。,1.2数据的筛选,筛选数据列表的意思就是将不符合用户特定条件的行隐藏起来，这样可以更方便的让用户对数据进行查看。Excel 提供了两种筛选数据列表的命令。自动筛选：适用于简单的筛选条件 高级筛选：适用于复杂的筛选条件想要使用 Excel 的自动筛选功能，首先要单击数据列表中的任意单元格，然后选择数据标签，单击筛选 按钮即可。,1.2数据的筛选,单击数据列表

6、中的任何一列标题行的下拉箭头，选择希望显示的特定行的信息，Excel 会自动筛选出包含这 个特定行信息的全部数据。,1.2数据的筛选,在数据表格中，如果单元格填充了颜色，使用 Excel 2007 我们还可以按照颜色进行筛选。如果条件比较多，可以使用“高级筛选”来进行。使用高级筛选功能可以一次把我们想要看到的数据都找出来。例如数据表中我们想要把性别为男、文化程度为研究生、工资大于 2000 的人显示出来。我们可以这么来做：先设置一个条件区域，第一行输入排序的字段名称，在第二行中输入条件，建立一个 条件区域。如下图：,1.2数据的筛选,然后选中数据区域中的一个单元格，单击数据标签中的高级筛选命令

7、。Excel 自动选择好了筛选的区域，我 们单击这个条件区域框中的拾取按钮，选中我们刚才设置的条件区域，单击拾取框中的按钮返回高级筛选 对话框，单击确定按钮，现在表中就是我们希望看到的结果了。,1.3数据的分类汇总与分级显示,分类汇总是 Excel 中最常用的功能之一，它能够快速地以某一个字段为分类项，对数据列表中的数值字 段进行各种统计计算，如求和、计数、平均值、最大值、最小值、乘积等。比如这张各部门工资统计表，我们希望可以得出数据表中每个部门的员工实发工资之和。,1.3数据的分类汇总与分级显示,现在我们用分类汇总来做：首先单击部门单元格，单击数据标签中的升序按钮，把数据表按照“部门”进 行

8、排序，然后在数据标签中，单击分类汇总按钮，在这里的分类字段的下拉列表框中选择分类字段为“部 门”，选择汇总方式为“求和”，汇总项选择一个“实发工资”，单击“确定”按钮。如下图：,1.3数据的分类汇总与分级显示,当我们点击确定后，我们就可以看到已经计算好各部门实发工资之和了。如图：,1.3数据的分类汇总与分级显示,在分类汇总中我们的数据是分级显示的，现在工作表的左上角出现了这样的一个区域，我们单击这个 1，在表中就只有这个总计项出现了。,单击这个 2，出现的就只有这些汇总的部分，这样我们可以清楚地看到各部门的汇总。,单击 3，可以显示所有的内容。,1.3数据的分类汇总与分级显示,复制汇总结果 当

9、我们使用分类汇总后，往往希望将汇总结果复制到一个新的数据表中，如下图。但当我们直接进行复制 后，无法只复制汇总结果，而复制的是所有数据。此时我们就需要使用“Alt+;”组合键选取当前屏幕中显示 的内容，然后在进行复制粘贴。,1.4条件格式,使用 Excel 中的条件格式功能，可以预置一种单元格格式，并在指定的某种条件被满足时自动应用于目 标单元格。可以预置的单元格格式包括边框、底纹、字体颜色等。此功能可以根据用户的要求，快速对特 定单元格进行必要的标识，以起到突出显示的作用。例如下图中的产品销售标，我们需要快速找出所有与“防水键盘”相关的销售数据。,1.4条件格式,首先我们全选所有数据，然后在

10、开始标签中找到条件格式按钮，单击突出显示单元格规则，在打开的 选项中单击等于按钮。,1.4条件格式,的对话框中输入我们想要查找的“防水键盘”，然后设置单元格显示样式，例如让单元格以“浅 红填充色深红色文本”显示，设置完毕后，点击确定按钮。,在点击确定后，我们可以数据表中已经显示出和防水键盘相关的所有信息。这样我们就可以快速查看我们所关心的信息了。如下图：,1.4条件格式,在 Excel 2007 中，使用条件格式不仅可以快速查找相关数据，还可以以数据条、色阶、图标的方式显示 数据，让用户可以对数据一目了然。首先我们全选所有数据，然后在开始标签中找到条件格式按钮，单击数据条，在打开的选项中选择任

11、 意颜色。当我们选择颜色时，即使不点击，也可以即时的看到数据变化。通过条件格式，我们就可以清楚 的看到那条数据销售额最高，那条数据销售额最低。如下图：,1.5数据透视表,数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。例如，计算平均数、标准差，建立列联表、计算百分比、建立新的数据子集等。建好数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。数据透视表可以从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策所用。我们来看下面这个数据表，在这个数据表中显示了每个财季产品销售的明细数据。,1.5数据透视表

12、,现在我们想把这里的统计每个财季的利润，可以结合条件格式判断最低利润出自哪个财季。首先打开插入 标签，单击数据透视表按钮，打开创建数据透视表对话框。然后选择透视表的数据来源的区域，Excel 已经 自动选取了范围，这里它的选取是正确的，我们不做什么改动，接下来我们选择透视表放置的位置，选择 新建工作表项，单击确定按钮。,1.5数据透视表,在工作表的透视表的各个部分都有提示，同时界面中出现了一个数据透视表字段列表，里面列出了所有我们可以使用的字段，我们的目的是查看统计每个财季的利润，因此我们在要添加到报表的字段中选择财季，然后拖动利润和数量字段到数值区域，此时，我们就可以看到每个财季的利润和销售

13、数量显示在数据透视 表中了。同样的，在数据透视表中可以直接看到利润和销售数量的汇总数目。,1.5数据透视表,现在我们就可以结合条件格式功能，查看出那个财季利润最低。如下图：,1.6冻结和拆分窗口,我们可以使用视图标签中的拆分按钮来拆分窗口。而如果只需要垂直拆分，可以直接拖动垂直 滚动条到最顶端，此时横项拆分线就会消失。,撤消窗口的拆分：同样在视图标签下，再次点击拆分按钮即可撤销窗口的拆分。,1.6冻结和拆分窗口,冻结窗格：我们可以使用冻结窗格来更好的查看数据表头。首先选中一个单元格，选择视图标签，点击冻 结窗，在下拉菜单中我们可以选择冻结第一行或第一列，当然我们也可以自行选择想要冻结的位置。如

14、下 图：,如果我们想要冻结第一行以及 A、B 两列，此时我们需要将光标放在第一行的下面，并且 A、B 两列的右侧，即 C2 单元格，此时点击冻结拆分窗格即可冻结第一行以及 A、B 两列。,1.7选择性粘贴,Excel 为我们提供了一些自动功能。例如选择性粘贴。这里的选择性粘贴是指把剪贴板中的内容按照一定的 规则粘贴到工作表中，而不是像前面那样简单地拷贝。就拿这个数据表来举例。如下图：,这里的“销售利润”一栏是我们使用公式计算得到的，选择这一栏，复制到 Sheet2 中，可以看到数值并没有跟着复制过来；这时就可以使用选择性粘贴了：单击鼠标右键，单击“选择性粘贴”命令，打开“选择性粘贴”对话框（如

15、下图），在“粘贴”一栏中选择“数值”，单击“确定”按钮，数值就可以粘贴过来 了。这种情况不仅是在几个工作表之间复制时会发生，在同一个工作表中进行复制时也会遇到，到时可要注意。,1.7选择性粘贴,选择性粘贴还有一个很常用的功能就是转置功能。简单地理解就是把一个横排的表变成竖排的或把一 个竖排的表变成横排的：选择这个表格，复制一下，切换到另一个工作表中，打开“选择性粘贴”对话框，选中“转置”前的复选框，单击“确定”按钮，可以看到行和列的位置相互转换了过来。另外一些简单的计算也可以用选择性粘贴来完成：选中这些单元格，复制一下，然后打开“选择性粘 贴”对话框，在“运算”一栏选择“加”，单击“确定”按钮

16、，单元格的数值就是原来的两倍了。此外你 还可以粘贴全部格式或部分格式，或只粘贴公式等等。,2.1图表的建立,图表是图形化的数据，它由点、线、面等图形与数据文件按特定的方式而组合而成。一般情况下。用户使用 Excel 工作薄内的数据制作图表，生成的图表也存放在工作薄中。图表是 Excel 的中要组成部分，具有直观形象、双向联动、二维坐标等特点。我们拿一个例子来讲吧：这是一个市场调查表，显示了几种品牌的 饮料在各个季度的销量百分比。我们来做一个表示第一季度的几种商品所占比例的饼图，首先选择数据区域，然后选择插入选项卡，单击 饼图按钮，再打开的下拉菜单中选择饼图样式，此时我们就可以看到我们已经创建了

17、一个饼图。如下图：,2.2图表的修改,我们经常可以看到那种有一部分同其它的部分分离的饼图，这种图的做法是：单击这个圆饼，在饼的周围 出现了一些句柄，再单击其中的某一色块，句柄到了该色块的周围，这时向外拖动此色块，就可以把这个 色块拖动出来了；同样的方法可以把其他各个部分分离出来。或者我们在插入标签中直接选择饼图下拉菜单，选择分离效果即可。如下图：,2.2图表的修改,我们还经常可以见到这样的饼图：把占总量比较少的部分单独拿出来做了一个小饼以便看清楚，做这种图的方法：在插入标签中直接选择饼图下拉菜单，选择相应效果即可。如下图：,3.1绝对地址与相对地址,随着公式的位置变化，所引用单元格位置也是在变

18、化的是相对引用；而随着公式位置的变化所引用单元格 位置不变化的就是绝对引用。下面讲一下“C4”、“$C4”、“C$4”和“$C$4”之间的区别。在一个工作表中，在 C4、C5 中的数据分别是 60、50。如果在 D4 单元格中输入“=C4”，那么将 D4 向下拖动到 D5 时，D5 中的内容就变成了 50，里面的公式是“=C5”，将 D4 向右拖动到 E4，E4 中的内容是 60，里面 的公式变成了“=D4”。,3.1绝对地址与相对地址,现在在 D4 单元格中输入“=$C4”，将 D4 向右拖动到 E4，E4 中的公式还是“=$C4”，而向下拖动到 D5 时，D5 中的公式就成了“=$C5”。

19、如果在 D4 单元格中输入“=C$4”，那么将 D4 向右拖动到 E4 时，E4 中的公式变为“=D$4”，而将 D4 向下拖动到 D5 时，D5 中的公式还是“=C$4”。如果在 D4 单元格中输入“=$C$4”，那么不论你将 D4 向哪个方向拖动，自动填充的公式都是“=$C$4”。原来 谁前面带上了“$”号，在进行拖动时谁就不变。如果都带上了“$”，在拖动时两个位置都不能变。,3.2公式创建,公式是由用户自定设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。使用公式是为了有目的地计算结果，因此 Excel 的公式必须（且只能）返回值。下面的表达式就是一个简单的公式实例。

20、=（C2+D3）*5从公式的结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，等号是不可 或缺的。但在实际应用中，公式还可以使用数组、Excel 函数或名称（命名公式）来进行运算。如果在某个区域使用相同的计算方法，用户不必逐个编辑函数公式，这是因为公式具有可复制性。如果希望在连续的区域中使用相同算法的公式，可以通过“双击”或“拖动”单元格右下角的填充柄进行公 式的复制。如果公式所在单元格区域并不连续，还可以借助“复制”和“粘贴”功能来实现公式的复制。,3.3函数使用,Excel 的工作表函数通常被简称为 Excel 函数，它是由 Excel 内部预先定义并按照特定的顺序、结

21、构来执行计算、分析等数据处理任务的功能模块。因此，Excel 函数也常被人们称为“特殊公式”。与公式一样，Excel函数的最终返回结果为值。Excel 函数只有唯一的名称且不区分大小写，它决定了函数的功能和用途。Excel 函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。如 SUM（A1:A10,B1:B10）。另 外有一些函数比较特殊，它仅由函数名和成对的括号构成，因为这类函数没有参数，如 NOW 函数、RAND 函数。在 Excel 2007 中我们可以找到公式标签，看到其中有很多函数的类型，当我们进行函数输入的时候，可以从中进行查找。,3.3函数使用,3.3.1Sumif函数的

22、使用例如这张报销明细表，现在我们需要计算出所有越野车的报销路桥费用总和。如下图：,3.3.1Sumif函数的使用,此时我们就可以使用 SUMIF 函数进行计算。我们在单元格中输入：“SUMIF(C3:C51,”越野车“,E3:E51)”，回车我们即可得到所有越野车基路桥费的总和。在函数公式中，C3:C51是我们要计算的单元格区域，“越野车”是定义的条件，E3:E51 是用于求和计算的实际单元格区域。如下图：,3.3.2 Vlookup函数使用,日常工作中，Vlookup是一个非常有用的Excel函数，它的作用是：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

23、下面以Excel2007举例，一步一步图解如何使用Vlookup函数。例如有两个表，分别是：A表和B表,要求在B表的B26到B31单元格中自动填入A表中A列与B表A列相同值（完全相同，不分大小写）所对应的C列的值,Vlookup函数使用,3.3.2 Vlookup函数使用,把这4个自定义项全填上就OK,3.3.2 Vlookup函数使用,最后一个：通常都要求精确匹配，所以应填FALSE，也可直接键入数字0，意义是一样的。确定后，可以看到B表B26中有返回值最后，向下复制公式即可。,4.1单元格内容的合并,根据需要，有时想把 B 列与 C 列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘

24、贴”来完成 操作，但如果有几万行，就不能这样办了。解决办法是：在 C 行后插入一个空列（如果 D 列没有内容，就直接在 D 列操作），在1 中输入“=B1&C1”，D1 列的内容就是 B、C 两列的和了。选中 D1 单元格，用鼠标指向单元格右下角的小方块“”，当光标变成“”后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了 B 列和 C 列的合并。这时先不要忙着把 B 列和 C 列删除，先要把 D 列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉 B、C、D 列的数据。,4.2如何避免错误信息,在 Excel 中输入公式后，有时不能正确地计算出结果，并在单元格内

25、显示一个错误信息，这些错误的产生，有的是因公式本身产生的，有的不是。下面就介绍一下几种常见的错误信息，并提出避免出错的办法。1）错误值：含义：输入到单元格中的数据太长或单元格公式所产生的结果太大，使结果在单元格中显示不下。或是日期和时间格式的单元格做减法，出现了负值。解决办法：增加列的宽度，使结果能够完全显示。如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式，比如改为文本格式，结果为负的时间量。,4.2如何避免错误信息,2）错误值：DIV/0!含义：试图除以 0。这个错误的产生通常有下面几种情况：除数为 0、在公式中除数使用了空单元格或 是包含零值单元格的单元格引用。解决办法：修改

26、单元格引用，或者在用作除数的单元格中输入不为零的值。3）错误值：VALUE!含义：输入引用文本项的数学公式。如果使用了不正确的参数或运算符，或者当执行自动更正公式功 能时不能更正公式，都将产生错误信息VALUE!。,4.2如何避免错误信息,解决办法：这时应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数 值。例如，单元格 C4 中有一个数字或逻辑值，而单元格 D4 包含文本，则在计算公式=C4D4 时，系统不 能将文本转换为正确的数据类型，因而返回错误值VALUE!。4）错误值：REF!含义：删除了被公式引用的单元格范围。解决办法：恢复被引用的单元格范围，或是重新设定引

27、用范围。,4.2如何避免错误信息,5）错误值：N/A含义：无信息可用于所要执行的计算。在建立模型时，用户可以在单元格中输入#N/A，以表明正在等 待数据。任何引用含有#N/A 值的单元格都将返回#N/A。解决办法：在等待数据的单元格内填充上数据。6）错误值：NAME?含义：在公式中使用了 Excel 所不能识别的文本，比如可能是输错了名称，或是输入了一个已删除的名 称，如果没有将文字串括在双引号中，也会产生此错误值,如何避免错误信息,解决办法：如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在；如果是名称，函数名拼写错误应就改正过来；将文字串括在双引号中；确认公式中使用的所有区域

28、引用都使用了冒号（：）。例如：SUM（C1：C10）。注意将公式中的文本括在双引号中。7）错误值：NUM!含义：提供了无效的参数给工作表函数，或是公式的结果太大或太小而无法在工 作表中表示。解决办法：确认函数中使用的参数类型正确。如果是公式结果太大或太小，就要修改公式，使其结果在-110307 和 110307 之间。,4.2如何避免错误信息,9）错误值：NULL！含义：在公式中的两个范围之间插入一个空格以表示交叉点，但这两个范围没有公共单元格。比如输入：“=SUM(A1:A10 C1:C10)”，就会产生这种情况。解决办法：取消两个范围之间的空格。上式可改为“=SUM(A1:A10,C1:C

29、10)”,4.3批量删除空行,有时我们需要删除 Excel 工作薄中的空行，一般做法是将空行一一找出，然后删除。如果工作表的行数很多，这样做就非常不方便。我们可以利用“自动筛选”功能，把空行全部找到，然后一次性删除。做法：先在表中插入新的一个空行，然后按下 Ctrl+A 键，选择整个工作表，找到数据标签，点击筛选按钮。这时在每一列的顶部，都出现一个下拉列表框，在下拉列表框中选择“空白”，直到页面内已看不到数据为止。在所有数据都被选中的情况下，点击鼠标右键，选择“删除”命令。这时所有的空行都已被删去，再单击 数据标签中选取筛选项中的自动筛选命令，工作表中的数据就全恢复了。如果想只删除某一列中的空白单元格，而其它列的数据和空白单元格都不受影响，可以先复制此列，把它粘贴到空白工作表上，按上面的方法将空行全部删掉，然后再将此列复制，粘贴到原工作表的相应位 置上。,4.4.快捷填充空单元格为0,选中需要填充的区域，查找和选择定位条件空值确定。在第一个单元格中输入0，按Ctrl+Enter结束。,谢 谢！,