Подставляем нужные значения в Excel из справочника функцией “Вертикальный просмотр”

По работе мне приходится общаться с большими массивами данных, представленных в некрасивой форме. Под красотой я подразумеваю чистый русский язык, грамотность, возможность переноса БД в другие программы. Одним из примеров является поле “ответственный менеджер”. Там встречаются значения, которые невозможно автоматически обработать, например, есть менеджер “Юлия Аргунова”, так вот, она может быть записана как “АРГУНОВА”, как ” АРГУНОВА” (впереди пробел), “Аргунова”, “ЮАргунова” и так далее.

Как решить такую проблему? Единоразово несложно, просто ищем и заменяем неправильный вариант на правильный стандартной функцией поиска:

Но если таких баз много, то начинается бессмысленная и беспощадная работа. И все вокруг меня делают такое вручную. Почему никто не хочет обучиться самым стандартным функциям Excel?

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

Работает она просто –

1. Делаем справочник, чтобы программа знала, какие значения подставляют какие значения

2. В нужной ячейке нажимаем кнопку вставить функцию (fx) и выбираем ВПР:

3. Дальше с помощью мастера мы выбираем три вещи:

а) искомое значение –  это просто анализируемая ячейка. в нашем случае А1, “АРГУНОВА”

б) потом таблицу значений, которую мы подготовили

в) номер столбца – как правило второй, но вдруг у вас большая таблица, очень удобно

4. Результат – мы нашли в справочнике нужное значение и подставили его.

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

Вторая ошибка – обязательно справочник нужно фиксировать. Иначе по мере сползания формулы вниз наша область справочника тоже поползет вниз. Вместо A1:B5 нужно писать $A$1:$B$6.

Эти ошибки нужно обязательно совершить – только так можно их запомнить.

А чтобы продлить формулу на весь столбец – не обязательно ее протягивать за угол, можно просто на него два раза кликнуть и формула автоматически применится ко всему столбцу.

Результат (лист справочника вынесен теперь в отдельную книгу – не обязательно его хранить в том же документе):

Поделиться

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *