Check-moscow.ru

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

Как выполнить vlookup и вернуть несколько значений по вертикали в Excel?

Объединение таблиц в Power Query

Работали ли вы с электронными таблицами Excel? Если нет, то вам нужно начать прямо сейчас. Это отличный инструмент, который позволяет быстро и легко обрабатывать большие объемы данных. И если вы все-таки работали с ними в качестве капитана умственного труда, я могу с уверенностью сказать, что вы не раз сталкивались с ситуацией, когда вам нужно построить электронную таблицу из нескольких источников данных. Например, можно использовать несколько одинаковых таблиц Excel и скопировать их одну под другую. Или вы можете дополнить таблицу новыми столбцами и анализом.

Exceltip

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

Сегодняшний пост посвящен формуле VPR, которая описывает многократное копирование без ручного редактирования.

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

На рисунке видно, что я следовал традиционному подходу, используя формулу ФПР.

функция ВПР excel

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

функция ВПР excel

Чтобы сослаться на разные части сводной таблицы, необходимо каждый раз менять номер столбца в формуле ВПР. К примеру, в поле Описание номер столбца должен быть 3-й, а в поле Бизнес сегмент — 4-й.

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

функция ВПР excel

Решение 1: Использование дополнительных ячеек

Чтобы решить эту проблему, можно использовать дополнительные ячейки. Как вы можете видеть, я поместил значение для номера столбца над каждой из формул БПФ. Теперь, вместо того чтобы вручную записывать это значение в каждой формуле =EPR($A3;$H$3:$L$13;3;False), мы обращаемся к дополнительной ячейке. Таким образом, наша формула будет выглядеть так: =FPR($A3;$H$3:$L$13;C3;False).

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

Читайте так же:
Как вставить переключатели или переключатели в Excel?

функция ВПР excel

Решение 2: Использование функции СТОЛБЕЦ()

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

Для тех, кто не знаком с этой функцией, она принимает в качестве аргумента адрес ячейки и возвращает номер столбца. Например, HUNDRED(D1) вернет 4, потому что столбец D имеет четвертый порядковый номер.

Мне нужно указать номер 3-го столбца в сводной таблице. Чтобы не кодировать вручную, я использую HUNDRED(C1).

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

функция ВПР excel

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

Как использовать ВПР

Теперь, когда вы знаете архитектуру формулы VRP. Давайте посмотрим, как это работает на практике на следующих примерах и шагах Excel VLOOKUP…

Рассмотрим электронную таблицу, которая включает следующие столбцы: Название продукта, Отзывы и Цена. Мы хотим, чтобы Excel вернул количество отзывов для определенного продукта.

На примере этой POI мы рассмотрим, как действовать дальше, выполнив следующие шаги.

  1. Введите название продукта, который вы хотите найти, в любую ячейку ниже или рядом с вашими родительскими данными (убедитесь, что вы избегаете опечаток).
  2. Затем выберите пустую ячейку рядом с новой ячейкой, которую вы только что создали, которая теперь содержит продукт, который вы хотите найти.
  3. В этой новой ячейке введите = VLOOKUP . Excel обычно рекомендует завершение. Когда вы это увидите, нажмите Tab на клавиатуре, чтобы продолжить работу с формулой ВПР.
  4. Затем выделите только что созданную ячейку, содержащую название целевого продукта. При этом позиция ячейки будет добавлена ​​в формулу ВПР. Введите запятую после выделенной ячейки в формуле ВПР. Например, если целевой элемент или продукт находится в ячейке E6 , формула будет иметь вид = VLOOKUP (E6, на данный момент.

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

Как выполнить ВПР для нескольких элементов

Вы также можете найти несколько значений в столбце с помощью БПФ. Это может быть очень полезно, когда вам нужно выполнить такие операции, как создание таблиц Excel или диаграмм на основе полученных данных. Давайте посмотрим, как это сделать, выполнив следующие шаги:

  1. Введите все элементы, которые вы хотите найти, в отдельные ячейки (в данном случае названия выбранных продуктов).
  2. Выберите ячейку рядом с первым продуктом и введите = ВПР (.
  3. Затем выделите первый элемент (значение поиска) из тех, которые вы только что ввели, чтобы добавить его в формулу ВПР. Введите запятую после этого.

3. Как смотреть налево с VLOOKUP

VLOOKUP — одна из самых мощных функций в Excel. Тем не менее, он имеет несколько недостатков. Одним из них является то, что эта функция возвращает значения, находящиеся справа от столбца поиска и не ищет слева. Но для решения этой проблемы есть обходной путь: комбинация функций VLOOKUP и ВЫБОР (CHOOSE).

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

Хитрость здесь состоит в том, чтобы виртуально изменить порядок столбцов в таблице, используя функцию CHOOSE. Многие думают, что эта функция работает только с массивами и одиночными индексами. Но это не так. В нашем случае в качестве диапазона для поиска мы укажем следующее выражение: ВЫБОР(<1;2>;C3:C7;B3:B7) или ВЫБОР(<2;1>;B3:B7;C3:C7).

Это выражение создаст ссылку на два объединенных столбца в порядке Цвет; ID. Это именно то, что нам нужно.

В итоге получаем следующую формулу:

Несмотря на то, что в таблице, которую мы имеем перед собой, столбец ID является первым столбцом, в функции FFT, из которой берется результат, мы указываем 2. Это происходит потому, что SELECT меняет местами столбцы.

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

Для этой цели можно также использовать комбинацию функций ИНДЕКС и ПОИСК:

=ИНДЕКС(B3:B7; ПОИСКПОЗ(F3;C3:C7;0))

Синтаксис

Аргументы

Если значение аргумента номер_колонки :

  • Меньше 1, функция БПФ возвращает значение ошибки #SIGN! больше количества столбцов в аргументе массива, функция VRP возвращает значение ошибки #SIGN!

Если аргумент interval_view равен TRUE или опущен, возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, возвращается наибольшее значение, которое меньше искомого_значения.

Осторожно. Если аргумент interval_view равен TRUE или опущен, значения в первом столбце аргумента table должны быть расположены в порядке возрастания, иначе функция БПФ может вернуть неверный результат.

При использовании аргумента interval_view как FALSE функция FFT ищет только точные совпадения. Первое значение аргумента таблицы, соответствующее аргументу искомое_значение, используется, если первый столбец аргумента таблицы имеет несколько значений. Если точного совпадения не найдено, возвращается ошибка #N/D.

Замечания

  • При поиске в большом массиве, особенно, если на листе Excel много формул, у пользователя могут возникать проблемы с производительностью. Первое, что приходит на ум — это произвести апгрейд компьютера. Да, функция ВПР считается достаточно медленной, однако, мало кто знает, что ее скорость работы можно увеличить десятикратно, если использовать поставить аргумент равным ИСТИНА (или 1). Да, здесь есть нюанс в виде обязательной сортировки, поэтому такое ускорение будет работать не всегда. Кстати скорость увеличивается из-за сортировки, в этом случае нет необходимости сравнивать каждое значение из столбца.
  • Функция ВПР осуществляет поиск всегда в самом первом (левом) столбце диапазона. Данное ограничение можно обойти, там где возможно, конечно, уменьшив аргумент таблица , чтобы искомый столбец стал самым первым
    Поиск ВПР всегда в самом левом столбце Поиск ВПР всегда в самом левом столбце
  • При поиске текстовых значений в первом столбце аргумента таблица убедитесь, что данные в этом столбце таблицы не содержат начальных пробелов, конечных пробелов, используемых не по правилам прямых ( ‘ или » ) и «парных» ( ‘ или “ ) кавычек или непечатаемых символов. В этих случаях функция ВПР может вернуть неправильное или непредвиденное значение
    Возвращение ошибки функцией ВПР Возвращение ошибки функцией ВПР
  • При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. В этом случае функция ВПР может вернуть неправильное или непредвиденное значение
  • Если значение аргумента «интервальный_просмотр» — ЛОЖЬ , а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (

Нюансы использования ВПР

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

Поиск в первом столбце

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

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

Если вы хотите выполнить поиск слева от искомого столбца, необходимо использовать ИНДЕКС + ПОИСК. Этот метод немного сложнее для понимания, чем VRP, но если вы знакомы с ним, то использование INDEX + SEARCH не должно быть слишком сложным.

Поиск до первого совпадения

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

Демонстрация поиска ВПР до первого совпадения

Демонстрация поиска первого совпадения IDP

Это ограничение можно обойти, решение не самое простое, но оно подробно описано в курсе Excel.

Независимость от регистра

Значение для поиска не обязательно писать заглавными буквами (как в Excel) или как оно записано в диапазоне (как в VPR).

Независимость от регистра

Независимая регистрация

Лишние пробелы

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

Различие в формате данных

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

Все можно исправить, переведя параметры в один формат. Например

— если в D5 есть буквы, а в таблице — цифры;

Приведение формата в самой формуле

Преобразование форматов в самой формуле

Существует несколько вариантов для изменения формата текста в числах:

  • Возвести в степень: G2^1;
  • Двойное отрицание: —G2;
  • Прибавить ноль: G2+0;
  • Умножить на один: G2*1.

Прекращение проявления ошибки #Н/Д

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

Не зафиксирован массив

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

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

Массив можно оформить как умную таблицу. Команда «Форматировать как таблицу» из вкладки «Главная», либо горячее сочетание Ctrl+T. В этом случае ссылки на такой диапазон будут автоматически становиться абсолютными. Но при работе с такими таблицами в Excel есть свои нюансы и, не всегда это подойдет

Фиксирование диапазона в ВПР

Фиксирование диапазона в ВПР

Относительный поиск

При использовании ВПР пользователи, как правило всегда ставят аргумент интервальный_просмотр равным «0» (ЛОЖЬ), что означает, что нужно выполнить точный поиск и значения в первом столбце не отсортированы. Однако, если поиск осуществляется числовых значений, в некоторых случаях, хорошим решением будет отсортировать значения и выполнить не точный поиск. А в некоторых случаях неточный поиск вообще будет единственным решением. Рассмотрим подробнее.

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

  • 0 — 60: F
  • 60 — 70: D
  • 70 — 80: C
  • 80 — 90: B
  • 90 — 100: A

Вот как выглядит формула, если вы используете для ее построения логическую функцию ЕСЛИ (да-да, решение будет лучше и при использовании ПЕРЕСЧЕТ или ЭЛИМИН, но, допустим, у вас не самая последняя версия Excel, а ВПР здесь выигрывает, да и новые логические функции тоже):

Изящное решение с ВПР по поиску оценки

Умное решение от UPR для поиска оценки

Посмотрите на рисунок на формуле с IF и сравните его с рисунком с FIR и неточным совпадением, последний выглядит намного лучше:

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

ВПР для поиска максимального значения

VPR для нахождения максимального значения

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