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

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

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

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

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

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

Рис. 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.2. Использование дополнительной строки с числами

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

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

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

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

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

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

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

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

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

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

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

Рис. 3.5. Май всё еще не является частью таблицы подстановки

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

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

Рис. 3.6. Определите диапазон F1_G5 в виде ТАБЛИЦЫ.

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

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

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

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

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

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

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

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

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

2 комментария для “Excel. Облегчая использование функции ВПР”

  1. как написать функцию впр, чтобы она выводила последнее значение?
    например есть ид клиента, по нему есть результат. было несколько переносов визита клиента, в последнем заключен договор. как вывести этот результат, т.к цепляется только первый (думает).
    можно ли как то сделать функцию фильтра по данным впр. например, сделать фильтр, чтобы в функции впр искался только результат «заключен договор»

    1. Lina, я описал пользовательскую функцию ВПР, которая ищет последнее вхождение. Однако, если я верно понял вашу задачу, думаю, что с ней справится функция СУММЕСЛИМН():

      (чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке)
      См. также Excel-файл.

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

Ваш адрес email не будет опубликован.