Как в 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 оповестит вас сообщением Не найдено ни одной ячейки, удовлетворяющей указанным условиям.

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

Читайте также:  Excel подсчет ячеек по цвету заливки excel

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

Источник: www.excel-vba.ru

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

Сделал как ты описал, но получил другое сообщение: “Данная команда неприменима для несвязных диапазонов”.
Нет ли у тебя объединенных ячеек (это вообще источник гемороя при работе с таблицами )?
Вообще, если для вставки выбрать одну ячейку, то данные вставляются. Но все подряд, а не с пропусками!

Что предпринять?
Можно в режиме автофильтра в первой ячейке второго столбца (куда вставляешь) написать формулу =(первая ячейка первого столбца) и скопировать ее на весь второй столбец. Формулы вставятся только в видимые ячейки.
Потом, при желании, можно заменить формулы на значения: снять автофильтр, выделить второй столбец, копировать, Правка – Специальная вставка – значения.

2. GreedBeef , 23.06.2006 13:32
Nosorog

Нет ли у тебя объединенных ячеек (это вообще источник гемороя при работе с таблицами )?
Нет, обычные ячейки

Можно в режиме автофильтра в первой ячейке второго столбца (куда вставляешь) написать формулу =(первая ячейка первого столбца) и скопировать ее на весь второй столбец.

Не получается
Делаю так – автофильтр, выделяю итоговый столбец, Ctrl+G “Выделить только видимые ячейки”, в первую итоговую ячейку – относительную формулу ссылки на ячейки с данными =[Книга2]Лист1!B1
Ctrl+Enter – копирую формулу на все выделенные ячейки

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

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), скопируйте эту формулу на все значения строк.
Например:
35
39
40
43
70
71
89
101
191
305

Скопируйте получившиеся числа строк.

В листе 2, Специальная вставка – Значения – вставить. – вы получите список строк подряд.

Заполните строки как вам нужно

Затем в VBA напишите следующее (или подобное – смотрите как вам надо)

В программе надеюсь разберетесь (вставлен мой пример)

Private Sub CommandButton1_Click()

‘считываем значение
ivalue = Worksheets(“Лист2”).Cells(i, 2).Value

For j = 1 To 14
‘находим значение и меняем ячейки рядом
Worksheets(“Лист1”).Cells(ivalue, 6 + j) = Worksheets(“Лист2”).Cells(i, 2 + j)

В результате – получите вставленные значения, которые шли подряд – в листе 2, в автофильтр – который в листе 1

11. Вика , 14.12.2011 15:06
А без бейсика никак не обойтись? Почти та же самая задача – из одного файла из отфильтрованного списка надо вставить значения в другой файл тоже в отфильтрованный список (количество элементов в обоих отфильтрованных списков одинаковое). В итоге вышеупомянутая ошибка: Данная команда неприменима для несвязных диапазонов. Ладно бы это была разовая задача, но количество данных в отфильтрованном списке постонно меняется и их не 10 штук. Excel – 2010.
12. Cynacyn , 08.11.2012 14:38
Я справился “вручную”.
Рядом со столбцом в который будем вставлять значения протягиваем функцию СТРОКА()
Я использовал СТРОКА()*1111 для того чтобы номер строки не совпал с копируемым значением.
Копируем столбец с номерами строк в соседний со значениями которые нужно перенести столбец (эти два столбца дальше буду называть “таблица1”)
Рядом со столбцом в который будем вставлять значения, где уже протянута функция СТРОКА() протягиваем функция ВПР(тут ячейку с номером строки в соседнем столбце;таблица 1;номер столбца в таблица1 со значениями для переноса; )
Выделяем полностью столбец в котором у нас функция ВПР, нажимаем “Выделить только видимые ячейки”, копируем.
Выделяем полностью столбец в который копируем, нажимаем “Выделить только видимые ячейки”, “Специальная вставка. “->”значения”.

P.S. У меня работало с автофильтром по одному столбцу, с несколькими столбцами не работает – ошибка “Данная команда не работает для несвязных диапазонов” или не вставляет в неотображаемые (не попавшие в фильтр) ячейки.

13. ЦЦ , 26.03.2014 14:57
Cynacyn
У меня работало с автофильтром по одному столбцу, с несколькими столбцами не работает – ошибка “Данная команда не работает для несвязных диапазонов”
у меня такое пишет даже при автофильтре по одному стобцу
14. ЦЦ , 28.03.2014 06:14
Подошёл вот этот макрос http://www.planetaexcel.ru/techniques/2/173/

Скрытый текст:Sub PasteToVisible()
Dim copyrng As Range, pasterng As Range
Dim cell As Range, i As Long

Источник: forum.ixbt.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: crazygamer.ru

Добавить комментарий

Adblock
detector