Как не копировать пустые ячейки в excel

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

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

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

Скопируйте и вставьте только непустые ячейки с формулой массива

Скопируйте и вставьте только непустые ячейки с кодом VBA

Скопируйте и вставьте только непустые ячейки с Kutools for Excel

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

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

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

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

3. В Перейти к специальному диалоговое окно, отметьте Константы вариант, см. снимок экрана:

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

5. А затем скопируйте и вставьте данные в нужное место. И были вставлены только непустые значения ячеек. Смотрите скриншот:

Внимание: Этот метод доступен только для констант, он не применяется к ячейкам формулы.

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

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

2. Нажмите Данные > Фильтр, см. снимок экрана:

3. Затем нажмите кнопку раскрывающегося списка в правом углу ячейки в выбранном списке и снимите флажок Пробелы вариант в раскрывающемся меню. Смотрите скриншот:

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

5. Наконец, вы можете скопировать отфильтрованные данные и вставить их в любое нужное место.

Внимание: Таким образом вступают в силу и значения, и формулы.

Скопируйте и вставьте только непустые ячейки с формулой массива

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

1. Помимо ваших данных, введите в пустую ячейку следующую формулу:

=LOOKUP(«zzzzz»,CHOOSE({1,2},»»,INDEX(A:A,SMALL(IF($A$1:$A$15<>»»,ROW($A$1:$A$15)),ROWS($B$1:B1)))))

Внимание: В приведенной выше формуле A1: A15 — это список данных, который вы хотите использовать. Вы можете изменить его по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter вместе, а затем выберите ячейку B1 и перетащите маркер заполнения в диапазон, который вы хотите содержать эту формулу, и все непустые значения ячеек были извлечены. Смотрите скриншот:

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

Внимание: Эта формула доступна только для констант и не применяется к ячейкам формулы.

Скопируйте и вставьте только непустые ячейки с кодом VBA

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

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: копируйте и вставляйте только непустые ячейки в Excel

Sub PasteNotBlanks()
'Update 20140325
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
If InputRng.Columns.Count > 1 Then
    MsgBox "Please select one column."
    Exit Sub
End If
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
InputRng.SpecialCells(xlCellTypeConstants).Copy Destination:=OutRng.Range("A1")
End Sub

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

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

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

Внимание: Этот код доступен только для констант.

Скопируйте и вставьте только непустые ячейки с Kutools for Excel

Есть ли способ намного проще, чем указано выше? Конечно, Kutools for Excel‘s Выбрать непустую ячейкуУтилита s может помочь вам сначала выбрать непустые ячейки, а затем скопировать и вставить их.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон ячеек и щелкните Кутулс > Выберите > Выбрать непустые ячейки. Смотрите скриншот:

2. Затем выбираются непустые ячейки, нажимаем Ctrl + C чтобы скопировать их, затем выберите ячейку, в которой вы хотите вывести результат, нажмите Ctrl + V для вставки выбранных непустых ячеек. Смотрите скриншот:

Скопируйте и вставьте только непустую ячейку

Некоторые из Вас, должно быть, обратили внимание на такой инструмент Excel как Paste Special (Специальная вставка). Многим, возможно, приходилось испытывать недоумение, если не разочарование, при копировании и вставке данных в Excel. Вы вставляли данные и получали совсем не то, что ожидали получить. Я покажу Вам некоторые интересные трюки с использованием Специальной вставки и расскажу, как использовать некоторые полезные возможности этого инструмента, чтобы данные всегда выглядели именно так, как нужно.

После прочтения этой статьи Вы научитесь транспонировать, удалять ссылки и пропускать пустые ячейки при помощи инструмента Paste Special (Специальная вставка). После каждого раздела приведено краткое пошаговое резюме. Пользуйтесь ссылками быстрого доступа или распечатайте эти резюме на бумаге, поместите рядом с компьютером и используйте их как удобную инструкцию. Ну что ж, приступим!

Если Вы хотите при помощи инструмента Paste Special (Специальная вставка) научиться вставлять только значения или форматирование, копировать ширину столбцов, умножать и делить данные на заданное число, а также прибавлять и удалять значение сразу из целого диапазона ячеек обратитесь к статье Специальная вставка в Excel: значения, форматы, ширина столбцов .

• Базовые знания о Специальной вставке
• Транспонировать: преобразуем столбцы в строки (или наоборот)
• Транспонировать – кратко о главном
• Удаляем гиперссылки
• Быстрое удаление гиперссылок – кратко о главном
• Пропускаем пустые ячейки
• Пропускаем пустые ячейки – кратко о главном

Базовые знания о Специальной вставке

Прежде чем воспользоваться инструментом Paste Special (Специальная вставка), Вы должны иметь что-то в буфере обмена, иначе, при попытке использовать эти функции, они будут серого цвета и не активны, как на рисунке ниже.

Первым делом Вы должны скопировать данные в буфер обмена, иначе не сможете воспользоваться функцией Специальная вставка. На рисунке видно, что на Ленте и в контекстном меню, инструмент Paste Special (Специальная вставка) не активен:

Сначала Вы должны выбрать и скопировать любую информацию. После этого действия Excel хранит скопированные данные в буфере обмена. Теперь Вы можете использовать Специальную вставку. Есть два способа вызвать эту функцию. Оба открывают диалоговое окно Paste Special (Специальная вставка), которое предоставляет доступ к целому набору полезных настроек (см. рисунок ниже).

Воспользуйтесь одним из следующих способов:

1. На вкладке Home (Главная) нажмите на маленький треугольник под словом Paste (Вставить) и в выпадающем меню выберите Paste Special (Специальная вставка).
2. Щелкните правой кнопкой мыши, а затем в контекстном меню выберите Paste Special (Специальная вставка).

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

Преобразуем столбцы в строки (или наоборот)

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

Для этого выделяем данные, копируем их, ставим курсор в нужную ячейку, открываем меню инструмента Paste Special (Специальная вставка) и выбираем Transpose (Транспонировать) – все это показано на изображениях ниже. Видите, как просто сделать таблицу такой, как Вы хотите? Представьте себе все возможности, которые дает инструмент Transpose (Транспонировать).

Первый пример транспонирования таблицы:

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

Например, Вам в руки попадает вот такой шаблон (смотрите ниже), предоставленный компанией, чтобы заполнить отчёт о расходах при посещении конференции.

Однако, на конференции, которую Вы посещали, был предоставлен электронный отчёт в таком виде:

Просто скопируйте нужную информацию (в нашем случае B1:B11), поместите курсор в ячейку, в которую Вы хотите вставить эту информацию (ячейка C3 в шаблоне), нажмите Paste Special (Специальная вставка), а затем выберите опцию Transpose (Транспонировать). Вот что получится:

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

Транспонировать – кратко о главном

1. Выберите данные.
2. Скопируйте выбранные данные. Команда Cut (Вырезать) не позволит использовать Специальную вставку, поэтому воспользуйтесь именно командой Copy (Копировать).
3. Поместите курсор в ячейку, в которую нужно вставить данные.

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

1. Нажмите Paste Special (Специальная вставка). Это можно сделать 2-мя путями:
   • Щелкните правой кнопкой мыши и выберите в контекстном меню Paste Special (Специальная вставка).
   • На вкладке Home (Главная) под командой Paste (Вставить) нажмите маленький треугольник и в открывшемся меню выберите Paste Special (Специальная вставка).

   Оба способа откроют диалоговое окно Paste Special (Специальная вставка), в котором Вы найдёте множество полезных настроек.

2. Поставьте галочку в строке опции Transpose (Транспонировать).

Удаляем гиперссылки (много и быстро)

Это было весело! Теперь давайте попробуем другую интересную функцию Специальной вставки. Этот трюк особенно полезен, когда требуется вставить текст, насыщенный гиперссылками. Гиперссылки иногда попадаются под руку в процессе работы с данными в Excel. Обратите внимание на синий подчёркнутый текст на рисунке ниже. В каждой ячейке столбца A содержится гиперссылка. Каждый раз, когда Вы кликаете по ячейке, компьютер открывает гиперссылку. Вы можете щелкать правой кнопкой мыши по каждой ячейке и выбирать команду Remove hyperlink (Удалить гиперссылку), но это займёт целую вечность. Представьте, если в Вашей таблице содержится 2000 строк и 1000 столбцов.

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

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

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

Быстрое удаление гиперссылок – кратко о главном

1. Выделите все ячейки, из которых требуется удалить гиперссылки.
2. Скопируйте их.
3. Поставьте курсор в ячейку, куда необходимо вставить скопированные данные.
4. Нажмите Paste Special (Специальная вставка).
5. Выберите пункт Values (Значения).
6. Нажмите ОК.
7. Вырежьте и вставьте ячейки в любое нужное Вам место.

Пропускаем пустые ячейки

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

Обычная команда Paste (Вставить) тут не поможет. Почему? – видно на рисунке ниже. Если Вы скопируете информацию (A6:A19) и вставите в ячейку E4, то эти приставучие пустые ячейки из столбца A будут записаны поверх данных в столбце E, которые Вы хотели бы сохранить. Не желаете ли узнать способ способный пропустить пустые ячейки?

Для этого скопируйте ячейки A5:A19, затем поместите курсор в первую ячейку области, куда необходимо вставить скопированные данные (E4). Далее нажмите Paste Special (Специальная вставка), поставьте галочку на опции Skip Blanks (Пропускать пустые ячейки) и нажмите ОК. Вуаля! Вы успешно скопировали данные из столбца A в столбец E, сохранив все нужные данные. Отличная работа! Это простой пример, но он показывает все возможности, которые открывает для Вас и Ваших таблиц инструмент Skip Blanks (Пропускать пустые ячейки).

Пропускаем пустые ячейки – кратко о главном

1. Выберите данные, которые хотите скопировать.
2. Скопируйте их.
3. Поместите курсор в начальную ячейку области, в которую хотите скопировать.
4. Нажмите Paste Special (Специальная вставка).
5. Выберите Skip Blanks (Пропускать пустые ячейки).
6. Нажмите ОК.

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