Как скопировать только то что отфильтровано в excel

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

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

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

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

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

   

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

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

   

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

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

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

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

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

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

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

​Смотрите также​ строки?​ и пишут все​: Лена, у Вас​может есть еще​

​Понимаю, что туплю,​ счастье. Экс думать​

​Если вам принципиально​ Но для этого​ Очень полезно​ точно Вы отфильтровали​

​ так.​.​ передвигаем диапазон в​

1. ​ без изменения ссылок».​​ функцию «Специальная вставка».​​Урок подготовлен для Вас​
2. ​По умолчанию Excel копирует​​Kay​​solotze​​ не Excel, а​​ какой вариант?​ но надо сделать…​​ не умеет. Да​​ копировать, то например​
3. ​ надо изучать Эксель​​4eng8​​ или все же​Очень удобная функция, при​​Так же, функцией​​ другое место. Выделенный​
4. ​Как переместить столбец, таблицу​ Ставим галочку у​​ командой сайта office-guru.ru​​ как видимые, так​
5. ​: Элементарно Ватсон ))),​​: igrtsk,​​ ОпенОфис. Там это​​solotze​​ArkaIIIa​

​ я вот тоже​

​ на фильтре вы​Z​
​: В большую (до​
​ скрыли лишние строки?​

​ заполнении ячеек, ничего​

office-guru.ru

Как скопировать в Excel.

​ «Заполнить» можно копировать​​ диапазон скопируется.​ ​ в Excel быстро.​ слов «Значения», нажимаем​Источник: http://www.excel-easy.com/examples/copy-visible-cells-only.html​ и скрытые ячейки.​​ сам сталкивался с​​это я уже​ вообще не нужно​​:​: Вы же всё​ не понял бы,​ выбрали необходимый товар,​
​: Микроскопом гвозди забивать​ 1 000 000​ ​ Ведь при фильтрации​
​ высчитывать не нужно.​ не только данные​Как заполнить пустые ячейки​Выделяем ячейки, диапазон​ «ОК». Получилось так.​Перевел: Антон Андронов​ Однако можно скопировать​
​ подобным. Это глюк​ поняла. Так я​ — фильтруете ячейки,​Ну, попробуйте тогда Сервис-Настройка-Команды,​ сами написали.​ что это одно​ выделяете колонку, где​ можно, но…​ строк) таблицу нужно​ действительно копирование идет​В следующей статье​ ячейки, но и​ в​ или всю таблицу.​Как скопировать формулу в​Автор: Антон Андронов​ только видимые ячейки.​ ​ 2007офиса.​ смогу как то​​ выделяете всю кучу​
​ в категории «Правка»​1) Выделяете весь​ и то же.​ необходимо вставить цену,​См. «Приемы», функция​ проставить цены по​ только в видимые​ мы собрали перечень​ формат ячейки.​
​Excel быстро​ Наводим мышку на​ Excel без формата​Рассмотрим несколько приемов,​В этом примере строка​Прежде чем копировать,​ сделать такое копирование?​ сразу и, не​ кнопка «Выделить только​ диапазон​ Вообще, для этого​ нажимаете «Ctrl+G», кнопку​
​ =ВПР() в подстановке​ каждому виду продукции:​ ячейки, а при​ способов анализа данных​Но, в диалоговом​.​ границу выделенного диапазона​
​.​как быстро копировать в​
​ 2 скрыта:​ удали любую пустую​Никто не может помочь​ задумываясь о плохом,​ видимые ячейки».​
​2) Тыкаете F5​ пользуются артикулами товара,​
​ выделить, и в​ значений (цен) через​Товар Цена​ скрытии во все.​ в Excel, в​ окне функции «Заполнить»,​В Excel есть​ (не на угол),​Многим знакома такая​Excel таблицу, ячейки, строку,​Если просто скопировать, а​ строку например снизу​ — никто просто!​ просто вставляете в​
​это вообще не​3) В появившемся​ или другими уникальными​ появившемся окне ставите​ подстановочную таблицу… как​Конфеты​
​ Если данное правило​ ​ зависимости от потребностей.​​ есть еще​
​ функция, которая поможет​ указатель мышки станет​ ситуация. Когда мы​ столбец, копировать формат,​ затем вставить этот​ таблицы. А потом​
​Такая задача: есть​ нужное место.​ могу найти!!! Либо​ окне внизу кнопочка​ идентификаторами.​ галку напротив «только​ вариант…​Печенье​ не действует в​ Смотрите статью «Анализ​функция Excel «Прогрессия»​ быстро заполнить ячейки​
​ в виде четырехсторонней​ копируем автозаполнением (протягиваем​ ​ скопировать без формата,​​ диапазон, результат получается​
​ уже копируй нужный​ Excel 2007, в​igrtsk​ я туплю, либо​ «выделить»​Z​
​ видимые ячейки», ну​ps Пример *.xls​
​Вафли​ Вашем случае, то​ данных в Excel».​. Этой функцией удобно​ или повторяющимися данными,​ стрелки. Нажимаем на​ черный крестик справа​​ заполнить пустые строки​ следующим:​ тебе диапазон.​ листе множество данных​: А я уже​ версия компа чудная.​4) Ставите флажок​: Потерял топор -​ а потом в​ возможно ускорит решение​
​Торты​ скорее всего это​ZoomZoom​ заполнять ячейки числами,​ или данными с​ левую кнопку мышки​ внизу ячейки), то​
​,​ ​Чтобы скопировать только видимые​​Ram​
​ (1302 строки). Я​ грешным делом подумал,​Не пойму в​ на «Только видимые​ поищи за поясом…​
​ эти выделенные ячейки​ вопроса…​Шоколад​​ лаги​​: Я отфильтровал таблицу​ например порядковый номер​ прогрессией.​ и двигаем выделенный​ копируется и формат​заполнить столбец числами с​ ячейки, выполните несколько​: правой клавишей мыши​ фильтрую их, чтобы​
​ что тему затролили​ чем дело.​ ячейки»​-​ или вставляйте необходимую​4eng8​Последовательно фильтрую ячейки​
​ZoomZoom​
​ по одному из​ с арифметической прогрессией​Например, у нас​ диапазон в другое​ ячейки, что портит​ прогрессией​ простых действий:​
​ — «специальная вставка»​ осталось только то,​solotze​
​ArkaIIIa​5) Тыкаете ОК​solotze​ цену, либо вручную​: Функция =ВПР() в​​ по наименованию продукции​: я в автофильтре​ полей,​
​ «1». Можно заполнить​ такая таблица.​ место листа Excel.​ нашу таблицу. Например,​, т.д. Основные приемы​Выделите диапазон​ насколько я помню…​

excel-office.ru

Как протянуть строку только на отфильтрованные ячейки (Как протянуть строку только на отфильтрованные ячейки)

​ что мне нужно.​​: что значит Опен-офис?​: У меня, к​6) У вас​
​: как в экселе​ ее введите и​
​ данном случае не​ и вставляю соответствующие​ снял галочки с​часть записей стали​ ячейки с другим​
​Чтобы заполнить пустые ячейки​ Выделенные ячейки переместились​ скопировалось так.​ копирования смотрите в​A1:A4​Игорь​
​ К примеру, у​ И как фильтровать?​ сожалению, нет под​ автоматом выделяются только​ скопировать данные без​ нажмите «Ctrl+Enter»​ применима, т.к. нет​
​ цены. По окончании​ некоторых вариантов, это​ скрыты.​
​ шагом прогрессии или​

​ словом «хлеб», выделяем​​ в новое место.​Скопировался формат верхней, копируемой​ статье «Копирование в​.​: геморойно это (если​ меня остались строки​solotze​ рукой 2003 экселя.​ видимые ячейки, вы​ скрытых ячеек​Z​ подстановочной таблицы с​ работы замечаю, что​ называется «скрыть»?​

​Я в верхней​​ заполнить ячейки с​ ячейки А23:А27 в​Еще один способ​ ячейки – скопировался​

​ Excel».​​На вкладке​ большие объемы) ,​

​ 1, 23, 34,​​: _Boroda_,​ Сделайте хотя бы​ их ctrl+с и​нашла вот это​: Есть, только ТС​ такими же значениями,​ одна из цен​ZoomZoom​

​ ячейке оставшихся данных​​ геометрической прогрессией.​ столбце А. На​ заполнить быстро столбец​ жирный шрифт. Чтобы​Как скопировать значение ячейки​Home​ но тоже выход:​ 36, 45, 344,​

​Надо что бы​​ скрин шапки, что​ куда нужно ctrl+v​

excelworld.ru

Почему при копировании в таблицу с фильтром вставляемое значение попадает в скрытые фильтром ячейки?

​ -​​ не знает, что​ как в рабочей.​ протаскивается на другие​: Irysha, спасибо большое,​ написал к примеру​
​Вспоминаем, арифметическая прогрессия​
​ закладке «Главная» в​
​ в таблице описан​
​ вставить скопированный текст​
​ в​
​(Главная) нажмите кнопку​
​1. фильтруешь данные​ 561 (в реале​ при копировании, скрытые​ у вас там​solotze​Выделяете диапазон — F5​ она — соответствующая,​ Определить соответствие можно​ товары. Очевидно, что​ помогло )))​ «zzz» и пытаюсь​ – прибавляет к​ разделе «Редактирование» нажимаем​ в статье «Заполнить​ не жирным, копируем​Excel без формата.​Find & Select​2. выбираешь КАЖДУЮ​ их в разы​ ячейки (цветные), не​

​ в менюшке верхней​​: так и делаю,​ — Выделить —​ т.е. — подстановочная…​ только визуально.​ при вставке значений​Irysha​ протянуть вниз,​ предыдущей цифре число​ на кнопку «Заполнить».​

​ быстро диапазон, массив​​ ячейки. А вставляем,​Копировать только значение​
​(Найти и выделить)​ строку отфильтрованных данных​ больше, но для​ копировались.​ вообще есть.​
​ не активна F5​ Только видимые ячейки​Однако — =ВПР(C2;$G$1:$H$13;2;0)​

​Во вложении показано,​​ в отфильтрованные ячейки​: Да, и я​дальше если снять​ (шаг), геометрическая прогрессия​Из появившегося списка​ в Excel» тут.​ не через функцию​ в ячейки Excel​
​ и кликните по​ (просчелкиваешь справа на​ примера я взял​igrtsk​solotze​Pelena​ — Ок. А​4eng8​ как примерно выглядит​ вставляемое значение попадает​

​ потестировала на своих​​ фильтр — видно​ – умножает предыдущую​ выбираем, в какую​

​Как переместить строку в​​ «Вставить», а через​ без формата копируемых​Go To Special​ сером фоне, где​
​ только несколько). И​:​: F5 нашла как​: F5 — функциональная​ дальше уже копируете,​: Я пользуюсь функцией​ проблема: последовательно подставляем​ в ячейки, скрытые​ файлах — копирование​ что «zzz» скопировалось​ цифру на число​ сторону копировать слово.​Excel​ функцию «Специальная вставка».​ данных, чтобы не​(Выделить группу ячеек).​ стоят номера строк)​

​ вот, мне этот​​solotze​ функционирует. Высветилось окошко.​ клавиша на клавиатуре.​ как обычно​
​ =ВПР() там, где​

​ значения цен каждому​​ фильтром. Учитывая объем,​ происходит только в​ на абсолютно все​ (шаг).​ Можно копировать ячейки​.​ В появившемся диалоговом​ нарушить формат нашей​Выберите опцию​ с нажатой клавишей​ диапазон отфильтрованных мною​,​ Но нет «выделить»​ Как она может​- но где​

​ это возможно. Но​​ товару, а при​ переделывание отнимает много​ выделенные ячейки. Может​ ячейки, в том​Например, так.​ по строке (вправо,​Выделяем строку, нажимаем​ окне выбираем функцию​ таблицы. Например, у​Visible cells only​ CTRL​ данных нужно выделить​

​Судя по расширению​​ArkaIIIa​ быть не активна?​
​ все это найти​

planetaexcel.ru

Копирование в экселе (как скопировать без скрытых ячеек)

​ конкретно в этом​​ снятии фильтра обнаруживаем,​ времени и нервов.​ я чего-то не​
​ числе отфильтрованные (скрытые)​В столбце А заполнили​​ влево), по столбцу​ на любую границу​ «Значение».​ нас такая таблица.​(Только видимые ячейки)​3. правой кнопкой​​ и скопировать в​ файла (​:​ArkaIIIa​
​ на клавиатуре не​ случае нужно сопоставлять,​
​ что цена 92,5​
​ Поэтому прошу помощи:​
​ знаю и мастера​
​причем я помню​
​ по арифметической прогрессии​
​ (вверх, вниз), т.д.​
​ выделенной строки и,​
​Или, нажимаем на​Нам нужно скопировать из​ и нажмите​
​ по выделенному тексту​ другой лист (или​

​.ods​​solotze​:​
​ могу.​ скажем такие позиции:​
​ протащилась на большое​
​ может кто знает,​ Excel увидят больше​ что вроде бы​
​ с шагом «1».​ Мы выбрали функцию​ удерживая мышку нажатой,​
​ значок внизу справа​
​ столбца B только​OK​ — «копировать»​ документ (файл). Если​) вы используете в​

​Повторно. Не «видя»,​​solotze​еще вот это​

​ «Ферреро пралине конф203гр*16/Москва»​​ каличество ячеек, по​ в чем проблема​KuklP​ эта фишка работала​

​ Когда нажимаем кнопку​​ Excel заполнить «Вниз».​​ перемещаем строку в​
​ выделенного диапазона. Ставим​ данные без формата​.​4. ставишь курсор​

​ я банально выделяю​​ работе не офисный​ что у Вас​Ну, попробуйте тогда​
​ нашла​ и «Конфеты «Ferrero​ всем товарам.​
​ и как ее​: Все же лучше​

​ правильно — копировала​​ «Прогрессия», выходит диалоговое​​ Получится так.​ другое место.​ галочку у функции​ (без синего цвета​
​Excel выделит видимые ячейки.​ куда тебе надо,​ отфильтрованное и вставляю​ пакет от Microsoft​
​ там на экране​ Сервис-Настройка-Команды, в категории​

​Меню​​ Pralina» (без этикета)»,​KuklP​ решить?​ выделить нужный диапазон​ «zzz» только на​ окно. Мы заполнили​Как заполнить пустые ячейки​Чтобы, не просто​

​ «Заполнить только значения».​​ ячеек). Выделяем столбец​ Теперь нажмите сочетание​ правой кнопкой -​

​ в другой файл,​​ , а Open​​ происходит — помочь​
​ «Правка» кнопка «Выделить​Правка​ чего функция ВПР​: Кто сказал? Как​KuklP​ — F5 -​ видимые ячейки (которые​ так.​ в столбце с​

​ переместить, а скопировать​​ Скопированный формат (жирные​

​ B, нажимаем правой​​Ctrl+C​ «вставить»​ то вставляется всё:​ Office либо LibreOffice..​ Вам очень трудно.​ только видимые ячейки».​Перейти​ сделать не сможет​ раз применима. Читайте​: Никакой проблемы нет.​

​ только видимые. Вводим​​ не отфильтрованы)​В столбце B установили​ разными данными, смотрите​

​ ячейки, наводим на​​ границы) исчезнет.​ мышкой на любую​

​, чтобы скопировать диапазон.​​вот и все!​
​ и скрытые строки,​А рекомендации вам​ Надергайте скринов и​solotze​

​Выделить​​ (хотя если кто-то​​ справку по ней.​​ Фильтр предназначен для​
​ нужное значение и​Вот мне нужно​​ арифметическую прогрессию с​​ в статье «Как​ границу выделенного диапазона,​Получится так, смотрите столбец​ выделенную ячейку и​Выберите ячейку​
​Сергей каханский​ и те, что​ дают по MS​ приложите к сообщению,​: может чтоб она​Только виденные ячейки​ подскажет дельный совет,​Ivan.kh​

​ отбора значений ИЗ​​ ctrl+Enter. Так сработает​
​ именно так чтобы​ шагом «10».​ заполнить таблицу в​ нажимаем на клавишу​

excelworld.ru

Как в Excel 2007 скопировать в другой документ отфильтрованные данные?

​Ctrl+V​​Андрей школьный​ чтобы скопировались и​ нет тех самых​solotze​
​такаюсь уже пол​но не могу​KuklP​ визуально, если нет​ нее. А задача​ZoomZoom​

​Irysha​​ с шагом «2».​Копирование форматирования в​ мышкой на границу​

​ статье «Как скопировать​​ на верхнюю ячейку​, чтобы вставить диапазон.​: Попробуйте выделять вместе​
​ вставились в другой​
​ меню и кнопок,​: вот что получается​ дня в эту​ все это найти​: Наведите порядок в​ соответствующей таблицы.​ решается формулами, макросами.​
​: Спасибо еще раз!​: Возникает вопрос: а​ Заполнили диалоговое окно​
​Excel​ выделенного диапазона и​ формулу в Excel​ столбца C, выбираем​
​Результат:​

​ с заголовком​​ документ ТОЛЬКО отфильтрованные​ о которых говорят​

​_Boroda_​​ кнопку​ в компе!!!​

​ записях. И будет​

@NSK-Mar 

As others have stated, Excel does not allow you to paste into a filtered data. The best way to do this is to sort instead of filter. The easiest way to do this, especially for information that is not easily sortable, is to:

— filter your info

— fill the cells of your filtered list with a color

— Clear the filter

— do a custom sort of the list by color and then by a-z (to make it match up with the information you are pasting in)

— Paste your information in your sheet

— Reorder the colors from your cells and reorder the information if necessary.

Note: If the original order of your information is important, do what one user suggested and add a column to your original sheet and number each row, so you can resort back to its original order.

Also, if you are working with extremely large lists where you first have to identify the matching cells before copying over the information, you can always use conditional formatting to identify and highlight any matching cells in a column from one sheet to another within the same workbook. After doing this, you can then follow the steps above to copy the information over. 

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

Если некоторые ячейки, строки или столбцы на листе не отображаются, вы сможете скопировать все ячейки (или только видимые ячейки). По умолчанию 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. Удачи!