Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Копирование строк на другой лист по диапазону значений фильтра
 
#1
07.01.2016 12:23:41
Добрый день!

Увы долгие скитания по форуму и отсутствие понимания кодов вынудило меня все же создать новую тему, т.к. много похожих тем но именно такой не нашел.
В общем ситуация следующая. Нужно что бы при изменении значений в листе "Ввод" в лист "Вывод" копировались полностью строки только те в которых имеется совпадение в столбце T:T с значениями фильтра в столбце A:A листа "Значения фильтра". В приложении краткий файл для понимания.

Для чего это нужно. Имеется база данных более 500 тыс. строк с регулярным обновлением, из которой нужно дублировать строки на другой лист только по одному критерию отбора. Единственная проблема в том что диапазон значений составляет 16 тысяч значений(В примере только 2) и фильтровать в ручную большая проблема. Если сможете помочь, буду крайне признателен.
Изменено: Vartan - 07.01.2016 13:07:21
 
 
 
#2
07.01.2016 12:34:20
Цитата
Vartan написал: совпадение в столбце S:S
Ничего не перепутали?
 
 
 
#3
07.01.2016 12:50:31
Спасибо за поправку, действительно перепутал по столбцу T:T :)
 
 
 
#4
07.01.2016 13:02:16
По кнопке устроит или нужно автоматом?  
 
 
 
#5
07.01.2016 13:11:41
Нужно автоматом при обновлении данных в вкладке "Ввод".
 
 
 
#6
07.01.2016 13:25:24
См. вариант. Событием для срабатывания макроса будет являться активация листа "Вывод".
 
 
 
#7
07.01.2016 14:13:26
Огромное спасибо!
 
 
 
#8
07.01.2016 14:16:50
Забыл в коде добавить отключение обновление экрана: на больших объёмах ускорит работу макроса. Справитесь сами? )
 
 
 
#9
07.01.2016 14:19:44
Начал фильтровать по 16и тысячам строк значений и все повисло))) Боюсь сам я ничего пока не могу сделать, нужна будет все же помощь в решении этого.
Не успел правда посмотреть чистит ли он страницу до обновления или нет?
 
 
 
#10
07.01.2016 14:23:30
Цитата
Vartan написал:
Не успел правда посмотреть чистит ли он страницу до обновления или нет?
Сначала весь диапазон (кроме заголовка) очищается, затем заполняется новым данными.
Почему повисло - не знаю. 16 000 - это количество значений для выборки?
 
 
 
#11
07.01.2016 14:28:03
Да. Т.е. помимо огромного массива в вводных, нужно отфильтровать и скопировать по огромному массивы значений и я как понимаю система подзагружается прилично, хотя если делать все в одном листе автофильтром по тому же списку, все фильтруется быстрее.  
 
 
 
#12
07.01.2016 14:29:11
Можно изменить алгоритм: внешний цикл по большому диапазону (будет однократный перебор), а вложенный цикл по массиву данных из столбца А первого листа.
Можно вообще сделать всё на массивах, но не знаю - хватит ли памяти: 500 000 строк и 50 столбцов.
 
 
 
#13
07.01.2016 14:29:42
А так?
Код
Sub DoFilter()
    With Sheets("Значения фильтра")
        Sheets("ввод").Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Range("A1").End(xlDown), Sheets("Вывод").Range("A1")
    End With
End Sub
There is no knowledge that is not power
 
 
 
#14
07.01.2016 14:35:38
Сорри, немного ошибся :)
Код
Sub DoFilter()
    With Sheets("Значения фильтра")
        Sheets("ввод").Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Range("A1", .Range("A1").End(xlDown)), Sheets("Вывод").Range("A1")
    End With
End Sub
There is no knowledge that is not power
 
 
 
#15
07.01.2016 16:41:42
В общем думал выйдет быстрее, но вышло наоборот комп завис наглухо с макросом %)
Изменено: Vartan - 07.01.2016 17:14:12
 
 
 
#16
07.01.2016 17:13:22
Цитата
Юрий М написал:
Можно изменить алгоритм: внешний цикл по большому диапазону (будет однократный перебор), а вложенный цикл по массиву данных из столбца А первого листа.
Можно вообще сделать всё на массивах, но не знаю - хватит ли памяти: 500 000 строк и 50 столбцов.

Задача на самом деле стояла ускорить процесс т.к. по примитивным формулам Excel считает сутки подобный массив, но вам виднее что в этой ситуации было бы наиболее эффективно. После интеграции в нужный файл код выглядит следующим образом:
Код
Private Sub Worksheet_Activate()
Dim LastRow As Long, FreeRow As Long, i As Long, j As Long, Arr()
    LastRow = Cells(Rows.Count, 14).End(xlUp).Row
    Range(Cells(4, 14), Cells(LastRow + 1, 55)).ClearContents
    FreeRow = 2
    With Sheets("Вводные")
        LastRow = .Cells(Rows.Count, 14).End(xlUp).Row
        Arr = .Range(.Cells(4, 14), .Cells(LastRow, 14)).Value
    End With
    With Sheets("С начала месяца по вчер.день")
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To UBound(Arr)
            For j = 2 To LastRow
                If .Cells(j, 19) = Arr(i, 1) Then
                    Range(Cells(FreeRow, 1), Cells(FreeRow, 55)).Value = .Range(.Cells(j, 1), .Cells(j, 55)).Value
                    FreeRow = FreeRow + 1
                End If
            Next


Код
Sub DoFilter()
    With Sheets("Значения фильтра")
        Sheets("ввод").Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Range("A1", .Range("A1").End(xlDown)), Sheets("Вывод").Range("A1")
    End With
End Sub


Прошу прощения, а можно расшифровать что это за скрипт?
Изменено: Vartan - 07.01.2016 17:14:42
 
 
 
#17
07.01.2016 17:17:46
Макрос от СуперКота - это расширенный фильтр с копированием результата на другой лист.
Мой макрос - перебор строк циклом.
У СуперКота должно быть гораздо быстрее, но вот только не знаю, как фильтр будет вести себя с 500 000 строк.
 
 
 
#18
07.01.2016 17:47:52
Действительно, у Супер Кота все работает гораздо быстрее даже с большого массива, но только он переносит на страницу "Вывод" только значения столбца по которому идет выборка, а нужно что бы копировалась вся строка где имеется значение совпадающее с тем что в "Значениях фильтра". Как в таком случае будет правильно?
 
 
 
#19
07.01.2016 17:55:38
Цитата
[QUOTE]Vartan написал: у Супер Кота все работает гораздо быстрее даже с большого массива
Цитата
[USER]Vartan написал: В общем думал выйдет быстрее, но вышло наоборот комп завис наглухо с макросом
Где истина? ))
 
 
 
#20
07.01.2016 18:11:11
У меня уже когнитивный диссонанс случился))) Целый день убил на поиск и попытку что-то написать) в итоге меня Excel шлет куда подальше)

По первому(Вашему) коду все делает правильно, но слишком долго. По второму(Супер кота) все делает быстро, но у меня выходит что он тупо копирует все с одного листа на другой без выборки. В общем вышло у меня вот так, но осталось только научить это все активироваться по нажатию на кнопку, чистить перед применением и самое главное копировать выборочно а не все подряд))

Код
Sub DoFilter()
    With Sheets("Вводные")
        Sheets("С начала месяца по вчер.день").Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Range("A1", .Range("A1").End(xlDown)), Sheets("Лист1").Range("A1")
    End With
End Sub


Немного поясню:
Список значений для фильтра находится на странице "Вводные" в диапазоне с N3:N16209
Динамическая база в вкладке "С начала месяца по вчер.день" так же как и в примере вкладка "Ввод"
А копировать нужно в "Лист1", в примере это "Вывод"

Прикрепил бы файл, да только он тяжелый слишком.
 
 
 
#21
07.01.2016 18:14:12
Цитата
Vartan написал: По первому(Вашему) коду все делает правильно, но слишком долго.
Попробуйте второй вариант (см. файл), о котором я говорил. Но на больших объёмах и должно быть не быстро. Но в любом случае быстрее, чем вручную ))
P.S. Код прокомментировал.
 
 
 
#22
07.01.2016 18:17:44
Доброе время суток
Цитата
Vartan написал: Действительно, у Супер Кота все работает гораздо быстрее даже с большого массива
Быстрее то оно быстрее. У меня на тестовом массиве 25 числовых столбцов, 25 текстовых, 500000 строк, Excel 2010 32bit макрос с .AdvancedFilter копировал около 7 минут.
Скрипт на Power Query отфильтровал за 29 секунд :) . Думаю, вариант на SQL запросе будет где-то также работать. Если что завтра выложу вариант.
 
 
 
#23
07.01.2016 18:26:10
Кнопка цитирования не для ответа [МОДЕРАТОР]

Мне такие слова мама не разрешает говорить "SQL","Power Query" :D
Пока не понятно, но если сможете помочь адаптировать мою БД, вы будете просто мой кумир, т.к. все расчеты в файле ведутся более 10 минут в среднем, что значительно тормозит работу.
 
 
 
#24
07.01.2016 18:32:22
За какое время отрабатывает мой второй вариант на Ваших данных?
 
 
 
#25
07.01.2016 18:55:16
Цитата
Vartan написал: Мне такие слова мама не разрешает говорить "SQL","Power Query"
Я так подозреваю, что это вы не верно интерпретируете извечные материнские слова "Учись сынок, не то..." ;) .
Вариант на Power Query. Пополняете таблицу на листе "Ввод", данные для фильтрации "Значения фильтра", в обоих случаях не трогая заголовки. На листе "Вывод" вкладка "Дата" - Обновить.
Для справки, Power Query надстройка в Excel 2010 Pro Plus sp1 и 2013, в 2016 - встроена в систему.

Успехов.
P. S. На моих тестовых данных MS Query не смог загрузить исходную таблицу :(
Юрий М. А почему бы не занести данные для фильтра в словарь? Гораздо же быстрее будет вместо в среднем
8000 * 500000 просмотров будет
14 * 500000 просмотров в 753 раза быстрее будет поиск.
Изменено: Андрей VG - 07.01.2016 19:05:12
 
 
 
#26
07.01.2016 20:19:57
А если код от СуперКота изменить вот так? Пардон если что - правил на коленке (уже протестировал :))

На всякий случай:
1. заголовок фильтра должен точно совпадать с заголовком фильтруемого столбца на листе Ввод
2. на листе Вывод, в первой строке должны присутствовать все те заголовки с листа Ввод, которые нужно вывести (порядок и кол-во произвольные)
3. Сразу оговорюсь: синтаксис работы с диапазонами изменил не из выпендрежа, а чтобы удобно было менять - у меня VBA шрифт настроен не на кириллицу. Плюс в конструкции With...End With стало меньше смысла.

Код
Sub DoFilter()
    Dim wsIn As Worksheet, wsOut As Worksheet, wsFilter As Worksheet
    Set wsIn = Worksheets("ввод")
    Set wsOut = Worksheets("Вывод")
    Set wsFilter = Worksheets("Значения фильтра")
  
    wsIn.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=wsFilter.Range(wsFilter.Cells(1, "A"), wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp)), _
        CopytoRange:=wsOut.Range(wsOut.Cells(1, "A"), wsOut.Cells(1, wsOut.Columns.Count).End(xlToLeft))
End Sub
Изменено: KL - 07.01.2016 20:43:57
KL
 
 
 
#27
07.01.2016 20:41:40
Андрей, со словарями не дружу - никак руки не доходят разобраться с ними )
Я изначально хотел вообще всё на массивах сделать, но засомневался: исходный диапазон 500 000 х 50, да ещё и параллельный (для выгрузки) изначально такой же. Вот и не знаю - хватит ли памяти? ) Если хватит, то думаю, что достаточно быстро должно отработать.
 
 
 
#28
07.01.2016 22:53:11
Всем огромное спасибо!

Сделал пока по проще по примеру Супер Кота массив обработался за 2 минуты, дальше буду изучать может сделаю что по автоматизированее :)

Юрий, увы макросы 1 и 2 примера вешали комп на глухо, так что пришлось пойти по простейшим путям.
 
 
 
#29
07.01.2016 23:18:51
Вас не поймёшь: то долго, а теперь "вешают наглухо" ))
А вариант Кирилла пробовали?  
 
 
 
#30
08.01.2016 11:40:02
Доброе время суток
Цитата
Юрий М написал:
со словарями не дружу - никак руки не доходят разобраться с ними )
А зря не доходят. У меня вариант на словаре и копированием по Union 1000 строк отработал за 23 секунды. И на стареньком netbook Lenovo U165 - 72 секунды. ;)
 
 
 
Страницы: 1 2 След.
Наверх