Excel

Объединение данных в ячеек excel: Объединение текста из двух или нескольких ячеек в одну

Содержание

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

Данные из нескольких ячеек можно объединить в одну с помощью символа "амперсанд" (&) или функции СЦЕП.

Объединение данных с помощью символа "амперсанд" (&)

  1. Выделите ячейку, в которую вы хотите вставить объединенные данные.

  2. Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.

  3. Введите символ & и пробел, заключенный в кавычки.

  4. Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&" "&B2.

Объединение данных с помощью функции СЦЕП

  1. Выделите ячейку, в которую вы хотите вставить объединенные данные.

  2. Введите выражение = СЦЕП(.

  3. Выберите первую ячейку, которую нужно объединить.

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

  4. Введите закрывающую скобку в конце формулы и нажмите клавишу ВВОД. Пример формулы: =CONCAT(A2, " Семья").

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую

Как сцепить ячейки в Excel при помощи различных функций и операторов

В этом руководстве вы узнаете о различных способах соединения текстовых строк, ячеек, диапазонов, столбцов и строк в Excel с использованием функции СЦЕПИТЬ, СЦЕП, ОБЪЕДИНИТЬ и оператора «&»

.

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

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

Что значит и как «сцепить» в Excel?

По сути, существует два способа объединения данных в таблицах:

  • соединить ячейки,
  • соединить их содержимое.

Когда вы объединяете ячейки, вы «физически» объединяете две или более клеточки в одну. В результате у вас есть одно большое поле, которое отображается в нескольких строчках и / или столбиках на вашем рабочем листе.

Когда вы объединяете значения ячеек, то вы работаете только с их

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

Объединение ячеек в Excel является темой отдельной статьи. А в этом руководстве мы рассмотрим два основных способа слияния их содержимого - с помощью функции СЦЕПИТЬ (CONCATENATE в английской версии), ОБЪЕДИНИТЬ ( или TEXTJOIN) и СЦЕП ( или CONCAT), а также оператора &.

Команда СЦЕПИТЬ предназначена для объединения различных фрагментов текста или объединения данных из нескольких ячеек в одну.

Синтаксис её выглядит следующим образом:

СЦЕПИТЬ (текст1, [текст2],…)

Где текст - текстовая строка, ссылка или какой-то результат, полученный при помощи формулы.

Замечание. В Excel 2016, 2019, Excel Online и Excel Mobile СЦЕПИТЬ заменяется функцией СЦЕП (CONCAT в английской версии), которая имеет точно такой же синтаксис. Хотя функция СЦЕПИТЬ сохраняется для обратной совместимости, рекомендуется использовать вместо нее СЦЕП, поскольку Microsoft не дает никаких обещаний, что старая функция будет доступна в будущих версиях программы.

Как сцепить несколько ячеек.

Простейшее выражение для конкатенации содержимого A1 и B1 выглядит следующим образом:

=СЦЕПИТЬ(A1; B1)

Обратите внимание, что они будут соединены друг с другом без разделителя, как в строке 3 на рисунке ниже. Думаю, такой вариант нас не устроит.

Чтобы разделить их пробелом, введите “ “ во втором аргументе, как в строке 4.

=СЦЕПИТЬ(A4, " ", B4)

Чтобы разделить объединенные значения с другими разделителями, такими как запятая, пробел или косая черта, смотрите здесь.

Объединение текстовой строки и ссылки.

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

=СЦЕПИТЬ(A3;B3;" выполнено")

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

Естественно, вы можете добавить текст в начале или в середине формулы СЦЕПИТЬ:

=СЦЕПИТЬ("Смотри ";A4; " "; B4)

Между объединенными элементами добавляется пробел (" "), поэтому результат отображается как "задание 2", а не "задание2".

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

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

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

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


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

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


Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (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) и скопируем туда текст такого простого макроса:

Sub MergeToOneCell()
    Const sDELIM As String = " "     'символ-разделитель
    Dim rCell As Range
    Dim sMergeStr As String
    If TypeName(Selection) <> "Range" Then Exit Sub   'если выделены не ячейки - выходим
    With Selection
        For Each rCell In .Cells
            sMergeStr = sMergeStr & sDELIM & rCell.Text  'собираем текст из ячеек
        Next rCell
        Application.DisplayAlerts = False   'отключаем стандартное предупреждение о потере текста
        .Merge Across:=False                'объединяем ячейки
        Application.DisplayAlerts = True
        .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))    'добавляем к объед.ячейке суммарный текст
    End With
End Sub

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

Ссылки по теме

 

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

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

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

К сожалению, в Microsoft Excel нет встроенного инструмента для выполнения таких задач. Конечно, есть команды Merge Cells (Объединить ячейки), Merge & Center (Объединить и поместить в центре) и прочие, но если Вы выберите 2 смежные ячейки с целью объединить их, как показано на снимке экрана ниже:

Вам будет показано сообщение с предупреждением:

  • в Excel 2013: Merging cells only keeps the upper-left cell value, and discards the other values (В объединённой ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны).
  • в Excel 2010 и 2007: The selection contains multiple data values. Merging into one cell will keep the upper-left most data only (Выделенная область содержит несколько значений данных. Объединение ячеек приведёт к потере всех значений, кроме левого верхнего).

Далее в этой статье Вы найдёте 2 способа, как объединить данные из нескольких столбцов в один без потери данных и, не используя макрос VBA.

Объединяем два столбца при помощи формул

Допустим, у Вас есть таблица с данными о клиентах, и Вы хотите объединить столбцы с именем (First Name) и фамилией (Last Name) в один столбец с полным именем (Full Name).

  1. Добавьте в таблицу новый столбец. Для этого наведите указатель мыши на заголовок столбца (в нашем случае это столбец D), кликните по нему правой кнопкой мыши и в контекстном меню выберите пункт Insert (Вставить). Давайте назовём новый столбец Full Name.
  2. В ячейке D2 запишите вот такую формулу:

    =CONCATENATE(B2," ",C2)
    =СЦЕПИТЬ(B2;" ";C2)

    B2 и C2 – это адреса ячеек из столбцов First Name и Last Name. Обратите внимание, что в формуле между ними стоит символ пробела, заключенный в кавычки. Это разделитель, который будет вставлен между соединёнными частями имени. В качестве разделителя Вы можете использовать любой другой символ, например, запятую.

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

  3. Скопируйте эту формулу во все остальные ячейки столбца Full Name. Пошаговую инструкцию как это делается Вы можете найти в статье Как вставить одинаковую формулу сразу во все выбранные ячейки.
  4. Итак, мы объединили имена из двух столбцов в один, но это по-прежнему формула. Если мы удалим столбец First name или Last name, то связанные с ними данные в столбце Full Name также будут потеряны.
  5. Теперь нам нужно превратить формулу в значение, чтобы мы могли удалить ненужные столбцы с нашего листа Excel. Выделите все ячейки с данными в объединённом столбце (выделите первую ячейку в столбце Full Name и затем нажмите Ctrl+Shift+Стрелка вниз.Скопируйте содержимое в буфер обмена (Ctrl+C или Ctrl+Ins – что предпочитаете), затем кликните правой кнопкой мыши по любой ячейке в том же столбце (Full Name) и в контекстном меню выберите Paste Special (Специальная вставка). Отметьте вариант Values (Значения) и нажмите ОК.
  6. Удалите столбцы First Name и Last Name, они нам больше не нужны. Для этого:
    • кликните по заголовку столбца B, затем, нажав и удерживая клавишу Ctrl, кликните по заголовку столбца C.
    • (другой способ) выберите любую ячейку в столбце B, нажмите Ctrl+Space (Пробел), чтобы выделить весь столбец B, затем нажмите Ctrl+Shift+Стрелка вправо, чтобы добавить к выделению столбец C.

После этого кликните правой кнопкой мыши по любому из выделенных столбцов и в контекстном меню нажмите Delete (Удалить).

Отлично, мы объединили имена из двух столбцов в один! Хоть это и потребовало немало усилий и времени.

Объединяем столбцы при помощи Блокнота

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

Вот пример: мы хотим объединить все те же два столбца с именем и фамилией в один.

  1. Выделяем оба столбца, которые необходимо объединить. Для этого выделяем ячейку B1, нажимаем Shift+Стрелка вправо, чтобы добавить к выделению ячейку C1. Затем нажимаем Ctrl+Shift+Стрелка вниз, чтобы выделить все ячейки с данными в двух столбцах.
  2. Скопируйте данные в буфер обмена (Ctrl+C или Ctrl+Ins – что предпочитаете).
  3. Запустите Блокнот: Start (Пуск) > All Programs (Все программы) > Accessories (Стандартные) > Notepad (Блокнот)
  4. Вставьте данные из буфера обмена в Блокнот (нажмите Ctrl+V или Shift+Ins)
  5. Скопируйте в буфер обмена символ табуляции. Для этого нажмите клавишу Tab в Блокноте, затем сочетание Ctrl+Shift+Стрелка влево, а затем Ctrl+X.
  6. Замените в Блокноте символы Tab на другие символы, которые Вы хотите использовать в качестве разделителей.Для этого нажмите Ctrl+H, чтобы открыть диалоговое окно Replace (Заменить). В поле Find what (Что) вставьте из буфера обмена символ табуляции. В поле Replace with (Чем) введите Ваш разделитель, например, пробел, запятую и т.д. Нажмите кнопку Replace All (Заменить все), а затем Cancel (Отмена), чтобы закрыть диалоговое окно.
  7. Нажмите Ctrl+A, чтобы выделить весь текст в Блокноте, затем Ctrl+C, чтобы скопировать его в буфер обмена.
  8. Вернитесь к листу Excel (нажмите Alt+Tab), выделите только ячейку B1 и вставьте текст из буфера обмена в таблицу.
  9. Переименуйте столбец B в Full Name и удалите столбец Last name.

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

Оцените качество статьи. Нам важно ваше мнение:

Как объединить строки в 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)

  • Объединить строки без пробелов между значениями:

    =CONCATENATE(A1,A2,A3)
    =СЦЕПИТЬ(A1;A2;A3)

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

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Перенос текста и объединение ячеек в Excel

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

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

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

Перенос текста в Excel

В следующем примере мы применим перенос текста по строкам к столбцу D.

  1. Выделите ячейки, текст в которых должен отображаться на нескольких строках. В нашем примере мы выделим ячейки в столбце D.
  2. Выберите команду Перенести текст на вкладке Главная.
  3. Текст будет перенесен по строкам.

Нажмите команду Перенести текст еще раз, чтобы отменить перенос.

Объединение ячеек в Excel

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

В примере ниже мы объединим диапазон A1:E1, чтобы создать заголовок для нашего листа.

  1. Выделите ячейки, которые требуется объединить.
  2. Нажмите команду Объединить и поместить в центре на вкладке Главная.
  3. Выделенные ячейки будут объединены в одну, а текст разместится в центре.

Кнопка Объединить и поместить в центре действует как переключатель, т.е. повторное нажатие на нее отменит объединение. Удаленные данные при этом не восстановятся

Дополнительные параметры объединения ячеек в Excel

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

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

Оцените качество статьи. Нам важно ваше мнение:

Как быстро объединить ячейки в Excel

Посмотреть видео - Объединить ячейки в Excel (с помощью формул)

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

Ваши данные могут находиться в соседних ячейках (строках / столбцах), или они могут находиться далеко на том же листе или даже на другом листе.

Как объединить ячейки в Excel

В этом руководстве вы узнаете, как объединить ячейки в Excel в различных сценариях:

  • Как объединить ячейки без пробела / разделителя между ними.
  • Как объединить ячейки с пробелом / разделителем между ними.
  • Как объединить ячейки с разрывом строки между ними.
  • Как сочетать ячейки с текстом и числами.

Как объединить ячейки без пробела / разделителя

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

Вы можете легко объединить ячейки в столбцах A и B, чтобы получить строку, такую ​​как A11, A12 и т. Д.

Вот как это сделать это:

  • Введите следующую формулу в ячейку, в которой вы хотите объединить строку:
     = A2 & B2 
  • Скопируйте и вставьте это во все ячейки.

Это даст вам что-то, как показано ниже:

Вы также можете сделать то же самое, используя функцию CONCATENATE вместо использования амперсанда (&).Приведенная ниже формула даст тот же результат:

 = СЦЕПИТЬ (A2, B2) 

Как объединить ячейки с пробелом / разделителем между

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

Предположим, у нас есть набор данных, как показано ниже:

Вот шаги, чтобы объединить имя и фамилию с пробелом между ними:

  • Введите следующую формулу в ячейку:
     = A2 & "" & B2 
  • Скопируйте и вставьте это во все ячейки.

Это объединит имя и фамилию с пробелом между ними.

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

Как объединить ячейки с разрывами строк между ними

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

Предположим, у вас есть набор данных, показанный ниже:

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

Для объединения этих ячеек можно использовать функцию СЦЕПИТЬ или знак & (амперсанд).

Однако простое объединение этих ячеек даст вам что-то, как показано ниже:

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

Здесь необходимо, чтобы каждый элемент адреса располагался в отдельной строке одной и той же ячейки. Вы можете добиться этого, используя функцию CHAR (10) вместе со знаком &.

CHAR (10) - это перевод строки в Windows, что означает, что он заставляет все после него перейти на новую строку.

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

 = A2 & CHAR (10) & B2 & CHAR (10) & C2 & CHAR (10) & D2 & CHAR (10) & E2 

В этой формуле используется CHAR (10) ) между каждой ссылкой на ячейку и вставляет разрыв строки после каждой ячейки. Получив результат, примените перенос текста к ячейкам, в которых есть результаты, и вы получите что-то, как показано ниже:

Как объединить ячейки с текстом и числами

Вы также можете объединить ячейки, содержащие разные типы данные.Например, вы можете комбинировать ячейки, содержащие текст и числа.

Давайте сначала рассмотрим простой пример.

Предположим, у вас есть набор данных, как показано ниже:

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

 = A2 & "-" & B2 

Здесь я использовал тире в качестве разделителя.

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

 = A2 & "регион имеет" & B2 & "офисы" 

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

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

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

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

Вот формула, которую я буду использовать:

 = A2 & "объем продаж в регионе" & B2 

Вот как выглядят результаты:

Видите ли вы здесь проблему? Посмотрите внимательно на формат продажной стоимости в результате.

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

Вот как это исправить.Используйте приведенную ниже формулу:

 = A2 & "продажи в регионе" & ТЕКСТ (B2, "$ ###, 0.0") 

В приведенной выше формуле вместо использования B2 мы использовали функцию ТЕКСТ. Функция ТЕКСТ позволяет отображать число в указанном формате и в виде текста. В приведенном выше случае используется формат $ ###, 0,0. Этот формат указывает Excel показывать число со знаком доллара, разделителем тысяч и одной десятичной точкой.

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

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

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

Вот формула, которая исправит это:

 = A2 & "родился" & ТЕКСТ (B2, "дд ммм ггг") 

И снова здесь мы использовали функцию ТЕКСТ и указали формат, в котором мы хотите, чтобы Дата рождения отображалась в результате.

Вот как работает этот формат даты:

  • дд - Показывает номер дня даты. (попробуйте использовать ddd и посмотрите, что произойдет).
  • ммм - показывает трехбуквенный код месяца.
  • ггг - показывает номер года.

Круто… Не правда ли?

Сообщите мне свои мысли в разделе комментариев.

Вам также могут понравиться следующие руководства по Excel:

Как разделить ячейки в Excel

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

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

Как разделить ячейки в Excel

Отменить слияние ячеек в Excel очень просто. Вот что вы делаете:

  1. Выберите одну или несколько ячеек, которые нужно разъединить.
  2. На вкладке Home в группе Alignment щелкните Merge & Center .

Или щелкните стрелку раскрывающегося списка рядом с кнопкой Объединить и центрировать и выберите Разъединить ячейки .

В любом случае Excel отключит все объединенные ячейки в выделенной области.Содержимое каждой объединенной ячейки будет помещено в верхнюю левую ячейку, остальные объединенные ячейки будут пустыми:

Как разделить все объединенные ячейки на листе

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

Чтобы разделить все ячейки на листе, выполните следующие действия:

  1. Выберите весь рабочий лист. Для этого либо щелкните маленький треугольник в верхнем левом углу листа, либо нажмите сочетание клавиш Ctrl + A.
  2. Выделив все ячейки на листе, посмотрите на кнопку Merge & Center :
    • Если он выделен, щелкните его, чтобы разделить все объединенные ячейки на листе.
    • Если он не выделен, на листе нет объединенных ячеек.

Как разделить ячейки и скопировать исходное значение в каждую ячейку без объединения

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

Чтобы разделить ячейки и заполнить повторяющимися значениями, выполните следующие действия:

  1. Выберите свою таблицу (или только столбцы, в которых объединены ячейки) и нажмите кнопку Объединить и центрировать на вкладке Home .Это разделит все объединенные ячейки, но только не объединенные ячейки в верхнем левом углу будут заполнены данными.
  2. Снова выберите всю таблицу, перейдите на вкладку Home > Редактирование группы , щелкните Найти и выбрать , а затем щелкните Перейти к специальному…
  3. В диалоговом окне Перейти к специальному отметьте галочкой опцию Пробелы и нажмите ОК :
  4. Выделив все пустые ячейки, введите знак равенства (=) и нажмите клавишу со стрелкой вверх.Это создаст простую формулу, которая заполняет первую пустую ячейку значением из приведенной выше ячейки:
  5. Поскольку вы хотите заполнить все не объединенные ячейки, которые в настоящее время пусты, нажмите Ctrl + Enter, чтобы ввести формулу во все выбранные ячейки.

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

Как разделить содержимое объединенной ячейки на несколько ячеек

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

  • Текст в столбцы - позволяет разделять текстовые строки по заданному разделителю, например запятой, точкой с запятой или пробелом, а также разделять подстроки фиксированной длины.
  • Flash Fill - быстрый способ разбить относительно простые текстовые строки одного образца.
  • Формулы для разделения текстовых строк и чисел - лучше всего использовать, когда вам нужно индивидуальное решение для определенного набора данных.
  • Инструмент «Разделить текст» - инструмент, который можно попробовать, когда все вышеперечисленные методы не дали результата.Он может разбивать ячейки по любому указанному символу или нескольким различным символам, по строке и маске (шаблон, который вы указываете).

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

Как найти объединенные ячейки в Excel

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

Итак, как найти объединенные ячейки на листе? Просто помните, что объединение ячеек связано с выравниванием, а выравнивание - это часть форматирования, и Excel Find может выполнять поиск по формату 🙂 Вот как:

  1. Нажмите Ctrl + F, чтобы открыть диалоговое окно Найти . Или перейдите на вкладку Home > Editing group и нажмите Find & Select > Find .
  2. В диалоговом окне «Найти и заменить » нажмите кнопку «Параметры », а затем нажмите «Формат
  3. ».
  4. Перейдите на вкладку Выравнивание , установите флажок Объединить ячейки под Текстовый элемент управления и нажмите ОК .
  5. А теперь щелкните либо:
    • Найти следующий , чтобы перейти к следующей объединенной ячейке.
    • Найти все , чтобы получить список всех объединенных ячеек.

Когда вы щелкнете по одному из найденных элементов, Excel выберет соответствующую объединенную ячейку на вашем листе:

Наконечник. Если вам просто интересно, есть ли какие-либо объединенные ячейки в определенном диапазоне, выберите этот диапазон и взгляните на кнопку Merge & Center . Если кнопка выделена, это означает, что в выбранном диапазоне есть хотя бы одна объединенная ячейка.

Вот как вы объединяете ячейки в Excel. Благодарю вас за чтение и надеюсь снова увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Объединение и разделение ячеек в Excel 2007

  1. Программное обеспечение
  2. Microsoft Office
  3. Excel
  4. Объединение и разделение ячеек в Excel 2007

Автор Грег Харви

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

Разделить можно только ячейку, которая была ранее объединена.

Объединение и центрирование ячеек

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

  1. Выберите диапазон ячеек, которые вы хотите объединить и центрировать.

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

    Заголовок рабочего листа перед объединением и центрированием.

  2. Нажмите кнопку «Объединить и центрировать» в группе «Выравнивание» на вкладке «Главная».

    Ячейки объединяются в одну, а текст (если есть) центрируется в объединенной ячейке.

    Заголовок рабочего листа после центрирования его по столбцам от A до E.

  3. (Необязательно) При желании измените выравнивание объединенной ячейки.

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

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

Разделение объединенной ячейки

Если вам нужно разделить ячейку, которую вы объединили с помощью кнопки «Объединить и центрировать», выполните следующие действия:

  1. Выберите объединенную ячейку.

    Кнопка «Объединить и центрировать» появится выбранной в группе «Выравнивание».

  2. Нажмите кнопку «Объединить и центрировать» в группе «Выравнивание».

    Объединенная ячейка снова возвращается к диапазону ячеек, и любой текст, содержащийся в объединенной ячейке, отображается в верхней левой ячейке диапазона.

Об авторе книги
Грег Харви является автором множества компьютерных книг, последней из которых является Excel Workbook для чайников и Roxio Easy Media Creator 8 для чайников, и самой популярной из них Excel 2003 для чайников и Excel 2003 All-In -Одна настольная справка для чайников .Он начал обучать бизнес-пользователей тому, как использовать персональные компьютеры IBM и сопутствующее компьютерное программное обеспечение, в тяжелые времена DOS, WordStar и Lotus 1-2-3 в середине 80-х годов прошлого века. Поработав в нескольких независимых учебных фирмах, Грег продолжил обучение на семестровых курсах по работе с электронными таблицами и программным обеспечением для управления базами данных в университете Golden Gate в Сан-Франциско.
Его любовь к обучению переросла в такую ​​же любовь к письму. Для чайников книги, конечно, его самые любимые книги, потому что они позволяют ему писать для своей любимой аудитории: новичков.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *