Вопрос Огромная таблица Excel, занимающая слишком много времени, чтобы обновлять ссылки или вычислять формулы


У меня есть таблица Excel с 5000 строк и столбцов до AY (размер 12 МБ). За исключением первых шести столбцов, остальные содержат либо vlookups или других формул. Все vlookup находятся в отдельном листе Excel. Я изменил настройку Excel, чтобы вручную обновлять ссылки и вычислять формулы. Теперь каждый раз, когда я пытаюсь обновить ссылки, либо Excel зависает, либо занимает около 15 минут.

Сейчас я работаю над разделением рабочего листа на четыре экземпляра; где я буду обновлять четыре разных листа Excel в последовательности. Но для этого требуется ручное копирование столбцов индекса на следующий этап. Для обновления ссылок требуется 3-4 часа. Вчера я начал получать сообщения об ошибках, которые:

Для завершения операции Excel недостаточно ресурсов.

Я остался без выбора, но чтобы разделить всю операцию на четыре маленьких шага, требующих ручного вмешательства.

Я посмотрел на INDEX. На самом деле это не применимо. Любые идеи о том, как я могу быстро это сделать?


4
2017-08-20 19:33


происхождения


Это похоже на хороший кандидат на приложение базы данных (ms-access или другое), а не на электронную таблицу. Excel действительно не был предназначен для обработки огромных объемов данных. - Stewbob


ответы:


5000 строк по 50 столбцов - это не большой рабочий лист. 12 МБ - вполне разумный размер файла небольшого и среднего размера, и вам не нужно будет делать что-либо столь радикальное, как переработка в базу данных или что-то подобное.

Из вашего описания проблема связана с ссылками. Насколько велики упоминаемые книги? Они пересчитываются, когда вы ссылаетесь на них? Мне нравится идея, предшествующая открытию: вы можете видеть, что происходит тогда.

Используется ли ваше использование памяти (см. Диспетчер задач), превышающее доступную физическую память? Как только память должна быть заменена на диск, все начинает замедляться.

Помимо справки о предварительном открытии и сортировке, я бы предложил взглянуть на формулы без поиска: можно ли преобразовать в формулы массива и вызывать один раз для расчета вместо одного в строке?

В VLOOKUP: вы пытались использовать INDEX(value_range, MATCH(lookup_key, key_range))? Бывают ситуации, когда они быстрее.

Вы не говорите, есть ли у вас VBA. Если вы это сделаете, посмотрите, сколько раз вызываются функции VBA и сколько времени они берут. Код VBA, который делает много ссылок на объекты Excel, может быть дорогостоящим.


3
2017-09-12 13:50





Если в вашем файле есть какие-либо данные, которые не должны быть условными и будут оставаться неизменными (например, дата рождения человека), то после вашего первого vlookup для получения данных «Копировать» и «Вставить специальные значения» весь столбец / row, чтобы остановить повторный подсчет этого файла при каждом запуске вычисления. Запуск vlookup на данные, полученные из другого vlookup, которые я заметил, занимает довольно много времени.


3
2017-07-27 19:04





К сожалению, так происходит с электронными таблицами Excel ... VBA - это не самый быстрый вариант.

Возможно, вам стоит рассмотреть возможность переноса данных в базу данных Microsoft Access.


2
2017-08-20 19:49





Откройте рабочую книгу (ы), с которой вы связываетесь, прежде чем открывать книгу, содержащую ссылки.

И отсортируйте данные, затем используйте отсортированную опцию в VLOOKUP (используйте True для последнего параметра).


2
2017-09-12 10:55





Я использую Office 10, чтобы обойти это.

  1. Я открыл свой огромный файл, повторно сохранил его как файл типа Excel workbook 1997-2003.
    • Это вызвало сообщение с вопросом о чем-то (я не помню).
  2. Я нажал OK, и у меня появился новый файл Excel 1997-2003.
    • Файл переместился с 25 МБ до 1 МБ.
  3. Затем я снова перезапустил его снова из старого файла 1997-2003 в новую книгу, и он стал еще меньше.

У меня была эта проблема с несколькими книгами, и это всегда решало ее для меня.


2
2017-11-04 18:06





Возможно, получить более быстрый компьютер или обновить оперативную память


1
2017-08-20 19:44



это только идет до сих пор, к сожалению: Excel не будет использовать преимущества более чем одного процессора или ядра процессора :( - warren


Я согласен с Майком Вудхаусом. Это не так много данных.

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


0
2017-09-12 14:51





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

То, как я ускорил процесс в 15 раз, заключался в том, чтобы сначала сделать запрос, чтобы выбрать нужные данные, вместо того, чтобы заставить Excel фильтровать его. Во-вторых, в окне «Свойства подключения» моего подключения к БД я проверил флажок «Удалить данные из внешнего диапазона данных перед сохранением книги». Это очень помогло, так что мой файл не постоянно увеличивался с большими объемами данных, которые я могу запросить.

Теперь все мое обновление занимает около 2-3 секунд вместо 30-45.


0
2018-05-11 21:48