Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Автофильтр по двум столбцам
 
#1
03.12.2009 13:55:45
Нет ли у кого-нибудь идей, как программно реализовать фильтр по условию "содержится" в любом из двух столбцов (не обязательно смежных) БЕЗ ИСПОЛЬЗОВАНИЯ вспомогательного столбца?  
Вспомогательный столбец не всегда удобно вставлять в большую таблицу, особенно если в ней имеются сгруппированные ячейки, да ещё к тому же не в заголовке, а где-то ещё...(фиг тогда формулу по столбцу "протянешь")    
Я сгруппированные ячейки и сам терпеть не могу - всю структуру таблицы и формулы портят, но начальству конечный вид отчетов с ними нравится.  
В приложенном примере (таблица составлена "от балды", смысла прошу не искать) фильтрация по содержимому столбцов А и В осуществляется по мере ввода в TextBox1, но с использованием дополнительного столбца С, в котором "сцепляются" данные из столбцов А и В (разделитель " <=> " , естественно, абсолютно не нужен, а использован в примере просто для наглядности).  
 
В идеале выглядеть всё должно также как и в примере, по без столбца С.    
При этом код программы должен располагаться ТОЛЬКО на этом листе чтобы лист спокойно можно было скопировать/переместить в другую книгу.  
Наверное, для текстбокса-фильтра придётся где-то в теле макроса задавать, по каким столбцам нужно фильтровать...    
В идеале - просто вводом каких-нибудь одинаковых символов-признаков в ячейки 1-ой строки столбцов (ну, например, fltr_1, как сделано в примере), но можно и чек-боксами в ячейках (но тогда имя каждого из них будет уникально и каждое из них придётся добавлять в код программы)  
Поискал по форуму, никаких идей не нарыл... Может, кто-нибудь хоть направление укажет, в котором "копать"?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#2
05.12.2009 15:50:20
Да... Ну и загадку я загадал знатокам! Ни одной реплики в ответ. Наверное, "репы чешут": вроде бы и сказать, что Ёксель чего-то не может не удобно, а что посоветовать - никто не знает... :-)
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#3
05.12.2009 15:56:50
А чего тут чесать? Я бы циклом пробежал по строкам и посмотрел оба этих столбца. Если хоть один из них "содержит", то строка видима, если нет - скрываем строку целиком.
 
 
 
#4
06.12.2009 22:42:17
А как скрыть строку аналогично автофильтру? Ведь не высоту же ей нулевую делать? Но ведь тогда если захочешь, например, как-то отформатировать отфильтрованные строки, а среди них будет таким образом скрытая, то и на неё формат распространится, а этого с отфильтрованными нормальным автофильтром данными не происходит. Там строки в самом деле прячутся так, что даже при копировании на другую страницу части отфильтрованной таблицы скрытые строки не копируются...  
А как по другому можно программно скрыть строку кроме как занулив её высоту, я не знаю... Может свойство какое ей поставить...?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#5
06.12.2009 22:58:31
А про предстоящее форматирование я не знал :-)    
P.S. Скрываю .Hidden = True
 
 
 
#6
07.12.2009 09:52:08
Да, метод  
   Selection.EntireRow.Hidden = True  
именно уменьшает высоту строки до нуля.  
А это совсем не одно и то же, что делает автофильтр со строками, не удовлетворяющими условию фильтрации... Ведь невидимую под фильтром строку даже выделить по переходу по стрелке "вниз" нельзя. Да и прогрессия заполнения на неё не распространяется.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#7
07.12.2009 13:33:21
{quote}{login=Alex_ST}{date=07.12.2009 09:52}{thema=}{post}А это совсем не одно и то же, что делает автофильтр со строками{/post}{/quote}Да ну?  
Запустите макрос в примере.
Bite my shiny metal ass!      
 
 
 
#8
07.12.2009 15:21:06
Ну и что?  
Высота-то строки, не попавшей в фильтр действительно =0, но при этом закрасьте-ка фильтрованный столбец, а потом сбросьте фильтр. Та ячейка, которая была скрыта фильтром, НЕ ЗАКРАСИТСЯ!!!  
Значит обнуление высоты строки  - это совсем не то же самое, что скрытие строки под фильтром, а нечто большее.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#9
07.12.2009 16:59:00
А то, что при применении автофильтра и  .Hidden = True - действие одинаковое.  
И оно отличается от .RowHeight=0  
При автофильтре и скрытии высота строки хоть и становится равной нулю, но где-то эксель помнит, что высота была =Х - он ведь потом именно Х и отображает при снятии фильта/отображении скрытого.  
Еще пример: выполните в моем примере команду Rows("10:10").Hidden = False при включенном автофильтре. Строка 10 отобразится также, как если бы сняли филтьр
Bite my shiny metal ass!      
 
 
 
#10
07.12.2009 17:07:29
да какая разница в конце-то концов, Hidden = True или RowHeight=0 с точки зрения того, что форматирование (или "протяжка" формул) и на то и на другое распространяется, а скрытые фильтром строки - нет.    
Так вот как раз в том и вопрос: как спрятать строку таким образом, чтобы она была также защищена от изменений формата и ввода данных, как и под фильтром?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#11
07.12.2009 17:12:12
{quote}{login=Alex_ST}{date=07.12.2009 05:07}{thema=}{post}как спрятать строку таким образом, чтобы она была также защищена от изменений формата и ввода данных, как и под фильтром?{/post}{/quote}  
Спрятать обычным способом, а форматировать кодом, проверяя условие Если .Hidden = False
 
 
 
#12
07.12.2009 17:34:42
Вам только кажется, что нет.  
На самом деле хитрый эксель втихоря выполняет еще одну команду:     Selection.SpecialCells(xlCellTypeVisible).Select  
Т.е. эксель "понимает", что раз отфильтровано, значит пользователя интересуют только видимые ячейки.  
Стало быть проверять, как советует Юрий, или к диапазону дополнительно добавлять .SpecialCells(xlCellTypeVisible)
Bite my shiny metal ass!      
 
 
 
#13
08.12.2009 11:24:08
Да-а-а... Значит, без вспомогательного столбца слищком сложно всё получится.  
Ладно, как-нибудь буду его по мере надобности вставлять в таблицы и скрывать от начальства.  
Лузер™, Юрий М, спасибо за помощь.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#14
08.12.2009 15:02:56
Alex_ST, не отчаивайтесь. Все просто. Надо просто контролировать выделение и выделять только видимые.
Bite my shiny metal ass!      
 
 
 
#15
08.12.2009 16:19:00
Это чтобы совсем легче стало. :)
Bite my shiny metal ass!      
 
 
 
#16
08.12.2009 17:16:45
Что-то я не въехал пока...  
Завтра буду разбираться.  
Когда оба бокса почеканы, ячейки столбцов перемешиваются... Явно фильтрует, но с условиями какая-то путаница.  
Но всё равно, спасибо за помощь, Лузер™
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#17
08.12.2009 17:41:39
Если один, то по одному столбцу (соответствующему), если оба, то по обоим с условием "или". Т.е. нашлось или в 1-м, или во 2-м столбце. Ибо условие "и" реализует стандартный фильтр.  
Как "ячейки столбцов перемешиваются" не понял.
Bite my shiny metal ass!      
 
 
 
#18
08.12.2009 17:43:18
Вы лучше выделение проверьте на предмет заливки (смены формата) только отфильтрованных
Bite my shiny metal ass!      
 
 
 
#19
10.12.2009 15:41:17
Лузер™, прошу прощения за дезинформацию - что-то глюк какой-то с компом 08.12.2009 вечером был... А вчера, пока я в "блокноте" это сообщение набивал, сервак повис и я его отправить не смог.  
Я ваш пример 08.12.2009 вечером попробовал и увидел, что отфильтрованные данные в ячейках 1-го столбца не соответствовали ячейкам 2-го столбца (ну, как будто из второго столбца перед фильтрацией ячейку со сдвигом вверх или вниз то ли удалили, то ли добавили). Это словами фиг объяснишь, да я и точно не помню как это вчера выглядело, а сегодня уже не повторилось.  
А вчера и сегодня фильтрация по двум столбцам пошла.  
Но сначала был глюк: пытаюсь выделить мышкой отфильтрованные ячейки, всё выделяется, а когда отпускаю кнопку мышки, то выделяется весь экран...  
А потом вдруг всё стало нормально. И заливаются только выделенные ячейки.  
Странно.  
Файл не перегружал, только подправил код чтобы убрать CaseSensitive (наставил где нужно UCase). А когда перешел из VBA к рабочему листу и попробовал фильтр, то вдруг выделение нормально заработало. Чудеса.  
И ещё вопрос (что-то я сам в коде не могу понять): как сбросить фильтр если в текстбоксе всё стёрли (ну, в смысле если после TextBox1_Change стало TextBox1.Value = "")
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#20
10.12.2009 17:43:57
{quote}{login=Alex_ST}{date=10.12.2009 03:41}{thema=}{post} как сбросить фильтр если в текстбоксе всё стёрли (ну, в смысле если после TextBox1_Change стало TextBox1.Value = ""){/post}{/quote}Во-первых, можно как в Sub Отобразить_всё() - .AutoFilter.Range.EntireRow.Hidden = False  
Но это снимет фильтр отовсюду.  
2. Можно (а может даже нужно) убрать проверку If TextBox1.Value <> "" Then  
Тогда условие iRow.Cells(1).Value Like "*" & TextBox1.Value & "*" будет выглядеть как iRow.Cells(1).Value Like "*" и выполняться всегда.  
3. Можно запоминать скрытый диапазон. Если очень много фильтровать, это будет наверное быстрее, чем каждую строку iRow.Hidden = True/False  
 
Пока думаю, что вариант 2 предпочтительней, хотя он не сильно от 1 отличается.
Bite my shiny metal ass!      
 
 
 
#21
10.12.2009 17:52:04
Что-то я сомневаюсь в корректной работе для многих столбцов.  
Данный код будет игнорировать фильтрацию в столбце D  
Думаю, лучше так:  
   For Each iRow In AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible)  
     If Not (iRow.Cells(1).Value Like "*" & TextBox1.Value & "*" Or iRow.Cells(2).Value Like "*" & TextBox1.Value & "*") Then If Not iRow.Hidden Then iRow.Hidden = True  
   Next  
Потестируйте.
Bite my shiny metal ass!      
 
 
 
#22
10.12.2009 17:55:54
Чуть лишнего оставил.  
так:  
For Each iRow In AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible)  
If Not (iRow.Cells(1).Value Like "*" & TextBox1.Value & "*" Or iRow.Cells(2).Value Like "*" & TextBox1.Value & "*") Then iRow.Hidden = True  
Next
Bite my shiny metal ass!      
 
 
 
#23
10.12.2009 18:56:05
Нет, с For Each iRow In AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible) я погорячился.  
Зато сделал с запоминанием диапазонов. Вроде быстрее шевелится.  
 
 
Private Sub TextBox1_Change()   ' фильтрация в столбце №3 по содержимому TextBox1  
Application.EnableEvents = False  
Application.ScreenUpdating = False  
Dim iRow As Range, iRowHidden As Range, iRowVisible As Range  
'If TextBox1.Value <> "" Then  
 If CheckBox1 And Not CheckBox2 Then Selection.AutoFilter Field:=1, Criteria1:="*" & TextBox1.Value & "*", Operator:=xlAnd  
 If CheckBox2 And Not CheckBox1 Then Selection.AutoFilter Field:=2, Criteria1:="*" & TextBox1.Value & "*", Operator:=xlAnd  
 If CheckBox2 And CheckBox1 Then  
   For Each iRow In AutoFilter.Range.Rows  
     If iRow.Cells(1).Value Like "*" & TextBox1.Value & "*" Or _  
     iRow.Cells(2).Value Like "*" & TextBox1.Value & "*" Then  
       Set iRowVisible = Union(iRow, IIf(iRowVisible Is Nothing, iRow, iRowVisible))  
     Else  
       Set iRowHidden = Union(iRow, IIf(iRowHidden Is Nothing, iRow, iRowHidden))  
     End If  
   Next  
   If Not iRowHidden Is Nothing Then iRowHidden.EntireRow.Hidden = True  
   If Not iRowVisible Is Nothing Then iRowVisible.EntireRow.Hidden = False  
   AutoFilter.Range.Rows(1).Hidden = False  
 End If  
'End If  
Application.EnableEvents = True  
Application.ScreenUpdating = True  
End Sub
Bite my shiny metal ass!      
 
 
 
#24
11.12.2009 15:21:58
Спасибо за помощь. Отлично работает.  
Пока разбирался с кодом пришла мысль: а нельзя ли аналогичным образом обойти ограничение автофильтра в 1000 уникальных записей?  
Т.е. для фильтрации использовать не автофильтр, а цикл со скрытием не удовлетворяющих условию записей?  
Ну, наверное, при фильтре по двум столбцам надо заменить    
For Each iRow In AutoFilter.Range.Rows  
на    
For Each iRow In Range(A:B).Rows ? Или что-нибудь ещё?  
Или это будет слишком долго считать?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#25
11.12.2009 15:28:30
Да-а-а... Попробовал.    
Ну, естественно, исправил ошибочку: не For Each iRow In Range(A:B).Rows, а For Each iRow In Range("A:B").Rows  
Работает. Но ме-е-е-е-дненно... Надо как-то ускорить. Наверное, не весь столбец задавать, а только до вычисленного последнего значения в нём.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#26
11.12.2009 15:30:10
Это долго. Лучше For Each iRow In UsedRange.Rows  
А 1000 Вам тут причем? Это же не 1000 строк, это только по треугольничку выпадающему что отображается.
Bite my shiny metal ass!      
 
 
 
#27
11.12.2009 16:13:10
Попробовал распространить метод фильтрации путём скрытия строк и на один столбец. Просто тупо прописал аналогичный (но, естественно, не такой же, а адаптированный)код на случаи Case CheckBox1 And Not CheckBox2 и Case CheckBox2 And Not CheckBox1  
Вроде заработало. Но программа явно требует оптимизации, т.к. фрагменты кода в разных частях явно повторяются...  
И ещё: если отключить автофильтр вообще, то вылетает ошибка в п/пр Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
 
P.S.После всех исправлений я думаю лучше всего будет вместо одного TextBox1 сделать в каждом столбце свой (TextBox1 и TextBox2). По умолчанию - видимые. И один широкий на 2 столбца - TextBox3 по умолчанию не видимый. И когда оба чек-бокса почеканы, то TextBox1 и TextBox2 становятся невидимыми и очишаются, а TextBox3 становится видимым и по нему будет идти фильтрация.  
Но в этом уже помощи не надо. Сам постараюсь сделать.  
 
А пока выкладываю свой пример, в котором надо бы оптимизировать код.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#28
14.12.2009 15:06:43
Попробовал я сам довести до "юзабилити" состояния фильтрацию по двум столбцам (ну, в смысле по критерию "содержится в любом из столбцов") без использования дополнительного столбца и что-то запутался...  
Идея такова:  
1. На листе есть два текст-бокса и ToggleButton между ними.  
2. Если ToggleButton = False (ToggleButton.Caption = "&"), то работать должно как фильтр по введенным в TextBox1 и  TextBox2 значениям в соответствующих (1 и 2) столбцах. Но так как используется цикл для Hide/Show строк, то ограничение на 1000 уникальных записей автофильтра можно обойти.  
3. Если ToggleButton = True (ToggleButton.Caption = "OR"), то работать должно как фильтр по критерию "значения, введенные в TextBox1 или TextBox2, содержатся в любом из столбцов"  
 
Возникшие проблемы:  
1. Использование UsedRange.Rows не позволяет (или я не придумал как это сделать) не прятать строки заголовков.  
2. Чушь какая-то отфильтровывается, кажется... Даже не пойму, какой критерий фильтрации получился.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#29
14.12.2009 17:09:25
1. Заголовок автофильтра тоже скрывается, я его отображал командой: AutoFilter.Range.Rows(1).Hidden = False  
Вы тоже можете прямо указать какую строку не нужно скрывать, например, первую: Rows(1).Hidden = False  
2. По-моему все верно фильтруется.
Bite my shiny metal ass!      
 
 
 
#30
16.12.2009 09:36:51
Лузер™, спасибо за помощь. Почти всё заработало. Строки заголовков "прятаться" перестали. Остались проблемы с форматированием выделенного и сбросом фильтра (я, наверное, когда дорабатывал ваш пример, что-то не учёл).    
Вчера я побился-побился с интерфейсом и пока интуитивно понятно для стороннего пользователя не получается. А сам я пока обойдусь и так.  
Времени на доработку нет. Пока мне проще применять свой доработанный совместно с вами "Удобный автофильтр" (автофильтр по мере ввода в текстбоксы) и прятать где-нибудь на листе дополнительный столбец. Чуть-чуть доработать только макрос в нем надо будет чтобы можно было принудительно задавать столбец для фильтрации, а не всегда вычислять его автоматически по положению текстбокса. Но это мелочи. Сам, наверное, справлюсь. Когда доработаю, кину пример в свою тему "Удобный автофильтр" чтобы "всплыла" и народ смог пользоваться.  
А эту тему временно для себя "замораживаю". Много более срочной работы.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
Страницы: 1
Читают тему
Наверх