Формула массива Excel Несколько условий Пустая строка

у меня есть столбцы

  • B4 (эта колонка листа B) = номер детали
  • База данных A = Дата
  • База данных C = часть №
  • База данных AA = текстовый комментарий

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

     =IFERROR(INDEX(Database!AA$2:$AA$5000;MATCH(MAX(IF(""<>
Database!$AA$2:$AA$5000;IF(B4=Database!$C$2:$C$5000;Database!$A$2:$A$5000)))
&B4;Database!$A$2:$A$5000&Database!$C$2:$C$5000;0));"")

Однако проблема заключается в том, что если я проверяю только самую последнюю дату или непустые ячейки, возвращается последняя строка. Однако, если я смешаю два условия, я получу пустые ячейки по всей доске.

Кто-нибудь знает, как объединить эти два условия с формулами массива? (Я знаю, что VBA сделал бы мою жизнь здесь намного проще, но я не должен использовать ничего, кроме самообновляющихся формул)


arrays conditional-statements excel array-formulas
person seulberg1    schedule 09.05.2016    source источник
comment
Это должна быть формула массива. Эти формулы должны быть подтверждены с помощью [Corntol]+[Shift]+[Enter]. После этого они появляются в фигурных скобках. Это так сделано?   -  person Axel Richter    schedule 09.05.2016

Ответы (1)


arrow_upward
0
arrow_downward

Это можно сделать без СПП с помощью ИНДЕКС с функцией AGGREGATE¹ обеспечивая циклическую обработку почти СУММПРОИЗВ не вводит формулу массива. Как и в формуле массива, количество обрабатываемых строк должно быть сведено к минимуму, чтобы избежать задержки вычислений.

Сначала используется AGGREGATE. найти максимальную дату по номеру детали и непустому комментарию; форма псевдо-MAXIF. Это значение используется во втором АГРЕГАТЕ для возврата номера строки в ИНДЕКС столбца комментариев, который также соответствует номеру детали и непустому комментарию.

В C13 в качестве стандартной формулы

=INDEX(AA:AA, AGGREGATE(15, 6, ROW(A$1:INDEX(A:A, MATCH(1E+99,A:A )))/((C:C=B13)*(A:A=AGGREGATE(14, 6, A$1:INDEX(A:A, MATCH(1E+99,A:A ))/((C$1:INDEX(C:C, MATCH(1E+99,A:A ))=B13)*SIGN(LEN(AA$1:INDEX(AA:AA, MATCH(1E+99,A:A ))))), 1))*SIGN(LEN(AA$1:INDEX(AA:AA, MATCH(1E+99,A:A ))))), 1))

max_date_two_conditions

¹ Функция AGGREGATE появился в Excel 2010. Он недоступен в более ранних версиях.

² Формулы массива необходимо завершить с помощью Ctrl+Shift+Enter↵. Если введено правильно, Excel заключит формулу в фигурные скобки (например, { и }). Вы не вводите фигурные скобки в себя. После правильного ввода в первую ячейку их можно заполнить или скопировать вниз или вправо, как и любую другую формулу. Попробуйте сократить ссылки на полный столбец до диапазонов, более точно представляющих объемы ваших фактических данных. Формулы массивов логарифмически поглощают циклы вычислений, поэтому рекомендуется сужать указанные диапазоны до минимума. См. Рекомендации и примеры формул массива для получения дополнительной информации.

person Community    schedule 09.05.2016
comment
Извинения; Я изначально не компенсировал непустые комментарии. - person ; 09.05.2016
comment
Не знаю, только ли мне, но для меня эти AGGREGATE формулы выглядят запутанно и их трудно понять. Даже сложнее, чем эквиваленты формулы массива. На мой взгляд, не совсем прогресс. - person Axel Richter; 09.05.2016
comment
в настоящее время пытаюсь обдумать это решение, буду держать вас в курсе. Спасибо уже за участие - person seulberg1; 09.05.2016