在 Microsoft Excel 中使用結構化參考的四種方法

在 Excel 中工作通常圍繞著尋找不同數據點之間的聯繫。但是，在插入複雜的公式時，重複使用相對和絕對顯式單元格引用（例如“B7”或其變體）只能讓您在公式欄變得難以閱讀的混亂之前達到此目的。

Excel 中的結構化參考可讓您透過為表格及其標題指派名稱來簡化工作。然後，這些名稱可以用作隱式儲存格引用，以便 Excel 可以自動取得結構化資料並進行計算。

以下是在 Excel 中使用結構化參考的一些最常見方法。

1.計算內表

由於結構化參考僅適用於表，因此利用它們的最佳方法是在這些相同的表中。

例如，我們將建立一個從 B2 到 F8 的簡單表，其中包含商店的銷售資料。請注意，我們將表命名為“Sales”（請參閱左上角的“表名稱”）。

讓我們計算一下每筆銷售的總金額：

第 1 步： 點選 F2（但不要點選下拉圖示）。轉到“主頁”，然後轉到“插入”，然後選擇“在右側插入表格列”。這將自動向表中新增一個新列。

第 2 步： 將 G 列標題命名為「Total」。

第 3 步： 在 G3 中，插入 =[@PricePerUnit]*[@Quantity] 並按 Enter 鍵。根據需要格式化單元格輸出。

「[@PricePerUnit]」和「[@Quantity]」是對這些欄位中對應欄位的引用。在列名稱之前的“@”參數意味著每個結果單元格將使用同一表格行中的參考。

翻譯一下，G3 中的公式 =[@PricePerUnit]*[@Quantity] 本質上與寫作 =$C3*$D3 相同。

2.取得表格以外的範圍

當您想要在表格外的儲存格中使用結構化參考時，您需要在參考前面加上 TableName。在我們前面的範例中，使用「Sales[Total]」將取得表格「Sales」的標題「Total」下的整個範圍。這意味著您將在一個陣列中獲得多個可以操作的值。

以下是 Excel 中儲存格 I3 中的外觀，前提是您為該範圍留有足夠的空間。

3.對列求和和部分求和

要快速對整列求和，您可以使用「表格設計」選項（在「表格樣式選項」下）中的「總計行」複選標記。以下是取得「數量」和「總計」列總計的範例。

雖然「總計」行本身無法移動，並將被放置在表格的末尾（允許插入），但您可以在其他地方複製其結果：

• 要取得「總計」欄位中所有行的總和，請使用 =SUM(Sales[Total]).
• 如果您只想取得可見列的總和，例如篩選表格後，請使用 =SUBTOTAL(109,Sales[Total])。這個公式是表格格式中的「總行」選項在其行中實際執行的操作。

您也可以根據表中找到的特定變數來取得部分總和，而無需對其進行格式化。例如：

• 要取得 Mike 的所有銷售總和，您可以使用 =SUMIF(Sales[Seller],”Mike”,Sales[Total])。公式中，「Mike」是手動輸入的字串。
• 要取得所有 ID 為「41230」的產品的總和，請使用以下 =SUMIF(Sales[ProductID],41230,Sales[Total])。請注意，由於 ProductID 欄位具有「常規」格式，因此您可以直接輸入數字。

4.透過間接方式從表格中進行資料驗證

假設您有先前使用過的 Sales 表。您可以建立自訂資料驗證選項，以便更輕鬆地搜尋表。讓我們建立一個較小的表格，讓您在 ProductID、日期或賣家之間進行選擇，然後從這些子集中選擇任何單一項目來顯示小計。

第 1 步： 在儲存格 B13 中，建立資料驗證（資料標籤 > 資料工具 > 資料驗證）。

第 2 步： 在彈出視窗中，從“允許”選項中選擇“列表”，然後在“來源”框中手動插入列的值，並以逗號分隔。在本例中，我們插入了「ProductID、Seller、Date。」

第 3 步： 在儲存格 C13 中，建立另一個資料驗證。再次選擇“清單”。對於“來源”，插入以下公式：=INDIRECT("Sales["&B13&"]").

第 4 步： 在儲存格 D13 中，使用下列公式：=SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).

您現在可以從兩個資料驗證清單中選擇選項，小計將顯示在D13中。

以上是在 Microsoft Excel 中使用結構化參考的四種方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！