Как рассчитать среднее значение последних n рядов в Excel 2019 с учетом пустых ячеек

Как усреднить последние N строк в Excel с пропусками и текстом

Работа с данными в Excel часто требует различных манипуляций, особенно когда речь идет о заполнении пустых ячеек или объединении текста с числами. Одна из распространенных задач — усреднение последних N строк, когда в данных могут быть пропуски или текстовые значения. В этой статье мы рассмотрим, как справиться с этой задачей, используя формулы Excel.

Пример данных

Предположим, вы имеете следующий набор данных в столбце A:

A
1
2
х
3
4
5
6
7
у
8
9
10

Если вы хотите получить среднее значение последних четырех строк (с пропуском ячеек, содержащих текст), вам нужно игнорировать строки, которые не являются числами. В этом случае мы будем усреднять значения от ячейки A8 до A11, игнорируя A9 и A10.

Динамическое усреднение

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

Подход 1: Использование формулы Excel 2019

Для пользователей Excel 2019 можно использовать следующую формулу:

=AVERAGE(
     INDEX(A:A,
     AGGREGATE(14,7,ROW(A:A)/((ISNUMBER(A:A))*(A:A<>"")),4)):
     INDEX(A:A,
     MATCH(2,1/((ISNUMBER(A:A))*(A:A<>"")))
 )

Объяснение формулы

1. MATCH: Находит позицию последнего числового значения в столбце A.

   =MATCH(2,1/((ISNUMBER(A:A))*(A:A<>"")))

2. AGGREGATE: Возвращает 4-й по величине номер строки, содержащей числовое значение.

   =AGGREGATE(14,7,ROW(A:A)/((ISNUMBER(A:A))*(A:A<>"")),4)

3. INDEX: Используется для получения значений ячеек согласно номерам строк, найденным ранее.

4. AVERAGE: Объединяет в себе все значения и вычисляет среднее.

Подход 2: Упрощенная формула для MS365

Если вы используете Microsoft 365, можно воспользоваться более короткой формулой:

=AVERAGE(TAKE(FILTER(A:A,(ISNUMBER(A:A)*(A:A<>"")),""),-4))

Альтернативный способ для более старых версий

Для более старых версий Excel можно использовать следующее:

=AVERAGE(
    INDEX(
        $A:$A,
        LARGE(
            ISNUMBER($A$1:INDEX($A:$A, LOOKUP(2, 1 / $A:$A, ROW($A:$A)))) *
            ROW($A$1:INDEX($A:$A, LOOKUP(2, 1 / $A:$A, ROW($A:$A)))),
            {1; 2; 3; 4}
        )
    )
)

Объяснение альтернативного способа:

• LOOKUP: Находит номер последней ячейки с числом.
• INDEX: Создает массив значений, затем фильтрует их для получения только чисел.
• LARGE: Возвращает N-ю по величине строки, чтобы взять последние четыре числовых значения.
• AVERAGE: Вычисляет среднее.

Заключение

Теперь вы знаете, как усреднить последние N строк в Excel, независимо от наличия текстовых значений или пробелов. Эти формулы помогут вам эффективно управлять данными и получать точные результаты. Не забудьте адаптировать формулы под ваши специфические условия и версию Excel, чтобы оптимизировать ваш рабочий процесс!

Источник