У меня есть две таблицы: информация и список.
В информационной таблице есть два столбца: имя и пол.
Таблица списка также имеет два столбца: мужской и женский.
Я пытаюсь автоматически заполнить таблицу List именами мужчин или женщин, используя формулы.
Я пытался использовать INDEX() и MATCH()
=INDEX(A:A, MATCH(D1, B:B, 0),1)
но он возвращает только имя с соответствующим полом.
Есть идеи?
Поскольку сопоставленные данные пропускают строки, вы не можете использовать простую формулу индекса / соответствия для извлечения имен (то есть без использования вспомогательных столбцов).
Самое простое решение требует использования формулы массива:
Массив введите (Ctrl+Shift+Enter) следующую формулу в D2 и скопируйте-вставьте / заполните-вниз / заполните-вправо в остальные столбцы таблицы D и E (не забудьте удалить { и }):
{=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE),ROW()-1)),"")}
Формула работает, изначально создавая массив, который содержит индекс строки, если пол совпадает, или FALSE противном случае: IF($B$2:$B$7=D$1,ROW($B$2:$B$7),FALSE) .
Затем функция SMALL() используется для извлечения следующего наименьшего индекса, соответствующего строке данных таблицы List: SMALL({…},ROW()-1) . Это работает потому, что функция SMALL() игнорирует логические значения.
Обратите внимание, что при вводе формулы в любую строку, отличную от строки 2 , или если после ввода строки формулы вставляются / удаляются над первой строкой таблицы данных списка, часть ROW()-1 должна быть скорректирована так, чтобы результат 1 для первой строки данных.
Наконец, этот индекс используется для извлечения соответствующего имени: INDEX($A:$A,<next smallest index>) .
IFERROR() просто для того, чтобы скрыть #NUM! ошибки, которые возникают, когда функция SMALL() исчерпывает допустимые индексы для возврата.
Ниже приведена более надежная, но более сложная версия формулы, которая автоматически подстраивается под количество строк данных в информационной таблице и не ломается, если строки вставляются / удаляются над первой строкой данных таблицы List.
{=IFERROR(INDEX($A:$A,SMALL(IF($B$1:INDEX($B:$B,COUNTA($B:$B))=D$1,ROW($B$1:INDEX($B:$B,COUNTA($B:$B))),FALSE),ROW()-ROW($B$2)+1)),"")}