使用 Microsoft Excel 時，用戶通常不會事先知道表格中將包含多少信息。 因此，我們並不了解在所有情況下都應涵蓋哪些範圍。 畢竟，一組單元格是一個變量概念。 為了擺脫這個問題，有必要使范圍生成自動化，以便它完全基於用戶輸入的數據量。
在 Excel 中自動更改單元格範圍
Excel 中自動範圍的優點是它們使使用公式變得更加容易。 此外，它們還可以顯著簡化包含大量公式的複雜數據的分析，其中包含許多函數。 你可以給這個範圍一個名字，然後它會根據它包含的數據自動更新。
如何在 Excel 中進行自動範圍更改
假設您是需要投資某個對象的投資者。 因此，我們希望獲得有關在這筆資金用於該項目的整個時間內您總共可以賺取多少的信息。 然而，為了獲得這些信息，我們需要定期監控這個對像給我們帶來了多少總利潤。 製作與此屏幕截圖相同的報告。
乍一看，解決方案很明顯：您只需對整列求和。 如果條目中出現，金額將獨立更新。 但是這種方法有很多缺點：
- 如果問題以這種方式解決，將無法將 B 列中包含的單元格用於其他目的。
- 這樣的表會消耗大量的 RAM，這將導致無法在弱計算機上使用文檔。
因此，有必要通過動態名稱來解決這個問題。 要創建它們，您必須執行以下操作序列：
- 轉到位於主菜單中的“公式”選項卡。 將有一個“定義的名稱”部分，其中有一個“分配名稱”按鈕，我們需要單擊該按鈕。
- 接下來，將出現一個對話框，您需要在其中填寫屏幕截圖所示的字段。 需要注意的是，我們需要應用函數 = 位移 與函數一起 CHECK創建自動更新範圍。
- 之後我們需要使用函數 和，它使用我們的動態範圍作為參數。
完成這些步驟後，我們可以看到當我們在其中添加新元素時，屬於“收入”範圍的單元格的覆蓋範圍是如何更新的。
Excel中的OFFSET函數
讓我們看看我們之前在“範圍”字段中記錄的函數。 使用功能 處置 給定 B 列中填充了多少個單元格，我們可以確定範圍的數量。 函數參數如下：
- 啟動單元格。 使用此參數，用戶可以指示範圍中的哪個單元格將被視為左上角。 它將向下和向右報告。
- 按行偏移的範圍。 使用此範圍，我們設置從範圍的左上角單元格開始偏移的單元格數。 您不僅可以使用正值，還可以使用零和負值。 在這種情況下，位移可能根本不發生，或者會以相反的方向進行。
- 按列的範圍偏移。 該參數與上一個參數類似，只是它允許您設置範圍的水平移動程度。 在這裡，您還可以同時使用零值和負值。
- 高度範圍的數量。 事實上，這個論點的標題讓我們明白了它的含義。 這是范圍應增加的單元格數。
- 寬度範圍的值。 該論點與前一個類似，只是它涉及列。
如果不需要，則不需要指定最後兩個參數。 在這種情況下，範圍值將只有一個單元格。 例如，如果您指定公式 =偏移量（A1；0；0），此公式將引用與第一個參數中的單元格相同的單元格。 如果垂直偏移設置為 2 個單位，那麼在這種情況下，單元格將引用單元格 A3。 現在讓我們詳細描述一下函數的含義 CHECK.
Excel 中的 COUNT 函數
使用功能 CHECK 我們確定總共填充了 B 列中的單元格。 也就是說，使用兩個函數，我們確定範圍內有多少個單元格被填充，並根據接收到的信息，確定範圍的大小。 因此，最終公式如下： =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
下面我們來看看如何正確理解這個公式的原理。 第一個參數指向我們的動態範圍從哪裡開始。 在我們的例子中，這是單元格 B2。 其他參數具有零坐標。 這表明我們不需要相對於左上角單元格的偏移量。 我們所填寫的只是范圍的垂直大小，我們使用函數作為 CHECK，它決定了包含一些數據的單元格的數量。 我們填寫的第四個參數是單位。 因此，我們表明範圍的總寬度應該是一列。
因此，使用函數 CHECK 用戶可以通過僅加載包含某些值的那些單元格來盡可能有效地使用內存。 因此，在工作中不會出現與電子表格將在其上運行的計算機性能不佳相關的額外錯誤。
因此，為了根據列數確定範圍的大小，您需要執行類似的操作序列，只有在這種情況下，您需要在第三個參數中指定單位，在第四個參數中指定公式 CHECK.
我們看到，借助 Excel 公式，您不僅可以自動進行數學計算。 這只是滄海一粟，但實際上它們允許您自動化幾乎所有想到的操作。
Excel中的動態圖表
因此，在最後一步中，我們能夠創建一個動態範圍，其大小完全取決於它包含多少填充單元格。 現在，基於此數據，您可以創建動態圖表，一旦用戶進行任何更改或添加額外的列或行，該圖表就會自動更改。 這種情況下的動作順序如下：
- 我們選擇我們的範圍，然後我們插入一個“帶分組的直方圖”類型的圖表。 您可以在“圖表 - 直方圖”部分的“插入”部分找到此項目。
- 我們在直方圖的隨機列上單擊鼠標左鍵，然後函數 =SERIES() 將顯示在函數行中。 在屏幕截圖中，您可以看到詳細的公式。
- 之後，需要對公式進行一些更改。 您需要替換“Sheet1！”之後的範圍到範圍的名稱。 這將導致以下功能： =ROW(Sheet1!$B$1;;Sheet1!收入;1)
- 現在仍然需要向報告中添加一條新記錄，以檢查圖表是否自動更新。
讓我們看一下我們的圖表。
讓我們總結一下我們是如何做到的。 在上一步中，我們創建了一個動態範圍，其大小取決於它包含多少元素。 為此，我們使用了功能組合 CHECK и 處置. 我們為這個範圍命名，然後我們使用對這個名稱的引用作為我們直方圖的範圍。 在第一階段選擇哪個特定範圍作為數據源並不那麼重要。 主要是稍後將其替換為範圍的名稱。 這樣可以節省大量內存。
命名範圍及其用途
現在讓我們更詳細地討論如何正確創建命名範圍並使用它們來執行為 Excel 用戶設置的任務。
默認情況下，我們使用常規單元格地址以節省時間。 當您需要一次或多次寫入範圍時，這很有用。 如果需要一直使用或需要自適應，則應使用命名範圍。 它們使創建公式變得更加容易，並且用戶分析包含大量函數的複雜公式也不會那麼困難。 讓我們描述創建動態範圍所涉及的一些步驟。
這一切都從命名單元開始。 為此，只需選擇它，然後在其名稱字段中寫入我們需要的名稱。 重要的是要容易記住。 命名時需要考慮一些限制：
- 最大長度為 255 個字符。 這足以指定一個你內心想要的名字。
- 名稱不得包含空格。 因此，如果它包含多個單詞，則可以使用下劃線字符分隔它們。
如果稍後在此文件的其他工作表上我們需要顯示此值或應用它來執行進一步的計算，則無需切換到第一個工作表。 您只需寫下此範圍單元格的名稱即可。
下一步是創建一個命名範圍。 程序基本相同。 首先，您需要選擇一個範圍，然後指定其名稱。 之後，此名稱可用於 Excel 中的所有其他數據操作。 例如，命名範圍通常用於定義值的總和。
此外，可以使用“設置名稱”工具使用“公式”選項卡創建命名範圍。 在我們選擇它之後，將出現一個窗口，我們需要在其中為我們的範圍選擇一個名稱，並指定它將手動擴展到的區域。 您還可以指定此範圍的操作位置：在單張紙內或整本書中。
如果已經創建了一個名稱範圍，那麼為了使用它，有一個稱為名稱管理器的特殊服務。 它不僅允許編輯或添加新名稱，還可以在不再需要時刪除它們。
應該記住，在公式中使用命名範圍時，刪除它後，公式不會自動被正確的值覆蓋。 因此，可能會出現錯誤。 因此，在刪除命名範圍之前，您需要確保它沒有在任何公式中使用。
創建命名範圍的另一種方法是從表中獲取它。 為此，有一個名為“從選擇中創建”的特殊工具。 據我們了解，要使用它，您必須首先選擇我們將編輯的範圍，然後設置我們擁有標題的位置。 因此，基於這些數據，Excel 將自動處理所有數據，並自動分配標題。
如果標題包含多個單詞，Excel 會自動用下劃線分隔它們。
因此，我們弄清楚瞭如何創建動態命名範圍以及它們如何讓您自動化處理大量數據。 如您所見，使用多個功能和功能內置的程序工具就足夠了。 根本沒有什麼複雜的，儘管乍一看對於初學者來說似乎很複雜。