Добрый день.
Работаю с файлом XLSX в MS2010 размером около 10Мб, в нём около 8 тыс. строк и 150 столбцов. В трёх столбцах - некороткие формулы типа ВПР* и ЕСЛИ*, в остальных - значения или суммирование. (понимаю, непрактично, но пока нет возможности изменить ситуацию).
Проблема возникла при превышении некоторого невидимого ограничения на количество строк или столбцов с формулами или их сочетания: _автофильтр_ (https://msexcel.ru/content/view/27/2/) по любому столбцу стал работать с задержкой до 30 сек.
Раздельное удаление части столбцов, строк или замены формул значениями, пересохранение файла, перенос в новую книгу явного эффекта не даёт. Эффект заметен только при одновременном удалении половины строк, столбцов и замены формул значениями.
Казалось бы, выхода нет, остаётся дробить лист или отказываться от сложных, но удобных формул.
Вдруг, совершенно случайно обнаружил: если удалить ЛЮБОЙ столбец на листе, даже пустой, за пределами таблицы, то тормоза исчезают абсолютно! То есть, при всём вышеописанном массиве данных, включая формулы, _автофильтр_ (https://msexcel.ru/content/view/27/2/) начинает работать мгновенно! Но только до первого сохранения. Далее всё по новой начинает тормозить, пока снова не удалю столбец. Пришлось вытащить на панель кнопку для удаления пустого столбца намного правее таблицы.
Получается, что Эксель запросто может работать с такими объёмами, но требует "пинка"?
Это глюк Экселя? Может быть кто-то смог решить проблему более цивилизованным способом?
автомат. вычисления формул отключали?
Цитироватьразмером около 10Мб, в нём около 8 тыс. строк и 150 столбцов. В трёх столбцах - некороткие формулы типа ВПР* и ЕСЛИ*
Пока еще файл не поломался сам по себе - пора задуматься...
Цитироватьесли удалить ЛЮБОЙ столбец на листе, даже пустой, за пределами таблицы...
Не встречал такого. Возможно, какое-то нарушение в структуре. Еще один сигнал для " пора задуматься".
Пересчёт отключен давно и надолго :) Задумывался много раз, оптимизировал и удалил всё что было можно. На одном листе оставлен минимум информации. Я уже не храню формулы в каждой ячейке, протягиваю их по столбцам по необходимости и потом сохраняю как значения кроме 1 стоки.
Так как работаю на большом предприятии, то обычные среднестатистические объёмы данных, с которыми работают пользователи, нужно умножить на 100, при том, что задачи остаются те же.
Сохранив файл в формат XLSB размер файла уменьшается с 10 до 3 Мб. Обычно работаю в нём. Если сохранить в формате XLS то размер варьируется от 30 до 300Мб (об этом ужасе я много лет назад уже создавал тему и решение проблемы). Нарушение структуры пытался исправить тем, что переносил данные в новую книгу, в том числе, как значения, а форматы уже рисовал вручную заново, чтобы уж никакие косячные хвосты не сохранились. Ну не помогает....
а как на счет именованных диапазонов?
Наличие множества форматов, объекты и примечания, условное_форматирование (http://msexcel.ru/content/view/116/2/) - все это грузит файл.
Данные можно хранить в таблице без всяких ухищрений, спрятать ее не не заглядывать, чтобы не портить эстетическое настроение :)
Можно вхранить в текстовом файле. Обработка - в VBA.
Всякие рюшечки-форматирования - только для отчетной таблицы.
Цитата: iron priest от 10.11.2015, 12:03
а как на счет именованных диапазонов?
Возможно, частично, да, но это не решит основную проблему - медленная работа экселя в пределах одного листа с таблицей указанного размера. А ведь до миллиона строк, который предлагает эксель пользователю для работы, ещё очень далеко.
После удаления столбца остаётся весь этот объём, со всеми неоптимальными формулами, и эксель обрабатывает лист мгновенно, и это может продожаться как угодно долго, пока не сохранишь файл.
Я пробовал этот фокус и на других файлах с большими таблицами, везде был прирост скорости.
vikttur, я в курсе проблем с избытком форматов, стилей, об этом я тоже создавал темы, всё почищено, будьте уверены :)
vikttur, про внешнее хранилище думаю постоянно, но, в силу особых условий и ограничений, пока не могу реализовать... Слишком взаимосвязаны хранящиеся данные, постоянно меняются, на ходу изменяются их многочисленные признаки, файлом постоянно пользуются несколько человек и вводят данные и т.п.
Это я такой капризный, 30 секунд ожидания для меня уже нервотрёпка, а коллеги рядом работают с файлами по 40 000 строк, ждут по 5 минут одного пересчёта ячеек и терпят :) Я туда даже не суюсь.
SQL вам в помощь с такими объемами информации
Да речь не об оптимизации хранения и обработки данных (это была бы отдельная тема форума), а об обнаруженной некорректной работе Экселя, у которого есть подтверждённая фактами возможность для быстрой работы с данными такого объёма, но из-за какого то глюка этот потенциал исчезаеат, пока не дашь "пинка".
выложите файл, так гадать можно очень долго и чаще всего безрезультатно
Прошу прощения за паузу. Вот файл (11Мб) https://yadi.sk/d/sZrewt8xkfrYz
Не ругайте за нерациональные формулы, сейчас вопрос не в этом. Данные подставлены произвольные.
Воспроизведение глюка:
1. Открыть файл
2. Применить _автофильтр_ (https://msexcel.ru/content/view/27/2/) по любому из столбцов, посчитав, сколько секунд на это понадобится Экселю (у меня - 20-30сек).
3. Сбросить _автофильтр_ (https://msexcel.ru/content/view/27/2/) по этому столбцу. Удалить любой столбец за пределами таблицы.
4. Применить снова _автофильтр_ (https://msexcel.ru/content/view/27/2/) по любому столбцу. Засечь время (у меня - 1 сек). После нескольких применений автофильтра сохранить файл. И повторить процедуру.
5. Попытаться найти рациональное объяснение увиденному :)
Удалите группировку (Данные -Разгруппировать -Удалить структуру)
у меня файл работает молниеносно при выключенном автоматическом пересчете, при включенном секунд 5 думает
Цитата: Prist от 25.11.2015, 12:23
Удалите группировку (Данные -Разгруппировать -Удалить структуру)
Удалил. К сожалению, на скорость фильтра не влияет.
Цитата: iron priest от 25.11.2015, 21:21
у меня файл работает молниеносно при выключенном автоматическом пересчете, при включенном секунд 5 думает
Сразу после открывания файла молниеносно работает фильтр? Если это действительно так, то это либо настройки экселя дают такой эффект, либо у Вас другая версия, либо дело в железе? У меня Эксель 14.0.6023.1000 (32бит), 2 компа: DualCore E5700 3Ггц+2Гб, i7+16Гб, на обоих одинаково медленное срабатывание фильтра. Пересчёт у меня отключен всегда.
у меня 2013 офис