搭配 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. 若要建立表格,請選取 A1 儲存格,然後按 Ctrl+T。

  3. 確認有核取 [有標題的表格] 方塊,然後按一下 [確定]

  4. 在 E2 儲存格中,輸入等號 (=),並按一下 C2 儲存格。

資料編輯列中,[@[銷售金額]] 結構化參照會出現在等號後面。

  1. 在右括弧後面直接輸入星號 (*),然後按一下 D2 儲存格。

資料編輯列中,[@[%佣金]] 結構化參照會出現在星號後面。

  1. 按 Enter 鍵。

Excel 會自動建立計算結果的欄,並將公式複製到整欄,並將其調整每個資料列。如需有關公式與計算結果的欄的詳細資訊,請參閱Excel 表格中的使用公式

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

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

  1. 在 Excel 的範例工作表中,按一下儲存格 E2

  2. 在資料編輯列中,輸入= C2 * D2 ,然後按 enter 鍵。

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

如何變更表格名稱?

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

  1. 選取 Excel 表格中的任何儲存格,在功能區顯示 [表格工具]。

  2. [設計] 索引標籤的 [表格名稱] 方塊中,輸入您要的名稱,然後按 Enter。

在我們的範例資料中使用名稱 DeptSales

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

  • 使用 v alid 字元以字母,先名稱底線字元 (_) 或反斜線 (\)。使用字母、 數字、 句號和底線字元的名稱。

您不能使用 "C"、"c"、"R" 或 "r" 做為名稱,因為這些字母已指定為當您在 [名稱][移至] 方塊中輸入儲存格欄或列,以選取使用中儲存格欄或列時的快速鍵。

  • 不使用 c 希臘文參照名稱不可以與儲存格參照相同,例如 Z$ 100 或 R1C1。

  • 不使用 s 速度 來分隔文字無法在名稱中使用空格。您可以使用沒有空格或輸入文字分隔字元底線字元 (_) 以及句號 (.)。例如,DeptSales、 Sales_Tax 或 First.Quarter。

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

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

結構化參照語法規則

您也可以在公式中手動輸入或變更結構化參照,但要執行這項作業,最好能瞭解結構化參照的語法。 讓我們來看看下列公式範例:

=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], [% 佣金]]

建議使用一個空格:

  • 在第一個左方括弧 ([) 後面

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

  • 在逗號後面。

參照運算子

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

這個結構化參照:

參照對象:

藉由使用:

此為儲存格範圍:

=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 表格外部連結,此連結的「來源」活頁簿必須在 Excel 中開啟以避免 #REF! 錯誤碼出現在包含連結的「目的地」活頁簿中。如果您先開啟目的地活頁簿,並出現 #REF! 錯誤碼,只要開啟來源活頁簿就可以解決此問題。如果您先開啟來源活頁簿,應該就不會看到錯誤碼。

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

  • 關閉欄標題    關閉表格欄標題 (在表格 [設計] 索引標籤的 [表格樣式選項] 群組中,清除 [標題列]),並不會影響使用欄名的結構化參照,您還是可以在公式中使用它們。 直接參考表格標題的結構化參照 (例如 =DeptSales[[#Headers],[%佣金]]) 會得出 #REF 的結果。

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

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

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

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

如果填寫方向為:

而在填寫時, 按:

然後:

往上或往下

沒有欄指定元調整。

往上或往下

Ctrl

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

右邊或左邊

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

上、下、右或左

Shift

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

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

請參閱

在 Excel 中的公式的概觀

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×