Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Медленно обрабатывается массив данных - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Медленно обрабатывается массив данных (Формулы/Formulas)
Медленно обрабатывается массив данных
Сергей2525 Дата: Среда, 16.01.2019, 10:34 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
есть вопрос по тормознутой обработке данных в эксель, может кто-то сталкивался.
Вводные:
Есть файл с данными содержит 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 файлов, что неудобно.

Остается один вопрос к формуле запроса суммирования данных, предполагаю, что "=суммеслимн(" в таком объеме долго обрабатывается. Возможно у кого то есть опыт работы с другими формулами.
Буду благодарен за совет.

Во вложении образец файла данных и запроса (два листа в одном файле).
К сообщению приложен файл: 11.xls (80.5 Kb)


Сообщение отредактировал Сергей2525 - Среда, 16.01.2019, 10:42
 
Ответить
СообщениеДобрый день!
есть вопрос по тормознутой обработке данных в эксель, может кто-то сталкивался.
Вводные:
Есть файл с данными содержит 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
Дата добавления - 16.01.2019 в 10:34
Nic70y Дата: Среда, 16.01.2019, 10:50 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Цитата Сергей2525, 16.01.2019 в 10:34, в сообщении № 1 ()
предполагаю, что "=суммеслимн
что тут предполагать одна из тормознутых формул,
стараюсь (при возможности ее избегать, так же как и суммесли, в малых кол-вах допускаю)
файл не смотрел


ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Сергей2525, 16.01.2019 в 10:34, в сообщении № 1 ()
предполагаю, что "=суммеслимн
что тут предполагать одна из тормознутых формул,
стараюсь (при возможности ее избегать, так же как и суммесли, в малых кол-вах допускаю)
файл не смотрел

Автор - Nic70y
Дата добавления - 16.01.2019 в 10:50
Сергей2525 Дата: Среда, 16.01.2019, 10:54 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
что тут предполагать одна из тормознутых формул,

ок, а альтернатива ей ?
 
Ответить
Сообщение
что тут предполагать одна из тормознутых формул,

ок, а альтернатива ей ?

Автор - Сергей2525
Дата добавления - 16.01.2019 в 10:54
Nic70y Дата: Среда, 16.01.2019, 11:10 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
ну может быть посмотреть в сторону Сводной таблицы или vba


ЮMoney 41001841029809
 
Ответить
Сообщениену может быть посмотреть в сторону Сводной таблицы или vba

Автор - Nic70y
Дата добавления - 16.01.2019 в 11:10
Сергей2525 Дата: Среда, 16.01.2019, 11:28 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
ну может быть посмотреть в сторону Сводной таблицы или vba

т.е. альтернативными формулами суммеслимн( не заменить?
 
Ответить
Сообщение
ну может быть посмотреть в сторону Сводной таблицы или vba

т.е. альтернативными формулами суммеслимн( не заменить?

Автор - Сергей2525
Дата добавления - 16.01.2019 в 11:28
_Boroda_ Дата: Среда, 16.01.2019, 11:55 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Сводной таблицей получается точно такая же таблица
К сообщению приложен файл: 11-2-11_1.xlsx (26.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСводной таблицей получается точно такая же таблица

Автор - _Boroda_
Дата добавления - 16.01.2019 в 11:55
Сергей2525 Дата: Среда, 16.01.2019, 13:20 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
сводная не вариант, пробовали, сложно обновлять данные,
ищу способ именно через упрощение формулы
 
Ответить
Сообщениесводная не вариант, пробовали, сложно обновлять данные,
ищу способ именно через упрощение формулы

Автор - Сергей2525
Дата добавления - 16.01.2019 в 13:20
_Boroda_ Дата: Среда, 16.01.2019, 13:39 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Сергей2525, 16.01.2019 в 13:20, в сообщении № 7 ()
сложно обновлять данные

Действительно? Вам сложно ткнуться правой кнопкой мыши в любое место сводной и выбрать пункт "Обновить"? Два клика мыши - это сложно. Если Вы хотите сказать про то, что диапазоны сводной изменяются, то это значит, что Вы невнимательно посмотрели на мою сводную - она сделана по диапазону, большему реального. Можно вообще на 99999 строк сделать (но лучше не нужно). Или можно по динамическому диапазону сделать, который сам себя считать будет. Более того, сводные прекрасно работают с закрытыми файлами-источниками

Можно и формулы подправить, вот Вам способ - найти нужное ЦФО (первую строку), внутри него найти нужную организацию (первую строку и ее кол-во) - это и будет диапазон строк для СУММЕСЛИМН Формула получится большая, сложная и малопонятная для пользователей, можно поиск диапазона засунуть в имена, но это немного затормозит работу


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Сергей2525, 16.01.2019 в 13:20, в сообщении № 7 ()
сложно обновлять данные

Действительно? Вам сложно ткнуться правой кнопкой мыши в любое место сводной и выбрать пункт "Обновить"? Два клика мыши - это сложно. Если Вы хотите сказать про то, что диапазоны сводной изменяются, то это значит, что Вы невнимательно посмотрели на мою сводную - она сделана по диапазону, большему реального. Можно вообще на 99999 строк сделать (но лучше не нужно). Или можно по динамическому диапазону сделать, который сам себя считать будет. Более того, сводные прекрасно работают с закрытыми файлами-источниками

Можно и формулы подправить, вот Вам способ - найти нужное ЦФО (первую строку), внутри него найти нужную организацию (первую строку и ее кол-во) - это и будет диапазон строк для СУММЕСЛИМН Формула получится большая, сложная и малопонятная для пользователей, можно поиск диапазона засунуть в имена, но это немного затормозит работу

Автор - _Boroda_
Дата добавления - 16.01.2019 в 13:39
Nic70y Дата: Среда, 16.01.2019, 14:29 | Сообщение № 9
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Доп. столбцы должны помочь (наверное).
В файле очень много безобразия, а именно пробелы в конце ЦФО,
либо от них избавляться (что лучше) либо
Код
=СЖПРОБЕЛЫ()


формулу в файле сократил, файл перезалил.
К сообщению приложен файл: 18.xlsx (19.7 Kb)


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Среда, 16.01.2019, 14:39
 
Ответить
СообщениеДоп. столбцы должны помочь (наверное).
В файле очень много безобразия, а именно пробелы в конце ЦФО,
либо от них избавляться (что лучше) либо
Код
=СЖПРОБЕЛЫ()


формулу в файле сократил, файл перезалил.

Автор - Nic70y
Дата добавления - 16.01.2019 в 14:29
Sancho Дата: Среда, 16.01.2019, 14:48 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
Дополню немного Александра.
Можно вообще на 99999 строк сделать (но лучше не нужно). Или можно по динамическому диапазону сделать, который сам себя считать будет.

как предложение - преобразовать исходные данные в таблицу, сводная сама расширит диапазон при изменении диапазона таблицы.

добавлю еще: плюс сводной таблицы не надо в итоговую таблицу вбивать заново все условия, где ни дай бог ошибешься одним символом
К сообщению приложен файл: _11-2-11_1.xlsm (35.0 Kb)


Сообщение отредактировал Sancho - Среда, 16.01.2019, 14:57
 
Ответить
СообщениеДополню немного Александра.
Можно вообще на 99999 строк сделать (но лучше не нужно). Или можно по динамическому диапазону сделать, который сам себя считать будет.

как предложение - преобразовать исходные данные в таблицу, сводная сама расширит диапазон при изменении диапазона таблицы.

добавлю еще: плюс сводной таблицы не надо в итоговую таблицу вбивать заново все условия, где ни дай бог ошибешься одним символом

Автор - Sancho
Дата добавления - 16.01.2019 в 14:48
Sancho Дата: Среда, 16.01.2019, 15:34 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
И еще, формулы ссылающиеся на все строки в столбцах на мой взгляд тормозят расчеты, в любом случае формула просматривает столбец на листе. Поправьте меня если я не прав.

преобразовать исходные данные в таблицу и направить формулы в таблицу.
К сообщению приложен файл: 4091409.xlsm (35.6 Kb)
 
Ответить
СообщениеИ еще, формулы ссылающиеся на все строки в столбцах на мой взгляд тормозят расчеты, в любом случае формула просматривает столбец на листе. Поправьте меня если я не прав.

преобразовать исходные данные в таблицу и направить формулы в таблицу.

Автор - Sancho
Дата добавления - 16.01.2019 в 15:34
Nic70y Дата: Среда, 16.01.2019, 15:40 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

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


ЮMoney 41001841029809
 
Ответить
Сообщение
Поправьте меня если я не прав
может да, а может нет,
все зависит от формулы и версии.
а вот умные таблицы хороши в малых количествах (но это не точно).

Автор - Nic70y
Дата добавления - 16.01.2019 в 15:40
Sancho Дата: Среда, 16.01.2019, 15:46 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
а вот умные таблицы хороши в малых количествах (но это не точно).


10к строк и 350 столбцов тянуло на ура с последующим анализом, срезами и графиками
 
Ответить
Сообщение
а вот умные таблицы хороши в малых количествах (но это не точно).


10к строк и 350 столбцов тянуло на ура с последующим анализом, срезами и графиками

Автор - Sancho
Дата добавления - 16.01.2019 в 15:46
Nic70y Дата: Среда, 16.01.2019, 16:02 | Сообщение № 14
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
10к строк и 350 столбцов
= 1 шт.


ЮMoney 41001841029809
 
Ответить
Сообщение
10к строк и 350 столбцов
= 1 шт.

Автор - Nic70y
Дата добавления - 16.01.2019 в 16:02
krosav4ig Дата: Среда, 16.01.2019, 16:37 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Sancho, если уж и делать умную таблицу, то и в сводной лучше заменить источник данных на Таблица1[#Все]


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеSancho, если уж и делать умную таблицу, то и в сводной лучше заменить источник данных на Таблица1[#Все]

Автор - krosav4ig
Дата добавления - 16.01.2019 в 16:37
Sancho Дата: Четверг, 17.01.2019, 07:45 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
krosav4ig, Ну да, не посмотрел, что там источник шире указан был, посмотрел что новые строки в сводную вошли и успокоился)
 
Ответить
Сообщениеkrosav4ig, Ну да, не посмотрел, что там источник шире указан был, посмотрел что новые строки в сводную вошли и успокоился)

Автор - Sancho
Дата добавления - 17.01.2019 в 07:45
InExSu Дата: Пятница, 18.01.2019, 00:20 | Сообщение № 17
Группа: Друзья
Ранг: Ветеран
Сообщений: 648
Репутация: 96 ±
Замечаний: 0% ±

Excel 2010, 365
Привет!
Цитата Сергей2525, 16.01.2019 в 10:34, в сообщении № 1 ()
может кто-то сталкивался

Сталкивался.
Пришлось в листах держать только значение. А надстройка умела вычислять листы отдельно (или все ), могла и формулы вернуть. Полный контроль над формулами и значениями.


Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac
 
Ответить
СообщениеПривет!
Цитата Сергей2525, 16.01.2019 в 10:34, в сообщении № 1 ()
может кто-то сталкивался

Сталкивался.
Пришлось в листах держать только значение. А надстройка умела вычислять листы отдельно (или все ), могла и формулы вернуть. Полный контроль над формулами и значениями.

Автор - InExSu
Дата добавления - 18.01.2019 в 00:20
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Медленно обрабатывается массив данных (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!