代码之家 › 专栏 › 技术社区 › Kev

div to 0处理excel

vba excel-tables excel-formula error-handling excel

0

Kev · 技术社区 · 7 年前

所以我有这个问题,我有两个表,一个是员工,另一个是项目。

员工表:

Year Name Type Jan Feb    
2018 Kevin Salary 5000 2000    
2018 Kevin Insurance 200 400    
2018 Alex Salary 3000 4000    
2018 Alex Insurance 300 400

项目表

Year Project_Name Employee_Name Jan_Hours_Worked Feb_Hours_Worked    
2018 Apple Alex 7 5    
2018 Apple Kevin 5 0    
2018 LG Kevin 0 3

现在,我正在创建一个每个月重复出现的所有项目和成本的结果列表,我需要的是为表2中的每个项目找到参与的员工,然后使用该信息从表1中查找成本,并计算每个月项目的小时收费率。

(例如,对于LG项目,我让Kevin在2月份完成这项工作,公司为他支付了2400英镑(工资+保险),然后2月份收取的小时费率将为2400英镑除以他在该项目上花费的总小时数,即3小时)。

对于2月份的苹果项目,这将是亚历克斯的:4400/5,因为凯文在2月份没有在这个项目中工作任何时间。例如,当这两个项目都在1月份为苹果项目工作时,那么应该是Kevin的成本/他工作的小时数+Alex的成本/他当月为该项目工作的小时数。

现在我有了计算这个的公式,除非我在表中有0的值,否则它可以很好地工作。

当它到0时,它给我除法到零的误差

=SUMPRODUCT(SUMIFS(Employees[Feb], Employees[Name],Project[Employee_Name], Employees[Year], 2018 )/Project[Feb_Hours_Worked],--(Project[Project_Name]=K14))

我如何修改此公式,使其适用于上述场景,而不会出现错误并找到每个月的正确结果。

1 回复 | 直到 6 年前

1

J.Doe 7 年前

你并没有让这变得容易,但我想我找到了一些合适的方法:

编辑:

对于DIV 0错误,使用与M9单元中的设置相同的设置:

{=SUMPRODUCT(($J$2:$J$4=$K10)*(M$2:M$4*(IFERROR(SUMIF($B$2:$B$5,$K$2:$K$4,E$2:E$5)/SUMIF($K$2:$K$4,$K$2:$K$4,M$2:M$4),0))))}

使用CTRL+SHIFT+ENTER进行验证

编辑2:

=SUMPRODUCT(M$2:M$9,(IFERROR(SUMIFS(E$2:E$17,$A$2:$A$17,$P5,$B$2:$B$17,$K$2:$K$9)/SUMIFS(M$2:M$9,$I$2:$I$9,$P5,$K$2:$K$9,$K$2:$K$9),0))*($I$2:$I$9=$P5)*($J$2:$J$9=$Q5))

使用CTRL+SHIFT+ENTER进行验证