В предыдущем посте я задал вопрос о том, как найти самую последнюю дату, связанную с конкретной сущностью, и вернуть ли это значение в 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)

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

|источник

2 ответа2

2

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

=IF(
     AND(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"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<($B1+2)),                       //Compare with SmallerSheet (date-2)
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)

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

|источник
2

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

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

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

Пара других возможных проблем, о которых нужно знать:

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

Тем не менее, вот формула, которая вам нужна. Поместите это в C2 на Sheet1 и скопируйте:

=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.
  • Таблица LargerSheet должна быть отсортирована как по имени (по возрастанию), так и по дате (по возрастанию), причем приоритет сортировки должен быть установлен в этом порядке.

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

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

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .