【和EXCEL对比】多表连接进行计算

    已知员工的基础工资表、员工的缺勤记录表和员工的绩效表,需要计算员工的实际薪水。计算时,使用如下公式:应发周薪=标准周薪*(1+绩效加成-缺勤小时/40)+奖金。


数据存储在三个工作表中,需要根据员工编号连接计算。

  • 由于考勤表和绩效表中,并未包含所有员工的资料,因此如果将这些数据单纯复制在一起,排序后分组时,各组的结构并不统一,此时再计算应发周薪时,就无法统一公式,因此只能用公式查找的方法将表格中的数据整合。为了引入员工的缺勤信息,在D1中填入公式:=IFERROR(INDIRECT(“’11bAbsence’!”&
    ADDRESS(MATCH(A2,’11bAbsence’!$A:$A,0),2)),0),其中11bAbsence为缺勤信息所在的工作表,如果无法找到员工的缺勤信息,则返回0。双击D1的右下角,将公式设定到所有员工数据行。类似的为了引入员工的绩效和奖金信息,在E1中填入公式=IFERROR(INDIRECT(“’12cPerformance’!”&
    ADDRESS(MATCH(A2,’12cPerformance’!$A:$A,0),2)),0),在F1中填入公式=IFERROR(INDIRECT(“’12cPerformance’!”&
    ADDRESS(MATCH(A2,’12cPerformance’!$A:$A,0),3)),0)。其中12cPerformance为绩效和奖金信息存储的工作表,如果找不到相应信息则返回0。同样把公式设定到所有员工数据行。结果如下:
  • 然后,在G2格中计算应发周薪,填入公式=C2*(1+E2-D2/40)+F2,结果如下:

    用Excel处理多表连接问题时,通常只能用复杂的公式来查找引用,难度较高,且非常不易于查看或修改。如果不会使用Excel中的查找引用等公式,那就只好手工去处理数据,这种工作就更是复杂了。


为了多表连接时判断连接值,三个计算网中的A2格及其同位格均被设为主格。

  • 执行连接操作,首先将缺席信息连接到第一张表中。为此,在第二个计算网中选定B2,按Ctrl+C复制,然后打开第一个计算网,选择D2,执行连接操作,选择左连接,结果如下:
  • 再将绩效和奖金信息连接到第一张表中。为此,在第三个计算网中同时选定B2和C2,按Ctrl+C复制,然后打开第一个计算网,选择E2,执行连接操作,选择左连接,结果如下:
  • 最后,计算应发周薪,只需要在G2中填写表达式=C2*(1+E2-D2/40)+F2即可,结果如下:

    使用计算表,只需要非常基本的操作,就可以完成多表连接等高级计算,人人都可以成为数据计算专家。