Auditportal.ru

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

Как использовать ВПР для создания уникального списка из таблицы в Excel?

Функция впр на английском в Excel

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

» V LOOKUP» (VLOOKUP на английском языке) означает вертикальную навигацию. Эта функция является одной из самых популярных в Excel. Например, вы можете легко сравнить номера телефонов людей или организаций в справочной таблице по их именам. Используя название товара, можно сравнить его цену. Это, наряду со многими другими возможностями, даст вам функция FFT. Она достаточно проста в использовании.

Использование функции

Давайте рассмотрим структуру ВПР и то, какие аргументы она включает. Как и любая другая функция в Excel, она начинается со знака равенства (=). Имя функции и аргументы заключены в круглые скобки.

PVR содержит 4 аргумента.

Функция ищет значение в крайнем левом столбце и возвращает значение в той же строке из заданного столбца.

Искомое значение

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

Он должен включать столбец для ответа и находиться справа от столбца, содержащего исходное значение.

Номер столбца

Последний аргумент — интервал навигации, может иметь 2 значения: 0 — FALSE, 1 — TRUE. отвечает за точный поиск (соответствует навигации сверху вниз). Если ничего не найдено, возвращается ошибка N/A (нет данных), 1 является приблизительной.

Интервальный просмотр

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

Примеры использования

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

Использование фильтра

После знака равенства введите VPR, а затем Enter и Fx для ввода аргументов.

Вводим функцию

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

Мастер настроек

Наш поиск будет ограничен искомыми значениями. В данном случае это ячейка E1.

Вводим искомые значения

В массиве выделяется место для второго аргумента.

Задаем диапазон

Пожалуйста, делайте абсолютные ссылки (указывайте знаки доллара или используйте клавишу F4 для всего диапазона), чтобы избежать «сползания» диапазона из-за того, что имя таблицы здесь не указано.

Делаем абсолютные ссылки

Возвращается номер столбца. В данном примере возврату подлежит графа 2 (товары). Для точного поиска 4-м аргументом является .

Вводим номер столбца

После ввода всех значений нажмите кнопку OK.

С помощью этой новой функции теперь можно изменить идентификационный номер фильтра для изменения названия продукта.

Изменение наименования

Я хотел бы привести еще один пример.

Теперь вы должны получить партию для каждого продукта на основе критерия «Количество».

Пример таблица

Если речь идет о небольшой партии продукции, она должна составлять от 100 до 200, если о средней партии — от 200 до 300 и т.д.

Читайте так же:
Как найти и перечислить все ссылки (внешние ссылки) в Excel?

В этом случае выберите столбец Критерий — Сторона (зафиксируйте F4). Что касается столбца №2, то вид интервала в данном случае должен быть равен 1 (это позволит вам получить значение, наиболее близкое к искомому).

Мастер функций

Как видно из полученной таблицы для количества, скажем, 110, произведенная партия мала (ближайшая меньшая — 100) и т.д. Убедитесь, что вы отсортировали критерии от большего к меньшему, иначе VPR не будет работать.

Функция ВПР в MS Excel

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

Как вызвать функцию ВПР. Функция ВПР в Excel

Давайте сначала разберемся, как вызвать эту функцию. Выберите вкладку Формулы. Нажмите кнопку Вставить функцию. И щелкните на ней. Таким же образом можно вызвать функцию БПФ, нажав Shift + F3.

Появится диалоговое окно Insert Function (Вставить функцию). Введите FFT в строке функции Find. Нажмите кнопку Найти. БПФ появляется в результатах поиска в поле Select Function (Выбрать функцию). Дважды щелкните по нему или нажмите OK. Появится диалоговое окно Функция БПФ — Аргументы функции.

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

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

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

Формула нашей функции и значение, которое мы хотим увидеть, соответственно сохраняются в соответствующей ячейке до вызова функции БПФ. В нашем случае это ячейка G3. Ячейка в столбце «Цена» таблицы 2 содержит эту информацию. Функция БПФ вставит цену конфеты А из таблицы 1 в столбец Цена таблицы 2, напротив конфеты А.

Функция БПФ вызывается так, как описано выше.

Аргументы функции. Функция ВПР в Excel.

Искомое_значение.

Это значение находится в диапазоне, указанном в столбце таблицы. В качестве примера выделим конфету G (ячейка E3, таблица #2). Из-за первого значения в столбце «Название конфеты», таблица №2. Это не основные, но удобные). Мы будем искать это значение в таблице №1 с помощью нашей функции.

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

Таблица.

Выберите диапазон таблицы, в котором должно быть найдено нужное значение, и объем данных для перемещения. В нашем примере это таблица 1. То, что мы ищем, — это название конфеты. Наша цель — перевести цену конфеты. Поместите курсор в нужную нам строку и выделите диапазон щелчком мыши. В нашем примере таблица №1 имеет диапазон от B1:C12. Добавив знак $, мы придадим ссылкам абсолютное значение. В строке Таблица — $B$1:$C$12 просто добавьте эти символы в ячейки диапазона.

Читайте так же:
Как назвать листы на основе значений ячеек (из списка) в Excel?

Мы можем дать нашей области название и ввести его в строке таблицы.

Как это сделать. Выберите интересующий нас диапазон. Таблица 1. Чтобы задать имя, перейдите на вкладку Формулы. Нажмите. Появляется диалоговое окно для создания имен. Пишем каждое имя. Тем не менее, мы должны помнить об этом. Например, Конфета. Нажмите OK.

В строке Таблица вместо диапазона введите имя, которое мы вам дали, — Candy

Номер_столбца.

Функция БПФ ищет значение в крайнем левом столбце таблицы с заданным диапазоном поиска. По умолчанию функция присваивает этому столбцу число 1. В нашем примере самый левый столбец — «Название конфеты» в таблице 1. Однако в Number_column нам нужно указать номер столбца в том порядке, в котором мы хотим переместить данные. В нашем примере это столбец «Цена» в таблице 1. Это «второй» по порядку слева направо в колонке названий конфет в таблице 1. Поэтому в столбце Number_ мы введем число 2. Если бы столбец «Цена» в таблице 1 был не вторым по порядку, а, скажем, десятым, то в столбце «Номер» мы бы, соответственно, ввели число 10.

Интервальный _просмотр.

В этой строке мы вводим число ноль «0». VPR будет проверять точные совпадения между значениями поиска (Search_value) и значениями в крайнем левом столбце диапазона поиска (Table). На нашем примере мы находим точные совпадения между столбцом «Название конфеты» в таблице №1 и столбцом «Название конфеты» в таблице №2.

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

Вот как это все выглядит вместе.

Рассчитайте формулу для всего столбца «Цена» в таблице №2. Из таблицы №1 в таблицу №2 были перенесены все цены.

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

У нас есть таблица 1 и таблица 2. Каждая таблица состоит из одного столбца. Таких простых таблиц достаточно, чтобы понять алгоритм работы функции БПФ в данном случае. Колонки содержат практически идентичные данные. Для этого сравните их и посмотрите, какие данные есть в таблице 2, а каких нет в таблице 1.

Справа от таблицы 2, в ячейке G3, вставляем функцию БПФ. Этот макет является примером, но вы можете использовать любую другую колонку и дизайн.

Введите следующее в диалоговом окне Аргументы функции:

Ценность, которую вы ищете. Это значение ячейки в таблице 2, наличие которой мы проверяем в таблице 1. В нашем примере это ячейка F3 (значение 9).

Читайте так же:
Как извлечь текст между запятыми / скобками / круглыми скобками в Excel?

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

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

_просмотр интервала. Здесь мы вставили ноль «0», потому что функция должна искать точные совпадения.

Нажмите OK и перетащите функцию на колонку. В некоторых ячейках вместо искомого значения появляется ошибка формулы: #N/D. В этом случае это означает, что указанный критерий поиска не был найден в проверяемом столбце.

Используя наш пример, таблица №2 имеет значение 17 и значение 10. В результате работы функции БПФ в ячейке вместо ожидаемого значения появилась ошибка #N/D. Поэтому в таблице №1 ни в одной ячейке нет значения 17 и значения 10.

Вы можете проверить прямо противоположное. И выясните, какие данные есть в таблице 1, а каких нет в таблице 2.

Обратите внимание. Функция ВПР в Excel.

Согласно строке Table, функция FFT ищет значения (значения, указанные в строке Search_value) в первом (крайнем левом) столбце таблицы, указанной в Table.

В таблице 1, например, мы добавили колонку Категория, и теперь колонка Название конфеты стала второй, а не первой. Если мы укажем все ячейки таблицы 1 в качестве диапазона в строке таблицы, функция VRP не будет работать (ошибка #N/D), потому что она будет искать точное соответствие в столбце Категория, таблица 1, для значения в столбце Название конфеты, таблица 2. И она не найдет точного соответствия.

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

Если в области действия таблицы есть определенный столбец, функция VRP будет проверять только этот столбец. И в этом правиле нет необходимости.

Вы можете искать на разных листах. Алгоритм один и тот же. Формула функции выглядит так: =AFR(E6;Sheet1!$B$1:$C$11;2;0). Функция FIR показана на листе 2. Поиск значений и передача данных показаны на Листе 1, а затем функция FIR на Листе 2. Заданное имя является альтернативой диапазону. Например, Candy. Таким образом, формула функции будет выглядеть так: =ФРП(E6;Конфеты;2;0).

Возможные ошибки.

#H/D — искомый столбец в таблице не является самым левым столбцом в области поиска.

#N/D — На искомый диапазон таблиц нет ссылок. Вы должны использовать абсолютные ссылки ($) или назвать указанный диапазон.

N/D — Функция VRP не нашла идеального совпадения в диапазоне поиска.

Читайте так же:
Как извлечь текст между двумя словами в Excel?

#N/D — Возможно, вам потребуется отсортировать искомый диапазон по возрастанию.

# SEVER! В этом случае, возможно, строка Column_number была неправильной, и функция не нашла данные, которые она должна переместить.

Функция ВПР в Excel – примеры использования и советы

Чтобы найти значение в таблице, используйте функцию БПФ в Excel, примеры которой приведены в статье ниже.

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

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

впр в excel примеры

Содержание

VLOOKUP (Vertical Lookup) — еще одно название функции, встречающееся в английской версии табличного процессора.

Аббревиатура WRP расшифровывается как «вертикальный обзор».

Анализ и поиск данных в таблице осуществляется путем перехода от столбца к столбцу в том порядке, в котором они появляются.

В Excel есть и противоположная функция, называемая HLOOKUP или HPR — горизонтальный поиск.

P EA выполняет поиск в таблице методом перебора столбцов, а не строк, поэтому эти два варианта работают по-разному.

Из-за количества строк в большинстве таблиц, большее количество пользователей предпочитают функцию БПФ.

Как выглядит синтаксис ВПР?

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

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

  • Используйте уже созданный документ или откройте новую пустую электронную таблицу;
  • Нажмите кнопку «Формулы», как показано на рисунке ниже;
  • Введите «VRP» или «VLOOKUP» в строке поиска, в зависимости от языка приложения;
  • Установите категорию «Полный список»;
  • Нажмите «Найти». .

впр в excel примеры

Рис. 2 — поиск формул в Excel

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

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

Общее описание ОФВ выглядит следующим образом

впр в excel примеры

Рисунок 3 — Сводка параметров

Рассмотрим подробнее каждое из значений, указанных в скобках:

  • <ЧТО> — первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
  • <НОМЕР_СТОЛБЦА> — тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
  • <ГДЕ> — здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
  • <ОТСОРТИРОВАНО> — этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.

См. также

Как работает ВПР. Полезный пример

Несколько примеров помогут нам лучше понять, как работает VLOOKUP. Рассмотрим простую таблицу с двумя столбцами. Она должна содержать код и название продукта.

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

Затем введите все необходимые параметры в окне, показанном на рисунке 3. Результат выполнения команды будет отображен в окне.

впр в excel примеры

Рисунок 4 — Пример поиска в простой таблице

Цветные ячейки на рисунке выше показывают значение элемента. Сортировка выполняется автоматически, если вы не ввели значение для сортировки.

Далее он «думает», что элементы в первом столбце вашей таблицы расположены по возрастанию.

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

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

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

Поиск прекратится автоматически, как только слово «ЧТО» и название предмета совпадут.

Если в таблице нет идентификатора, который вы ввели для названия товара, поиск VLOOKUP даст результат «N/A», что означает, что для указанного номера товара нет.

впр в excel примеры

Рисунок 5 — Второй пример UPR

Когда использовать ВПР?

Выше описаны два варианта использования VLOOKUP.

Это первый вариант VLOOKUP, подходящий для случаев, подобных приведенному ниже:

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

Первый орфографический вариант VLOOKUP не может найти элемент, если нет значения, меньшего или равного искомому значению. В ячейке результата будет возвращено только «N/A».

V PR (сортировка «0» по имени) имеет еще один вариант для больших таблиц, содержащих одинаковое имя в нескольких ячейках.

VLOOKUP упрощает работу с данными, возвращая первую найденную строку.

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

С помощью ВПР можно найти текстовые строки в объектах листа, которые имеют различные типы значений.

впр в excel примеры

Рис.6 — пример поиска по текстовому значению

V PR полезна для удаления больших пробелов. Функция эффективно находит все имена с пробелами, позволяя быстро их удалить. Пример:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector