搭配 Excel 表格使用結構化參照

當你 建立 Excel 表格時，Excel 會為表格以及表格中的每個欄位標題指派名稱。 當您新增公式至 Excel 表格時，這些名稱可在您輸入公式時自動出現，並自動選取表格中的儲存格參照，而不需要手動輸入。 以下範例示範 Excel 的功能：

Excel 使用表格及欄名稱	來取代明確的儲存格參照
=Sum(C2:C7)	=SUM(DeptSales[銷售金額])

表格與欄名的組合，稱為結構化參照。 每當您新增或移除表格資料時，結構化參照中的名稱也會隨之調整。

當您在 Excel 表格以外建立一個參照表格資料的公式時，也會出現結構化參照。 參照可以讓您更容易地在大型活頁簿中尋找表格。

要在公式中包含結構化參考，請選擇你想參考的表格儲存格，而不是在公式中輸入它們的儲存格參考。 讓我們用以下範例資料輸入一個公式，自動利用結構化參考來計算銷售佣金金額。

銷售人員	區域	銷售金額	% 佣金	佣金金額
立民	北部	260	10%
棟材	南部	660	15%
莉華	東部	940	15%
哲翰	西部	410	12%
媚卉	北部	800	15%
冠廷	南部	900	15%

1. 複製上表中的範例資料，包括欄目，並貼到新的 Excel 工作表的 A1 格中。
2. 要建立表格，請選擇資料範圍內的任一儲存格，然後按 Ctrl+T。
3. 確認勾選 「我的表格有標頭」 這個框，並選擇 確定。
4. 在 E2 格子中，輸入等號 (=) ，並選擇 C2。
   資料編輯列中，[@[銷售金額]] 結構化參照會出現在等號後面。
5. 在括號後 (*) 直接輸入星號，並選擇 D2 格。
   資料編輯列中，[@[%佣金]] 結構化參照會出現在星號後面。
6. 按 Enter。
   Excel 會自動建立計算結果欄，將公式複製到整欄中，並根據每個資料列調整。

使用明確的儲存格參照時會如何？

如果您在計算結果欄中輸入明確的儲存格參照，則較難看出公式的計算內容。

1. 在你的範例工作表中，選擇儲存格 E2
2. 在公式列中，輸入 =C2*D2 並按下 Enter。

請注意，當 Excel 將該公式複製到欄中時，它並不會使用結構化參照。 例如，如果您在現有 C 和 D 欄之間新增一欄，您必須修改公式。

如何變更表格名稱？

建立 Excel 表格時，Excel 會建立預設的表格名稱 (表格1、表格2，依此類推)，但您可以把表格名稱變更為更有意義的名稱。

1. 在表格中選擇任一儲存格，以顯示色帶上的 表格設計 標籤。
2. 在 表格名稱 框輸入你想要的名稱，然後按下 Enter 鍵。

在我們的範例資料中，我們使用了 DeptSales這個名稱。

為表格命名時使用下列規則：

• 使用有效字元 名字開頭一定要有字母、 (_) ，或反斜線 (\) 。 其餘部分使用字母、數字、句點，並用底線標示字元。 你不能用「C」、「c」、「R」或「r」來表示名稱，因為這些名稱已經被指定為選擇活動儲存格欄位或列的捷徑，當你在 「名稱 」或 「前往」 欄位時。

• 不要使用儲存格參考 名稱不能和儲存格參考相同，例如 Z$100 或 R1C1。
• 不要用空格來分隔單字 名稱中不能使用空格。 你可以使用底線符號 _) (和句點 (.) 作為詞語分隔符。 例如，DeptSales、Sales_Tax或第一季。
• 字元不超過255字元 一個表格名稱最多可包含 255 個字元。
• 使用獨特的表格名稱 不允許重複姓名。 Excel 不會區分名字中的大寫和小寫，所以如果你輸入「Sales」但同一工作簿裡已經有另一個名字叫「SALES」，系統會提示你選擇一個獨特的名字。
• 使用物件識別碼 如果你打算混合使用表格、樞紐分析表和圖表，建議在名稱前加上物件類型。 例如：tbl_Sales代表銷售表，pt_Sales代表銷售樞紐分析表，chrt_Sales代表銷售圖表，或ptchrt_Sales代表銷售樞紐分析圖。 這樣你所有的名字都會在 名稱管理器中保持有序的清單。

結構化參照語法規則

你也可以手動在公式中輸入或更改結構化參考，但要做到這點，理解結構化引用語法會比較有幫助。 讓我們來看看以下公式範例：

=SUM(DeptSales[[#Totals],[銷售金額]],DeptSales[[#Data],[佣金金額]])

此公式具有下列結構化參照的元件：

• **表格名稱：**DeptSales 是自訂表格名稱。 它會參照表格資料，而不需要任何頁首或合計列。 您可以使用預設的表格名稱，例如「表格1」，或將其變更為使用自訂名稱。
• 欄位指定符：[銷售金額]和[佣金金額]是使用它們所代表欄位名稱的欄位指定符。 其會參照欄資料，而不需要任何欄標題或合計列。 而且指定元一律以方括弧括住，如下所示。
• 項目指定器：[#Totals] 和 [#Data] 是特殊的項目指定器，指涉表格中特定部分，例如總列。
• 表格指定元：[[#Totals],[銷售金額]] 和 [[#Data],[佣金金額]] 是代表結構化參照外部部分的表格指定元。 外部參照在表格名稱後面，您用方括弧將其括住。
• 結構化參考： (DeptSales[[#Totals]，[銷售金額]] 與 DeptSales[[#Data]，[Commission Amount]] 是結構化參考，以一個字串表示，從表格名稱開始，以欄位指定符結束。

若要手動建立或編輯結構化參考，請使用以下語法規則：

• 在規格符周圍用括號 標示所有表格、欄位及特殊項目指定符都必須以相符的括號 ([ ]) 包圍。 含有其他指定元的指定元需要外部成對方括弧來括住其他指定元的內部成對方括弧。 例如： =DeptSales[[銷售人員]：[Region]]
• 所有欄位標頭都是文字字串 但在結構化參考文獻中使用時，它們不需要引用。 數字或日期 (如 2014 或 1/1/2014)，也會被視為文字字串。 你不能用帶有欄位標題的表達式。 例如， DeptSalesFYSummary[[2014]：[2012]] 這個表達式無法使用。

在欄位標題周圍使用括號，並標示特殊字元 若有特殊字元，整個欄頭必須以括號包覆，這表示欄位指定符中必須使用雙括號。 例如：=DeptSalesFYSummary[[金額總計]]

以下是公式中需要額外括號的特殊字元清單：

• Tab
• 換行
• 回車
• 逗號 (，)
• 冒號 (:)
• 句點 (.)
• 左邊支架 ([)
• 右括號 (])
• 英鎊符號 (#)
• 單引號 ( )
• 雙引號 (“)
• 左支架 ({)
• 右支架 (})
• 美元符號 ($)
• 插入號 (^)
• & 符號 (&)
• 星號 (*)
• 加號 (+)
• 等號 (=)
• 負號 ( )
• 比象徵更偉大 (>)
• 象徵性 (<)
• 分區符號 (/)
• 在標誌 (@)
• 反斜線 (\)
• 驚嘆號 (！)
• 左括號 ( ()
• 右括號 () )
• 百分比符號 ( )
• 問號 (?)
• 回溯 (')
• 分號 (;)
• 蒂爾德 (~)
• 底線 (_)

• 欄位標頭中某些特殊字元使用轉脫字元 有些字元有特殊意義，需要使用單引號 (') 作為逃脫字元。 例如：=DeptSalesFYSummary['#OfItems]

以下是需要逃脫角色的特殊角色清單， ( 公式中 ) ：

• 左邊支架 ([)
• 右括號 (])
• 英鎊符號 (#)
• 單引號 ( )
• 在標誌 (@)

利用空格字元提升結構化參考文獻的可讀性 你可以使用空格字元來提升結構化參考的可讀性。 例如：=DeptSales[ [銷售人員]:[區域] ] 或 =DeptSales[[#Headers], [#Data], [% 佣金]]。

建議使用一個格子：

• 在第一個左角組 ([)
• 在最後一個右括號之前 (]) 。
• 在逗號後面。

參照運算子

若希望指定儲存格範圍時更加彈性，可以使用下列參照運算子合併欄指定元。

這個結構化參照：	參照對象：	藉由使用：	此為儲存格範圍：
=DeptSales[[銷售人員]:[區域]]	兩個以上之相鄰欄中的所有儲存格	: (冒號) 範圍運算子	A2:B7
=DeptSales[銷售金額],DeptSales[佣金金額]	兩個以上欄的組合	, (逗號) 聯集運算子	C2:C7, E2:E7
=DeptSales[[銷售人員]:[銷售金額]] DeptSales[[區域]:[% 佣金]]	兩個以上欄的交集	(空格) 交集運算子	B2:C7

特殊項目指定元

若要參照表格特定部分 (如只參照合計列)，您可以在結構化參照中使用下列任何一個特殊項目指定元。

此特殊項目指定元：	參照對象：
#All	整個表格 (包含欄標題、資料及合計 (如果有的話))。
#Data	只有資料列。
#Headers	只有標題列。
#Totals	只有合計列。 如果沒有合計列，則會傳回 Null。
#This Row 或 @ 或 @[欄名]	僅限與公式同一列的儲存格。 這些指定符不能與其他特殊物品指定符合併使用。 使用它們來強制執行參照的隱含交集行為或覆寫隱含交集行為，以及參照欄的單一值。 Excel 會自動在有一行以上資料的表格中，將「#This Row」指定元變更為較短的 ＠ 指定元。 但如果你的表格只有一列，Excel 不會取代 #This 列指定符，這可能導致你增加更多列時出現意想不到的計算結果。 若要避免計算問題，請確定您在輸入任何結構化參照公式前，已在資料表中輸入多個列。

讓計算結果欄中的結構化參照符合條件

建立計算結果欄時，一般會使用結構化參照來建立公式。 此結構化參照可以是不完整或完整的。 例如，要建立名為「佣金金額」的計算欄位，計算佣金金額（以美元計算），你可以使用以下公式：

結構化參照的類型	範例	註解
不完整的	=[銷售金額]*[% 佣金]	乘上目前列的對應值。
完整的	=DeptSales[銷售金額]*DeptSales[% 佣金]	乘上這兩欄之每列的對應值。

一般規則是：如果你在資料表中使用結構化參考，例如建立計算欄位時，可以使用非限定結構化參考;但如果你在表格外使用結構化參考，則必須使用完全限定的結構化參考。

使用結構化參照的範例

以下是使用結構化參照的一些方法。

這個結構化參照：	參照對象：	也就是細胞範圍：
=DeptSales[[#All],[銷售金額]]	銷售金額欄中的所有儲存格。	C1:C8
=DeptSales[[#Headers],[% 佣金]]	% 佣金欄的標題。	D1
=DeptSales[[#Totals],[區域]]	區域欄的合計。 如果沒有合計列，則會傳回 Null。	B8
=DeptSales[[#All],[銷售金額]:[% 佣金]]	「銷售金額」與「% 佣金」中的所有儲存格。	C1:D8
=DeptSales[[#Data],[% 佣金]:[佣金金額]]	只有「% 佣金」與「佣金金額」欄的資料。	D2:E7
=DeptSales[[#Headers],[區域]:[佣金金額]]	只有「地區」與「佣金金額」之間的欄標題。	B1:E1
=DeptSales[[#Totals],[銷售金額]:[佣金金額]]	「銷售金額」到「佣金金額」欄的合計。 如果沒有「合計」列，則會傳回 Null。	C8:E8
=DeptSales[[#Headers],[#Data],[% 佣金]]	只有「% 佣金」的標題與資料。	D1:D7
=DeptSales[[#This Row], [佣金金額]] 或 =DeptSales[@佣金金額]	目前列與「佣金金額」欄交會的儲存格。 若與標頭或總列在同一列使用，則會回傳 #VALUE！ 錯誤。 如果您在有多列資料的表格中，以較長的形式輸入此結構化參照 (#This Row)，Excel 會自動以短的形式 (@) 取代。 它們運作的方式相同。	E5 (如果目前列為 5)

使用結構化參照的策略

在處理結構化參考文獻時，請考慮以下幾點。

• 使用公式自動完成 當您輸入結構化參照，並確定使用的是正確語法時，使用公式自動完成就會十分有用。 欲了解更多資訊，請參閱 使用公式自動補全。

• 決定是否要為半選取的表格產生結構化參考 預設情況下，當你建立公式時，選擇表格中的儲存格範圍會半選取儲存格，並自動進入結構化參考，而不是公式中的儲存格範圍。 此半選取範圍行為簡化了結構化參照的輸入。 你可以透過在「檔案>選項>公式>操作」對話框中選擇或清除「在公式中使用表格名稱」的勾選框來開啟或關閉此行為。

• 使用帶有外部連結到其他工作簿 Excel 表格的工作簿 如果一個工作簿包含指向另一個工作簿中 Excel 表格的外部連結，該連結的來源工作簿必須在 Excel 中開啟，以避免包含連結的目標工作簿出現 #REF！ 錯誤。 如果你先打開目標工作簿並出現 #REF！ 錯誤，接著打開原始碼工作簿，這些錯誤就會被解決。 如果您先開啟來源活頁簿，應該就不會看到錯誤碼。

• 將一個範圍轉換成表格，將一個表格轉換成一個範圍 當你將表格轉換成範圍時，所有儲存格的參考都會變成它們對應的絕對 A1 式參考。 當你將範圍轉換成表格時，Excel 不會自動將該範圍內的任何儲存格參考改成對應的結構化參考。

• 關閉欄位標題 你可以在 表格設計 分頁 >的標頭列中切換欄位標題。 如果你關閉了表格欄位標題，使用欄位名稱的結構化參考不會受到影響，你仍然可以在公式中使用它們。 直接指向表格標頭的結構化引用 (例如 =DeptSales[[#Headers]，[%Commission]]) ，將產生 #REF。

• 新增或刪除欄位與列到 由於資料表資料範圍經常變動，結構化參考的儲存格參考會自動調整。 例如，如果您在公式中使用表格名稱來計算表格中所有的資料儲存格，然後您又新增一列的資料，儲存格參照會自動進行調整。

• 重新命名表格或欄 如果重新命名欄或表格，則 Excel 會自動變更該表格及欄標題在用於活頁簿之所有結構化參照中的使用。

• 移動、複製及填充結構化參考 當你複製或移動使用結構化參考的公式時，所有結構化參考都會保持不變。

  注意

  複製結構化參考和填充結構化參考是兩回事。 當你複製時，所有結構化引用保持不變;而當你填寫公式時，完全限定的結構化引用會像一系列一樣調整欄位指定符，如下表所總結。

如果填寫方向為：	而在填寫時，按：	然後：
往上或往下	無	沒有欄指定元調整。
往上或往下	Ctrl	欄指定元的調整方式與數列相同。
右邊或左邊	無	欄指定元的調整方式與數列相同。
上、下、右或左	Shift	取代目前儲存格的值，而是移動當前儲存格的值，並插入欄位指定符。

需要更多協助嗎？

你隨時可以向 Excel 技術社群 的專家詢問，或在 社群中獲得支援。

相關主題

Excel 表格概觀
建立表格及設定其格式
合計 Excel 表格中的資料 (機器翻譯)
設定 Excel 表格格式 (機器翻譯)
藉由新增或移除列和欄調整表格大小
篩選範圍或表格中的資料
將表格轉換為範圍
Excel 表格相容性問題
將 Excel 表格匯出到 SharePoint
Excel 公式概述