您是否有 Excel 中可以調整大小的數據表，即在工作過程中行（列）的數量可以增加或減少？ 如果表格大小“浮動”，那麼您將必須不斷監控這一時刻並糾正它：
- 引用我們表格的報告公式中的鏈接
- 根據我們的表格構建的數據透視表的初始範圍
- 根據我們的表格構建的圖表的初始範圍
- 使用我們的表格作為數據源的下拉列表的範圍
所有這些總不會讓你感到無聊😉
創建一個動態的“橡膠”範圍會更加方便和正確，它會根據數據的實際行數和列數自動調整大小。 要實現這一點，有幾種方法。
方法一、智能表
突出顯示您的單元格範圍並從選項卡中選擇 主頁 - 格式為表格（主頁 - 格式為表格）:
如果您不需要添加到表格中的條紋設計作為副作用，那麼您可以在出現的選項卡上將其關閉 構造函數（設計）. 以這種方式創建的每個表都會收到一個名稱，該名稱可以在選項卡的同一位置替換為更方便的名稱 構造函數（設計） 在該領域 表名稱 （表名）.
現在我們可以使用動態鏈接到我們的“智能表”：
- 表1 – 鏈接到除標題行之外的整個表格 (A2:D5)
- 表 1[#全部] – 鏈接到整個表格 (A1:D5)
- 表 1[彼得] – 引用沒有第一個單元格標題的範圍列 (C2:C5)
- 表 1[#Headers] – 鏈接到帶有列名的“標題”（A1：D1）
此類引用在公式中非常有用，例如：
= SUM（表1[莫斯科]) – 計算“莫斯科”列的總和
or
=VPR(F5;表1;3;0) – 從單元格 F5 在表格中搜索月份並為其發出聖彼得堡總和（什麼是 VLOOKUP？）
通過在選項卡上選擇創建數據透視表時，可以成功使用此類鏈接 插入 - 數據透視表（插入 - 數據透視表） 並輸入智能表的名稱作為數據源：
如果您選擇此類表格的一個片段（例如，前兩列）並創建任何類型的圖表，那麼在添加新線時，它們將自動添加到圖表中。
創建下拉列表時，不能使用指向智能表格元素的直接鏈接，但您可以使用戰術技巧輕鬆繞過此限制 - 使用函數 間接 （間接），將文本轉換為鏈接：
那些。 以文本字符串（用引號括起來！）形式的智能表的鏈接會變成完整的鏈接，並且下拉列表通常會感知它。
方法二：動態命名範圍
如果出於某種原因不希望將數據轉換為智能表，那麼您可以使用稍微複雜但更微妙和通用的方法——在 Excel 中創建一個引用我們表的動態命名範圍。 然後，和智能表一樣，您可以在任何公式、報表、圖表等中自由使用創建的範圍的名稱。讓我們從一個簡單的例子開始：
任務：創建一個動態命名範圍，該範圍將引用城市列表，並在添加或刪除新城市時自動拉伸和縮小大小。
我們將需要任何版本的兩個內置 Excel 函數 - 波伊波茲 （匹配） 確定範圍的最後一個單元格，以及 INDEX （指數） 創建動態鏈接。
使用 MATCH 查找最後一個單元格
匹配（查找值，範圍，匹配類型） – 在範圍（行或列）中搜索給定值並返回找到它的單元格的序號的函數。 例如，公式 MATCH(“March”;A1:A5;0) 將返回數字 4 作為結果，因為單詞“March”位於 A1:A5 列的第四個單元格中。 最後一個函數參數 Match_Type = 0 表示我們正在尋找完全匹配。 如果未指定此參數，則函數將切換到最接近的最小值的搜索模式——這正是可以成功用於查找數組中最後一個被佔用的單元格的方法。
訣竅的本質很簡單。 MATCH 在從上到下的範圍內搜索單元格，理論上應該在找到最接近給定值的最小值時停止。 如果您指定一個明顯大於表中任何可用值的值作為所需值，則 MATCH 將到達表的最末端，什麼也找不到，並給出最後一個填充單元格的序列號。 我們需要它！
如果我們的數組中只有數字，那麼我們可以指定一個數字作為所需的值，它顯然大於表中的任何一個：
為了保證，您可以使用數字 9E + 307（9 乘以 10 的 307 次方，即 9 和 307 個零）——原則上 Excel 可以使用的最大數字。
如果我們的列中有文本值，那麼作為最大可能數的等價物，可以插入構造REPEAT(“i”, 255)——由255個字母組成的文本字符串“i”——最後一個字母字母表。 由於 Excel 在搜索時實際上會比較字符代碼，因此從技術上講，我們表格中的任何文本都將“小於”這麼長的“yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”行：
使用 INDEX 生成鏈接
現在我們知道了表格中最後一個非空元素的位置，它仍然可以形成一個指向我們整個範圍的鏈接。 為此，我們使用以下功能：
索引（範圍；行數；列數）
它通過行號和列號給出單元格的內容，例如，我們的表中的函數 =INDEX(A1:D5;3;4) 與前一種方法的城市和月份將給出 1240 - 內容從第 3 行第 4 列開始，即單元格 D3。 如果只有一列，則可以省略其編號，即公式 INDEX(A2:A6;3) 將在最後一個屏幕截圖中給出“Samara”。
還有一個不完全明顯的細微差別：如果 INDEX 不只是像往常一樣在 = 符號之後輸入到單元格中，而是用作對冒號之後範圍的引用的最後部分，那麼它不再給出單元格的內容，但它的地址！ 因此，像 $A$2:INDEX($A$2:$A$100;3) 這樣的公式將在輸出中引用範圍 A2:A4。
這就是 MATCH 函數的用武之地，我們將其插入 INDEX 以動態確定列表的結尾：
=$A$2:索引($A$2:$A$100; 匹配（代表（“我”；255）;A2:A100))
創建命名範圍
仍然需要將它們全部打包成一個整體。 打開標籤 式 （公式） 然後點擊 名稱管理員 （名稱經理）. 在打開的窗口中，單擊按鈕 創建 （新），在字段中輸入我們的範圍名稱和公式 範圍 （參考）:
它仍然可以點擊 OK 並且準備好的範圍可以在任何公式、下拉列表或圖表中使用。
- 使用 VLOOKUP 函數鏈接表和查找值
- 如何創建自動填充的下拉列表
- 如何創建數據透視表來分析大量數據