excel2007应用技巧

《excel2007应用技巧》由会员分享，可在线阅读，更多相关《excel2007应用技巧（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 的自动筛选功能，首先要单击数据列表中的任意单元格，然后选择数据标签，单击筛

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

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

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

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

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

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

12、个数据表中显示了每个财季产品销售的明细数据。,1.5数据透视表,现在我们想把这里的统计每个财季的利润，可以结合条件格式判断最低利润出自哪个财季。首先打开插入 标签，单击数据透视表按钮，打开创建数据透视表对话框。然后选择透视表的数据来源的区域，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 的中要组成部分，具有直观形象、双向联动、二维坐标等特点。我们拿一个例子来讲吧：这是一个市场调查表，显示了几种品牌的 饮料在各个季度的销量百分比。 我们来做一个表示第一季度的几种商品所占比例的饼图，首先选择数据区域，然后选择插入选项卡，单击 饼图按钮，再打开的下拉菜单中选择饼图样式，此时我们就可以看到我们已经创建了一个饼图。如下图：,2.2图表的修改,我们经常可以看到那种有一部分同其它的部分分离的饼图，这种图的做法是：单击这个圆饼，在饼的周围 出现了一些句柄，再单击其中的某一色块，句柄到了该色块的周围，这时向外拖动此色块，就可以把这个 色块拖动出来了；同样的方法可以把其他各个部分分离出来。 或者我们在插入标签中直接选择饼