Поиск и вставка данных на основе диапазона дат

Question

В другом сообщении была дана формула, которая должна была помочь в поиске и вставке записи, если дата, связанная с этой записью, была меньше даты на другом листе. Формула была следующей:

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

Однако это не совсем так, как хотелось бы. Мне интересно, может быть, просто моя проблема немного другая. По сути, я хотел бы, чтобы для данной записи в SmallerSheet были проверены даты, связанные с несколькими объектами в LargerSheet, и возвращалась самая последняя дата из LargerSheet, только если Most Recent Date <SmallerSheet Date. Я думаю, что вышеприведенная формула делает прямо сейчас, проверяя, является ли дата SmallerSheet более поздней, чем хотя бы одна из дат в LargerSheet, что приводит к тому, что утверждение всегда является истинным, поскольку каждая запись SmallerSheet имеет несколько записей, связанных с ней в LargerSheet уходят очень далеко в прошлое. Это легко исправить?

Answer 1

Я собираюсь дать вам формулу, которая работает не так, как вы перечислили, поскольку MATCH возвращает первую функцию.

Эта формула является формулой массива, поэтому вводится с помощью сочетаний клавиш CTRL+SHIFT+ ВВОД.

Это предполагает, что ваша большая таблица находится в столбце А. Дата, которую вы хотите сравнить, находится в ячейке E2.

=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))

Для каждой ячейки в большой таблице выполняется оператор if. Если ячейка меньше, чем E2, она возвращает свою дату (которую Excel хранит в виде числа), если ячейка больше, чем E2, она возвращает 0. Взяв максимум всех этих операторов if, он возвращает наибольшую дату, которая меньше E2.

Answer 2

Предполагая, что вы можете отсортировать данные в вашем LargerSheet, вы можете решить вашу проблему следующим образом:

Сначала сделайте пользовательскую сортировку на LargerSheet; сортировать сначала по имени (AZ), а затем по дате (от самого старого к новому). Теперь все записи с одинаковыми именами сгруппированы, и последняя запись в каждой группе является самой последней датой для этого имени.

--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :

Затем в SmallerSheet в столбце рядом с каждым именем используйте следующую формулу (при условии, что, как и в LargerSheet, имя находится в столбце A, а дата - в столбце B). Уберите пробел и комментарии.

=IF( INDIRECT("LargerSheet!$B"& //Cell starting with "$B" and ending with MATCH($A1,LargerSheet!$A:$A,1) //row of the last date for the name. )<$B1, //Compare with SmallerSheet date INDIRECT("LargerSheet!$B"& //"Then" return LargerSheet date, MATCH($A1,LargerSheet!$A:$A,1) ),$B1) //"Else" return SmallerSheet date.

Это должно вернуть самую последнюю дату из LargerSheet, если она меньше, чем дата из SmallerSheet (для каждого имени), или дату из SmallerSheet, если нет.

Если необходимо выбрать больше условий для выбора даты, попробуйте использовать логические функции "И" и "ИЛИ" или измените сам тест. Например, следующая модификация формулы будет возвращать самую последнюю дату из LargerSheet, если она находится в пределах -2 дней от даты в SmallerSheet, И также до тех пор, пока значение в столбце C в LargerSheet больше нуля , Как видите, формула начинает выглядеть громоздкой, поэтому следует соблюдать осторожность, чтобы соответствовать скобки и проверить синтаксис. Использование нескольких столбцов для разбиения формулы на этапы может помочь.

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)

Наконец, вы можете подойти к этой проблеме другим способом: добавить столбец в LargerSheet, который выполняет поиск по единственной записи имени в SmallerSheet, выполнить тест и вернуть значение TRUE/FALSE на основе теста.