Member 13861149 Ответов: 3

Мне нужен код VBA, чтобы найти сумму чисел в столбце.

У меня есть рабочий лист с примерно 60 столбцами, начинающимися с G. Я хочу найти сумму значений в ячейках/строках (начиная с G3) в каждом столбце на основе заданного значения в конкретной ячейке. (то есть значение, скажем, 10 задано в ячейке G1, я хочу получить общее количество значений из ячейки G3 в G13 и отобразить его в G2). То же самое нужно сделать для столбцов H,I,J и т. д.

Что я уже пробовал:

Я попробовал следующий код. Он просто дает мне сумму всех чисел.
Суб дуральной()
Дим Как Долго
N = Ячейки(Строки.Count, "G").End(xlUp).Row
Клеток(Н + 1, "Г").Формула = "=сумм(В5:г" &амп; Н &амп; ")"
Конец Подводной Лодки

Richard MacCutchan

Сумма(B5:G" & N & ")"
Почему вы начинаете с B5?

Вы можете довольно легко сгенерировать необходимый код, используя Record Macro особенность Excel. Затем отрегулируйте его в соответствии с вашими требованиями.

3 Ответов

Рейтинг:
2

OriginalGriff

Функция SUM принимает диапазон значений, выраженных в виде 'start':'end' включительно.
Если вы передадите ему многоколоночный диапазон, он суммирует несколько столбцов!

Замените бит "B5" на "G3", чтобы получить диапазон одного столбца.

Рейтинг:
14

CHill60

Смотрите комментарий от Ричарда и решение от OriginalGriff.

Следующее решение явно подхватывает остальную часть вашего требования - то есть проверяет все заполненные столбцы начиная с G, подбирает количество строк для суммирования из ячейки 3 каждого столбца, выполняет суммирование для каждого столбца

Sub dural()
    Dim lastCol As Integer
    lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

    Dim col As Integer
    For col = 7 To lastCol  'From G until finished
        Dim numRows As Long
        numRows = ActiveSheet.Cells(1, col).Value2
        
        Dim rng As String
        If numRows > 0 Then
            rng = ConvertToLetter(col) & "3:" & ConvertToLetter(col) & CStr(numRows + 3)
            
            'Get the appropriate total
            ActiveSheet.Cells(2, col).Formula = "=SUM(" & rng & ")"
        End If
    Next

End Sub
Я воспользовался ConvertToLetter функция опубликована на форумах Microsoft:
Function ConvertToLetter(iCol As Integer) As String
'https://support.microsoft.com/en-gb/help/833402/how-to-convert-excel-column-numbers-into-alphabetical-characters
    Dim iAlpha As Integer
    Dim iRemainder As Integer
    iAlpha = Int(iCol / 27)
    iRemainder = iCol - (iAlpha * 26)
    If iAlpha > 0 Then
        ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
        ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
End Function
Это не сработало, но это довольно элегантное решение кажется прекрасным
Function ConvertToLetter(icol As Integer) As String
    ConvertToLetter = Split(Cells(1, icol).Address, "$")(1)
End Function
Кредит: Крис Ньюман в компании Thespreadsheetguru.com[^]

Member 13861149

Спасибо за ваш ответ....
Я получаю эту сумму. Но сумма одной строки добавляется дополнительно(то есть я получаю сумму 11 строк(от G3 до G13, но она должна быть от G3 до G12), если я введу 10 в ячейку G1).

CHill60

Ой, извините, я этого не заметил ... измените настройку rng к следующему 

rng = ConvertToLetter(col) & "3:" & ConvertToLetter(col) & CStr(numRows + 2)

Member 13861149

Спасибо.... Это работает.... Я изменил код, чтобы получить сумму из D6, E6 и т. д. В D3,E3,F3 и т. д... Во время запуска макроса я получаю ошибку 1004, и она указывает на таблицу ActiveSheet.Клетки(3, col).Формула = "=сумм(" &ампер; " РНГ " &ампер; ")" линия. Пожалуйста, найдите код ниже и помогите мне решить эту проблему.

Dim lastCol как целое число
lastCol = ActiveSheet.UsedRange.Колонка - 1 + ActiveSheet.UsedRange.Столбцы.Рассчитывать

Dim col как целое число
Для col = 4 до lastCol 'от G до завершения
Тусклый онемение как долго
numRows = ActiveSheet.Клетки(1, col).Значение2

Тусклый ГСЧ как строка
Если numRows > 0, то
ГСЧ = ConvertToLetter(кол) &ампер; "6:" &амп; ConvertToLetter(кол) &амп; функция cstr(numRows + 2)

- Получите соответствующую сумму
Активный лист.Клетки(3, col).Формула = "=сумм(" &амп; ГСЧ и усилитель; ")"
Конец, Если
Следующий

CHill60

Я не получаю никаких сообщений об ошибках. Поставьте точку останова на этой линии и проверьте значения rng.
Вы также можете попробовать поместить код в модуль, а не на сам рабочий лист.
То ActiveSheet может быть проблема в том, чтобы вы могли явно назвать лист, например 

Sheets(1).Cells(1,col).Value2
или 
Sheets("Sheet1").Cells(1, col).Value2

Member 13861149

ГСЧ имеет значения от А6 до А10, что неверно. Но все остальные переменные сохраняют значения, как и ожидалось.

Попробовал также две заданные строки кода. Но все равно показывает ту же ошибку.
Все это прекрасно работает для одной листовой рабочей книги.

CHill60

Если он отлично работает для одной листовой книги, то попробуйте объединить явное именование листа (замените все экземпляры ActiveSheet, а не только пример, который я привел) с помещением кода в модуль - не забудьте удалить тот, который находится на рабочем листе

Member 13861149

Я проверил значения, которые содержит каждая переменная. Код работает плавно до столбца AZ. Но когда col=53 (то есть BA), rng должен быть BA6:BA7, но я получаю значение "A[6:A[7", которое следует за ошибкой времени выполнения

CHill60

А! Этот код, который я "украл" с форумов Microsoft, должно быть, имеет проблему. Я посмотрю на него. Спасибо за то, что вы выяснили, в чем заключается проблема - я сам использую этот код, так что это была проблема, которая только и ждала своего часа!

CHill60

Теперь я исправил эту функцию (или, скорее, я украл код откуда-то еще!)

Member 13861149

Новый код функции ConvertTo() не возвращает точное значение. ГСЧ в настоящее время содержит только диапазон,например 6:10,но не принимает символы, такие как D,E, F и т.д.(То есть раньше значение в диапазоне было D6:D10, E6:E10 и т.д., Но теперь его 6:10). Таким образом, макрос полностью идет не так.

CHill60

Я исправил ошибку ввода в своем решении, но на самом деле вам тоже нужно приложить некоторые усилия. Очевидно, что у вас их нет 

Option Explicit
в верхней части вашего модуля - вы всегда должны добавить это. На самом деле, если вы перейдете на вкладку Инструменты, Параметры, редактор и поставите галочку напротив опции "требовать объявления переменной", она будет автоматически вставлена в любой новый код. Вы также должны убедиться, что опция "автоматическая проверка синтаксиса" также включена.

Member 13861149

Спасибо. Теперь он работает без каких-либо ошибок...

Maciej Los

Кэролайн, на этот раз я должен проголосовать за 3, потому что ... .. Пожалуйста, смотрите мой ответ.

CHill60

Я согласен - см. Мой комментарий выше о том, чтобы явно назвать лист. А 3 это более чем справедливо и я должен помнить чтобы перестать быть ленивым :-)

Рейтинг:
0

Maciej Los

Обращаясь к CHill60[^] ответ, я бы избегал этого ActiveSheet, ActiveCell, и т.д. Использование, потому что это может привести к нескольким проблемам!
Представьте себе, вы активировали Sheet2, где есть много данных, но ваш код должен вставляться SUM формулы в Sheet1 Что происходит, когда вы используете "неконтекстуальный" код?

Вы потеряете данные в Sheet2!


Взгляните на приведенный ниже код:
Option Explicit

Sub CalculateSUM()
    Dim NoOfRows As Integer, NoOfCols As Integer, i As Integer
    Dim wsh As Worksheet, SumRow As Range
    

    'context!!!
    Set wsh = ThisWorkbook.Worksheets("Sheet1")
    'get number of rows to sum it up
    NoOfRows = wsh.Range("G1")
    'set first row with sum
    Set SumRow = wsh.Range("G2")
    'set no of columns to insert sum formula (60-1), because we use Offset(ColumnOffset:=xxx) method
    NoOfCols = 59
    For i = 0 To NoOfCols
        SumRow.Offset(ColumnOffset:=i).Formula = "=SUM(" & _
                SumRow.Offset(RowOffset:=1, ColumnOffset:=i).Address & ":" & _
                SumRow.Offset(RowOffset:=NoOfRows, ColumnOffset:=i).Address & ")"
    Next

End Sub


Для получения более подробной информации, пожалуйста, смотрите:
Диапазон.Свойство Смещения (Excel)[^]

Удачи вам!