☰

尋找某個範圍內文字字串的第 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 博客，然後搜尋 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 鍵時，將不會有任何效果。

此外，擴展數組公式後，您需要選擇所有結果單元格才能變更該公式。否則你會得到“你不能只改變數組的一部分”。

相關內容