Как объединить ячейки в Excel, игнорируя или пропуская пустые?
Встроенная функция Excel КОНКАТЕНАЦИЯ позволяет быстро объединить значения нескольких ячеек в одну. Если среди выбранных ячеек есть пустые, эта функция включит и их в результат. Однако иногда нужно объединить только ячейки с данными, пропустив пустые. Как это сделать в Excel?
Конкатенация ячеек с игнорированием или пропуском пустых с помощью формулы
Конкатенация ячеек с игнорированием или пропуском пустых с помощью пользовательской функции
Конкатенация ячеек с игнорированием или пропуском пустых с помощью Kutools для Excel
Конкатенация ячеек с игнорированием или пропуском пустых с помощью формулы
Предположим, у вас в ячейках есть данные, среди которых встречаются пустые. Чтобы объединить строки в одну ячейку, воспользуйтесь формулой.
Введите эту формулу в пустую ячейку, куда вы хотите поместить результат конкатенации:=A1&,IF(A2<,>,«»,"-"&,A2,«»)&IF(A3<>«»,"-"&A3,«»)&IF(A4<>«»,"-"&A4,«»)&IF(A5<>«»,"-"&A5,«»), а затем перетащите маркер заполнения вправо до тех ячеек, к которым нужно применить формулу. Строковые значения объединятся без пустых ячеек — как показано на следующем снимке экрана:
Совет: если нужно объединить больше строк, используйте функцию ЕСЛИ для соединения ячеек, например: =A1&,IF(A2<,>,«»,"-"&,A2,«»)&IF(A3<>«»,"-"&A3,«»)&IF(A4<>«»,"-"&A4,«»)&IF(A5<>«»,"-"&A5,«»)&IF(A6<>«»,"-"&A6,«»). Символ «–» в формуле можно заменить любым другим разделителем по вашему выбору.
Конкатенация нескольких ячеек с игнорированием или пропуском пустых: Kutools для Excel's Объединить строки, столбцы или ячейки без потери данныхпозволяет объединять или конкатенировать несколько строк, столбцов или ячеек без потери данных, пропуская или игнорируя пустые ячейки.Нажмите, чтобы скачать Kutools для Excel!
Kutools для Excel: более чем 300 удобных надстроек для Excel, бесплатная пробная версия без ограничений на 30 дней.Скачайте и начните бесплатный пробный период прямо сейчас! |
Конкатенация ячеек с игнорированием или пропуском пустых с помощью пользовательской функции
Если нужно объединить множество ячеек, приведённая выше формула станет слишком громоздкой. В этом случае воспользуйтесь следующей пользовательской функцией.
1. Удерживая клавиши Alt + F11, откройте окно Microsoft Visual Basic для приложений.
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
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек