Есть 2 таблицы: • в родительской таблице 10 тыс. строк • в дочерней МИНИМУМ 10 тыс. строк (по факту всегда примерно в 2 раза больше, чем в родительской), т.к. там одна строка родительской может повторяться несколько раз (связь "один-ко-многим") • я тяну из родительской в дочернюю разную информацию (по короткому уникальному ключу) - порядка 5-10 полей (столбцов)
На 5 тысячах всё было довольно шустро, но уже на 10ти комп начал сильно призадумываться
Алгоритм (автопересчёт у меня в таких файлах всегда выключен): 1. разумеется, первое, что я сделал, это в отдельное поле прописал ПОИСКПОЗ по ключу (чтобы вычислить его один раз, а не для каждого поля) и остальные поля просто тяну ИНДЕКСом со ссылкой на поле с ПОИСКПОЗ 2. следующее, что я собираюсь сделать, это заменить функции ПОИСКПОЗ, СУММЕСЛИ(МН) и СЧЁТЕСЛИ(МН) на макросы (НЕ макрофункции, а макросы со вставкой)
Собственно, вот мы и подошли непосредственно к вопросу: если позиция уже вычислена, то "долго ли" ИНДЕКСы будут тянуть столбцы? Иными словами, как сильно будет тупить пересчёт?
Я понимаю, что вопрос сильно нуждается в практике и я её выполню (и отпишусь), но может, кто-то на своём опыте может уже сказать? Лично я пока склоняюсь к тому, что любая замена функций (что обычных, что макро) на вычисления внутри кода с последующей вставкой на лист всегда будет шустрее. И чем больше данных, тем более явный прирост в скорости. Основная проблема при этом только в том, что структура таблиц пока не утряслась и при любых движениях нужно будет править код
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, Алексей, писатель вы наш форумный. Ну ведь написано много а толку - нет. Дайте хоть посмотреть что там за формулы, индексы. 10 - 20 к строк - надо постараться чтоб пошли тормоза.
Jack Famous написал: любая замена функций (что обычных, что макро) на вычисления внутри кода с последующей вставкой на лист всегда будет шустрее
Функции листа работают быстрее, т.к. написаны на языке, который роднее родного VBA Но чем их больше, тем больше считают. И существует невидимая плавающая граница, когда формулы выдыхаются и пора передавать эстафету макросам. А еще при несоблюдении диеты и кормлении книги формулами она полнеет и наступает момент, когда поход к врачу неизбежен..
Цитата
Jack Famous написал: "долго ли" ИНДЕКСы будут тянуть столбцы?
ИНДЕКС - одна из самых быстрых и если уж она начинает тормозить, то проблема или в построении вычислений, или в большом объеме данных.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous: следующее, что я собираюсь сделать, это заменить функции ПОИСКПОЗ, СУММЕСЛИ(МН) и СЧЁТЕСЛИ(МН) на макросы (НЕ макрофункции, а макросы со вставкой)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
vikttur: Каким боком к этой теме упомяутые функции?
никаким. т.к. на основной вопрос вы ответили
Цитата
vikttur: ИНДЕКС - одна из самых быстрых и если уж она начинает тормозить, то проблема или в построении вычислений, или в большом объеме данных
и тогда тему надо назвать, "как организовать более шуструю структуру данных", что мне не очень нравится, т.к. слишком расплывчато)))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
ИНДЕКС сама по себе совершенно безобидная функция, а вот внутри нее, может оказаться что сами индексы считает многоэтажная массивная формула и тормоза будут не из-за работы ИНДЕКС, а из-за вычисления индексов строки и столбца
Jack Famous, что в Excele запрещены макросы? придкмали и реализовали тестовую площадку, проверили и получили ответ полагаю что 1 млн. формул =В99999 и =ИНДЕКС(В:С;99999;1) excel будет вычислять приблизительно одинаковое время
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
Ігор Гончаренко: тормоза будут не из-за работы ИНДЕКС, а из-за вычисления индексов строки и столбца
это не тот случай, т.к.
Цитата
Jack Famous: первое, что я сделал, это в отдельное поле прописал ПОИСКПОЗ по ключу (чтобы вычислить его один раз, а не для каждого поля) и остальные поля просто тяну ИНДЕКСом со ссылкой на поле с ПОИСКПОЗ
я в принципе понял, что, если есть номер позиции, то ИНДЕКС сработает очень быстро. По тестам пока даже получается, что быстрее подтянуть цену из родительской таблицы, чем вычислить её по 2ум полям в дочерней (если номер позиции всё-равно вычисляется)
P.S.: у меня каждый индекс тянет из своего поля, так что вместо =ИНДЕКС(В:С;99999;1) было бы =ИНДЕКС(В:B;99999), что ещё быстрее должно быть
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, то что у нас разные взгляды и на оформление и на … опущу. Но если это рабочая таблица, то начинать надо с исключения лишних операций. 12000 раз выполняется формула в контрактация!АГ =MATCH([@[КЛЮЧ ПОДРЯДЧИКА]];cntr[КЛЮЧ ПОДРЯДЧИКА];0) ну хорошо что там значение самое первое, но при одинаковых значениях запишите подлиннее, но =IF([@[КЛЮЧ ПОДРЯДЧИКА]]=C1;AG1;MATCH([@[КЛЮЧ ПОДРЯДЧИКА]];cntr[КЛЮЧ ПОДРЯДЧИКА];))
Ну а в целом - какими бы небыли быстрыми функции, многочисленный пересчет будет влиять, если наложить условное форматирование, то ……
не понял. У меня ищет позицию каждого подрядчика в таблице подрядчиков по ключу. На что вы хотите это заменить (какую проверку добавить для отсечения медленной ветки) я не понял. Тем более, что я вообще планирую везде заменить ПОИСКПОЗ на макрос. Другое дело, что я нашёл 3 лишние СУММЕСЛИ в "контрактации". Я тянул ими суммы по ключу из выполнения, хотя можно их заменить на вычисление КОЛ-ВО*ЦЕНА
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
всем большое спасибо! По результатам замены функций макросами отпишусь
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
БМВ: 12000 раз выполняется формула в контрактация! АГ =MATCH([@[КЛЮЧ ПОДРЯДЧИКА]];cntr[КЛЮЧ ПОДРЯДЧИКА];0) ну хорошо что там значение самое первое, но при одинаковых значениях запишите подлиннее, но =IF([@[КЛЮЧ ПОДРЯДЧИКА]]=C1;AG1;MATCH([@[КЛЮЧ ПОДРЯДЧИКА]];cntr[КЛЮЧ ПОДРЯДЧИКА])
в C1 вообще шапка, ладно, типа ссылаемся на поле ключей, но что с чем проверяем? Если ниже-выше тот же ключ, то взять ту же позицию? В этом был смысл? Если да, то, в принципе можно было выиграть конечно, но вычислять ПОИСКПОЗ макросом всё-равно круче
Цитата
БМВ: Кроме этого у меня вопрос к функции FILE_ПроверитьНаПустоты, и думаю еще много возникнет, если копнуть
хочу продолжить Предлагаю название темы: Связанные умные таблицы с большим объёмом данных. Как ускорить пересчёт?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
В качестве итога, понятно что при глобальных изменениях, таких как сортировка 12к строк, пересчет занимает время. но тормозом оказалась та самая UDF, упомянутая выше , и которая конечно сократила запись, но доставляла массу работы процессору. Алексей, а не сам ли виновен в этом ?
БМВ, не со всем согласен - UDF действительно тормозит (спасибо за то, что нашли), но изменённая логика СУММЕСЛИ дала пока бОльший прирост (ещё в процессе). Штатные функции пока не менял… Отпишусь позже
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Друзья, а кто знает, насколько сильно грузят файл именованные диапазоны? Хочу к ним в коде привязываться… У меня их порядка 50ти и большинство размером 1 столбец * ~20 тыс. строк
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Про именованные диапазоны могу сказать только такое, если к 90 тыс уникальных строк ВПРю примерно такой же массив, то когда эта формула считается внутри смарт-таблицы, то расчет может и зависнуть, а если ту же самую формулу протянуть рядом вне таблицы, то считает без проблем за полминуты. Но вообще всякие фильтры и формулы в смарт-таблицах работают медленнее чем в обычных диапазонах. Если же вы их будете считать в массивах, а имена использовать только для удобства, то не думаю, что это будет сильно медленнее чем просто указание абсолютных/относительных адресов. офф: зато очень быстро с именованными диапазонами работает PQ. А чего еще от меня ждать?
PooHkrd: Но вообще всякие фильтры и формулы в смарт-таблицах работают медленнее чем в обычных диапазонах
это печальный факт - очень надеюсь ,что будущих версиях это поправят. Действительно почти любые действия типа добавления/удаления столбцов/строк или же расчёты происходят гораздо дольше и с ростом объёмов это становится сильно заметно. Очень удручает вставка в такую таблицу новых данных снизу - это кошмар, но можно предварительно "растянуть" таблицу с запасом (я, например, макросом растягиваю на 10 тыс за 1 сек) и тогда вставится намного быстрее, после чего удалить лишние пустые строки
А вообще я именно просто про их наличие спрашивал - не грузит и здорово. Очень удобно на них ссылаться, особенно когда структура таблиц "плавает" и столбцы постоянно "переезжают" и переименовываются
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, вопрос имеет несколько вариантов ответа. Если именованный диапазон - просто синоним обычного - то не грузит, а если в нем формула с расчетом, то понятно, что всё зависит от формулы, при этом сколько раз употребил этот диапазон в формуле своей, столько раз он и будет считаться. то есть это просто краткая запись. А вот на счет умных таблиц - тут удивляться нечему, дополнительно нужно рассчитать диапазон исходя из имени …..
ну у меня именованные диапазоны могут состоять как из значений, так и из формул. В формулах я ссылаюсь на столбцы умной таблицы - НЕ на имена. Имена нужны только для более-менее стабильной привязки в макросе
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄