Excel算式怎麼打？從入門到精通，掌握實用公式技巧，提升你的工作效率

Byadmin

「唉呀，我的Excel算式又跑不動了！」相信許多朋友在處理報表、分析數據時，都曾經遇到這樣的冏境吧？別擔心，這絕對是人之常情！尤其是在剛接觸Excel，或是需要處理較複雜的計算時，Excel算式怎麼打，確實是個讓人頭痛的問題。但是，只要掌握了正確的方法和一些常用的公式，你會發現Excel根本就是你的超級助手！今天，我就要帶大家從最基礎的開始，一步一步解鎖Excel算式的奧秘，讓你也能成為Excel高手，讓數據分析變得輕鬆又有效率！

Excel算式入門：為什麼要學？

很多人可能會問，Excel算式有那麼重要嗎？我可以直接手動計算啊。確實，對於簡單的幾筆數據，手動計算或許可行。但當你的數據量龐大，或是需要頻繁更新、進行多種組合分析時，手動計算不僅耗時耗力，還極容易出錯。一個小小的數字輸入錯誤，就可能導致整個報表的結果偏差。這時候，Excel算式就顯得尤為重要了！

透過學習Excel算式，你可以：

  • 大幅提升工作效率： 自動化計算，省去重複性的人工操作。
  • 確保數據準確性： 減少人為錯誤，讓分析結果更可靠。
  • 進行複雜分析： 掌握各種函數，可以進行條件判斷、查找、匯總等進階分析。
  • 製作動態報表： 隨著數據更新，報表結果也能自動更新，隨時掌握最新狀況。
  • 展現專業能力： 熟練運用Excel算式，是現代職場不可或缺的基本技能之一。

我自己剛開始接觸Excel時，也覺得公式很難記，常常需要翻書或是上網搜尋。但隨著使用的次數越來越多，加上一些前輩的指導，我發現其實很多公式的邏輯是相通的。只要理解了最核心的概念，再搭配一些常用函數的練習，你會發現「Excel算式怎麼打」這個問題，將不再是你的絆腳石，而是你通往數據分析之路的墊腳石！

Excel算式基礎：從「=」號開始

在Excel中，任何一個算式或公式，都必須以「=」號開頭。這是Excel辨識你輸入的是計算指令，而不是單純的文字或數字的關鍵。所以，當你想在儲存格中進行計算時，請務必記得在輸入公式前，先打上「=」。

認識Excel的基本運算符號

在進入複雜的函數之前，我們先來認識一下Excel最基礎的運算符號，這些符號跟我們平常數學課學的一樣：

  • 加法： + （例如：=A1+B1）
  • 減法： - （例如：=A1-B1）
  • 乘法： * （例如：=A1*B1）
  • 除法： / （例如：=A1/B1）
  • 指數： ^ （例如：=A1^2，表示A1的平方）

範例：

假設A1儲存格是10，B1儲存格是5。

  • 在C1儲存格輸入 =A1+B1，結果會是 15。
  • 在C2儲存格輸入 =A1*B1，結果會是 50。

儲存格參照的應用

上面這些例子，我們都用到了「儲存格參照」，例如 A1、B1。這代表Excel會去讀取A1和B1這兩個儲存格內的數值，然後進行運算。這種參照方式的好處是，當你修改了A1或B1的數值時，含有該參照的公式結果也會自動更新，這就是Excel強大的自動化之處！

除了直接點選儲存格，你也可以直接輸入儲存格的名稱。如果你習慣用鍵盤輸入，只要記住欄位名稱（A, B, C…）和列號（1, 2, 3…）就可以囉。

混合使用運算符號與括號

跟數學一樣，Excel也遵循運算優先順序的規則：先乘除後加減，括號內的優先計算。所以，如果你需要控制運算的先後順序，就要善用括號 ()。

範例：

假設A1=10, B1=5, C1=2。

  • 輸入 =A1+B1*C1，Excel會先計算 B1*C1 (5*2=10)，然後再加上A1 (10+10)，結果是 20。
  • 輸入 =(A1+B1)*C1，Excel會先計算括號內的 A1+B1 (10+5=15)，然後再乘以C1 (15*2)，結果是 30。

看到這裡，你應該對Excel算式的最基本結構有了概念。記住，一切都從「=」開始，然後是你的運算符號和儲存格參照。是不是沒有想像中那麼難呢？

Excel函數：讓計算更智慧

當我們需要進行更複雜、更專業的計算時，Excel的「函數」（Function）就派上用場了！函數就像是Excel內建好的各種「工具」，可以幫我們快速完成特定的任務，省去自己寫複雜公式的麻煩。理解函數，是掌握「Excel算式怎麼打」的關鍵所在。

什麼是函數？

簡單來說，函數就是一個預先定義好的公式，它接收特定的輸入值（稱為「引數」或「參數」），然後返回一個計算結果。每個函數都有一個特定的名稱，例如 SUM、AVERAGE、IF 等。

函數的基本結構是：

=函數名稱(引數1, 引數2, ...)

引數可以是：

  • 儲存格參照： 例如 A1、B2:B5 (一整個範圍)。
  • 數字： 例如 10、0.05。
  • 文字： 例如 "及格" (文字需要用雙引號括起來)。
  • 其他函數： 函數也可以作為另一個函數的引數，這稱為「巢狀函數」。

常用的Excel函數介紹與應用

接下來，我們介紹幾個最常用、最實用的Excel函數，讓你的數據處理能力瞬間升級！

1. SUM 函數：加總的好幫手

這是最基本但也最重要的函數之一，用於計算一系列數字的總和。

  • 語法： =SUM(number1, [number2], ...)
  • 用途： 可以加總單一儲存格、儲存格範圍，或是混合使用。

範例：

  • =SUM(A1:A10)：計算A1到A10儲存格的總和。
  • =SUM(A1, B1, C1)：計算A1、B1、C1這三個儲存格的總和。
  • =SUM(A1:A5, C1:C5)：計算A1到A5以及C1到C5這兩段範圍的總和。

我的小技巧： 當你需要加總一整欄或一整列的數據時，可以選取數字範圍下方的空白儲存格（或右方的空白儲存格），然後按下「常用」工具列上的「自動加總」圖示（一個希臘字母 Σ），Excel會自動幫你填入 SUM 公式，是不是超方便！

2. AVERAGE 函數：計算平均值

計算一組數字的平均值。

  • 語法： =AVERAGE(number1, [number2], ...)
  • 用途： 同 SUM 函數，可以處理單一儲存格、範圍，或混合使用。

範例：

  • =AVERAGE(B1:B20)：計算B1到B20儲存格的平均值。

3. COUNT 函數：計算數值儲存格的個數

計算參數列表中，包含數字的儲存格數量。

  • 語法： =COUNT(value1, [value2], ...)
  • 用途： 只能計算包含「數字」的儲存格。空白儲存格、文字儲存格都不會被計算在內。

範例：

  • =COUNT(A1:A10)：計算A1到A10範圍內，有多少個儲存格含有數字。

4. COUNTA 函數：計算非空白儲存格的個數

計算參數列表中，不為空白的儲存格數量。

  • 語法： =COUNTA(value1, [value2], ...)
  • 用途： 只要儲存格不是空的，無論是數字、文字、錯誤值，都會被計算進去。

範例：

  • =COUNTA(C1:C10)：計算C1到C10範圍內，有多少個儲存格是「有內容」的。

為什麼要區分 COUNT 和 COUNTA？ 這兩個函數在判斷數據完整性時非常有用。例如，你統計一份名單，想知道總共有多少人填寫了資料，就會用 COUNTA；但如果你只想知道其中有多少人的「分數」被記錄下來，就用 COUNT。這樣是不是更清楚它們的用途了？

5. MAX 函數與 MIN 函數：找出最大值與最小值

快速找出一個數值範圍中的最大值或最小值。

  • 語法： =MAX(number1, [number2], ...) ； =MIN(number1, [number2], ...)
  • 用途： 找出數列中的最大值或最小值。

範例：

  • =MAX(D1:D50)：找出D1到D50儲存格中的最大數值。
  • =MIN(E1:E50)：找出E1到E50儲存格中的最小數值。

6. IF 函數：邏輯判斷的瑞士刀

這是功能非常強大的一個函數，可以根據你設定的條件，來決定要顯示什麼結果。它就像一個「如果…那麼…就…否則…」的判斷式。

  • 語法： =IF(logical_test, value_if_true, value_if_false)
  • 說明：
    • logical_test：你設定的判斷條件，例如 A1>80。
    • value_if_true：如果判斷條件為「真」（True）時，要顯示的結果。
    • value_if_false：如果判斷條件為「假」（False）時，要顯示的結果。

範例：

假設A欄是學生的分數，我們想在B欄判斷學生成績是否及格（及格分數為60分）。

  • 在B1儲存格輸入 =IF(A1>=60, "及格", "不及格")

這樣，如果A1分數大於等於60，B1就會顯示「及格」，否則就顯示「不及格」。然後將這個公式往下拖曳填滿，就可以快速判斷所有學生的成績了！

進階應用： IF 函數還可以巢狀使用，也就是在 value_if_true 或 value_if_false 的地方，再放入一個 IF 函數，來進行多層次的判斷。例如，判斷成績是「優」、「良」、「及格」、「不及格」。

=IF(A1>=90, "優", IF(A1>=80, "良", IF(A1>=60, "及格", "不及格")))

7. VLOOKUP 函數：查找的王者

VLOOKUP 是Excel中用途最廣泛、也最能體現其強大之處的函數之一。它讓你可以在一個表格中，根據一個關鍵值，去查找另一個表格或同一表格的其他欄位對應的資料。

  • 語法： =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • 說明：
    • lookup_value：你要查找的目標值，也就是你的「關鍵字」。
    • table_array：要從哪個範圍去查找資料。非常重要： 查找值 (lookup_value) 必須位於 table_array 的最左邊一欄。
    • col_index_num：在 table_array 中，你要回傳第幾欄的資料。最左邊那一欄是 1，往右依序是 2, 3, 4…。
    • [range_lookup]：決定是進行「精確比對」（輸入 FALSE 或 0）還是「近似比對」（輸入 TRUE 或 1，通常用於數值區間判斷）。預設是 TRUE，但我們通常需要精確比對，所以建議輸入 FALSE。

範例：

假設我們有一個「訂單資料表」(Sheet1)，其中有「產品編號」(A欄) 和「訂購數量」(B欄)。

另外，我們有一個「產品價格表」(Sheet2)，其中有「產品編號」(A欄) 和「產品單價」(B欄)。

現在，我們想在「訂單資料表」中，根據「產品編號」，找出對應的「產品單價」，並計算該筆訂單的總價。

  • 在 Sheet1 的 C1 儲存格（假設這是你要放單價的欄位）輸入：
  • =VLOOKUP(A1, Sheet2!$A$1:$B$100, 2, FALSE)
  • 然後在 D1 儲存格（這是要放總價的欄位）輸入：
  • =C1*B1 (C1是單價，B1是訂購數量)

重要提醒：

  • Sheet2!$A$1:$B$100 中的 $ 符號，是鎖定儲存格範圍，這樣當你把公式往下拖曳時，價格表的範圍才不會跑掉。
  • 2 代表我們要從 Sheet2 的第二欄（也就是產品單價）去抓資料。
  • FALSE 代表我們要找「完全相同」的產品編號。

VLOOKUP 是初學者最常搞混的函數之一，但一旦你理解了它的邏輯，它絕對是你的數據處理神器！

Excel進階技巧：讓你的算式更強大

學會了基礎公式和常用函數後，我們來聊聊一些能讓你的Excel算式更上一層樓的技巧。

絕對參照、相對參照與混合參照

在前面 VLOOKUP 的例子中，我們提到了 $ 符號，這就是所謂的「絕對參照」。Excel 預設的參照方式是「相對參照」，意思是當你複製公式到其他儲存格時，公式中的儲存格參照會跟著移動。但有時候，我們希望某個儲存格的參照是固定的，這時候就需要絕對參照或混合參照。

  • 相對參照： A1 （預設，複製公式時會變動）
  • 絕對參照： $A$1 （無論複製到哪裡，永遠參照 A1）
  • 混合參照： $A1 （欄固定，列不固定）或 A$1 （列固定，欄不固定）

按下 F4 鍵，可以在相對參照、絕對參照、混合參照之間循環切換，非常好用！

公式中的邏輯運算符

在 IF 函數的判斷條件中，除了 >、<、= 之外，我們還可以加入邏輯運算符，來處理更複雜的條件。

  • AND 函數： =AND(logical1, [logical2], ...) ，所有條件都為真時，結果才為真。
  • OR 函數： =OR(logical1, [logical2], ...) ，只要有一個條件為真，結果就為真。
  • NOT 函數： =NOT(logical) ，反轉邏輯值。

範例：

判斷學生成績是否同時達到 A1>=80 (優良) 且 B1 (出勤率) >=90%。

=IF(AND(A1>=80, B1>=0.9), "優良", "需加強")

善用 SUMIF 和 COUNTIF

這兩個函數是 SUM 和 COUNT 的進階版本，它們可以在滿足特定條件時，才進行加總或計數。

  • SUMIF 函數： =SUMIF(range, criteria, [sum_range])
    • range：要判斷條件的範圍。
    • criteria：判斷的條件（例如 ">100"、"蘋果"）。
    • sum_range：要加總的實際範圍（如果與 range 相同，可省略）。
  • COUNTIF 函數： =COUNTIF(range, criteria)
    • range：要計數的範圍。
    • criteria：計數的條件。

範例：

有一個銷售紀錄表，A欄是產品名稱，B欄是銷售金額。

  • 我想知道「蘋果」的總銷售金額： =SUMIF(A1:A100, "蘋果", B1:B100)
  • 我想知道銷售金額超過 500 元的紀錄有多少筆： =COUNTIF(B1:B100, ">500")

SUMIF 和 COUNTIF 在進行分組統計時非常常用，例如統計各個產品的銷售額、各個業務員的業績量等等。

總結：持續練習，成為Excel算式達人

「Excel算式怎麼打」這個問題，其實並沒有標準答案，因為你需要處理的數據和情境千變萬化。但今天我們學到的基礎公式、常用函數，以及進階技巧，絕對是你踏入Excel算式世界的堅實基礎。最重要的一點，就是「持續練習」！

剛開始可能會覺得有點陌生，公式記不住，邏輯也理不清。這都沒關係！我建議大家可以找一些實際的工作報表，或是網路上公開的數據集，試著用今天學到的公式和函數去動手操作。遇到問題時，別急著放棄，試著理解為什麼會出錯，然後再上網搜尋相關的解決方案。Excel的說明檔也是一個很好的資源，雖然有時候有點生硬，但裡面的解釋通常很精確。

記住，Excel算式是一個不斷學習和累積的過程。每一次成功打出一個有用的公式，都會帶給你滿滿的成就感，也會讓你對Excel的能力有更深的認識。相信我，只要你願意花時間去嘗試，你一定能克服「Excel算式怎麼打」的難題，讓Excel成為你工作上最得力的助手！

常見問題與專業解答

Q1: 我的Excel公式出現 #VALUE! 錯誤，是什麼原因？

#VALUE! 錯誤是Excel中最常見的錯誤之一，通常表示公式中引數的資料類型不正確。例如，你嘗試將文字和數字相加，或是函數期望的是數字，但卻傳入文字。最常見的原因是：

  • 儲存格中含有文字，但公式嘗試將其視為數字。
  • 函數的引數傳遞錯誤，例如 VLOOKUP 的 col_index_num 設定了不存在的欄位。
  • 公式中使用了不兼容的運算符號，例如在文字串中使用數學運算。

解決方法： 仔細檢查公式中涉及的儲存格，確保它們的資料類型符合公式的需求。可以使用 ISNUMBER()、ISTEXT() 等函數來檢查儲存格的資料類型。同時，檢查函數的引數是否正確傳遞。

Q2: 我想複製公式，但參照的儲存格卻跑掉了，怎麼辦？

這就是上面提到的「相對參照」的問題。當你複製公式時，Excel會自動調整儲存格參照，讓它對應到新的位置。如果你希望某個參照是固定的，例如在計算匯率時，匯率的儲存格就不應該跑掉，那麼你需要使用「絕對參照」或「混合參照」。

解決方法： 在公式編輯列中，選取你希望固定的儲存格參照（例如 A1），然後按下 F4 鍵。每按一次 F4，參照的模式就會切換：A1 (相對) -> $A$1 (絕對) -> $A1 (混合) -> A$1 (混合)。你也可以手動輸入 $ 符號來達到同樣的效果。

Q3: VLOOKUP 找不到我要的資料，而且顯示 #N/A 錯誤，該怎麼辦？

#N/A 錯誤表示 VLOOKUP 在查找範圍中找不到你指定的 lookup_value。可能的原因有：

  • 資料不符： 查找值與查找範圍的最左欄資料完全不符。請注意，即使是肉眼看起來一樣，也可能是因為有額外的空格、換行符、或是數字格式與文字格式不同。
  • 查找範圍設定錯誤： table_array 的範圍沒有包含你要查找的資料，或者 lookup_value 不在 table_array 的最左邊一欄。
  • 近似比對誤用： 如果你設定了 range_lookup 為 TRUE (或省略)，但查找的資料並非排序過的，也可能導致找不到。
  • 多餘的空格： 這是最常見的隱藏殺手。請檢查查找值和查找範圍最左欄的儲存格，確認是否有額外的空格。可以使用 TRIM() 函數來清除。

解決方法：

  1. 檢查資料： 仔細比對 lookup_value 和 table_array 的第一欄，確保沒有多餘的空格、換行符，並且資料格式一致（數字、文字）。可以使用 =TRIM(A1) 清除A1儲存格的空格，然後再進行查找。
  2. 確認範圍： 確保 table_array 的範圍涵蓋了你要查找的資料，並且 lookup_value 確實位於該範圍的最左側欄位。
  3. 設定精確比對： 對於大多數情況，請務必在 VLOOKUP 公式最後加上 FALSE 或 0，以進行精確比對。
  4. 使用 INDEX/MATCH 組合： 如果你的查找值不在表格的最左側，VLOOKUP 就無能為力了。這時候，可以考慮使用 INDEX 和 MATCH 函數的組合，它們比 VLOOKUP 更靈活。

Q4: 我想同時滿足多個條件進行加總，SUMIF 只能用一個條件，怎麼辦？

你說得沒錯，SUMIF 只能處理單一條件。如果你需要同時滿足多個條件進行加總，就必須使用 SUMIFS 函數。

SUMIFS 函數語法： =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  • sum_range：要加總的範圍。
  • criteria_range1：第一個要判斷條件的範圍。
  • criteria1：第一個判斷條件。
  • criteria_range2：第二個要判斷條件的範圍。
  • criteria2：第二個判斷條件。
  • ...以此類推，可以設定多個條件。

範例： 假設 A欄是產品名稱，B欄是地區，C欄是銷售金額。我想找出「蘋果」在「台北」地區的總銷售金額。

=SUMIFS(C1:C100, A1:A100, "蘋果", B1:B100, "台北")

Q5: 如何讓我的 Excel 公式更容易閱讀和理解？

當公式變得越來越複雜時，閱讀和理解也會變得困難。有一些小技巧可以幫助你：

  • 使用有意義的儲存格名稱： 可以透過「公式」->「定義名稱」功能，為重要的儲存格或範圍取一個有意義的名稱（例如，將匯率儲存格命名為 ExchangeRate）。這樣在公式中就可以直接使用名稱，例如 =A1*ExchangeRate，比 =A1*$C$1 更好理解。
  • 善用顏色標記： 在公式中，Excel 通常會自動用不同的顏色標記不同的儲存格參照。你也可以手動調整，讓相關的儲存格顏色一致，幫助你快速辨識。
  • 拆解複雜公式： 如果一個公式非常長，可以考慮將它拆解成幾個步驟，利用中間的輔助欄位來計算。雖然會多幾個欄位，但更容易檢查和除錯。
  • 添加公式註解： 雖然 Excel 本身沒有直接的公式註解功能，但你可以利用旁邊的儲存格，用文字說明這個公式的用途或邏輯。
  • 使用 N() 函數： 有時候，公式中可能會包含錯誤值，如果不想讓錯誤值影響到後續計算，可以使用 N() 函數（雖然它最常用於將文字數字轉成數字，但在某些情況下，它能幫助處理潛在的錯誤）。

Excel算式怎麼打