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

Обновлено: 21.11.2024

Сталкивались ли вы с проблемой копирования и вставки ячеек со скрытыми ячейками? Очевидно, это раздражает и отнимает много времени. Эта статья продемонстрирует вам, как копировать только видимые ячейки в Excel четырьмя быстрыми способами. Эти способы могут быть полезны для Excel для Microsoft 365, Excel для Интернета, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

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

я. Использование сочетания клавиш

Если у вас мало времени для выполнения какого-либо анализа, использование ярлыков может оказаться весьма полезным. На следующем рисунке у нас есть набор данных учебного заведения, который содержит информацию о студентах, хотя 4-я строка отсутствует. Есть полезная комбинация клавиш для копирования видимых ячеек только в Excel и это ALT + ; (точка с запятой). Следующие шаги относятся к сочетанию клавиш:

  1. Выберите набор данных A1:C7.
  2. Нажмите ALT +; (точка с запятой)
  3. Скопируйте набор данных (нажав CTRL+C)
  4. Вставить основу по вашему выбору (нажав CTRL+V)

ii. Использование инструмента «Перейти к специальному» на вкладке «Главная»

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

  1. Выберите параметр «Найти и выбрать».
  2. Выберите специальные предложения
  3. Нажмите «Только видимые ячейки».
  4. Нажмите "ОК".
  5. Выберите диапазон ячеек A1:C7.
  6. Скопируйте диапазон ячеек A1:C7 (нажав CTRL+C)
  7. Вставьте в нужное место, и результат будет показан на следующем рисунке (нажатие клавиш CTRL+V).

iii. Еще одно быстрое использование инструмента «Перейти к специальному»

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

  1. Выберите набор данных A1:C7.
  2. Скопируйте набор данных A1:C7, просто нажав CTRL+C.
  3. Вставьте в нужное место, просто нажав CTRL+V.

iv. Настройка панели быстрого доступа

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

  1. Открыть панель быстрого доступа для настройки
  2. Нажмите "Дополнительные команды".

  1. Выберите диапазон ячеек A1:C7.
  2. Выбрать видимые ячейки
  3. Скопируйте диапазон ячеек A1:C7 (нажав CTRL+C)
  4. Вставьте в нужное место и получите результат (нажав CTRL+V)

Заключение

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

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

Выполните следующие действия:

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

Совет. Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

Нажмите "Главная" > "Найти и выбрать" и выберите "Перейти к специальному".

Нажмите "Только видимые ячейки" > "ОК".

Нажмите «Копировать» (или нажмите Ctrl+C).

Выделите верхнюю левую ячейку области вставки и нажмите "Вставить" (или нажмите Ctrl+V).

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

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

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

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

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

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

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Что вы делаете, когда вам нужно скопировать диапазон ячеек в Excel и вставить его в другое место?

В большинстве случаев следующие три шага позволяют выполнить работу:

  1. Выберите ячейки, которые хотите скопировать.
  2. Скопируйте ячейки (Control + C).
  3. Выберите целевую ячейку и вставьте эти ячейки (Control + V).

Но что, если в наборе данных есть скрытые ячейки?

Тогда этих трех шагов недостаточно.

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

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

Обратите внимание, что в этом наборе данных есть скрытые строки (посмотрите на номера строк).

Теперь посмотрите, что произойдет, когда я попытаюсь скопировать эти ячейки и вставить их в другое место.

В приведенном выше примере я выбрал видимые ячейки, но когда я вставил эти ячейки в другое место, он скопировал как видимые, так и скрытые ячейки.

Обходной путь – убедиться, что Excel выбирает только видимые ячейки. Затем я могу копировать и вставлять только эти видимые ячейки.

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

Это руководство охватывает:

Выберите видимые ячейки с помощью сочетания клавиш

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

  • Для Windows: ALT + ; (удерживайте клавишу ALT, а затем нажмите клавишу с запятой)
  • Для Mac: Cmd+Shift+Z.

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

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

Хотя использование сочетания клавиш — это самый быстрый способ выделить видимые ячейки, если вы не хотите использовать клавиатуру или не помните сочетание клавиш, есть другой способ.

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

Вот шаги:

При этом будут выбраны все видимые ячейки в наборе данных.

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

Еще один отличный способ выделить видимые ячейки в Excel — добавить команду на панель быстрого доступа (QAT).

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

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

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

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

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

Оглавление

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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