Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммирование по столбцу до первой пустой ячейки
 
#1
10.07.2017 15:45:01
Друзья, никак не могу решить простую на первый взгляд задачу. Есть таблица в пару тысяч строк и сотню столбцов. Нужно вычислить суммы по разделам, состоящим из нескольких строк, и разделенных между собой пустыми строками. То есть просуммировать вверх до первой пустой ячейки. Голову сломал, не могу найти решение. Хэлп! Файл с примером прилагаю. Так же нужно при суммировании игнорировать ячейки с ошибками
 
 
 
#2
10.07.2017 16:21:25
в столбец С в строку с Итого
Код
=АГРЕГАТ(9;6;(ИНДЕКС(C$1:C$200;1+ПРОСМОТР(2;1/($B$1:$B$200="")/(СТРОКА(B$1:B$200)<СТРОКА());СТРОКА(B$1:B$200))):ИНДЕКС(C$1:C$200;СТРОКА()-1)))
 
 
 
#3
10.07.2017 16:25:52
copper-top, спасибо огромное! Работает!!!
Вы не могли бы вкратце описать принцип работы этой формулы? Всё прекрасно работает, но хочется понять как)
 
 
 
#4
10.07.2017 17:08:37
в файле, может быть, будет понятно. вообщем, нужно определить диапазон. принцип работы функции просмотр не смогу описать .
 
 
 
#5
10.07.2017 17:22:36
Заполняем ИТОГО, пропуская ошибки. Количество столбцов редактируйте переменной y.
Код
Sub InsertSumInGroup()
Dim lstr&, sum&, i&, y&, r As Range, lev As Range, rr
lstr = Cells(Rows.Count, 2).End(xlUp).Row
Set lev = Range("A2:A" & lstr).SpecialCells(xlCellTypeBlanks)
Application.ScreenUpdating = False
For Each r In lev.SpecialCells(xlCellTypeBlanks).Areas
    For y = 3 To 10
        Cells(r.Row + r.Count - 1, y) = Application.Evaluate("INDEX(SUM(IFERROR(" & Cells(r.Row, y).Resize(r.Count - 1).Address & ",0)),)")
    Next y
Next
Application.ScreenUpdating = True
End Sub
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#6
10.07.2017 17:23:58
Еще вариант. Формула массива, вводится одновременным нажатием Контрл Шифт Ентер
=СУММ(ЕСЛИОШИБКА(C30:ИНДЕКС(C:C;ПРОСМОТР(;-КОДСИМВ($A$1:$A30);СТРОКА(C$1:C30)));))
Еще вариант
=СУММ(ЕСЛИОШИБКА(C$1:C30;))-СУММЕСЛИ($B$1:$B30;$B31;C$1:C30)*2
Или обычная формула
=СУММЕСЛИ($B$1:$B30;"*";C$1:C30)-СУММЕСЛИ($B$1:$B30;$B31;C$1:C30)*2
Изменено: _Boroda_ - 10.07.2017 17:35:42
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#7
10.07.2017 19:55:00
Немного измененная последняя формула Александра
=СУММ(СУММЕСЛИ(B$1:B30;{"*";"ИТОГО"};C$1:C30)*{1;-2})
Изменено: АlехМ - 10.07.2017 19:56:42
Алексей М.
 
 
 
#8
11.07.2017 10:51:21
Друзья, большое всем спасибо! Все варианты испробованы, все работают корректно. Остановился на предложенном copper-top тк был первым
 
 
 
#9
11.07.2017 10:55:18
Цитата
Drusus написал:
Остановился на предложенном  copper-top  тк был первым
Сильный аргумент.  :)  
Алексей М.
 
 
 
#10
11.07.2017 12:21:11
Drusus, все формулы рабочие. только измените так как вам нужно. например, так
Код
=СУММ(СУММЕСЛИ($B$1:ИНДЕКС($B$1:$B$200;СТРОКА()-1);{"*";"ИТОГО"};C$1:ИНДЕКС(C$1:C$200;СТРОКА()-1))*{1;-2})
и пользуйтесь.
 
 
 
#11
17.06.2022 12:09:56
Был такой же вопрос, сделал функцию, прсто удобнее, и не будет казусов при добавлении и перетосовке строк, в моем случае это важно.
Код
Function СУММДОПУСТ() 'считает сумму значений вниз по столбцу до первой пустой ячейки
    Dim acr, acc, ac As Range, fnd As Range, rng As Range
    Set ac = Application.Caller
    acr = ac.Row: acc = ac.Column
    Set fnd = Range(Cells(acr + 1, acc), Cells(acr + 20, acc)).Find("", , , 1)
    Set rng = Range(Cells(acr + 1, acc), Cells(fnd.Row, fnd.Column))
    СУММДОПУСТ = WorksheetFunction.Sum(rng)
End Function
 
 
 
#12
17.06.2022 13:05:54
testuser,  убрав одни казусы, вы добавили другие, например пересчет функции только при вводе. acr + 20 сильно ограничивает , да и лучше заменить на cells.count
Употреблять Range(  не совсем корректно, также как и Cells без указания лист.
да и find то не нужен
Код
Function СУММДОПУСТ() As Double 'считает сумму значений вниз по столбцу до первой пустой ячейки
    Dim ac As Range
    Set ac = Application.Caller
    If ac.Offset(1) <> "" Then СУММДОПУСТ = WorksheetFunction.Sum(ac.Parent.Range(ac.Offset(1), ac.Offset(1).End(xlDown)))
End Function
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх