При работе с формулой ВПР в Excel может сложиться ситуация, когда получить данные нужно не из столбца справа от ключевого столбца, а слева. ВПР так делать не умеет, в таких случаях к нам приходит на помощь сцепка из двух функций СМЕЩ и ПОИСКПОЗ, которые мы сегодня и изучим.
Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:
Как вы видите, мы могли бы по фамилии вытащить год рождения при помощи функции ВПР, но год рождения стоит слева от столбца Фамилия, конечно можно поменять их местами, но в реальной жизни такое решение не всегда возможно, потому будет решать такую задачу. (Кстати, если вы еще не знакомы с функцией ВПР или неуверенно ею пользуетесь, ознакомьтесь со статьей на нашем сайте: "Функция ВПР в Excel"). Для ее решение нам будут нужны функции СМЕЩ и ПОИСКПОЗ. Разберем их синтаксис:
СМЕЩ(ссылка;смещение по строкам;смещение по столбцам)
Ссылка - ячейка, от которой рассчитывается смещение по строкам и столбцам.
Смещение по строкам - указываем число, на какое количество строк надо сдвинуться. Положительное число - количество строк вниз, отрицательное число - количество строк вверх.
Смещение по столбцам - указываем число, на какое количество столбцов надо сдвинуться. Положительное число - количество столбцов вправо, отрицательное число - количество столбцов влево.
К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).
ПОИСКПОЗ(искомое значение; просматриваемый массив; тип сопоставления)
Искомое значение - это то значение, что мы ищем в таблице
Просматриваемый массив - таблица, где мы ищем искомое значение
Тип сопоставления - как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.
К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ("Петров";B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.
Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.
Итак, для нашего примера формула будет следующая:
=СМЕЩ(БД!$B$1;ПОИСКПОЗ(B2;БД!$B$1:$B$6;0)-1;-1)Сначала мы используем формулу СМЕЩ, так как мы будет считать смещение относительно конкретной ячейки. Первый параметр - ссылка на заголовок столбца "Фамилия" на листе с базой данных, далее нам нужно узнать на сколько строк нам надо сдвинуться, для этого мы используем функцию ПОИСКПОЗ. В ней в качестве первого параметра указываем фамилию, которую ищем, вторым параметром наш столбец с фамилиями в листе с базой данных. Из полученного значения мы вычитаем 1, что бы убрать эффект заголовка столбца. Последний параметр - смещение на один столбец влево, т.е. -1.
Таким образом, используя нехитрую комбинацию из двух функций мы получили универсальный аналог ВПР, который может возвращать значения, как со столбцов справа, так и слева.
Надеюсь, статья была для вас полезной. Спасибо за внимание.
0 comments:
Отправить комментарий
Спасибо за комментарий.