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

Question

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

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

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

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

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

Answer 1

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

Answer 2

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

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

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

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

Что касается VLOOKUP : вы пытались использовать INDEX(value_range, MATCH(lookup_key, key_range))? Есть ситуации, когда это быстрее.

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

Answer 3

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

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

Answer 4

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

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

Answer 5

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

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

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

Answer 6

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

Answer 7

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

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

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

Answer 8

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

Из вашего первого поста я подозреваю, что именно с этого и началось замедление вычислений. Вы пытались переместить все данные в один и тот же рабочий файл и обновить все ссылки, чтобы они были внутренними для этого файла?

Answer 9

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

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