Excel 可以複製公式嗎？超實用技巧大解密，讓你效率翻倍！

Byadmin

Excel 活用：公式複製的奧秘與精湛技巧

「欸，我的 Excel 表格裡有好多重複的計算，想把這個公式複製到旁邊的欄位，結果跑出來的數字都不對，到底怎麼回事啊？」相信不少第一次接觸 Excel，或是習慣手動輸入的朋友，肯定都遇過這種讓人頭痛的狀況吧！別擔心！今天這篇文章，就是要來徹底解開「Excel 可以複製公式嗎？」這個看似簡單，卻又藏著許多學問的問題。

我可以非常明確且快速地告訴你：當然可以！而且這是 Excel 最基本、也最核心的功能之一！ 如果你還在一個一個手動輸入公式，那真的太落伍啦！學會公式複製，不僅能大幅節省你的寶貴時間，更能有效避免人為錯誤，讓你的報表、數據分析更加精準專業。

為什麼我的公式複製後會跑掉？

在深入探討複製技巧之前，我們先來釐清一個常見的迷思。很多時候，當我們複製公式後，會發現結果出現錯誤，原因通常出在「儲存格參照」的變化。Excel 中的儲存格參照，主要分為三種：

  • 相對參照 (Relative Reference)： 這是預設的參照方式。當你複製公式時，儲存格的參照會「相對」於複製的目標位置進行移動。例如，你在 A1 輸入 `=B1+C1`，然後將公式複製到 A2，Excel 會自動將公式變成 `=B2+C2`。
  • 絕對參照 (Absolute Reference)： 使用「$」符號來鎖定儲存格。當你複製公式時，絕對參照的儲存格位置「不會」改變。例如，你在 A1 輸入 `=$B$1+C1`，複製到 A2 後，公式會變成 `=$B$1+C2`。這裡的 `$B$1` 永遠指向 B1，而 `C1` 則會變成 `C2`。
  • 混合參照 (Mixed Reference)： 結合了相對參照和絕對參照。你可以只鎖定欄或列。例如，`=$B1+C$1`。複製到 A2 後，公式會變成 `=$B2+C2`。

我的經驗告訴我，搞懂這三種參照的差異，絕對是學會 Excel 公式複製的關鍵第一步！ 很多時候，我們複製公式後發現數字跑掉，就是因為沒有正確使用絕對參照或混合參照，讓公式在移動時，參照的目標也跟著跑了。

Excel 公式複製的超實用技巧大公開！

好啦，理論講完了，是時候來點實際的！學會了參照的觀念，我們就可以開始玩轉公式複製了。以下這些技巧，絕對讓你驚呼：「原來 Excel 可以這麼好用！」

技巧一：最基本的「向下填滿」與「向右填滿」

這是最常用、也最直觀的複製方式。當你計算好一個欄位或列的公式後：

  1. 選取包含已正確輸入公式的儲存格。
  2. 你會看到該儲存格的右下角有一個小小的「填滿控點」（一個小黑點）。
  3. 向下複製： 將滑鼠指標移到填滿控點上，指標會變成一個細細的「+」號。這時候，按住滑鼠左鍵，向下拖曳你想要複製公式的範圍。
  4. 向右複製： 同樣地，將滑鼠指標移到填滿控點上，按住滑鼠左鍵，向右拖曳。

貼心提醒： 如果你想要複製到很多列或很多欄，有一個更快的捷徑！快點兩下那個「填滿控點」，Excel 會自動偵測左邊的欄位是否有連續的數據，並將公式「盡可能」地填滿。是不是超方便的！

技巧二：複製公式，但不複製格式

有時候，我們只想複製公式的計算邏輯，而不希望把來源儲存格的顏色、字體、框線等格式也一起帶過來。這時候，可以使用「選擇性貼上」功能：

  1. 複製包含公式的儲存格（Ctrl + C）。
  2. 選取你想要貼上公式的目標儲存格範圍。
  3. 在目標儲存格範圍上按滑鼠右鍵，選擇「選擇性貼上」。
  4. 在彈出來的對話框中，選擇「貼上」選項下的「公式」，然後點擊「確定」。

這個技巧的應用場景非常多！ 比如，你做了一個很漂亮的報表模板，裡面已經預設好公式，但要給不同部門的同仁使用時，你可能只想讓他們貼上公式，保留他們自己原有的報表格式，這時候就派上用場了。

技巧三：複製公式，但鎖定儲存格參照（絕對參照的神奇之處）

前面有提到，當我們複製公式時，儲存格參照會相對移動。但如果我們希望某個參照「固定不動」，就像鎖鏈一樣牢牢綁住它，這時候就要運用到絕對參照了！

想像一下，你要計算每個產品的銷售額佔總銷售額的比例。這時候，總銷售額這個數字（假設在 E1 儲存格）就需要被「固定」住。

  1. 在你的公式中，在你想要鎖定的儲存格參照前加上「$」符號。例如，如果要鎖定 B1 儲存格，寫法就是 `$B$1`。
  2. 如果你只想鎖定列，但允許欄移動，就寫成 `$B1`。
  3. 如果你只想鎖定欄，但允許列移動，就寫成 `B$1`。

小撇步： 輸入公式時，如果你已經輸入了儲存格參照（例如 B1），直接點選那個參照，然後按鍵盤上的 `F4` 鍵，Excel 會自動幫你循環切換不同的參照模式：`B1` -> `$B$1` -> `B$1` -> `$B1` -> `B1`。是不是超級方便！

技巧四：跨工作表的公式複製

這絕對是進階一點的應用，但學會了，你會覺得 Excel 的威力無窮！當你需要計算不同工作表上的數據時，複製公式一樣適用，而且 Excel 會很聰明地幫你處理。

例如，你有一個「總計」工作表，需要匯總「一月」、「二月」兩個工作表的銷售額。你可以在「總計」工作表的 A1 儲存格輸入 `=SUM(一月!B2:B10)`。當你將這個公式複製到 A2 儲存格時，Excel 會自動變成 `=SUM(二月!B2:B10)`，前提是你複製的方式是正確的（例如使用填滿控點）。

重點來了！ 如果你在公式中明確引用了另一個工作表，例如 `=Sheet1!A1`，然後將這個公式複製到 Sheet2 的 A2，它會自動變成 `=Sheet1!A2`。但如果你想鎖定 Sheet1 的 A1 儲存格，則要寫成 `=Sheet1!$A$1`。

我的親身經驗： 剛開始接觸跨工作表引用時，常常會被各種「Sheet 名称」搞得頭昏腦脹。但多練習幾次，你會發現 Excel 的邏輯其實相當清晰，它會自動幫你記錄你引用的工作表名稱。最怕的是，你複製了一個公式，但目標儲存格旁邊的欄位並沒有對應的 Sheet 名稱，這時候 Excel 就會「盡量」去尋找，有時候可能會出錯，所以，在跨工作表複製時，多檢查一下參照是否正確，絕對是避免錯誤的黃金法則。

為什麼要掌握公式複製？它能為你帶來什麼？

效率提升是顯而易見的！ 想像一下，一份報表需要計算幾百、幾千甚至上萬列的數據。如果一個一個手動輸入，那得花費多少時間？學會公式複製，幾秒鐘就能搞定，剩下的時間你可以去做更有價值的事情，比如分析數據、提出見解。

精確度大幅提高！ 人類總是會犯錯的，尤其是長時間、重複性的工作。手動輸入公式，難免會出個小差錯，導致整個報表結果失準。公式複製，只要你第一個公式是正確的，後續的複製基本上就能保證其準確性，大大降低了出錯的機率。

專業形象加分！ 當你能夠快速、準確地處理數據，呈現出專業的報表時，無論是在工作還是學術上，都會讓人覺得你對 Excel 掌握得非常熟練，大大提升你的專業形象。

創造彈性與動態性！ 透過公式的設定，你可以讓你的報表變得非常「動態」。當原始數據更新時，只需要修改幾個關鍵的輸入欄位，整個報表的結果就會自動更新，省去了你重新計算、重新輸入的麻煩。

實際應用場景舉例

讓我們來看看幾個實際應用公式複製的場景，你會發現它無所不在！

場景一：銷售額計算與占比分析

假設你有一個銷售報表，包含「產品名稱」、「單價」、「數量」等欄位。你想要計算「總銷售額」（單價 x 數量），以及每個產品的銷售額佔總銷售額的比例。

  • 計算總銷售額： 在「總銷售額」欄位的第一列輸入 `=B2*C2`（假設單價在 B 欄，數量在 C 欄），然後將公式向下填滿。
  • 計算銷售額占比： 首先，需要在報表下方或旁邊計算出「總銷售額合計」（例如用 `SUM(D2:D100)`，假設總銷售額在 D 欄）。然後，在「占比」欄位的第一列輸入 `=D2/$E$1` （假設總銷售額合計在 E1）。這裡的 `$E$1` 就是絕對參照，確保在複製公式時，總銷售額合計的位置不會改變。然後將公式向下填滿。

場景二：績效考核與獎金計算

假設你需要根據員工的銷售額來計算獎金。可能有一個基礎的獎金率，或是按照不同的銷售額級距有不同的獎金比例。

  • 基礎獎金計算： 如果獎金率是固定的（例如 5%，放在一個單獨的儲存格，假設是 F1），則公式會是 `=D2*$F$1` （假設銷售額在 D 欄）。
  • 級距獎金計算： 這時候可能需要用到 `IF` 函數搭配絕對參照。例如，如果銷售額大於 100,000，獎金率是 8%；大於 50,000，獎金率是 5%；否則 2%。公式可能會寫成：`=D2*IF(D2>=100000, 0.08, IF(D2>=50000, 0.05, 0.02))`。這時候，`D2` 是相對參照，會隨著複製往下移動，但數字 `100000` 和 `50000` 則是固定的。

關於公式複製常見問題解答

在使用 Excel 的過程中，難免會遇到一些特殊的狀況，以下整理了一些常見的問答，希望能幫助大家更全面地理解 Excel 公式複製。

Q1：我複製公式後，出現了 #REF! 錯誤，該怎麼辦？

詳細解答： `#REF!` 錯誤通常表示你複製的公式參照了一個不存在的儲存格。這最常見的原因是你複製的公式，參照了你後來刪除的儲存格或工作表。例如，你在 A1 輸入 `=B1`，然後將公式複製到 A2。接著你刪除了 B1 儲存格。這時候 A1 和 A2 都會變成 `#REF!` 錯誤，因為它們都指向了已經不存在的 B1。

解決方法：

  • 檢查參照： 仔細查看出現 `#REF!` 錯誤的儲存格中的公式，看看它參照了哪些儲存格，然後確認這些儲存格是否存在。
  • 撤銷操作： 如果是剛刪除的儲存格，立刻按下 `Ctrl + Z` 進行撤銷。
  • 重新建立公式： 如果無法撤銷，你可能需要重新建立公式，確保參照是正確且有效的。
  • 注意刪除操作： 在進行刪除儲存格、欄、列或工作表時，要特別小心，最好先檢查是否有公式引用到它們，或是先將公式複製為「值」，以免造成錯誤。

Q2：我只想複製公式，但 Excel 自動幫我複製了格式，我該如何只複製公式？

詳細解答： 這也是一個非常常見的問題，如前所述，Excel 在複製貼上時，預設會盡可能保留原始的格式。要只複製公式，請務必使用「選擇性貼上」功能。

再次強調操作步驟：

  1. 複製來源儲存格（Ctrl + C）。
  2. 選取目標儲存格範圍。
  3. 在目標儲存格上按右鍵，選擇「選擇性貼上」。
  4. 在「貼上」的選項中，選擇「公式」。
  5. 點擊「確定」。

另外，值得一提的是，如果你在貼上時，點選了「選擇性貼上」選項後，看到的右側「貼上」選項中，有一個「公式」的圖標（通常是一個帶有 fx 的符號），直接點選它，也能達到只貼上公式的效果。

Q3：我在複製公式時，希望某個欄位固定，但另一欄位可以隨意移動，該如何設定？

詳細解答： 這就是我們前面提到的「混合參照」的應用。透過在欄位名稱或列號前加上「$」符號，來控制參照的固定程度。

舉例說明：

  • 假設你的公式是 `=SUM(A1:A10)`，你想將這個公式複製到旁邊的欄位，但希望「A1」這個範圍的「欄」固定在 A，但「列」可以隨著複製的動作往下移動（例如變成 A2、A3…）。這時候，你應該將公式寫成 `=SUM($A1:$A10)`。
  • 相對地，如果你希望「欄」可以移動（例如 A、B、C…），但「列」必須固定為 1，則可以寫成 `=SUM(A$1:A$10)`。

掌握混合參照，就像擁有了一把瑞士刀！ 它可以讓你精準地控制公式在複製時，哪些部分需要保持不變，哪些部分需要跟著移動，大大增加了公式運用的彈性。

Q4：Excel 的「填滿控點」雙擊就能自動填滿，這是怎麼辦到的？

詳細解答： 這是一個非常方便的自動化功能。當你雙擊填滿控點時，Excel 會「偵測」你正在複製的公式的「左側」或「上方」連續儲存格的數據範圍。它會自動將公式向下或向右複製，直到遇到第一個空白儲存格為止。

這是什麼意思呢？ 假設你在 C2 輸入了一個公式，而 B 欄從 B2 開始有連續的數據，一直到 B10，B11 是空的。當你雙擊 C2 的填滿控點時，Excel 就會自動將 C2 的公式複製到 C3、C4…一直到 C10，然後停止。因為它看到 B11 是空的，就知道數據範圍結束了。

小提醒： 這個功能非常有效率，但也需要注意，如果你的左側或上方欄位有「不規則的空白」，Excel 可能會提前停止複製，導致公式沒有完全填滿。所以，在使用雙擊填滿控點之前，最好先確認一下你的數據範圍是否連續。

總之，Excel 的公式複製功能，絕對是你處理數據的好幫手！從最簡單的填滿控點，到進階的選擇性貼上和絕對參照，只要掌握了這些技巧，你就能大幅提升工作效率，讓你的 Excel 操作更加得心應手！

Excel可以複製公式嗎