☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Поиск и вставка данных на основе диапазона дат

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

=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, уходящих очень далеко во времени. Легко ли это исправить?

решение1

Предполагая, что вы можете отсортировать данные в 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, выполнить проверку и вернуть значение ИСТИНА/ЛОЖЬ на основе проверки.

решение2

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

Эта формула является формулой массива, поэтому вводится с помощьюCTRL+SHIFT+ENTER.

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

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

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

Связанный контент