Всем доброго времени суток! Лихорадочно пытаюсь уменьшить вес создаваемой таблицы на 20000 строк и 600 столбцов (сам не хочу, но начальство видит это только так и слов на понимает Вопрос : можно ли заменить на функцию пользователя кучу СУММПРОИЗВ ? и будет ли это реально быстрее данной функции ?
Всем доброго времени суток! Лихорадочно пытаюсь уменьшить вес создаваемой таблицы на 20000 строк и 600 столбцов (сам не хочу, но начальство видит это только так и слов на понимает Вопрос : можно ли заменить на функцию пользователя кучу СУММПРОИЗВ ? и будет ли это реально быстрее данной функции ?китин
Игорь, ЮДФ обычно медленнее стандартных функций. Исключения могут быть для вложенных функций в Excel. А вот если сделать не UDF-кой, а просто вставку значений макросом (сначала всё по кнопке, потом по событию изменения в строке вставку пересчет макросом и вставку нового только в этой строке, то должно быть быстрее. Сам сделаешь или помочь?
Игорь, ЮДФ обычно медленнее стандартных функций. Исключения могут быть для вложенных функций в Excel. А вот если сделать не UDF-кой, а просто вставку значений макросом (сначала всё по кнопке, потом по событию изменения в строке вставку пересчет макросом и вставку нового только в этой строке, то должно быть быстрее. Сам сделаешь или помочь?_Boroda_
Намного медленнее? А на вес файла как ЮДФки влияют? А то у меня на этих объемах да еще с СУММПРОИЗВ файлик до 7 метров подрос. Может Бог с ней со скоростью.(особенно если разница невелика)
Намного медленнее? А на вес файла как ЮДФки влияют? А то у меня на этих объемах да еще с СУММПРОИЗВ файлик до 7 метров подрос. Может Бог с ней со скоростью.(особенно если разница невелика)китин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
можно ли заменить на функцию пользователя кучу СУММПРОИЗВ
Можно : [vba]
Код
Function D(R As Range, R1 As Range) Dim M(), M1(), MF(), I&, II& M = R.Value M1 = R1.Value ReDim MF(1 To UBound(M), 1 To 1) For I = 1 To UBound(M) For II = 1 To UBound(M, 2) MF(I, 1) = MF(I, 1) + M(I, II) * M1(1, II) Next Next D = MF End Function
[/vba] Это будет формула массива - вводимая для всех строк сразу. За счет того что это будет одна формула Возможно будет быстрее... но далеко не факт. все же
можно ли заменить на функцию пользователя кучу СУММПРОИЗВ
Можно : [vba]
Код
Function D(R As Range, R1 As Range) Dim M(), M1(), MF(), I&, II& M = R.Value M1 = R1.Value ReDim MF(1 To UBound(M), 1 To 1) For I = 1 To UBound(M) For II = 1 To UBound(M, 2) MF(I, 1) = MF(I, 1) + M(I, II) * M1(1, II) Next Next D = MF End Function
[/vba] Это будет формула массива - вводимая для всех строк сразу. За счет того что это будет одна формула Возможно будет быстрее... но далеко не факт. все же
Растянул А:Т и СУММПРОИЗВ по столбцу V на 2000 строк. Сохранил. Размер 201 509.
Поставил свою простенькую Юдф на столбец V [vba]
Код
Function SumPr(d1 As Range, d2 As Range) n_ = d1.Cells.Count'кол-во ячеек в диапазоне For I = 1 To n_'цикл от 1 до n z_ = d1(I) * d2(I) + z_'i-е значения перемножаем и складываем с уже перемноженными ранее Next I SumPr = z_ End Function
[/vba]Размер 201 588.
Поставил ЮДФ Ярослава. Размер 202 565.
Растянул А:Т и СУММПРОИЗВ по столбцу V на 2000 строк. Сохранил. Размер 201 509.
Поставил свою простенькую Юдф на столбец V [vba]
Код
Function SumPr(d1 As Range, d2 As Range) n_ = d1.Cells.Count'кол-во ячеек в диапазоне For I = 1 To n_'цикл от 1 до n z_ = d1(I) * d2(I) + z_'i-е значения перемножаем и складываем с уже перемноженными ранее Next I SumPr = z_ End Function
Function D(R As Range, R1 As Range) Dim M(), M1(), MF(), I&, II& M = R.Value 'Закидываем диапазон R в массив M1 = R1.Value 'Закидываем диапазон R1 в массив ReDim MF(1 To UBound(M), 1 To 1) 'Создаем новый пустой массив с количеством строк = массив 1 For I = 1 To UBound(M) 'Проходим по всем строкам For II = 1 To UBound(M, 2) 'Проходим по всем столбцам MF(I, 1) = MF(I, 1) + M(I, II) * M1(1, II) 'Выполняем перемножение и суммирование Next Next D = MF 'отдаем функции результат. End Function 'Радуемся :)
Саш, так у меня тоже простенькая - по идее на больших объемах моя должна быстрее считать, потому что все вычисления происходят в массивах и только один раз. А в твоей функции она в каждой строке - обращается к диапазону... Игорь - с Вас отчет о тестировании
Function D(R As Range, R1 As Range) Dim M(), M1(), MF(), I&, II& M = R.Value 'Закидываем диапазон R в массив M1 = R1.Value 'Закидываем диапазон R1 в массив ReDim MF(1 To UBound(M), 1 To 1) 'Создаем новый пустой массив с количеством строк = массив 1 For I = 1 To UBound(M) 'Проходим по всем строкам For II = 1 To UBound(M, 2) 'Проходим по всем столбцам MF(I, 1) = MF(I, 1) + M(I, II) * M1(1, II) 'Выполняем перемножение и суммирование Next Next D = MF 'отдаем функции результат. End Function 'Радуемся :)
Саш, так у меня тоже простенькая - по идее на больших объемах моя должна быстрее считать, потому что все вычисления происходят в массивах и только один раз. А в твоей функции она в каждой строке - обращается к диапазону... Игорь - с Вас отчет о тестировании SLAVICK
по идее на больших объемах моя должна быстрее считать
Скорее всего да. Я потому и написал, что совсем по-простому сделал. Просто потому, что здесь другой подход нужен. Например, такой - формулы только в первой строке таблицы (в строке 6), остальное - значениями. И есть 2 макроса - 1. копирует формулы из строки 6, размножает их вниз и преобразует все это в значения, 2. при изменении чего-либо в столбцах F:T - 2.1. если изменение в строках 1:4 - см. п.1 2.2. если изменение в строках 6:ПоследняяЗаполненнаяПоСтолбцуЕ - все также, как и в п.1, но только для текущей строки. п.2 работает только при включенном флажке. Если нужно множественное изменение на листе, то отключаем флажок, меняем и запускаем "Пересчитать всё" Чуть поменял макрос и файл перевложил
[vba]
Код
Sub tt() Application.ScreenUpdating = 0 'откл автообновление экрана r0_ = 6 'нач. строка r1_ = Range("E" & Rows.Count).End(xlUp).Row - r0_ 'кон строка c1_ = Cells(r0_ - 1, Columns.Count).End(xlToLeft).Column 'кон. столбец For i = 22 To c1_ 'цикл с 22 столбца до конца If Cells(r0_, i).HasFormula Then 'если в нач строке формула Cells(r0_, i).Copy Cells(6, i).Resize(r1_) 'копируем ее вниз Cells(r0_ + 1, i).Resize(r1_).Copy 'копируем вставленное Cells(r0_ + 1, i).Resize(r1_).PasteSpecial (xlPasteValues) 'вставляем в то же место значениями End If 'конец ИФа (вот такой он, да) Next i 'конец цикла Application.CutCopyMode = 0 'снимаем муравьев копирования MsgBox "Всё" 'сообщение, что все сделали End Sub
Private Sub Worksheet_Change(ByVal Target As Range) 'при каждом изменении в ячейках If CheckBox1.Value = False Then Exit Sub 'если галка не стоит - выход If Target.Count > 1 Then Exit Sub 'если изменено насколько ячеек - выход If Target.Column < 6 Then Exit Sub 'если столбец Б6 - выход If Target.Column > 20 Then Exit Sub 'если столбец >20 - выход Application.EnableEvents = 0 'откл. реакцию на события Application.ScreenUpdating = 0 'откл автообновление екрана r0_ = 6 'нач. строка If Target.Row < r0_ - 1 Then 'если изменения выше r0 (с 1 по 4 строки) tt 'запускаем макрос tt Else 'иначе r1_ = Range("E" & Rows.Count).End(xlUp).Row - r0_ 'определяем номер нижн заполн строки по столбцу Е If Target.Row < r1_ Then 'если изменения выше r1 If Target.Row <> r0_ Then 'если изменения не в строке r0 rt_ = Target.Row 'определяем строку, в которой изменения c1_ = Cells(r0_ - 1, Columns.Count).End(xlToLeft).Column 'кон. столбец For i = 22 To c1_ 'цикл с 22 столбца до конца If Cells(r0_, i).HasFormula Then 'если в нач строке формула Cells(r0_, i).Copy Cells(rt_, i) 'копируем ее в ячейку строки, в которой изменения Cells(rt_, i) = Cells(rt_, i).Value 'преобразуем в значение End If ' Next i ' End If ' End If ' End If ' Application.EnableEvents = 1 'вкл. реакцию на события End Sub Sub qq() Application.EnableEvents = 1 'вкл. реакцию на события End Sub
по идее на больших объемах моя должна быстрее считать
Скорее всего да. Я потому и написал, что совсем по-простому сделал. Просто потому, что здесь другой подход нужен. Например, такой - формулы только в первой строке таблицы (в строке 6), остальное - значениями. И есть 2 макроса - 1. копирует формулы из строки 6, размножает их вниз и преобразует все это в значения, 2. при изменении чего-либо в столбцах F:T - 2.1. если изменение в строках 1:4 - см. п.1 2.2. если изменение в строках 6:ПоследняяЗаполненнаяПоСтолбцуЕ - все также, как и в п.1, но только для текущей строки. п.2 работает только при включенном флажке. Если нужно множественное изменение на листе, то отключаем флажок, меняем и запускаем "Пересчитать всё" Чуть поменял макрос и файл перевложил
[vba]
Код
Sub tt() Application.ScreenUpdating = 0 'откл автообновление экрана r0_ = 6 'нач. строка r1_ = Range("E" & Rows.Count).End(xlUp).Row - r0_ 'кон строка c1_ = Cells(r0_ - 1, Columns.Count).End(xlToLeft).Column 'кон. столбец For i = 22 To c1_ 'цикл с 22 столбца до конца If Cells(r0_, i).HasFormula Then 'если в нач строке формула Cells(r0_, i).Copy Cells(6, i).Resize(r1_) 'копируем ее вниз Cells(r0_ + 1, i).Resize(r1_).Copy 'копируем вставленное Cells(r0_ + 1, i).Resize(r1_).PasteSpecial (xlPasteValues) 'вставляем в то же место значениями End If 'конец ИФа (вот такой он, да) Next i 'конец цикла Application.CutCopyMode = 0 'снимаем муравьев копирования MsgBox "Всё" 'сообщение, что все сделали End Sub
Private Sub Worksheet_Change(ByVal Target As Range) 'при каждом изменении в ячейках If CheckBox1.Value = False Then Exit Sub 'если галка не стоит - выход If Target.Count > 1 Then Exit Sub 'если изменено насколько ячеек - выход If Target.Column < 6 Then Exit Sub 'если столбец Б6 - выход If Target.Column > 20 Then Exit Sub 'если столбец >20 - выход Application.EnableEvents = 0 'откл. реакцию на события Application.ScreenUpdating = 0 'откл автообновление екрана r0_ = 6 'нач. строка If Target.Row < r0_ - 1 Then 'если изменения выше r0 (с 1 по 4 строки) tt 'запускаем макрос tt Else 'иначе r1_ = Range("E" & Rows.Count).End(xlUp).Row - r0_ 'определяем номер нижн заполн строки по столбцу Е If Target.Row < r1_ Then 'если изменения выше r1 If Target.Row <> r0_ Then 'если изменения не в строке r0 rt_ = Target.Row 'определяем строку, в которой изменения c1_ = Cells(r0_ - 1, Columns.Count).End(xlToLeft).Column 'кон. столбец For i = 22 To c1_ 'цикл с 22 столбца до конца If Cells(r0_, i).HasFormula Then 'если в нач строке формула Cells(r0_, i).Copy Cells(rt_, i) 'копируем ее в ячейку строки, в которой изменения Cells(rt_, i) = Cells(rt_, i).Value 'преобразуем в значение End If ' Next i ' End If ' End If ' End If ' Application.EnableEvents = 1 'вкл. реакцию на события End Sub Sub qq() Application.EnableEvents = 1 'вкл. реакцию на события End Sub
Например, такой - формулы только в первой строке таблицы (в строке №6), остальное - значениями
Я так и делаю обычно, в своих файлах - у меня даже макрос копирования верхней строки в надстройке есть - постоянно им пользуюсь
Просто, если это файл не Игоря а руководству - то им же нужно еще объяснить как им пользоваться... а это, иногда, самое сложное И еще действия макроса отменить нельзя - вот клацнут они что-то и все
Пытался сделать функцию быстрее чем СУММПРОИЗВ... что то так и не понял быстрее она или нет - протянул таблицу до конца листа - просчитало и так и так меньше чем за секунду...
Например, такой - формулы только в первой строке таблицы (в строке №6), остальное - значениями
Я так и делаю обычно, в своих файлах - у меня даже макрос копирования верхней строки в надстройке есть - постоянно им пользуюсь
Просто, если это файл не Игоря а руководству - то им же нужно еще объяснить как им пользоваться... а это, иногда, самое сложное И еще действия макроса отменить нельзя - вот клацнут они что-то и все
Пытался сделать функцию быстрее чем СУММПРОИЗВ... что то так и не понял быстрее она или нет - протянул таблицу до конца листа - просчитало и так и так меньше чем за секунду...SLAVICK
обе ЮДФ отработали на ура. на глаз разности в скорости незаметно. но..... на размер файла это они никак не повлияли. Разница 100 кб на 7 метрах это ничто.
руководству я ручки затуплю напильником посредством установки защиты (с этим чуть позже). По их задумке должна быть единая диспетчерская таблица по всем изделиям, включаю покупные. наш доблестный кавторанг думает, что таблица из 20000 строк будет нагляднее , чем 10 таблиц по 2000 строк
обе ЮДФ отработали на ура. на глаз разности в скорости незаметно. но..... на размер файла это они никак не повлияли. Разница 100 кб на 7 метрах это ничто.
руководству я ручки затуплю напильником посредством установки защиты (с этим чуть позже). По их задумке должна быть единая диспетчерская таблица по всем изделиям, включаю покупные. наш доблестный кавторанг думает, что таблица из 20000 строк будет нагляднее , чем 10 таблиц по 2000 строк китин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
что таблица из 20000 строк будет нагляднее , чем 10 таблиц по 2000 строк
Ну тут на любителя - мне тоже проще пользоваться одной таблицей, чем кучей маленьких... В них же не видно связи с данными - может мне просто фантазии не хватает [/offtop]
что таблица из 20000 строк будет нагляднее , чем 10 таблиц по 2000 строк
Ну тут на любителя - мне тоже проще пользоваться одной таблицей, чем кучей маленьких... В них же не видно связи с данными - может мне просто фантазии не хватает [/offtop]SLAVICK
мне тоже проще пользоваться одной таблицей, чем кучей маленьких
может быть. но это в случае, когда куча деталей унифицированы. а когда на каждый огромный заказ ( а у меня их порядка 15) идет свой набор деталей , наверно лучше делить детали по этим заказам. Но у них головы большие и умные.
мне тоже проще пользоваться одной таблицей, чем кучей маленьких
может быть. но это в случае, когда куча деталей унифицированы. а когда на каждый огромный заказ ( а у меня их порядка 15) идет свой набор деталей , наверно лучше делить детали по этим заказам. Но у них головы большие и умные.китин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
Сообщение отредактировал китин - Вторник, 19.04.2016, 12:19
ААААА а откуда туда флажок поставить , я ведь не знаю и какой макрос на него повесить???? [offtop]плохо быть тупым незнайкой :dont: это ведь вкладка разработчик вставить элемент управления формы флажок??? а макрос то какой? у Саши в файле на ПКМ флажок не реагирует, то есть не посмотреть назначит макрос
ААААА а откуда туда флажок поставить , я ведь не знаю и какой макрос на него повесить???? [offtop]плохо быть тупым незнайкой :dont: это ведь вкладка разработчик вставить элемент управления формы флажок??? а макрос то какой? у Саши в файле на ПКМ флажок не реагирует, то есть не посмотреть назначит макроскитин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
Сообщение отредактировал китин - Вторник, 19.04.2016, 13:58
сунул макрос от _Boroda_, аж в 3 листа ( я правильно в модуль каждого листа пихал макрос и правил его в зависимости от листа?) все работает!!!!книга упала на метр!!!! Уже хорошо!!!! только заметил , что при сортировке таблицы макрос не работает это плохо.
сунул макрос от _Boroda_, аж в 3 листа ( я правильно в модуль каждого листа пихал макрос и правил его в зависимости от листа?) все работает!!!!книга упала на метр!!!! Уже хорошо!!!! только заметил , что при сортировке таблицы макрос не работает это плохо.китин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852