Добрый день. Прошу помощи в составлении формулы. Есть месяцы под каждым из них содержится 6 столбцов связанные с финансовой деятельностью за данный месяц. Периодически нужно добавлять или убирать информацию нескольких последних месяцев. т.е. сумма реализаций за весь период или сумма реализаций без учета 2х последних, затрудняется это тем что нужно постоянно редактировать формулу. В файле примере для столбца CS "реализация" и CV "Оплачено" я сделал формулы которые считают как раз то что мне нужно, быстрым указанием последнего нужно в расчетах столбца.... а вот для столбцов CT "Сумма отправленных документов на оплату потребителю, руб." CU "Сумма врученных документов на оплату потребителю" не получается так как там есть условие. Суть в чем - хочется что бы как для столбцов оплата и реализация при указании конца диапазона проводилось суммирование столбца реализация, но учитывались даты отправки/вручения документов. для каждого месяца отдельно и в сумму попадали только те у которых дата указана. Есть ли возможность это сделать одной формулой без дополнительных вычислений.
Для реализации, без летучести: =ОКРУГЛ(СУММЕСЛИ($M$2:ИНДЕКС($M$2:$CS$2;ПОИСКПОЗ($CS$1;$M$1:$CS$1;));"Реализация";M3:ИНДЕКС(M3:CS3;ПОИСКПОЗ($CS$1;$M$1:$CS$1;)));2) В CS1 выпадающий список с данными строки 1.
Дльше не разбирался, но, может, предложенное натолкнет на решение.
это значит, что файл с большим количеством строк будет работать медленно? у меня просто более 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 "Сумма врученных документов на оплату потребителю" не получается так как там есть условие. Суть в чем - хочется что бы как для столбцов оплата и реализация при указании конца диапазона проводилось суммирование столбца реализация, но учитывались даты отправки/вручения документов. для каждого месяца отдельно и в сумму попадали только те у которых дата указана.
yozhik, пересчитывается где?? в этом вопрос в столбце реализация?? да..а мне нужно
Цитата
a.i.mershik написал: для столбцов CT "Сумма отправленных документов на оплату потребителю, руб." CU "Сумма врученных документов на оплату потребителю" не получается так как там есть условие
а какую формулу там сделать я не знаю так как постоянно то один то другой столбец являетяс последним то новый коорого еще не в данном примере так как месяцы добавляются.
yozhik,все понял, да действительно невнимательно я посмотрел.. тогда к вам ещё вопрос а как изменить макрос что бы при добавлении нового месяца учитывался он так же ?
Тут или вторую строчку править надо, Target.Address ="$CS$1" - на новый адрес ячейки, где будете указывать имя столбца, или переписывать макрос по другому. Расчет вставляется со смещением на 1 и два столбца от этой изменяемой ячейки
yozhik, Прошу прощения, но сейчас проверил, что при изменении названия последнего задйествованного столбца все равно идет подсчет и учет реализации месяца которые идет после этого последнего столбца..май месяц исключил.. к тому же добавил новый месяц и в нес изменения в If Target.Address = "$CY$1" Then но этот месяц вообще не учитывается.. да и при добавлении месяца в данных строках нужно добавлять в формулу новые условия...а это как раз хочу избежать
у Вас код в общем модуле, а надо в модуль листа его вставить. На ярлык листа правым мышом - исходный текст - откроется модуль листа, туда код и разместить
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, понял, спасибо. Еще один вопрос на какое действие реагирует макрос что бы произвести перерасчет...а то удаляю. суммы иил даты он не пересчитывает..?
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 стоял уже другйо последний столбец и значение считались после него как для первого варианта..вставляю такой же макрос ссылку на другую ячейку макрос ругается... может конечно эту проверку сделать сразу при изменении одной из двух ячеек только расчет для одной один проводить я для другйо - отличие только в количестве месяцев так сказать...
yozhik, ага, все отлично спасибо, только еще вставил все норма работает но изменяются значения только тех в которых сделано F2-enter..можно ли как то что бы при любой из этих двух ячеек расчет происходил и в другой так же что бы не нужно было два раза заходить в ячейку сначала в одну потом в другую
кажется нашел ошибку там была указана русская буква поэтом ошибка.
чем дальше в лес, тем толще партизаны..) если два столбца с изменяемыми ячейками будут всегда равноудалены - столбец 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.