excel高级函数公式大全（excel函数公式）

本文目录

• excel函数公式
• 您必须知道的10个高级Excel公式

excel函数公式

一、excel函数公式大全及使用方法
一、数字处理 1、取绝对值函数 公式：=ABS（数字） 2、取整函数 公式：=INT（数字） 3、四舍五入函数 公式：=ROUND（数字，小数位数） 二、判断公式 1、如果计算的结果值错误那么显示为空 公式：=IFERROR（数字/数字，） 说明：如果计算的结果错误则显示为空，否则正常显示。

2、IF语句的多条件判定及返回值 公式：IF(AND（单元格（逻辑运算符）数值，指定单元格=返回值1），返回值2，） 说明：所有条件同时成立时用AND，任一个成立用OR函数。 三、常用的统计公式 1、统计在两个表格中相同的内容 公式：B2=COUNTIF（数据源：位置，指定的，目标位置） 说明：如果返回值大于0说明在另一个表中存在，0则不存在。

如果，在此示例中所用到的公式为：B2=COUNTIF(Sheet15!A:A,A2) 2、统计不重复的总数据 公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)） 说明：用COUNTIF函数统计出源数据中每人的出现次数，并用1除的方式把变成分数，最后再相加。 四、数据求和公式 1、隔列求和的应用 公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3） 说明：如果在标题行中没有规则就可以用第2个公式 2、单条件应用之求和 公式：F2=SUMIF(A:A,C:C) 说明：这是SUMIF函数的最基础的用法 五、查找与引用公式 1、单条件查找 说明：VLOOKUP是excel中最常用的查找方式 六、字符串处理公式 1、多单元格字符串的合并 说明：Phoic函数只能合并字符型数据，不能合并数值 2、截取结果3位之外的部分 说明：LEN计算总长度，LEFT从左边截总长度-3个 七、日期计算相关 1、日期间相隔的年、月、天数计算 A2是开始日期（2011-12-2）,B2是结束日期（2013-6-11）。

计算： 相差多少天的公式为：=datedif(A2,B2,d) 其结果：557 相差多少月的公式为： =datedif(A2,B2,m) 其结果：18 相差多少年的公式为： =datedif(A2,B2,Y) 其结果：1 不考虑年份相隔多少月的公式为：=datedif(A1,B1,Ym) 其结果：6 不考虑年份相隔多少天的公式为：=datedif(A1,B1,YD) 其结果：192 不考虑年份月份相隔多少天的公式为：=datedif(A1,B1,MD) 其结果：9 datedif函数第3个参数说明： Y 时间段中的整年数。 M 时间段中的整月数。
D 时间段中的天数。 MD 日期中天数的差。

忽略月和年。 YM 日期中月数的差。

忽略日和年。 YD 日期中天数的差。

您必须知道的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》=**，C2《=C5），C6，C7）
任何花费大量时间进行各种类型财务模型的人都知道，嵌套IF公式可能是一场噩梦。将IF与AND或OR函数结合使用可以使公式更易于审核，并使其他用户更容易理解。在下面的示例中，您将看到我们如何结合使用各个功能来创建更高级的公式。
有关如何在Excel中执行此功能的详细信息，请参阅有关如何将IF与AND/OR一起使用的免费指南。高级Excel公式-如果和
3.OFFSET与SUM或**ERAGE相结合
公式：=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公式