Excel как скопировать без скрытых строк

Копирование только видимых ячеек

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Единственная проблема со скрытыми ячейками заключается в том, что, хотя они невидимы, Excel по-прежнему выбирает и копирует их содержимое.

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

Читать еще: Как вставить подпись в excel

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

Есть два способа выбрать только видимые ячейки в Excel. Одним из них является простой способ сочетания клавиш. Другой метод — это растянувшееся меню ленты. Мы собирались подробно описать оба.

Выбрать только видимые ячейки в сочетаниях клавиш Excel

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

После того, как вы выбрали ячейки, нажмите Alt +, сочетание клавиш. Белый контур появится вокруг выделенных ячеек.

Этот контур означает, что выбраны только видимые ячейки. Продолжайте и используйте сочетания клавиш Ctrl + C и Ctrl + V, чтобы скопировать и вставить содержимое видимых ячеек.

Выбрать только видимые ячейки в меню Excel

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

появится окно с длинный список опций на выбор. Выберите параметр «Только видимые ячейки» и нажмите «ОК». Тот же белый контур, который появился вокруг выделенных ячеек, когда вы нажали Alt +, снова появится сочетание клавиш.

Это показывает, что выделены только видимые ячейки. Используйте копирование / вставку ярлыков или кнопок для копирования и вставки содержимого ячейки.

Само собой разумеется, что клавиатура Ярлык — это самый простой способ выбрать только видимые ячейки в Excel. Это странно неясное сочетание клавиш, и пункт меню очень хорошо скрыт.Пользователи, которые не знают о Alt +, вставляют ярлыки в ячейку промежуточного листа и очищают ее, прежде чем вставлять содержимое в нужное место.

Это вряд ли удобно, хотя все же проще, чем переходить через пункты меню. Тем не менее, Alt + — это сочетание клавиш, которое вы хотите где-то записать.

Excel как скопировать без скрытых строк

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

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

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

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

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

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

Читать еще: Как выделить всю строку в excel

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

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

По умолчанию Excel копирует как видимые, так и скрытые ячейки. Однако можно скопировать только видимые ячейки.

В этом примере строка 2 скрыта:

Если просто скопировать, а затем вставить этот диапазон, результат получается следующим:

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

Выделите диапазон A1:A4.
На вкладке Home (Главная) нажмите кнопку Find & Select (Найти и выделить) и кликните по Go To Special (Выделить группу ячеек).
Excel выделит видимые ячейки. Теперь нажмите сочетание Ctrl+C, чтобы скопировать диапазон.
Выберите ячейку A6 и нажмите 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

Читать еще: Формула расчета рабочего времени в excel

Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(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. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.

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

0 0 голоса

Рейтинг статьи