=SUM(VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&…
- User
=SUM(VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&"mm")&"'!M"),2,FALSE)+VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&"mm")&"'!M"),4,FALSE)+VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&"mm")&"'!M"),10,FALSE))/VLOOKUP("工资表",绩效汇总!B:E,4,FALSE)
- Assistant
This formula is calculating the sum of three VLOOKUP functions, divided by another VLOOKUP function.
The formula is looking up a value in cell B3 in the "考勤表-23.xls" workbook.
The VLOOKUP function is searching for the value in cell B3 in the range M in the worksheet that corresponds to the month and year from the "工资表" worksheet.
The first VLOOKUP( ) is retrieving the value from the second column in the range M.
The second VLOOKUP( ) is retrieving the value from the fourth column in the range M.
The third VLOOKUP( ) is retrieving the value from the tenth column in the range M.
After retrieving these three values, they are summed together using the SUM( ) function.
The denominator of the division is another VLOOKUP function. The VLOOKUP( ) is searching for the value "工资表" in the range B:E in the "绩效汇总" worksheet and retrieving the value from the fourth column.
The final result of the formula is the sum of the three values divided by the denominator.
- User
=SUM(VLOOKUP(B3,INDIRECT(“'[考勤表-23.xls]”&TEXT(VLOOKUP(“工资表”,绩效汇总!B:D,3,FALSE),“yyyy”&“-”&“mm”)&“'!D:M”),2,FALSE)+VLOOKUP(B3,INDIRECT(“'[考勤表-23.xls]”&TEXT(VLOOKUP(“工资表”,绩效汇总!B:D,3,FALSE),“yyyy”&“-”&“mm”)&“'!D:M”),4,FALSE)+VLOOKUP(B3,INDIRECT(“'[考勤表-23.xls]”&TEXT(VLOOKUP(“工资表”,绩效汇总!B:D,3,FALSE),“yyyy”&“-”&“mm”)&“'!D:M”),10,FALSE))/VLOOKUP(“工资表”,绩效汇总!B:E,4,FALSE)解释一下这个公式,并说明为什么这么做,便于人们理解。
- Assistant
这个公式计算了三个VLOOKUP函数的和,然后除以另一个VLOOKUP函数的结果。
该公式在“考勤表-23.xls”工作簿中查找B3单元格中的值。
VLOOKUP函数在“绩效汇总”工作表的B:D范围内查找值“工资表”,然后检索第三列的值,这个值代表了月份和年份。
然后,用TEXT函数将这个值格式化为“yyyy-mm”的形式,并将其与“'[考勤表-23.xls]”字符串连接起来,形成一个字符串。
INDIRECT函数将这个字符串作为参数,动态地构建引用到考勤表-23.xls工作簿中对应月份的D:M范围。
然后,三个VLOOKUP函数分别在这个动态引用的范围内查找B3的值。第一个VLOOKUP函数检索第二列的值,第二个VLOOKUP函数检索第四列的值,第三个VLOOKUP函数检索第十列的值。
这三个值被SUM函数加总。
分母部分是另一个VLOOKUP函数,它在绩效汇总工作表的B:E范围内查找值“工资表”,然后检索第四列的值。
最终公式的结果是这三个值的总和除以分母的结果。
这种设置可能是为了从“绩效汇总”工作表和“考勤表-23.xls”工作簿中检索与“工资表”相关的数据,并计算某些指标。这样设置可以使公式根据不同月份和年份自动更新,提高了公式的灵活性和可复用性。