Элементы соответствия 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) 

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

  • Записи Count X соответствуют определенным условиям и только там, где есть соответствующая запись Y
  • Excel считается уникальным с условием
  • excel = SIN (ошибка MOD ($ B5; 23) / 23 * 2 * PI ())
  • Поиск значения в столбце Excel
  • Функция INDIRECT, относящаяся к ячейке с текстом, не работает
  • Найдите <> в суммике
  • Date Выделить формулу Excel условное форматирование
  • Написание формулы для переиндексации столбца с дубликатами
  • Excel Logic без использования VBA
  • Автоматическое создание последовательного диапазона дат / номеров от сингулярного численного значения
  • Excel соответствует двум столбцам, а третий -
  • Interesting Posts

    Формула, не рассчитанная с помощью EPPLUS

    Excel VBA для использования между ними в разделе Where

    Как определить диапазон в excel, например Range («B & i: C» & j)

    Переименование файлов с помощью Excel VBA – GrantAccessToMultipleFiles

    Сделать макро независимым, который будет использоваться для генерации графика на нескольких листах в excel

    Как удалить дубликаты записей полностью

    Сортировка списка по дате, затем письмо

    Как заставить эту программу открывать последнюю измененную книгу даты по дате в названии

    Microsoft.Office.Tools.Excel.ApplicationFactory.GetVstoObject вызывает утечку памяти VBA?

    Как Excel вычисляет разрешение метафайлов, которые он генерирует при копировании диапазона «как показано на экране»?

    Как представить раскрытие данных, которое объединяет значения из 3 столбцов, но отображает только первый столбец после выбора

    Проверьте ошибки перед вызовом функции или внутри функции?

    Производительность чтения OpenXML слишком медленная при анализе файла excel

    Как фильтровать формулы на основе дат?

    Как экспортировать таблицу HTML в Excel с помощью сетки

    Давайте будем гением компьютера.