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

Як об’єднати клітинки ігнорувати або пропускати порожні місця в Excel?

Excel З'єднати Ця функція може допомогти вам швидко об’єднати кілька значень комірок в одну комірку. Якщо у вибраних клітинках є кілька порожніх комірок, ця функція також поєднає пробіли. Але, іноді, ви просто хочете об’єднати клітинки з даними та пропустити порожні комірки, як можна закінчити це в Excel?

Конкатенати клітин ігнорують або пропускають порожні місця з формулою

Конкатенати клітинок ігнорують або пропускають порожні місця за допомогою користувацької функції

Об’єднані клітинки ігнорують або пропускають пробіли за допомогою Kutools для Excel


Припустимо, у мене є такі дані комірок, які заповнені деякими порожніми клітинками, щоб об’єднати рядки в одну комірку, ви можете застосувати формулу для її вирішення.

doc поєднувати пропуски пропусків 1

Будь ласка, введіть цю формулу в порожню комірку, куди ви хочете поставити об'єднаний результат, =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,""), потім перетягніть маркер заповнення прямо до комірок, до яких ви хочете застосувати цю формулу, і значення рядків були об'єднані без порожніх комірок, як показано на наступному знімку екрана:

doc поєднувати пропуски пропусків 2

Tips : Якщо потрібно об'єднати більше рядків, вам слід використовувати функцію IF, щоб приєднати комірки, наприклад =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,"")&IF(A6<>"","-"&A6,""). "-" символ у формулі можна замінити будь-якими іншими роздільниками, які вам потрібні.


Об’єднати кілька клітинок ігнорувати або пропускати порожні місця:

Kutools для Excel's Поєднувати Рядки, стовпці або комірки без втрати даних Ця функція може допомогти вам об’єднати або об’єднати кілька рядків, стовпців або комірок без втрати даних, але пропустити або проігнорувати порожні комірки. Клацніть, щоб завантажити Kutools для Excel!

doc поєднувати пропуски пропусків 7

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


Якщо потрібно об’єднати кілька комірок, наведена вище формула буде занадто складною для виконання, тому для її розв’язання можна використовувати наступну визначену користувачем функцію.

1. Утримуйте клавішу Alt + F11 клавіші в Excel, і він відкриває Microsoft Visual Basic для додатків вікна.

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

Код VBA: Конкатенати клітинок ігнорує порожні місця:

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "/"): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

3. Збережіть і закрийте це вікно коду, поверніться до робочого аркуша, а потім введіть цю формулу: = об'єднані клітинки (A1: A5) в порожню комірку і перетягніть маркер заповнення прямо до комірок, щоб застосувати цю формулу, і ви отримаєте такий результат:

doc поєднувати пропуски пропусків 3

Tips : У наведеному вище коді VBA ви можете змінити/”До будь-яких інших роздільників, які вам потрібні.


Окрім нудної формули та коду, тут я можу порадити зручний інструмент -Kutools для Excel, з його потужним Поєднувати Утиліта, ви можете без зусиль об'єднати кілька комірок в одну комірку.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть значення комірок, яке потрібно об’єднати.

2. Клацання Кутулс > Злиття та розділенняПоєднуйте рядки, стовпці або комірки, не втрачаючи даних, див. скріншот:

3. У спливаючому діалоговому вікні:

  • (1.) Виберіть тип комірок, які ви хочете об'єднати, ви можете об'єднати стовпці, об'єднати рядок і об'єднати всі комірки в окрему комірку;
  • (2.) Вкажіть роздільник для об’єднаного вмісту;
  • (3.) Виберіть місце для розміщення результату, ви можете помістити результат у верхню клітинку або нижню комірку;
  • (4.) Вкажіть, як мати справу з об’єднаними комірками, ви можете зберігати або видаляти вміст із цих об’єднаних комірок, а також можете об’єднувати ці об’єднані комірки.

doc поєднувати пропуски пропусків 5

4. Після закінчення налаштувань натисніть Ok і вибрані рядки були об'єднані в одну клітинку окремо, і вона автоматично пропустила порожні місця, див. знімок екрана:

doc поєднувати пропуски пропусків 6

Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


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

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

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

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

Опис


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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How would you change the "/" sign for CHAR(10) or line break?
This comment was minimized by the moderator on the site
Hello, Alan,To combine the cells with the line break, you can apply the below simple formula:=TEXTJOIN(CHAR(10),TRUE,A1:A5)
After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
Please try, thank you!
This comment was minimized by the moderator on the site
super, thank Q
This comment was minimized by the moderator on the site
I had issues with the formula provided causing leading delimiters, so I did this instead and it works well. This is concatenating cells horizontally while ignoring blank cells and results in no extra commas.

=IF(A2="", "", A2)&IF(A2="", "", ", ")&IF(B2="", "", B2)&IF(B2="", "", ", ")&IF(C2="", "", C2)
This comment was minimized by the moderator on the site
This worked a treat, thanks so much
This comment was minimized by the moderator on the site
Hi, used the macro and changed the "/" to a comma "," but got a lot of commas and it appeared to add all the blank cells.
I am doing a nested if statement to determine the appropriate sorting in the database. Is this enough to make the blank cell 'active' so that the macro sees this and adds it to the text string? How to work around that?
thanks much
This comment was minimized by the moderator on the site
Hello, Melinda,
the above vba code works well in my worksheet, you just need to change the separator / to comma as below:

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice 20151103
For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & ","): Next
Concatenatecells = Left(nn, Len(nn) - 1)
End Function

and then apply this formula:=concatenatecells(A1:A5)
This comment was minimized by the moderator on the site
thanks!
I found my problem was in the logic statement that I used to select data for these cells that I was trying to text string. I used a " " instead of "" for the false statement. That was picked up by the macro and used as a space bar and came out , , , , , text, , , ,
So I went back and took out the space and just have the "" and then the macro worked great.
Of course I am learning macros so that's another adventure.
thanks much!!
This comment was minimized by the moderator on the site
Thank you, it was very helpful!
This comment was minimized by the moderator on the site
Please help, i dont always have a value in my first column, that couses that I end up with a seperator infort of the final result. Is there a way around this?
This comment was minimized by the moderator on the site
ever get tis figured out? same boat.
This comment was minimized by the moderator on the site
I don't know how to do it all in one cell, but I added a row with this formula: =IF(LEFT(U20,1)=",",RIGHT(U20,LEN(U20)-2),U20).
My separator was a comma and a space ", " so I used -2 for LEN. U20 is the cell with the concatenated &if formula. The logic of this formula is that if the first character from the left equals a comma, then delete the first two characters; otherwise leave it alone.

Hope this helps.
This comment was minimized by the moderator on the site
Thank you! This saved hours of frustration on my part! Works as a charm!
This comment was minimized by the moderator on the site
The VBA script is wrong, because the output of the formula puts a huge space between the delimiters.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations