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

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

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

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

paste-to-visible1.png

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

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

paste-to-visible2.png

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

paste-to-visible3.png

Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для "Ашанов" не фиксированная, а составляет 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

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

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

 



19.04.2013 08:20:50
Добрый день. Есть ещё один способ без макроса, но более удобный на больших диапазонах чем первый.
Сначала поступаем так же как и в способе 1: фильтруем, в первую ячейку вводим нужное значение или формулу. А затем копируем ячейку с введенным значением, далее (не отменяя копирования) выделяем нужный диапазон - поскольку это обычно весь столбец, то удобно использовать  ctrl+shif+вверх/вниз. Далее нажимаем ctrl+G, нажимаем кнопку "Выделить", отмечаем пункт "только видимые ячейки", далее "ОК" и вставка. Ву-а-ля
Чем для меня он лучше Способа №1? Иногда приходится вот таким образом вставлять данные на диапазоне в несколько тысяч строк - замахаешься тянуть за уголок. Двойной щелчок на угле ячейки не сработает на весь диапазон, если в ячейках диапазона есть пропуски (пустые ячейки среди полных) или наоборот  - а у меня такое часто и густо....
19.04.2013 21:52:52
Можно еще немного проще:
1. отфильтровать
2. выделить весь столбец - F5 - Выделить - Только видимые
3. не снимая выделения, ввести формулу в первую ячейку и нажать Ctrl+Enter

Спасибо за полезное уточнение :)
Tim
10.12.2013 11:52:05
После выделения диапазона "ctrl+shif+вверх/вниз", можно нажать "Ctrl+D" и значением заполнится выделенный, отфильтрованный диапазон.
Ctrl+R заполняет выделенные ячейки в строке.
Позволю себе выложить здесь ссылку "в тему"(надеюсь Николай не будет против): Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
Достаточно давно написал код, который выложен в статье. Можно скопировать только видимые ячейки и вставить так же только в видимые. Могут быть скрыты как строки, так и столбцы. В общем совершенно без разницы что и как скрыто, вставлено будет в то, что видно :D
09.05.2013 15:41:47
Я только "за", спасибо - ценная штука! :)
30.05.2013 15:38:20
Очень полезная вещь, давно видел, но не думал что в работе мне пригодится, а тут как припекло вставлять в большие массивы, вспомнил об этой теме. Спасибо, Николай и The Prist оба макроса положил себе в арсенал надстройки.
13.09.2013 07:41:02
Здравствуйте.
Очень часто приходится работать с большими таблицами данных (до 10000 строк). Постоянно сталкиваюсь со следующей проблемой - при фильтрации строк по какому либо параметру, в случае если этих строк отфильтровалось довольно мало - таблица начинает жутко тормозить (процессор i7 + 4 Гб оперативы) и работать с ней практически невозможно.
Вопрос - есть ли альтернативные способы работы с фильтрацией больших таблиц ? Причем уточнюсь - мне не нужны результаты какого либо подсчета - для этого я пользуюсь сводными таблицами. Мне нужно копировать отфильтрованные данные или тупо посмотреть результат фильтрации находящийся в конце таблицы.
Спасибо.
01.10.2013 11:27:05
Николай, часто использую данный макрос, но есть к нему одно замечание.... он работает только если данные находятся в одной книге. Если же данные находятся в разных книгах, то выдаёт ошибку
"Диапазон копирования и вставки разного размера" - хотя размеры одинаковы. Если же беру тот же лист с которого копирую, переношу в книгу в которую вставляю, всё работает (так что проблема точно не в размерах).
Вы не могли бы его адаптировать для работы с разными книгами?
01.11.2013 08:49:26
ОГРОМНЕЙШЕЕ СПАСИБО ЗА МАКРОС, СПАСЛИ МОЖНО СКАЗАТЬ ЖИЗНЬ!!!
01.11.2013 10:43:31
Не за что, Самвел :)
04.06.2014 17:00:44
Доброе время суток! При применении макроса у меня выдает ошибку о разном размере диапазонов: макрос считает и скрытые ячейки... Это так и должно быть? Уже второй день разгадываю эту задачку(( Помогите пожалуйста советом, подскажите как вставить скопированный диапазон в отфильтрованные ячейки.
25.06.2014 14:12:30
Спасибо Вам большое! Ваши "ПРИЕМЫ" спасает огромное количество рабочих часов!
Повесила макрос на горячую клавишу и красота :)
Вопрос.
1. Как провести копирование данных из одного фильтрованного диапазона в аналогичных по размерам другой?
2. Как сохранить макрос так, чтоб его можно было использовать в других открытых\созданных файлах, чтоб он стал "постоянным"?

Заранее спасибо !
09.07.2014 17:04:54
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
20.09.2018 15:18:16
Большое спасибо!
25.07.2014 10:21:06
Отличный макрос! Спасибо.
Вопрос: при указании диапазонов копирования и вставки не дает перейти на другую книгу (ни Alt+Tab, ни через панель задач не переключает). Это только у меня так или это особенность макроса?
14.10.2014 15:19:13
Добрый день. Один вопрос, этот макрос отвязывает от формул и показывает только результат. Можно ли его переписать чтобы данных которые мы вставляем оставались формулы. Например у меня есть список где сверху вниз прописанны формулы:
=b1
=b2
=b3
...
Мне нужно перенести эти формулы в отфильтрованные ячейки. Если нет, то может можете подсказать другой вариант?
31.10.2014 11:28:06
Excel 2003. В вашем примере: как вставить данные из отфильтрованного столбца Сумма, в столбец Скидка, без макросов и формул?
27.11.2014 18:54:53
СПАСИБО ОГРОМНОЕ!!!
За макрос отдельный РЕСПЕКТ!!!
21.01.2015 19:48:35
Николай Павлов, большое спасибо за макрос! Я, хоть и сталкивалась с excel, но с макросами дела не имела. А тут понадобилось в большущую таблицу выгрузки сайта, фильтруя ее значения,  вставлять не цифры и формулы, а текст, уникальные названия товаров. Копировать значения ячеек по одному совершенно нереально, долго и запутаешься. А с макросом (диапазон вставки и копирования были на разных листах одной книги) все получилось, пока с одним значением фильтра. Но встретились в конце такая закавыка.
Фильтр сняла, вернее, поставила галочки у всех значений, как у вас в видео. Здесь было все нормально, все вернулось к прежнему виду, правда, я не проверила всю  портянку. Только вот почему-то после нажатия кнопки сохранить мне выпало окошко — как будто ошибка. Там написано:

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

И вот я теперь не знаю, на какую кнопку нажимать, и что за возможности потеряются... Файл выгрузки большой — столбцов много, мне можно менять значения только одного столбца. Подскажите, что значит это сообщение?
21.01.2015 22:52:11
После пересылки по почте файла почему-то текст совершенно меняется, вместо русских букв появляются английские. Цифры причем не изменяются. А в исходном файле всё нормально Вначале показалось, что как будто бы русские слова набирались в английской раскладке. Но нет,  по количеству знаков слова одинаковые, но раскладка совершенно непонятная.  Например, слово "Контент" отображается как "Jnmremr".  Почему так получается, ничего не понимаю! Я меняла в формате ячеек только выравнивание по горизонтали по заполнению, а не по значению. Это и есть возможности несовместимые с форматом о которых предупредило окошко, выскочившее при сохранении?
29.06.2015 09:35:36
Господа, а как в Экселе 2010 скопировать отфильтрованные значения и вставить их, допустим, в соседний столбец? Просто тут какая-то засада и он вставляет только часть значений(первую группу строк)... Спасибо.
30.07.2015 12:25:03
Ни один из способов не работает. Вылазит ошибка: Невозможно создать или скопировать ссылку на данный диапазон данных, так как она слишком сложна. Что это значит? Как быть? Таблица очень большая. Если сидеть и тянуть за правый нижний угол ячейки потребуется целая вечность. А ни один другой способ не работает. Подскажите, что можно сделать.
09.08.2015 20:30:25
Здравствуйте! Спасибо Вам за Вашу очень полезную статью. :) Макрос я себе скопировала, и он успешно работает. Но также я хотела научиться альтернативному способу вставки данных в отфильтрованные ячейки, и вот тут у меня ничего не получилось. Помогите, пожалуйста!
У меня есть таблица с ФИО, где напротив выбранных нужно вставить сумму. Я произвожу следующие действия:
1. Копирую данные, которые необходимо вставить около фамилий.
2. Затем около отфильтрованного списка фамилий в нужном столбце выделяю нужное количество ячеек, нажимаю F5 -- выделить -- только видимые ячейки. Выделяются нужные ячейки и я правой клавишей мыши вставляю туда уже заранее скопированные данные.
Но вставить данные корректно, так, как мне надо, не получается. Допустим, таблица имеет следующий вид:
ФИО
Афиногенов И.С.20 000,00  
Артемьев О.А.
Сеченова Н.К.20 000,00  
Кирова К.У.
Булычев Р.Л.40 000,00  
Антонова Ж.К.20 000,00  
40 000,00  
50 000,00  

Я отфильтровываю список и оставляю видимыми только фамилии Афиногенов, Сеченова и Антонова, напротив которых нужно вставить суммы 20000, 40000, 50000. Но данные встают напротив этих фамилий: 20000, 20000, 20000. Если снять фильтр и раскрыть весь список, то  увидим то, что выше в таблице. Данные попадают даже туда, где списка нет. Что же делать? Уже давно пытаюсь решить эту проблему и ничего не получается... Подскажите мне, пожалуйста!!!  
10.11.2015 15:13:40
Да прием работает, но есть минус если стоят не значения а формулы
Добрый день, спасибо за макрос, как его можно модифицировать, чтобы вставлять данные в нескрытые ячейки, при этом транспонировав данные? Количество ячеек одинаковое.
25.11.2015 20:40:18
wassЮлия если до сих пор не нашли другие способы, можете пользоваться формулой =ЕСЛИ(RC[-1]="Афиногенов И.С.";"20000,00";ЕСЛИ(RC[-1]="Сеченова Н.К.";"40000,00";ЕСЛИ(RC[-1]="Антонова Ж.К.";"50000,00")))
18.12.2015 13:01:42
Добрый день, не подскажете с чем может быть связана след.проблема?

Вставляю из другой таблицы в свою отфильтрованную таблицу какие-нибудь значения. Убираю фильтр, позже в ходе работы натыкаюсь на эти значения в НЕНУЖНЫХ строках.

Как такое происходит? До сих пор не могу понять?

В отфильтрованной таблице можно протягивать?
Можно вставлять необходимую инфу выделяя в столбце сразу несколько строк-> ctrl v?
Есть ещё достаточно простой способ вставки в отфильтрованный диапазон. Отфильтровать данные. В колонке получателе =А1(ссылка на ячейку с переносимыми данными) протянуть формулу до конца. Расфильтроваться (обязательно), копировать всю колонку получатель - и не снимая выделения вставить данные как значения, и таким образом завершить перенос данных!
Способ нельзя применять, если в колонке получателе есть формулы которые необходимо сохранить.
17.07.2016 12:49:35
Здравствуйте! Спасибо за отличный макрос! А есть ли какой-то способ, чтоб скопировать данные из отфильтрованных ячеек и этот скопированный диапазон вставить тоже в отфильтрованные?
13.12.2016 20:57:39
Добрый день.
Скопировал макрос, но получаю ошибку - Диапазоны копирования и вставки разного размера
Что я делаю не так?
И как исправить?

Спасибо!
04.01.2017 16:42:29
Как я могу подсказать не видя что именно вы делаете?
Николай, здравствуйте.
Натолкнулся на аналогичную ошибку. Готов показать файл и прокомментировать действия. Как лучше это сделать?
16.03.2017 14:49:04
Можно прислать файлик мне на почту в исходном виде и подробно, по шагам описать что и как вы делаете. Постараюсь помочь :)
04.01.2017 21:18:10
Приветвую.
Набросал свой макрос ориентируясь на ваш. Проблем в тестовой работе нет, как всегда спасибо. Отлично копирует из видимых отфильтрованных в видимые отфильтрованные и данные и формулы. Но:
Один вопрос к вам как к профессионалу: как поведет себя макрос в боевом режиме при многопользовательском доступе файлу и одновременной работе нескольких пользователей, где каждый пользователь работает с данными, активно их фильтрует и пр. Не наломает ли он дров?
Спасибо.

Код:

Sub PasteValuesToVisible()
Dim originalRange As Range, targetRange As Range
Dim cell As Range, i As Long

title = "Paste values"

On Error GoTo errMyErrorHandler
Call GetRanges(title, originalRange, targetRange)
On Error GoTo 0

If targetRange.Cells.Count <> originalRange.Cells.Count Then
MsgBox "Äèàïàçîíû êîïèðîâàíèÿ è âñòàâêè ðàçíîãî ðàçìåðà!", vbCritical
Exit Sub
End If

For Each cell In targetRange
If cell.EntireRow.Hidden = False Then
cell.Value = Cells(cell.Row, originalRange.Column).Value
End If
Next cell

Exit Sub
errMyErrorHandler:
MsgBox Err.Description, vbExclamation + vbOKCancel, "Error: " & CStr(Err.Number)
Err.Clear
End Sub

Sub PasteFormulasToVisible()
Dim originalRange As Range, targetRange As Range
Dim cell As Range, i As Long

title = "Paste formulas"

On Error GoTo errMyErrorHandler
Call GetRanges(title, originalRange, targetRange)
On Error GoTo 0

If targetRange.Cells.Count <> originalRange.Cells.Count Then
MsgBox "Äèàïàçîíû êîïèðîâàíèÿ è âñòàâêè ðàçíîãî ðàçìåðà!", vbCritical
Exit Sub
End If

For Each cell In targetRange
If cell.EntireRow.Hidden = False Then
cell.Formula = Cells(cell.Row, originalRange.Column).Formula
End If
Next cell

Exit Sub
errMyErrorHandler:
MsgBox Err.Description, vbExclamation + vbOKCancel, "Error: " & CStr(Err.Number)
Err.Clear
'Resume Next
End Sub

Sub GetRanges(ByVal title As String, originalRange As Range, targetRange As Range)
Set originalRange = Application.InputBox("Source range", title, Type:=8, Default:=Selection.Address)
Set targetRange = Application.InputBox("Target range", title, Type:=8, Default:=originalRange.Address)
End Sub
16.03.2017 14:47:59
Русские буквы в макросе поломались. Когда копируете код, то включайте русскую раскладку - тогда этого эффекта не будет :)
А насчет многопользовательского режима могу сказать одно - никаких гарантий, надо пробовать на месте. Чем больше человеческого фактора, тем "веселее".
05.02.2018 16:14:02
Не могли бы вы написать макрос, который формулы копирует? Сижу пол дня на работе голову ломаю
21.02.2018 09:01:36
Олег, можно подправить 19-ю строку, где происходит копирование так, чтобы она копировала не значение а формулу - заменить Value на Formula.
21.02.2018 00:52:56
Супер решение! Спасибо Николай! У меня все получилось, вот только есть один важный нюанс для моей задачи. Сбрасывается форматирование вставляемых данных (некоторые слова выделены цветом). Можно ли дополнить макрос, чтобы сохранилось форматирование? Буду очень признательна.
21.02.2018 08:59:50
Мила, добавьте после 19-й строки еще одну:
cell.Interior.Color = copyrng.Cells(i).Interior.Color
05.03.2018 21:18:34
Спасибо еще раз Николай! Но я не совсем верно написала. Некоторые слова из вставляемых данных выделены не цветом, а шрифтом (а точнее красным шрифтом). Попробовала заменить вашу подсказку на cell.Font.Color = copyrng.Cells(i).Font.Color, но все равно данные вставляются не так как нужно, где-то форматирование (выделение красным шрифтом) сохраняется, где-то добавляется, где-то полностью сбрасывается. Пробовала на разных примерах. Никакой логики нет. Странно конечно, должно ведь работать. Возможно данную задачу нужно решать с помощью метода Selection.PasteSpecial Paste:=xlPasteFormats.
21.02.2018 15:43:43
огромное спасибо за макрос, это просто ценный клад)
16.04.2018 07:42:19
Добрый день! Спасибо за макрос, всё работает! Но есть одно но - когда человек нажимает Esc (либо нажимает "Отмена") - выскакивает ошибка макроса. В принципе всё бы ничего, человек может просто нажать End и окно ошибки закрывается. Но у меня Лист защищён, я в начале Вашего макроса добавил снятие защиты и в конце - опять Защита листа. Так вот, когда пользователь нажимает "Отмена" - после закрытия окна ошибки - лист остаётся Незащищённым ...

Подскажите как исправить, чтобы по нажатию Esc (либо "Отмена") - макрос завершался до конца (где у меня идёт исполнение процедуры защиты)
24.04.2018 14:18:20
Доброго времени суток!

Николай, а мне макрос не помог. Каждый раз выдаёт ошибку - диапазоны копирования и вставки разного размера!

Делаю следующим образом: отфильтровываю данные для копирования, жму макрос "PasteToVisible", выбираю диапазон для копирования видимых ячеек, выбирают диапазон для вставки видимых ячеек. По форматированию, количеству видимых ячеек данные идентичные. Ошибка.

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

А можно подкорректировать макрос, чтобы копировать диапазон видимых данных и вставлять в диапазон видимых ячеек?
20.09.2018 15:20:20
В этом комментарии решение проблемы www.planetaexcel.ru/techniques/2/173/#3629
Здраствуйте.
Очень часто приходится вставлять много фоток. Например 2 страница 1 фото, 3 страница 2 фото и.т.д., кто может с решением этой задачи?
Наверх