錦子老師

寶島銀行每天八點時將由三位行員一同到場，一位取消保全系統，一位打開側門，一位監控ATM，但全程皆須由保全陪同，我們想要知道每一位行員該月份負責的最後一天為那一天，如下圖所示，要如何做呢？

步驟1：點取F3儲存格輸入公式「=OFFSET($A$1,MAX((SUBSTITUTE($B$2:$B$32,E2,"")<> $B$2:$B$32)*ROW($B$2:$B$32))-1,0)」後，按Ctrl+Shift+Enter將其轉為陣列公式({=OFFSET($A$1, MAX((SUBSTITUTE($B$2:$B$32,E2,"")<>$B$2:$B$32)*ROW($B$2:$B$32))-1,0)})完成輸入。

OFFSET函數解析

OFFSET(Reference,Rows,Cols,Height,Width) 根據指定參照位址取得列數及欄數範圍

Reference 為參照位址，用以計算位移結果的開始位址。

Rows 用以指示左上角儲存格要向上(負數值)或向下(正數值)移動的列數

Cols  用以指示左上角儲存格要向左(負數值)或向右(正數值)移動的欄數

Height 為傳回參照位址包括的儲存格高度(儲存格範圍列數)

Width 為傳回參照位址包括的儲存格寬度(儲存格範圍欄數)

MAX函數解析

Max(Number1,Number2…..) 傳回最大值

Number,Number2…. 為1到255個參數，代表欲計算的值、空白儲存格、文字字串、邏輯值等.. 。

SUBSTITUTE函數解析

SUMPRODUCT(Array1,Array2….) 傳回多個陣列或範圍中各個元素相乘積之總和

Array1,Array2…. 為起2到255個陣列，用以求其乘積後再加總，所有陣列大小須相同。

公式解析

=OFFSET($A$1,MAX((SUBSTITUTE($B$2:$B$32,E2,"")<> $B$2:$B$32)*ROW($B$2:$B$32))-1,0) 首先將B2到B32儲存格範圍中包含E2儲存格內容的資料全部用空白取代(SUBSTITUTE ($B$2:$B$32,E2,""))，若不等於B2到B32儲存格範圍內容則傳回TRUE(值為1),否則傳回FALSE(值為0)，在乘上所在列編號(ROW($B$2:$B$32))，傳回其最大值(MAX((SUBSTITUTE($B$2:$B$32, E2,"")<> $B$2:$B$32)*ROW($B$2:$B$32)))即為最後負責日期的列號，但由於第一列為起始位址，故必須將最大值減一，才不會偏差一列，整個亂掉，由於只是在A欄抓資料，故欄位移動為0。

步驟2：將滑鼠指標移到F2儲存格右下角拖拉方塊上方，待指標變為「+」後，按滑鼠左鍵二下或按住滑鼠左鍵不放拖曳至F20儲存格，將H3儲存格公式複製F3:F20儲存格中，如下圖所示。

今天的教程就到這裡啦。希望大家能有收穫！