Microsoft Excle 常用功能筆記
一、VLOOKUP 函數使用
用途
場景 | 說明 |
---|---|
人事管理 | 依員工編號找姓名、部門、薪資。 |
產品銷售 | 依產品代碼查詢價格、供應商。 |
成績系統 | 根據學生成績查對應等級。 |
財務對帳 | 根據帳號查找交易明細。 |
庫存管理 | 根據品項查詢剩餘數量。 |
數據匹配整合 | 把不同表格的資料整合在一起。 |
語法
lookup_value
要查找的值(可以是數字、文字、或儲存格參照)。
例如:A2、"蘋果"、101。
table_array
要搜尋的資料範圍,第一欄必須包含查找值。
例如:A2:D20。
col_index_num
在 table_array 中要返回的欄位編號(第一欄為 1)。
例如:2 表示返回範圍中的第二欄。
[range_lookup](可選)
TRUE(或省略):模糊匹配。
- 匹配前做排序
- 只能數字之間,才能才能做匹配
- 匹配數字只找小於等於的最大值
FALSE:精確匹配(最常用)。
建議多數情況用 FALSE,避免錯誤。
常見限制
-
只能向右查找(查找欄必須在範圍第一欄)。
-
回傳第一個匹配值,不會找出所有結果。
-
模糊匹配需要排序,否則會返回錯誤。
-
大型資料表會降低效能。
互相之間資料資料型態不同是不能匹配,但可以在公式中做轉換
- 文字轉數字匹配,可以使用文字*1
- 數字轉文字匹配,可以使用數字&""
二、Index 使用
用法
INDEX 是 Excel 中非常靈活的查詢函數,
主要用途是 根據指定的「列號」與「欄號」,返回表格中對應位置的資料。
常見用途:
-
精準定位取值(根據行列座標抓取資料)。
-
比 VLOOKUP 更靈活的查找(可向左查找,無欄位位置限制)。
-
與 MATCH 搭配,建立動態查找公式(INDEX + MATCH)。
-
多維資料查找(支援二維、甚至三維資料表)。
-
建立動態範圍(作為其他公式的資料來源)。
語法
- array:資料範圍或陣列,例如
A1:C10
。 - row_num:要取資料範圍的列號(在陣列中的位置,不是工作表的實際列號)。
- [column_num]:要取資料範圍的欄號(可選,若省略則預設為第 1 欄)
三、MATCH函數使用
用途
在一列或一行中搜尋特定的值,並返回該值在範圍中的相對位置(不是儲存格地址)。
常見用途:
-
找出某個值在資料範圍中的位置(第幾列或第幾欄)。
-
搭配 INDEX 做動態查找(取出對應的資料)。
-
計算排序、排名位置。
-
配合動態範圍公式使用。
語法
lookup_value
要查找的值(可以是數字、文字、或儲存格參照)。-
lookup_array
要搜尋的範圍(必須是一列或一欄)。 -
[match_type](可選)
-
1
(預設):找小於或等於查找值的最大值(升序排序)。 -
0
:精確匹配(最常用)。 -
-1
:找大於或等於查找值的最小值(降序排序)。
-
四、 SUMIFS 與 COUNTIFS 函數使用
用途
在多個條件下,對符合條件的數值加總與次數。
在單個條件下,將函數名S拿掉,對符合條件的數值加總與次數。
語法
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)
- sum_range:要加總的範圍。
- criteria_range1:第一個條件檢查範圍。
- criteria1:第一個條件。
- criteria_range2, criteria2:可選,第二條件、第三條件…
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
- criteria_range1:第一個條件檢查範圍。
- criteria1:第一個條件。
- criteria_range2, criteria2:可選,第二條件、第三條件…
五、 TODAY、DATE、YEAR、MONTH 與 DAYS 函數使用
Excel 日期的本質
- Excel 的日期其實是數字(序列值,Serial Number)。
- 這個數字代表從基準日開始經過的天數。
- 預設在 Windows 版 Excel(1900 系統)
- 將日期儲存格格式,轉成『數字』,會變成45866 ,表示從1900/1/1到現成的天數
日期運算特性
-
日期加減數字 → 天數移動
-
=TODAY()+1
→ 明天 -
=TODAY()-7
→ 七天前
-
-
日期減日期 → 天數差
-
=TODAY()-DATE(2025,1,1)
→ 距離今年 1 月 1 號幾天
-
-
跨月份計算
-
DATE
函數會自動進位或退位:-
=DATE(2025, 14, 5)
→ 2026/2/5 -
=DATE(2025, 1, 35)
→ 2025/2/4
-
-
常見陷阱
問題 | 原因 | 解法 |
---|---|---|
顯示 #### | 欄寬不足或日期序號為負數(1900 系統不能處理 1900/1/1 以前的日期) | 調整欄寬或改成文字格式 |
日期變成文字 | 輸入格式 Excel 認不出(例如 2025-13-1 ) |
請用合法格式或 DATE 函數 |
日期運算結果錯亂 | 1900 / 1904 系統切換造成 | 在 Excel 選項檢查「使用 1904 日期系統」設定 |
用途
- TODAY() 傳回系統目前的日期(不含時間)。每天重新計算會自動更新。
- DATE(year, month, day) 依照指定的年份、月份與日期,組合成一個合法的日期值。
- YEAR(serial_number) 從日期值中擷取「年份」(四位數)。
- MONTH(serial_number) 從日期值中擷取「月份」(1~12)。
- DAY(serial_number) 從日期值中擷取「日」(1~31)。
留言