![压缩包含许多空白的列数据](https://linux22.com/image/1570334/%E5%8E%8B%E7%BC%A9%E5%8C%85%E5%90%AB%E8%AE%B8%E5%A4%9A%E7%A9%BA%E7%99%BD%E7%9A%84%E5%88%97%E6%95%B0%E6%8D%AE.png)
我在这里和其他地方看到过这种和类似的方法:
如何将包含缺失数据的列表压缩为不包含缺失数据单元格的新列表?
我有病人的数据,基本上是这样的:
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。现在运行正常。