Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Задать в ячейке диапазон суммирования
 
#1
11.02.2012 18:37:15
Добрый день,очень нужна ваша помощь.:)Не знаю возможно ли такое,но возникла такая проблема:  
есть 1 строка ряд данных,ниже в строке идет обычное суммирование каждых 3 ячеек со сдвигом на 1 ячейку.И вот возник вопрос:можно так сделать чтобы диапазон суммирования я задавал в ячейке,т.е ввел в ячейку цифру 3 и суммировало по 3 ячейки,ввел 5 суммировало по 5 ячеек?!  
Если такое возможно,помогите пожалуйста..
 
 
 
#2
11.02.2012 18:44:17
=СУММ(B4:ИНДЕКС(B4:Q4;G2))  
Это для одной ячейки: сумма с начала диапазона до указанной ячейки.  
Протягиваемая без проверки на конец диапазона:  
=СУММ(ИНДЕКС($B$4:$Q$4;(СТОЛБЕЦ(A:A)-1)*$G$2+1):ИНДЕКС($B$4:$Q$4;СТОЛБЕЦ(A:A)*$G$2))
 
 
 
#3
11.02.2012 19:02:49
.
Excel 2007
 
 
 
#4
11.02.2012 19:14:04
Супер,спасибо большое!
 
 
 
#5
11.02.2012 19:21:24
Думаю такой вариант бутет проще
 
 
 
#6
11.02.2012 20:23:38
Понял, что не так понял :)  
 
Yuriy, проще будет так:  
=СУММ(СМЕЩ(B4;;;;$G$2))  
 
Но оптимальнее:  
=ЕСЛИ(СЧЁТ(B4:$Q$4)<$G$2;"";СУММ(B4:ИНДЕКС($B$4:$Q$4;СТОЛБЕЦ(A:A)+$G$2-1)))  
 
Недостаток формул ABC и Yuriy - применение летучих функций ДВССЫЛ() и СМЕЩ(), которые пересчитываются при любом изменении на листе.
 
 
 
#7
11.02.2012 20:40:02
Виктор мне лично понравилась  
Ваша чуть переделанный  
=ЕСЛИОШИБКА(СУММ(B4:ИНДЕКС(B4:$Q4;$G$2));"")
Excel 2007
 
 
 
#8
14.02.2012 17:47:17
Добрый день!Пришлось возвратится к этой теме. Использовал формулу от Vikttur =ЕСЛИОШИБКА(СУММ(B4:ИНДЕКС(B4:$Q4;$G$2));""),  
все хорошо работает, но возникла одна проблема: нужно обрабатывать около 1000 строк и 100 столбцов,при этом файл сильно распухает:). Нельзя ли сделать на основании этой формулы макрос? Помогите пожалуйста..
 
 
 
#9
14.02.2012 19:29:15
Макрокодером записал,получилась вот такая забавная зверушка:)  
Sub Sss()  
Set sh1 = Sheets("Сумм.диапазонов")  
sh1.Range("F2:CS1001") = ClearContents  
sh1.[F2:CS1001].Formula = "=IFERROR(SUM(Дни!RC[-1]:INDEX(Дни!RC[-1]:RC97,RC5)),0)"
sh1.Range("F2:CS1001").Value = sh1.Range("F2:CS1001").Value  
sh1.Range("F2:CS1001").Replace What:="0", Replacement:="", LookAt:=xlWhole    
End Sub  
Но Excel от такого творчества через раз зависает..:(  
Посоветуйте пожалуйста,как улучшить макрос?
 
 
 
#10
14.02.2012 23:42:37
В данном случае формула со СМЕЩ - БЫСТРЕЕ в несколько раз. Видимо, потому что проще. А "летучесть" не играет роли, т.к. формула вычисляется 1 раз и тут же заменяется значением. Попробуйте:  
 
Sub Sss()  
With Sheets("Сумм.диапазонов").Range("F2:CS1001")  
   .ClearContents 'необязательно  
'    .FormulaR1C1 = "=IFERROR(SUM(Дни!RC[-1]:INDEX(Дни!RC[-1]:RC97,RC5)),0)"
   .FormulaR1C1 = "=IFERROR(SUM(OFFSET(Дни!RC[-1],,,,RC5)),0)" 'СМЕЩ
   .Value = .Value  
   .Replace What:="0", Replacement:="", LookAt:=xlWhole  
End With  
End Sub  
 
Правда, формулы могут давать РАЗНЫЕ результаты в правых столбцах диапазона: ИНДЕКС дает ошибку, если второй аргумент больше числа столбцов в диапазоне, а в случае СМЕЩ такая ошибка не возникает.
 
 
 
#11
15.02.2012 02:23:28
Ваш макрос на 1000 строк по времени считает как мой на 50 строк.:) На вскидку результат вроде без ошибок. Спасибо Вам огромное!
 
 
 
Страницы: 1
Читают тему
Наверх