Note: The other languages of the website are Google-translated. Back to English

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

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

Vlookup и вернуть все соответствующие значения по вертикали

Vlookup и вернуть все соответствующие значения по горизонтали

Vlookup и вернуть все соответствующие значения в одну ячейку


Vlookup и вернуть все соответствующие значения по вертикали

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

1. Введите или скопируйте эту формулу в пустую ячейку, в которую вы хотите вывести результат:

=IFERROR(INDEX($B$2:$B$15, SMALL(IF($D$2=$A$2:$A$15, ROW($A$2:$A$15)-ROW($A$2)+1), ROW(1:1))),"" )

Внимание: В приведенной выше формуле B2: B15 содержит ли столбец соответствующую запись, которую вы хотите вернуть; A2: A15 столбец содержит критерий; и D2 - это конкретный критерий, на основе которого вы хотите возвращать значения. Пожалуйста, измените их по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое значение, а затем перетащите дескриптор заполнения вниз, чтобы получить все соответствующие записи по мере необходимости, см. снимок экрана:


Vlookup и вернуть все соответствующие значения по горизонтали

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

1. Введите или скопируйте эту формулу в пустую ячейку, в которую вы хотите вывести результат:

=IFERROR(INDEX($B$2:$B$15,SMALL(IF($E$1=$A$2:$A$15,ROW($A$2:$A$15)-ROW($A$2)+1),COLUMN(A1))),"")

Внимание: В приведенной выше формуле B2: B15 содержит ли столбец соответствующую запись, которую вы хотите вернуть; A2: A15 столбец содержит критерий; и D2 - это конкретный критерий, на основе которого вы хотите возвращать значения. Пожалуйста, измените их по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое значение, а затем перетащите маркер заполнения вправо, чтобы получить все необходимые записи, как вам нужно, см. снимок экрана:


Vlookup и вернуть все соответствующие значения в одну ячейку

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

1. Введите или скопируйте формулу ниже в пустую ячейку:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))

Внимание: В приведенной выше формуле B2: B15 содержит ли столбец соответствующую запись, которую вы хотите вернуть; A2: A15 столбец содержит критерий; и D2 - это конкретный критерий, на основе которого вы хотите возвращать значения. Пожалуйста, измените их по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter ключи вместе, чтобы собрать все совпадающие значения в одну ячейку, см. снимок экрана:

Советы: Эта формула успешно применяется только в Excel 2016 и более поздних версиях. Если у вас нет Excel 2016, просмотрите здесь чтобы снять это.


Более относительные статьи о Vlookup:

  • Vlookup и возврат данных соответствия между двумя значениями в Excel
  • В Excel мы можем применить обычную функцию Vlookup, чтобы получить соответствующее значение на основе заданных данных. Но иногда нам нужно выполнить vlookup и вернуть значение соответствия между двумя значениями, как бы вы могли справиться с этой задачей в Excel?
  • Vlookup и возврат нескольких значений из раскрывающегося списка
  • В Excel, как вы могли vlookup и возвращать несколько соответствующих значений из раскрывающегося списка, что означает, что когда вы выбираете один элемент из раскрывающегося списка, все его относительные значения отображаются одновременно, как показано на следующем снимке экрана. В этой статье я расскажу о решении шаг за шагом.
  • Vlookup возвращает пустое значение вместо 0 или н / д в Excel
  • Обычно, когда вы применяете функцию vlookup для возврата соответствующего значения, если соответствующая ячейка пуста, она возвращает 0, а если подходящее значение не найдено, вы получаете сообщение об ошибке # N / A. Как сделать так, чтобы вместо отображения значения 0 или # N / A отображалась пустая ячейка?
  • Vlookup возвращает несколько столбцов из таблицы Excel
  • На листе Excel вы можете применить функцию Vlookup, чтобы вернуть совпадающее значение из одного столбца. Но иногда вам может потребоваться извлечь совпадающие значения из нескольких столбцов, как показано на следующем снимке экрана. Как можно было получить соответствующие значения одновременно из нескольких столбцов с помощью функции Vlookup?
  • Значения Vlookup на нескольких листах
  • В excel мы можем легко применить функцию vlookup, чтобы вернуть совпадающие значения в одной таблице рабочего листа. Но задумывались ли вы когда-нибудь над тем, как получить значение vlookup на нескольких листах? Предположим, у меня есть следующие три листа с диапазоном данных, и теперь я хочу получить часть соответствующих значений на основе критериев из этих трех листов.


  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
вкладка kte 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (5)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо, вы экономите много работы здесь!





Что касается вашей формулы {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

Я немного изменил: (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





С Уважением,



Джефф
Этот комментарий был сведен к минимуму модератором на сайте
Как вы можете настроить формулу, чтобы у вас было несколько выходных данных в столбце D? Например, я хочу заполнить столбец D каждой из стран, чтобы все их соответствующие города были возвращены в E? Единственная работа, которую я вижу, это ручное изменение $D$2
Этот комментарий был сведен к минимуму модератором на сайте
После того, как я расширил данные, в некоторых ячейках вместо пустых отображается «0».
Этот комментарий был сведен к минимуму модератором на сайте
Это означает, что в ячейке (ячейках) из столбца, на который ссылается, отсутствуют данные или данные неправильного типа. Например, я работаю с кучей данных и мне нужны все идентификационные номера работников под определенным руководителем. Однако в столбце таблицы, на который ссылаются идентификационные номера всех работников, в некоторых ячейках указаны фамилии рабочих, а не их идентификационные номера. Эти точные ячейки производили 0, когда была введена вся формула, и это потому, что это был текст в столбце, который в основном состоит из чисел.
Этот комментарий был сведен к минимуму модератором на сайте
Как сделать так, чтобы все строки заполнялись, как в твоем видео, без нажатия ctrl+shift+enter
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места