Excel как скопировать отфильтрованные ячейки

Excel скопировать только отфильтрованные ячейки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще: Как в excel защитить ячейку от редактирования

.odssolotze:
могу. скажем такие позиции:
протащилась на большое
может кто знает, 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». заполнить таблицу в нажимаем на клавишу

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

С. выбираем, в появившемся
A6: Попробуйте выделять вместе мне нужны. Office. По сему может попроще будет была активна, надоКопировать как это можно: Конечно не понятно таблицы, а не в любой версии протягивание работало.В столбце С Excel повторяющимися данными» «Ctrl», удерживая ееО разных способах копирования оке, функцию «Копировать».и нажмите с заголовкомА как сделать, у вас и разобраться. еще куда нажатьВставить реализовать, буду благодарен). как определяете соответствие для вставки В Экса.Хелп! )))

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

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

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

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

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

_Boroda_ кнопку в компе.

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

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

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

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

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

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

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

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

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

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

Читать еще: Как в эксель делать диаграммы

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

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

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

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

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

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

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

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

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

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

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

. и в нем нужно поставить фиксированную скидку в 1000 рублей каждому «Ашану».

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

Умный Excel в данном случае понимает, что вы хотите ввести значения именно в отфильтрованные ячейки и делает то, что нужно:

Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для «Ашанов» не фиксированная, а составляет 10% от суммы сделки, то в первую отфильтрованную строку можно ввести не константу (1000), а формулу (=C2*10%) и также скопировать вниз.

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

Другое дело, если вам необходимо вставить в отфильтрованные ячейки не одинаковые значения или формулы, а разные, да еще и брать их из другого диапазона. Тогда придется использовать несложный макрос. Нажмите сочетание клавиш Alt+F11, в открывшемся окне Visual Basic вставьте новый пустой модуль через меню Insert — Module и скопируйте туда этот код:

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

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

Можно еще немного проще:
1. отфильтровать
2. выделить весь столбец — F5 — Выделить — Только видимые
3. не снимая выделения, ввести формулу в первую ячейку и нажать Ctrl+Enter

Спасибо за полезное уточнение

Спасибо Вам большое! Ваши «ПРИЕМЫ» спасает огромное количество рабочих часов!
Повесила макрос на горячую клавишу и красота
Вопрос.
1. Как провести копирование данных из одного фильтрованного диапазона в аналогичных по размерам другой?
2. Как сохранить макрос так, чтоб его можно было использовать в других открытыхсозданных файлах, чтоб он стал «постоянным»?

1. Вот так:
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.Cells.Cells.Count <> copyrng.Cells.Count Then
MsgBox «Диапазоны копирования и вставки разного размера!»,vbCritical
Exit Sub
End If

‘переносим данные из одного диапазона в другой только в видимые ячейки
For Each cell In pasterng
If cell.EntireRow.Hidden = False Then
cell.Value = Cells(cell.Row, copyrng.Column).Value
End If
Next cell
End Sub

Николай Павлов, большое спасибо за макрос! Я, хоть и сталкивалась с excel, но с макросами дела не имела. А тут понадобилось в большущую таблицу выгрузки сайта, фильтруя ее значения, вставлять не цифры и формулы, а текст, уникальные названия товаров. Копировать значения ячеек по одному совершенно нереально, долго и запутаешься. А с макросом (диапазон вставки и копирования были на разных листах одной книги) все получилось, пока с одним значением фильтра. Но встретились в конце такая закавыка.
Фильтр сняла, вернее, поставила галочки у всех значений, как у вас в видео. Здесь было все нормально, все вернулось к прежнему виду, правда, я не проверила всю портянку. Только вот почему-то после нажатия кнопки сохранить мне выпало окошко — как будто ошибка. Там написано:

Читать еще: Как в файл эксель вложить файл

«выгрузка.csv» может содержать возможности несовместимые с форматом «CSV( разделители — запятые)». Сохранить книгу в этом формате?
Чтобы сохранить этот формат, удалив все несовместимые возможности, нажмите кнопку Да.
Чтобы сохранить все возможности, нажмите кнопку нет и сохраните файл в формате последней версии excel.
Чтобы узнать, какие возможности могут быть потеряны, нажмите кнопку Справка.

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

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

В общем-то смысл статьи уже, думаю, понятен из названия. Просто чуть-чуть расширю.

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

Как скопировать отфильтрованные данные без копирования пустых ячеек

0 Anu [2018-08-31 11:31:00]

сценарий

У меня есть лист с большим количеством ценностей. Я использую макрос для фильтрации этих значений.

Что мне нужно

Мне нужно скопировать только отфильтрованные значения из текущего листа на другой лист. Я делаю следующий путь

вопрос

Вышеприведенный код делает именно то, что предполагается сделать, копируя только видимые ячейки. Но проблема в том, что он копирует весь лист, включая все пустые ячейки до номера строки 1048480 и даже больше. Но мои данные только до строки номер 12. Как скопировать только видимые ячейки, содержащие данные, а не все эти пустые ячейки?

Фотографий

Filtered Sheet После копирования отфильтрованных данных на другой лист