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

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

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

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

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

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

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

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

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

Уверена, Вы сталкивались с этой проблемой. Если скопировать данные и обычным CTRL+V вставить их в отфильтрованную таблицу, то они попадут и в скрытые строки тоже . Это совсем не то, что нам нужно — см. скрин ниже. Узнаёте?

Можно решить эту задачу, подтянув данные формулами, без макроса. Ниже показываю 3 варианта.

Функции СМЕЩ + СЧЁТЕСЛИ

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

И любые из этих условий можно использовать для функций СЧЁТЕСЛИ/МН, СУММЕСЛИ/МН и СРЗНАЧЕСЛИ/МН (на моём онлайн курсе «Расширенные возможности» показываю все варианты условий на примере выборочного суммирования).

В этом случае нашим помощником будет функция СЧЁТЕСЛИ (или СЧЁТЕСЛИМН, если зафильтровано несколько столбцов), которая способна посчитать в каждой строке её уникальный порядковый номер от начала таблицы.

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

Осталось позвать на помощь функцию ссылок и массивов СМЕЩ и вот результат:
D9=СМЕЩ($F$96;СЧЁТЕСЛИ($C$3:C9;»самовывоз из питомника»)-1;0)

покупка

Как вставить данные в отфильтрованный список, пропуская только скрытые строки в Excel?

Как все мы знаем, когда мы пытаемся вставить значения в список фильтрованной таблицы, скрытые строки не пропускаются, поэтому мы получим неверный результат. Например, у меня есть два листа, лист 1 содержит данные, которые были отфильтрованы, а лист 2 содержит данные, которые я хочу вставить в фильтрованный лист 1, как показано на следующих снимках экрана. Есть ли какая-нибудь функция, с помощью которой мы можем вставить данные в отфильтрованные строки только в Excel?

Copy and Paste cell values into visible or filtered cells only:

With Kutools for Excel‘s Paste to Visible Range feature, you can quickly copy and paste data from one place to another visible or filtered cells only.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Вставить данные в отфильтрованный список в Excel с помощью вспомогательных столбцов

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

1. Сначала нажмите Данные > Фильтр чтобы удалить фильтр, введите 1, 2 в ячейки C2 и C3 отдельно, которые находятся рядом со столбцом фильтра, и выберите ячейки C2 и C3, затем перетащите маркер заполнения в ячейки, соответствующие вашим данным на листе 1. См. снимок экрана:

2. Затем снова отфильтруйте данные, нажав Данные > Фильтр, в этом примере я отфильтрую «KTE» из Листа 1 и введу эту формулу = СТРОКА () в ячейку D2, затем заполните эту формулу до нужных видимых ячеек столбца D, см. скриншоты:

3. А затем снова отмените фильтр и отсортируйте данные по столбцу D в порядке возрастания, все элементы KTE были отсортированы вместе, см. Снимок экрана:

4. Затем вы можете скопировать данные A1: B6 из листа 2 и вставить их в диапазон A2: B7 листа 1.

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

6. И порядок данных был восстановлен, затем вы можете удалить содержимое столбца C и столбца D по мере необходимости, наконец, вы можете отфильтровать необходимые данные, и вы увидите, что отфильтрованные данные были успешно заменены новыми данными.

Вставьте данные в отфильтрованный список в Excel с помощью Kutools for Excel

Первый метод слишком сложен для применения, поэтому я представлю вам удобный инструмент — Kutools for Excel, С его Вставить в видимый диапазон функция, вы можете быстро вставить данные в отфильтрованный список без особых усилий.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Сначала выберите структуру данных, которую вы хотите скопировать, и вставить в отфильтрованный список. А затем нажмите Кутулс > Диапазон > Вставить в видимый диапазон, см. снимок экрана:

2. А потом Вставить в видимый диапазон Появится окно подсказки, щелкните ячейку или диапазон ячеек, в которые вы хотите вставить новые данные, см. снимок экрана:

3. Затем нажмите OK Кнопка, новые данные были вставлены только в отфильтрованный список, и данные скрытых строк также сохраняются.

(1.) Если вы выберете Только вставить значения вариант, только значения будут вставлены в отфильтрованные данные, см. screesnhot:

(2.) Если вы выберете Все вариант, значения, а также форматирование будут вставлены в отфильтрованные данные, см. screesnhot:

Копировать и вставлять значения ячеек в видимые или отфильтрованные ячейки только с помощью Kutools for Excel:

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Лучшие инструменты для офисной работы

Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего.

Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

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

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

если кто-то не знает, как это сделать: выделяем диапазон — Alt+;(для английской раскладки);Alt+ж(для русской). Подробнее можно почитать в этой статье

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

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

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

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

Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA( Alt + F11 ) —Insert —Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt + F8 -выделяем имя макроса —Выполнить (Run) .
Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(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

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 .
Если необходимо переносить только значения (т.е. если в ячейке будут формулы, то в итоге будет перенесен результат вычисления этой формулы), надо заменить строку в коде:

rCell.Copy ActiveCell.Offset(li, le)

rCell.Copy ActiveCell.Offset(li, le)