袋鼠金融導讀|在處理大量資料的過程中, EXCEL 函數是不可或缺的利器。無論是進行資料比對、清理、統計,還是運用邏輯進行高效處理,善用 EXCEL 函數可以讓工作效率倍增。本篇將系統性整理各類函數,並針對常見應用場景,提供詳細的介紹和操作示例,讓您快速掌握必備技巧。另外,也可以參考其他職業相關文章:常見面試問題、作品集製作、履歷撰寫上的 7 大重點,讓你的準備更加得心應手!
EXCEL 實用函數懶人包 | ||||
---|---|---|---|---|
函數類別 | 函數名稱 | 功能簡介 | 應用場景 | 語法範例 |
關聯匹配類 | VLOOKUP | 在垂直範圍內查找目標值並返回對應值 | 跨表查找商品價格 | VLOOKUP(查找值, 查找範圍, 返回列號, [精確或近似匹配]) |
HLOOKUP | 在水平範圍內查找目標值並返回對應值 | 跨行查找資料 | HLOOKUP(查找值, 查找範圍, 返回行號, [精確或近似匹配]) | |
INDEX | 根據行列位置返回儲存格中的值 | 精準定位特定資料 | INDEX(範圍, 行號, 列號) | |
MATCH | 返回目標值在指定範圍中的位置 | 搭配 INDEX 進行靈活查找 | MATCH(查找值, 查找範圍, [匹配方式]) | |
RANK | 計算某數值在一組資料中的排名 | 生成成績或業績排名 | RANK(數值, 數據範圍, [排序方式]) | |
清理處理類 | TRIM | 清除字串開頭與結尾的空格 | 資料清理 | TRIM(字串) |
CONCATENATE / & | 合併多個儲存格的內容 | 合併姓名、地址等 | CONCATENATE(字串 1, 字串 2) 或 =A1 & B1 | |
LEFT / RIGHT / MID | 提取字串的部分內容 | 提取代碼、分類等 | LEFT(字串, 字元數) / =RIGHT(字串, 字元數) / =MID(字串, 起始位置, 字元數) | |
REPLACE / SUBSTITUTE | 替換字串中的部分內容 | 替換代碼、修正名稱 | REPLACE(字串, 起始位置, 替換字元數, 新字串) / =SUBSTITUTE(字串, 舊字串, 新字串) | |
FIND / SEARCH | 查找目標字元在字串中的位置 | 查找代碼或特定內容 | FIND(查找字元, 字串) / =SEARCH(查找字元, 字串) | |
邏輯運算類 | IF | 根據條件返回不同結果 | 自動分類合格與否 | IF(條件, 成立結果, 不成立結果) |
AND / OR | 判斷多條件的邏輯結果 | 複雜條件篩選 | AND(條件 1, 條件 2) / =OR(條件 1, 條件 2) | |
計算統計類 | MIN / MAX | 找出資料中的最小值與最大值 | 快速定位範圍內的極值 | MIN(範圍) / =MAX(範圍) |
AVERAGE | 計算平均值 | 計算成績平均分 | AVERAGE(範圍) | |
COUNT / COUNTIF | 統計資料筆數 | 計算符合條件的資料量 | COUNT(範圍) / =COUNTIF(範圍, 條件) | |
SUM / SUMIF | 計算總和,並可篩選條件 | 計算符合條件的銷售額 | SUM(範圍) / =SUMIF(範圍, 條件, 加總範圍) | |
SUMPRODUCT | 計算多範圍加權總和 | 利用權重進行加總分析 | SUMPRODUCT(範圍 1, 範圍 2) | |
時間序列類 | TODAY / NOW | 返回當前日期或時間 | 生成自動更新的日期 | TODAY() / =NOW() |
YEAR / MONTH / DAY | 提取日期中的年份、月份或日期 | 分析日期相關數據 | YEAR(日期) / =MONTH(日期) / =DAY(日期) | |
WEEKDAY | 返回日期對應的星期幾 | 計算工作日、排班表 | WEEKDAY(日期) | |
DATEDIF | 計算兩個日期之間的間隔 | 統計工齡、租賃時間 | DATEDIF(開始日期, 結束日期, 單位) |
目錄
EXCEL 函數:關聯匹配類-快速整合多表資料
處理多表或跨表資料時,關聯匹配類 EXCEL 函數提供了強大的支援,能夠輕鬆完成資料比對與提取。
1. VLOOKUP
功能:在指定範圍內查找目標值,並返回同列中對應的值。
語法:
=VLOOKUP(查找值, 查找範圍, 返回值所在的列號, [精確匹配或近似匹配])
應用範例:
當需要從一個銷售表中找到某商品的對應價格時,使用 VLOOKUP 可以快速完成查找。
2. HLOOKUP
功能:在指定範圍的首行查找目標值,並返回對應列中的值。
語法:
=HLOOKUP(查找值, 查找範圍, 返回值所在的行號, [精確匹配或近似匹配])
區別:VLOOKUP 是垂直查找,HLOOKUP 是水平查找。
3. INDEX
功能:根據指定的行列位置,返回儲存格中的值。
語法:
=INDEX(範圍, 行號, 列號)
應用範例:從一個數據表中,直接定位某個特定儲存格的值。
4. MATCH
功能:返回目標值在指定範圍中的位置。
語法:
=MATCH(查找值, 查找範圍, [匹配方式])
結合應用:搭配 INDEX 函數,可進行更靈活的資料查找。
5. RANK
功能:計算某數值在一組資料中的排名。
語法:
=RANK(數值, 數據範圍, [排序方式])
應用場景:快速生成成績排名或業績排序。
|
---|
EXCEL 函數:清理處理類-資料前置處理利器
清理原始資料是進行資料分析的第一步,這些 EXCEL 函數可以幫助處理空格、合併字串、字元替換等問題。
1. Trim
功能:清除字串開頭與結尾的空格。
語法:
=TRIM(字串)
應用範例:清理不必要的空格,讓資料更加規整。
2. Concatenate
功能:合併多個儲存格的內容。
語法:
=CONCATENATE(字串 1, 字串 2, …)
替代方式:可用 & 運算符替代,適合少量字串合併。
3. Left / Right / Mid
功能:從字串中提取部分字元。
- Left:從左開始提取字串。
語法:=LEFT(字串, 提取字元數) - Right:從右開始提取字串。
語法:=RIGHT(字串, 提取字元數) - Mid:從指定位置提取字串。
語法:=MID(字串, 起始位置, 提取字元數)
應用範例:提取商品代碼中的特定部分進行分類。
4. Replace / Substitute
功能:替換字串中的內容。
- Replace:根據位置替換字元。
語法:=REPLACE(字串, 起始位置, 替換字元數, 新字串) - Substitute:根據字串內容替換。
語法:=SUBSTITUTE(字串, 要替換的舊字串, 新字串)
區別:Replace 用於位置替換,Substitute 用於文本替換。
5. Find / Search
功能:查找指定字元在字串中的位置。
- Find:區分大小寫。
語法:=FIND(查找字元, 字串, [起始位置]) - Search:不區分大小寫。
語法:=SEARCH(查找字元, 字串, [起始位置])
|
---|
EXCEL 函數:邏輯運算類-精準判斷資料條件
1. IF
功能:根據條件返回不同的結果。
語法:
=IF(條件, 條件成立時的結果, 條件不成立時的結果)
應用範例:自動分類「合格」或「不合格」。
2. AND / OR
功能:進行多條件邏輯判斷。
- AND:所有條件均為 TRUE 時返回 TRUE。
- OR:只要有一條條件為 TRUE 即返回 TRUE。
語法:
=AND(條件 1, 條件 2, …)
=OR(條件 1, 條件 2, …)
EXCEL 函數:計算統計類-提升資料分析效率
常用函數總覽
- MIN / MAX:找出數據中的最小值與最大值。
- AVERAGE:計算平均值。
- COUNT / COUNTIF / COUNTIFS:統計資料筆數。
- SUM / SUMIF / SUMIFS:計算總和,並可按條件篩選。
進階應用:
- SUMPRODUCT:進行多範圍加權運算。
語法:=SUMPRODUCT(範圍 1, 範圍 2)
EXCEL 函數:時間序列類處理時間相關資料
常用函數
- TODAY:返回今天的日期。
- NOW:返回當前的日期與時間。
- YEAR / MONTH / DAY:提取年份、月份或日期。
- WEEKDAY:返回日期對應的星期幾。
- DATEDIF:計算兩個日期之間的間隔(天數、月數、年數)。
應用範例:計算工作日數、統計租賃時間等。
EXCEL 函數結語
學會使用 EXCEL 函數,不僅能提升資料處理效率,還能讓複雜的數據分析變得更加簡單有趣。從基礎函數到進階應用,本文涵蓋的內容適用於各類場景,讓您在面對任何 EXCEL 挑戰時,都能游刃有餘。快試著應用這些函數,開啟資料處理的新篇章!
EXCEL 函數常見問題
可以使用 Shift + 滑鼠點選終點,或是在打函數時按住 Ctrl + A 選取連續數據範圍。
通常是因為數據類型不符,檢查計算範圍是否包含文字或其他非數值資料。
可以使用函數名稱的首字母在函數清單中搜尋,或使用 fx 按鈕查看函數說明。