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

Обновлено: 21.11.2024

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

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

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

Это руководство охватывает:

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

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

  • Использование функции "Текст в столбцы".
  • Использование текстовых функций Excel.
  • Использование быстрой заливки (доступно в 2013 и 2016 годах).

Разделить ячейки в Excel с помощью текста в столбец

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

Вот шаги, чтобы разделить эти имена на имя и фамилию:

Это мгновенно разделит текст ячейки на два разных столбца.

Примечание.

  • Функция «Текст в столбец» разбивает содержимое ячеек на основе разделителя. Хотя это хорошо работает, если вы хотите разделить имя и фамилию, в случае с именем, отчеством и фамилией оно разделит его на три части.
  • Результат использования функции "Текст в столбец" является статическим. Это означает, что если в исходных данных есть какие-либо изменения, вам придется повторить процесс, чтобы получить обновленные результаты.

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

Функции Excel Text отлично подходят, когда вы хотите нарезать и нарезать текстовые строки.

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

Разделение имен, содержащих имя и фамилию

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

Извлечение имени

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

Эта формула определяет первый символ пробела, а затем возвращает весь текст до этого символа пробела:

Эта формула использует функцию ПОИСК для получения позиции пробела. В случае с Брюсом Уэйном космический персонаж находится на 6-й позиции. Затем он извлекает все символы слева от него с помощью функции LEFT.

Извлечение фамилии

Аналогично, чтобы получить фамилию, используйте следующую формулу:

Эта формула использует функцию поиска, чтобы найти положение пробела с помощью функции ПОИСК. Затем он вычитает это число из общей длины имени (которое задается функцией ДЛСТР). Это дает количество символов в фамилии.

Затем эта фамилия извлекается с помощью функции RIGHT.

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

Разделение имен, содержащих имя, отчество и фамилию

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

Формула в таких случаях немного сложна.

Извлечение имени

Чтобы получить имя:

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

Извлечение отчества

Чтобы получить отчество:

Функция MID начинается с первого пробела и извлекает отчество, используя разницу положения первого и второго пробела.

В случае отсутствия отчества функция MID возвращает ошибку. Чтобы избежать ошибки, она заключена в функцию ЕСЛИОШИБКА.

Извлечение фамилии

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

Эта формула проверяет, есть ли отчество или нет (путем подсчета количества пробелов). Если есть только 1 символ пробела, он просто возвращает весь текст справа от символа пробела.

Но если их 2, то он обнаруживает второй пробел и возвращает количество символов после второго пробела.

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

Разделение ячеек в Excel с помощью быстрой заливки

Мгновенная заливка — это новая функция, представленная в Excel 2013.

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

Например, возьмем данные об имени и фамилии:

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

Вот как вы можете извлечь имя из списка с помощью Flash Fill:

Как работает мгновенное заполнение?

Flash Fill ищет шаблоны в наборе данных и копирует шаблон.

Мгновенная заливка — это удивительно умная функция, которая в большинстве случаев работает так, как ожидалось. Но в некоторых случаях это также не работает.

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

Если в таком случае я извлеку отчество, Flash Fill ошибочно вернет фамилию в случае отсутствия имени.

Честно говоря, это все еще хорошее приближение к тенденции. Однако это не то, что я хотел.

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

Вот еще один пример, где Flash Fill работает блестяще.

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

Чтобы быстро получить город, введите название города для первого адреса (в этом примере введите Лондон в ячейке B2) и используйте автозаполнение, чтобы заполнить все ячейки. Теперь используйте Flash Fill, и вы мгновенно получите название города по каждому адресу.

Точно так же вы можете разделить адрес и извлечь любую часть адреса.

Обратите внимание, что для этого потребуется, чтобы адрес был однородным набором данных с одним и тем же разделителем (в данном случае запятой).

Если вы попытаетесь использовать Flash Fill, когда шаблона нет, появится сообщение об ошибке, как показано ниже:

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

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

Разделить ячейку

Используйте следующий прием, чтобы "разделить" ячейку в Excel.

<р>1. Например, задача Б начинается в 13:00 и требует для выполнения 2 часов.

Предположим, задача Б начинается в 13:30. Мы хотели бы разделить ячейку B3 и раскрасить правую половину.

<р>2. Выберите столбец C.

<р>3. Щелкните правой кнопкой мыши и выберите "Вставить".

<р>4. Ширина столбца по умолчанию составляет 64 пикселя. Измените ширину столбцов B и C на 32 пикселя.

<р>5. Выберите ячейку B1 и ячейку C1.

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

<р>7. Повторите шаги 5–6 для ячеек B2 и C2 (а также для ячеек B4 и C4).

<р>8. Измените цвет фона ячейки B3 на «Без заливки».

Примечание: предположим, что задача А заканчивается в 15:30. Используйте трюк, описанный выше, чтобы «разделить» ячейку E2. Загрузите файл Excel и попробуйте.

Текст в столбцы

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

<р>1. Выберите диапазон с полными именами.

<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Текст в столбцы".

Появится следующее диалоговое окно.

<р>3. Выберите «С разделителями» и нажмите «Далее».

<р>4. Снимите все флажки в разделе "Разделители", кроме флажка "Запятая и пробел".

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

Быстрая заливка

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

<р>1. Сначала разделите содержимое одной ячейки на несколько ячеек.

<р>2. Выделите ячейку B1 и нажмите CTRL + E (быстрое заполнение).

<р>3. Выберите ячейку C1 и нажмите CTRL + E.

<р>4. Выберите ячейку D1 и нажмите CTRL + E.

Примечание. Мгновенное заполнение в Excel работает, только если оно распознает шаблон. Загрузите файл Excel и попробуйте. Посетите нашу страницу о Flash Fill, чтобы узнать больше об этом замечательном инструменте Excel.

Формулы для разделения ячеек

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

<р>1. Приведенная ниже формула возвращает имя.

<р>2. Приведенная ниже формула возвращает фамилию.

<р>3. Выберите диапазон B2:C2 и перетащите его вниз.

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

Махеш Маквана

Махеш Маквана
Писатель

Махеш Маквана — технический писатель-фрилансер, специализирующийся на написании практических руководств. Он пишет технические учебники уже более десяти лет. Он написал для некоторых известных технических сайтов, включая MakeUseOf, MakeTechEasier и Online Tech Tips. Подробнее.

У вас есть несколько значений в одной ячейке, которые вы хотите разделить на несколько ячеек? Если это так, в Microsoft Excel есть две простые в использовании опции, которые помогут вам разделить ячейки. Мы покажем вам, как их использовать.

Содержание

Разделение ячеек в Excel с быстрой заливкой

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

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

Сначала щелкните ячейку C2 и вручную введите имя, которое появится в ячейке B2. В данном случае это будет «Махеш».

Нажмите на ячейку C2, чтобы она была выбрана. Затем на ленте Excel вверху щелкните вкладку "Данные".

На вкладке "Данные" в разделе "Инструменты данных" выберите "Быстрая заливка".

И тут же Excel разделит имена из всех записей столбца B и добавит их в столбец C.

Чтобы разделить полученные оценки, щелкните ячейку D2 и вручную введите отметки для ячейки B2. В данном случае это будет «80».

Нажмите на ячейку B2, чтобы она была выбрана. Затем на ленте Excel перейдите на вкладку «Данные». Выберите параметр «Быстрая заливка», и все ваши оценки будут разделены и доступны в столбце D.

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

Разделить ячейки в Excel с текстом на столбцы

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

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

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

Пока ваши ячейки выделены, на ленте Excel вверху нажмите вкладку "Данные".

На вкладке "Данные" в разделе "Инструменты данных" выберите параметр "Текст в столбцы".

Excel откроет окно «Мастер преобразования текста в столбцы». Здесь выберите вариант «С разделителями», а затем нажмите «Далее» внизу.

На следующем экране в разделе «Разделители» выберите символ или символы, которые разделяют значения в ваших ячейках. В нашем примере значения разделены запятой и пробелом, поэтому мы включим параметры «Запятая» и «Пробел».

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

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

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

Вернитесь к окну "Мастер преобразования текста в столбцы" внизу и нажмите "Готово".

И ваши значения в одной ячейке теперь разделены на несколько ячеек.

Когда все ваши данные организованы так, как вы хотите, вы можете получить представление о них с помощью встроенного в Excel инструмента Анализатора данных.

  • › Что означает ИК и как вы его используете?
  • › Matter — это долгожданный стандарт умного дома
  • › Что такое GrapheneOS и как она делает Android более приватным?
  • › 7 основных функций Microsoft Excel для составления бюджета
  • › Сколько оперативной памяти требуется вашему ПК?
  • › 5 вещей, которые вы, вероятно, не знали о GIF-файлах

При работе с данными и электронными таблицами большое значение имеют удобочитаемость и структура.

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

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

Если объем данных очень велик, обычно довольно сложно структурировать читабельность данных.

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

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

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

В новых версиях Excel есть специальная функция, позволяющая делать это с помощью меню Данные.

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

Оглавление

Как разделить один столбец на несколько столбцов

Допустим, у вас есть список имен, который вы хотите разделить на столбцы Имя и Фамилия.

  1. Выберите столбец, который вы хотите разделить
  2. На ленте данных выберите «Текст в столбцы» (в группе Инструменты данных). Откроется мастер преобразования текста в столбцы.
  3. Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран. Если вы не знакомы с термином 'разделитель', это символ, указывающий, как данные в ячейках отделены друг от друга, например, имя и фамилия в каждой ячейке. разделены пробелом. Это означает, что разделителем здесь является символ пробела.
  4. Нажмите "Далее".
  5. Вот настройки, которые необходимо выполнить на втором этапе мастера Tetx to Columns:
    • По умолчанию разделитель Tab установлен. Но мы не хотим этим пользоваться. Мы хотим использовать разделитель Пробел. Поэтому снимите флажок с разделителя Tab и установите флажок Пробел (1).
    • Есть также флажок, который позволяет вам указать, хотите ли вы рассматривать последовательные разделители как один (2). Это означает, что если у вас по ошибке есть два пробела между именами, вы хотите рассматривать их как один пробел?
    • Вы можете увидеть, как ваши данные будут выглядеть после разделения, в области предварительного просмотра данных (3) в нижней части диалогового окна. Обратите внимание, что при выборе пробела мы получаем именно тот результат, который нам нужен.
    • Наконец нажмите «Далее» (4)
  6. Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр Общие, который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки. Оставьте его с выбранным параметром Общие и нажмите "Готово".

    7. Теперь у нас есть два столбца данных: имя в столбце A и фамилия в столбце B.

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

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

    У вас также есть возможность выбрать место назначения разделенных данных.

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

    Само собой разумеется, что количество столбцов, на которые будут разбиты ваши данные, зависит от выбранных вами разделителей.

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

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

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

    Возьмите, например, лист ниже.

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

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

    1. Выберите столбец, который вы хотите разделить
    2. На ленте данных выберите «Текст в столбцы» (в группе «Инструменты данных»). Откроется мастер преобразования текста в столбцы.
    3. Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран.
    4. Нажмите "Далее".
    5. По умолчанию разделитель табуляции отмечен флажком. Снимите все отмеченные разделители и выберите вариант «Другое». В маленьком поле рядом с этой опцией вам нужно указать символ-разделитель, который вы хотите использовать. Если вы хотите указать символ разрыва строки. Нажмите Ctrl + J на ​​клавиатуре. Это покажет крошечную мигающую точку внутри коробки. Это означает, что был вставлен разделитель разрыва строки.

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

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

    1. Нажмите "Далее".
    2. Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр "Общие", который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки.
    3. Здесь мы также хотим, чтобы все столбцы отображались, начиная со столбца B, чтобы новые ячейки не перезаписывали существующий столбец. Рядом с местом назначения мы видим написанную ячейку «$ A $ 2». Мы можем изменить это, выбрав нужную ячейку назначения «$B$2» и нажав «Готово».
    4. Вы можете получить диалоговое окно с вопросом, хотите ли вы заменить данные, которые уже присутствуют в целевых ячейках. Нажмите "ОК".

      5. После этого вы найдете столбцы с B по E, каждый из которых содержит отдельный элемент адреса, который присутствовал в столбце A.

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

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

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

        4. Обратите внимание, что в Excel нет возможности разбить необъединенную ячейку на ячейки меньшего размера (как это возможно в MS Word).

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

        Вышеупомянутые шаги были указаны при условии, что вы используете версии Excel с 2013 по 2019. Однако Microsoft продолжает обновлять свои меню, вкладки и параметры в каждой новой версии, которую она выпускает.

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

        Читайте также: