Функция впр как расшифровывается

Функция впр как расшифровывается

Самый популярный в мире редактор электронных таблиц предлагает пользователю практически неограниченное количество возможностей. Закрепить строку в Excel при прокрутке, нарисовать график или диаграмму, собрать и систематизировать информацию из разных источников — всё это можно сделать в несколько кликов мышью. Отдельного внимания заслуживает функция ВПР, служащая для сопоставления данных; как ею пользоваться — попробуем разобраться.

Что такое ВПР в Excel?

ВПР расшифровывается как «вертикальный просмотр». В английском интерфейсе для её обозначения используется термин VLOOKUP, означающий то же самое, или VPR, являющийся калькой русской аббревиатуры.

Важно: все названия равноправны, могут свободно комбинироваться и заменяться одно другим, однако чаще всего используется первое — хотя бы потому, что позволяет не переключать раскладку клавиатуры.

Назначение функции ВПР — найти и вернуть в ячейку любое значение из исходной таблицы на основании указанных данных. Особенно полезна эта опция Excel, когда необходимо извлечь информацию из огромной сводной таблицы, сопоставить несколько источников данных или осуществить быстрый поиск по нескольким параметрам — примеры решения каждой из перечисленных задач будут приведены ниже.

Совет: научиться правильно применять функцию VLOOKUP чуть сложнее, чем построить диаграмму в Excel; если планируется однократный поиск по единственной или нескольким строкам таблицы, проще будет воспользоваться комбинацией клавиш Ctrl + F и обработать данные вручную.

Как пользоваться ВПР в Excel?

Пользователь, уже пробовавший сделать буклет или создать базу данных, понимает, что функционал Экселя значительно шире, чем кажется на первый взгляд; при этом функции, применяемые относительно нечасто, вынесены едва ли не в центр «ленты», а нужные приходится искать в выпадающих меню. Так и с ВПР — за одно нажатие до него не добраться.

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

Пример №1

Имеется сводная таблица с наименованиями товаров, датами и объёмами поставок и сроками годности. Требуется, не прибегая к опции поиска, вывести в любую свободную ячейку информацию о конкретном продукте.

Решение:

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

  • В любом удобном месте (для облегчения работы — лучше на новой странице) создать таблицу с искомыми параметрами. Названия могут совпадать с исходными или отличаться от них; главное — чтобы было прямое соответствие значений, иначе выведенная информацию окажется неверной или бесполезной.

  • В первой строке новой таблицы Excel вписать любое из требуемых наименований. Работать с ВПР можно и по другому параметру, например по объёму поставки, однако логически правильнее и проще для восприятия использовать в качестве ориентира заглавный столбец первой таблицы.

  • Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».

  • Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».

  • В открывшемся окошке вызвать верхний выпадающий список и выбрать кликом мыши пункт «Полный алфавитный перечень».

  • Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».

  • Другой способ вызвать функцию VLOOKUP — на той же вкладке «Формулы» открыть выпадающий список «Ссылки и массивы» и выбрать пункт «ВПР».

  • В первой строчке нового диалогового окна указать, нажав на обращённую вверх стрелочку, ячейку с наименованием искомого товара, находящуюся во вновь созданной таблице.

  • Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.

  • В третьей — порядковый номер столбца, в котором требуется осуществить поиск…

  • …Или сам столбец, выделив его аналогичным образом.

  • В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».

  • Если значение в строке появилось, но оно не соответствует ожидаемому, тогда следует проверить правильность установленного формата ячейки.

  • В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…

  • …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».

  • Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.

  • Поскольку функция Excel ВПР достаточно сложна в применении, простое «растягивание» на следующую строку не даст результата: пользователь увидит или значение, вновь не соответствующее ожидаемому, или извещение о неправильном применении формулы.

  • Чтобы добиться требуемого результата, следует или повторить для новой строчки все перечисленные манипуляции, или выделить уже готовую и скопировать в верхнем текстовом поле Excel формулу…

  • …После чего вставить её в нужную строку…

  • …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».

  • Нажав на клавишу Enter и при необходимости вновь подкорректировав формат ячейки, пользователь получит заполненную краткую таблицу с достоверной информацией из большой сводной.

  • Теперь при изменении значения в исходной таблице оно автоматически будет обновляться в новой, что крайне удобно для отслеживания корректировок в реальном времени.

  • Если же, напротив, нужно зафиксировать полученные данные, защитив их от изменений, следует выделить получившуюся таблицу и скопировать её, используя контекстное меню.

  • После чего, вновь вызвав его, выбрать пункт «Специальная вставка», отметить кружочком в разделе «Вставить» пункт «Значения» и кликнуть по кнопке «ОК».

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

  • Но главное — другое: теперь, единожды настроив выдачу в таблице Excel, можно вписывать в верхнюю строчку любые требуемые наименования продуктов — искомые значения появятся в строках автоматически в уже установленном формате.

Важно: при использовании функции ВПР в Excel значения в строках новой таблицы привязываются не только к исходной, но и к текущему местоположению. При копировании данных в другое место (даже на том же листе) пользователь получит ячейки с ошибками. Исправить ситуацию можно, как было описано ранее, скопировав данные и вставив их с помощью «Специальной вставки».

Пример №2

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

Решение:

  • Первый шаг, как и в предыдущем случае, — открыть обе таблицы. В идеале их следует разместить на одном листе — так будет проще проверить правильность объединения по одному или нескольким выбранным позициям.

  • Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.

  • Выделив щелчком мыши первую свободную ячейку под названием, пользователь должен уже известным образом вызвать функцию ВПР — после первого примера это не сложнее, чем сделать график в Excel.
Читайте также:  Вес картриджа для принтера пустой

  • В первой строке диалогового окна следует указать наименование позиции из первой таблицы — той, в которую добавляются данные.

  • Во второй — вставить выделением всю вторую таблицу, включая названия столбцов и строк.

  • В третьей — указать номер столбца второй таблицы, который планируется сопоставить с первым. Последняя строка, как и в первом примере, должна принять значение «0», иначе получить результаты, точно соответствующие заданным параметрам, не выйдет.

  • Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.

  • Теперь не нужно подставлять формулу в каждую строку — достаточно зажать левой клавишей мыши крестик в правом нижнем углу ячейки и «растянуть» её на все позиции — данные будут подставлены автоматически и в нужном порядке.

  • Соответствия сохранятся даже при пересортировке строк в первой таблице.

  • А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

Важно: бывает, что при пересортировке строк во второй таблице Excel данные в первой, полученные с помощью функции ВПР, теряются. В таком случае имеет смысл «закрепить» значения, выделив диапазон во второй строке диалогового окна VLOOKUP и нажав клавишу F4.

Вопросы от новичков

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

Как сравнить таблицы с помощью ВПР?

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

  • Открыть обе таблицы, а при необходимости — перенести их на один лист.

  • Добавить к одной из них новый столбец, название которого отражает произошедшие изменения.

  • Вставить в первую ячейку под заголовком нового столбца формулу ВПР, как было описано выше. В первой строчке диалогового окна указывается наименование товара из первой таблицы, во второй — вносится вся вторая таблица, включая заголовки, в третий — номер ряда, который нужно сравнить, а в четвёртый — снова значение «0».

  • Когда в ячейке появится требуемой значение, достаточно «растянуть» его на все позиции — данные подставятся автоматически.

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

Как с помощью ВПР сделать выпадающий список?

Выпадающие списки — крайне удобная функция Excel, позволяющая не вводить каждый раз наименование искомой позиции, а выбирать его из перечня. Самый простой выпадающий список можно сделать следующим образом:

  • Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».

  • Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

  • Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

  • Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

  • В строке «Источник» указать требуемый диапазон наименований, после чего нажать «ОК».

  • Теперь пользователь сможет, щёлкая по треугольничку возле названия продукта, выбирать в выпадающем списке нужную позицию — данные в ячейках ниже обновятся автоматически.

Важно: при изменении параметров в исходной таблице соответствующие коррективы будут внесены Excel и в «результирующую».

Подводим итоги

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

Автор: rf-webmaestro · Опубликовано 27.04.2018 · Обновлено 08.02.2019

Редактор Эксель – очень мощная программа для работы с таблицами. Иногда бывает так, что приходится работать с большим объемом данных. В таких случаях используются различные инструменты поиска информации. Функция «ВПР» в Excel – одна из самых востребованных для этой цели. Рассмотрим её более внимательно.

Расшифровка

Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»

Как пользоваться функцией

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

  1. Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.

  1. Добавим несколько полей, которые будем использовать для демонстрации формул.

  1. В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
  2. Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
  3. Выбираем категорию «Полный алфавитный перечень».
  4. Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».

  1. Затем нас попросят указать «Аргументы функции»:
    • В поле «Искомое выражение» указываем ссылку на ячейку, в которой мы написали нужную нам фамилию.
    • Для того чтобы заполнить поле «Таблица», достаточно просто выделить все наши данные при помощи мышки. Ссылка подставится автоматически.
    • В графе «Номер столбца» указываем номер 2, поскольку в нашем случае имя находится во второй колонке.
    • Последнее поле может принимать значения «0» или «1» («ЛОЖЬ» и «ИСТИНА»). Если укажете «0», то редактор будет искать точное совпадение по заданным критериям. Если же «1» – то во время поиска не будут учитываться полные совпадения.
    • Для сохранения кликните на кнопку «OK».

    1. В результате этого мы получили имя «Томара». То есть, всё правильно.

    Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи Ctrl + C и Ctrl + V не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

    Для того чтобы всё сработало правильно, нужно сделать следующее:

    1. Кликните на ячейку с первой функцией.
    2. Перейдите в строку ввода формул.
    3. Скопируйте текст при помощи Ctrl + C .

    1. Сделайте активной следующее поле.
    2. Снова перейдите в строку ввода формул.
    3. Нажмите на горячие клавиши Ctrl + V .

    Только таким способом редактор не изменит ссылки в аргументах функции.

    1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу Enter .

    1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.

    1. После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.

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

    То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

    Как использовать функцию «ВПР» для сравнения данных

    Демонстрировать эту возможность будем при помощи двух таблиц. Представим, что у нас на втором листе появились обновлённые данные о сотрудниках. Наша задача состоит в том, чтобы узнать, что именно изменилось. Для этого нужно будет сделать следующее.

    1. Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш Ctrl + C и Ctrl + V ).
    2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.
    Читайте также:  Телефон оператора мегафон сибирь

    1. Добавим ещё один столбец в нашу старую таблицу.

    1. Переходим в первую клетку нового столбца и вводим там следующую формулу.

    =ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)

    • $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
    • Лист2! – эти значения нужно искать на листе с указанным названием;
    • $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
    • 4 – номер столбца в указанной области данных;
    • ЛОЖЬ – искать точные совпадения.
    1. Новая информация выведется в том месте, где мы указали формулу.
    2. Результат будет следующим.

    1. Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.

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

    Теперь мы можем без труда определить, в записях какого сотрудника произошли изменения.

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

    В подобных случаях приходится использовать различные дополнительные столбцы, в которых объединяют информацию с нескольких колонок. А это выглядит некрасиво и не совсем удобно.

    Функция «ВПР» и выпадающие списки

    Рассмотрим примеры использования этих двух инструментов одновременно. Для этого нужно выполнить следующие действия.

    1. Перейдите в ячейку, в которой происходит выбор фамилии.
    2. Откройте вкладку «Данные».
    3. Кликните на указанный инструмент и выберите пункт «Проверка данных».

    1. В новом окне в графе «Тип данных» выберите пункт «Список».

    1. После этого появится новое поле «Источник». Кликните туда.
    2. Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
    3. Для продолжения нажмите на «OK».

    1. Благодаря этому у вас появится список всех фамилий, которые находятся в базе. Выберите что-нибудь из предложенного.

    1. Вы увидите, что данные автоматически изменились. При этом всё подставится согласно выбранной фамилии.

    Сводные таблицы такой возможностью похвастаться не могут. Функция «ВПР» – очень мощный инструмент для поиска любой информации.

    Ошибки #Н/Д

    С подобной проблемой сталкиваются многие пользователи, которые только начинают пользоваться этой функцией. Как правило, ошибка «#Н/Д» возникает в следующих случаях:

    • вы включили точный поиск (последний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
    • вы включили неточный поиск (последний параметр 1/ИСТИНА), но при этом данные не отсортированы по возрастанию (если используется приблизительный поиск, то разработчики Microsoft рекомендуют использовать упорядоченные данные);
    • аргументы функции имеют различный формат (например, что-то в текстовом виде, а остальное – в числовом);
    • в формуле присутствуют опечатки или лишние символы (пробелы, непечатаемые знаки, переносы и так далее).

    Отличие от функции «ГПР»

    Данный инструмент практически точно такой же, только ищет по горизонтали. Более подробно о нем можно узнать на официальном сайте Microsoft.

    Заключение

    В данной статье мы пошагово рассмотрели, как пользоваться функцией «ВПР» в редакторе Excel. Кроме этого, было показано несколько примеров. Данная инструкция ориентирована на новичков (чайников).

    Если у вас что-то не получается, возможно, вы неправильно указываете аргументы функции. Например, неправильный номер столбца или неверный диапазон ячеек. Попробуйте повторить описанные выше действия заново, но на этот раз более внимательно. И у вас всё получится.

    Видеоинструкция

    Для тех, кому пошаговая инструкция не помогла, мы приготовили специальное видео, в котором всё рассказывается еще более детально с дополнительными замечаниями. Возможно, так вам будет проще разобраться.

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

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

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

    Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

    ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

    Синтаксис

    Функция ВПР имеет четыре параметра:

    =ВПР( ; ; [; ] ), тут:

    — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

    — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

    — номер столбца в диапазоне, из которого будет возвращено значение;

    это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

    Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

    Как же конкретно работает формула ВПР

    • Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
    • Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).
    Читайте также:  Сервис apple imessage facetime что это

    Схемы работы формул

    ВПР тип I

    ВПР тип II

    Следствия для формул вида I

    1. Формулы можно использовать для распределения значений по диапазонам.
    2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
    3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
    4. Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
    5. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

    Следствия для формул вида II

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

    Производительность работы функции ВПР

    Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

    • мне нужен более мощный компьютер;
    • мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

    И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

    Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

    Недостатки формулы

    Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

    Некоторые аспекты применения формулы в реальной жизни

    Диапазонный поиск

    Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

    Поиск текстовых строк

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

    Борьба с пробелами

    Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

    Разный формат данных

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

    =ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

    =ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

    Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

    • Двойное отрицание —D7.
    • Умножение на единицу D7*1.
    • Сложение с нулём D7+0.
    • Возведение в первую степень D7^1.

    Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

    Как подавить выдачу #Н/Д

    Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

    Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

    Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

    Массив

    Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

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

    Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

    Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

    Ну и на грани гениальности — оформить массив в виде умной таблицы.

    Использование функции СТОЛБЕЦ для указания колонки извлечения

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

    Создание составного ключа через &»|»&

    Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

    Ссылка на основную публикацию
    Фотографии купе в поезде
    Интересный фотоотчет о поездке на одном из первых рейсов двухэтажных поездов. Смотрим далее, как все устроено внутри таких двухэтажных вагонов...
    Уравнение окружности в полярных координатах
    Определение: замкнутая плоская кривая, все точки которой одинаково удалены от данной точки (центра О), лежащей в той же плоскости, что...
    Уравнение пучка прямых проходящих через точку
    Совокупность прямых, проходящих через некоторую точку, называется пучком прямых с центром в этой точке. Если и - уравнения двух пересекающихся...
    Фотография с самым большим разрешением в мире
    Представляем вашему вниманию нашу подборку самых больших фотографий в мире. Для их просмотра вам будет необходим FlashPlayer. Его можно скачать...
    Adblock detector