Макрос расчета динамического диапазона

У меня есть документ Excel, состоящий из 12 листов.
Каждый лист содержит много данных, начиная от столбцов от A до X и с переменным диапазоном в строках.
Я пытаюсь вычислить среднее значение и стандартную ошибку. каждого столбца, для каждого листа. Желательно с выводом на сводной ведомости.

Мой мыслительный процесс:

  1. Мне удалось позволить каждой ячейке под последней строкой вычислить среднее значение.
  2. Когда я попытался сделать то же самое со стандартной ошибкой, проблема в том, что среднее значение, рассчитанное на шаге 1), было включено в расчеты.
  3. Ведь представляется более удобным отображать результаты этих расчетов в отдельной вкладке «Сводка».

Вот код, который я пробовал, который работает для получения средних значений прямо под последним значением в столбцах.

Sub ColumnAverageFormula()

For i = 3 To 24
    Columns(Columns(i).Address).Select
    Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Formula = "=Average(" & Cells(1, ActiveCell.Column).Address(0, 0) & ":" & Cells(1, ActiveCell.Column).End(xlDown).Address(0, 0) & ")"
    Next i
For j = 3 To 24
    Columns(Columns(j).Address).Select
    Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Formula = "=stdev.p(" & Cells(1, ActiveCell.Column).Address(0, 0) & ":" & Cells(1, ActiveCell.Column).End(xlDown).Address(0, 0) & ")"
    Next j
End Sub

Tl; dr: я хотел бы написать код, который вычисляет среднее значение и стандартную ошибку каждого столбца каждого листа в моем файле Excel, и результаты должны быть получены на «сводном» листе.


excel average vba standard-error
person DCZ    schedule 01.02.2016    source источник
comment
Можете ли вы привести краткий пример того, как будет выглядеть этот сводной лист? Будет ли это просто набор чисел или ссылка на столбец, к которому применяется формула?   -  person Hambone    schedule 01.02.2016
comment
Вы сэкономите много хлопот, если ваши данные будут правильно Table.   -  person PatricK    schedule 01.02.2016
comment
Привет всем, спасибо за ваши быстрые ответы. Вот пример файла. Теперь я дал столбцам заголовок, его можно удалить, если он усложняет задачу. ‹a href=filedropper.com/example_8›file‹/a› Спасибо за продвигать!   -  person DCZ    schedule 01.02.2016

Ответы (1)


arrow_upward
2
arrow_downward

Формула, которую вы вставляли, ссылалась на диапазон, начинающийся с строки 1. Обычно описанный вами набор данных имеет какую-то текстовую метку заголовка столбца в первой строке. Если бы это было так, вы бы хотели начать со строки 2, а не с строки 1. Следующее начинается с строки 1.

Sub ColumnAverageFormula()
    Dim c As Long, sr As Long

    With Worksheets("Sheet1")
        sr = .Cells(Rows.Count, 3).End(xlUp).Row
        For c = 3 To 24
            .Cells(sr + 1, c).Formula = "=average(" & .Range(.Cells(1, c), .Cells(sr, c)).Address(0, 0) & ")"
            .Cells(sr + 2, c).Formula = "=stdev.p(" & .Range(.Cells(1, c), .Cells(sr, c)).Address(0, 0) & ")"
        Next c
    End With
End Sub


'as an alternate, you might consider putting all of the formulas in at once
Sub ColumnAverageFormula()
    Dim c As Long, sr As Long

    With Worksheets("Sheet1")
        sr = .Cells(Rows.Count, 3).End(xlUp).Row
        .Cells(sr + 1, 3).Resize(1, 22).Formula = "=average(" & Range(.Cells(1, 3), .Cells(sr, 3)).Address(0, 0) & ")"
        .Cells(sr + 2, 3).Resize(1, 22).Formula = "=stdev.p(" & Range(.Cells(1, 3), .Cells(sr, 3)).Address(0, 0) & ")"
    End With
End Sub

Если вы хотите начать со строки 2, измените .Range(.Cells(1, c), ... на .Range(.Cells(2, c), ....

person Community    schedule 01.02.2016
comment
Привет Jeeped, Это уже здорово. Я попробовал код, и он работает! Знаете ли вы, как я могу зациклить это на всех листах и ​​скопировать результаты на сводную страницу? Большое спасибо за ваш ответ! - person DCZ; 01.02.2016
comment
Пожалуйста. Что касается вашего продолжения, это должен быть новый вопрос (вопросы), иначе он перерастет в Вопрос о русской кукле. Начните с чего-нибудь (есть много примеров) и начните новый вопрос, если у вас возникнут проблемы. - person ; 01.02.2016