Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Очистка данных и Power Query

Разделение данных в Excel: от простого к сложному с помощью инструмента "Текст по столбцам"

forest_paw 07.09.2025 804

Разделение данных в Excel: от простого к сложному с помощью инструмента "Текст по столбцам"

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

Пошаговые действия относятся к настольному Excel для Windows, включая Excel 2016. На Mac интерфейс отличается. В Excel для Интернета доступно разделение по разделителям, но полного трёхшагового мастера нет.

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

Город, штат и индекс в столбце A. С выбранными данными выберите Данные, Текст по столбцам. В шаге 1 мастера выберите С разделителями вместо Фиксированной ширины.
Город, штат и индекс в столбце A. С выбранными данными выберите Данные, Текст по столбцам. В шаге 1 мастера выберите С разделителями вместо Фиксированной ширины.

Совет: В Excel для Microsoft 365 и Excel 2024 можно извлечь ZIP-код после последнего пробела с помощью формулы Русская версия: =ТЕКСТПОСЛЕ(A2;" ";-1)English version: =TEXTAFTER(A2," ",-1). В исходной строке не должно быть пробелов после индекса; в Excel 2021 и более ранних выпусках этой функции нет.

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

  1. Выделите диапазон данных (например, A2:A99) и перейдите на вкладку Данные → Текст по столбцам.
  2. В шаге 1 мастера выберите С разделителями и нажмите Далее.
  3. В шаге 2 снимите флажок с Табуляция и выберите Запятая. Предварительный просмотр покажет результат. Нажмите Далее.
Шаг 2 мастера предлагает разделители: Табуляция, Точка с запятой, Запятая, Пробел и Другой. При выборе Запятая предпросмотр внизу показывает Город в A, а Штат и Индекс в B.
Шаг 2 мастера предлагает разделители: Табуляция, Точка с запятой, Запятая, Пробел и Другой. При выборе Запятая предпросмотр внизу показывает Город в A, а Штат и Индекс в B.

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

  1. В шаге 3 оставьте формат столбцов Общий и нажмите Готово.
Шаг 3 мастера. Оставьте каждый столбец с форматом Общий. Другие варианты: Текст, Дата и Не импортировать столбец.
Шаг 3 мастера. Оставьте каждый столбец с форматом Общий. Другие варианты: Текст, Дата и Не импортировать столбец.

Разделение штата и индекса с сохранением формата

  1. Выделите столбец с объединенным штатом и индексом (B2:B99) и снова запустите Текст по столбцам.
  2. В шаге 1 выберите Фиксированная ширина. В шаге 2 поставьте границу перед первой цифрой индекса: для строки OH 01234 без начальных пробелов это позиция после третьего символа. Если после первого разделения остался начальный пробел, учтите его при установке границы. Проверьте все строки в предварительном просмотре.
  3. В шаге 3 выделите второй столбец (индекс) и установите формат Текст, чтобы сохранить ведущие нули.
Все еще в шаге 3 мастера, выберите заголовок для второго столбца и укажите, что почтовые индексы должны быть в формате Текст.
Все еще в шаге 3 мастера, выберите заголовок для второго столбца и укажите, что почтовые индексы должны быть в формате Текст.

В Microsoft 365 и Excel 2024: можно настроить автоматическое преобразование данных. В Windows откройте Файл → Параметры → Данные, на Mac — Excel → Параметры → Правка. В разделе автоматического преобразования отключите удаление ведущих нулей и преобразование в число. Эти настройки не заменяют выбор формата Текст в мастере и не восстанавливают уже потерянные нули.

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

Работа со сложными случаями

Фиксированная ширина и шрифты

Инструмент Фиксированная ширина часто работает лучше, чем кажется. Если данные выглядят неровно в пропорциональном шрифте (например, Calibri), попробуйте переключиться на моноширинный (Courier New) для проверки выравнивания.

7 строк данных. Каждая строка содержит Имя, Фамилию, Адрес, Город в столбце A. В первых трех строках современный шрифт делает данные неровными. Но в строках 4-7 применен моноширинный шрифт Courier New, и данные выровнены. Таким образом, Фиксированная ширина сработает.
7 строк данных. Каждая строка содержит Имя, Фамилию, Адрес, Город в столбце A. В первых трех строках современный шрифт делает данные неровными. Но в строках 4-7 применен моноширинный шрифт Courier New, и данные выровнены. Таким образом, Фиксированная ширина сработает.

Разделение по символу перевода строки (Alt+Enter)

Если данные в ячейке разделены с помощью Alt+Enter, в Excel для Windows выберите в шаге 2 мастера разделитель Другой, установите курсор в его поле и нажмите Ctrl+J для ввода символа перевода строки. Поле может выглядеть пустым; проверьте результат в предварительном просмотре.

В столбце A вы видите Имя, Улица, Город в одной ячейке, разделенные Alt+Enter. Введя Ctrl+J в поле Другой: в шаге 2 мастера, Excel разделит эти строки на новые столбцы.
В столбце A вы видите Имя, Улица, Город в одной ячейке, разделенные Alt+Enter. Введя Ctrl+J в поле Другой: в шаге 2 мастера, Excel разделит эти строки на новые столбцы.

Три специальные ситуации, которые легко решает "Текст по столбцам"

  • Даты в формате ГГГГММДД: Оставьте значение целым в одном столбце; в шаге 3 мастера выберите формат Дата и укажите ГМД из выпадающего списка.
  • Отрицательные числа с минусом в конце (например, 831.25-): В шаге 3 нажмите кнопку Дополнительно и включите Минус в конце для отрицательных чисел. Для примера с точкой также задайте точку как десятичный разделитель.
  • Данные с точками-заполнителями (например, из оглавления): В шаге 2 выберите разделитель Другой, введите точку и установите флажок Считать последовательные разделители одним.
Три сложных случая данных, которые можно решить с помощью Текста по столбцам: Даты, сохраненные как 20201225, числа, сохраненные как 831.25- и запись из оглавления, отделенная от номеров страниц неизвестным количеством повторяющихся точек.
Три сложных случая данных, которые можно решить с помощью Текста по столбцам: Даты, сохраненные как 20201225, числа, сохраненные как 831.25- и запись из оглавления, отделенная от номеров страниц неизвестным количеством повторяющихся точек.

Заключение

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

Поделиться:

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