Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

=SUM(VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&…

  1. User

    =SUM(VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&"mm")&"'!D:D:M"),2,FALSE)+VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&"mm")&"'!D:D:M"),4,FALSE)+VLOOKUP(B3,INDIRECT("'[考勤表-23.xls]"&TEXT(VLOOKUP("工资表",绩效汇总!B:D,3,FALSE),"yyyy"&"-"&"mm")&"'!D:D:M"),10,FALSE))/VLOOKUP("工资表",绩效汇总!B:E,4,FALSE)

  2. 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 D:D: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 D:D:M.

    The second VLOOKUP( ) is retrieving the value from the fourth column in the range D:D:M.

    The third VLOOKUP( ) is retrieving the value from the tenth column in the range D:D: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.

  3. 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)解释一下这个公式,并说明为什么这么做,便于人们理解。

  4. 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”工作簿中检索与“工资表”相关的数据,并计算某些指标。这样设置可以使公式根据不同月份和年份自动更新,提高了公式的灵活性和可复用性。