0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как ВПР с выпадающим списком в Excel?

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

В Excel функция VRP позволяет перетаскивать данные из одной таблицы в соответствующие ячейки другой таблицы. Ее английское название — VLOOKUP. Она очень удобна и часто используется. Ведь вручную сопоставлять диапазоны с десятками тысяч имен проблематично.

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

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

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

В прайс-листе указаны затраты на материалы. Это отдельная таблица.

Прайс-лист.

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

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

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

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

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

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

        Аргумент Таблица.

          1. Чтобы Excel мог напрямую ссылаться на эти данные, ссылка должна быть постоянной. Выделите значение в поле Таблица и нажмите F4. Появится символ $.

          Абсолютные ссылки.

          1. В поле аргумента «Номер колонки» введите число «2». Именно здесь находятся данные, которые вы хотите «сбросить» в первую таблицу. Интервальное представление» является ЛОЖНЫМ, потому что нам нужны точные, а не приблизительные значения.

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

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

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

          Теперь вы можете найти стоимость материалов, умножив их количество на цену.

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

          1. Выберите столбец со вставленными ценами.
          2. Щелкните правой кнопкой мыши — «Копировать».
          3. Когда столбец все еще выделен, щелкните правой кнопкой мыши — «Paste Special».
          4. Установите флажок в поле «Значения». OK.

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

          В ячейках не будет формул. Останутся только значения.

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

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

          Новый прайс.

            1. В старом тарифе сделайте графу «Новый тариф».

            Добавить колонку новая цена в стаырй прайс.

            1. Выберите первую ячейку и выберите функцию FFT. Установите аргументы (см. выше). Для нашего примера: .

            Это означает, что вы берете обозначение материала в диапазоне A2:A15 и ищете его в «Новой цене» в столбце A. Затем вы берете данные из второго столбца с новой ценой (new price) и вставляете их в ячейку C2.

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

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

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

            До сих пор мы предлагали только название материала для анализа. Хотя на практике выбор значения на основе 2, 3 и т.д. часто предполагает сравнение нескольких диапазонов с данными. критериями.

            Таблица является примером:

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

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

            От одного поставщика поступает несколько наименований, что усложняет ситуацию.

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

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

                1. Таким же образом мы объединяем критерии поиска:

                Объединяем искомые критерии.

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

                Разбор формулы.

                Давайте рассмотрим формулу более подробно:

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

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

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

                Сначала создадим выпадающий список:

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

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

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

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

                    1. После нажатия кнопки OK появится раскрывающийся список.

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

                    При выборе конкретного материала в столбце Цена должен появиться соответствующий рисунок. Поместите курсор в ячейку E9 (где должна появиться цена).

                    1. Откройте «Мастер функций» и выберите FFT.
                    2. Первым аргументом является ячейка с выпадающим списком. Таблица представляет собой поле с названиями материалов и ценами. Столбцы соответственно равны 2. Функция выглядит следующим образом: .
                    3. Нажмите Enter и наслаждайтесь результатом.

                    Результат работы выпадающего списка.

                    Изменение материала приведет к изменению цены:

                    Связь цен с материалами.

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

                    Функция ВПР() в EXCEL

                    Функция FFT() — одна из наиболее часто используемых функций в EXCEL, поэтому мы рассмотрим ее подробно.

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

                    Синтаксис функции

                    FPR(поиск_значения; таблица; количество_столбцов; диапазон_видов)

                    The_value вы ищете — это значение, которое вы пытаетесь найти в столбце данных. Искомое значение может быть числом или текстом, но обычно это число. Искомое значение должно находиться в первом (крайнем левом) столбце диапазона ячеек, указанного в таблице.

                    Ячейки таблицы представляют собой диапазон значений. В левой колонке таблицы выполняется поиск искомого_значения, а соответствующий результат выводится из правой колонки (хотя, в принципе, левая колонка может выводить искомое_значение напрямую). Существует тенденция называть левый столбец ключевым столбцом. Функция возвращает #N/D, если первый столбец не содержит искомого значения.

                    Column_number — номер столбца таблицы, из которого должен быть возвращен результат. Самый левый столбец (ключ) содержит число 1 (которое используется для поиска).

                    Interval_view может принимать 2 значения: TRUE (поиск значения, близкого или равного критерию) и FALSE (поиск значения, точно равного критерию). True означает, что первый столбец таблицы отсортирован в алфавитном или возрастающем порядке. Это метод по умолчанию, используемый в функции, если не указан другой метод.

                    Ниже приводится статья о том, как решать распространенные проблемы с помощью функции VPR().

                    Задача1. Справочник товаров

                    Рассмотрим исходную таблицу (см. пример файла Лист Reference).

                    Задача состоит в том, чтобы выбрать нужный товар и отобразить его Название и Цену.

                    Примечание Это «классическая» проблема при использовании VPR() (см. статью Ссылка).

                    Для вывода Name используйте формулу =VPR($E9;$A$13:$C$19;2;FALSE) или =VPR($E9;$A$13:$C$19;2;TRUE) или =VPR($E9;$A$13:$C$19;2) (т.е. параметр Interval_view может быть установлен в FALSE или TRUE или не установлен вообще). Number_column должен иметь значение =2, так как столбец Name всегда имеет номер 1.

                    Для получения цены используйте аналогичную формулу: =FRP($E9;$A$13:$C$19;3;False) (значение параметра номер столбца должно быть =3).

                    Этот ключевой столбец содержит числа и должен содержать искомое значение (условие задачи). Если первый столбец не содержит элемента поиска, функция возвращает ошибку #N/D. Это может произойти, например, из-за опечатки. Если элемент поиска введен неверно, можно воспользоваться выпадающим списком (см. ячейку E9), чтобы убедиться, что он введен правильно.

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

                    Для этого типа задач лучше всего сначала отсортировать верхний столбец (это также поможет сделать выпадающий список более четким). Также, в случае несортированного списка, FFT() с параметром Interval_view TRUE (или опущенным) не будет работать.

                    В файле каталога образцов листов также рассматриваются альтернативные формулы (дающие тот же результат) с использованием функций INDEX() , SEARCH() и PROSWER(). Если ключевой столбец (элемент столбца) не является самым левым столбцом в таблице, функция FFT() не используется. В этом случае необходимо использовать альтернативные формулы. Функция string INDEX() , SEARCHPROPER() создает так называемый правый ВПР: =INDEX(B13:B19;FINDPOZ($E$9;$A$13:$A$19;0);1)

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

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

                    Примечание Никогда не используйте VPR() с параметром Interval_view True (или опущенным), если ключевой столбец не отсортирован по возрастанию, поскольку результат работы формулы непредсказуем (если VPR() найдет значение, которое больше искомого, то выведет значение, которое находится строкой выше).

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

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

                    Чтобы решить эту проблему с помощью VPR(), нужно выполнить следующие условия:

                    1. Искомый ключевой столбец должен быть самым левым в таблице;
                    2. Ключевой столбец должен быть отсортирован по возрастанию;
                    3. Значение параметра Interval_view должно быть установлено на TRUE или опущено.

                    Для вывода названия продукта используйте формулу =FORM($A7;$A$11:$B$17;2;TRUE)

                    Эта формула используется для вывода найденной цены (которая не обязательно совпадает с установленной): =VPR($A7;$A$11:$B$17;1;TRUE).

                    Как видно на изображении выше, функция VPR() нашла самую высокую цену, которая меньше или равна заданному значению (см. файл примера «Найти ближайшее число» ). Это связано с тем, как работает функция поиска: Если FFT() находит значение, которое больше искомого, он выводит значение, которое находится на одну строку выше него. Поэтому, если искомое значение меньше минимального значения в ключевом столбце, функция возвращает ошибку #H/D.

                    Найденное значение может быть не самым близким. В данном случае ближайшим значением для 199 будет 150 (хотя ближайшая цена все равно 200). Опять же, это происходит потому, что функция находит наибольшее число, которое меньше или равно заданному числу.

                    V PR() не поможет вам найти ближайшее значение к искомому. Проблемы такого рода рассматриваются в разделе Ближайшее ЧИСЛО. Там же вы найдете решение проблемы поиска ближайшего значения, когда ключевой столбец не отсортирован.

                    Примечание: Для удобства строка таблицы, содержащая найденное решение, выделена с помощью условного форматирования. Это можно сделать с помощью формулы =Find($A$7;$A$11:$A$17;1)=CROSS()-CROSS($A$10) .

                    Примечание: Если в ключевом столбце есть значение, совпадающее с искомым, функция с параметром Интервал_просмотра = FALSE вернет первое найденное значение, равное искомому, а с параметром = TRUE — последнее (см. изображение ниже).

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

                    голоса
                    Рейтинг статьи
                    Читайте так же:
                    Как автоматически отсортировать дату при вводе или изменении даты в Excel?
Ссылка на основную публикацию
Adblock
detector