Как в excel выделить только видимые ячейки
Копирование только видимых ячеек
Если некоторые ячейки, строки или столбцы на листе не отображаются, вы сможете скопировать все ячейки (или только видимые ячейки). По умолчанию Excel копирует не только видимые, но и скрытые или фильтрованные ячейки. Если же требуется скопировать только видимые ячейки, выполните действия, описанные ниже. Например, можно скопировать только сводные данные из структурированного листа.
Выполните указанные ниже действия:
Выделите ячейки, которые вы хотите скопировать. Дополнительные сведения можно найти в разделе выделение ячеек, диапазонов, строк и столбцов на листе.
Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
Щелкните Главная > Найти и выделить, а затем выберите пункт Выделение группы ячеек.
Выберите параметр только видимые ячейки и нажмите кнопку ОК.
Щелкните Копировать (или нажмите клавиши CTRL+C).
Выделите левую верхнюю ячейку области вставки и нажмите кнопку Вставить (или нажмите клавиши CTRL + V).
Совет: Чтобы скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните вкладку другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
Примечание: При копировании значения последовательно вставляются в строки и столбцы. Если область вставки содержит скрытые строки или столбцы, возможно, потребуется отобразить их, чтобы увидеть все скопированные данные.
При копировании и вставке видимых ячеек в диапазоне данных, который содержит скрытые ячейки или к которому применен фильтр, можно заметить, что скрытые ячейки вставляются вместе с видимыми. К сожалению, вы не можете изменить этот параметр, когда вы копируете и вставляете диапазон ячеек в Excel в Интернете, так как Вставка только видимых ячеек недоступна.
Тем не менее, если отформатировать данные как таблицу и применить фильтр, можно скопировать и вставить только видимые ячейки.
Если не нужно форматировать данные как таблицу и установлено классическое приложение Excel, можно открыть книгу в нем, чтобы скопировать и вставить видимые ячейки. Для этого нажмите кнопку Открыть в Excel и выполните действия, описанные в статье копирование и вставка только видимых ячеек.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Источник: support.office.com
Как выделить только видимые ячейки в excel?
В программе эксель можно выделять только видимые ячейке двумя способами: вывести на панель настроек горячую клавишу или использовать функционал «Найти и выделить». Даная функция очень нужна, когда работаешь с фильтрами, так как пи обычном выделение, часто выделяются и скрытые ячейки. Поэтому давайте рассмотрим подробно оба способа выделения видимых ячеек.
Первый способ. Разберемся, как вывести на панель горячую клавишу.
Первый шаг. Поставим курсор мыши на верхнюю панель настроек в том месте, где нет иконок и нажмем правую клавишу мыши, чтобы появилось меню, в котором выберем «Настройка ленты».
Второй шаг. На экране отобразиться меню «Параметры Excel». В этом меню вверху справа есть раздел «Выбрать команды», нажимаем на стрелочку рядом с ним и из выпадающего списка выбираем строку «Все команды», после в нижнем меню по алфавиту будут выстроены все доступные команды. Нам необходимо найти строку «Выделить видимые ячейке», после эту кнопку необходимо добавить в любую закладку, создав предварительно для неё группу в этой вкладке с помощью кнопки «Создать группу». После чего можно закрывать меню.
Третий шаг. В итоге в нашем примере в закладке «Главная» появился раздел «Работа с данными», в котором есть кнопка «Выделить видимые ячейки». Теперь с помощью неё можно выделять нужные ячейке после использования фильтра. Вы сначала делаете фильтр, потом выделяете отфильтрованный диапазон, а потом нажимаете на эту кнопку, чтобы программа выделила только видимые ячейки.
Второй способ. Выделим нужные ячейки с помощью функционала «Найти и выделить».
Первый шаг. Сначала выделим нужный отфильтрованный диапазон ячеек, после перейдем на верхней панели настроек в закладку «Главная», где найдем блок «Редактирование» и нажмем на иконку «Найти и выделить».
Второй шаг. В итоге на экране появится дополнительное меню, в котором необходимо нажать на строку «Выделить группу ячеек».
Третий шаг. На экране появится меню «Выделить группу ячеек», в котором можно найти в нижнем правом углу строку «только видимые ячейки», напротив этой строки ставим точку и закрываем данное меню.
Четвертый шаг. В итоге программа выделить только видимые ячейке в таблице.
Видео как в экселе выделить только видимые ячейки.
Источник: portalonline.ru
Выделить только видимые ячейки в Excel
В некоторых случаях нам нужно определить расположение особых типов ячеек на листах. Например, разве не удобно иметь возможность определять местоположение всех ячеек, которые содержат формулу, или, допустим, всех ячеек, значения которых зависят от текущей ячейки? Excel предоставляет легкий способ нахождения этих и многих других специальных типов ячеек. Ключом ко многим типам специальных выборов является диалоговое окно Выделение группы ячеек. Выберите Главная → Найти и выделить → Выделение группы ячеек для отображения диалогового окна Выделение группы ячеек (рис. 5.1). Другой способ открытия этого диалогового окна: нажмите клавишу F5, а затем в появившемся диалоговом окне Переход — кнопку Выделить.
После того как вы установите нужный переключатель в диалоговом окне Выделение группы ячеек, Excel выбирает соответствующее подмножество ячеек из текущего выбора. Как правило, это приводит к множественному выбору. Если ни одна ячейка не удовлетворяет условиям, Excel оповестит вас сообщением Не найдено ни одной ячейки, удовлетворяющей указанным условиям.
Если вы работаете с диалоговым окном Выделение группы ячеек, когда выбрана только одна ячейка, Excel основывает свой поиск на всей активной области листа. В противном случае поиск основывается на выбранном диапазоне.
В табл. 5.1 приведено описание вариантов, доступных в диалоговом окне Выделение группы ячеек.
Таблица 5.1. Параметры диалогового окна Выделение группы ячеек
Попробуйте устанавливать переключатель Выделить в диалоговом окне Выделение группы ячеек в разные положения, чтобы проверить, какие флажки становятся доступными. Например, четыре флажка, расположенных под положением формулы, становятся доступными и при установке переключателя в положение константы для дальнейшего уточнения результатов. Кроме того, положения только непосредственно и на всех уровнях переключателя зависимые ячейки становятся активными и при установке переключателя в положение влияющие ячейки. То же самое происходит с положениями всех и этих же при установке переключателей проверка данных и условные форматы.
Источник: officeassist.ru
Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
В общем-то смысл статьи уже, думаю, понятен из названия. Просто чуть-чуть расширю.
Ни для кого не секрет, что 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
Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(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, 10 144 скачиваний)
Копируем только видимые ячейки и вставляем только в видимые
По просьбам посетителей сайта решил доработать данную процедуру. Теперь возможно копировать любые диапазоны: со скрытыми строками, скрытыми столбцами и вставлять скопированные ячейки также в любые диапазоны: со скрытыми строками, скрытыми столбцами. Работает совершенно так же, как и предыдущий: нажатием клавиш Ctrl+q копируем нужный диапазон(со скрытыми/отфильтрованными строками и столбцами или не скрытыми), а вставляем сочетанием клавиш Ctrl+w. Вставка производится так же в скрытые/отфильтрованные строки и столбцы или без скрытых.
Если в копируемом диапазоне присутствуют формулы, то во избежание смещения ссылок можно копировать только значения ячеек – т.е. при вставке значений будут вставлены не формулы, а результат их вычисления. Или если необходимо сохранить форматы ячеек, в которые происходит вставка – будут скопированы и вставлены только значения ячеек. Для этого надо заменить строку в коде(в файле ниже):
rCell.Copy rResCell.Offset(lr, lc)
rResCell.Offset(lr, lc) = rCell.Value
В файле ниже обе эти строки присутствуют, Вам надо лишь оставить ту, которая больше подходит под Ваши задачи.
Tips_Macro_CopyPasteInHiddenCells.xls (54,5 KiB, 8 442 скачиваний)
Так же см.:
[[Excel удаляет вместо отфильтрованных строк – все?! Как избежать]]
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Дмитрий, здравствуйте!
У меня почему-то не работают ваши файлы.
Приведу пример на последнем файле:
Открываю его в Excel 2013; разрешаю редактирование-вылезает ошибка Run-time error ‘1004’:
Method ‘OnKey’ of object’_Application’ failed
Когда нажимаю debug вылезает окошко макроса и там подсвечена желтым часть строки:
Application.OnKey “^q”, “My_Copy”
А если пробую нажимать в таблице ctrl+q, то вылезает окошко форматирования
Подскажите, пожалуйста, как это исправить.
Дмитрий, когда скачиваете файл с интернета – он открывается в защищенном режиме, о чем у Вас появляется предупреждение. Нельзя обратиться к свойствам листов и книг, если книга открыта в защищенном режиме. А именно так по умолчанию открываются книги, скачанные откуда-то. Варианта по сути два:
1. При открытии файла разрешить редактирование и перезапустить книгу.
2. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.
Дмитрий, спасибо большое!
После пересохранения книги и добавления надежного расположения все заработало=)
Источник: www.excel-vba.ru
Как в excel выделить только видимые ячейки
Сделал как ты описал, но получил другое сообщение: “Данная команда неприменима для несвязных диапазонов”.
Нет ли у тебя объединенных ячеек (это вообще источник гемороя при работе с таблицами 
)?
Вообще, если для вставки выбрать одну ячейку, то данные вставляются. Но все подряд, а не с пропусками!
Что предпринять?
Можно в режиме автофильтра в первой ячейке второго столбца (куда вставляешь) написать формулу =(первая ячейка первого столбца) и скопировать ее на весь второй столбец. Формулы вставятся только в видимые ячейки.
Потом, при желании, можно заменить формулы на значения: снять автофильтр, выделить второй столбец, копировать, Правка – Специальная вставка – значения.
| 2. GreedBeef , 23.06.2006 13:32 |
| Nosorog
Нет ли у тебя объединенных ячеек (это вообще источник гемороя при работе с таблицами )? Можно в режиме автофильтра в первой ячейке второго столбца (куда вставляешь) написать формулу =(первая ячейка первого столбца) и скопировать ее на весь второй столбец. Не получается Формулы вставятся только в видимые ячейки. |
| 3. GreedBeef , 26.06.2006 14:46 |
| Или только через макросы можно это реализовать? Хотя хотелось бы проделывать такие вставки вручную. |
| 4. Nosorog , 26.06.2006 15:41 |
| GreedBeef Я все же не могу смоделировать у себя ситуацию, когда возникает ошибка, которую ты описал: “Данные не могут быть вставлены из-за несоответствия формы и размеров области копирования и области вставки”. Значит, копируется столбец из таблицы, к которой применен автофильтр, в другую книгу в таблицу, к которой тоже применен автофильтр? И ты хочешь, чтобы каждая видимая ячейка первой таблицы скопировалась в очередную видимую ячейку второй таблицы? Так? |
| 5. GreedBeef , 26.06.2006 20:57 |
| Nosorog
На Листе2 в ячейках A1:A10 находятся числа 1. 10. К сообщению приложены файлы: 1.png, 290×396, 9Кb |
| 6. Vic_K , 26.06.2006 23:13 |
| GreedBeef Если копируем с листа с автофильтром и вставляем в лист с автофильтром: то только на промежуточный лист и там сравнивать. Кстати получить ошибку у казанную вами мне так и не удалось, при вставке всего столбца валится такая же как у Nosorog. Если выделить именно те ячейки которые будут копироваться, то происходит вставка первой и последней ячеек. Да, совсем забыл офис 2003. |
| 7. GreedBeef , 27.06.2006 00:31 |
| Nosorog Значит, копируется столбец из таблицы, к которой применен автофильтр, в другую книгу в таблицу, к которой тоже применен автофильтр? Нет Vic_K Постараюсь объяснить ещё раз – я копирую данные из обычной таблицы (без автофильтра) в колонку другой таблицы, у которой по одному из столцов применен автофильтр. Excel 2003 (imho для данного вопроса это не важно) |
| 8. Vic_K , 27.06.2006 08:35 |
| GreedBeef Да я попробовал, выделять диапазон когда вставляю и получил такую же ошибку как и вы. если просто поставить курсор и нажать вставить, то происходит вставка без учета фильтра. моё мнение напрямую никак. |
| 9. Gogins-jan , 04.02.2007 19:45 |
| Попробуй сначала отсортировать диапазон по столбцу со значениями по которым потом будешь задавать фильтрацию данных. Проверено на практике, значительно упрощает жизнь. Вообще имею немного более сложную задачу и ответа на свой вопрос ещё не нашёл. В твоём случае корень проблеммы лежит в том, что Ехсел при фильтрации просто напросто скрывает строки с ненужными параметрами. Попробуй скрыть в диапазоне несколько строк и вставить прогрессию из элементарных чисел в столбец и ты получишь тоже сообщение. Просто функция копирования или прогрессии не может работать в двух несвязанных диапазонах. |
| 10. Alex1111_1 , 10.07.2011 03:59 |
| Есть решение
В листе 1, Там где список уже с автофильтром – рядом в столбце поставьте СТРОКА(А1), скопируйте эту формулу на все значения строк. Скопируйте получившиеся числа строк. В листе 2, Специальная вставка – Значения – вставить. – вы получите список строк подряд. Заполните строки как вам нужно Затем в VBA напишите следующее (или подобное – смотрите как вам надо) В программе надеюсь разберетесь (вставлен мой пример) Private Sub CommandButton1_Click() ‘считываем значение For j = 1 To 14 В результате – получите вставленные значения, которые шли подряд – в листе 2, в автофильтр – который в листе 1 |
| 11. Вика , 14.12.2011 15:06 |
| А без бейсика никак не обойтись? Почти та же самая задача – из одного файла из отфильтрованного списка надо вставить значения в другой файл тоже в отфильтрованный список (количество элементов в обоих отфильтрованных списков одинаковое). В итоге вышеупомянутая ошибка: Данная команда неприменима для несвязных диапазонов. Ладно бы это была разовая задача, но количество данных в отфильтрованном списке постонно меняется и их не 10 штук. Excel – 2010. |
