В этом посте мы увидим, как вставить содержимое нескольких ячеек, чтобы все было в одной ячейке. Это распространенная проблема, и ее довольно легко решить с помощью функции СЦЕПИТЬ в Excel. Эта функция может не только соединять ячейки, но и добавлять другие символы. Строка формул выглядит так.
Здесь между ячейками вставлены пробелы («»), а в конце рисуется восклицательный знак.
Однако именно эти пробелы являются первым неприятным моментом при использовании функции СЦЕПИТЬ, потому что функция вставляет ячейки без «сшивания» и пробелы приходится добавлять вручную. Если вам нужно связать только несколько слов, проблема невелика, но когда вам нужно вставить как минимум дюжину ячеек, добавление пробелов вручную немного утомительно. Чтобы упростить процесс, вам могут посоветовать ввести пробел в отдельной ячейке, а затем поставить на него ссылку.
Так что идет немного быстрее. Хотя это не очень эффективно, вам нужно будет занять отдельную камеру. И если вы случайно удалите эту ячейку, обычно этого не произойдет. В общем, при небольшом объеме данных работают оба варианта.
Кстати, не все знают, что в Excel есть еще и специальный оператор для вставки ячеек — & (и амперсанд). Вам просто нужно вставить его между соединенными ячейками или символами.
Вводить амперсанд неудобно — сначала нужно переключиться на английскую раскладку, а потом нажать Shift + 7. Поэтому рекомендуется использовать специальную комбинацию: удерживая клавишу Alt, нажимать 3 и 8 на цифровой клавиатуре. Это очень помогает сохранять душевное спокойствие.
Формула СЦЕПИТЬ и амперсанд работают нормально, если вам не нужно связывать большое количество ячеек. Вы устали вводить «;» или «&». Также, как правило, между ячейками добавляется пробел. К сожалению, вы не можете (уже) указать в качестве аргумента весь диапазон. Выход из этой ситуации следующий.
Функция СЦЕПИТЬ для большого количества ячеек
Один из вариантов — указать массив данных в качестве ссылки на ячейку. Сразу стоит отметить, что данные могут располагаться как по горизонтали, так и по вертикали. Если данные находятся в одной строке, делаем следующее. Например, слова, разделенные ячейками, находятся в пятой строке. Теперь в пустой ячейке укажите весь диапазон для подключения и добавьте пробел («») через амперсанд (» «).
Затем мы нажимаем F9, чтобы формула выдала результат вычисления, в нашем случае это будет массив.
Как видите, к каждому слову добавлен пробел, а между словами стоит точка с запятой — именно то, что вам нужно вставить в формулу СЦЕПИТЬ. Теперь снимем лишние скобки и вставим этот массив в формулу. Нажмите Ввод.
Данные могут располагаться не по горизонтали (как в примере выше), а по вертикали. В этом случае значения в результирующем массиве будут разделены двоеточиями. Вам нужно будет сделать дополнительный шаг, чтобы заменить их точкой с запятой (через «Найти и заменить», горячую клавишу Ctrl + H или даже быстрее — используйте функцию TRANSPOSE, чтобы создать вертикальный массив из горизонтального массива).
Этот вариант работает нормально, но подходит для одноразового использования, поскольку исходная информация не связана с исходными ячейками. Чтобы изменить, вам нужно будет войти в формулу или повторить шаги для новых данных. Кроме того, существует ограничение на длину формулы, и не всегда можно таким образом связать несколько тысяч ячеек.
Поэтому перейдем к следующему способу. Он, признаюсь, выглядит не очень эстетично. Но дешево, надежно и практично! (c) В любом случае прием помогает решить проблему. И это самое главное. Нам понадобится дополнительный столбец (или строка, в зависимости от того, как находятся исходные данные для подключения).
Мы рассматриваем только вертикальное расположение данных (горизонтальное будет работать так же). Нижняя строка предназначена для создания столбца рядом с данными с формулой СЦЕПИТЬ, которая будет последовательно соединять одно слово за раз по мере того, как вы спускаетесь. В нашем случае первая формула будет содержать только слово «Александр» (и пробел), следующая внизу добавляет «Сергеевич» (и пробел) и т.д.
Нужная ячейка будет последней, которая уже объединяет все слова. Результат можно оставить в виде формулы или сохранить как значение, удалив все вычисления.
Соединение текста и даты
И еще одна хитрость с формулой CONCATENA. Чаще всего приходится сочетать текст с датой. Дата, как известно, представляет собой простое порядковое число (отсчет времени начинается с 1 января 1900 года), которому присваивается желаемый формат даты. Если вы напрямую свяжете текст и дату, на выходе будет указанный текст и простое число. Возьмем, например, слово «Сегодня» (не забудьте добавить пробел в конце) и функцию СЕГОДНЯ (которая всегда возвращает текущую дату).
Как видите, ничего не произошло.
Для правильной склейки необходимо «обернуть» дату в формулу ТЕКСТ, которая дает заданный формат числа.
Формат ДД.ММ.АЙ указывает, в какой форме должна быть указана дата. Например, если указать формат ДД.ММ.ГГГГ, дата будет выглядеть так: 03.09.2015 (год состоит из 4 цифр).
Кстати, добавить текст к числу или дате можно не только с помощью функции СЦЕПИТЬ. Вы можете использовать собственный числовой формат. Однажды в моем рабочем файле была автоматически обновляемая метка.
Поэтому я всегда прекрасно знал текущую дату и день недели. В этом случае содержимое ячейки состояло только из одной функции СЕГОДНЯ.
Эту идею довольно просто реализовать. Но это уже чистое форматирование. Я при случае расскажу вам поподробнее.
PS Все вышеперечисленные проблемы решены в новых функциях Excel СЦЕПИТЬ и ОБЪЕДИНИТЬ .
3 способа склеить текст из нескольких ячеек
Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
В категории «Текст» есть функция СЦЕПИТЬ, которая объединяет содержимое нескольких ячеек (до 255) в единое целое, позволяя комбинировать их с произвольным текстом. Например, вот так:
Градиент: не забывайте пробелы между словами — они должны быть записаны как отдельные аргументы и заключены в круглые скобки, т.к текст.
Конечно, если вам нужно собрать много сниппетов, использовать эту функцию уже не очень удобно, так как вам придется писать ссылки на каждый сниппет ячейки отдельно. Поэтому, начиная с версии Excel 2016 года, функция СЦЕПИТЬ была заменена более продвинутой версией с аналогичным именем и тем же синтаксисом: функция СЦЕПИТЬ. Его ключевое отличие в том, что теперь вместо отдельных ячеек в качестве аргументов можно указывать целые диапазоны — текст из всех ячеек из всех диапазонов будет объединен в одно целое:
Для массового объединения также удобно использовать новую функцию TEXTJOIN, представленную в Excel 2016. Она имеет следующий синтаксис:
= ОБЪЕДИНЕНИЕ (разделитель; Пропустить_или_пустые_ячейки; Диапазон1; Диапазон2 .)
- Разделитель — символ, который будет вставлен между фрагментами
- Второй аргумент отвечает за игнорирование пустых ячеек (ИСТИНА или ЛОЖЬ)
- Range 1, 2, 3. — диапазоны ячеек, содержимое которых мы хотим вставить
Например:
Способ 2. Символ для склеивания текста (&)
Это универсальный и компактный способ подключения, который работает во всех версиях Excel.
Чтобы суммировать содержимое нескольких ячеек, используйте знак плюса «+», а для вставки содержимого ячеек используйте знак «&» (на большинстве клавиатур встречается под цифрой «7»). При его использовании необходимо помнить, что:
- Этот символ должен быть размещен в каждой точке соединения, например, на всех «стыках» строк текста, так же, как вы добавляете некоторые преимущества при добавлении большего количества чисел (2 + 8 + 6 + 4 + 8)
- Если вам нужно вставить произвольный текст (даже если это просто точка или пробел, не говоря уже обо всем слове), то этот текст необходимо заключить в кавычки. В приведенном выше примере с функцией СЦЕПИТЬ Excel позаботится о кавычках — в этом случае вам придется вводить их вручную.
Например, вот как вы можете поместить свое полное имя в ячейку из трех, добавив пробелы:
Если совместить это с функцией извлечения первых букв из текста — ВЛЕВО, то можно получить фамилию с инициалами в формуле:
Способ 3. Макрос для объединения ячеек без потери текста.
У нас есть текст в нескольких ячейках и желание объединить эти ячейки в одну, объединив там свой текст. Проблема одна: кнопка «Объединить и центрировать» в Excel может объединять ячейки, но сложность текста — живым остается только текст в левой верхней ячейке.
Чтобы объединить ячейки с объединенным текстом (как в таблицах Word), вам понадобится макрос. Для этого откройте редактор Visual Basic во вкладке Разработчик — Visual Basic или нажав Alt + F11, вставьте новый программный модуль в нашу книгу (меню Вставка — Модуль) и скопируйте туда текст такого простого макроса:
Теперь, если вы выберете несколько ячеек и запустите этот макрос с помощью сочетания клавиш Alt + F8 или кнопки «Макрос» на вкладке «Разработчик» — «Макрос», Excel объединит выбранные ячейки в одну, объединяя текст, разделенный пробелами.
