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

У нас есть следующий лист excel:

Отбираются ячейки от A2 до A10: опция 3 и опция 4 отфильтровываются. Теперь мы хотим вставить числа в A12: A16 рядом с видимыми ячейками (так в B2: B10). Как мы можем это сделать?

6 ответов

Пусть Y и Z представляют собой два столбца нуля (т. е. вне ваших данных).

1. Переместите ваши данные с A12-A16 на B12-B16.
2. В Y2 введите =ROW() . (В Excel 2010 =ROW может работать и даже может потребоваться.)
3. Перетащите вниз до Y10.
4. При выборе Y2-Y10 введите Alt + ; . (@Sean: Спасибо, что научи меня этому трюку!)
5. Копировать; затем нажмите A12 и вставьте.
6. Введите =IFERROR(VLOOKUP(Y2,A$12:B$16,2,FALSE),"") в Z2 и перетащите на Z10. (Теперь это должно выглядеть как данные в B12-B16.)
7. Отключить фильтрацию.
8. Скопируйте Z2-Z10 и вставьте значения в B2-B10.
9. (Удалить столбцы Y и Z.)

Я не могу найти способ вставки и избежать скрытых строк (я могу копировать только видимые элементы, используя ALT + ; перед копированием), не прибегая к VBA.

Этот макрос будет копировать A12: 16 в B2 и вниз, пропуская скрытые строки

Скопируйте макрокод из @SeanCheshire с небольшими изменениями, чтобы он предложил пользователю выбрать область копирования и область вставки.

Область копирования может выбирать диапазон, пример A1: A10, В области пасты просто нужно выбрать 1 ячейку, например B11, если пусть B12-B14 скрыт, то она будет вставляться в B11, B15, B16, . B23

‘Привет, у меня были проблемы с вышеизложенным (Excel 2007); он продолжал копировать последнее значение в «rangeFrom» в ячейки под «rangeTo». Непредсказуемо. Иногда это сработало, иногда это не так. В результате у меня есть электронная таблица с примерно 100 000 строк данных, которые теперь я считаю поврежденными . D’OH!

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

Я новичок в VBA (более удобный с Java и новичком в этом тоже), поэтому, пожалуйста, укажите ошибки моих путей, если это необходимо.

Приветствия за помощь!

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

1. Заполните отдельный временный столбец с помощью
2. Удалить фильтр
3. Убедитесь, что только ячейки в строках с необходимыми данными заполнены с помощью
4. Нумерует всю таблицу в другом столбце
5. Сортировка таблицы в порядке возрастания с использованием
6. Вставьте необходимые данные
7. Восстановить исходный порядок, используя нумерованный столбец
8. Удалить временные столбцы

Заметки. * Использование Office 2007. * По моему мнению, это единственный логический путь.

Если вы используете Office 2013 и выше, существует очень элегантное решение: Это Flash Fill (ctrl + E) — (Ribbon Home-Editing — Fill -Flash Fill) Но Flash Fill сложно, не так просто, как Ctrl + V, потому что вы должны дать хотя бы один пример в пункте назначения, чтобы он работал!

Итак, как это работает.

1. Шаг 1: Скопируйте первую ячейку источника и
2. вставьте значение в ячейку в отфильтрованном месте назначения. Это «пример».
3. Выберите все ячейки-источники и ctrl + C
4. установите курсор на ячейку, недавно обновленную по назначению, затем
5. «ctrl + e» запустится. (Только видимые / отфильтрованные ячейки будут заполнены из источника.)

Мы любим Flash Fill для данных, вставленных в отфильтрованное место назначения.

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

Способ 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.H > cell.Value = Cells(cell.Row, copyrng.Column).Value
End If
Next cell
End Sub

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

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

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

Здравствуйте! Спасибо Вам за Вашу очень полезную статью. Макрос я себе скопировала, и он успешно работает. Но также я хотела научиться альтернативному способу вставки данных в отфильтрованные ячейки, и вот тут у меня ничего не получилось. Помогите, пожалуйста!
У меня есть таблица с ФИО, где напротив выбранных нужно вставить сумму. Я произвожу следующие действия:
1. Копирую данные, которые необходимо вставить около фамилий.
2. Затем около отфильтрованного списка фамилий в нужном столбце выделяю нужное количество ячеек, нажимаю F5 — выделить — только видимые ячейки. Выделяются нужные ячейки и я правой клавишей мыши вставляю туда уже заранее скопированные данные.
Но вставить данные корректно, так, как мне надо, не получается. Допустим, таблица имеет следующий вид:

Я отфильтровываю список и оставляю видимыми только фамилии Афиногенов, Сеченова и Антонова, напротив которых нужно вставить суммы 20000, 40000, 50000. Но данные встают напротив этих фамилий: 20000, 20000, 20000. Если снять фильтр и раскрыть весь список, то увидим то, что выше в таблице. Данные попадают даже туда, где списка нет. Что же делать? Уже давно пытаюсь решить эту проблему и ничего не получается. Подскажите мне, пожалуйста.

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

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

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

В отфильтрованной таблице можно протягивать?
Можно вставлять необходимую инфу выделяя в столбце сразу несколько строк-> 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.H >= 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 517 скачиваний)

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

rCell.Copy rResCell.Offset(lr, lc)

rResCell.Offset(lr, lc) = rCell.Value

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

Tips_Macro_CopyPasteInHiddenCells.xls (54,5 KiB, 7 824 скачиваний)

Так же см.:
[[Excel удаляет вместо отфильтрованных строк — все?! Как избежать]]

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Дмитрий, здравствуйте!
У меня почему-то не работают ваши файлы.
Приведу пример на последнем файле:
Открываю его в Excel 2013; разрешаю редактирование-вылезает ошибка Run-time error ‘1004’:
Method ‘OnKey’ of object’_Application’ failed
Когда нажимаю debug вылезает окошко макроса и там подсвечена желтым часть строки:
Application.OnKey "^q", "My_Copy"
А если пробую нажимать в таблице ctrl+q, то вылезает окошко форматирования
Подскажите, пожалуйста, как это исправить.

Дмитрий, когда скачиваете файл с интернета — он открывается в защищенном режиме, о чем у Вас появляется предупреждение. Нельзя обратиться к свойствам листов и книг, если книга открыта в защищенном режиме. А именно так по умолчанию открываются книги, скачанные откуда-то. Варианта по сути два:
1. При открытии файла разрешить редактирование и перезапустить книгу.
2. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.

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