关闭

关闭

关闭

封号提示

内容

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

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

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

上传者: hjh123_9244 2013-10-23 评分 0 0 0 0 0 0 暂无简介 简介 举报

简介:本文档为《精通Excel 2007数据分析与业务建模word版+CDdoc》,可适用于高等教育领域,主题内容包含精通Excel数据分析与业务建模第章日期和日期函数第章Excel中的新功能第章Excel中的新功能什么是功能区?什么是快速访问工具栏?什么是浮动工具符等。

精通Excel数据分析与业务建模第章日期和日期函数第章Excel中的新功能第章Excel中的新功能什么是功能区?什么是快速访问工具栏?什么是浮动工具栏?如何简便地找到有用的组合键?在Excel中能创建比Excel中更大的工作表吗?什么是公式自动完成?什么是Office按钮?什么是主题?什么是SmartArt如何改变一张工作表的显示比例?如何同时显示一个工作簿的多个副本?在打印电子表格前有什么新的查看方式?如何让没有安装Excel的用户使用Excel文件?其他变化?微软的OfficeExcel是全新的并且有许多的改进。新的界面和新的功能最初会使有经验的用户迷惑但在熟悉新的界面后很少有人再想回头使用老版本的Excel!对于Excel初学者新的界面会使你更容易地找到Excel的更复杂、更有用的功能。本章将简要讲述Excel的一些改变。本书主要聚焦在应用Excel进行业务建模和数据分析上所以不会花费很多时间介绍像SmartArt和主题这样的图像方面的功能。如果想得到更多关于这些功能的信息请参考MarkDodge和CraigStinson所著的《精通Excel中文版》(清华大学出版社)。什么是功能区Excel开发人员问Excel用户在使用Excel时遇到的典型困难是什么时发现人们想要但无法找到的以上的功能已经在Excel中实现了。问题是许多很好的功能(如数据表格、合并计算和规划求解)很难找到。为使读者更容易地找到和学习Excel及其他Office产品的所有完美功能Office开发组制作了功能区(如图所示)。图功能区中的“开始”选项卡功能区中的选项取决于选择哪个选项卡。开始。这个选项卡包含大部分的工作表编辑和格式命令(如字体和单元格对齐方式)及剪贴板命令(如“粘贴”和“选择性粘贴”)。页面布局。这个选项卡包含控制工作表打印和工作表外观(如是否显示网格线)的一些命令。插入。使用这个选项卡中的命令可以在工作表中插入想放入的内容如剪贴画、图表和数据透视表。数据。这个选项卡中的命令是关于数据分析功能的如排序和筛选。公式。这个选项卡中的命令用于命名单元格区域、体验Excel的奇妙功能、进行数学计算或审核工作表的结构。审阅。使用这个选项卡中的命令来管理工作表批注、保护工作表、进行拼写检查、跟踪工作表改变或完成相关任务。视图。这个选项卡用于控制工作表的显示方式。可以冻结窗格排列和重排窗口控制页面布局(后文有更详细的介绍)。开发工具。这个选项卡中的命令主要用于开发Excel宏。你也可以插入用户窗体和控件(详见第章)。如果这个选项卡不可见单击Office按钮(如图所示)单击“Excel选项”按钮然后在“常用”页中选中“在功能区显示“开发工具”选项卡”复选框。图Office按钮加载项。Excel的一些加载项(如规划求解或分析工具包)可以在这个选项卡中找到。这个选项卡只有在至少安装了一个加载项时才可见。这些选项卡和功能区使Excel提供的功能一目了然。如果觉得功能区占了太多空间可以隐藏(或重新显示)它:按下CtrlF双击任何选项卡或右击功能区然后选择“功能区最小化”选项。什么是快速访问工具栏?可能有一些命令使用频率比其他的更高而在选项卡之间切换来查找命令会使操作很慢。Excel提供了快速访问工具栏可以把常用的命令集中在此。快速访问工具栏的默认位置在功能区上面、屏幕的左上角如图所示。图快速访问工具栏要把命令添加到快速访问工具栏只需右击一个命令选择“添加到快速访问工具栏”选项。还可以使用以下方式添加:单击Office按钮单击“Excel选项”按钮打开“自定义”页面(如图所示)。选择要添加的命令然后单击“添加”和“确定”按钮。如要从快速访问工具栏中移除任何命令只需右击该命令选择“从快速访问工具栏删除”选项。可以把快速访问工具栏移动到功能区的下面:右击工具栏选择“在功能区下方显示快速访问工具栏”选项。图可以在快速访问工具栏中添加、移除和排列命令什么是浮动工具栏?当选择一个单元格的内容、右击一个单元格或一个单元格区域时浮动工具栏就出现了如图所示。浮动工具栏提供访问最常用的格式设置工具的快捷方式。如果需要阻止浮动工具栏出现可以单击Office按钮再单击“Excel选项”按钮然后在“常用”页面中清除“选择时显示浮动工具栏”复选框。如何简便地找到有用的组合键?按下一次Alt键就可以显示选项卡级别的快捷组合键(再按一次就可以隐藏)。按下命令所在选项卡的对应键就可以显示选项卡级别和命令级别的所有组合键。例如按AltM组合键显示“公式”选项卡。按AltHH组合键显示“填充颜色集合”以此类推。在Excel中能创建比在Excel中更大的工作表吗?Excel中工作表的大小限制为行和列。而Excel允许最多行和列。要验证此限制可以按下F键(可以定位任何单元格)键入如CAT单击“确认”按钮。Excel真的会定位到单元格CAT!Excel的设计还使针对大型工作表的计算更快了。事实上如果计算机有n个处理器那么相关的操作就会比用Excel时快n倍因为Excel可以利用多处理器的优势。还有一些其他的限制在Excel中得到改进例如:亿的颜色选择。不限制层数的条件格式。多至列的排序。允许层的“撤消”操作。一个公式中允许多至个字符。一个单元格中允许个字符。什么是公式自动完成?假设要输入一个公式来计算一组单元格的平均值。可以从键入“=AV”开始然后新的公式自动完成功能就会出现如图所示。不需要输入完整的“average”只需按下Tab键或双击“AVERAGE”Excel就会自动输入“=Average”到公式中。如果使用区域名称(参见第章)或新的表格功能(参见第章)将会真正感受到公式自动完成功能带来的好处。图浮动工具栏图公式自动完成功能什么是Office按钮?前文已经简单地讨论过Office按钮。在单击Office按钮后出现的菜单中可以实现以下功能。实现文件级别的主要任务如保存、关闭、打开和打印。自定义Excel的各个方面(通过单击“Excel选项”按钮)。安装Excel加载项(单击“Excel选项”按钮显示“加载项”页面)。什么是主题?主题可以控制工作表的颜色、字体和特殊效果。主题作用于整个工作簿。要选择一个主题只需选择“页面布局”选项卡然后在“主题”组中单击“主题”按钮。Excel提供许多主题选择图显示了一部分主题。当鼠标指向某一主题时Office的实时预览功能会显示选择这个主题后表格的外观。还有一个选择就是通过在颜色、字体、效果列表中选择创建自定义主题并保存起来供以后使用所有自定义主题都会显示在“自定义”目录中。图主题的选择什么是SmartArtSmartArt提供许多优雅的图样和效果这些图样已经超出了常见的圆形、矩形和箭头样式。想看SmartArt如何工作只需打开一个新的工作簿在“插入”选项卡的“插图”组中单击“SmartArt”按钮将看到如图所示的选项。图SmartArt选项我们选择第个选项然后在每个块中输入达拉斯小牛队上场队员的名字结果如图所示。图SmartArt示例如何改变一个工作表的显示比例?在Excel中通过移动屏幕右下角的显示比例滑块(如图所示)可以轻松地放大或缩小工作表。图显示比例滑块在打印电子表格前有什么新的查看方式?“视图”选项卡中的“工作簿视图”组(如图所示)提供了可用的视图。图工作簿视图选项“普通”视图是常用的工作表视图。“页面布局”视图显示单独的每一页允许增加页眉和页脚调整页边距等。“分页预览”视图显示并允许改变分页。如何同时显示一个工作簿的多个副本?假设工作簿包含一年中每一个月的工作表。如果要实现一些涉及不同月份的计算那么可以在屏幕上同时看到工作簿的不同工作表会很有帮助。要看到工作簿的多个视图需要单击“视图”选项卡再重复单击“新窗口”按钮直到得到足够多的电子表格的视图(例如如果需要个视图就单击“新窗口”按钮两次)。然后单击“全部重排”按钮选择这些副本的排列方式。在每个窗口里面显示不同的工作表会使开发涉及多个工作表的公式变得更简单。如何让没有安装Excel的用户使用Excel文件?如果用户有Office、Office、OfficeXP或Office只需在保存文件时选择Excel工作簿文件格式即可。如果用户使用Office则以Excel工作簿文件格式存储。Excel默认的文件格式是“Excel工作簿”创建的文件以xlsx为扩展名。注意:如果使用了Excel的新功能工作簿与Excel的早期版本可能不会完全兼容。要确定工作簿是否兼容早期版本可以运行Excel的兼容性检查器。单击Office按钮指向“准备”然后选择“运行兼容性检查器”选项。如果工作簿包含如图所示的SmartArt兼容性检查器会通知我们该SmartArt与早期版本不兼容如图所示。图兼容性检查器其他变化?我们在本章讨论的Excel新增功能不属于业务建模和数据分析范畴所以我们只是对这些功能加以简单介绍。而下面的Excel的新功能对数据分析和业务建模非常重要我们将详细讲述。创建区域名称的方法已经大为改进详情参见第章。条件格式的选择有了极大扩展。新的强调视觉冲击的条件格式选项令人惊奇它有助于更好地理解数据(数据条、图标和色阶)。第章将详细讨论条件格式。排序和筛选功能有了很大改进。甚至可以实现以单元格或字体的颜色来排序!Excel还使移除重复数据变得容易详情参见第章和第章。新的“表”功能将给电子表格建模带来变革。合理使用“表”功能可以在为工作簿添加新的数据时自动更新公式、格式及图表。第章将详细描述“表”功能。数据透视表已经修订改进了第章对这个重要的数据分析功能有详细的讨论。Excel图表的外观有了改进这一点可以在翻看书中的Excel图表时看出。新的“IFERROR”功能(参见第章)将使修改可怕的#REF和#NA出错信息变得更容易这些出错信息常常把电子表格计算变成一场噩梦!新的COUNTIFS(参见第章)和SUMIFS及AVERAGEIFS(参见第章)将使汇总数据集变得更简单。第章区域名称第章区域名称如果对亚利桑那州、加利福尼亚州、蒙大拿州、纽约州和新泽西州的销量进行汇总可以使用像AZCAMTNYNJ这样的计算汇总公式代替SUM(A:A)以得到正确答案吗?公式AVERAGE(A:A)的功能是什么?工作簿范围内的名称与工作表范围内的名称有什么区别?我确实开始喜欢使用区域名称。我在很多工作簿中都定义了区域名称但是这些区域名称在公式中没有显现如何才能使这些区域名称在以前创建的公式中显现呢?如何便捷地选取命名的区域?如何把一个所有区域名称(及其所代表的单元格)的列表粘贴到工作表中?在使用工作表时可能用过像SUM(A:A)这样的公式在这种情况下必须找出单元格A:A中的内容。如果单元格A:A中的内容是美国各个州的销量那么是不是使用公式SUM(USSales)更容易理解呢?本章将介绍如何定义一个单元格名称或一个单元格区域的名称还会讲述如何在公式中使用区域名称。如何创建命名区域?有种方法可以创建命名区域。在“名称框”中输入一个区域名称。在“公式”选项卡的“定义的名称”组中单击“根据所选内容创建”按钮。在“公式”选项卡的“定义的名称”组中单击“名称管理器”按钮或单击“定义名称”按钮。使用“名称框”来创建区域名称“名称框”就位于A列标签的正上方如图所示(显示编辑栏后才能看到“名称框”)。要在“名称框”中创建区域名称只要选取要命名的单元格或单元格区域单击“名称框”输入要使用的名称按下Enter键就创建了区域名称。单击“名称框”中的下拉箭头就可以显示在本工作簿中的所有区域名称。也可以用按下F键打开“粘贴名称”对话框来显示所有区域名称。当从“名称框”中选择一个区域名称时Excel会自动选择跟区域名称对应的单元格这样就能确认选择了想要命名的单元格或单元格区域。图可以选择要命名的单元格区域在“名称框”中输入区域名称例如假设想将F为命名east、将F命名为west(参见图和示例文件Eastwestxlsx)只要选择单元格F在“名称框”中输入east按下Enter键再选择单元格F在“名称框”中输入west按下Enter键。现在如果在其他单元格中引用单元格F就可以用=east而不是=F。这意味着不管什么时候在公式中见到引用“east”将会插入单元格F中的数据。假设要将一个矩形单元格区域(如A:B)命名为Data只要选择单元格区域A:B在“名称框”中输入Data按下Enter键。现在公式=AVERAGE(Data)意味着对单元格区域A:B中的数值求平均值(参见示例文件Dataxlsx和图)。图将F命名为east将F命名为west图将单元格区域A:B命名为Data有时候我们希望命名一组由不连续的矩形区域组成的单元格例如在图和示例文件Noncontigxlsx中我们想把B:C、E:G、B:C这些区域命名为Noncontig。要进行这样的命名先选择三个区域中的一个区域(可以选B:C)按下Ctrl键的同时选择其他两个区域(E:G和B:C)然后松开Ctrl键在“名称框”中输入Noncontig按下Enter键即可。在任何公式中使用Noncontig将指向单元格区域B:C、E:G和B:C的内容。例如在单元格E中输入=AVERAGE(Noncontig)将得出(因为区域内的个数值之和是除以得到)。图命名不连续的单元格区域用“根据所选内容创建”创建命名区域工作簿Statesxlsx包含美国个州的三月份销量图显示了这个数据的一个子集我们要以每个州的缩写来命名B:B中的每个单元格。要完成这个工作选择区域A:B单击“公式”选项卡的“定义的名称”组中的“根据所选内容创建”按钮(如图所示)然后选中“最左列”复选框如图所示。图以各州的缩写来命名各州的销量当引用某个单元格时可以使用缩写而不是使用列标和行号。图“根据所选内容创建”按钮图选中“最左列”复选框Excel现在就把选择区域内第一列的名称与所选区域内第二列的单元格联系起来于是B被指定为名称ALB命名为AK以此类推。如果是在“名称框”中创建这些名称会很枯燥乏味!可以单击“名称框”中的下拉箭头来确认已经创建所需的区域名称。使用“名称管理器”选项来创建区域名称如果单击“公式”选项卡中的“名称管理器”按钮然后选择“新建”会显示如图所示的“新建名称”对话框。假设要将单元格区域A:B命名为range(区域名称不区分大小写)只需在“名称框”中输入range然后指向区域或在“引用位置”中输入=A:B单击“确定”按钮即可完成。此时“新建名称”对话框如图所示。图创建区域名称前的“新建名称”对话框图创建区域名称后的“新建名称”对话框如果单击“范围”下拉箭头可以选择范围是工作簿还是工作簿中的某一个工作表我们稍后讨论这个问题目前我们选择默认的整个工作簿如果愿意还可以给区域名称加上注释。名称管理器如果单击“名称”中的下拉箭头会在“名称框”中看到区域名称range(还有其他创建的区域名称)在Office系统中有一个简单办法来编辑或删除区域名称而这个方法是Office早期版本中没有的。只要选择“公式”选项卡然后单击“名称管理器”按钮打开“名称管理器”对话框就会看到一个所有区域名称的列表例如对于文件Statesxlsx“名称管理器”对话框如图所示。图Statesxlsx的“名称管理器”对话框要编辑任何区域名称只要选择该区域名称再单击“编辑”按钮就可以改变区域名称、区域引用的单元格或区域名称的作用范围。如要删除一组区域名称首先选择要删除的区域名称。如果这些区域名称是连续排列的只要选择第个名称然后按下Shift键再选择最后一个名称如果这些名称不是连续排列的可以任意选择一个要删除的名称再按下Ctrl键不动然后选择其他所有要删除的名称。最后单击“删除”按钮来删除所选择的区域名称。下面介绍一些使用区域名称的具体示例。如果对亚利桑那州、加利福尼亚州、蒙大拿州、纽约州和新泽西州的销量进行汇总可以使用像AZCAMTNYNJ这样的计算汇总公式代替SUM(A:A)以得到正确答案吗?让我们回到文件Statesxlsx其中我们指定了每个州的缩写作为每个州销量的区域名称如果我们想知道阿拉巴马州、阿拉斯加州、亚利桑那州和阿肯色州的总销量我们可以使用公式SUM(B:B)也可以指向单元格B、B、B和B公式会是如下方式:=ALAKAZAR而后一种方法的公式显然更容易理解。使用区域名称的另外一个示例参见文件Historicalinvestxlsx(如图所示)表中包含一年的各种投资的收益率包括Stocks(股票)、TBills(短期国库券)、TBones(长期国库券)(部分行没有显示)。图历史投资数据选择单元格区域B:D后选择“公式”|“根据选择内容创建”然后选择“首行”来创建名称B:B的区域名称为StocksC:C的区域名称为TBillsD:D的区域名称为TBonds现在我们不再需要知道数据的位置了例如在B中键入=AVERAGE(我们可以按下F“粘贴名称”对话框就出现了如图所示。然后在“粘贴名称”列表中选择Stocks单击“确定”按钮再键入右括号公式=AVERAGE(Stocks)就可以计算出Stocks的平均收益率()。这种方法的美妙之处在于即使我们记不住数据的位置仍然可以在工作簿的任何地方使用股票(Stocks)收益数据!如果我们不提到令人兴奋的Excel新的自动完成功能就太疏忽了当你键入=AVERAGE(T的时候Excel将会自动显示一个所有以T开头的区域名称列表只需单击TBills就可完成区域名称的输入如图所示。AVERAGE(A:A)的功能是什么?如果在公式中用到列的名称(以A:A、C:C等形式)Excel将把整个列作为一个命名区域。例如输入公式=AVERAGE(A:A)会对所有A列中的数字求平均值。如果需要频繁输入新的数据到列中使用针对全部列的区域名称很有用处。例如如果A列包含产品每月销量每月都会输入新的销量数据公式就可以给出最新的销量平均值。但必须提醒的是如果在A列中输入公式=AVERAGE(A:A)将得到一个循环引用的信息因为单元格的数值中包含了含有计算销量平均值公式的单元格第章将介绍如何解决循环引用。同样键入公式=AVERAGE(:)将计算第一行的平均值。图可以使用“粘贴名称”对话框来添加一个区域名称到公式中图自动完成功能示例工作簿范围内的名称和工作表范围内的名称有什么区别?文件Sheetnamesxlsx有助于我们理解工作簿范围内的区域名称和工作表范围内的区域名称的不同在使用“名称框”创建名称时该名称是工作簿范围的。例如用“名称框”将sheet的单元格区域E:E命名为sales而这些单元格的数值分别是、和那么在任何工作表中输入公式=SUM(sales)都将得到结果因为在“名称框”中创建的名称都是具有工作簿范围的所以在工作簿中任何地方引用名称sales(具有工作簿范围)都会指向sheet的E:E在任何工作表中输入公式=SUM(sales)都会得到结果这是因为在工作簿任何地方Excel都会把sales指向sheet的E:E。现在假设在sheet的E:E中分别键入、和在sheet的E:E中键入、和。下一步到名称管理器中把sheet的E:E命名为jam并且定义它的范围是sheet然后再切换到sheet在名称管理器中将E:E命名为jam并定义它的范围是sheet“名称管理器”对话框将如图所示。现在在每个工作表中输入公式=SUM(jam)会得到什么结果呢?在sheet中=SUM(jam)将把sheet的E:E求和因为这些单元格的数值是、和得到结果。在sheet中=SUM(jam)把sheet的E:E求和=。然而在sheet中公式=SUM(jam)会出现一个#NAME错误因为没有在sheet中定义名称jam!如果在sheet中键入公式=SUM(Sheet!jam)Excel将识别出代表sheet的单元格区域E:E的工作表级别的名称得到结果=。因此以工作表名称开始后面使用!将允许我们引用一个不在本工作表范围内的名称。图拥有工作表范围名称和工作簿范围名称的“名称管理器”对话框我确实开始喜欢使用区域名称。我在很多工作簿中都定义了区域名称但是这些区域名称在公式中没有显现如何才能使这些区域名称在以前创建的公式中显现呢?让我们看一下文件Applynamesxlsx如图所示。图如何在公式中应用区域名称在单元格F中已经输入产品的价格(price)产品需求(demand)在F中等于*F单位成本(unitcost)和固定成本(fixedcost)已经在单元格F和F中分别输入利润(profit)在F中用公式=F*(FF)F计算我们单击“公式”选项卡中的“根据所选内容创建”按钮然后选中“最左列”复选框将F命名为priceF命名为demandF命名为unitcostF命名为fixedcostF命名为profit。我们想让这些名称在F和F的公式中显示。要应用这些区域名称首先选择想要应用这些名称的区域(此例中为F:F)在“公式”选项卡的“定义的名称”组中单击“定义名称”下拉箭头然后选择“应用名称”。突出显示要使用的名称单击“确定”按钮可以看出像我们希望的那样F现在包含公式=*price而F包含公式=demand*(priceunitcost)fixedcost。如何便捷地选取命名的区域?如果想选取一个命名区域的单元格只要按下Ctrl*组合键就可以选取整个区域。如何把一个所有区域名称(及其所代表的单元格)的列表粘贴到工作表中?按下F键来显示“粘贴名称”框单击“粘贴列表”按钮一个所有区域名称的列表和每个名称所代表的单元格都粘贴到工作表中粘贴位置从当前的单元格开始。备注Excel不允许使用字母r和c作为区域名称。如果使用“根据所选内容创建”来创建区域名称并且名称中包含空格Excel会插入下划线“”来填充空格。例如名称Product将被创建成Product。区域名称不能以数字开始并且不能像单元格引用。例如和A都不能作为区域名称由于Excel可以超过列不能使用cat之类的区域名称因为存在一个CAT单元格。如果试图将一个单元格命名为CATExcel会提示是无效名称也许你所能做的也就是把单元格命名为cat。区域名称中可用的符号只有两个就是点()和下划线()。练习文件Stockxlsx包含通用汽车和微软的每月股票收益命名包含每只股票每月收益的区域计算每只股票月平均收益。打开一个工作表将包含A:B和A:B的区域命名为Red。利用给出的任何两个城市的经纬度文件Citydistancexlsx计算任意两个城市间的距离。给每个城市的经度和纬度定义区域名称然后确保这些名称出现在计算总距离的公式中。第章LOOKUP函数第章LOOKUP函数如何写一个基于收入计算税率的公式?已知产品ID如何查找产品价格?假设产品价格是随时间变化的已知产品的销售时间如何编写公式来计算产品的价格?LOOKUP函数的语法LOOKUP函数可以在工作表中“查找”数值。Excel可以实现垂直查找(用VLOOKUP函数)和水平查找(用HLOOKUP函数)。在垂直查找中查找操作从工作表的第一列开始在水平查找中查找操作从工作表的第一行开始。因为大多数的公式用到的查找都是垂直查找我们将集中讲述VLOOKUP函数。VLOOKUP函数的语法VLOOKUP函数的语法如下方括号()内为可选参数。VLOOKUP(lookupvalue,tablerange,columnindex,rangelookup)lookupvalue是要在工作表第一列中查找的值。tablerange是包含整个查找表格的区域这个表格区域包括尝试匹配查找值的第一列还包括任何我们将要在其中查找公式结果的列。columnindex是取得LOOKUP函数结果的表格区域内的列标。rangelookup是可选参数。rangelookup允许指定是精确匹配还是近似匹配如果rangelookup为真(TRUE)或省略那么表格区域内的第一列一定是按数字升序排列的。如果rangelookup为真(TRUE)或省略并且在表格区域内第一列找到了精确匹配的值Excel将返回精确匹配行的值如果rangelookup为真(TRUE)或省略而精确匹配的值不存在Excel就返回第一列中比要查找值小的最大值如果rangelookup为假(FALSE)并且在表格区域中第一列找到精确匹配的值Excel将返回表中相匹配行的值如果没有精确匹配的值则返回#NA(没有找到)。注意rangelookup参数为表示TRUE表示FALSE。HLOOKUP函数的语法在HLOOKUP函数中Excel会试图在表格区域的第一行(而不是第一列)来定位要查找的数值。HLOOKUP函数只是把VLOOKUP函数语法的“column”换成“row”。下面研究一些查找(LOOKUP)函数的有趣示例。如何写一个基于收入计算税率的公式?下面的例子显示了表格区域的第一列数值按升序排列时VLOOKUP是如何工作的。假设税率根据收入来确定如下表所示。Incomelevel(收入水平美元)Taxrate(税率)~~~以上关于编写根据收入确定税率的公式的示例请参考文件Lookupxlsx如图所示。图使用LOOKUP函数来计算税率表中第一列已经按升序排序首先在D:D中先输入相关的信息(税率和临界值)将区域D:E命名为lookup建议总是命名要用到的表格区域这样就不需要记住表格区域的具体位置并且当复制包含LOOKUP函数的公式时要查找的区域总是正确的。为阐述LOOKUP函数如何工作我在区域D:D中输入一些收入数据通过在E:E中依次复制公式VLOOKUP(D,lookup,,TRUE)就计算出D:D中列出的收入的税率检查一下在E:E中LOOKUP函数是如何工作的注意因为在公式中columnindex是答案总是从所选表格区域的第二列得到。D中的得到的结果是#NA这是因为比表格区域内第一列的最小收入值还要小如果想在收入为时得到税率是只要把D中的替换成或更小的值即可。D中的正好与表格区域内第一列的一个数值完全一样所以函数返回税率是。D中的 在表格区域的第一列中没有完全相符的值在这种情况下函数找到比 小的最大值就是 函数返回第二列中跟收入 对应的税率。D中的 在表格区域的第一列中也没有完全相符的值函数就找到比 小的最大值返回第二列中跟收入对应的税率。D中的 也不能完全匹配表格区域第一列中的某值函数在表格区域第一列中找到比 小的最大值返回第二列中与收入相对应的税率。在F:F中我们把rangelookup参数值从“TRUE”改为“FALSE”从F复制公式VLOOKUP(D,lookup,,FALSE)到F:F。因为D的值与表格区域第一列的收入完全相符得到税率是。而F:F的其他项全部显示#NA这是因为D:D中的其他收入都与表格区域第一列中的数值不完全相符。已知产品ID如何查找产品价格?通常表格区域第一列包含的数值并不是按升序排列的。例如表格区域第一列为产品ID列表或雇员姓名。从我教几千名金融分析员的经验来看我发现在表格区域第一列的数值不是升序排列时很多人不知道怎样处理LOOKUP函数在这种情况下只需要知道一个简单的规则:就是设定rangelookup参数为FALSE。以下是一个例子。在文件Lookupxlsx(见图)中可以看到种产品的价格按照产品ID排列怎样写一个公式才能通过产品ID得到产品价格呢?图从产品ID查找价格当表格区域不是按升序排列时在LOOKUP函数中使用FALSE作为最后一个参数许多人会像我在单元格I中那样输入公式:VLOOKUP(H,lookup,)然而要注意的是当省略第个参数(rangelookup)时此参数会被认为是TRUE。因为在表格区域lookup(H:H)中产品ID不是按照字母顺序排列的所以返回一个错误答案()。如果在单元格I中输入公式VLOOKUP(H,lookup,,FALSE)就会返回正确的答案()。也可以在公式中使用FALSE通过雇员的姓氏或ID找到雇员的薪水。顺便说一下可以看到在图中我们隐藏了A~G列在Excel中要隐藏某些列可以先选择要隐藏的列单击“开始”选项卡在“单元格”组中单击“格式”按钮选择“隐藏和取消隐藏”然后再选择“隐藏列”。假设产品价格是随时间变化的已知产品的销售时间如何编写公式来计算产品的价格?假设产品价格取决于产品出售的日期如何在公式中使用LOOKUP函数得到正确的产品价格呢?假设产品的价格如下表所示:Datesold(销售日期)Price(价格美元)年月-月年月-月年月-月我们要写一个公式来确定在年任何时期出售的产品的正确价格考虑到多样性我们来使用HLOOKUP函数我已经在表格区域的第一行放入产品价格变化的日期参见文件Datelookupxlsx如图所示。图使用HLOOKUP函数确定一个根据销售日期变化的价格从C复制公式HLOOKUP(B,lookup,,TRUE)到C:C这个公式会试图比较B列中的日期和区域B:D的第一行:对任何在到范围内的日期LOOKUP函数会找到并且返回在B中的价格对任何在到范围内的日期LOOKUP函数会找到并且返回在C中的价格对任何晚于的日期LOOKUP函数会找到并且返回在D中的价格。练习文件Hrxlsx给出了雇员ID、薪水和工作年限。写一个公式通过ID号码得到雇员薪水再写一个公式通过雇员ID得到雇员的工作年限。文件Assignxlsx给出了分配给个组的工人还给出了每个工人对每个组的适宜度(~)。写一个公式得到每个工人对所分配组的适宜度。假设要在电视体育节目中推广微软的产品购买的广告越多单个广告的价格就越低如下表所示。Numberofads(广告数)Priceperad(广告单价美元)~~~以上例如购买个广告每个广告需付美元购买个广告每个广告只需要付美元。写一个公式得到购买任意多个广告的总花费。假设要在一个受欢迎的电视音乐节目中推广微软的产品购买第一组广告是一个价格购买的广告越多每个广告的花费就越低如下表所示。Adnumber(广告数)Priceperad(广告单价美元)~~~以上例如如果购买个广告前个广告需要各付美元而其他个各付美元。如果购买个广告前个广告需要各付美元另外个需要各付美元其余的个每个只需付美元。写一个公式来得到购买任意数目广告的花费。(提示:在表格区域中可能至少需要列而且公式也许要包含两个LOOKUP函数。)从银行贷款年、年、年或年的年利率显示在下表中。Durationofloan(贷款年限年)Annualloanrate(贷款的年利率)如果贷款年限为~年但是没有在表中列出贷款利率会根据表格中利率插值得来。例如:贷款年因为年位于年到年区间的处年利率将如下计算:写一个公式来得到贷款年限为~年的任意值时的年利率。任何两个美国城市间距离(夏威夷和阿拉斯加除外)可以用以下公式来近似计算:文件Citydataxlsx包含指定美国城市的经纬度创建一个表给出所选城市中任意两个之间的距离。在文件Pinevalleyxlsx中第个工作表包括在PineValley大学工作的雇员的薪水第个工作表包含雇员的年龄第个工作表包含雇员的工作年限。创建第个工作表来包含所有雇员的薪水、年龄和工作年限。文件Lookupmultiplecolumnsxlsx包含一个电器商店销售的相关信息在B中输入了一个销售员的名字。在C中编写公式然后复制到D:F公式要提取每个销售员的收音机销量并存放到C提取电视销量并存放到D提取打印机销量并存放到E提取CD销量并存放到F。第章INDEX函数第章INDEX函数有一个美国城市间距离的列表如何编写函数来返回其中两个城市(如西雅图和迈阿密)间的距离?如何编写公式来引用包含每个城市与西雅图之间距离的整个列?INDEX函数的语法INDEX函数可以返回一组数字中任意行列中的项最常用的INDEX函数语法如下:INDEX(Array,RowNumber,ColumnNumber)举例说明公式INDEX(A:D,,)会返回A:D中的第二行第三列的项也就是单元格C中的内容。有一个美国城市间距离的列表如何编写函数来返回其中两个城市(如西雅图和迈阿密)间的距离?文件Indexxlsx(如图所示)包含个美国城市间的距离含有距离数据的区域C:J命名为distances。图可以使用INDEX函数计算城市间距离假设在一个单元格中输入波士顿到丹佛之间的距离因为波士顿起始的距离在区域distances的第行而到丹佛的距离在区域中的第列正确的公式是:INDEX(distances,,)得到的结果是波士顿和丹佛之间的距离为英里。同样要知道西雅图和迈阿密之间的距离(更远一些)可以用公式INDEX(distances,,)得出西雅图和迈阿密之间的距离是英里。假设西雅图超音速篮球队着手一次巡回赛要在凤凰城、洛杉矶、丹佛、达拉斯和芝加哥进行比赛最后返回西雅图。我们能否轻松地计算出他们的总行程?如图所示只需按出访顺序列出他们要访问的城市()开始和结束都是西雅图复制公式INDEX(distances,C,C)到D:DD中的公式计算出从西雅图到凤凰城(城市号码为)的距离D中的公式计算出从凤凰城到洛杉矶的距离以此类推。超音速队巡回赛的总旅程为英里。只是为了好玩我用INDEX函数算出迈阿密热队在NBA季度赛中的总旅程比其他队都要多。图西雅图超音速队巡回赛的总旅程如何编写公式来引用包含每个城市与西雅图之间距离的整个列?INDEX函数使引用数组的整行或整列变得很简单。如果设置行数(rownumber)为INDEX函数将引用整列如果设置列数(columnnumber)为INDEX函数会引用整行。例如要计算所有城市到西雅图的总距离可以用下面两个公式中的一个:SUM(INDEX(distances,,)SUM(INDEX(distances,,)第个公式会累加distances数组中第行(行)的数据第个公式会累加distances数组中第列(列J)的数据。不管用哪种我们发现从西雅图出发到其他城市的总距离都是英里如图所示。练习用INDEX函数分别计算洛杉矶与凤凰城及丹佛与迈阿密之间的距离。用INDEX函数计算达拉斯到所有其他城市的总距离。马克库班要带达拉斯小牛队去芝加哥、丹佛、洛杉矶、凤凰城和西雅图比赛他们的总旅程会是多少?文件Productxlsx包含种产品的每月销量使用INDEX函数来计算产品在月的销量计算月份的总销量。文件Nbadistancesxlsx显示了任意两个NBA赛场的距离假设从亚特兰大开始按列表顺序访问每个赛场再回到亚特兰大总旅程是多少?第章MATCH函数第章MATCH函数已知几种产品的月销量如何编写公式得到一个产品某个月份的销量?例如月份产品的销量是多少?已知棒球队员的薪水列表如何编写公式得到薪水最高的队员?薪水排名第五的队员是谁?已知一个投资项目的年现金流量如何编写公式得出需要多少年才能收回初始投资额?假设有一个行的工作表包括个名字。此时需要找到JohnDoe这个名字而这个名字是在表中某个位置(并且只出现一次)是否能有一个公式来返回JohnDoe所在的行数?Excel的MATCH函数用于在一个已知数组里定位与给出的字符串或数字第个相符的位置。在需要知道一个数值的位置而不是某个单元格内的值的情况下应该使用MATCH函数而不是LOOKUP函数MATCH函数的语法结构如下:MATCH(lookupvalue,lookuprange,matchtype)在下面的解释中我们假设要查找区域的所有单元格都是在同一列中。在MATCH函数的语法结构中:lookupvalue是要在查找区域内查找的值。lookuprange是要查找匹配值的查找区域。matchtype=要求查找区域内的数值是以升序排列的MATCH函数会返回包含在区域内小于或等于查找值的最大值所在行的位置(相对于查找区域顶部的位置)。matchtype=要求查找区域内的数值是以降序排列MATCH函数会返回包含在区域内大于或等于查找值的最后一行的位置(相对于查找区域顶部的位置)。matchtype=会返回查找区域中第个与查找值完全相符的行的位置(我们将在第章讲述如何找第个或第个相符值所在行的位置)。当完全相符的值不存在并且matchtype=Excel会返回#NA。大多数的MATCH函数应用都用matchtype=但是如果matchtype没有写则会默认为matchtype=。因此当查找区域没有排序时使用matchtype=这是最常见的情况。文件Matchexxlsx(如图所示)包含了MATCH函数语句结构的个例子。在单元格B中公式MATCH("Boston",B:B,)返回值为因为在区域B:B的第一行就有值Boston字符型的值必须用双引号("")括起来。在B中公式MATCH("Phoenix",B:B,)返回值为因为单元格B(B:B区域中的第个)是找到Phoenix的第个单元格。在E中公式MATCH(,E:E,)返回值为因为在E:E区域内小于或等于的最大值在单元格E(查找区域的第个)中。在单元格G中公式MATCH(,G:G,)返回结果为这是因为在G:G区域中最后一个大于或等于的值在单元格G(查找区域内的第个)中。图使用MATCH函数在一个区域内找到某个值的位置MATCH函数也可以适用于不完全匹配的情况。例如公式MATCH("Pho*",B:B,)返回值为星号作为通配符也就是说Excel会在B:B区域搜索第个以Pho开始的字符串顺便说一下在第章的价格查找练习中用公式VLOOKUP("x*",lookup,)也会返回产品X的价格()。如果查找区域局限在一行之中Excel会返回在查找区域内找到第个相符值的相对位置从左到右如下例所示在与其他Excel函数(如VLOOKUP、INDEX或MAX)一起使用时MATCH常常很有用处。已知几种产品的月销量如何编写公式得到一个产品某个月份的销量?例如月份产品的销量是多少?文件Productlookupxlsx(如图所示)列出了月-月种NBA摇头玩偶的销量如何编写公式计算某个产品在某个月份的销量?秘诀就是:使用MATCH函数找到产品所在行的位置再用MATCH函数找到相对应的月份的列的位置然后使用INDEX函数来返回某个月份某个产品的销量。图MATCH函数可以和其他函数(如INDEX和VLOOKUP)结合使用将包含玩偶销量的区域B:G命名为Sales在单元格A中输入要查找的产品在B中输入月份在C中使用公式MATCH(A,A:A,)来确定区域中哪一行包含玩偶Kobe的销量在D中使用公式MATCH(B,B:G,)确定哪一列包含月份销量。现在有了包含所需数据的行数和列数就可以在单元格E中使用公式INDEX(Sales,C,D)来得到需要的销量数据更多关于INDEX函数的信息参见第章。已知棒球队员的薪水列表如何编写公式得到薪水最高的队员?薪水排名第的队员是谁?文件Baseballxlsx(如图所示)列出了在赛季付给名主要棒球队员的薪水数据没有以薪水排序需要编写公式返回薪水最高和第高的队员名字。要找到薪水最高的队员的名字我们如下操作。用MAX函数确定最高薪水是多少。用MATCH函数来确定薪水最高的队员所在行数。用VLOOKUP函数(用包含该队员薪水的“行”为参数)来查找这个队员的名字。我们把包含队员薪水的区域C:C命名为salaries把在VLOOKUP函数中用到的区域(A:C)命名为lookup。图本例使用MAX、MATCH和VLOOKUP函数来找到和在列表中显示最高值在C中先用公式MAX(salaries)得到最高薪水是接下来在C中使用公式MATCH(C,salaries,)来确定拥有最高薪水的队员的位置使用matchtype=是因为薪水不是以升序或降序排列的我们得到行的队员有最高的薪水。最后在C中用公式VLOOKUP(C,lookup,)在查找区域第二列中找到队员名字。一点不奇怪我们发现艾力克斯罗德里格斯是年薪水最高的球员。要查找薪水第高的队员

热点搜索换一换

用户评论(0)

0/200

精彩专题

上传我的资料

每篇奖励 +2积分

资料评价:

/63
1下载券 下载 加入VIP, 送下载券

意见
反馈

立即扫码关注

爱问共享资料微信公众号

返回
顶部