... далее для компановки можно использовать вот такую связку A+L+F+H+I - если по столбцу Е стоит ИСХ, а если КОР - то A+L+F+J+K Это для того, чтобы не использовать большое количество условий а только условие в столбце Е. Т.е. строка с КОР должна заменить стоку с ИСХ если A+L+F+J+K равно A+L+F+H+I. Затем уже производим вычисление как в макросе Сверка_2_БК
Ну, теперь-то все понятно
Вот второй шаг. Кнопка "PUSH 1" делает то же, что и раньше, "PUSH 2" заменяет исходные строки корректирующими строками. Сверка_2_БК у нас вычисляет стр. и нак. части. А вот после "PUSH 2" что делать? Получившиеся строки тоже надо как-то скомбинировать. Как?
И еще. В макросе имеет значение порядок следования листов. Чтобы не получилось так: в выходном массиве сначала идут корректировки, а потом исходные данные. Это всегда будет обеспечено или нужно в макросе что-нибудь предусмотреть?
Quote
... далее для компановки можно использовать вот такую связку A+L+F+H+I - если по столбцу Е стоит ИСХ, а если КОР - то A+L+F+J+K Это для того, чтобы не использовать большое количество условий а только условие в столбце Е. Т.е. строка с КОР должна заменить стоку с ИСХ если A+L+F+J+K равно A+L+F+H+I. Затем уже производим вычисление как в макросе Сверка_2_БК
Ну, теперь-то все понятно
Вот второй шаг. Кнопка "PUSH 1" делает то же, что и раньше, "PUSH 2" заменяет исходные строки корректирующими строками. Сверка_2_БК у нас вычисляет стр. и нак. части. А вот после "PUSH 2" что делать? Получившиеся строки тоже надо как-то скомбинировать. Как?
И еще. В макросе имеет значение порядок следования листов. Чтобы не получилось так: в выходном массиве сначала идут корректировки, а потом исходные данные. Это всегда будет обеспечено или нужно в макросе что-нибудь предусмотреть?nilem
Получившиеся строки тоже надо как-то скомбинировать. Как?
Теперь опять используем связку с листа Сверка A+L: т.е. если строки на листе промежуточный(2) совпадают по A+L - то складываем данные в столбцах N,O,P,Q по этим строкам и находим разность N-O,P-Q и прибавляем к разности - которая была вычислена на листе Сверка по данной связке A+L и результат заносим в U и V. А - я так думаю, что немного непонятно объяснил Мы получили после "PUSH 2" данные с которыми можно работать и макрос Сверка_2_БК это уже итоговым получаеться - т.е. вот как в макросе
Code
Sub Сверка_2_БК() Dim x, i&, wshList(), bu, t() wshList = Array("промежуточный (2)") 'здесь получаеться вместо всех листов я подставил лист промежуточный (2)
With CreateObject("Scripting.Dictionary") .CompareMode = 1 x = [a4].CurrentRegion.Value For i = 2 To UBound(x) .Item(x(i, 1) & x(i, 12)) = Array(x(i, 14) - x(i, 15), x(i, 16) - x(i, 17)) Next i
For Each bu In wshList x = Sheets(bu).[a4].CurrentRegion.Value For i = 2 To UBound(x) If .Exists(x(i, 1) & x(i, 12)) Then t = .Item(x(i, 1) & x(i, 12)) t(0) = Round(t(0) + x(i, 14) - x(i, 15), 2) t(1) = Round(t(1) + x(i, 16) - x(i, 17), 2) .Item(x(i, 1) & x(i, 12)) = t End If Next i Next bu
x = Application.Transpose(Application.Transpose(.Items)) Sheets("Сверка").[u5:v5].Resize(.Count).Value = x End With
End Sub
Вот так будет завершающий этап. Вернее этот макрос показывает завершающий результат - поскольку я так думаю - что в Вашем варианте ему уже места нет, настолько все переделано - это я просто чтобы не мучаться с формулами подправил этот макрос Т.е. теперь это все три действия надо как то свести в одну кучу - без дополнительных листов и кнопок
Листы будут следовать так - как указано и будут еще добавляться, поэтому нужно учесть возможность добавления для расчета нужных листов! Они идут последовательно по отчетным периодам - так что по полседовательности листов ничего не нужно. Корректировки в одном расчетном периоде могут идти только за предыдущий период - т.е. например на листе 1-2011 могут быть корректировки только за перриод 1-2010,2-2010 но никак за 1-2011! Едиственное что - можно ли както учесть что может возникнуть такая ситуация - что например на листе 1-2011 может быть корректировка на уже откорректированную на листе 2-2010 за перриод 1-2010 - т.е. я так думаю, что данные на листе промежуточный (2) формируются последовательно по прохождению листов - и по идее последней будет корректировка, которая будет на листе 1-2011 - так вот последнюю и нужно будет брать в расчет! Ну вот как то так!
Quote (nilem)
Получившиеся строки тоже надо как-то скомбинировать. Как?
Теперь опять используем связку с листа Сверка A+L: т.е. если строки на листе промежуточный(2) совпадают по A+L - то складываем данные в столбцах N,O,P,Q по этим строкам и находим разность N-O,P-Q и прибавляем к разности - которая была вычислена на листе Сверка по данной связке A+L и результат заносим в U и V. А - я так думаю, что немного непонятно объяснил Мы получили после "PUSH 2" данные с которыми можно работать и макрос Сверка_2_БК это уже итоговым получаеться - т.е. вот как в макросе
Code
Sub Сверка_2_БК() Dim x, i&, wshList(), bu, t() wshList = Array("промежуточный (2)") 'здесь получаеться вместо всех листов я подставил лист промежуточный (2)
With CreateObject("Scripting.Dictionary") .CompareMode = 1 x = [a4].CurrentRegion.Value For i = 2 To UBound(x) .Item(x(i, 1) & x(i, 12)) = Array(x(i, 14) - x(i, 15), x(i, 16) - x(i, 17)) Next i
For Each bu In wshList x = Sheets(bu).[a4].CurrentRegion.Value For i = 2 To UBound(x) If .Exists(x(i, 1) & x(i, 12)) Then t = .Item(x(i, 1) & x(i, 12)) t(0) = Round(t(0) + x(i, 14) - x(i, 15), 2) t(1) = Round(t(1) + x(i, 16) - x(i, 17), 2) .Item(x(i, 1) & x(i, 12)) = t End If Next i Next bu
x = Application.Transpose(Application.Transpose(.Items)) Sheets("Сверка").[u5:v5].Resize(.Count).Value = x End With
End Sub
Вот так будет завершающий этап. Вернее этот макрос показывает завершающий результат - поскольку я так думаю - что в Вашем варианте ему уже места нет, настолько все переделано - это я просто чтобы не мучаться с формулами подправил этот макрос Т.е. теперь это все три действия надо как то свести в одну кучу - без дополнительных листов и кнопок
Листы будут следовать так - как указано и будут еще добавляться, поэтому нужно учесть возможность добавления для расчета нужных листов! Они идут последовательно по отчетным периодам - так что по полседовательности листов ничего не нужно. Корректировки в одном расчетном периоде могут идти только за предыдущий период - т.е. например на листе 1-2011 могут быть корректировки только за перриод 1-2010,2-2010 но никак за 1-2011! Едиственное что - можно ли както учесть что может возникнуть такая ситуация - что например на листе 1-2011 может быть корректировка на уже откорректированную на листе 2-2010 за перриод 1-2010 - т.е. я так думаю, что данные на листе промежуточный (2) формируются последовательно по прохождению листов - и по идее последней будет корректировка, которая будет на листе 1-2011 - так вот последнюю и нужно будет брать в расчет! Ну вот как то так!Ed_Vard
Вот шаг три. Складываем N,O,P,Q из "Сверка" и "промежуточный", а разность стр. и нак. выводим в U, V. Правильно? Проверяйте.
Не совсем так! лист Сверка - это и должен быть итоговым - в нем получаються данные за текущий расчетный период - т.е. в нашем случае за 2-2011 - и на нем нам нужно получить итоговый результат - к разности на листе Сверка между t(0)=N-O и t(1)=P-Q прибавить зазность с листа промежуточный t(2)=N-O и t(3)=P-Q - т.е. вот так n(0)=t(0)+t(2) и n(1)=t(1)+t(3) - и вот эти n нужно вывести в U, V. Это как бы получаеться нарастающим итогом начиная с 1-2010. технологически это выглядит так: 1. На лист Сверка получаем данные из файлов xml за текущий расчетный перриод - в нашем случае за 2-2011 и производим вычисление t(0)=N-O и t(1)=P-Q 2. На основании данных с листа Серка при связке A+L - выбираем людей с листов 1-2010,2-2010,1-2011,КОРР-ТП за предыдущие расчетные перриоды (это они у нас заносяться при нажатии "PUSH 1" на лист промежуточный ) 3. При связке A+L+F+J+K и A+L+F+H+I заменяем ИСХ на КОР при нажатии "PUSH 2" на листе промежуточный 4. Нужно теперь на листе промежуточный сложить совпавшие данные при связке A+L и получить разность t(2)=N-O и t(3)=P-Q 5. На листе Сверка произвести сложение n(0)=t(0)+t(2) и n(1)=t(1)+t(3) и внести данные в U и V.
Т.е. Если вы нажмете кнопку ВР2_БК на листе сверка - то увидите как заносяться данные - только там не учитываються эти КОР - они там просто сумируються как обычные! Это просто для аналогии! Вот так вот должно получиться только с учетом всех манипуляций - т.е. листа промежуточные быть не должно - все вычисления в массиве!
Quote (nilem)
Вот шаг три. Складываем N,O,P,Q из "Сверка" и "промежуточный", а разность стр. и нак. выводим в U, V. Правильно? Проверяйте.
Не совсем так! лист Сверка - это и должен быть итоговым - в нем получаються данные за текущий расчетный период - т.е. в нашем случае за 2-2011 - и на нем нам нужно получить итоговый результат - к разности на листе Сверка между t(0)=N-O и t(1)=P-Q прибавить зазность с листа промежуточный t(2)=N-O и t(3)=P-Q - т.е. вот так n(0)=t(0)+t(2) и n(1)=t(1)+t(3) - и вот эти n нужно вывести в U, V. Это как бы получаеться нарастающим итогом начиная с 1-2010. технологически это выглядит так: 1. На лист Сверка получаем данные из файлов xml за текущий расчетный перриод - в нашем случае за 2-2011 и производим вычисление t(0)=N-O и t(1)=P-Q 2. На основании данных с листа Серка при связке A+L - выбираем людей с листов 1-2010,2-2010,1-2011,КОРР-ТП за предыдущие расчетные перриоды (это они у нас заносяться при нажатии "PUSH 1" на лист промежуточный ) 3. При связке A+L+F+J+K и A+L+F+H+I заменяем ИСХ на КОР при нажатии "PUSH 2" на листе промежуточный 4. Нужно теперь на листе промежуточный сложить совпавшие данные при связке A+L и получить разность t(2)=N-O и t(3)=P-Q 5. На листе Сверка произвести сложение n(0)=t(0)+t(2) и n(1)=t(1)+t(3) и внести данные в U и V.
Т.е. Если вы нажмете кнопку ВР2_БК на листе сверка - то увидите как заносяться данные - только там не учитываються эти КОР - они там просто сумируються как обычные! Это просто для аналогии! Вот так вот должно получиться только с учетом всех манипуляций - т.е. листа промежуточные быть не должно - все вычисления в массиве!Ed_Vard
По-моему, все правильно. Только результат надо выводить, видимо, сразу на Сверку. Попробуйте изменять N,O,P,Q на листе Сверка и понажимать кнопочки. Вроде бы все сходится.
По-моему, все правильно. Только результат надо выводить, видимо, сразу на Сверку. Попробуйте изменять N,O,P,Q на листе Сверка и понажимать кнопочки. Вроде бы все сходится.nilem
По-моему, все правильно. Только результат надо выводить, видимо, сразу на Сверку. Попробуйте изменять N,O,P,Q на листе Сверка и понажимать кнопочки. Вроде бы все сходится.
Кажеться -да - вроде все нормально! Т.е. от листа промежуточный уйти не получиться и собрать все в один макрос?
Quote (nilem)
По-моему, все правильно. Только результат надо выводить, видимо, сразу на Сверку. Попробуйте изменять N,O,P,Q на листе Сверка и понажимать кнопочки. Вроде бы все сходится.
Кажеться -да - вроде все нормально! Т.е. от листа промежуточный уйти не получиться и собрать все в один макрос?Ed_Vard
Получится, только сначала проверьте все как следует. Потому что потом уже точно не разобраться smile
А может как то по блокам с поянениями - а то если не разобраться - то это будет плохо - так как я всетаки хочу разобраться и при необходимости внести изменения если понадобиться! Я понимаю - что это может наглость с моей стороны - ноесли что-то нужно будет изменить - я вообще запутаюсь!
Quote (nilem)
Получится, только сначала проверьте все как следует. Потому что потом уже точно не разобраться smile
А может как то по блокам с поянениями - а то если не разобраться - то это будет плохо - так как я всетаки хочу разобраться и при необходимости внести изменения если понадобиться! Я понимаю - что это может наглость с моей стороны - ноесли что-то нужно будет изменить - я вообще запутаюсь!Гость
Доброго времеи суток, уважаемые форумчане. В связи с затянувшейся командировкой не не закончил с данным макросом за это время возник вопрос в этом макросе объявлятеся вот такой массив wshList = Array("1-2010", "2-2010", "1-2011"), где каждое значение - это определенный лист! в свзя с тем, что приходиться постоянно менять - какие листы учитывать, а какие нет возникла идея использовать чекбоксы - т.е. какждому листу будет соответствовать 1 чекбокс. При проверке - что чекбокс активирован - то лист к которому относиться данный чекбокс добавляеться в массив. Проблема в том - что не могу понять - как можно переменную добавить в данный массив! Подскажите - как это можно реализовать.
Доброго времеи суток, уважаемые форумчане. В связи с затянувшейся командировкой не не закончил с данным макросом за это время возник вопрос в этом макросе объявлятеся вот такой массив wshList = Array("1-2010", "2-2010", "1-2011"), где каждое значение - это определенный лист! в свзя с тем, что приходиться постоянно менять - какие листы учитывать, а какие нет возникла идея использовать чекбоксы - т.е. какждому листу будет соответствовать 1 чекбокс. При проверке - что чекбокс активирован - то лист к которому относиться данный чекбокс добавляеться в массив. Проблема в том - что не могу понять - как можно переменную добавить в данный массив! Подскажите - как это можно реализовать.Ed_Vard
Можно сделать разными способами - 1. можно перебирать массив Array("1-2010", "2-2010", "1-2011", "КОРР-ТП") и в соответствии с чекбоксом текущего листа формировать массив wshList - или через Redim Preserve наращивать, или собирать строку названий, которую позже разбить в массив с помощью Split 2. можно в существующем коде после For Each bu In wshList добавить проверку, что чекбокс этого bu в нужном состоянии.
Можно сделать разными способами - 1. можно перебирать массив Array("1-2010", "2-2010", "1-2011", "КОРР-ТП") и в соответствии с чекбоксом текущего листа формировать массив wshList - или через Redim Preserve наращивать, или собирать строку названий, которую позже разбить в массив с помощью Split 2. можно в существующем коде после For Each bu In wshList добавить проверку, что чекбокс этого bu в нужном состоянии.Hugo
Т.к. Ваших чекбоксов не видел - смотрите на мои. Сделал на строке.
что то интернет через пень-колоду работает! Hugo, да - вроде понял - в принципе подходит - только как обратиться к данному модулю - чтобы получить значения? Или весь свой модуль переписывать в модуль формы? т.е. у меня вот такой код [vba]
Code
Sub Выборка_по_работодателю() Dim VR, x, i&, j As Long, wshList(), bu, z(), per$ ', t() wshList = Array("1-2010", "2-2010", "1-2011", "2-2011", "3-2011", "4-2011", "1-2012", "2-2012", "3-2012", "4-2012") VR = "071-011-" & [N1] With Sheets("Выборка") .Range("A5:V" & .Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents End With ReDim z(1 To 100000, 1 To 17)
With CreateObject("Scripting.Dictionary") .CompareMode = 1
For Each bu In wshList x = Sheets(bu).Range("A5:Q" & Sheets(bu).Cells(Rows.Count, 1).End(xlUp).Row).Value For i = 1 To UBound(x) If x(i, 1) = VR Then If x(i, 5) = "ИСХ" Then If Not .Exists(x(i, 1) & x(i, 12)) Then j = j + 1: .Item(x(i, 1) & x(i, 12)) = j z(j, 1) = x(i, 1) z(j, 2) = Left(x(i, 2), 20) 'z(j, 3) = x(i, 3) 'z(j, 4) = x(i, 4) z(j, 5) = x(i, 5) z(j, 6) = x(i, 6) z(j, 7) = x(i, 7) z(j, 8) = x(i, 8) z(j, 9) = x(i, 9) z(j, 10) = x(i, 10) z(j, 11) = x(i, 11) z(j, 12) = x(i, 12) z(j, 13) = x(i, 13) End If End If End If Next i Next bu
End With
With [a5:q5].Resize(j) .Value = z
End With End Sub
[/vba]
и мне его надо в код формы засунуть? или можно как то получить значение для wshList из пользовательской формы? из функции представляю как а из формы неврублюсь
Quote (Hugo)
Т.к. Ваших чекбоксов не видел - смотрите на мои. Сделал на строке.
что то интернет через пень-колоду работает! Hugo, да - вроде понял - в принципе подходит - только как обратиться к данному модулю - чтобы получить значения? Или весь свой модуль переписывать в модуль формы? т.е. у меня вот такой код [vba]
Code
Sub Выборка_по_работодателю() Dim VR, x, i&, j As Long, wshList(), bu, z(), per$ ', t() wshList = Array("1-2010", "2-2010", "1-2011", "2-2011", "3-2011", "4-2011", "1-2012", "2-2012", "3-2012", "4-2012") VR = "071-011-" & [N1] With Sheets("Выборка") .Range("A5:V" & .Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents End With ReDim z(1 To 100000, 1 To 17)
With CreateObject("Scripting.Dictionary") .CompareMode = 1
For Each bu In wshList x = Sheets(bu).Range("A5:Q" & Sheets(bu).Cells(Rows.Count, 1).End(xlUp).Row).Value For i = 1 To UBound(x) If x(i, 1) = VR Then If x(i, 5) = "ИСХ" Then If Not .Exists(x(i, 1) & x(i, 12)) Then j = j + 1: .Item(x(i, 1) & x(i, 12)) = j z(j, 1) = x(i, 1) z(j, 2) = Left(x(i, 2), 20) 'z(j, 3) = x(i, 3) 'z(j, 4) = x(i, 4) z(j, 5) = x(i, 5) z(j, 6) = x(i, 6) z(j, 7) = x(i, 7) z(j, 8) = x(i, 8) z(j, 9) = x(i, 9) z(j, 10) = x(i, 10) z(j, 11) = x(i, 11) z(j, 12) = x(i, 12) z(j, 13) = x(i, 13) End If End If End If Next i Next bu
End With
With [a5:q5].Resize(j) .Value = z
End With End Sub
[/vba]
и мне его надо в код формы засунуть? или можно как то получить значение для wshList из пользовательской формы? из функции представляю как а из формы неврублюсь Ed_Vard
Сообщение отредактировал Ed_Vard - Понедельник, 27.02.2012, 02:17
Вернее я хотел сказать - что у меня несколько разных модулей - но в них есть данная строка и хотелось бы использовать Ваш вариант формирования строки массива. Или придется для каждого модуля делать свою форму?
Вернее я хотел сказать - что у меня несколько разных модулей - но в них есть данная строка и хотелось бы использовать Ваш вариант формирования строки массива. Или придется для каждого модуля делать свою форму?Ed_Vard
файл с моими данными имеет размер 22 метра - прицепить не получиться мне просто для примера - как можно получить данные сформированные в Вашем примере для wshList - использовать в одном из моих модулей - который я привел для 3 строки wshList. Т.е. подставить значения полученные в форме для значения в модуле для строки wshList.
Quote (Hugo)
А где Ваши чекбоксы?
файл с моими данными имеет размер 22 метра - прицепить не получиться мне просто для примера - как можно получить данные сформированные в Вашем примере для wshList - использовать в одном из моих модулей - который я привел для 3 строки wshList. Т.е. подставить значения полученные в форме для значения в модуле для строки wshList.Ed_Vard
Если чекбоксы на листе - то можно в каждом модуле вызывать макрос (один отдельный), который будет считывать эти чекбоксы и формировать массив нужных названий - можно его сделать публичным или передавать дальше как параметр. Если чекбоксы на форме - это можно делать нажатием ОК в форме. Что в общем уже в коде в файле выше почти реализовано, чуть подправьте:
В модуле:
[vba]
Code
Option Explicit Public wshList
Sub tt() UserForm1.Show End Sub
Sub tttt() Dim i& For i = 0 To UBound(wshList) MsgBox wshList(i) Next End Sub
[/vba] В форме:
[vba]
Code
Private Sub CommandButton1_Click() Dim arr, s$, i& ', wshList ' тут объявление убираем arr = Array("1-2010", "2-2010", "1-2011", "КОРР-ТП") For i = 0 To UBound(arr) If Me.Controls("CheckBox" & i + 1) Then s = s & arr(i) & "|" Next s = Left(s, Len(s) - 1) Module1.wshList = Split(s, "|") End Sub
[/vba] Вызываете форму, отмечаете чекбоксы, жмёте ОК (ну или можно переделать). Далее закрыли форму, выполняете tttt второй/другой кнопкой или своим кодом - перебираете сформированный формой массив.
Если чекбоксы на листе - то можно в каждом модуле вызывать макрос (один отдельный), который будет считывать эти чекбоксы и формировать массив нужных названий - можно его сделать публичным или передавать дальше как параметр. Если чекбоксы на форме - это можно делать нажатием ОК в форме. Что в общем уже в коде в файле выше почти реализовано, чуть подправьте:
В модуле:
[vba]
Code
Option Explicit Public wshList
Sub tt() UserForm1.Show End Sub
Sub tttt() Dim i& For i = 0 To UBound(wshList) MsgBox wshList(i) Next End Sub
[/vba] В форме:
[vba]
Code
Private Sub CommandButton1_Click() Dim arr, s$, i& ', wshList ' тут объявление убираем arr = Array("1-2010", "2-2010", "1-2011", "КОРР-ТП") For i = 0 To UBound(arr) If Me.Controls("CheckBox" & i + 1) Then s = s & arr(i) & "|" Next s = Left(s, Len(s) - 1) Module1.wshList = Split(s, "|") End Sub
[/vba] Вызываете форму, отмечаете чекбоксы, жмёте ОК (ну или можно переделать). Далее закрыли форму, выполняете tttt второй/другой кнопкой или своим кодом - перебираете сформированный формой массив.Hugo
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
