Excel 公式備忘單:高級指南

Excel 公式備忘單:高級指南

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

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

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

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

好的,讓我們開始吧。

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

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

目錄

數組公式

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

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

  1. 獨特的

  2. 種類

  3. 篩選

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

1.獨特的功能

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

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

=唯一(A2:A6)

Excel 公式備忘單:高級指南

2.排序功能

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

排序(數組,[排序索引],[排序順序],[按列])

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

  • sort_index:要排序的列(默認為 1)

  • sort_order:1 表示升序(默認)或 2 表示降序)。

  • by_col:TRUE 按列排序(默認)或 FALSE 按行排序。

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

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

=排序(A2:C6)

Excel 公式備忘單:高級指南

3.過濾功能

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

=FILTER(數組,包括,[if_empty])

  • array:要過濾的範圍。

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

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

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

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

Excel 公式備忘單:高級指南

隨機化 Excel 函數

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

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

1.RANDBETWEEN函數

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

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

=RANDBETWEEN(1, 100)

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

Excel 公式備忘單:高級指南

2.RANDARRAY函數

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

語法是:

RANDARRAY([行],[列],[最小],[最大],[整數])

  • 行數:行數

  • columns : 列數

  • min : 最小數

  • max : 最高數

  • 整數:默認為 TRUE,否則使用十進制數。

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

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

Excel 公式備忘單:高級指南

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)

Excel 公式備忘單:高級指南

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)

Excel 公式備忘單:高級指南

高級統計公式

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

1.百分位函數

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

PERCENTILE.INC(數組,k)

  • 數組:單元格範圍

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

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

=PERCENTILE.INC(B2:B6, 0.7)

Excel 公式備忘單:高級指南

2.QUARTILE功能

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

QUARTILE.INC(數組,夸脫)

  • 數組:數據范圍

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

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

=QUARTILE.INC(A2:A5, 1)

Excel 公式備忘單:高級指南

高級數據分析和操作公式

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

  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 公式備忘單:高級指南

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

3. GETPIVOTDATA函數

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

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

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

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

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

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

=GETPIVOTDATA(“銷售額”,A1,“顏色”,“紅色”)

Excel 公式備忘單:高級指南

高級錯誤處理

即使是最基本的 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)

Excel 公式備忘單:高級指南

高級查找公式

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

  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)

Excel 公式備忘單:高級指南

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 技能提高到高級水平。

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


在 Power Automate 中執行直到循環控制

在 Power Automate 中執行直到循環控制

了解有關 Do Until 循環控制如何在 Power Automate 流中工作的基礎過程,並熟悉所需的變量。

使用 Deneb 為 LuckyTemplates 製作自定義視覺對象

使用 Deneb 為 LuckyTemplates 製作自定義視覺對象

受限於 LuckyTemplates 中提供的標準視覺效果?學習使用 Deneb 和 Vega-Lite 為 LuckyTemplates 創建自定義視覺對象,提升您的數據可視化能力。

在 Power Automate Desktop 中將日期添加到文件名

在 Power Automate Desktop 中將日期添加到文件名

在此博客中,您將了解如何使用 Power Automate Desktop 自動將日期添加到文件名的開頭或結尾。

Power Query:如何快速添加註釋

Power Query:如何快速添加註釋

找出幾種不同的方法在 Power Query 中添加註釋,這對於不喜歡記筆記的人非常有幫助。

Power Apps – SharePoint 集成指南

Power Apps – SharePoint 集成指南

在此博客中,您將了解 MS Power Apps 界面的基礎知識並了解 Power Apps-SharePoint 集成的過程。

什麼是 Python 中的自我:真實世界的例子

什麼是 Python 中的自我:真實世界的例子

什麼是 Python 中的自我:真實世界的例子

如何在 R 中保存和加載 RDS 文件

如何在 R 中保存和加載 RDS 文件

您將學習如何在 R 中保存和加載 .rds 文件中的對象。本博客還將介紹如何將對像從 R 導入 LuckyTemplates。

回顧前 N 個工作日——DAX 編碼語言解決方案

回顧前 N 個工作日——DAX 編碼語言解決方案

在此 DAX 編碼語言教程中,了解如何使用 GENERATE 函數以及如何動態更改度量標題。

在 LuckyTemplates 中使用多線程動態視覺技術展示見解

在 LuckyTemplates 中使用多線程動態視覺技術展示見解

本教程將介紹如何使用多線程動態可視化技術從報告中的動態數據可視化中創建見解。

LuckyTemplates 篩選上下文簡介

LuckyTemplates 篩選上下文簡介

在本文中,我將貫穿過濾器上下文。篩選上下文是任何 LuckyTemplates 用戶最初應該了解的主要主題之一。