Страницы: 1
RSS
Статус бар с количеством заполненных и пустых ячеек
 
Здравствуйте!

К сожалению, не получилось самому разобраться, прошу помощи.

У меня есть макрос, собрал его из разных источников, так что если кто-то увидит знакомое - спасибо вам!)))

Код
Sub PereborDiapazonaYacheek()
'Отключаем обновление экрана
    Application.ScreenUpdating = False
'Шаг 1: Объявить переменные
    Dim MyRange As Range
    Dim MyCell As Range
'Шаг 2: Определение целевого диапазона
    Set MyRange = Range("D5:O943")
'Шаг 3: Запуск цикла через диапазон
    For Each MyCell In MyRange
'Шаг 4: Какое-либо действие с каждой ячейкой
    Range("D4").Select
    Selection.Copy
    MyCell.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
'Шаг 5: Перейти к следующей ячейке в диапазоне
    Next MyCell
'Возвращаем обновление экрана
    Application.ScreenUpdating = True
End Sub

Смысл его в том, что он копирует формулу из ячейки D4 в каждую отдельную ячейку диапазона D5:O943 и потом копирует только её значение. И так по очереди по каждой ячейке.

Хочу вставить сюда стандартный статус бар (который отображает результат в левом нижнем углу окна), чтобы он вел подсчет по этому диапазону типа: "Обработано 80 ячеек, осталось 12000"

Все ячейки в этом диапазоне имеют какое то значение, пустых нет.

 
Код
Sub PereborDiapazonaYacheek2()
'Отключаем обновление экрана
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
'Шаг 1: Объявить переменные
    Dim MyRange As Range
    Dim MyCell As Range
'Шаг 2: Определение целевого диапазона
    Set MyRange = Range("D5:O943")
    
    MyRange.FormulaR1C1 = Range("D4").FormulaR1C1
    MyRange.Parent.UsedRange.Calculate
    
    Dim a As Variant
    a = MyRange
    MyRange = a
    
'Возвращаем обновление экрана
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
 
DaePae, Я правильно понимаю что вместо быстрого результата вам нужна визуализация искуственно замедленного процесса?

Код
Sub PereborDiapazonaYacheek()
    Range("D4").Copy
    With Range("D5:O943")
        .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub


Цитата
МатросНаЗебре написал:
   MyRange.Parent.UsedRange.Calculate
- а вот это опасно. Не зная что за формула и какие связи , включены ли итерации, можно пересчитать не всё что нужно и получить некорректный результат.
Но если даже так то почему не просто
Код
    MyRange.Calculate


А вот за напоминание FormulaR1C1 = Range("D4").FormulaR1C1 спасибо. Позволило уложиться в отведенных мне 10 строк :-)

Код
Sub PereborDiapazonaYacheek1()
    With Range("D5:O943")
        .FormulaR1C1 = Range("D4").FormulaR1C1
        .Calculate
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub
Изменено: БМВ - 03.04.2020 11:29:36
По вопросам из тем форума, личку не читаю.
 
БМВ,
Цитата
БМВ написал: вместо быстрого результата вам нужна визуализация искуственно замедленного процесса?
В этом случае не особо важно сколько это займет времени - в любом случае будет не быстро. Выполнение этого макроса я буду ставить, например, на ночь. За это время точно закончит обработку. Мне важно в это момент понимать - что-то происходит, идет ли какой то процесс, или нет (меняется количество заполненных ячеек + примерное понимание когда это закончится - тоже нужно).

Этот код:
Код
Sub PereborDiapazonaYacheek()
    Range("D4").Copy
    With Range("D5:O943")
        .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub
работает на 50 % быстрее - спасибо, буду его использовать!

А этот код:
Код
Sub PereborDiapazonaYacheek1()
    With Range("D5:O943")
        .FormulaR1C1 = Range("D4").FormulaR1C1
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub
копирует значение ячейки D4 во весь диапазон.

Цитата
БМВ написал: Не зная что за формула и какие связи , включены ли итерации, можно пересчитать не всё что нужно и получить некорректный результат.
прикрепил файл, в котором все это делаю (лист "1")
Изменено: DaePae - 03.04.2020 13:47:58
 
DaePae, Ответы уже пошли не по вопросу, но беда в том что у вас сама формула некорректна с точки зрения производительности, вот и тормозит все

=IFERROR(IF(ISNA(MATCH($A4&D$3&D$1&D$2;'2'!$C:$C&'2'!$A:$A&'2'!$D:$D&'2'!$B:$B;0));
INDEX('3'!$A$1:$G$2560;MATCH($A4&D$3&D$1;'3'!$C:$C&'3'!$A:$A&'3'!$B:$B;0);MATCH(D$2;'3'!$1:$1;0));
IF(INDEX('3'!$A$1:$G$2560;MATCH($A4&D$3&D$1;'3'!$C:$C&'3'!$A:$A&'3'!$B:$B;0);MATCH(D$2;'3'!$1:$1;0))="";"не должны, но внесли";"Х"));
"нет в выгрузке")

Вы используете для поиска диапазон всего столбца  или строки. В случае одноразового выполнения - это худо бедно отработает, но не в случае массовой формулы. Меняйте на здравые. Все что такое '2'!$C:$C&'2'!$A:$A&'2'!$D:$D&'2'!$B:$B - это полный атас. Далее Дважды искать не имеет смысла, двойная работа и первый ЕСЛИ надо менять на ЕСЛИОШ.
Уверен, что залетает все.

Ну и в качестве реабилитации второго скрипта- кто ж знал что у вас формула массива в ячейке. Тут требуется по иному.
Изменено: БМВ - 03.04.2020 10:48:43
По вопросам из тем форума, личку не читаю.
 
Цитата
Хочу вставить сюда стандартный статус бар (который отображает результат в левом нижнем углу окна),
Может вам это нужно
http://www.excel-vba.ru/chto-umeet-excel/otobrazit-process-vypolneniya/
 
Изучал вчера. Но проблема в том, что, как мне кажется,  в моем макросе немного другой подход к количеству циклов (не знаю правильно или нет выражаюсь).

Т.е. цикл выглядет не так:
Код
lr = 1 To lAllCnt
или как то еще. Поэтом у меня сразу ступор, знакомого ничего не вижу, повторить не смогу, пытался.

Я уже подумал немного упростить и сделать просто MsgBox с отсчетом обратного времени. Я точно знаю сколько потребуется времени на заполнение всех ячеек (примерно), хочу указать фиксированное время, пусть отсчитывается назад (при необходимости поменяю).

Пока ищу решение в интернете, еще не нашел. Если кто поможет прописать это в макросе (MsgBox с отчетом фиксированного времени назад), буду очень благодарен.
Изменено: DaePae - 03.04.2020 13:48:20
 
DaePae, читайте №6. Вам нужно оптимизировать формулу, а не мультик показывать пока она считает.
Ограничьте диапазон столбцов разумным, Сколько у вас максимально данных? 10 -100 тыс. но не миллион же. Сколько раз вы по этому миллиону бегаете сейчас? Вот ваши тормоза и вылезают.
Изменено: БМВ - 03.04.2020 10:59:14
По вопросам из тем форума, личку не читаю.
 
Согласен с вами, увеличивает время и очень сильно. Но я прикинул, на данный момент это займет около 30 минут, каждый год время обработки будет увеличиваться на эти 30 минут. Это не страшно. Но зато я про это могу забыть и знаю, что она всегда пробежит этот диапазон и его не нужно никогда контролировать. Конечно, когда обработка дойдет до, например, 10 часов, это уже будет странно, но и в таблице появятся в этом случае данные за 20 лет - это вряди когда то наступит в этой таблице, к этому времени это уже роботы будут делать на луне)))

Спасибо вам за замечания, но при таком подходе мне немного легче. А то, что вы помогли с макросом и так его оптимизировали - еще раз спасибо, я понял принцип и буду его теперь применять, именно так.
Изменено: DaePae - 03.04.2020 13:48:41
 
Цитата
DaePae написал:
Но зато я про это могу забыть и знаю, что она всегда пробежит этот диапазон и его не нужно никогда контролировать.
тогда поясните, как вас спасет это при  условии что  вы уже зафиксировали диапазон INDEX('3'!$A$1:$G$2560; чтоб вы ненашли в строках больше 2560 не отработает. и будет ошибка
попробуйте так, за логику я не уверен

=IFERROR(
IFERROR(
IF(INDEX('3'!$A$1:$G$2560;MATCH($A4&D$3&D$1;'3'!$C$1:$C$2560&'3'!$A$1:$A$2560&'3'!$B$1:$B$2560;0);MATCH(D$2;'3'!$1:$1;0))="";"не должны, но внесли";"Х");
INDEX('3'!$A$1:$G$2560;MATCH($A4&D$3&D$1;'3'!$C$1:$C$2560&'3'!$A$1:$A$2560&'3'!$B$1:$B$2560;0);MATCH(D$2;'3'!$1:$1;0)));
"нет в выгрузке")
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал: как вас спасет это при  условии что  вы уже зафиксировали диапазон INDEX('3'!$A$1:$G$2560
Это ошибка))) Еще раз спасибо))) Очень грубая и страшная ошибка.
Изменено: DaePae - 03.04.2020 13:49:29
 
Цитата
DaePae написал:
как мне кажется,  в моем макросе немного другой подход к количеству циклов
ну и да и как бы и нет. Ваш цикл:
Код
For Each MyCell In MyRange 'Шаг 4: Какое-либо действие с каждой ячейкой
    
Next MyCell
теперь подпихиваем:
Код
Dim lr as long, lAllCnt as long
lAllCnt = MyRange.Cells.Count
lr = 0
For Each MyCell In MyRange 'Шаг 4: Какое-либо действие с каждой ячейкой
    lr = lr +1
    '.... какие-то действия с ячейкой
    'вывод статус-бара
Next MyCell
но как отмечено ранее - Ваш код нуждается не в визуализации, а в оптимизации. И в общем-то все советы даны. Написал этот пост лишь для того, чтобы наглядно показать как в таких случаях использовать подсчет ячеек для статус-бара.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, Дмитрий, Тут главное чтоб затраты на статус бар и его индикацию суммарно не превысили затраты на основное действие  :D . При текущей формуле это конечно не относится к данной задаче.

Цитата
DaePae написал: Это... Очень грубая и страшная ошибка.
Ок, тогда задачу надо решать иначе, раз уже все равно макрос, то временно сделать на листе 3 столбец где склеиваем нужное для поиска, что сразу позволит уйти от формул массива и ускорит расчет. Диапазон для поиска тоже можно сделать динамическим, это конечно сделает формулу длиннее, но тоже ускорит расчет. А если сразу формулу генерить в коде, то и вовсе диапазон сразу можно подставить нужный.

30 мин калькуляции  - люди, что вы делаете?!!! Планета перегревается от таких расчетов!!!

о сам не заметил, как каламбур написал  planetaexcel перегревается от таких расчетов  :D

ТЕМА: Как при помощи статус бара использовать остатки мощности процессора занятого не эффективным расчетом?  ;)
Изменено: БМВ - 03.04.2020 13:50:08
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
главное чтоб затраты на статус бар и его индикацию суммарно не превысили затраты на основное действие
и опять: и да и нет. Бывали у меня случаи, когда заказчику было не важно выполнится код за 5 секунд или за минуту - главное, чтобы красиво показать сам факт того, что что-то делается  :D  
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
БМВ, Пока так. Вы бы знали с чего я начинал...))) "Совершенствуюсь" ;)
Изменено: DaePae - 03.04.2020 12:54:02
 
В данном случае, поскольку всё равно применяются макросы, нет смысла в использовании формул. Время расчета не должно превышать 3 секунд (для 2500 организаций) и "мультфильм" не нужен.
Владимир
 
Цитата
sokol92 написал:
поскольку всё равно применяются макросы, нет смысла в использовании формул
А медведь против!!! Отказ от формулы может сильно усложнить код, и не факт что даст выигрыш в скорости, ну разве что область значений будет обрабатывать.
По вопросам из тем форума, личку не читаю.
 
Здравствуйте, Михаил! Вот так и делаются фейк-ньюс! :)  Самое главное убрали из цитаты "в даном случае". А так - я часто вставляю макросом формулы с последующей заменой на значения.
Изменено: sokol92 - 03.04.2020 13:34:03
Владимир
 
Владимир, приветствую. Нет, я исключительно про данный случай говорю. Хотя как всегда считаю, что прибегать к коду нужно только тогда когда другие возможности или отсутствуют или не эффективны, как правило из-за большого количества повторных операций.
Изменено: БМВ - 03.04.2020 14:08:06
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
прибегать к коду нужно только тогда когда другие возможности или отсутствуют или не эффективны
Возможно я не прав, но хочется изменить формулировку: Или если НЕТ возможности применять стандартные возможности  :)  
 
Цитата
БМВ написал:
раз уже все равно макрос, то временно сделать на листе 3 столбец где склеиваем нужное
Для макроса "третий столбец не нужен".
Цитата
sokol92 написал:
В данном случае, поскольку всё равно применяются макросы, нет смысла в использовании формул. Время расчета не должно превышать 3 секунд (для 2500 организаций)
Даже если проверять весь столбец, время расчета не буде превышать 3-5 сек; для 2500 организаций - глазом моргнуть не успеете.
Цитата
БМВ написал:
Отказ от формулы может сильно усложнить код, и не факт что даст выигрыш в скорости,
использование простых массивов, в данном случае, код не усложнит; а время расчетов уменьшит во много раз.
 
Михаил Витальевич С.,  Михаил, для формулы 3й столбец тоже не обязателен, но облегчит, а вот по времени расчета кодом-  может и да, а может и нет. В любом случае правильно написанная формула будет работать много быстрее чем сейчас.  А код будет сложнее.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
В любом случае правильно написанная формула будет работать много быстрее чем сейчас.
согласен
Цитата
БМВ написал:
А код будет сложнее.
для меня - неоднозначно...
Страницы: 1
Наверх