Один критерий поиска, несколько результатов

Question

Мне нужно вывести результаты из столбца отсортированных данных только с одним критерием поиска. Иногда есть несколько вхождений для одного и того же критерия. LOOKUP находит только первое вхождение. Мне нужно ввести в ячейку ячейку для сопоставления в J8:J581 и соответствующие данные для отображения isN8:N581

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

.

input=bob
output=    bob  RED
                BLUE
                Green

Answer 1

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

1. Выберите ваши данные
2. Создайте таблицу Insert>Tables>Table(установите флажок «Моя таблица имеет заголовки»)
3. После создания таблицы просто нажмите кнопку фильтра (стрелка вниз в строке заголовка таблицы) и выберите значение фильтра (например, в вашем примере выберите «Боб»). Это вернет только строки «Боб».

Вот изображение «до»:

И после:

РЕДАКТИРОВАТЬ: На основе вашей дополнительной информации я бы рассмотрел добавление сводной таблицы на основе вашей таблицы данных. Это позволит вам создать "обрисованное" представление вашей информации, чтобы лучше увидеть, какие кемпинги имеют только один идентификатор, а какие несколько идентификаторов. Это будет выглядеть примерно так:

Также, к сведению @pnuts, Excel ограничен 10 000 элементов в раскрывающемся списке (т. е. фильтре), но нет документированного ограничения на членов таблицы. У меня были таблицы с 10 000 строк.

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

1. Возьмите исходную таблицу данных и добавьте строку "счетчик". =IF([@Name]=D1,F1+1,1)Это вернет количество строк с одинаковым именем.
2. Создайте сводную таблицу Insert>Tables>Pivot Tableна основе ваших данных.
3. Отформатируйте сводную таблицу следующим образом:
   • Метки строк = Name, Code, Counter, и отключите промежуточные и общие итоги для всех значений.
   • Счетчик фильтров = Очистить 1, будут отображаться только имена с несколькими значениями.
   • Название фильтра = любое конкретное название кемпинга, которое вас интересует.

Answer 2

Допустим, ваша входная ячейка — A1, а выходные ячейки — A3 для имени и B3:B.. для выходного диапазона (при этом конец диапазона B спускается вниз до максимального количества результатов, которые вы ожидаете увидеть).

Для A3 введите формулу =A1.

Для B3 введите формулу

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

Это формула массива, и ее нужно ввести с помощью комбинации клавиш Control- Shift- Enter. Затем вы можете скопировать ее в нижнюю часть выходного диапазона.

Обратите внимание, что формула жестко закодирована для входного списка с максимальным количеством строк 99. Вы можете изменить ее на любую необходимую вам длину. Хотя можно было бы ссылаться на весь столбец (J:J и N:N), это привело бы к снижению производительности, чего вы, вероятно, хотите избежать.

Как работает формула

Работая изнутри наружу, формула сначала сравнивает имя, по которому должен быть выполнен поиск (ячейка A3), с полным списком имен (до 99 имен в диапазоне J1:J99). Это сравнение показано в строке 6 разбиения функции, показанного ниже.

Результатом этого сравнения является массив со значениями True для совпадения и значениями False для несовпадения, например, {False, False, False, False, True, True, False, ... и т. д.}.

Затем выполняется сравнение этого массива с массивом, который можно рассматривать как «номера строк» ​​списка имен: {1, 2, 3, 4, 5, 6, ... 99}. Это сравнение выполняется оператором IF в строках 6-8 таблицы формул.

Сравнение выполняется поэлементно. Если элемент массива сравнения имен равен True, то IF возвращает соответствующий ему номер строки; если элемент равен False, то IF возвращает FALSE. Используя два примера массивов выше, результатом оператора IF будет {False, False, False, False, 4, 5, False, ...}.

Продолжая, функция SMALL (начинающаяся в строке 8 структуры функции) используется для получения k-го наименьшего элемента этого нового массива из IF. «K» в этом случае предоставляется выражением ROWS($N$1:$N1), которое просто подсчитывает от 1 до 99, когда вся формула копируется вниз со строки 1 по строку 99 (ROWS($N$1:$N1) = 1, ROWS($N$1:$N2) = 2 и т. д.).

Итак, SMALL сначала найдет наименьший элемент массива, созданного IF,игнорируяэлементы, которые являются False. Другими словами, он вернет первый номер строки, где сравниваемое имя совпадает с именем в списке поиска имен. В нашем примере это число 4, как показано в 6-м столбце таблицы ниже.

Шаг завершения использует ИНДЕКС для значений поиска, чтобы извлечь элемент, соответствующий номеру строки, который был вычислен. В этом случае 4-й элемент в примере списка цветов для вопроса — «желтый». (ЕСЛИОШИБКА гарантирует, что будет показан пробел, когда формула не сможет найти совпадение.)

Это был результат, полученный первой копией полной формулы. Когда она копируется в следующую ячейку, единственное, что меняется в расчетах, — это значение «k» для функции НАИМЕНЬШИЙ, которое увеличивается до 2. А второй наименьший номер строки, который был найден, — это 5, что дает значение «белый».