Как улучшить производительность с большими файлами Excel

Question

Я работаю с Excel 2007 на Windows 7, Core i5 2,53 ГГц (2 ядра, 4 потока), с 4 ГБ памяти DDR3 @ 533 МГц.

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

1. а. вставить часть необработанных данных в 6 столбцов х 36 600 строк
   б. извлеките интересующие меня данные из а) и работайте с ними с 8 столбцами х 36 600 строк

Затем я копирую результаты формул из 6 столбцов в 1.b) и вставляю их во второй лист, где собираюсь собрать все необходимые данные и проанализировать их.

2. а. для каждой группы по 6 столбцов x 36 600 строк мне нужно будет добавить 5 столбцов формул (в основном небольшие добавления, содержащие не более 2 значений в каждой, и несколько INDEX-MATCH);
   б. общий объем данных в итоге составит 130 из этих групп, то есть: 780 столбцов данных плюс 650 столбцов формулы x 36 600 строк.
   с. Как только я соберу и проанализирую все данные, я превращу их в значения, которые позже я буду использовать с формулами LOOKUPs/INDEX-MATCHes.
   д. Ни на одном листе нет автоматического форматирования, оба имеют минимальное форматирование (цветные ячейки, чтобы идентифицировать те, у которых есть формулы), если я нажимаю CTRL + END, он идет прямо к концу используемого диапазона.

Итак, моя проблема в том, что на данный момент я построил структуру формулы, к которой я буду добавлять данные по крупицам, но размер файла составляет 166 МБ и он использует все ресурсы моего процессора. Я установил расчет вручную, но открытие, закрытие или сохранение этого файла все еще занимает до 5 минут.

Вопросы:

1. Можно ли ожидать такой медлительности с файлом размером 166 МБ?
2. Будет ли это работать лучше, если я скомпилирую данные в виде 65 столбцов данных плюс 78 столбцов с формулами х 367 000 строк?
3. Поскольку я нахожусь в процессе приобретения нового ноутбука, сможет ли лучший процессор преодолеть трудности? (Я думаю о линиях QuadCore i7 с 16 ГБ оперативной памяти)

ПРИМЕЧАНИЕ, касающееся Вопроса 3: я не ищу рекомендации по аппаратному обеспечению, поскольку я нахожусь в процессе приобретения машины, описанной выше. Я хочу знать, могу ли я ожидать улучшения производительности при работе с этим файлом.

Answer 1

Excel очень интенсивно использует память и неэффективен при работе с большими массивами. Использование SSD или оперативной памяти не помогает, потому что большинство из них будет находиться в памяти, а не на жестком диске. Следите за использованием памяти в диспетчере задач и старайтесь, чтобы оперативная память в Excel не превышала 2 ГБ. Разбейте данные на более мелкие файлы и используйте объединяющую или сводную таблицу для вызова данных по мере необходимости, вместо того чтобы открывать все один раз. Обычно файл размером 55 МБ может занимать 1 ГБ памяти в Excel и открываться в течение 2-3 минут в зависимости от структуры формулы, поэтому 5 минут для открытия 166 МБ не являются неожиданными.

Сохраните в двоичные файлы, и это улучшит загрузку и сохранение больших файлов. Используйте самый быстрый процессор и память. Чтобы проверить, является ли ваш файл Excel масштабируемым для ядер ЦП, запустите расчет с таймером в VBA, используя только 1 ядро ЦП, а затем повторно запустите, используя 2 ядра. Если ускорение близко к 100%, то больше ядер также поможет. Используйте рабочий стол вместо ноутбука.

Answer 2

Это просто для решения вопросов, которые я задаю и на которые не были даны ответы в комментариях.

2) Да, меньше столбцов с большим количеством строк более эффективно.

3) Помог новый ноутбук. Все еще требуется некоторое время, чтобы открыть и рассчитать рабочий лист, но это намного быстрее, чем раньше (четвертая или меньшая часть времени ожидания).