☰

Найти 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, чтобы получить список блогов Джо, затем выполнить поиск второго вхождения L, чтобы получить список блогов Джеффа Теда, и так далее.

Есть ли способ найти N-ный экземпляр строки в диапазоне ячеек?

решение1

Это не так просто, как кажется. Во-первых, чтобы найти номера строк всех вхождений "L" в столбце D, вы должны использовать:

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

Сохранить это какформула массива: после ввода нажмите Ctrl+Shift+Return (или Command+Shift+Return на Mac). Затем онпоказыватьв фигурных скобках. И в результате у него будет не одна ячейка, а столько ячеек, сколько диапазон, над которым вы работаете. В примере выше вы получите 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, это не даст никакого эффекта.

Также, как только формула массива будет расширена, вам нужно будет выбрать все ячейки результата, чтобы изменить эту формулу. В противном случае вы получите«Вы не можете изменить только часть массива».

Связанный контент