17個高級Excel公式——所有專業人士都必須知道

已發表: 2019-07-28

如果您發現自己經常彎曲雙手並浪費時間在 Excel 中手動執行操作，那麼如果您知道如何以正確的方式使用它，您就會錯過 Excel 的強大功能。 Microsoft Excel 以其公式而聞名，該公式可以有效地處理單元格中的大量數據，而無需在該過程中進行太多人工干預。

我們編制了 17 個高級 Excel 公式，它們具有將冗長的平凡手動任務轉換為幾秒鐘工作的不可思議的能力。這些是每個專業人士都應該觸手可及的公式，以節省自己寶貴的時間，或者在關鍵時刻給老闆留下深刻印象。

瀏覽列表，告訴我們你最喜歡的！

學習者平均獲得 58% 的加薪，最高可達 400%。

1. 索引匹配

公式 = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

Excel 上 VLOOKUP 或 HLOOKUP 公式的絕佳替代方案，它在執行查找任務時有一些缺點。 INDEX MATCH 是 2 個獨立函數的組合公式：

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

MATCH：此公式返回單元格在行或列中的位置。

INDEX 和 MATCH 公式組合的一個示例是：當您查找並根據姓名返回一個人的身高時，您可以使用此公式通過 INDEX MATCH 公式更改兩個變量（在本例中為姓名和身高） .

資源

讓我們一步一步地分解它：

如何結合 INDEX 和 MATCH

類型索引
選擇要索引的區域
用 F4 鎖定區域
找到要從中查找數據的行
用系列信息輸入 MATCH 並用 F4 鎖定區域
鍵入 0 表示完全匹配，關閉括號
按回車

有趣的數據科學項目想法

現在，您在工作表中擁有一組完全動態且功能齊全的列和行，其中所有通過單元格和行進行正確數據的梳理都是自動完成的。

2. 平均

要執行平均公式以找到任何數字範圍的平均值，您需要遵循以下步驟：

在格式中輸入您正在計算的值、單元格或單元格的比例。

公式應以 =AVERAGE(number1, number 2, etc.) 開頭

如果要對工作表中的一系列單元格進行平均，請執行以下步驟：

輸入您在格式中計算的值、單元格或單元格的比例，就像您在上面所做的一樣
公式應類似於 =AVERAGE(Start Value: End Value)。

如果您想知道如何輸入單元格區域的值，您可以用鼠標選擇工作表上的區域並用 F4 鎖定它。這將使 Excel 為您完成其餘的工作，而無需對數字與組中的項目數進行任何求和和除法。

3. 蘇米夫

這個公式在 excel 中表示為 SUMIF(range, criteria, [sum range])。這將導致滿足您設置的要求的所需單元格範圍內的值的總和。例如，=SUMIF(C3: C12, ">70,000) 將僅從值大於 70,000 的單元格返回 C3 和 C12 的單元格之間的值之和。

在財務、工資甚至成本計算的情況下，您需要根據您設置的條件確定與特定員工相關聯的潛在客戶的價值。手動執行此操作非常耗時並且會減慢速度。

上述條件的公式可以是 =SUMIF(range, criteria, [sum_range])，其中 Range 可以定義為您需要從中選擇值的工作表的範圍。

[sum_range] 定義為除了輸入的第一個 Range 之外，您要添加的附加或可選 Range。

這是一個例子：

資源

4. 與 SUM 相結合的偏移量

單獨使用 OFFSET 函數可能不方便，但是當與其他服務結合使用時，如果您想創建一個可以對任意可變數量的單元格求和的動態角色，您可以獲得一個具有更快結果的複雜公式，即常規 SUM 公式，即static 需要與 OFFSET 功能結合使用。

因此，為了找出變量單元格中值的總和，公式應該是：

=SUM(B4:OFFSET(B4,0,E2-1))

資源

這裡 SUM 函數的結束引用被 OFFSET 函數替換。從 B4 開始的 SUM 公式以變量結尾，並且是從 B4 開始的 OFFSET 公式，以 E2 (“3”) 中的值減一繼續。這有助於該方法移動兩個單元格並彙總三年的數據。在上面的參考中，您可以看到單元格 F7 包含單元格 B4 的總和：D4 = 15。