Как использовать цикл for в макросе Excel

Question

Я полностью новичок в Excel, но я хочу создать макрос, в котором я могу заполнить два столбца, а также начальный и конечный номер строк, и он будет выглядеть следующим образом:

=(A1*B1+A2*B2+A3*B3...)/((SUM of column B)/N))

где N - количество строк.

Как бы я сделал это в Excel?

Answer 1

Прежде всего, все будет намного проще, если вы будете использовать стандартные адреса ячеек!

Обычно на ячейки ссылаются по их "A1" -адресам, но в VBA (Visual Basic для приложений) к ним также можно обращаться с помощью координат [Row, Column]. Например, Cell(1, 2) будет "B1".

---

Вы можете вычислить значение либо с помощью формул, либо с помощью пользовательских VBA (макросы). Формулы часто являются более простой альтернативой, но в некоторых случаях использование VBA имеет свои преимущества.

Использование формул

Вот расположение образцов данных:

Столбцы A и B заполнены числами, которые нужно умножить вместе.

Ячейка "C2" содержит формулу, используемую для вычисления значения.

=(SUMPRODUCT(A:A;B:B)/SUM(B:B))/(COUNTA(B:B)-1)

• SUMPRODUCT умножает значения столбцов A и B для каждой строки и суммирует их.
• SUM складывает все числа, найденные в столбце B.
• COUNTA вычисляет количество непустых ячеек в столбце B (мы должны вычесть заголовок).

Посмотрите функции на странице поддержки Microsoft Office для получения дополнительной информации.

Использование VBA

Вы можете достичь того же (и многое другое!) используя VBA.

Откройте VBE (редактор Visual Basic) с помощью Alt + F11 или на вкладке разработчика.

Создайте новый модуль стандартного кода.

Вставьте приведенный ниже код в модуль. Вы можете настроить его под свои нужды.

Option Explicit

Sub Test()
    Dim targetSheet As Worksheet
    Dim valueColumnA As Long
    Dim valueColumnB As Long
    Dim headerSize As Long
    Dim lastRow As Long
    Dim currentRow As Long
    Dim value As Double

    '''---Config---'''
    Set targetSheet = ThisWorkbook.Sheets(1)
    valueColumnA = 1
    valueColumnB = 2
    headerSize = 1
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    '''---Logic---'''
    'Multiply the columns for each row and sum them up
    For currentRow = headerSize + 1 To lastRow
        value = value _
            + targetSheet.Cells(currentRow, valueColumnA).value _
            * targetSheet.Cells(currentRow, valueColumnB).value
    Next

    'Divide by sum of numbers found in valueColumnB
    value = value / Application.WorksheetFunction.Sum( _
        targetSheet.Columns(valueColumnB))

    'Divide by number of rows
    value = value / (lastRow - headerSize)

    Debug.Print value
End Sub

StackOverflow имеет много хороших вопросов и ответов о VBA.