Excel 公式備忘單：中級指南

因此，您已經成功掌握了Excel 的基礎知識——恭喜您！現在，您已經決定提升一個檔次並深入研究更高級的東西，對嗎？好吧，您很幸運，因為我們專門為您準備了一個方便的中級 Excel 備忘單！

這份 Microsoft Excel 中級備忘單涵蓋了廣泛的主題，可以提升您在數值分析、文本處理以及復雜的日期和時間邏輯方面的技能。

通過將 Excel 中級備忘單作為桌面參考，您可以深入了解 Microsoft Excel 更強大的公式和函數。

現在，請務必繼續閱讀，同時打印並保存下面的備忘單！

讓我們開始吧！

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

目錄

• 中級數學公式
  • 條件邏輯
• 中級統計公式
  • 1. MINA 和 MAXA
  • 2. COUNTA 和 COUNTIF
• 用於財務分析的 Excel 公式
  • 一、產品功能
  • 2. QUOTIENT 和 LOG 函數
  • 投資場景的功能
• 中級 Excel 日期和時間公式
  • 1.月中函數
  • 2. 中間週函數
  • 3.中間時間函數
• 用於條件代數和邏輯代數的 Excel 函數
  • 1.IF函數
  • 2. AND 函數
  • 3.或函數
  • 4. 非函數
• 引用單元格
  • 1. 間接函數
  • 2. 索引功能
  • 3.偏移函數
• 中間文本公式
  • 1.TEXTJOIN函數
  • 2.替換函數
  • 3.替代功能
• 中間查找公式
• 處理 Microsoft Excel 錯誤的公式
  • 1.IFERROR函數
  • 2. IFNA 功能
• 公式和函數快捷方式
• 中間單元格格式
• 最後的想法

中級數學公式

我們的初學者備忘單涵蓋了最常見的初學者數學函數，例如 SUM 函數。中級用戶應該熟悉這些更複雜的功能：

• ABS()返回數字的絕對值。

• SQRT()返回數字的平方根

• RAND()提供一個介於 0 和 1 之間的隨機數

條件邏輯

中級用戶還應該知道如何使用這些函數根據多個條件執行數學計算：

• SUMIFS(sum_range, criteria_range1, criteria1, [….])

• COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

• AVERAGEIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

這些函數評估提供的標準並產生 TRUE 或 FALSE 的邏輯值。

假設您在 A、B 和 C 列中有以下銷售數據：

要計算紅色 T 恤的銷售額總和，請使用 SUMIFS 公式：

=SUMIFS(C2:C6, A2:A6, “T 卹”, B2:B6, “紅色”)

要計算紅色 T 恤的行數，請使用 COUNTIFS 公式：

=COUNTIFS(C2:C6, A2:A6, “T 卹”, B2:B6, “紅色”)

要計算紅色 T 恤的平均銷售額，請使用 AVERAGEIFS 公式：

=AVERAGEIFS(C2:C6, A2:A6, “T 卹”, B2:B6, “紅色”)

中級統計公式

除了 MIN 和 MAX 等基本功能外，Excel 還為中級用戶提供了更廣泛的統計公式。以下是一些最有用的：

• 米娜

• MAXA

• 伯爵

• COUNTIF

1. MINA 和 MAXA

更常用的 MIN 和 MAX 函數會忽略文本值。

替代的 MINA 和 MAXA 函數在查找最高值或最低值時會同時考慮文本和數字。文本值被評估為就好像它們是零一樣。

差異如下圖所示。第一行評估最小值 10，而第二行評估為 0，因為存在文本值。

2. COUNTA 和 COUNTIF

COUNTA 函數用於計算區域中非空單元格的數量。

COUNTIF 函數不太具體，因為它用於計算範圍內滿足特定條件或標準的單元格數。

您還可以使用這些函數來計算列中不同值的數量。該視頻展示瞭如何操作。

用於財務分析的 Excel 公式

在執行財務分析（例如預測投資）時，您會想要使用幾個公式：

• 產品（編號 1，[編號 2 ...]）

• 商（分子，分母）

• 日誌（數字，[基地]）

一、產品功能

假設您要計算初始投資 1,000 美元（單元格 B1）在五年（單元格 B3）後年利率為 4%（單元格 B2）的未來價值。

使用 PRODUCT 函數通過以下公式計算未來值：

=B1 * 產品(1 + B2)^B3

該公式將返回您投資的未來價值。

2. QUOTIENT 和 LOG 函數

下一步是計算在給定利率下，一項投資的價值翻倍需要多少年。

將 QUOTIENT 函數與 LOG 函數結合使用，計算如下：

=商（日誌（2）/日誌（1 + B2），1）

該公式將返回 17 和样本數據，表明以 4% 的年利率計算，投資價值翻一番需要 17 年。

此圖顯示了 Excel 工作表中的公式：

投資場景的功能

這是一些適用於特定金融場景的功能。其中之一可能正是您所需要的：

• NPV – NPV（淨現值）函數將根據一系列基於貼現率的未來現金流計算投資的淨現值。

• ACCRINT – ACCRINT函數計算定期支付利息的證券的應計利息。這對於確定自上次付款日期到給定結算日期的證券所賺取的利息很有用。

• INTRATE – INTRATE函數計算完全投資證券的利率。

• PMT – PMT函數計算債務證券的總支付額。

• IRR – IRR函數提供內部收益率。

• YIELD – YIELD函數提供基於利率、面值和期限的證券收益率。

中級 Excel 日期和時間公式

Excel 中的基本日期和時間函數包括當前日期的 NOW 和 TODAY 函數。中級用戶還應該知道如何使用以下方法從給定日期提取組件：

• 日（日期）

• 月（日期）

• 年（日期）

公式=MONTH(“2023 年 4 月 23 日”)將返回第 4 個月的結果 4。同樣，DAY 和 YEAR 函數將分別返回 23 和 2023。

1.月中函數

中級用戶有時會處理在日期中添加或減去月份以及查找月末的問題。

• EDATE（開始日期，number_of_months）

• EOMONTH（開始日期，月數）

例如，公式=EDATE(“2023 年 4 月 23 日”, 2)將計算兩個月後的日期。

公式=EOMONTH(23 April 2023", 2)計算兩個月後的月底。

結果是“2023 年 6 月 30 日”，其中考慮到 6 月只有 30 天。如果您指定 3 個月，則結果將為“2023 年 7 月 31 日”。

2. 中間週函數

您可以結合使用 SUM 和 WEEKDAY 函數來計算日期範圍內的整個工作日數。

WEEKDAY 函數返回給定日期的星期幾。第二個參數確定編號系統，默認為星期日到星期六。

如果單元格區域 B1:B40 中有一系列日期，請使用此公式計算工作日：

=SUM(–(工作日(B1:B40,2)>5))

3.中間時間函數

YEARFRAC 函數計算兩個日期之間一年的分數。

TIMEVALUE 函數用於將以文本表示的時間轉換為表示一天 24 小時比例的十進制數。

結果是一個 Excel 序列號，其中 1 表示全天 24 小時，0.5 表示 12 小時，0.25 表示 6 小時，依此類推。

假設單元格 A1 中有一個文本格式為“17:45”的時間，並且您想要將其轉換為十進制數。這是公式：

=時間值(A1)

值 0.74 表示在六點前十五分鐘的時間已經過去了 24 小時制的比例。

將時間單元格從時間格式更改為文本格式。您可能希望將其格式化為百分比以便更清楚。

用於條件代數和邏輯代數的 Excel 函數

條件函數和邏輯函數對於 Excel 中的決策至關重要。以下函數可以與更基本的 Excel 公式結合使用以獲得強大的邏輯：

1. 如果（條件，value_if_true，value_if_false）：

2. AND（條件 1，條件 2，...）

3. 或（條件 1，條件 2，...）

4. 不是（條件 1，條件 2，...）

IF 函數計算條件並根據條件是真還是假返回不同的值。

AND、OR 和 NOT 函數檢查哪些條件為真並讓您做出相應的決定。

下面是一些基於我們之前使用的銷售數據的例子。

1.IF函數

假設您希望在商品的銷售額低於 15 時顯示文本“Low Sales”。如果銷售額較高，您希望在新列中顯示“High Sales”。

使用此公式並將其複製到其他行：

=IF(C2<15, “低銷量”, “高銷量”)

2. AND 函數

假設您想為銷量至少為 15 的 T 卹顯示 TRUE 或 FALSE。使用這個公式：

=AND(A3=”T卹”, C3>=15)

3.或函數

假設您要測試多個條件，如果滿足任何條件則返回 TRUE，否則返回 FALSE，那麼您將使用 OR 函數。

例如，如果我們在單元格 A1 和 B1 中有測試分數，並且我們想知道其中一個分數是否高於 80，我們可以使用：=OR(A1>80, B1>80)。如果任一分數（或兩者）超過 80，這將返回 TRUE，如果兩者都小於或等於 80，則返回 FALSE。

4. 非函數

假設您想檢查該物品是否不是連帽衫。使用這個公式：

=NOT(A2=”連帽衫”)

引用單元格

初學者很快就會熟悉使用絕對引用和相對引用，例如 $A$1 或 B2。中級用戶應該熟悉使用間接引用、索引和偏移量：

1. 間接（ref_text）

2. 索引（範圍，行數，列數）

3. OFFSET（參考單元格、行、列）：

1. 間接函數

INDIRECT 函數返回以文本形式輸入的指定單元格引用的值。要引用單元格 B3，請使用以下公式：

=間接（“B3”）

與使用簡單單元格引用相比的優勢在於此函數為您提供動態引用。這意味著當電子表格的結構發生變化時，例如一行被刪除，Excel 會自動更新引用。

2. 索引功能

INDEX函數用於根據行號和列號引用指定範圍內的單元格。

要引用六行表格中的單元格 B3，請使用以下公式：

=索引(B1:B6, 3)

3.偏移函數

OFFSET 函數返回一個單元格或範圍，該單元格或範圍與引用單元格相距指定的行數和列數。

如果要顯示跨兩個單元格且距 A1 向下一個單元格的單元格值，請使用以下公式：

=偏移量(A1, 2, 1)

以下是實際中的公式：

中間文本公式

初學者應該知道像 LEFT 這樣的文本函數，它從字符串的左側提取一個或多個字符。高級用戶應該熟悉以下功能：

1. TEXTJOIN（定界符，ignore_empty，text1，[text2，...]）

2. 替換（舊文本、起始編號、字符數、新文本）

3. 替換（文本，舊文本，新文本，[instance_num]）

1.TEXTJOIN函數

TEXTJOIN 函數使用您提供的分隔符連接單元格。您還可以指定忽略空白單元格的位置。

要生成 A 列中某些項目的逗號分隔列表，請使用以下公式：

=TEXTJOIN(“, “, 真, A2:A4)

2.替換函數

replace 函數允許您指定要交換到目標中的字符串的起始位置和長度。

要用“XX”替換字符串的前兩個字符，請使用以下公式：

=TEXTJOIN(A2, 1, 2, “XX”)

3.替代功能

替代函數用新的文本字符串替換另一個文本字符串中指定的文本字符串。

要將單詞“Jeans”替換為“Leggings”，請使用以下公式：

=SUBSTITUTE(A3, “牛仔褲”, “緊身褲”)

以下是實際中的公式：

中間查找公式

初學者備忘單中涵蓋了 VLOOKUP 和 MATCH 等常見查找函數。

中級用戶應該熟悉 CHOOSE 查找功能：

• 選擇(index_num, value1, [value2, …])

CHOOSE 函數根據指定的索引號從值列表中返回一個值。

假設您有一列標記為 1、2 或 3 的 T 卹尺碼。您希望在同一行中分別顯示小、中和大類別。使用這個公式：

=選擇（A1，“小”，“中”，“大”）

處理 Microsoft Excel 錯誤的公式

初學者備忘單列出了您在使用 Excel 時會看到的典型錯誤消息。中級用戶應該能夠使用公式優雅地處理錯誤。

1. IFERROR（值，if_error）

2. IFNA（值，value_if_na）

1.IFERROR函數

除數為零的錯誤很常見，但 Excel 的默認顯示為#DIV/0！每個用戶都不清楚。

使用 IFERROR 函數將標準錯誤替換為“不能被零除”，公式如下：

=IFERROR(A1/B1, “不能被零除”)

此圖顯示第三行中的原始錯誤和處理第四行的 IFERROR 函數。

2. IFNA 功能

Excel 中的 IFNA 函數用於捕獲和處理#N/A 錯誤。

該備忘單的另一部分顯示了 CHOOSE 函數。如果索引號不是整數，此函數將返回 N/A 錯誤。

假設輸入的 T 卹尺碼為 1.5，用戶誤以為這會顯示介於小號和中號之間的類別。相反，它會顯示#N/A錯誤。

要提供更有用的錯誤，請使用以下公式：

=IFNA(CHOOSE(A2, “低”, “中”, “高”), “無效​​索引”)

公式和函數快捷方式

有多種鍵盤快捷鍵可以讓您更高效地處理工作表。

• F2：編輯活動單元格並將插入點定位在單元格內容的末尾。

• F9：計算並顯示所選公式部分的結果。

• Ctrl + Shift + Enter：輸入數組公式。

• Shift + F3：打開“插入函數”對話框。

• Esc：取消輸入公式並恢復為原始單元格內容。

其中一些 Excel 快捷方式可能不適用於所有語言或鍵盤佈局。

中間單元格格式

在財務分析中，包含數字、日期和貨幣的單元格的正確格式至關重要。要格式化單元格，請按照下列步驟操作：

1. 選擇要設置格式的單元格。

2. 右鍵單擊選定的單元格，然後選擇“設置單元格格式”。

3. 選擇適當的類別（例如，數字、貨幣、日期）並應用所需的格式。

財務中需要考慮的一些常見格式類型包括：

• 貨幣：1,234.56 美元

• 百分比：12.34%

• 會計：（$1,234.00）

• 日期：2023 年 5 月 10 日

最後的想法

隨著您對本備忘單中突出顯示的各種公式和函數越來越熟悉，您會發現自己能夠更好地處理日益複雜的任務和項目。

練習是掌握任何技能的關鍵，所以不要害怕在日常工作中嘗試這些公式。他們會將您的專業知識從初級水平提升到中級水平。

這只是對 Excel 的中間函數可以用來簡化您的工作、簡化您的分析並幫助您從數據中發現見解的嘗試。

當您開始組合備忘單中的功能並根據您的獨特需求定制它們時，真正的魔法就會發生。所以，不要在這裡停下來。不斷探索，不斷試驗，您會發現 Excel 不僅僅是一種工具，它還是遊戲規則的改變者！

感覺您想更上一層樓？查看我們的Excel 高級公式備忘單。