Страницы: 1
RSS
Суммирование массива с условием и указанием вручную последнего столбца месяца, задействованного в расчете.
 
Добрый день.
Прошу помощи в составлении формулы. Есть месяцы под каждым из них содержится 6 столбцов связанные с финансовой деятельностью за данный месяц.
Периодически нужно добавлять или убирать информацию нескольких последних месяцев. т.е. сумма реализаций за весь период или сумма реализаций без учета 2х последних, затрудняется это тем что нужно постоянно редактировать формулу. В файле примере для столбца CS "реализация"  и CV "Оплачено" я сделал формулы которые считают как раз то что мне нужно, быстрым указанием последнего нужно в расчетах столбца.... а вот для столбцов CT "Сумма отправленных документов на оплату потребителю, руб." CU "Сумма врученных документов на оплату потребителю" не получается так как там есть условие.
Суть в чем - хочется что бы как для столбцов оплата и реализация при  указании конца диапазона проводилось суммирование столбца реализация, но учитывались даты отправки/вручения документов. для каждого месяца отдельно и в сумму попадали только те у которых дата указана. Есть ли возможность это сделать одной формулой без дополнительных вычислений.
Изменено: a.i.mershik - 26.06.2018 12:04:28
Не бойтесь совершенства. Вам его не достичь.
 
Для реализации, без летучести:
=ОКРУГЛ(СУММЕСЛИ($M$2:ИНДЕКС($M$2:$CS$2;ПОИСКПОЗ($CS$1;$M$1:$CS$1;));"Реализация";M3:ИНДЕКС(M3:CS3;ПОИСКПОЗ($CS$1;$M$1:$CS$1;)));2)
В CS1 выпадающий список с данными строки 1.

Дльше не разбирался, но, может, предложенное натолкнет на решение.
 
Цитата
vikttur написал:
Для реализации, без летучести
это значит, что файл с большим количеством строк будет работать медленно? у меня просто более 6500.
Для реализации нормально то оно..а меня как раз другие интересуют ...но спасибо сейчас посмотрю может даст направление мыслей.
Изменено: a.i.mershik - 26.06.2018 12:59:13
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
файл с [6500] строк будет работать медленно?
Нет, не будет. В смысле: зависнет :)
 
макросом пойдет?
в модуль листа
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$CS$1" Then
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    Dim rng As Range
    Set rng = Range(Target.Value & 1)
    If rng Is Nothing Then MsgBox ("Нет такого столбца"): Exit Sub
    For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, Target.Column + 1).Value = ""
        Cells(i, Target.Column + 2).Value = ""
        For j = 13 To rng.Column Step 6
            If Cells(i, j + 1).Value > 0 Then Cells(i, Target.Column + 1).Value = Cells(i, Target.Column + 1).Value + Cells(i, j).Value
            If Cells(i, j + 2).Value > 0 Then Cells(i, Target.Column + 2).Value = Cells(i, Target.Column + 2).Value + Cells(i, j).Value
        Next
    Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End Sub
 
yozhik, ну думаю пойдет только не пойму...какие формулы должны быть в ячейках столца CT и CU??
Цитата
вот для столбцов CT "Сумма отправленных документов на оплату потребителю, руб." CU "Сумма врученных документов на оплату потребителю" не получается так как там есть условие.
Суть в чем - хочется что бы как для столбцов оплата и реализация при  указании конца диапазона проводилось суммирование столбца реализация, но учитывались даты отправки/вручения документов. для каждого месяца отдельно и в сумму попадали только те у которых дата указана.
Изменено: a.i.mershik - 26.06.2018 14:00:56
Не бойтесь совершенства. Вам его не достичь.
 
не надо там ничего, ячейку CS1 меняете (месяц ограничиваете), все пересчитывается
 
yozhik, пересчитывается где?? в этом вопрос в столбце реализация?? да..а мне нужно
Цитата
a.i.mershik написал:
для столбцов CT "Сумма отправленных документов на оплату потребителю, руб." CU "Сумма врученных документов на оплату потребителю" не получается так как там есть условие
а какую формулу там сделать я не знаю так как постоянно то один то другой столбец являетяс последним то новый коорого еще не в данном примере так как месяцы добавляются.
Изменено: a.i.mershik - 26.06.2018 14:07:55
Не бойтесь совершенства. Вам его не достичь.
 
вставьте код в модуль листа и поэкспериментируйте) код считает и подставляет значения, формулы тут не при чем

Цитата
a.i.mershik написал: пересчитывается где??
пересчитывается построчно, результат вставляется в столбцы CT и CU
 
yozhik,все понял, да действительно невнимательно я посмотрел.. тогда к вам ещё вопрос а как изменить макрос что бы при добавлении нового месяца учитывался он так же ?
Не бойтесь совершенства. Вам его не достичь.
 
Тут или вторую строчку править надо, Target.Address ="$CS$1" - на новый адрес ячейки, где будете указывать имя столбца, или переписывать макрос по другому. Расчет вставляется со смещением на 1 и два столбца от этой изменяемой ячейки
 
yozhik,  Прошу прощения, но сейчас проверил, что при изменении названия последнего задйествованного столбца все равно идет подсчет и учет реализации месяца которые идет после этого последнего столбца..май месяц исключил.. к тому же добавил новый месяц и в нес изменения в If Target.Address = "$CY$1" Then но этот месяц вообще не учитывается..
да и при добавлении месяца в данных строках нужно добавлять в формулу новые условия...а это как раз хочу избежать

Изменено: a.i.mershik - 26.06.2018 15:07:13
Не бойтесь совершенства. Вам его не достичь.
 
у Вас код в общем модуле, а надо в модуль листа его вставить. На ярлык листа правым мышом - исходный текст - откроется модуль листа, туда код и разместить
 
Цитата
a.i.mershik написал:
..май месяц исключил.. к тому же добавил новый месяц
вот тут я не совсем понял. По задумке можно исключить только последний (последние) месяц, ограничивая число столбцов. Исключить месяц в середине не получится, если только у него даты удалить
 
yozhik, ой блин  да туплю я очень, да точно! сейчас на оригинале тогда проверю файла. т.е. не важно добавляю я или убирию главное что бы в макросе ссылка была на нужную ячейку так?
Не бойтесь совершенства. Вам его не достичь.
 
да, если структура месяцев (6 столбцов каждый месяц) и расчет вставляем правее на 1 и 2 столбца, то достаточно менять адрес
и на всякий случай второй строкой прописать проверку
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count>1 then exit sub
If Target.Address = "$CS$1" Then
 
yozhik, понял, спасибо. Еще один вопрос на какое действие реагирует макрос что бы произвести перерасчет...а то удаляю. суммы иил даты он не пересчитывает..?  
Изменено: a.i.mershik - 26.06.2018 15:37:48
Не бойтесь совершенства. Вам его не достичь.
 
только на изменение указанной в коде ячейки. Можно не меняя значения этой ячейки - F2 и Enter
Изменено: yozhik - 26.06.2018 15:42:33
 
yozhik,  все понял, разобрался, еще вопрос, и последнее если нужно сместить результат вычислений макроса какую строчку изменить ..т.е. допустим сместить их в сторону в право на 10 столбцов или 5 допустим?
Не бойтесь совершенства. Вам его не достичь.
 
Cells(i, Target.Column + 1).Value   - везде в коде где есть Target.Column+  - на сколько надо сместить, столько и плюсуйте. В примере Target.Column+1 для вставки просчета условия "дата отправки")  и след строчкой +2 для условия "дата вручения"
сместить на 10  будет
Код
If Cells(i, j + 1).Value > 0 Then Cells(i, Target.Column + 10).Value = Cells(i, Target.Column + 10).Value + Cells(i, j).Value            
If Cells(i, j + 2).Value > 0 Then Cells(i, Target.Column + 11).Value = Cells(i, Target.Column + 11).Value + Cells(i, j).Value
 
yozhik, я наверное уже задолбал вопросами, а как вставить такой же макрос только уже новое значение последнего столбца будет в другом месте... т.е. я хочу что бы DJ стоял уже другйо последний столбец и значение считались после него как для первого варианта..вставляю такой же макрос ссылку на другую ячейку макрос ругается... может конечно эту проверку сделать сразу при изменении одной из двух ячеек только расчет для одной один проводить я для другйо - отличие только в количестве месяцев так сказать...
Изменено: a.i.mershik - 26.06.2018 16:11:52
Не бойтесь совершенства. Вам его не достичь.
 
такой же макрос еще один вставлять нельзя, можно делать проверку двух адресов, например
Код
If Target.Address = "$CS$1" Or Target.Address = "$DJ$1" Then
только учитывайте, что расчет вставляется всегда правее изменяемой ячейки. Изменили $CS$1 - правее $CS$1, изменили $DJ$1 - правее $DJ$1
 
yozhik, ага, все отлично спасибо, только еще вставил все норма работает но изменяются значения только тех в которых сделано F2-enter..можно ли как то что бы при любой из этих двух ячеек расчет происходил и в другой так же что бы не нужно было два раза заходить в ячейку сначала в одну потом в другую

кажется нашел ошибку там была указана русская буква поэтом ошибка.

Изменено: a.i.mershik - 26.06.2018 16:51:12
Не бойтесь совершенства. Вам его не достичь.
 
чем дальше в лес, тем толще партизаны..)
если два столбца с изменяемыми ячейками будут всегда равноудалены - столбец CY + 11 (именно 11 столбцов) = столбец DJ, можно так
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$CY$1" Or Target.Address = "$DJ$1" Then
    If Target.Address = "$CY$1" Then ofs = 12 Else ofs = -10
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    Dim rng As Range
    On Error Resume Next
    Set rng = Range(Target.Value & 1)
    If rng Is Nothing Then MsgBox ("Нет такого столбца"): Exit Sub
    For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, Target.Column + 1).Value = ""
        Cells(i, Target.Column + 2).Value = ""
        For j = 13 To rng.Column Step 6
            If Cells(i, j + 1).Value > 0 Then Cells(i, Target.Column + 1).Value = Cells(i, Target.Column + 1).Value + Cells(i, j).Value
            If Cells(i, j + 2).Value > 0 Then Cells(i, Target.Column + 2).Value = Cells(i, Target.Column + 2).Value + Cells(i, j).Value
        Next
        Cells(i, Target.Column + ofs).Value = Cells(i, Target.Column + 1).Value
        Cells(i, Target.Column + ofs + 1).Value = Cells(i, Target.Column + 2).Value
    Next
Application.EnableEvents = False
    If Target.Address = "$CY$1" Then [dj1].Value = Target.Value Else [cy1].Value = Target.Value
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End Sub
 
yozhik, нет мне не нужно чтобы последний столбец в двух ячейка был одинаков они всегда разные.и соответсвенно суммы разные. Т.е. первое что вы сделали это считается за весь период для него мы указывваем названия столбца №1, а второе нужно для учетабез одного или нескольких месяцев указыввается столбец №2.
Изменено: a.i.mershik - 26.06.2018 17:58:09
Не бойтесь совершенства. Вам его не достичь.
 
тогда так, хотя код, наверное, не самый оптимальный..)
при добавлении месяцев придется менять строку
s1 = "$CY$1": s2 = "$DJ$1"
 
yozhik, спасибо большое, буду пробовать.
Не бойтесь совершенства. Вам его не достичь.
Страницы: 1
Наверх