Элементы соответствия EXCEL в прерывистом диапазоне

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

Позвольте мне привести примерный пример: у меня есть два списка элементов, в этом случае имена мальчиков и имена девочек. Они будут помещены в разные листы. На рисунке ниже, для простоты, они помещаются в разные столбцы (диапазон прерываний). Я хочу, чтобы формула генерировала список в ячейках A3: A14 (список всех элементов в прерывистом диапазоне, который соответствует содержанию текста «jo».

введите описание изображения здесь

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

Пока что у меня есть:

{IF( MATCH( INDEX(Named_Range, SMALL(IF(Named_Range="*"&$A$3&"*", ROW(Named_Range)-ROW(INDEX(Named_Range,1,1))+1),ROW()-1)), '(Names)'!C:C, 0), INDEX(Named_Range, SMALL(IF(Named_Range="*"&$A$3&"*", ROW(Named_Range)-ROW(INDEX(Named_Range,1,1))+1), ROW()-1)), "") } 

Named_Range объявлен как =D9:D13,F3:F6

Маленькая функция возвращает n-й элемент, соответствующий критериям. Он также должен СОЗДАТЬ одно из имен в (Имена)! C: C. Если это так, я получаю значение этой ячейки. Для простоты рассмотрим, что «(Имена)»! C: C содержит все имена, поэтому он всегда будет правдой.

Эта формула работала для меня, используя один столбец в качестве диапазона. Тем не менее, я не могу оценить формулу с использованием прерывистого диапазона без сбоя Excel, поэтому я ищу совет, как правильно это сделать. Благодарю.

Возможно, лучший способ справиться с прерывистым диапазоном – сделать его непрерывным …?

Ваша проблема может быть упрощена благодаря небольшой функции VBA. Эта функция принимает прерывистый именованный диапазон и возвращает один непрерывный столбец, содержащий все те же значения. Таким образом, вы можете использовать ваши формулы INDEX/MATCH/SMALL т. Д. Обычным способом

 Function Arrange(rng As Range) As Variant Dim temp As Variant Dim i As Long Dim r As Range ReDim temp(1 To rng.Cells.Count) i = 1 For Each r In rng temp(i) = r.Value i = i + 1 Next r Arrange = Application.Transpose(temp) End Function 

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

 =INDEX(ARRANGE(Named_Range),1) 

Например, для получения первого элемента.

  • Насколько велика большая?
  • Excel: как вставить дефис для пустых строк?
  • Частичное, Точное совпадение для двух столбцов
  • EXCEL: ссылка на содержимое ячейки внутри LINE ()
  • Ссылка на столбец с использованием строки из соседней ячейки
  • Строка в столбце, но 2by2
  • Проверьте наличие значения и, если да, если другие ячейки в этой строке попадают в диапазон
  • Формула для автоматического изменения истекших дат
  • Отображать смежные данные с наибольшим значением в пределах диапазона, включая дубликаты
  • В Excel, как получить левые 5 символов каждой ячейки в указанном столбце и поместить их в новый столбец
  • Приблизительно в пределах диапазона Excel?
  • Давайте будем гением компьютера.