當您 建立 Excel 表格時,Excel 會為表格及表格中的每個欄標題指派名稱。 當您新增公式至 Excel 表格時,這些名稱可在您輸入公式時自動出現,並自動選取表格中的儲存格參照,而不需要手動輸入。 以下範例示範 Excel 的功能:
Excel 使用表格及欄名稱 |
來取代明確的儲存格參照 |
---|---|
=Sum(C2:C7) |
=SUM(DeptSales[銷售金額]) |
表格與欄名的組合,稱為結構化參照。 每當您新增或移除表格資料時,結構化參照中的名稱也會隨之調整。
當您在 Excel 表格以外建立一個參照表格資料的公式時,也會出現結構化參照。 參照可以讓您更容易地在大型活頁簿中尋找表格。
若要在公式中包含結構化參照,請按一下您要參照的表格儲存格,而不是在公式中輸入其儲存格參照。 讓我們使用下列範例數據,輸入會自動使用結構化參照來計算銷售傭金金額的公式。
銷售人員 |
區域 |
銷售金額 |
% 佣金 |
佣金金額 |
---|---|---|---|---|
立民 |
北部 |
260 |
10% |
|
棟材 |
南部 |
660 |
15% |
|
莉華 |
東部 |
940 |
15% |
|
哲翰 |
西部 |
410 |
12% |
|
媚卉 |
北部 |
800 |
15% |
|
冠廷 |
南部 |
900 |
15% |
-
複製上表中的範例數據,包括欄標題,並貼到新 Excel 工作表的儲存格 A1 中。
-
若要建立表格,請選取數據範圍內的任何單元格,然後按 Ctrl+T。
-
確定已核取 [ 有標題的表格] 方 塊,然後按兩下 [ 確定]。
-
在單元格 E2 中,輸入等號 (=) ,然後按單擊單元格 C2。
資料編輯列中,[@[銷售金額]] 結構化參照會出現在等號後面。
-
直接在右括弧後面輸入星號 (*) ,然後按兩下單元格 D2。
資料編輯列中,[@[%佣金]] 結構化參照會出現在星號後面。
-
按 Enter。
Excel 會自動建立計算結果欄,將公式複製到整欄中,並根據每個資料列調整。
使用明確的儲存格參照時會如何?
如果您在計算結果欄中輸入明確的儲存格參照,則較難看出公式的計算內容。
-
在範例工作表中,按兩下 E2 單元格
-
在數據編輯列中,輸入 =C2*D2 ,然後按 Enter。
請注意,當 Excel 將該公式複製到欄中時,它並不會使用結構化參照。 例如,如果您在現有 C 和 D 欄之間新增一欄,您必須修改公式。
如何變更表格名稱?
建立 Excel 表格時,Excel 會建立預設的表格名稱 (表格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], [% 佣金]]。
建議使用一個空格:
-
第一個左括弧 ( 之後[)
-
在最後一個右中括弧前面 (]) 。
-
逗號之後。
參照運算子
若希望指定儲存格範圍時更加彈性,可以使用下列參照運算子合併欄指定元。
這個結構化參照: |
參照對象: |
藉由使用: |
此為儲存格範圍: |
---|---|---|---|
=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 技術社群中的專家,或在社群中取得支援。
相關主題
Excel 表格 概觀影片:建立 Excel 表格 並設定其格式Excel 表格 中的數據總計設定 Excel 表格 格式新增或移除列和欄以調整表格大小篩選範圍或表格 中的數據將表格轉換成範圍Excel 表格相容性問題將 Excel 表格導出至 SharePointExcel 公式概觀