Excel. Облегчая использование функции ВПР

Это третья глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.

Присвойте имя вашей таблице подстановки. Проблема: моя таблица подстановки расположена на другом листе. Формула ВПР может сбивать с толку:

=ВПР(A2;’Таблица подстановки’!$A$1:$B$30;2;ЛОЖЬ)

Стратегия: присвойте имя диапазону, включающему таблицу подстановки. Для этого выделите ячейки А2:В30. Щелкните в поле имя слева от строки формул. Введите простое имя, например, Описание и нажмите Enter. Теперь формула ВПР принимает вид: =ВПР(A2;Описание;2;ЛОЖЬ). Учтите, что имя диапазона не должно содержать пробелов и начинаться с цифры (подробнее см. Excel. Имена диапазонов).

Рис. 3.1. Введите имя таблицы подстановки в поле слева от строки формул

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Стратегия: чтобы упростить копирование, подготовьтесь к нему:

• В окне Аргументы функции в поле Искомое_значение введите ссылку, выделите ее и нажмите <F4> три раза. Это приведет к изменению А2 до $А2. Такая смешанная ссылка позволит корректно протащить формулу по столбцам. ВПР всегда будет искать значение в столбце А.
• В окне Аргументы функции в поле Таблица введите ссылку на диапазон, выделите ее и нажмите <F4> один раз. Таблица подстановки будет иметь четыре знака доллара. Т.е., ВПР после протаскивания формулы всегда будет ссылаться на один и тот же диапазон. Или присвойте таблице подстановки имя, и используйте его в ВПР.

Наибольшая проблема – это третий аргумент. Может быть, проще всего отредактировать формулу, заменяя 2 на 3, потом на 4, на 5, и так далее. Тем не менее, позвольте предложить два более оригинальных метода:

• Используйте дополнительную строку с числами от 2 до 13. Расположите этот ряд выше таблицы подстановки, которую вы пытаетесь построить (рис. 3.2). Затем, вместо того, чтобы указывать в качестве третьего параметра 2, дайте ссылку, на ячейку, которая вернет значение 2 – B1; выделите ссылку и нажмите <F4> два раза, чтобы изменить его на В$1. При протаскивании формулы сохранится ссылка на первую строку и соответствующий столбец (поэкспериментируйте!).
• Второе решение еще более изящно. Используйте функцию СТОЛБЕЦ(В1), которая возвращает номер столбца для указанной ячейки. Поскольку В1 находится во второй колонке, функция вернет 2. Я не хочу сказать, что в мире компьютерных фанатов это простейший способ написания цифры 2. Тем не менее, преимущество заключается в том, что при копировании этой формулы вправо, ссылка автоматически изменится на С1, а функция СТОЛБЕЦ(С1) вернет 3. Этот способ позволяет обойтись без значений в строке 1.

Рис. 3.2. Использование дополнительной строки с числами

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

Примечание: это совпадение, что формула в B4 ссылается на СТОЛБЕЦ(В1). Вы используете формулу СТОЛБЕЦ(В1), так как вам нужно число 2 для выборки из таблицы подстановки, а столбец В – это второй столбец рабочего листа. Даже если в таблице на рисунке выше расположить формулу в ячейке XEG4, третий аргумент все равно остался бы СТОЛБЕЦ(В1).

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

Альтернативные стратегии: вы можете ускорить работу если вместо ВПР используете конструкцию из двух функций: ПОИСКПОЗ и ИНДЕКС (см. главу 5).

Преобразуйте таблицу подстановки в инструмент Excel ТАБЛИЦА. Проблема: я постоянно добавляю новые строки в нижнюю часть моей таблицы подстановки. Затем, я должен переписать формулы ВПР для включения новых строк.

Стратегия: использование инструмента ТАБЛИЦА упрощает этот процесс. ТАБЛИЦА автоматически включает в себя вновь добавленные строки и переписывать формулы ВПР не нужно.

Рассмотрим пример. Таблица подстановки (рис. 3.4, область $F$2:$G$5) содержит только четыре строки, по одной для каждого истекшего месяца в году. Новые данные за май (D10:D11) возвращают ошибку #Н/Д, так как мая нет в таблице подстановки.

Рис. 3.4. Мая нет в таблице подстановки, поэтому в основной таблице ошибка #Н/Д

При добавлении новых данных в F6:G6, формулы в D10:D11 остаются прежними, ссылаясь лишь на $F$2:$G$5, поэтому ВПР по-прежнему возвращает #Н/Д (рис. 3.5). Однако, если вы измените в ВПР ссылку на таблицу подстановки на $F$2:$G$6 то теперь формула вернет корректный результат.

Рис. 3.5. Май всё еще не является частью таблицы подстановки, до тех пор, пока вы не перепишите формулы ВПР

Вернемся к первоначальной ситуации (см. рис. 3.4). Кликните на любую ячейку в диапазоне $F$2:$G$5 и нажмите сочетание клавиш Ctrl+Т (Т английское). Excel отображает диалоговое окно создание таблицы. Нажмите Оk (рис. 3.6). Диапазон $F$1:$G$5 автоматически отформатируется, строки получат чередование цветов, в заголовках появятся раскрывающиеся фильтры, в правом нижнем углу правой нижней ячейки ТАБЛИЦЫ появится маленький треугольник (если за него потянуть, область ТАБЛИЦЫ расширится), появится новая вкладка на ленте Excel: РАБОТА С ТАБЛИЦАМИ –> КОНСТРУКТОР (рис. 3.7).

Рис. 3.6. Определите диапазон $F$1:$G$5 в виде ТАБЛИЦЫ

Рис. 3.7. Отформатированная ТАБЛИЦА

Отметим, что на данный момент в оригинальной формуле ВПР ничего не изменилось. Ссылка на таблицу подстановки не переключилась автоматически на ТАБЛИЦУ (рис. 3.8). Отличие рис. 3.4 от рис. 3.8 в том, что во втором случае таблица подстановки превращена в ТАБЛИЦУ. Это заметно по чередованию цветов строк и раскрывающимся фильтрам.

Рис. 3.8. Хотя таблица подстановки преобразована в ТАБЛИЦУ, формула ВПР осталась той же

Однако, если вы наберете новые данные в F6:G6, ТАБЛИЦА автоматически расширится, чтобы включать в себя ряд 6. Почему-то Excel автоматически обновит формулы ВПР, и включит в них всю таблицу подстановки. Это кажется невероятным, но это так (рис. 3.9).

Рис. 3.9. Формулы ВПР автоматически изменили ссылку на расширенную таблицу подстановки