Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Автофильтр более 10000 строк, Оптимизация работы с автофильтром с объемом строк более 10000
 
#1
09.09.2013 11:57:43
Здравствуйте, уважаемые форумчане.

Проблема распространенная. Excel 2010 не фильтрует по критерию. Ссылается на то, что столбец содержит более 10000 уникальных элементов.

Прошерстил поиск данного форума. Лучшего ответа, чем по ссылке не нашел. Однако тема эта уже в архиве и добавлять там ответы нельзя. А уточнения имеются.

В прилагаемом файлике по столбцу U необходимо найти строки в реестре с буквосочетанием "цинк". Понятное дело, в прилагаемом файле это может сделать автофильтр, но в исходнике около 40 000 строк, и автофильтр отказывается там работать.

Так вот в архивной теме слэн и KuklP предложили хорошие решения. Однако есть недостатки или неверное применение кодов мной. Так например решение слэна (пост №32 в теме архива) не может фильтровать по всему содержимому строки (т.е. при вводе "цинк" "оцинкован" отобран не будет, а "цинкование" будет). Решение KuklP позволяет отбирать по содержимому всей ячейки, но перекидывает на отдельный лист, что для моих целей неудобно.

С учетом всего вышесказанного, прошу помочь решить задачку. В красной ячейке столбца U при вводе "цинк" должно отобраться 3 строки на этом листе. Код в данной ячейке должен уметь отбирать по диапазону, содержащему около 40000 строк.

Спасибо!
Изменено: Mandor - 09.09.2013 11:59:53
 
 
 
#2
09.09.2013 13:00:57
Код
Sub Mfind(c As Range)
    Dim X As Range, r As Range, fA$
    If c.Text <> "" Then
        Sheets(2).UsedRange.Clear
        Set X = Me.UsedRange.Columns(c.Column).Find(c.Text & "*", LookAt:=xlPart)
        If Not X Is Nothing Then
            Set r = X
            fA = X.Address
            Do
                Set X = Me.UsedRange.Columns(c.Column).FindNext(X)
                Set r = Application.Union(r, X)
            Loop While Not X Is Nothing And X.Address <> fA
            r.EntireRow.Copy Sheets("Лист2").[a1]
            Me.[a2].Select
            Sheets("Лист2").Activate
        End If: End If
End Sub

И код из второго листа думаю не нужен. Я его убрал.
Изменено: Hugo - 09.09.2013 13:03:23
 
 
 
#3
09.09.2013 13:12:28
Mr. Hugo, не могли бы вы приложить файлик.

Я совсем темный в кодах. Действую по наитию. Вставил ваш код в лист 1, сохранил, а изменений не наблюдаю...
 
 
 
#4
09.09.2013 13:32:07
Файл не покажу - я на работе.
Если сумели добавить в модуль листа - значит можете не добавить, а заменить уже существующий там аналогичный код.
Там должны быть два макроса -
Private Sub Worksheet_Change(ByVal Target As Range)
и этот
Sub Mfind(c As Range)
И во втором листе я не стал вникать зачем там аналогичный код - я его просто убрал. Думаю он там не нужен. Возможно он мешает.
 
 
 
#5
09.09.2013 14:04:33
Вот теперь получилось, спасибо за разъяснение.

Но дело в том, что отфильтрованный материал попадает на новый лист. А мне нужно в исходном листе вносить изменения в отфильтрованных строках...

Возможно ли сделать так, чтобы отфильтрованные по условию строки отразились в исходном листе (лист1)?

На всякий случай прилагаю файл с кодом Hugo.
Изменено: Mandor - 09.09.2013 14:05:12
 
 
 
#6
09.09.2013 14:16:54
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> [u1].Address Then Exit Sub
    If Target = "" Then Me.Rows.Hidden = 0: Exit Sub
    Call Mfind([u1])
End Sub

Sub Mfind(c As Range)
    Dim X As Range, r As Range, fA$
    Set X = Me.UsedRange.Columns(c.Column).Find(c.Text & "*", LookAt:=xlPart)
    If Not X Is Nothing Then
        Set r = X: fA = X.Address
        Do
            Set X = Me.UsedRange.Columns(c.Column).FindNext(X)
            Set r = Application.Union(r, X)
        Loop While Not X Is Nothing And X.Address <> fA
        Me.UsedRange.Offset(2).EntireRow.Hidden = -1
        r.EntireRow.Hidden = 0
    End If
End Sub
Изменено: KuklP - 09.09.2013 14:24:40
Я сам - дурнее всякого примера! ...
 
 
 
#7
10.09.2013 06:20:19
KuklP, спасибо вам большое за помощь.

Разрешите уточнить, можно ли подкорректировать код так, чтобы сделать автофильтр по нескольким столбцам в одном листе? Будут ли тогда данные фильтры работать взаимоотбирая? Например в столбце А отобрали по критерию "доставка", в столбце В доотобрали по критерию "автомобиль", в столбце С доотобрали по критерию "легковой".
 
 
 
#8
11.09.2013 11:42:57
Собственно говоря родился еще один вопрос. Зачем разработчики Excel поместили на лист столько строк? Какое-то издевательство на самом деле над пользователями. Сделать - сделали, а как оптимизировать работу нужно додумывать самому. Хорошо, что есть такой ресурс как этот!
 
 
 
#9
11.09.2013 11:54:05
Не помещайте на лист столько данных.
Сохраняйте файл в формате 2003 - тогда и строк будет не так много :)
А по вопросу "зачем" - иногда это бывает нужно.
Сейчас есть работа с файлом на полмиллиона строк - говорят иногда выгрузка переваливала за миллион...
 
 
 
#10
11.09.2013 12:21:42
Цитата
иногда выгрузка переваливала за миллион...
Если уж речь идет о выгрузке (экспорте) такого масштаба, можно предположить, что эта выгрузка - "из-под" мощного ПО.
А если ПО мощное, то такой объем информации "из-под" него может выгружать только тот, у кого  - "ни ума, ни фантазии"! :D
Мощное ПО всегда располагает мощнейшими шаблонами-фильтрами, основанными на алгоритмах для данной прикладной сферы.
 
 
 
#11
11.09.2013 13:18:18
Цитата
Разрешите уточнить, можно ли подкорректировать код так
Безусловно можно. Но хлопотно и это уже выйдет за рамки простой помощи на форуме.
Цитата
Зачем разработчики Excel поместили на лист столько строк? Какое-то издевательство на самом деле над пользователями.
А у меня бампер и подушка безопасности в машине. Зачем? По Вашей логике я должен все время тормозить об стены, столбы, чтоб оправдать работу изготовителей? Иначе это
Цитата
Какое-то издевательство на самом деле над пользователями
?  :D
P.S. и вообще, в который уже раз пишу - форум не стол халявных заказов. Я показал Вам принцип, как это можно сделать - развивайте, делайте под свою хотелку. А если нет ни знаний, ни желания(времени и т.д.) ими овладевать, тогда прямая дорога именно в стол заказов, но не халявных: http://www.planetaexcel.ru/forum/?PAGE_NAME=list&FID=7
Изменено: KuklP - 11.09.2013 13:24:21
Я сам - дурнее всякого примера! ...
 
 
 
#12
11.09.2013 13:56:43
У человека идёт выгрузка по DDE, обычно за день до 300к строк - и в общем вполне пока удобно динамично обрабатывать в Экселе, макрос справляется раз в пару секунд.
Но иногда изредка как раз нужна эта "подушка безопасности" до миллиона.
Хотя в будущем есть мысль переводить всё в нормальную базу, когда будет отработана вся схема обработки данных. Пока тренируемся "на кошках" :)
 
 
 
#13
17.12.2020 09:24:32
Здравствуйте, хотел обратиться с таким же вопросом, похожая ситуация только строк в листе около 150000 и нужно выбрать нужные строки на новый лист или книгу. Хотел воспользоваться макросом предложенным KuklP

Private Sub Worksheet_Change(ByVal Target As Range)  
  If Target.Address <> [g3].Address Then Exit Sub
  Call Mfind([g3])
End Sub  

Sub Mfind(c As Range)  
  Dim X As Range, r As Range, fA$  
  If c.Text <> "" Then  
      Sheets(2).UsedRange.Clear  
      Set X = Me.UsedRange.Columns(4).Find(c.Text & "*", LookAt:=xlWhole)  
      If Not X Is Nothing Then  
          Set r = X  
          fA = X.Address  
          Do  
              Set X = Me.UsedRange.Columns(4).FindNext(X)  
              Set r = Application.Union(r, X)  
          Loop While Not X Is Nothing And X.Address <> fA  
          r.EntireRow.Copy Sheets("Лист2").[a1]
          Me.[a2].Select
          Sheets("Лист2").Activate  
      End If: End If  
End Sub  


но не получается его применить на практике, не пойму как он работает. Вставляю его в исходный текст Лист1 нажимаю запуск макроса он мне показывает окно создать новый макрос.

Приложу пример своей урезанной таблицы (выбрать строки нужно по названию выделенным красным), может кто объяснит как его правильно использовать!
Изменено: Денис Корсачёв - 17.12.2020 09:25:05
 
 
 
Страницы: 1
Читают тему
Наверх