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

Обновлено: 04.07.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 ошибочно вернет фамилию в случае отсутствия имени.

< /p>

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

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

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

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

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

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

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

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

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

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

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

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

Как разделить ячейку Excel на столбцы

Есть два способа разделить ячейку Excel: с помощью разделителей или с использованием фиксированной ширины. Эти два параметра имеют небольшие различия.

Столбцы с разделителями и фиксированной ширины

При выборе параметра "Разделители" ваши данные будут разделены с помощью запятых, пробелов, точек, тире или других знаков форматирования, которые могут быть в одной ячейке. Например, если вы написали чье-то имя как "Смит, Джон", Смит и Джон будут разделены на отдельные столбцы.
Параметр «Фиксированная ширина» позволяет вручную выбрать, где Excel будет разделять данные.Например, если у вас есть список имен, вы можете указать Excel разделить данные так, чтобы в одном столбце была первая буква имени каждого, а во втором — остальная часть имени.

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

<р>1. В Excel выберите ячейку, группу ячеек или весь столбец с текстом, который вы хотите разделить. Он должен содержать две части данных, разделенные «разделителем». Обычно это запятая, точка, тире или пробел.

<р>2. Перейдите на вкладку "Данные", а затем нажмите "Текст в столбцы".

Выберите диапазон ячеек, которые вы хотите разделить, и выберите «Текст в столбцы» на вкладке «Данные». Дэйв Джонсон/Инсайдер

<р>3. В окне «Мастер преобразования текста в столбцы» выберите «С разделителями». Если вы хотите, чтобы столбцы имели фиксированную ширину, см. следующий раздел.

<р>4. Нажмите "Далее".

<р>5. Выберите один или несколько разделителей — другими словами, то, что Excel должен использовать, чтобы знать, как разделить столбцы. В таких случаях, как имена или местоположения, это часто будет запятая. Некоторые виды данных, импортированных из другой электронной таблицы или базы данных, могут быть разделены табуляцией или пробелом. Вы также можете выбрать более одного разделителя. Нажмите "Далее".

Наиболее распространенным разделителем является запятая, но используйте тот (или те), которые подходят для ваших данных. Дэйв Джонсон/Инсайдер

<р>6. Выберите способ форматирования столбца и, если хотите, используйте параметр «Назначение», чтобы указать, где в электронной таблице появится новый столбец. Если вы не выберете этот параметр, новые разделенные столбцы перезапишут текущие ячейки.

<р>7. Нажмите "Готово".

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

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

<р>2. Перейдите на вкладку "Данные", а затем нажмите "Текст в столбцы".

<р>3. В окне «Мастер преобразования текста в столбцы» выберите «Фиксированная ширина» и нажмите «Далее».

<р>4. Нажмите на линейку в верхней части «Предварительного просмотра данных», чтобы указать, где вы хотите разбить текст на несколько столбцов. После того, как вы добавите разрыв, вы можете перетащить его, чтобы изменить его положение. Вы также можете добавить несколько разрывов, чтобы создать более двух столбцов. Если вы добавляете ненужный разрыв, дважды щелкните его, чтобы удалить. Когда закончите, нажмите "Далее".

<р>5. Выберите способ форматирования столбца и, если хотите, используйте параметр «Назначение», чтобы указать, где в электронной таблице появится новый столбец. Если вы не выберете этот параметр, новые разделенные столбцы перезапишут текущие ячейки.

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

Зачем разделять данные? Часто возникает необходимость отсортировать, отфильтровать или выполнить математические вычисления с данными, которые в данный момент не находятся в отдельном столбце. Классический пример – наличие имени и фамилии в столбце листа и их размещение в отдельных столбцах.

Правила успешного разделения данных столбцов Excel

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

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

С. Некоторые ячейки могут не разделяться правильно. Например, при разделении имен с помощью пробела в качестве разделителя записи с отчеством, такие как Sue Ann Jones , будут разделены на три столбца вместо двух, и их необходимо будет исправить. Дополнительную информацию см. в последнем разделе.

Как разделить, если разделителем является запятая, точка с запятой, табуляция или пробел

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

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

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

► 3. Откроется мастер преобразования текста в столбцы. Выберите «С разделителями» и нажмите «Далее».

► 4. Теперь мы указываем, какой символ Excel должен использовать для разделения данных. Убедитесь, что галочка стоит только в одном из полей в разделе «Разделитель» — запятая в нашем примере. Предварительный просмотр результатов отображается внизу. Нажмите «Далее».

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

► 6. Excel вернется к рабочему листу с выделенным первым столбцом. Разделенные данные в новом столбце будут иметь цвет и шрифт рабочего листа по умолчанию.

Как разделить, если символ или специальный символ является разделителем

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

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

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

► 3. Откроется мастер преобразования текста в столбцы. Выберите «С разделителями» и нажмите «Далее».

► 4. Убедитесь, что в разделе «Разделители» установлен флажок «Другое», и введите символ, который вы используете в качестве разделителя, в белом поле. Предварительный просмотр внизу показывает данные при разделении. Нажмите «Далее».

► 5. При необходимости измените формат данных для каждого столбца или используйте формат Excel по умолчанию. Затем нажмите Готово.

► 6. Excel вернется к рабочему листу с данными, разделенными на два столбца и выделенным первым столбцом.

Проблема: неточное разделение данных

Если разделитель появится где-то еще в данных, некоторые результаты будут неточными. В этом примере и название книги (Dog-Friendly Hotels), и фамилия автора (Ron Smith-Davies) содержат дефисы — наш разделитель. Поскольку Excel разделяет данные каждый раз, когда видит дефис, в строках 2 и 3 есть дополнительные столбцы, как показано на изображении для предварительного просмотра ниже.

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

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

При использовании функции «Текст в столбцы» Excel использует столько столбцов, сколько необходимо для выполнения разделения. В этом примере разделитель, пробел, повторяется в данных, и у нас есть только один пустой столбец. Таким образом, столбец данных будет перезаписан.

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

Если мы нажмем «ОК», Excel полностью перезапишет следующий столбец (или столбцы) независимо от того, сколько строк действительно нуждается в дополнительной ячейке.В нашем примере таблицы столбец F содержал даты приема на работу, но теперь все эти данные исчезли!

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

< бр />

Брайан Кларк

Брайан Кларк
Писатель

Брайан работает в сфере журналистики и издательского дела более 15 лет. В течение последних 10 лет он занимался технологиями, в том числе гаджетами, социальными сетями, безопасностью и веб-культурой. Прежде чем стать фрилансером, Брайан был управляющим редактором The Next Web. В настоящее время он проводит время в ряде изданий, как онлайн, так и вне его, включая The New York Times, Popular Science и The Next Web, среди прочих. Подробнее.

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

Как использовать «Текст в столбцы» в Excel

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

Перейдите на вкладку "Данные" в верхней части ленты Excel.

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

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

Проверьте разделители "Запятая" и "Пробел", а затем нажмите кнопку "Далее". Разделители — это просто способ разделения данных. В этом случае мы используем запятую и пробел, потому что каждая ячейка в столбце А имеет запятую и пробел, разделяющие их. Вы можете использовать любой разделитель, соответствующий вашему набору данных.

Далее мы собираемся щелкнуть ячейку, в которой мы хотим начать добавлять данные (в данном случае B2), и нажать «Готово». Это добавит имя и фамилию в соответствующие столбцы.

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

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

Как использовать «Быстрое заполнение» в Excel

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

Щелкните внутри первой ячейки соответствующего столбца (в нашем примере — «Первый») и введите имя первого человека в наборе данных.

Нажмите «Ввод» на клавиатуре, чтобы перейти к следующей ячейке вниз. На вкладке "Главная" на ленте нажмите "Редактирование", а затем "Быстрая заливка".

Кроме того, вы можете нажать Ctrl+E на клавиатуре.

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

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

На вкладке "Главная" нажмите "Редактирование", а затем "Быстрая заливка". Или используйте сочетание клавиш Ctrl + E.

И снова Flash Fill попытается вычислить данные, которые вы хотите заполнить в столбце.

Если мгновенное заполнение не работает должным образом, всегда можно отменить действие (Ctrl+Z).

› Как начать новую строку в ячейке в Microsoft Excel
› Как восстановить метки панели задач в Windows 11
› Почему прозрачные чехлы для телефонов желтеют?
› Что означает XD и как вы его используете?
› Худшее, что есть в телефонах Samsung, — это программное обеспечение Samsung.
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
› Почему СМС должен умереть

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