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

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

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

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

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

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

Щелкните Главная > Найти и выделить, а затем выберите пункт Выделение группы ячеек.

Выберите параметр только видимые ячейки и нажмите кнопку ОК.

Щелкните Копировать (или нажмите клавиши CTRL+C).

Выделите левую верхнюю ячейку области вставки и нажмите кнопку Вставить (или нажмите клавиши CTRL + V).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Cpu usage 100 что делать

Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для "Ашанов" не фиксированная, а составляет 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.H > cell.Value = Cells(cell.Row, copyrng.Column).Value
End If
Next cell
End Sub

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

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

Читайте также:  Gigabyte p43 es3g bios

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

Здравствуйте! Спасибо Вам за Вашу очень полезную статью. Макрос я себе скопировала, и он успешно работает. Но также я хотела научиться альтернативному способу вставки данных в отфильтрованные ячейки, и вот тут у меня ничего не получилось. Помогите, пожалуйста!
У меня есть таблица с ФИО, где напротив выбранных нужно вставить сумму. Я произвожу следующие действия:
1. Копирую данные, которые необходимо вставить около фамилий.
2. Затем около отфильтрованного списка фамилий в нужном столбце выделяю нужное количество ячеек, нажимаю F5 — выделить — только видимые ячейки. Выделяются нужные ячейки и я правой клавишей мыши вставляю туда уже заранее скопированные данные.
Но вставить данные корректно, так, как мне надо, не получается. Допустим, таблица имеет следующий вид:

ФИО
Афиногенов И.С. 20 000,00
Артемьев О.А.
Сеченова Н.К. 20 000,00
Кирова К.У.
Булычев Р.Л. 40 000,00
Антонова Ж.К. 20 000,00
40 000,00
50 000,00

Я отфильтровываю список и оставляю видимыми только фамилии Афиногенов, Сеченова и Антонова, напротив которых нужно вставить суммы 20000, 40000, 50000. Но данные встают напротив этих фамилий: 20000, 20000, 20000. Если снять фильтр и раскрыть весь список, то увидим то, что выше в таблице. Данные попадают даже туда, где списка нет. Что же делать? Уже давно пытаюсь решить эту проблему и ничего не получается. Подскажите мне, пожалуйста.

Добрый день, не подскажете с чем может быть связана след.проблема?

Вставляю из другой таблицы в свою отфильтрованную таблицу какие-нибудь значения. Убираю фильтр, позже в ходе работы натыкаюсь на эти значения в НЕНУЖНЫХ строках.

Как такое происходит? До сих пор не могу понять?

В отфильтрованной таблице можно протягивать?
Можно вставлять необходимую инфу выделяя в столбце сразу несколько строк-> ctrl v?

Добрый день.
Скопировал макрос, но получаю ошибку — Диапазоны копирования и вставки разного размера
Что я делаю не так?
И как исправить?

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

Ни для кого не секрет, что 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.H >= rCell.Row — rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

Читайте также:  Hp omen как зайти в биос

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

Tips_Macro_CopyPasteInHiddenRows.xls (46,5 KiB, 9 523 скачиваний)

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

rCell.Copy rResCell.Offset(lr, lc)

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

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

Tips_Macro_CopyPasteInHiddenCells.xls (54,5 KiB, 7 832 скачиваний)

Так же см.:
[[Excel удаляет вместо отфильтрованных строк — все?! Как избежать]]

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

Поиск по меткам

Дмитрий, здравствуйте!
У меня почему-то не работают ваши файлы.
Приведу пример на последнем файле:
Открываю его в Excel 2013; разрешаю редактирование-вылезает ошибка Run-time error ‘1004’:
Method ‘OnKey’ of object’_Application’ failed
Когда нажимаю debug вылезает окошко макроса и там подсвечена желтым часть строки:
Application.OnKey "^q", "My_Copy"
А если пробую нажимать в таблице ctrl+q, то вылезает окошко форматирования
Подскажите, пожалуйста, как это исправить.

Дмитрий, когда скачиваете файл с интернета — он открывается в защищенном режиме, о чем у Вас появляется предупреждение. Нельзя обратиться к свойствам листов и книг, если книга открыта в защищенном режиме. А именно так по умолчанию открываются книги, скачанные откуда-то. Варианта по сути два:
1. При открытии файла разрешить редактирование и перезапустить книгу.
2. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.

Дмитрий, спасибо большое!
После пересохранения книги и добавления надежного расположения все заработало=)

Ссылка на основную публикацию
Eax 1300 pro 256 mb
ATI, наконец, выпустила на рынок новое поколение видеокарт. Новое семейство содержит три новых видеочипа: R520, RV530 и RV515. Нетрудно догадаться,...
Cpu amd fx 8300
Описание AMD начала продажи AMD FX-8300 в октябре 2012. Это десктопный процессор на архитектуре Vishera, в первую очередь рассчитанный на...
Cpu hash что это
AIDA64 имеет множество тестов, которые возможно применять для оценивания состояния разных составляющих компьютера или техники в целом. Это искусственные тесты,...
Ebay как отправить товар из россии
Вопросов, связанных с торговлей на площадке eBay у жителей России возникает бесконечное множество. Особенно в нынешних экономических реалиях, когда продажи...
Adblock detector