Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 3 След.
RSS
Удобный автофильтр
 
#1
20.11.2009 13:53:00
По работе мне приходится составлять и анализировать большие таблицы, поэтому с целью упрощения процедуры использования автофильтров я уже давно написал для личного употребления несколько макросов, а среди них и эти, значительно упрощающие работу с автофильтром Excel.  
 
На листе "Фильтр-Ячейка" прилагаемого примера образец для фильтрации данных в столбцах A...D (1...4) по критерию "содержит" вводится в ячейки A1...D4. После ввода данных и выхода из каждой из этих ячеек в соответствующем столбце включается автофильтр.  
Всё очень просто и достаточно удобно. Но не хотелось бы каждый раз лезть в код чтобы добавить фильтр по какому-либо ещё столбцу...  
Вот я и подумал, что удобно было бы помечать ячейки в заголовке, в которых записываются условия фильтрации, а программа уже сама бы определяла, нужно по изменению помеченной ячейки фильтровать или нет...  
Самое простое пришедшее в голову решение - наставить в требуемые ячейки какие-нибудь мелкие элементы управления, например маленькие текст-боксы, как сделано в примере TextBox1....TextBox4. А программа тогда сама будет определять, является ли вводимый в ячейки текст критерием фильтрации или нет по факту наличия/отсутствия в ней элемента управления. Именно элементы управления, а не автофигуры я решил использовать для того, чтобы их не так просто было случайным неаккуратным движением мышки переместить/удалить, а только в режиме конструктора.  
Затык в доработке кода у меня настал в том, что я никак не могу получить информацию о том, есть в ячейке элемент управления или нет?  
 
На листе же "Фильтр-Текстбокс" для ввода условий фильтрации используются текст-боксы. Преимущества перед вводом в ячейки - данные фильтруются по мере ввода символов. Есть три варианта реализации. Подробности - в комментариях.  
Очень удобная заготовка. Только опять же хотелось бы доработать. Идея доработки в том, что номер столбца для фильтрации должен определяться сам по положению текст-бокса, а не исправлениями кода программы.  
Проблема при попытке доработки возникла схожая с предыдущей: как определить адрес (номер столбца) ячейки, на которой расположен элемент управления?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#2
20.11.2009 14:15:18
По первому вопросу. Можно в ячейках вместо маленьких TextBox'ов разместить CheckBox'ы, сделать линки на одноимённую ячейку, и тогда легко отследить наличие в ячейке элемента управления. Сами значения True/False или спрячутся за контролом, или шрифт покрасить в белый.  
По второму вопросу. А если по событию Change в TextBox присваивать "своей" ячейке значение самого бокса? Тогда узнаем, какой столбец.
 
 
 
#3
20.11.2009 14:33:08
Что-то я вдруг разлогинился во время ввода ответа и прошел какой-то глюк...  
 
Повторяю.  
{quote}{login=Юрий М}{date=20.11.2009 02:15}{thema=}{post}По первому вопросу. Можно в ячейках вместо маленьких TextBox'ов разместить CheckBox'ы, сделать линки на одноимённую ячейку, и тогда легко отследить наличие в ячейке элемента управления. Сами значения True/False или спрячутся за контролом, или шрифт покрасить в белый.  
По второму вопросу. А если по событию Change в TextBox присваивать "своей" ячейке значение самого бокса? Тогда узнаем, какой столбец.{/post}{/quote}  
По первому ответу я что-то пока не въехал... Чек-боксы я пробовал изначально, но текст-боксы мне в своё время показались лучше для этих целей. Помозгую на выходных. Или может примерчик кинете?  
А по второму, пардон, не очень удачный файл-пример скинул... Вся прелесть этих текстбоксов в том, что при выводе на экран они закрывают заголовок столбца, а при распечатке не видны (выставлено свойство - не выводить на печать). Но всё равно, ещё не до конца осмыслил ваш совет... Как вы узнаете, какой ячейке нужно присваивать значения?  
А! Кажется догадываюсь: в свойствах слинковать текстбокс с любой ячейкой столбца, а потом считывать у элемента LincCell (ну, или как его там?) Спасибо за идею. Буду пробовать.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#4
20.11.2009 15:12:37
По первому вопросу. Ведь Вам нужно проверить наличие элемента управления в конкретном столбце? Вариант в файле.
 
 
 
#5
20.11.2009 15:29:00
Добавил ТекстБоксы. Если нельзя занимать первую строку - сделайте линк куда-нибудь ниже.
 
 
 
#6
20.11.2009 15:30:32
Не, так как в вашем примере - не проходит, ведь значения чек-бокса "ИСТИНА"/"ЛОЖЬ" забивают текст в ячейках, который как раз и вводится для указания критерия фильтрации...  
А вот как раз если использовать махонькие-махонькие текст-боксы, то в слинкованную ячейку LincedCell можно свободно вводить критерии фильтрации. Ну и пусть этот текст появится в махоньком, да ещё и залитом в цвет шрифта текстбоксе(ведь линкование-то "дуплексное")... Кто на него смотреть-то будет?  
А нужно всего лишь в программе при каждом изменении в ячейках первой (ну, или в какой там строке будут расположены ячейки ввода критериев?)опрашивать все текстбоксы на листе с целью выяснения у них, с ячейками каких столбцов они слинкованы. Можно на всякий случай ещё и проверять их на "микроразмер".    
И тогда уже можно будет определить, включать по изменению слинкованной ячейки автофильтр или нет...    
Бла-бла-бла... Сам-то понял, чего наговорил? Если честно - не до конца, но рациональное зерно чувствую где-то рядом...  
Ну, в общем, мысля по голове пошла... Спасибо за идею. Буду пробовать.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#7
20.11.2009 15:33:30
Повторюсь: " Если нельзя занимать первую строку - сделайте линк куда-нибудь ниже". Добавлю: можно куда-нибудь правее. Только при этом придётся при вычислении столбца делать поправку на выбранный сдвиг. Чем не вариант?
 
 
 
#8
20.11.2009 15:33:41
Юрий, ну вы и скоры на проги!  
Пока я ответ писал, вы ещё один пример выложили...  
Кажется, тоже не то... Надо ещё пощупать.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#9
20.11.2009 15:35:22
Почему не то? Будет у Вас первая строка свободна!
 
 
 
#10
20.11.2009 15:35:46
Юрий, я не понимаю, чем вам текстбоксы не по душе? По-моему всё должно красиво получиться...
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#11
20.11.2009 15:36:06
Вопросов больше чем ответов...  
Вот есть конкретное:  
аналог кнопки "Отобразить все":  
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData  
 
Далее вопросы:  
 
Вы уверены, что Worksheet_Calculate самое лучшее событие для снятия фильтра?  
 
>>После полного стирания текста текстбокс фокус ввода не теряет  
Если он должен терять, то кто приобретает? Ячейка Вас не устраивает, тогда как задумывается?  
 
Чем не устраивает фильтр-ячейка? Можно его переделать в сторону любого количества столбцов.  
 
По коду:  
Cells(Target.Row, Target.Column).Value равнозначно Target.Value  
 
По привязке контролов к фильтру: А при изменении столбцов не придется код менять, добавляя код для нового текст бокса? Может тогда классом их оформить?  
 
 
ЗЫ. пока писал - отвлекали по работе - потому так поздно ответ фактически на первый пост темы
Bite my shiny metal ass!      
 
 
 
#12
20.11.2009 15:37:48
Да мне, что ТекстБоксы, что ЧекБоксы... Вы же спрашивали как определить наличие элемента управления в конкретной ячейке, и как узнать номер столбца. Вот я предложил примеры. А уж принять или нет - воля Ваша.
 
 
 
#13
20.11.2009 15:50:37
Юрий, ну, если быть точным, то вы, кажется всё-таки не ответили на вопрос "как определить наличие элемента управления в конкретной ячейке?"  
Вы предложили вместо этого слинковать объект с ячейкой. Для данного конкретного приведенного мною примера это можно применить. Ещё раз спасибо за идею.  
НО!  
Ведь предлагаемом вами варианте объект не обязательно должен быть в той же ячейке, куда показывает его свойство LincedCell    
Т.е. мы можем и переместить объект в режиме конструктора, а при этом его свойство LincedCell не изменится.  
А ведь сам Эксель-то знает, за какой ячейкой закреплен каждый графический объект... Ведь указываются же свойства: Перемещать вместе с ячейками, перемещать, но не изменять и т.д.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#14
20.11.2009 16:07:01
Совершенно верно - я указал вариант. Один из вариантов.    
"Ведь предлагаемом вами варианте объект не обязательно должен быть в той же ячейке, куда показывает его свойство LincedCell    
Т.е. мы можем и переместить объект в режиме конструктора, а при этом его свойство LincedCell не изменится." Всё правильно. Но ведь Вам нужно визуально представить его в этой ячейке? Тогда какой смысл его (контрол) потом перемещать?  
Как Excel отслеживает координаты ячейки, над которой находится контрол - я не знаю. Чем мог - тем и помог. Если что не так - уж извините. Хотя, вариант рабочий.
 
 
 
#15
20.11.2009 17:19:14
{quote}{login=Юрий М}{date=20.11.2009 04:07}{thema=}{post}  
Как Excel отслеживает координаты ячейки, над которой находится контрол - я не знаю. {/post}{/quote}.Placement за это отвечает или Формат объекта - свойства, там перемещать/не перемещать...  
А отследить можно с помощью .Top и .Left, которые сравнивать с .Top и .Left у ячеек/столбцов и т.п.
Bite my shiny metal ass!      
 
 
 
#16
20.11.2009 21:29:36
{quote}{login=Лузер™}{date=20.11.2009 05:19}{thema=Re: }{post}{quote}{login=Юрий М}{date=20.11.2009 04:07}{thema=}{post}  
Как Excel отслеживает координаты ячейки, над которой находится контрол - я не знаю. {/post}{/quote}.Placement за это отвечает или Формат объекта - свойства, там перемещать/не перемещать...  
А отследить можно с помощью .Top и .Left, которые сравнивать с .Top и .Left у ячеек/столбцов и т.п.{/post}{/quote}  
Во! Это было бы вообще СУПЕР! Тогда, перемещая, например, окошко фильтра-текстбокса можно было бы изменять и столбцы, за фильтрацию в которых он отвечает. И не нужно было бы лазить в код программы и что-то править при удалении/дабавлении/перемещении столбцов.  
Лузер, а не затруднит примерчик сделать как вычислить адрес ячейки, в которой находится графический объект (ну, естественно, не весь, а его верхний левый угол)?  
Тогда уж дальше я бы сам как-нибудь дальше исхитрился и довёл свои фильтры до предела мечтаний.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#17
23.11.2009 10:17:57
Мне кажется это неудобным - входить в конструктор, чего-то двигать, встанет еще криво (хотя это может только у меня руки кривые).  
Смотрите, сделано на примере Private Sub Column_D_Change(). выделяется ближайшая ячейка левее и выше текстбокса.  
Можно его автоматом подгонять под эту ячейку.
Bite my shiny metal ass!      
 
 
 
#18
23.11.2009 10:24:02
{quote}{login=Лузер™}{date=23.11.2009 10:17}{thema=}{post}Можно его автоматом подгонять под эту ячейку.{/post}{/quote}На том же примере Private Sub Column_D_Change() тексбокс закрывает собой вышеназванную ячейку
Bite my shiny metal ass!      
 
 
 
#19
23.11.2009 23:32:48
Лузер™, ОГРОМНОЕ спасибо за пример.    
К сожалению я сейчас не на работе (простудился, температурю, сел на бюллетень), а все мои файлы примеров и шустрый интернет у меня там. Только вечером дома добрался до И-нета и потому долго не реагировал на Ваш ответ.  
Ваш пример, когда текстбокс Column_D по событию Change прыгает на место (в левый верхний угол ячейки, за которой он закреплён), да ещё и принимает размеры этой ячейки мне ОЧЕНЬ ПОМОГ!  
Теперь я понял, как сделать так, чтобы при перемещении в "конструкторе" текстбокса, программа сама настраивалась на фильтрацию в том столбце, где стоит текстбокс. И не нужно теперь лезть в VBA!  
А для каждого создаваемого для фильтрации текстбокса теперь достаточно в VBA создать абсолютно одинаковую процедуру обработки события Change. Это очень упрощает работу.  
 
Посмотрите, пожалуйста,в примере: набор текста в текстбоксе с салатовой заливкой включает автофильтр в том столбце, где находится левый верхний угол текстбокса.  
Осталось только два не решенных вопроса:  
1. Как проверить, включен ли автофильтр в СТОЛБЦЕ чтобы не вылетало в дебаггер? (в моем примере это произойдёт если салатовый текстбокс переместить, например, в столбец J). Что-то очень простое, но я никак не вспомню...  
2. Какое всё-таки событие надо обрабатывать чтобы стирать текст из комбобоксов-фильтров при сбросе всех фильтров ("Показать всё")?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#20
24.11.2009 15:24:06
Alex_ST, по поводу сроков не переживайте. У меня на работе запарка, а в выходные так к компу и не попал. Так что ответ получился через три дня.  
По вопросам есть встречные:  
1. Нужно именно с одного столбца фильтр снимать или можно сразу со всех? Во втором случае проще так: If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData.  
В первом - завтра на работе гляну аналогичный код. Сейчас не помню.  
2. Я надеюсь, что Вы скажете какое событие. Мне же неизвестно в какой момент фильтрация уже не нужна. Давайте Вы скажете об этом моменте, а мы подберем событие.
Bite my shiny metal ass!      
 
 
 
#21
24.11.2009 17:58:28
Неправильно понял вопросы. Ответ на первый:  
If Intersect(ActiveSheet.Columns(iLeft), ActiveSheet.AutoFilter.Range.Columns) Is Nothing Then Exit Sub  
После того как iLeft посчитано, разумеется :)  
Второй вопрос понял. Думаю. :)
Bite my shiny metal ass!      
 
 
 
#22
24.11.2009 18:51:15
При нажатии на "отобразить все" никаких событий не генерится. Проверено с помощью файла в предпоследнем посте темы http://www.planetaexcel.ru/forum.php?thread_id=6300.  
Но!  
Можно вставить на лист летучую функцию, например, ТДАТА(). Тогда любое действие с автофильтром будет вызывать пересчет. Как следствие - Worksheet_Calculate.    
Минус - пересчет при каждом чихе - может сильно замедлить.  
И добавьте "ручное" отключение событий с помощью переменной Идет_Обработка, ибо обычное отключение с элементами форм не помогает.    
см пример.
Bite my shiny metal ass!      
 
 
 
#23
24.11.2009 23:13:29
Лузер™, ещё раз большое спасибо за помощь.  
С "ручной обработкой", конечно, извращение, но придётся использовать если никак по другому не получится... К стати, знаете про хитрость: если ячейке придать формат ;;; - "три точки с запятой" то её текст на экране не будет виден ни при какой заливке и цвете шрифта? Это очень удобно чтобы прятать служебные данные.  
А по поводу проверки наличия кнопки автофильтра в столбце при помощи конструкции:  
If Intersect(ActiveSheet.Columns(iLeft), ActiveSheet.AutoFilter.Range.Columns) Is Nothing Then Exit Sub  
работает, но Intersect для меня слишком "экзотическая" функция... Никогда не только не применял, но и не видел как это делают... По хелпу попытался понять и не понял, что вы здесь проверяете с её помощью... А какую-нибудь из "общенародных" функций никак не применить? Ну, чтобы понятно всем было без подробных комментариев?  
 
Я набросал (пока для себя, недооформил, выложу завтра после обеда)пример с учетом Ваших советов.    
Всё работает, но хотелось бы упростить код путём замены повторяющихся в обработке каждого текстбокса однотипных операций одной универсальной подпрограммкой. Этой подпрограммке в аргументах надо передавать элемент управления-текстбокс, в котором произошло событие Change, а она уже вычислит столбец и проведёт фильтрацию. И что-то я не могу никак задать тип переменной для аргумента?  
Sub My_Filter (TxtBox as Control) - не проходит... завтра сам ещё попробую разные варианты...  
Не подскажете, какой тип переменной задать чтобы по ней можно было в подпрограмме и позицию на экране и значение определить?
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#24
24.11.2009 23:18:09
{quote}{login=Alex_ST}{date=24.11.2009 11:13}{thema=}{post}К стати, знаете про хитрость: если ячейке придать формат ;;; - "три точки с запятой" то её текст на экране не будет виден ни при какой заливке и цвете шрифта? Это очень удобно чтобы прятать служебные данные.  
{/post}{/quote}  
Достаточно одной точки с запятой, можно использовать в УФ вместо белого текста.
 
 
 
#25
24.11.2009 23:29:51
А у меня одну ; не хочет брать. Минимум две. Три - действительно скрывает. Не знал.
 
 
 
#26
24.11.2009 23:31:59
У меня 2007 и благополучно скрывает, к сожалению не могу проверить на 2003.
 
 
 
#27
25.11.2009 09:43:08
Alex_ST, нет в ней ничего экзотического. Обычная функция. Мы просто не все знаем, потому что не все были нужны. Когда понадобятся - разберемся.  
Intersect возвращает диапазон, созданный пересечением диапазонов - аргументов функции. В данном случае проверяется входит ли столбец Columns(iLeft) в столбцы автофильтра AutoFilter.Range.Columns, т.е. тот самый Ваш вопрос "Как проверить, включен ли автофильтр в СТОЛБЦЕ". Если не входит, то Intersect возвращает Nothing, тогда Exit Sub.  
Так что давайте зачтем Intersect как "общенародную"?  
По вопросу упрощения кода и задания тип переменной.  
Я 20.11.2009, 15:36 писал:  
По привязке контролов к фильтру: А при изменении столбцов не придется код менять, добавляя код для нового текст бокса? Может тогда классом их оформить?  
 
Смысл: все текстбоксы обрабатываются одним кодом события в модуле класса. Добавление нового текстбокса не требует добавления кода, он (текстбокс) сам добавляется в класс.
Bite my shiny metal ass!      
 
 
 
#28
25.11.2009 10:05:54
Забыл "про хитрость":  
Это не хитрость, а пользовательский формат.  
Нажимаем F1 ищем статью "Создание и удаление пользовательских числовых форматов"  
Там читаем:  
Описание кодов форматов может включать в себя до четырех разделов. В этих разделах, отделяемых друг от друга точкой с запятой, задаются форматы положительных, отрицательных, нулевых значений и текста в указанном порядке. Если задано только два раздела, первый из них используется для положительных чисел и нулей, а второй — для отрицательных чисел. Если раздел один, то заданный формат будут иметь все числа. В случае пропуска раздела соответствующая ему точка с запятой все равно должна включаться в описание формата  
 
Смотрим на приложенный рисунок и видим ровно три ";"  
Я думаю всем понятно почему у кого один, у кого два разделителя работают
Bite my shiny metal ass!      
 
 
 
#29
25.11.2009 10:51:01
Хочу немного добавить к сказанному Лузером, по поводу пользовательского формата ";;;" и использования в УФ:    
К великому сожалению, ошибки Эксель не относит ни к числам ни к тексту, потому скрыть их так не получится.
 
 
 
#30
25.11.2009 13:40:20
Лузер™, спасибо за разъяснения про Intersect. На самом деле всё не так сложно. Особенно если знать, что столбцы автофильтра можно получить по AutoFilter.Range.Columns... Да, пробелы моего образования в VBA Excel огромны. Что-то я в VBA Access такого метода не встречал...  
А по поводу оформления классом я просто не въезжаю, к сожалению... Опять пробелы в образовании. Слова-то вроде все знакомые, а смысла не понимаю...  
Я вот тут дооформил обещанный вчера примерчик. Там 4 текстбокса, в которых задаются условия для автофильтров. Столбцы для фильтрации определяются автоматически по левому верхнему углу каждого текстбокса (по предложенному вами способу).    
А вот как упростить код что-то не понимаю.    
Ваша фраза "все текстбоксы обрабатываются одним кодом события в модуле класса. Добавление нового текстбокса не требует добавления кода, он (текстбокс) сам добавляется в класс" звучит очень заманчиво, но абсолютно не понятно как реализуется...
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
Страницы: 1 2 3 След.
Наверх