В эксель формула впр: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

Как работает функция ВПР в Excel. Подробно на примерах.

Количество просмотров: 1 273

Использование ВПР В Excel

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

Чтобы разобраться, как работает эта функция, надо немного отойти от Excel в сторону реальной жизни. Каждый из нас делает звонки. Неважно, со смартфона или айфона. Главное тут другое. Редко кто набирает нужный телефонный номер вручную. Наших контактов так много, что запоминать их нет смысла. Мы все эти номера и не запоминаем, а храним всю эту информацию в телефонной книжке нашего гаджета. И вот тут наступает интересный момент. При звонке мы выбираем ИМЯ АБОНЕНТА, с которым хотим соединиться по телефону. А вот сам звонок идет уже по его НОМЕРУ. Как это происходит? Да просто. Наш гаджет проверяет первый столбец телефонной книжки. Как только он находит выбранное нами ИМЯ АБОНЕНТА, он в строчке с найденным ИМЕНЕНМ переходит в столбец с НОМЕРАМИ и получает нужную информацию.

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

Схематично это выглядит следующим образом:

Синтаксис функции ВПР.

Как же использовать функцию ВПР в Excel? Для этого вначале разберемся в порядке указания данных при вводе ВПР, другими словами, в синтаксисе. Указывают по очереди, в скобках после названия, следующие исходные данные, или, по-научному, аргументы.

  1. Искомое значение. Именно его Excel будет искать в ПЕРВОМ столбце нашей таблицы. Тут важно отметить такие тонкости. Прежде всего это значение в первом столбце таблицы должно быть уникальным. Само название ВПР расшифровывается не как «Вертикальный Поиск Решения», как многие думают, а как «Вертикальный ПЕРВЫЙ Результат». Именно ПЕРВЫЙ. К примеру, в приведенной раньше на рисунке таблице нельзя для поиска использовать столбец с именем или фамилией. Они могут быть одинаковыми, а вот номер телефона всегда уникальный. Кстати, именно поэтому нельзя в телефонной книге на телефоне указать одинаковые имена для разных людей. Телефон просто не даст это сделать. Другая тонкость в том, что, как ни странно, но Excel будет искать как точное значение, так и близкое к нему. Другими словами, вовсе не факт, что ВПР найдет именно то значение, которое мы задали. Подробнее об этом поговори чуть-чуть попозже.
  2. Таблица поиска. Тут надо указать диапазон, в ПЕРВОМ столбце, или, если кому-то так проще, в ПЕРВОЙ КОЛОНКЕ которого Excel станет искать заданное нами искомое значение. В этой таблице может быть любое количество столбцов. Столбец может быть и только один. Обычно такое применяется, когда надо определить, присутствует ли наше значение в списке или нет. С другой стороны, в этом случае проще и быстрее использовать функции ПОИСКПОЗ или СЧЕТЕСЛИ.
  3. Номер столбца. Порядковый номер столбца нашей таблицы, из которого мы желаем получить результат. Еще раз обращаю внимание на этот аспект. Наше тестовое значение Excel ищет в первом столбце, а вот результат берет из этой же таблицы, но уже колонки с указанным нами номером. Еще раз отмечу – номер столбца задается ВНУТРИ таблица поиска. Именно поэтому на примере ниже третьим аргументом указана цифра «2», хотя если считать от начала листа, то колонка явно не будет третьей.
  1. Интервальный просмотр. Это если говорить официально. А если по-простому, то способ поиска, о чем уже говорилось. Есть два варианта.
  • Указать слово ИСТИНА, ввести цифру 1 или вообще ничего не указать. Тогда Excel будет искать как само значение, так и ближайшее к нему, но меньше. Значения в первом столбце нашей таблицы поиска должны располагаться по возрастанию. Данный вариант применяется, когда надо найти не конкретное значение, а обработать некий диапазон. На предыдущем рисунке обрабатываются диапазоны цен, для каждого из которых задан свой процент скидки.
  • Написать слово ЛОЖЬ или цифру 0. В этом случае будет идти точный поиск заданного значения. Именно таким образом по указанному номеру мы определили фамилию абонента.

Работа функции ВПР и возможные проблемы.

Часто на просторах интернета можно найти высказывание, что если задать четвертым аргументом значение ИСТИНА ИЛИ 1, то ВПР сработает быстрее. Это неправда. Посмотрите на скриншоты ниже. Обратите внимание, что быстрее оказался вариант, в котором в качестве интервального просмотра использована ЛОЖЬ.

 

На самом деле скорость зависит от расположения заданного нами искомого значения в таблице. Если оно ближе к началу, то быстрее сработает вариант с ЛОЖЬЮ, а если ближе к концу, то быстрее вариант с ИСТИНОЙ. Дело в самом поиске. Если мы укажем четвертым аргументом ИСТИНУ, то Excel ищет наше значение в первом столбце СНИЗУ ВВЕРХ. Если же выбрать вариант ЛОЖЬ, то поиск будет идти СВЕРХУ ВНИЗ.

При выборе варианта ИСТИНА Excel двигается по первому столбцу вверх до тех пор, пока не наткнется либо на указанное нами значение, либо на первое же значение, которое заведомо меньше указанного нами. Как только это произошло, поиск заканчивается.

Именно поэтому в примере ниже для директора Excel выводит оклад равный окладу бухгалтера, хотя вообще-то для директора значатся совсем другие цифры…

Еще раз повторю. Если мы указываем в конце истину, или единицу, то первый столбец в нашей таблице для поиска ОБЯЗАН быть по возрастанию. Именно с этим связана проблема на скриншоте выше.

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

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

Значение в столбце код есть, а ВПР не смог найти расчетную цену. Как же так? А все просто.

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

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

Работа функции ВПР и исправление ошибок.

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

Если же таблица уже готовая, проверьте, имеются ли ошибки при поиске значения, которое в ней явно присутствует. Обычно это как раз числа в виде текста. Если это так, добавьте в начало колонку, в которой принудительно переведите все значения в текст с помощью одноименной функции и используйте для поиска именно этот столбец. Как вариант, примените комбинацию ВПР и ЕСЛИОШИБКА. Например, в следующем примере Excel принудительно ищет заданное значение в первом столбце как число, а если это не удается, то уже как текст.

Обратите внимание, что несовпадение в форматах уже никак не помешало получить результат

Заключение. 

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

Остается только добавить, что, во-первых, в Excel имеется полностью идентичная по написанию и применению функции ВПР функция ГПР. Разница только в том, что если ВПР работает со столбцами, то ГПР работает со строками. Во-вторых, в английском варианте эти функции называются соответственно VLOOKUP и HLOOKUP. Ну и в-третьих, если ваше искомое значение не находится в самом первом столбце, к примеру, если вам в прайсе, скриншот которого приведен выше, надо по названию товара определить его код, то использование ВПР ничего не даст. Надо применять комбинацию функций ИНДЕКС или СМЕЩ с функцией ПОИСКПОЗ. ОБ этом мы поговорим немного попозже.

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

498-11cookie-checkКак работает функция ВПР в Excel. Особенности применения.no

Использование операторов вычисления в формулах Excel

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно. ..Меньше

Операторы определяют тип вычисления, которое необходимо выполнить для элементов формулы. Excel следует общим математическим правилам для вычислений, а именно: Скобки , Экспоненты , Умножение и деление и Сложение и вычитание или аббревиатура PEMDAS (Пожалуйста, извините, моя дорогая тетя Салли). Использование круглых скобок позволяет изменить порядок вычислений.

Типы операторов. Существует четыре различных типа операторов вычисления: арифметический , сравнение , конкатенация текста и ссылка .

  • Арифметические операторы

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

    93

    Арифметический оператор

    Значение

    Пример

    + (плюс)

    Дополнение

    =3+3

    – (знак минус)

    Вычитание
    Отрицание

    =3–3
    =-3

    * (звездочка)

    Умножение

    =3*3

    / (косая черта)

    Отдел

    = 3/3

    % (знак процента)

    Процент

    30%

  • org/ListItem»>

    Операторы сравнения

    Вы можете сравнить два значения с помощью следующих операторов. Когда два значения сравниваются с помощью этих операторов, результатом является логическое значение — либо ИСТИНА, либо ЛОЖЬ.

    Оператор сравнения

    Значение

    Пример

    = (знак равенства)

    равно

    =А1=В1

    > (знак больше)

    Больше

    =А1>В1

    < (знак меньше)

    Менее

    =А1<В1

    >= (знак больше или равно)

    Больше или равно

    =А1>=В1

    <= (знак меньше или равен)

    Меньше или равно

    =А1<=В1

    <> (без знака равенства)

    Не равно

    =А1<>В1

  • org/ListItem»>

    Оператор конкатенации текста

    Используйте амперсанд ( и ), чтобы объединить (объединить) одну или несколько текстовых строк для создания единого фрагмента текста.

    Текстовый оператор

    Значение

    Пример

    и (амперсанд)

    Соединяет или объединяет два значения для создания одного непрерывного текстового значения

    =»Север»&»ветер» приводит к «Борей».
    Где A1 содержит «Фамилия», а B1 содержит «Имя», = A1 &», «& B1 приводит к «Фамилия, Имя».

  • Эталонные операторы

    Объедините диапазоны ячеек для вычислений со следующими операторами.

    Справочный оператор

    Значение

    Пример

    : (двоеточие)

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

    В5:В15

    , (запятая)

    Оператор объединения, объединяющий несколько ссылок в одну ссылку

    =СУММ(B5:B15;D5:D15)

    (пробел)

    Оператор пересечения, который создает одну ссылку на ячейки, общие для двух ссылок

    В7:Д7 С6:С8

Формулы обновления Excel — формулы работают неправильно

Excel без формул неполный. Я предполагаю, что Excel станет ничем, когда больше не будет доступных формул. На самом деле, Формулы делают Excel мощным и доступным для всех. Вы когда-нибудь задумывались о том, что будете делать, если формулы Excel внезапно перестанут работать?

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

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

Вот основные моменты, на которые следует обратить внимание:

  • Формулы Excel не работают: в этом случае формула вернет ошибку или неправильный результат.
  • Формулы Excel не обновляются: в этом случае формула отображается как старое значение после обновления значений зависимых ячеек.
  • Формула Excel не вычисляется: в этом случае в ячейке отображается формула, а не результат.

Формулы Excel не работают

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

Соответствие всем открытым и закрытым скобкам

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

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

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

Добавить все необходимые аргументы в функцию Excel

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

Если требуемый аргумент не добавлен в формулу, вы немедленно получите уведомление с предупреждением «Вы ввели слишком мало аргументов для этой функции». С другой стороны, если вы ввели больше аргументов, чем требуется, вы снова получите уведомление с сообщением об ошибке «Вы ввели слишком много аргументов для этой функции».

Избегайте добавления более 64 функций в формулу

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

  • Excel 2003 или более ранняя версия позволяет вы добавляете до 7 вложенных функций.
  • Excel 2016, 2013, 2010 и 2007 позволяют добавлять до 64 вложенных функций.
  • Числа не нужно заключать в двойные кавычки

Имейте в виду, что когда значение заключено в двойные кавычки, Excel будет рассматривать его как текстовую строку. Это просто означает, что когда вы добавляете формулу типа = ЕСЛИ (A1> 0, «1»), в Excel число 1 будет рассматриваться как текст, и поэтому вы не можете использовать возвращенные единицы в других вычислениях.

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

=ЕСЛИ(A1>0, 1)

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

Формулы обновления Excel с помощью ленты

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

Только на текущем листе

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

  • Откройте вкладку Формулы.
  • Выберите «Расчетный лист» в меню ленты.
  • Будет обновлен весь расчет текущего листа.
  • Нажав на опцию Рассчитать лист, вы найдете окончательный лист:

Обновить всю книгу

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

  • Откройте вкладку «Формулы».
  • Нажмите кнопку «Рассчитать сейчас», и все формулы во всей книге будут обновлены.

После обновления всей книги все значения будут обновлены.

Сочетания клавиш для обновления формул Excel:

Подобно другим функциям, в Excel также есть сочетания клавиш для обновления формул.

Читайте также: