Excel 高级公式

Microsoft Office Excel是最受欢迎和著名的工具。它帮助小型、中型和大型企业通过其执行函数和高级Excel公式的特性将数据维护、存储和分析为有用的信息。

95%的Excel用户应用基本表单。有一些用于复杂计算的函数和高级Excel公式。这些函数旨在方便查找和格式化大量数据，而高级Excel公式则用于从给定的特定数据集中获取新信息。

所有高级Excel公式都可以将冗长的手动任务转化为几秒钟的工作。以下是一些高级公式，如VLOOKUP、INDEX、MATCH、IF、SUMPRODUCT、AVERAGE、SUBTOTAL、OFFSET、LOOKUP、ROUND、COUNT、SUMIFS、ARRAY、FIND、TEXT等等。

1. INDEX MATCH

这是VLOOKUP或HLOOKUP公式的高级替代方案，它具有多个缺点和限制。INDEX MATCH是Excel公式的强大组合，将带领您的财务分析和财务建模进入一个新的水平。

• INDEX根据列和行号返回表中单元格的值。
• MATCH返回行或列中单元格的位置。

Formula: =INDEX(B1:D6,MATCH(B10,B1:B6,0),MATCH(B11,B1:D1,0))

这是INDEX和MATCH公式结合的一个例子。在这个例子中，我们根据员工的姓名查找并返回他们的工作经验年限。由于姓名和年份都是公式中的变量，我们可以同时更改它们。

2. IF与AND/OR组合

谁花费大部分时间进行各种类型的财务模型的人面临着嵌套的IF公式可能会非常困难。将IF与AND或OR函数组合在一起可以是一种简化公式的好方法，使其更容易进行审计和其他用户更易于理解。

Formula: =IF(AND(C2>=C4,C2<=C5),C6,C7)

在下面的例子中，您将看到如何使用各个函数结合起来创建一个更高级的公式。

3. 使用SUM或AVERAGE结合OFFSET

单独使用OFFSET函数并不特别高级，但是当我们将它与其他函数如SUM或AVERAGE结合使用时，我们可以创建一个相当复杂的公式。

假设您想创建一个可以对可变数量的单元格求和的动态函数。通过常规的SUM公式，您只能进行静态计算，但是通过添加OFFSET，您可以让单元格引用移动起来。

4. CHOOSE

这个高级的Excel公式可以简化冗长的IF函数语句，并提取您想要的特定数据集。当特定给定条件有两个以上的结果时，可以使用它。

CHOOSE函数在财务建模中非常适用于场景分析。它允许您在特定数量的选项之间进行选择并返回您选择的“选择”。

5. XNPV和XIRR

假设您是一名在投资银行、股票研究、财务规划与分析（FP&A）或其他任何需要贴现现金流的公司财务领域工作的分析师。在这种情况下，这些公式将是更好的选择。

Formula: =XNPV(discount rate, cash flows, dates)

简单地说，XNPV和XIRR允许您为每个被贴现的现金流应用特定的日期。Excel NPV和IRR公式的基本问题是它们假设现金流之间的期间是相等的。作为分析师，通常情况下您会遇到现金流的时间不均等的情况，这个公式就是用来解决这个问题的。

6. SUMIF和COUNTIF

这两个高级公式是有条件函数的重要用途。SUMIF会累加满足特定条件的所有单元格的值，而COUNTIF会计算满足特定条件的所有单元格的个数。

7. PMT和IPMT

如果您从事商业银行、房地产、FP&A;或任何需要处理债务计划的财务分析师职位，这两个公式对您会非常有帮助。

PMT公式可以给出贷款期内等额还款的金额。您可以与IPMT一起使用，它告诉您相同类型贷款的利息支付情况，然后将本金和利息支付分开。

8. LEN和TRIM

LEN和TRIM公式对需要整理和操作大量数据的财务分析师非常有用。不幸的是，我们得到的数据并不总是组织得很完美，有时候会出现单元格开头或结尾的额外空格等问题。

Formulas: =LEN(text) and =TRIM(text)

LEN公式返回给定文本字符串的字符数，这在想要计算一段文字中有多少个字符时很有用。

TRIM公式用于修剪或删除在从其他源复制的一组数据中出现的额外空格。

9. CONCATENATE

Concatenate不是单独的功能。它只是一种创新的方式，用于连接来自不同单元格的信息，使工作表更具动态性。

此功能在Excel中结合了LEFT和RIGHT函数，其中通过设置变量从左侧和右侧提取数据的特定部分来准备新的数据列。这是金融分析师执行金融建模时非常强大的工具。

Formula: =A1&" more text"

在下面的例子中，你可以看到如何将文本”New York”和”, 结合起来创建”New York, NY”。这样可以在工作表中创建动态的标题和标签。现在，不需要直接更新单元格B8，而是可以分别更新单元格B2和D2。对于大型数据集，这是一项非常有用的技能。

10. CELL、LEFT、MID 和 RIGHT 函数

这些高级Excel函数可以组合使用，创建一些非常高级和复杂的公式进行使用。CELL 函数可以返回有关单元格内容的各种信息，例如其名称、位置、行、列等。

• LEFT 函数可以从单元格的开头返回文本（从左到右）。
• MID 函数从单元格的任何起始点返回文本（从左到右）。
• RIGHT 函数从单元格的结尾返回文本（从右到左）。

11. RANDBETWEEN()

此高级Excel公式用于生成在您设置的值之间的随机数。它有助于在电子表格中模拟某些结果或行为。

12. PROPER 函数

此 proper 函数用于将单元格中句子的字母大写或大写。可以以自定义方式完成。您可以选择性地以所需格式更改字母。

13. ROUND 函数

此函数用于对具有小数点后多个数字的数据进行四舍五入，以方便计算。您无需格式化单元格。

14. VLOOKUP

此函数用于在大段的数据中查找某些信息，并将该数据提取到新建的表中。您必须访问函数选项。插入函数选项卡将允许您输入“VLOOKUP”，或者您可以在列表中找到它。选择后，将打开一个带有不同一组选项框的向导框。您可以将变量输入到以下位置：

• Lookup_value: 这是您键入的变量的选项，用于在较大表格的单元格中查找信息的值。
• Table Array: 它设置了从中提取信息的大表格的范围。它设置了您要选择的数据的范围。
• Col_index_num: 此命令框指定要提取数据的列。
• Range_lookup: 在这里，您输入 true 或 false。选择 true 选项将在变量不匹配时给出与您要查找的内容最接近的信息集。输入 false 时，它将给出您要查找的确切值，或者在找不到数据时显示 #N/A。

15. CONVERT() 单位转换

CONVERT() 高级Excel公式有助于确定不同单位数据的转换值。这个多功能函数也可用于转换货币和许多其他东西。