我有三张纸,sheet1,sheet2和sheet3 .
工作表1具有以下列:项目,员工姓名,工作小时数,日期工作表2具有列类似:员工姓名,费率/小时
所以两张纸之间的共同点是员工姓名 .
工作表1将每天更新,因此工作表将没有定义范围 .
花费的$ $将在= Sheet1上计算!工作小时数* Sheet2费率/小时(这两张表中常见的是员工姓名)
我完成了每个项目的总花费,但我需要知道如何计算过去30天的花费 .
您可以使用IF从总和中删除旧值 . 就像是:
=IF(NOW()-Sheet1!Date <= 30, Sheet1!HoursWorked*Sheet2!Rate,0)
在工作表1中创建一个名为“Cost”的新列 . 将以下公式放入E2中并复制下来 .
= IF(D2-TODAY()> 30,0,C2 * INDEX(SHEET2RATECOLUMN,MATCH(B2,SHEET2NAMECOLUMN,0)))
使用工作表1作为输入在工作表3上创建新的数据透视表 . 将员工放在行上,并在表的数据部分中汇总“成本” .
假设表1列D是日期,列B是员工,列C是小时 .
表1
Project Employee Hours Date AA Ken 2.0 14-Feb BB Ken 1.0 01-Jan AA Rob 1.5 15-Feb BB Ken 7.0 01-Mar
表2
Employee Rate Ken 50.00 Rob 20.00
表3
Project Ken Rob Total AA <B2> BB Total
在B2中输入以下公式= SUMIFS(Sheet1!$ C:$ C,Sheet1!$ B:$ B,B $ 1,Sheet1!$ A:$ A,$ A2,Sheet1!$ D:$ D,“>”与TODAY() - 30)* INDEX(Sheet2的$ B:$ B,MATCH(B $ 1,$ Sheet2的一个:$ A,0!))
将公式复制到所有项目行和员工列 . 将Sum方程放入“总”列和行中 .
3 回答
您可以使用IF从总和中删除旧值 . 就像是:
在工作表1中创建一个名为“Cost”的新列 . 将以下公式放入E2中并复制下来 .
= IF(D2-TODAY()> 30,0,C2 * INDEX(SHEET2RATECOLUMN,MATCH(B2,SHEET2NAMECOLUMN,0)))
使用工作表1作为输入在工作表3上创建新的数据透视表 . 将员工放在行上,并在表的数据部分中汇总“成本” .
假设表1列D是日期,列B是员工,列C是小时 .
表1
表2
表3
在B2中输入以下公式= SUMIFS(Sheet1!$ C:$ C,Sheet1!$ B:$ B,B $ 1,Sheet1!$ A:$ A,$ A2,Sheet1!$ D:$ D,“>”与TODAY() - 30)* INDEX(Sheet2的$ B:$ B,MATCH(B $ 1,$ Sheet2的一个:$ A,0!))
将公式复制到所有项目行和员工列 . 将Sum方程放入“总”列和行中 .