MS Excel Formula 試算表 20 大職場常用公式大全

excel公式大全｜現時返 Office 工，都一定要學識 MS Office 辦公軟件，Word、Excel 及 PowerPoint 都是基本要識的工具。當中 Excel 可以儲存海量大數據資料，而且具備試算表公式函數功能，如果能夠掌握 MS Excel 試算表公式函數，定能事半功倍，更靈活運用你的 MS Excel 資料數據庫。以下使用一個Excel 公式範例數據庫，圖解列出 10 大常用 MS Excel 試算表公式函數，即使對 MS Excel 公式零知識的傻瓜新手，都可即學即用！

在 Micrsoft Office 系列軟件，包括 Word、Excel、Powerpoint 等，令最多打工仔頭痛的便是 Excel，有時要將一堆雜亂的資料拼入 Excel 亦是難事。此外，因為它的功能豐富，除了可以儲存資料外，還可以利用不同的公式，快速有效整理內容，若同時學識快捷鍵則事半功倍。今次 ezone.hk 就為大家準備十分 Excel 最常用的公式，若能好好掌握，日常使用 Excel 已經不是難事！

 

Excel公式01｜SUM

SUM功能是一個非常實用的工具，主要用於計算範圍、單個數字或是單元格列中的數值總和。用戶只需選擇一系列的單元格，然後使用SUM函數（例如：=SUM(A1:A10)），Excel就會自動計算出這些單元格中所有數字的總和。這個功能大大節省了手動相加的時間，提高了工作效率，是處理財務報表、學術研究數據分析等任務的強大助手。

• 功能：加總，把不同儲存格加起來
• 公式：=SUM（儲存格,儲存格...）；=SUM（儲存格範圍）
• 例子：=SUM(F3:L3)，把 F3 至 L3 儲存格範圍加起來；

=SUM(F3,H3,K3)，把 F3, H3, K3 三個獨立儲存格加起來 

Excel公式02｜AVERAGE

AVERAGE功能提供了快速計算一系列數值平均值的便利。使用者只需選擇包含數字的單元格範圍，然後應用AVERAGE函數（例如：=AVERAGE(B1:B5)），Excel便會自動計算出所選範圍內所有數值的平均數。這個功能對於進行數據分析、學術研究或是日常辦公中需要快速得出數據平均值的情況非常實用，極大地提升了工作效率和準確性。功能：平均

• 公式：=AVERAGE(儲存格,儲存格...)；=AVERAGE（儲存格範圍）
• 例子：=AVERAGE(G3,H3,I3,L3），計算這幾個選定儲存格的平均數值。
• =AVERAGE(G3:L3)，計算 G3 至 L3 的儲存格的平均數值。

 

Excel公式03｜COUNT

COUNT功能專門用於計算一個範圍內的數值個數。當用戶需要知道某一範圍內有多少個單元格含有數字時，可以使用這個函數（例如：=COUNT(C1:C10)）。Excel會自動返回範圍內包含數字的單元格總數。這項功能對於進行數據統計和分析特別有用，幫助用戶快速獲取數據集中的數值個數

• 功能：統計含數字及日期資料的儲存格
• 公式：=COUNT（儲存格,儲存格...）；=COUNT（儲存格範圍）
• 例子：=COUNT(B3:L3)，如下圖示，不計算文字資料，但計算數字和日期資料，因此答案是 8。

 

Excel公式04｜COUNTA

COUNTA功能用於計算範圍內非空單元格的數量，包括含有數字、文字、日期等任何類型的數據。使用方法簡單，只需指定一個或多個範圍（例如：=COUNTA(D1:D10)），Excel便能返回該範圍內所有非空單元格的總數。這個功能對於需要評估數據集完整性、進行數據整理前的預檢或計算有效數據點的情況非常實用。

• 功能：統計出選定範圍內有多少個儲存格不是空格
• 公式：=COUNTA（儲存格範圍）
• 例子：=COUNTA(B3:L3)，由於把文字、日期、數字都統計在內，因此答案是 11

 

Excel公式05｜CONCATENATE

CONCATENATE函數用於將多個文字字符串合併成一個字符串。透過這個功能，用戶可以輕鬆將不同單元格中的文字資料結合起來，例如將名字和姓氏合併成全名（例如：=CONCATENATE(A1, " ", B1)）。這項功能在處理大量需要合併文字的數據時特別有用，如製作報表、整理資料庫時。CONCATENATE功能為數據整理和報告製作提供了極大的便利，使得文字數據的處理更加靈活和高效。

• 功能：合併文字儲存格
• 公式：=（儲存格,儲存格）
• 例子：=CONCATENATE(D3,E3)，把 India 與 Delhi 合併起來 > IndiaDelhi
• =CONCATENATE(D3," ",E3)，也是把 India 與 Delhi 合併起來，不過兩個文字之間有空格 > India Delhi

 

Excel公式06｜IF

IF 函數是一種條件函數，可以執行基於給定條件成立與否的邏輯測試。其基本語法為=IF(條件, 值1, 值2)，其中“條件”是需要測試的條件，“值1”是條件成立時的返回值，“值2”是條件不成立時的返回值。這個函數非常適用於數據分析、預算編制、成績計算等多種場景，能夠有效地幫助用戶根據不同條件快速做出決策。

• 功能：有條件計算儲存格
• 公式：=IF（儲存格=條件,結果,結果）
• 例子：=IF(D3="India","Hindi","Other Language")，由於 D3 是 India，因此在輸入公式的儲存格（M3）會顯示 Hindi。

如果把公式裡的 D3 改為 D5（USA），由於 D5 不是 India，因此 M3 會顯示 Other Language。

在 M3 儲存格輸入公式 ： =IF(F3<=3000,"Work Harder","Good Job")

如果 F3 的數字細過或等如 3000，M3 顯示 Work Harder，否則 M3 顯示 Good Job。

Excel公式07｜COUNTIF

COUNTIF函數允許用戶根據指定的條件對範圍內的單元格進行計數。其基本格式為=COUNTIF(範圍, 條件)，其中“範圍”是需要查找的單元格範圍，“條件”是用於計數的條件，可以是數字、表達式或文字。這個功能在數據分析時特別有用，如計算某一範圍內符合特定條件的數據點數量。例如，計算一列成績中及格（分數>=60）的學生數量。COUNTIF函數為數據篩選和條件統計提供了強大的支持，使得複雜數據的處理變得簡單高效。

• 功能：有條件統計儲存格
• 公式：=COUNTIF（儲存格範圍, 條件）
• 例子：=COUNTIF(C3:C57,"BMW")，統計在 C3 至 C57 儲存格裡，有幾多個儲存格是 BMW。

COUNTIF 就是把 COUNT 與 IF 混合使用。 

Excel公式08｜VLOOKUP

VLOOKUP函數是一種查找和參照工具，允許用戶在表格的一列中搜索特定值，並從同一行的另一列返回相關的值。其基本語法為=VLOOKUP(查找值, 範圍, 列號, [近似匹配])。這個功能在處理大量數據，如客戶信息、產品價格表等時非常有用。它可以幫助用戶快速找到所需信息，提高工作效率。不過，使用VLOOKUP時需要確保查找列位於範圍的最左側。

• 功能：尋找範圍內的儲存格
• 公式：=VLOOKUP（條件,儲存格範圍,由選定儲存格範圍開始計算的行數,絕對值）
• 例子：在 P3 儲存格輸入 =VLOOKUP(O3,C2:F11,4,0)，要搵出在 C2 至 F11 儲存格範圍裡，哪個儲存格符合 O3（Honda）條件（即 C2 至 F11 哪個儲存格是 Honda），搵到這個儲存格後，在 P3 顯示「第 4 行」（由選定儲存格範圍開始計算為第 1 行，即 C 行是「第 1 行」，「第 4 行」就是 F）的儲存格內容，答案是 3100。

 

Excel公式09｜DropDown List

DropDown List功能允許用戶在單元格中創建一個下拉菜單，從而可以從預定義的列表中選擇數據。這項功能非常適用於需要從固定選項集中選擇值的情況，如填寫表格或數據錄入時。通過設置數據驗證規則，可以輕鬆創建下拉列表，提高數據錄入的準確性和效率。此外，DropDown List功能還能幫助維持數據的一致性和整潔，對於數據分析和報表製作尤為重要。

• 功能：創建下拉式表格 
• 用法：這個不是 Excel Formula 試算表公式，但它是一個很實用的功能，有助管理試算表。
• 例子：在 O3 儲存格，揀 Data > Data Validation；在 Allow 選項揀 List；在 Source 選項，選擇你需要的範圍，這個範圍裡的儲存格，都被放到下拉式表格裡，因此可任你選擇改變 O3 的儲存格內容。

 

Excel公式10｜SUMIF

SUMIF函數允許用戶根據特定條件對範圍內的數值進行求和。這個函數的基本語法是=SUMIF(範圍, 條件, [求和範圍])，其中“範圍”是要檢查條件的單元格範圍，“條件”是用於篩選哪些單元格需要加總的條件，而“求和範圍”是實際需要求和的數值所在的範圍。如果省略“求和範圍”，則默認在“範圍”內求和。SUMIF功能非常適合於需要對符合特定條件的數值進行加總的場景，如計算特定產品的銷售總額、統計特定分數段的學生人數等。

• 功能：有條件加總儲存格
• 公式：=SUMIF（儲存格範圍, 條件, 儲存格範圍）
• 例子：在 O8 儲存格輸入公式 =SUMIF(C3:C57,O3,F3:F57)；

在 C3 至 C57 文字儲存格範圍裡，只要文字是與 O3（Aston Martin）相同，那麼就會把與 C3:C57 相對應的 F3:F57 數字儲存格加起來。 

在 O3 儲存格做了表格，就可以很容易地改變 O3 儲存格內容，從而改變了公式的參數（O3 文字條件），然後搵出其它需要的答案。

Excel公式11│TEXT（格式化文字）

將數值或日期轉換為指定格式的文字，便於報表展示。常用於日期格式化及貨幣、百分比等格式的顯示調整。

• 功能：將數值轉換為特定格式的文字，例如日期、貨幣格式等。
• 公式：=TEXT(數值, 格式)
• 例子：將 2025/01/01 轉換成 2025年1月1日：
  =TEXT(A1, "yyyy年m月d日")
  
  

Excel公式12│PROPER（字首大寫）

自動將文字中的每個單字首字母轉為大寫，其他字母變小寫。適合用於名稱、標題或地名的文字格式化。

• 功能：將文字轉換為每個單字的字首大寫。
• 公式：=PROPER(文字)
• 例子：將 hong kong 轉換為 Hong Kong：
  =PROPER(A1)
  
  

Excel公式13│ISNUMBER（檢查是否為數字）

判斷單元格中的內容是否為數字，返回 TRUE 或 FALSE。可用於篩選數據或驗證輸入內容。

• 功能：判斷指定的單元格是否為數字。
• 公式：=ISNUMBER(值)
• 例子：如果 A1 是數字，返回 TRUE，否則返回 FALSE：
  =ISNUMBER(A1)
  
  

Excel公式14│SEARCH（搜尋文字位置）

搜尋特定文字在字串中的位置，返回數值表示位置。不區分大小寫，適合檢查內容是否包含某些字詞。

• 功能：搜尋特定文字在字串中的位置（不分大小寫）。
• 公式：=SEARCH(要找的文字, 目標字串, [開始位置])
• 例子：搜尋 apple 在 I like apple pie 中的位置：
  =SEARCH("apple", A1)
  
  

Excel公式15│NETWORKDAYS（計算工作日天數）

計算兩個日期間的工作日數，排除週末及指定假期。用於排期、項目計劃或請假天數計算。

• 功能：計算兩個日期間的工作日數（不包括週末）。
• 公式：=NETWORKDAYS(開始日期, 結束日期, [假期])
• 例子：計算 2025/01/01 至 2025/01/31 的工作日數：
  =NETWORKDAYS(A1, A2)
  
  

Excel公式16│LEN（計算字元長度）

統計文字或字串的總字元數，包括空格與標點符號。常用於驗證輸入長度或處理文字數據。

• 功能：計算文字或字串的長度。
• 公式：=LEN(文字)
• 例子：計算 Hello World! 的字元長度（包括空格）：
  =LEN(A1)
  
  

Excel公式17│SUBSTITUTE（替換文字）

替換字串中的指定內容為新的文字，支持多次替換。用於批量修正文字錯誤或格式統一。

• 功能：將字串中的特定文字替換為其他文字。
• 公式：=SUBSTITUTE(文字, 要替換的文字, 新文字)
• 例子：將 apple 替換為 orange：
  =SUBSTITUTE(A1, "apple", "orange")
  
  

Excel公式18│UNIQUE（提取唯一值）

從數據範圍中提取不重複的唯一值，便於製作去重列表或統計分析。特別適用於大數據處理。

• 功能：從一列數據中提取不重複的值。
• 公式：=UNIQUE(範圍)
• 例子：從 A1:A10 中提取唯一值：
  =UNIQUE(A1:A10)
  
  

Excel公式19│SORT（排序數據）

根據指定的條件或列對數據進行排序，支持升序或降序。適合整理列表或優化表格呈現順序。

• 功能：根據指定順序排序數據。
• 公式：=SORT(範圍, [排序列], [排序順序])
• 例子：將 A1:A10 按升序排序：
  =SORT(A1:A10, 1, TRUE)
  
  

Excel公式20│FILTER（篩選數據）

根據條件自動篩選數據，生成符合條件的結果集。可用於動態篩選報表數據或分析特定條件的資料。

• 功能：根據條件篩選數據。
• 公式：=FILTER(範圍, 條件, [如果空白顯示的值])
• 例子：篩選出 A1:A10 中大於 50 的數值：
  =FILTER(A1:A10, A1:A10>50)
  
  

【Office 技巧相關報道】

【熱門報道】

即刻【按此】，用 App 睇更多產品開箱科技影片

Source：ezone.hk