Evolcom.ru

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

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

ВПР в Экселе – 7 крутых приёмов, о которых вы возможно не знаете

Здравствуйте, друзья. Сегодня практикуем функцию ВПР Excel (на английском — VLOOKUP). Я покажу вам несколько примеров использования функции, которые многие мои ученики не использовали, а зря.

Если вы понятия не имеете, как работает ТПД, сначала прочитайте эту статью, а затем возвращайтесь!

Неточный поиск в Excel

Многие пользуются ВПР, чтобы найти строку по значению и вернуть любую её колонку. Однако, бывают ситуации, когда возможно найти лишь ближайшее.

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

  • 0-200 у.е. – премии не будет
  • 200-300 у.е. – 3% от продаж
  • 300-500 у.е. – 5%
  • свыше 500 у.е. – 7%

Бонусы рассчитываются для каждого сотрудника.

расчет процентов с помощью ВПР

Мои ученики решили задачу, используя функцию IF в нескольких случаях. В результате получилась гигантская формула: =IF(D3 < $H$4 ; $I$3 ; IF(D3 < $H$5 ; $I$4 ; IF(D3 < $H$6 ; $I$5 ; $I$6))))

расчет процентов с ЕСЛИ

К сожалению, результаты правильные, но формула слишком большая и сложная для понимания. Как мы можем сделать ее короче? При неточном FIR-поиске ищется меньшее значение. Давайте воспользуемся этим: =FRP(D3;$H$3:$I$6;2;TRUE)

расчет процентов с ВПР

Она достигает того же результата, но с сокращенными и более четкими вычислениями. Согласно последнему аргументу «TRUE» в функции, был выполнен неточный поиск. Вместо громоздкой формулы — простое решение.

Как это работает? Продажи Смирнова, например, составляют 205 у.е. VRP ищет в таблице с бонусами 200, то есть наименьшее число. Вы получите соответствующий бонус (3%).

ВПР по двум условиям в Excel

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

Рассмотрим на примере предыдущей таблицы. Заметили, что в ней — два Соколова? Первый – в Центральном районе, второй — в Южном. Как найти продажи второго? Точный поиск ВПР вернет первого, если искать по фамилии. Придется искать по двум параметрам – ФИО и району.

Для этого добавьте дополнительный столбец D в таблицу исходных данных. Используйте функцию CLUT для объединения в ней имен и регионов. Например, вот так:

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

Теперь используйте инструмент VPR для поиска в таблице D3:D17 по имени + регион:

ВПР по двум параметрам

Первый аргумент записывается «I2 & I3», т.е. вышеупомянутая связка Фамилия+Регион. Оператор конкатенации «&» объединяет строки, записанные как I2 («Соколов») и I3 («Юг»). Затем ВПР будет искать значение «Соколов-Юг». И он найдет его в ячейке D11, возвращая искомые продажи.

Одновременно можно использовать три и более параметров.

Использование имен в формуле ВПР

Вернемся к формуле из предыдущего параграфа: = VPR(I2&I3;$D$3:$E$17;2;0) . Хотя она коротка и проста, она может сбить с толку, если вы видите ее в первый раз. Простым решением будет использование информативных имен вместо ссылок.

В этой статье я подробно объяснил, как присвоить имя ячейке или диапазону. Самый простой способ — выделить нужную таблицу или ячейку и ввести ее имя в поле «Имя» слева от строки формул. Затем нажмите Enter, чтобы применить имя.

Теперь та же формула выглядит так

Читайте так же:
Как переместить повторяющиеся строки на другой лист в Excel?

ВПР с именами

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

Динамическое изменение столбца для вывода

Очень интересный пример. Взгляните на фотографию:

продажи за квартал

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

  1. В ячейке L3 я организовал выпадающий список с перечнем месяцев. Вы можете обойтись без выпадающего списка и ввести месяцы вручную. Но мой вариант удобнее
  2. Я получил номер столбца для выбранного месяца с помощью функции =DESCRIPTION(month;B2:I2) . Эта формула возвращает индексный номер выбранного месяца в диапазоне столбцов B2:I2
  3. Я вставил формулу из предыдущего шага вместо номера выходного столбца функции: =VRP(name;B2:I17;SEARCH(month;B2:I2);0) . Теперь, если я изменю период, SEARCH пересчитает номер выходного столбца и результат будет пересчитан.

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

изменяемый номер столбца

Подстановочные символы и ВПР

Если вы не знаете точную строку, которую ищете, можно использовать подстановочные знаки:

  • «*» — любое количество любых символов
  • «?» — один любой символ

Что такое точка? В ячейке B13 таблицы записана фамилия Семенов. Его фамилия пишется через «е». Семенов не может быть найден путем поиска его по фамилии «Семенов».

ВПР не нашел Семёнова

А по строке «Семёнов», через «ё» — мы получим правильный результат:

ВПР нашел Семёнова

Так часто бывает, когда таблицы заполняют вручную несколько человек. Кто-то пишет через «Ё», кто-то через «Е». Аналогично «Й» и «И». Как исключить подобные ошибки? Запишем вместо спорной буквы подстановочный символ: =ВПР(«Сем?нов»;$B$3:$E$17;4;0)

подстановочный символ

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

Обход ошибок при поиске ВПР

Ошибка #H/D, возвращаемая, когда искомое значение не найдено, выглядит очень некрасиво. А если на ячейку с результатом поиска ссылаются другие формулы — они тоже будут повреждены.

Мы можем обойти такие ошибки, используя две базовые функции:

  • IF ERROR(выражение, значение_если_ошибка) — это универсальная формула. Если первый аргумент возвращает ошибку, то возвращается второй аргумент
  • UND(выражение) — проверяет выражение и возвращает TRUE, если есть ошибка, или FALSE, если все в порядке. Эта функция часто используется как часть других функций. Например, IF.

Чтобы преобразовать #N/D в информативную строку, воспользуйтесь следующей формулой:

Если теперь мы ищем значение, которое не найдено, вместо ошибки отображается строка «Не найдено». Аналогично, мы можем вернуть пустую строку («») или null, чтобы зависимые формулы не нарушались.

ЕСЛИОШИБКА

Ускорение работы с помощью двойного ВПР

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

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

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

Запишем такую формулу: =ЕСЛИ(ВПР(значение ; массив ;1;1)=значение ; ВПР(значение ; массив ; номер_столбца ;1);«Не найдено») .

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

быстрый поиск с двумя ВПР

Посмотрите и убедитесь, насколько живее он стал!

Это все приемы, которые я хотел вам показать. Попробуйте и используйте их, чтобы сделать свою работу более эффективной. До скорой встречи!

Exceltip

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

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

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

Поэтому мы рассмотрим четыре способа создания неоднозначной функции с двумя условиями:

  1. Использование дополнительной
  2. -колонки Использование функции SELECT для создания новой
  3. -таблицы отображения Использование функций ИНДЕКС и ПОИСК
  4. Использование функции РЕЗЮМЕ

Во-первых, давайте разберемся с самым простым.

Использование дополнительной колонки

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

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

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

В своем классическом виде функция FFT не поможет нам, потому что она может вернуть только значение, соответствующее одному условию. Мы можем исправить это, объединив значения столбцов «Месяц» и «Город». Мы вводим формулу =B2&C2 в ячейку A2 и расширяем ее до ячейки A13. Используя значения столбца А, мы можем получить нужное значение. Запишите формулу в ячейку G3:

Формула объединяет два условия ячеек G1 и G2 в одну строку и проверяет их в столбце А. После нахождения нужного условия формула возвращает значение четвертого столбца таблицы A1:D13, то есть столбца Sales.

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

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

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

Использование функции SELECT предполагает создание новой таблицы для отображения, в которой значения в столбцах «Месяц» и «Город» уже объединены. Наша формула будет выглядеть следующим образом

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

  1. Объединяет значения столбцов Month и City в единую матрицу: JanMoscow, FebMoscow…
  2. Объединяет две матрицы в одну двухколоночную таблицу.

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

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

Теперь формула стала более ясной.

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

Использование функций ИНДЕКС и ПОИСКПОЗ

Еще один способ, который мы с вами обсудим, включает формулы массивов и функции ИНДЕКС и ПОИСК.

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

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

Рассмотрим, что делает каждая часть этой формулы.

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

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

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

Использование СУММПРОИЗВ

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

Эта формула использует подход, аналогичный предыдущей. В этой виртуальной таблице сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и C2:C13, соответственно. Затем сравниваются оба этих массива и получается массив из единиц и нулей, где единица присваивается строке, в которой оба условия совпадают. Виртуальный массив также состоит из диапазона D2:D13. В шестой строке нашего виртуального массива находится только одна единица, поэтому формула возвращает 189.

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

Лучше всего прочитать статью о функции СУММПРОИЗ, чтобы узнать, как работает эта формула.

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

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

Вам также могут быть интересны следующие статьи

11 комментариев

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

Можете ли вы вернуть все значения ВПР?

В РП — это мощная функция. Однако на семинарах по Power Excel я часто получаю вопрос о том, может ли функция БПФ возвращать совпадающие значения. Вы знаете, что функция БПФ с False в качестве четвертого аргумента всегда возвращает первое найденное совпадение. Согласно снимку экрана, ячейка F2 возвращает значение 3623, поскольку это первое совпадение, найденное для задания J1199.

VLOOKUP возвращает информацию из первого совпадения

Тогда возникает вопрос, может ли VLOOKUP возвращать все совпадения.

УПО этого не сделает. Но другие функции могут.

Если вы хотите просуммировать все затраты на работу J1199, вы должны использовать =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),

Используйте СУММЕСЛИМН, чтобы подсчитать каждое совпадение

Если у вас есть текстовые значения и вы хотите объединить все результаты в одно значение, вы можете использовать =TEXTJOIN(«,»,TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,»»)) . Формула работает только в Office 365 и Excel 2019.

TEXTJOIN объединит все результаты в одно значение

Также может возникнуть необходимость вернуть все результаты задания в новый диапазон рабочего листа. Новая функция =FILTER(B2:C53,A2:A53=K1, «None Found»), которая появится в Office 365 в 2019 году, устранит эту проблему:

Читайте так же:
Как предотвратить прокрутку верхней строки в Excel?

Функция ФИЛЬТР медленно распространяется среди подписчиков Office 365

Иногда люди хотят выполнить все операции VRP и суммировать их. В отсортированной таблице поиска можно использовать =SUM(LOOKUP(B2:B53,M3:N5)).

Старая функция ПРОСМОТР работает, если вы можете выполнить версию ВПР с приблизительным соответствием.

Если вам нужно суммировать все VRP с версией точного совпадения, вам нужно получить доступ к динамическим таблицам, чтобы использовать =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE))).

Суммируйте все VLOOKUP с версией VLOOKUP с точным соответствием

Чтобы узнать больше о динамических массивах, прочитайте статью «Динамические массивы в Excel прямо в точку».

Смотреть видео

Стенограмма видео

Изучайте Excel с подкаста эпизод 2247: Можно ли вернуть все значения из VLookUp?

Здравствуйте, добро пожаловать на веб-трансляцию. Я Билл Джален. На прошлой неделе на моем семинаре в Эпплтоне, штат Висконсин, прозвучали два вопроса, оба связанные между собой. Они сказали: «Эй, как нам вернуть все UDP, верно? В данном случае, как и в случае с J1199, есть куча матчей, и, как вы знаете, они хотят вернуть их все обратно, и мой первый вопрос, когда кто-то задает мне этот вопрос, — ну, что вы хотите сделать с этими матчами? Вы хотите добавить цифры или объединить текст? И это весело. Два вопроса на семинаре, один человек хочет сложить их, а другой — объединить результаты.

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

Прежде всего, если мы хотим сложить их все вместе, мы не будем использовать БПФ. Мы будем использовать функцию SUMIF или SUMIFS, которая сложит все, что соответствует данному элементу. Итак, SUMMESLIMS. Как вы можете видеть здесь, я собираюсь нажать F4, чтобы заблокировать суммирование числовых значений. Таким образом, когда я скопирую это вниз, он будет продолжать указывать на тот же диапазон, а затем мы хотим пойти и посмотреть, совпадает ли номер JOB в столбце A, снова F4, со значением слева от нас. Если мы скопируем вниз E2 — в данном случае — мы увидим ALL для каждого элемента. (SUMSLIMN ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))

Давайте проведем небольшую проверку. J1199. Всего 25365. OK. Так что это работает. Если это числа, и вы хотите получить все числа и сложить их, переключитесь на SUMMESLY или SUMMESLYN, но если это текст, ОК, теперь эта функциональность доступна в Office 365 в феврале 2017 года. Поэтому если у вас Excel 2016 или Excel 2013, Excel 2010 или любая из этих старых программ, вы не получите эту функцию. Это функция под названием TEXTJOIN. TEXTJOIN. Это еще одна функция от (Joe McDade — 01:50), который только что познакомил нас со всеми этими замечательными динамическими матричными формулами на Ignite в 2018 году, и Джо заставил TEXTJOIN работать с матрицами, и это действительно круто.

Поэтому разделителем здесь будет SPACE, независимо от EMPTY. Здесь мы хотим игнорировать EMPTY, потому что в следующей части, операторе IF, мы создадим кучу пустых полей. ЕСЛИ этот элемент над A2, F4, = номер этого заказа здесь, то я хочу соответствующий элемент в столбце C, F4, иначе я также хочу «». Закройте этот оператор IF. Закройте TEXTJOIN. Нажимать ли клавиши CONTROL + SHIFT + ENTER? Нет, не знаю. Это приносит мне все продукты, которые идут вот так, верно? Итак, возвращая все VPP, если мы хотим добавить их, да, если мы хотим объединить их, да. (= TEXTJOIN («,», True, IF ($ A $ 2 : $ A $ 53 = E2, $ C $ 2 : $ C $ 53, «»))

Читайте так же:
Как посчитать отработанное время и время обеда в Excel?

Хорошо, теперь есть еще одна возможность, когда люди спрашивают меня, могут ли они вернуть все ВПР. Это может стать проблемой, когда мы хотим найти каждый из этих расходов здесь и рассчитать ВПР, а затем сложить их все. Например, мне не нужны ВПР здесь, ВПР там, ВПР здесь и ВПР здесь. Я просто хочу суммировать их все, поэтому мы будем использовать функцию SUM, а затем старую функцию LOOKUP. LOOKUP говорит, что мы собираемся найти все эти значения в столбце B. Мне не нужна F4 здесь, потому что я никуда не буду их копировать. ,. Вот наша таблица поиска. ), закройте SUMM, и он выйдет и сделает каждый отдельный VPR, а затем просуммирует их все таким образом. (= SUMS (VIEW (B2: B53; K3: L5)))

Ну, здравствуйте. Все эти темы есть в моей книге LIV: 54 величайших совета всех времен. Для получения дополнительной информации нажмите на значок i в правом верхнем углу.

Тогда вопрос в том, можно ли вернуть все ВПР? Думаю, да, но без SOP. Решение будет включать SUMIF, TEXTJOIN, SUM или LOOKUP.

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

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

Вы знаете, что даже эту формулу можно улучшить, используя динамические массивы. Для поиска необходимо использовать TRUE. Что если вы хотите использовать FALSE? Мы можем изменить его на ERP, найти весь этот текст в этой таблице, 2,. В этом случае я бы использовал TRUE, но в противном случае можно использовать FALSE. CONTROL + SHIFT + ENTER. Нет, это просто сработает, верно? (= AMOUNT (SMP (B2: B53; K3: L5,2; True))

Введение динамических плат в начале 2019 года решит многие проблемы.

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

Скачать файл Excel

Чтобы скачать файл Excel: can-you-return-all-vlookup-values.xlsx

Когда кто-то спрашивает: «Может ли функция VLOOKUP вернуть все совпадения? «, ответ — нет. Однако существует множество других функций, которые могут делать то же самое.

Идея на сегодняшний день в Excel

Я обратился за советом к своим друзьям из группы Excel. Сегодняшняя идея — подумать об этом:

«Стандартизируйте свои данные так, как если бы другие стандартизировали свои данные для вас.

Кевин Лербасс

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