Excel公式中位址的相對、絕對與混合參照

適用: Excel 2016、2013、2010、2007、2003

品管統計工作需要大量繁複、重複的計算，因此活用Excel的『公式』幫助完成複雜或重複的計算，Excel公式除了加減乘除等運算符號外，最重要的當然是參照的儲存格位址，若僅是一個單獨儲存格就很單純，但若需要複製公式到其他儲存格時的，就要特別注意複製後的位址是否符合所需要的參照。
例如每日不良率計算如表1，資料佈署在A1:C5位置，希望Excel幫助完成不良率計算。

	A	B	C	D	E	F	G	H
1	日期	檢查數	不良數	不良率	不良成本		不良單價
2	2016/8/1	100	1				10	元
3	2016/8/2	200	3
4	2016/8/3	300	6
5	2016/8/4	400	10
6

表1：品管資料數據佈署

1 相對參照位址
表1用Excel計算不良率一般作法是先計算8月1日的不良率，然後複製這個不良率的公式到其他日期上，步驟如下

1. 位置D2：輸入『=C2/B2』公式，然後按下『enter』
2. 複製D2內容：滑鼠仍在D2位置，右鍵『複製』
3. 選取貼上位置：選取D3:D5，右鍵『貼上』

完成後所有不良率都計算出來如表2，依次將滑鼠移到D3、D4、D5位址，可以看到不良率的計算公式分別為『=C3/B3』、『=C4/B4』、『=C5/B5』，發現公式中的不良數、檢查數位址，雖然只是複製『=C2/B2』，Excel會自動幫我們變更為『=C3/B3』、『=C4/B4』、『=C5/B5』而完成正確計算，這種方式是Excel預設的相對參照，公式中的位址如B2、C2的位址寫法在Excel上稱為『相對參照』位址。

	A	B	C	D
1	日期	檢查數	不良數	不良率	不良率公式
2	2016/8/1	100	1	0.01	=C2/B2
3	2016/8/2	200	3	0.015	=C3/B3
4	2016/8/3	300	6	0.02	=C4/B4
5	2016/8/4	400	10	0.025	=C5/B5
6

表2：不良率計算結果與其計算公式

2 絕對參照位址
表1的E欄需要Excel計算不良成本，沿用前述不良率計算方法來計算不良成本，同樣先計算8月1日的不良成本，然後複製這個不良成本的公式到其他日期上，步驟如下

1. 1 位置E2：輸入『=C2*G2』公式，然後按下『enter』
2. 複製D2內容：滑鼠仍在D2位置，右鍵『複製』
3. 選取貼上位置：選取E3:E5，右鍵『貼上』

完成後所有不良成本都計算出來如表3，但是杯具發生了，8月2日以後的不良成本都是0，明明有不良數的。將滑鼠分別移到F3、F4、F5位址上可以看到公式內容『=C3*G3』、『=C4*G4』、『=C5*G5』，而G3~G5位置是空白，因此不良成本的計算結為當然是0。

	A	B	C	D	E	F	G	H
1	日期	檢查數	不良數	不良率	不良成本	不良成本公式	單價
2	2016/8/1	100	1	0.01	10	=C2*G2	10	元
3	2016/8/2	200	3	0.015	0	=C3*G3
4	2016/8/3	300	6	0.02	0	=C4*G4
5	2016/8/4	400	10	0.025	0	=C5*G5
6

表3：不良成本計算結果與其計算公式

要解決這種不適當的『相對參照位址』複製工作，就需要鎖定『位址』，作法如下

1. 位置E2：輸入『=C2*$G$2』公式，然後按下『enter』
2. 複製D2內容：滑鼠仍在D2位置，右鍵『複製』
3. 選取貼上位置：選取E3:E5，右鍵『貼上』

完成後所有不良成本都計算出來而且是正確的如表4，再次將滑鼠分別移到F3、F4、F5位址上可以看到公式內容『=C3*$G$2』、『=C4*$G$2』、『=C5*$G$2』，是正確的公式，當位址的欄與列都鎖定而不參照，這種位址稱為『絕對參照』位址。

	A	B	C	D	E	F	G	H
1	日期	檢查數	不良數	不良率	不良成本	不良成本公式	單價
2	2016/8/1	100	1	0.01	10	=C2*$G$2	10	元
3	2016/8/2	200	3	0.015	30	=C3*$G$2
4	2016/8/3	300	6	0.02	60	=C4*$G$2
5	2016/8/4	400	10	0.025	100	=C5*$G$2
6

表4：不良成本正確計算結果與其計算公式

3 四種參照的表示方法

1. 相對參照：根據前述不良率與不良成本的計算，Excel預設複製不良率公式中儲存格位址參照是相對的，其欄R與列C的位址單純以『RC』表示。
2. 絕對參照：當要計算不良成本需要複製固定的G2位址，因此公式中需要加入貨幣符號『$』，簡稱為錢號，而成為$G$2，此處第1個錢號是固定G欄，第2個錢號是固定列2，其欄R與列C的位址以『$R$C』表示，讓Excel計算時欄與列都固定而不『參照』，就是絕對位址。
3. 混合參照-欄鎖定：有時計算公式只需要固定欄，但列卻要參照，這時就使用$RC來表示
4. 混合參照-列鎖定：另一方式若要參照欄，但列卻要固定，則使用C$R表示

4 用F4在相對、絕對與混合參照之間來回切換
雖然在位址上加上錢號並不是困難，但輸入公式時加上錢號也是有點煩，Excel提供一個古老的做法是運用『F4』鍵，可以來回切換錢號，請在工作表任一空白儲存個練習，例如在H4儲存格輸入不良成本公式，當輸入『=C2*G2』後還未enter前，游標仍留在G2後面時，試著按第1次F4，此時G2變成$G$2，按第2次F4，此時$G$2變成G$2，按第3次F4，此時G$2變成$G2，按第4次F4，此時$G2變成G2，若再按F4就回到第1次按F4的狀態。