Добрый день! В месте где я работаю на моей должности используется большой экселевский файл, в который выгружается информация из БД порядка ~60 столбцов, плюс правее еще столько же столбцов, в которых записано куча формул с ВПР, СУММЕСЛИМН и подобными. Количество строк в файле при этом может достигать и значений в 50000, т.е. итого порядка 6 млн. рабочих ячеек Сейчас занимаюсь оптимизацией всего этого, т.к. местами работать стало невозможно. При работе есть необходимость использовать именнованные диапазоны на столбцы для читабельности формул.
И собственно вопрос: что потенциально с таким количеством данных будет работать быстрее умная таблица или именованные диапазоны? Если именованные диапазоны, то какие: динамические или целый столбец = именованный диапазон?
умные таблицы и именованные диапазоны при обращении к ним в формулах никаких явных преимуществ друг перед другом не имеют ничего оптимизировать не получится пока не сократите количество формул когда разделите все на просто данные (без всяких формул, условных форматов и пр.) и отчеты из этих данных, когда формул останется не более нескольких тысяч - все начнет летать
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
PooHkrd написал: VBA и Power Query быстрее. На таких объемах лучше без формул. И шансов ошибиться при копированиях/протягиваниях значительно меньше.
А лучше Python и MySQL и без экселя) VBA будет использоваться для выгрузки данных, а все для чего мог бы понадобиться PQ планирую реализовать в БД из которой будут данные выгружаться
Цитата
Ігор Гончаренко написал: ничего оптимизировать не получится пока не сократите количество формул
Количество формул и планируется сократить, т.к. будем переделывать БД с нуля, просто я замечал иногда, при работе с умными таблицами бывают зависоны, когда удаляешь строки, что-то дополнительно ВПРишь и т.п. хотя количество строк в таблице не превышало 10к, а формулы были написаны в 1-5 столбцах. Хотя недавно посмотрел видео, где демонстрируется, что работа с умными таблицами сокращает время работы ВПРов и тому подобных формул.
BapuK, сократить время работы ВПРов можно только за счет того что в качестве диапазонов будут указываться не столбцы листа целиком, а столбцы таблицы, в которых значительно меньше ячеек. Ну и удобно, т.к. диапазон динамический. При этом сама формула ВПР желательно чтобы использовалась вне этой таблицы. Если формула будет в самой таблице, то тормозов будет сильно больше, на что бы она ни ссылалась.
BapuK, умные таблицы на объёмах более 10 тыс строк лучше не использовать, т.к. сортировка, фильтрация, вставка и удаление столбцов и строк будет жестоко тупить
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
PooHkrd написал: сократить время работы ВПРов можно только за счет того что в качестве диапазонов будут указываться не столбцы листа целиком, а столбцы таблицы, в которых значительно меньше ячеек.
ну это только для тех функций, которые не ограничивают диапазон тем что реально заполнен.
Цитата
PooHkrd написал: Если формула будет в самой таблице, то тормозов будет сильно больше, на что бы она ни ссылалась.
Уже хочу проверить. проверил, разница не существенна разброс в пределах одного процента. 200000 данных обратной сортировки 10000 формул VLOOKUP. =VLOOKUP([@A];Table2[B];1;) =VLOOKUP(Table3[@A];Table2[B];1;) =VLOOKUP(A2;Table2[B];1;) =VLOOKUP(A2;$I$2:$I$200000;1;) =VLOOKUP(A2;$I:$I;1;)
БМВ, проверяйте. Особенно клинит, если формулы вставлять в столбцы таблицы автозаполнением. Ибо с другой стороны если не им, то зачем оно вообще в "умных" таблицах нужно? Как бы для этого инструмент и делался, чтобы само все протягивалось при добавлении новых строк.
PooHkrd написал: сократить время работы ВПРов можно только за счет того что в качестве диапазонов будут указываться не столбцы листа целиком, а столбцы таблицы, в которых значительно меньше ячеек.
Если есть уверенность, что #Н/Д не будет. то при точном поиске без разницы, как указан диапазон. Сократить время вычислений ВПР (ПОИСКПОЗ...) можно ну очень существенно, если искать в сортированном диапазоне с указанием неточного поиска. И вот в таком варианте указывать лучше ограниченный диапазон, будет меньше итераций.
Но это все не по теме. Автор просит сравнение двух инструментов. А если будет желание послушать умных и не очень по поводу оптимизации, то нужна другая тема
По вопросу. Скорость работы инструментов напрямую зависит от задачи, от данных на листе. размещения, форматирования и прочих вещей, влияющих на скорость. И вопрос, поставленнй в заголовке темы - это как о курице и яйце...
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
BapuK написал: что потенциально с таким количеством данных будет работать быстрее умная таблица или именованные диапазоны?
мой ответ: без разницы (возможно разница и есть, но вы ее не уловите, особенно если именованный диапазон ссылается на те же данные, что и умная таблица)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
Ігор Гончаренко, спасибо. Тогда второй вопрос по именованным диапазонам: использовать столбец целиком или же делать их динамическими? Мое предположение, что особой разницы не будет из-за того, что ресурсы на обработку статичного диапазона в целый столбец будут соизмеримы с ресурсами на поиск границ того самого динамического диапазона. Или я неправ?
Речь не идет про динамические диапазоны в последних версиях 365 офиса, т.к. установлен офис 2019, а там этой фишки нет и приходится руками в именованных диапазонах извращаться
BapuK написал: соизмеримы с ресурсами на поиск границ того самого динамического диапазона.Или я неправ?
Боюсь что не правы. Искать границы диапазона формуле не нужно, эти данные все время хранятся в оперативке и загружаются в неё сразу при открытии файла. При изменении размера умной таблицы либо именованного диапазона, эти размеры сразу записываются в память и формулы могут их использовать. А вот функция если заточена на более быструю работу когда ссылается на конкретный диапазон вместо столбца целиком это очень даже почувствует.
BapuK написал: будут соизмеримы с ресурсами на поиск границ того самого динамического диапазона
в дополнение к №15. Ну если не делать глупостей и не впихивать поиск этого диапазона в каждую формулу или даже использовать для этого формулу в именованном диапазоне, то сделать это один раз в отдельной сервисной ячейке и это использовать много раз, то разница будет заметна, но может оказаться что тормозом будет не то какой диапазон формула обсчитывает, а сколько раз эта формула вызывается на листе. Например некоторые вычисления нужно блокировать еще на подходе. типа связку =IFERROR(VLOOKUP(B2;....;...;0));"") , если в B2 "" как результат предыдущего вычисления. Лучше заменить на =IF(B2="";"";IFERROR(VLOOKUP(B2;....;...;0));"")) .