當前位置:商標查詢大全網 - 會計考試 - 10妳必須知道的高級Excel公式

10妳必須知道的高級Excel公式

每個財務分析師花在Excel上的時間比他們可能承認的要多。根據多年的經驗,我們整理出了每個世界級財務分析師都必須知道的最重要、最高級的Excel公式。

1.索引匹配

公式:=索引(C3: E9,匹配(B13,C3: C9,0),匹配(B14,C3: E3,0))

這是VLOOKUP或HLOOKUP公式的高級替代方案,後者有幾個缺點和限制。INDEXMATCH是Excel公式的強大組合,讓妳的財務分析和財務建模只需上壹層樓。

INDEX根據列號和行號返回表格中單元格的值。

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

這是壹個結合索引和匹配公式的示例。在這個例子中,我們查找並根據壹個人的名字返回他的身高。因為名字和身高是公式中的變量,所以我們可以同時改變它們!

高級Excel公式-索引匹配

對於逐步說明或如何使用這個公式,請參考免費指南如何在Excel中使用INDEXMATCH。

2.如果與和/或結合使用

公式:= IF(AND(C2 & gt;= C2 C4 & lt;=C5)、C6、C7)

任何花大量時間研究各種類型的金融模型的人都知道,嵌套IF公式可能是壹場噩夢。將IF與AND或OR函數結合使用可以使公式更容易被其他用戶查看和理解。在下面的例子中,您將看到我們如何組合各種函數來創建更高級的公式。

有關如何在Excel中執行此功能的更多信息,請參考免費指南,了解如何使用IF和/或。高級Excel公式-如果和

3.偏移與總和或平均值相結合。

公式:= sum (B4: offset (B4,0,E2-1))

OFFSET函數本身並不是特別高級,但是當我們把它和sum或average之類的函數結合起來,就可以創建壹個相當復雜的公式。假設您想要創建壹個動態函數,您可以對可變數量的單元格求和。使用常規的求和公式,您只能進行靜態計算,但通過添加偏移量,您可以移動單元格引用。

工作原理:為了使這個公式有效,我們使用SOFF函數替換SUM函數末尾的參考單元格。這使得公式是動態的,引用為E2的單元格可以告訴Excel您要累積多少個連續的單元格。現在,我們有了壹些高級的Excel公式!

下面是這個稍微復雜的公式的截圖。

總偏移公式

可以看到,求和公式以單元格B4開始,但以壹個變量結束,這個變量是壹個偏移量公式,以B4開始,以E2(?3?)繼續減壹。這將把求和公式的結尾移動到2個單元格,並對3年的數據(包括起點)求和。正如您在單元格F7中看到的,單元格B4: D4的總和是15,這是offset和sum公式給出的結果。

在我們的高級Excel課程中,學習如何逐步構建這個公式。

選擇

公式:=CHOOSE(choice,option1,option2,option3)

選擇功能是財務建模的壹個很好的場景分析。它允許您在特定數量的選項之間進行選擇,並返回選定的?選擇?。比如,假設妳對明年的收入增長有三種不同的假設: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,?& gt=21?)

這兩個高級公式非常適合使用條件函數。SUMIF將所有符合特定條件的單元格相加,COUNTIF計算所有符合特定條件的單元格。例如,假設您要統計所有大於或等於21(美國法定飲酒年齡)的單位,以了解參加客戶活動需要多少瓶香檳。可以使用COUNTIF作為高級解決方案,如下圖截圖所示。

COUNTIF公式

在高級Excel課程中,我們將更詳細地分解這些公式。

7.PMT和IPMT

公式:=PMT(利率,期數,現值)

如果妳在商業銀行、房地產、FP&A或者任何處理債權計劃的金融分析師崗位工作,妳需要知道這兩個詳細的公式。

PMT公式可以在貸款期限內為您提供相同的還款價值。妳可以和IPMT(告訴妳同類型貸款的利息支付)結合起來,然後分別支付本金和利息。

這是壹個如何使用PMT函數以30%的利率獲得5%的每月按揭付款654.38+0萬美元的示例。

8.LEN和TRIM

公式:=LEN(文本)和=TRIM(文本)

這些公式不常見,但壹定很復雜。這些應用程序非常適合需要組織和處理大量數據的金融分析師。不幸的是,我們得到的數據並不總是組織得很好,有時可能會出現諸如單元格開頭或結尾有多余空格的問題。

在以下示例中,您可以看到TRIM公式是如何清除Excel數據的。

高級Excel-TRIM

9.連接

公式:= a1&?更多的話?

Tandem本身並不是壹個真正的函數,它只是壹種創新的方式,將不同單元的信息連接起來,使工作表更加動態。這是財務分析師執行財務建模的壹個非常強大的工具(更多信息請參見我們免費的財務建模指南)。

在下面的例子中,您可以看到如何放置文本。紐約?另外?,?用什麽?紐約?結合起來創造?紐約州紐約市?。這允許您在工作表中創建動態標題和標簽。現在,您可以直接更新單元格B2和D2,而不是直接更新單元格B8。擁有大量的數據集對妳來說是壹項很有價值的技能。

系列公式的Excel示例

10.單元格、左、中和右功能

您可以結合這些高級Excel函數來創建壹些非常高級和復雜的公式。CELL函數可以返回關於單元格內容的各種信息(如名稱、位置、行、列等)。).LEFT函數可以從單元格的開頭(從左到右)返回文本,MID函數可以從單元格的任何起點(從左到右)返回文本,RIGHT函數可以從單元格的結尾(從右到左)返回文本。

以下是這三個公式的作用說明。

要看如何有效地將它們與單元格函數結合起來,我們將在高級Excel公式類中為您壹步步分解。

單元格、左側、中間和右側公式