Вставить значения в отфильтрованные ячейки. Копирование в Excel отфильтрованных данных. Копирование только видимых ячеек

Вставить в видимые строки в Excel. Вставить только в видимые строки в Excel числа, формулы, текст можно несколькими способами. Когда нужно вставить…

Содержание

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

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

Вставить значения в видимые ячейки с помощью VBA

Вставьте значения в видимые ячейки с помощью Вставить в видимый диапазон Kutools for Excel хорошая идея3

Вставить значения в видимые ячейки с помощью VBA

В Excel нет других способов решить эту проблему, кроме VBA.

1. Нажмите Alt + F11 одновременно, а Microsoft Visual Basic для приложений окно выскакивает.

2. Нажмите Вставить > Модуль, затем вставьте ниже код VBA в всплывающее окно модуля.

VBA: вставлять значения только в видимые ячейки.

Sub CopyFilteredCells() ‘Updateby20150203 Dim rng1 As Range Dim rng2 As Range Dim InputRng As Range Dim OutRng As Range xTitleId = “KutoolsforExcel” Set InputRng = Application.Selection Set InputRng = Application.InputBox(“Copy Range :”, xTitleId, InputRng.Address, Type: = 8) Set OutRng = Application.InputBox(“Paste Range:”, xTitleId, Type: = 8) For Each rng1 In InputRng rng1.Copy For Each rng2 In OutRng If rng2.EntireRow.RowHeight > 0 Then rng2.PasteSpecial Set OutRng = rng2.Offset(1).Resize(OutRng.Rows.Count) Exit For End If Next Next Application.CutCopyMode = FalseEnd Sub

3. Нажмите F5 ключа или Запустите , затем появится диалоговое окно, в котором вы можете выбрать значения для копирования. Смотрите скриншот:
док-паста-видимая-ячейка-1

4. После выбора значений щелкните OK, затем появится другое диалоговое окно для выбора диапазона для вставки выбранных данных. Смотрите скриншот:
док-паста-видимая-ячейка-2

5. Нажмите OK. Вы можете видеть только видимые ячейки, в которые вставлены выбранные значения.

Вставьте значения в видимые ячейки с помощью Вставить в видимый диапазон Kutools for Excel

Собственно, если вы используете Вставить в видимый диапазон полезности Kutools for Excel – удобный инструмент Excel, вы можете быстро и легко решить проблему вставки значений в видимый диапазон.
док вставить видимую ячейку 6

После установки Kutools for Excel, сделайте следующее:(Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите диапазон значений, которые вы хотите скопировать и вставить, затем щелкните Kutools > Диапазон > Вставить в видимый диапазон > Все or Только вставить значения. Смотрите скриншот:
док вставить видимую ячейку 7

2. Затем Вставить в видимый диапазон появится диалоговое окно и выберите диапазон, в который вы хотите вставить значения. Смотрите скриншот:
док вставить видимую ячейку 8

3. Нажмите OK. Теперь выбранные значения вставляются только в видимый диапазон.


Примечание:
Эта утилита работает как со скрытыми, так и с отфильтрованными строками или столбцами.

Щелкните здесь, чтобы получить более подробную информацию об утилите «Вставить в видимый диапазон».

  Вставить только в видимые ячейки

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

офисный дно

Вставить в видимые строки в Excel.

​Смотрите также​: Собственно тема: скрываю​ ​ происходит.​​ по умолчанию, т.е.​RMMMM​ у меня таких​ в чем причина.​ становлюсь в первую​ pasterng = Application.InputBox(“Диапазон​ все отфильтрованные строки​ протягивании все ок)​ – далее добавить​ “Запрос”, Type:=8) Set​ же значения во​Главная​ формулу, цифру, т.д.​ написали число).​Вставить только в видимые​ строки (или пользуюсь​Demetry​
​ с копированием выделенных​​: Собственно суть вопроса​​ таблиц для обработки​
​оставил в покое​ ячейку столбца, в​ вставки”, “Запрос”, Type:=8)​
​ списка, то все​Pelena​ столбец, НО тогда​ pasterng = Application.InputBox(“Диапазон​ все отфильтрованные строки​ >​ Нажимаем сочетание клавиш​Теперь, или нажимаем​ строки в​ фильтром, что чаще).​: Надо выделить нужные​ ячеек проблем нет,​ в названии. Подробнее​ под сотню и​ макрос, начал делать​ который необходимо вставить​ ‘проверяем, чтобы они​ просто. Предположим, что​
​: А у Вас​
​ нарушается последовательность списка​ вставки”, “Запрос”, Type:=8)​
Вставить в видимые строки Excel.​ списка, то все​Найти и выделить​ «Ctrl» + «Enter».​
​ сочетание клавиш «Ctrl»​
​Excel​ Есть диапазон ячеек​ ячейки, потом Ctrl-G​ скопированное прекрасно вставляется​ на всякий случай:​ в каждой строк​ способом, который описал​ данные, то вставляются​ были одинакового размера​ у нас есть​ не получилось?​ ЗАГОЛОВКИ ПОДПУНКТЫ с​ ‘проверяем, чтобы они​ просто. Предположим, что​, а затем выберите​В Excel ячейки​
​ + G, или​числа, формулы, текст​ я хочу чтобы​ – Выделить… -Только​ через Ctrl-V, но​ Есть таблица, применяю​ под пятьдесят тысяч)​ в начале темы,​ скопированные данные во​ If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <>​ вот такой список​​Выкладывайте файл​ текстом описанием и​

​ были одинакового размера​ у нас есть​ пункт​ можно выделить цветом​ клавишу F5. Выйдет​ можно несколькими способами.​ копия вставилась в​ видимые ячейки. Потом​ затирает ячейки внизу​ к ней фильтр,​Юрий М​

​ тоже выдает ошибку.​ все ячейки, включая​

​ copyrng.Cells.Count Then MsgBox​ сделок:​Гость​ т.п. НУЖНО СОХРАНИТЬ​ If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <>​ вот такой список​Выделение группы ячеек​ по поределенному условию,​ диалоговое окно «Переход».​ Когда нужно вставить​ видимые ячейки -​ обычным копипастом.​ (как я описал​ выделяю первую отфильтрованную​: EgorAg, свои сообщения​ на разных компьютерах​ скрытые, если же​ “Диапазоны копирования и​… и в нем нужно поставить​: вот сам файлик​ ЦЕЛОСТНОСТЬ!!!!) ХЕЛП!!!!!!​

​ copyrng.Cells.Count Then MsgBox​

​ сделок:​.​ которое мы зададим​ Нажимаем кнопку «Выделить…».​ числа, формулы, текст​ но вставка происходит​Алексей пинчук​ в первом посте).​ строку, удерживая Shift​ можно редактировать. В​ тоже пробовал уже)​ выделяю весь диапазон​ вставки разного размера!”,vbCritical​ фиксированную скидку в​http://rghost.ru/38132320​Serge_007​ “Диапазоны копирования и​… и в нем нужно поставить​Выберите параметр​ условным форматированием и​

Как вставить только в видимые стоки Excel.

​ И, в новом​ не во все​ во все строки​: > На самом​ Не работает именно​ выделяю последнюю отфильтрованную​ том числе, менять​так что, если​ ячеек, в которые​ Exit Sub End​

excel-office.ru>

Как вставить скопированное в отфильтрованную таблицу? Без макроса.

Уверена, Вы сталкивались с этой проблемой. Если скопировать данные и обычным

CTRL+V

вставить их в отфильтрованную таблицу, то

они попадут и в скрытые строки тоже

. Это совсем не то, что нам нужно – см. скрин ниже. Узнаёте?

Можно решить эту задачу, подтянув данные формулами, без макроса. Ниже показываю 3 варианта.

Функции СМЕЩ + СЧЁТЕСЛИ

Если таблица отфильтрована по понятному однозначному условию

(конкретный текст или текст содержит, известное число или число < либо > заданного и т.д.). На скрине ниже я выбрала «Самовывоз из питомника».

И любые из этих условий можно использовать для функций СЧЁТЕСЛИ/МН, СУММЕСЛИ/МН и СРЗНАЧЕСЛИ/МН (на моём онлайн курсе «

Расширенные возможности

» показываю все варианты условий на примере выборочного суммирования).

В этом случае нашим помощником будет функция СЧЁТЕСЛИ (или СЧЁТЕСЛИМН, если зафильтровано несколько столбцов), которая способна посчитать в каждой строке её уникальный порядковый номер от начала таблицы.

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

Осталось позвать на помощь функцию ссылок и массивов СМЕЩ и вот результат:

D9=СМЕЩ($F$96;СЧЁТЕСЛИ($C$3:C9;”самовывоз из питомника”)-1;0)

Формулу ячейки

D9

я просто скопировала вниз и все данные корректно разместились на своих местах. Если сбросить фильтр, то всё будет ОК. Диапазон

D:D

можно скопировать и вставить значениями на своё же место, избавившись от формулы.

Функции СМЕЩ + ПРОМЕЖУТОЧНЫЕ.ИТОГИ

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

Тогда берём в работу другую функцию, способную посчитать количество неважно каких записей от начала таблицы и таким образом снова получим тот самый номер:

E4=СМЕЩ($G$81;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$D$3:D4)-1;0)

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

E4

, а диапазон ячеек

D4:E4

. И снова всё получилось.

Функция ВПР

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

. То не забывайте про функцию ВПР – это ведь её работа )) На скрине ниже фильтром я выбрала только нужные номера заказов. Конечно, в диапазоне для подстановки эти же названия тоже должны быть:

D4=ВПР(A4;$F$90:$G$96;2;0)

Функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и ВПР мы изучаем на моём онлайн курсе «

Расширенные возможности

».

Создавать свои ссылки с помощью СМЕЩ, ДВССЫЛ и других полезных функций можно научиться на курсе «

Формулы массива

». И Excel начнёт работать по нашим правилам, а не мы по его ограничениям.

Первый урок курса можно посмотреть бесплатно, кнопка ниже.

Файл с готовыми формулами

ВПР, ЕСЛИ, СУММЕСЛИ и др.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Вставляем только значения

Возьмём для примера таблицу учёта прибыли от продаж печенья на благотворительной распродаже выпечки. Вы хотите вычислить, какая прибыль была получена за 15 недель. Как видите, мы использовали формулу, которая складывает сумму продаж, которая была неделю назад, и прибыль, полученную на этой неделе. Видите в строке формул =D2+C3? Ячейка D3 показывает результат этой формулы – $100. Другими словами, в ячейке D3 отображено значение. А сейчас будет самое интересное! В Excel при помощи инструмента Paste Special (Специальная вставка) Вы можете скопировать и вставить значение этой ячейки без формулы и форматирования. Эта возможность иногда жизненно необходима, далее я покажу почему.

Предположим, после того, как в течение 15 недель Вы продавали печенье, необходимо представить общий отчёт по итогам полученной прибыли. Возможно, Вы захотите просто скопировать и вставить строку, в которой содержится общий итог. Но что получится, если так сделать?

Специальная вставка в Excel

Упс! Это совсем не то, что Вы ожидали? Как видите, привычное действие скопировать и вставить в результате скопировало только формулу из ячейки? Вам нужно скопировать и выполнить специальную вставку самого значения. Так мы и поступим! Используем команду Paste Special (Специальная вставка) с параметром Values (Значения), чтобы все было сделано как надо.

Специальная вставка в Excel

Заметьте разницу на изображении ниже.

Специальная вставка в Excel

Применяя Специальная вставка > Значения, мы вставляем сами значения, а не формулы. Отличная работа!

Возможно, Вы заметили ещё кое-что. Когда мы использовали команду Paste Special (Специальная вставка) > Values (Значения), мы потеряли форматирование. Видите, что жирный шрифт и числовой формат (знаки доллара) не были скопированы? Вы можете использовать эту команду, чтобы быстро удалять форматирование. Гиперссылки, шрифты, числовой формат могут быть быстро и легко очищены, а у Вас останутся только значения без каких-либо декоративных штучек, которые могут помешать в будущем. Здорово, правда?

На самом деле, Специальная вставка > Значения – это один из моих самых любимых инструментов в Excel. Он жизненно необходим! Часто меня просят создать таблицу и представить её на работе или в общественных организациях. Я всегда переживаю, что другие пользователи могут привести в хаос введённые мной формулы. После того, как я завершаю работу с формулами и калькуляциями, я копирую все свои данные и использую Paste Special (Специальная вставка) > Values (Значения) поверх них. Таким образом, когда другие пользователи открывают мою таблицу, формулы уже нельзя изменить. Это выглядит вот так:

Специальная вставка в Excel

Обратите внимание на содержимое строки формул для ячейки D3. В ней больше нет формулы =D2+C3, вместо этого там записано значение 100.

И ещё одна очень полезная вещь касаемо специальной вставки. Предположим, в таблице учёта прибыли от благотворительной распродажи печенья я хочу оставить только нижнюю строку, т.е. удалить все строки, кроме недели 15. Смотрите, что получится, если я просто удалю все эти строки:

Специальная вставка в Excel

Появляется эта надоедливая ошибка #REF! (#ССЫЛ!). Она появилась потому, что значение в этой ячейке рассчитывается по формуле, которая ссылается на ячейки, находящиеся выше. После того, как мы удалили эти ячейки, формуле стало не на что ссылаться, и она сообщила об ошибке. Используйте вместо этого команды Copy (Копировать) и Paste Special (Специальная вставка) > Values (Значения) поверх исходных данных (так мы уже делали выше), а затем удалите лишние строки. Отличная работа:

Специальная вставка в Excel

Специальная вставка > Значения: основные моменты

  1. Выделите данные
  2. Скопируйте их. Если данные не скопировать, а вырезать, то команда Paste Special (Специальная вставка) будет недоступна, поэтому обязательно используйте копирование.
  3. Выделите ячейку, в которую нужно вставить скопированные данные.
  4. Нажмите Paste Special (Специальная вставка). Это можно сделать:
    • кликнув правой кнопкой мыши и выбрав в контекстном меню Paste Special (Специальная вставка).
    • на вкладке Home (Главная), нажмите маленький треугольник под кнопкой Paste (Вставить) и в выпадающем меню выберите Paste Special (Специальная вставка).
  5. Отметьте опцию Values (Данные).
  6. Нажмите ОК.

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010. 

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

  1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
  2. В группе Сервис [Tools] (или на вкладке Вставка [Insert] в группе Фильтры [Filters]) выбрать кнопку Вставить срез [Insert Slicer] .

Вставка среза в Excel

  1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
    Вставка срезов

Форматирование срезов

  1. Выделить срез.
  2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

Форматирование срезов

  1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Зачем нужны фильтры в таблицах Эксель

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

Скрытые при помощи фильтра строки таблицы не исчезают. Можно условно представить, что их высота становится равной нулю (про изменение высоты строк и ширины столбцов я ранее рассказывал). Таким образом, остальные строки, не скрытые фильтром, как бы “склеиваются”. То, что получается в результате, и есть таблица с наложенным фильтром.

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

Как сделать (наложить) фильтр на таблицу Excel

Это очень просто! Допустим, у нас есть таблица с заголовками. Поскольку фильтр на таблицу Эксель накладывается на определённую часть таблицы, то эту самую часть нужно сначала выделить. Сам Excel ни за что не догадается, что вы хотите, поэтому выделяем в строке с заголовками таблицы нужные ячейки.

Обратите внимание:

Если выделить ВСЮ строку, то Эксель автоматически установит фильтр на ВСЕ столбцы, в которых есть хоть что-то. Если в столбце ничего нет, то фильтр не накладывается.

После того как выделили нужные столбцы, нужно собственно установить фильтр. Самый простой способ — это зайти на вкладке ленты “Главное” / “Фильтр и сортировка” и нажать фильтр. Привожу скриншот из Office 2010, в прочих версиях Excel фильтр на таблицу накладывается аналогично.

После нажатия этой кнопки в выделенные ячейки добавится фильтр.

Если не выделять несколько ячеек, а просто щёлкнуть на одну, то фильтр добавляется на ВСЮ строку, как если бы вы её выделили.

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

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

Как задать несколько параметров

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

Например, мы уже отфильтровали таблицу по условию «Страна» и оставили только товары российского происхождения.

14.png

Теперь необходимо дополнительно отделить товары стоимостью 100 рублей, для этого применяем отсев в разделе «Цена».

15.png

Нажимаем «Ок» и получаем только товары стоимостью 100 рублей, произведенные в России.

16.png

Задача 7.1. (Совпадают ли 2 значения в одной строке?)

Есть таблица, в которой указаны Год выпуска и Год покупки автомобиля.

Требуется вывести только те строки, в которых Год выпуска совпадает с Годом покупки. Это можно сделать с помощью элементарной формулы =В10=С10 .

Поясняющий текст в ячейке С6 НЕ должен совпадать ни с одним заголовком столбца таблицы! В противном случае фильтр будет работать неправильно.

Текстовый фильтр в Эксель

Рассмотрим, как отфильтровать в Excel ячейки с определенным текстом. Простейший способ – это, по аналогии с прошлым примером, ввести нужный текст (или его часть) в поиске.

Однако, можно и более гибко настраивать отбор. Если в окне фильтра нажать «Текстовые фильтры», то в контекстном меню появится выбор способа сопоставления: равно, не равно, начинается с, заканчивается на, содержит, не содержит.

tekstovie-filtri-300x168.jpg

Например, нужно выбрать людей, которых не зовут Богдан. Выберем вариант «не содержит» и запишем ему критерий « Богдан ». Поставим пробелы перед именем и после. Иначе, например, Богданов Егор Егорович тоже попадет под фильтр, хотя его не зовут Богданом:

filtr-ne-soderzhit-300x168.jpg

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...