☰

查找某个范围内文本字符串的第 N 个实例？

tag-icon • tag-icon openoffice openoffice-calc
查找某个范围内文本字符串的第 N 个实例？

我有一份名单，旁边是一份班次名单。我想按天整理一份每班次的人员名单，该名单将自动动态更新。我不知道最好的做法是什么。

在主名册中，有一列员工姓名，后面跟着 14 列班次。然后，我想根据第一张表中的班次创建 14 个姓名列表。

例如：主要范围有：

        A      B   C   D   E
   ---------- --- --- --- ---
1  Joe Blogs   E   E   L   O
2  Jill Bleg   L   L   E   E
3  Geoff Ted   O   L   L   L

例如，我想查找 D 列，并搜索 L 的第一个实例以列出 Joe Blogs，然后搜索 L 的第二个实例以列出 Geoff Ted，依此类推。

有没有什么方法可以在单元格范围内搜索字符串的第 N 个实例？

答案1

这并不像看起来那么简单。首先，要找到 D 列中所有出现“L”的行号，您可以使用：

=IF(D1:D3="L"; ROW(D1:D3))

另存为数组公式：输入后，按 Ctrl+Shift+Return（或 Mac 上的 Command+Shift+Return）。然后展示在花括号中。并且它的结果不会是单个单元格，而是与您正在处理的范围一样多的单元格。在上面的例子中，您将得到 3 个单元格，其值为 1、空白和 3。

下一个，使用SMALL找到第 N 个值：

SMALL(numberlist; n)

返回（无序）数字范围或数字数组中第小的n数字numberlist。

应用SMALL上述内容，您将回到单个单元格的结果：

=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)

尽管结果是一个单元格，但仍需要使用 Ctrl+Shift+Return 进行保存。

现在，知道了行号，INDEX就可以在第一列找到名称A1:A3：

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)

再次，所有这些都需要使用 Ctrl+Shift+Return 来保存。

但是，这样的公式不能拖拽到其他单元格中，因为排名“1”和“2”不会自动变成“3”，以此类推。而是根据公式所在的行来计算所需的排名：

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)

将其作为数组公式保存在第 1 行的某处后，可以将其向下拖动以添加到第 2 行、第 3 行等等。

或者，您可以复制/粘贴数组公式来动态调整列和行引用，而不是扩展结果。在下面的屏幕截图中，我将以下内容从 B7、B12 和 B17 复制到其他单元格中：

=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)

请注意，数组公式的键盘快捷键只有在实际更改公式后才会起作用；当您只是按“Return”键，然后再次进入公式并按“Ctrl+Shift+Return”键将不起作用。

此外，一旦扩展了数组公式，您需要选择所有结果单元格才能更改该公式。否则您将得到“你不能只改变数组的一部分”。

相关内容