excel - 多个公式需要简化

Question

我有一个包含多个页面的电子表格文件。一个称为输入，即 60 组信息。60 个组中的每一个的列都相同，但值不同。从这里我想有多个公式来总结数据。

例如：

我有一个摘要页面，我希望单元格 D5 将输入页面中的值与每行标题为每周的列相加。我的输入页面有 1050 个数据。使用格式和标题有 50 行，留下 1000 行数据。所以这 1000 行需要在 60 个组中总计。

我愿意接受有关使其更简单的建议。

我的数据的一个例子是这样的

B10 = Date1
C10 = Product
D10 = W1
E10 = W2
F10 = W3
G10 = RB
I10 = W1
J10 = W2
K10 = W3

L10 = RB

这持续了 60 组和 1000 天。因此，摘要页面显示每个日期的 W1、W2、W3 和 RB 的总数。我不想要一个添加我想要自动计算的每一列的公式。我的 60 个组实际上会变成每个组的 4 个值，然后根据日期变成 1000 次。我的 VLOOKUP 一直返回错误，所以我不知道我哪里出错了。任何指导将不胜感激

标签： excelexcel-formulagoogle-sheets-formulavlookup

Answer 1

您在 B10:B27 中没有日期 - 这就是为什么您的 vlookup 不起作用所以第一步是重新创建它：

=ARRAYFORMULA(VLOOKUP(ROW(Inputs!A9:A), 
 IF(Inputs!B9:B<>"", {ROW(Inputs!A9:A), Inputs!B9:B}), 2, 1))

那么我们需要列 D 和范围 G:J 作为虚拟数组，所以：

=ARRAYFORMULA({VLOOKUP(ROW(Inputs!A9:A), 
 IF(Inputs!B9:B<>"", {ROW(Inputs!A9:A), Inputs!B9:B}), 2, 1), Inputs!D9:D, Inputs!G9:J})

接下来，我们以相反的顺序对其进行排序：

=ARRAYFORMULA(SORT({VLOOKUP(ROW(Inputs!A9:A), 
 IF(Inputs!B9:B<>"", {ROW(Inputs!A9:A), Inputs!B9:B}), 2, 1), Inputs!D9:D, Inputs!G9:J}, ROW(Inputs!A9:A), 0))

现在我们可以在您的摘要表上查找：

=ARRAYFORMULA(IFNA(VLOOKUP(B5:B, SORT({VLOOKUP(ROW(Inputs!A9:A), 
 IF(Inputs!B9:B<>"", {ROW(Inputs!A9:A), Inputs!B9:B}), 2, 1), 
 Inputs!D9:D, Inputs!G9:J}, ROW(Inputs!A9:A), 0), {3,4,5,6}, 0)))

从那里我们只需将 Inputs!G9:J 范围绑定到 Inputs 表中第 3 行的数字，并使范围选择动态：

=ARRAYFORMULA(IFNA(VLOOKUP($B$5:$B, SORT({VLOOKUP(ROW(Inputs!$A$9:$A), 
 IF(Inputs!$B$9:$B<>"", {ROW(Inputs!$A$9:$A), Inputs!$B$9:$B}), 2, 1), 
 Inputs!$D$9:$D, INDIRECT("Inputs!"&
 ADDRESS(9, H3*7-2*(H3-1), 4)&":"&SUBSTITUTE(
 ADDRESS(1, H3*10-5*(H3-1), 4), 1, ))}, ROW(Inputs!$A$9:$A), 0), {3,4,5,6}, 0)))

现在您只需复制单元格 H5 并粘贴到 M5、R5 等

演示电子表格