- Как сделать ВПР в Excel: понятная пошаговая инструкция.
- Особенности использования формулы ВПР в Excel
- Как создать функцию ВПР в Excel
- Аргументы функции ВПР
- Автозаполнение
- Как использовать функцию ВПР в Excel
- Использование функции ВПР для работы с несколькими таблицами и другими функциями
- Использование приблизительного значения
- Быстрое сопоставление двух таблиц с ВПР в Эксель
- ВПР и приблизительный интервальный просмотр
- Как работает функция ВПР в Excel: несколько примеров для «чайников».
- Использование точного и приблизительного поиска.
- Применяйте именованный диапазон.
- Использование символов подстановки и другие тонкости критерия поиска.
- Использование нескольких условий.
- «Умная» таблица.
- Специальные инструменты для ВПР в Excel.
- Мастер ВПР — простой способ писать сложные формулы
- Стандартный ВПР.
- «Левый» ВПР.
- Примеры функции ВПР в Excel
- Конструкция с функцией ЕСЛИОШИБКА
- Разные форматы критерия в таблицах
- Функция СЖПРОБЕЛЫ для чистки текстового критерия
- Подсчет номера столбца в большой таблице
- Почему не работает функция ВПР
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала на простом примере разберем, как работает функция ВПР в Excel. Допустим, у нас есть две таблицы. Первый – это прайс-лист с названиями и ценами. Второй — приказ купить некоторые из этих предметов. Поиск в прайс-листе нужного товара и ввод цены в заказ вручную — очень утомительное занятие. Ведь прайс-лист с ценами может иметь сотни строк. Мы должны делать все автоматически.
Нам нужно найти название интереса в первом столбце и вернуть (то есть отобразить в ответ на наш запрос) содержимое из нужного столбца в той же строке, что и имя.
Наш прайс-лист находится в столбцах A и B. Список покупок находится в EH. Допустим, первым пунктом в списке продуктов являются бананы. Нам нужно найти этот товар в столбце А, где указаны все наименования, а затем поместить цену в ячейку G2.
Для этого в G2 запишем следующую формулу:
=ОТКРЫТЬ(E2,$A$2:$B$7,2,0)
А теперь подробно разберем, как сделать ВПР.
- Берем значение из E2.
- Ищем точное совпадение (поскольку четвертый параметр равен 0) в диапазоне $A$2:$B$7 в первом (самом левом) столбце. Обратите внимание, что лучше сразу использовать абсолютные ссылки на прайс-лист, чтобы ссылка не «промахнулась» при копировании этой формулы».
- Если товар найден, переходим ко второму столбцу диапазона (на это указывает третий параметр = 2).
- Берем из него цену и вставляем в нашу ячейку G2.
Случилось? Теперь просто скопируйте формулу из G2 в G3:G8.
Отчет о продажах готов.
Чтобы понять, что такое точное совпадение, попробуйте изменить название товара в A5 или E2. Например, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу получите ошибку #Н/Д. Это означает, что товар не найден. В то же время таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.
Обратим внимание на четвертый параметр. Мы указали null (можно написать FALSE), что означает «точный поиск». Но что, если вы забудете его указать и в итоге получите номер столбца, из которого берутся нужные данные?
Давайте пошагово рассмотрим, что будет происходить в этом случае.
- Берем значение из E2.
- Начинаем искать его в крайнем левом столбце в диапазоне $A$2:$B$7, то есть в столбце A. Так как совпадений в A2 не найдено, смотрим дальше: что ниже.
- Там находим товар «Сливы». Это предполагает, что наш список отсортирован по алфавиту. Ведь это главное условие для нахождения примерного совпадения.
- Поскольку в отсортированном списке слово «сливы» находится ниже слова «бананы», функция решает, что нет смысла искать дальше слово, начинающееся с «Б». Процесс можно остановить. И придерживаться буквы «А». То есть это ближайшее значение.
- Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 в результате вычислений.
К сожалению, «бананы» в нашем прайсе были ниже, но они «просто не попали». И неправильная цена теперь написана в списке покупок.
В этом руководстве мы рассмотрели только основы. Как вы можете использовать его на самом деле?
Особенности использования формулы ВПР в Excel
Функция ВПР имеет свои особенности, о которых следует знать.
- Первую функцию можно считать общей для функций, которые используются для многих ячеек, написав формулу в одной из них, а затем скопировав ее в остальные. Здесь приходится учитывать относительность и абсолютность отсылок. Особенно в ВПР критерий (первое поле) должен иметь относительную ссылку (без знака $), так как каждая ячейка имеет свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес поля пишется через $). Если этого не сделать, область будет «уходить вниз» при копировании формулы, и многие значения просто не будут найдены, так как искать будет негде.
- Номер столбца, введенный в третье поле «Номер_столбца» при использовании мастера функций, должен считаться, начиная с самого критерия.
- Функция ВПР из диапазона искомых данных возвращает первое значение сверху. Это значит, что если во второй таблице, куда мы пытаемся «подтянуть» какие-то данные, есть несколько ячеек с одинаковыми критериями, ВПР захватит первое значение сверху в пределах выбранного диапазона. Это следует помнить. Например, если мы хотим прибавить количество из другой таблицы к цене товара, а там этот товар встречается несколько раз (в нескольких строках), то к цене будет прибавлено первое сверху количество.
- Должен быть установлен последний параметр в формуле, равный 0 (ноль). В противном случае формула может работать неправильно.
- После использования ВПР лучше сразу удалить саму формулу, оставив только полученные значения. Делается это очень просто. Выделяем область с полученными значениями, нажимаем «копировать» и приклеиваем значения на то же место с помощью специального клея. Если таблицы расположены в разных книгах Excel, очень удобно разорвать внешние ссылки (и оставить вместо них только значения) с помощью специальной команды, расположенной по пути Данные → Изменить ссылки.
После вызова функции разрыва внешних ссылок появится диалоговое окно, в котором нужно нажать кнопку «Разорвать ссылку», а затем «Закрыть».
Это удалит все внешние ссылки сразу.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксисом. Обычно функция начинается со знака равенства «=», за которым следует имя функции и аргументы в скобках.
Пишем формулу в столбце цены (С2). Это можно сделать двумя способами:
- Выберите ячейку и введите функцию.
- Выберите ячейку → нажмите Fx (Shift + F3) → выберите вкладку «Ссылки и массивы» → выберите функцию ВПР → нажмите «ОК».
После этого открывается окно, где можно заполнить ячейки в аргументах формулы.
Синтаксис функции ВПР выглядит следующим образом:
=ВПР(искомое значение, таблица, номер столбца, интервальный поиск)
В нашем случае мы получаем следующую формулу:
=ПРОСМОТР(A2;$G$2:$H$11;2;0)
Аргументы функции ВПР
Теперь разберемся, что и куда писать.
Со знаком равенства «=» и названием «ВПР» все готово. Поговорим об аргументах. Они пишутся в круглых скобках через точку с запятой или заполняются ячейками окна функции. Формула ВПР имеет 4 аргумента: значение поиска, таблица, номер столбца и поиск диапазона.
Искомое значение — это имя ячейки, из которой мы будем «вытягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой она получает информацию.
В нашем случае выбираем ячейку «А2», в ней указано название товара. ВПР возьмет это имя и будет искать аналогичную ячейку в другой таблице с прайс-листом.
=ОТКРЫТЬ(A2;
Таблица — это ряд ячеек, из которых мы хотим «подтянуть» данные для нужного значения. Мы используем абсолютные ссылки в этом аргументе. Это означает, что в формуле таблица будет иметь вид «$G$2:$H$11» вместо «G2:H11». Вы можете ввести символы «$» вручную или выбрать «G2:H11» внутри формулы и нажать F4. Если этого не сделать, то таблица не будет зафиксирована в формуле и изменится при копировании.
В нашем случае это таблица с прайсом. Формула будет искать в нем совпадение с той ячейкой, которая была указана в первом аргументе формулы — А2 (Кофе). Нажмите F4 и сделайте ссылку абсолютной.
=ОТКРЫТЬ(A2,$G$2:$H$11
Номер столбца — это столбец в таблице, из которого должны быть извлечены данные. Именно из него мы и будем «вытягивать» результат.
- Формула сканирует таблицу по вертикали.
- Находит совпадение в крайнем левом столбце с искомым значением.
- Глядя на столбец напротив, порядок, который мы указываем в этом аргументе.
- Отправляет данные в ячейку с формулой.
Читайте также: 7 приемов изменить формат ячеек в Excel
В нашем случае это столбец с ценой продукции в прайс-листе. Формула ищет нужное значение ячейки А2 (Кофе) в первой колонке прайс-листа и «подтягивает» данные из второй колонки (потому что мы указали цифру 2) в ячейку с формулой.
=ОТКРЫТЬ(A2,$G$2:$H$11,2
Интервальный поиск — это параметр, который может принимать 2 значения: «истина» или «ложь». Истинно указывается в формуле цифрой 1 и означает примерное совпадение с искомым значением. False обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и «истинные» критерии обычно используются при работе с числами, а точные и «ложные» — при работе с именами.
В нашем случае искомое значение — это текстовое имя. Поэтому используем точный поиск — ставим цифру 0 и закрываем скобки.
=VLOOKUP(A2;$G$2:$H$11;2;0)Сообщество теперь в Telegram Подпишитесь и будьте в курсе последних ИТ-новостей Подпишитесь
Автозаполнение
В конце продлеваем формулу до конца, в результате чего происходит автодополнение.
Для правильной работы функции ВПР во время автозаполнения искомое значение должно быть относительной ссылкой, а таблица должна быть абсолютной.
- В нашем случае искомое значение равно A2. Это относительная ссылка на ячейку, поскольку в ней нет символов «$». Из-за этого ссылка на искомое значение меняется относительно каждой строки, когда автозаполнение происходит в других ячейках: A2 → A3 →… → A11. Это удобно, когда вам нужно повторить формулу на нескольких строках, потому что вам не придется вводить ее заново.
- Таблица фиксируется абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменяются во время автозаполнения. Таким образом, расчет будет правильным каждый раз и на основе таблицы.
Как использовать функцию ВПР в Excel
Например, будет использоваться приведенная ниже таблица, где в первом столбце данные о наименовании товара и артикуле, а во втором данные о цене и все. Соответственно, мы должны добавить данные из второй части в первую согласно соответствующей статье.
Вы можете выполнить эту задачу, используя конструкцию:
=ОТКРЫТЬ(B2;H2:J26;2;0)
Где:
- B2 — источник, который будет сравниваться с первой таблицей;
- H2:J26 – область поиска;
- 2 — сюда входит номер столбца, который необходимо заменить при нахождении совпадения;
- 0 — отправляет точное значение, при выборе 1 будет передаваться приблизительное значение.
Как видите, система смогла найти указанный артикул и установить цену.
Теперь вы хотите растянуть формулу, но она будет прокручиваться вниз, изменяя область поиска, поэтому вам нужно заключить ее в знак доллара ($). Для этого перед каждым указанием столбца и строки ставим $ и растягиваем конструкцию.
=ОТКРЫТЬ(B2;$H$2:$J$26;2;0)
Все готово.
Использование функции ВПР для работы с несколькими таблицами и другими функциями
В следующем примере давайте посмотрим, как еще мы можем использовать функцию для поиска и извлечения информации по критериям и объединить функцию с функцией ЕСЛИОШИБКА. Например, у нас есть два отчета — отчет о количестве товара и отчет о цене за единицу товара, который нам нужен для расчета себестоимости. Опять же, с небольшим объемом данных это можно сделать вручную, но когда у нас есть большой объем, функция ВПР поможет нам справиться с этим быстрее и эффективнее. В ячейке D3 начинаем писать функцию:
- B3 — критерий поиска данных.
- F3:G14 — область, в которой наша функция будет искать соответствие между критериями и данными в строке.
- Цифра «2» — это номер столбца с нужной нам по критерию информацией.
- Цифра «0» (или можно использовать слово «ЛОЖЬ») — для точности результатов.
Когда мы задаем формуле критерий поиска, она начинает поиск совпадений с верхней ячейки в первом столбце (шаг 1 на картинке). Затем функция «считывает» все критерии сверху вниз, пока не найдет точное совпадение (шаг 2). Когда ВПР дойдет до Hepilor, он посчитает нужное количество столбцов справа (шаг 3) и выдаст нам нужное значение критерия — цену 86,90 (шаг 4):
Но сейчас у нас есть данные только по первому критерию. Чтобы заполнить третий столбец D первой таблицы до конца, нужно просто скопировать функцию последнего критерия. Однако на этом этапе для корректной работы область, где производится поиск, должна быть зафиксирована, иначе матрица данных будет «скатываться» вниз и нам это не удастся. Для этого используем абсолютные ссылки на диапазон в ячейке D3 – выделяем курсором диапазон F3:G14 и нажимаем клавишу F4, после чего копируем формулу в конец таблицы:
В итоге получаем нужный нам результат:
Однако наш пример был основан на полном соблюдении критериев из обеих таблиц — одинаковое количество элементов, одно и то же имя. Но что, если, например, последние четыре позиции были удалены из отчета о ценах на пакеты? Тогда у нас будет ошибка #Н/Д в первой таблице в позициях, которые находятся на одной строке с искомым критерием:
Если вас не устраивает содержимое этой ячейки, вы можете заменить значение ошибки. Для этого мы объединяем функцию ВПР с функцией ЕСЛИОШИБКА. Синтаксис функции ЕСЛИОШИБКА (значение, значение_если_ошибка), поэтому значением будет используемая нами функция ВПР, а значением при ошибке будет то, что мы хотим видеть вместо #Н/Д, например дефис, но обязательно заключенный в цитаты:
В итоге мы получим красиво оформленную таблицу с нужным видом:
Использование приблизительного значения
Не всегда критерии поиска должны точно совпадать в таблицах. Иногда будет достаточно определенной площади, которая будет включать в себя нужный критерий. Например, у нас есть список сотрудников с показателями выполнения плана продаж и система мотивации, которая показывает нам, какой процент премии от зарплаты заработали сотрудники:
Как видите, размер премии зависит от диапазона по системе премирования, куда попадает показатель эффективности продаж конкретного сотрудника. Мы видим, что при выполнении плана менее чем на 100 % премия не начисляется, а при 107 % (выше 100 %, но менее 110 %) работник получает премию в размере 10 %. Описанные показатели премии нам необходимо ввести с помощью функции ВПР в графу «Премия» первой таблицы, только на этот раз критерий будет находиться в определенном диапазоне.
Для корректной работы убедитесь, что границы диапазонов во второй таблице в крайнем левом столбце расположены в порядке возрастания сверху вниз (шаг 1). Формула берет выбранный нами критерий и выполняет поиск в первом столбце второй таблицы (шаг 2) и перебирает все значения сверху вниз (шаг 3). Как только функция находит первое значение, превышающее критерий из первой таблицы, она «откатывается» (шаг 4) и считывает значение, соответствующее найденному критерию (шаг 5). Другими словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:
Итак, наша функция будет выглядеть так:
А результат использования функции ВПР с приблизительным поиском имеет такой результат:
Например, у сотрудника Ольги премия 0%, так как она выполнила 76% продаж, то есть перевыполнила план на 0%. А сотрудница Наталья сдала на 21% сверх нормы и получила вознаграждение в размере 20%, что мы и увидим, если сравним данные из двух таблиц самостоятельно.
Использование функции ВПР на этих примерах не заканчивается, есть много других задач, которые удобно решать с помощью этой функции. Он облегчает работу с большими объемами данных, минимизирует ошибки по сравнению с самостоятельными вычислениями, прост в понимании и использовании.
Быстрое сопоставление двух таблиц с ВПР в Эксель
Для получения второго показателя (веса) нужно добавить ту же формулу, но вместо 2 указать 3, так как этот показатель является третьим столбцом.
=ОТКРЫТЬ(B2;$H$2:$J$26;3;0)
Затем расширите формулу таким же образом.
Миссия выполнена.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы извлекли значения из таблицы, используя поиск с точным интервалом. Подходит для работы с титрами. Теперь давайте рассмотрим ситуацию, когда может потребоваться приблизительное отображение интервала.
Задача. Товар был доставлен в магазин. Вы должны назначить каждому продукту размер партии в зависимости от количества.
Товары те же, что и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейку «Пакет», как показано в предыдущем примере.
Разница в том, что теперь искомое значение является числом, а интервальный поиск — истинным, что означает приблизительный поиск. Оказывается, это результат:
Что случилось? Аргумент поиска интервала имеет значение 1, что означает, что формула ВПР ищет в таблице ближайшее меньшее значение поиска.
В нашем случае количество товара «Кофе» равно 380. ВПР принимает это число за искомое значение, после чего ищет ближайшее меньшее число в соседней таблице — число 300. В конце функция «вытягивает вверх» данные из противоположного столбца («Большой»). Если количество товара «Кофе» = 340 — это «крупная партия». Важно, чтобы крайний левый столбец в таблице, указанной в формуле, был отсортирован по возрастанию. В противном случае ВПР работать не будет.
Значения и данные во второй таблице отсортированы по убыванию — ВПР не работает
Как работает функция ВПР в Excel: несколько примеров для «чайников».
Предположим, нам нужно выбрать данные конкретного человека из списка сотрудников. Давайте разберемся, какие здесь есть тонкости.
Во-первых, нужно сразу определиться: нужен точный или приблизительный поиск. Ведь у них разные требования к подготовке исходных данных.
Использование точного и приблизительного поиска.
Посмотрите, какие результаты выборки цен мы получаем, используя приблизительный поиск по неупорядоченным данным.
Обратите внимание, что четвертый параметр равен 1.
Некоторые результаты верны, но в большинстве случаев они ошибочны. Функция продолжает просматривать данные в столбце D имен элементов, пока не встретит значение, превышающее значение, указанное в качестве критерия поиска. Затем она останавливается и возвращает приз.
Поиск цены египетских бананов закончился на первой позиции, так как сливы фиксируются на второй. И это слово по правилам алфавита ниже, чем «бананы египетские». Так что нет необходимости искать дальше. У нас получилось 145. И неважно, что это цена абрикосов. Охота за ценами на сливы продолжалась до тех пор, пока D15 не придумал слово ниже по алфавиту: яблоки. Мы остановились и взяли цену с предыдущей линии.
Посмотрите, как бы все сложилось, если бы все было сделано правильно. Мы просто делаем сортировку, как указано стрелкой.
Вы спросите: «Тогда зачем это неточное отображение, если с ним столько проблем?»
Он отлично подходит для выбора значений из определенных диапазонов.
Допустим, у нас есть скидка для клиентов в зависимости от количества купленных товаров. Вам нужно быстро рассчитать, сколько процентов будет выплачено за совершенную покупку.
Если у нас есть количество 11 единиц, то мы просматриваем столбец D, пока не встретим число больше 11. Это 20, и оно находится в строке 4. Мы останавливаемся здесь. Это означает, что наша скидка находится в строке 3 и равна 3%.
При работе с интервалами типа «от — до» этот прием вполне пригоден.
И еще один совет.
Применяйте именованный диапазон.
Чтобы упростить работу с формулами, вы можете создать именованный диапазон и ссылаться на него позже. В нашем случае назовем его «Данные сотрудника» (помните, что пробелы здесь не допускаются).
В ячейку B2 введем желаемую фамилию, а в ячейках C2:F2 запишем формулы:
=ПРОСМОТР($B$2;ДанныеСотрудника;2;ЛОЖЬ)
=ПРОСМОТР($B$2;ДанныеСотрудника;3;ЛОЖЬ)
=ПРОСМОТР($B$2;ДанныеСотрудника;4;ЛОЖЬ)
=ПРОСМОТР($B$2;Данные Сотрудника;5;ЛОЖЬ)
Как видите, отличаются они только номером столбца, из которого будет извлекаться нужная информация. Вы можете использовать 0 вместо FALSE.
Каковы преимущества здесь?
- Вы ослеплены буквами, цифрами и знаками доллара в общих адресных областях?
Формула именованного диапазона выглядит намного дружелюбнее, нагляднее и понятнее. Вместо скучных и безликих координат вы видите идентификаторы, которые вызывают у вас какие-то ассоциации. Согласитесь, «цена» или «цена» — это, вероятно, информация о ценах.
- Если по какой-то причине вам нужно изменить координаты области поиска, которые вы использовали в большом количестве формул — вам нужно исправлять каждую формулу или использовать функцию «Найти и заменить»? Согласитесь, это очень долго, кропотливо, возможны ошибки.
Используйте именованную область, просто нажмите
Меню — Формула — Менеджер имен.
Затем найдите нужную область в списке областей и настройте ее. Изменения будут автоматически применены ко всем формулам.
- При использовании обычных адресов нам всегда приходится думать о том, следует ли использовать относительную или абсолютную адресацию. Эта проблема не возникает при использовании именованных пространств.
Использование символов подстановки и другие тонкости критерия поиска.
Как и в предыдущих примерах, при вводе фамилии выполняется точный поиск. Но есть несколько вещей, о которых мы раньше не упоминали.
- Регистр букв не влияет на результат. Можете писать все заглавными буквами — ничего не изменится. Вы видите пример ниже.
- Если в списке есть люди с такой же фамилией, будет найден только первый. Как мы уже говорили ранее, как только находится что-то подходящее, процесс останавливается.
- Вы можете использовать подстановочные знаки * и ?. Напомню, что знак вопроса заменяет любой символ, а звездочка — любое количество символов (включая ноль). Мы упоминали о них в начале.
Это имеет смысл делать, если мы знаем только часть значения аргумента.
Но в то же время будьте осторожны — снова будет найдено только первое совпадающее совпадение, как показано на скриншоте. Это очень важное ограничение, которое необходимо учитывать.
Теперь посмотрим, как можно работать с подстановочными знаками, если условия выбора вводятся не вручную, а берутся из таблицы Excel.
Формула в ячейке F2 выглядит так:
=ПРОСМОТР(«*»&D2&»*»;$A$2:$B$7;2;0)
Здесь мы используем оператор «склеивания» строк &.
Конструкция «*»&D2&»*» означает, что к содержимому ячейки D2 с обеих сторон добавляются звездочки *. То есть ищем каждое вхождение этого слова — до и после него могут быть другие слова и символы. Как, например, произошло с продуктом «персик». Первый параметр в нашем случае будет выглядеть как «*peach*». При поиске такого дизайна в качестве приемлемой альтернативы будет определено «Консервированные персики (Турция)».
Использование нескольких условий.
Еще один простой пример для «чайников» — как использовать несколько условий при выборе нужного значения?
Предположим, у нас есть список имен и фамилий. Нужно найти нужного человека и показать сумму его дохода.
В F2 мы используем следующую формулу:
=ВПР(D2&» «&E2;$A$2:$B$21;2;0)
Давайте посмотрим, как работает ВПР в этом случае.
В начале формируем условие. Для этого с помощью оператора & «склейте» имя и фамилию вместе, вставив между ними пробел.
При этом не забудьте заключить пробел в кавычки, иначе Excel не воспримет его как текст.
Итак, в таблице доходов ищем ячейку с именем и фамилией, разделенными пробелом.
Далее все происходит по уже подготовленной схеме.
Вы можете попробовать сделать так, чтобы между именем и фамилией было больше пробелов. Заменяем пробел в формуле подстановочным знаком «*».
Заметно так — D2&»*»&E2
Но помните при этом, что совпадение имени и фамилии уже не будет абсолютно точным. Аналогичный пример мы оценили чуть выше.
Более сложные и точные способы работы с несколькими условиями мы рассмотрим отдельно. Смотрите ссылки в конце.
«Умная» таблица.
И еще одна рекомендация: используйте «умный» стол.
Может быть очень удобно сначала преобразовать справочную таблицу (прайс-лист) в «умную» с помощью команды Главная – Форматировать как таблицу в английской версии Excel, а затем указать во втором аргументе использовать имя созданного стол. Кстати, она будет присвоена ей автоматически.
В этом случае размер списка предметов с ценами нас больше не будет беспокоить в дальнейшем. Когда вы добавляете в прайс-лист новые товары или удаляете их, размеры «умного стола» будут подстраиваться сами собой.
Специальные инструменты для ВПР в Excel.
Несомненно, ВПР — одна из самых мощных и полезных функций Excel, но она также и одна из самых запутанных. Чтобы упростить работу, вы можете использовать надстройку Ultimate Suite for Excel с инструментом VLOOKUP Wizard, который может сэкономить вам много времени на поиск нужных данных.
Мастер ВПР — простой способ писать сложные формулы
Интерактивный мастер ВПР проведет вас через необходимые параметры конфигурации поиска, чтобы создать идеальную формулу для заданных вами критериев. В зависимости от вашей структуры данных он будет использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если ему нужно извлечь значения слева от столбца поиска.
Вот что вам нужно сделать, чтобы получить формулу для вашего задания:
- Запустите мастер с помощью кнопки Vlookup Wizard на ленте Ablebits Data.
- Выберите свою основную таблицу (Your table) и таблицу поиска (Lookup table).
- Укажите следующие столбцы (во многих случаях они выбираются автоматически):
- Ключевой столбец (key column) — находится в вашей основной таблице, содержит значения для поиска.
- Колонка Lookup — где будем искать.
- Столбец возврата — из него получаем значения.
- Нажмите кнопку Вставить).
Давайте посмотрим все это в действии.
Стандартный ВПР.
Запустите мастер ВПР. Указываем координаты основной таблицы и таблицы поиска, а также ключевой столбец (из которого берем значения для поиска), поисковый столбец (где будем их искать) и столбец результатов (из его, в случае успеха, берем соответствующее значение и вставляем в основную таблицу). Просто заполните все обязательные поля, как показано на рисунке ниже. Прописываем руками (или указываем мышкой) только области. Поля просто выбираются из выпадающего списка.
Как и в предыдущих примерах, наша задача — найти цену для каждого товара, извлекая его из прайс-листа. Если диапазон поиска (Цена) является крайним левым столбцом в диапазоне поиска, для точного совпадения вставляется обычная формула ВПР:
Вам не нужно ничего писать от руки.
После нажатия кнопки «Вставить» справа от столбца с названием продукта вставляется дополнительный, который озаглавлен так же, как и столбец результатов. Здесь будут записаны все найденные ценовые значения, причем в виде формулы. При необходимости его можно исправить или использовать в других таблицах.
«Левый» ВПР.
Когда столбец результатов (Цена) находится слева от области поиска (Цена), мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:
Левую ВПР мы рассмотрели в отдельной статье. Там же можно увидеть формулы для ручного ввода. Здесь мы получаем их автоматически, не погружаясь в тонкости синтаксиса и правильную орфографию.
Дополнительный бонус! При разумном использовании ссылок на ячейки результирующие формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновления ссылок.
Надеемся, что наша пошаговая инструкция по использованию функции ВПР в таблицах Excel была доступна и понятна даже «чайникам». Конечно, эти очень простые рекомендации можно использовать только в самых простых случаях. Все более сложное рассмотрим отдельно.
Примеры функции ВПР в Excel
Для следующих примеров использования функции ВПР мы возьмем немного другие данные.
Требуется составить цены из второй таблицы в первую. Код используется здесь как критерий. Шаги для расчета ВПР показаны ниже.
Вторая таблица меньше первой, т.е отсутствуют некоторые коды. Для отсутствующих элементов функция ВПР возвращает ошибку #Н/Д.
Кстати, появление таких ошибок можно использовать себе на пользу, когда нужно найти отличия в таблицах. Но скорее всего мешают ошибки.
Конструкция с функцией ЕСЛИОШИБКА
Наряду с функцией ВПР часто используется функция ЕСЛИОШИБКА, которая «подавляет» ошибки #Н/Д и вместо этого возвращает значение. Обычно это 0 или пусто.
Как видите, ошибок больше нет, а вместо них пустые ячейки.
Разные форматы критерия в таблицах
Одной из частых причин ошибок является несовпадение форматов критериев в двух таблицах. Текстовый и числовой форматы рассматриваются функцией ВПР как разные значения. Есть два варианта.
В первом случае критерии в первой таблице хранятся в виде чисел, а критерии во второй таблице — в виде текста.
В ячейках с числами, сохраненными в виде текста, в верхнем левом углу появляется зеленый треугольник. Вы можете выбрать все такие числа и выбрать Преобразовать в число из раскрывающегося списка.
Это решение используется довольно часто. Но не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-то базы 1С. В таких файлах вообще все сохраняется как текст. И если мы планируем постоянно использовать такие данные, поместите их в заранее подготовленную область, лучше, чтобы формулы работали без дополнительного вмешательства.
Автоматически изменить формат критерия во второй таблице нельзя, потому что ссылка ведет на всю выборку. Вам нужно смешать ссылку на критерий в первой таблице. Для этого нужно добавить функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ требует указания формата. Достаточно ввести формат #. Ниже фото готовой формулы.
Две ошибки по-прежнему связаны с тем, что этих продуктов нет во второй таблице. Чтобы подавить их, вы снова можете использовать функцию ЕСЛИОШИБКА.
Вторая ситуация заключается в том, что «текст» — это критерий из первой таблицы. Форматы опять не совпадают.
Как и в прошлый раз, внесем коррективы в функцию ВПР. Преобразование «текста» в «число» еще проще. Достаточно к ссылке критерия «текст» добавить 0 или умножить на 1.
Есть и третья, смешанная ситуация. Это гораздо реже. Это когда в первой и второй таблицах критерии хранятся и в виде числа, и в виде текста, перемешанного между собой. Здесь вы должны использовать сразу все функции, описанные выше: ЕСЛИОШИБКА, ТЕКСТ и +0. Сначала пишем ЕСЛИОШИБКА, а в качестве первого аргумента этой функции пишем ВПР с определенной конструкцией для изменения формата. Например ВПР с формулой ТЕКСТ. В качестве второго аргумента (то есть того, что должно быть в случае ошибки) пишем вторую ВПР с +0. Так что если ВПР с функцией ТЕКСТ не выдает ошибки, то все ОК. Однако если первая конструкция возвращает ошибку #Н/Д, функция ЕСЛИОШИБКА заменяет вторую конструкцию ВПР на +0. Другими словами, мы сначала заставляем все критерии быть текстовыми, а затем числовыми. Таким образом, ВПР проверяет оба формата. Один из них будет соответствовать формату другой таблицы. Немного громоздко, но в целом работает.
Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В этом случае всю формулу можно снова «обернуть» в ЕСЛИОШИБКА.
Функция СЖПРОБЕЛЫ для чистки текстового критерия
В качестве критерия рекомендуется брать уникальный код, где опечатки, характерные для текста, маловероятны. Но иногда кода все же нет и критерием является текст (названия организаций, имена людей и так далее). В этом случае возможны случайные орфографические ошибки. Одна из самых распространенных ошибок — лишние пробелы. Проблема просто решается с помощью функции TRIM для всех критериев. Вы можете сделать это внутри формулы ВПР, или вы можете сначала просмотреть все критерии в обеих таблицах. Кому как удобнее.
Подсчет номера столбца в большой таблице
Если во второй таблице много столбцов, да еще некоторые из них скрыты или сгруппированы, напрямую вычислить количество столбцов между критерием и искомыми данными очень сложно. Есть хитрость, позволяющая вообще не считать эти столбцы. Для этого при выборе второй таблицы смотрите в правый нижний угол выделенной области. Отображается подсказка о количестве выбранных строк и столбцов. Запоминаем количество столбцов и вставляем ВПР в формулу.
Отличная экономия времени.
Почему не работает функция ВПР
В этой части статьи мы рассмотрим, почему не работает функция ВПР и возможные ошибки в работе функции ВПР.
Тип ошибки | причина | Решение |
#СЕЙЧАС | Неправильное размещение искомого столбца | Столбец таблицы, по которому выполняется поиск, ДОЛЖЕН быть крайним левым.
|
#СЕЙЧАС | Площадь стола не фиксирована | Если первое значение отображалось правильно, а после подтягивания формулы ВПР в некоторых ячейках возникает ошибка #Н/Д и диапазон таблицы не фиксируется.
|
#СЕЙЧАС | Не удалось найти точное совпадение (если в интервальном сканировании выбран поиск точного значения (0) | – |
#СЕЙЧАС | Интервальный поиск ищет ближайшее значение (1), а таблица, в которой выполняется поиск, не сортируется. | Отсортируйте первый столбец таблицы в порядке возрастания. |
#СЕЙЧАС | Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы. | Используйте функции CLEAN или TRIM. |
#СВЯЗЬ! | Значение номера столбца больше, чем количество столбцов в таблице | Проверьте номер столбца, содержащего возвращаемое значение. |
#ИМЯ? | В формуле пропущены кавычки | Если вы используете текст вместо ссылки на ячейку в качестве значения поиска, вы должны заключить его в кавычки.
Например: =OPEN(«Товар 1», Цены!$A$2:$B$8,2,0) |