Excel - 来自分隔单元格的动态列表/从动态行中删除空白

Question

计划

• Excel 2013

目标

• 使用数据验证的“列表”选项，从逗号分隔列表创建单元格内下拉列表
• 使用数据验证的“列表”选项，从没有任何空格的干净值行、动态生成的不可避免地受到空格污染的行创建单元格内下拉列表

限制

• 无 VBA、宏或其他胡言乱语 - 仅公式
• 数据保存在行中，而不是列中。将数据转换为列会有些困难，我宁愿避免这样做

详细信息

我最初的计划是创建一个包含逗号分隔列表的单元格，然后将其用于创建数据验证列表。成功创建格式良好的逗号分隔列表后，似乎您不能简单地引用该列表并由数据验证工具对其进行翻译，现在我需要找到一种替代方法。

数据保存在一长行(不是列，并且不能轻松转换为列)中，该行是通过对上面的各个值求和而生成的。这意味着两个值之间可能有很多空格 - 这是不可避免的。

以下网站有一个数组公式，可用于从列中删除空白单元格，但是我没能将其转换为适用于行的公式: http://www.cpearson.com/excel/NoBlanks.aspx

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

如果有人对使其连续工作有任何想法，我将不胜感激。它似乎需要的不仅仅是用 COLUMN() 替换 ROW() 函数 - 可能与 INDIRECT() 上的“1:”有关，尽管我不明白该公式是如何工作的。

与此同时，我会继续使用它，当然，如果有人对行数据或逗号分隔数据问题有任何其他非 VBA 解决方案，我会洗耳恭听。

编辑:为了澄清，上面的链接提供了一个解决方案，用于将一组垂直数据放入水平结果 channel ，但这对我的水平数据集没有帮助。

Answer 1

这是你想要的吗？

=IFERROR(INDEX($A$1:$E$1,1,SMALL(IF(LEN($A$1:$E$1)<>0,COLUMN($A$1:$E$1),9.9E+100),COLUMN())),"")

我只是假设数据如下所示:
Row1 中的数据并在 Row2 中应用公式。

Row2 中的结果将是 Row1 中不带空格的数据。

编辑1:

我看到您需要它来数据验证列表>
所以我认为这就是您可能需要的:

使用这个公式:

=IFERROR(TRANSPOSE(INDEX($A$1:$E$1,1,SMALL(IF(LEN($A$1:$E$1)<>0,COLUMN($A$1:$E$1),9.9E+100),ROW(A1)))),"")

希望这有帮助。
我只是猜测您想要什么，但这应该能让您了解如何调整数组公式。