Microsoft Excle 常用功能筆記

一、VLOOKUP 函數使用

用途

場景 說明
人事管理 依員工編號找姓名、部門、薪資。
產品銷售 依產品代碼查詢價格、供應商。
成績系統 根據學生成績查對應等級。
財務對帳 根據帳號查找交易明細。
庫存管理 根據品項查詢剩餘數量。
數據匹配整合 把不同表格的資料整合在一起。

 語法

    lookup_value

        要查找的值(可以是數字、文字、或儲存格參照)。

        例如:A2、"蘋果"、101。

    table_array

        要搜尋的資料範圍,第一欄必須包含查找值

        例如:A2:D20。

    col_index_num

        在 table_array 中要返回的欄位編號(第一欄為 1)。

        例如:2 表示返回範圍中的第二欄。

    [range_lookup](可選)

        TRUE(或省略):模糊匹配。

  • 匹配前做排序
  • 只能數字之間,才能才能做匹配
  • 匹配數字只找小於等於的最大值

        FALSE:精確匹配(最常用)。

        建議多數情況用 FALSE,避免錯誤。

常見限制

  1. 只能向右查找(查找欄必須在範圍第一欄)。

  2. 回傳第一個匹配值,不會找出所有結果。

  3. 模糊匹配需要排序,否則會返回錯誤。

  4. 大型資料表會降低效能。

  5. 互相之間資料資料型態不同是不能匹配,但可以在公式中做轉換

    • 文字轉數字匹配,可以使用文字*1
    • 數字轉文字匹配,可以使用數字&""

二、Index 使用

用法

INDEXExcel 中非常靈活的查詢函數,
主要用途是 根據指定的「列號」與「欄號」,返回表格中對應位置的資料

常見用途:

  1. 精準定位取值(根據行列座標抓取資料)。

  2. 比 VLOOKUP 更靈活的查找(可向左查找,無欄位位置限制)。

  3. MATCH 搭配,建立動態查找公式(INDEX + MATCH)。

  4. 多維資料查找(支援二維、甚至三維資料表)。

  5. 建立動態範圍(作為其他公式的資料來源)。

語法

=Index(array, row_num, [column_num])
  • array:資料範圍或陣列,例如 A1:C10
  • row_num:要取資料範圍的列號(在陣列中的位置,不是工作表的實際列號)。
  • [column_num]:要取資料範圍的欄號(可選,若省略則預設為第 1 欄)

三、MATCH函數使用

用途 

在一列或一行中搜尋特定的值,並返回該值在範圍中的相對位置(不是儲存格地址)。

常見用途:

  1. 找出某個值在資料範圍中的位置(第幾列或第幾欄)。

  2. 搭配 INDEX 做動態查找(取出對應的資料)。

  3. 計算排序、排名位置。

  4. 配合動態範圍公式使用。

語法

=MATCH(lookup_value, lookup_array, [match_type])
  • 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到現成的天數

日期運算特性

  1. 日期加減數字 → 天數移動

    • =TODAY()+1 → 明天

    • =TODAY()-7 → 七天前

  2. 日期減日期 → 天數差

    • =TODAY()-DATE(2025,1,1) → 距離今年 1 月 1 號幾天

  3. 跨月份計算

    • 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)。

留言

這個網誌中的熱門文章

簽字筆 奇異筆 光碟筆 油漆筆 麥克筆 差別在哪

DC 常用指令

ultravnc 遠端遙控教學軟體