Вопрос поступил от студента проходящего курс "Excel бизнес-анализ и прогнозирование".

Суть «кейса» состоит в том, чтобы средствами Excel перенести значения из вертикального диапазона в одну ячейку.
Конечно, есть вариант копирования значений из каждой ячейки, вставляя текст в одну, или «склеивать» части текста с помощью амперсанда (&), но это может быть долго, неудобно, и с высокой вероятностью ошибок, особенно если нужно объединить большое количество ячеек. Поэтому рассмотрим варианты, которые помогут сохранить нам и время, и нервные клетки.

Самым простым и очевидным способом может оказаться опция «Заполнить» (Fill), которая находится на вкладке «Главная» (Home).

Для этого нужно:

1. Расширить ячейку, чтобы в ней мог поместиться объединенный текст (если значений много, то ширина должна быть большой).
2. Выделить диапазон ячеек, который нужно объединить.
3. На вкладке «Главная», в группе команд «Редактирование», выбрать «Заполнить» и в выпадающем списке – опцию «Выровнять».

Как видим на изображении ниже, мы легко и просто получили результат. Текст из ячеек переместился в одну и разделен пробелами.
 

Однако, данный способ имеет недостатки, которые не всегда позволяют им воспользоваться:

• ячейки разделяются пробелом, нельзя указать другой разделитель;
• инструмент работает «на месте», нельзя отобразить результат в другой ячейке;
• если в диапазоне, который нужно объединить находятся цифры или формулы – инструмент не будет работать;
• значения остались только в одной ячейке, в остальных они исчезли (в большинстве случаев их нужно сохранить);
• если значений много, то ширина столбца может быть очень большой, что не всегда удобно.

 

В «кейсе» нашего студента как раз нужно было объединить числа, перечислив их в итоговой ячейке через запятую.

Поэтому рассмотрим другие способы решения данной задачи.

Если Вы пользуетесь версиями Excel 2019 или подпиской Office 365, то задача максимально упрощается.

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

 

Т.е. для решения задачи можно добавить столбец с разделителями, которыми хотим разделить текст (прием хорош, если нужны разные разделители) и воспользоваться данной функцией.

Формула может выглядеть примерно так:

=СЦЕП(A3:B19)

Также в версиях Excel 2019 и подписка Office 365 появилась функция ОБЪЕДИНИТЬ (TEXTJOIN), которая позволяет объединить значения из нескольких диапазонов, вставляя между текстовыми значениями указанный разделитель.

 

При использовании данной функции создавать столбец с разделителями уже нет необходимости.

В первом аргументе функции указываем каким символом нужно разделить объединённый текст.

Во втором аргументе указываем, нужно ли пропускать пустые ячейки (ИСТИНА – пустые ячейки игнорируются, ЛОЖЬ – пустые ячейки учитываются).

А далее выделяем ячейки с текстом, который нужно объединить.

Функцию можно записать так:

=ОБЪЕДИНИТЬ(",";ИСТИНА;A3:A19)

Если же у Вас более ранняя версия Excel, в которой нет функций СЦЕП и ОБЪЕДИНИТЬ, чтобы не идти длинным путем:

1. Добавьте столбец с разделителями (аналогично тому, как делали с функцией СЦЕП), объедините столбцы между собой построчно, к примеру:

=A3&B3

или

=СЦЕПИТЬ(A3;B3)

Результатом «склеивания» будут уже не числа а текст.

1. Скопируйте результат и вставьте как значения.
2. Воспользуйтесь опцией «Заполнить» (Fill), описанной в начале данной статьи.

Для решения задачи таже можно воспользоваться и макросом, но это уже  другая история… (Кстати их мы изучаем на курсе Excel VBA (Макросы): автоматизация бизнес-задач)

Автор статьи тренер DATAbi Михаил Беленчук

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"

 

Дізнайтеся більше про повну програму "EXCEL VBA (МАКРОСИ): АВТОМАТИЗАЦІЯ БІЗНЕС-ЗАДАЧ"