☰

压缩包含许多空白的列数据

tag-icon • tag-icon microsoft-excel microsoft-excel-2003
压缩包含许多空白的列数据

我在这里和其他地方看到过这种和类似的方法：

如何将包含缺失数据的列表压缩为不包含缺失数据单元格的新列表？

我有病人的数据，基本上是这样的：

D       E       F           H         I         J
label1  label2  label3      label4    label5    label6
8       15      7           8         15        7
                            4         11        3
                            5         13        1
4       11      3           2          8        0
                            3          5        2



5       13      1

2       8       0                    


3       5       2
  • 第 1 行是数据标签
  • 最多 5 个可能的数字值随机分布在 D、E、F 列中
  • 有很多空白
  • 收集这些最多 5 个数字，并将它们放在 3x5y 网格 H2:H6 中，这样我就可以对它们进行平均并在其他地方对它们进行标准差
  • 无法返回 NUM! 个错误

这里和其他地方已经找到了 2 个解决方案，但它们不起作用。

=IFERROR(INDEX(D:D,SMALL(IF(ISNUMBER(D:D),ROW(D:D)),ROW(D1))),"")

这在 Excel 2016 xlsx 格式中不起作用，但有时似乎在 2003 Excel .xls 中起作用。这是真正奇怪的部分。我想使用 2016 格式，因为 97-2003 在尝试保存时会出现一些错误。公式返回空白或第一行数据标签，或 D2 中的所有相同数字，而不是压缩的最多 5 个数字。

=IF(ROWS(H$2:H2)>COUNTA(D:D),"",INDEX(D:D,SMALL(IF(D$2:D$256<>"",ROW(D$2:D$256)),ROWS(H$2:H2))))

这不起作用，因为它会为完全空白的列或数值少于 5 个的列返回 NUM! 错误值。此工作簿将包含“未来”日期和空白的工作表，但公式已粘贴或模板化，并且从 H2:J6 开始的整个工作簿在 Sheet1 上被平均和标准差。这反过来又被绘制成图表。它不能有非数字错误值。

答案1

我的声誉不到50，所以我无法发表评论。

据我所知，您只需要删除空白行，对吗？似乎一个简单的过滤器就可以完成这项工作。据我所知，这就是我从样本中得到的结果。

然而可能会有空白细胞而不是空白行。在这种情况下，一种更耗时但更容易实现的方法是将数据从 D 列复制粘贴到其他地方，删除空白并删除重复项。对其他两列执行相同操作即可完成。这很容易，因为您总共只有 3 列需要处理。

答案2

=IFERROR(INDEX(D:D,SMALL(IF(ISNUMBER(D:D),ROW(D:D)),ROW(D1))),"")

如果正确粘贴，并且最后的 (ROW(D1)) 保持相对引用，这实际上会起作用。其他引用可以根据需要是相对的或绝对的。许多来源会建议突出显示数组公式的所有目标单元格，然后在第一个单元格中键入公式，然后按 cntl + shift + enter。这是错误的。这实际上会在每个单元格中放置完全相同的公式。最终的 ROW 相对引用必须在每个目标单元格中​​更新才能输出公式。

单击所需输出区域的第一个单元格。输入公式。C+S+E。然后抓住右下角并将公式拖到所有所需的输出单元格中。就我而言，我将公式输入到 H2。C+S+E。将其从 H2:H6 向下拖动。然后将该列组从 H2:H6 水平拖动到 J2:J6。现在运行正常。

相关内容