Перейти до основного матеріалу

Як скопіювати та вставити лише непорожні клітинки в Excel?

Більшість з нас може страждати від цієї проблеми, коли ми копіюємо список даних, які містять кілька порожніх комірок, а потім вставляємо їх в інший стовпець, порожні клітинки також будуть вставлені. Це дуже дратує, коли нам не потрібні порожні місця і ми хочемо вставити лише порожні клітинки. Як у цьому випадку ми могли швидко та зручно скопіювати та вставити лише непорожні клітинки в Excel? Ось декілька швидких прийомів, щоб вирішити це:

Скопіюйте та вставте лише незаповнені комірки за допомогою команди "Перейти до спеціального"

Скопіюйте та вставте лише незаповнені клітинки з функцією фільтра

Скопіюйте та вставте лише незаповнені клітинки з формулою масиву

Скопіюйте та вставте лише незаповнені клітинки з кодом VBA

Копіюйте та вставляйте лише непорожні клітинки за допомогою Kutools для Excel гарна ідея3


стрілка синя права міхур Скопіюйте та вставте лише незаповнені комірки за допомогою команди "Перейти до спеціального"

З Перейти до спеціального Ви можете спочатку вибрати всі дані, а потім скопіювати та вставити їх в інше місце.

1. Виберіть свій список даних, які ви хочете використовувати.

doc-copy-only-nonblanks1

2. Потім натисніть Головна > Знайти та вибрати > Перейти до спеціального, див. скріншот:

doc-copy-only-nonblanks1

3, в Перейти до спеціального діалогове вікно, поставте галочку Константи варіант, див. скріншот:

doc-copy-only-nonblanks1

4. Потім натисніть OK, і у списку були вибрані лише комірки значень.

doc-copy-only-nonblanks1

5. А потім скопіюйте та вставте дані в потрібне місце. І було вставлено лише значення порожніх комірок. Дивіться знімок екрана:

doc-copy-only-nonblanks1

примітки: Цей метод доступний лише для констант, він не застосовується до клітин формули.


стрілка синя права міхур Скопіюйте та вставте лише незаповнені клітинки з функцією фільтра

Команда фільтр Ця функція може допомогти вам спочатку відфільтрувати непусті клітинки, а потім скопіювати та вставити їх в інші комірки, як вам потрібно.

2. Клацання дані > фільтр, див. скріншот:

doc копіювати лише незаповнені6

3. Потім натисніть кнопку спадного меню у правому куті комірки у вибраному списку та зніміть прапорець Бланки у спадному меню. Дивіться знімок екрана:
doc-copy-only-nonblanks1

4. А потім клацніть OK, всі непусті клітинки були відфільтровані, як показано на наступному скріншоті:

doc-copy-only-nonblanks1

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

примітки: Як значення, так і формули набувають чинності, використовуючи цей спосіб.


Виділіть непусті клітинки відразу

Натисніть, щоб отримати безкоштовну повнофункціональну пробну версію за 30 днів!
doc kutools виділити не порожні клітинки

стрілка синя права міхур Скопіюйте та вставте лише незаповнені клітинки з формулою масиву

Щоб скопіювати та вставити лише непорожні клітинки, можна також застосувати таку формулу масиву.

1. Окрім своїх даних, введіть у порожню комірку таку формулу:

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

doc-copy-only-nonblanks1

примітки: У наведеній вище формулі, A1: A15 це список даних, який ви хочете використовувати. Ви можете змінити його відповідно до своїх потреб.

2. Потім натисніть Shift + Ctrl + Enter клавіші разом, а потім виберіть комірку B1 і перетягніть маркер заповнення до діапазону, який ви хочете містити у цій формулі, і всі непусті значення комірки були витягнуті. Дивіться знімок екрана:

doc-copy-only-nonblanks1

3. Оскільки вони є формулами, коли ви копіюєте їх в інше місце, вам слід скопіювати та вставити їх як значення.

примітки: Ця формула доступна лише для констант, не застосовується до клітинок формули.


стрілка синя права міхур Скопіюйте та вставте лише незаповнені клітинки з кодом VBA

Якщо вас цікавить код VBA, ви можете виконати наступний код, щоб закінчити це завдання.

1. Утримуйте клавішу ALT + F11 і відкриває Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код 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 клавіші для запуску цього коду, з'явиться підказка з нагадуванням про вибір діапазону даних, який ви хочете використовувати. Дивіться знімок екрана:

doc-copy-only-nonblanks1

4. А потім клацніть OK, з’явиться інше вікно запиту, яке дозволить вам вибрати комірку для розміщення даних.

doc-copy-only-nonblanks1

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

примітки: Цей код доступний лише для постійних.


стрілка синя права міхур Копіюйте та вставляйте лише непорожні клітинки за допомогою Kutools для Excel

Чи є набагато простіший спосіб, ніж вище? Звичайно, Kutools для Excel's Виберіть Nonblank CellУтиліта s може допомогти вам спочатку виділити непорожні комірки, а потім скопіювати та вставити їх.

Kutools для Excel, з більш ніж 300 зручні функції, полегшує вам роботу. 

після безкоштовна установка Kutools для Excel, виконайте наведені нижче дії.

1. Виберіть діапазон комірок і натисніть Кутулс > Select > Виберіть Nonblank Cells. Дивіться знімок екрана:
doc виділити непусту клітинку 2

2. Потім виділяються непусті клітинки, натисніть Ctrl + C щоб скопіювати їх, потім перейдіть до вибору комірки, куди ви хочете вивести пасетований результат, натисніть Ctrl + V вставити виділені непусті клітинки. Дивіться знімок екрана:
doc виділити непусту клітинку 3

стрілка синя права міхур Скопіюйте та вставте лише порожню комірку

Kutools для Excel: 300+ функцій, які ви повинні мати в Excel, 30-денна безкоштовна пробна версія звідси

Найкращі інструменти продуктивності офісу

Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

вкладка kte 201905


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
=Filter(A:A,A:A<>"")
This comment was minimized by the moderator on the site
Array formula Works fine on text
but don't work on numbers.
This comment was minimized by the moderator on the site
Not working on Numbers !!!
Works fine on text !!!
Please guide
This comment was minimized by the moderator on the site
Thanks for the great tips! I am using the Find & Select feature, but it's taking a long time because my spreadsheet contains 70 columns and 450k rows. Is there a way to fasten the process?
This comment was minimized by the moderator on the site
When i select and copy only non-blank cells, how can i then paste it somewhere else with keeping the same empty ranges between them?
This comment was minimized by the moderator on the site
These tools delete the blank cells and compress the row. I want the format to be retained, including the spaces, but I do not want empty cells to overwrite cells containing existing data. i.e. I want to add the new, copied, cells in a column onto an existing column.
This comment was minimized by the moderator on the site
Hi #abc I have used your formula =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('List of Overdue items'!A:A,SMAL L(IF('List of Overdue items'!A$1:A$15 00"",ROW('List of Overdue items'!A$1:A$15 00)),ROWS('List of Overdue items'!B$1:B2)) ))) I have data in one Worksheet and want to copy that data into another worksheet (same doc). Can you help?
This comment was minimized by the moderator on the site
Is there a way to make the macro work when referencing between different sheets?
This comment was minimized by the moderator on the site
This formula is absolutely amazing, thank you. Unfortunately it breaks when trying to copy from another sheet, is there a workaround?
This comment was minimized by the moderator on the site
correct the references when using the formula between different sheets. It does work. =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('List of Overdue items'!A:A,SMALL(IF('List of Overdue items'!A$1:A$1500"",ROW('List of Overdue items'!A$1:A$1500)),ROWS('List of Overdue items'!B$1:B2)))))
This comment was minimized by the moderator on the site
thanks for the super formula: =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$15"",ROW($A$1:$A$15)),ROWS($B$1:B1)))))
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations