850在储存格范围找出是否有重复-SUMPRODUCT、COUNTA、COUNT函数

850	在储存格范围找出是否有重复 SUMPRODUCT、COUNTA、COUNT函数
示范档	无	范例档	850.XLSX	结果档	850F.XLSX

宝岛银行每天八点时将由三位行员一同到场，一位取消保全系统，一位打开侧门，一位监控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。