Найти N-й экземпляр текстовой строки в диапазоне?

Question

У меня есть список имен рядом со списком смен. Я хочу составить список людей в каждую смену по дням, который будет автоматически и динамически обновляться. Я не могу найти лучший способ сделать это.

В основном списке будет столбец с именами сотрудников, за которыми следуют 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-й экземпляр строки в диапазоне ячеек?

Answer 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, ничего не изменится.

Кроме того, после расширения формулы массива вам необходимо выбрать все результирующие ячейки, чтобы изменить эту формулу. В противном случае вы получите "Вы не можете изменить только часть массива".