Как разделить данные из одного столбца на три в 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 на несколько столбцов (с помощью преобразования текста в столбцы, формул и быстрой заливки)

Бен Стоктон

Бен Стоктон
Писатель

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

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

Как использовать текст в столбцах в Excel

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

Эта функция работает с помощью функции «Текст в столбцы», доступ к которой можно получить на вкладке «Данные» на панели ленты Microsoft Excel.

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

Сначала вам нужно выбрать ячейки, содержащие ваши данные (ячейки с A1 по A12 в приведенном выше примере).

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

Откроется окно «Мастер преобразования текста в столбцы», в котором вы сможете начать разделение данных. В параметрах выберите переключатель «С разделителями» и нажмите «Далее», чтобы продолжить.

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

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

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

Когда будете готовы, нажмите "Далее", чтобы продолжить.

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

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

Если вы хотите полностью пропустить столбец, выберите его, а затем выберите параметр «Не импортировать столбец (пропустить)». Нажмите «Готово», чтобы начать преобразование.

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

Как использовать мгновенное заполнение в Excel

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

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

Чтобы использовать функцию «Быстрое заполнение», начните с ввода заголовков столбцов в строке 1. В нашем примере «Идентификатор сотрудника» будет помещен в ячейку B1, «Имя» — в ячейку C1 и т. д.

Для каждого столбца выберите строку заголовка. Начните с B1 (в этом примере заголовок «Идентификатор сотрудника»), а затем в разделе «Инструменты данных» на вкладке «Данные» нажмите кнопку «Быстрое заполнение».

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

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

В нашем примере это будет первый пример данных в ячейке B2 ("101") после ячейки заголовка в B1 ("Идентификатор сотрудника").

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

В приведенном выше примере длинный столбец (столбец A) был разделен на шесть новых столбцов (от B до G).

Поскольку расположение строк с 1 по 12 одинаковое, функция "Быстрое заполнение" позволяет копировать и разделять данные, используя строку заголовка и первый бит данных.

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

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

Практическая рабочая тетрадь

Загрузите следующую рабочую тетрадь и выполните упражнение.

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

1. Функция Excel «Текст в столбцы» для разделения одного столбца на несколько столбцов

Функция Excel «Текст в столбцы» — это встроенная функция. Предположим, у нас есть набор данных (B4:D9) продуктов Microsoft. Мы собираемся разделить информацию одного столбца (B5:B9) на несколько столбцов.

ШАГИ:

  • Сначала выберите диапазон столбцов (B5:B9) для разделения.
  • Далее перейдите на вкладку "Данные".
  • Нажмите «Текст в столбцы» в разделе «Инструменты данных».

  • Откроется окно первого шага мастера.
  • Теперь выберите термин "с разделителями" и нажмите "Далее".

  • Мы видим окно Мастера Шаг 2. Установите флажок "Пробел".
  • Посмотрите, как выглядит результат в окне предварительного просмотра данных.
  • Затем нажмите "Далее".

  • Открылось окно 3-го шага мастера. Выберите «Общие» в параметре «Формат данных столбца».
  • После этого выбираем место, где мы хотим увидеть результат в поле Destination.
  • Проверьте, отображается ли результат прямо в окне предварительного просмотра данных.
  • Нажмите "Готово".

  • Наконец, мы видим, что данные одного столбца разбиты на несколько столбцов.

2. Разделение нескольких строк одного столбца на несколько столбцов в Excel

С помощью функции «Текст в столбцы» мы можем разделить несколько строк одного столбца на несколько столбцов в Excel. Допустим, у нас есть набор данных (B4:D9) продуктов Microsoft с годами в одном столбце. Мы собираемся разделить их.

ШАГИ:

  • Выберите диапазон столбцов (B5:B9) для разделения.
  • Затем перейдите на вкладку "Данные" > "Инструменты данных" > "Текст в столбцы".

  • Откроется окно мастера, шаг 1.
  • Выберите термин «с разделителями» и нажмите «Далее».

  • Теперь в окне мастера, шаг 2, установите флажок «Другое» и введите «,».
  • Посмотрите, как выглядит результат в окне предварительного просмотра данных.
  • Нажмите "Далее".

  • В окне «Шаг 3» мастера выберите «Общие» в параметре «Формат данных столбца».
  • Затем выберите место, где мы хотим увидеть результат, в поле "Назначение".
  • Проверьте, отображается ли результат прямо в окне предварительного просмотра данных.
  • После этого нажмите "Готово".

  • Появится окно подтверждения. Нажмите "ОК".

3. Разделить объединенную ячейку как один столбец на несколько столбцов в Excel

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

ШАГИ:

  • Сначала выделите все объединенные ячейки одного столбца.
  • Перейдите на вкладку "Главная".
  • Нажмите раскрывающееся меню "Объединить и центрировать" в разделе "Выравнивание".
  • Теперь выберите "Разъединить ячейки".

  • Мы видим, что ячейки не объединены и разделены на разные столбцы.

Похожие чтения:

  • Формула Excel для разделения одного столбца на несколько столбцов (4 примера)
  • Извлечение текста перед символом в Excel (4 быстрых способа)
  • Как преобразовать число в дату в Excel (6 простых способов)

4. Функция мгновенного заполнения Excel для разделения одного столбца на несколько столбцов

В Excel есть несколько специальных умных инструментов. «Мгновенная заливка» — одна из них. Мгновенное заполнение копирует образец ячейки и дает результат, аналогичный этой ячейке. Здесь у нас есть набор данных продуктов Microsoft с годами. Мы собираемся разделить данные этого столбца (B4:B9) на несколько столбцов.

ШАГИ:

  • Выберите ячейку C5 и запишите в ней название продукта «Microsoft Excel».
  • Затем выберите ячейку D5 и запишите год «2018».

  • Теперь выберите ячейку C5 и используйте инструмент "Маркер заполнения", чтобы автоматически заполнить пустые ячейки.
  • Далее рядом с параметром "Автозаполнение" нажмите "Быстрое заполнение".

  • Проделайте то же самое для следующего столбца, и мы увидим результат.

5. Разделить один столбец на несколько столбцов с помощью VBA

Код Microsoft Excel Visual Basic for Application помогает нам разделить один столбец на несколько столбцов. Допустим, у нас есть набор данных (B4:B14) продуктов Microsoft Excel за несколько лет. Мы собираемся разделить этот столбец на два столбца D4 и E4.

ШАГИ:

  • Сначала выберите все значения из столбца.
  • Затем перейдите на рабочий лист с вкладки листа и щелкните его правой кнопкой мыши.
  • Выберите «Просмотреть код».

  • Откроется окно модуля VBA.
  • Введите код:
  • Затем нажмите «Выполнить».

  • В окне подтверждения выберите «Выполнить».

  • После этого выберите диапазон ввода и нажмите "ОК".

  • Запишите количество строк, которые мы хотим видеть в новом столбце, и нажмите "ОК".

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

  • В итоге мы видим, что все значения одного столбца разбиваются на два.

6. Формула Excel INDEX для разделения одного столбца на несколько столбцов

Функция ИНДЕКС Excel вместе с функцией СТРОКИ используется для разделения одного столбца. Предположим, у нас есть набор данных (B4:B14). Мы собираемся разделить эти значения набора данных на два столбца (столбец 1 и столбец 2).

ШАГИ:

  • Сначала выберите ячейку D5.
  • Теперь введите формулу:

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

  • Затем выберите ячейку E5.
  • Запишите формулу:

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

7. Функции Excel LEFT & RIGHT для разделения одного столбца на несколько столбцов

Функция Excel LEFT возвращает крайние левые символы текстовой строки, тогда как функция RIGHT в Excel помогает нам извлечь последние символы из текстовой строки. Обе они являются текстовыми функциями в Excel. Здесь у нас есть набор данных (B4:B9) в одном столбце. Мы собираемся использовать текстовые функции для разделения значений из одного столбца.

ШАГИ:

➥ Разбивка формулы

➤ ПОИСК(" ",B5)

Функция ПОИСК вернет позицию пробела.

➤ ВЛЕВО(B5,ПОИСК(" ",B5)-1)

Это вернет значение.

  • Затем нажмите Enter и используйте инструмент "Маркер заполнения", чтобы автоматически заполнить ячейки.

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

➥ Разбивка формулы

➤ ПОИСК(" ",B5)

Функция ПОИСК вернет позицию пробела.

➤ ДЛСТР(B5)

Функция ДЛСТР вернет общее количество символов.

➤ ВПРАВО(B5,ДЛСТР(B5)-ПОИСК(" ",B5))

Это вернет значение.

Заключение

Это самый быстрый способ разделить один столбец на несколько столбцов в Excel. Добавлена ​​рабочая тетрадь. Давай, попробуй. Не стесняйтесь спрашивать что-либо или предлагать какие-либо новые методы.

Похожие материалы

  • Excel 3D SUMIF для нескольких листов
  • Как объединить Excel SUMIF и VLOOKUP на нескольких листах
  • Подсчет определенных символов в ячейке Excel (4 быстрых способа)

Нурайда Кашмин

Привет всем! Я Нурайда. Сердечно приветствую вас в моем профиле. Здесь я буду размещать статьи, связанные с Microsoft Excel. Обладает сильным образованием в области машиностроения благодаря экспериментальному обучению в Университете инженерии и технологий Раджшахи. Владеет Microsoft Word, творческим письмом, Microsoft Excel, управлением проектами, поддержкой организации различных мероприятий, отчетностью, мониторингом и документацией, онлайн-адвокацией и управлением мероприятиями, связанными с SAP и молодежными лидерами.

ВПР диапазон дат и возвращаемое значение в Excel (4 подходящих метода)

Скрыть или показать столбцы на основе выбора раскрывающегося списка в Excel

Как использовать формулу ЕСЛИ для диапазона дат в Excel (6 методов)

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

Оставить ответ Отменить ответ

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

ExcelDemy — это место, где вы можете изучить Excel, анализ данных и другие программы, связанные с Office. Мы даем советы, как направлять, а также предоставляем решения Excel для ваших бизнес-задач.

Excel: как анализировать данные (разделить столбец на несколько)

В Excel (2016, 2013, 2010) можно разбивать данные из одного столбца на два или более столбцов. И сделать это можно за несколько простых шагов. Предположим, что столбец A содержит «Фамилия, Имя». Выполните следующие действия, чтобы разделить данные из столбца A на столбец «Фамилия» и столбец «Имя». Не нужно вырезать и вставлять!

Откройте электронную таблицу Excel, содержащую данные, которые вы хотите разделить, а затем:

  1. Выделите столбец, содержащий объединенные данные (например, фамилию, имя), нажав букву непосредственно над столбцом.
  2. Перейдите на вкладку "Данные" на ленте, затем найдите группу "Инструменты данных" и нажмите "Текст в столбцы". Появится «Мастер преобразования текста в столбцы».
  3. На шаге 1 мастера выберите «С разделителями» > Нажмите [Далее].
  4. Разделитель – это символ или пробел, разделяющий данные, которые вы хотите разделить. Например, если в вашем столбце написано «Смит, Джон», вы должны выбрать «Запятая» в качестве разделителя. Выберите разделитель в ваших данных.
  5. Установите флажок "Рассматривать последовательные разделители как один".
  6. Нажмите [Далее].
  7. В разделе "Формат данных столбца" выберите "Общие".
  8. Нажмите красную стрелку/значок электронной таблицы справа от текстового поля "Назначение".
  9. Выделите столбцы, в которые вы хотите включить разделенные данные, щелкнув буквы непосредственно над столбцами (вы можете выбрать столбцы из любого места в электронной таблице). Или вручную нажмите и перетащите, чтобы выбрать продажи, для которых вы хотите использовать разделенные данные.
  10. Нажмите красную стрелку/значок электронной таблицы еще раз, чтобы вернуться к мастеру.
  11. Нажмите [Готово].

Примечание:
Если данные, которые вы хотите разделить, НЕ содержат разделителя (тире, запятая, табуляция и т. д.) для разделения данных, выберите «Фиксированная ширина» на первом шаге «Преобразовать текст в Колонка" Мастер. Этот параметр позволяет вручную создавать деления в данных путем перетаскивания линии разрыва.

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

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