Как ограничить количество ячеек/строк/столбцов Excel

[Romancobran]

Всем привет, Excel маньяки.
Сделал я значит крутой автоматизированный отчет по работе, но вот незадача - он стал безумно медленным, хотя не смотря даже на это, время на него я трачу 2.5 минуты, в то время, как человек который его вел до меня тратил 15-20 минут на мартышкин ручной труд.
Так вот, 2.5 минуты не предел, потому что 40 секунд файл раздупляется, после того, как в него помещаешь сырую отчётность для дальнейших математических расчётов.
Итак, на листе имеется порядка тысячи ячеек, в каждой из которых киллометровая =счётеслимн, которая осуществляет подсчтёт по 27 диапазонам ( столбцы сверху вниз доупора, потому что файл будет расширяться со временем), ссылаясь на технический лист, куда вкладывается сырая отчётность. по каждому столбцу есть несколько критериев по поиску в этой формуле. таким образом каждая ячейка грубо говоря 27 раз проходит вниз по ячейкам столбца (а строк миллион), чтобы найти и посчитать нужные значения. В итоге - я грешу именно на это. И вот вопрос: Предположим, что в обозримом будущем мне понадобится всего 10 тысяч строк, как мне к чертям снести остальные 990.000 тысяч строк? Не СКРЫТЬ, не СГРУППИРОВАТЬ, а именно вообще чтобы их не было на листе, что позволит избежать излишней математики?
З.Ы - не нужно только советовать ограничить критерий поиска в каждой формуле по столбцу до 10тысячной ячейки. Во-первых, я сойду с ума исправлять формулы, они не одинаковые. Во-вторых, файл будет расширяться, в один момент когда я пробью 10 тысяч ячеек вниз, следующие он перестанет считать и об этом можно ненароком забыть)
Надеюсь написал понятно!
Заранее спасибо откликнувшимся !)

[IgorGO]

Сделал я значит крутой автоматизированный отчет по работе

и со старта вы сильно ошиблись "крутой отчет", который еле ворочается, это ржавая шарманка, а не музыкальный инструмент и добыть из нее можно только сркрип и скрежет, а не ноты и музыку
а формулы там сами написались или их писали вы?
какие написали - такие и работают, такие результаты и имеете в итоге

напишите 10 формул СЧЕТЕСЛИ, которые считают что-то в одних и тех же 100тыс. ячеек каждая
и в другом файле
напишите 100тыс формул, которые считают что-то в одних и тех же 10 ячейках каждая

и те 10 и те 100тыс формул в итоге обработали вместе 1 млн. ячеек информации
сравните работу этих двух файлов, когда вы, что-то, поменяли в одной из базовых ячеек
вывод:
суммарные размеры обрабатываемых диапазонов - ерунда по сравнению с количеством формул в файле!
скорее всего подход нужно пересматривать в корне. правильный подход к организации данных и вычислений над ними может реанимировать файл

[Romancobran]

Сообщение от IgorGO

и со старта вы сильно ошиблись "крутой отчет", который еле ворочается, это ржавая шарманка, а не музыкальный инструмент и добыть из нее можно только сркрип и скрежет, а не ноты и музыку
а формулы там сами написались или их писали вы?
какие написали - такие и работают, такие результаты и имеете в итоге

напишите 10 формул СЧЕТЕСЛИ, которые считают что-то в одних и тех же 100тыс. ячеек каждая
и в другом файле
напишите 100тыс формул, которые считают что-то в одних и тех же 10 ячейках каждая

и те 10 и те 100тыс формул в итоге обработали вместе 1 млн. ячеек информации
сравните работу этих двух файлов, когда вы, что-то, поменяли в одной из базовых ячеек
вывод:
суммарные размеры обрабатываемых диапазонов - ерунда по сравнению с количеством формул в файле!
скорее всего подход нужно пересматривать в корне. правильный подход к организации данных и вычислений над ними может реанимировать файл

Да вы поэт))
Безусловно, вы правы, и определенно, подход к обработке данных можно найти другой, но пока что мой скилл не настолько высок. Я итак, как мне кажется, переплюнул сам себя, разработав то, что получилось в итоге, и даже с учётом задумчивости файла , он все равно себя оправдывает, уменьшая время работы с ним практически в 10 раз. Были мысли, что может быть стоит разделить один файл на два, где второй с расчетами будет ссылаться на первый с базой данных, либо придумать что-то с power pivot, но тут тоже есть минусы, объяснять не буду, долго) а диапазон просмотра по формуле счёт уменьшить и сделать проще я даже и не знаю как, очень много критериев отбора искомых значений. Возможное решение для себя вижу только такое, что прежде чем вставлять сырые данные для срабатывания формул - удалять из них устаревшие данные, т.е. За январь-март, оставляя апрель. Но это тоже отнимает время. Хотя, если это можно как-то сделать в .vbs, это уже интересно. В одной из моих тем мне уже помогли добрые люди сделать .vbs для склейки двух отчётов, может в этот код запихнуть ещё такую манипуляцию, где строки будут удаляться, если не соответствуют по критерию дата )) надо попробовать.

[Aleksandr H.]

Сообщение от Romancobran

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

"упор" это где? "Последняя строка листа" или "последняя ячейка с данными в конкретном столбце"?
Напишите свою функцию суммеслимн которая как-то будет угадывать нужный диапазон.


офтоп

Сообщение от Romancobran

он все равно себя оправдывает, уменьшая время работы с ним практически в 10 раз.

Однажды я писал макрос замены рутины. Через некоторое время ускорил его еще в 3 раза. Звучит солидно "ускорил в 3 раза", коллеги неистово респектовали, но когда слышали что вместо 12 сек работает 4 сек, эффект ушел. А я задумался "вот те 8 секунд, которые я выиграл, такой тайм-менеджмент, почему я еще не миллионер?". Но это такое, лирика оффтопная.

[Romancobran]

Сообщение от Aleksandr H.

"упор" это где? "Последняя строка листа" или "последняя ячейка с данными в конкретном столбце"?
Напишите свою функцию суммеслимн которая как-то будет угадывать нужный диапазон.


офтоп

Однажды я писал макрос замены рутины. Через некоторое время ускорил его еще в 3 раза. Звучит солидно "ускорил в 3 раза", коллеги неистово респектовали, но когда слышали что вместо 12 сек работает 4 сек, эффект ушел. А я задумался "вот те 8 секунд, которые я выиграл, такой тайм-менеджмент, почему я еще не миллионер?". Но это такое, лирика оффтопная.

Ахах,забавная история))
Упор это последняя ячейка на листе, то есть миллион какая-то там. В общем , я нашел способ облегчить жизнь слабенькому мобильному процессору .
Пример : был счёт если по диапазону $D:$D , ограничил до $D$1:$D$100.000
Как вы понимаете , формулы там конские , в одной ячейке по формуле сяетеслимн идёт просмотр по , кажется, 4 или 5 столбцам таким Макаром , к каждому из них есть критерии поиска. Если интересно, сейчас выложу пример одной формулы сообщением ниже )
В общем , сократив диапазон с миллиона ячеек до 100.000 по столбцу, формулы стали отрабатываться на моем ноуте не 1 минуту, а секунд 10, так что я всё-таки был прав и не зря убил пару часов, чтобы переделать все формулы на всех страницах документа )) итого вместо 15-20 минут времени на отчёт уходит суммарно минута . Считаю, что это успех )))

[Romancobran]

Вот)

[Romancobran]

Стало вот так) Даже как-то красиво получилось, что подсветка ссылок ячеек с условием плавно смещается :D
Да, наверняка можно было как-то проще сделать всё через power pivot такие операции,либо через VBA но я пока не настолько хорош)

[Hugo121]

Если бы меняли используя Ctrl+H думаю пары часов не было бы нужно...

[Romancobran]

Сообщение от Hugo121

Если бы меняли используя Ctrl+H думаю пары часов не было бы нужно...

Интересненько, ну пару часов я конечно утрировал , но все же. Про ctrl H - пойду гляну , что за фишка такая)) отличный форум и форумчане, прокачиваюсь не по дням а по часам ))

[Hugo121]

Обычная замена. Ставите в настройках замену в формулах, и меняете $D:$D на $D$1:$D$100000 ну и ещё пару тройку других пар. Сразу по всей книге можно.