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

Содержание

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

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

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

6 ответов

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

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

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

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

  • Заполните отдельный временный столбец с помощью
  • Удалить фильтр
  • Убедитесь, что только ячейки в строках с необходимыми данными заполнены с помощью
  • Нумерует всю таблицу в другом столбце
  • Сортировка таблицы в порядке возрастания с использованием
  • Вставьте необходимые данные
  • Восстановить исходный порядок, используя нумерованный столбец
  • Удалить временные столбцы
  • Заметки. * Использование Office 2007. * По моему мнению, это единственный логический путь.

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

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

  • Шаг 1: Скопируйте первую ячейку источника и
  • вставьте значение в ячейку в отфильтрованном месте назначения. Это «пример».
  • Выберите все ячейки-источники и ctrl + C
  • установите курсор на ячейку, недавно обновленную по назначению, затем
  • «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 — выделить — только видимые ячейки. Выделяются нужные ячейки и я правой клавишей мыши вставляю туда уже заранее скопированные данные.
    Но вставить данные корректно, так, как мне надо, не получается. Допустим, таблица имеет следующий вид:

    ФИО Афиногенов И.С. 20 000,00 Артемьев О.А. Сеченова Н.К. 20 000,00 Кирова К.У. Булычев Р.Л. 40 000,00 Антонова Ж.К. 20 000,00 40 000,00 50 000,00

    Я отфильтровываю список и оставляю видимыми только фамилии Афиногенов, Сеченова и Антонова, напротив которых нужно вставить суммы 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. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
    Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.

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

    Содержание

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

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

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

    6 ответов

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

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

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

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

  • Заполните отдельный временный столбец с помощью
  • Удалить фильтр
  • Убедитесь, что только ячейки в строках с необходимыми данными заполнены с помощью
  • Нумерует всю таблицу в другом столбце
  • Сортировка таблицы в порядке возрастания с использованием
  • Вставьте необходимые данные
  • Восстановить исходный порядок, используя нумерованный столбец
  • Удалить временные столбцы
  • Заметки. * Использование Office 2007. * По моему мнению, это единственный логический путь.

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

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

  • Шаг 1: Скопируйте первую ячейку источника и
  • вставьте значение в ячейку в отфильтрованном месте назначения. Это «пример».
  • Выберите все ячейки-источники и ctrl + C
  • установите курсор на ячейку, недавно обновленную по назначению, затем
  • «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 — выделить — только видимые ячейки. Выделяются нужные ячейки и я правой клавишей мыши вставляю туда уже заранее скопированные данные.
    Но вставить данные корректно, так, как мне надо, не получается. Допустим, таблица имеет следующий вид:

    ФИО Афиногенов И.С. 20 000,00 Артемьев О.А. Сеченова Н.К. 20 000,00 Кирова К.У. Булычев Р.Л. 40 000,00 Антонова Ж.К. 20 000,00 40 000,00 50 000,00

    Я отфильтровываю список и оставляю видимыми только фамилии Афиногенов, Сеченова и Антонова, напротив которых нужно вставить суммы 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. Добавить папку, в которую скачиваются вложения, в список доверенных(Файл -Параметры -Центр управления безопасностью -Параметры центра управления безопасностью -Надежные расположения).
    Есть еще вариант: в настройках макросов разрешить запуск макросов с уведомлением. Тогда сначала будет появляться запрос на редактирование, а самым последним запрос на разрешение выполнения содержимого.

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

    Источник: hololenses.ru

    Понравилась статья? Поделиться с друзьями:
    Ваш домашний советник
    Добавить комментарий