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

Обновлено: 21.11.2024

Сэнди Риттенхаус

Сэнди Риттенхаус
Писатель

С ее B.S. В области информационных технологий Сэнди много лет проработала в ИТ-индустрии в качестве руководителя проекта, руководителя отдела и руководителя отдела управления проектами. Она узнала, как технологии могут обогатить как профессиональную, так и личную жизнь, используя правильные инструменты. И со временем она поделилась этими предложениями и практическими рекомендациями на многих веб-сайтах. Имея за плечами тысячи статей, Сэнди стремится помочь другим использовать технологии в своих интересах. Подробнее.

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

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

Копирование и вставка по умолчанию со скрытыми ячейками в Excel

По умолчанию при копировании диапазона ячеек в Excel, содержащего скрытые ячейки, эти скрытые ячейки отображаются при вставке.

Например, мы скрыли строки с 3 по 12 (с февраля по ноябрь) на следующем снимке экрана.

Когда мы выбираем видимый диапазон ячеек, используем действие «Копировать», а затем «Вставить», появляются эти скрытые ячейки.

Если это не то, что вам нужно, узнайте, как этого избежать.

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

Эта изящная скрытая функция доступна в Microsoft Excel как для Windows, так и для Mac. И, к счастью, это работает точно так же.

Начните с выбора ячеек, которые вы хотите скопировать и вставить. Затем перейдите на вкладку «Главная» и щелкните стрелку раскрывающегося списка «Найти и выбрать» (увеличительное стекло). Выберите «Перейти к специальному».

В появившемся окне выберите «Только видимые ячейки» и нажмите «ОК».

Не снимая выделения с ячеек, используйте действие «Копировать». Вы можете нажать Ctrl+C в Windows, Command+C на Mac, щелкнуть правой кнопкой мыши и выбрать «Копировать» или нажать «Копировать» (значок с двумя страницами) на ленте на вкладке «Главная».

Теперь переместите туда, где вы хотите вставить ячейки, и используйте действие Вставить. Вы можете нажать Ctrl+V в Windows, Command+V на Mac, щелкнуть правой кнопкой мыши и выбрать «Вставить» или нажать «Вставить» на ленте на вкладке «Главная».

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

Если вы часто выполняете подобные действия в Word, обязательно ознакомьтесь с нашими инструкциями по вырезанию, копированию и вставке в Microsoft Word.

› Как пропустить вставку пустых ячеек при копировании в Microsoft Excel
› Как удалить пробелы в Microsoft Excel
› Как вставить галочку в Microsoft Excel
› Как восстановить метки панели задач в Windows 11
› Как установить Google Play Маркет в Windows 11
› Что означает XD и как вы его используете?
› Почему прозрачные чехлы для телефонов желтеют?
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)

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

Но когда дело доходит до отфильтрованных данных, копирование и вставка данных не всегда проходит гладко.

Вы когда-нибудь пытались вставить что-нибудь в отфильтрованную таблицу? Это не так просто, как кажется.

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

Оглавление

Копирование из отфильтрованного столбца без скрытых ячеек

Предположим, у вас есть следующий набор данных:

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

Для этого вы можете применить к таблице фильтр следующим образом:

Выберите всю таблицу.
На вкладке Данные нажмите кнопку "Фильтр" в группе "Сортировка и фильтрация".
Вы увидите маленькие стрелки в каждой ячейке строки заголовка. Они предназначены, чтобы помочь вам фильтровать ваши клетки. Вы можете щелкнуть любую стрелку, чтобы выбрать фильтр для соответствующего столбца.
В этом примере мы хотим отфильтровать только те строки, которые содержат отдел «ИТ». Итак, выберите стрелку рядом с заголовком отдела и снимите галочки напротив всех отделов, кроме «ИТ». Вы можете просто снять флажок «Выбрать все», чтобы быстро снять все флажки, а затем просто выбрать «ИТ».
Нажмите "ОК". Теперь вы увидите только строки с отделом «ИТ».

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

Итак, все, что вам нужно сделать, это:

Выберите видимые строки, которые вы хотите скопировать.
Нажмите CTRL+C или щелкните правой кнопкой мыши->Копировать, чтобы скопировать выбранные строки.
Выберите первую ячейку, в которую вы хотите вставить скопированные ячейки.
Нажмите CTRL+V или щелкните правой кнопкой мыши->Вставить, чтобы вставить ячейки.

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

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

В таких случаях также довольно просто скопировать только видимые строки. Вот что вам нужно сделать:

Выберите видимые строки, которые вы хотите скопировать.
Нажмите ALT+; (клавиша ALT и клавиша с запятой вместе). Если вы на Mac, нажмите Cmd+Shift+Z. Этот ярлык позволяет выбирать только видимые строки, пропуская скрытые ячейки.
Нажмите CTRL+C или щелкните правой кнопкой мыши->Копировать, чтобы скопировать выбранные строки.
Выберите первую ячейку, в которую вы хотите вставить скопированные ячейки.
Нажмите CTRL+V или щелкните правой кнопкой мыши->Вставить, чтобы вставить ячейки.

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

Но этого нельзя сказать о вставке в отфильтрованный столбец.

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

Когда дело доходит до вставки в отфильтрованный столбец, может быть два случая:

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

В первом случае вставить в отфильтрованный столбец довольно просто.

Допустим, мы хотим заменить все ячейки, содержащие Отдел = "IT", на полную форму: "Информационные технологии".

Для этого вы можете ввести слово «Информационные технологии» в любую пустую ячейку, скопировать его и вставить в видимые ячейки отфильтрованного «Отдел». Столбец. Вот пошаговая инструкция, как это сделать:

Выберите пустую ячейку и введите слова «Информационные технологии».
Скопируйте его, нажав CTRL+C или щелкните правой кнопкой мыши->Копировать.
Выберите все видимые ячейки в столбце с заголовком Отдел.
Вставьте скопированное значение, нажав CTRL+V или щелкните правой кнопкой мыши->Вставить.

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

Чтобы убедиться в этом, удалите фильтр, выбрав Данные->Фильтр.Обратите внимание, что все остальные ячейки столбца «Отдел» остаются без изменений.

Два способа вставки набора значений в видимые строки отфильтрованного столбца

Теперь давайте посмотрим, что происходит, когда вы хотите вставить набор значений в видимые ячейки отфильтрованного столбца. Допустим, вы хотите вставить список окладов только для строк, содержащих Department="Информационные технологии".

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

Это связано с тем, что вы не можете вставлять данные в ячейки диапазона, содержащего скрытые строки или столбцы. Это одно из ограничений Excel. Обойти это невозможно, но есть несколько приемов, которые можно использовать для достижения этой цели.

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

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

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

Для приведенного выше примера (где вы хотите скопировать набор значений заработной платы только в строки с отделом = «Информационные технологии») выполните следующие шаги:

Нажмите знак равенства ("=") в первой ячейке столбца, в который вы хотите вставить (G3).
Теперь выберите первую ячейку из списка, которую хотите скопировать (в нашем примере H3).
Это просто создаст ссылку на ячейку. Вы должны увидеть формулу: =H3 в ячейке G3.
Скопируйте эту формулу вниз, перетащив маркер заполнения (в правом нижнем углу ячейки G3). Формула должна быть вставлена только в видимые ячейки столбца G.
Чтобы убедиться в этом, удалите фильтр, выбрав Данные->Фильтры. Вот изображение столбца G без фильтров после операции копирования-вставки. Чтобы вам было понятнее, я выделил скопированные ячейки светло-зеленым цветом.
Теперь то, что вы скопировали, было просто ссылками на исходные ячейки. Поэтому, если вы попытаетесь удалить исходные ячейки после завершения копирования и вставки, скопированные значения исчезнут и из столбца G.
Чтобы избежать этого, вам нужно вставить эти результаты формулы как значения. Это довольно легко. Пока вы находитесь в режиме без фильтрации, скопируйте все ячейки столбца G, щелкните правой кнопкой мыши и выберите «Вставить значения» во всплывающем меню.
Все, теперь вы можете удалить исходные значения.

Вставка набора значений в видимые строки отфильтрованного столбца — с помощью VBScript

Это довольно простой и быстрый метод. Все, что вам нужно сделать, это скопировать приведенный ниже VBScript в окно разработчика и запустить его.

Чтобы использовать приведенный выше код, выполните следующие действия:

Выберите все строки, которые необходимо отфильтровать (включая заголовки столбцов).
На ленте меню Разработчик выберите Visual Basic.
Когда откроется окно VBA, нажмите Вставить->Модуль и вставьте приведенный выше код в окно модуля.

Теперь ваш макрос готов к запуску. Чтобы запустить код:

Сначала выделите ячейки, которые хотите скопировать.
Запустите скрипт, выбрав Разработчик->Макросы-> paste_to_filtered_col
Код попросит вас выбрать ячейки назначения (куда вы хотите вставить скопированные ячейки).
Выберите ячейки и нажмите "ОК".

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

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

Нажмите стрелку Настроить панель инструментов, расположенную над лентой меню Excel.
Выберите «Дополнительные команды» в раскрывающемся меню.
Откроется диалоговое окно Параметры Excel.
Нажмите раскрывающийся список под пунктом Выбрать команды из и выберите Макросы.
Выберите имя созданного вами макроса. В нашем случае это «ThisWorkbook.paste_to_filtered_col». Нажмите кнопку «Добавить>>».
Нажмите "ОК".

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

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

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

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

Вы скопировали ячейку и хотите вставить ее в несколько других ячеек. Звучит легко, правда? Но что, если вы хотите вставить это значение только во все видимые ячейки? Чтобы вы пропустили все отфильтрованные или скрытые ячейки между ними? Это также очень просто, но требует еще одного маленького шага. Вот как это сделать!

Способ 1. Используйте «Перейти к специальному» для вставки во все видимые ячейки

Первый метод основан на основных функциях Excel. Он использует специальную функцию Go to. Вот шаги в деталях.

Скопируйте ячейку, которую хотите вставить, во все видимые ячейки.
Выделите весь диапазон ячеек, в который вы хотите вставить его, включая скрытые или отфильтрованные ячейки.
Откройте специальное окно Перейти. Для этого нажмите на маленькую стрелку рядом с кнопкой «Найти и выбрать». Он расположен в правой части ленты «Главная» в Excel. Теперь нажмите «Перейти к специальному».
В качестве альтернативы вы можете использовать сочетания клавиш: Нажмите Ctrl + G на клавиатуре, чтобы открыть обычное окно «Перейти». Теперь либо нажмите кнопку «Специальная», либо нажмите Alt + S на клавиатуре.
В окне "Перейти к специальному" выберите "Только видимые ячейки" и подтвердите, нажав OK.
Теперь вы видите, что выбраны только видимые ячейки. Вставьте скопированные ячейки как обычно.

Способ 2. Делайте это быстрее с помощью Professor Excel Tools

Вы можете быстрее вставлять в видимые ячейки только с помощью Professor Excel Tools.

Как и в нашем методе 1 выше, сначала выберите весь диапазон ячеек.
На ленте Professor Excel нажмите стрелку «Инструменты выделения», а затем «Выбрать все видимые ячейки».
Вставьте свои значения.

Вы можете бесплатно попробовать Профессор Excel Tools. Эта надстройка расширяет возможности Excel более чем 125 новыми функциями, все на новой ленте «Профессор Excel». Нажмите здесь, чтобы начать загрузку (регистрация не требуется).

Эта функция включена в нашу надстройку Excel «Professor Excel Tools»

(без регистрации, загрузка начинается сразу)

Более 35 000 пользователей не могут ошибаться.

Также интересно:

Хенрик Шиффнер — независимый бизнес-консультант и разработчик программного обеспечения. Живет и работает в Гамбурге, Германия. Помимо увлечения Excel, он увлекается фотографией и спортом.

Оставить комментарий Отменить ответ

Лучшие записи и страницы

Объединение файлов Excel: как объединить книги в один файл
Возврат пустых ячеек вместо нулей в формулах Excel: легко!
Тысячи или миллионы в Excel: как изменить единицу измерения
Не удается выполнить прокрутку в Excel? Вот причины и простые решения!
Как отобразить сразу все строки или столбцы в Excel
Кнопки для вставки изображений или диаграмм в Excel выделены серым цветом?
ДВССЫЛ: как использовать текст в качестве ссылки на ячейку в Excel
Оглавление в Excel: 4 простых способа создания каталогов
Именованные диапазоны в Excel: просмотр всех определенных имен (включая скрытые имена)
Вставить название листа в ячейку: легко! 3 способа вернуть имя рабочего листа

Не пропустите главное

«Professor Excel Tools»: добавьте в Excel более 120 замечательных функций!

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

Лучшие советы, рекомендации и учебные пособия по Excel.
1 раз в месяц.
Никакого спама. Обещано.

Обзор конфиденциальности

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

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

Вставить, пропуская скрытые/отфильтрованные ячейки и строки, выбирая только видимые ячейки

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

<р>1. Выберите отфильтрованный диапазон G3:G24 и нажмите Alt + ; при этом выделять только видимые ячейки.

<р>2. В строке формул введите =C3 (C3 — первая ячейка с отфильтрованной ценой) и одновременно нажмите клавиши Ctrl + Enter, чтобы заполнить все выделенные ячейки. Затем вы увидите, что отфильтрованные цены вставляются в указанный диапазон, игнорируя скрытые ячейки и строки.

Вставить, пропуская скрытые/отфильтрованные ячейки и строки, с помощью Kutools for Excel

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

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

<р>1. Выберите диапазон, который вы скопируете, а затем нажмите Kutools > Диапазон > Вставить в видимый диапазон.

Примечание. Вы также можете нажать Предприятие > Вставить в видимый диапазон.

Демонстрация: вставка, пропускающая скрытые/отфильтрованные ячейки и строки в Excel

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

Суммирование/счет/усреднение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек/строк/столбцов

Обычно функция SUM/Count/Average подсчитывает все ячейки в указанном диапазоне независимо от того, скрыты/отфильтрованы ячейки или нет. В то время как функция промежуточного итога может только суммировать/подсчитывать/усреднять, игнорируя скрытые строки. Однако функции Kutools for Excel SUMVISIBLE / COUNTVISIBLE / AVERAGEVISIBLE легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы. Полнофункциональная бесплатная пробная версия на 30 дней!

Читайте также: