Как скопировать фильтр в excel

Переместить фильтр

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

В ней отфильтрованы данные по трем столбцам: Контрагент, Отсрочка платежа дни, Штрафы за просрочку оплаты. При этом из более чем 80-ти контрагентов отобрано фильтром только 25 необходимых, для отсрочки платежа установлено условие не показывать строки с просрочкой 25 дней и менее, а для штрафов — не показывать строки с суммами меньше или равные 1000р.
Ситуация1: точно такие же условия надо применить к такой же таблице, но за другие периоды. Вручную придется в другой таблице заново выбирать всех контрагентов и проставлять условия на другие столбцы.
Ситуация2: в таблицу необходимо добавить еще один столбец — Условия предоставления кредитного лимита. И поверх прежних условий фильтрации применить фильтр еще и по новому столбцу. Вручную придется сначала полностью убрать фильтр с листа, добавить столбец, установить фильтр заново и прописать все условия. Никак по-другому не получится(если только речь не об умной таблице — Вставка -Таблица).
С помощью команды Переместить фильтр обе ситуации становятся решаемыми очень просто. Все, что необходимо — выделить диапазон с нужным фильтром, перейти на вкладку MulTEx -группа Ячейки/Диапазоны —Диапазоны —Переместить фильтр:

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

И распространить эти условия на диапазон: указывается диапазон ячеек вместе с заголовком, к которому необходимо применить скопированные условия фильтрации.
Если столбцов в новом диапазоне меньше, чем в скопированном фильтре — то в новую таблицу будут перенесены условия первых n столбцов скопированного фильтра, где n — количество столбцов в новой таблице. Если столбцов больше — фильтр будет установлен на все указанные столбцы.
Если условия скопированного фильтра необходимо распространить на умную таблицу(Вставка (Insert) —Таблица (Table) ), то в поле И распространить эти условия на диапазон допускается указать одну любую ячейку внутри этой таблицы. Границы такой таблицы будут определены автоматически.

Расскажи друзьям, если статья оказалась полезной:

Видеоинструкции по использованию надстройки MulTEx

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

покупка

Как скопировать данные на другой лист с помощью расширенного фильтра в Excel?

Обычно мы можем быстро применить Расширенный фильтр функция извлечения данных из необработанных данных на одном листе. Но иногда, когда вы пытаетесь скопировать отфильтрованный результат на другой рабочий лист, вы получаете следующее предупреждающее сообщение. В таком случае, как бы вы могли справиться с этой задачей в Excel?

Скопируйте данные на другой лист с помощью расширенного фильтра с помощью функции расширенного фильтра

Вот простой способ решения этой задачи: сделайте следующее:

1. Активируйте рабочий лист, на который вы хотите скопировать отфильтрованный результат, с помощью функции расширенного фильтра.

2. Затем нажмите Данные > Фильтр, см. снимок экрана:

3. В Расширенный фильтр диалоговое окно, выберите Скопировать в другое место из Действие раздел, а затем щелкните кнопку, чтобы выбрать диапазон данных, который вы хотите отфильтровать, на другом листе рядом с Диапазон списка, продолжайте нажимать кнопка рядом с Диапазон критериев чтобы выбрать критерии из другого листа, см. снимок экрана:

4. Затем нажмите кнопка рядом с Скопировать в поле, чтобы выбрать ячейку, в которую вы хотите вывести отфильтрованный результат из активного листа, а затем щелкните OK Кнопка, отфильтрованный результат был извлечен на другой лист на основе вашей исходной таблицы данных.

Скопируйте данные на другой лист с помощью расширенного фильтра с помощью кода VBA

Если у вас есть опыт работы с кодом VBA, вы можете завершить эту работу с помощью следующего кода VBA.

1. Активируйте рабочий лист, который вы хотите отфильтровать, и скопируйте результат на другой лист.

2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.

3. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: скопируйте данные на другой лист с помощью расширенного фильтра:

 Sub Advancedfiltertoanothersheet() 'Updateby Extendoffice Dim xStr As String Dim xAddress As String Dim xRg As Range Dim xCRg As Range Dim xSRg As Range On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select the filter range:", "Kutools for Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub Set xCRg = Application.InputBox("Please select the criteria range:", "Kutools for Excel", "", , , , , 8) If xCRg Is Nothing Then Exit Sub Set xSRg = Application.InputBox("Please select the output range:", "Kutools for Excel", "", , , , , 8) If xSRg Is Nothing Then Exit Sub xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False xSRg.Worksheet.Activate xSRg.Worksheet.Columns.AutoFit End Sub 

4, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, позволяющее выбрать диапазон данных, который вы хотите отфильтровать, см. снимок экрана:

5. Затем нажмите OK, и появляется другое диалоговое окно, напоминающее вам о выборе диапазона критериев, на основе которого вы хотите выполнить фильтрацию, см. снимок экрана:

6. Продолжайте нажимать OK, в появившемся диалоговом окне выберите ячейку на другом новом листе, в которой вы хотите вывести результат фильтрации, см. снимок экрана:

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

Вставка в отфильтрованные строки

Весьма распространенная ситуация, вопрос про которую мне задают почти на каждом тренинге. Есть таблица, в которой Фильтром (Данные — Фильтр) отобраны несколько строк. Задача — вставить какие-либо нужные нам значения именно в видимые отфильтрованные строки, пропуская при этом скрытые. Обычное копирование-вставка при этом не сработает, т.к. данные вставятся не только в видимые, но и в скрытые ячейки. Давайте посмотрим, как можно обойти эту проблему.

Способ 1. Вставка одинаковых значений или формул

Если вам нужно вставить одни и те же значения во все отфильтрованные строки списка, то все просто. Предположим, что у нас есть вот такой список сделок: . и в нем нужно поставить фиксированную скидку в 1000 рублей каждому «Ашану». Фильтруем наш список Автофильтром, оставляя на экране только «Ашаны». Вводим нужное значение в первую ячейку и протягиваем (копируем за правый нижний угол ячейки) вниз: Умный Excel в данном случае понимает, что вы хотите ввести значения именно в отфильтрованные ячейки и делает то, что нужно: Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для «Ашанов» не фиксированная, а составляет 10% от суммы сделки, то в первую отфильтрованную строку можно ввести не константу (1000), а формулу (=C2*10%) и также скопировать вниз.

Способ 2. Макрос вставки любых значений

Другое дело, если вам необходимо вставить в отфильтрованные ячейки не одинаковые значения или формулы, а разные, да еще и брать их из другого диапазона. Тогда придется использовать несложный макрос. Нажмите сочетание клавиш Alt+F11, в открывшемся окне Visual Basic вставьте новый пустой модуль через меню Insert — Module и скопируйте туда этот код:

Sub PasteToVisible() Dim copyrng As Range, pasterng As Range Dim cell As Range, i As Long 'запрашиваем у пользователя по очереди диапазоны копирования и вставки Set copyrng = Application.InputBox("Диапазон копирования", "Запрос", Type:=8) Set pasterng = Application.InputBox("Диапазон вставки", "Запрос", Type:=8) 'проверяем, чтобы они были одинакового размера If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <> copyrng.Cells.Count Then MsgBox "Диапазоны копирования и вставки разного размера!",vbCritical Exit Sub End If 'переносим данные из одного диапазона в другой только в видимые ячейки i = 1 For Each cell In pasterng If cell.EntireRow.Hidden = False Then cell.Value = copyrng.Cells(i).Value i = i + 1 End If Next cell End Sub

Как легко сообразить, макрос запрашивает у пользователя по очереди два диапазона — копирования и вставки. Затем проверяет, чтобы их размеры совпадали, т.к. разница в размерностях вызовет впоследствии ошибку при вставке. Затем макрос перебирает все ячейки в диапазоне вставки и переносит туда данные из диапазона копирования, если строка видима (т.е. не отфильтрована).

Ссылки по теме

Как скопировать отфильтрованные строки в новый Excel файл

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

Как макрос работает

Этот макрос захватывает диапазон AutoFilter, открывает новую книгу, а затем вставляет данные.

Код макроса

Sub SkopirovatOtfiltrovannieStroki() 'Шаг 1: Проверить, есть ли на листе фильтр If ActiveSheet.AutoFilterMode = False Then Exit Sub End If 'Шаг 2: Скопируйте отфильтрованный диапазон для новой книги ActiveSheet.AutoFilter.Range.Copy Workbooks.Add.Worksheets(1).Paste 'Шаг 3: Столбцы приводим в соответствие по размеру Cells.EntireColumn.AutoFit End Sub

Как этот код работает

1. Шаг 1 использует свойство AutoFilterMode, чтобы проверить есть ли на листе автофильтры. Если нет, то мы выходим из процедуры.
2. Каждый объект AutoFilter имеет свойство Range. Это свойство Range возвращает строки, к которым применяется Автофильтр, то есть он возвращает только те строки, которые отображаются в отфильтрованном наборе данных. На шаге 2 мы используем метод копирования, чтобы захватить эти строки, а затем вставить строки в новую книгу. Обратите внимание, что мы используем Workbooks.Add.Worksheets, это говорит Excel вставить данные в первый лист вновь созданной книги.
3. Шаг 3 говорит Excel, чтобы размер столбцов соответствовал данным, которые мы только что вставили.

Как использовать

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
3. Выберите Insert➜Module.
4. Введите или вставьте код.