Критерии соответствия между двумя таблицами, чтобы найти максимальную дату в списке

У меня есть 2 таблицы данных, одна из которых состоит всего из 5 столбцов, а вторая - из 100 столбцов, но, упрощенно говоря, они имеют форматирование, указанное ниже. Оба состоят из сотен строк.

Таблица 1 (сортировка по возрастанию, все столбцы)

        A       B       C       D       E
    1   Date    TeamF   Trans   Name    TeamT
    2   date1   teamF1  trans1  name1   teamT1
    3   date2   teamF2  trans2  name2   teamT2
    4   date3   teamF3  trans3  name3   teamT3
    5   date4   teamF4  trans4  name4   teamT4
    6   date5   teamF5  trans5  name5   teamT5

Таблица 2 (без сортировки)

        A       B       C       D       E       F       G       ,etc.
    1   Team    Game    Date    Opp     NameA   NameB   NameC   ,etc...
    2   team1   game1   date1   opp1    statsA1 statsB1 statsC1 ,etc...
    3   team2   game2   date2   opp2    statsA2 statsB2 statsC2 ,etc...
    4   team3   game3   date3   opp3    statsA3 statsB3 statsC3 ,etc...
    5   team4   game4   date4   opp4    statsA4 statsB4 statsC4 ,etc...
    6   team5   game5   date5   opp5    statsA5 statsB5 statsC5 ,etc...

Я пытался получить формулу INDEX/MATCH, которую я обычно использую (ниже), немного более сложной, чтобы избежать некоторых ошибок, которые у меня были при обработке данных. Я пытаюсь извлечь TeamT имя из таблицы 1 и заполнить stats строк в таблице 2, столбцы E и далее. Я делаю это на основе

  1. совпадение Names между столбцом D таблицы 1 и строкой 1 таблицы 2
  2. Dates в таблице 1 является ‹= Dates в столбце C таблицы 2 по следующей формуле

моя текущая формула =INDEX(Table1!$A:$E,MATCH(1,(Table1!$D:$D=Table2!E$1)*(Table1!$A:$A<=Table2!$C2),0),5)

Как вы, вероятно, видите, если в таблице 1 есть несколько дат, меньших, чем C2, я получаю только первую. Чего бы очень хотелось, так это максимума (новейшего). Я играл с операторами MAX/IF и INDEX/MATCH/IF, но не могу получить правильный результат. Ниже приведены некоторые примеры тех неудачных попыток.

пример 1 =MAX(IF(Table1!$D:$D=Table2!E$1,IF(Table1!$A:$A<=Table2!$C2,Table1!$E:$E)))

пример 2 =INDEX(Table1!$E:$E,MATCH(MAX(IF(Table1!$D:$D=Table2!E$1,Table1!$A:$A<=Table2!$C2)),IF(Table1!$D:$D=Table2!E$1,Table1!$A:$A<=Table2!$C2),0))

Любая помощь будет оценена. Я пробовал много решений, которые нашел, но все еще выдергиваю волосы на этом.

У меня также есть второй вариант формулы, где я сопоставляю даты на основе Dates в Таблице 1, когда> = Dates в Таблице 2 столбец C. В этом случае мне нужно минимальное совпадение (самая старая дата). Полагаю, я бы просто использовал для этого MIN вместо MAX?


excel excel-formula excel-2010 excel-match
person NathanC    schedule 06.10.2017    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Если я правильно вас понял, вот ответ на ваш первый вопрос.

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

Формула массива (одновременно нажмите Ctrl + Shift + Enter), которую я использовал из cell K2:

=IFERROR(INDEX($E$2:$E$11,MATCH(MAX(IF(--($D$2:$D$11=K$1)*--($A$2:$A$11<$I2)*$A$2:$A$11,$A$2:$A$11)),IF(--($D$2:$D$11=K$1)*--($A$2:$A$11<$I2)*$A$2:$A$11,$A$2:$A$11),0)),"")

Вы можете перетащить это в нужные вам поля. Обратите внимание на это, чтобы найти самую последнюю дату. Потому что я не уверен на 100%, что это то, что вы ищете. Поэтому в моем примере из cell O2 мне удалось найти последнюю строку, и формула (обычная формула) следующая:

=IFERROR(INDEX($E$2:$E$11,LOOKUP(2,1/(--($D$2:$D$11=O$1)*--($A$2:$A$11<$I2)),(ROW($E$2:$E$11)-ROW(O$1)))),"")

Что касается второго вопроса, я предоставлю вам выяснить у этих двоих, и это должно помочь.

person ian0411    schedule 07.10.2017
comment
Большое спасибо. Ваше решение помогло найти дату, но не всегда самую последнюю. Я обнаружил новое решение после работы с функцией MAXIFS, чтобы найти самую последнюю дату, и соединил его с функцией VLOOKUP с несколькими критериями. Результат выложу здесь. - person NathanC; 07.10.2017
comment
Жаль, что в моем Excel нет MAXIFS для тестирования. Но формула массива (первая или та, которая используется в columns K, L and M) должна работать в вашей ситуации. Но я заметил одно отличие - это <= в вашем вопросе / ответе, что я пропустил знак = в своей формуле. Или вы не могли бы привести несколько примеров, чтобы я знал, в чем проблема в моей формуле, чтобы ее можно было улучшить в следующий раз? - person ian0411; 08.10.2017

arrow_upward
0
arrow_downward

Я сам решил это с помощью MAXIFS, чтобы найти максимальную дату и многокритериальную функцию ВПР.

это находит максимальную дату меньше, чем текущая строка, на основе совпадения имени

=VLOOKUP(MAXIFS(Table1!$A:$A,Table1!$D:$D,E$1,Table1!$A:$A,"<="&$C2)&E$1,CHOOSE({1,2},Table1!$A:$A&Table1!$D:$D,Table1!$E:$E),2,0)

решение минимальной даты, превышающей текущую строку, на основе совпадения имени:

=VLOOKUP(MINIFS(Table1!$A:$A,Table1!$D:$D,E$1,Table1!$A:$A,">="&$C2)&E$1,CHOOSE({1,2},Table1!$A:$A&Table1!$D:$D,Table1!$B:$B),2,0)

person NathanC    schedule 07.10.2017