數據驗證（數據有效性）結合Offset函數的綜合用法科技頭條網

數據驗證（數據有效性）結合Offset函數的綜合用法

#數據驗證# 在2013版本以前稱為#數據有效性#。數據驗證主要用於驗證數據的輸入的準確性，避免輸入非法值以及在輸入非法值時能給出提示或警告。可以根據實際需要限制輸入範圍，提供預設值通過下拉列表選擇輸入……可以說數據驗證功能十分強大能很好的協助我們報表設計。

先看下演示效果：

如上圖所示為我們常規用法。我們通過允許-序列在 “來源” 選定我們限定的數據源單元格區域這樣我們的目標單元格就只能按我們的數據源內容進行選擇輸入了。如下圖：

但是如果我們的數據源區域會發生變化呢？比如說我們公司會有新進的員工，比如說我們的學習課程會隨著學年越往上越增多那麼我們的數據源就會不完整，發生遺漏的問題，這時候我們可以使用數據驗證結合函數來解決。這就是我們今天要講的擴展用法。

我們來看個例子：

如上圖所示：不同學年的課程是不一樣的。

一年級的課程為

二年級的課程為：

三年級的課程為：

可以看到隨著學年的上升課程也增多了。我們在本例中要實現當表格上方的年級選擇一年級的時候下面的課程單元格中的數據驗證選項應為一年級的課程，當表格上方的年級選擇二年級的時候下面的課程單元格中的數據驗證選項應為二年級的課程……

如何實現呢？這裡我們需要數據驗證結合定義名稱使用Offset()函數來實現。

先來看下OFFSET()函數的語法：

OFFSET 函數

語法

OFFSET(reference, rows, cols, [height], [width])

OFFSET 函數語法具有下列參數：

Reference 必需。要作為偏移基準的參照。引用必須引用單元格或相鄰單元格區域。否則， OFFSET 返回 #VALUE! 。Rows 必需。需要左上角單元格引用的向上或向下行數。使用 5 作為 rows 參數，可指定引用中的左上角單元格為引用下方的 5 行。 Rows 可為正數（這意味著在起始引用的下方）或負數（這意味著在起始引用的上方）。Cols 必需。需要結果的左上角單元格引用的從左到右的列數。使用 5 作為 cols 參數，可指定引用中的左上角單元格為引用右方的 5 列。 Cols 可為正數（這意味著在起始引用的右側）或負數（這意味著在起始引用的左側）。高度可選。需要返回的引用的行高。 Height 必須為正數。寬度可選。需要返回的引用的列寬。 Width 必須為正數。

備註

如果 "行" 和 "cols 偏移" 引用覆蓋了工作表的邊緣，則 offset 返回 #REF! 。如果省略 height 或 width，則假設其高度或寬度與 reference 相同。OFFSET 實際上並不移動任何單元格或更改選定區域；它只是返回一個引用。 OFFSET 可以與任何期待引用參數的函數一起使用。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 可計算 3 行 1 列區域（即單元格 C2 下方的 1 行和右側的 2 列的 3 行 1 列區域）的總值。

本例：

1.定義名稱： KC ，在引用位置輸入=OFFSET(Sheet1!$I$2,1,Sheet1!$T$3,Sheet1!$U$3,1)