excel - 如何使用数组公式对 excel 3d 数组中的列、行、列求和

Question

我正在 Excel 中构建一个日程安排工具。这包括具有开始和结束日期的任务列表，以及可以分配给每个任务的人员。

为了能够将此数据输出到我们的预算系统中，我需要能够计算在给定月份内预订的员工时间的百分比(所有任务的总和)。

我可以使用以下公式计算一名工作人员在给定月份内用于单个给定任务的可用时间:

=SUM(INDEX(WorkdayArrayMonCol,MATCH(B2,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(C2,WorkdayArrayDateCol))*TRANSPOSE('Staff Profile'!$B$13:$B$17))

这对于单个任务非常有效，但我需要一个适用于一系列任务的等效项。

我目前的尝试是这样的:

=IFERROR(MMULT(MMULT(TRANSPOSE(ROW(INDEX(WorkdayArrayMonCol,MATCH(B2:B12,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(C2:C12,WorkdayArrayDateCol)))^0),INDEX(WorkdayArrayMonCol,MATCH(B2:B12,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(C2:C12,WorkdayArrayDateCol))),'Staff Profile'!$B$13:$B$17),0)

+ctrl+shift+回车

公式使用Index/Match查找B2:B12(任务开始日期)和C2:C12(任务结束日期):

如果任务超出当前月份的范围，则使用 0 (0/01/1900)。这些搜索表 WorkdayArray

有 6 列，日期、星期一、星期二....到星期五，然后返回日期范围的二维数组，其中包含要对每个工作日中有多少个在该范围内的值求和。这是准确捕捉公共(public)假期所必需的。

然后将此二维数组乘以工作人员的工作可用性，位于 'Staff Profile'!$B$13:$B$17，5 个值中的每一个均为 0>=>1代表周一至周五。例如，如果他们在星期二工作半天，而在一周的其余时间全天工作，则 5 个单元格将类似于 {1,0.5,1,1,1}。

使用此处发布的技术:How to use an array formula to return an array of sums in Excel我已经设法将其压缩为一个向量，但我无法将其加总到最终总数。

使用上面的公式，输出为{8.5;4;2;2;0;0;0;0;0;0;1}，这是每个任务的可用工作日，但是我尝试使用链接的 MMULT 技术对结果数据求和，SUMPRODUCT、SUM 或 SUMIF 保持结果在返回的相同数据中:{8.5;4;2;2;0;0;0;0;0;0;1}

由于每个财务月 (y) 将重复此计算，因此在每个员工 (x) 中，通过使用 (z) 张作为 x、y 和 z 中的每一个来进行 3D excel 表示是不可行的大得不切实际。我宁愿不使用 VBA，而且我已经足够接近它似乎应该可以使用公式。

非常感谢!

编辑:Zac 要求的单页版本(相同的公式，调整后的引用):

单张版:

.

我选择了 11 个单元格来说明数组结果(我想要的是输出这些值的总和 17.5 的公式)。

Answer 1

我使用了不同的方法来获得所需的结果，即这个数组公式:

=SUM(COUNTIFS(B2:B12,"<="&Table1[Date],C2:C12,">="&Table1[Date])*MMULT(Table1[[Monday]:[Friday]],P3:P7))

用CTRL+SHIFT+ENTER确认

COUNTIFS部分为您提供了一个垂直向量，表示每个日期在 B 列和 C 列日期范围内出现的次数，然后是 MMULT。部分为您提供另一个垂直向量，每个日期的值基于 P3:P7值(value)观

然后可以将这两个向量相乘并相加得到最终结果 - 17.5 显示在 P12 中