Как рассчитать среднее значение ячеек с разных листов в Excel?

Если нужно вычислить среднее значение одного и того же диапазона на нескольких листах — например, найти средние продажи в диапазоне A1:A10 на листах с именами Sheet1–Sheet5, — Excel предлагает готовое решение с помощью формулы. Этот метод идеально подходит, когда все листы имеют одинаковую структуру и согласованную систему именования.

Шаги:

Выберите пустую ячейку для результата (например, C3) и введите следующую формулу:

=AVERAGE(Sheet1:Sheet5!A1:A10)

После нажатия клавиши Enter Excel вернёт среднее значение ограниченного диапазона на всех листах от Sheet1 до Sheet5.

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

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

Этот вариант особенно удобен, когда диапазоны различаются на разных листах. Введите формулу в ячейку результата и нажмите Enter.

Kutools для Excel значительно расширяет возможности извлечения и объединения значений из одной и той же ячейки или диапазона на нескольких листах благодаря функции Автоматическое инкрементирование ссылок на листе. Это особенно полезно при работе с большим количеством листов, имеющих одинаковый макет.

Шаги использования:

1. Откройте новый лист (например, сводный) и выберите ячейку для вычисления среднего значения — например, D7.

2. Перейдите на вкладку Kutools > Дополнительно(в группе)Формулы) > Автоматическое инкрементирование ссылок на листе.

3. В диалоговом окне:
— Выберите порядок заполнения из раскрывающегося списка Порядок заполнения(например,)Заполнить по столбцу, затем по строке).
— В разделе Список листов отметьте листы, содержащие ячейку, которую нужно усреднить.
— Нажмите Заполнить диапазон, затем закройте диалоговое окно.

4. Значения выбранных ячеек появятся в диапазоне (например,)D7:D11). Затем введите следующую формулу в любую пустую ячейку, чтобы рассчитать среднее значение:

=AVERAGE(D7:D11)

Нажмите Enter, чтобы получить результат. Этот способ упрощает консолидацию, но не обновляется автоматически при добавлении новых листов — если список листов изменится, функцию придётся запускать заново.

Иногда нужно одновременно рассчитать средние значения для нескольких соответствующих ячеек сразу на нескольких листах — например, объединить результаты по ячейкам A1, B1 и C1 со всех листов. Стандартные формулы превращают этот процесс в громоздкую задачу, но утилита Kutools для Excel Объединить(листы и книги) делает её значительно проще.

Как использовать эту функцию:

1. Нажмите KUTOOLS PLUS > Объединить, чтобы открыть мастер «Объединение листов».

2. В мастере (шаг 1 из 3):
Установите флажок Объединить и вычислить данные из нескольких книг в один лист, затем нажмите Далее, чтобы продолжить.

3. На шаге 2 из 3:
— Выберите листы для включения в разделе Список листов.
— Используйте кнопку Обзор, чтобы задать диапазон для усреднения.
— Нажмите Одинаковый диапазон, если диапазоны идентичны на всех листах.
— Нажмите Далее, чтобы перейти дальше.

4. На шаге 3 из 3:
Выберите значение Среднее в раскрывающемся списке Функция. При необходимости настройте метки строк и столбцов и нажмите Готово.

5. Появится диалоговое окно с вопросом, хотите ли вы сохранить текущие настройки как сценарий для будущего использования. Выберите Да или Нет в зависимости от ваших потребностей.

Теперь каждая ячейка в указанном Область размещения списка будет отображать среднее значение соответствующих ячеек со всех Выбранные листы. Этот метод особенно полезен для регулярных операций или быстрой консолидации больших объёмов структурированных данных.

Для пользователей, которым необходимо автоматизировать усреднение ячеек на нескольких листах — особенно когда имена листов не идут подряд, часто меняются или когда вы хотите определять параметры Указать диапазон во время выполнения — эффективным решением может стать макрос VBA. Такой метод лучше всего подходит опытным пользователям или книгам, в которые часто добавляют или переименовывают листы.

Приведённый ниже код VBA позволяет динамически указывать имена листов и диапазоны ячеек, а затем вычислять среднее значение по заданному диапазону на всех указанных листах. Он идеально подходит для консолидации данных из сложных или часто обновляемых книг.

Как настроить и использовать это решение на основе VBA:

1. Перейдите на вкладку Разработчик в Excel. Если она не отображается, включите её через Файл > Параметры > Настроить ленту. Нажмите Visual Basic, чтобы открыть редактор. Затем выберите Вставка > Модуль и вставьте следующий код:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. Чтобы запустить макрос, нажмите F5 в редакторе или закройте его и перейдите в меню Разработчик > Макросы, выберите AverageAcrossSheets и нажмите Выполнить.

3. Когда появится запрос, введите список имён листов, разделённых запятыми (например,)Sheet1,Sheet3,Summary), а затем укажите диапазон (например, A1:A10).

4. Макрос рассчитает сумму и количество значений с каждого допустимого листа, а затем отобразит среднее значение в диалоговом окне.

Примечания по параметрам:

• Имена листов нечувствительны к регистру, но должны совпадать точно.
• Диапазон может представлять собой отдельную ячейку, полный столбец (например, B:B) или прямоугольный диапазон (например, D2:E12).
• Недопустимые или отсутствующие листы будут пропущены без уведомления.