在不确定列号的情况下如何使用Vlookup查找

最近小伙伴在收集放假前的排班数据 但是收上来的数据乱七八糟的 长下面这样

但是老板们只想看排班率 所以我们最终做的表应该是这样

需要计算出排班率

排班率=排班人数/总人数

合计之外的每一个单元格 都需要引用 除了最基础的等于=引用 我们还有一种更加万能的Vlookup+Match的方法 这样无论日期怎么变化 无论日期顺序是否能对上 我们都不用更改公式

例如A部门,2月1日的排班率应该这么写

=IFERROR(VLOOKUP($A18,$A$1:$K$8,MATCH(B$17,$A$2:$K$2,0),0)/VLOOKUP($A18,$M$2:$N$8,2,0),"-")

A18 单元格为部门A

A1:K8 单元格为我们收集到的排班人数

B17 单元格为排班率日期

A2:K2 单元格为我们排班人数的日期

M2:N8单元格是总人数

其中 分子排班人数的公式是

VLOOKUP($A18,$A$1:$K$8,MATCH(B$17,$A$2:$K$2,0),0)

排班人数里面的日期匹配 我们用Match函数动态确定列号

MATCH(B$17,$A$2:$K$2,0)

分母总人数比较简单 就是常规的Vlookup

VLOOKUP($A18,$M$2:$N$8,2,0)

外面套一个Iferror 防止下一次收集排班更改日期导致错误码 影响美观

通过上面的公式 设置一个百分比格式(快捷键CTRL+SHIFT+5)就可以自动填写部门x日期的排班率

部门合计我们需要确定部门的行号即可 为防止部门变动 最好也用公式确定行号 这一块 可以有两种写法

一种是用Sum,Offset,Index,Match函数组合

=SUM(OFFSET(INDEX($A$1:$K$8,MATCH($A18,$A$1:$A$8,0),2),0,0,1,11))/(VLOOKUP($A18,$M$2:$N$8,2,0)*10)

思路就是用Index,Match确定部门第一个单元格 然后Offset扩展到部门的所有列 最后Sum求和

日期合计同理

一种是用Sum,Indirect,Match函数组合

=SUM(INDIRECT("B"&MATCH($A18,$A$1:$A$8,0)&":K"&MATCH($A18,$A$1:$A$8,0)))/(VLOOKUP($A18,$M$2:$N$8,2,0)*10)

思路也是通过Match确定部门行号 然后借用Indirect构建区域 最后Sum求和

总计直接求和相除

完