Данные строки для определенных заголовков столбцов на основе идентификатора строки

Question

У меня есть лист с несколькими сотнями строк данных о сотрудниках. На самом базовом уровне он перечисляет EmployeeID "S, Name и перечисляет навыки, которые они имеют.

То, что я сделал, находится на другом листе (в моем Employee Lookup Рабочая тетрадь) Я использовал комбинацию функций индекса и соответствия для поиска EmployeeID что я вхожу и возвращаю уровень квалификации из паспорта навыков сотрудника.

То, что я хочу сделать, это попытаться упростить способ получения данных (у меня ограниченные знания VBA, но некоторые знания формулы Excel, следовательно, соответствие индекса вложенности). Employee Skills лист имеет Column A с EmployeeID с и Row A с названиями навыков (и предыдущими заголовками).

Я могу настроить Index / Match для каждой строки, но тогда мне нужны номера столбцов для каждого навыка и посмотреть, что EmployeeID и вернуть навыки (даже если они не заполнены).

Я бы на самом деле не пытался понять, как начать кодировать это, но я думаю, что был бы более простой способ сделать это, чем сделать Index / Match формула для каждого ссылочного навыка (при наличии от 4 до 15 навыков на отдел, когда я помогаю с 4 отделами).

Index / Match Функции заняли бы много времени и уже есть для одной группы. Я не думаю, что то, что я хочу, может быть сделано в Excel, но, к сожалению, это ресурс данных, с которым я остался.

Пример макета электронной таблицы:

Answer 1

Короткий ответ:

^{(Подробный скриншот с цветовой кодировкой, разбивающий формулу ниже, или полноэкранный} .PNG ^здесь.)

Вот решение с использованием примеров данных, аналогичных вашим:

"Короткая версия" формулы в D16 является:

=INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0))

Фактическая формула, которую я использовал, выглядит пугающей, но это просто вышеупомянутая формула дважды (копировать / вставить) вместе с IF так что это приведет к пустой ячейке, если "исходная ячейка" пуста (как партитура Джейн)...

=IF(INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0))=0,"",INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0)))

Как только эта формула в D16 Я просто "заполнил" (или скопировал / вставил) другие навыки.

• Обратите внимание, что все ссылки на ячейки в приведенной выше формуле являются " абсолютными ", за исключением C16,

Мы хотим, чтобы все диапазоны ячеек оставались неизменными, даже если мы копируем / вставляем (или заполняем) ячейку в другую ячейку... за исключением C16: это то, на чем основан наш поиск, так что однажды нужно будет измениться, когда мы копируем ячейку в другом месте.

---

Пример "двумерный индекс / совпадение":

https://image.ibb.co/dyxxtm/SO_screenshot_2_D_Index_Match.png

Скачать .xslx образец файла:

Чтобы получить лучшее представление о том, как это работает, вы можете скачать файл Excel со скриншота здесь, чтобы поэкспериментировать, изменить и адаптировать его по своему усмотрению.

^{Это прямая ссылка для скачивания на Macro-free} .XSLX ^{, размещенный на Jumpshare, который позволяет просматривать онлайн, но не любит сложных формул, а столбцовое форматирование портится в некоторых браузерах (однако вы также можете скачать его из онлайн-просмотра с помощью кнопки СКАЧАТЬ в правом верхнем углу ↗ ↗ зрителя.}

---

INDEX & MATCH Документация по функциям и другие примеры:

• Microsoft (Office.com): функции MATCH и INDEX (с видео)

• Microsoft: переключение между относительными, абсолютными и смешанными ссылками

• ExcelJet: двусторонний поиск с помощью INDEX и MATCH

• MSDN: выполнение двумерных поисков

• mbaExcel: руководство: как решить, какую формулу поиска Excel использовать

• Переполнение стека: индекс / совпадение с VBA

• ExcelTip: как использовать индекс / совпадение для 2 критериев с VBA

---

Ускоренный курс по абсолютным и относительным ссылкам на ячейки

Ссылка на ячейку - ссылка на ячейку относится к ячейке или диапазону ячеек на листе и может использоваться в формуле, чтобы Microsoft Office Excel мог найти значения или данные, которые вы хотите вычислить по этой формуле. _{( Источник)}

По умолчанию ссылка на ячейку является относительной. Например, когда вы ссылаетесь на ячейку A2 из клетки C2, вы на самом деле имеете в виду ячейку, которая находится в двух столбцах слева (C минус A) и в том же ряду (2). Формула, которая содержит относительную ссылку на ячейку, изменяется при копировании из одной ячейки в другую.

Например, если вы копируете формулу =A2+B2 из клетки C2 в D2 формула в D2 регулируется вправо на один столбец и становится =B2+C2, Если вы хотите сохранить исходную ссылку на ячейку в этом примере при копировании, вы сделаете ссылку на ячейку абсолютной, предшествуя столбцам (A а также B) и строка (2) со знаком доллара ($). Затем, когда вы копируете формулу =$A$2+$B$2 от C2 в D2 формула остается неизменной.

В менее частых случаях вы можете захотеть сделать ссылку на ячейку " смешанной ", поставив перед столбцом или значением строки знак доллара, чтобы "заблокировать" столбец или строку (например, $A2 или же B$3),

_{( Источник)}

---

>

"Региональная запоздалая мысль":

Отвечая на вопрос о региональных различиях в Excel, я задался вопросом: $ является международным символом для Абсолютных Ссылок, или был ли символ изменен "через водоем" .

("Будет ли Brexit менять "UK Absolutes" от =€A€1 в =£A£1?!")

Быстрый поиск показал, что $ это действительно "всемирный" символ, используемый для абсолютных Excel - однако некоторые пользователи (никогда не набирали $ раньше) было трудно найти его. Вот ссылка, описывающая, как набрать $ Знак доллара на неамериканских клавиатурах...

---

Удачи с вашим проектом! Дайте знать, если у вас появятся вопросы...