Как вставить значение строки автоматически на основе другого значения ячейки

Question

Сначала посмотрите этот лист Excel:

Таблица месяца содержит данные за весь год. Если я выберу любой месяц из таблицы, набрав или выбрав из выпадающего меню (например, "Выбрано"), то после определенных строк будут отображаться только эти строки. Как, например, я хочу, чтобы выбранные строки появлялись после строк 14 и далее (т.е. строки не будут появляться перед строкой 15). Как этого добиться?

К вашему сведению, количество выбранных строк может отличаться. Например, в приведенной выше таблице выбраны две строки (апрель и сентябрь). Но это может быть март, апрель и сентябрь. Поэтому я не могу указать диапазон ячеек, в котором будут отображаться выбранные строки. Также будет текст на 17-й строке. Таким образом, выбранные строки должны быть вставлены динамически после строк 14.

Есть идеи как этого добиться?

Answer 1

Я, вероятно, хотел бы использовать сводную таблицу. Месяц будет идти в КОЛОННАХ, в то время как B, C & D в ЗНАЧЕНИЯХ (это должно, по сути, сделать таблицу более или менее похожей на вашу). Затем параметр выбора нужно поместить в ФИЛЬТРЫ, чтобы показать только то, что вы хотите.

Плохая вещь в том, что каждой команде, в которой вы хотите, чтобы изменения отображались, вам нужно обновлять сводную таблицу (RMB и Refresh или через Analyze; в качестве альтернативы вы можете использовать макрос, чтобы делать это периодически для вас).

Answer 2

Шаг 1 - Список

Сначала вы можете добавить "Выбранную" часть с выпадающим списком.
Выбрав E1 , перейдите на вкладку « Данные » и выберите « Проверка данных». Выберите List и напишите Selected as Source.
Теперь перетащите E1 вниз на E12 чтобы получить список всех ячеек.

Шаг 2 - Код

Добавление строк
Для отслеживания этих ячеек изменение Sub Worksheet_Change прекрасно работает. Щелкните правой кнопкой мыши свой "Лист1" (или эквивалент) и выберите « Показать код».

Чтобы отслеживать и копировать выбранные строки, мы можем сделать что-то вроде этого:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, r As Integer
On Error GoTo EndM
If Target.Value = "Selected" And Target.Column = 5 And Target.Row < 13 Then
    For r = 15 To 30
        If Cells(r, 1).Value = "" Then
            Range("A" & r & ":D" & r).Cells.Value = Range("A" & Target.Row & ":D" & Target.Row).Cells.Value
            i = 1
        End If
        If i > 0 Then GoTo EndM
    Next r
End If
EndM:
End Sub

Это ищет изменения в листе. И если изменить любую ячейку с E1 на E12 , измененную на "select", то она копирует столбцы A D той же строки в первую пустую строку, начиная с строки 15.
Заметка
Это не мешает вам выбрать одну и ту же запись дважды, то есть получить две строки января. Также установлено ограничение до 30 строки. После строки 30 он не будет добавлять больше записей. Можно изменить, изменив значение 30 в For r = 15 To 30

Удаление строк
Мы можем использовать в основном один и тот же метод, чтобы снова удалить строки, когда мы удаляем "Выбранное" значение, добавляя

If Target.Column = 5 And Target.Row < 13 And Target.Value = "" Then
    For r = 15 To 30
        If Cells(r, 1).Value = Cells(Target.Row, 1) Then
            Range("A" & r & ":D" & r).Cells.Delete
        End If
    Next r
End If

Между End If и EndM: в предыдущем примере.

Обработчик ошибок необходим для того, чтобы лист не выдавал ошибку при каждом удалении нескольких ячеек одновременно.
Заметка
Это не запустится, если вы удалите несколько ячеек одновременно, и не удалит каждую строку, если один и тот же месяц присутствует дважды подряд.

Конечно, вы можете добавить или изменить функциональность, например, ограничить добавление нескольких записей, добавив:

            ElseIf Cells(r, 1).Value = Cells(.Row, 1).Value Then
            MsgBox ("Multiple entries are not allowed!")
            GoTo EndM

Между i = 1 и End If в части добавления.
Удачи!