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

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

Как быстро пользоваться фильтром в Excel

На рисунке показан наш журнал истории взаимных расчетов с клиентами.

История продаж и выставления счетов в разрезе клиентов:

Нам нужно показать различные варианты данных, разделенные на группы: названия компаний, города и суммы. Мы также хотим добавить итог к последней группе.

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

Предположим, что первая группа данных должна включать данные, относящиеся к сделкам на сумму более 20 000 рублей, осуществленным ADEX Comp. Для этого:

1. Выделите заголовок таблицы «Сумма» D1 и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр». Для исходной таблицы включиться режим автофильтра и в ее заголовках появятся кнопки выпадающего меню.
2. Откройте выпадающее меню ячейки D1 и выберите опцию «Числовые фильтры»-«Настраиваемый фильтр». В результате появиться диалоговое окно «Пользовательский автофильтр».
3. В первом выпадающем списке данного окна укажите на значение «больше», а в поле ввода напротив введите значение 20000. И нажмите ОК.
4. Теперь раскройте выпадающее меню в ячейке A1 («Название фирмы») и выберите только фирму ADEX Comp.

Во-вторых, были применены условия фильтрации данных.

Перевод формул в их текст (без сохранения результирующих значений)

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

Нажмите Ctrl+H, чтобы открыть диалоговое окно «Найти и заменить», а затем введите значения в соответствующие поля.

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

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

В Excel, когда вы копируете и вставляете данные в диапазон, который был отфильтрован или содержит скрытые строки/столбцы, они также будут вставлены в отфильтрованные или скрытые ячейки. Можно ли вставить данные только в видимые ячейки Excel? Kutools for Excel’s Paste to Visible поможет вам сделать это быстро.

Вставьте в видимую область демонстрации

• Перерабатывайте все: Добавляйте наиболее часто используемые или сложные формулы, графики и прочее в избранное для быстрого использования в будущем.
• Более 20 текстовых функций: извлечение числа из текстовой строки; извлечение или удаление частей текста; преобразование чисел и валют в английские слова.
• Инструменты слияния: объединение нескольких книг и рабочих листов в один; объединение нескольких ячеек/строк/столбцов без потери данных; объединение повторяющихся строк и итогов.
• Инструменты разделения: разделение данных на несколько рабочих листов на основе значений; из одной книги в несколько файлов Excel, PDF или CSV; из одного столбца в несколько столбцов. Вставка Spring Скрытие скрытых/фильтруемых строк ; Подсчет и суммирование по цвету фона ; Массовая отправка персонализированных писем нескольким получателям.
• Super Filter:
• Более 300 расширенных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Легко развертывается на вашем предприятии или в организации.

Нажмите Кутулс > Диапазон > Вставить в видимый > Все / Только вставить значения. Смотрите скриншот:

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

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

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

Вставляет все значения с форматированием ячейки в видимые ячейки:

2. Если вы хотите вставить все значения с форматированием ячеек в видимые ячейки, нажмите Coutools > Range > Paste to visible > All. См. скриншот:

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

Чтобы вставить в видимые ячейки только значения выделенных ячеек, нажмите Ктул > Диапазон > Вставить в видимые > Вставить только значения. См. скриншот :

3. Если появится окно подсказки «Вставить в видимый диапазон», выберите ячейку или диапазон, в который нужно вставить выбранные данные со всем форматированием ячеек, а затем нажмите значок кнопки OK. См. снимок экрана:

Это приведет к тому, что выбранные данные будут вставлены только в отфильтрованные строки. См. скриншоты:

Вставьте все, включая форматирование ячеек, в видимые ячейки:

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

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

Чтобы вставить данные только в видимые строки листа, вы можете использовать Paste to Visible Range, если на вашем листе есть скрытые строки.

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

Значения вставляются в строки с форматированием ячейки:

2. Нажмите Cthulz > Range > Paste to visible > All, чтобы вставить все значения, отформатированные с помощью форматирования ячеек в видимых строках. См. скриншот:

Вставка значений только в те строки, которые видны:

Вы можете вставить только выбранные значения в видимые строки, нажав Cthulz > Range > Paste to visible > Only paste values. См. снимок экрана:

3. Вставить в диапазон, видимый в окне запроса, выберите ячейку или диапазон, в который нужно вставить выбранные данные, затем нажмите значок кнопки OK. Просмотр экрана

После этого вы увидите, что выбранные данные были вставлены в видимые строки, как показано на снимках ниже:

Вставьте все с форматированием ячеек в видимых строках:

Значения следует вставлять только в видимые строки:

Ноты:

Эта утилита предлагает функции отмены (Ctrl + Z).

(2) Если ваша электронная таблица содержит отфильтрованные и скрытые строки, эта функция также поможет вам эффективно вставить выбранные данные только в видимые ячейки.

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

Kutools для Excel: более 300 полезных функций, которые вы можете попробовать бесплатно в течение 30 дней без ограничений. Скачайте и попробуйте бесплатно прямо сейчас!

Kutools for Excel

Описанная выше функциональность — лишь одна из 300 мощных возможностей Kutools for Excel.

Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.

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

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

Способ 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.
Чтобы узнать, какие возможности могут быть потеряны, нажмите кнопку Справка.

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