Как копировать формулы в Excel
![](http://officeassist.ru/wp-content/uploads/19075/tmp-91e8f1e2-2058-48a6-98c5-1b26eb92561a-360x111.jpg)
Ничто так не раздражает, как ручная правка формул. При этом меня не покидает ощущение, что все это можно сделать более легким путем. Такое ощущение появляется, к примеру, когда вы редактируете формулу ВПР.
Сегодняшний пост посвящен формуле ВПР, описывающий многоразовое копирование без необходимости ручной правки.
В нашем примере, я пытаюсь вернуть определенную информацию по номеру продукта. У меня есть сводная таблица, где находится описание продукта, сегмент бизнеса и цена. Используем функцию ВПР.
На рисунке видно, что я использовал общепринятый подход в использовании формулы ВПР.
![функция ВПР excel](http://officeassist.ru/wp-content/uploads/19075/tmp-91e8f1e2-2058-48a6-98c5-1b26eb92561a.jpg)
Но если я скопирую формулу в следующую ячейку, excel не изменил номер столбца, как если бы это была относительная ссылка.
![функция ВПР excel](http://officeassist.ru/wp-content/uploads/19075/tmp-1175abaf-2a6f-4080-908f-4ce0bb4ef4eb.jpg)
Чтобы сослаться на разные части сводной таблицы, необходимо каждый раз менять номер столбца в формуле ВПР. К примеру, в поле Описание номер столбца должен быть 3-й, а в поле Бизнес сегмент — 4-й.
Многие из нас делают такую правку вручную. Может показаться, что ничего зазорного в этом нет, но когда таких столбцов больше 10, это становится утомительным, часто вызывая мысли о самоубийстве.
![функция ВПР excel](http://officeassist.ru/wp-content/uploads/19075/tmp-3d2a5922-5ce3-4cb3-8996-29fca8e71a0e.jpg)
Решение 1: Использование дополнительных ячеек
Простым решением данного вопроса будет использование дополнительных ячеек. Как вы в видите, над каждой формулой ВПР я поместил значение номера столбца. Теперь, вместо ручного прописывания этого значения в каждой формуле =ВПР($A3;$H$3:$L$13;3;ЛОЖЬ), мы ссылаемся на дополнительную ячейку. Т.е. наша формула примет вид =ВПР($A3;$H$3:$L$13;C3;ЛОЖЬ).
Таким образом, номер столбца в формуле ВПР будет каждый раз исправляться, когда я буду копировать ее в соседнюю колонку.
![функция ВПР excel](http://officeassist.ru/wp-content/uploads/19075/tmp-be78d2b5-9a2a-4b28-98dc-6ac5d2459a3f.jpg)
Решение 2: Использование функции СТОЛБЕЦ()
Если вам не по вкусу первое решение, и вам требуется более элегантный метод, вы можете воспользоваться функцией СТОЛБЕЦ. Этот метод не требует использования дополнительных ячеек.
Для тех, кто не знает, функция СТОЛБЕЦ принимает в качестве аргумента адрес ячейки и возвращает номер столбца этой ячейки. К примеру, СТОЛБЕЦ(D1) вернет значение 4, так как колонка D имеет четвертый порядковый номер.
В нашем случае, мне необходимо указать 3-й номер столбца в сводной таблице. Поэтому вместо ручного коддинга, я использую СТОЛБЕЦ(C1).
При копировании формулы ВПР поперек столбцов, функция СТОЛБЕЦ автоматически сдвигается вместе с другими ссылками. Это позволяет копировать ВПР без того, чтобы корректировать наши ссылки вручную.
![функция ВПР excel](http://officeassist.ru/wp-content/uploads/19075/tmp-3c71cf63-db24-46da-a01a-28e24e52ce7c.jpg)
На этом все. Я уверен, что существуют другие, более продвинутые способы решения данной проблемы, но эти два метода, которые я использую в своей работе.