Как сделать впр в excel понятная инструкция?

15 ответов на вопрос “Как сделать впр в excel понятная инструкция?”

  1. TOXIPOCA Ответить

    Проблема
    Возможная причина
    Неправильное возвращаемое значение
    Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.
    #Н/Д в ячейке
    Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.
    Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.
    Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.
    #ССЫЛКА! в ячейке
    Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.
    Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.
    #ЗНАЧ! в ячейке
    Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.
    Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.
    #ИМЯ? в ячейке
    Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(“Иванов”;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате “Иванов” и никак иначе.
    Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.
    Ошибки #ПЕРЕНОС! в ячейке
    Это конкретная ошибка #SPILL! Обычно это означает, что формула полагается на неявное пересечение для искомого значения и использует в качестве ссылки весь столбец. Например, = ВПР (а:а, а:к, 2; ложь). Проблему можно устранить, заменив ссылку для подстановки с помощью оператора @ следующим образом: = ВПР (@ а:а, а:к, 2, ложь). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку, а не на весь столбец: = ВПР (a2; а:к; 2; ложь).

  2. Adoragelv Ответить

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

    Синтаксис и описание функции ВПР в Excel

    Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.
    Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.
    Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).
    Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.
    Синтаксис функции ВПР выглядит следующим образом:
    ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
    Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний – необязательный.
    искомое_значение – это значение для поиска.
    Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Excel. Например:
    Поиск числа: =ВПР(40; A2:B15; 2) – формула будет искать число 40.
    Поиск текста: =ВПР(«яблоки»; A2:B15; 2) – формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
    Поиск значения из другой ячейки: =ВПР(C2; A2:B15; 2) – формула будет искать значение в ячейке C2.
    таблица – это два или более столбца данных.
    Помните, что функция ВПР всегда ищет искомое значение в первом столбце таблицы. Ваш таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру, что означает, что прописные и строчные буквы считаются идентичными.
    Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A – это первый столбец таблицы A2: B15.
    номер_столбца – номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.
    Самый левый столбец в указанной таблице равен 1, второй столбец – 2, третий – 3 и т. д.
    Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).
    4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

    Функция ВПР в Excel примеры

    Теперь давайте рассмотрим несколько примеров использования функции ВПР для реальных данных.

    Функция ВПР на разных листах

    На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.
    Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.
    Конечно, вам не нужно вводить имя листа вручную. Просто начните вводить формулу, и когда дело дойдет до аргумента таблица, переключитесь на рабочий лист поиска и выберите диапазон с помощью мыши.
    Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены»:
    =ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

    Функция ВПР в Excel – Функция ВПР на разных листах
    Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.

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

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

    Функция ВПР в Excel – Присвоение имени диапазону
    Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:
    =ВПР(«Продукт 1»;Продукты;2)

    Функция ВПР в Excel – Пример функции ВПР с именем диапазона
    Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется, когда формула копируется в другие ячейки, и вы можете быть уверены, что ваш диапазон поиска всегда останется верным.
    Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» –> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

    Функция ВПР в Excel – Пример функции ВПР с именем таблицы
    Полная формула может выглядеть примерно так:
    =ВПР(“Продукт 1”;Таблица6[[Продукт]:[Цена]];2)
    или даже =ВПР(“Продукт 1”;Таблица6;2).
    Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменятся независимо от того, где копируется формула ВПР.

    Функция ВПР с несколькими условиями

    Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

    Функция ВПР в Excel – Таблица исходных данных
    Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
    Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.
    Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:
    =B2&C2
    При помощи этой формулы мы сцепляем значение столбца «Продукт» и «Тип». Заполняем все ячейки.
    Теперь таблица для поиска выглядит следующим образом:

    Функция ВПР в Excel – Добавление вспомогательного столбца
    Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:
    =ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)
    Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

    Функция ВПР в Excel – Пример ВПР с несколькими условиями
    Теперь разберем ошибки функции ВПР.

    Почему не работает функция ВПР

    В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.
    Тип ошибки
    Причина
    Решение
    #Н/Д
    Неверное расположение столбца, по которому происходит поиск
    Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым.
    Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы.
    Или создайте вспомогательный дублирующий столбец, слева в таблице.
    #Н/Д
    Не закреплен диапазон таблицы
    Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен.
    Используйте абсолютные ссылки ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон.
    Или используйте именованный диапазон
    #Н/Д
    Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)
    –
    #Н/Д
    В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица, по которой происходит поиск не отсортирована.
    Отсортируйте первый столбец таблицы по возрастанию наименований.
    #Н/Д
    Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.
    Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.
    #ССЫЛКА!
    Значение номер столбца превышает число столбцов в таблице
    Проверьте номер столбца, содержащий возвращаемое значение.
    #ИМЯ?
    В формуле пропущены кавычки
    Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки.
    Например:
    =ВПР(“Продукт 1”; Цены!$A$2:$B$8;2;0)
    Надеюсь, что теперь даже для чайников функция ВПР в Excel будет понятна.

  3. Fate Ответить

    Как удалить строку в excel – подробная инструкция
    Сумма в excel, подробная инструкция
    Графика с помощью ВПР
    Из PDF в Excel – инструкция по конвертации
    Допустим, у вас есть две таблицы с ценами на запчасти, первая за 2015 год, вторая за 2016. Вы хотите сравнить цены каждой запчасти по прошествии года.
    Таблица с ценами 2015 года:

    Таблица с ценами 2016 года:

    Казалось бы, что сложного в этой задаче – просто упорядочим обе таблицы по имени запчасти и скопируем данные из одной таблицы в другую. Но проблема в том в 2016 году появились новые запчасти и данные просто не совпадут. Может появиться желание вручную перенести данные. В данном примере это легко выполнимо, а представьте, что таких строк у вас не 10, а 10 000, к примеру. Именно для решения таких задач функция ВПР подходит просто идеально.

    Как использовать функцию ВПР в Excel

    Скорее всего, ваши таблицы будут в разных файлах. Для удобства, скопируйте их на разные листы одной книги, как в примере выше. Нам нужно чтобы в таблице 2016 года в третьем столбце появились данные из 2015 года. Выбираем ячейку С2 и пишем «=ВПР(» без кавычек, Excel сразу выведет подсказку:

    Рассмотрим синтаксис команды «ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])» подробнее.
    Искомое значение – значения, которые мы будем искать в другой таблица, в нашем случае это ячейки А2-А10.

    Таблица – часть второй таблицы (в нашем случае 2015 года) где мы будем искать искомые значения. В нашем случае переходим на вкладку 2015 и выделяем столбцы A и B.

    Номер столбца – столбец из которого будут подставляться данные, в нашем случае это «2».
    Интервальный просмотр – искать точное совпадение или примерное. Нам нужно точное, поэтому указываем «ЛОЖЬ».
    Итого, конечная формула получается такая: «=ВПР(A2;’2015′!A:B;2;ЛОЖЬ)». То есть, еще раз, как работает формула на конкретном примере: берется слово МОТОР из таблицы 2015 и ищется в таблице 2016, после чего берется число 1000 из второго столбца и подставляется в таблицу 2016.
    Остается только растянуть формулу на всю длину столбца, ну и в нашем случае добавить столбец с разницей.

    У двух значений написано #Н/Д – это значит «Нет данных», это как раз те позиции, которых не было в 2015 году.
    Вот и все! Если будут вопросы – пишите! Удачи!

  4. PoZiTif4iK Ответить

    Принцип работы функции прост. Первый аргумент содержит критерий для поиска. Как только найдено совпадение в таблице (второй аргумент), то из нужного столбца (третий аргумент) найденной строки берется информация и подставляется в ячейку с формулой.
    Простое применение ВПР – поиск значений в таблице Excel. Он имеет значение в больших объемах данных.

    Найдем количество фактически выпущенной продукции по названию месяца.
    Результат выведем справа от таблицы. В ячейке с адресом H3 будем вводить искомое значение. В примере здесь будет указываться название месяца.
    В ячейке H4 введем саму функцию. Это можно делать вручную, а можно воспользоваться мастером. Для вызова поставьте указатель на ячейку H4 и нажмите значок Fx около строки формул.

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

    Появится окно ВПР для таблицы Excel.

    Чтобы указать первый аргумент (критерий), поставьте курсор в первую строку и щелкните по ячейке H3. Ее адрес появится в строке. Для выделения диапазона поставьте курсор во вторую строку и начните выделять мышью. Окно свернется до строки. Это делается для того, чтобы окно не мешало видеть Вам весь диапазон и не мешало выполнять действия.

    Как только Вы закончите выделение и отпустите левую кнопку мыши, окно вернется в свое нормальное состояние, а во второй строке появится адрес диапазона. Он вычисляется от левой верхней ячейки до правой нижней. Их адреса разделены оператором «:» – берутся все адреса между первым и последним.

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

    Последнюю строку оставьте пустой. По умолчанию значение будет равно 1, посмотрим, какое значение выведет наша функция. Нажмите «ОК».

    Результат обескураживает. «Н/Д» означает некорректные данные для функции. Мы не указали значение в ячейке H3, и функция ищет пустое значение.

    Введем название месяца и значение изменится.

    Только оно не соответствует действительности, ведь настоящее фактическое количество выпущенной продукции в январе равно 2000.
    Это влияние аргумента «Условие поиска». Изменим его на 0. Для этого поставьте указатель на ячейку с формулой и снова нажмите Fx. В открывшемся окне введите «0» в последнюю строку.

    Нажимайте «ОК». Как видим, результат изменился.

    Чтобы проверить второе условие из начала нашей статьи (среди формул функция не ищет) изменим условия для функции. Увеличим диапазон и попробуем вывести значение из столбца с вычисляемыми значениями. Укажите значения как на скриншоте.

    Нажмите «Ок». Как видите, результат поиска оказался 0, хотя в таблице стоит значение 85%.

    ВПР в Excel «понимает» только фиксированные значения.

    Сравнение данных двух таблиц Excel

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

    На двух листах мы имеем одинаковые таблицы с разными данными.

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

    В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.

    Растяните формулу на весь столбец.

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

    После копирования Вы получите сводный отчет с двух листов.

    Подстановка данных из одной таблицы Excel в другую

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

    И в ячейку G3 поместите функцию ВПР. Диапазон опять берем с соседнего листа.

    В результате столбец второй таблицы будет скопирован в первую.

    Вот и вся информация о незаметной, но полезной функции ВПР в Excel для чайников. Надеемся, она поможет Вам при решении задач.

  5. Rockbreaker Ответить

    Для студентов, экономистов и всех тех, кто в ходе своей работы использует достаточно большие массивы данных (таблицы), была создана очень полезная функция Excel вертикальный поиск решения (ВПР). Она применяется для поиска нужного значения в таблицах и имеет 4 аргумента:
    Искомое_значение – содержимое ячейки, которое необходимо найти в крайнем левом столбце таблицы.
    Таблица – это массив данных, в рамках которого происходит поиск нужного значения. Координаты задаются адресами верхней левой и нижней правой ячеек в Аргументах функции.
    Номер_столбца – аргумент отсчитывается от крайнего левого столбца Таблицы (предыдущий параметр функции). Он всегда задается числом.
    Интервальный_просмотр – необязательный параметр, определяющий вид поиска. Может принимать два значения: ЛОЖЬ и ИСТИНА. ЛОЖЬ подразумевает поиск точного совпадения, ИСТИНА задает интервальный поиск.

    Поиск значения в таблице с помощью функции ВПР

    Рассмотрим, как работает функция ВПР, на простом примере. Допустим, имеется таблица с двумя столбцами: в первом проставлены номера по порядку, во втором содержатся фамилии. Необходимо по номеру найти фамилию. Если в таблице тысячи строк или нужно отыскать много фамилий, ручной процесс будет долгим и трудоемким. В таком случае выручит функция ВПР в Экселе. Пошаговая инструкция ее применения выглядит следующим образом:
    Заносим в ячейку вне таблицы, например D1, номер, по которому необходимо найти фамилию.
    Ставим курсор в ячейку D2 и нажимаем значок fx (располагается слева от строки формул).

    В окне Мастера функций находим категорию Ссылки и массивы, выбираем функцию ВПР и нажимаем ОК

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

    Аргументы функции


    Искомое_значение: указываем ячейку D1, где хранится искомый номер.
    Таблица: задаем ее координатами левой верхней ячейки D3 и правой нижней E7.
    Номер_столбца: в исходной таблице столбец с фамилиями является вторым.
    Важно
    Номер_столбца отсчитывается относительно крайнего левого столбца исходной таблицы, он может не совпадать с номером столбца Excel.
    Интервальный_просмотр: записываем ЛОЖЬ, что означает поиск точного совпадения. Значение ИСТИНА задает поиск в интервале, такой вариант рассмотрим ниже.
    Важно
    Если аргумент Интервальный_просмотр не указан, то по умолчанию будет осуществляться поиск в интервале.
    После указания всех аргументов получаем формулу вида =ВПР(G3;D3:E7;2;0). Таким образом, функция ВПР возьмет значение Климов из ячейки G3, найдет в первом столбце исходной таблицы точное совпадение и выведет значение из второго столбца. В данном случае это 12. Меняя значение ячейки G3, можно получать различные значения

    Поиск в интервале для функции ВПР

    Рассмотрим подробнее поиск в интервале для функции ВПР. Например, есть таблица с фамилиями, а вместо номеров проставлены дробные коэффициенты. При этом данные отсортированы по возрастанию

    Алгоритм действий

    В ячейку D1 заносим дробное число, по которому будет осуществляться поиск.
    В ячейке D2 с помощью Мастера функций находим ВПР и по аналогии с предыдущим примером указываем все аргументы, кроме Интервального_просмотра. В нем пишем ИСТИНА.
    Получится формула =ВПР(D1;A1:B6;2;ИСТИНА).
    При указании значения ИСТИНА в аргументе Интервальный_просмотр функция ВПР будет работать так:
    если вводить в ячейку D1 любые значения в диапазоне от 4,1 до 5,02, не включая само число 5,02, то результат будет один – Сидоров;
    при вводе чисел меньше 1,256, будет выдаваться сообщение об ошибке #Н/Д;
    если в D1 будет значение больше, чем 10,45, функция вернет последнюю фамилию в списке.
    Важно
    Если таблица отсортирована неправильно, то и функция ВПР не будет работать корректно.
    Если бы таблица была отсортирована правильно, то значение 8 в ячейке D1 привело бы к выводу результата Симонова по итогам работы функции ВПР в ячейке D2. Поэтому поиск в интервале следует использовать крайне осторожно. В большинстве случаев аргументу Интервальный_просмотр лучше присваивать значение ЛОЖЬ.

    Объединение таблиц с помощью функции ВПР

    Помимо поиска нужных значений, функция ВПР в Эксель может применяться для объединения таблиц. Рассмотрим ее использование на примере двух таблиц. Первая содержит номера, фамилии и суммы зарплат, она размещается на Листе1. Во вторую внесены номера, фамилии, суммы премий, она находится на Листе2. Порядок расположения номеров и фамилий по строкам в таблицах может различаться.
    Чтобы в таблицу на Листе1 быстро перенести значения премий сотрудников, используем функцию ВПР:
    Устанавливаем курсор в ячейку D1 Листа1 и запускаем Мастер функций. В аргументе Искомое_значение указываем ячейку A1.
    В аргументе Таблица в данном случае необходимо указать данные массива с Листа2, так как именно в этой таблице функция должна находить и брать информацию. Для этого, не закрывая окно Аргументы функций, переходим на Лист2 и привычным способом отмечаем границы таблицы (рис. 11).
    Важно
    Чтобы не было проблем с копированием формулы в дальнейшем, адреса угловых ячеек таблицы делаем абсолютными с помощью значка $. То есть вместо A1:C6 пишем $A1:1:1:C$6.
    В аргументе Номер_столбца указываем число, соответствующее номеру столбца с информацией о премии в таблице на Листе2. В нашем примере это 3.
    В Интервальный_просмотр пишем логическое значение ЛОЖЬ, чтобы функция искала точно совпадающие значения.
    Подтверждаем ввод аргументов нажатием кнопки ОК. В итоге в ячейке D1 будет записана формула =ВПР(A1;Лист2!$A1:1:1:C$6;3;ЛОЖЬ).
    Выделяем ячейки от D1 и ниже, нажимаем Ctrl+D для копирования формулы. При этом будет меняться только первый аргумент: A2, A3 и так далее.
    Таким образом, значения из таблицы на Листе2 перенесены на Лист1.
    Очевидно, что функция ВПР в Excel может применяться при решении таких трудоемких задач, как поиск информации и объединение таблиц. Мы рассмотрели ее работу на простых примерах, однако с помощью ВПР можно производить гораздо более сложные расчеты быстро и качественно.
    Подробнее о функциях программы Excel и их применении читайте здесь. Ответы на другие вопросы о работе в Excel читайте на Справочнике!

  6. Mera Ответить

    В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.
    Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке).
    Примечание. Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).

    Задача2. Поиск ближайшего числа

    Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.

    Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:
    Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
    Ключевой столбец должен быть обязательно отсортирован по возрастанию;
    Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.
    Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)
    Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)
    Как видно из картинки выше, ВПР() нашла наибольшую цену, которая меньше или равна заданной (см. файл примера лист “Поиск ближайшего числа”). Это связано следует из того как функция производит поиск: если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его. Как следствие, если искомое значение меньше минимального в ключевом столбце, то функцию вернет ошибку #Н/Д.
    Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.
    Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО. Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.
    Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).
    Примечание: Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА – последнее (см. картинку ниже).

    Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().

  7. Mixed Tv Vm Ответить


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

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

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

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

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

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

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

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

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

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

  8. Вампирша Ответить

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

    Для решения задачи нам необходимо подтянуть цены на товары из таблицы “Прайс-лист” в столбец “Цена за кг” таблицы “Заказы”. Т.е. подставить цены, которые соответствуют каждому товару из нижней таблицы в верхнюю.
    В Эксель, чтобы решить этот пример, есть несколько возможностей, одна из которых – использование функции ВПР. Становимся на первую ячейку, где необходимо подставить данные, и начинаем писать функцию, начиная как обычно со знака равенства.

    Должна появится подсказка, согласно которой мы будем задавать ей аргументы. Если расшифровать работу ВПР с технического языка на нормальный, то функция VLOOKUP будет искать выбранный нами товар из таблицы “Заказы” в крайнем левом столбце таблицы “Прайс-лист” и, если найдет это товар, выводить значение его цены, которое находится на той же строке, что и найденный товар. Коротко это выглядит как на скриншоте ниже.

    У функции ВПР 4 аргумента. Первым аргументом, который мы подставим в формулу, является то самое искомое значение, цену для которого нужно найти во второй таблице “Прайс-лист”. После написания названия функции и добавления открывающей скобки, щелкаем по искомой ячейке и добавляем ее аргументом. В моем случае это ячейка “E4” со значением “Яблоки”. Далее ставим разделитель – точка с запятой.

    Вторым аргументом является таблица, в которой мы ищем значение и из которой подтягиваем цену. Здесь нужно просто выделить таблицу “Прайс-лист” и лучше, чтобы вы не зацепили лишних ячеек (шапка, пустые ячейки и просто лишняя информация). В конце не забываем ставить точку с запятой.

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

    Третий аргумент – номер столбца в таблице “Прайс-лист”, из которого мы хотим подтянуть нужное нам значение. В нашм примере все просто. В первом столбце мы ищем значение, а из второго столбца подтягиваем цену. Ставим цифру 2, а после точку с запятой.

    Последний параметр называется “Интервальный просмотр” – это логический аргумент и может принимать только 2 значения: 0 или 1 (включено/выключено). Данным значением мы определяем, точно, цифра 0, или приблизительно, цифра 1, мы ищем наименование в прайс-листе. В случае с текстовыми наименованиями лучше использовать точный поиск (цифра 0), потому что приблизительный более менее точно работает в случае ячеек с числами. Вводим интервальный просмотр как 0 и закрываем скобку. После этого нажимаем клавишу Enter, чтобы формула сработала. В итоге мы получим ячейку с ценой, подтянутой из таблицы прайс-листа.


    В колонку “Итоговая стоимость” прописываем простую формулу умножения веса партии на цену за кг.

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

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

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

    Еще одним полезным применением ВПР является ее использование для сравнения двух таблиц с целью нахождения совпадений или различий. Например, если в одной из таблиц убрать несколько строк, то полного совпадения в данных уже не будет и в некоторых ячейках появится надпись #Н/Д. Эта надпись говорит о том, что значения из таблицы с заказами не были найдены в таблице прайс-лист, что соответственно приводит к ошибке.

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

    Дополнительно у нас появляется критерий “от и до”, который говорит о размере партии. В примере мы будет отталкиваться от ее веса. Например, если Excel показать вес партии 15 кг, то он будет понимать, что это мелкая партия.
    Давайте разбираться как это работает. Начинаем писать функция ВПР, но в конце формулы ставим не 0, а 1. В аргументе, где нужно указать таблицу с ячейками, мы не будем указывать столбец с параметром “От и до”, так как Excel такую запись не поймет. Взамен этого мы вводим поле критерий, который и будет определять начало и конец диапазона весов. В конце нажимаем клавишу Enter и протягиваем на все ячейки.


    Есть некоторые особенности работы, которые необходимо пояснить, чтобы было понятно, что здесь произошло. Мы видим, что при определенном весе функция вытащила из второй таблицы определенную партию. Чтобы более точно понять логику, давайте посмотрим на вес, к примеру, в 15 кг. В таблице с размерами партий, такого критерия нет. Но когда мы ставим интервальный просмотр в значение 1, функция будет вытаскивать значение, которое является ближайшим меньшим. При весе партии в 15 кг, ВПР вытащит из таблицы значение с критерием 10. Если вес партии будет 47 кг, то функция вытащит значение с критерием 30, которое будет ближайшим и меньшим.

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

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

  10. VideoAnswer Ответить

Добавить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *