Нужная функция ВПР в экселе; пошаговая {инструкция} как воспользоваться, для чайников и начинающих

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

Функция ВПР в Excel дозволяет данные из одной таблицы переставить в надлежащие ячейки 2-ой. Ее английское наименование – VLOOKUP. Весьма комфортная и нередко применяемая. Т. к. сравнить вручную спектры с десятками тыщ наименований проблематично.

КАК ПОЛЬЗОВАТЬСЯ ФУНКЦИЕЙ ВПР В EXCEL

Допустим, на склад компании по производству тары и упаковки поступили материалы в определенном количестве.

Таблица материалов.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

Прайс-лист.

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

    1. Приведем первую таблицу в подходящий нам вид. Добавим столбцы «Стоимость» и «Стоимость/Сумма». Установим валютный формат для новейших ячеек.
    2. Выделяем первую ячейку в столбце «Стоимость». В нашем примере – D2. Вызываем «Мастер функций» при помощи клавиши «fx» (в начале строчки формул) либо нажав комбинацию жарких кнопок SHIFT+F3. В группы «Ссылки и массивы» находим функцию ВПР и нажимаем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и избрать из выпадающего перечня «Ссылки и массивы».

    Фызов функции ВПР.

      1. Раскроется окно с аргументами функции. В поле «Разыскиваемое значение» — спектр данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен отыскать во 2-ой таблице.

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

        1. Последующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Перебегаем на лист с ценами. Выделяем спектр с наименованием материалов и ценами. Показываем, какие значения функция обязана сравнить.
          1. Чтоб Excel ссылался конкретно на эти данные, ссылку необходимо зафиксировать. Выделяем значение поля «Таблица» и жмем F4. Возникает значок $.
          1. В поле аргумента «Номер столбца» ставим цифру «2». Тут находятся данные, которые необходимо «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам необходимы четкие, а не ориентировочные значения.

          Заполнены все аргументы.

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

          Результат использования функции ВПР.

          Сейчас отыскать стоимость материалов не составит труда: количество * стоимость.

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

          1. Выделяем столбец со вставленными ценами.
          2. Правая клавиша мыши – «Копировать».
          3. Не снимая выделения, правая клавиша мыши – «Особая вставка».
          4. Поставить галочку напротив «Значения». ОК.

          Специальная вставка.

          Формула в ячейках пропадет. Останутся лишь значения.

          БЫСТРОЕ СРАВНЕНИЕ ДВУХ ТАБЛИЦ С ПОМОЩЬЮ ВПР

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

          Новый прайс.

            1. В древнем прайсе делаем столбец «Новенькая стоимость».
            1. Выделяем первую ячейку и избираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: .

            Это означает, что необходимо взять наименование материала из спектра А2:А15, поглядеть его в «Новеньком прайсе» в столбце А. Потом взять данные из второго столбца новейшего прайса (новейшую стоимость) и подставить их в ячейку С2.

            Заполнение новых цен.

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

            ФУНКЦИЯ ВПР В EXCEL С НЕСКОЛЬКИМИ УСЛОВИЯМИ

            До сего времени мы давали для анализа лишь одно условие – наименование материала. На практике же часто требуется сопоставить несколько диапазонов с данными и избрать значение по 2, 3-м и т.д. аспектам.

            Таблица для примера:

            Поставщики материалов.

            Представим, нам необходимо отыскать, по какой стоимости привезли гофрированный картон от ОАО (форма организации публичной компании; акционерное общество) «Восток». Необходимо задать два условия для поиска по наименованию материала и по поставщику.

            Дело осложняется тем, что от 1-го поставщика поступает несколько наименований.

              1. Добавляем в таблицу последний левый столбец (принципиально!), объединив «Поставщиков» и «Материалы».

              Объединение поставщиков и материалов.

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

                Разглядим формулу детально:

                1. Что отыскиваем.
                2. Где отыскиваем.
                3. Какие данные берем.

                ФУНКЦИЯ ВПР И ВЫПАДАЮЩИЙ СПИСОК

                Допустим, какие-то данные у нас изготовлены в виде раскрывающегося перечня. В нашем примере – «Материалы». Нужно настроить функцию так, чтоб при выбирании наименования появлялась стоимость.

                Поначалу создадим раскрывающийся перечень:

                  1. Ставим курсор в ячейку Е8, где и будет этот перечень.
                  2. Заходим на вкладку «Данные». Меню «Проверка данных».

                  Проверка данных.

                    1. Избираем тип данных – «Перечень». Источник – спектр с наименованиями материалов.

                    Параметры выпадающего списка.

                    1. Когда нажмем ОК – сформируется выпадающий перечень.

                    Выпадающий список.

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

                    1. Открываем «Мастер функций» и избираем ВПР.
                    2. 1-ый аргумент – «Разыскиваемое значение» — ячейка с выпадающим перечнем. Таблица – спектр с наименованиями материалов и ценами. Столбец, соответственно, 2. Функция заполучила последующий вид: .
                    3. Жмем ВВОД и наслаждаемся результатом.

                    Изменяем материал – изменяется стоимость:

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

                    Exceltip

                    Блог о программке Microsoft Excel: приемы, хитрости, секреты, трюки

                    Четыре метода использования ВПР с несколькими критериями

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

                    Итак, мы с вами разглядим четыре варианта сотворения подстановочной функции с 2-мя критериями:

                    1. Внедрение доборной колонки
                    2. Внедрение функции ВЫБОР для сотворения новейшей таблицы просмотра
                    3. Внедрение функций ИНДЕКС и ПОИСКПОЗ
                    4. Внедрение функции СУММПРОИЗВ

                    Ну а начнем мы с вами с самого обычного.

                    Внедрение доборной колонки

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

                    Разглядим традиционный пример. У нас имеется таблица с продажами по месяцам и городкам. И нам нужно найти значение продаж, соответственное двум условиям: месяц – Февраль и город – Самара.

                    Исходная-таблица

                    Внедрение функции ВПР в традиционном виде нам не поможет, потому что она сумеет возвратить значение, соответственное лишь одному условию. Из положения нам поможет выйти доп столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Сейчас мы сможем применять значения столбца А, чтоб возвратить нужное значение. Прописываем в ячейке G3 формулу:

                    Данная формула соединяет воединыжды два условия ячеек G1 и G2 в одну строчку и просматривает его в столбце А. Опосля того, как необходимое условие было найдено, формула возвращает значение с 4-ого столбца таблицы A1:D13, т.е. столбца Реализации.

                    Дополнительный-столбец

                    Внедрение функции ВЫБОР для сотворения новейшей таблицы просмотра

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

                    Внедрение функции ВЫБОР предполагает создание новейшей таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет смотреться последующим образом:

                    Главный момент данной формулы заключается в части ВЫБОР(<1;2>;B2:B13&C2:C13;D2:D13), который делает две вещи:

                    1. Соединяет воединыжды значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
                    2. Соединяет воединыжды два массива в таблицу, состоящую из 2-ух столбцов.

                    Результатом работы данной функции будет таблица, которая смотрится последующим образом:

                    Массив-для-просмотра

                    Сейчас формула стала наиболее понятной.

                    ВАЖНО: Потому что мы употребляли формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтоб отдать знать программке о наших намерениях. Опосля нажатия данной композиции кнопок, программка автоматом установит фигурные скобки в начале и в конце формулы.

                    Внедрение функций ИНДЕКС и ПОИСКПОЗ

                    3-ий метод, который мы с вами разглядим, также подразумевает внедрение формулы массива и использует функции ИНДЕКС и ПОИСКПОЗ.

                    Формула будет смотреться последующим образом.

                    Давайте разберем, что делает любая часть данной формулы.

                    Поначалу разглядим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В этом случае поочередно сравнивается значение ячейки G1 с каждым значением ячеек спектра B2:B13 и ворачивается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сопоставление делается со значением ячейки G2 и спектром C2:C13. Дальше мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Композиция ИСТИНА * ИСТИНА дает нам итог 1 (ИСТИНА). Давайте поглядим на картину ниже, которая поможет разъяснить механизм работы наиболее наглядно.

                    Массив-для-просмотра

                    Сейчас мы можем сказать, где находится строчка, удовлетворяющая обоим условиям. Функция ПОИСКПОЗ ищет положение 1 в результирующем массиве и возвращает 6, потому что единица встречается в 6-ой строке. Дальше функция ИНДЕКС возвращает значение 6-ой строчки спектра D2:D13.

                    Внедрение СУММПРОИЗВ

                    СУММПРОИЗВ одна из самых массивных формул Excel. У меня даже есть отдельная статья, посвященная данной формуле. Наш 4-ый метод использовании нескольких критерий заключается в написании формулы с функцией СУММПРОИЗВ. И смотреться она будет последующим образом:

                    Механизм работы данной формулы идентичен с механизмом работы предшествующего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с спектрами B2:B13 и С2:С13 соответственно. Дальше оба этих массива сопоставляются и выходит массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Дальше данный виртуальный массив перемножается на спектр D2:D13. Потому что в нашем виртуальном массиве будет лишь одна единица в 6-ой строке, формула вернёт итог 189.

                    Данная функция не будет работать, если в спектре D2:D13 имеются текстовые значения.

                    Чтоб осознать, как работает данная формула, рекомендую прочесть статью о функции СУММПРОИЗ.

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

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

                    Для вас также могут быть увлекательны последующие статьи

                    11 объяснений

                    А я воспользовался лишь одной — самой обычной.
                    Создателю спасибо за обзор! Весьма нередко приходится воспользоваться в работе данной для нас функцией.

                    Функция впр в excel для чайников примеры

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

                    = ВПР ( искомое_значение ; массив ; номер_столбца ; тип_поиска )

                    искомое_значение — константа либо ссылка на ячейку, значение которой вы собираетесь находить

                    массив — спектр ячеек, состоящий, как минимум, из 2-ух столбцов (в неприятном случае данная формула теряет всякий смысл) и, обычно, огромного количества строк

                    номер_столбца — номер столбца, обозначенного масива, из которого будут ворачиваться данные

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

                    ВПР производит поиск в первом столбце массива и, если значение найдено, то возвращает итог, взятый на пересечении соответственной строчки и обозначенного в 3-ем параметре столбца массива.

                    Схема

                    Некие замечания о ВПР

                    Пример Замечание
                    Разыскиваемое значение ищется в ПЕРВОМ столбце массива. Это базовое ограничение ВПР , которое серьёзно вредит универсальности данной функции. Почти все юзеры, прикипев к ВПР , и, не зная наиболее гибких альтернатив, вынужденно подстраивают свои таблицы под её способности, делая подходящий столбец первым.
                    Поиск оснанавливается на первом совпадении результата, если тип поиска = 0 (четкое совпадение) и на крайнем, если тип поиска = 1 (примерное совпадение). Таковым образом у вас есть возможность с помощью ВПР возвращать или первую совпавшую строчку, или последнюю. Но имейте в виду, что при нечётком поиске (тип поиска = 1) 1-ый столбец массива должен быть отсортирован по возрастанию .
                    ВПР поддерживает внедрение знаков подстановки ( * и ? ) в параметре искомого значения. » ? » — подменяет собой хоть какой один знак, а » * » — подменяет хоть какое количество всех знаков. Если используете знаки подстановки, то четвёртый параметр должен быть 0 (ЛОЖЬ).
                    Нечёткий поиск нередко используют для распределения значений по каким или спектрам. К примеру, разглядим традиционный пример вычисления значения скидки для клиента в зависимости от заказанного количества продукта. Видите ли, эта задачка совершенно вписывается в способности нечёткого поиска ВПР . Не запамятовывайте лишь про сортировку.
                    При поиске текстовых строк ВПР не лицезреет различия в регистре букв.
                    Если значение не найдено, то формула возвращает код ошибки #Н/Д (#N/A). С помощью доборной функции ЕСЛИОШИБКА (IFERROR) эти ошибки из эстетических либо других суждений можно перехватывать и подставлять, как в этом примере, пустую строчку (либо что угодно другое) — в этом случае юзеры не беспокоятся попусту, разглядывая таинственные #Н/Д , и, как следствие существенно пореже, отвлекают обученных людей от принципиального.

                    Функция ГПР (HLOOKUP)

                    Функция ГПР ( Г оризонтальный ПР осмотр) — это этот же самый ВПР , в котором строчки поменяли на столбцы и напротив. ГПР имеет последующий синтаксис:

                    = ГПР ( искомое_значение ; массив ; номер_строки ; тип_поиска )

                    искомое_значение — константа либо ссылка на ячейку, значение которой вы собираетесь находить

                    массив — спектр ячеек, состоящий, как минимум, из 2-ух строк (в неприятном случае данная формула теряет всякий смысл) и, обычно, огромного количества столбцов

                    номер_строки — номер строчки, обозначенного масива, из которой будут ворачиваться данные

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

                    ГПР производит поиск в первой строке массива и, если значение найдено, то возвращает итог, взятый на пересечении соответственного столбца и обозначенного в 3-ем параметре строчки массива.

                    Интересно почитать:  Функция поиска в excel
Ссылка на основную публикацию
Adblock
detector