Функция ВПР значительно упрощает работу с данными, однако у ВПР есть несколько существенных недостатков и одним из них является зависимость от четко прописанных в аргументах номеров столбцов.
Поясню что имею в виду на примере.
У нас есть список сотрудников и задача состоит в том, что нужно сформировать карточку, в которой из выпадающего списка выбирается сотрудник, а остальные поля заполняются автоматически, подтягиваясь из основного списка.
Для большей наглядности создам карточку на этом же листе.
Названия полей карточки сформирую с помощью заголовков основной таблицы - скопирую их в буфер обмена и затем вставлю транспонировав.
В первой ячейке карточки должен быть выпадающий список. Сформирую его самым простым способом - через вкладку Данные и проверку данных.
Далее мы должны подтянуть данные из основной таблицы и проще всего это реализовать можно как раз с помощью функции ВПР.
Напомню, как это делается.
Сначала указываем искомое значение - это будет ячейка с выпадающим списком. Затем указываем диапазон данных, в котором будет осуществляться поиск - выделяем всю таблицу без заголовков. И далее указывается номер столбца в указанном диапазоне, откуда нужно будет вернуть значение.
То есть функция ВПР произведет поиск искомого значения в крайнем левом столбце выделенного диапазона и затем вернет значение ячейки из указанного столбца. В данном случае это будет второй столбец выделенного диапазона и я указываю цифру 2. Ну и последний аргумент - точное совпадение или ноль.
Не забываем фиксировать ссылки с помощью клавиши F4 (подробнее об этом в видео об абсолютных и относительных ссылках) и затем растянем формулу по последующим ячейкам карточки.
Останется лишь заменить номера столбцов на соответствующие.
После этих действий все будет работать и при выборе сотрудников из списка другие поля карточки будут автоматически заполняться. Но что произойдет, если нам понадобится в основную таблицу внести еще один столбец?
Так как номера столбцов в аргументах функции заданы цифрой, то теперь в карточки отображаются неверные данные. Чтобы все исправить нам придется вручную изменять все формулы и корректировать номера столбцов.
Для наглядности давайте сделаем так - проставим напротив соответствующих полей карточки номера столбцов, а затем изменим формулу, подставив в качестве номеров столбцов эти значения.
Теперь при вставке нового столбца мы можем откорректировать это значение, чтобы исправить формулу, но давайте эту задачу автоматизируем.
Поможет нам в этом функция ПОИСКПОЗ. Она выполняет поиск указанного значения в диапазоне ячеек и возвращает относительную позицию ячейки со значением в этом диапазоне.
Давайте применим эту функцию. Нам нужно определить номер конкретного столбца с данными, а названия заголовков столбцов и полей карточки у нас совпадают. Поэтому в качестве искомого значения указываем название соответствующего поля карточки. Затем нужно указать диапазон, в котором будет производиться поиск позиции искомого значения - это строка с заголовками основной таблицы. Не забываем диапазон зафиксировать с помощью клавиши F4. Последний аргумент - точное совпадение или ноль.
Растягиваем формулу на все ячейки карточки и убеждаемся, что все работает верно.
Осталось лишь интегрировать функцию ПОИСКПОЗ в основную формулу, то есть просто скопируем ее из вспомогательного столбца и откорректируем функцию ВПР.
Ссылки на мои ресурсы по Excel
★ Телеграм