Формула Excel: Объединение ячеек с запятой

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

Формула 1. Объединение ячеек через запятую с использованием оператора &

Универсальная формула:

Аргументы

Как работает эта формула

Например, чтобы объединить ячейки каждой строки из столбцов B, C, D и E в одну ячейку через запятую, используйте приведённую ниже формулу в пустой ячейке.

Нажмите клавишу Enter, и ячейки объединятся с разделением запятыми.

Пояснение

&: этот оператор используется для объединения ячеек и запятых.

Примечание

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

Формула 2. Объединение ячеек через запятую с помощью функции СЦЕПИТЬ

Универсальная формула:

Аргументы

Как работает эта формула

Например, чтобы объединить ячейки B3, C3, D3 и E3 в одну ячейку с запятыми, используйте приведённую ниже формулу в пустой ячейке.

Нажмите клавишу Enter, после чего ячейки будут объединены с разделением запятыми.

Пояснение

СЦЕПИТЬ — функция, которая объединяет текстовые строки.

Примечание

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

Если среди объединяемых ячеек есть пустые, при использовании указанных выше формул (оператор & и функция СЦЕПИТЬ) в результирующей ячейке появятся лишние запятые, как показано на снимке экрана ниже.

В таком случае воспользуйтесь формулой 3.

Формула 3. Объединение ячеек через запятую с помощью комбинации функций ПОДСТАВИТЬ и СЖПРОБЕЛЫ

Универсальная формула:

Аргументы

Как работает эта формула

Например, чтобы объединить ячейки B3, C3, D3 и E3 в одну ячейку через запятую, используйте приведённую ниже формулу в пустой ячейке.

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

Пояснение

СЖПРОБЕЛЫ— функция: функция СЖПРОБЕЛЫ удаляет все лишние пробелы из текстовой строки. Формула TRIM(B3&« »&C3&« »&D3&« »&E3) сначала объединяет ячейки, разделяя их пробелами, а затем убирает лишние пробелы — благодаря этому в результате не появляются начальные или конечные запятые.

ПОДСТАВИТЬ — функция, которая заменяет указанный текст на другой. Например, формула =SUBSTITUTE(TRIM(B3&,« »&,C3&,« »&,D3&,« »&,E3),« »,", ") заменит все пробелы на запятую с пробелом в объединённой строке (результат объединения ячеек B3, C3, D3 и E3).

Примечание

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

Объединение ячеек с другими разделителями

Если вы хотите использовать другие разделители вместо запятых, замените «,» в формулах на нужные символы. Например, объединение со знаком плюс.

=SUBSTITUTE(TRIM(B6&,« »&,C6&,« »&,D6&,« »&,E6),« »,«+»)
Или
=CONCATENATE(B3,«+»,C3,«+»,D3,«+»,E3)
Или
=B3&«+»&C3&«+»&D3&«+»&E3

Пример файла

Нажмите, чтобы скачать пример файла

Связанные формулы

• Проверка, содержит ли ячейка определённый текст
  Чтобы проверить, содержит ли ячейка любой текст из диапазона A, но не содержит ни одного текста из диапазона B, используйте формулу массива, сочетающую функции СЧЁТ, ПОИСК и И в Excel.
• Проверка, содержит ли ячейка одно из нескольких значений, но исключает другие
  В этом руководстве представлена формула для быстрой проверки: содержит ли ячейка хотя бы одно из указанных значений, но при этом не содержит других. Также подробно разобраны аргументы этой формулы в Excel.
• Проверка, содержит ли ячейка одно из значений
  Допустим, у вас в Excel есть список значений в столбце E, и вы хотите проверить, содержат ли ячейки в столбце B хотя бы одно из этих значений, чтобы получить результат ИСТИНА или ЛОЖЬ.
• Проверка, содержит ли ячейка число
  Иногда бывает нужно проверить, содержит ли ячейка числовые значения. В этом руководстве представлена формула, которая возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ — если не содержит.