Как скопировать данные в эксель с фильтрами

Как скопировать данные в эксель с фильтрами

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

• Сохраняйте отфильтрованные данные на новый лист с функциями копирования и вставки
• Сохраняйте отфильтрованные данные на новый лист с помощью функции копирования и перемещения

Сохраняйте отфильтрованные данные на новый лист с функциями копирования и вставки

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

Ноты:
(1) Нажатие Shift + F11 одновременно с клавишами можно также создать новый рабочий лист.
(2) Если вы собираетесь сохранить отфильтрованные данные в новую книгу, просто нажмите Ctrl + N клавиши одновременно, чтобы создать новую книгу.

2. Выберите отфильтрованные данные и скопируйте их, нажав Ctrl + C одновременно.

3. Перейдите к новому листу или книге, выберите пустую ячейку и нажмите Ctrl + V одновременно клавиши, чтобы вставить отфильтрованные данные.

Затем вы увидите, что только отфильтрованные данные копируются и вставляются в новый рабочий лист / книгу. Однако высота строки и ширина столбца теряются. См. Снимки экрана ниже:

Простое разделение диапазона на основе критериев в столбце и сохранение разделенных диапазонов на новых новых листах

Kutools for ExcelАвтора Разделить данные Утилита может помочь пользователям Excel легко разделить диапазон на несколько листов на основе критериев в одном столбце исходного диапазона.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

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

Если отфильтрованные данные слишком велики, чтобы их можно было легко выбрать и скопировать, и вы настроили ширину столбцов и высоту строк для многих столбцов и строк, копирование и вставка может быть не лучшим решением. В этих условиях следует попробовать Kutools for Excel’s Удалить скрытые (видимые) строки и столбцы утилита.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

1. Щелкните правой кнопкой мыши имя листа, который содержит отфильтрованные данные, на панели вкладок листа и выберите Переместить или скопировать из контекстного меню.

Шаг 2. Во всплывающем диалоговом окне «Перемещение или копирование» укажите книгу в Переместить выбранные листы в книгу раскрывающийся список, проверьте Создать копию и нажмите OK кнопку.

Внимание: Чтобы сохранить отфильтрованные данные на новом листе, выберите имя текущей книги в Переместить выбранные листы в книгу раскрывающийся список; чтобы сохранить отфильтрованные данные в новую книгу, выберите (новая книга).

3. Теперь вы попадете в новый рабочий лист / книгу, нажмите Kutools > Удалить > Удалить скрытые (видимые) строки и столбцы, а затем в открывшемся диалоговом окне

(1) Выберите В активном листе в Заглянуть раскрывающийся список;
(2) Проверить Ряды вариант в Удалить тип раздел;
(3) Проверить Скрытые строки вариант в Подробный тип раздел;
(4) Щелкните значок Ok кнопку.

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

Теперь вы увидите, что все отфильтрованные данные зарезервированы на новом листе, а все форматирование ячеек, включая высоту строк и ширину столбцов, сохраняется.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

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

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

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

Если вам нужно вставить одни и те же значения во все отфильтрованные строки списка, то все просто. Предположим, что у нас есть вот такой список сделок:

. и в нем нужно поставить фиксированную скидку в 1000 рублей каждому "Ашану".

Фильтруем наш список Автофильтром, оставляя на экране только "Ашаны". Вводим нужное значение в первую ячейку и протягиваем (копируем за правый нижний угол ячейки) вниз:

Умный Excel в данном случае понимает, что вы хотите ввести значения именно в отфильтрованные ячейки и делает то, что нужно:

Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для "Ашанов" не фиксированная, а составляет 10% от суммы сделки, то в первую отфильтрованную строку можно ввести не константу (1000), а формулу (=C2*10%) и также скопировать вниз.

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

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

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

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

Можно еще немного проще:
1. отфильтровать
2. выделить весь столбец — F5 — Выделить — Только видимые
3. не снимая выделения, ввести формулу в первую ячейку и нажать Ctrl+Enter

Спасибо за полезное уточнение

Позволю себе выложить здесь ссылку «в тему»(надеюсь Николай не будет против): Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
Достаточно давно написал код, который выложен в статье. Можно скопировать только видимые ячейки и вставить так же только в видимые. Могут быть скрыты как строки, так и столбцы. В общем совершенно без разницы что и как скрыто, вставлено будет в то, что видно

Я только «за», спасибо — ценная штука!

Не за что, Самвел

Спасибо Вам большое! Ваши «ПРИЕМЫ» спасает огромное количество рабочих часов!
Повесила макрос на горячую клавишу и красота
Вопрос.
1. Как провести копирование данных из одного фильтрованного диапазона в аналогичных по размерам другой?
2. Как сохранить макрос так, чтоб его можно было использовать в других открытых\созданных файлах, чтоб он стал «постоянным»?

1. Вот так:
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.Cells.Cells.Count <> copyrng.Cells.Count Then
MsgBox «Диапазоны копирования и вставки разного размера!»,vbCritical
Exit Sub
End If

‘переносим данные из одного диапазона в другой только в видимые ячейки
For Each cell In pasterng
If cell.EntireRow.Hidden = False Then
cell.Value = Cells(cell.Row, copyrng.Column).Value
End If
Next cell
End Sub

Николай Павлов, большое спасибо за макрос! Я, хоть и сталкивалась с excel, но с макросами дела не имела. А тут понадобилось в большущую таблицу выгрузки сайта, фильтруя ее значения, вставлять не цифры и формулы, а текст, уникальные названия товаров. Копировать значения ячеек по одному совершенно нереально, долго и запутаешься. А с макросом (диапазон вставки и копирования были на разных листах одной книги) все получилось, пока с одним значением фильтра. Но встретились в конце такая закавыка.
Фильтр сняла, вернее, поставила галочки у всех значений, как у вас в видео. Здесь было все нормально, все вернулось к прежнему виду, правда, я не проверила всю портянку. Только вот почему-то после нажатия кнопки сохранить мне выпало окошко — как будто ошибка. Там написано:

«выгрузка.csv» может содержать возможности несовместимые с форматом «CSV( разделители — запятые)». Сохранить книгу в этом формате?
Чтобы сохранить этот формат, удалив все несовместимые возможности, нажмите кнопку Да.
Чтобы сохранить все возможности, нажмите кнопку нет и сохраните файл в формате последней версии excel.
Чтобы узнать, какие возможности могут быть потеряны, нажмите кнопку Справка.

И вот я теперь не знаю, на какую кнопку нажимать, и что за возможности потеряются. Файл выгрузки большой — столбцов много, мне можно менять значения только одного столбца. Подскажите, что значит это сообщение?

Задание 4. Копирование результатов фильтрации на другой лист

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

ПРИМЕР 3. Выберите из таблицы страны, имеющие плотность населения меньше 10 чел на км 2 ИЛИ больше 100 чел. на км 2 .

Создайте новый лист — Лист7.

Заголовки критериев поиска скопируйте в свободное место на листе с таблицей (вернитесь на лист Расширенный), например, I2.

Заполните строки критериев (в I2 и I3)

Заголовки интересующих полей скопируйте в свободное место на новом листе — Листе7, например, В3.

Выберите любую ячейку Листа7, на котором будут находиться отфильтрованные данные.

Выберите команду ДАННЫЕ — кнопка Дополнительно.

Укажите в диалоговом окне Расширенный фильтр следующие параметры:

Обработка — Скопировать результат в другое место.

Исходный диапазон — перейдите на лист с таблицей и выделите ее всю.

Диапазон условий — как только вы поставите текстовый курсор в поле Диапазон условий, так сразу же; окажетесь на листе, где будут находиться результаты фильтрации (Лист7).

Перейдите на лист с исходными данными — выделите область критериев.

Поместить результат в диапазон — поставьте текстовый курсор в поле и снова окажетесь на листе результатов фильтрации — выделите скопированные ранее заголовки целевой области (В3:С3).

Нажмите ОК. Получаем результат в целевой области.

Переименуйте Лист7 в лист Копирование результатов. Сохраните книгу Страны в своей папке.

Задание 5. Связанные таблицы

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

Создание связанной таблицы

Откройте файл-документСвязанные таблицы.xlsxиз папкиЗадания по Excel 2. Сохраните файл в своей папке.

Преобразуйте диапазон данныхв таблице всвязанную таблицу. Для этого выделите таблицу и выберите командуВСТАВКА — группа Таблицы — Таблица. Появится диалоговое окноСвязанные таблицы.

Поставьте флажок Таблица с заголовками, если выбранный диапазон содержит данные для заголовков столбцов, и нажмитеOK. Иначе каждому столбцу будут автоматически присвоены названияСтолбец1,Столбец2и т.д.

Диапазон ячеек таблицы стал связанной таблицей. На Инструментальной ленте появится контекстная вкладкаРАБОТА С ТАБЛИЦАМИ—Конструктор.

Примечание. Другой вариант создания связанной таблицы — выбрать команду ГЛАВНАЯ — группа Стили — Форматировать как таблицу.

Для того, чтобы обратно преобразовать связанную таблицу в диапазон ячеек нужно выделить любую ячейку таблицы и выбрать команду РАБОТА С ТАБЛИЦАМИ — Конструктор — преобразовать в диапазон.

Добавление итогов

В связанной таблице легко подсчитать итоговые значения.

Вставьте строку итоговпосле последней строки связанной таблицы. Для этого выделите любую ячейку таблицы и выберите командуРАБОТА С ТАБЛИЦАМИ — Конструктор — группа Параметры стилей таблиц — Строка итогов. В конце таблицы будет добавлена строка с итоговыми данными.

Воспользовавшись выпадающим меню можно выбрать значение итоговой функции.

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

Выберите функциюСуммаиз раскрывающегося списка в стоке итогов.

Как скопировать данные в эксель с фильтрами

Всем привет.
Вопрос, наверное, совсем странный, но решение не нагуглилось.

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

Что пробовал:
1. Макросы из сети — не завелись почему-то.
2. Выделить диапазон идентичный копируемого, нажать Alt+; (выделить только видимые ячейки) и вставить — выдает ошибку «данная команда не применима для нескольких диапазонов».

Пример во вложении.

Всем привет.
Вопрос, наверное, совсем странный, но решение не нагуглилось.

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

Что пробовал:
1. Макросы из сети — не завелись почему-то.
2. Выделить диапазон идентичный копируемого, нажать Alt+; (выделить только видимые ячейки) и вставить — выдает ошибку «данная команда не применима для нескольких диапазонов».

Пример во вложении.

Сообщение Всем привет.
Вопрос, наверное, совсем странный, но решение не нагуглилось.

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

Что пробовал:
1. Макросы из сети — не завелись почему-то.
2. Выделить диапазон идентичный копируемого, нажать Alt+; (выделить только видимые ячейки) и вставить — выдает ошибку «данная команда не применима для нескольких диапазонов».

Пример во вложении.

Спасибо! Автор — Logotip
Дата добавления — 13.04.2018 в 11:22