Excel 公式備忘單：高級指南

因此，您已經了解了基礎知識，解決了中級問題，現在您已準備好與大砲搏鬥 - Excel 的高級公式！Excel 具有許多用於復雜計算的高級函數和公式，因此有一個指南可以準確指出您需要的功能和公式。

此 Excel 公式備忘單涵蓋高級預測公式、統計分析、數據操作函數、錯誤處理等。

本參考資料將使您了解如何使用這些高級功能。每個公式都附有清晰的解釋、語法和實際示例，以幫助中級 Excel 用戶成為高級高級用戶。

請下載並打印備忘單並將其放在手邊。

好的，讓我們開始吧。

Excel-formulas-cheat-sheet-advanced-1下載

首先，讓我們進入數組公式。

目錄

• 數組公式
  • 1.獨特的功能
  • 2.排序功能
  • 3.過濾功能
• 隨機化 Excel 函數
  • 1.RANDBETWEEN函數
  • 2.RANDARRAY函數
• Microsoft Excel 中的高級預測公式
  • 1. FORECAST.ETS函數
  • 2.趨勢功能
• 高級統計公式
  • 1.百分位函數
  • 2.QUARTILE功能
• 高級數據分析和操作公式
  • 1.轉置功能
  • 2.頻率功能
  • 3. GETPIVOTDATA函數
• 高級錯誤處理
• 高級查找公式
  • 1.XLOOKUP函數
  • 2.XMATCH函數
• 最後的想法

數組公式

我們的初學者備忘單向您展示瞭如何手動排序和過濾數據。高級用戶使用數組公式以編程方式執行此操作。

數組公式允許您同時對多個單元格執行計算。這是三個關鍵功能：

1. 獨特的

2. 種類

3. 篩選

其中一些函數僅在最新版本的 Microsoft Excel 中可用。

1.獨特的功能

UNIQUE 函數接受一個範圍並返回一個唯一值列表。

假設您有服裝項目的銷售數據。要查找 A 列中的唯一項，請使用以下公式：

=唯一（A2：A6）

2.排序功能

SORT 函數對范圍的內容進行排序。語法是：

排序（數組，[排序索引]，[排序順序]，[按列]）

• array：要排序的值的範圍。

• sort_index：要排序的列（默認為 1）

• sort_order：1 表示升序（默認）或 2 表示降序）。

• by_col：TRUE 按列排序（默認）或 FALSE 按行排序。

最後三個參數是可選的，默認值通常是您想要的。

要按第一列對示例數據進行排序，請使用以下公式：

=排序(A2:C6)

3.過濾功能

FILTER 函數允許您根據特定條件過濾範圍。這是語法：

=FILTER（數組，包括，[if_empty]）

• array：要過濾的範圍。

• include：確定要過濾哪些值的條件。

• if_empty：指定在沒有值滿足過濾條件時返回什麼（默認為“”）。

假設您要過濾示例數據中的行以僅顯示銷售額大於 15 美元的行。使用這個公式：

=過濾器(A2:C6, C2:C6>15)

隨機化 Excel 函數

我們的中級秘籍展示瞭如何使用 RAND 函數生成一個介於 0 和 1 之間的隨機數。

高級 Excel 用戶知道如何使用隨機函數快速生成樣本數據。

1.RANDBETWEEN函數

RANDBETWEEN 函數比 RAND 更靈活，因為您可以將底部和頂部數字指定為 0 和 1 以外的值。

要生成數字介於 1 和 100 之間的數據，請將此公式輸入單元格 A1：

=RANDBETWEEN(1, 100)

然後，將單元格複製到所需數量的行和列。生成隨機數網格需要幾秒鐘：

2.RANDARRAY函數

您可能認為最好避免手動複製 RANDBETWEEN 函數。要獲得超級高級，您可以在最新版本的 Microsoft Excel 中使用新的 RANDARRAY 函數。

語法是：

RANDARRAY（[行]，[列]，[最小]，[最大]，[整數]）

• 行數：行數

• columns : 列數

• min : 最小數

• max : 最高數

• 整數：默認為 TRUE，否則使用十進制數。

要在六行九列中生成 1 到 100 之間的隨機數，請使用以下公式：

=RANDARRAY(6, 9, 1, 100, 真)

Microsoft Excel 中的高級預測公式

Excel 的預測功能用於根據現有數據趨勢預測未來值。這些功能有助於根據您的數據識別模式和項目趨勢。

1. FORECAST.ETS函數

在 Excel 2016 中，較舊的 FORECAST 函數已替換為一組較新的函數。

您可以根據所需的特定預測模型選擇函數。例如，FORECAST.ETS 函數使用指數平滑算法。

語法是：

FORECAST.ETS（目標日期、值、時間線）

• target_date：您想要為其計算值的日期。

• 值：歷史數據。

• 時間線：日期範圍

假設您在 A 列中有從 1 月 1 日到 5 日的日期，在 B 列中有銷售額。此公式將預測下一個銷售額：

=FORECAST.ETS(“2023 年 1 月 6 日”，B2:B6, A2:A6)

2.趨勢功能

TREND 函數基於最小二乘法投影一組值。它返回一個數組。語法是：

趨勢（known_y，[known_x]，[new_x]，[const]）

• known_y : y 值的範圍

• known_x：x值的範圍

• new_x：計算值的範圍

通常，known_y 是數據點，而 known_x 是日期。

使用與上一個示例中相同的數據，您可以在單元格 C2 中輸入以下公式。將生成一組值。

=趨勢（B2：B6，A2：A6）

高級統計公式

高級統計功能包括計算百分位數和四分位數。一些數學函數可用於向後兼容，但建議使用最新版本。

1.百分位函數

此函數計算低於特定值的數據點的百分比。語法是：

PERCENTILE.INC（數組，k）

• 數組：單元格範圍

• k：從 0 到 1 的百分位數

假設您要計算 B 列中數據的第 70 個百分位數。使用以下公式：

=PERCENTILE.INC(B2:B6, 0.7)

2.QUARTILE功能

此函數是百分位數函數的變體，但使用四分之一來劃分數據。這是語法：

QUARTILE.INC（數組，夸脫）

• 數組：數據范圍

• 夸脫：第 25 個百分位數為 1，第 50 個百分位數為 2，第 75 個百分位數為 3，最大值為 4。

下面的公式將計算 A 列中數據的第一個四分位數。

=QUARTILE.INC(A2:A5, 1)

高級數據分析和操作公式

一些高級功能可讓您切換數據格式、分析頻率分佈以及從數據透視表中提取數據。

1. 移調

2. 頻率

3. 獲取數據透視表

1.轉置功能

有時您希望將行中的數據移動​​到列中，反之亦然。您可以手動執行此操作或改用 TRANSPOSE 功能。

假設單元格 A2、A3 和 A4 中有項目“T 卹”、“連帽衫”和“牛仔褲”。你想把它們變成列標題。此函數返回單行中的值：

=轉置(A2:A4)

2.頻率功能

此函數計算數據集中值的頻率分佈。這是語法：

頻率（data_array，bins_array）

• data_array：值的範圍。

• bins_array：要使用的間隔。

假設你在 B 列有銷售數據，你想根據金額的多少來分析這些值的頻率分佈：

• 低於 20。

• 從 20 到 80。

• 80以上。

這代表三個 bin，可以使用以下公式計算：

=頻率（A2：A6，{20,80}）

有關 Excel 中頻率分佈的更多信息，請觀看此視頻：

3. GETPIVOTDATA函數

此功能可讓您從數據透視表中提取匯總信息。這是語法：

GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

• data_field：要從數據透視表中檢索的數據字段或值。

• pivot_table：對數據透視表的引用。

• field1、item1 等：要過濾的字段/項目對。

假設您有一個基於所售商品顏色的數據透視表。要提取紅色商品的銷售額，請使用以下公式：

=GETPIVOTDATA（“銷售額”，A1，“顏色”，“紅色”）

高級錯誤處理

即使是最基本的 Excel 公式也會產生錯誤。中級用戶應該知道如何使用 ISERROR 來處理錯誤。高級用戶還應該熟悉用於錯誤識別的 ERROR.TYPE 函數。

ERROR.TYPE 函數有助於識別單元格或公式中的特定錯誤類型。

它返回一個對應於不同錯誤類型的數值，例如#N/A、#VALUE!、#REF! 等。

假設您在單元格 A1 中有一個錯誤並且您想要確定它的錯誤類型。以下公式將返回與特定錯誤對應的數字：

=錯誤類型(A1)

您可以將其與多個函數結合使用，以根據錯誤類型做出不同的響應。這些是最常見的錯誤及其值：

1. ＃無效的！（在範圍內未找到公共單元格）

2. #DIV/0！（除以零或空單元格）

3. ＃價值！（公式中的數據類型或參數不當）

4. #參考！（引用的單元格已被刪除或存在循環引用）

5. ＃姓名？（Excel 無法識別函數或範圍）

6. #NUM！（無效數值）

7. #N/A（找不到值）

假設您要處理三種特定的錯誤類型。使用此公式根據類型顯示特定的錯誤消息：

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, “除以零錯誤”, IF(ERROR.TYPE(C2)=3, “無效​​值錯誤”, IF(ERROR.TYPE(C2 )=7, “未找到值錯誤”, “其他錯誤”))), C2)

高級查找公式

我們的初學者和中級備忘單涵蓋了一系列查找功能。以下是一些高級選項：

1. XLOOKUP

2. XMATCH

1.XLOOKUP函數

此查找函數允許您在範圍內搜索值並從另一列或範圍返回相應的值。

與VLOOKUP等更簡單的查找函數相比，它提供了更多功能。這是語法：

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value：您要搜索的值。

• lookup_array：查找的範圍。

• return_array：將顯示相應值的範圍。

• match_mode：完全匹配 (0)、下一個更小 (1)、下一個更大 (-1) 或通配符匹配 (2)。

• search_mode：-1 表示從上到下，1 表示從下到上，或 2 表示二進制搜索。

• if_not_found：設置未找到匹配項時返回的值。

假設您要在一系列數據中搜索首次出現的某件衣服並返回銷售額。此公式將查找文本“連帽衫”，如果找到則返回相鄰單元格中的值：

=XLOOKUP(“連帽衫”, A2:A6, B2:B6)

2.XMATCH函數

此函數允許您在範圍或數組中查找指定值的位置。這是語法：

XMATCH(lookup_value, lookup_array, [match_type], [search_mode])

• lookup_value：您要查找的值。

• lookup_array：您要搜索的範圍。

• match_type：完全匹配 (0)、次小 (-1)、次大 (1)。

• search_mode：二進制搜索 (1) 或線性搜索 (2)。

假設您要在 B 列的範圍內查找第一次出現的黃色項目。使用以下公式：

=XMATCH(“黃色”, B2:B6, 0)

最後的想法

該備忘單涵蓋了廣泛的功能，從統計分析、查找公式、數據操作技術到錯誤處理策略。

所提供的示例和解釋有助於揭開這些高級公式的神秘面紗，即使是經驗有限的人也能理解它們。

當您開始將它們合併到您的 Excel 任務中時，您正在將您的 Excel 技能提高到高級水平。

但請記住，這份備忘單只是冰山一角。當您發揮創造力、混合搭配這些公式並對其進行定制以解決您的獨特挑戰時，真正令人驚奇的事情就會發生。Excel 就像一塊畫布，而這些公式就是您的調色板——所以，繼續吧，畫出您的傑作吧！