☰

Индексация диапазона дат, если дата больше другой даты и находится в пределах n дней от нее

tag-icon • tag-icon microsoft-excel worksheet-function
Индексация диапазона дат, если дата больше другой даты и находится в пределах n дней от нее

Впредыдущий постЯ задал вопрос о том, как найти самую последнюю дату, связанную с определенной сущностью, и вернуть это значение в Sheet2, если оно больше связанной даты на Sheet2. Однако я понял, что настоящая проблема заключается не в том, чтобы вернуть самую последнюю дату, если условие выполнено, а в том, чтобы вернуть дату, если она больше и находится в пределах 2 дней (или, как правило, n дней) от контрольной даты.

Формула, полученная ранее, была следующей:

=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.

Нет ли способа добавить еще одно условие к INDIRECT(), чтобы он спрашивал не только, что это так, < $B1но и что его расстояние до даты равно <2?

Я попробовал следующую формулу, но безуспешно:

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

Образец листа можно найтиздесь.

решение1

Мне пришлось вернуться к вашей серии вопросов, чтобы увидеть, в чем проблема, но (с небольшими изменениями)Формула @Hannuпочти верно. Проблема, с которой вы столкнулись при его реализации, заключается в том, что макет вашей электронной таблицы не соответствует тому, что было указано @jbmorch в ихответ на ваш предыдущий вопрос.

Макет был очень четко указан в ответе @jbmorch и имеет значение по нескольким причинам:

  • Вы не указали много подробностей о макете вашей электронной таблицы в своем вопросе, за исключением частей вашей псевдоформулы, которые включали имена листов. Поэтому @jbmorch и другим пришлось спекулировать и создавать свои собственные макеты, чтобы составить подходящую формулу.
  • Если не упорядочить столбцы в таблице так, как указано в ответе, ссылки на столбцы в таблице будут неверными, пока вы их не скорректируете.
  • Порядок сортировки на LargerSheet важен из-за принципа работы функции ПОИСКПОЗ — если строки не отсортированы так, как указано в ответе, функция ПОИСКПОЗ не выдаст точных результатов.
  • В ответе @jbmorch также предполагается (поскольку иной информации предоставлено не было), что ваши данные начинаются в строке 1. Таким образом, их ответ был написан для такой электронной таблицы и будет давать ошибочные результаты, если не скорректировать их с учетом фактического макета.

Еще несколько возможных проблем, о которых следует знать:

  • Вам необходимо убедиться, что все записи даты/времени на самом деле отформатированы как даты и время, в противном случае Excel не сможет выполнять правильные сравнения. Это можно проверить в параметрах формата в свойствах ячейки, а также, попробовав применить к ячейке математические операции. (например: если A2 содержит 1/7/2003, а B2 — =A2+2, то значение для B2 должно разрешиться как 1/9/2003).
  • В вашем образце листа содержится несколько записей в Sheet1, которые на самом деле соответствуют критериям, которые вы ищете для возврата значения из LargerSheet. Это немного затрудняет устранение неполадок. Фактически, единственное совпадение, которое я нашел, было в строке 9. (Дата для DAILY, JIM в этой строке в Sheet1 на самом деле была в пределах двух дней от последней даты для него в LargerSheet.)

Итак, вот формула, которая вам нужна. Перетащите ее в ячейку C2 на Листе1 и скопируйте вниз:

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

Еще раз обратите внимание на то, чтобы ваш лист был отформатирован.точнокак показано ниже, в противном случае формула не будет работать без корректировок.

  • Все датыдолженбыть отформатированы как даты, а не как текст или числа.
  • Оба листадолженв столбце A указаны имена, в столбце B — даты, а фактические данные начинаются со строки 2.
  • Большелистдолженсортироваться как по имени (по возрастанию), так и по дате (по возрастанию) с установленным приоритетом сортировкив этой последовательности.

Еще одна вещь, которую следует знать, — это различие между «в течение 2 дней» и «в течение 48 часов» — эта формула использует последнее. То есть, если время находится 5/6/2012 03:00:00на Sheet1, а соответствующее значение на LargerSheet — 5/8/2012 03:00:01то оператор IF будет оцениваться как FALSE и вернет значение из Sheet1 вместо значения из LargerSheet. Потребуются существенные изменения формулы, чтобы учесть, хотите ли вы сопоставить «любое будущее время в течение следующих двух дней» вместо «любое время в течение 48 часов».

Кроме того, поскольку оператор IF использует исключающее больше ( >) вместо больше или равно, то в случаях, когда совпадения совпадают в точности, он будет оцениваться как ЛОЖЬ. Если вы хотите, чтобы он оценивался как ИСТИНА для точных совпадений, замените >на >=.

решение2

ПРИМЕЧАНИЕ: Я просто просматриваю ваш IF() и меняю оператор условия.
Если это не работает для вас - это может по крайней мере дать представление о том, как это написать/изменить.

=ЕСЛИ(
     AND(INDIRECT("LargerSheet!$B"& //Ячейка, начинающаяся с "$B" и заканчивающаяся на
     MATCH($A1,LargerSheet!$A:$A,1) //строка последней даты для имени.
     )<$B1, //Сравнить с датой SmallerSheet
     INDIRECT("LargerSheet!$B"& //Ячейка, начинающаяся с "$B" и заканчивающаяся на
     MATCH($A1,LargerSheet!$A:$A,1) //строка последней даты для имени.
     )<($B1+2)), //Сравнить с SmallerSheet (date-2)
     INDIRECT("LargerSheet!$B"& //"Then" вернуть дату LargerSheet,
     ПОИСКПОЗ($A1,БольшийЛист!$A:$A,1)
     ),$B1)

... важное дополнение находится AND( condition1, condition2 )между IF(и первым ,для IF.

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