|
|
Регистрация Восстановить пароль |
Повторная активизация e-mail |
Регистрация | Задать вопрос |
Заплачу за решение |
Новые сообщения |
Сообщения за день |
Расширенный поиск |
Правила |
Всё прочитано |
|
Опции темы | Поиск в этой теме |
12.04.2020, 22:56 | #1 |
Пользователь
Регистрация: 10.04.2020
Сообщений: 23
|
Как ограничить количество ячеек/строк/столбцов Excel
Всем привет, Excel маньяки.
Сделал я значит крутой автоматизированный отчет по работе, но вот незадача - он стал безумно медленным, хотя не смотря даже на это, время на него я трачу 2.5 минуты, в то время, как человек который его вел до меня тратил 15-20 минут на мартышкин ручной труд. Так вот, 2.5 минуты не предел, потому что 40 секунд файл раздупляется, после того, как в него помещаешь сырую отчётность для дальнейших математических расчётов. Итак, на листе имеется порядка тысячи ячеек, в каждой из которых киллометровая =счётеслимн, которая осуществляет подсчтёт по 27 диапазонам ( столбцы сверху вниз доупора, потому что файл будет расширяться со временем), ссылаясь на технический лист, куда вкладывается сырая отчётность. по каждому столбцу есть несколько критериев по поиску в этой формуле. таким образом каждая ячейка грубо говоря 27 раз проходит вниз по ячейкам столбца (а строк миллион), чтобы найти и посчитать нужные значения. В итоге - я грешу именно на это. И вот вопрос: Предположим, что в обозримом будущем мне понадобится всего 10 тысяч строк, как мне к чертям снести остальные 990.000 тысяч строк? Не СКРЫТЬ, не СГРУППИРОВАТЬ, а именно вообще чтобы их не было на листе, что позволит избежать излишней математики? З.Ы - не нужно только советовать ограничить критерий поиска в каждой формуле по столбцу до 10тысячной ячейки. Во-первых, я сойду с ума исправлять формулы, они не одинаковые. Во-вторых, файл будет расширяться, в один момент когда я пробью 10 тысяч ячеек вниз, следующие он перестанет считать и об этом можно ненароком забыть) Надеюсь написал понятно! Заранее спасибо откликнувшимся !) |
13.04.2020, 00:12 | #2 | |
Новичок
СтарожилДжуниор
Регистрация: 05.02.2008
Сообщений: 9,487
|
Цитата:
а формулы там сами написались или их писали вы? какие написали - такие и работают, такие результаты и имеете в итоге напишите 10 формул СЧЕТЕСЛИ, которые считают что-то в одних и тех же 100тыс. ячеек каждая и в другом файле напишите 100тыс формул, которые считают что-то в одних и тех же 10 ячейках каждая и те 10 и те 100тыс формул в итоге обработали вместе 1 млн. ячеек информации сравните работу этих двух файлов, когда вы, что-то, поменяли в одной из базовых ячеек вывод: суммарные размеры обрабатываемых диапазонов - ерунда по сравнению с количеством формул в файле! скорее всего подход нужно пересматривать в корне. правильный подход к организации данных и вычислений над ними может реанимировать файл
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
|
|
13.04.2020, 04:23 | #3 | |
Пользователь
Регистрация: 10.04.2020
Сообщений: 23
|
Цитата:
Безусловно, вы правы, и определенно, подход к обработке данных можно найти другой, но пока что мой скилл не настолько высок. Я итак, как мне кажется, переплюнул сам себя, разработав то, что получилось в итоге, и даже с учётом задумчивости файла , он все равно себя оправдывает, уменьшая время работы с ним практически в 10 раз. Были мысли, что может быть стоит разделить один файл на два, где второй с расчетами будет ссылаться на первый с базой данных, либо придумать что-то с power pivot, но тут тоже есть минусы, объяснять не буду, долго) а диапазон просмотра по формуле счёт уменьшить и сделать проще я даже и не знаю как, очень много критериев отбора искомых значений. Возможное решение для себя вижу только такое, что прежде чем вставлять сырые данные для срабатывания формул - удалять из них устаревшие данные, т.е. За январь-март, оставляя апрель. Но это тоже отнимает время. Хотя, если это можно как-то сделать в .vbs, это уже интересно. В одной из моих тем мне уже помогли добрые люди сделать .vbs для склейки двух отчётов, может в этот код запихнуть ещё такую манипуляцию, где строки будут удаляться, если не соответствуют по критерию дата )) надо попробовать. |
|
13.04.2020, 09:38 | #4 |
2 the Nation Glory
Старожил
Регистрация: 27.05.2014
Сообщений: 3,289
|
"упор" это где? "Последняя строка листа" или "последняя ячейка с данными в конкретном столбце"?
Напишите свою функцию суммеслимн которая как-то будет угадывать нужный диапазон. офтоп Однажды я писал макрос замены рутины. Через некоторое время ускорил его еще в 3 раза. Звучит солидно "ускорил в 3 раза", коллеги неистово респектовали, но когда слышали что вместо 12 сек работает 4 сек, эффект ушел. А я задумался "вот те 8 секунд, которые я выиграл, такой тайм-менеджмент, почему я еще не миллионер?". Но это такое, лирика оффтопная.
Кто умер, но не забыт, тот бессмертен.
Лао-Цзы. |
13.04.2020, 15:08 | #5 | |
Пользователь
Регистрация: 10.04.2020
Сообщений: 23
|
Цитата:
Упор это последняя ячейка на листе, то есть миллион какая-то там. В общем , я нашел способ облегчить жизнь слабенькому мобильному процессору . Пример : был счёт если по диапазону $D:$D , ограничил до $D$1:$D$100.000 Как вы понимаете , формулы там конские , в одной ячейке по формуле сяетеслимн идёт просмотр по , кажется, 4 или 5 столбцам таким Макаром , к каждому из них есть критерии поиска. Если интересно, сейчас выложу пример одной формулы сообщением ниже ) В общем , сократив диапазон с миллиона ячеек до 100.000 по столбцу, формулы стали отрабатываться на моем ноуте не 1 минуту, а секунд 10, так что я всё-таки был прав и не зря убил пару часов, чтобы переделать все формулы на всех страницах документа )) итого вместо 15-20 минут времени на отчёт уходит суммарно минута . Считаю, что это успех ))) |
|
13.04.2020, 15:09 | #6 |
Пользователь
Регистрация: 10.04.2020
Сообщений: 23
|
Вот)
|
13.04.2020, 15:12 | #7 |
Пользователь
Регистрация: 10.04.2020
Сообщений: 23
|
Стало вот так) Даже как-то красиво получилось, что подсветка ссылок ячеек с условием плавно смещается :D
Да, наверняка можно было как-то проще сделать всё через power pivot такие операции,либо через VBA но я пока не настолько хорош) |
13.04.2020, 15:45 | #8 |
Старожил
Регистрация: 11.05.2010
Сообщений: 5,166
|
Если бы меняли используя Ctrl+H думаю пары часов не было бы нужно...
webmoney: E265281470651 Z422237915069 R418926282008
|
13.04.2020, 16:07 | #9 |
Пользователь
Регистрация: 10.04.2020
Сообщений: 23
|
|
13.04.2020, 16:17 | #10 |
Старожил
Регистрация: 11.05.2010
Сообщений: 5,166
|
Обычная замена. Ставите в настройках замену в формулах, и меняете $D:$D на $D$1:$D$100000 ну и ещё пару тройку других пар. Сразу по всей книге можно.
webmoney: E265281470651 Z422237915069 R418926282008
|
Похожие темы | ||||
Тема | Автор | Раздел | Ответов | Последнее сообщение |
[БД FireBird] Ограничить количество строк с таблицы JOIN | KBO | Помощь студентам | 3 | 26.05.2019 13:35 |
Неправильно выводит количество строк и столбцов | Otar4ik | Помощь студентам | 2 | 07.09.2015 09:38 |
Ограничить количество строк в ListBox | konstantin1990 | Microsoft Office Excel | 21 | 30.08.2013 15:07 |
ограничить количество строк | vostok | Microsoft Office Excel | 7 | 02.01.2013 18:42 |
как в стрингриде задавать ширину(высоту) ячеек, а для отдельных строк (столбцов)) | sdp | Компоненты Delphi | 3 | 20.06.2007 23:02 |