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

Excel 使用表格及欄名稱

來取代明確的儲存格參照

=Sum(C2:C7)

=SUM(DeptSales[銷售金額])

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

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

若要在公式中包含結構化參照,請按一下您要參照的表格儲存格,而不是在公式中輸入其儲存格參照。 讓我們使用下列範例資料來輸入自動使用結構化參照來計算銷售傭金金額的公式。

銷售人員

Region

銷售 金額

% 佣金

佣金金額

立民

北部

260

10%

棟材

南部

660

15%

莉華

東部

940

15%

哲翰

西部

410

12%

媚卉

北部

800

15%

冠廷

南部

900

15%

  1. 複製上表中的範例資料,包括欄標題,然後將它貼到新工作表的儲存格 A1 Excel儲存格。

  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」的名稱,系統會提示您選擇唯一的名稱。

  • 使用物件識別碼  如果您打算混合使用表格、樞紐分析表和圖表,建議以物件類型為名稱的首碼。 例如:tbl_Sales資料表的pt_Sales、銷售樞紐分析表的 chrt_Sales,以及銷售圖表的 ptchrt_Sales或樞紐分析圖。 這會將您所有的名稱都保留于 Name Manager 中的已排序 清單中

結構化參照語法規則

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Tab

  • 換行

  • 回車

  • 逗號 (,)

  • 冒號 (:)

  • 句點 (.)

  • 左括弧 ([)

  • 右括弧 (])

  • 井號 (#)

  • 單引號 ()

  • 雙引號 (")

  • 左大 ({)

  • 右大 (})

  • 貨幣符號 ($)

  • Caret (^)

  • Ampersand (&)

  • 星號 (*)

  • 加號 (+)

  • 等號 (=)

  • 減號 (-)

  • 大於符號 (>)

  • 小於符號 (<)

  • 除號 (/)

  • 針對欄標題中的某些特殊字元使用逸出字元    有些字元具有特殊意義,必須使用單引號 (') 作為逸出字元。 例如:=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概觀
影片:建立表格並Excel格式
合計資料表中的Excel資料
格式化表格Excel表格
新增或移除列和欄以調整表格大小
篩選範圍或表格中的資料
將表格轉換成範圍
Excel相容性問題
匯出資料Excel資料表SharePoint
公式概觀Excel

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×