Тестирование нескольких результатов INDEX/MATCH, не глядя на них

Question

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

Пример:
Всех попросили собрать команду по боулингу (из четырех человек). В каждой команде должна быть хотя бы одна женщина. Каждый считает себя, а затем вводит "Y" для трех других членов своей команды.

Мне нужна формула, которая идентифицирует четырех членов команды, а затем проверяет их по основному гендерному списку и возвращает "True", если есть женщина.

Answer 1

Я бы сделал это, используя вспомогательный ряд.
Вы можете добавить его выше / ниже вашего диапазона, обновлять его с помощью формулы, а также скрывать. Формула в строке помощника:
=IF(INDEX($N$3:$N$11,MATCH(B2,$M$3:$M$11,0))="f",1,0)

Формула для проверки, входит ли в команду хотя бы одна женщина:
=SUM((B3:J3="y")*($B$1:$J$1))>0
Это формула массива, поэтому нажмите CTRL+SHIFT+ENTER после ее ввода.

Answer 2

У меня есть небольшая разница в ответе Мате.

• Создайте вспомогательную строку, которая просто дублирует столбец M , столбец "пол", но транспонируется (помещая пол каждого человека в столбец).  Например, используя строку 24, положить

  =INDEX($M$1:$M$10, COLUMN())
  

  в ячейку B24 и перетащите / заполните вправо, в столбец J Нам не нужно использовать MATCH() потому что, если (например) мы находимся в столбце F (т.е. столбце 6), который является столбцом Боба, то пол Боба находится в строке 6.  И нам не нужно делать IF() потому что мы можем проверить значение на следующем шаге.

  Как и в ответе Мате, вы можете скрыть этот вспомогательный ряд, если хотите.

  В качестве альтернативы, вы можете полностью исключить столбец M и просто сохранить гендеры подряд.

• Где-нибудь в строке 2 (например, ячейка N2) введите формулу

  =SUMPRODUCT(--(B2:J2="y"), --(B$24:J$24="f"))
  

  SUMPRODUCT() , как следует из его названия, умножает некоторые вещи, а затем добавляет продукты.  По сути, это то же самое, что SUM Мате SUM(range1*range2) , но его не нужно вводить как формулу массива.  Так как Мате не объяснил свой ответ: он смотрит на каждый столбец в диапазоне B - J (столбцы для членов).  Он просматривает текущую строку (показанную как 2), чтобы увидеть, является ли член (соответствующий этому столбцу) членом команды, соответствующей этой строке.  И он смотрит на строку 24, чтобы увидеть, является ли член женщиной.  Используйте -- для преобразования TRUE в 1 и FALSE в 0 .  Умножьте их, чтобы получить 1 для каждого члена этой команды, который является женщиной; затем добавьте эти единицы и нули, чтобы получить число женщин в команде.

  И, конечно же, перетащите / заполните строку 10.

И, конечно же, вы можете проверить, равно ли число 0, возможно с условным форматированием.

Answer 3

Спасибо всем, кто посмотрел и рассмотрел ответ. Продолжая работать над этой проблемой вчера, я пришел к выводу, что неправильно подхожу к этому. Я закончил тем, что изменил макет таблицы - по сути, разместив «у» на оси X. Окончательная формула влечет за собой гораздо больше, чем я первоначально просил, но в конце концов я обнаружил, что функция SMALL была ключевой; в этой версии я настроил его так, чтобы он возвращал значение для каждого вхождения (я ожидал только три), и создал одну формулу для каждого. После этого я связал его с HLOOKUP, и если была возвращена ошибка. альт!

= IF($ L8 = "+", IF(AND(ISERROR(ГПР (INDEX (КОСВЕННАЯ ("таблица!«& АДРЕС (79,55+MATCH (E $ 6, таблица!$ BD $ 78:$ CL $ 78,0)) & ":" & АДРЕС (130,55+MATCH (E $ 6, таблицы!$ BD $ 78:$ CL $ 78,0))), МАЛАЯ (IF(таблица!$ BC $ 79:$ BC $ 117 = $ K8, ROW (таблицы!$ BC $ 79:$ BC $ 117) -строка (INDEX (таблица!$ BC $ 79:$ BC $ 117,1,1))+1), 1)), $ E $ 2:$ K $ 3,1, FALSE)), ISERROR (HLOOKUP (INDEX (INDIRECT ("таблицы!")«& АДРЕС (79,55+MATCH (E $ 6, таблица!$ BD $ 78:$ CL $ 78,0)) & ":" & АДРЕС (130,55+MATCH (E $ 6, таблицы!$ BD $ 78:$ CL $ 78,0))), МАЛАЯ (IF(таблица!$ BC $ 79:$ BC $ 117 = $ K8, ROW (таблицы!$ BC $ 79:$ BC $ 117) -строка (INDEX (таблица!$ BC $ 79:$ BC $ 117,1,1))+1), 2)), $ E $ 2:$ K $ 3,1, FALSE)), ISERROR (HLOOKUP (INDEX (INDIRECT ("таблицы!")«& АДРЕС (79,55+MATCH (E $ 6, таблица!$ BD $ 78:$ CL $ 78,0)) & ":" & АДРЕС (130,55+MATCH (E $ 6, таблицы!$ BD $ 78:$ CL $ 78,0))), МАЛАЯ (IF(таблица!$ BC $ 79:$ BC $ 117 = $ K8, ROW (таблицы!$ BC $ 79:$ BC $ 117) -строка (INDEX (таблица!$ BC $ 79:$ BC $ 117,1,1))+1), 3)), $ E $ 2:$ K $ 3,1, FALSE))), "", "X"), "") как CSE ,