您必须知道的10个高级Excel公式

每个财务分析师在Excel中花费的时间都比他们可能会承认的要多。基于多年的经验，我们编制了每个世界级的财务分析师都必须知道的最重要和最先进的Excel公式。

1.索引匹配

公式：=INDEX（C3：E9，MATCH（B13，C3：C9,0），MATCH（B14，C3：E3,0））

这是VLOOKUP或HLOOKUP公式（具有多个缺点和局限性）的高级替代方法。INDEXMATCH是Excel公式的强大组合，它将使您的财务分析和财务建模更上一层楼。

INDEX根据列号和行号返回表中单元格的值。

MATCH返回单元格在行或列中的位置。

这是将INDEX和MATCH公式组合在一起的示例。在此示例中，我们查找并根据其姓名返回一个人的身高。由于名称和身高都是公式中的变量，因此我们可以同时更改它们！

高级Excel公式-索引匹配

有关分步说明或如何使用此公式的信息，请参阅有关如何在Excel中使用INDEXMATCH的免费指南。

2.IF与AND/OR结合

公式：=IF（AND（C2>=C4，C2<=C5），C6，C7）

任何花费大量时间进行各种类型财务模型的人都知道，嵌套IF公式可能是一场噩梦。将IF与AND或OR函数结合使用可以使公式更易于审核，并使其他用户更容易理解。在下面的示例中，您将看到我们如何结合使用各个功能来创建更高级的公式。

有关如何在Excel中执行此功能的详细信息，请参阅有关如何将IF与AND/OR一起使用的免费指南。高级Excel公式-如果和

3.OFFSET与SUM或AVERAGE相结合

公式：=SUM（B4：OFFSET（B4,0，E2-1））

该OFFSET函数自身没有特别先进，但当我们与像总和或平均值等功能结合起来，我们可以创建一个相当复杂的公式。假设您要创建一个动态函数，可以对可变数量的单元格求和。使用常规的SUM公式，您只能进行静态计算，但是通过添加OFFSET，可以使单元格引用移动。

工作原理：为了使此公式起作用，我们用SOFF函数替换SUM函数的结尾引用单元格。这使公式变得动态，并且引用为E2的单元格可以告诉Excel您要累加多少个连续单元格。现在，我们有了一些高级Excel公式！

下面是该稍微复杂的公式的屏幕截图。

总偏移量公式

如您所见，SUM公式以单元格B4开始，但以变量结尾，该变量是OFFSET公式，从B4开始并以E2中的值（“3”）继续减一。这会将求和公式的结尾移到2个单元格上，求和3年的数据（包括起点）。正如您在单元格F7中看到的那样，单元格B4：D4的总和为15，这是偏移量和总和公式给出的结果。

在我们的高级Excel课程中，了解如何逐步构建此公式。

4.选择

公式：=CHOOSE（choice，option1，option2，option3）

该CHOOSE函数是伟大的财务建模情景分析。它允许您在特定数量的选项之间进行选择，并返回所选的“选择”。例如，假设您对明年的收入增长有三种不同的假设：5％，12％和18％。如果您告诉Excel您想要选择＃2，则使用CHOOSE公式可以返回12％。

阅读有关Excel中方案分析的更多信息。

选择功能

要观看视频演示，请查看我们的高级Excel公式课程。

5.XNPV和XIRR

公式：=XNPV（折现率，现金流量，日期）

如果您是从事投资银行业务，股票研究，财务计划与分析（FP＆A）或需要折现现金流的公司财务的其他领域的分析师，那么这些公式将为您节省很多时间！

简而言之，XNPV和XIRR允许您将特定日期应用于折现后的每个现金流量。Excel的基本NPV和IRR公式的问题在于，它们假定现金流量之间的时间段相等。通常，作为分析师，您会遇到现金流分配不均匀的情况，而此公式就是解决问题的方法。

XNPVExcel中的高级财务公式

有关更详细的细分，请参阅免费的IRR与XIRR公式指南以及XNPV指南。

6.SUMIF和COUNTIF

公式：=COUNTIF（D5：D12，“>=21”）

这两个高级公式非常适合使用条件函数。SUMIF添加满足特定条件的所有单元格，COUNTIF计算满足特定条件的所有单元格。例如，假设您想对所有大于或等于21（在美国的法定饮酒年龄）的单元进行计数，以找出参加客户活动需要多少瓶香槟。您可以将COUNTIF用作高级解决方案，如下面的屏幕快照所示。

COUNTIF公式

在高级Excel课程中，我们将更详细地分解这些公式。

7.PMT和IPMT

公式：=PMT（利率，期数，现值）

如果您在商业银行，房地产，FP＆A或任何处理债务计划的财务分析师职位中工作，则需要了解这两个详细公式。

PMT公式可为您提供贷款期限内同等还款的价值。您可以将其与IPMT（告诉您相同类型的贷款的利息支付）结合使用，然后分开支付本金和利息。

这是一个示例，说明了如何使用PMT功能以30％的利率获得5％的100万美元按揭的月供。

8.LEN和TRIM

公式：=LEN（text）和=TRIM（text）

这些公式不太常见，但肯定非常复杂。这些应用程序非常适合需要组织和处理大量数据的财务分析师。不幸的是，我们获得的数据并不总是组织得很好，有时单元格的开头或结尾可能会出现多余的空格之类的问题

在下面的示例中，您可以看到TRIM公式如何清除Excel数据。

高级Excel-TRIM

9.连接

公式：=A1＆“更多文字”

串联本身并不是一个真正的功能，它只是一种创新的方式，可以连接来自不同单元的信息，并使工作表更具动态性。对于财务分析师执行财务建模，这是一个非常强大的工具（请参阅我们的免费财务建模指南以了解更多信息）。

在下面的示例中，您可以看到如何将文本“NewYork”加“，”与“NY”结合起来以创建“NewYork，NY”。这使您可以在工作表中创建动态标题和标签。现在，您可以直接更新单元格B2和D2，而不必直接更新单元格B8。拥有大量数据集，这是一项宝贵的技能可供您使用。

串联公式Excel示例

10.CELL，LEFT，MID和RIGHT功能

可以将这些高级Excel函数组合在一起，以创建一些非常高级和复杂的公式来使用。CELL函数可以返回有关单元格内容的各种信息（例如其名称，位置，行，列等）。LEFT函数可以从单元格的开头（从左到右）返回文本，MID从单元格的任何起点（从左到右）返回文本，而RIGHT从单元格的结尾（从右到左）返回文本。

下面是这三个公式的作用说明。

要查看如何将它们与CELL函数有效地结合在一起，我们将在高级Excel公式类中逐步为您进行分解。

CELL，LEFT，MID和RIGHT公式