Excel 2010/2013 вставляет строки очень медленно

Я обнаружил, что вставка строк в структуры таблиц или в обычные ячейки - вручную или иным образом - очень и очень медленная. Например, вставка 7 строк в таблицу (содержащую только буквальные строки) или в соседние ячейки на листе без условного форматирования занимает более 10 минут.

Рабочая книга состоит из 45 рабочих листов и 20 таблиц, причем большие таблицы содержат XML-файлы размером около 10 КБ. XML-файлы электронных таблиц имеют размер 33 МБ, большинство из них имеют размер около 300 КБ, 5 из них имеют размер более 1 МБ, а один - 15 МБ. Он довольно сложный, но не массивный. Все вычисления проходят слева направо вверх вниз, с правого листа на левый лист, и мне в основном удалось избежать формул массива. Все таблицы имеют обычную структуру, а вычисляемые столбцы имеют только одну формулу. Большинство столбцов таблицы являются вычисляемыми, и только несколько столбцов меньшего размера содержат буквальные данные.

У меня есть много условного форматирования на паре листов, но я очень тщательно следил за тем, чтобы оно было рациональным и не допускало его фрагментации: у меня есть около 45 правил для всего листа, и они обобщены для охвата всех столбцов. Основная обработка для принятия решений о форматировании перенесена в таблицы в качестве вспомогательных столбцов и, как я уже сказал, имеет очень регулярную структуру.

Кажется, что этот тип редактирования не является потокобезопасным, поэтому загружается только один процессор, и дисковая активность очень мала. Я не могу понять, что excel делает все это время. Конечно, я установил расчет вручную...

Я видел комментарии, связывающие этот тип вещей с увеличенными ограничениями строк и столбцов, но я не понимаю, почему это должно быть фактором. Если я посмотрю на XML-файлы электронных таблиц, там будет код только для строк и столбцов, занятых значениями или формулами. Так почему же незанятые клетки в игре?

Это оказывает огромное влияние на мою продуктивность, хотя я многому учусь, читая подобные сайты в свободное время. Мне действительно нужно выяснить, в чем проблема, чтобы я мог избежать или обойти эту проблему, если это возможно.

Может ли кто-нибудь помочь мне в этом?

На всякий случай, если люди задаются этим вопросом, ответ — использовать power query и power view в excel. Я считаю, что средние (500 тыс. строк) наборы данных, сложные структуры и преобразования работают без сучка и задоринки. Я больше никогда не использую формулы в таблицах. Другое дело, что это естественным образом приводит вас к мощному би, что здорово. Это мой совет.


excel insertion
person Cool Blue    schedule 23.10.2013    source источник
comment
Вставки вызывают неявную перезапись всех формул в сдвинутых ячейках и любых формул, опирающихся на какие-либо данные в смещенных ячейках - я полагаю. Я предполагаю, что цепочка вычислений используется для определения того, какие ячейки могут потребоваться переписать при вставке/удалении, но это может быть просто прямое сканирование всей книги.   -  person Orbling    schedule 23.10.2013
comment
Влияет ли вставка только в конец таблицы (с сортировкой при необходимости) на производительность?   -  person Orbling    schedule 23.10.2013
comment
но зачем сдвигать пустое пространство? И что делать со ссылками на таблицы, которые являются обычными структурами? Если вы измените содержимое таблицы или даже количество строк, это не повлияет ни на какие ссылки через обычную структуру. =tablename[@[имя столбца]], например. эта ссылка не зависит от содержимого таблицы или даже от количества строк. Так и есть: =Index(tablename[имя столбца], то же самое для этого: match(ref1,tablename[имя столбца 2],0))... что нужно сделать? я не понимаю...   -  person Cool Blue    schedule 23.10.2013
comment
Нет, сэр, изменение размера таблицы для добавления строк в конце так же мучительно... похоже, это не имеет значения.   -  person Cool Blue    schedule 23.10.2013
comment
Да, ничего из этого не нужно переписывать, но знает ли об этом Excel, не глядя каждый раз? Вряд ли удастся кэшировать цепочку зависимостей для такой большой книги. Однако итоговые значения таблицы и внутренние определения таблицы необходимо изменить. Вы вставляете по частям или просто вставляете один блок?   -  person Orbling    schedule 23.10.2013
comment
Всего один блок, например 7 строк в таблице с 3 столбцами. Это занимает около 30 минут...   -  person Cool Blue    schedule 23.10.2013
comment
Но электронная таблица определяется кодом в XML, верно? и это то, что движет и информирует модель принятия решений. Таким образом, модель принятия решений просматривает XML-файлы и корректирует вещи: это предел ее вселенной. При чем здесь белое пространство, сэр? Например, он имеет такой же статус, как и все другие электронные таблицы в мире. Потому что на него нет ссылок в структуре XML. Так зачем же нужно идти и проверять их?   -  person Cool Blue    schedule 23.10.2013
comment
Возможно, стоит взглянуть на дерево зависимостей, которое исходит из этой таблицы. Звучит чертовски много времени для такой операции. Мне было бы интересно посмотреть, как он обращается к файлам в это время, посмотреть, что он делает. Возможно, используйте монитор процессов.   -  person Orbling    schedule 23.10.2013
comment
Он определяется только XML для хранения, я сомневаюсь, что он использует его внутри в этой форме. Но с таким большим набором данных, возможно, придется поменять местами - хотя, как вы говорите, он не гигантский. Должно быть какое-то несоответствие.   -  person Orbling    schedule 23.10.2013
comment
На самом деле активность ввода-вывода почти нулевая, что видно по световому индикатору и подтверждается графиком в мониторе процессов. Он полностью интенсивно использует процессор и, как я уже говорил выше, ограничен одним процессором.   -  person Cool Blue    schedule 23.10.2013
comment
но если его можно однозначно определить с помощью XML, зачем создавать в памяти плоскую, тупую модель, включающую в себя все, что не имеет отношения к делу? Как и в XML, если есть ссылка на новую ячейку, ее можно добавить в модель... это такой же объект, как и любой другой. Я не могу себе представить, почему они сделали это, сэр...   -  person Cool Blue    schedule 23.10.2013
comment
Вы смотрели, почему используется только одно ядро? Используете ли вы вообще не потокобезопасные функции, какой-либо пользовательский XLL, который не помечен как потокобезопасный? (См.: msdn.microsoft.com/en-us/library/office. /bb687899.aspx) Вы пытались выполнить вставку из функции VBA с активным Application.ScreenUpdating = False?   -  person Orbling    schedule 23.10.2013
comment
Да, он будет определен в памяти с помощью формы внутреннего объекта, какой-то формы разреженной таблицы, хотя это не XML.   -  person Orbling    schedule 23.10.2013
comment
Он имеет такое же поведение в моей тестовой системе, как Excel 2010, и обычное поведение для любой большой электронной таблицы, которая у меня есть, включая табличные структуры. Я также заметил, что некоторые таблицы, в которые я не могу копировать и вставлять в ячейки. Например, я использую контроль V, и ничего не происходит.   -  person Cool Blue    schedule 23.10.2013
comment
Я попробую из VBA, как вы предлагаете, и посмотрю, что произойдет... Хотя VBA не является потокобезопасным. Я проверю, но я где-то читал, что такие правки не являются потокобезопасными...   -  person Cool Blue    schedule 23.10.2013
comment
И да, сэр, это моя точка зрения, это будет разреженная таблица включенных объектов. Вот почему я хотел бросить вызов любому формирующемуся общепринятому мнению о влиянии дополнительного пространства. Мне кажется ложным.   -  person Cool Blue    schedule 23.10.2013
comment
Я также попробую с отключенным условным форматированием на листах, которые зависят от того, над которым я работаю. Это может быть проблемой, потому что у меня много ячеек (десятки тысяч) с условным форматированием. Но CF для всего листа (около 80 тыс. ячеек) управляется только 45 правилами, и все решения обрабатываются вспомогательными столбцами и передаются с именованными диапазонами.   -  person Cool Blue    schedule 23.10.2013
comment
Обновление: я удалил ВСЕ свои красивые CF, и это не имело никакого значения. Даже однопоточность сохранилась. У меня нет UDF на затронутом листе и всего около 16 ячеек во всей книге, которые используют UDF, и они не изменчивы и не часто рассчитываются. Как указывалось ранее, расчеты у меня все равно отключены, поэтому речь идет не о выполнении модели, а о ее реструктуризации.   -  person Cool Blue    schedule 23.10.2013
comment
Ради интереса, сколько времени занимает полный пересчет?   -  person Orbling    schedule 23.10.2013
comment
Спасибо за интерес! Основной лист сборки данных — это то, на что уходит все время пересчета, и это занимает 6,5 секунды с грязного, но нормальный пересчет составляет около 1,5 секунды. У меня есть панель инструментов, где я вручную контролирую расчеты, и это примерно полсекунды. Как я уже сказал, я очень тщательно следил за тем, чтобы структура оставалась регулярной (или, по крайней мере, я много раз перебирал ее и доводил до такой степени: хотел бы я сказать, что у меня все получилось с первого раза, но: не так уж и много). :К).   -  person Cool Blue    schedule 23.10.2013
comment
Ну что ж, тем более загадочно с задержкой реструктуризации. На много порядков интенсивнее полного пересчета. Из опыта я знаю, что изменение определений таблиц происходит медленно, и мои мысли о том, почему, такие же, как и выше. Но это никак не может объяснить задержки, с которыми вы сталкиваетесь. Интересно, есть ли интерфейс отладки...   -  person Orbling    schedule 23.10.2013

Ответы (3)


arrow_upward
4
arrow_downward

Длительное время вставки может быть связано с тем, что ИНДЕКС (или другие функции) ссылаются на весь столбец или на всю строку.

У меня была очень похожая проблема: не слишком сложный рабочий лист (около 2500 строк, с 15 столбцами данных (результаты запроса) и около 10 столбцов формул для извлечения данных из результатов запроса. когда я вставил столбец, первый столбцы могут быть вставлены в течение 4 секунд или около того, но вторая вставка займет больше минуты. Ого! Я искал в Интернете и нашел этот сайт http://support.microsoft.com/kb/2755145.

Мой опыт:

Я использовал такую ​​формулу, как =ИНДЕКС(11:11,1,ПОИСКПОЗ(AC$5,$10:$10,0)), около 25000 раз на моем листе. Вы можете видеть, что каждая формула дважды ссылается на всю строку. По-видимому, когда я добавлял столбец, поскольку это затрагивало каждую строку и, следовательно, каждую из моих формул, Excel добросовестно работал, пытаясь выяснить, что с этим делать.

Основываясь на том, что я узнал с веб-сайта Microsoft, я изменил формулу на =INDEX(QueryResults,ROW()-ROW(QueryHeaders),MATCH(AC$5,QueryHeaders,0)), где QueryResults и QueryHeaders являются простыми именованными диапазонами.

После того, как я сделал это изменение по всему листу, вставка столбца стала почти мгновенной — менее секунды.

person Chuck Trese    schedule 02.10.2014
comment
Спасибо, Чак. Нет, у меня никогда не бывает целых столбцов или строк в формулах. Это не проблема в моем случае. - person Cool Blue; 02.11.2014
comment
Это устранило мою проблему с очень важным документом, в котором была огромная таблица с множеством ссылок на другие листы. Спасибо! Мне потребовалось удивительно много времени, чтобы найти этот ответ. - person Zeretil; 20.07.2018

arrow_upward
1
arrow_downward

Похоже на описанную здесь проблему http://fastexcel.wordpress.com/2012/01/30/excel-2010-tableslistobject-slow-update-and-how-to-bypass/

Если это так, вам нужно нарушить одно из условий, чтобы обойти его:
Чтобы это замедление произошло, каждое из следующих условий должно быть выполнено:

A cell within the Table must be selected
The sheet containing the Table must be the Active Sheet
The cell being updated must be on the same sheet as the table, but does not have to be within the table
There must be a reasonable number of formulas in the workbook.


Возможно, вы могли бы сделать обновление косвенно через VBA с другим активным листом. Или, возможно, перемещение всех формул в отдельную книгу позволит обойти это.
Или преобразовать ваши таблицы обратно в нормальные диапазоны (и использовать имена динамических диапазонов, если это необходимо).

person Charles Williams    schedule 23.10.2013
comment
Привет, я попытался запустить макрос, чтобы вставить строку в объект списка на целевом листе. Отметив точку вставки на основе активной ячейки, макрос активирует лист без таблиц, а затем выполняет вставку в целевой лист. Теоретически он повторно активирует целевой лист, но я не задерживался достаточно долго, чтобы выяснить это. В основном происходит такое же нелепое поведение, и для вставки одной строки в очень простую таблицу требуется более 10 минут. Я думаю, что таблицы сломаны в Excel. Это очень разочаровывает. - person Cool Blue; 24.10.2013
comment
Не могли бы вы подтвердить, что если вы измените таблицу обратно на диапазон, она будет работать быстро? (В противном случае вы, возможно, обнаружили другую проблему). Если вы подтвердите, я попытаюсь подать отчет об ошибке. - person Charles Williams; 24.10.2013
comment
Я только что протестировал вставку строки, а не изменение значения: это медленно как с таблицами, так и с обычными диапазонами, поэтому ваша ситуация НЕ такая, как описана в моем посте. Возможно, вы можете обойти это, просто изменив значение в пустой строке, а не вставив строку. - person Charles Williams; 24.10.2013
comment
Я могу подтвердить, что это все еще дьявольски медленно, если я сначала конвертирую в диапазон. Что касается вашего второго пункта, я не понимаю предложенного вами обходного пути, Чарльз... - person Cool Blue; 24.10.2013
comment
Ваша проблема возникает при вставке строки. Если вы просто вводите значения в пустые строки в нижней части диапазона, это не будет медленным. Но если вы используете таблицы, вам также нужно избегать проблемы, описанной в моем посте. - person Charles Williams; 24.10.2013

arrow_upward
0
arrow_downward

Попробуйте удалить условное форматирование, а затем повторно применить его с помощью vba после завершения основного кода. Работал на меня.

person avalanche1    schedule 26.12.2013
comment
Привет, я все еще заинтересован в этом, так что спасибо за предложение. Моя проблема, однако, не в медленном VBA, а в том, что excel занимает огромное количество времени для вставки столбцов в таблицы. Я многому научился с тех пор, как написал свой первоначальный пост, поэтому теперь я знаю, почему он загружает только один ЦП и что у Excel есть один основной поток, но я до сих пор не понимаю, есть ли хроническая проблема с таблицами в Excel. Что касается CF, это действительно не проблема, потому что то же самое поведение сохранилось, когда я его удалил. И мой CF очень чистый, я слежу за тем, чтобы он не фрагментировался, и я гарантирую, что все решения находятся во вспомогательных столбцах. - person Cool Blue; 26.12.2013
comment
У меня похожая проблема. Когда я добавляю данные, я вставляю строку вверху, потому что данные составляют около 4000 строк на 50 столбцов. Иногда это длится целую вечность, а иногда всего пару секунд. Это очень расстраивает, когда вам нужно выкурить ошибку. Вся математика выполняется на отдельном рабочем листе, но этот отдельный рабочий лист использует функцию ИНДЕКС. Между прочим, Excel стал моим основным вычислительным инструментом с тех пор, как Mathcad закрылся пятнадцать лет назад. Я все еще не очень хорошо понимаю многословный макроязык, но я могу заставить его делать много работы без понимания. - person richard1941; 13.07.2017