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

Функция ВПР в Экселе для чайников

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

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

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

Итак, давайте приступим к разбору функции ВПР в Экселе для чайников на моём примере.

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Там, где написано категория выбираем «Ссылки и массивы». В списке выделите ее и нажимайте «OK».

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

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

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

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

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать надо точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «OK».

Тут обратите внимание на следующее, если работаете с числами и указываете «Истина», то вторая таблица (это наш прайс) конечно должна быть отсортирована по возрастанию. К примеру, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, ввиду того, что она думает, что ниже числа лишь больше.

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

=ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)

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

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

Сейчас можем рассчитать столбец Стоимость. Растянем формулу на необходимое количество строчек.

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

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 в данный момент $I$2:$J$14.

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

В открывшемся окне «Тип данных» будет «Список», ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «OK».

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

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1).

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

Дальше указываем столбец (2), данные из которого надо вытянуть, пишем ЛОЖЬ, для поиска точных совпадений, и щёлкаем «OK».

Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.

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

Жмем по любой ячейке в столбце D и вставляем один новый.

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

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1».

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

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12». Выделите строчку и нажмите «F4», чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2) и пишем «ЛОЖЬ».

В конце нажмите кнопку «OK».

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

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

Формула округления в Excel: краткое руководство

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

Формула округления в Excel — округление вверх

То же, что и ОКРУГЛ, но всегда округляет числа в меньшую сторону. Для округления в меньшую сторону используется следующий метод: 3,14159.до трех знаков после запятой: = ОКРУГЛЕНИЕ ВНИЗ (3,14159,3), что даст 3,141.

Формула округления в Excel для округления поможет пользователю округлить десятичное значение до ближайшего большего целого числа. Например, 1,8930 будет округлено до ближайшего большего значения, равного 1,9.

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

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

Создайте новый столбец и переименуйте его как значение «процент округления».

Теперь выберите первую ячейку и введите формулу = ОКРУГЛ(M2,2), чтобы округлить первые два десятичных разряда, как показано на рисунке ниже.

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

Нажмите клавишу ВВОД и перетащите ячейку по всему столбцу, чтобы применить формулу ко всему столбцу, как показано на рисунке ниже.

Формула округления в Excel — округление до

Формула округления в Excel для округления в меньшую сторону поможет пользователю округлить десятичное значение до ближайшего меньшего целого числа. Например, 1,8630 будет округлено до ближайшего большего значения, равного 1,8.

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

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

Создайте новый столбец и переименуйте его как значение округления в процентах.

Теперь выберите первую ячейку и введите формулу = ОКРУГЛ(M2,2), чтобы округлить первые два десятичных разряда в меньшую сторону, как показано на рисунке ниже.

Выберите функцию округления из предложенных вариантов.

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

На этом вы подошли к концу этого руководства «Все, что вам нужно знать о формуле округления в Excel?»

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков. Зарегистрируйтесь сейчас!

Следующие шаги

«Диаграммы в Excel» могут стать вашей следующей остановкой. Создание визуально привлекательных диаграмм для представления данных является важным методом бизнес-аналитики.

Вы ищете дополнительную информацию о Microsoft Excel или интересуетесь бизнес-аналитикой?

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

Есть к нам вопросы? Пожалуйста, не стесняйтесь сообщить нам об этом в разделе комментариев ниже, и наша команда экспертов будет рада решить все ваши вопросы.

Основы формул Excel

, которые должен знать каждый владелец бизнеса

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

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

Формулы Excel

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

К счастью, все формулы в Excel построены одинаково. Все они начинаются со знака равенства (=), за которым следует один или несколько элементов. Знак равенства просто сообщает Excel, что мы сохраняем формулу в этой конкретной ячейке.

Каждый из элементов после знака равенства может быть любым или всеми из следующих:

1. Константы
2. Операторы
3. Ссылки
4. Функции

1. Константа

Константа — это статическое значение, а не результат вычисления. Например, числа 1, 2, 3, 4 и 5 или даже текст типа «ExcelSuperSite» — все это примеры констант, т. е. они не изменяются сами по себе и не являются результатом вычислений.

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

2. Операторы

Операторы определяют, какой тип операции (вычисления) вы хотите выполнять над элементами в формулах. Существует четыре различных типа операторов, которые вы можете использовать:

а. Арифметика
б. Сравнение
c. Текст
д. № по каталогу

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

92

б. Операторы сравнения используются для сравнения двух элементов. Результатом сравнения является логическое значение TRUE или FALSE.

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

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

Точно так же, как при расчете обычной математической задачи, Excel при выполнении расчетов следует основным математическим принципам. Excel начинается слева и работает справа, подчиняясь правилам математического приоритета — B O M D A S (скобки — порядок — умножение — деление — сложение — вычитание).

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

Например, следующая формула дает 7, поскольку Excel выполняет умножение перед сложением (в соответствии со стандартными математическими правилами приоритета). Формула умножает 2 на 3, а затем добавляет 1 к результату.

=1+2*3 = 7

Однако, если мы добавим круглые скобки, чтобы изменить порядок вычислений, мы можем получить результат 9вместо. Excel добавляет 1 к 2, а затем умножает этот результат на 3, чтобы получить в общей сложности 9.

= (1+2) * 3 = 9

3. Ссылки

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

Excel ссылается на ячейки на листе, используя букву столбца и номер строки конкретной ячейки. Например, ссылка на ячейку C5 относится к ячейке на пересечении столбца C и строки 5.

Ссылка на ячейку в Excel

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

Чтобы попытаться объяснить это немного подробнее, рассмотрим следующий пример:

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

Простая формула в Excel с использованием констант

Вот это было просто, так зачем вам использовать в формулах ссылки вместо констант? Рассмотрим следующее расширение вышеизложенного. Вместо списка из 5 чисел для расчета 10% теперь у вас есть список, скажем, из 50 чисел, и, чтобы сделать вещи немного интереснее, вы хотите рассчитать диапазон процентов, скажем, 5%, 10% и 15%.

Это МОЖЕТ быть выполнено точно так же, как в приведенном выше примере, но для этого потребуется много ручного редактирования всех ваших формул. Нет ничего плохого в том, чтобы много работать, но я также предпочитаю работать с умом. Итак, давайте начнем заставлять Excel работать на нас, а не наоборот, и делать всю работу за нас.

Прежде чем мы изменим формулы в столбце C, давайте сначала введем константу в процентах в ячейку C1 [щелкните ячейку C1, а затем введите «10%» {без кавычек}, затем нажмите клавишу ввода]

В ячейке C4 введите:

= B4 * C1, затем нажмите клавишу ввода

Эта формула говорит о том, что нужно взять значение того, что находится в ячейке B$, а затем умножить его на значение того, что находится в ячейке C1.

Теперь скопируйте эту формулу во все ячейки столбца C.

Формула с использованием ссылок в Excel

Введя формулу таким образом, мы можем легко обновить процентную константу в ячейке C1 до 5% или 15%, как в нашем примере, и нам не нужно вручную редактировать какие-либо формулы, чтобы получить результаты мы после. Точно так же все значения в столбце B также могут быть обновлены, и мы сэкономим себе много работы, если Excel сделает все это за нас.

Ссылки на формулы в Excel – 10%

Ссылки на формулы в Excel – 5%

Ссылки на формулы в Excel – 15%

4. Функции

Функции — это предопределенные формулы в Excel, доступные для использования. Полный список функций, доступных для использования, можно найти, щелкнув любую ячейку в Excel, а затем нажав клавиши SHIFT и F3 (вместе) или нажав кнопку «Вставить функцию» рядом с панелью формул.