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

套用到
Microsoft 365 Excel Mac 版 Microsoft 365 Excel Excel 2024 Mac 版 Excel 2024 Excel 2021 Mac 版 Excel 2021 Excel 2019 Excel 2016 Excel Mobile

建立 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 或 First.Quarter。
  • 使用不超過 255 個字元 資料表名稱最多可以有高達 255 個字元。
  • 使用唯一的資料表名稱 不允許重複的名稱。 Excel 不會區分名稱中的大小寫字元，因此如果您輸入 “Sales”，但在同一個活頁簿中已經有另一個名為 “SALES” 的名稱，系統會提示您選擇唯一名稱。
  • 使用物件識別碼 如果您打算混合使用表格、樞紐分析表和圖表，建議您在名稱前面加上物件類型。 例如：銷售資料表的 tbl_Sales、銷售樞紐分析表的 pt_Sales，以及銷售圖表的 chrt_Sales，或銷售樞紐分析圖的 ptchrt_Sales。 這樣會將您的所有名稱保留在 [名稱管理員] 中的排序清單中。

結構化參照語法規則

您也可以在公式中手動輸入或變更結構化參照，但若要執行此操作，瞭解結構化參照語法將有所幫助。 讓我們回顧一下下面的公式範例：

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

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

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

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

  • 用括弧括住規範 所有表格、欄和特殊項目規範都必須以相符的括弧括住 ([ ]) 。 含有其他指定元的指定元需要外部成對方括弧來括住其他指定元的內部成對方括弧。 例如： =DeptSales[[Sales Person]：[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 公式概觀