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

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

Сделайте следующее:

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

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

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

   

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

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

   

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

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

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

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

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

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

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

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

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

Как легко сообразить, макрос запрашивает у пользователя по очереди два диапазона — копирования и вставки. Затем проверяет, чтобы их размеры совпадали, т.к. разница в размерностях вызовет впоследствии ошибку при вставке. Затем макрос перебирает все ячейки в диапазоне вставки и переносит туда данные из диапазона копирования, если строка видима (т.е. не отфильтрована).

Ссылки по теме

• Что такое макросы, как их использовать, куда вставлять код макроса на Visual Basic

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

​Смотрите также​​ и скрытые (отфильтрованные).​Гость​ текстом описанием и​ заново — при​ рядом с выделенными​ cell.EntireRow.Hidden = False​ для ввода значений​Специальная вставка​Вставка содержимого скопированной области​формулы​ содержатся пустые ячейки.​Все содержимое и форматирование​ исходные ячейки (изменения,​ ячейках:​ внизу страницы. Для​, а затем выберите​Примечание:​Как быть?​: новые данные вставляются​ т.п. НУЖНО СОХРАНИТЬ​ фильтре по столбцу​

​ в соседнем столбце​ Then cell.Value =​ и для ввода​.​ без математического действия.​Клавиша T​Транспонировать​ ячеек с использованием​ внесенные в исходных​Пункт меню​ удобства также приводим​ пункт​Мы стараемся как​Владимир​ не с помощью​ ЦЕЛОСТНОСТЬ!!!!) ХЕЛП!!!!!!​ B, все видимые​ ставить маркер, потом​ copyrng.Cells(i).Value i =​ формул. Например, если​

​Перемещение и копирование листа​

1. ​сложить​значения​Вставка содержимого скопированных ячеек​ темы, примененной к​ ячейках, отражаются и​

   ​Что вставляется​​ ссылку на оригинал​Выделение группы ячеек​ можно оперативнее обеспечивать​

2. ​: Вы скопируйте отфильтрованные​​ заполнения, а копируются​​Serge_007​​ ячейки из столбца​​ сортировать по нему​ i + 1​​ скидка для «Ашанов»​​Перемещение и копирование ячеек,​

   

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

4. ​Вставить​​ (на английском языке).​​.​ вас актуальными справочными​

   

5. ​ строки и вставьте​ с нового листа…(при​: В Excel 2007/2010​​ А надо скопировать​​ и копировать уже​ End If Next​

   ​ не фиксированная, а​​ строк и столбцов​ копирования к значениям​ том виде, как​ Данные строк будут​без рамки​ вставлено изображение).​Все содержимое и формат​Можно копировать и вставлять​Выберите параметр​

   ​ материалами на вашем​​ их в другой​ протягивании все ок)​ по умолчанию данные​ в столбец E.​ нужные ячейки.​ cell End Sub​ составляет 10% от​Весьма распространенная ситуация, вопрос​ в области вставки.​

support.office.com

Копирование и вставка определенного содержимого ячейки

​ они отображаются в​​ вставлены в столбцы,​Содержимое и формат ячеек,​Ширины столбцов​ ячеек, включая связанные​ определенного содержимого ячеек​только видимые ячейки​ языке. Эта страница​ лист. Думаю, проблема​Pelena​ вставляются только в​И т.д.​Guest​Как легко сообразить, макрос​ суммы сделки, то​ про которую мне​вычесть​ ячейках.​ и наоборот.​ кроме границ ячеек.​Вставка ширины столбца или​

​ данные.​ и атрибуты (например,​и нажмите кнопку​ переведена автоматически, поэтому​ исчезнет.​: А у Вас​​ отфильтрованные строки, что​ ​Ship​ ​: Нужно так:​​ запрашивает у пользователя​​ в первую отфильтрованную строку​​ задают почти на​Вычитание значений из области​форматы​Вставить связь​Ширины столбцов​ диапазона столбцов в​​формулы​​ формулы, форматы, примечания​​ОК​​ ее текст может​Dyka​ не получилось?​​ бы вставить во​​: Так и делайте.​При скрытии строк,​ по очереди два​

​ можно ввести не​ каждом тренинге. Есть​

Пункты меню «Вставить»

1. ​ копирования из значений​Вставка только форматов ячеек.​Если данные представляют собой​

2. ​Атрибуты ширины столбца или​​ другой столбец или​​Только формулы.​​ и проверки). По​ ​.​

3. ​ содержать неточности и​: Нет, так не​Выкладывайте файл​

4. ​ все необходимо воспользоваться​​ Столбец В ротфильтровали​​ остаются видимыми, к​ диапазона — копирования​​ константу (1000), а​​ таблица, в которой​ в области вставки.​примечания​ рисунок, он связывается​​ диапазона столбцов в​​ диапазон столбцов.​Формулы и форматы чисел​ умолчанию при использовании​

   

Параметры специальной вставки

1. ​Dyka​ районы» в столбец​: Стоит 2007 …в​

2. ​Отфильтровали по другому​​А1543​​ вызовет впоследствии ошибку​​ одинаковые значения или​ ​ какие-либо нужные нам​

3. ​разделить​ в область вставки.​Совет:​

4. ​ чисел.​​ области вставки.​​ ячеек.​+ C и​​ области вставки и​​ секунд и сообщить,​​: С макросами плохо​​ «Т» вставить значения​

   

5. ​ том то и​

   

Параметры вставки

Параметры операций

​ Выберите параметр определенных​(или нажмите клавиши​ кнопок внизу страницы.​ мыслей? Метод Paste​

Доступны и другие параметры:

​ туда данные из​

​ несложный макрос. Нажмите​

​без рамки​​Специальная вставка​Условное форматирование из скопированных​​нажмите кнопку​​Только атрибуты ширины столбцов.​ меню​​ Чтобы скопировать выделенный фрагмент​​ оригинал (на английском​ на скрытость строк?​: Файл пока не​

1. ​ в нужные отфильтрованные,​ что попробовал.​В854​

2. ​ строка видима (т.е.​​Alt+F11​​ не только в​​ ли будет вставлена​ ​Вставка всего содержимого и​

3. ​. Имена параметров могут​ ячеек объединяется с​Копировать​

4. ​Транспонировать​​Вставить​​ на другой лист​​ языке) .​​ или как то​​ смотрела, но для​​ побывал и спецвставкой​​vikttur​​В1543​

   

5. ​ не отфильтрована).​​, в открывшемся окне​​ видимые, но и​​ скопированных данных в​​ формат, кроме границ​ немного различаться, но​

   ​ условным форматированием в​	​.​
   ​Содержимое скопированных ячеек со​	​или выберите​ или в другую​Если некоторые ячеек, строк​
   ​ по другому?​	​ копировать/вставить этот способ​
   ​ с галкой «Пропускать​	​: Конечная цель бесконечных​Только таких строк​Guest​ Visual Basic вставьте​
   ​ в скрытые ячейки.​	​ виде строк или​
   ​ ячеек.​	​ результаты идентичны.​
   ​ области вставки.​	​Щелкните первую ячейку в​ сменой ориентации. Данные​Специальная вставка​
   ​ книгу, щелкните вкладку​	​ и столбцов на​TimSha​ действительно не пойдет.​ пустые ячейки». НЕ​
   ​ фильтраций?​	​ несколько сотен получается.​: Подскажите. Не могу​ новый пустой модуль​
   ​ Давайте посмотрим, как​	​ столбцов и связывание​Ширины столбцов​Выделите ячейки с данными​Параметры операций позволяют выполнить​
   ​ области, куда требуется​	​ строк будут вставлены​и выберите один​ другого листа или​
   ​ листе не отображается,​	​: А в чем​Эти две таблицы​ ВЫХОДИТ!!!!​
   ​Может, есть другие​	​КЭ​ разобраться.​ через меню​ можно обойти эту​

   ​ вставленных данных для​Вставка ширины столбца или​ и атрибутами, которые​​ математические действия со​​ вставить скопированное содержимое.​ в столбцы, и​​ из вариантов в​​ выберите другую книгу​ у вас есть​ «изыск» такого подхода?​ ведь как-то связаны​

   ​Гость​	​ пути?​
   ​: Поставить знак «=»​	​В Excel таблица​Insert — Module​
   ​ проблему.​	​ скопированных данных.​ диапазона столбцов в​ требуется скопировать.​
   ​ значениями из областей​	​На вкладке​ наоборот.​ окне «​
   ​ и выделите левую​	​ возможность копирования всех​ Может по-другому следует​ между собой, может​
   ​: Короче никто не​	​Гость​ и протянуть, а​ на 10000 строк.​

   ​и скопируйте туда​Если вам нужно вставить​Команда​ другой столбец или​На панели инструментов​ копирования и вставки.​Главная​Вставить значения​Специальная вставка​

   ​ верхнюю ячейку области​	​ ячеек, или только​
   ​ взглянуть на проблему​	​ формулой попробовать найти​ знает и никто​: Уважаемые знающие люди…помогите​ потом скопировать столбец​ Нужно при скрытии​
   ​ этот код:​	​ одни и те​Задача​
   ​ диапазон столбцов.​	​Стандартная​Параметр​щелкните стрелку рядом​Только значения в том​». Атрибуты, кроме​ вставки.​ видимые ячейки. По​ и найти другое​ ​ нужное значение?​​ не поможет?!?!?​ плиз…​​ B и «вставить​​ некоторых строк скопировать​​Sub PasteToVisible() Dim​​ же значения во​​пропускать пустые ячейки​​формулы и форматы чисел​​нажмите кнопку​​Результат​

См. также

​ с кнопкой​

​ виде, как они​ можно выбрать исключаются​

support.office.com

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

​Примечание:​ умолчанию Excel копирует​ решение?​Tviga​Serge_007​Суть есть список​​ значения», что б​​ данные из колонки​ copyrng As Range,​ все отфильтрованные строки​Позволяет предотвратить замену значений​Вставка только формул и​Копировать​Нет​Вставить​ отображаются в ячейках.​ при вставке.​ При копировании значения последовательно​ скрытых или отфильтрованных​Guest​: вобщем так.​: Я уже ответил.​

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

​ из около 2000​ только цифры остались​ А в колонку​ pasterng As Range​ списка, то все​ в области вставки,​ форматов чисел из​.​Вставка содержимого скопированной области​

​и выберите пункт​Значения и форматы чисел​Более новые версии​ вставляются в строки​

​ ячейки не только​: Основной ход мысли​не совсем понятно​Почему у Вас​ строк с огловлениями​Guest​ В.​ Dim cell As​

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

​ Office 2011 ​ и столбцы. Если​ видимые ячейки. Если​ — если видима​ как завязаны между​ не так -​ пустыми строками …​: Так не получится.​Когда скрываю, выделяю​ Range, i As​ у нас есть​ области содержатся пустые​

Способ 2. Макрос вставки любых значений

​значения и форматы чисел​ области, куда требуется​сложить​.​ чисел.​Выделите ячейки с данными​ область вставки содержит​ это не является​ или если высота​ собой листы. Нет​​ надо разбираться локально​​ и 15 столбцами​Мне собственно нужно​ нужные данные -​ Long ‘запрашиваем у​​ вот такой список​​ ячейки.​Вставка только значений и​

​ вставить скопированное содержимое.​Добавление значений из области​Выберите нужные параметры.​Значения и исходное форматирование​ и атрибутами, которые​ скрытые строки или​ вас не устраивают,​ строки не =​ никаких одинаковых данных.​Pelena​фильтрую по допустим​ следующее (рассказываю более​ F5 — «выделить»​ пользователя по очереди​ сделок:​Транспонировать​ форматов чисел из​На вкладке​ копирования к значениям​Пункт меню​Только значения и атрибуты​ требуется скопировать.​ столбцы, возможно, потребуется​ выполните действия, описанные​ 0 то вставалять.​ Куча повторяющихся значений.​: Я так понимаю​ 5 столбцу, получаю​ подробно).​ — «только видимые​ диапазоны копирования и​… и в нем нужно поставить​Заменить столбцы копируемых данных​

​ выделенных ячеек.​Главная​ в области вставки.​Что вставляется​ цвета чисел и​На вкладке​ отобразить их, чтобы​ в этой статье,​Dyka​ Кодов нет. Из​ новые данные вставляются​ список из 560​В столбце А​ ячейки».​ вставки Set copyrng​ фиксированную скидку в​ строками и наоборот.​

planetaexcel.ru

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

​Объединить условное форматирование​​в группе​вычесть​
​Все​ размера шрифта.​Главная​ увидеть все скопированные​ чтобы скопировать только​: The_Prist большущее спасибо!!!​ того что было​
​ с помощью заполнения?​ строк… в соседний​ цифровые данные.​А вот как​ = Application.InputBox(«Диапазон копирования»,​
​ 1000 рублей каждому​Вставить связь​Условное форматирование из скопированных​Редактирование​

​Вычитание значений из области​​Все содержимое и формат​Форматирование​нажмите кнопку​ данные.​ видимые ячейки. Например​очень пригодиться в​ предложено могу только​
​Тогда нужно сделать​

​ столбец ПЫТАЮСЬ вставить​​В столбце B​ вставить в колонку​ «Запрос», Type:=8) Set​ «Ашану».​Вставляемые значения связываются с​ ячеек объединяется с​нажмите кнопку​ копирования из значений​ ячеек, включая связанные​Все атрибуты форматирования ячеек,​Копировать​

​Правда горячие клавиши​​ столбцов. И формулу.​ выделить диапазон ячеек,​ и получается что​При фильтре по​ понять.​ вставки», «Запрос», Type:=8)​

​: После того, как​​ были одинакового размера​ нужное значение в​ абсолютную ссылку на​ копирования и вставки​.​ вставки на значения​
​Клавиша C​Вставляемые значения связываются с​ вставить скопированное содержимое.​ на вашем языке.​Выделите ячейки, которые нужно​
​ документ закрывается), но​Вроде все сошлось.​ окне ВЫДЕЛЕНИЕ ГРУППЫ​

​ скрытие ячейки, а​​ столбца А надо​ выделили, нажмите ctrl+c(копировать).​
​ If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <>​ первую ячейку и​

planetaexcel.ru

Таблица Фильтрация ВСТАВКА -СРОЧНО НУЖНА ПОМОЩЬ!!!

​ копируемую ячейку или​​ областей, в поле​В диалоговом окне «​
​ из области копирования.​Вставка только значений в​ исходными. При вставке​На вкладке​ Эта страница переведена​
​ скопировать подробнее, читайте​ я макросы на​http://zalil.ru/33260588​ ЯЧЕЕК поставить переключатель​ не в те​ скопировать в столбец​ Щелкаете левой кнопкой​ copyrng.Cells.Count Then MsgBox​ протягиваем (копируем за​ диапазон ячеек в​Специальная вставка​Специальная вставка​
​разделить​ том виде, как​ связи в копируемые​
​Главная​ автоматически, поэтому ее​ статью выберите ячеек,​ форму-кнопку повесил и​Dyka​ ТОЛЬКО ВИДИМЫЕ ЯЧЕЙКИ,​ которые я фильтранул……​ С.​ в какую хотите​ «Диапазоны копирования и​

​ правый нижний угол​​ новом месте.​диалогового окна в​» в разделе​Деление значений из области​ они отображаются в​ данные Excel вводит​щелкните стрелку рядом​ текст может содержать​ диапазонов, строк и​

​ тогда все получилось.​​: Собственно тема: скрываю​ ввести нужное значение​ВОПРОС как вставить​Далее значение фильтра​ ячейку столбца B​ вставки разного размера!»,vbCritical​ ячейки) вниз:​Примечание:​ группе​Вставить​ вставки на значения​ ячейках.​

​ абсолютную ссылку на​​ с кнопкой​ неточности и грамматические​ столбцов на листе.​

​Dyka​​ строки (или пользуюсь​
​ и нажать +.​ в те которые​ меняется и, все​

​ и нажимаете ctrl+v​​ Exit Sub End​Умный Excel в данном​ Этот параметр доступен только​
​операция​выполните одно из​ из области копирования.​форматы​ копируемую ячейку или​Вставить​ ошибки. Для нас​Совет:​: Только, наверное, не​ фильтром, что чаще).​ В этом случае​ отфильтрованы!!!!​ заново — при​

​ (вставить).​​ If ‘переносим данные​ случае понимает, что​ при выборе​выберите математическую операцию,​

​ указанных ниже действий.​​Параметр​Содержимое и формат ячеек.​ диапазон ячеек в​и выполните одно​ важно, чтобы эта​

​ Чтобы отменить выделение ячеек,​​ обязательно делать проверку​ Есть диапазон ячеек​
​ заполнятся только видимые​

​(можно скопировать отфильтрофанные​​ фильтре по столбцу​
​Так?​
​ из одного диапазона​ вы хотите ввести​все​ который вы хотите​Команда​
​Результат​примечания​

​ новом месте.​​ из указанных ниже​ статья была вам​ щелкните любую ячейку​ на количество выделенных​
​ я хочу чтобы​ ячейки.​ данные на новый​ B, все видимые​Ship​

​ в другой только​​ значения именно в​
​или​ применить к данным,​Задача​Пропускать пустые ячейки​Вставка только примечаний к​Вставить как рисунок​ действий. Параметры в​ полезна. Просим вас​ на листе.​ строк на втором​ копия вставилась в​Гость​
​ лист «вставить значения»​
​ ячейки из столбца​

excelworld.ru

Как при вставке ячеек игнорировать скрытые строки?

​ видимые ячейки -​​: Как вариант могу​ — далее добавить​ А надо скопировать​ что нужно вставить​ i = 1​

​ делает то, что​​в разделе​Команда​Все содержимое и формат​ и атрибутов в​проверка​

​Связанный рисунок​​Вставка​ и сообщить, помогла​Главная​ этого избежать надо​ но вставка происходит​ скинуть вам файл,​ столбец, НО тогда​ в столбец D.​ также не по​

​ For Each cell​​ нужно:​Вставить​Результат​ ячеек, включая Вставить​ области вставки, когда​Только правила проверки данных.​

​Скопированные данные как изображение​​зависит от типа​ ли она вам,​ >​ сделать подсчет скопированных​ во все строки​

​ со ссылкой на​​ данных в выделенных​ с помощью кнопок​Найти и выделить​ ячеек.​ подряд, в т.ч.​ получится буду благодарен…​ ЗАГОЛОВКИ ПОДПУНКТЫ с​ меняется и, все​

planetaexcel.ru

​ как. Если только​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Выделите диапазон A1:A4.
2. На вкладке Home (Главная) нажмите кнопку Find & Select (Найти и выделить) и кликните по Go To Special (Выделить группу ячеек).
3. Выберите опцию Visible cells only (Только видимые ячейки) и нажмите OK.
4. Excel выделит видимые ячейки. Теперь нажмите сочетание Ctrl+C, чтобы скопировать диапазон.
5. Выберите ячейку 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.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, 9 515 скачиваний)

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

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

В этой статье мне хотелось бы представить вам самые эффективные приемы работы в Microsoft Excel, собранные мной за последние 10 лет работы над проектами и проведения тренингов по этой замечательной программе. Здесь нет описания суперсложных технологий, но есть приемы на каждый день — простые и эффективные, описанные без «воды» — только «сухой остаток». На освоение большинства из этих примеров у вас уйдет не более одной-двух минут, а вот сэкономить они вам помогут гораздо больше.

Быстрый переход к нужному листу

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

Это намного быстрее, чем прокручивать ярлычки листов этими же кнопками в поисках нужного.

Копирование без повреждения форматирования

Сколько сотен (тысяч?) раз я видел эту картину, стоя за спиной своих слушателей во время тренингов: пользователь вводит формулу в первую ячейку затем и «протягивает» ее на весь столбец, нарушая форматирование нижерасположенных строк, поскольку такой способ копирует не только формулу, но и формат ячейки. Соответственно, дальше приходится вручную исправлять повреждения. Секунда на копирование и потом 30 — на починку испорченного копированием дизайна.

Начиная с Excel 2002, есть решение этой проблемы — простое и изящное. Сразу после копирования (протаскивания) формулы на весь столбец, нужно воспользоваться смарт-тегом — небольшим значком, который временно появляется в правом нижнем углу диапазона. Нажатие на него выведет список возможных вариантов копирования, где и можно выбрать Копировать только значения ( Fill without formatting). В этом случае формулы копируются, а форматирование — нет:

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

Если вы работаете в Microsoft Excel больше недели, то должны были уже сталкиваться с подобной проблемой: в некоторых случаях при копировании-вставке ячеек их вставляется больше, чем было, на первый взгляд, скопировано. Это может происходить, если копируемый диапазон включал в себя скрытые строки/столбцы, группировки, промежуточные итоги или фильтрацию. Рассмотрим в качестве примера один из таких случаев:

В этой таблице посчитаны промежуточные итоги и сделана группировка строк по городам — это легко понять по кнопкам «плюс-минус» слева от таблицы и по разрывам в нумерации видимых строк. Если выделить, скопировать и вставить данные из этой таблицы обычным способом, то мы получим 24 лишних строки. Нам же хочется скопировать и вставить только итоги!

Можно решить проблему, кропотливо выделяя каждую строку итогов и удерживая при этом клавишу CTRL — как для выделения несмежных диапазонов. Но если таких строк не три-пять, а несколько сотен или тысяч? Есть другой, более быстрый и удобный путь:

Выделите копируемый диапазон (в нашем примере — это A1:C29)

Нажмите на клавиатуре клавишу F5 и затем кнопку Выделить ( Special) в открывшемся окне.
Появится окно, позволяющее пользователю выделять не все подряд, а только нужные ячейки:

В этом окне выберите опцию Только видимые ячейки ( Visible cells only) и нажмите ОК.

Полученное выделение теперь можно смело копировать и вставлять. В результате мы получим копию именно видимых ячеек и вставим вместо ненужных 29-ти только необходимые нам 5 строк.

Если есть подозрение, что подобную операцию вам придется проделывать часто, то имеет смысл добавить на панель инструментов Microsoft Excel кнопку для быстрого вызова такой функции. Это можно сделать через меню Сервис>Настройка (Tools> Customize), затем перейти на вкладку Команды (Commands), в категории Правка (Edit) найти кнопку Выделить видимые ячейки (Select visible cells) и перенести ее мышью на панель инструментов:

Превращение строк в столбцы и обратно

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

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

Щелкаем правой кнопкой мыши по пустой ячейке и выбираем команду Специальная вставка ( Paste Special)

В открывшемся окне ставим флаг Транспонировать ( Transpose) и жмем ОК:

Быстрое добавление данных в диаграмму

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

Другой путь — простой, быстрый и красивый — выделить ячейки с новыми данными, скопировать их (CTRL+C) и вставить (CTRL+V) прямо в диаграмму. Excel 2003, в отличие от более поздних версий, поддерживает даже возможность перетаскивания выделенного диапазона ячеек с данными и забрасывания его прямо в диаграмму с помощью мыши!

Если хочется контролировать все нюансы и тонкости, то можно использовать не обычную, а специальную вставку, выбрав в меню Правка>Специальная вставка ( Edit> Paste Special). В этом случае Microsoft Excel отобразит диалоговое окно, позволяющее настроить куда и как именно будут добавлены новые данные:

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

Заполнение пустых ячеек

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

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

Есть способ решить эту задачу быстро и красиво при помощи одной формулы:

Выделите все ячейки в столбце с пустотами (т.е. диапазон A1:A12 в нашем случае)

Чтобы в выделении остались только пустые ячейки, нажмите клавишу F5 и в открывшемся окне переходов — кнопку Выделить. Увидите окно, позволяющее выбрать — какие именно ячейки мы хотим выделить:

Установите переключатель в положение Пустые ( Blank ) и нажмите ОК. Теперь в выделении должны остаться только пустые ячейки:

Не меняя выделения, т.е. не трогая мышь, введем формулу в первую выделенную ячейку (А2). Нажмите на клавиатуре на знак «равно» и затем на «стрелку вверх». Получим формулу, которая ссылается на предыдущую ячейку:

Чтобы ввести созданную формулу сразу во все выделенные пустые ячейки, нажмите не клавишу ENTER, а сочетание CTRL+ ENTER. Формула заполнит все пустые ячейки:

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

Выпадающий список в ячейке

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

Идея очень проста — во всех случаях, когда вы должны ввести данные из какого-либо набора, вместо ручного ввода в ячейку с клавиатуры выбирать нужное значение мышью из выпадающего списка:

Выбор товара из прайс-листа, имени клиента из клиентской базы, ФИО сотрудника из штатного расписания и т.д. Вариантов применения этой функции множество.

Чтобы создать выпадающий список в ячейке:

Выделите ячейки, в которых вы хотите создать выпадающий список.

Если у вас Excel 2003 или старше, то выберите в меню Данные>Проверка (Data>Validation). Если у вас Excel 2007/2010, то перейдите на вкладку Данные (Data) и нажмите кнопку Проверка данных (Data validation).

В открывшемся окне выберите вариант Список (List) из раскрывающегося списка.

В поле Источник (Source) надо указать значения, которые должны быть в списке. Тут возможны варианты:

Вписать в это поле текстовые варианты через точку с запятой

Если диапазон ячеек с исходными значениями находится на текущем листе — достаточно его просто выделить мышью.

Если он находится на другом листе этой книги, то ему придется заранее дать имя (выделить ячейки, нажать CTRL+F3, ввести имя диапазона без пробелов), а затем прописать это имя в поле Источник:

Также, именованный диапазон может быть динамическим, т.е. созданным на основе функций СМЕЩ ( OFFSET) и СЧЁТЗ ( COUNTA). Тогда при дописывании к нему новых данных, они будут автоматически в него включаться и отображаться в выпадающем списке.

На вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) можно ввести подсказку для пользователя, которая будет появляться при выделении ячейки ввода и ругательное сообщение, если пользователь вместо выбора одного из разрешенных вариантов упрямо попытается вписать свой текст напрямую с клавиатуры:

Заключение

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

Копирование и вставка в Excel, включая скрытые столбцы

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

Добавление картинки цели — то есть в надежде вставить скрытое содержимое столбцов B и C в целевую электронную таблицу. Это вообще возможно?

Вероятно, не очень хорошая форма, чтобы задать 2 вопроса в одном посте, однако есть ли альтернативы выполнению функции фильтрации и копирования-вставки в другую электронную таблицу вручную? То есть:

• запустить ручной фильтр для очистки пробелов в поле Количество;
• сделать выбор
• сделать ручную функцию Ctrl + C — Ctrl + V

Есть ли способ сделать это проще? К сожалению, пока нет VBA или макро-опыта.

Нет, вы не можете сделать это с обычными функциями Excel, так как Excel не может знать, какие столбцы / ячейки пропустить, когда один из столбцов имеет пустые значения, это что-то, что должен решить человек.

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

Просмотр / Макросы / Запись макроса

Назовите свой макрос

Делайте то, что вам нужно, помня, что Excel записывает каждое ваше движение, преобразовывая их в коды VBA в фоновом режиме. Для вашего случая сделайте следующее:

• Отфильтруйте пробелы с помощью поля со списком фильтров
• Выберите диапазон, используя CTRL-G / Специальный / Текущий регион (не выделяйте ячейки мышью или с помощью клавиатуры, ваш код должен быть универсальным, не должен содержать ручные диапазоны, поскольку вы не хотите выполнять какое-либо кодирование)
• CTRL-C для копирования
• Если ячейка «для вставки» не зафиксирована во всех ваших случаях, вам следует прекратить запись макроса здесь. Если ячейка вставки зафиксирована, вставьте содержимое во время записи макроса.

После записи макроса назначьте ярлык для нового макроса, используя: Макросы / Просмотр макросов / Меню параметров

Вуаля! Теперь вы можете делать точно то же, что и при записи макроса, с помощью этой комбинации клавиш. Если вы не вставляли содержимое во время записи, то вы с / б используете макро-ярлык и переходите к ячейке, которую хотите вставить, и нажимаете CTRL-V .

Когда вы почувствуете себя достаточно уверенно, попробуйте меню « Правка» в макросах и посмотрите, какой код у вас в руках, возможно, внесите небольшие изменения и т. Д. Я видел много людей, которые вначале не знакомы с базовым кодированием, но каким-то образом начали писать свои собственные коды. увидев эту функцию в Excel. Удачи!

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

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

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

Выберите ячейки, которые вы хотите скопировать и вставить.

Перейдите на вкладку «Главная» и коснитесь значка «Найти и выбрать». Похоже на увеличительное стекло.

В появившемся раскрывающемся меню выберите «Перейти к специальному».

Нажмите «Только видимые ячейки» в появившемся окне, затем нажмите «ОК».

Нажмите Ctrl + C в Windows или Command + C на Mac, чтобы скопировать выбранные ячейки.

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

Теперь вы скопировали и вставили строки в Excel, не отображая скрытые строки.

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

Нажмите Alt + ; (точка с запятой) в Windows или Cmd + Shift + Z на Mac. Этот ярлык выбирает только видимые ячейки.

Нажмите Ctrl + C для Windows или Command + C для Mac, чтобы скопировать данные.

Перейдите к новой таблице и нажмите Ctrl + V для Windows или Command + V для Mac, чтобы вставить туда информацию.

Ваш новый рабочий лист будет содержать данные только из видимых строк.

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

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

Как только текст будет выделен, уберите палец с экрана. Появится меню с опциями для вырезания, копирования или вставки.

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

Нажмите «Копировать» в меню, отображаемом рядом с выбранными ячейками.

Перейдите к новому рабочему листу, затем нажмите и удерживайте палец на экране. Через несколько секунд отобразится слово «Вставить».

Нажмите «Вставить», чтобы ввести данные в рабочий лист.

Теперь вы скопировали и вставили только видимые строки в Excel.

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

Теперь ваша новая таблица будет заполнена только видимыми строками.

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

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

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

Как только вы уберете палец с экрана, появится меню с вариантами вырезания, копирования или вставки.

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

Нажмите «Копировать» в меню рядом с выделенным текстом.

Перейдите к новому рабочему листу и удерживайте палец на экране.

Нажмите «Вставить» во всплывающем меню, чтобы ввести данные в новую таблицу.

Теперь видимые строки скопированы и вставлены из вашего старого листа.

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

Вы успешно скопировали и вставили только видимые строки вашего рабочего листа.

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

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

Выберите ячейки, которые вы хотите скопировать и вставить.

Нажмите Ctrl + C, чтобы скопировать данные из выбранных ячеек.

Перейдите на новый рабочий лист и нажмите Ctrl + V, чтобы вставить информацию.

Теперь видимые строки скопированы и вставлены в вашу новую таблицу.

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

Новая таблица теперь содержит информацию только из видимых ячеек.

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

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