☰

在 Excel 中创建数组时如何跳过单元格？

tag-icon • tag-icon microsoft-excel
在 Excel 中创建数组时如何跳过单元格？

我希望在创建数组时将单元格排除在数组公式之外，即这些单元格中没有数组。这可能吗？

在此图中，左侧表格是我用来生成中间和右侧报告的数据。在实际电子表格中，数据分布在多个工作表中，并且每月都会添加更多工作表。

目前，中心表是使用数组进行输出的结果，这会导致子项的任务编号所在行的总和重复。我无法（不允许）删除这些子项的任务编号。

右边的表格是我目前拥有的，每个已填充金额框中都有一个公式。但是，由于这个公式实际上由不同数据表的多个 sumif 组成，因此我希望只需更新一个单元格，然后CTRL+ SHIFT+ENTER即可更新整列单元格。这个表格也是我希望数组所在的单元格。

来自实际表格的公式：

=SUMIFS('April 18'!S:S,'April 18'!M:M,Summary!A:A,'April 18'!O:O,"4.2018")+SUMIFS('May 18'!T:T,'May 18'!N:N,Summary!A:A,'May 18'!P:P,"4.2018")+SUMIFS('July 18'!$U:$U,'July 18'!$N:$N,Summary!$A:$A,'July 18'!$Q:$Q,"4.2018")+SUMIFS('June 18'!$U:$U,'June 18'!$N:$N,Summary!$A:$A,'June 18'!$Q:$Q,"4.2018")

床单

答案1

这里有很多小事需要假设，但正如他们所说，魔鬼就在细节中。

我的假设是，子项的任务编号与表格中引导它们的非子项的任务编号相同。例如，单元格 K3 包含非子项，因为它是具有特定任务编号的一个或多个中的第一个。K4、K5 和 K6 包含子项，因为它们的任务编号与 K2 的任务编号相同。

如果是这样，您可以在 L 列的单元格中使用以下公式：

=IF(J2=J1,"",SUMIFS($C:$C,$A:$A,$F$3:$F:$13,$B:$B,"7.2018")

为了使测试成功IF()，如果您需要用 0 代替“”，只需更改该部分即可。问题出现的一个例子是在此列（L 列）中添加值的一些方法。如果您希望单元格为空白，但出于这种担忧而使用 0，只需格式化该列，使第三个格式化参数（用于单元格等于 0 的情况）为空。例如： 0,000.00;-0,000.00;值为 0 的单元格将显示为空白。

其余公式就是图片所示的公式。

如果这不是关于非子项和子项的正确假设，那么一切也还不算完。您肯定有我们看不到的识别子项的方法。并且大概是非子项。只需使用上述相同的方法，但使用您识别项目之间差异的方法。我使用了我的方法，因为它看起来有效，并让解决方案在呈现的内容中完整。但最干净的方法是使用任何您可以识别差异的方法。

我指出，由于子项的缩进，您肯定有一个。由于某些已知的差异，这必定会发生。这不是 Excel 会随在某处找到的值一起带来的东西。如果具有默认格式的单元格获得引用某个格式化单元格的公式，它将对数字执行此操作。但不是字符串。所以一定有某种方法。只需在测试中检查该差异即可IF()。

答案2

假设和澄清。

  1. 您的原始公式未经验证。以下示例使用简化数据和使用 SUMIFS 的基本公式
  2. 假设您使用右缩进来缩进子项。如果您恰好使用空格，请使用右缩进按钮来对齐子项。此外，假设您的所有项在相应的单元格中基本上都是左对齐的。

在此示例中，示例输入表位于单元格 A1:A13，输出表位于 F2:F14。

按ALT+打开 VBA 编辑器F11。插入一个模块（从插入菜单）并将以下代码粘贴到其中。

Function IndLevl(cell As Range)

    Application.Volatile
    IndLevl = cell.IndentLevel

End Function

这将创建一个用户定义函数 (UDF)，该函数接受单元格作为范围并以数字形式返回缩进级别。没有进行任何验证，因此请避免将单元格范围作为输入传递给它，例如 A1:A4。只需将单个单元格作为参数传递即可，例如=IndLevl(A4)

如果使用 Excel 2007 或更高版本，请将文件保存为 .xlsm（启用宏的 Excel 工作表）

现在，在您的输出表中，将 IF 中的公式扭曲，首先检查缩进级别，然后决定是否执行公式或只是让其为空白。

=IF(IndLevl(G3)>0,"",SUMIFS($C:$C,$A:$A,F3,$B:$B,1.2))

将公式拖到所需单元格。参见下面的屏幕截图。您可以将公式包装在 IF 中，然后根据需要将其转换为数组公式。

请注意，该Application.Volatile语句使公式变得不稳定。但如果您稍后更改缩进级别，它将不会自行执行。在这种情况下，只需按 F9 即可重新计算工作表。

请参阅下面的屏幕截图。

在此处输入图片描述

相关内容