ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Как ускорить работу макроса по поиску ячеек в таблице.
24 сообщений из 24, страница 1 из 1
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130404
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
Всем добрый день!

У меня была такая задача:
Есть список кодов и соответствующей им информации. Есть список городов. Каждому городу соответствует лист рабочей книги, и на листе снова список кодов с данными по ним.

Необходимо создать таблицу с возможностью выбрать код и город и увидеть данные именно для них. То есть инфо с нескольких листов собирается в одну таблицу и потом автофильтром - выбор нужного.

Я написала макрос, который создавал бы массив исходных кодов (скажем 50) и городо (где-то 20). И получается таблица 50*20 строк. Потом просто каждый код ищется на соответствуюзем листе. Но такой макрос работает оооочень медленно. Минут 15. Может ли как-то оптимизировать этот процесс?


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
For l =  4  To ulastrow
name = Sheets( 1 ).Cells(l,  38 ).Value    'на этом листе список всех городов, такие же имена у листов, всего их ulastrow
For k =  11  To klastrow   'klastrow - столько всего строк в таблице (50*20)
For i =  4  To ilastrow    'ilastrow - столько кодов на каждом листе
If Sheets("Under").Cells(k,  1 ).Value = name Then   
Call vlookup_value_in_report(name)
End If
Next i
Next k



Public Sub vlookup_value_in_report(name As String)
If Sheets("Under").Cells(k,  5 ).Value = Sheets(name).Cells(i,  3 ).Value Then
If Sheets(name).Cells(i, j_date +  2 ).Value <  0 . 93  Then
Sheets("Under").Cells(k,  7 ) = Sheets(name).Cells(i, j_date).Value - Sheets(name).Cells(i, j_date +  1 ).Value
Sheets("Under").Cells(k,  8 ) = Sheets(name).Cells(i, j_date +  2 ).Value
Sheets("Under").Cells(k,  9 ) = Sheets(name).Cells(i, j_week).Value - Sheets(name).Cells(i, j_week +  1 ).Value
Sheets("Under").Cells(k,  10 ) = Sheets(name).Cells(i, j_week +  2 ).Value
Sheets("Under").Cells(k,  11 ) = Sheets(name).Cells(i, j_month).Value - Sheets(name).Cells(i, j_month +  1 ).Value
Sheets("Under").Cells(k,  12 ) = Sheets(name).Cells(i, j_month +  2 ).Value
End If
End If

End Sub


Next l


Заранее спасибо за помощь!
...
Рейтинг: 0 / 0
22.02.2011, 13:41
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130437
Фотография ПЕНСИОНЕРКА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПЕНСИОНЕРКА
Участник
skleppi,

непонятно

зачем объединять листы городов, если выбор по городу
...
Рейтинг: 0 / 0
22.02.2011, 13:52
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130468
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
ПЕНСИОНЕРКА,

имеется в виду объединить данные со всех листов в одну таблицу, чтобы в этой таблице можно было выбрать нужный город
...
Рейтинг: 0 / 0
22.02.2011, 14:03
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130541
Фотография ПЕНСИОНЕРКА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПЕНСИОНЕРКА
Участник
skleppi,

я запустила на условных данных 50*20 циклов 1 минута

в чем особенности ваших таблиц
не помешало бы вам выложить
-кусочек таблицы города
-первую таблицу со списком городов( можно 1город)
-таблицу under--она хоть и перезаписывается, но оформление, форматы
...
Рейтинг: 0 / 0
22.02.2011, 14:27
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130605
EducatedFool
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EducatedFool
Гость
Ускорить код можно
Если особо не стараться - можно ускорить в 10-100 раз,
если приложить чуточку усилий - то время обработки данных сократится до 1...4 секунд.

Но ничем не смогу вам помочь, пока не увижу вашего файла (желательно целиком).

Сначала одной строкой (типа arr = [a1:h5000].value )считываете данные диапазона ячеек в двумерный массив, потом обрабатываете данные в этом массиве.
Производительность увеличится в десятки раз. Запись данных на лист - http://excelvba.ru/code/Array2worksheet] аналогично .

Автофильтр http://excelvba.ru/code/ArrAutofilter] можно применить и к двумерному массиву , не собирая предварительно данные на отдельный лист
...
Рейтинг: 0 / 0
22.02.2011, 14:51
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130607
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
ПЕНСИОНЕРКА,

кодов на самом деле около 200, это я не верно написала, что 50, и примерно 25 городов.
Таблица города выглядит очень просто, но очень громоздко:

столбец с кодами, остальные столбцы - данные за каждый день и накопительно за неделю, за 1 день три вида данных для каждого кода, и так 28-31 дней и накопительные данные за 4 недели.

Таблица со списком городов: просто перечисление в столбик.

Таблица Under имеет такую шапку:

Город, код, описание, ответственный и 6 столбцов, в которые записываются данные именно для этого кода и этого города, и ищутся эти данные на листе с именем "Город",

и вот сам поиск кода занимает очень много времени, так как на каждом листе макрос пробегает все 200 или меньше кодов, чтобы найти нужный, и так для каждого кода и для каждого города.
...
Рейтинг: 0 / 0
22.02.2011, 14:51
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130613
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
EducatedFool,

1...4 секунды...было бы супер! а не подскажите, может здесь обсуждались такие способы обработки?
...
Рейтинг: 0 / 0
22.02.2011, 14:53
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37130747
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
EducatedFool,

не знала о таком способе)))) просто супер))
...
Рейтинг: 0 / 0
22.02.2011, 15:45
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37134812
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
Кстати, если листы "Городов" в данных книгах "однотипны" (то есть все столбцы данных относятся к одним и тем же датам или неделям...), то можно было воспользоваться и Сводом. Вот только пришлось бы отключать автоперерасчет, поскольку при такой структуре обновление сводной таблицы тормозило бы любое внесение изменений.

А вообще - срочно меняйте структуру хранения исходных данных. ИМХО, Вы и сейчас уже вынуждены строго контролировать точность формул, особенно этих ваших "данные за неделю".
У вас ведь всего сущностей-то - код, дата, вид, город (+данные). Вот они должны быть справочниками. А данные - одной последовательной таблицей с этими полями. А все "данные за неделю", "данные по виду и городу" - это обычные выборки. И совсем необязательно переходить на СУБД (даже типа Access), всё можно сделать и средствами Excel. Нужно только понимать, что не следует смешивать исходные данные и выборки-отчеты для печати... и всё сразу начнёт получаться
...
Рейтинг: 0 / 0
25.02.2011, 04:23
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37135107
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
AndreTM, а каким образом можно осуществить выборки средствами excel?
...
Рейтинг: 0 / 0
25.02.2011, 10:31
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37135815
vkodor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vkodor
Участник
skleppi,

Вы уверены что сами писали этот макрос? Мне кажется над Вами кто-то пошутил. Запихнуть sub внутрь sub-a это круто.
А если по существу, то вместо циклов можно использовать метод find
Код: plaintext
1.
2.
3.
4.
Set rng = ThisWorkbook.Worksheets("Under").Range("A:A").Find(What:=name , LookIn:=xlValues, LookAt:= _
                    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
                If Not rng Is Nothing Then
                       msgbox rng.Address
                End If

это существенно уменьшит скорость, а так же можно воспользоваться конструкцией With она то же уменьшит время работы.
...
Рейтинг: 0 / 0
25.02.2011, 14:00
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37135944
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
vkodor, уверена, второй sub туда случайно попал
...
Рейтинг: 0 / 0
25.02.2011, 14:49
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37135949
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
vkodor, точнее - что вы имеет в виду))) там один sub
...
Рейтинг: 0 / 0
25.02.2011, 14:50
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37135978
vkodor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vkodor
Участник
skleppi,

я говорил об этом
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
For l =  4  To ulastrow
...

Public Sub vlookup_value_in_report(name As String)
...
End Sub


Next l

в цикле Public Sub vlookup_value_in_report
...
Рейтинг: 0 / 0
25.02.2011, 14:59
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37136004
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
vkodor, вижу. случайно)
спасибо за совет, попробую метод Find
...
Рейтинг: 0 / 0
25.02.2011, 15:05
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37136969
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
skleppiAndreTM, а каким образом можно осуществить выборки средствами excel?Каким, каким...
ADODB + источник данных (поймите, что "Source" могут быть хоть любые диапазоны любого листа той же книги, хоть их объединение) и выполнить нужный запрос...
Вообще, если бы вы выложили пример вашей таблички, было бы не в пример быстрее посоветовать, что можно сделать, и в каком направлении.
...
Рейтинг: 0 / 0
25.02.2011, 22:00
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37137120
Ципихович Эндрю
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ципихович Эндрю
Участник
EducatedFool,
>Сначала одной строкой (типа arr = [a1:h5000].value)считываете данные диапазона ячеек в двумерный массив
Подскажите, а с данными из Ворда, что то вроде этого можно???
...
Рейтинг: 0 / 0
26.02.2011, 01:38
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37137123
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
Ципихович Эндрю,
Можно, а смысл?
Если вы имеете некую таблицу данных в WORD - она либо должна быть уже готовой, импортированной/сформированной другими приложениями; либо вы можете её выгрузить в какое-либо приложение и действовать уже его методами.
Tertium non datur. Всё же Word - это больше для отчётности, а не для вычислений. Как бы вы не пытались доказать обратное...
...
Рейтинг: 0 / 0
26.02.2011, 01:44
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37137137
Ципихович Эндрю
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ципихович Эндрю
Участник
AndreTM, со смыслом потом разберусь, как её затолкать??
И подскажите пожалуйста, как делается
выгрузить таблицу Ворд в какое-либо приложение ??
...
Рейтинг: 0 / 0
26.02.2011, 02:16
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37137168
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
Ципихович Эндрювыгрузить таблицу Ворд в какое-либо приложение ??Выделить, скопировать в буфер и... вставить, например, в Экзель.
Еще можно просто проанализировать структуру таблицы, записывая её в массив - а потом расписать в нужном приложении.
Можно сохранить как XML или HTML - а потом распарсить...
...
Рейтинг: 0 / 0
26.02.2011, 04:10
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37141149
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
AndreTMskleppiAndreTM, а каким образом можно осуществить выборки средствами excel?Каким, каким...
ADODB + источник данных (поймите, что "Source" могут быть хоть любые диапазоны любого листа той же книги, хоть их объединение) и выполнить нужный запрос...
Вообще, если бы вы выложили пример вашей таблички, было бы не в пример быстрее посоветовать, что можно сделать, и в каком направлении.


Вот такая труктура у таблицы
...
Рейтинг: 0 / 0
01.03.2011, 10:54
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37143134
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
skleppiВот такая структура у таблицыАллё... тук-тук...
Может, я неправильно сформулировал, но нужна не структура таблицы, а таблиц ы с примером данных .
Я же указал уже, что, скорее всего, придётся вообще менять подход к хранению данных, то есть полностью изменять структуру имеющихся таблиц.
...
Рейтинг: 0 / 0
02.03.2011, 02:18
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37143374
skleppi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
skleppi
Гость
AndreTMskleppiВот такая структура у таблицыАллё... тук-тук...
Может, я неправильно сформулировал, но нужна не структура таблицы, а таблиц ы с примером данных .
Я же указал уже, что, скорее всего, придётся вообще менять подход к хранению данных, то есть полностью изменять структуру имеющихся таблиц.


ну все таблицы имеют одну и ту же структуру - как в примере, а данные в столбцах - числа. Примеры данных - 100, 200, 300.......
тук-тук...
...
Рейтинг: 0 / 0
02.03.2011, 09:44
| Ответить | Цитировать | Написать  
Как ускорить работу макроса по поиску ячеек в таблице.
    #37145695
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
Ну странная разбивка-то... "2nd, ratio, 2nd, ratio...", причем на день приходится три столбца данных... что они означают-то?
Поймите, я от вас пытаюсь добиться, какие данные у вас являются, так сказать" исходными и заносятся откуда-то, а какие - рассчитываемыми (и хранению как таковому в нормализованной базе обычно не подлежащими).
Вас же просили - дайте кусочек реальной вашей таблицы. С данными, а не ваше оформление...
...
Рейтинг: 0 / 0
03.03.2011, 05:34
| Ответить | Цитировать | Написать  
24 сообщений из 24, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Как ускорить работу макроса по поиску ячеек в таблице.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&tid=2177114]:
 0ms
get settings:
 11ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 414ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 2ms
others: 222ms
total:  749ms
созд. / загр.: 749ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]