Добрый день! есть вопрос по тормознутой обработке данных в эксель, может кто-то сталкивался. Вводные: Есть файл с данными содержит 9 листов в каждом листе массив данных объемом 20-30 тыс. строк и до 100 столбцов Есть второй файл который содержит 24 листа на каждом одинаковая табличная форма запроса к первому файлу. Табличная форма каждого листа содержит порядка 370 строк и 22 столбца В каждой ячейке табличной формы запроса прописана формула =суммеслимн( которая обращается к файлу с данными и суммирует данные по 5 критериям. (количество формул 370*22) Количество уникальных критериев по отбору 1 критерий 60-90 шт. 2 критерий 24-30 шт. 3 критерий 75-110 шт. 4 и 5 критерий даты (отбор осуществляется по интервалу дат)
Файл с данными весит 20 мб файл с таблицей запроса данных весит 9 мб
При любом действии в файле таблица запроса обработка занимает десятки минут Например сохранить файл, вставить строку, перенести лист и т.д.
Больше всего времени занимает сохранение.
Пробовал обрабатывать в эксел 2016, 2013 и 2010 скорость не меняется Что делалось: 1. Отключился автоматический пересчет таблицы, результат - скорость увеличилась, но данные все равно нужно рано или поздно пересчитывать и это вешает машину. 2. Увеличилась производительность машины (перенес данные в удаленный терминал с более мощным процессором и 16 гб оперативки) результат - обработка вместо 20 минут занимает 10-12 минут 3. Разбросал файл таблица запроса на 24 отдельных файла (Один лист с запросов один файл) результат - обработка стала работать быстрее, но для полноценной работы нужно держать открытыми все 25 файлов, что неудобно.
Остается один вопрос к формуле запроса суммирования данных, предполагаю, что "=суммеслимн(" в таком объеме долго обрабатывается. Возможно у кого то есть опыт работы с другими формулами. Буду благодарен за совет.
Во вложении образец файла данных и запроса (два листа в одном файле).
Добрый день! есть вопрос по тормознутой обработке данных в эксель, может кто-то сталкивался. Вводные: Есть файл с данными содержит 9 листов в каждом листе массив данных объемом 20-30 тыс. строк и до 100 столбцов Есть второй файл который содержит 24 листа на каждом одинаковая табличная форма запроса к первому файлу. Табличная форма каждого листа содержит порядка 370 строк и 22 столбца В каждой ячейке табличной формы запроса прописана формула =суммеслимн( которая обращается к файлу с данными и суммирует данные по 5 критериям. (количество формул 370*22) Количество уникальных критериев по отбору 1 критерий 60-90 шт. 2 критерий 24-30 шт. 3 критерий 75-110 шт. 4 и 5 критерий даты (отбор осуществляется по интервалу дат)
Файл с данными весит 20 мб файл с таблицей запроса данных весит 9 мб
При любом действии в файле таблица запроса обработка занимает десятки минут Например сохранить файл, вставить строку, перенести лист и т.д.
Больше всего времени занимает сохранение.
Пробовал обрабатывать в эксел 2016, 2013 и 2010 скорость не меняется Что делалось: 1. Отключился автоматический пересчет таблицы, результат - скорость увеличилась, но данные все равно нужно рано или поздно пересчитывать и это вешает машину. 2. Увеличилась производительность машины (перенес данные в удаленный терминал с более мощным процессором и 16 гб оперативки) результат - обработка вместо 20 минут занимает 10-12 минут 3. Разбросал файл таблица запроса на 24 отдельных файла (Один лист с запросов один файл) результат - обработка стала работать быстрее, но для полноценной работы нужно держать открытыми все 25 файлов, что неудобно.
Остается один вопрос к формуле запроса суммирования данных, предполагаю, что "=суммеслимн(" в таком объеме долго обрабатывается. Возможно у кого то есть опыт работы с другими формулами. Буду благодарен за совет.
Во вложении образец файла данных и запроса (два листа в одном файле).Сергей2525
что тут предполагать одна из тормознутых формул, стараюсь (при возможности ее избегать, так же как и суммесли, в малых кол-вах допускаю) файл не смотрел
что тут предполагать одна из тормознутых формул, стараюсь (при возможности ее избегать, так же как и суммесли, в малых кол-вах допускаю) файл не смотрелNic70y
Действительно? Вам сложно ткнуться правой кнопкой мыши в любое место сводной и выбрать пункт "Обновить"? Два клика мыши - это сложно. Если Вы хотите сказать про то, что диапазоны сводной изменяются, то это значит, что Вы невнимательно посмотрели на мою сводную - она сделана по диапазону, большему реального. Можно вообще на 99999 строк сделать (но лучше не нужно). Или можно по динамическому диапазону сделать, который сам себя считать будет. Более того, сводные прекрасно работают с закрытыми файлами-источниками
Можно и формулы подправить, вот Вам способ - найти нужное ЦФО (первую строку), внутри него найти нужную организацию (первую строку и ее кол-во) - это и будет диапазон строк для СУММЕСЛИМН Формула получится большая, сложная и малопонятная для пользователей, можно поиск диапазона засунуть в имена, но это немного затормозит работу
Действительно? Вам сложно ткнуться правой кнопкой мыши в любое место сводной и выбрать пункт "Обновить"? Два клика мыши - это сложно. Если Вы хотите сказать про то, что диапазоны сводной изменяются, то это значит, что Вы невнимательно посмотрели на мою сводную - она сделана по диапазону, большему реального. Можно вообще на 99999 строк сделать (но лучше не нужно). Или можно по динамическому диапазону сделать, который сам себя считать будет. Более того, сводные прекрасно работают с закрытыми файлами-источниками
Можно и формулы подправить, вот Вам способ - найти нужное ЦФО (первую строку), внутри него найти нужную организацию (первую строку и ее кол-во) - это и будет диапазон строк для СУММЕСЛИМН Формула получится большая, сложная и малопонятная для пользователей, можно поиск диапазона засунуть в имена, но это немного затормозит работу_Boroda_
И еще, формулы ссылающиеся на все строки в столбцах на мой взгляд тормозят расчеты, в любом случае формула просматривает столбец на листе. Поправьте меня если я не прав.
преобразовать исходные данные в таблицу и направить формулы в таблицу.
И еще, формулы ссылающиеся на все строки в столбцах на мой взгляд тормозят расчеты, в любом случае формула просматривает столбец на листе. Поправьте меня если я не прав.
преобразовать исходные данные в таблицу и направить формулы в таблицу.Sancho
Сталкивался. Пришлось в листах держать только значение. А надстройка умела вычислять листы отдельно (или все ), могла и формулы вернуть. Полный контроль над формулами и значениями.
Сталкивался. Пришлось в листах держать только значение. А надстройка умела вычислять листы отдельно (или все ), могла и формулы вернуть. Полный контроль над формулами и значениями.InExSu
Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac