Получите последние 5 ненулевых значений подряд

Question

У меня есть большая электронная таблица с множеством строк, где данные могут иметь нули и положительные / отрицательные значения, но все числовые значения. Я хотел бы написать формулу (или пять формул, если необходимо), чтобы сказать мне, что последние пять ненулевых значений для каждой строки.

Пример (одна строка данных):1 - 5 - 0 - 8 - 10 - 0 - 7 - 0 - 3 - 4

В приведенном выше примере данных в идеале формула (или 5 формул) будет возвращать:4 - 3 - 7 - 10 - 8

Любая помощь очень ценится, ломал голову над этим.

-Спасибо

Answer 1

Предполагая, что ваши значения в A1:J1, эта формула массива **, например, в L1:

=INDEX($A1:$J1,LARGE(IF($A1:$J1<>0,COLUMN($A1:$J1)-MIN(COLUMN($A1:$J1))+1),COLUMNS($A:A)))

Скопируйте справа еще 4 столбца.

Это также можно скопировать, чтобы получить аналогичные результаты для данных в A2:J2, A3:J3 и т.д.

С уважением

** Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы просто нажимать ENTER, вы сначала удерживаете CTRL и SHIFT, и только потом нажимаете ENTER. Если вы сделали это правильно, вы заметите, что Excel заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).

Answer 2

Вы можете сделать это с помощью формул и нескольких вспомогательных массивов. Скажем, ваши данные в столбцах с A по J, начиная с строки 1. Используйте столбцы с L по P для пяти результатов. Столбцы R-AA - это первый вспомогательный массив, а столбцы AC-AL - второй вспомогательный массив. Вы можете скрыть вспомогательные массивы или переместить их в другое место (просто настройте ссылочные вычисления столбцов).

Начнем с первого вспомогательного массива. В Excel есть все виды функций, которые работают слева направо, но нет функций, работающих справа налево (если только вы не используете израильское издание :-)). Первый вспомогательный массив просто меняет порядок данных. Ячейка R1 будет содержать:

    =OFFSET($A1,0,27-COLUMN(), )

Скопируйте это в ячейки с S1 по AA1.

Второй вспомогательный массив определяет местоположения и порядковые номера ненулевых значений. Ячейка AC1 содержит:

    =IF(R1=0,"",COLUMN()-28-COUNTIF($R1:R1,0))

Скопируйте это в столбцы AD через AL.

Теперь для значений результата. Введите эту формулу в L1 и скопируйте в M1 через P1:

    =INDEX($R1:$AA1, ,MATCH(1,$AC1:$AL1,0))

Измените формулы от M до P, заменив параметр поиска в функции MATCH на 2–5, чтобы формулы от M до P были:

    =INDEX($R1:$AA1, ,MATCH(2,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(3,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(4,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(5,$AC1:$AL1,0))

Теперь все формулы могут быть скопированы на лист по мере необходимости.