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

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

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

Умеет работать со скрытыми строками и столбцами.

Если заранее выделить (удерживая Ctrl) диапазоны копирования и вставки, то после открытия этого окна адреса этих диапазонов автоматически попадут в соответствующие поля — останется только нажать на ОК.

Excel, хоть и плохо, но умеет копировать только видимые ячейки (нужно предварительно жать F5 — Выделить — Только видимые), а вот вставлять только в видимые он не умеет ни в одной версии.

Может ли это повредить мои данные?

Только если вставите не туда.

Как скопировать и вставить лишь видимые ячейки в Excel ⁠ ⁠

Вы когда-нибудь пытались скопировать и вставить диапазон ячеек со скрытыми строками и/или столбцами? Если да, то Вам наверняка знакома особенность Excel, заключающаяся в том, что в результате подобного действия вставляются все ячейки, а не лишь видимые.

Рассмотрим, как данную особенность можно обойти и добиться вставки лишь реально видимых ячеек. Для этого:

Шаг первый: Выбираем нужный к копированию диапазон.

Шаг второй: Открываем с помощью комбинации клавиш CTRL+G окно «Переход» и в нём нажимаем на кнопку «Выделить»:

В следующем окне выбираем опцию «только видимые ячейки» и подтверждаем выбор.

В итоге вот так выбираются все видимые ячейки (левая часть следующей картинки). Копируем данное выделение с помощью CTRL+C (результат копирования отображен справа на следующей картинке):

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

Вот такой подход. О нём я наглядно рассказал в следующем видео, советую его посмотреть:

719 постов 15K подписчика

Подписаться Добавить пост

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

2 года назад

Вот это реальный лайфхак! Сколько нервов пожжено было!

раскрыть ветку
2 года назад

Ого как мудрёно.
быстрые клавиши alt+; (выделить видимые ячейки) и вставляйте куда хотите))))
есть только одна проблема «;» в русской раскладке только с шифтом нажимается.
но не все потеряно если не хотите менять раскладку всякий раз то можно просто вывести кнопку на панель быстрого доступа очень удобно

раскрыть ветку
2 года назад

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

раскрыть ветку
2 года назад
Подскажите, как копировать, что бы вставлялся и размер ячеек?
раскрыть ветку
7 месяцев назад

В русской раскладке нужно нажимать одновременно: alt shift и 4

кстати, кто может подсказать — как кнопки в экселе выводить?

Похожие посты
1 год назад

LifeHacks для военных⁠ ⁠

1 год назад

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

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

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

1. Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).
2. После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

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

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

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

× The language used in menus and dialogs (Язык, используемый в меню и диалоговых окнах) in ASAP Utilities can be set to «Русский (Russian)».

Диапазон › 12. Копировать выделенный фрагмент и вставлять только в видимые ячейки.

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

Снимки экрана

Запуск этой утилиты

• Нажмите кнопку ASAP Utilities › Диапазон › 12. Копировать выделенный фрагмент и вставлять только в видимые ячейки.
• Укажите сочетание клавиш: ASAP Utilities › Избранное и сочетания клавиш › Редактировать избранное и сочетания клавиш.

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

​Смотрите также​​ и скрытые (отфильтрованные).​Гость​ текстом описанием и​ заново — при​ рядом с выделенными​ cell.EntireRow.Hidden = False​ для ввода значений​Специальная вставка​Вставка содержимого скопированной области​формулы​ содержатся пустые ячейки.​Все содержимое и форматирование​ исходные ячейки (изменения,​ ячейках:​ внизу страницы. Для​, а затем выберите​Примечание:​Как быть?​: новые данные вставляются​ т.п. НУЖНО СОХРАНИТЬ​ фильтре по столбцу​

​ в соседнем столбце​ Then cell.Value =​ и для ввода​.​ без математического действия.​Клавиша T​Транспонировать​ ячеек с использованием​ внесенные в исходных​Пункт меню​ удобства также приводим​ пункт​Мы стараемся как​Владимир​ не с помощью​ ЦЕЛОСТНОСТЬ. ) ХЕЛП. ​ B, все видимые​ ставить маркер, потом​ copyrng.Cells(i).Value i =​ формул. Например, если​

​Перемещение и копирование листа​

1. ​сложить​значения​Вставка содержимого скопированных ячеек​ темы, примененной к​ ячейках, отражаются и​ ​Что вставляется​​ ссылку на оригинал​Выделение группы ячеек​ можно оперативнее обеспечивать​
2. ​: Вы скопируйте отфильтрованные​​ заполнения, а копируются​​Serge_007​​ ячейки из столбца​​ сортировать по нему​ i + 1​​ скидка для «Ашанов»​​Перемещение и копирование ячеек,​
3. ​Добавление значений из области​​Вставка только значений в​​ с изменением ориентации.​​ исходным данным.​​ в ячейках, куда​
4. ​Вставить​​ (на английском языке).​​.​ вас актуальными справочными​
5. ​ строки и вставьте​ с нового листа. (при​: В Excel 2007/2010​​ А надо скопировать​​ и копировать уже​ End If Next​ ​ не фиксированная, а​​ строк и столбцов​ копирования к значениям​ том виде, как​ Данные строк будут​без рамки​ вставлено изображение).​Все содержимое и формат​Можно копировать и вставлять​Выберите параметр​ ​ материалами на вашем​​ их в другой​ протягивании все ок)​ по умолчанию данные​ в столбец E.​ нужные ячейки.​ cell End Sub​ составляет 10% от​Весьма распространенная ситуация, вопрос​ в области вставки.​

Копирование и вставка определенного содержимого ячейки

​ они отображаются в​​ вставлены в столбцы,​Содержимое и формат ячеек,​Ширины столбцов​ ячеек, включая связанные​ определенного содержимого ячеек​только видимые ячейки​ языке. Эта страница​ лист. Думаю, проблема​Pelena​ вставляются только в​И т.д.​Guest​Как легко сообразить, макрос​ суммы сделки, то​ про которую мне​вычесть​ ячейках.​ и наоборот.​ кроме границ ячеек.​Вставка ширины столбца или​

​ данные.​ и атрибуты (например,​и нажмите кнопку​ переведена автоматически, поэтому​ исчезнет.​: А у Вас​​ отфильтрованные строки, что​ ​Ship​ ​: Нужно так:​​ запрашивает у пользователя​​ в первую отфильтрованную строку​​ задают почти на​Вычитание значений из области​форматы​Вставить связь​Ширины столбцов​ диапазона столбцов в​​формулы​​ формулы, форматы, примечания​​ОК​​ ее текст может​Dyka​ не получилось?​​ бы вставить во​​: Так и делайте.​При скрытии строк,​ по очереди два​

​ можно ввести не​ каждом тренинге. Есть​

Пункты меню «Вставить»

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

​ грамматические ошибки. Для​

​ пойдет. Задача вставить​

​Гость​ спецвставкой и поставить​ по одному значению,​