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