Как объединить ячейки, игнорируя или пропуская пустые в Excel?

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

Объединение ячеек с игнорированием или пропуском пустых с помощью формулы

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

Объединение ячеек с игнорированием или пропуском пустых с помощью Kutools для Excel

Объединение ячеек с игнорированием или пропуском пустых с помощью формулы

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

Введите эту формулу в пустую ячейку, куда вы хотите поместить результат объединения: =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,""), затем перетащите маркер заполнения вправо к ячейкам, к которым вы хотите применить эту формулу. Значения строк будут объединены без пустых ячеек, как показано на скриншоте ниже.

Советы: Если нужно объединить больше строк, используйте функцию IF для соединения ячеек, например: =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,"")&IF(A6<>"","-"&A6,""). Символ "–" в формуле можно заменить любым другим разделителем по вашему выбору.

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

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

1. Удерживайте клавиши Alt + F11 в Excel, чтобы открыть окно Microsoft Visual Basic for Applications.

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

Код 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. Сохраните и закройте это окно кода, вернитесь на лист, затем введите эту формулу: =concatenatecells(A1:A5) в пустую ячейку и перетащите маркер заполнения вправо к ячейкам, к которым вы хотите применить эту формулу, и вы получите следующий результат:

Советы: В приведенном выше коде VBA вы можете изменить символ “/” на любой другой разделитель, который вам нужен.

Объединение ячеек с игнорированием или пропуском пустых с помощью Kutools для Excel

Помимо скучных формул и кодов, здесь я могу порекомендовать удобный инструмент — Kutools для Excel. Благодаря мощной утилите Объединить, вы сможете легко объединить несколько ячеек в одну.

После установки Kutools для Excel выполните следующие действия:

1. Выберите значения ячеек, которые вы хотите объединить.

2. Нажмите Kutools > Объединить и разделить > Объединить строки, столбцы или ячейки без потери данных, см. скриншот:

3. В появившемся диалоговом окне:

• (1.) Выберите тип ячеек, которые вы хотите объединить. Можно объединить столбцы, строки или все ячейки в одну;
• (2.) Укажите разделитель для ваших объединенных данных;
• (3.) Выберите место для размещения результата: в верхнюю или нижнюю ячейку;
• (4.) Укажите, как обрабатывать объединенные ячейки: можно сохранить или удалить содержимое из объединенных ячеек, а также объединить сами ячейки.

4. После завершения настройки нажмите кнопку ОК, и выбранные строки будут объединены в одну ячейку по отдельности, автоматически пропуская пустые ячейки, см. скриншот:

Скачайте и попробуйте Kutools для Excel прямо сейчас!

Демонстрация: Объединение ячеек с игнорированием или пропуском пустых с помощью Kutools для Excel