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

Вход

Регистрация

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

 

= Мир MS Excel/Фильтр по значению в одном столбце (не могу найти алгоритм) - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Фильтр по значению в одном столбце (не могу найти алгоритм) (Макросы/Sub)
Фильтр по значению в одном столбце (не могу найти алгоритм)
Benos Дата: Пятница, 25.09.2020, 00:18 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013 / 2016 / 365
Добрый день, всем!
Для упрощения своей работы решил создать динамический чек лист(если можно так выразиться), но ни как не могу придумать алгоритм реализации.
Суть в следующем...
Есть сводная таблица с требованиями. Каждая строка отдельное требование (в одном столбце). Последующие столбцы, своего рода классификаторы (однозначное требование... "зеленое", "тонкое", "мое"... )
Через стандартный фильтр вручную фильтруем нужные нам значения и тем самым сужается список требований.
Но есть один столбец в котором указан диапазон...
Вида: "100 - 500" , "< 50", "Все", "300-700", "< 300" (набросал пример в приложении)
И получается, для того что бы отфильтровать диапазон, например от 10 до 400... нужно пролистывать фильтр и выбирать все возможные варианты... "100-500" + "< 50" + "Все" + "< 300" + "300 - 700" и т.д. так как искомый диапазон так или иначе пересекает, то что забито в фильтре...
Вариантов много и надо самому прикидывать пресекается ли твой диапазон с тем или иным чекбоксом в фильтре... тут велика вероятность ошибки (постоянно что то выпадает, либо не туда ткну мышкой и т.д.).

Смотрю в сторону VBA потому, что в дальнейшем планирую отсев отфильтрованного в отельную книгу...

Но честно скажу... соображений по реализации вообще ни каких.
Если кто может подкинуть идею или может у кого есть какие реализации...
Буду благодарен.

З.Ы. В приложении кода нет... не знаю с чего начать :)

К сообщению приложен файл: 7891010.zip (7.0 Kb)
 
Ответить
СообщениеДобрый день, всем!
Для упрощения своей работы решил создать динамический чек лист(если можно так выразиться), но ни как не могу придумать алгоритм реализации.
Суть в следующем...
Есть сводная таблица с требованиями. Каждая строка отдельное требование (в одном столбце). Последующие столбцы, своего рода классификаторы (однозначное требование... "зеленое", "тонкое", "мое"... )
Через стандартный фильтр вручную фильтруем нужные нам значения и тем самым сужается список требований.
Но есть один столбец в котором указан диапазон...
Вида: "100 - 500" , "< 50", "Все", "300-700", "< 300" (набросал пример в приложении)
И получается, для того что бы отфильтровать диапазон, например от 10 до 400... нужно пролистывать фильтр и выбирать все возможные варианты... "100-500" + "< 50" + "Все" + "< 300" + "300 - 700" и т.д. так как искомый диапазон так или иначе пересекает, то что забито в фильтре...
Вариантов много и надо самому прикидывать пресекается ли твой диапазон с тем или иным чекбоксом в фильтре... тут велика вероятность ошибки (постоянно что то выпадает, либо не туда ткну мышкой и т.д.).

Смотрю в сторону VBA потому, что в дальнейшем планирую отсев отфильтрованного в отельную книгу...

Но честно скажу... соображений по реализации вообще ни каких.
Если кто может подкинуть идею или может у кого есть какие реализации...
Буду благодарен.

З.Ы. В приложении кода нет... не знаю с чего начать :)

Автор - Benos
Дата добавления - 25.09.2020 в 00:18
Michael_S Дата: Пятница, 25.09.2020, 00:57 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Benos, не знаю, у кого как, но у нас счас ночь :) Ну и вам доброго дня!
Файл 7Kb - и в архив? Зачем?
Нарисуйте на отдельном листе, как вам видится результат. И не надо в архив!
 
Ответить
СообщениеBenos, не знаю, у кого как, но у нас счас ночь :) Ну и вам доброго дня!
Файл 7Kb - и в архив? Зачем?
Нарисуйте на отдельном листе, как вам видится результат. И не надо в архив!
Автор - Michael_S
Дата добавления - 25.09.2020 в 00:57
Benos Дата: Пятница, 25.09.2020, 23:36 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013 / 2016 / 365
Michael_S, просто не думал, что тут кто-то ночью как и я сидит :)
Собственно мозговой штурм родил такой подход...
[vba]
Код
Sub FouDIN()
    Dim DinMIN As Integer
    Dim DinMAX As Integer
    Dim iRowArr() As String
    Dim iCell As Long
    Dim iRowNum As String
    Dim j As Integer
    
    DinMIN = Cells(1, "C").Value ' Задаем минимальное значние для диапазона
    DinMAX = Cells(1, "D").Value ' Задамем максимальное значние для диапазона
    
    For iCell = 3 To Sheets("Tab").Cells(Rows.Count, "B").End(xlUp).Row ' перебор строк
        'Debug.Print Sheets("Tab").Cells(iCell, "B").Row
        iRowArr = Split(Cells(iCell, "B").Value, "&") ' каждую строку бью на массив -> получаю число из размерного ряда
        For j = LBound(iRowArr) To UBound(iRowArr) ' перебираю массив в поисках нужных значений
            'Debug.Print iRowArr(j)
            If DinMIN = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска нижнего порога выборки
            If DinMAX = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска поиск верхнего порога выборки
        Next j
            
    Next iCell
    ' получили строку с номерами строк, в которые попали в выборку
    Debug.Print iRowNum
End Sub
[/vba]
Коряво, но все что в голову пришло.
Принцип следующий...
1. Ввел через разделитель все размеры из диапазона
2. Проверяю есть ли среди размеров минимальный (нижняя граница выборки), аналогично для верхней
3. Если есть совпадение, значит строка попадает под мой диапазон
4. Заношу номер строки в "массив"(строку), для дальнейшей копии ее...

Вижу три минуса (как минимум):
1. В таблице есть столбец с "какой-то чушью" - столбец с типоразмерами
2. Есть сомнения, что проверяя наличие минимум и максимум в размерном ряде - не будет ошибок с отбором
3. В итоговой строке(iRowNum) с номерами строк для копирования... есть повторы... но это наверное можно исключить проверкой...

З.Ы. Не нашел функции для добавления элемента в массив... поэтому решил реализовать для через строку.
К сообщению приложен файл: 5014855.xlsm (19.3 Kb)


Сообщение отредактировал Benos - Пятница, 25.09.2020, 23:38
 
Ответить
СообщениеMichael_S, просто не думал, что тут кто-то ночью как и я сидит :)
Собственно мозговой штурм родил такой подход...
[vba]
Код
Sub FouDIN()
    Dim DinMIN As Integer
    Dim DinMAX As Integer
    Dim iRowArr() As String
    Dim iCell As Long
    Dim iRowNum As String
    Dim j As Integer
    
    DinMIN = Cells(1, "C").Value ' Задаем минимальное значние для диапазона
    DinMAX = Cells(1, "D").Value ' Задамем максимальное значние для диапазона
    
    For iCell = 3 To Sheets("Tab").Cells(Rows.Count, "B").End(xlUp).Row ' перебор строк
        'Debug.Print Sheets("Tab").Cells(iCell, "B").Row
        iRowArr = Split(Cells(iCell, "B").Value, "&") ' каждую строку бью на массив -> получаю число из размерного ряда
        For j = LBound(iRowArr) To UBound(iRowArr) ' перебираю массив в поисках нужных значений
            'Debug.Print iRowArr(j)
            If DinMIN = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска нижнего порога выборки
            If DinMAX = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска поиск верхнего порога выборки
        Next j
            
    Next iCell
    ' получили строку с номерами строк, в которые попали в выборку
    Debug.Print iRowNum
End Sub
[/vba]
Коряво, но все что в голову пришло.
Принцип следующий...
1. Ввел через разделитель все размеры из диапазона
2. Проверяю есть ли среди размеров минимальный (нижняя граница выборки), аналогично для верхней
3. Если есть совпадение, значит строка попадает под мой диапазон
4. Заношу номер строки в "массив"(строку), для дальнейшей копии ее...

Вижу три минуса (как минимум):
1. В таблице есть столбец с "какой-то чушью" - столбец с типоразмерами
2. Есть сомнения, что проверяя наличие минимум и максимум в размерном ряде - не будет ошибок с отбором
3. В итоговой строке(iRowNum) с номерами строк для копирования... есть повторы... но это наверное можно исключить проверкой...

З.Ы. Не нашел функции для добавления элемента в массив... поэтому решил реализовать для через строку.
Автор - Benos
Дата добавления - 25.09.2020 в 23:36
Benos Дата: Суббота, 26.09.2020, 00:52 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013 / 2016 / 365
неее... не правильно идет отбор... при разных вариантах не все попадает :)
[vba]
Код
            If DinMIN = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска нижнего порога выборки
            If DinMAX = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска поиск верхнего порога выборки
[/vba]
Тут условие не правильно установлено...


Сообщение отредактировал Benos - Суббота, 26.09.2020, 00:53
 
Ответить
Сообщениенеее... не правильно идет отбор... при разных вариантах не все попадает :)
[vba]
Код
            If DinMIN = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска нижнего порога выборки
            If DinMAX = iRowArr(j) Then iRowNum = iRowNum & " " & iCell 'поиска поиск верхнего порога выборки
[/vba]
Тут условие не правильно установлено...
Автор - Benos
Дата добавления - 26.09.2020 в 00:52
Pelena Дата: Суббота, 26.09.2020, 08:56 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Не совсем понятен принцип отбора. Например, если заданы значения мин=15 и макс=300, как в примере, то строка 100-500 удовлетворяет этому критерию? То есть диапазон полностью должен перекрывать критерий или частично?
И, на мой взгляд, если уж делать доп. столбцы, то удачнее вариант на Лист1, а не на листе Tab.
Тогда если ищем хотя бы частичное перекрытие диапазона и критерия, можно задать условие типа
если мин_критерий меньше Max (столбец на лист1) и макс_критерий больше Min (столбец на лист1), то строка подходит

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНе совсем понятен принцип отбора. Например, если заданы значения мин=15 и макс=300, как в примере, то строка 100-500 удовлетворяет этому критерию? То есть диапазон полностью должен перекрывать критерий или частично?
И, на мой взгляд, если уж делать доп. столбцы, то удачнее вариант на Лист1, а не на листе Tab.
Тогда если ищем хотя бы частичное перекрытие диапазона и критерия, можно задать условие типа
если мин_критерий меньше Max (столбец на лист1) и макс_критерий больше Min (столбец на лист1), то строка подходит
Автор - Pelena
Дата добавления - 26.09.2020 в 08:56
Benos Дата: Суббота, 26.09.2020, 15:21 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013 / 2016 / 365
Pelena, согласен.. сумбурно объяснил принцип отбора :)
Вы как всегда правы...
[vba]
Код
Sub FouDIN2()
    Dim DinMIN As Integer
    Dim DinMAX As Integer
    Dim iCell As Long
    Dim NumRow As String

        
    DinMIN = Sheets("Tab1").Cells(1, "B").Value
    DinMAX = Sheets("Tab1").Cells(1, "C").Value
    
    For iCell = 3 To Sheets("Tab1").Cells(Rows.Count, "B").End(xlUp).Row ' ïåðåáîð ñòðîê
        If DinMAX >= Sheets("Tab1").Cells(iCell, "B").Value And _
           DinMIN <= Sheets("Tab1").Cells(iCell, "C").Value Then _
           NumRow = NumRow & " " & iCell
    Next iCell
    Debug.Print NumRow
End Sub
[/vba]
То что надо :)

Цитата
если мин_критерий меньше Max (столбец на лист1) и макс_критерий больше Min (столбец на лист1), то строка подходит

Именно эту логику и не смог подобрать
 
Ответить
СообщениеPelena, согласен.. сумбурно объяснил принцип отбора :)
Вы как всегда правы...
[vba]
Код
Sub FouDIN2()
    Dim DinMIN As Integer
    Dim DinMAX As Integer
    Dim iCell As Long
    Dim NumRow As String

        
    DinMIN = Sheets("Tab1").Cells(1, "B").Value
    DinMAX = Sheets("Tab1").Cells(1, "C").Value
    
    For iCell = 3 To Sheets("Tab1").Cells(Rows.Count, "B").End(xlUp).Row ' ïåðåáîð ñòðîê
        If DinMAX >= Sheets("Tab1").Cells(iCell, "B").Value And _
           DinMIN <= Sheets("Tab1").Cells(iCell, "C").Value Then _
           NumRow = NumRow & " " & iCell
    Next iCell
    Debug.Print NumRow
End Sub
[/vba]
То что надо :)

Цитата
если мин_критерий меньше Max (столбец на лист1) и макс_критерий больше Min (столбец на лист1), то строка подходит

Именно эту логику и не смог подобрать
Автор - Benos
Дата добавления - 26.09.2020 в 15:21
Benos Дата: Воскресенье, 27.09.2020, 12:04 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013 / 2016 / 365
Добрый день, еще раз!
Снова уперся в стену :)
[vba]
Код
Sub FouDIN2()
    Dim DinMIN As Integer
    Dim DinMAX As Integer
    Dim iCell As Long
    Dim NumRow As String
    Dim NumRowAr() As String
            
    DinMIN = ActiveWorkbook.Sheets("Tab1").Cells(1, "B").Value
    DinMAX = ActiveWorkbook.Sheets("Tab1").Cells(1, "C").Value
    
    Set RNGS = ActiveWorkbook.Sheets("Tab1").AutoFilter.Range.Offset(1, 0).Resize(ActiveWorkbook.ActiveSheet.AutoFilter.Range.Rows.Count - 1, _
        ActiveWorkbook.ActiveSheet.AutoFilter.Range.Columns.Count).Columns(1).SpecialCells(xlCellTypeVisible)
    For Each iSet In RNGS
        If DinMAX >= ActiveWorkbook.Sheets("Tab1").Cells(iSet.Row, "B").Value And _
           DinMIN <= ActiveWorkbook.Sheets("Tab1").Cells(iSet.Row, "C").Value Then _
           NumRow = NumRow & "-" & iSet.Row
    Next
    Debug.Print NumRow
    NumRowAr = Split(NumRow, "-")
    For i = 0 To UBound(NumRowAr)
        If NumRowAr(i) <> "" Then
            ActiveWorkbook.Sheets("Tab1").Cells(NumRowAr(i), "A").Interior.Color = vbGreen
            Debug.Print ActiveWorkbook.Sheets("Tab1").Cells(NumRowAr(i), "A").Value
        End If
    Next i
    ActiveWorkbook.Sheets("Tab1").Columns("A").AutoFilter Field:=1, Criteria1:=Array(NumRowAr), Operator:=xlFilterValues
End Sub
[/vba]
Для того, что бы скопировать отфильтрованные строки. Фильтрую первый столбец по значению (так как знаю номер строки, определяю значение и подставляю его в массив и затем в критерий в автофильтре)
Но почему то фильтруется только первая строка?
По выборке должно быть 2 строки (которые подсвечены Зеленым).
К сообщению приложен файл: 8104671.xlsm (21.5 Kb)
 
Ответить
СообщениеДобрый день, еще раз!
Снова уперся в стену :)
[vba]
Код
Sub FouDIN2()
    Dim DinMIN As Integer
    Dim DinMAX As Integer
    Dim iCell As Long
    Dim NumRow As String
    Dim NumRowAr() As String
            
    DinMIN = ActiveWorkbook.Sheets("Tab1").Cells(1, "B").Value
    DinMAX = ActiveWorkbook.Sheets("Tab1").Cells(1, "C").Value
    
    Set RNGS = ActiveWorkbook.Sheets("Tab1").AutoFilter.Range.Offset(1, 0).Resize(ActiveWorkbook.ActiveSheet.AutoFilter.Range.Rows.Count - 1, _
        ActiveWorkbook.ActiveSheet.AutoFilter.Range.Columns.Count).Columns(1).SpecialCells(xlCellTypeVisible)
    For Each iSet In RNGS
        If DinMAX >= ActiveWorkbook.Sheets("Tab1").Cells(iSet.Row, "B").Value And _
           DinMIN <= ActiveWorkbook.Sheets("Tab1").Cells(iSet.Row, "C").Value Then _
           NumRow = NumRow & "-" & iSet.Row
    Next
    Debug.Print NumRow
    NumRowAr = Split(NumRow, "-")
    For i = 0 To UBound(NumRowAr)
        If NumRowAr(i) <> "" Then
            ActiveWorkbook.Sheets("Tab1").Cells(NumRowAr(i), "A").Interior.Color = vbGreen
            Debug.Print ActiveWorkbook.Sheets("Tab1").Cells(NumRowAr(i), "A").Value
        End If
    Next i
    ActiveWorkbook.Sheets("Tab1").Columns("A").AutoFilter Field:=1, Criteria1:=Array(NumRowAr), Operator:=xlFilterValues
End Sub
[/vba]
Для того, что бы скопировать отфильтрованные строки. Фильтрую первый столбец по значению (так как знаю номер строки, определяю значение и подставляю его в массив и затем в критерий в автофильтре)
Но почему то фильтруется только первая строка?
По выборке должно быть 2 строки (которые подсвечены Зеленым).
Автор - Benos
Дата добавления - 27.09.2020 в 12:04
Pelena Дата: Воскресенье, 27.09.2020, 19:17 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Вы формируете массив NumRowAr из номеров строк, а для фильтра надо, чтобы он содержал значения ячеек.
Исправьте строку
[vba]
Код
NumRow = NumRow & "-" & iset.Row
[/vba]
на
[vba]
Код
NumRow = NumRow & "-" & iset.Value
[/vba]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВы формируете массив NumRowAr из номеров строк, а для фильтра надо, чтобы он содержал значения ячеек.
Исправьте строку
[vba]
Код
NumRow = NumRow & "-" & iset.Row
[/vba]
на
[vba]
Код
NumRow = NumRow & "-" & iset.Value
[/vba]
Автор - Pelena
Дата добавления - 27.09.2020 в 19:17
Gustav Дата: Среда, 30.09.2020, 23:02 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2731
Репутация: 1132 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
А ведь задачку вполне можно решить и на формулах, не прибегая к VBA. Например, можно воспользоваться идеями для решения задачи о пересечении интервалов дат, взятыми отсюда.

Мне там понравилось решение от MCH (см. в комментариях), от которого я и буду отталкиваться. Суть решения - в проверке значения формулы
Код
МИН(из_Концов_интервалов)-МАКС(из_Начал_интервалов)

Если значение формулы неотрицательное, то диапазоны пересекаются (в частном случае при =0 диапазоны касаются в точке). Следующая формула (для 3-й строки) будет возвращать значение ИСТИНА, если диапазоны пересекаются, и ЛОЖЬ - в противном случае:
Код
=МИН($C$1;C3)-МАКС($B$1;B3)>=0

Эту формулу можно протянуть вниз, распространив на все строки таблицы, после чего в автофильтре для столбца с этой формулой использовать значение ИСТИНА для отбора пересекающихся диапазонов.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеА ведь задачку вполне можно решить и на формулах, не прибегая к VBA. Например, можно воспользоваться идеями для решения задачи о пересечении интервалов дат, взятыми отсюда.

Мне там понравилось решение от MCH (см. в комментариях), от которого я и буду отталкиваться. Суть решения - в проверке значения формулы
Код
МИН(из_Концов_интервалов)-МАКС(из_Начал_интервалов)

Если значение формулы неотрицательное, то диапазоны пересекаются (в частном случае при =0 диапазоны касаются в точке). Следующая формула (для 3-й строки) будет возвращать значение ИСТИНА, если диапазоны пересекаются, и ЛОЖЬ - в противном случае:
Код
=МИН($C$1;C3)-МАКС($B$1;B3)>=0

Эту формулу можно протянуть вниз, распространив на все строки таблицы, после чего в автофильтре для столбца с этой формулой использовать значение ИСТИНА для отбора пересекающихся диапазонов.
Автор - Gustav
Дата добавления - 30.09.2020 в 23:02
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Фильтр по значению в одном столбце (не могу найти алгоритм) (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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