Excel vlookup формула: Функция ПРОСМОТРX — Служба поддержки Майкрософт

Функция ПРОСМОТРX — Служба поддержки Майкрософт

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

Примечание: XLOOKUP недоступна в Excel 2016 и Excel 2019, однако вы можете столкнуться с ситуацией использования книги в Excel 2016 или Excel 2019 с функцией XLOOKUP в ней, созданной другим пользователем с помощью более новой версии Excel.

Синтаксис

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

=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска]) 

Примеры

В примере 1    используется XLOOKUP для поиска названия страны в диапазоне, а затем возврата ее телефонного кода страны. Он включает аргументы lookup_value (ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Он не включает аргумент match_mode , так как по умолчанию XLOOKUP создает точное совпадение.

Примечание: XLOOKUP использует массив подстановки и возвращаемый массив, тогда как ВПР использует один массив таблиц, за которым следует номер индекса столбца. Эквивалентная формула ВПР в этом случае будет: =VLOOKUP(F2;B2:D11;3;FALSE)

———————————————————————————

В примере 2    выполняется поиск сведений о сотрудниках на основе идентификатора сотрудника. В отличие от ВПР, XLOOKUP может возвращать массив с несколькими элементами, поэтому одна формула может возвращать имя сотрудника и отдел из ячеек C5:D14.

———————————————————————————

В примере 3    к предыдущему примеру добавляется аргумент if_not_found .

———————————————————————————

В примере 4    в столбце C выполняется поиск личного дохода, указанного в ячейке E2, и поиск соответствующей налоговой ставки в столбце B. Он задает аргумент if_not_found для возврата 0 (ноль), если ничего не найдено. Аргумент match_mode имеет значение 1, что означает, что функция будет искать точное совпадение, а если не удается найти его, она возвращает следующий более крупный элемент. Наконец, аргумент search_mode имеет значение 1, что означает, что функция будет выполнять поиск от первого элемента к последнему.

Примечание: 
Lookup_array столбец XARRAY находится справа от return_array столбца, тогда как ВПР может смотреть только слева направо.

———————————————————————————

Пример 5    использует вложенную функцию XLOOKUP для выполнения вертикального и горизонтального совпадения. Сначала выполняется поиск валовой прибыли в столбце B, затем выполняется поиск Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращается значение на пересечении двух. Это аналогично совместному использованию функций INDEX и MATCH .

Совет: Для замены функции HLOOKUP можно также использовать XLOOKUP.

Примечание: Формула в ячейках D3:F3: =XLOOKUP(D2,$B 6:$B 17;XLOOKUP($C 3,$C 5:$G 5;$C 6:$G 17)).).

———————————————————————————

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

Формула в ячейке E3: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))

Как это работает? XLOOKUP возвращает диапазон, поэтому при вычислении формула выглядит следующим образом: =SUM($E$7:$E$9). Вы можете увидеть, как это работает самостоятельно, выбрав ячейку с формулой XLOOKUP, аналогичной этой, а затем выберите Формулы > Аудит формул > Оценить формулу, а затем выберите Оценить, чтобы выполнить вычисление.

Примечание: Спасибо MVP Microsoft Excel , Билл Елен, за то, что он предложил этот пример.

———————————————————————————

Дополнительные сведения

См. также

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Функция ПОИСКПОЗX

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Исправление ошибки #Н/Д в функции ВПР

В этом разделе описаны наиболее распространенные причины получения ошибочных результатов при использовании функции ВПР. Предоставляются рекомендации по использованию функций ИНДЕКС и ПОИСКПОЗ вместо нее.

Совет: Кроме того, ознакомьтесь с материалом Краткая справочная карточка: советы по устранению неполадок функции ВПР. На ней указаны основные причины получения результата #Н/Д. Сведения приводятся в удобном формате PDF. Файл в формате PDF можно распечатать или предоставить другим пользователям.

Проблема: искомое значение не находится в первом столбце аргумента

таблица

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

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

Ошибка #Н/Д #N/A возникает, поскольку значение поиска «Капуста» находится во втором столбце (Продукты) аргумента таблица A2:C10. В этом случае Excel ищет значение в столбце A, а не в столбце B.

Решение. Чтобы исправить ошибку, измените ссылку ВПР так, чтобы она указывала на правильный столбец. Если это невозможно, попробуйте переместить столбцы. Это тоже может быть весьма неудобно при использовании больших или сложных таблиц, где значения в ячейках получены в результате других вычислений (или невозможно перемещать столбцы по другим причинам). В таком случае можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ, которые позволяют находить значения в любом столбце вне зависимости от его позиции в таблице подстановки. (см. следующий раздел).

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

Функции ИНДЕКС и ПОИСКПОЗ можно эффективно применять во многих случаях, когда функция ВПР не позволяет получить нужные результаты. Основное их преимущество заключается в том, что значения можно искать в столбце таблицы в любой позиции в таблице подстановки. Функция ИНДЕКС возвращает значение из указанной таблицы или диапазона в соответствии с его позицией. Функция ПОИСКПОЗ возвращает относительную позицию значения в диапазоне или таблице. Используя функции ИНДЕКС и ПОИСКПОЗ вместе, можно находить значение в таблице или в массиве, указав относительную позицию значения.

Существует несколько преимуществ использования функций ИНДЕКС и ПОИСКПОЗ вместо ВПР.

• 
  При использовании функций ИНДЕКС и ПОИСКПОЗ возвращаемое значение не обязательно должно находиться в том же столбце, что и столбец подстановки. При использовании функции ВПР возвращаемое значение, напротив, должно быть в указанном диапазоне. Почему это важно? При использовании функции ВПР вам нужно знать номер столбца, содержащего значение. Это может показаться не слишком сложным, но это всерьез затрудняет работу, если используется большая таблица, в которой нужно подсчитать количество столбцов. Кроме того, если добавить или удалить столбец. придется пересчитать столбцы и изменить значение аргумента номер_столбца. При использовании функций ИНДЕКС и ПОИСКПОЗ не нужно подсчитывать столбцы.

• 
  При использовании функций ИНДЕКС и ПОИСКПОЗ можно указать строку или столбец (или и строку, и столбец) в массиве. Это означает, что значения можно искать по вертикали и по горизонтали.

• 
  С помощью функций ИНДЕКС и ПОИСКПОЗ можно находить значения в любом столбце. В отличие от функции ВПР, которая находит только значения в первом столбце таблицы, функции ИНДЕКС и ПОИСКПОЗ будут работать вне зависимости от того, в каком столбце находится значение.

• org/ListItem»>

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

• 
  Функции ИНДЕКС и ПОИСКПОЗ обеспечивают более гибкие возможности поиска.Они могут находить точное совпадение, а также значение больше или меньше искомого. ВПР ищет только наиболее близкое (по умолчанию) или точное значение. Кроме того, функция ВПР предполагает, что первый столбец в таблице отсортирован в алфавитном порядке, и возвращает первое наиболее близкое совпадение, поэтому вы можете получить не те данные, которые ожидали.

Синтаксис

Чтобы создать синтаксис для функций ИНДЕКС или ПОИСКПОЗ, необходимо вложить синтаксис функции ПОИСКПОЗ в аргумент массива или ссылки функции ИНДЕКС. Это выглядит следующим образом:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])

Заменим функцию ВПР в приведенном выше примере функциями ИНДЕКС и ПОИСКПОЗ. Синтаксис будет выглядеть следующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

Что означает:

=ИНДЕКС(возвратить значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Проблема: не найдено точное совпадение

Если для аргумента диапазон_поиска задано значение ЛОЖЬ, а функции ВПР не удается найти точное совпадение, возвращается ошибка #Н/Д.

Решение. Если вы уверены, что необходимые данные действительно есть в таблице, но функции ВПР не удается их найти, убедитесь, что в ячейках нет скрытых пробелов или непечатаемых символов. Кроме того, убедитесь, в ячейках выбран правильный тип данных. Например, для ячеек с числами необходимо выбрать формат Числовой, а не Текстовый.

Также можно использовать функции ПЕЧСИМВ или СЖПРОБЕЛЫ для очистки данных в ячейках.

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если для аргумента диапазон_поиска задано значение ИСТИНА, а искомое значение меньше наименьшего значения в массиве, возвращается ошибка #Н/Д. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Решение.

• Исправьте искомое значение.

• Если невозможно изменить искомое значение, а при подстановке значений требуется более высокая гибкость, попробуйте использовать функции ИНДЕКС и ПОИСКПОЗ вместо ВПР — см. раздел выше в этой статье. Они позволяют находить значения больше или меньше искомого, а также равные ему. Дополнительные сведения см. в предыдущем разделе этой статьи.

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если для аргумента диапазон_поиска задано значение ИСТИНА, но один из столбцов не отсортирован по возрастанию (от А до Я), возвращается ошибка #Н/Д.

Решение.

• Измените функцию ВПР так, чтобы искать точное совпадение. Для этого укажите для аргумента диапазон_поиска значение ЛОЖЬ. Для значения ЛОЖЬ сортировка не требуется.

• Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

При наличии в ячейках значений времени или больших десятичных чисел Excel возвращает ошибку «#Н/Д» из-за точности чисел с плавающей запятой. Числа с плавающей запятой включают цифры после десятичной запятой. (Значения времени хранятся в Excel в виде чисел с плавающей запятой. ) Excel не может хранить крупные числа с плавающей запятой, поэтому для правильной работы функции такие числа нужно округлять до 5 десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

• 
  Исправление ошибки #Н/Д

• 
  ВПР: как избавиться от ошибок #Н/Д

• org/ListItem»>

  
  Арифметические операции со значениями с плавающей запятой могут выдавать неточные результаты в Excel

• 
  Краткий справочник: функция ВПР

• 
  Функция ВПР

• 
  Полные сведения о формулах в Excel

• 
  Рекомендации, позволяющие избежать появления неработающих формул

• org/ListItem»>

  
  Поиск ошибок в формулах

• 
  Все функции Excel (по алфавиту)

• 
  Функции Excel (по категориям)

Функция

XLOOKUP — служба поддержки Microsoft

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

Примечание. Функция XLOOKUP недоступна в Excel 2016 и Excel 2019., однако вы можете столкнуться с ситуацией использования книги в Excel 2016 или Excel 2019 с функцией XLOOKUP в ней, созданной кем-то другим, использующим более новую версию Excel.

Синтаксис

Функция XLOOKUP выполняет поиск в диапазоне или массиве, а затем возвращает элемент, соответствующий первому найденному совпадению. Если совпадений нет, XLOOKUP может вернуть ближайшее (приблизительное) совпадение.

= XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode]) 

Примеры

Пример 1     использует функцию XLOOKUP для поиска названия страны в диапазоне, а затем возвращает ее телефонный код страны. Он включает аргументы lookup_value (ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Он не включает аргумент match_mode , так как XLOOKUP по умолчанию выдает точное совпадение.

Примечание. XLOOKUP использует массив поиска и возвращаемый массив, тогда как VLOOKUP использует один массив таблиц, за которым следует порядковый номер столбца. Эквивалентная формула ВПР в этом случае будет выглядеть так: = ВПР(F2,B2:D11,3,ЛОЖЬ)

—————————————————————————

Пример 2     ищет информацию о сотруднике по его идентификационному номеру. В отличие от ВПР, XПР может возвращать массив с несколькими элементами, поэтому одна формула может возвращать как имя сотрудника, так и отдел из ячеек C5:D14.

—————————————————————————

Пример 3     к предыдущему примеру добавляется аргумент if_not_found .

—————————————————————————

Пример 4     ищет в столбце C личный доход, указанный в ячейке E2, и находит соответствующую налоговую ставку в столбце B. Он устанавливает if_not_found аргумент для возврата 0 (ноль), если ничего не найдено. Аргумент match_mode имеет значение 1, что означает, что функция будет искать точное совпадение и, если не найдет, возвращает следующий больший элемент. Наконец, аргумент search_mode имеет значение 1 , что означает, что функция будет искать от первого элемента до последнего.

Примечание: XARRAY’s lookup_array 9Столбец 0004 находится справа от столбца return_array , тогда как функция ВПР может смотреть только слева направо.

—————————————————————————

Пример 5     использует вложенную функцию XLOOKUP для вертикального и горизонтального сопоставления. Сначала он ищет Валовая прибыль в столбце B, затем ищет Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращает значение на пересечении двух значений. Это похоже на совместное использование функций ИНДЕКС и ПОИСКПОЗ.

Совет: Вы также можете использовать XLOOKUP вместо функции HLOOKUP.

Примечание: Формула в ячейках D3:F3: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) .

—————————————————————————

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

Формула в ячейке E3: =СУММ(XПР(B3,B6:B10,E6:E10):XПР(C3,B6:B10,E6:E10))

Как это работает? XLOOKUP возвращает диапазон, поэтому при вычислении формула выглядит так: =СУММ($E$7:$E$9). Вы можете увидеть, как это работает самостоятельно, выбрав ячейку с формулой XLOOKUP, похожей на эту, затем выберите  Formulas > Formula Auditing > Evaluate Formula, а затем выберите Оцените , чтобы выполнить вычисления.

Примечание. Спасибо Биллу Джелену, MVP Microsoft Excel, за предложение этого примера.

—————————————————————————

Нужна дополнительная помощь?

​​​​​

См. также

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Функция XMATCH

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Видео: ВПР — когда и как использовать

ВПР — когда и как использовать

Обучение Эксель 2013.

ВПР — когда и как использовать

ВПР — когда и как использовать

ВПР: когда и как использовать

• ВПР: когда и как использовать
  

  видео

• Гайки и болты VLOOKUP
  

  видео

• Поиск значений
  

  видео

• Скопируйте формулу ВПР
  

  видео

Следующий:

Расширенные функции ЕСЛИ

Важно: Попробуйте использовать новую функцию XLOOKUP, улучшенную версию VLOOKUP, которая работает в любом направлении и возвращает точные совпадения по умолчанию, что делает ее более простой и удобной в использовании, чем ее предшественница. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена ​​последняя версия Office.

Основы использования ВПР.

Использовать ВПР

1. В строке формул введите = ВПР( ) .

2. В скобках введите искомое значение, а затем запятую. Это может быть фактическое значение или пустая ячейка, которая будет содержать значение: (ч3,

3. Введите массив таблиц или таблицу поиска, диапазон данных, которые вы хотите найти, и запятую: (h3,B3:F25,

4. org/ListItem»>

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

5. Введите значение поиска диапазона: ИСТИНА или ЛОЖЬ . TRUE находит частичные совпадения, FALSE находит точные совпадения. Ваша готовая формула выглядит примерно так: =ВПР(h3,B3:F25,3,ЛОЖЬ)

Хотите больше?

Функция ВПР

Краткий справочник: Напоминание о ВПР

Краткая справочная карта: советы по устранению неполадок функции ВПР

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

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

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

Для этого я щелкну ячейку, в которой я хочу увидеть цены, введу знак =, ВПР и круглые скобки.

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

Я введу h3 в качестве первого аргумента, потому что именно там я буду вводить номера деталей.

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

Номера деталей начинаются в ячейке B3, и если я прокрутю вниз, вы увидите, что значения состояния заканчиваются в ячейке E52.

Итак, я введу B3, двоеточие и E52, затем еще одну запятую.