Polytech-soft.com

ПК журнал
8 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel соединение строк

Как выполнять Конкатенацию в Excel Чтобы Объединять Строки Текста

Russian (Pусский) translation by Andrey Rybin (you can also view the original English article)

Многие люди воспринимают Microsoft Excel, как инструмент по работе с электронными таблицами. Хотя Excel используется для модификации и создания электронных таблиц, его особая функция — это автоматизация работы с данными.

Одним из ключевых навыков для работы с данными является объединение ячеек, также известное как конкатенация. Давайте рассмотрим способы объединения данных в Microsoft Excel.

Что Означает Конкатенация?

Предположим, что у вас есть имя клиента в колонке С, и фамилия в колонке D. Вы хотите, чтобы в колонке E, у вас было записано полное имя, состоящее из этих двух частей, но это слишком затратно, заново все записывать в новой колонке.

На рисунке ниже я работаю с данными о сотрудниках. У меня есть имя и фамилия в разных колонках, но я хочу объединить их в одно полное имя в колонке E.

Пример, как выглядит конкатенация в Excel, для данных о именах сотрудников.

К счастью, Excel может сделать это для нас автоматически. Эта техника называется конкатенация, и в общем под этим подразумевается объединение ячеек.

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

Как Делать Конкатенацию в Excel (Смотри и Учись)

Посмотрите этот короткий видеоурок ниже, чтобы получить представление о том, как использовать конкатенацию для объединения текстовых строк и данных. И обязательно закачайте бесплатное приложение к уроку в виде рабочих документов Excel. А затем прочтите текстовую часть урока, чтобы узнать еще больше о конкатенации в Microsoft Excel.

3 Способа Объединения Текста в Excel

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

1. СЦЕПИТЬ

Используется в Excel 2013 и более ранних версиях

Вы можете использовать функцию СЦЕПИТЬ, чтобы объединять ячейки. Используйте точку с запятой, чтобы разделять ячейки или поля, которые вы хотите объединить. Однако, если вы пользуетесь Excel 2016, то возможно вы захотите использовать более обновленную версию этой функции, о которой расскажем дальше.

2. СЦЕП

Используется в Excel 2016 и Excel Mobile.

В Excel 2016 Microsoft отказались от функции СЦЕПИТЬ и заменила ее на СЦЕП (прим. перевод.: однако поддержка старой функции так же осталось, для возможности работы с документами старых версий). Использование точно такое же, просто Microsoft сократили название. (Кто же будет их обвинять? Я часто ошибаюсь в длинном названии).

Если вы открываете электронную таблицу в Excel 2013 или более поздней версии, она по-прежнему работает правильно в Excel. Со временем вы возможно обновите свои формулы и будете использовать СЦЕП, но есть третий способ объединять ячейки, который я рекомендую использовать прежде вего. Давайте узнаем, про этот способ.

Рекомендуемый способ объединения текста.

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

Однако, иногда может быть сложно прочитать формулу с «&» стоящей между каждой парой ячеек, или прочесть кусок текста в формуле. Чтобы формула лучше читалась, я могу использовать СЦЕП. Любая функция отлично работает для объединения.

3 Практических Упражнения по Объединениям в Excel

Для всех этих примеров я буду использовать рекомендуемый для объединения текста & оператор . Давайте рассмотрим примеры.

Для этих упражнений, я рекомендую использовать документы Excel, которые я создал специально для этого урока. Если вы пропустили это раньше, то обязательно скачайте бесплатно прилагающиеся к этому уроку, Книгу Excel, с которой вы сможете работать и следить за действиями по ходу урока.

Упражнение 1. Элементарное объединение в Excel

Используйте вкладку с названием Basic Join в книге Excel прилагающейся к этому уроку.

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

Для этого мы используем самый простой подход. Чтобы объединить текст из ячейки А2 с текстом в ячейке В2, я записал простую формулу в ячейке С2:

Я использовал основную формулу «&» чтобы объединить ячейки А2 и В2, и затем потянул результат вниз, чтобы создать другие объединенные сочетания.

Excel объединит текст из ячеек А2 и В2 и в результате получится сложное слово в С2. Я могу потянуть формулу вниз, чтобы распространить ее и на другие ячейки, чтобы комбинировать все данные из колонок А и В.

Упражнение 2. Добавление Пробела в Объединение

Используйте вкладку с названием Add Your Own Space из Excel — книги, прилагающейся к уроку.

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

Однако, нам надо немного изменить формулу. Если мы объединим мое имя и фамилию, то мы получим «AndrewChildress«. Нам нужно вставить пробел в формулу, чтоб разделить имя и фамилию. Давайте посмотрим, как это сделать:

В этом примере, я собираюсь объединить ячейки С2 и D2 (колонки, те что слева на картинке). И еще, нам нужно вставить пробел, между именем и фамилией.

Читать еще:  Сравнить списки в excel на различия

Мы можем использовать кавычки, чтобы добавить наш собсвтенный текст в объединение. Просто используйте еще одни символ «&», и пробел внутри, чтобы разделить имя. Посмотрите, как это должно выглядеть:

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

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

Упражнение 3. Добавление Вашего Текста

Используйте вкладку с названием Add Your Own Text из Excel — книги, прилагающейся к уроку.

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

Среди данных на картинке ниже, у меня есть колонка А с пунктом назначения. Я хочу, чтобы в колонке В, было сообщение «I’ve been to«(я был в), и затем название из колонки А. Проблема в том, что у меня нет «I’ve been to» в моей таблице, поэтому я вручную вбиваю это в формулу.

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

Пример Excel формулы объединения с текстом «I’ve been to».

Не забудьте добавить точку в конце, чтобы получилось правильное предложение! Я написал строковую переменную в кавычках — («I’ve been to «) и затем добавил A2, и добавил точку в конце.

В результате моя формула выглядит следующим образом:

Продвинутый Уровень Excel: Создание Первичного Ключа

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

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

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

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

Ниже несколько пояснений, почему плохо использовать одну колонку в виде первичного ключа:

  • Мы не можем использовать имя клиента, чтобы уникальным образом идентифицировать транзакцию. Что если у нас будет два покупателя с именем «Боб Смит»?
  • Мы не можем использовать время заказа в качестве уникального идентификатора. В большом магазние, в одно время могут быть зарегестрированы две покупки.
  • Также и наименование товара, заказанного клиентом или адрес доставки, могут появляться несколько раз в вашей базе данных.

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

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

В результате получается мешанина символов в колонке H. Однако, это как раз то, что нам нужно.

Объединение нескольких колонок данных для получения уникального первичного ключа.

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

Вы можете запустить функцию Удалить Дубликаты для колонки H, чтобы вычистить любые конфликтующие записи. Вы также можете эффективно использовать функцию ВПР, потому, что строки уникальны.

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

Подводим Итоги и Продолжаем Обучение

СЦЕП и «&» — казалось бы, просты формулы Excel, но они гораздо ценнее, чем может показаться на первый взгляд. Не важно просто ли вы объединяете данны в более сложные строки, или создаете первичный ключ, я думаю, что это хороший навык, которому стоит научиться.

  • Наш другой урок Как Находить и Удалять Дубликаты — это урок Excel, в котором мы рассматриваем автоматизацию повседневной работы по очистке данных.
  • Официальная документация Microsoft по СЦЕП и СЦЕПИТЬ — это ценный и авторитетный источник, из которого можно во всех подробностях узнать как объединять текст и ячейки.
  • Я начал этот урок с того, что назвал Excel отличным инструментом автоматизации. ВПР — это другая функция, которая позволяет автоматизировать работу с данными и избежать перепечатывания данных, и по этой функции у нас также есть урок — как использовать ВПР.

Может вы можете придумать другие примеры использования этой функции и поделитесь этим с читателями Envato Tuts+? Оставляйте комментарии ниже, делитесь своими знаниями или задавайте вопросы.

3 способа склеить текст из нескольких ячеек

Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE) , которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:

Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст.

Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT) . Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:

Читать еще:  Найти одинаковые строки в excel

Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN) , появившуюся начиная с Excel 2016. У нее следующий синтаксис:

=ОБЪЕДИНИТЬ( Разделитель ; Пропускать_ли_пустые_ячейки ; Диапазон1 ; Диапазон2 . )

  • Разделитель — символ, который будет вставлен между фрагментами
  • Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
  • Диапазон 1, 2, 3 . — диапазоны ячеек, содержимое которых хотим склеить

Например:

Способ 2. Символ для склеивания текста (&)

Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.

Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

  • Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
  • Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.

Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:

Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT) , то можно получить фамилию с инициалами одной формулой:

Способ 3. Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt + F11 , вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) , то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Как в Excel объединить текст из двух и нескольких ячеек в одну

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

Объединение текста нескольких ячеек в одну – базовая функция при работе с табличными данными, с ее помощью содержание произвольного количества полей можно сцеплять нужным образом и размещать в свободных ячейках. Она используется при составлении пофамильных списков сотрудников, отчетов различных направлений и других операциях с большими массивами информации. Для объединения ячеек есть два способа: с потерей данных и без потери. В первом случае в объединенной ячейке остается только одно значение, во втором – сохраняется весь текст. Второй способ предполагает как минимум четыре варианта объединения: с использованием двух функций («Сцеп-сцепить» и «Объединить»), при помощи символа амперсанд и с применением макроса. В статье подробно разберемся, как в Excel объединить текст нескольких ячеек в одну.

С помощью функции

Excel предусматривает несколько функций для объединения ячеек. Для начала, расскажем о самой простой и «теряющей» информацию. Если интересует, как в Экселе соединить текст из двух ячеек в одну, оставив только одно значение – то вам понадобится функция «объединить» в разделе «Формат ячейки».

  1. Выделите мышкой ячейки, которые нужно объединить.
  2. По образовавшемуся полю щелкните правой кнопкой мыши.
  3. Выберите пункт «Формат ячеек».
  4. В появившемся окне зайдите в раздел «Выравнивание».
  5. Отметьте галочкой пункт «Объединить ячейки» и нажмите «Ок». Выделенные поля объединяться в одну большую с сохранением содержания первой ячейки.

Также эта функция есть в графе «Главная» в разделе «Выравнивание» в верхней части экрана.

С помощью функции «Сцеп-сцепить»

В ранних версиях Excel для объединения текста ячеек применялась функция «Сцепить», в новых вариантах программы ее заменили функцией «Сцеп». Для ее использования:

  1. Щелкните по пустой ячейке в Excel.
  2. Поставьте знак «=» и большими буквами введите «Сцепить» либо «Сцеп».
  3. В скобках после формулы кликните по ячейке, которая должна быть первой в объединенной ячейке, либо введите ее адрес вручную (например, A1). Затем поставьте точку с запятой, после щелкните или напишите вторую ячейку. Затем поставьте точку с запятой и отметьте третье поле. После перечисления адресов нажмите «Enter». В поле, где вбивали формулу, появится объединенный текст. Он появится без пробелов между текстовыми блоками. Чтобы этого избежать, отбейте пробел в каждой отдельно взятой ячейке после находящегося текста, либо после адреса ячейки в формуле добавьте пробел вручную, написав « » (пробел в кавычках). Пробел в формуле также отделяется от остальных элементов точкой с запятой.

Как в Экселе соединить текст из двух ячеек в одну с помощью функции «Объединить»

«Объединить» — усовершенствованная вариация функции «Сцеп». Ее отличие в том, что параметры вводятся единожды на этапе написания функции. В частности, разделитель придется указать один раз для содержимого всех ячеек. Для того чтобы воспользоваться функцией «Объединить» нужно выполнить действия:

  1. В пустой ячейке поставьте знак «=» и напишите большими буквами «Объединить».
  2. В скобках после формулы укажите разделитель в кавычках (« » ,«,»,« и »), затем поставьте точку с запятой. Второй параметр формулы – это игнорирование пустых полей. Если они не должны учитываться в формуле, напишите «1», в ряде версий этот параметр обозначается словом «Истина» или «Ложь». Затем перечислите через запятую ячейки, которые нужно объединить, и нажмите «Enter».
Читать еще:  Как сделать таблицу в excel 2020

В итоге формула должна иметь вид: «=ОБЪЕДИНИТЬ(« »;ИСТИНА; «A1»; «A2»; «A3»).

Если в A1 – «Иванов», в А2 – «Иван», в А3 – «Иванович», то после нажатия «Enter» в пустой ячейке появится «Иванов Иван Иванович».

С помощью символа «&»

Четвертый способ объединить ячейки – использовать амперсанд ( «&»). Для этого:

  1. В пустой ячейке наберите «=».
  2. Напишите нужную ячейку, например, А1, затем поставьте знак «&», потом в кавычках введите разделитель. Это может быть пробел или запятая, он также помещается в кавычки, потом введите вторую ячейку, которую нужно объединить. Если их больше двух, пробел выставите после каждой нового поля. Формула должна выглядеть так: «=А1&« »&А2& « »&А3». После нажатия «Enter» содержимое ячеек объединится.

С помощью макроса

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

Чтобы записать макрос:

  1. В разделе «Разработчик» в графе «Код» нажмите на клавишу «Записать макрос».
  2. В появившемся окне выберите имя макроса, чтобы потом было удобнее его искать, присвойте ему сочетание клавиш и место хранения, добавьте описание.
  3. Нажмите «Ок», затем точно и без ошибок (потому что макрос записывает и ошибки тоже) проделайте алгоритм.
  4. Вернитесь во вкладку «Разработчик» и «Код» и нажмите «Остановить запись».

Макрос записывается в виде кода Visual Basic для приложений на одноименном языке программирования, который доступен для редактирования при нажатии комбинации ALT + F11. При ручном редактировании становится доступным правка записанного макроса, однако это требует дополнительных знаний и недоступно для рядового пользователя. Также макросы способны работать в нескольких продуктах Microsoft Office одновременно. Выполняется макрос нажатием присвоенной комбинации либо через окно «Макрос», которое вызывается комбинацией ALT + F8.

Как объединить строки в Excel 2010 и 2013 без потери данных

Это руководство рассказывает о том, как объединить несколько строк в Excel. Узнайте, как можно быстро объединить несколько строк в Excel без потери данных, без каких-либо макросов и надстроек. Только при помощи формул!

Объединение строк в Excel – это одна из наиболее распространённых задач в Excel, которую мы встречаем всюду. Беда в том, что Microsoft Excel не предоставляет сколько-нибудь подходящего для этой задачи инструмента. Например, если Вы попытаетесь совместить две или более строки на листе Excel при помощи команды Merge & Center (Объединить и поместить в центре), которая находится на вкладке Home (Главная) в разделе Alignment (Выравнивание), то получите вот такое предупреждение:

The selection contains multiple data values. Merging into one cell will keep the upper-left most data only. (В объединённой ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.)

Если нажать ОК, в объединённой ячейке останется значение только из верхней левой ячейки, все остальные данные будут потеряны. Поэтому очевидно, что нам нужно использовать другое решение. Далее в этой статье Вы найдёте способы объединить нескольких строк в Excel без потери данных.

Как объединить строки в Excel без потери данных

Задача: Имеется база данных с информацией о клиентах, в которой каждая строка содержит определённые детали, такие как наименование товара, код товара, имя клиента и так далее. Мы хотим объединить все строки, относящиеся к определённому заказу, чтобы получить вот такой результат:

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

Как объединить несколько строк в Excel при помощи формул

Microsoft Excel предоставляет несколько формул, которые помогут Вам объединить данные из разных строк. Проще всего запомнить формулу с функцией CONCATENATE (СЦЕПИТЬ). Вот несколько примеров, как можно сцепить несколько строк в одну:

    Объединить строки и разделить значения запятой:

=CONCATENATE(A1,», «,A2,», «,A3)
=СЦЕПИТЬ(A1;», «;A2;», «;A3)
Объединить строки, оставив пробелы между значениями:

=CONCATENATE(A1,» «,A2,» «,A3)
=СЦЕПИТЬ(A1;» «;A2;» «;A3)
Объединить строки без пробелов между значениями:

Уверен, что Вы уже поняли главное правило построения подобной формулы – необходимо записать все ячейки, которые нужно объединить, через запятую (или через точку с запятой, если у Вас русифицированная версия Excel), и затем вписать между ними в кавычках нужный разделитель; например, “, “ – это запятая с пробелом; ” “ – это просто пробел.

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

    Выделите пустую ячейку на листе и введите в неё формулу. У нас есть 9 строк с данными, поэтому формула получится довольно большая:

=CONCATENATE(A1,», «,A2,», «,A3,», «,A4,», «,A5,», «,A6,», «,A7,», «,A8)
=СЦЕПИТЬ(A1;», «;A2;», «;A3;», «;A4;», «;A5;», «;A6;», «;A7;», «;A8)

  • Скопируйте эту формулу во все ячейки строки, у Вас должно получиться что-то вроде этого:
  • Теперь все данные объединены в одну строку. На самом деле, объединённые строки – это формулы, но Вы всегда можете преобразовать их в значения. Более подробную информацию об этом читайте в статье Как в Excel заменить формулы на значения.
  • Ссылка на основную публикацию
    ВсеИнструменты 220 Вольт
    Adblock
    detector