搭配 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. 按下輸入

    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。

  • 不要使用空格來分隔文字 無法在名稱中使用空格。您可以使用的底線字元 (_) 及句號 (.) 做為 word 分隔符號。例如,DeptSales、 Sales_Tax 或 First.Quarter。

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

  • 使用唯一表格名稱 不允許重複的名稱。 Excel 不會區分名稱中的大寫和小寫字元,因此,如果您輸入「Sales」,但已有其他名稱為「SALES」的表格在同一活頁簿中,Excel 會提示您選擇唯一名稱。

  • 使用物件識別碼 如果您打算在具有混合表格、 樞紐分析表和圖表,最好的物件類型的前置詞您的名稱。例如: tbl_Sales sales 資料表、 樞紐分析表的銷售 pt_Sales 及 chrt_Sales 銷售圖表或 ptchrt_Sales 的銷售的樞紐分析圖。這會將所有您的名稱排序清單中的 [名稱管理員

結構化參照語法規則

您也可以輸入或變更以手動方式在公式中的結構化的參照,但若要執行這項作業,這樣有助於瞭解結構化的參照語法。我們請透過下列公式的範例:

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

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

  • 資料表名稱:   DeptSales是自訂的資料表名稱。它會參考表格資料,不含任何頁首] 或 [合計列。您可以使用預設表格名稱,例如 Table1,或將其變更為使用的自訂名稱。

  • 欄指定元:   [銷售金額][佣金金額] 是欄指定元所代表的資料行的名稱。他們參考資料行的資料,沒有任何欄標題或合計列。永遠如下圖所示,請以方括弧括住規範。

  • 項目指定元:   [#Totals][#Data]是參照表格,如合計列的特定部分的特殊項目指定元。

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

  • 結構化參照:   (DeptSales [[#Totals],[銷售金額]]DeptSales [[#Data],[佣金金額]是結構化的參照,以表格名稱開頭和欄指定元結尾的字串表示。

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

  • 識別符號前後使用方括弧   所有資料表、 欄及特殊項目指定元都必須放在雙引號中比對方括弧 ([])。包含其他規範的規範要求外部相符的方括號括住內部比對括弧括住的其他規範。例如: = 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 Row」指定元,這可能會在您新增更多列時,導致非預期的計算結果。 若要避免計算問題,請確定您在輸入任何結構化參照公式前,已在資料表中輸入多個列。

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

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

結構化參照的類型

範例

註解

不完整的

=[銷售金額]*[% 佣金]

乘上目前列的對應值。

完整的

=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],[%佣金]]) 會導致#REF

  • 新增或刪除欄與列至資料表   因為經常變更表格的資料範圍,結構化參照的儲存格參照自動調整。例如,如果您在公式中使用表格名稱計算資料表中的所有資料儲存格,然後新增一列的資料的儲存格參照自動調整。

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

  • 移動、 複製及填寫結構化的參照   複製或移動使用結構化的參照的公式時,所有結構化的參照會保持相同。

    附註: 複製結構化的參照,並執行的結構化參照的填滿不相同的動作。複製時,所有結構化的參照時,保持相同,當您填滿公式,完整的結構化的參照調整欄識別符號,例如秩序下表中。

如果填寫方向為:

而在填寫時, 按:

然後:

往上或往下

沒有欄指定元調整。

往上或往下

Ctrl

欄指定元的調整方式與數列相同。

右邊或左邊

欄指定元的調整方式與數列相同。

上、下、右或左

Shift

會移動目前儲存格值,並插入欄指定元,而不是覆寫目前儲存格中的值。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

相關主題

Excel 表格概觀
影片: 建立和格式化 Excel 表格
合計 Excel 表格中的資料
格式化 Excel 表格
調整藉由新增或移除列和欄的表格大小
篩選範圍或表格中的資料
轉換表格為範圍
Excel 表格相容性問題
匯出 Excel 表格至 SharePoint
概觀中的公式Excel

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×