您可能不知道如何使用Excel中的OFFSET函數

您是否知道Microsoft Excel 中的 OFFSET 函數可以幫助您執行許多其他操作，而不僅僅是儲存格參考？以下是您可能不知道的在 Excel 中使用 OFFSET 的獨特方法。

Microsoft Excel 的 OFFSET 函數是一個靈活且強大的工具。不幸的是，由於其複雜性和類似函數的數量，該 Excel 函數並未充分利用。

這個相對簡單的功能可以幫助使用者節省時間和精力。它簡化了從排序良好的主資料集中獲取準確資料點的過程。它還允許簡單地創建詳細報告、互動式視覺化和複雜的儀表板。

建立自訂大小的資料集

Excel 中 OFFSET 函數最常見的用途之一是收集可調整大小的資料集。對於大型資料集，人們可能只想在任何給定時間考慮資料的一小部分。

例如，要提供員工銷售總額的自動報告，您需要取得給定一週或幾週的總金額。

為每個總計編寫不同的 SUM 函數非常容易。然而，使用OFFSET函數會更容易使用並且重複使用更快。

首先，在儲存格 B6 中輸入您想要開始的日期。然後，在 B7 中輸入您想要執行報告的天數。接下來，使用簡單的 SUM 和 AVERAGE 函數來取得所選時間內每位員工的更多資料：

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

調整開始日期或時間就像編輯儲存格一樣簡單，但您可以更輕鬆地拖曳資料組並調整資料組的大小。

允許建立自訂報告

使用與上述相同的方法，透過自訂變數非常容易地建立完整的報告。您可以讓 OFFSET 函數中的第一個參數引用另一張工作表。這允許您將數據輸入和報告分開。

上述操作也可以將選定的資料從一個工作表拖曳到另一個工作表。此外，它允許您僅顯示與當前報告相關的資訊。使用之前的相同數據設定一個顯示每日總計的報告非常簡單。您無需檢索不相關日期的資料即可執行此操作。

放置在儲存格 B5 中的下列偏移函數將擷取 B2 中指定的天數。它從 B1 中選擇的周開始，僅顯示相關期間的數據：

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

可以限制 B1 和 B2 中的條目，並使用資料驗證在 Excel 中建立可用週數的下拉清單。

試算表分頁

OFFSET 函數還可以實現簡單的電子表格分頁，這是通常與使用 Excel Developer 標籤相關的任務。這可以使處理大量資料變得更簡單、更有效。

當使用包含多達 30,000 行資料的電子表格時，一次查看所有資料可能會很困難。建立一個接受 1 到 300 之間數字範圍的下拉式選單，讓您可以按頁快速分隔數據，每頁有 100 個結果。

OFFSET 函數始終將其錨點放置在頂部資料單元格中。然而，第二個參數是垂直偏移。該值等於所選頁碼減100。偏移量是水平的，第三個參數可以設定為0。

第四個參數設定為 100，第五個參數是資料集中的列數。這種方法使得分割和管理資料表變得非常容易。

下面是分頁資料的範例。它在一張紙上包含從感測器記錄的 500 條訊息，在另一張紙上包含分頁報告。

該報告使用單元格 B1 中的資料驗證來建立一個包含頁面的下拉式選單。儲存格 A3 中的下列表達式建立報告正文：

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

以上是Excel中偏移函數的一些使用方法，很少人知道，但它們確實很有用，尤其是當你需要處理大量資料時。

希望這篇文章對您有用。