Как скопировать значения в отфильтрованную таблицу эксель

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

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

При копировании и вставке видимых ячеек в диапазоне данных, который содержит скрытые ячейки или к которому применен фильтр, можно заметить, что скрытые ячейки вставляются вместе с видимыми. К сожалению, это нельзя изменить при копировании и вклеии диапазона ячеек в Excel в Интернете так как возможность в виде вметки только видимых ячеек недоступна.

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

Если не нужно форматировать данные как таблицу и установлено классическое приложение Excel, можно открыть книгу в нем, чтобы скопировать и вставить видимые ячейки. Для этого нажмите кнопку Открыть в Excel и выполните действия, которые можно сделать в окте Копирование и вкопка только видимых ячеек.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как скопировать отфильтрованные строки в новый 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. Введите или вставьте код.

покупка

Как вставить значения в видимые / отфильтрованные ячейки только в Excel?

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

Вставьте значения в видимые ячейки с помощью Вставить в видимый диапазон Kutools for Excel

Вставить значения в видимые ячейки с помощью VBA

В Excel нет других способов решить эту проблему, кроме VBA.

1. Нажмите Alt + F11 одновременно, а Microsoft Visual Basic для приложений окно выскакивает.

2. Нажмите Вставить > Модули, затем вставьте ниже код VBA в всплывающее окно модуля.

VBA: вставлять значения только в видимые ячейки.

Sub CopyFilteredCells() 'Updateby20150203 Dim rng1 As Range Dim rng2 As Range Dim InputRng As Range Dim OutRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Copy Range :", xTitleId, InputRng.Address, Type: = 8) Set OutRng = Application.InputBox("Paste Range:", xTitleId, Type: = 8) For Each rng1 In InputRng rng1.Copy For Each rng2 In OutRng If rng2.EntireRow.RowHeight > 0 Then rng2.PasteSpecial Set OutRng = rng2.Offset(1).Resize(OutRng.Rows.Count) Exit For End If Next Next Application.CutCopyMode = False End Sub

3. Нажмите F5 ключа или Run , затем появится диалоговое окно, в котором вы можете выбрать значения для копирования. Смотрите скриншот:

4. После выбора значений щелкните OK, затем появится другое диалоговое окно для выбора диапазона для вставки выбранных данных. Смотрите скриншот:

5. Нажмите OK. Вы можете видеть только видимые ячейки, в которые вставлены выбранные значения.

Вставьте значения в видимые ячейки с помощью Вставить в видимый диапазон Kutools for Excel

Собственно, если вы используете Вставить в видимый диапазон полезности Kutools for Excel — удобный инструмент Excel, вы можете быстро и легко решить проблему вставки значений в видимый диапазон.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите диапазон значений, которые вы хотите скопировать и вставить, затем щелкните Кутулс > Диапазон > Вставить в видимый диапазон > Все or Только вставить значения. Смотрите скриншот:

2. Затем Вставить в видимый диапазон появится диалоговое окно и выберите диапазон, в который вы хотите вставить значения. Смотрите скриншот:

3. Нажмите OK. Теперь выбранные значения вставляются только в видимый диапазон.

Вставить только значения в видимые ячейки	Вставить все в видимые ячейки

Примечание: Эта утилита работает как со скрытыми, так и с отфильтрованными строками или столбцами.

Вставить значения в отфильтрованные ячейки. Копирование в Excel отфильтрованных данных. Копирование только видимых ячеек

Вставить только в видимые строки в Excel числа, формулы, текст можно несколькими способами. Когда нужно вставить числа, формулы, текст не во все строки таблицы, можно воспользоваться фильтром. Как установить фильтр и как фильтровать в Excel, смотрите в статье «Фильтр в Excel ». Но, чтобы вставить данные только в видимые ячейки, нужны свои способы, особенно, если много строк.
Первый способ — обычный.
Возьмем такую таблицу. Таблица будет одна для всех примеров.
Уберем фильтром все цифры 2 из таблицы. В оставшиеся видимые ячейки поставим цифру 600. В ячейку В2 ставим число 600, затем копируем его вниз по столбцу (тянем за правый нижний угол ячейки В2). Значения скопировались только в видимые ячейки. Точно также можно вставлять и формулы. Мы в ячейке С2 пишем такую формулу. =А2*10
Получилось так.
Отменим фильтр. Получилась такая таблица.
Формула и цифры вставились только в отфильтрованные строки.
Второй способ.
Также отфильтруем данные. В первой ячейке пишем число, формулу, текст, т.д. Теперь, если строк тысячи, то выделяем ячейки так: нажимаем клавиши «Ctrl» + «Shift» + кнопку (стрелочку) вниз (или кнопку вверх, зависит от того, где хотим выделить ячейки – ниже или выше ячейки, в которой написали число).
Теперь, или нажимаем сочетание клавиш «Ctrl» + G, или клавишу F5. Выйдет диалоговое окно «Переход». Нажимаем кнопку «Выделить…». И, в новом диалоговом окне «Выделение группы ячеек» ставим галочку у слов «Только видимые ячейки». Нажимаем «ОК». Затем вставляем как обычно.

Ещё один способ вызвать диалоговое окно «Выделение группы ячеек». На закладке «Главная» в разделе «Редактирование» нажимаем на кнопку «Найти и выделить». В появившемся списке нажимаем на функцию «Выделение группы ячеек».

Чтобы заполнить видимые ячейки в выделенных столбцах Excel , нажимаем сочетание клавиш «Ctrl» + D. И все выделенные столбцы заполнятся данными или формулой, как в первой ячейке. В нашем примере, мы написали число800 в ячейку D2 столбца D.

Третий способ.
В новом столбце (в нашем примере – столбец Е) выделяем ячейки. Нажимаем клавишу F5. Выйдет диалоговое окно «Переход». Нажимаем кнопку «Выделить…». И, в новом диалоговом окне «Выделение группы ячеек», ставим галочку у слов «Только видимые ячейки». Нажимаем «ОК». Теперь, не отменяя выделения, в первой ячейке столбца (у нас – Е2) вводим формулу, цифру, т.д. Нажимаем сочетание клавиш «Ctrl» + «Enter».

Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки

В общем-то смысл статьи уже, думаю, понятен из названия. Просто чуть-чуть расширю.

Ни для кого не секрет, что Excel позволяет выделить только видимые строки(например, если некоторые из них скрыты или применен фильтр) .

Так вот, если скопировать таким образом только видимые ячейки, то скопируются они как положено. Но при попытке вставить скопированное в диапазон отфильтрованный(либо содержащий скрытые строки) — то результат вставки будет не совсем такой, как Вы ожидали. Данные будут вставлены даже в скрытые строки.

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

Option Explicit Dim rCopyRange As Range «Этим макросом копируем данные Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub «Этим макросом вставляем данные, начиная с выделенной ячейки Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox «Вставляемый диапазон не должен содержать более одной области!», vbCritical, «Неверный диапазон»: Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns.Count li = 0: lCount = 0: le = iCol — 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row — rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига (ThisWorkbook) :

Option Explicit «Отменяем назначение горячих клавиш перед закрытием книги Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey «^q»: Application.OnKey «^w» End Sub «Назначаем горячие клавиши при открытии книги Private Sub Workbook_Open() Application.OnKey «^q», «My_Copy»: Application.OnKey «^w», «My_Paste» End Sub

Теперь можно скопировать нужный диапазон нажатием клавиш Ctrl + q , а вставить его в отфильтрованный — Ctrl + w .

(46,5 KiB, 9 622 скачиваний)

Копируем только видимые ячейки и вставляем только в видимые
По просьбам посетителей сайта решил доработать данную процедуру. Теперь возможно копировать любые диапазоны: со скрытыми строками, скрытыми столбцами и вставлять скопированные ячейки также в любые диапазоны: со скрытыми строками, скрытыми столбцами. Работает совершенно так же, как и предыдущий: нажатием клавиш Ctrl + q копируем нужный диапазон(со скрытыми/отфильтрованными строками и столбцами или не скрытыми) , а вставляем сочетанием клавиш Ctrl + w . Вставка производится так же в скрытые/отфильтрованные строки и столбцы или без скрытых.
Если в копируемом диапазоне присутствуют формулы, то во избежание смещения ссылок можно копировать только значения ячеек — т.е. при вставке значений будут вставлены не формулы, а результат их вычисления. Или если необходимо сохранить форматы ячеек, в которые происходит вставка — будут скопированы и вставлены только значения ячеек. Для этого надо заменить строку в коде(в файле ниже):

rCell.Copy rResCell.Offset(lr, lc)

rResCell.Offset(lr, lc) = rCell.Value

В файле ниже обе эти строки присутствуют, Вам надо лишь оставить ту, которая больше подходит под Ваши задачи.

(54,5 KiB, 7 928 скачиваний)

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

Если некоторые ячейки, строки или столбцы на листе не отображаются, вы сможете скопировать все ячейки (или только видимые ячейки). По умолчанию Excel копирует не только видимые, но и скрытые или фильтрованные ячейки. Если же требуется скопировать только видимые ячейки, выполните действия, описанные ниже. Например, можно скопировать только сводные данные из структурированного листа.

Выполните указанные ниже действия.

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

При копировании и вставке видимых ячеек в диапазоне данных, который содержит скрытые ячейки или к которому применен фильтр, можно заметить, что скрытые ячейки вставляются вместе с видимыми. К сожалению, вы не можете изменить этот параметр, когда вы копируете и вставляете диапазон ячеек в Excel в Интернете, так как Вставка только видимых ячеек недоступна.

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

Если не нужно форматировать данные как таблицу и установлено классическое приложение Excel, можно открыть книгу в нем, чтобы скопировать и вставить видимые ячейки. Для этого нажмите кнопку Открыть в Excel и выполните действия, описанные в статье копирование и вставка только видимых ячеек .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте