Check-moscow.ru

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

Как выполнить обратный просмотр vlookup в обратном порядке в Excel?

Как выполнить обратный просмотр vlookup в обратном порядке в Excel?

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

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

Пример способов использования функции ПРОСМОТР

Если вы хотите выполнить поиск только в одном столбце или строке, используйте функцию SCAN. В приведенном выше примере найдена цена в столбце D.

Рассмотрите одну из новых функций подведения итогов в зависимости от версии вашего Office.

F FT осуществляет поиск данных в одной строке или столбце, или в нескольких строках и столбцах таблицы (например). Это расширенная версия функции SCROLL. Посмотрите видеоролик о том, как использовать функцию VRP.

Если вы используете Microsoft 365, используйте XLOOKUP: он не только быстрее, но и позволяет искать в любом направлении (вверх, вниз, влево, вправо).

Как использовать функцию ВПР в Excel?

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

В этом руководстве рассказывается, как использовать функцию ВПР, подробно объясняя синтаксис, аргументы, возвращаемое значение, примечания к функциям и простые примеры.
Кроме того, в конце руководства мы перечисляем множество практических примеров часто используемых формул VLOOKUP, которые включают Vlookup на разных листах или книгах, Vlookup и сохранение форматирования ячейки результата, возврат vlookup несколько результатов и другие формулы vlookup в общих случаях.

Синтаксис

Аргументы

Lookup_value ( required ): Значение, которое вы ищете. Он должен находиться в первом столбце области table_array.

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

Col_index (требуется): номер конкретного столбца (является целым числом) таблицы table_table, из которого будет возвращаться значение совпадения.

  • Число в первой колонке равно 1 для самой левой колонки table_array.

Range_lookup (необязательно): булево значение, указывающее, возвращает ли функция VLOOKUP точное или приблизительное соответствие:

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

Важно : В этом случае значения в первом столбце table_array должны быть отсортированы в порядке возрастания;
В противном случае функция VLOOKUP может не вернуть правильное значение.

  • Если range_lookup равен FALSE или 0, функция FFT будет возвращать только точное совпадение. Если точное совпадение не найдено, будет возвращена ошибка # N/A. Но если первый столбец table_array имеет два или более значений, которые соответствуют lookup_value, vlookup будет использовать только значение первого из найденных.
Читайте так же:
Как выделить строку, если ячейка содержит дату в Excel?

В этом случае значения в первой колонке table_array не должны сортироваться.

Возвращаемое значение

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

Примечания к функциям

1. Функция ПРОСМОТР ищет значение только слева направо..
Значение поиска находится в крайнем левом столбце, а значение результата должно быть в любом столбце справа от значения поиска.
2. Существует два режима сопоставления для функции ВПР. Они являются точным совпадением и приблизительным совпадением.
Функция ВПР по умолчанию будет использовать приблизительное совпадение.
3. Она вернет значение ошибки # Н/Д, если искомое значение не может быть найдено .

Примеры

В этом разделе используется функция VOOKUP из Excel.

Пример 1: точное совпадение VS приблизительное совпадение с функцией VLOOKUP

1.1 Просматривание точного совпадения :

Как показано на скриншоте ниже, как искать значение точного соответствия «Персик» и возвращать соответствующее значение из третьего столбца диапазона в таблице B3: E8? Для этого вы можете выполнить следующие действия

1. Выделите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите клавишу Enter для получения результата.

1.2 Приблизительное соответствие просмотру :

Вы хотите найти номер заказа «10006», если он не может быть найден, сделайте приблизительное совпадение.

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

Примечания :

  • В двух приведенных выше формулах G4 и H4 — это ссылки на ячейки, содержащие искомые значения;
  • B3: E8 и B3: F8 — это диапазоны таблиц, в которых находятся столбец значения поиска и столбец значения результата;
  • 3 — номер столбца, в котором будет возвращено совпадающее значение;
  • FALSE указывает на нахождение в этом случае значения точного совпадения;
  • TRUE выполняет поиск приблизительного совпадения. Поскольку искомое значение 10006 не было найдено в первом столбце, оно будет соответствовать следующему наименьшему значению 10005 и вернет результат 9,00 долларов США из третьего столбца таблицы.
Пример 2: Vlookup возвращает пустое значение или конкретное значение вместо # Н/Д

Выполнение точного совпадения с помощью функции FFT возвращает значение ошибки # N/A, если нужное значение не найдено. См. скриншот :

Как я могу оставить ячейку результата пустой или вернуть конкретное значение вместо # N/A, когда искомое значение не найдено? Решение показано ниже.

1. Скопируйте приведенную ниже формулу в ячейку, в которой вы хотите отобразить результат, и нажмите Enter:

В ячейке, содержащей результат, будет отображаться «не найдено» в формуле. вместо ошибки # N/A. Если ячейка результата должна оставаться пустой, замените «не найдено» пробелом. И вы можете изменить его в зависимости от ваших потребностей.

Дополнительные примеры

Формулы Vlookup — в книгах/листах

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

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

Vlookup для сравнения двух списков на отдельных листах
Предположим, что есть два списка имен, расположенных на разных листах «Имя-1» и «Имя-2», для сравнения этих двух списков и поиска совпадающих имен в Имена-1 i f они выходят в Names-2. Формула ВПР в этой статье окажет вам услугу.
Нажмите, чтобы узнать больше …

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

Формулы Vlookup — сохранить форматирование результатов

Сохранить форматирование ячеек при Vlookup
Обычно при применении формулы VLOOKUP вы получите результат без форматирования. Как сохранить исходное форматирование ячейки результата? Вот решение.
Нажмите, чтобы узнать больше …

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

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

Vlookup и вернуть совпадающее значение с комментарием к ячейке
Когда вы применяете функцию Vlookup для возврата совпадающего значения, она вернет только значение. Иногда вам может потребоваться vlookup и вернуть совпадающее значение, включая его исходный комментарий. Как ты мог это исправить?
Нажмите, чтобы узнать больше …

Формулы Vlookup — возврат нескольких результатов

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

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

Vlookup и возврат нескольких значений без дубликатов
Иногда вам может потребоваться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но как сохранить уникальные значения только при появлении повторяющихся результатов? Вот решение.
Нажмите, чтобы узнать больше …

Читайте так же:
Как выделить даты старше года в Excel?

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

Формулы Vlookup — другие распространенные случаи

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

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

Vlookup определенное значение и возвращает только истину или ложь/да или нет
Обычно при использовании формулы Vlookup для поиска точно совпадающего значения возвращается значение ошибки # Н/Д, если значение может не может быть найден, или вернуть правильный результат при сопоставлении значения. Однако в некоторых случаях нам нужно только вернуть Yes или No/True или False, чтобы просто напомнить себе, существует ли значение. Решение, описанное в этой статье, может помочь.
Нажмите, чтобы узнать более…

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

VBA обратный цикл For Loop с инструкцией STEP

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

Счетчик переменных имеет одно значение.

Как вы могли заметить, мы можем использовать оператор Stepn для запуска цикла как вперед, так и назад. Значение по умолчанию для Stepn равно 1, но его можно изменить, если мы хотим пропустить некоторые значения, чтобы n стало больше 1, или если мы хотим двигаться в обратном направлении, чтобы n стало отрицательным.

Левый ВПР

Настоятельно рекомендуется сначала посмотреть этот видеоурок, если вы новичок в функции VLOOKUP.

Проблема

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

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

Однако на практике часто возникают ситуации, когда, например, вы хотите искать данные в столбце справа, но не слева от столбца с результатами:

Проблема с левым ВПР

Найти стоимость по коду заказа очень просто — обычный BPR поможет вам в кратчайшие сроки. Однако как по коду определяется наименование товара? На тренингах этот вопрос чаще всего задают как «а как вы делаете левый надзор? «.

Давайте рассмотрим несколько способов, как это сделать.

Способ 1. Лобовая атака

Используя принцип Оккама, можно скопировать нужный столбец вправо (или сделать его ссылкой) и использовать обычный VRP:

Копируем столбец правее

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

Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР

Если вы не можете переставить столбцы на листе, вы можете сделать это виртуально, то есть «на лету», прямо в самой формуле. Это можно сделать с помощью функции CHOOSE. Как правило, она используется для выбора элемента по номеру из списка. Ее можно использовать, например, для замены дня недели на его текстовый аналог:

Функция ВЫБОР

На первый взгляд это не кажется сверхъестественным, но есть несколько хитростей.

Во-первых, вместо текстовых имен выбранных элементов списка («Mon», «Tue» и т.д.) можно использовать адреса диапазонов. А затем функция вернет ссылку на выбранный диапазон. Так, например, формула

=ВЫБОР(2; A1:A10 ; D1:D10 ; B1:B10 )

… выдает ссылку на второй указанный диапазон (D1:D10).

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

=ВЫБОР( ; A1:A10 ; D1:D10 ; B1:B10 )

В результате две первые ленты (A1:A10 и D1:D10) будут склеены вместе.

Теперь мы можем осуществить «поиск слева» с помощью нашего ВПР:

Левый ВПР с помощью ВЫБОР

Единственное отличие от «классического FFT» заключается в том, что диапазон задается путем склеивания двух столбцов Код заказа и Товар с помощью функции SELECT. В остальном все знакомо.

Два основных недостатка этого метода — скорость (примерно в 5-7 раз медленнее стандартного FPR) и отсутствие знакомства с коллегами (но, возможно, это даже плюс!).

Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ

Если вы не хотите полагаться только на функцию FFT, вы можете использовать ее более мощный аналог — комбинацию двух очень полезных функций, INDEX и MATCH :

ИНДЕКС и ПОИСКПОЗ для реализации левого ВПР

Функция ПОИСКПОЗ ищет заданное значение (C2, то есть нужный нам код заказа) в одномерном диапазоне (столбец кода в таблице C10:C25) и выводит порядковый номер ячейки, в которой оно найдено — в нашем случае это будет число 4, так как нужный нам код заказа является четвертым по счету в таблице.

Читайте так же:
Как заблокировать определенные столбцы, всегда видимые на листе или в книге?

И тут в игру вступает функция INDEX, которая может извлечь данные из вертикального массива-столбца (наименования товаров в B10:B25) по серийному номеру (который ранее был найден с помощью SEARCH). Содержимое четвертой ячейки из столбца Товар выдается функцией ИНДЕКС.

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

Я уже анализировал подобный пример (с видео) в этой статье. И мы еще долго можем говорить о функции INDEX 🙂

Способ 4. Функция СУММЕСЛИ(МН)

Если из таблицы нужно извлечь число (например, объем в литрах), иногда проще использовать функцию выборочного суммирования SUMIF или ее старшую сестру SUMIFS, чтобы реализовать «левое БПФ»:

СУММЕСЛИ вместо ВПР

Недостатки этого подхода очевидны — он работает только для чисел и при условии, что в столбце нет повторяющихся значений. При наличии дублирующихся заказов (более одного с одинаковым кодом) эта функция суммирует все объемы, а не отправляет первый, как в случае с BPR. Она также примерно в 3-4 раза медленнее, чем обычное БПФ, поэтому не очень эффективна.

Здесь есть отличная статья о выборочном подсчете функций по одному или нескольким состояниям.

Способ 5. Готовая макрофункция из PLEX

Для тех, кого не пугает использование макросов, можно воспользоваться готовой функцией Visual Basic VLOOKUPS, которая входит в состав надстройки PLEX для Microsoft Excel. По сравнению с обычной VRP она может:

  • Поиск в нескольких столбцах одновременно (до 3)
  • Выводить результаты из любого столбца (левого или правого — неважно)
  • Выводить не только первое найденное значение, но и желаемое значение по порядку
  • Можно указать, что выводить, если ничего не найдено, вместо ошибки #N / D.

У этого метода есть два недостатка: необходимо сохранять файл макроса (XLSM), а скорость работы каждой макрофункции не очень высока — на больших таблицах она может быть значительно медленнее.

Действия после разворачивания таблицы и итоги работы.

Теперь, когда таблица расширена в обратном направлении столбцов, можно применить, в частности, функцию DAG.

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

Perevorot

Более подробно о функциях IEP и GPR мы поговорим на следующих уроках.

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

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

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