參照的藝術：公式往下拖時，哪些要動、哪些要釘住

我有一張價格清單，每樣商品都要打 85 折，折扣率 0.85 放在 D2。我在 B2 寫了 =A2*D2，結果對了，往下一拖，B3 到 B6 全部變成 0。

原因出在 D2 前後少了 $。這篇用這個折扣表走一遍：相對、絕對、混合參照，加上範圍、跨工作表，以及該怎麼判斷 $ 要放哪裡。

來看看拖完的樣子：

拖拉之後，位址跑去哪了

公式裡的 A2，試算表看的是「同一列的 A 欄」，所以把公式從 B2 拖到 B3，它就自動改讀 A3，每一列各讀各的原價，這是我們要的。

麻煩的是 D2 也被當成「同一列的 D 欄」。拖到 B3 變成 D3，D3 是空的，這列的 A3 是 1250，1250 乘以空白就是 0。下圖把三種寫法往下拖之後的變化放在一起：

← 左右滑動看完整的圖，點圖可放大

圖：綠色粗體是拖完會變的部分，深色是原封不動的部分。

三種寫法就是這三個名字：

• 相對參照：什麼都不加，A2，往哪拖就往哪移。
• 絕對參照：欄和列前面都加 $，$D$2，怎麼拖都不動。
• 混合參照：只鎖一邊，$A2 鎖住欄、A$2 鎖住列。

$ 放在哪裡

我個人不背這些符號，只對公式裡的每個位址問一句：「我把這個公式複製出去，這個位址要跟著走，還是留在原地？」

折扣表裡，原價要跟著走，折扣率要留在原地。所以把 B2 改成：

往下拖到 B6，來看看結果：

手動打 $ 很快就會厭煩，編輯公式時把游標放在位址上，在 Chrome 上按 F4（Windows 直接按；Mac 筆電的 F4 若是功能鍵，改按 fn+F4），就會在 D2、$D$2、D$2、$D2 之間輪流切換。

兩個方向各鎖一個

如果有三種折扣，0.9、0.85、0.8 放在 B2:D2，原價在 A3:A7，想一次排出每個價格在每種折扣下的結果，就得兩個方向都鎖：

← 左右滑動看完整的圖，點圖可放大

圖：每一格都讀自己那一列的原價、自己那一欄的折扣率。

在 B3 輸入下面這條，再往右、往下拖滿 B3:D7：

$A3 往右拖永遠讀 A 欄，往下拖列號照常加；B$2 往下拖永遠讀第 2 列，往右拖欄名照常加。來看看結果：

同一張表也可以用一個陣列公式展開，那是 陣列思維 裡的做法。

範圍的兩端各自決定

A2:A6 其實是兩個位址，起點和終點各自可以加 $，所以範圍也會在拖拉時跑掉。

我用 B 欄的折後價算排名，F 欄沒有鎖範圍，G 欄鎖住：

F 欄的 4、2 看起來沒錯，錯在後面：拖到 F4，範圍變成 B4:B8，633.25 只跟下面的值比，排名就算成 2，這種錯不會顯示錯誤，最難發現。

另一種用法是只鎖起點：在 E2 輸入 =SUM($B$2:B2) 再往下拖，每往下一列，範圍就多吃一格，得到累計加總：

← 左右滑動看完整的圖，點圖可放大

圖：起點釘住、終點沒有 $，往下拖就多吃一格。

E2 到 E6 會是 323、1385.5、2018.75、3880.25、3961.85。

換到別張工作表

折扣率如果放在另一張叫「設定」的工作表 D2，公式寫成：

工作表名稱後面加驚嘆號，再接位址，$ 的判斷方式完全一樣。名稱裡有空格的話要用單引號包起來，像是 '價格 設定'!$D$2。

不想記住折扣率在哪張工作表的哪一格，可以幫那一格取名字：選取後點「資料」>「已命名範圍」，取名「折扣率」，公式就變成 =A2*折扣率。名稱本身就是釘住的，不用加 $，插入列、欄也比較穩。

想用文字組出參照的人，可以看 動態參照的謹慎使用指南。想多練幾題 $ 的判斷，可以接著看 讓公式活起來。

下次拖公式之前，先問一句：這個要不要跟著走？

我是喜特先生，Mr. Sheet，我們下個教學見！