我有一个包含2张工作表的Excel电子表格,sheet1是从共享时间表系统中导出的数据,sheet2将用于报告目的,主要是按周显示时间细分。
我需要一个求和小时值(sheet1!G:G
)的公式,其中sheet1(sheet1!AI:AI
,使用=D2-WEEKDAY(D2-1)
计算)中的星期开始日期列与sheet2(第3行)中的星期开始行相匹配。
到目前为止,我已经尝试过SUMIF,SUMPRODUCT和现在的SUM(IF)公式,并且都具有相同的问题,它们要么找不到SUM的值(肯定存在的值),要么只会对数组中的第一个值求和。例如,对于从“ 12/06/2016”开始的一周,我应该总共有336小时,但目前它仅找到列表中的第一个值14。到目前为止,我尝试使用的其他公式如下:{=SUM(IF(sheet1!$AI$2:$AI$300=CB$3,sheet1!$G$2:$G$300,0))}
=SUMIF(sheet1!AI$2:AJ$300, CB$3, sheet1!G$2:G$300)
=SUMPRODUCT( -- (sheet1!$AI$2:$AI$300 = CB$3), sheet1!$G$2:$G$300)
所有公式都有相同的问题,并且在工作表2中的每一列产生相同的值,这使我怀疑这与我的周初计算/数组有关,但我无法弄清楚是什么原因。当前低于此值和低于该值的期望值的示例:
05/06/2016 12/06/2016 19/06/2016 26/06/2016
0.00 14.00 0.00 11.00
05/06/2016 12/06/2016 19/06/2016 26/06/2016
82.85 336.00 297.75 307.25
包含数据和公式的示例文件-https://www.dropbox.com/s/5c2c5tyl993vxyt/SUMIF%20Example.xlsx?dl=0
最佳答案
第一页上的“日期”列同时包含日期和时间部分。
一种解决方法:将您的每周开始公式更改为:
=INT(A2-WEEKDAY(A2-1))
Excel将日期存储为1900年1月1日= 1的整数。时间存储为一天的一部分。因此,例如,您在Sheet1!A2中的第一个条目实际上是:
2016/6/6上午10:27:37
存储为
42527.4358449074
通过删除小数部分,您可以将其与sheet2上的每周起始数据进行匹配并获得所需的结果。
您的
SUMIF
和SUMPRODUCT
函数现在也将正常工作。关于arrays - SUM(IF)仅在数组中找到第一个值,有时,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38565691/