Страницы: 1
RSS
Сумма N последних ненулевых значений
 
С Новым Годом всех! Пример прикрепил. Тяжело объяснить словами, что нужно. Необходимо суммировать последние N ненулевых значений. Сложность в том, как задать начало такого диапазона после первых N ненулевых значений, так как сумма должна быть рассчитана снова.
Изменено: alexhorse - 02.01.2017 21:09:54
 
=ЕСЛИ(ОСТАТ(СЧЁТЕСЛИ($E$4:E4;"<>0");$A$2)=0;СУММ($E$4:E4)-СУММ($A$3:A3);)
 
Круто! Спасибо. У меня единственное пожелание - дело в том что я привел пример с малым количеством строк, а по факту их тысячи. Насколько эффективно будет "тянуть" сумму? Или это самый "простой" вариант? Читал тут давно на форуме, что лучше создавать дополнительные столбцы а не создавать мега-формулы. Что скажете?
 
Цитата
Насколько эффективно будет "тянуть" сумму?
Проверить - никак?
Цитата
лучше создавать дополнительные столбцы а не создавать мега-формулы
Не такая это формула, чтобы под нее столбцы мостить. Упростить суммирование можно, если знать максимально возможный размер диапазона (его и задавать в формуле).
 
Попробуйте макросом
Код
Sub iSumma()
Dim iSumma As Integer
Dim i As Integer
Dim n As Integer
Dim iLastRow As Integer
  iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
  n = 4
M1:  i = 0
     iSumma = 0
  Do
    If Cells(n, "E") <> 0 Then
      iSumma = iSumma + Cells(n, "E")
      i = i + 1
    End If
      n = n + 1
  Loop While i <> 5
      Cells(n - 1, "A") = iSumma
    If n > iLastRow Then Exit Sub
      GoTo M1
End Sub
 
Так суммирование легче:
=ЕСЛИ(ОСТАТ(СЧЁТЕСЛИ($E$4:E4;"<>0");$A$2)=0;ИНДЕКС($E$3:E3;ПОИСКПОЗ(500;$A$3:A3)):E4;"")
В А3 вписать число.
 
А что если немного усложнить, добавив чтоб суммировало столбец по заданному заголовку?
 
А макрос вам подошел?
 
Не умею ими пользоваться. то есть смогу вставить его, но дело в том что формулы хотя бы додумать смогу и отредактировать, а макрос уже сложнее )
 
Цитата
Не умею ими пользоваться. то есть смогу вставить его,
Макрос в модуль листа1
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1:E2")) Is Nothing Then
   Application.EnableEvents = False
Dim iSumma As Integer
Dim i As Integer
Dim n As Integer
Dim iLastRow As Integer
Dim FoundKomanda As Range
Dim Stolb As Integer
  iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
    Range("A6:A" & iLastRow).ClearContents
  Set FoundKomanda = Rows(5).Find(Range("E2"), , xlValues, xlWhole)
If Not FoundKomanda Is Nothing Then
  Stolb = FoundKomanda.Column
  n = 6
M1:  i = 0
     iSumma = 0
  Do
    If Cells(n, Stolb) <> 0 Then
      iSumma = iSumma + Cells(n, Stolb)
      i = i + 1
    End If
      n = n + 1
  Loop While i <> Range("E1")
      Cells(n - 1, "A") = iSumma
    If n > iLastRow Then Exit Sub
      GoTo M1
 Else
   MsgBox "Нет в таблице команды: " & Range("E2")
 End If
End If
   Application.EnableEvents = True
End Sub
Пробуйте, макрос срабатывает на изменение ячеек Е1 или Е2 в вашем втором примере
 
alexhorse, здравствуйте. В формулу viktturа добавил выбор диапазона. Проверяйте.
P.S. Формуле выбора диапазона присвоил имя. Смотрите диспетчер имен.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
всем спасибо. буду тестировать
 
Доброго дня всем, господа. Немного изменил предложенный вами вариант, и как видел один раз подпись у одного из участников форума мол "каждое решение приводит к еще большим проблемам" =))) это мой случай.
так вот - проверять то нужно ПОСЛЕДНИЕ НЕ нулевые случаи, то есть чтоб сумма просматривала результаты событий вверх от строки суммирования
Прикрепил пример.
 
До этого тоже суммировались поледние ненулевые события. Задача изменилась. Теперь уже не один и не два - три столбца для суммирования, изменилось условие.
Создайте новую тему с названием, четко отражающим суть задачи.
Страницы: 1
Наверх