☰

如何使用单元格引用中的列表创建与 CHOOSE 等效的功能

tag-icon • tag-icon microsoft-excel worksheet-function libreoffice-calc
如何使用单元格引用中的列表创建与 CHOOSE 等效的功能

可以使用该函数从逗号分隔的列表中选择特定值CHOOSE。例如：

=CHOOSE(A1,"A","B","C","D")

其中 A1 包含索引值，"A","B","C","D"是列表。

但是，CHOOSE只对函数中嵌入的实际列表有效，而不是对列表的引用。

假设您遇到一种情况，即列表是动态的，并在单元格中创建和存储。假设现在单元格 B1 包含逗号分隔的字符串"A","B","C","D"，而在另一个时间它可能是一个不同的列表。

要求仍然是根据A1中的索引值从该列表中选择，因此相当于伪代码：

=CHOOSE(A1,CONTENTS(B1))

有没有办法可以达到同样的效果？

  • 我正在寻找一种通用方法。列表可以是任何内容和任意数量的项，这排除了与特定数量的项目或特定类型或格式的项目相关的解决方案。它需要处理符合 CSV 标准的任何列表的一般情况。但是，列表不会大到超出任何 Excel 限制。
  • 请注意，这与 INDIRECT 函数的操作不同，INDIRECT 函数不适用于此目的。
  • 解决方案需要像函数一样运行（自动保持与内容变化同步）。这样就排除了需要人工干预的解决方案。如果自动触发的 VBA 解决方案是唯一可行的解​​决方案，则不会排除这种解决方案，但这是不可取的，因为 VBA 并非始终可用，而且会限制将解决方案转移到其他电子表格应用程序的能力。
  • 并不排除使用辅助列，但是数据的可变性质将使涉及将列表解析为单独单元格的解决方案变得不切实际。

答案1

我想建议一个相对简单的方法。

在此处输入图片描述

A 列包含要选择的值的字符串。B 列包含选择的索引值。C 列显示选定的值。

公式

  • 将发生值放入 B 列。

  • 在单元格中写入此公式C2如果值以空格分隔：

    =TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))),(B2-1)*LEN(A$2)+1,LEN(A$2)))

  • 您可以在单元格中使用此公式C9以逗号分隔的值。

    =TRIM(MID(SUBSTITUTE(A$9,",",REPT(" ",LEN(A$9)),(B9-1)*LEN(A$9)+1,LEN(A$9)))

怎么运行的

此方法将每个分隔符替换为等于整个数据字符串长度的空格。然后，它计算从每个值开始或之前的断点，并截取与整个原始字符串一样长的片段。片段将始终包含目标值加上一些填充，而不包含任何其他值的部分。填充被修剪，剩下的就是目标值。

让我来分解一下这个公式：

当位于辅助列单元格中时B2有3，我们正在寻找第三个值，Data3。

LEN(A2) returns:  23

SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2)))  Returns: 

Data1                       Data2                       Data3                       Data4

然后：

(B2-1)*LEN(A2)+1 returns:  47

这是 Data2 和 Data3 之间的位置，23 个字符之后是 Data3 之后的位置。MID 函数获取该代码段，其中包含填充、Data3 和更多填充。

TRIM removes the padding, leaving `Data3`.

笔记，如果 Helper Col 值为2然后公式

=(B2-1)*LEN(A2)+1

返回24代替四十七

和公式

=TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))),24,23))

返回Data2。

注意：根据需要调整公式中的单元格引用。

答案2

这是一个老问题，也是 XY 问题的一个例子。我太专注于方法而没有意识到还有其他方法可以得到结果。在重新构思问题后，我意识到这可以通过一些标准公式来实现。

任务实际上是根据逗号分隔符提取第 N 段文本。您只需找到相关的逗号并使用 MID 函数即可。

假设单元格 A1 包含索引 ( N)，单元格 B1 包含逗号分隔列表。查找字符（在本例中为逗号）第 N 次出现的标准方法是：

=FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1))

CHAR(1) 只是一个字符，不会成为任何单元格内容的一部分。SUBSTITUTE 函数有一个可选的第四个参数，用于定义要替换的特定出现。此公式将逗号的第 N 次出现（单元格 A1）替换为 CHAR(1)，然后找到它。

第一个和最后一个列表值仅在一侧有逗号，因此需要扩展此公式才能使用 MID 函数。要查找第 N 个列表项的起始位置，我们可以使用：

=IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1)

请注意，项目 2 的起点是参考第一个逗号，因此要定位的相关逗号是 N-1。该项目的第一个字符位于逗号后 +1 位置。

要找到最后一个列表项的结束位置，我们可以使用 LEN 函数，我们只需要确定它是最后一个项。列表项的数量将比分隔符的数量多一个。我们可以使用以下命令获取逗号的数量：

=LEN(B1)-LEN(SUBSTITUTE(B1,",",""))

这会将所有逗号替换为零长度的空值，然后找出字符串长度的差异。差异就是逗号的数量。

指定起点后，MID 使用要提取的文本的长度。这是通过从列表项末尾的位置减去起点得出的，该位置将是下一个逗号的位置，或者对于最后一个项目，是下一个逗号所在的位置，字符串长度后一个字符。因此，测量终点将是：

=IF(A1>(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))),LEN(B1)+1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1)))

长度参数是通过从上面的测量点减去项目起始位置来找到的。将其与 MID 函数结合起来可得出：

=MID(B1,IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1),IF(A1>(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))),LEN(B1)+1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1)))-IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1))

在此处输入图片描述

此公式仅提取项目。如果像本例中一样，项目是引号中的文本字符串，而您不想要引号，则需要删除引号。由于任何清理都取决于实际数据和实际要求，因此我不会进一步使公式复杂化。

此外，与任何函数一样，参数值也需要合法。如果使用超出范围的索引值，则会产生错误。因此，可以使用 IFERROR 包装公式，以定义针对特定用例要执行的操作。

请注意，这种方法的局限性是列表值不能包含任何嵌入的逗号。

答案3

当谈到字符串操作时，我总是喜欢使用正则表达式。

我第一次发现Excel RegEx 查找/替换插件，所以使用它，但你可能也可以找到其他免费工具。

最简单的方法是使用这个公式：
=RegExReplace($A$1,"([^,]*,){"&C1-1&"}([^,]*)(,.*)?","$2")

在此处输入图片描述

怎么运行的：

  • =RegExReplace($A$1,"([^,]*,){"&C1-1&"}([^,]*)(,.*)?","$2")
  • ([^,]*,){"&C1-1&"}
    • ([^,]*,)- 匹配不包含逗号 ( ) 的字符序列，,后面跟着一个逗号
      • （也记得它是第 1 组）
    • {"&C1-1&"}- 前一组C1减一
    • ([^,]*)- 匹配不包含逗号的字符序列，并将其记为组 #2
    • (,.*)?- 匹配逗号后跟任意字符序列。（?：此部分是可选的，如果需要最后一项，则可能不存在）。
    • "$2"- 用存储为 #2 的组替换原始字符串

阅读更多解释这里。

如果输入是有效索引，则返回正确的项目，对于无效索引（0 或高于项目数），则整个输入字符串将是结果。

此公式适用于您的示例，但并不适用于您问题中的所有“CSV 一致列表”。 （据我所知，您的问题的其他答案均不正确，但是，如果您需要的话，可以更轻松地改进此解决方案）：

  • 正如我原始答案中的公式一样，它不会删除"条目周围的引号（），但使用原始内容周围的另一个函数很容易解决：
    =RegExReplace(...,"""(.*)""","$1")
  • 更困难的部分是管理项目内的分隔符（"first","second,","third"）和转义分隔符（"quote "" within item"）

相关内容