Многоразовое копирование формулы ВПР

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

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

В нашем примере, я пытаюсь вернуть определенную информацию по номеру продукта. У меня есть сводная таблица, где находится описание продукта, сегмент бизнеса и цена. Используем функцию ВПР.

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

функция ВПР excel

Но если я скопирую формулу в следующую ячейку, excel не изменил номер столбца, как если бы это была относительная ссылка.

функция ВПР excel

Чтобы сослаться на разные части сводной таблицы, необходимо каждый раз менять номер столбца в формуле ВПР. К примеру, в поле Описание номер столбца должен быть 3-й, а в поле Бизнес сегмент —  4-й.

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

функция ВПР excel

Решение 1: Использование дополнительных ячеек

Простым решением данного вопроса будет использование дополнительных ячеек. Как вы в видите, над каждой формулой ВПР я поместил значение номера столбца. Теперь, вместо ручного прописывания этого значения в каждой формуле =ВПР($A3;$H$3:$L$13;3;ЛОЖЬ), мы ссылаемся на дополнительную ячейку. Т.е. наша формула примет вид =ВПР($A3;$H$3:$L$13;C3;ЛОЖЬ).

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

функция ВПР excel

Решение 2: Использование функции СТОЛБЕЦ()

Если вам не по вкусу первое решение, и вам требуется более элегантный метод, вы можете воспользоваться функцией СТОЛБЕЦ. Этот метод не требует использования дополнительных ячеек.

Для тех, кто не знает, функция СТОЛБЕЦ принимает в качестве аргумента адрес ячейки и возвращает номер столбца этой ячейки. К примеру, СТОЛБЕЦ(D1) вернет значение 4, так как колонка D имеет четвертый порядковый номер.

В нашем случае, мне необходимо указать 3-й номер столбца в сводной таблице. Поэтому вместо ручного коддинга, я использую СТОЛБЕЦ(C1).

При копировании формулы ВПР поперек столбцов, функция СТОЛБЕЦ автоматически сдвигается вместе с другими ссылками. Это позволяет копировать ВПР без того, чтобы корректировать наши ссылки вручную.

функция ВПР excel

На этом все. Я уверен, что существуют другие, более продвинутые способы решения данной проблемы, но эти два метода, которые я использую в своей работе.

 


9 комментариев

  1. хотел бы спросить. можно ли устроить «суммарный» ВПР. для примера: в таблице несколько раз встречается один и тот же элемент, в итоговую таблицу я должен занести сумму всех одинаковых элементов, получается сделать через сводную таблицу, но мне бы хотелось обойтись без неё.

  2. Есть еще вариант с формулой: Поискпоз()

    =ВПР($A3;$H:$L;ПОИСКПОЗ(C$2;$H$2:$L$2;0);ЛОЖЬ) в ячейке С3

    В данном случае номер нужного столбца формула будет искать сама.

    Самый универсальный вариант на все случаи жизни

  3. Возник вопрос из вышеизложенного, как реализовать следующую возможность:
    допустим есть куча файлов в которых куча листов, и, чтобы не менять каждый раз путь к файлу, брать название файла из какой-нибудь ячейки в текущем листе.
    Например имеем строку =ВПР($D$4;’D:\Папка1\Файл1.xls]Лист1′!$A$4:$E$5523;5;ЛОЖЬ), которая берет значение из колонки 5 Листа1 Файла1, находящегося в Папке1, следующее значение нужно из такой-же колонки 5, Листа1 но Файла2 в Папке2. Можно ли указать допустим в ячейке А1 текущего листа книги Файл2 в ячейке А2 Папка2, для того чтобы в формулу встали соответствующие значения? ДВССЫЛ в данной задаче не рассматриваем так как она работает только с открытой книгой, СУММПРОИЗВ, к стати, тоже.

    • Ефим, на данный момент в Excel не реализована возможность указания текстовой строки в качестве ссылки на другую закрытую книгу. Но существует пользовательская функция, написанная Харланом Гровом, под названием PULL, которая позволяет вытягивать данные из закрытой книги, указав текстовую строку в качестве аргумента. Скачать функцию вы можете у него на сайте функция PULL

  4. Добрый день,
    Ренат, подскажите пож-та, =ВПР($А3;$H$3:$L$13;3;ЛОЖЬ)
    в $А3 используется 1 (один) значение, а если имеется 2 значение/столбца то как быть? Большое спасибо!

    Файл:  .xlsx

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

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