Подскажите пожалуйста.
Есть файл, в котором очень много данных.
Для того чтобы расчитывать определённые столбцы необходимо выбирать данные
по нескольким условиям и тут есть два варианта:
1) функция Суммпроизв (()*()*()) или массив - это сильно замедляет работу (длительное время пересчёта)
2) ввод дополнительных столбцов - это сильно раздувает размер файла, да и если честно тоже не очень быстро получается

Вопрос: Можно ли как-то спомощью написания макроса ускорить эти действия. Т.е. не подкините ли какую-нибуть идею быстрого способа суммирования значений столбца по нескольким условиям (просматриваются 2-4 столбца соседние на соответствие определённым условиям). Вариант со сводной таблицей вряд ли подойдёт, так как эти данные промежуточные и даже если вытягивать потом из сводной данные назад в форму, то всё равно долго придётся ждать обновления сводной таблицы).

KL (XL)

1. Можно немного поконкретней с примерами и формулами (лучше с файлами с небольшим кол-вом данных)?
2. Что значит "очень много данных"?
3. Какая машина и версии Windows и Office?
4. Сколько времени занимает полный перерасчет?
5. Возможно формулу можно ускорить, сделать нелетучей и вообще оптимизировать.
6. Зависит от формулы, но практически наверняка макрос будет считать [намного] медленнее.
7. Преимущество макроса в данном случае скорее всего будет заключаться в большем контроле над перерасчетом (что в случае с формулами достигается отключением автоматического перерасчета и использованием клавиши F9)

Реальность конечно может оказаться иной, когда добавится недостающая информация.

KL
[MVP - Microsoft Excel]


1. Выкладываю зип-архив с файлом, ячейки на листе "опреац" которые выбирают данные с других листов пометил розовым цветом. Файл не полный, туда ещё много чего охота навесить (всякие проверки, доп информац.). так что будет ещё медленнее.
2. Теоритически строк может быть сколько угодно, практически наверное хватит 365 (т.е. - один год, а может и меньше)
3. разные
4. смотря сколько строк
5. чем и занимаюсь, это уже 3-я версия файла
6. жаль
7. Это понятно, как раз этого и пытаюсь избежать, чтобы между вводом цифры и появлением выходных данных не было ощутимой задерки

Спасибо за участие

KL (XL)1. не вижу ни одной формулы типа Суммпроизв(()*()*())
2. для 500 строк, полный пересчет всех формул на листе "опреац" на моей машине (Intel Core Duo 2,16Mhz, RAM 3326MB) занимает 1.256,35 миллисек (чуть более секунды). Даже если предположить, что в твоем компе все в 10 раз медленнее, речь идет о 12,5 сек, что для такой тяжеловесной модели совсем неплохо.
3. если промежуточные данные не нужны, то возможно формулы типа Суммпроизв(()*()*()) моглибы ускорить процесс.
4. какие данные нужно получить в конечном итоге для твоей формы (mission-critical)? в каких ячейках и какого листа они содержаться?
5. сводная таблица может оказаться наилучшим и наиболее быстрым вариантом для твоей модели. И не надо ждать обновления сводной таблицы, его можно спровоцировать макросом.

KL
[MVP - Microsoft Excel]

1) Суммпроизв(()*()*()) - нет потому что я сделал Суммесли () с использованием "Ключа" (это я так назвал столбец, а там не знаю как правильно назвать). Так вроде быстрей?
2) Если вы считаете, что нормально - наверное так и есть! Просто пользователь ожет не дождаться пересчёта, начав вводить следующее значение. Можно ли как- то принудительно проводить пересчёт до конца, без возможности прерывания?
3) В том и дело, что промежуточные данные нужны, изначально у меня всё и было на Суммпроизв(()*()*()), а потом потребовалось добавить конкретики и все стало работать медленно.
4) Фыходными данными могут быть различные формы, составленные из содержащихся на листе "операц" информации, скорее всего она ещё расширится кое-какими расчётами.
А непосредственно в своей работе человеку, насколько я понял, нужны все столбцы. Просто раньше это было в нескольких формах, а я предложил объединить в одну для мобильности.
5) Я пока не пробовал, надо подумать, но так как много расчётных величин мне кажется что лучше не будет (но подумать обязательно нужно)


Спасибо за ответ. Я ещё думал может сделать поменьше строк и написать какой-нибуть алгоритм чтобы почаще переходить в новую книгу с удалением старых данных.
И ещё может там не понятно из формы, но в теории предназначена для каждодневного заполнения данных с просмотром результатов и в случае необходимости возможностью поиграться входными танными в случае отклонения каких-либо нормативов (факт на сегодня - план на несколько месяцев вперёд).

KL (XL)

1. Если кол-во выходных данных тоже, то Суммесли быстрее несомненно
2. Нормально "...для такой тяжеловесной модели..." . Для пользователя любая модель затрачивающая на расчеты >=1 сек ненормальна ( http://www.decisionmodels.com/optspeed.htm ) Подобные модели обычно делают для себя, а не для других :-) Не думаю, что возможно принудительно проводить пересчёт до конца, без возможности прерывания, не используя макросов.
3. Я плохо себе представляю форму, на которой могли бы поместиться данные одной строки (не говоря уже обо всех строках) и при этом форма бы помещалась на экране целиком (у меня разрешение 1920:1200). Может генерировать результаты только одной строки за раз, а все переменные параметры задавать с пом. комбобоксов (Validation)?
4. -
5. сводная таблица считает во много раз быстрее

KL
[MVP - Microsoft Excel]
1) -
2) -

3) Имелось введу что форм будет не одна, а например 5-7 в разной компановке данных + оперативная работа.

KL (XL)
3. ... Может генерировать результаты только одной строки за раз, а все переменные параметры задавать с пом. комбобоксов (Validation)?
[MVP - Microsoft Excel]
Это честно говоря не понял. Как это результаты одной строки? И ещё я не знаю что такое комбобоксы?

4)Но для того чтобы было что сводить, нужно расчитать исходные данные для сводной

Выводы:
1) Привыкнуть к задержке
2) Попробовать сводные таблицы в комбинации с функцией
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()
3) Впринципе изменить форму
4)... это то чего я пока не знаю, может когда узнаю всё станет гораздо проще

Пробовал обновлять по изменению на листе сводную таблицу и возвращать данные назад в форму примерно та же задержка получается.

А никто не подскажет выращения вроде Если(Ешибка(А1/В1);0;А1/В1) можно как то по другому записать, чтобы не указывать два раза выражение А1/В1.

А то получается очень длинно, а возможно и долго, напимер вот такая лобуда
=ЕСЛИ(ЕОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(Лист1!$A$3;"'"&$DA$1&"'["&ТЕКСТ($A11;"ДД.ММ.ГГГГ")&"] '"&$DA$2&"'['"&DC$6&"'] '"&$DA$3&"'['"&DC$7&"'] '"&$DA$4&"'"));0;ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(Лист1!$A$3;"'"&$DA$1&"'["&ТЕКСТ($A11;"ДД.ММ.ГГГГ")&"] '"&$DA$2&"'['"&DC$6&"'] '"&$DA$3&"'['"&DC$7&"'] '"&$DA$4&"'"))

KL (XL) DeggasadА никто не подскажет выращения вроде Если(Ешибка(А1/В1);0;А1/В1) можно как то по другому записать, чтобы не указывать два раза выражение А1/В1.

Если(В1=0;0;А1/В1)
???

KL
[MVP - Microsoft Excel]

Формула более сложная, я просто плохой пример привёл. Нужно проверять на ошибки.

KL (XL) DeggasadПробовал обновлять по изменению на листе сводную таблицу и возвращать данные назад в форму примерно та же задержка получается.

Идея спользования сводных таблиц предполагала практически полный отказ от формул (во всяком случае от тяжелых).

Ты что, сохранил формулы с Суммесли и добавил таблицу на базе полученных результатов?

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

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

KL
[MVP - Microsoft Excel]

Я как раз добавил сводную для исходных данных, вместо всех ячеек с суммесли () вставил получить.даннные.свод.табл.(), но так как текта написания функций сильно прибавилось - размер файла вырос чуть ли не в 2 раза.
Я знаю что можно создавать колькулируемые поля, но только не нашёл где прочитать про правила их написания, т.е. кроме стандартной справки (например как задать условие?)
Сводная обновляется при изменении на листах исходных данных. а не на расчётном листе, поэтому можно и весь лист смотреть.
Но честно говоря мне хотелось спросить как отследить изменение ячеек только заданного диапазона, т.к. может пригодиться.

Со сводными таблицами есть одна беда. После изменения какого-либо значения обновляется сводная таблица и после этого становится недоступна отмена последнего действия - это довольно неудобно!

Ser ArturTebe nujno dobavitj RAM 512-1024 Mb
(OZU)
Ну да - тут и Exelя скоро наверное не станет

Ser ArturRAM uvelichivaet skorostj vsex prilojenij MS Office
Я только "ЗА"