EXCEL2007高级技巧大全(有史以来最全).pdf

1 Excel 第第一一章章 数据的整理和分析数据的整理和分析 1.1 数据的排序数据的排序 Excel 提供了多种方法对工作表区域进行排序，用户可以根据需要按行或列、按升序或降序已经使用自定义排序命令。当用户按行进行排序时，数据列表中的列将被重新排列，但行保持不变，如果按列进行排序，行将被重新排列而列保持不变。没有经过排序的数据列表看上去杂乱无章，不利于我们对数据进行查找和分析，所以此时我们需要按照对数据表进行整理。我们可以将数据列表按“出生年月”进行排序。如下图：首先我们需要单击数据列表中的任意一个单元格，然后单击数据标签中的排序按钮，此时会出现排序的对话框。看到弹出的排序对话框中，在“主要关键字”下拉列表框中选择“出生年月”，在设置好主要关键字后，可以对排序依据进行设置，例如，数值、单元格颜色、字体颜色、单元格图标，在这样我们可以选择默认的数据作为排序依据。最后我们可以对数据排序次序进行设置，在次序下拉菜单中选择升序、降序或自定义排序。在这里我们选择升序，设置完成后单 2 击确定即可。如下图：我们除了可以对数据表进行单一列的排序之外，如果用户希望对列表中的数据按“性别”的升序来排序、性别相同的数据按“文化程度”升序排序、“性别”和“文化程度”都相同的记录按照“基本工资”从小到大的顺序来排序，此时我们就要对 3 个不同的列进行排序才能达到用户的要求。首先我们需要单击数据列表中的任意一个单元格，然后单击数据标签中的排序按钮，此时会出现排序的对话框。看到弹出的排序对话框中，在“主要关键字”下拉列表框中选择“性别”，在添加好主要关键字后，单击“添加条件”按钮，此时在对话框中显示“次要关键字”，同设置“主要关键字”方法相同，在下拉菜单中选择“文化程度”，然后再点击“添加条件”添加第三个排序条件，选择“基本工资”。在设置好多列排序的条件后，单击确定即可看到多列排序后的数据表。如下图：在 Excel 2007 中，排序条件最多可以支持 64 个关键字。1.2 数据的筛选数据的筛选 筛选数据列表的意思就是将不符合用户特定条件的行隐藏起来，这样可以更方便的让用3 户对数据进行查看。Excel 提供了两种筛选数据列表的命令。自动筛选：适用于简单的筛选条件 高级筛选：适用于复杂的筛选条件 想要使用 Excel 的自动筛选功能，首先要单击数据列表中的任意单元格，然后选择数据标签，单击筛选按钮即可。单击数据列表中的任何一列标题行的下拉箭头，选择希望显示的特定行的信息，Excel会自动筛选出包含这个特定行信息的全部数据。如下图：在数据表格中，如果单元格填充了颜色，使用 Excel 2007 我们还可以按照颜色进行筛选。如果条件比较多，可以使用“高级筛选”来进行。使用高级筛选功能可以一次把我们想要看到的数据都找出来。例如数据表中我们想要把性别为男、文化程度为研究生、工资大于 2000 的人显示出来。我们可以这么来做：先设置一个条件区域，第一行输入排序的字段名称，在第二行中输入条件，建立一个条件区域。如下图：4 然后选中数据区域中的一个单元格，单击数据标签中的高级筛选命令。Excel 自动选择好了筛选的区域，我们单击这个条件区域框中的拾取按钮，选中我们刚才设置的条件区域，单击拾取框中的按钮返回高级筛选对话框，单击确定按钮，现在表中就是我们希望看到的结果了。设置高级筛选条件设置高级筛选条件 高级筛选可以设置行与行之间的“或”关系条件，也可以对一个特定的列指定三个以上的条件，还可以指定计算条件，这些都是它比自动筛选优越的地方。高级筛选的条件区域应该至少有两行，第一行用来放置列标题，下面的行则放置筛选条件，需要注意的是，这里的列标题一定要与数据清单中的列标题完全一样才行。在条件区域的筛选条件的设置中，同一行上的条件认为是“与”条件，而不同行上的条件认为是“或”条件。1.3 在筛选时使用通配符在筛选时使用通配符 在设置自动筛选的自定义条件时，可以使用通配符，其中问号（？）代表任意单个字符，星号（*）代表任意一组字符。5 1.4 数据的分类汇总与分级显示数据的分类汇总与分级显示 分类汇总是 Excel 中最常用的功能之一，它能够快速地以某一个字段为分类项，对数据列表中的数值字段进行各种统计计算，如求和、计数、平均值、最大值、最小值、乘积等。比如这张各部门工资统计表，我们希望可以得出数据表中每个部门的员工实发工资之和。现在我们用分类汇总来做：首先单击部门单元格，单击数据标签中的升序按钮，把数据表按照“部门”进行排序，然后在数据标签中，单击分类汇总按钮，在这里的分类字段的下拉列表框中选择分类字段为“部门”，选择汇总方式为“求和”，汇总项选择一个“实发工资”，单击“确定”按钮。如下图：6 当我们点击确定后，我们就可以看到已经计算好各部门实发工资之和了。如下图：在分类汇总中我们的数据是分级显示的，现在工作表的左上角出现了这样的一个区域，我们单击这个 1，在表中就只有这个总计项出现了。7 单击这个 2，出现的就只有这些汇总的部分，这样我们可以清楚地看到各部门的汇总。单击 3，可以显示所有的内容。复制汇总结果复制汇总结果 当我们使用分类汇总后，往往希望将汇总结果复制到一个新的数据表中，如下图。但当我们直接进行复制后，无法只复制汇总结果，而复制的是所有数据。此时我们就需要使用“Alt+;”组合键选取当前屏幕中显示的内容，然后在进行复制粘贴。1.5 条件格式条件格式 使用 Excel 中的条件格式功能，可以预置一种单元格格式，并在指定的某种条件被满足时自动应用于目标单元格。可以预置的单元格格式包括边框、底纹、字体颜色等。此功能可以根据用户的要求，快速对特定单元格进行必要的标识，以起到突出显示的作用。8 例如下图中的产品销售标，我们需要快速找出所有与“防水键盘”相关的销售数据。首先我们全选所有数据，然后在开始标签中找到条件格式按钮，单击突出显示单元格规则，在打开的选项中单击等于按钮。9 在弹出的对话框中输入我们想要查找的“防水键盘”，然后设置单元格显示样式，例如让单元格以“浅红填充色深红色文本”显示，设置完毕后，点击确定按钮。在点击确定后，我们可以数据表中已经显示出和防水键盘相关的所有信息。这样我们就可以快速查看我们所关心的信息了。如下图：在 Excel 2007 中，使用条件格式不仅可以快速查找相关数据，还可以以数据条、色阶、图标的方式显示数据，让用户可以对数据一目了然。首先我们全选所有数据，然后在开始标签中找到条件格式按钮，单击数据条，在打开的选项中选择任意颜色。当我们选择颜色时，即使不点击，也可以即时的看到数据变化。通过条件格式，我们就可以清楚的看到那条数据销售额最高，那条数据销售额最低。如下图：10 1.6 数据透视表数据透视表 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。例如，计算平均数、标准差，建立列联表、计算百分比、建立新的数据子集等。建好数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。数据透视表可以从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策所用。我们来看下面这个数据表，在这个数据表中显示了每个财季产品销售的明细数据。11 现在我们想把这里的统计每个财季的利润，可以结合条件格式判断最低利润出自哪个财季。首先打开插入标签，单击数据透视表按钮，打开创建数据透视表对话框。然后选择透视表的数据来源的区域，Excel 已经自动选取了范围，这里它的选取是正确的，我们不做什么改动，接下来我们选择透视表放置的位置，选择新建工作表项，单击确定按钮。现在就出现了我们建立的数据透视表。在工作表的透视表的各个部分都有提示，同时界面中出现了一个数据透视表字段列表，里面列出了所有我们可以使用的字段，我们的目的是查看统计每个财季的利润，因此我们在要添加到报表的字段中选择财季，然后拖动利润和数量字段到数值区域，此时，我们就可以看到每个财季的利润和销售数量显示在数据透视表中了。同样的，在数据透视表中可以直接看到利润和销售数量的汇总数目。现在我们就可以结合条件格式功能，查看出那个财季利润最低。如下图：12 1.7 数据透视图的使用数据透视图的使用 我们还可以根据数据透视表直接生成图表：点击选项标签，点击数据透视图按钮，在弹出的对话框中选择图表的样式后，单击确定就可以直接创建出数据透视图。不难看出这个图表跟我们平时使用的图表基本上一致，所不同的只是这里多了几个下拉箭头，单击“财季”的下拉箭头，这实际上是透视表中字段，选择“Q1”，可以看到图表中的数据也发生了变化，其它有很多在透视表中使用的方法也可以在这个图表中使用，把图表的格式设置一下，一个漂亮的报告图就完成了。13 1.8 获取外部数据获取外部数据 在 Excel 中，我们可以获取外部的数据到 Excel 中，这样我们就可以利用 Excel 的功能对数据进行整理和分析。以一个例子来看：这里有这样的一个文本文件，我们把其中的数据引入到 Excel 中来。打开 Excel，打开数据标签，我们可以看到很多种文件类型都可以倒入到 Excel 表格中。我们点击“自文本”按钮，然后选择我们文本所在位置，选择完毕后，此时会弹出“文本导入向导”，这里的原始数据类型就选择“分隔符号”一项，在这个对话框的下面有一个预览框，从这里可以看出来现在的第一行没有什么用处，因此我们将这里的“导入起始行”输入框中的数值改成 2，单击“下一步”。14 现在我们要选择文本文件的数据字段分隔符，根据文件的特点，我们选择为“Tab 键”，单击“下一步”按钮。现在要选择导入数据的默认格式，现在的默认格式是“常规”，我们这里不做什么改动，单击“完成”按钮，此时出现“导入数据”对话框，我们可以选择将数据导入到现有工作表或新建工作表。把文本文件中的数据全部引入进来以后，把它们稍微调整一下，我们就可以使用这些数据了。15 1.9 冻结和拆分窗口冻结和拆分窗口 拆分窗口：我们可以使用视图标签中的拆分按钮来拆分窗口。而如果只需要垂直拆分，可以直接拖动垂直滚动条到最顶端，此时横项拆分线就会消失。撤消窗口的拆分：同样在视图标签下，再次点击拆分按钮即可撤销窗口的拆分。冻结窗格：我们可以使用冻结窗格来更好的查看数据表头。首先选中一个单元格，选择视图标签，点击冻结窗，在下拉菜单中我们可以选择冻结第一行或第一列，当然我们也可以自行选择想要冻结的位置。如下图：如果我们想要冻结第一行以及 A、B 两列，此时我们需要将光标放在第一行的下面，并且 A、B 两列的右侧，即 C2 单元格，此时点击冻结拆分窗格即可冻结第一行以及 A、B 两列。16 1.10 表对象表对象 在 Excel 2007 中，我们可以直接将数据表格直接转换为表对象，这样我们就可以直接对数据表格进行排序、筛选、调整格式等操作。首先我们找到插入标签，点击表按钮，此时我们就将数据表格转换为了表对象。我们可以看到，当我们把数据转换为表对象后，他会自动出现筛选功能。图下图：并且在设计标签中，我们可以即时调整表的样式等。如下图：1.11 插入插入 SmartArt 在 Excel 2007 中，有了一种新的对象可以更好的丰富 Excel 数据表，那就是 SmartArt。我们可以通过插入标签，点击 SnartArt，在弹出得对话框中选择想要插入 SmartArt 类型以及具体样式，如下图：17 我们选择基本列表后，点击确定，这样一个 SmaerArt 对象就直接添加到我们的数据表当中，此时我们就可以输入文字、数据并且调整样式了。很快，一个漂亮的 SmartArt 就已经出现在我们的 Excel 当中了。如下图：1.12 插入其他的对象插入其他的对象 插入一个已存在的对象：在插入标签中，点击对象按钮，在对象对话框中（如下图）选择“Microsoft Office Visio 绘图”，点击确定，此时就会在 Excel 中启动 Visio 绘图功能，当我们绘制好图形后，就可以将 Visio 图直接插入到 Excel 中。18 1.13 选择性粘贴选择性粘贴 Excel 为我们提供了一些自动功能。例如选择性粘贴。这里的选择性粘贴是指把剪贴板中的内容按照一定的规则粘贴到工作表中，而不是像前面那样简单地拷贝。就拿这个数据表来举例。如下图：这里的“销售利润”一栏是我们使用公式计算得到的，选择这一栏，复制到 Sheet2 中，可以看到数值并没有跟着复制过来；这时就可以使用选择性粘贴了：单击鼠标右键，单击“选择性粘贴”命令，打开“选择性粘贴”对话框（如下图），在“粘贴”一栏中选择“数值”，单击“确定”按钮，数值就可以粘贴过来了。这种情况不仅是在几个工作表之间复制时会发生，在同一个工作表中进行复制时也会遇到，到时可要注意。19 选择性粘贴还有一个很常用的功能就是转置功能。简单地理解就是把一个横排的表变成竖排的或把一个竖排的表变成横排的：选择这个表格，复制一下，切换到另一个工作表中，打开“选择性粘贴”对话框，选中“转置”前的复选框，单击“确定”按钮，可以看到行和列的位置相互转换了过来。另外一些简单的计算也可以用选择性粘贴来完成：选中这些单元格，复制一下，然后打开“选择性粘贴”对话框，在“运算”一栏选择“加”，单击“确定”按钮，单元格的数值就是原来的两倍了。此外你还可以粘贴全部格式或部分格式，或只粘贴公式等等。1.14 自动更正自动更正 现在我们在单元格中输入（c）然后按一下回车，可以看到输入的字符变了。这是 Excel的自动更正功能，我们再输入(r)，回车；同样可以看到输入的字符又变了。其实这些都是可以设置的：点击 office 按钮，单击选项，在校对中找到自动更正选项按钮，单击会打开自动更正对话框（如下图），这里列出了所有自动更正的选择，在“键入时自动替换”列表中的头两个就是我们刚才看到的，输入(c)则替换为(c)，输入(r)则替换为(r)。20 我们还可以把自己容易犯错的词做一个自动更正的词条，以后再输入起来就很方便了：在“替换”输入框中写入“制做”，在“替换为”输入框中填上“制作”，单击“添加”按钮将其添加到列表中，单击“确定”按钮；在单元格中输入“制做”，回车，可以看到“制做”就被替换成了“制作”。如果你不想使用哪个自动更正的选项了，就在自动更正对话框中把这个选项去掉就行了。1.15 宏的使用宏的使用 在 Excel 中有一种重要的自动功能，那就是“宏”。我们每次听到“宏”总是和病毒在一块儿，一般自己做的宏是不会对自己有害的。合理地使用宏可以大幅度地提高工作效率，而且有些操作不使用宏的话会很麻烦。最简单的宏可以通过录制来做。在 Excel 2007 中要想使用宏功能，需要添加开发工具标签。首先点击 Office 按钮，单击 Excel 选项按钮，在常用选项中，将“在功能区显示开发工具选项卡”勾选，点击确定返回 Excel 界面后，我们就可以看到在标签中多了一个“开发工具”。21 现在比如我们写一个宏，让它删除 A3 单元格的内容：点击开发工具标签，单击录制宏按钮，在弹出“录制新宏”对话框（如下图），在“宏名”输入框中输入宏的名字，单击“确定”按钮。此时我们就可以进行宏的录制了，在开发工具标签中最左侧可以看到目前的状态是“停止录制”，表示当我们在创建一个宏后，就已经开始在录制宏了；单击 A3 单元格，按一下Delete 键，这个宏的操作就算完成了，单击“停止录制”。现在在 A3 单元格中随便填上数值，选择其它的单元格，找到开发工具标签，单击宏按钮，打开“宏”对话框（如下图），选择我们刚才录制的宏，单击“执行”按钮，A3 单元格的内容就没有了。现在我们打开一个带有宏的工作簿，Excel 会提醒我们打开的文件中带有宏，如果你不能确定宏是否带有恶意的成分就选择“禁用宏”，否则可以选择“启用宏”；在禁用之后即使宏中有恶意成份也不会对你的机器起作用了。22 1.16 超链接的使用超链接的使用 在 Excel 中我们可以使用超级链接连接其它的文件或 Web 地址，也可以粘贴一个电子邮件地址，在用鼠标单击这个地址时自动启动相应的电子邮件程序，发送电子邮件等等。选中要插入超级链接的单元格，找到插入标签，单击超链接按钮，打开“插入超级链接”对话框（如下图），这里的“要显示的文字”输入框中显示的就是单元格中的文字；先来插入一个经常去的 Web 页面：注意左边这个“原有文件或网页”按钮处于“按下”的状态，在列表中显示出了最近浏览过的页面，在上面的输入框中我们输入 ，单击“确定”按钮将这个超级链接设置好；现在将鼠标移动到单元格上，鼠标变成了手的形状，单击，系统就会自动启动浏览器对我们输入的页面进行浏览了。不过这只是超级链接作用的一个小方面，更多的时候使用超级链接是为了查阅工作表的方便；在 A1 单元格上单击右键，从弹出的菜单中单击“超链接”按钮，就可以打开“插入超链接”对话框（如下图），单击左边“链接到”列表中的“本文档中的位置”按钮，对话框的样子就发生了改变，单击“或在这篇文档中选择位置”：列表中“单元格引用”前面的加号，选择“Sheet2”，在“请键入单元格引用”输入框中就显示出了“A1”，这正是我们想要链接的单元格，单击“确定”按钮设置好链接，现在单击这个单元格，Excel 就自动跳转到了 Sheet2 的 A1 单元格上。23 我们还可以像在网页上那样插入一个邮件地址：选择单元格，打开单击右键，从弹出的菜单中选择“超链接”按钮，打开“插入超链接”对话框，单击左边“链接到”列表中的“电子邮件地址”按钮，然后在“电子邮件地址”输入框中输入要插入的电子邮件地址，单击“确定”按钮，就将电子邮件地址链接到了单元格中；单击单元格，就会启动默认的电子邮件程序给刚才插入的电子邮件地址发送信件了。我们还可以链接到其它的文档中，打开“插入超链接”对话框，选择“链接到”列表中的“原有文件或网页”按钮，然后单击“当前文件夹”按钮，选择要链接的文件，单击“确定”按钮设置好链接，单击刚才设置的单元格，就可以转而查阅链接的文档了。1.17 数据的有效性数据的有效性 使用 Excel 的数据有效性功能，可以对输入单元格的数据进行必要的限制，并根据用户的设置，禁止数据输入或让用户选择是否继续输入该数据。例如在一个员工报销单中，用户只能在日期中输入 2008 年度、报销类型中只能进行选择规定的类型、发票号码只能输入 10 位数等。我们一起来看一下例子：在下图中是一个员工报销单，我们需要对表中的数据进行有效性设置。首先想要设置的单元格，比如我们想要对“日期”列进行设置，点击“日期”列中任意单元格，点击数据标签中的有效性按钮，在“设置”选项卡中找到“允许”下拉菜单，单击“日期”，在“数据”中选择“介于”，在“开始日期”和“结束日期”中分别输入“2008-1-1”和“2008-12-31”。如下图：然后选择“输入信息”选项卡，在“标题”和“输入信息”中分别输入“请输入日期”和“输入日期范围：2008-1-1 至 2008-12-31”。最后选择“出错警告”选项卡，同样在“标题”和“错误信息”中输入“输入错误”和“输入日期范围：2008-1-1 至 2008-12-31”。在输入完信息后，点击确定。此时我们返回数据表，点击单元格就可以看到我们所设置的。24 除了可以对单元格进行日期的设置外，我们还可以对单元格进行序列的设置，让用户在单元格中对所有信息进行挑选。同样在数据标签中点击数据有效性按钮，在“设置”选项卡“允许”中找到“序列”，在“来源”中输入所选信息，例如“住宿,交通费,其他”，同样设置好“输入信息”和“出错警告”点击确定即可。点击单元格，我们会看到下拉箭头，点击之后，我们就可以对单元格中的数据进行选择了。如下图：25 Excel 第二章第二章 Excel 中的图表中的图表 2.1 图表的建立图表的建立 图表是图形化的数据，它由点、线、面等图形与数据文件按特定的方式而组合而成。一般情况下。用户使用 Excel 工作薄内的数据制作图表，生成的图表也存放在工作薄中。图表是 Excel 的中要组成部分，具有直观形象、双向联动、二维坐标等特点。我们拿一个例子来讲吧：这是一个市场调查表，显示了几种品牌的饮料在各个季度的销量百分比。我们来做一个表示第一季度的几种商品所占比例的饼图，首先选择数据区域，然后选择插入选项卡，单击饼图按钮，再打开的下拉菜单中选择饼图样式，此时我们就可以看到我们已经创建了一个饼图。如下图：单击创建好的图表，此时点击设计标签，在这里我们可以对图表的布局和样式进行选择，或者修改选择的数据等。通过 Excel 2007 新的样式，我们可以简单的设计出漂亮的图表。如下图：26 2.2 图表的修改图表的修改 我们经常可以看到那种有一部分同其它的部分分离的饼图，这种图的做法是：单击这个圆饼，在饼的周围出现了一些句柄，再单击其中的某一色块，句柄到了该色块的周围，这时向外拖动此色块，就可以把这个色块拖动出来了；同样的方法可以把其他各个部分分离出来。或者我们在插入标签中直接选择饼图下拉菜单，选择分离效果即可。如下图：把它们合起来的方法是：先单击图表的空白区域，取消对圆饼的选取，单击选中分离的一部分，按下左键向里拖动鼠标，就可以把这个圆饼合并到一起了。我们还经常可以见到这样的饼图：把占总量比较少的部分单独拿出来做了一个小饼以便看清楚，做这种图的方法：在插入标签中直接选择饼图下拉菜单，选择相应效果即可。如下图：27 2.3 趋势线的使用趋势线的使用 趋势线可以简单地理解成一个品牌在几个季度中市场占有率的变化曲线，使用它我们可以很直观地看出一个牌子的产品的市场占有率的变化，还可以通过这个趋势线来预测下一步的市场变化情况：创建好图表后，选择布局标签，点击趋势线下拉菜单，此时我们就可以看到趋势线类型。如下图：选择指数趋势线后，我们就可以直接在图表中添加相应趋势线。现在图表中就多了一条我们刚刚添加的第一季度的趋势线，从这条线我们可以清楚地看出第一季度的变化趋势是缓慢下降的。这样就比直接看这个柱形图清楚多了。如果想看第二季度的变化趋势，同样打开添加趋势线下拉菜单，在下面的选择数据系列列表框中选择第二季度就可以了。还可以用这个趋势线预测下一步的市场走势：点击趋势线下拉菜单中的“其它趋势线选项”，打开“设置趋势线格式”对话框，在“趋势预测”一栏中将“前推”输入框中的数字改为“1”，单击“确定”按钮。28 现在，就看到趋势变化了。29 2.4 添加系列添加系列 如果我们得到了第四季度的统计数据，需要把它加入到这个表中，我们希望在加入到表格中后在图表中也看到第四季度的数据。方法如下：首先把数据添加进去。在已经创建好的图表中右键单击，此时我们选择单击“选择数据”选项。如下图：在打开的“选择数据源”对话框中，单击“图表数据区域”输入框中的拾取按钮，选择已经添加好的数据区域。如下图：返回“选择源数据”对话框，此时我们就可以在“图例项（系列）”中看到已经添加好的第四季度数据，单击“确定”按钮就可以完成这个序列的加入了。30 讲起来很繁琐，其实这些图表的插入都是很简单的，都是那么几步，只是不同的图表要用在不同的地方而已，如果需要，我们还可以随时把完成的工作转换成其它的图表形式表现出来，自己多练习练习，就会找到规律合计桥、和技巧。2.5 常见的图表常见的图表 通常我们使用柱形图和条形图来表示产品在一段时间内的生产和销售情况的变化或数量的比较，如我们上面的季度产品份额的柱状图就是显示各个品牌的市场份额的比较和变化。如果要体现的是一个整体中每一部分所占的比例时，我们通常使用“饼图”，如我们做的各种饮料市场份额的饼图。此外比较常用的就是折线图和散点图了，折线图通常也是用来表示一段时间内某种数值的变化，常见的如股票的价格的折线图等。散点图主要用在科学计算中。例如：我们有正弦和余弦曲线的数据，我们可以使用这些数据来绘制出正弦和余弦曲线：选择数据区域，然后在插入标签中选择散点图按钮，就生成了一个函数曲线图；改变一下它的样式，一个漂亮的正余弦函数曲线就做出来了。31 Excel 第第三三章章 公式和函数公式和函数 3.1 绝对地址与相对地址绝对地址与相对地址 随着公式的位置变化，所引用单元格位置也是在变化的是相对引用；而随着公式位置的变化所引用单元格位置不变化的就是绝对引用。下面讲一下“C4”、“$C4”、“C$4”和“$C$4”之间的区别。在一个工作表中，在 C4、C5 中的数据分别是 60、50。如果在 D4 单元格中输入“=C4”，那么将 D4 向下拖动到 D5 时，D5 中的内容就变成了 50，里面的公式是“=C5”，将 D4 向右拖动到 E4，E4 中的内容是 60，里面的公式变成了“=D4”。现在在 D4 单元格中输入“=$C4”，将 D4 向右拖动到 E4，E4 中的公式还是“=$C4”，而向下拖动到 D5 时，D5 中的公式就成了“=$C5”。如果在D4单元格中输入“=C$4”，那么将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将 D4 向下拖动到 D5 时，D5 中的公式还是“=C$4”。如果在 D4 单元格中输入“=$C$4”，那么不论你将 D4 向哪个方向拖动，自动填充的公式都是“=$C$4”。原来谁前面带上了“$”号，在进行拖动时谁就不变。如果都带上了“$”，在拖动时两个位置都不能变。32 3.2 公式创建公式创建 公式是由用户自定设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果，因此 Excel 的公式必须（且只能）返回值。下面的表达式就是一个简单的公式实例。=（C2+D3）*5 从公式的结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，等号是不可或缺的。但在实际应用中，公式还可以使用数组、Excel 函数或名称（命名公式）来进行运算。如果在某个区域使用相同的计算方法，用户不必逐个编辑函数公式，这是因为公式具有可复制性。如果希望在连续的区域中使用相同算法的公式，可以通过“双击”或“拖动”单元格右下角的填充柄进行公式的复制。如果公式所在单元格区域并不连续，还可以借助“复制”和“粘贴”功能来实现公式的复制。下面我们就通过具体实例来了解公式。数据表中展示了某公司 4 月所有产品的销售情况，现在我们就可以通过使用公式计算总销售额。在 E21 单元格中输入“=D21*C21”回车即可得到总销售额金额。然后使用填充的方式将其他产品计算出来。如下图：这是我们在平时使用到最简单的公式应用。如果我们想要计算数据表中的利润金额。我们通常的办法是在 H21 单元格中输入公式：“=G21*F21*0.2”。这样我们就可以计算出利润 33 金额。如下图。但如果我们在计算利润时忘记了利润率的话，计算也就无法实现。但我们可以通过定义单元格名称来帮我们实现。将利润率写在单元格中，选中此单元格，此时我们可以看到在名称框中显示的行号列表，我们可以自己定义名称，在地址栏中输入“利润率”，然后回车。如下图：定义好名称后，我们在输入公式的时候就可以直接输入中文名称，这样，也可以进行计算了。例如在 H21 单元格中输入：“=G21*F21*利润率”，此时我们可以看到已经计算出来的结果。如下图：在这里我们还可以使用快捷键在连续区域中填充公式：当选择 H21:H25 区域后，再按下“Ctrl+D”组合键可以实现公式的快速复制。3.3 函数使用函数使用 Excel 的工作表函数通常被简称为 Excel 函数，它是由 Excel 内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此，Excel 函数也常被人们称为“特殊公式”。与公式一样，Excel 函数的最终返回结果为值。Excel 函数只有唯一的名称且不区分大小写，它决定了函数的功能和用途。34 Excel 函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。如 SUM（A1:A10,B1:B10）。另外有一些函数比较特殊，它仅由函数名和成对的括号构成，因为这类函数没有参数，如 NOW 函数、RAND 函数。在 Excel 2007 中我们可以找到公式标签，看到其中有很多函数的类型，当我们进行函数输入的时候，可以从中进行查找。下面我们就通过具体的实例来看一下函数的应用。例如这张工资表，现在我们需要计算出所有办公室人员的基本工资总和。如下图：此时我们就可以使用 SUMIF 函数进行计算。我们在单元格中输入：“=SUMIF(A2:A17,”办公室”,C2:C17)”，回车我们即可得到所有办公室基本工资的总和。在函数公式中，A2:A17 是我们要计算的单元格区域，“办公室”是定义的条件，C2:C17 是用于求和计算的实际单元格区域。如下图：35 97 Excel 第第四四章章 高级技巧高级技巧 4.1 编辑技巧编辑技巧 1 编辑技巧 （1）分数的输入 如果直接输入“1/5”，系统会将其变为“1 月 5 日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。（2）序列“001”的输入 如果直接输入“001”，系统会自动判断 001 为数据 1，解决办法是：首先输入“”（西文单引号），然后输入“001”。（3）日期的输入 如果要输入“4 月 5 日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。（4）填充条纹 如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在单元格内填入“*”或“”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，右键单击“单元格格式”按钮，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮，如下图：（5）多张工作表中输入相同的内容 几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住 Ctrl 键，再单击窗口左下角的 Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的 Ctrl 键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。（6）不连续单元格填充同一数据 选中一个单元格，按住 Ctrl 键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住 Ctrl 键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。（7）利用 Ctrl*选取文本 如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下 Ctrl*键可选定整个表格。Ctrl*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。（8）快速清除单元格的内容 如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按 Delete 键的方法了。要彻底清除单元格,可用以下方法：选定想要清除的单元格或单元格范围；在开始标签中单击清除按钮，在下拉菜单中选择“全部清除”，这些单元格就恢复了本来面目。4.2 单元格内容的合并单元格内容的合并 根据需要，有时想把 B 列与 C 列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴”来完成操作，但如果有几万行，就不能这样办了。解决办法是：在 C 行后插入一个空列（如果 D 列没有内容，就直接在 D 列操作），在1 中输入“=B1&C1”，D1 列的内容就是 B、C 两列的和了。选中 D1 单元格，用鼠标指向单元格右下角的小方块“”，当光标变成“”后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了 B 列和 C 列的合并。这时先不要忙着把 B 列和 C 列删除，先要把 D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉 B、C、D 列的数据。下面是一个“&”实际应用的例子。用 AutoCAD 绘图时，有人喜欢在 EXCEL 中存储坐标点，在绘制曲线时调用这些参数。存放数据格式为“x，y”的形式，首先在 Excel 中输 入坐标值，将 x 坐标值放入 A 列，y 坐标值放入到 B 列，然后利用“&”将 A 列和 B 列合并成 C 列，在 C1 中输入：=A1&，&B1，此时 C1 中的数据形式就符合要求了，再用鼠标向下拖动 C1 单元格，完成对 A 列和 B 列的所有内容的合并。合并不同单元格的内容，还有一种方法是利用 CONCATENATE 函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）”。比如，假设在某一河流生态调查工作表中，B2 包含“物种”、B3 包含“河鳟鱼”，B7 包含总数 45，那么：输入“=CONCATENATE（本次河流生态调查结果：，B2，B3，为，B7，条/公里。）”计算结果为：本次河流生态调查结果：河鳟鱼物种为 45 条/公里。4.3 条件显示条件显示 我们知道，利用 If 函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入 60 以下的分数时，能显示为“不及格”；输入 60 以上的分数时，显示为“及格”。这样的效果，利用 IF 函数可以很方便地实现。假设成绩在 A2 单元格中，判断结果在 A3 单元格中。那么在 A3 单元格中输入公式：=if（A260，“不及格”，“及格”）同时，在 IF 函数中还可以嵌套 IF 函数或其它函数。例如，如果输入：=if（A260，“不及格”，if（A2=90，“及格”，“优秀）就把成绩分成了三个等级。如果输入=if（A260，“差，if（A2=70，“中”，if（A20，SUM（A1：A5），0）此式就利用了嵌套函数，意思是，当 A1 至 A5 的和大于 0 时，返回这个值，如果小于 0，那么就返回 0。还有一点要提醒你注意：以上的符号均为半角，而且 IF 与括号之间也不能有空格。4.4 自定义格式自定义格式 Excel 中预设了很多有用的数据格式，基本能够满足使用的要求，但对一些特殊的要求，如强调显示某些重要数据或信息、设置显示条件等，就要使用自定义格式功能来完成。Excel的自定义格式使用下面的通用模型：正数格式，负数格式，零格式，文本格式，在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。（1）使用颜色 要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel 识别的颜色名为：黑色、红色、白色、蓝色、绿色、青色和洋红。Excel 也识别按颜色 X指定的颜色，其中 X 是 1 至 56 之间的数字，代表56 种颜色。如下图：（2）添加描述文本 要在输入数字数据之后自动添加文本，使用自定义格式为：文本内容；要在输入数字数据之前自动添加文本，使用自定义格式为：文本内容。符号的位置决定了 Excel 输入的数字数据相对于添加文本的位置。（3）创建条件格式 可以使用六种逻辑符号来设计一个条件格式：（大于）、=（大于等于）、（小于）、=（小于等于）、=（等于）、（不等于），如果你觉得这些符号不好记，就干脆使用“”或“=”号来表示。由于自定义格式中最多只有 3 个数字段，Excel 规定最多只能在前两个数字段中包括 2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第 3 段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。自定义格式的通用模型相当于下式：；0正数格式；50000$#,#0.00_);红色999999#,#0,_m吨;999#,_k_m千克;#_k克”可以看到，使用条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工作表的可读性和效率。另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式；0只显示负数和零，输入的正数则不显示；格式“；”则隐藏所有的输入