Excel 的使用通常以識別不同資料集之間的關係為中心。然而，在插入複雜的公式時，過度使用相對和絕對明確單元格引用（例如“B7”及其變體）可能會導致公式欄混亂無序。
在 Excel 中使用結構化參考可以透過允許您為表格及其標題指定名稱來增強此過程。這些指定的名稱用作隱式儲存格引用，使 Excel 能夠自動擷取和計算結構化資料。
以下是在 Excel 中使用結構化參考的幾種有效方法。
1. 在表格內執行計算
結構化引用僅與表一起使用，因此使用它們的最佳方法是在這些類型的表中。
例如，建立一個從 B2 到 F8 的簡單表，其中包含商店的銷售資訊。請務必將表標記為“銷售”（檢查左上角的“表名稱”）。
接下來，我們將計算每筆銷售的總額：
第 1 步：選擇儲存格 F2（確保未按一下下拉圖示）。導航至“主頁”，然後“插入”，然後選擇“在右側插入表格列”以自動在表格中包含新列。
步驟 2：將新列 G 的標題指定為「總計」。
步驟 3：在儲存格 G3 中輸入=[@PricePerUnit]*[@Quantity]並按 Enter。根據需要調整儲存格格式。
引用“[@PricePerUnit]”和“[@Quantity]”對應於列中各自的欄位。列名稱前面的“@”符號表示每個結果單元格將從表的同一行派生其值。
為了澄清，G3 中的公式=[@PricePerUnit]*[@Quantity]實際上對應於=$C3*$D3。
2. 檢索超出表格的範圍
當您打算在表格外部的儲存格中使用結構化參考時，必須以表格名稱開始引用。例如，在我們前面的範例中，使用「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. 使用 INDIRECT 從表中實現資料驗證
假設您正在使用前面提到的銷售表，您可以建立自訂資料驗證設定以方便更輕鬆地搜尋資料。讓我們建立一個較小的表，允許選擇產品 ID、日期或賣家，使您能夠從這些集中選擇單一項目來顯示小計。
步驟 1：在儲存格 B13 中，設定資料驗證（資料標籤 > 資料工具 > 資料驗證）。
步驟 2：在出現的彈出視窗中，從“允許”下拉清單中選擇“清單”，然後將列值手動輸入到“來源”欄位中，並以逗號分隔。在此範例中，我們輸入了「ProductID、Seller、Date」。
步驟 3：在儲存格 C13 中，建立另一個資料驗證實例。再次選擇“清單”。對於“來源”，請包含公式：=INDIRECT("Sales["&B13&"]")。
步驟 4：在儲存格 D13 中，套用下列公式：=SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total])。
現在，您可以從兩個資料驗證清單中選擇選項，結果小計顯示在 D13 中。
發佈留言