因此，我有一个 Excel，其数据在 Sheet1!B1:Z2000 范围内，并且它在随机位置包含该范围内的大约 1500 个 ID。
在这种情况下，ID 是文本数据（数据示例“334_2_975”），长度可以变化（另一个示例“433_13_1390”），但除了数字和下划线之外没有特殊字符。
我想将数组中的 ID 列表放在 Sheet3 中的一列中，每次计算工作表时，该列表也会跟上原始数据的变化。因此，每次更新数据时都不需要手动复制和粘贴值。我设法创建了可以连接数据的公式，但它需要手动复制和粘贴才能摆脱解决方案创建的数组中的额外“0”值。同样重要的是将 ID 列表放入另一个工作表中，而不是与数据放在同一个工作表中。
那么有什么解决方案吗？我更喜欢只使用公式的解决方案，但如果您有一个使用 VBA 的解决方案，我也很高兴听到这个消息并尝试一下。下面是我对有缺陷的解决方案的解释以及示例 excel 的链接。
我的错误解决方案：
创建 Sheet2 将“=Sheet1!A1:Z20000”添加到单元格 A1//以数组形式添加所有 ID，但空白处为 0
创建 Sheet3 从 Sheet1 复制所有内容，仅将值粘贴到 Sheet3 从 Sheet3 点击替换并将所有“0”替换为“实际空白”
Sheet3 中单元格 AA1 中的此公式 =SUM(COUNTIF(A1:Z2000,{"?*"})) //帮助 AB1 中的另一个公式
Sheet3 中单元格 AB1 中的此公式 =IF(ROWS($1:1)>$AA$1,"",INDIRECT(TEXT(SMALL(IF($A$1:$Z$2000<>"",10^5*ROW($A$1:$Z$2000)+COLUMN($A$1:$Z$2000)),ROWS($1:1)),"R0C00000"),0))
我将此公式拖到行号“单元格 AA1 中的值”或直到我开始获得空值。这种方式甚至可以连接值，但每次想要更新列表时，我都需要重复复制粘贴和替换值操作。
链接到包含我的解决方案和示例数据的示例 Excel： https://hydrohexcom-my.sharepoint.com/:x:/g/personal/heikki_ruohola_hydrohex_com/EZCjS757vttFhpEWwauj3o4Ba3TZlFf91fL0FKeaxj9FWw?e=jKn6l7
答案1
将二维表展开为带有空白的一维表，从 Sheet2 的 A2 开始：
=INDEX(Sheet1!$A$1:$Z$20000,QUOTIENT(ROW()-2,COLUMN($Z$20000))+1,MOD(ROW()-2,COLUMN($Z$20000))+1)
现在将 A1 的值设为“ID”，并添加过滤器。单击过滤器向下箭头，删除所有 0。
如果您的数据也包含 0，您可能必须先将 sheet1 “克隆”到 sheet2，用可以过滤的唯一字符交换空格，例如“％”：
=IF(ISBLANK(sheet1!A1),"%",sheet1!A1)