Excel — отличный инструмент для хранения и анализа данных. И часто вам придется иметь дело с типами текстовых данных, такими как имена, регионы, отделы или названия продуктов.
В таких случаях хорошо знать, как манипулировать текстовыми данными и получить желаемый результат.
Одна из наиболее распространенных задач, которые приходится выполнять большинству пользователей Excel, — это работа с набором данных с именами. Часто вы обнаруживаете, что имя и фамилия находятся в отдельных столбцах, и вам может потребоваться объединить эти имя и фамилию и получить их как объединенное имя в ячейке.
В этом руководстве по Excel я покажу вам несколько различных способов сочетания имени и фамилии в Excel.
Вы можете легко сделать это, используя простые формулы (такие как Concatenate или TextJoin) и такие функции, как Flash Fill и Power Query.
4 способа сочетания имени и фамилии в Excel
Предположим, у вас есть набор данных, как показано ниже, и вы хотите объединить имя в столбце A и фамилию в столбце B.
Давайте посмотрим, как это сделать.
Использование функции СЦЕПИТЬ (или амперсанда)
Комбинировать разные текстовые строки из разных ячеек в Excel довольно просто. В Excel есть встроенная формула Concatenate, предназначенная только для этой цели.
Ниже приведена формула, которая объединит имя и фамилию (разделенные пробелом):= СЦЕПИТЬ (A2; ""; B2)
СЦЕПИТЬ — это новая функция, которая была введена в Excel 2016 и заменяет функцию СЦЕПИТЬ. Но на данный момент обе функции по-прежнему доступны, и вы можете использовать любую из них.
Ниже приведена формула СЦЕПИТЬ, если вы хотите ее использовать:= СЦЕПИТЬ (A2; ""; B2)
Приведенная выше формула просто берет имя и фамилию и объединяет их. И поскольку я хочу, чтобы они были разделены пробелом, я использовал «» (пробел в двойных кавычках) в качестве второго аргумента.
Вы также можете использовать оператор амперсанда для конкатенации.
Предполагая, что у вас один и тот же набор данных, вы можете использовать приведенную ниже формулу для объединения имени и фамилии:= A2 & "" & B2
Оператор амперсанда объединяет текст до и после него. В приведенном выше примере я объединил три части — имя, пробел и фамилию.
Теперь, когда вы понимаете концепцию, вы можете комбинировать имена в разных форматах, если хотите. Например, вы можете указать фамилию, а затем имя или запятую вместо пробела между именами.
Если вам нужно только комбинированное имя и вы хотите избавиться от имени и фамилии, вам следует сначала преобразовать значения формулы в статические значения. После этого вы можете удалить / удалить имя и фамилию.
Использование функции TEXTJOIN
TEXTJOIN — это функция, доступная в Excel2021-2022 и Office 365.
Если у вас есть доступ к этой функции, лучше всего использовать ее для объединения ячеек и столбцов (так как она намного лучше, чем приведенные выше методы CONCATENATE и амперсанда).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите объединить имя и фамилию.
Ниже приведена формула для этого:= TEXTJOIN (""; ИСТИНА; A2: B2)
Вышеупомянутая функция TEXTJOIN принимает три аргумента:
- Разделитель, который в этом примере представляет собой пробел в двойных кавычках (поскольку мы хотим, чтобы имя и фамилия разделялись пробелом)
- Логическое значение, где ИСТИНА означает, что в случае наличия пустых ячеек формула игнорирует их.
- Диапазон, содержащий ячейки, которые вы хотите объединить.
Это быстрее, чем обычная формула конкатенации, а также проще в создании и управлении. Поэтому, если у вас есть доступ к функции TEXTJOIN в Excel, лучше использовать ее вместо любой другой формулы.
Использование Flash Fill
Flash Fill — это умная функция, которая пытается понять шаблон и дать вам результат.
Позвольте мне объяснить, как это работает.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите объединить имя и фамилию.
Ниже приведены шаги, которые можно использовать для этого с помощью Flash Fill.
- В ячейке C2 введите желаемый результат. В нашем примере это будет «Бобби Бейкер».
- В ячейке C3 начните вводить ожидаемый результат. Вы заметите, что Excel показывает ожидаемый результат во всех ячейках (в светло-сером тексте). Это Flash Fill в действии
- Нажмите клавишу Enter
Вышеупомянутые шаги мгновенно заполнят все ячейки объединенным именем.
В некоторых случаях возможно, что вы не увидите заливку флэш-памяти при вводе текста во второй ячейке.
Не волнуйтесь, иногда такое бывает.
В таком сценарии ниже другие шаги, которые вы можете использовать, чтобы заставить работать Flash Fill:
- В ячейке C2 введите желаемый результат. В нашем примере это будет «Бобби Бейкер».
- Выберите выберите C3
- Перейдите на вкладку «Главная»
- В группе «Редактирование» щелкните значок «Заливка».
- Нажмите на Flash Fill.
Вышеупомянутые шаги мгновенно выберут узор из ячейки выше и заполнят весь столбец комбинированным именем.
В случае, если Flash-заливка не может подобрать правильный узор и дает неверный результат, заполните две ячейки вручную, а затем выполните указанные выше действия.
Вы также можете использовать сочетание клавиш Ctrl + E для заливки с использованием флэш-заливки.
Объединение имени и фамилии (или даже имени, отчества и фамилии) — это простая операция, с которой легко справится Flash Fill.
Имейте в виду, что Flash Fill не идеален. Он работает, определяя шаблоны и используя один и тот же шаблон для заполнения всех ячеек в столбце. Хотя, скорее всего, это сработает должным образом, рекомендуется перепроверить результат Flash Fill.
Использование Power Query
Power Query — потрясающий инструмент, который используется для извлечения и преобразования данных.
Вы также можете использовать это, чтобы быстро объединить столбцы и объединить имя и фамилию.
Для работы Power Query ваши данные должны находиться в таблице Excel (или, по крайней мере, в именованном диапазоне).
Для этого урока я конвертирую набор данных с первым и последним именем в таблицу Excel.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите объединить два столбца, чтобы получить полное имя.
Ниже приведены шаги по преобразованию данных в таблицу Excel:
- Выберите любую ячейку в наборе данных
- Перейдите на вкладку «Вставка».
- Щелкните значок таблицы
- В диалоговом окне «Создание таблицы» убедитесь, что диапазон указан правильно.
- Нажмите ОК
Вышеупомянутые шаги преобразуют диапазон данных в таблицу Excel.
Теперь давайте посмотрим, как объединить имя и фамилию с помощью Power Query:
- Выберите любую ячейку в таблице
- Перейдите на вкладку «Данные».
- В группе «Получить и преобразовать данные» выберите параметр «Из листа». Откроется редактор Power Query.
- В редакторе Power Query убедитесь, что на левой панели выбрана правая таблица. Если у вас только одна таблица, вы увидите только одну опцию на левой панели.
- Выберите столбцы, которые необходимо объединить (удерживайте клавишу Ctrl, а затем щелкните заголовок столбца, чтобы выбрать столбец)
- Щелкните правой кнопкой мыши и выберите параметр «Объединить столбцы».
- В открывшемся диалоговом окне «Объединить столбцы» выберите «Пробел» в качестве разделителя (из раскрывающегося списка).
- Введите имя для нового объединенного столбца. Я использую «Полное имя»
- Щелкните ОК. Это позволит удалить два столбца, которые у нас уже есть, и заменить их новым столбцом с полным именем.
- Перейдите на вкладку «Файл», а затем нажмите «Закрыть и загрузить».
Вышеупомянутые шаги позволят вставить новый лист в книгу с таблицей, в которой есть один столбец с полным именем.
Если вам нужны исходные столбцы, а также новый столбец, содержащий полное имя, вместо того, чтобы выбирать столбцы и объединять их, сначала выберите вкладку «Добавить столбец» в редакторе Power Query, затем выберите столбцы, а затем нажмите «Объединить» Колонны. Это добавит новый столбец с полным именем.
По сравнению с методами формул и Flash Fill, Power Query определенно немного длиннее.
Но преимущество использования этого метода заключается в том, что в случае изменения исходных данных вы можете быстро обновить запрос, и полученные данные будут обновлены автоматически.
Кроме того, Power Query широко используется для объединения различных таблиц и данных из нескольких листов и книг. Итак, если у вас есть данные об именах, которые вы хотите объединить, это один шаг в уже существующем рабочем процессе запроса мощности.
В этом уроке я рассказал, как объединить имя и фамилию в Excel. Но если у вас есть имя, отчество и фамилия, вы можете использовать для этого те же методы.
Надеюсь, вы нашли этот урок полезным.
