Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Создание структуры макросом. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Создание структуры макросом. (Макросы/Sub)
Создание структуры макросом.
Iurii Дата: Вторник, 23.07.2019, 12:34 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день.
Просьба помочь оптимизировать макрос.
Есть файл(файл с макросом добавил во вложение) с однородными данными в колонках A-C. Нужно создать структуру/группировку + для каждого уровня добавить по строке где будут прописаны формулы (промежуточные итоги, максимумы и т. д.)

Написал макрос для этой цели. Макрос работает, но метод который я использовал медленный. Файл с 200 тис. строк группировал около часа(и это макрос прописывал только 2 формулы, а будет больше). Метод банальный, макрос проверяет значения верхней и нижней ячеек в колонке, если они одинаковые то ничего не делаем, если они разные - значит нужно добавить строку и сгруппировать строки выше.
Подскажите, пожалуйста, как ускорить мой макрос.



В дальнейшем, чтобы для каждого из 3х уровней не дублировать одинаковый код, помещу его в цикл(действия одинаковые, разница в номере колонки).

P.S. Еще есть проблема с счетчиком цикла. Нужно перебрать все ячейки со 2й по последнюю. For i = 2 To LC. (LC = Sheets("рабочий").Range("A1").End(xlDown).Row - узнаю последнюю ячейку в файле), но так как в цикле нужно добавлять строки, количество которых я не знаю, цикл останавливаеться до перебора всех ячеек. Придумал "костыль" добавить к LC +100(число которое больше чем добавляться строк). Можно как то обновить значение счетчика LC внутри цикла?

Спасибо!
К сообщению приложен файл: Test.xlsb (34.8 Kb)


Сообщение отредактировал Iurii - Вторник, 23.07.2019, 12:36
 
Ответить
СообщениеДобрый день.
Просьба помочь оптимизировать макрос.
Есть файл(файл с макросом добавил во вложение) с однородными данными в колонках A-C. Нужно создать структуру/группировку + для каждого уровня добавить по строке где будут прописаны формулы (промежуточные итоги, максимумы и т. д.)

Написал макрос для этой цели. Макрос работает, но метод который я использовал медленный. Файл с 200 тис. строк группировал около часа(и это макрос прописывал только 2 формулы, а будет больше). Метод банальный, макрос проверяет значения верхней и нижней ячеек в колонке, если они одинаковые то ничего не делаем, если они разные - значит нужно добавить строку и сгруппировать строки выше.
Подскажите, пожалуйста, как ускорить мой макрос.



В дальнейшем, чтобы для каждого из 3х уровней не дублировать одинаковый код, помещу его в цикл(действия одинаковые, разница в номере колонки).

P.S. Еще есть проблема с счетчиком цикла. Нужно перебрать все ячейки со 2й по последнюю. For i = 2 To LC. (LC = Sheets("рабочий").Range("A1").End(xlDown).Row - узнаю последнюю ячейку в файле), но так как в цикле нужно добавлять строки, количество которых я не знаю, цикл останавливаеться до перебора всех ячеек. Придумал "костыль" добавить к LC +100(число которое больше чем добавляться строк). Можно как то обновить значение счетчика LC внутри цикла?

Спасибо!

Автор - Iurii
Дата добавления - 23.07.2019 в 12:34
bmv98rus Дата: Вторник, 23.07.2019, 13:06 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Код смотрел по диагонали, просто не очень интересно
1. для ускорения отключите пересчет. То что отключено отображение и предупреждении не отключают расчет при изменениях на листах, а при вставке строк и формул пересчет запускается.
2. цикл FOR можно заменить на While/Dо , и лучше держать счетчик строк определенный в начале и прибавлять к нему во время вставки нужное количество.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеКод смотрел по диагонали, просто не очень интересно
1. для ускорения отключите пересчет. То что отключено отображение и предупреждении не отключают расчет при изменениях на листах, а при вставке строк и формул пересчет запускается.
2. цикл FOR можно заменить на While/Dо , и лучше держать счетчик строк определенный в начале и прибавлять к нему во время вставки нужное количество.

Автор - bmv98rus
Дата добавления - 23.07.2019 в 13:06
Iurii Дата: Вторник, 23.07.2019, 13:26 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
bmv98rus, Спасибо за советы.

Я правильно понял, нужно в начало добавить Application.Calculation = xlCalculationManual, а в конец Application.Calculation = xlCalculationAutomatic?
Я думал что отключение пересчета негативно повлияет на вставку формул.

Перепишу цикл на While/Dо.

После вставки строк я добавлял к счетчику +1 но что то не помогло, подскажите что я сделал не так:

 
Ответить
Сообщениеbmv98rus, Спасибо за советы.

Я правильно понял, нужно в начало добавить Application.Calculation = xlCalculationManual, а в конец Application.Calculation = xlCalculationAutomatic?
Я думал что отключение пересчета негативно повлияет на вставку формул.

Перепишу цикл на While/Dо.

После вставки строк я добавлял к счетчику +1 но что то не помогло, подскажите что я сделал не так:


Автор - Iurii
Дата добавления - 23.07.2019 в 13:26
bmv98rus Дата: Вторник, 23.07.2019, 13:48 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
1 - да
2 -
подскажите что я сделал не так:

не внимательно прочли совет, я не говорил продолжить использовать For он определяет границы изменения переменной один раз и как бы не менялась значение LC потом, на количество циклов не повлияет.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение1 - да
2 -
подскажите что я сделал не так:

не внимательно прочли совет, я не говорил продолжить использовать For он определяет границы изменения переменной один раз и как бы не менялась значение LC потом, на количество циклов не повлияет.

Автор - bmv98rus
Дата добавления - 23.07.2019 в 13:48
_Boroda_ Дата: Вторник, 23.07.2019, 14:39 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. Бегите по строкам снизу вверх
2. засуньте данные в массив и оттуда уже сравнивайте
[vba]
Код
    nach = 2
    n_ = Cells(Rows.Count, 1).End(3).Row - nach + 1
    ar1 = Cells(nach, 3).Resize(n_)
    For i = n_ To nach Step -1
        If ar1(i, 1) <> ar1(i - 1, 1) Then
[/vba]

3. К теме не относится, но все-таки[vba]
Код
Dim nach, i, k, kon, LC As Long
[/vba] 'почитайте про объявление переменных. Long только LС

4. Выделите таблицу - вкладка Данные - Промежуточный итог. Там поиграйтесь (снимите 3 галки внизу) для групп и потом для менеджеров (поверх того, что уже сделали для групп). Потом попробуйте записать все это макросом. Возможно подойдет


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. Бегите по строкам снизу вверх
2. засуньте данные в массив и оттуда уже сравнивайте
[vba]
Код
    nach = 2
    n_ = Cells(Rows.Count, 1).End(3).Row - nach + 1
    ar1 = Cells(nach, 3).Resize(n_)
    For i = n_ To nach Step -1
        If ar1(i, 1) <> ar1(i - 1, 1) Then
[/vba]

3. К теме не относится, но все-таки[vba]
Код
Dim nach, i, k, kon, LC As Long
[/vba] 'почитайте про объявление переменных. Long только LС

4. Выделите таблицу - вкладка Данные - Промежуточный итог. Там поиграйтесь (снимите 3 галки внизу) для групп и потом для менеджеров (поверх того, что уже сделали для групп). Потом попробуйте записать все это макросом. Возможно подойдет

Автор - _Boroda_
Дата добавления - 23.07.2019 в 14:39
bmv98rus Дата: Вторник, 23.07.2019, 15:06 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
_Boroda_, Алексанра, а вот не совсем согласная я,
1. сложнее определить порой начало конец блока, но не видя данных сказать сложно. по этом и да и нет.
2. мне кажется основные тормоза не из-за перебора а именно из-а вставки.

[offtop]
Dim nach, i, k, kon, LC As Long

зато с этим
Dim a, B, col1, col2 As Variant
получилось как надо :-)[/offtop]


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение_Boroda_, Алексанра, а вот не совсем согласная я,
1. сложнее определить порой начало конец блока, но не видя данных сказать сложно. по этом и да и нет.
2. мне кажется основные тормоза не из-за перебора а именно из-а вставки.

[offtop]
Dim nach, i, k, kon, LC As Long

зато с этим
Dim a, B, col1, col2 As Variant
получилось как надо :-)[/offtop]

Автор - bmv98rus
Дата добавления - 23.07.2019 в 15:06
_Boroda_ Дата: Вторник, 23.07.2019, 15:17 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. Моя не понимай твою русский
сложнее определить порой начало конец блока, но не видя данных сказать сложно. по этом и да и нет.


2. Безусловно. Нро если уж все равно вставляем, то лучше уж хоть как-то ускорить

3. или можно не вставлять. Создать еще один массив (в 2 раза больший исходного - 2n) и заполнять его данными из первого массива и расчетными формулами. Вот здесь уже цикл сверху вниз нужен будет. Потом вставить второй массив на лист вместо первого. И отдельно в том же цикле сделать массив n х 3, который (не весь, а сколько получится группировок) будем заполнять строками начала и окончания и уровнем группировок. Потом пробегаемся по этому массиву и лепим группировки на листе

4. Но все равно гораздо быстрее будет использовать встроенные возможности
Выделите таблицу - вкладка Данные - Промежуточный итог


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. Моя не понимай твою русский
сложнее определить порой начало конец блока, но не видя данных сказать сложно. по этом и да и нет.


2. Безусловно. Нро если уж все равно вставляем, то лучше уж хоть как-то ускорить

3. или можно не вставлять. Создать еще один массив (в 2 раза больший исходного - 2n) и заполнять его данными из первого массива и расчетными формулами. Вот здесь уже цикл сверху вниз нужен будет. Потом вставить второй массив на лист вместо первого. И отдельно в том же цикле сделать массив n х 3, который (не весь, а сколько получится группировок) будем заполнять строками начала и окончания и уровнем группировок. Потом пробегаемся по этому массиву и лепим группировки на листе

4. Но все равно гораздо быстрее будет использовать встроенные возможности
Выделите таблицу - вкладка Данные - Промежуточный итог

Автор - _Boroda_
Дата добавления - 23.07.2019 в 15:17
Iurii Дата: Вторник, 23.07.2019, 16:09 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо за советы и критику =).
2. засуньте данные в массив и оттуда уже сравнивайте

Спасибо за код, я в массивах еще не разбираюсь, всегда обходил их стороной - будет повод подучить.

3. К теме не относится, но все-таки
Dim nach, i, k, kon, LC As Long
'почитайте про объявление переменных. Long только LС

В готовом файле будет строк около 200тис. nach, i, kon, LC - это номера строк, их значение может быть до 200к, а у Integer диапазон до 32767. А k(длина промежуточного итога) сделаю Integer'ом =)

зато с этим
Dim a, B, col1, col2 As Variant
получилось как надо :-)

Да, тут "психанул" не был уверен каким типом данных цвет объявлять, думал временно поставлю Variant, а потом вернусь и исправлю, нооо.. "Ничто так не постоянно, как временное"(с)


Сообщение отредактировал Iurii - Вторник, 23.07.2019, 16:19
 
Ответить
СообщениеСпасибо за советы и критику =).
2. засуньте данные в массив и оттуда уже сравнивайте

Спасибо за код, я в массивах еще не разбираюсь, всегда обходил их стороной - будет повод подучить.

3. К теме не относится, но все-таки
Dim nach, i, k, kon, LC As Long
'почитайте про объявление переменных. Long только LС

В готовом файле будет строк около 200тис. nach, i, kon, LC - это номера строк, их значение может быть до 200к, а у Integer диапазон до 32767. А k(длина промежуточного итога) сделаю Integer'ом =)

зато с этим
Dim a, B, col1, col2 As Variant
получилось как надо :-)

Да, тут "психанул" не был уверен каким типом данных цвет объявлять, думал временно поставлю Variant, а потом вернусь и исправлю, нооо.. "Ничто так не постоянно, как временное"(с)

Автор - Iurii
Дата добавления - 23.07.2019 в 16:09
Iurii Дата: Вторник, 23.07.2019, 16:40 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
4. Выделите таблицу - вкладка Данные - Промежуточный итог. Там поиграйтесь (снимите 3 галки внизу) для групп и потом для менеджеров (поверх того, что уже сделали для групп). Потом попробуйте записать все это макросом. Возможно подойдет


Так пробовал, получается не очень. Промежуточный итог не всегда правильно итоговые строки вставляет, а макросом я могу строки вставлять куда хочу и прописывать формулы какие хочу.


Сообщение отредактировал Iurii - Вторник, 23.07.2019, 16:47
 
Ответить
Сообщение
4. Выделите таблицу - вкладка Данные - Промежуточный итог. Там поиграйтесь (снимите 3 галки внизу) для групп и потом для менеджеров (поверх того, что уже сделали для групп). Потом попробуйте записать все это макросом. Возможно подойдет


Так пробовал, получается не очень. Промежуточный итог не всегда правильно итоговые строки вставляет, а макросом я могу строки вставлять куда хочу и прописывать формулы какие хочу.

Автор - Iurii
Дата добавления - 23.07.2019 в 16:40
bmv98rus Дата: Вторник, 23.07.2019, 16:50 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
В готовом файле будет строк около 200тис. nach, i, kon, LC - это номера строк

Вот Вам и написали, что при вашей записи только LC будет Long , а всё что до этого автоматом Variant. То что работает, это тольок по тому что оно включает любой тип.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
В готовом файле будет строк около 200тис. nach, i, kon, LC - это номера строк

Вот Вам и написали, что при вашей записи только LC будет Long , а всё что до этого автоматом Variant. То что работает, это тольок по тому что оно включает любой тип.

Автор - bmv98rus
Дата добавления - 23.07.2019 в 16:50
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Создание структуры макросом. (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!