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

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

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

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

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

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

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

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

Как скопировать и вставить только видимые ячейки в Microsoft Excel

Когда дело доходит до программ для выполнения задач с электронными таблицами, нельзя отрицать, что Microsoft Excel является самым популярным и используемым инструментом не только в нашей стране, но и во всем мире. Одно из действий, которое мы чаще всего выполняем при его использовании, — это копирование и вставка, что очень легко сделать. Однако, когда дело доходит до утечки данных, копирование и вставка этих данных не всегда так просто, как можно было бы ожидать.

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

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

Скопируйте видимые ячейки в Excel

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

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

Это приведет к появлению нового всплывающего окна, в котором мы можем выбирать между несколькими вариантами, которые оно нам позволяет: ячейки с комментариями, константы, формулы, пробелы, ячейки с проверкой данных и т. д. Здесь нас интересует «Только видимые ячейки» вариант, поэтому мы нажимаем на него, чтобы выбрать и нажимаем «ОК».

Далее, с выбранными ячейками, мы нажимаем, для чего мы можем использовать сочетание клавиш Ctrl + C или щелкните правой кнопкой мыши и выберите Копировать. Мы также можем щелкнуть непосредственно значок «Копировать» (значок с двумя страницами), который мы находим в разделе «Буфер обмена» на вкладке «Главная».

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

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

Способ 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

Kutools for Excel

Повышает Excel с помощью 300+
Мощные функции

В Excel, когда вы копируете и вставляете некоторые данные в диапазон, который был отфильтрован или содержит скрытые строки, данные также будут вставлены в эти отфильтрованные или скрытые ячейки. Можем ли мы просто вставлять данные в видимые ячейки только в Excel? Да, Kutools for Excel‘s Вставить в видимый Функция поможет вам сделать это быстро.

Расположение Вставить в видимый: Kutools > Вставить в видимый

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

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

1. Выберите данные, которые вы хотите скопировать и вставьте в отфильтрованный список.

2, На Кутулс вкладка в Диапазон и ячейки группа, пожалуйста, нажмите Вставить в видимый.

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

Результат: выбранные данные сразу вставляются в отфильтрованные строки.

Примечание:

• Если вы хотите копировать и вставлять только значения (не форматирование и не формулы), нажмите Вставить в видимый >Только вставить значения in шаг 2.
• Эта утилита поддерживает отменить (Ctrl + Z).
• Эта функция помогает вставлять данные только в видимые ячейки, поэтому она работает независимо от того, есть ли в ваших данных отфильтрованные или скрытые строки.

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

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

Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab: Использование удобных вкладок в вашем офисе, как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel: Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.

Kutools for Excel

Описанный выше функционал — лишь одна из 300 мощных функций Kutools for Excel.

Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 30 дней.