data_client

Технологии Blogger.

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

При работе с формулой ВПР в Excel может сложиться ситуация, когда получить данные нужно не из столбца справа от ключевого столбца, а слева. ВПР так делать не умеет, в таких случаях к нам приходит на помощь сцепка из двух функций СМЕЩ и ПОИСКПОЗ, которые мы сегодня и изучим.

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

Как вы видите, мы могли бы по фамилии вытащить год рождения при помощи функции ВПР, но год рождения стоит слева от столбца Фамилия, конечно можно поменять их местами, но в реальной жизни такое решение не всегда возможно, потому будет решать такую задачу. (Кстати, если вы еще не знакомы с функцией ВПР или неуверенно ею пользуетесь, ознакомьтесь со статьей на нашем сайте: "Функция ВПР в Excel"). Для ее решение нам будут нужны функции СМЕЩ и ПОИСКПОЗ. Разберем их синтаксис:

СМЕЩ(ссылка;смещение по строкам;смещение по столбцам)

Ссылка -  ячейка, от которой рассчитывается смещение по строкам и столбцам.

Смещение по строкам - указываем число, на какое количество строк надо сдвинуться. Положительное число - количество строк вниз, отрицательное число - количество строк вверх.

Смещение по столбцам - указываем число, на какое количество столбцов надо сдвинуться. Положительное число - количество столбцов вправо, отрицательное число - количество столбцов влево.

К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).

ПОИСКПОЗ(искомое значение;  просматриваемый массив; тип сопоставления)

Искомое значение - это то значение, что мы ищем в таблице

Просматриваемый массив - таблица, где мы ищем искомое значение

Тип сопоставления - как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.

К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ("Петров";B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.

Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

Итак, для нашего примера формула будет следующая:

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

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

Надеюсь, статья была для вас полезной. Спасибо за внимание.

Опубликовать в Google Plus