ReSQL.ru
     
powered by simpleCommunicator - 2.0.55     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Оптимизация кода переноса данных между таблицами
28 сообщений из 28, показаны все 2 страниц
  все  
Оптимизация кода переноса данных между таблицами
    #37016622
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
Есть два xls файла. В каждом (как ни странно) есть таблица. Первый файл - это прайс-лист вида:

[номер товара] - [цена]

Второй файл - факт наличия вида:

[номер товара] - [остаток]

Задача простейшая: во второй файл переместить свежие цены. Эта задача решена и работает. Всё было хорошо, пока менеджеры не вошли во вкус и не стали гонять прайсы по 8000-10000 наименований.

Сейчас скрипт берет номер товара из файла-остатков, ищет полным перебором совпадение номера в файле-прайсе и при совпадении переносит цену.

Посовейтуйте что-нибудь. А то у меня мысли уже остановились...

Требуется оптимизация скрипта:

Код: 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.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
Sub svedenie()
    Dim fi_so, fi_dis As String
    Dim k As Double

    ' считываем параметры
    fi_so = Range("H5").Value ' имя файла источника
    le_so = Range("H6").Value ' буква столбца, из которого берутся данные
    le_id_so = Range("H7").Value ' буква столбца, по которому смотрится совпадение
    
    fi_dis = Range("H11").Value ' имя файла вставки
    le_dis = Range("H12").Value ' буква столбца вставки
    le_id_dis = Range("H13").Value ' буква столбца совпадения
    
    fi_so = Format(fi_so) + ".xls"
    fi_dis = Format(fi_dis) + ".xls"
    
    src_last_row = Windows(fi_so).ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    dst_last_row = Windows(fi_dis).ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    Windows(fi_dis).Activate
    ' Идем по цели
    For m =  1  To dst_last_row
        kod = le_id_dis + Format(m)
        dst_id = Range(kod).Value
        kod_dst = le_dis + Format(m)
        ' Ищем в исходном файле совпадение
        For n =  1  To src_last_row
            kod = le_id_so + Format(n)
            src_id = Windows(fi_so).ActiveSheet.Range(kod).Value
            ' если значение не пустое и есть совпадение - переносим данные
            If src_id > "" And src_id = dst_id Then
                kod_src = le_so + Format(n)
                Range(kod_dst).Select
                Range(kod_dst).Value = Windows(fi_so).ActiveSheet.Range(kod_src).Value
                Exit For
            Else
                ' отмечаем ячейку, которая сейчас обрабатывается
                k = m Mod  20 
                If k =  0  Then
                    Range(kod_dst).Select
                End If
            End If
        Next n
    Next m
    
End Sub
...
Рейтинг: 0 / 0
15.12.2010, 17:14
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016667
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
может коллекции?

мысли вслух...
...
Рейтинг: 0 / 0
15.12.2010, 17:27
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016674
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
метод Find не пытался использовать ?
поиск значения в Range
...
Рейтинг: 0 / 0
15.12.2010, 17:31
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016742
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
michael Rметод Find не пытался использовать ?
поиск значения в Range

о! сдвинулись с мертвой точки. Вам спасибо!

пока я переписываю - еще подкиньте идей плз.
...
Рейтинг: 0 / 0
15.12.2010, 17:57
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016761
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
rashman,

Надо отсортировать обе таблицы по полю "номер товара"
После этого сделать один проход, перемещая цены.
Сортировку сделать стандартными возможностями эксель - это быстро.
...
Рейтинг: 0 / 0
15.12.2010, 18:06
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016770
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
rashman

забыл добавить
поиск делай по полному совпадению текста
есть такая константа в методе Find
...
Рейтинг: 0 / 0
15.12.2010, 18:11
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016779
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Есть еще вариант - сделать SQL-запросом.
Это будет эффективно и эффектно.
Вот у AndreTM рука набита...
...
Рейтинг: 0 / 0
15.12.2010, 18:15
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016787
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
Shocker.Pro

а как SQL запрос в Excel-e сделать?
c SQL знаком
...
Рейтинг: 0 / 0
15.12.2010, 18:18
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016791
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
michael RShocker.Pro

а как SQL запрос в Excel-e сделать?
c SQL знаком
открыть через ADO
ща поищу пример, недавно тут был
...
Рейтинг: 0 / 0
15.12.2010, 18:18
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016798
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
Shocker.Proоткрыть через ADO
ща поищу пример, недавно тут был
вот
9847080
надо просто на UPDATE элементарный запрос сделать с JOIN-ом
...
Рейтинг: 0 / 0
15.12.2010, 18:22
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016824
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
Код: 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.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
Sub svedenie()
   
    Dim fi_so, fi_dis As String
    Dim k As Double
    
    ' считываем параметры
    fi_so = Range("H5").Value ' имя файла источника
    le_so = Range("H6").Value ' буква столбца, из которого берутся данные
    le_id_so = Range("H7").Value ' буква столбца, по которому смотрится совпадение
    
    fi_dis = Range("H11").Value ' имя файла вставки
    le_dis = Range("H12").Value ' буква столбца вставки
    le_id_dis = Range("H13").Value ' буква столбца совпадения
    
    fi_so = Format(fi_so) + ".xls"
    fi_dis = Format(fi_dis) + ".xls"
    
    src_last_row = Windows(fi_so).ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    dst_last_row = Windows(fi_dis).ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    src_range_key = le_id_so + "1:" + le_id_so + Format(src_last_row)
    
    Windows(fi_dis).Activate
    ' Идем по цели
    For m =  1  To dst_last_row
        kod = le_id_dis + Format(m)
        dst_id = Range(kod).Value
        kod_dst = le_dis + Format(m)
        With Windows(fi_so).ActiveSheet.Range(src_range_key)
            Set fnd_range = .Find(dst_id, LookIn:=xlValues)
            If Not fnd_range Is Nothing Then
                kod_src = le_so + Format(fnd_range.Row)
                Range(kod_dst).Value = Windows(fi_so).ActiveSheet.Range(kod_src).Value
            End If
        End With
        Range(kod_dst).Select
    Next m
    
End Sub

Результат превзошел мои ожидания! Раньше на обработку 8000 позиций уходило 10 с лишним минут и более (в зависимости от размера прайса). Сейчас выполнилось за ~20 секунд!

ОГРОМНЕЙШЕЕ СПАСИБО!

Shocker.Pro, Вам спасибо за комменты. Сортировку таблиц делал. С SQL-запросами знаком. ADO - одна из моих любимых библиотек ;) Но для этой задачи хочется чего-то попроще и побыстрее. На данном этапе меня вполне удовлетворил Find :) Но всё равно - Спасибо за идеи!
...
Рейтинг: 0 / 0
15.12.2010, 18:42
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016828
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
Shocker.Pro

а прикольненько SQL в Excel-е использовать
надо эту идею Эндрю кинуть с его массивами
там и group by + count можно
...
Рейтинг: 0 / 0
15.12.2010, 18:44
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016845
пробуй камнем
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пробуй камнем
Гость
rashman,
выложите плз, Ваши таблички, - хочу одну идею проверить. Возможно будет быстрее работать, чем с .Find
...
Рейтинг: 0 / 0
15.12.2010, 18:57
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37016856
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
Берите
...
Рейтинг: 0 / 0
15.12.2010, 19:03
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017124
пробуй камнем
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пробуй камнем
Гость
rashman,
мало похоже на прайс
Хотел сравнить, будет ли ADO отрабатывать быстрее, чем экселевский .Find, - видно не судьба :(
Может Вы сравните на реальных данных? Процеду вставить в модуль книги-приемника, подправить константы, чтобы соответствовали действительности. Код писался в предположении, что пр.xls - прайс-лист, 38.xls - "факт наличия".
 
Код: 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.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
Sub UpdatePrice()
' таблица-источник
' описание содержит полный путь к файлу и спецификацию источника данных
Const S_SRC_TBL$ = "[пр$] IN 'c:\temp\пр.xls'[Excel 8.0;HDR=Yes;IMEX=1]"
' поля, выбираемые из источника (ключ и источник апдейта)
Const S_SRC_FLD$ = "[nomer_article],[val_3]"
' поле - источник апдейта
Const S_SRC_UPD$ = "[val_3]"
' ключ
Const S_SRC_KEY$ = "[nomer_article]"


' обновляемая таблица
Const S_DST_TBL$ = "[38$]"
' поля, выбираемые из обновляемой таблицы (ключ и назначение апдейта)
Const S_DST_FLD$ = "[article_nomer],[rest_shop_23]"
' поле - назначение апдейта
Const S_DST_UPD$ = "[rest_shop_23]"
' ключ
Const S_DST_KEY$ = "[article_nomer]"

Dim oCn
Dim sCnStr$, sSQL$

' строка соединения
' (!) при IMEX=1 получаем Automation error, почему - так и не понял... :(
' (!) если в обновляемом поле не было ни одной записи,
'     то после обновления Excel сохранит добавленные числа как текст;
'     мелочь(?), но неприятно
sCnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Mode=ReadWrite;Data Source=" _
    & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'"
    
' создание объекта соединение
Set oCn = CreateObject("ADODB.Connection")

' текст запроса на обновление
sSQL = "UPDATE" _
    & " (SELECT " & S_DST_FLD & " FROM " & S_DST_TBL & ") AS A" _
    & " INNER JOIN" _
    & " (SELECT " & S_SRC_FLD & " FROM " & S_SRC_TBL & ") AS B" _
    & " ON A." & S_DST_KEY & "=B." & S_SRC_KEY _
    & " SET A." & S_DST_UPD & "=B." & S_SRC_UPD _
    & " WHERE B." & S_SRC_UPD & " IS NOT NULL;"
    

' открытие соединения, выполнение запроса
oCn.Open sCnStr
oCn.Execute sSQL

' закрытие соединения, обнуление переменной
oCn.Close: Set oCn = Nothing

End Sub
...
Рейтинг: 0 / 0
15.12.2010, 22:56
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017193
Фотография Игорь Горбонос
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Игорь Горбонос
Участник
> Автор: rashman


А ВПР уже не канает?

Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
16.12.2010, 00:30
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017197
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
хм

очень долгий update получается через SQL
интересно а какие то индексы или ключи можно засобачить на такую таблицу ?
чисто из интереса спрашиваю
...
Рейтинг: 0 / 0
16.12.2010, 00:42
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017215
пробуй камнем
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пробуй камнем
Гость
michael R,
если не затруднит, приведиты цифры (кол-во записей в исходной и обновляемой таблицах, ЦПУ, оператива).
У меня на примере ТС - менее 4c (Pentium Dual-Core T2370 @1.73 GHz, 2 GB DDR2-667 SDRAM)
michael R... интересно а какие то индексы или ключи можно засобачить на такую таблицу ? ...нет, к сожалению(?)
...
Рейтинг: 0 / 0
16.12.2010, 01:10
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017468
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
пробуй камнем

ну в принципе записей не много
6 тыс на 8 тысяч
SELECT быстрый а Update медленный
я смотрю идея плохая если нужна скорость лучше стандартными средствами
...
Рейтинг: 0 / 0
16.12.2010, 09:46
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017488
пробуй камнем
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пробуй камнем
Гость
michael R,
 
полуОФФ:
согласен, в данном случае "стандартные" методы лучше. Заметил ещё баг - при многократном выполнении моего кода на одних и тех-же данных увеличивается время выполнения апдейта и занимаемая Excel-ем память.
Много думал(с)
...
Рейтинг: 0 / 0
16.12.2010, 09:55
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017490
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
пробуй камнем

так маленький вопрос

а как в SQL запросе EXCEL-а получить числовое значение в подзапросе
Select cast(F1 as int) NEWVAL не срабатывает
...
Рейтинг: 0 / 0
16.12.2010, 09:55
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017509
пробуй камнем
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пробуй камнем
Гость
michael R,
принудительно никак, - Jet сканирует первые N записей источника и самостоятельно определяет тип даных. Можно извращаться с функциями приведения типов
Код: plaintext
SELECT CInt(F1) AS NEWVAL
но толку не будет.
ЗЫ: профи, поправьте, если не прав.
...
Рейтинг: 0 / 0
16.12.2010, 10:02
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017527
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
Я выложил реальные файлы, присланные из отдела продаж. 38 - факт наличия на точках. пр - это прайс. Только сейчас обратил внимание, что там цен нет :) Но это не влияет на выполнение задачи. Можно брать любую колонку для теста.

Игорь Горбонос> Автор: rashman


А ВПР уже не канает?



Что такое есть ВПР?
...
Рейтинг: 0 / 0
16.12.2010, 10:10
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017549
пробуй кмнем
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
пробуй кмнем
Гость
rashman,
 
встроенная функция ВПР
Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных.

Буква «В» в имени функции ВПР означает «вертикальный».

Синтаксис

ВПР(искомое_значение;таблица ;номер_столбца ;интервальный_просмотр)
...
Рейтинг: 0 / 0
16.12.2010, 10:15
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017599
Фотография michael R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
michael R
Участник
точно функция VLOOKUP это в английской версии Excel-а
тогда даже макрос писать не надо
...
Рейтинг: 0 / 0
16.12.2010, 10:29
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37017904
rashman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rashman
Гость
авторИщет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.

Ключевые слова выделены. В них загвоздка. В моём скрипте менеджер задает сам колонки с ключами и колонки откуда куда копировать.
...
Рейтинг: 0 / 0
16.12.2010, 12:09
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37018446
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
и все же я бы сделал ADO
засунули входную таблицу в рекордсет и легко ищется, сортируется и все все все.
...
Рейтинг: 0 / 0
16.12.2010, 14:52
| Ответить | Цитировать | Написать  
Оптимизация кода переноса данных между таблицами
    #37018712
vit9
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vit9
Гость
Народ, немного не в тему, но не могу найти нигде ответ...
Подскажите пожалуйста где можно посмотреть и обсудить следующее:
как удалить данные из оперативной памяти (из PivotCache???), после обновления сводной таблицы? Вопрос в связи с тем, что в файле несколько тяжёлых сводных таблиц! уже после обновления нескольких оперативная память забивается т.к. PivotCache для каждой из них отдельный (данные разные) и Excel просто вырубает.
SOS!!!

После обновления сводных(х):
...
ActiveSheet.PivotTables("х1").PivotCache.Refresh
...
ActiveSheet.PivotTables("х2").PivotCache.Refresh
...
ActiveSheet.PivotTables("х3").PivotCache.Refresh
...
как выгрузить из оперативки PivotCache (1,2,3)?
...
Рейтинг: 0 / 0
16.12.2010, 16:15
| Ответить | Цитировать | Написать  
28 сообщений из 28, показаны все 2 страниц
  все  
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Оптимизация кода переноса данных между таблицами
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=60&tid=2159144]:
 0ms
get settings:
 10ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 38ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 65ms
get tp. blocked users:
 1ms
others: 326ms
total:  475ms
созд. / загр.: 475ms
x
x
Закрыть


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