Страницы: 1
RSS
Что быстрее именованные диапазоны или умные таблицы
 
Добрый день!
В месте где я работаю на моей должности используется большой экселевский файл, в который выгружается информация из БД порядка ~60 столбцов, плюс правее еще столько же столбцов, в которых записано куча формул с ВПР, СУММЕСЛИМН и подобными.
Количество строк в файле при этом может достигать и значений в 50000, т.е. итого порядка 6 млн. рабочих ячеек
Сейчас занимаюсь оптимизацией всего этого, т.к. местами работать стало невозможно.
При работе есть необходимость использовать именнованные диапазоны на столбцы для читабельности формул.

И собственно вопрос: что потенциально с таким количеством данных будет работать быстрее умная таблица или именованные диапазоны? Если именованные диапазоны, то какие: динамические или целый столбец = именованный диапазон?

Заранее спасибо за ваши ответы)
Изменено: BapuK - 22.07.2020 08:34:11
 
Цитата
BapuK написал:
что потенциально с таким количеством данных будет работать быстрее
VBA и Power Query быстрее. На таких объемах лучше без формул. И шансов ошибиться при копированиях/протягиваниях значительно меньше.
Вот горшок пустой, он предмет простой...
 
умные таблицы и именованные диапазоны при обращении к ним в формулах никаких явных преимуществ друг перед другом не имеют
ничего оптимизировать не получится пока не сократите количество формул
когда разделите все на просто данные (без всяких формул, условных форматов и пр.) и отчеты из этих данных, когда формул останется не более нескольких тысяч - все начнет летать
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
PooHkrd написал:
VBA и Power Query быстрее. На таких объемах лучше без формул. И шансов ошибиться при копированиях/протягиваниях значительно меньше.
А лучше Python и MySQL и без экселя)
VBA будет использоваться для выгрузки данных, а все для чего мог бы понадобиться PQ планирую реализовать в БД из которой будут данные выгружаться

Цитата
Ігор Гончаренко написал:
ничего оптимизировать не получится пока не сократите количество формул
Количество формул и планируется сократить, т.к. будем переделывать БД с нуля, просто я замечал иногда, при работе с умными таблицами бывают зависоны, когда удаляешь строки, что-то дополнительно ВПРишь и т.п. хотя количество строк в таблице не превышало 10к, а формулы были написаны в 1-5 столбцах. Хотя недавно посмотрел видео,  где демонстрируется, что работа с умными таблицами сокращает время работы ВПРов и тому подобных формул.
 
BapuK, сократить время работы ВПРов можно только за счет того что в качестве диапазонов будут указываться не столбцы листа целиком, а столбцы таблицы, в которых значительно меньше ячеек. Ну и удобно, т.к. диапазон динамический. При этом сама формула ВПР желательно чтобы использовалась вне этой таблицы. Если формула будет в самой таблице, то тормозов будет сильно больше, на что бы она ни ссылалась.
Вот горшок пустой, он предмет простой...
 
Цитата
BapuK написал:
60 столбцов, плюс правее еще столько же столбцов, в которых записано куча формул с ВПР, СУММЕСЛИМН и подобными
Если в каждом столбце протянуто по одной формуле, то вашу вай-ку можно скопировать, оставить только десять строк и прикрепить файл к теме.
 
BapuK, умные таблицы на объёмах более 10 тыс строк лучше не использовать, т.к. сортировка, фильтрация, вставка и удаление столбцов и строк будет жестоко тупить
Цитата
BapuK: А лучше Python и MySQL и без экселя)
а зачем тогда было сюда писать? К тому же вряд ли вы проводили сравнение скорости выполнения, не так ли?…
Связанные умные таблицы с большим объёмом данных. Как ускорить пересчёт?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
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;)
Изменено: БМВ - 22.07.2020 10:41:08
По вопросам из тем форума, личку не читаю.
 
БМВ, проверяйте. Особенно клинит, если формулы вставлять в столбцы таблицы автозаполнением. Ибо с другой стороны если не им, то зачем оно вообще в "умных" таблицах нужно? Как бы для этого инструмент и делался, чтобы само все протягивалось при добавлении новых строк.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: сократить время работы ВПРов можно только за счет того что в качестве диапазонов будут указываться не столбцы листа целиком, а столбцы таблицы, в которых значительно меньше ячеек.
Если есть уверенность, что #Н/Д не будет. то при точном поиске без разницы, как указан диапазон. Сократить время вычислений ВПР (ПОИСКПОЗ...)  можно ну очень существенно, если искать в сортированном диапазоне с указанием неточного поиска. И вот в таком варианте указывать лучше ограниченный диапазон, будет меньше итераций.

Но это все не по теме. Автор просит сравнение двух инструментов. А если будет желание послушать умных и не очень по поводу оптимизации, то нужна другая тема

По вопросу. Скорость работы инструментов напрямую зависит от задачи, от данных на листе. размещения, форматирования и прочих вещей, влияющих на скорость. И вопрос, поставленнй в заголовке темы - это как о курице и яйце...
 
Цитата
БМВ: проверил
привет! Не противоречит Павлову?
В новых версиях это уже важно
Изменено: Jack Famous - 22.07.2020 10:52:54
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
PooHkrd написал:
Особенно клинит, если формулы вставлять в столбцы таблицы автозаполнением.
проверил, результаты выше. Но нужно делить на пересчет и заполнение таблицы. Да последнее много дольше.
По вопросам из тем форума, личку не читаю.
 
вопрос вполне конкретный:
Цитата
BapuK написал:
что потенциально с таким количеством данных будет работать быстрее умная таблица или именованные диапазоны?
мой ответ: без разницы (возможно разница и есть, но вы ее не уловите, особенно  если именованный диапазон ссылается на те же данные, что и умная таблица)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, спасибо. Тогда второй вопрос по именованным диапазонам:
использовать столбец целиком или же делать их динамическими?
Мое предположение, что особой разницы не будет из-за того,  что ресурсы на обработку статичного диапазона в целый столбец будут соизмеримы с ресурсами на поиск границ того самого динамического диапазона.
Или я неправ?

Речь не идет про динамические диапазоны в последних версиях 365 офиса, т.к. установлен офис 2019, а там этой фишки нет и приходится руками в именованных диапазонах извращаться
Изменено: BapuK - 23.07.2020 06:46:39
 
Цитата
BapuK написал:
соизмеримы с ресурсами на поиск границ того самого динамического диапазона.Или я неправ?
Боюсь что не правы. Искать границы диапазона формуле не нужно, эти данные все время хранятся в оперативке и загружаются в неё сразу при открытии файла. При изменении размера умной таблицы либо именованного диапазона, эти размеры сразу записываются в память и формулы могут их использовать. А вот функция если заточена на более быструю работу когда ссылается на конкретный диапазон вместо столбца целиком это очень даже почувствует.
Вот горшок пустой, он предмет простой...
 
Цитата
ПооХкрд написал:
Искать границы диапазона формуле не нужно, эти данные все время хранятся в оперативке и загружаются в неё сразу при открытии файла
в именованном диапазоне, чтобы сделать его динамическим, нужно писать формулу, что-то типа
Код
=$A$2:ИНДЕКС($A$2:$A$100000; ПОИСКПОЗ(ПОВТОР("я";255);A2:A100000))

Вот и вопрос разве на связку индекс+поискпоз не будет постоянно тратиться ресурс?

Именованных диапазонов будет много, почти все столбцы

 
Цитата
BapuK написал:
будут соизмеримы с ресурсами на поиск границ того самого динамического диапазона
в дополнение к №15. Ну если не делать глупостей и не впихивать поиск этого диапазона в каждую формулу или даже использовать для этого формулу в именованном диапазоне, то сделать это один раз в отдельной сервисной ячейке и это использовать много раз, то разница будет заметна, но может оказаться что тормозом будет не то какой диапазон формула обсчитывает, а сколько раз эта формула вызывается на листе. Например некоторые вычисления нужно блокировать еще на подходе. типа  связку =IFERROR(VLOOKUP(B2;....;...;0));"") , если в B2 "" как результат предыдущего вычисления. Лучше заменить на  =IF(B2="";"";IFERROR(VLOOKUP(B2;....;...;0));"")) .
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх