Огромная электронная таблица Excel, занимающая слишком много времени обновить ссылки или вычислить формулы
У меня есть электронная таблица Excel с 5 000 строк и столбцы до ДА (размер 12 МБ). За исключением первых шести столбцов, остальные содержат также vlookups или другие формулы. Все vlookups находятся в отдельном рабочем листе Excel. Я изменил настройки Excel, чтобы вручную обновить ссылки и вычислить формулы. Теперь каждый раз я пытаюсь обновить ссылки, или Excel зависает, или он берет что-то как 15 минут.
Прямо сейчас я работаю над разделением рабочего листа в четыре экземпляра; где я обновлю четыре различных листа Excel в последовательности. Но это требует, чтобы вручную копирование столбцов индекса затем подготовило. Требуется 3-4 часа для обновления ссылок. Вчера я начал получать сообщения об ошибках что:
Excel делает недостаточно ресурсов для завершения операции.
Меня оставили без выбора, но разделять всю операцию на четыре небольших шага, требующие ручного вмешательства.
Я посмотрел на ИНДЕКС. Едва ли применимый на моем случае. Какие-либо идеи о том, как я мог сделать его быстро?
7 ответов
К сожалению, это - способ, которым это с электронными таблицами Excel... VBA не является точно самой быстрой опцией там.
Вы могли бы хотеть рассмотреть миграцию данных к Базе данных Microsoft Access.
5 000 строк на 50 столбцов не являются большим рабочим листом. 12 МБ являются совершенно разумным малым и средним размером файла, и у Вас не должно быть потребности сделать что-либо столь же решительное как переделывающий к базе данных или чему-то подобному.
Из Вашего описания проблема, кажется, ссылки. Насколько большой рабочие книги, на которые ссылаются? Они повторно вычисляют, когда Вы связываетесь с ними? Мне нравится предвводная идея: Вы видите то, что происходит затем.
Является Ваше использование памяти (взгляд на Диспетчер задач) превышением Вашей доступной физической RAM? После того как память должна быть выгружена к диску, вещи начинают замедляться много.
Вне предварительного открытия и сортировки справки, я предложил бы смотреть на формулы непоиска: кого-либо можно преобразовать в формулы массива и назвать однажды на вычисление вместо однажды на строку?
На вещи VLOOKUP: Вы попытались использовать INDEX(value_range, MATCH(lookup_key, key_range))? Существуют ситуации, где это быстрее.
Вы не говорите, есть ли у Вас какой-либо VBA. Если Вы делаете, посмотрите на то, сколько раз функций VBA вызвано и сколько времени они берут. Код VBA, который делает большую ссылку на объекты Excel, может быть дорогим.
Откройте рабочую книгу (книги), с которой Вы связываетесь прежде, чем открыть рабочую книгу, содержащую ссылки.
И вид, данные затем используют отсортированную опцию в VLOOKUP (используют Верный для последнего параметра).
Я соглашаюсь с Mike Woodhouse. Это не много данных.
Как тест я сделал бы новую электронную таблицу с данными из обеих электронных таблиц объединенной в 1 новую электронную таблицу. Это скажет Вам, если проблема произойдет, потому что данные находятся в двух отдельных электронных таблицах. Это не должно быть проблемой, но это будет хороший тест.
Я не знаю, является ли это какой-либо справкой для Вашей ситуации, так как она включает ссылки базы данных, но у меня была та же проблема за исключением того, что она брала слишком долго для обновления электронной таблицы, выполняющей запрос базы данных.
Путем я убыстрился, вещи фактором 15 был первым созданием запроса выбрать необходимые данные вместо того, чтобы заставить Excel отфильтровать его. Во-вторых, в Окне свойств Соединения моего соединения с БД, я поставил галочку у опции "Remove data from the external data range before saving the workbook". Это помогло чрезвычайно так, чтобы мой файл непрерывно не увеличивался в размере с большими объемами данных, которые я могу запрашивать.
Теперь мое все обновление занимает приблизительно 2-3 секунды вместо 30-45.
Если существуют какие-либо данные в Вашем файле, который не должен быть условным выражением и останется тем же (например, дата рождения человека) затем после Вашего первого vlookup, чтобы заставить данные, 'Копию' и 'Особенную Вставку - Значения весь столбец/строка мешать файлу повторно вычислить те ячейки каждый раз, когда Вы выполняете вычисление. Выполнение vlookup на данных, полученных от другого vlookup, который я заметил, занимает некоторое время.