在回顾了基本函数、单元格引用以及日期和时间函数之后，我们现在深入了解Microsoft Excel的一些更高级的功能。我们提出了解决财务、销售报告、运输成本和统计方面的经典问题的方法。

这些功能对企业、学生和那些只想了解更多信息的人都很重要。

VLOOKUP和HLOOKUP

下面是一个示例来说明垂直查找(VLOOKUP)和水平查找(HLOOKUP)功能。这些函数用于将数字或其他值转换为可理解的内容。例如，可以使用VLOOKUP获取零件号并返回项目描述。

为了研究这一点，让我们回到第4部分中的“决策者”电子表格，Jane正在尝试决定穿什么去学校。她对自己的穿着不再感兴趣，因为她找到了一个新男友，所以她现在会随意穿衣服和鞋子。

在简的电子表格中，她列出了垂直栏目中的服装和水平栏目中的鞋子。

她打开电子表格，函数RANDBETWEEN(1，3)生成一个介于1和3之间的数字，对应于她可以穿的三种服装。

她使用函数RANDBETWEEN(1，5)在五种鞋中挑选。

因为Jane不能穿数字，所以我们需要将其转换为名称，所以我们使用查找函数。

我们使用VLOOKUP函数将装备编号转换为装备名称。HLOOKUP将鞋号转换为排中的各种类型的鞋子。

电子表格对服装的工作方式如下所示：

Excel会从1到3之间随机选择一个数字，因为她有三套服装可供选择。

接下来，公式使用=VLOOKUP(B11，A2：B4，2)将数字转换为文本，它使用随机数B11中的值在A2：B4范围内查找。然后，它根据第二列中列出的数据给出结果(C11)。

我们用同样的技巧挑选鞋子，只不过这次我们用VOOKUP而不是HLOOKUP。

示例：基本统计信息

几乎每个人都知道统计学中的一个公式-平均值-但还有另一个对商业很重要的统计数字：标准差。

例如，许多上过大学的人都为他们的SAT成绩而苦恼。他们可能想知道与其他学生相比，他们的排名如何。大学也想知道这一点，因为许多大学，特别是著名的大学，拒绝了SAT分数低的学生。

那么，我们或一所大学将如何衡量和解读SAT成绩呢？以下是5名学生的SAT成绩，从1，870分到2，230分不等。

需要了解的重要数字包括：

平均值-平均值也称为“平均值”。

标准差(std或σ)-此数字显示一组数字的分散程度。如果标准差很大，那么这些数字就相距很远，如果它是零，那么所有的数字都是相同的。你可以说标准差是平均值和观测值之间的平均差，即1，998和每个SAT分数之间的平均差。请注意，使用希腊语符号sigma“σ”缩写标准差是很常见的。

百分位数-当一个学生获得高分时，他们可以吹嘘自己在前99个百分位数或类似的位置。“百分位数排名”是指分数低于某一特定分数的百分比。

标准差和概率是紧密相连的。您可以说，对于每个标准差，该数字在该标准差数内的概率或可能性为：

正如你所看到的，任何SAT分数超过3个STD的可能性几乎为零，因为99.73%的分数在3个STD以内。

现在让我们再看一遍电子表格，并解释它是如何工作的。

现在我们来解释这些公式：

=平均值(B2：B6)

B2：B6范围内所有分数的平均值。具体地说，就是所有分数的总和除以参加测试的人数。

=STDEV.P(B2：B6)

B2：B6范围内的标准偏差。“.P”表示STDEV.P用于所有分数，即，整个群体，而不仅仅是子集。

=PERCENTRANK.EXC($B$2：$B$6，B2)

这将根据SAT分数(在本例中为B2)计算B2：B6范围内的累计百分比。例如，83%的分数低于沃克的分数。

将结果绘制成图表

将结果放在图表中可以更容易地理解结果，此外，您还可以将其显示在演示文稿中，以便更清楚地表达您的观点。

学生们在横轴上，他们的SAT成绩以蓝色条形图的形式显示在从1600到2300的范围内(垂直轴)。

百分位数排名是从0到90%的右侧垂直轴，由灰色线条表示。

如何创建图表

创建图表本身就是一个主题，不过我们将简要说明上面的图表是如何创建的。

首先，选择要在图表中显示的单元格范围。在本例中是A2到C6，因为我们需要数字和学生的名字。

从“插入”菜单中选择“图表”->“推荐图表”：

计算机推荐使用“聚集柱，次轴”图表。“次轴”部分表示它绘制两个垂直轴。在这种情况下，这张图表就是我们想要的那张。我们不需要做其他任何事。

您可以使用四处移动图表并重新调整其大小，直到您将其调整为所需的大小和位置。一旦您感到满意，就可以将图表保存在电子表格中。

如果右键单击图表，然后单击“选择数据”，它会显示为该范围选择了哪些数据。

“推荐图表”功能通常使您不必处理诸如确定要包含哪些数据、如何分配标签以及如何分配左右垂直轴等复杂细节。

在“Select data Source”对话框中，单击“Legend Entries(Series)”下的“Score”，然后按“Edit”，并将其更改为“Score”。

然后将系列2(“百分位数”)更改为“百分位数”。

返回到您的图表，单击“图表标题”并将其更改为“SAT分数”。现在我们有了一张完整的图表。它有两个水平轴：一个表示SAT分数(蓝色)，另一个表示累积百分比(橙色)。

示例：运输问题

运输问题是一种称为“线性规划”的数学的经典例子。这使您可以在特定约束下最大化或最小化某个值。它在各种业务问题上都有很多应用，因此了解它的工作原理是很有用的。

在开始这个示例之前，我们必须启用“Excel求解器”。

启用规划求解加载项

选择“文件”->“选项”->“加载项”。在“加载项”选项的底部，单击“管理：Excel加载项”旁边的“转到”按钮。

在出现的菜单上，单击复选框以启用“求解器加载项”，然后单击“确定”。

示例：计算最低iPad发货成本

假设我们正在运送iPad，我们试图用尽可能低的运输成本填满我们的配送中心。我们与一家卡车运输和航空公司达成了一项协议，将iPad从上海、北京和香港运往如下所示的配送中心。

每台iPad的发货价格是从工厂到配送中心再到工厂的距离除以2万公里。例如，从上海到墨尔本有8，024公里，每台iPad的价格是8，024/20，000美元或0.4美元。

问题是，我们如何以尽可能低的成本将所有这些iPad从这三家工厂运往这四个目的地？

正如您可以想象的那样，如果没有一些公式和工具，可能很难计算出这一点。在这种情况下，我们总共要出货462，000台(F12)iPad。这些工厂的有限产能为500，250台(G12)。

在电子表格中，您可以看到它是如何工作的，我们在单元格B10中键入了1，这意味着我们要将1台iPad从上海运往墨尔本。由于这条路线的运输成本是每台iPad 0.40美元，所以总成本(B17)是0.40美元。

使用函数=SUMPRODUCT(成本，已发运)“成本”是范围B3：E5计算的数字。

和“已发货”范围为B9：E11：

SUMPRODUCT将“成本”乘以“已发货”范围(B14)。这就是所谓的“矩阵乘法”。

为了使SUMPRODUCT正常工作，成本和发货这两个矩阵的大小必须相同。您可以通过增加成本并以零值运送列和行来绕过此限制，从而使数组大小相同，并且不会影响总成本。

使用解算器

如果我们所要做的就是将矩阵的“成本”乘以“装运”，这将不会太复杂，但是我们还必须处理那里的约束。

我们必须装运每个配送中心所需的货物。我们将该常量放入求解器中，如下所示：$B$12：$E$12>=$B$13：$E$13。这意味着发货内容的总和(即单元格$B$12：$E$12)必须大于或等于每个配送中心所需的值($B$13：$E$13)。

我们的出货量不能超过我们的产量。我们这样写约束：$F$9：$F$11<=$G$9：$G$11。换句话说，我们从每个工厂发运的$F$9：$F$11不能超过(必须小于或等于)每个工厂的能力：$G$9：$G$11。

现在转到“数据”菜单并按下“求解器”按钮。如果“求解器”按钮不在那里，您需要启用求解器加载项。

键入前面详细说明的两个约束，然后选择“Shipping”范围，这是我们希望Excel计算的数字范围。另外，选择默认算法“Simplex LP”，并指出我们希望“最小化”单元格B15(“总运输成本”)，其中显示为“设置目标”。

按“解决”，Excel将结果保存到电子表格中，这正是我们想要的。您还可以将其保存，以便可以处理其他场景。

如果计算机说它找不到解决方案，那么你做了一些不合逻辑的事情，例如，你可能要求的iPad数量超过了工厂的产量。

在这里，Excel说它找到了一个解决方案。按“确定”保留解决方案并返回电子表格。

示例：净现值

一家公司如何决定是否投资一个新项目？如果“净现值”(NPV)为正，他们就会投资。这是大多数金融分析师采取的标准做法。

例如，假设Codelco矿业公司想要扩大安迪纳斯铜矿。确定是否推进项目的标准方法是计算净现值。如果净现值大于零，那么在给定两个投入(1)时间和(2)资金成本的情况下，项目将是有利可图的。

简而言之，资金成本是指如果他们把钱存在银行，能赚多少钱。你用资本成本将现金价值折现到现值，换句话说，五年后100美元可能是今天的80美元。

在第一年，我们预留了4500万美元作为资本，为这项计划提供资金。会计师们已确定他们的资金成本是百分之六。

当他们开始开采时，随着公司发现并出售他们生产的铜，现金开始流入。显然，他们开采的越多，赚的钱就越多，他们的预测显示，他们的现金流不断增加，直到每年达到900万美元。

13年后，净现值为3945，074美元，因此该项目将会盈利。根据金融分析师的说法，“回收期”是13年。

创建数据透视表

“数据透视表”基本上就是一个报表。我们称它们为透视表，因为您可以轻松地将它们从一种类型的报表切换到另一种类型的报表，而不必制作完整的新报表。所以它们在适当的位置转动。让我们展示一个教授基本概念的基本示例。

示例：销售报告

销售人员非常有竞争力(这是销售人员的一部分)，所以他们自然想知道他们在季度末和年末的表现如何，加上他们的佣金会有多少。

假设我们有三个销售人员-卡洛斯、弗雷德和朱莉-都在卖石油。他们2014财年每个财季的销售额(以美元计)显示在下面的电子表格中。

要生成这些报告，我们创建一个数据透视表：

选择[插入]->[透视表]，位于工具栏左侧：

选择所有行和列(包括销售员姓名)，如下图所示：

透视表对话框出现在电子表格的右侧。

如果我们单击数据透视表对话框中的所有四个字段(季度、年、销售额和销售人员)，Excel会向电子表格中添加一个毫无意义的报告，但是为什么呢？

如您所见，我们已经选择了要添加到报告中的所有四个字段。Excel的默认行为是按文本字段对行进行分组，然后对所有其余行求和。

这里它给我们的是2014+2014+2014+2014=24，168，这是无稽之谈。并给出了1+2+3+4=10*3=3 0的1+2+3+4的和。我们不需要此信息，因此我们取消选择这些字段以将其从透视表中删除。

“Sum of Sales”(总销售额)是相关的，因此我们将对其进行修正。

示例：按推销员列出的销售额

您可以将“销售总和”编辑为“总销售额”，这样更清楚。此外，您还可以将单元格设置为货币格式，就像设置任何其他单元格的格式一样。首先单击“Sum of Sales”，然后选择“Value Field Settings”。

在出现的对话框中，我们将名称更改为“Total Sales”，然后单击“Number Format”并将其更改为“Currency”。

然后，您可以在数据透视表中看到您的作品：

示例：按推销员和季度划分的销售额

现在让我们添加每个季度的小计。要添加小计，只需左键单击“季度”字段并按住它并将其拖动到“行”部分即可。您可以在下面的屏幕截图上看到结果：

现在，让我们删除“季度总和”值。只需单击箭头，然后单击“删除字段”。在屏幕截图中，您现在可以看到我们添加了“Quarter”行，它按季度细分每个销售人员的销售额。

记住这些技能之后，您现在就可以从您自己的数据创建数据透视表了！

结束 / 结尾 / 结论 / 推论

最后，我们向您展示了Microsoft Excel公式和函数的一些功能，您可以将Microsoft Excel应用于您的业务、学术或其他需要。

正如您所看到的，Microsoft Excel是一个巨大的产品，具有如此多的功能，以至于大多数人，即使是高级用户，也不是所有的人都知道这些功能。可能有人会说这太复杂了，我们觉得更全面。

希望通过向您提供大量的实际示例，我们不仅展示了Microsoft Excel中可用的功能，还教会了您一些有关统计、线性编程、创建图表、使用随机数以及其他您现在可以在学校或工作场所采用和使用的想法的知识。

记住，如果你想回去再上一次课，你可以从第一课重新开始！