搭配 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 或 First.Quarter。

• 使用不超過 255 個字元 表格名稱最多可以有 255 個字元。

• 使用唯一數據表名稱 不允許重複的名稱。 Excel 不會區分名稱中的大小寫字元，因此如果您在同一個活頁簿中輸入了「銷售」，但已經有另一個名稱稱為「SALES」，系統會提示您選擇唯一名稱。

• 使用物件識別碼  如果您打算混合使用表格、數據透視表和圖表，建議您以物件類型將名稱置入前置詞。 例如：銷售數據表的tbl_Sales、銷售數據透視表的pt_Sales，以及銷售圖表的chrt_Sales，或銷售樞紐分析圖的ptchrt_Sales。 這會將您所有的名稱保留在 [名稱管理員] 的排序列表中。

結構化參照語法規則

您也可以在公式中手動輸入或變更結構化參照，但若要這麼做，有助於了解結構化參照語法。 讓我們來看看下面這個公式範例：

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

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

• 表格名稱：    DeptSales 是自定義表格名稱。 它會參照表格資料，而不需要任何頁首或合計列。 您可以使用預設的表格名稱，例如「表格1」，或將其變更為使用自訂名稱。

• 欄指定元：    [銷售金額] 和 [傭金金額] 是使用其所代表之欄名的欄指定元。 其會參照欄資料，而不需要任何欄標題或合計列。 而且指定元一律以方括弧括住，如下所示。

• 專案指定元：    [#Totals] 和 [#Data] 是參照表格特定部分的特殊專案指定元，例如合計列。

• 表格指定元：    [[#Totals],[銷售金額]] 和 [[#Data],[佣金金額]] 是代表結構化參照外部部分的表格指定元。 外部參照在表格名稱後面，您用方括弧將其括住。

• 結構化參照：    (DeptSales[[#Totals]，[銷售金額]] 和 DeptSales[[#Data]，[傭金金額]] 是結構化參照，以以數據表名稱開頭並以欄指定元結尾的字串表示。

若要手動建立或編輯結構化參照，請使用這些語法規則：

• 在識別符號前後使用方括弧    所有表格、欄及特殊項目指定元必須用成對的方括弧 ([ ]) 括住。 含有其他指定元的指定元需要外部成對方括弧來括住其他指定元的內部成對方括弧。 例如： =DeptSales[[銷售人員]：[區域]]

• 所有欄標題都是文字字串    但是當它們用於結構化參照時，不需要使用引號。 數字或日期 (如 2014 或 1/1/2014)，也會被視為文字字串。 您不能將運算式與欄標題一起使用。 例如，DeptSalesFYSummary[[2014]:[2012]] 這個運算式就無法運作。

在含有特殊字元的欄標題前後使用方括弧括住    只要含有特殊字元，整個欄標題都必須用方括弧括住，也就是說，欄指定元必須用兩個方括弧括住。 例如：=DeptSalesFYSummary[[金額總計]]

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

• Tab

• 換行

• 歸位

• 逗號 (，)

• 冒號 (:)

• 句點 (.)

• 左括弧 ([)

• 右括弧 (])

• 井字型大小 (#)

• 單引號 ( )

• 雙引號 (“)

• 左大括弧 ({)

• 右大括弧 (})

• 貨幣符號 ($)

• 插入號 (^)

• & 符號 (&)

• 星號 (*)

• 加號 (+)

• 等號 (=)

• 減號 (-)

• 大於符號 (>)

• 小於符號 (<)

• 除號 (/)

• 在符號 (@)

• 反斜線 (\)

• 驚嘆號 (！)

• 左括弧 ( ()

• 右括弧 () )

• 百分比符號 (%)

• 問號 (?)

• 退格 ()

• 分號 (;)

• 波狀符號 (~)

• 底線 (_)

• 針對欄標題中的某些特殊字元使用逸出字元    有些字元具有特殊意義，必須使用單引號 (') 作為逸出字元。 例如：=DeptSalesFYSummary['#OfItems]

以下是在公式中需要逸出字元 ( ) 的特殊字元清單：

• 左括弧 ([)

• 右括弧 (])

• 井字型大小 (#)

• 單引號 ( )

• 在符號 (@)

使用空格字元來改善結構化參照的可讀性    您可以使用空格字元來改善結構化參照的可讀性。 例如：=DeptSales[ [銷售人員]:[區域] ] 或 =DeptSales[[#Headers], [#Data], [% 佣金]]。

建議使用一個空格：

• 第一個左括弧 ( 之後[)

• 在最後一個右中括弧前面 (]) 。

• 逗號之後。

參照運算子

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

特殊項目指定元

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

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

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

一般要遵循的規則為：如果是在表格內使用結構化參照 (如建立計算結果欄時)，可以使用不完整的結構化參照；但如果是在表格外部使用結構化參照，則需要使用完整的結構化參照。

使用結構化參照的範例

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

使用結構化參照的策略

使用結構化參照時，請考慮下列事項。

• 使用公式自動完成    當您輸入結構化參照，並確定使用的是正確語法時，使用公式自動完成就會十分有用。 如需詳細資訊，請參 閱使用公式自動完成。

• 決定是否要為半選取範圍中的數據表產生結構化參照    根據預設，當您建立公式時，單擊表格中的單元格範圍半選取單元格，並自動輸入結構化參照，而非公式中的單元格範圍。 此半選取範圍行為簡化了結構化參照的輸入。 您可以選取或清除 [檔案 > 選項] > [公式] > [使用公式] 對話框中的 [在公式中使用表格名稱] 複選框，來開啟或關閉此行為。

• 使用包含連至其他活頁簿中的 Excel 表格之外部連結的活頁簿    如果活頁簿包含另一個活頁簿中 Excel 表格的外部連結，則必須在 Excel 中開啟該連結來源活頁簿，以避免在包含連結的目的地活頁簿中 發生 #REF！ 錯誤。 如果您先開啟目的地活頁簿， 並出現 #REF！ 錯誤，則會在您開啟來源活頁簿時解決這些錯誤。 如果您先開啟來源活頁簿，應該就不會看到錯誤碼。

• 將範圍轉換為表格以及將表格轉換為範圍    當您將表格轉換為範圍時，所有儲存格參照都會變更為其同等的絕對 A1 樣式參照。 當您將範圍轉換成表格時，Excel 不會自動將此範圍的任何儲存格參照變更為其同等的結構化參照。

• 關閉欄標題    您可以從 [設計] 索引標籤的 [標題欄] > 開啟和關閉表格欄標題。 如果您關閉表格欄標題，使用欄名的結構化參照不會受到影響，您仍然可以在公式中使用它們。 直接參照表格標題的結構化參照 (例如 =DeptSales[[#Headers]，[%傭金]]) 會導致 #REF。

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

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

• 移動、複製及填寫結構化參照    當您複製或移動使用結構化參照的公式時，所有結構化參照都會維持原貌。

  附註: 複製結構化參照並填入結構化參照並不相同。 當您複製時，所有結構化參照都會保持不變，而當您填入公式時，完整的合格結構化參照會調整欄指定元，例如下表摘要的數列。

需要更多協助嗎？

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

相關主題

Excel 表格
概觀影片：建立 Excel 表格
並設定其格式Excel 表格
中的數據總計設定 Excel 表格
格式新增或移除列和欄以調整表格大小
篩選範圍或表格
中的數據將表格轉換成範圍
Excel 表格相容性問題
將 Excel 表格導出至 SharePoint
Excel 公式概觀