精通Excel 2007数据分析与业务建模word版+CD

42 精通Excel 2007数据分析与业务建模 41 第6章 日期和日期函数 第0章 Excel 2007中的新功能 第0章 Excel 2007中的新功能 · 什么是功能区？ · 什么是快速访问工具栏？ · 什么是浮动工具栏？ · 如何简便地找到有用的组合键？ · 在Excel 2007中能创建比 Excel 2003中更大的工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 吗？ · 什么是公式自动完成？ · 什么是Office 按钮？ · 什么是主题？ · 什么是SmartArt? · 如何改变一张工作表的显示比例？ · 如何同时显示一个工作簿的多个副本？ · 在打印电子表格前有什么新的查看方式？ · 如何让没有安装Excel 2007的用户使用Excel 2007文件？ · 其他变化？ 微软的Office Excel 2007是全新的，并且有许多的改进。新的界面和新的功能最初会使有经验的用户迷惑，但在熟悉新的界面后，很少有人再想回头使用老版本的Excel！对于Excel初学者，新的界面会使你更容易地找到Excel 2007的更复杂、更有用的功能。本章将简要讲述Excel 2007的一些改变。本书主要聚焦在应用Excel进行业务建模和数据分析上，所以不会花费很多时间介绍像SmartArt和主题这样的图像方面的功能。如果想得到更多关于这些功能的信息，请参考Mark Dodge和Craig Stinson所著的《精通Excel 2007中文版》(清华大学出版社，2008)。 什么是功能区? Excel开发人员问Excel用户在使用Excel时遇到的典型困难是什么时，发现人们想要但无法找到的90%以上的功能已经在Excel 2003中实现了。问题是许多很好的功能(如数据表格、合并计算和规划求解)很难找到。为使读者更容易地找到和学习Excel及其他Office产品的所有完美功能，Office开发组制作了功能区(如图0.1所示)。 图0.1 功能区中的“开始”选项卡 功能区中的选项取决于选择哪个选项卡。 · 开始。这个选项卡包含大部分的工作表编辑和格式命令(如字体和单元格对齐方式)及剪贴板命令(如“粘贴”和“选择性粘贴”)。 · 页面布局。这个选项卡包含控制工作表打印和工作表外观(如是否显示网格线)的一些命令。 · 插入。使用这个选项卡中的命令可以在工作表中插入想放入的内容，如剪贴画、图表和数据透视表。 · 数据。这个选项卡中的命令是关于数据分析功能的，如排序和筛选。 · 公式。这个选项卡中的命令用于命名单元格区域、体验Excel的奇妙功能、进行数学计算或审核工作表的结构。 · 审阅。使用这个选项卡中的命令来管理工作表批注、保护工作表、进行拼写检查、跟踪工作表改变或完成相关任务。 · 视图。这个选项卡用于控制工作表的显示方式。可以冻结窗格，排列和重排窗口，控制页面布局(后文有更详细的介绍)。 · 开发工具。这个选项卡中的命令主要用于开发Excel宏。你也可以插入用户窗体和控件(详见第25章)。如果这个选项卡不可见，单击Office按钮(如图0.2所示)，单击“Excel选项”按钮，然后在“常用”页中选中“在功能区显示“开发工具”选项卡”复选框。 图0.2 Office按钮 · 加载项。Excel的一些加载项(如规划求解或分析工具包)可以在这个选项卡中找到。这个选项卡只有在至少安装了一个加载项时才可见。 这些选项卡和功能区使Excel提供的功能一目了然。如果觉得功能区占了太多空间，可以隐藏(或重新显示)它：按下Ctrl+F1；双击任何选项卡；或右击功能区，然后选择“功能区最小化”选项。 什么是快速访问工具栏？ 可能有一些命令使用频率比其他的更高，而在选项卡之间切换来查找命令会使操作很慢。Excel 2007提供了快速访问工具栏，可以把常用的命令集中在此。快速访问工具栏的默认位置在功能区上面、屏幕的左上角，如图0.3所示。 图0.3 快速访问工具栏 要把命令添加到快速访问工具栏，只需右击一个命令，选择“添加到快速访问工具栏”选项。还可以使用以下方式添加：单击Office按钮，单击“Excel 选项”按钮，打开“自定义”页面(如图0.4所示)。选择要添加的命令，然后单击“添加”和“确定”按钮。如要从快速访问工具栏中移除任何命令，只需右击该命令，选择“从快速访问工具栏删除”选项。可以把快速访问工具栏移动到功能区的下面：右击工具栏，选择“在功能区下方显示快速访问工具栏”选项。 图0.4 可以在快速访问工具栏中添加、移除和排列命令 什么是浮动工具栏？ 当选择一个单元格的内容、右击一个单元格或一个单元格区域时，浮动工具栏就出现了，如图0.5所示。浮动工具栏提供访问最常用的格式设置工具的快捷方式。 如果需要阻止浮动工具栏出现，可以单击Office按钮，再单击“Excel选项”按钮，然后在“常用”页面中清除“选择时显示浮动工具栏”复选框。 如何简便地找到有用的组合键？ 按下一次Alt键就可以显示选项卡级别的快捷组合键(再按一次就可以隐藏)。按下命令所在选项卡的对应键，就可以显示选项卡级别和命令级别的所有组合键。例如，按Alt+M组合键显示“公式”选项卡。按Alt+H+H组合键显示“填充颜色集合”，以此 类推。 在Excel 2007中能创建比在Excel 2003中更大的工作表吗？ Excel 2003中工作表的大小限制为64 000行和256列。而Excel 2007允许最多1 048 576行和65 536列。要验证此限制，可以按下F5键(可以定位任何单元格)，键入如CAT1000000，单击“确认”按钮。Excel真的会定位到单元格CAT1000000！Excel 2007的设计还使针对大型工作表的计算更快了。事实上，如果计算机有n个处理器，那么相关的操作就会比用Excel 2003时快n倍，因为Excel 2007可以利用多处理器的优势。 还有一些其他的限制在Excel 2007中得到改进，例如： · 43亿的颜色选择。 · 不限制层数的条件格式。 · 多至64列的排序。 · 允许100层的“撤消”操作。 · 一个公式中允许多至8000个字符。 · 一个单元格中允许32 000个字符。 什么是公式自动完成？ 假设要输入一个公式来计算一组单元格的平均值。可以从键入“=AV”开始，然后新的公式自动完成功能就会出现，如图0.6所示。不需要输入完整的“average”，只需按下Tab键或双击“AVERAGE”，Excel就会自动输入“=Average”到公式中。 如果使用区域名称 (参见第1章)或新的表格功能(参见第24章)，将会真正感受到公式自动完成功能带来的好处。 图0.5 浮动工具栏 图0.6 公式自动完成功能 什么是Office 按钮？ 前文已经简单地讨论过Office按钮。在单击Office按钮后出现的菜单中可以实现以下功能。 · 实现文件级别的主要任务，如保存、关闭、打开和打印。 · 自定义Excel的各个方面(通过单击“Excel 选项”按钮)。 · 安装Excel加载项(单击“Excel选项”按钮，显示“加载项”页面)。 什么是主题？ 主题可以控制工作表的颜色、字体和特殊效果。主题作用于整个工作簿。要选择一个主题，只需选择“页面布局”选项卡，然后在“主题” 组中单击“主题”按钮。Excel 2007提供许多主题选择，图0.7显示了一部分主题。当鼠标指向某一主题时，Office的实时预览功能会显示选择这个主题后表格的外观。还有一个选择就是通过在颜色、字体、效果列表中选择，创建自定义主题，并保存起来供以后使用，所有自定义主题都会显示在“自定义”目录中。 图0.7 主题的选择 什么是SmartArt? SmartArt提供许多优雅的图样和效果，这些图样已经超出了常见的圆形、矩形和箭头样式。想看SmartArt如何工作，只需打开一个新的工作簿，在“插入” 选项卡的“插图”组中单击“SmartArt”按钮，将看到如图0.8所示的选项。 图0.8 SmartArt选项 我们选择第1个选项，然后在每个块中输入达拉斯小牛队上场队员的名字，结果如 图0.9所示。 图0.9 SmartArt示例 如何改变一个工作表的显示比例？ 在Excel 2007中，通过移动屏幕右下角的显示比例滑块(如图0.10所示)可以轻松地放大或缩小工作表。 图0.10 显示比例滑块 在打印电子表格前有什么新的查看方式？ “视图”选项卡中的“工作簿视图”组(如图0.11所示)提供了可用的视图。 图0.11 工作簿视图选项 “普通”视图是常用的工作表视图。“页面布局”视图显示单独的每一页，允许增加页眉和页脚，调整页边距等。“分页预览” 视图显示并允许改变分页。 如何同时显示一个工作簿的多个副本？ 假设工作簿包含一年中每一个月的工作表。如果要实现一些涉及不同月份的计算，那么可以在屏幕上同时看到工作簿的不同工作表会很有帮助。要看到工作簿的多个视图，需要单击“视图”选项卡，再重复单击“新窗口”按钮，直到得到足够多的电子表格的视图(例如，如果需要3个视图，就单击“新窗口”按钮两次)。然后单击“全部重排”按钮，选择这些副本的排列方式。在每个窗口里面显示不同的工作表会使开发涉及多个工作表的公式变得更简单。 如何让没有安装Excel 2007的用户使用Excel 2007文件？ 如果用户有Office 97、Office 2000 、Office XP或Office 2003，只需在保存文件时选择Excel 97-2003工作簿文件格式即可。如果用户使用Office 95，则以Excel 5.0/95工作簿文件格式存储。 Excel 2007默认的文件格式是“Excel工作簿”，创建的文件以.xlsx为扩展名。 注意：如果使用了Excel 2007的新功能，工作簿与Excel的早期版本可能不会完全兼容。 要确定工作簿是否兼容早期版本，可以运行Excel的兼容性检查器。单击Office 按钮，指向“准备”，然后选择“运行兼容性检查器”选项。如果工作簿包含如图0.9所 示的SmartArt，兼容性检查器会通知我们，该SmartArt与早期版本不兼容，如图0.12 所示。 图0.12 兼容性检查器 其他变化？ 我们在本章讨论的Excel新增功能不属于业务建模和数据分析范畴，所以我们只是对这些功能加以简单介绍。而下面的Excel 2007的新功能对数据分析和业务建模非常重要，我们将详细讲述。 · 创建区域名称的方法已经大为改进，详情参见第1章。 · 条件格式的选择有了极大扩展。新的强调视觉冲击的条件格式选项令人惊奇，它有助于更好地理解数据(数据条、图标和色阶)。第22章将详细讨论条件格式。 · 排序和筛选功能有了很大改进。甚至可以实现以单元格或字体的颜色来排序！Excel 2007还使移除重复数据变得容易，详情参见第23章和第40章。 · 新的“表”功能将给电子表格建模带来变革。合理使用“表”功能可以在为工作簿添加新的数据时自动更新公式、格式及图表。第24章将详细描述“表” 功能。 · 数据透视表已经修订改进了，第38章对这个重要的数据分析功能有详细的 讨论。 · Excel图表的外观有了改进，这一点可以在翻看书中的Excel 图表时看出。 · 新的“IFERROR”功能(参见第11章)将使修改可怕的#REF和#N/A出错信息变得更容易，这些出错信息常常把电子表格计算变成一场噩梦！ · 新的COUNTIFS(参见第18章)和SUMIFS及AVERAGEIFS(参见第19章)将使汇总数据集变得更简单。 第1章 区域名称 第1章 区域名称 · 如果对亚利桑那州、加利福尼亚州、蒙大拿州、纽约州和新泽西州的销量进行汇总，可以使用像AZ+CA+MT+NY+NJ这样的计算汇总公式代替SUM(A21:A25)以得到正确答案吗？ · 公式AVERAGE(A:A)的功能是什么？ · 工作簿范围内的名称与工作表范围内的名称有什么区别？ · 我确实开始喜欢使用区域名称。我在很多工作簿中都定义了区域名称，但是这些区域名称在公式中没有显现，如何才能使这些区域名称在以前创建的公式中显 现呢？ · 如何便捷地选取命名的区域？ · 如何把一个所有区域名称(及其所代表的单元格)的列表粘贴到工作表中？ 在使用工作表时可能用过像SUM(A5000:A5049)这样的公式，在这种情况下必须找出单元格A5000:A5049中的内容。如果单元格A5000:A5049中的内容是美国各个州的销量，那么是不是使用公式SUM(USSales)更容易理解呢？本章将介绍如何定义一个单元格名称或一个单元格区域的名称，还会讲述如何在公式中使用区域名称。 如何创建命名区域？ 有3种方法可以创建命名区域。 · 在“名称框”中输入一个区域名称。 · 在“公式”选项卡的“定义的名称”组中单击“根据所选内容创建”按钮。 · 在“公式”选项卡的“定义的名称”组中单击“名称管理器”按钮或单击“定义名称”按钮。 使用“名称框”来创建区域名称 “名称框”就位于A列标签的正上方，如图1.1所示(显示编辑栏后才能看到“名称框”)。要在“名称框”中创建区域名称，只要选取要命名的单元格或单元格区域，单击“名称框”，输入要使用的名称，按下Enter键，就创建了区域名称。单击“名称框”中的下拉箭头就可以显示在本工作簿中的所有区域名称。也可以用按下F3键打开“粘贴名称”对话框来显示所有区域名称。当从“名称框”中选择一个区域名称时，Excel会自动选择跟区域名称对应的单元格，这样就能确认选择了想要命名的单元格或单元格区域。 图1.1 可以选择要命名的单元格区域，在“名称框”中输入区域名称 例如，假设想将F3为命名east、将F4命名为west(参见图1.2和示例文件Eastwest.xlsx)，只要选择单元格F3，在“名称框”中输入east，按下Enter键，再选择单元格F4，在“名称框”中输入west，按下Enter键。现在如果在其他单元格中引用单元格F3，就可以用=east而不是=F3。这意味着不管什么时候在公式中见到引用“east”，将会插入单元格F3中的数据。 假设要将一个矩形单元格区域(如A1:B4)命名为Data，只要选择单元格区域A1:B4，在“名称框”中输入Data，按下Enter键。现在公式=AVERAGE(Data)意味着对单元格区域A1:B4中的数值求平均值(参见示例文件Data.xlsx和图1.3)。 图1.2 将F3 命名为east，将F4命名为west 图1.3 将单元格区域A1:B4命名为Data 有时候我们希望命名一组由不连续的矩形区域组成的单元格，例如在图1.4和示例文件Noncontig.xlsx中，我们想把B3:C4、E6:G7、B10:C10这些区域命名为Noncontig。要进行这样的命名，先选择三个区域中的一个区域(可以选B3:C4)，按下Ctrl键的同时选择其他两个区域(E6:G7和B10:C10)，然后松开Ctrl键，在“名称框”中输入Noncontig，按下Enter键即可。在任何公式中使用Noncontig将指向单元格区域B3:C4、E6:G7和B10:C10的内容。例如，在单元格E11中输入=AVERAGE(Noncontig)将得出4.75(因为区域内的12个数值之和是57，57除以12得到4.75)。 图1.4 命名不连续的单元格区域 用“根据所选内容创建”创建命名区域 工作簿States.xlsx包含美国50个州的三月份销量，图1.5显示了这个数据的一个子集，我们要以每个州的缩写来命名B6:B55中的每个单元格。要完成这个工作，选择区域A6:B55，单击“公式”选项卡的“定义的名称”组中的“根据所选内容创建”按钮(如 图1.6所示)，然后选中“最左列”复选框，如图1.7所示。 图1.5 以各州的缩写来命名各州的销量，当引用某个单元格时 可以使用缩写而不是使用列标和行号 。 图1.6 “根据所选内容创建”按钮 图1.7 选中“最左列”复选框 Excel 现在就把选择区域内第一列的名称与所选区域内第二列的单元格联系起来，于是，B6被指定为名称AL，B7命名为AK，以此类推。如果是在“名称框”中创建这些名称会很枯燥乏味！可以单击“名称框”中的下拉箭头来确认已经创建所需的区域名称。 使用“名称管理器”选项来创建区域名称 如果单击“公式”选项卡中的“名称管理器”按钮，然后选择“新建”，会显示如 图1.8所示的“新建名称”对话框。 假设要将单元格区域A2:B7命名为range1(区域名称不区分大小写)，只需在“名称框”中输入range1，然后指向区域或在“引用位置”中输入=A2:B7，单击“确定”按钮即可完成。此时，“新建名称”对话框如图1.9所示。 图1.8 创建区域名称前的“新建名称”对话框 图1.9 创建区域名称后的“新建名称”对话框 如果单击“范围”下拉箭头，可以选择范围是工作簿还是工作簿中的某一个工作表，我们稍后讨论这个问题，目前我们选择默认的整个工作簿，如果愿意，还可以给区域名称加上注释。 名称管理器 如果单击“名称”中的下拉箭头，会在“名称框”中看到区域名称range1(还有其他创建的区域名称)，在Office 2007系统中有一个简单办法来编辑或删除区域名称，而这个方法是Office早期版本中没有的。只要选择“公式”选项卡，然后单击“名称管理器”按钮，打开“名称管理器”对话框，就会看到一个所有区域名称的列表，例如，对于文件States.xlsx，“名称管理器”对话框如图1.10所示。 图1.10 States.xlsx的“名称管理器”对话框 要编辑任何区域名称，只要选择该区域名称，再单击“编辑”按钮，就可以改变区域名称、区域引用的单元格或区域名称的作用范围。 如要删除一组区域名称，首先选择要删除的区域名称。如果这些区域名称是连续排列的，只要选择第1个名称，然后按下Shift键，再选择最后一个名称；如果这些名称不是连续排列的，可以任意选择一个要删除的名称，再按下Ctrl键不动，然后选择其他所有要删除的名称。最后单击“删除”按钮来删除所选择的区域名称。 下面介绍一些使用区域名称的具体示例。 如果对亚利桑那州、加利福尼亚州、蒙大拿州、纽约州和新泽西州的销量进行汇总，可以使用像AZ+CA+MT+NY+NJ这样的计算汇总公式代替SUM(A21:A25)以得到正确答案吗？ 让我们回到文件States.xlsx，其中我们指定了每个州的缩写作为每个州销量的区域名称，如果我们想知道阿拉巴马州、阿拉斯加州、亚利桑那州和阿肯色州的总销量，我们可以使用公式 SUM(B6:B9)，也可以指向单元格B6、B7、B8和B9，公式会是如下方式：=AL+AK+AZ+AR，而后一种方法的公式显然更容易理解。 使用区域名称的另外一个示例参见文件Historicalinvest.xlsx(如图1.11所示)，表中包含一年的各种投资的收益率，包括Stocks(股票)、T.Bills(短期国库券)、T.Bones(长期国库券)(部分行没有显示)。 图1.11 历史投资数据 选择单元格区域B7:D81后，选择“公式”|“根据选择内容创建”，然后选择“首行”来创建名称，B8:B81的区域名称为Stocks，C8:C81的区域名称为T.Bills，D8:D81的区域名称为T.Bonds，现在我们不再需要知道数据的位置了，例如在B84中键入=AVERAGE(，我们可以按下F3，“粘贴名称”对话框就出现了，如图1.12所示。然后在“粘贴名称”列表中选择Stocks，单击“确定”按钮，再键入右括号，公式=AVERAGE(Stocks)就可以计算出Stocks的平均收益率(12.05%)。这种方法的美妙之处在于，即使我们记不住数据的位置，仍然可以在工作簿的任何地方使用股票(Stocks)收益 数据！ 如果我们不提到令人兴奋的Excel 2007新的自动完成功能就太疏忽了，当你键入=AVERAGE(T的时候，Excel将会自动显示一个所有以T开头的区域名称列表，只需单击T.Bills就可完成区域名称的输入，如图1.13所示。 AVERAGE(A:A)的功能是什么？ 如果在公式中用到列的名称(以A:A、C:C等形式)，Excel将把整个列作为一个命名区域。例如，输入公式=AVERAGE(A:A)会对所有A列中的数字求平均值。如果需要频繁输入新的数据到列中，使用针对全部列的区域名称很有用处。例如，如果A列包含产品每月销量，每月都会输入新的销量数据，公式就可以给出最新的销量平均值。但必须提醒的是，如果在A列中输入公式=AVERAGE(A:A)，将得到一个循环引用的信息，因为单元格的数值中包含了含有计算销量平均值公式的单元格，第10章将介绍如何解决循环引用。同样，键入公式 =AVERAGE(1:1)将计算第一行的平均值。 图1.12 可以使用“粘贴名称”对话框来添加一个区域名称到公式中 - 图1.13 自动完成功能示例 工作簿范围内的名称和工作表范围内的名称有什么区别？ 文件Sheetnames.xlsx有助于我们理解工作簿范围内的区域名称和工作表范围内的区域名称的不同，在使用“名称框”创建名称时，该名称是工作簿范围的。例如，用“名称框”将sheet3的单元格区域E4:E6命名为sales，而这些单元格的数值分别是1、2和4，那么在任何工作表中输入公式 =SUM(sales)，都将得到结果7，因为在“名称框”中创建的名称都是具有工作簿范围的，所以在工作簿中任何地方引用名称sales(具有工作簿范围)，都会指向sheet3的E4:E6，在任何工作表中输入公式=SUM(sales)都会得到结果7，这是因为在工作簿任何地方，Excel 都会把sales指向sheet3 的E4:E6。 现在假设在sheet1的E4:E6中分别键入4、5和6，在sheet2的E4:E6中键入3、4和5。下一步到名称管理器中，把sheet1的E4:E6命名为jam，并且定义它的范围是sheet1，然后再切换到sheet2，在名称管理器中将E4:E6命名为jam，并定义它的范围是sheet2，“名称管理器”对话框将如图1.14所示。 现在，在每个工作表中输入公式=SUM(jam)会得到什么结果呢？在sheet1中，=SUM(jam)将把sheet1的E4:E6求和，因为这些单元格的数值是4、5和6，得到结果15。在sheet2中，=SUM(jam)把sheet2的E4:E6求和，3+4+5=12。然而在sheet3中，公式=SUM(jam)会出现一个#NAME错误，因为没有在sheet3中定义名称jam！如果在sheet3中键入公式=SUM(Sheet2!jam)，Excel将识别出代表sheet2的单元格区域E4:E6的工作表级别的名称，得到结果3+4+5=12。因此，以工作表名称开始，后面使用！，将允许我们引用一个不在本工作表范围内的名称。 图1.14 拥有工作表范围名称和工作簿范围名称的“名称管理器”对话框 我确实开始喜欢使用区域名称。我在很多工作簿中都定义了区域名称，但是这些区域名称在公式中没有显现，如何才能使这些区域名称在以前创建的公式中显现呢？ 让我们看一下文件Applynames.xlsx，如图1.15所示。 图1.15 如何在公式中应用区域名称 在单元格F3中已经输入产品的价格(price)，产品需求(demand)在F4中等于10000-300*F3，单位成本(unitcost)和固定成本(fixed cost)已经在单元格F5和F6中分别输入，利润(profit)在F7中用公式=F4*(F3-F5)-F6计算，我们单击“公式”选项卡中的“根据所选内容创建”按钮，然后选中“最左列”复选框，将F3命名为price，F4命名为demand，F5命名为unitcost，F6命名为fixed cost，F7命名为profit。我们想让这些名称在F4和F7的公式中显示。要应用这些区域名称，首先选择想要应用这些名称的区域(此例中为F4:F7)，在“公式”选项卡的“定义的名称”组中单击“定义名称”下拉箭头，然后选择“应用名称”。突出显示要使用的名称，单击“确定”按钮，可以看出，像我们希望的那样，F4现在包含公式=10000-300*price，而F7包含公式=demand*(price-unitcost)-fixed_cost。 如何便捷地选取命名的区域？ 如果想选取一个命名区域的单元格，只要按下Ctrl+*组合键就可以选取整个区域。 如何把一个所有区域名称(及其所代表的单元格)的列表粘贴到工作表中？ 按下F3键来显示“粘贴名称”框，单击“粘贴列表”按钮，一个所有区域名称的列表和每个名称所代表的单元格都粘贴到工作表中，粘贴位置从当前的单元格开始。 备注 · Excel不允许使用字母r和c作为区域名称。 · 如果使用“根据所选内容创建”来创建区域名称，并且名称中包含空格，Excel会插入下划线“_”来填充空格。例如，名称Product 1将被创建成Product_1。 · 区域名称不能以数字开始并且不能像单元格引用。例如，32和A4都不能作为区域名称，由于Excel 2007可以超过16 000列，不能使用cat1之类的区域名称，因为存在一个CAT1单元格。如果试图将一个单元格命名为CAT1，Excel会提示是无效名称，也许你所能做的也就是把单元格命名为cat1_。 · 区域名称中可用的符号只有两个，就是点(.)和下划线(_)。 练习 1. 文件Stock.xlsx包含通用汽车和微软的每月股票收益，命名包含每只股票每月收益的区域，计算每只股票月平均收益。 2. 打开一个工作表，将包含A1:B3和A6:B8的区域命名为Red。 3. 利用给出的任何两个城市的经纬度，文件Citydistance.xlsx计算任意两个城市间的距离。给每个城市的经度和纬度定义区域名称，然后确保这些名称出现在计算总距离的公式中。 第2章 LOOKUP函数 第2章 LOOKUP函数 · 如何写一个基于收入计算税率的公式？ · 已知产品ID，如何查找产品价格？ · 假设产品价格是随时间变化的，已知产品的销售时间，如何编写公式来计算产品的价格？ LOOKUP函数的语法 LOOKUP函数可以在工作表中“查找”数值。Excel可以实现垂直查找(用VLOOKUP函数)和水平查找(用HLOOKUP函数)。在垂直查找中，查找操作从工作表的第一列开始；在水平查找中，查找操作从工作表的第一行开始。因为大多数的公式用到的查找都是垂直查找，我们将集中讲述VLOOKUP函数。 VLOOKUP函数的语法 VLOOKUP函数的语法如下，方括号([])内为可选参数。 VLOOKUP(lookup value, table range, column index,[range lookup]) · lookup value 是要在工作表第一列中查找的值。 · table range是包含整个查找表格的区域，这个表格区域包括尝试匹配查找值的第一列，还包括任何我们将要在其中查找公式结果的列。 · column index是取得LOOKUP函数结果的表格区域内的列标。 · range lookup是可选参数。range lookup允许指定是精确匹配还是近似匹配，如果range lookup为真(TRUE) 或省略，那么表格区域内的第一列一定是按数字升序排列的。如果range lookup为真(TRUE)或省略，并且在表格区域内第一列找到了精确匹配的值，Excel将返回精确匹配行的值；如果range lookup为真(TRUE)或省略，而精确匹配的值不存在，Excel 就返回第一列中比要查找值小的最大值；如果range lookup为假(FALSE)，并且在表格区域中第一列找到精确匹配的值，Excel将返回表中相匹配行的值；如果没有精确匹配的值，则返回#N/A(没有找到)。注意range lookup参数为1表示TRUE，0表示FALSE。 HLOOKUP函数的语法 在HLOOKUP函数中，Excel会试图在表格区域的第一行(而不是第一列)来定位要查找的数值。HLOOKUP函数只是把VLOOKUP函数语法的“column”换成“row”。 下面研究一些查找(LOOKUP)函数的有趣示例。 如何写一个基于收入计算税率的公式？ 下面的例子显示了表格区域的第一列数值按升序排列时VLOOKUP是如何工作的。假设税率根据收入来确定，如下表所示。 Income level(收入水平/美元) Tax rate(税率/%) 0～9999 15 10 000～29 999 30 30 000～99 999 34 100 000以上 40 关于编写根据收入确定税率的公式的示例，请参考文件Lookup.xlsx，如图2.1所示。 图2.1 使用LOOKUP函数来计算税率，表中第一列已经按升序排序 首先在D6:D9中先输入相关的信息(税率和临界值)，将区域D6:E9命名为lookup，建议总是命名要用到的表格区域，这样就不需要记住表格区域的具体位置，并且当复制包含LOOKUP函数的公式时，要查找的区域总是正确的。为阐述LOOKUP函数如何工作，我在区域D13:D17中输入一些收入数据，通过在E13:E17中依次复制公式VLOOKUP(D13,lookup,2,TRUE)，就计算出D13:D17中列出的收入的税率，检查一下在E13:E17中LOOKUP函数是如何工作的，注意，因为在公式中column index是2，答案总是从所选表格区域的第二列得到。 · D13中的-1000得到的结果是#N/A，这是因为-1000比表格区域内第一列的最小收入值还要小，如果想在收入为-1000时得到税率是15%，只要把D6中的0替换成-1000或更小的值即可。 · D14中的30 000正好与表格区域内第一列的一个数值完全一样，所以函数返回税率是34%。 · D15中的29 000在表格区域的第一列中没有完全相符的值，在这种情况下，函数找到比29 000小的最大值就是10 000，函数返回第二列中跟收入10 000对应的税率30%。 · D16中的98 000在表格区域的第一列中也没有完全相符的值，函数就找到比98 000小的最大值，返回第二列中跟收入30 000对应的税率34%。 · D17中的104 000也不能完全匹配表格区域第一列中的某值，函数在表格区域第一列中找到比104 000小的最大值，返回第二列中与收入100 000相对应的税率40%。 · 在F13:F17中，我们把range lookup参数值从“TRUE”改为“FALSE”，从F13 复制公式VLOOKUP(D13,lookup,2,FALSE)到F14:F17。因为D14的值与表格区域第一列的收入30 000完全相符，得到税率是34%。而F13:F17的其他项全部显示#N/A，这是因为D13:D17中的其他收入都与表格区域第一列中的数值不完全 相符。 已知产品ID，如何查找产品价格？ 通常，表格区域第一列包含的数值并不是按升序排列的。例如，表格区域第一列为产品ID列表或雇员姓名。从我教几千名金融分析员的经验来看，我发现在表格区域第一列的数值不是升序排列时很多人不知道怎样处理LOOKUP 函数，在这种情况下只需要知道一个简单的规则：就是设定range lookup参数为FALSE。 以下是一个例子。在文件Lookup.xlsx(见图2.2)中，可以看到5种产品的价格按照产品ID排列，怎样写一个公式才能通过产品ID得到产品价格呢？ 图2.2 从产品ID查找价格，当表格区域不是按升序排列时， 在LOOKUP函数中使用FALSE作为最后一个参数 许多人会像我在单元格I18中那样输入公式：VLOOKUP(H18,lookup2,2)，然而要注意的是，当省略第4个参数(range lookup)时，此参数会被认为是TRUE。因为在表格区域lookup2(H11:H15)中产品ID不是按照字母顺序排列的，所以返回一个错误答案(3.5)。如果在单元格I18中输入公式VLOOKUP(H18,lookup2,2,FALSE)，就会返回正确的答案(5.2)。 也可以在公式中使用FALSE通过雇员的姓氏或ID找到雇员的薪水。 顺便说一下，可以看到在图2.2中我们隐藏了A～G列，在Excel 2007中要隐藏某些列，可以先选择要隐藏的列，单击“开始”选项卡，在“单元格”组中单击“格式”按钮，选择“隐藏和取消隐藏”，然后再选择“隐藏列”。 假设产品价格是随时间变化的，已知产品的销售时间，如何编写公式来计算产品的 价格？ 假设产品价格取决于产品出售的日期，如何在公式中使用LOOKUP函数得到正确的产品价格呢？假设产品的价格如下表所示： Date sold(销售日期) Price(价格/美元) 2005年1月－4月 98 2005年5月－8月 105 2005年9月－12月 112 我们要写一个公式来确定在2005年任何时期出售的产品的正确价格，考虑到多样性，我们来使用HLOOKUP函数，我已经在表格区域的第一行放入产品价格变化的日期，参见文件Datelookup.xlsx，如图2.3所示。 图2.3 使用HLOOKUP函数确定一个根据销售日期变化的价格 从C8复制公式HLOOKUP(B8,lookup,2,TRUE)到C9:C11，这个公式会试图比较B列中的日期和区域B2:D3的第一行：对任何在2005-1-1到2005-4-30范围内的日期，LOOKUP函数会找到2005-1-1并且返回在B3中的价格；对任何在2005-5-1到2005-7-31范围内的日期，LOOKUP函数会找到2005-5-1并且返回在C3中的价格；对任何晚于2005-8-1的日期，LOOKUP函数会找到2005-8-1并且返回在D3中的价格。 练习 1. 文件Hr.xlsx给出了雇员ID、薪水和工作年限。写一个公式通过ID号码得到雇员薪水，再写一个公式通过雇员ID得到雇员的工作年限。 2. 文件Assign.xlsx给出了分配给4个组的工人，还给出了每个工人对每个组的适宜度(0～10)。写一个公式得到每个工人对所分配组的适宜度。 3. 假设要在电视体育节目中推广微软的产品，购买的广告越多，单个广告的价格就越低，如下表所示。 Number of ads(广告数) Price per ad(广告单价/美元) 1～5 12 000 6～10 11 000 11～20 10 000 20以上 9 000 例如，购买8个广告，每个广告需付11 000美元，购买14个广告，每个广告只需要付10 000美元。写一个公式得到购买任意多个广告的总花费。 4. 假设要在一个受欢迎的电视音乐节目中推广微软的产品，购买第一组广告是一个价格，购买的广告越多，每个广告的花费就越低，如下表所示。 Ad number(广告数) Price per ad(广告单价/美元) 1～5 12 000 6～10 11 000 11～20 10 000 20以上 9 000 例如，如果购买8个广告，前5个广告需要各付12 000美元，而其他3个各付11 000美元。如果购买14个广告，前5个广告需要各付12 000美元，另外5个需要各付11 000美元，其余的4个每个只需付10 000美元。写一个公式来得到购买任意数目广告的花费。(提示：在表格区域中可能至少需要3列，而且公式也许要包含两个LOOKUP函数。) 5. 从银行贷款1年、5年、10年或30年的年利率显示在下表中。 Duration of loan(贷款年限/年) Annual loan rate(贷款的年利率/%) 1 6 5 7 10 9 30 10 如果贷款年限为1～30年，但是没有在表中列出，贷款利率会根据表格中利率插值得来。例如：贷款15年，因为15年位于10年到30年区间的1/4处，年利率将如下计算： 写一个公式来得到贷款年限为1～30年的任意值时的年利率。 6. 任何两个美国城市间距离(夏威夷和阿拉斯加除外)可以用以下公式来近似计算： 文件Citydata.xlsx包含指定美国城市的经纬度，创建一个表，给出所选城市中任意两个之间的距离。 7. 在文件Pinevalley.xlsx中，第1个工作表包括在Pine Valley大学工作的雇员的薪水，第2个工作表包含雇员的年龄，第3个工作表包含雇员的工作年限。创建第4个工作表来包含所有雇员的薪水、年龄和工作年限。 8. 文件Lookupmultiplecolumns.xlsx包含一个电器商店销售的相关信息，在B17中输入了一个销售员的名字。在C17中编写公式，然后复制到D17:F17，公式要提取每个销售员的收音机销量并存放到C17，提取电视销量并存放到D17，提取打印机销量并存放到E17，提取CD销量并存放到F17。 第3章 INDEX 函数 第3章 INDEX函数 · 有一个美国城市间距离的列表，如何编写函数来返回其中两个城市(如西雅图和迈阿密)间的距离？ · 如何编写公式来引用包含每个城市与西雅图之间距离的整个列？ INDEX函数的语法 INDEX函数可以返回一组数字中任意行列中的项，最常用的INDEX函数语法如下： INDEX(Array, Row Number, Column Number) 举例说明，公式INDEX(A1:D12,2,3)会返回A1:D12中的第二行第三列的项，也就是单元格C2中的内容。 有一个美国城市间距离的列表，如何编写函数来返回其中两个城市(如西雅图和迈阿密)间的距离？ 文件Index.xlsx(如图3.1所示)包含8个美国城市间的距离，含有距离数据的区域C10:J17命名为distances。 图3.1 可以使用INDEX函数计算城市间距离 假设在一个单元格中输入波士顿到丹佛之间的距离，因为波士顿起始的距离在区域distances的第1行，而到丹佛的距离在区域中的第4列，正确的公式是：INDEX(distances,1,4)，得到的结果是波士顿和丹佛之间的距离为1991英里。同样，要知道西雅图和迈阿密之间的距离(更远一些)，可以用公式INDEX(distances,6,8)，得出西雅图和迈阿密之间的距离是3389英里。 假设西雅图超音速篮球队着手一次巡回赛，要在凤凰城、洛杉矶、丹佛、达拉斯和芝加哥进行比赛，最后返回西雅图。我们能否轻松地计算出他们的总行程？如图3.2所示，只需按出访顺序列出他们要访问的城市(8-7-5-4-3-2-8)，开始和结束都是西雅图，复制公式INDEX(distances,C21,C22)到D21:D26，D21中的公式计算出从西雅图到凤凰城(城市号码为7)的距离，D22中的公式计算出从凤凰城到洛杉矶的距离，以此类推。超音速队巡回赛的总旅程为7112英里。只是为了好玩，我用INDEX函数算出迈阿密热队在NBA季度赛中的总旅程比其他队都要多。 图3.2 西雅图超音速队巡回赛的总旅程 如何编写公式来引用包含每个城市与西雅图之间距离的整个列？ INDEX函数使引用数组的整行或整列变得很简单。如果设置行数(row number)为0，INDEX函数将引用整列，如果设置列数(column number)为0，INDEX函数会引用整行。例如，要计算所有城市到西雅图的总距离，可以用下面两个公式中的一个： SUM(INDEX(distances,8,0) SUM(INDEX(distances,0,8) 第1个公式会累加distances数组中第8行(行17)的数据，第2个公式会累加distances数组中第8列(列J)的数据。不管用哪种，我们发现，从西雅图出发到其他城市的总距离都是15 221英里，如图3.1所示。 练习 1. 用INDEX函数分别计算洛杉矶与凤凰城及丹佛与迈阿密之间的距离。 2. 用INDEX函数计算达拉斯到所有其他城市的总距离。 3. 马克·库班要带达拉斯小牛队去芝加哥、丹佛、洛杉矶、凤凰城和西雅图比赛，他们的总旅程会是多少？ 4. 文件Product.xlsx包含6种产品的每月销量，使用INDEX函数来计算产品2在3月的销量，计算4月份的总销量。 5. 文件Nbadistances.xlsx显示了任意两个NBA赛场的距离，假设从亚特兰大开始，按列表顺序访问每个赛场再回到亚特兰大，总旅程是多少？ 第4章 MATCH 函数 第4章 MATCH函数 · 已知几种产品的月销量，如何编写公式得到一个产品某个月份的销量？例如，6月份产品2的销量是多少？ · 已知棒球队员的薪水列表，如何编写公式得到薪水最高的队员？薪水排名第五的队员是谁？ · 已知一个投资项目的年现金流量，如何编写公式得出需要多少年才能收回初始投资额？ 假设有一个5000行的工作表，包括5000个名字。此时需要找到John Doe 这个名字，而这个名字是在表中某个位置(并且只出现一次)，是否能有一个公式来返回John Doe所在的行数？Excel的MATCH函数用于在一个已知数组里定位与给出的字符串或数字第1个相符的位置。在需要知道一个数值的位置而不是某个单元格内的值的情况下，应该使用MATCH 函数而不是LOOKUP函数，MATCH函数的语法结构如下： MATCH (lookup value, lookup range,[match type]) 在下面的解释中，我们假设要查找区域的所有单元格都是在同一列中。在MATCH函数的语法结构中： · lookup value是要在查找区域内查找的值。 · lookup range是要查找匹配值的查找区域。 · match type=1要求查找区域内的数值是以升序排列的，MATCH函数会返回包含在区域内小于或等于查找值的最大值所在行的位置(相对于查找区域顶部的 位置)。 · match type= -1要求查找区域内的数值是以降序排列，MATCH函数会返回包含在区域内大于或等于查找值的最后一行的位置(相对于查找区域顶部的位置)。 · match type=0会返回查找区域中第1个与查找值完全相符的行的位置(我们将在第19章讲述如何找第2个或第3个相符值所在行的位置)。当完全相符的值不存在并且match type=0，Excel会返回#N/A。大多数的MATCH函数应用都用match type=0，但是如果match type没有写，则会默认为match type=1。因此，当查找区域没有排序时使用match type=0，这是最常见的情况。 文件Matchex.xlsx(如图4.1所示)包含了MATCH函数语句结构的3个例子。 在单元格B13中，公式MATCH("Boston",B4:B11,0)返回值为1，因为在区域B4:B11的第一行就有值Boston，字符型的值必须用双引号("")括起来。在B14中，公式MATCH("Phoenix",B4:B11,0)返回值为7，因为单元格B10(B4:B11区域中的第7个)是找到Phoenix的第1个单元格。在E12中，公式MATCH(0,E4:E11,1)返回值为4，因为在E4:E11区域内小于或等于0的最大值在单元格E7(查找区域的第4个)中。在单元格G12中，公式MATCH(-4,G4:G11,-1)返回结果为7，这是因为在G4:G11区域中最后一个大于或等于-4的值在单元格G10(查找区域内的第7个)中。 图4.1 使用MATCH函数在一个区域内找到某个值的位置 MATCH函数也可以适用于不完全匹配的情况。例如，公式MATCH("Pho*",B4:B11,0)返回值为7，星号作为通配符，也就是说Excel会在B4:B11区域搜索第1个以Pho开始的字符串，顺便说一下，在第2章的价格查找练习中，用公式VLOOKUP("x*",lookup2,2)也会返回产品X212的价格(4.80)。 如果查找区域局限在一行之中，Excel会返回在查找区域内找到第1个相符值的相对位置，从左到右，如下例所示，在与其他Excel函数(如VLOOKUP、INDEX或MAX)一起使用时，MATCH常常很有用处。 已知几种产品的月销量，如何编写公式得到一个产品某个月份的销量？例如，6月份产品2的销量是多少？ 文件Productlookup.xlsx(如图4.2所示)列出了1月－6月4种NBA摇头玩偶的销量，如何编写公式计算某个产品在某个月份的销量？秘诀就是：使用MATCH函数找到产品所在行的位置，再用MATCH函数找到相对应的月份的列的位置，然后使用INDEX函数来返回某个月份某个产品的销量。 图4.2 MATCH函数可以和其他函数(如INDEX和VLOOKUP)结合使用 将包含玩偶销量的区域B4:G7命名为Sales，在单元格A10中输入要查找的产品，在B10中输入月份，在C10中使用公式MATCH(A10,A4:A7,0)来确定区域中哪一行包含玩偶Kobe的销量，在D10中使用公式MATCH(B10,B3:G3,0)确定哪一列包含6月份销量。现在有了包含所需数据的行数和列数，就可以在单元格E10中使用公式INDEX (Sales,C10,D10)来得到需要的销量数据，更多关于INDEX函数的信息参见第3章。 已知棒球队员的薪水列表，如何编写公式得到薪水最高的队员？薪水排名第5的队员是谁？ 文件Baseball.xlsx(如图4.3所示)列出了在2001赛季付给401名主要棒球队员的薪水，数据没有以薪水排序，需要编写公式返回薪水最高和第5高的队员名字。 要找到薪水最高的队员的名字，我们如下操作。 · 用MAX函数确定最高薪水是多少。 · 用MATCH函数来确定薪水最高的队员所在行数。 · 用VLOOKUP函数(用包含该队员薪水的“行”为参数)来查找这个队员的名字。 我们把包含队员薪水的区域C12:C412命名为salaries，把在VLOOKUP函数中用到的区域(A12:C412)命名为lookup。 图4.3 本例使用MAX、MATCH和VLOOKUP函数来找到和在列表中显示最高值 在C9中，先用公式MAX(salaries)得到最高薪水是22 000 000，接下来在C8中使用公式MATCH(C9,salaries,0)来确定拥有最高薪水的队员的位置，使用match type=0是因为薪水不是以升序或降序排列的，我们得到345行的队员有最高的薪水。最后在C6中，用公式VLOOKUP(C8,lookup,2)在查找区域第二列中找到队员名字。一点不奇怪，我们发现艾力克斯·罗德里格斯是2001年薪水最高的球员。 要查找薪水第5高的队员