8 способов объединения в Excel могут улучшить ваши данные

Как

ПоРайан Дубе

Последнее обновление13 ноября 2019 г.

Функция конкатенации в Excel является одной из наиболее часто используемых функций. Причина в том, что он такой универсальный. Всякий раз, когда данные в вашей электронной таблице выглядят не совсем так, как вам хотелось бы, или вам нужен лучший способ представления данных в вашей таблице, конкатенация может помочь.

Как работает конкатенация в Excel

Функция Concatenate очень проста в использовании. Синтаксис для функции работает следующим образом:

CONCATENATE (текст1, текст2, текст3,…)

Эта функция выглядит очень просто. Он объединит несколько текстовых строк в одну строку. Вы помещаете каждую строку (или ячейку, которая содержит строку), разделенные запятыми внутри функции.

Хотя это может быть просто, его использование в изобилии.

В этой статье мы рассмотрим несколько примеров наиболее полезных способов использования функции Concatenate в различных проектах.

1. Форматирование Имен

Одним из наиболее распространенных применений функции Concatenate является ситуация, когда ваша электронная таблица содержит имена в двух столбцах, как в примере выше. Это один из самых простых способов использовать эту функцию, так как он просто объединяет два столбца с пробелом в середине.

Синтаксис для этого:

КОНКАТЕНАТ (B2, ”“, A2)

Это поместит имя и фамилию в правильном порядке с пробелом в середине. Чтобы заполнить оставшуюся часть столбца для других имен, просто нажмите и удерживайте сдвиг ключ на клавиатуре и двойной щелчок.

Это автоматически заполнит столбец остальными именами.

2. Обновление статуса на простом языке

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

Экономьте время, используя Concatenate для создания статуса с конечным результатом в самой верхней части листа. Если результат вычислений находится в ячейке G47, вы используете следующую формулу для создания обновленного статуса в ячейке A1:

= CONCATENATE («Общие расходы на сегодняшний день: $», G47)

Это приведет к утверждению в верхней части таблицы, как показано ниже.

Теперь вы можете сэкономить время, просто открыв лист и сразу получив конечный результат.

3. Разбор почтового адреса

Одним из наиболее популярных применений функции Concatenate является получение различных полей в базе данных, заполненной информация от пользователей, клиентов или кого-либо еще, и объединение их полного адреса в единый поле.

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

В вашей электронной таблице могут быть части адреса, распределенные по полям, разбросанным по всей таблице.

Вы можете создать отформатированный адрес, поместив части адреса в одну ячейку, разделенную символом возврата каретки. В Excel это «Char (10)».

Вы собираете адрес, используя следующую функцию:

= CONCATENATE (G2, CHAR (10), H2, CHAR (10), K2, ”,”, L2, ”“, M2)

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

Результат может фактически выглядеть как беспорядочный беспорядок. Это потому, что вам нужно отформатировать ячейки адреса с текстом Wrap.

Сделайте это, выбрав все ячейки адреса, щелкнув правой кнопкой мыши и выбрав Формат ячеек. В окне «Формат ячеек» выберите центровка вкладка и включить перенос текста.

Теперь вы увидите, что все адреса отформатированы, как и ожидалось.

Этот подход можно использовать для любого поля, в котором вы хотите создать форматированный текст, который собирает данные из всей электронной таблицы.

4. Разобрать URL

Еще одно хорошее использование функции Concatenate - это объединение всех элементов для полная строка URL. Например, в электронной таблице продуктов URL-ссылка на купленный продукт может состоять из домена сайта, отдела и идентификатора продукта.

Для каждой строки вы можете построить URL, используя как статическую, так и динамическую адресацию для ячеек.

= СЦЕПИТЬ ( « https://”,D1,”/”,D3,”/”,O3,”.php”)

Это присоединяет статический текст «https:» к началу и «.php» к концу строки URL для этого продукта. Вы вставите сегменты URL-адреса, выбрав ячейки для домена, отдела и идентификатора продукта между каждой прямой косой чертой. Все они разделены запятыми.

Результатом является идеально отформатированная URL-ссылка на этот продукт.

Вы можете заполнить остальные ячейки, используя тот же подход, что и выше. Удерживая нажатой клавишу Shift, поместите указатель мыши в нижний правый угол первой ячейки, где вы создали объединенный URL-адрес, пока не увидите двойные параллельные линии. Дважды щелкните, чтобы заполнить остальные ячейки в этом столбце той же функцией. Ячейки будут автоматически настраиваться для каждого ряда.

5. Динамические титульные таблицы

Одним из любимых применений функции Concatenate является динамическое присвоение названий диаграмм в электронной таблице. Это часто необходимо для обновления таких вещей, как текущая дата для электронной таблицы, которая используется в качестве периодического отчета.

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

= CONCATENATE («Общий объем продаж по состоянию на:«, ГОД (СЕЙЧАС ()))

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

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

Сделать это:

1. Выберите заголовок диаграммы
2. Выберите поле функции и введите «= I1» (замените I1 где вы построили объединение заголовков диаграмм)
3. Нажмите Войти и название диаграммы будет соответствовать динамически обновляемой ячейке

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

6. Объединить цифры и текст

Еще одна полезная функция функции Concatenate заключается в том, что она позволяет объединять текстовые ячейки и числовые ячейки в одну строку. Вы также можете ввести даты, если вы преобразуете поле даты в текст.

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

= CONCATENATE (C4, «продано», E4, «единицы измерения«, D4 », на«, TEXT (A4, «мм / дд / гггг»))

Это займет два текстовых поля (имя торгового представителя и название подразделения) и объединит его с полем с номером и датой (номер подразделения и дата продажи).

Это позволяет вам делать такие вещи, как создание целого текстового поля, которое затем можно использовать для автоматической отправки содержимого электронной почты с листа Excel.

7. Автоматически создавать HTML

Если вы воспользуетесь этой возможностью для объединения текста, значений и других данных из вашей электронной таблицы в одну ячейку содержимого, а затем комбинируя это с использованием функции новой строки Char (10) и HTML-кодов, вы можете создавать HTML-код, который можно вставить в любой веб-страница.

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

Например, допустим, что вы работаете на веб-сайте книжного клуба и публикуете еженедельные обновления для участников с последними материалами для чтения за месяц. Если вы используете электронную таблицу для регистрации каждого ежемесячного обновления, вы можете использовать Concatenate для создания содержимого страницы для этой записи.

Создайте еще одну ячейку для содержимого веб-страницы, а затем объедините свой контент, используя записи ячейки на листе:

= CONCATENATE («Привет всем снова!

», CHAR (10),« В этом месяце мы будем читать »А2» по ",БИ 2,".

”, Char (10),” Это название было опубликовано в »С2». »,« Я с нетерпением жду услышать, что вы все думаете! »)

Как видите, объединение статических строк, информации из ячеек и кодов форматирования в сочетании с HTML форматирование кодов позволяет вам создавать любой контент, который динамически извлекает информацию из вашего электронные таблицы.

Приложив немного креатива, вы можете создать много содержимого страницы. И просто скопируйте и вставьте его прямо в ваш блог.

8. Генерировать коды

Еще один полезный прием, для которого вы можете использовать метод конкатенации, состоит в том, чтобы объединить фрагменты информации из каждого поля в специальный код. Вы можете использовать этот код в качестве уникального идентификатора.

Используя пример электронной таблицы продукта, предположим, что вы хотите создать уникальный код для идентификации каждой отдельной покупки, сделанной на веб-сайте. В этом примере код покупки будет состоять из:

• Первые две буквы отдела
• Последние две буквы имени клиента
• Первые два числа почтового индекса
• Последние 8 номеров идентификатора продукта

Вы можете сделать все это, комбинируя функцию Concatenate с Левая и правая строковые функции:

= СЦЕПИТЬ (ЛЕВЫЙ (D3,2), вправо (H3,2), влево (M3,2), вправо (O3,8))

Это вложит функцию Left (захват символов слева) и функцию Right (перевод символов справа) в функцию Concatenate.

Это полезно для всего: от создания уникальных паролей до создания уникального URL-адреса веб-сайта для веб-страниц.