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

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

Шаги:

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

=AVERAGE(Sheet1:Sheet5!A1:A10)

После нажатия Enter, Excel вернет среднее значение указанного диапазона на всех листах от Лист1 до Лист5.

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

=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. Когда вас попросят, введите список имен рабочих листов, разделенных запятыми (например, Лист1,Лист3,Сводный), затем укажите диапазон (например, A1:A10).

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

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

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