Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Изменение расширенного фильтра (макрос). - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Изменение расширенного фильтра (макрос). (Формулы/Formulas)
Изменение расширенного фильтра (макрос).
andruha912009 Дата: Среда, 23.11.2016, 20:17 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Добрый день!
Уважаемые коллеги, прочив тему http://www.planetaexcel.ru/techniques/2/197/по использованию расширенного фильтра с помощью макроса, я попробовал применть этот способ в моей задаче. (макрос уже прописа в исходном коде листа)

В моем тестовом примере наведен простой способ фильтрации - фильтруем Поля1,2,3,4 в соответствии с теми значениями, которые находятся в диапазоне критериев A2 :D 2.

Далее у меня возникают вопросы:

1. Проставив значение 1 в поле А2, исходная таблица фильтруется не в полном объеме, оставляя лишние строки (начиная с 10)
2. Самое важное - для моей задачи необходимо применять не наложенный фильтр (по принципу лесенки),а комбинированный. У меня будет использоваться в дипазоне критериев только значения 1. Например, если проставить 1 в Поле1, Поле3, Поле4, должны отображаться только строки 5,7,9,10, 13, так как они содержат еденицы Поля1, Поля3, Поля4 (или еще пример, если проставить 1 в Поле3, Поле4, в исходной таблице должны быть показаны только 9,10, 13 строка.

Подскажите, пожалуйста, можно ли это реализовать с помощью макроса?
Спасибо!
К сообщению приложен файл: 9669537.xlsm(19Kb)
 
Ответить
СообщениеДобрый день!
Уважаемые коллеги, прочив тему http://www.planetaexcel.ru/techniques/2/197/по использованию расширенного фильтра с помощью макроса, я попробовал применть этот способ в моей задаче. (макрос уже прописа в исходном коде листа)

В моем тестовом примере наведен простой способ фильтрации - фильтруем Поля1,2,3,4 в соответствии с теми значениями, которые находятся в диапазоне критериев A2 :D 2.

Далее у меня возникают вопросы:

1. Проставив значение 1 в поле А2, исходная таблица фильтруется не в полном объеме, оставляя лишние строки (начиная с 10)
2. Самое важное - для моей задачи необходимо применять не наложенный фильтр (по принципу лесенки),а комбинированный. У меня будет использоваться в дипазоне критериев только значения 1. Например, если проставить 1 в Поле1, Поле3, Поле4, должны отображаться только строки 5,7,9,10, 13, так как они содержат еденицы Поля1, Поля3, Поля4 (или еще пример, если проставить 1 в Поле3, Поле4, в исходной таблице должны быть показаны только 9,10, 13 строка.

Подскажите, пожалуйста, можно ли это реализовать с помощью макроса?
Спасибо!

Автор - andruha912009
Дата добавления - 23.11.2016 в 20:17
Pelena Дата: Среда, 23.11.2016, 20:35 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
1) в исходной таблице не должно быть полностью пустых строк. У Вас в примере строка 12 была пустой
2) чтобы критерии соединялись через логическое ИЛИ, надо их записывать в разных строках
К сообщению приложен файл: 3770646.xlsm(19Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
1) в исходной таблице не должно быть полностью пустых строк. У Вас в примере строка 12 была пустой
2) чтобы критерии соединялись через логическое ИЛИ, надо их записывать в разных строках

Автор - Pelena
Дата добавления - 23.11.2016 в 20:35
andruha912009 Дата: Среда, 23.11.2016, 20:43 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Спасибо большое.
На вашем примере, когда я 1 стоит только в Поле2 (В ячейке B3) - отображается вся таблица, а мне нужны только 12 и 15 строка, так как только в них содержаться 1 Поля2.

с ув., Андрей
 
Ответить
СообщениеСпасибо большое.
На вашем примере, когда я 1 стоит только в Поле2 (В ячейке B3) - отображается вся таблица, а мне нужны только 12 и 15 строка, так как только в них содержаться 1 Поля2.

с ув., Андрей

Автор - andruha912009
Дата добавления - 23.11.2016 в 20:43
Pelena Дата: Среда, 23.11.2016, 20:44 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Почему в В3? Поставьте в В2


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПочему в В3? Поставьте в В2

Автор - Pelena
Дата добавления - 23.11.2016 в 20:44
andruha912009 Дата: Среда, 23.11.2016, 20:52 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
работает. а как отфильтровать 2 и 4 поле по тем строкам, где есть 1? (должны быть 12, 13, 16 строка)
Суть задачи,чтобы инструмент был универсальным.(в дальнейшем я в чекбоксах буду выбирать, какие поля должны входить список проверяемых на наличие единиц)
 
Ответить
Сообщениеработает. а как отфильтровать 2 и 4 поле по тем строкам, где есть 1? (должны быть 12, 13, 16 строка)
Суть задачи,чтобы инструмент был универсальным.(в дальнейшем я в чекбоксах буду выбирать, какие поля должны входить список проверяемых на наличие единиц)

Автор - andruha912009
Дата добавления - 23.11.2016 в 20:52
Pelena Дата: Среда, 23.11.2016, 20:57 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
в дальнейшем я в чекбоксах буду выбирать

Где об этом было сказано хоть слово? Составьте пример, соответствующий задаче
[p.s.]И не надо дублировать каждый свой ответ в личку[/p.s.]


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
в дальнейшем я в чекбоксах буду выбирать

Где об этом было сказано хоть слово? Составьте пример, соответствующий задаче
[p.s.]И не надо дублировать каждый свой ответ в личку[/p.s.]

Автор - Pelena
Дата добавления - 23.11.2016 в 20:57
andruha912009 Дата: Среда, 23.11.2016, 21:10 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Во вложении обновленный пример.
Суть задачи: Если выбраны чекбоксы Поле2 и Поле4, исходная таблица должна фильтроваться по строкам, которые содержат единицы в 2 и 4 поле (операция ИЛИ)
Если выбраны чекбоксы Поле1 и Поле3 - должны отображаться 12, 14, 16 строки.
Макрос должен учитывать любую комбинацию выбранных чекбоксов.

Большое спасибо за помощь.
К сообщению приложен файл: 1890784.xlsm(23Kb)


Сообщение отредактировал andruha912009 - Среда, 23.11.2016, 22:24
 
Ответить
СообщениеPelena, Во вложении обновленный пример.
Суть задачи: Если выбраны чекбоксы Поле2 и Поле4, исходная таблица должна фильтроваться по строкам, которые содержат единицы в 2 и 4 поле (операция ИЛИ)
Если выбраны чекбоксы Поле1 и Поле3 - должны отображаться 12, 14, 16 строки.
Макрос должен учитывать любую комбинацию выбранных чекбоксов.

Большое спасибо за помощь.

Автор - andruha912009
Дата добавления - 23.11.2016 в 21:10
Pelena Дата: Среда, 23.11.2016, 22:46 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Посмотрите такой вариант
[vba]
Код
Private Sub Worksheet_Calculate()
    Dim rng As Range
    Application.ScreenUpdating = False
    Range("A6").CurrentRegion.EntireRow.Hidden = False
    If Not (Cells(1, 1) Or Cells(1, 2) Or Cells(1, 3) Or Cells(1, 4)) Then Exit Sub
    For i = Range("A6").CurrentRegion.Row + 1 To Range("A6").CurrentRegion.Row + Range("A6").CurrentRegion.Rows.Count - 1
        f = False
        For j = 1 To 4
            f = f Or Cells(1, j) And Cells(i, j) = 1
        Next j
        If Not f Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
    Next i
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub
[/vba]
К сообщению приложен файл: 5303562.xlsm(23Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПосмотрите такой вариант
[vba]
Код
Private Sub Worksheet_Calculate()
    Dim rng As Range
    Application.ScreenUpdating = False
    Range("A6").CurrentRegion.EntireRow.Hidden = False
    If Not (Cells(1, 1) Or Cells(1, 2) Or Cells(1, 3) Or Cells(1, 4)) Then Exit Sub
    For i = Range("A6").CurrentRegion.Row + 1 To Range("A6").CurrentRegion.Row + Range("A6").CurrentRegion.Rows.Count - 1
        f = False
        For j = 1 To 4
            f = f Or Cells(1, j) And Cells(i, j) = 1
        Next j
        If Not f Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
    Next i
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub
[/vba]

Автор - Pelena
Дата добавления - 23.11.2016 в 22:46
andruha912009 Дата: Среда, 23.11.2016, 22:50 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Супер, большущее вам спасибо!!!
 
Ответить
СообщениеPelena, Супер, большущее вам спасибо!!!

Автор - andruha912009
Дата добавления - 23.11.2016 в 22:50
andruha912009 Дата: Среда, 23.11.2016, 23:00 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, подскажите, пожалуйста, где находится макрос проставления единиц при активации флажков?
 
Ответить
СообщениеPelena, подскажите, пожалуйста, где находится макрос проставления единиц при активации флажков?

Автор - andruha912009
Дата добавления - 23.11.2016 в 23:00
Pelena Дата: Среда, 23.11.2016, 23:08 | Сообщение № 11
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Каждый флажок связан со своей ячейкой. Увидеть можно, кликнув правой кнопкой мыши по флажку -- Формат объекта -- Связь с ячейкой. А в оранжевых ячейках просто формулы, ссылающиеся на эти ячейки, чтобы возникало событие пересчёта, на которое повешен макрос


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеКаждый флажок связан со своей ячейкой. Увидеть можно, кликнув правой кнопкой мыши по флажку -- Формат объекта -- Связь с ячейкой. А в оранжевых ячейках просто формулы, ссылающиеся на эти ячейки, чтобы возникало событие пересчёта, на которое повешен макрос

Автор - Pelena
Дата добавления - 23.11.2016 в 23:08
andruha912009 Дата: Среда, 23.11.2016, 23:11 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Да, спасибо!
Я просто смотрю в свойствах назначения макроса к флажкам, и там числятся Флажок1_Щелчок, Флажок2_Щелчок и т.д, которых нигде не могу найти.
 
Ответить
СообщениеPelena, Да, спасибо!
Я просто смотрю в свойствах назначения макроса к флажкам, и там числятся Флажок1_Щелчок, Флажок2_Щелчок и т.д, которых нигде не могу найти.

Автор - andruha912009
Дата добавления - 23.11.2016 в 23:11
andruha912009 Дата: Четверг, 24.11.2016, 14:06 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Сейчас пытаюсь разобраться, как поведет себя этот макрос, если у меня будут использоваться дополнительные ручные фильтры.
В конце я добавил еще одно поле Месяц.
Есть две проблемы, которые мешают мне применить этот макрос к основной таблицы
1. Например, если кликаю по 4 флажку и после этого выбираю какое-то значение в фильтре Месяца, отбор сбивается.
2. Наоборот, выбираю значение в поле Месяц (201502), затем фильтрую, например, по 4 флажку -> работает, но когда я снимаю 4 флажок, те строки, которые относятся к Месяцу 201502 - обратно не отображаются.

Так же я разбирался с вашим макросом, где указывается условие его работы, когда ни одни из флажков не выбран - происходит выход из функции. Возможно, для исправления 2 пункта можно использовать другое действие - например отобразить принудительно все строки с учетом ручного выбраного фильтра.
Спасибо.
К сообщению приложен файл: 4514151.xlsm(24Kb)
 
Ответить
СообщениеPelena, Сейчас пытаюсь разобраться, как поведет себя этот макрос, если у меня будут использоваться дополнительные ручные фильтры.
В конце я добавил еще одно поле Месяц.
Есть две проблемы, которые мешают мне применить этот макрос к основной таблицы
1. Например, если кликаю по 4 флажку и после этого выбираю какое-то значение в фильтре Месяца, отбор сбивается.
2. Наоборот, выбираю значение в поле Месяц (201502), затем фильтрую, например, по 4 флажку -> работает, но когда я снимаю 4 флажок, те строки, которые относятся к Месяцу 201502 - обратно не отображаются.

Так же я разбирался с вашим макросом, где указывается условие его работы, когда ни одни из флажков не выбран - происходит выход из функции. Возможно, для исправления 2 пункта можно использовать другое действие - например отобразить принудительно все строки с учетом ручного выбраного фильтра.
Спасибо.

Автор - andruha912009
Дата добавления - 24.11.2016 в 14:06
Pelena Дата: Четверг, 24.11.2016, 15:59 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
andruha912009, что мешало Вам сразу озвучить все условия? Больше переделывать не буду.
Вариант с доп. столбцом (столбец А скрыт) и обычным фильтром
К сообщению приложен файл: 8225745.xlsm(25Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеandruha912009, что мешало Вам сразу озвучить все условия? Больше переделывать не буду.
Вариант с доп. столбцом (столбец А скрыт) и обычным фильтром

Автор - Pelena
Дата добавления - 24.11.2016 в 15:59
andruha912009 Дата: Четверг, 24.11.2016, 17:44 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Спасибо!
В этом варианте макроса вручную прописаны значения в колонке с месяцем?
То есть, если вместо 201501 был бы другой текст, макрос бы не срабатывал?

Спасибо.
 
Ответить
СообщениеPelena, Спасибо!
В этом варианте макроса вручную прописаны значения в колонке с месяцем?
То есть, если вместо 201501 был бы другой текст, макрос бы не срабатывал?

Спасибо.

Автор - andruha912009
Дата добавления - 24.11.2016 в 17:44
Pelena Дата: Четверг, 24.11.2016, 18:10 | Сообщение № 16
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Это не так


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЭто не так

Автор - Pelena
Дата добавления - 24.11.2016 в 18:10
andruha912009 Дата: Четверг, 24.11.2016, 19:23 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, могли бы вы описать принцип работы вашего макроса?
Я уже несколько раз пробовал изменить значения в дополнительных фильтрах, он не работает
Подскажите, пожалуйста, можно ли довать другие дополнительные поля в качестве ручных фильтров? Сориентируйте, пожалуйста, что нужно изменить, дальше я пойду сам.
Заранее благодарен.
 
Ответить
СообщениеPelena, могли бы вы описать принцип работы вашего макроса?
Я уже несколько раз пробовал изменить значения в дополнительных фильтрах, он не работает
Подскажите, пожалуйста, можно ли довать другие дополнительные поля в качестве ручных фильтров? Сориентируйте, пожалуйста, что нужно изменить, дальше я пойду сам.
Заранее благодарен.

Автор - andruha912009
Дата добавления - 24.11.2016 в 19:23
Pelena Дата: Четверг, 24.11.2016, 19:31 | Сообщение № 18
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
За фильтрацию полей с флажками отвечает доп. столбец А с формулой
Код
=ИЛИ($B$4:$E$4*($B7:$E7=1))
Если хотя бы один флажок поставлен, фильтруется по этому столбцу с критерием ИСТИНА. Если ни одного флажка нет, то фильтр с этого столбца сбрасывается. Все остальные дополнительные поля с фильтрами работают как обычно. Макрос их не затрагивает.

Что значит "не работает"?
Напоминаю, что в таблице не должно быть сквозных пустых строк Не актуально


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗа фильтрацию полей с флажками отвечает доп. столбец А с формулой
Код
=ИЛИ($B$4:$E$4*($B7:$E7=1))
Если хотя бы один флажок поставлен, фильтруется по этому столбцу с критерием ИСТИНА. Если ни одного флажка нет, то фильтр с этого столбца сбрасывается. Все остальные дополнительные поля с фильтрами работают как обычно. Макрос их не затрагивает.

Что значит "не работает"?
Напоминаю, что в таблице не должно быть сквозных пустых строк Не актуально

Автор - Pelena
Дата добавления - 24.11.2016 в 19:31
andruha912009 Дата: Четверг, 24.11.2016, 20:21 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, я изменил ваш макрос (адреса проверяемых ячеек), а так же добавил несколько фильтров. При вызове одного \ нескольких флажков таблица полностью скрывается :(
К сообщению приложен файл: 8225745_New.xlsm(26Kb)
 
Ответить
СообщениеPelena, я изменил ваш макрос (адреса проверяемых ячеек), а так же добавил несколько фильтров. При вызове одного \ нескольких флажков таблица полностью скрывается :(

Автор - andruha912009
Дата добавления - 24.11.2016 в 20:21
Pelena Дата: Четверг, 24.11.2016, 20:29 | Сообщение № 20
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Если Вы перенесли столбец Критерии с 1 на 9 место, то в макросе тоже надо было изменить.
И фильтр должен быть включен на всей таблице или хотя бы на столбце Критерии
К сообщению приложен файл: 4271193.xlsm(25Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЕсли Вы перенесли столбец Критерии с 1 на 9 место, то в макросе тоже надо было изменить.
И фильтр должен быть включен на всей таблице или хотя бы на столбце Критерии

Автор - Pelena
Дата добавления - 24.11.2016 в 20:29
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Изменение расширенного фильтра (макрос). (Формулы/Formulas)
Страница 1 из 212»
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!