Я выбрал подход с двумя формулами. Было бы гораздо лучше иметь единую формулу и просто скопировать ее на всю таблицу, но я решил, что усилия не стоили эффекта. Поэтому у меня есть следующая формула для извлечения ведущего наставника. (183)
=INDEX(Table1[Teacher],MATCH(B$11,INDIRECT("Table1["&$A12&"]"),0))
С этой целью я преобразовал вашу первую таблицу в таблицу Excel (Table1), охватывающую диапазон A1:D7. Это точная копия вашей таблицы. Таким образом, заголовками являются Учитель, Обучение1, Обучение2 и Обучение3. Поэтому Table1[Teacher] эквивалентно A2:A7. Вы можете заменить один синтаксис на другой, но использование таблицы даст вам динамический диапазон, который вам придется создать в противном случае.
У меня есть ваша выходная таблица в диапазоне A11: E14. Поэтому у меня есть Training1, Training2 и Training3 в A12:A14. Таким образом, INDIRECT("Table1["&$A12&"]" относится к столбцам таблицы с этими именами, поскольку формула копируется вниз. Этот компонент занимает видное место в следующей формуле.
Приведенная выше формула находится в ячейке B12, а затем копируется в ячейку B13:14. Обратите внимание на его ссылку на B11, откуда он получает ведущего наставника. Измените его ссылку на A12, чтобы поймать строку, в которой у вас есть Training1, которая должна соответствовать заголовку столбца в таблице.
Вот формула для ячейки C12. Вы можете использовать его как в Office 365, но в более ранних версиях Excel это будет формула массива. Формулы массива требуют подтверждения CTL+SHIFT+ENTER. После настройки скопируйте его в C14:E14.
=INDEX(Table1[Teacher],SMALL(IF(INDIRECT("Table1["&$A12&"]")="Tutor",ROW(INDIRECT("Table1["&$A12&"]"))-ROW(INDEX(INDIRECT("Table1["&$A12&"]"),1))+1),COLUMN()-2))
В этой формуле есть неточность, которую я решил оставить вам для исправления. Как видите, он содержит слово Tutor (в отличие от Lead Tutor) для поиска в таблице. В моей тестовой таблице у меня есть слово в C11:E11 (скопировано из вашего дизайна таблицы), и я должен был взять его оттуда, как я сделал в первой формуле, которая относится к B11. Как бы то ни было, это поможет вам понять формулу, но вы можете заменить слово Tutor на C$11, если вы так настроили свою таблицу.
Имеется ссылка на столбец «Учитель» в таблице и 3 ссылки на A12, уже описанные выше. Обращаю ваше внимание на COLUMN()-2). Когда вы введете эту формулу в столбец C, она вернет 1, а скопированная вправо будет считать 2 и 3. Так определяется ранг для функции НАИМЕНЬШИЙ. Настройте его в зависимости от столбца, в который вы вставляете формулу.
Редактировать
Предполагая, что вы не хотите преобразовывать первую таблицу в таблицу, вы можете ссылаться на диапазоны по именам, которые вы назначаете (и делаете динамическими в этом процессе). Создайте 4 именованных диапазона следующим образом:
A2:A7 as "Teachers"
B2:B7 as "Training1"
C2:C7 as "Training2"
D2:D7 as "Training3"
Обратите внимание, что 3 из вновь названных диапазонов имеют имена в A12:A14. Теперь формулы для B12 и C12 будут выглядеть следующим образом.
[B12] =INDEX(Teachers,MATCH(B$11,INDIRECT($A12),0))
[C12] = INDEX(Teachers,SMALL(IF(INDEX(INDIRECT($A12),,1)="Tutor",ROW(INDEX(INDIRECT($A12),,1))-ROW(INDEX(INDIRECT($A12),1))+1),COLUMN()-2))
person
Variatus
schedule
28.02.2021
