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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Изменение расширенного фильтра (макрос). (Формулы/Formulas)
Изменение расширенного фильтра (макрос).
andruha912009 Дата: Среда, 23.11.2016, 20:17 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 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 (19.2 Kb)
 
Ответить
СообщениеДобрый день!
Уважаемые коллеги, прочив тему 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
Группа: Админы
Ранг: Местный житель
Сообщений: 19157
Репутация: 4411 ±
Замечаний: ±

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


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

Автор - Pelena
Дата добавления - 23.11.2016 в 20:35
andruha912009 Дата: Среда, 23.11.2016, 20:43 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 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
Группа: Админы
Ранг: Местный житель
Сообщений: 19157
Репутация: 4411 ±
Замечаний: ±

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


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

Автор - Pelena
Дата добавления - 23.11.2016 в 20:44
andruha912009 Дата: Среда, 23.11.2016, 20:52 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 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
Группа: Админы
Ранг: Местный житель
Сообщений: 19157
Репутация: 4411 ±
Замечаний: ±

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

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


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

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

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

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

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


Сообщение отредактировал 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
Группа: Админы
Ранг: Местный житель
Сообщений: 19157
Репутация: 4411 ±
Замечаний: ±

Excel 365 & Mac Excel
Посмотрите такой вариант
[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 (22.7 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 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
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

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

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


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

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

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

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

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

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

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

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

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


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

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

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

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

Спасибо.

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

Excel 365 & Mac Excel
Это не так


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

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

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

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

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

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


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

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

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

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

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

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


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

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

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