雅寶社區 · 頂客論壇 (AHPAL.COM)

🏠 生活小常識

Excel 完全指南：公式、圖表與自動化

發表時間：2026 年 10 月 01 日 | 更新日期：2026 年 10 月 01 日 | 編輯：雅寶社區編輯團隊

AVERAGE：平均。例如 =AVERAGE(B1:B10) 會計算平均值，而且會自動忽略空白儲存格。

COUNT／COUNTA：COUNT 只數「數字」的個數，COUNTA 則連文字、日期都算。例如 =COUNTA(C1:C10) 可以算出有幾筆資料被填寫。

IF：條件判斷。語法是 =IF(條件, 成立時顯示, 不成立時顯示)。例如成績大於等於 60 分顯示「及格」，否則顯示「不及格」：=IF(D2>=60,"及格","不及格")。

進階一點，你可以把 IF 和 AND、OR 搭配使用。例如要同時符合「國文及格」和「英文及格」才顯示「通過」：

=IF(AND(E2>=60,F2>=60),"通過","未通過")

如果要「其中一科及格就算通過」，就把 AND 改成 OR。

3. 資料查找雙雄：VLOOKUP 與 XLOOKUP

VLOOKUP 是 Excel 最知名的函數之一，用途是「從一張大表裡面找出某個值對應的資料」。它的語法如下：

=VLOOKUP(要找的值, 資料範圍, 第幾欄, 是否近似比對)

舉例來說，你有一張員工代號對照表在 A 到 C 欄（A 是代號、B 是姓名、C 是部門），你想在另一張表用代號查出姓名：

=VLOOKUP(F2,$A$2:$C$100,2,FALSE)

最後的 FALSE 代表「完全比對」，也就是一定要找到一模一樣的值。如果寫 TRUE 或省略，就會變成「近似比對」，很容易查錯，所以一般建議一律寫 FALSE。

不過 VLOOKUP 有幾個限制：只能往右查、插入欄位會導致欄位編號跑掉、無法從右邊查左邊。如果你用的是 Microsoft 365 或 Excel 2021 之後的版本，強烈建議改用 XLOOKUP：

=XLOOKUP(要找的值, 搜尋範圍, 回傳範圍)

XLOOKUP 不用算第幾欄，也不必擔心插入欄位，還能向左查找，甚至找不到時可以自訂顯示文字。例如：

=XLOOKUP(F2,$A$2:$A$100,$B$2:$B$100,"查無此人")

如果你公司還在用舊版 Excel，那就繼續用 VLOOKUP，但記得把資料範圍用絕對參照鎖定，並且養成「查找值放在最左欄」的好習慣。

4. 文字與日期函數：讓報表更人性化

工作中很多資料是文字格式，例如姓名、地址、產品編號。以下幾個文字函數非常實用：

LEFT／RIGHT／MID：從左、右、中間擷取文字。例如 =LEFT(A2,3) 可以取出前三碼。

LEN：計算字數，常用來檢查身分證字號或電話號碼長度是否正確。

TEXT：把數字轉成指定格式的文字，例如 =TEXT(A2,"0000") 會補零成四位數。

CONCAT／TEXTJOIN：合併文字。TEXTJOIN 還能設定分隔符號，例如把地址各部分用逗號串起來。

日期方面，TODAY() 會回傳今天日期，DATEDIF 可以計算兩個日期之間的差距。例如要算到職年資：

=DATEDIF(B2,TODAY(),"Y")

這樣就會回傳滿幾年。如果要更精確到月或日，把 "Y" 改成 "M" 或 "D" 即可。

二、圖表與視覺化：讓數字自己說故事

光是算出數字還不夠，真正厲害的報表是「一眼就能看懂」。圖表就是把冷冰冰的數字變成圖像，讓主管、客戶或同事快速掌握重點。但圖表不是隨便選一個就好，選錯圖表類型，反而會讓人誤解資料。

1. 常見圖表類型與適用時機

Excel 內建十多種圖表，以下是最常用的五種：

直條圖／橫條圖：適合比較不同項目之間的大小，例如各部門業績、各產品銷售量。橫條圖適合項目名稱較長時使用。

折線圖：適合呈現「隨時間變化」的趨勢，例如每月營收、每日體重。注意橫軸通常是時間。

圓餅圖：適合呈現「佔比」，但項目不宜超過五到六項，否則會難以閱讀。如果佔比太接近，建議改用直條圖。

散佈圖：適合觀察兩個變數之間的關係，例如廣告支出與銷售額的關聯性。

組合圖：把不同類型的圖表疊在一起，例如用直條圖顯示營收、折線圖顯示成長率。適合需要同時比較「量」與「率」的情境。

選擇圖表的基本原則：比較用直條、趨勢用折線、佔比用圓餅、關聯用散佈。如果不確定，就先做直條圖，通常最不容易出錯。

2. 圖表美化與排版技巧

預設的 Excel 圖表其實不難看，但總少了點質感。以下幾個調整可以讓你的圖表立刻升級：

刪除不必要的元素：例如格線、圖例、座標軸標題，如果沒有幫助就刪掉。圖表越乾淨，重點越突出。

加上資料標籤：在直條圖上直接顯示數字，讀者不用再對照座標軸。但項目太多時要避免，以免擁擠。

調整顏色：同一個圖表不要用超過三種主要顏色。可以用公司的品牌色，或選擇對比明顯但柔和的色系。

標題要有結論：不要只寫「每月營收」，而是寫「第三季營收成長 15%」，讓標題本身就是一個訊息。

統一格式：如果一份報告有多張圖表，字型、顏色、標籤位置盡量一致，看起來會更專業。

另外，Excel 的「格式化為表格」功能（Ctrl+T）可以讓資料範圍自動擴充，圖表也會跟著更新，非常適合會持續新增資料的報表。

3. 樞紐分析圖：動態互動的視覺化

如果你還沒用過樞紐分析表，那真的錯過 Excel 最強大的功能之一。樞紐分析表可以讓你用「拖曳」的方式，快速把大量資料做成摘要報表。而樞紐分析圖則是它的圖表版本，而且可以搭配篩選器，做到「動態互動」的效果。

操作步驟大致如下：

選取資料範圍，點選「插入」→「樞紐分析表」。

把欄位拖到「列」、「欄」、「值」區域。例如把「部門」拖到列、「月份」拖到欄、「業績」拖到值。

點選「分析」→「樞紐分析圖」，選擇喜歡的圖表類型。

在樞紐分析圖上，可以插入「交叉分析篩選器」或「時間表」，讓使用者自己選擇要看的部門或期間。

這樣一來，你只要做一次，就能產生多種檢視角度，不必每次重新畫圖。對於每月、每季都要重複做報表的人來說，這個技巧可以省下大量時間。

三、自動化與效率提升：讓 Excel 自己工作

公式和圖表已經能解決大部分問題，但如果你每天、每週都在做重複的動作，例如複製貼上、格式整理、合併多個檔案，那就應該考慮自動化。Excel 提供了幾種不同層級的自動化工具，從簡單到進階都有。

1. 快速填入與資料驗證：減少手動輸入錯誤

快速填入（Flash Fill）是 Excel 2013 之後加入的功能，只要你在旁邊欄位輸入一兩個範例，Excel 就會自動猜出規則，幫你把整欄填好。例如你有一欄是「王小明 0912-345-678」，你想拆出手機號碼，只要在旁邊輸入第一筆的手機號碼，然後按 Ctrl+E，Excel 就會自動完成剩下的。這個功能對於整理從系統匯出的資料特別好用。

資料驗證則可以限制使用者輸入的內容。例如你可以設定某欄只能輸入「是」或「否」，或是只能輸入 1 到 100 之間的數字。做法是：選取範圍 →「資料」→「資料驗證」→ 設定條件。這樣可以大幅減少因為輸入錯誤而導致的計算錯誤。

2. 格式化為表格與結構化參照

前面提過 Ctrl+T 可以將資料範圍轉換成「表格」。這個功能除了讓外觀變漂亮之外，最大的好處是結構化參照。舉例來說，當你把 A1:C100 轉成表格並命名為「銷售資料」之後，公式就可以寫成：

=SUM(銷售資料[金額])

這樣即使你新增資料列，公式也會自動包含新的範圍，不必手動修改。而且表格會自動套用格式、篩選按鈕，還能搭配樞紐分析表使用。可以說是把資料「升級」成資料庫的第一步。

3. 巨集與 VBA：入門自動化

如果你需要更強大的自動化，例如「每天早上打開檔案、整理資料、寄出郵件」，那就需要用到巨集。巨集是用 VBA（Visual Basic for Applications）寫成的程式，但你不一定要會寫程式才能用。Excel 有「錄製巨集」功能，可以把你的操作步驟記錄下來，之後一鍵重播。

錄製巨集的步驟：

點選「檢視」→「巨集」→「錄製巨集」。

輸入巨集名稱，設定快捷鍵（例如 Ctrl+Shift+R）。

開始執行你要自動化的操作，例如選取範圍、套用格式、插入圖表。

完成後按「停止錄製」。

之後只要按快捷鍵，Excel 就會重複你剛才的所有動作。不過要注意，錄製巨集會把「選取儲存格」這種動作也記下來，如果資料位置改變，可能會出錯。所以建議錄製時盡量使用鍵盤快速鍵，並且搭配表格或命名範圍，讓巨集更穩定。

如果你願意學一點 VBA，可以做到更精準的控制。例如以下這段程式碼會把 A 欄所有空白儲存格填上「待補」：

Sub FillBlanks()

Dim rng As Range

For Each rng In Range("A1:A100")

If rng.Value = "" Then rng.Value = "待補"

Next rng

End Sub

把這段貼到 VBA 編輯器（按 Alt+F11 開啟）的模組中，就能執行。當然，這只是入門範例，VBA 能做的事遠比你想像的多。

4. Power Query：不用寫程式的資料清理神器

如果你用的是 Excel 2016 之後的版本（或 Microsoft 365），裡面藏了一個超級強大的工具：Power Query。它的位置在「資料」→「取得與轉換資料」。Power Query 可以幫你從各種來源匯入資料，例如 CSV、文字檔、網頁、資料庫，然後進行清理、合併、篩選、樞紐等動作，最後載入 Excel 工作表或資料模型。

最大的好處是：整個流程會被記錄下來。下次只要按「全部重新整理」，所有步驟就會自動重跑。例如你每個月都要把十二個分公司的 CSV 檔合併成一份總表，用 Power Query 設定一次，之後只要把新檔案放進同一個資料夾，按重新整理就完成了。這比手動複製貼上快上幾十倍，而且不會出錯。

Power Query 的學習曲線比 VBA 低很多，因為大部分操作都是點選按鈕，不需要寫程式。如果你經常處理重複性的資料清理工作，強烈建議花一個下午學會它。

四、常見錯誤與疑難排解

即使學會了公式、圖表和自動化，實際操作時還是會遇到一些狀況。以下整理幾個最常見的問題與解法。

1. 公式出現 #N/A、#VALUE!、#REF! 怎麼辦？

#N/A：通常是 VLOOKUP 或 XLOOKUP 找不到對應值。檢查查找值是否有拼錯、多了空白，或是資料類型不一致（例如一邊是文字、一邊是數字）。可以用 IFERROR 包住公式，顯示友善訊息：=IFERROR(VLOOKUP(...),"查無資料")。

#VALUE!：表示公式中的某個引數類型不對，例如把文字當數字加。檢查儲存格內容是否含有非數字字元。

#REF!：表示參照的儲存格被刪除了。例如公式原本參照 A1，但 A1 整欄被刪掉，就會出現這個錯誤。按 Ctrl+Z 復原，或重新修改公式。

#DIV/0!：除以零。檢查分母是否為 0 或空白。可以用 IF 避開：=IF(B2=0,"",A2/B2)。

2. 日期格式跑掉或無法計算

Excel 的日期其實是一個數字（序號），1900 年 1 月 1 日等於 1，之後每過一天加 1。所以如果你看到儲存格顯示「44562」而不是日期，表示格式被設成「一般」或「數值」。只要選取該儲存格，按 Ctrl+1 開啟「儲存格格式」，改成「日期」即可。

另外，從系統匯出的日期有時會是文字格式（例如「2024/01/05」但靠左對齊），這時加減計算會出錯。可以用「資料」→「資料剖析」或 =DATEVALUE() 轉成真正的日期。

3. 檔案太大、跑很慢

Excel 檔案如果包含大量公式、格式化、圖片或樞紐分析表，很容易變得又肥又慢。以下幾個方法可以改善：

把已經不需要的公式「值化」：複製 → 選擇性貼上 → 值。

刪除不必要的格式化範圍，尤其是整欄或整列套用格式。

避免使用整個欄參照（如 A:A），改成明確範圍（如 A1:A1000）。

如果資料量真的很大，考慮拆成多個檔案，或用 Power Query 從外部來源讀取。

關閉自動計算：公式 → 計算選項 → 手動，需要時再按 F9 重算。

五、學習路徑與資源推薦

Excel 的功能非常多，與其想一次全部學會，不如根據自己的工作需求，挑選最實用的部分深入。以下提供一個簡單的學習路徑：

第一週：熟悉基本公式、SUM、AVERAGE、IF、相對與絕對參照。目標是能做出簡單的收支表。

第二週：學會 VLOOKUP 或 XLOOKUP、COUNTIF、SUMIF，並練習用樞紐分析表整理資料。

第三週：練習圖表製作與美化，學會用樞紐分析圖做互動報表。

第四週：學習資料驗證、格式化為表格、快速填入，減少手動輸入。

進階：依照需求學習 Power Query、巨集錄製或 VBA。

至於學習資源，除了 Microsoft 官方的支援網站之外，YouTube 上有非常多免費教學頻道，例如 ExcelIsFun、Leila Gharani、電腦玩物等。另外，也可以買一本實體的 Excel 工具書放在桌邊，遇到問題時翻一下，比上網搜尋更有效率。

最後，也是最重要的一點：多練習、多應用。Excel 不是用「看」的就會，一定要自己動手做。你可以拿自己的生活資料來練習，例如記帳、健身紀錄、旅遊規劃，甚至把家裡的開銷做成圖表。當你發現 Excel 能幫你解決真實問題時，學習動力就會源源不絕。

希望這篇指南能幫助你從 Excel 新手變成高手。記住，公式是基礎，圖表是溝通，自動化是效率。三者搭配起來，你就能把時間留給更重要的事，而不是每天加班複製貼上。祝你在資料的世界裡，愈用愈順手！

🏠 返回首頁