Ручное разбиение содержимого ячейки на несколько столбцов в Microsoft Excel отнимает много времени и сопровождается ошибками. К счастью, в программе есть сразу несколько удобных инструментов — от классических мастеров до умных функций, которые быстро и точно справятся с этой задачей.
Разделяем данные с помощью инструмента «Текст по столбцам»
Один из самых простых способов — воспользоваться встроенным помощником «Текст по столбцам». Он пошагово проводит вас через процесс, что особенно удобно для тех, кто предпочитает точные инструкции.
Например, нужно разбить полные имена, которые записаны в столбце А, на фамилии и имена, вставив их в столбцы В и С соответственно.
Выделяю необходимые ячейки в столбце А, затем на вкладке «Данные» нажимаю кнопку «Текст по столбцам».
В появившемся мастере выбираю опцию «С разделителями» и нажимаю «Далее».
Разделитель — это символ, по которому Excel поймёт, где делить текст. В нашем случае имена и фамилии разделены запятой и пробелом — ставлю галочки напротив этих вариантов. Внизу сразу отображается, как данные будут разбиты. Если всё устраивает, жму «Далее».
Далее очищаю поле «Поместить результат» и указываю ячейку, куда хочу вывести разделённые данные — например, В2 — затем нажимаю «Готово».
Если нужно, можно заменить исходные данные разделёнными, просто указав в поле «Поместить результат» левый верхний угол исходных данных — в нашем случае А2.
В итоге имена и фамилии аккуратно распределены по разным столбцам. Исходный столбец А можно удалить, так как теперь он не связан с новыми данными.
Обратите внимание: если имя или фамилия содержат два слова (например, двойное имя или сложная фамилия), разделение по пробелу создаст отдельный столбец для каждого слова, что может быть неудобно.
В такой ситуации выбираем в мастере «Текст по столбцам» только запятую в качестве разделителя, снимем галочку с пробела.
Чтобы убрать лишние пробелы в начале имён, добавляю рядом ещё один столбец и в ячейке D2 пишу формулу:
Функция TRIM убирает лишние пробелы в тексте, оставляя только пробелы между словами, а [@[Имя]] — это ссылка на столбец «Имя» в таблице.
Как пользоваться функцией TRIM в Microsoft Excel
Быстро избавляемся от лишних пробелов в тексте.
Нажимаю Enter — и имена появляются в столбце D уже без пробелов в начале. Благодаря структуре таблицы формула автоматически распространяется на все остальные строки.
Теперь выделяю все новые имена, нажимаю Ctrl C, потом Ctrl Alt V, выбираю «Значения» и жму Enter.
В конце удаляю столбец со старыми именами с пробелами (С) и исходный столбец с полными именами (А) — теперь таблица аккуратна.
Разделение с помощью автоматического инструмента Flash Fill
Excel умеет многое, и Flash Fill — один из самых удобных автоматических помощников.
Если мастер «Текст по столбцам» кажется вам слишком медленным, Flash Fill распознает шаблон и быстро разделит данные по вашему примеру.
Хватит тратить время! Как ускорить работу в Excel
Используйте встроенные инструменты для решения задач быстро и легко.
Начинаю с ячейки, куда хочу вывести первую часть — например, В2, ввожу вручную «Smith» и жму Enter.
Затем на вкладке «Данные» нажимаю кнопку «Flash Fill»...
...или просто нажимаю Ctrl E на клавиатуре.
Excel анализирует введённые данные, находит закономерность и автоматически заполняет оставшиеся ячейки столбца с фамилиями.
Повторяю то же для имён в столбце С: ввожу первое имя, жму Enter, затем Ctrl E.
Flash Fill отлично подойдёт для небольших наборов данных — просто быстро проверьте результат. Но при работе с большими объёмами лучше использовать более надёжные методы, например, формулы Excel.
Разделение с помощью встроенных функций Excel
Ещё один вариант — использовать формулы Excel для разбиения данных. Помните: в этом случае результат будет связан с исходной ячейкой, так что для независимости данных придётся копировать и вставлять значения вручную.
TEXTSPLIT
Функция TEXTSPLIT разделяет текст из одной ячейки на несколько столбцов по заданному разделителю и возвращает результат в виде массива.
Что такое «растекшиеся» массивы в Excel и как с ними работать
Как использовать функции, которые возвращают сразу несколько значений.
Важно: эти функции нельзя применять в таблицах Excel с форматированием — только в обычных диапазонах.
Представим, надо разделить список столиц и штатов в столбце А на два смежных столбца — В и С.
В ячейку В2 ввожу формулу:
Здесь A2 — исходная ячейка, а ", " — разделитель, состоящий из запятой и пробела.
Нажимаю Enter — и вижу, что результат занимают сразу два соседних столбца с синей рамкой, показывающей «растекшийся» массив.
Если данные находятся вне таблицы, формулу нужно копировать вниз вручную, потянув маркер заполнения справа внизу ячейки до конца списка.
TEXTBEFORE и TEXTAFTER
В отличие от TEXTSPLIT, эти функции возвращают либо часть текста до разделителя, либо после него. Они отлично подходят для работы с таблицами Excel, что удобно.
Продолжаем пример с разбиением в столбцах. В ячейке В2 набираю формулу:
Здесь [@[Столица и штат]] — ссылка на исходный столбец, а ", " — разделитель (запятая с пробелом). Формула возвращает текст, который стоит до запятой.
Как использовать структурированные ссылки в Excel
Ссылки по именам таблиц и столбцов вместо обычных адресов ячеек.
В столбце С для штатов вводим формулу:
Жму Enter — теперь получаем текст после запятой.
Главное преимущество TEXTBEFORE и TEXTAFTER — можно выводить результат в любые столбцы, а не только в соседние.
LEFT, MID и RIGHT
Функции LEFT, MID и RIGHT позволяют выделять части текста на жёстко заданных позициях. Это удобно, если данные имеют чёткую структуру — например, коды или серийные номера.
Как выделить подстроку в Microsoft Excel
Выбираем только нужный фрагмент из строки с данными.
Представим, первые три символа кода — это страна, последние три — идентификатор, а символ посередине — ранг. Цель — разделить эти элементы по столбцам В, С и D соответственно.
В ячейке В2 пишу формулу:
LEFT возвращает первые 3 символа слева из столбца «Код».
В D2 применяю RIGHT — она берёт последние 3 символа:
В C2 — MID, чтобы получить символ посередине, начиная с 4-го:
Число 4 — позиция начала, а 1 — количество извлекаемых символов.
Так я организовал данные из одного столбца в три отдельных.
Разделение с помощью редактора Power Query
Многие считают Power Query слишком громоздким инструментом, хотя он создан для упрощения работы и отлично подходит для разбивки данных на столбцы.
Например, есть таблица с командами NFL, которые нужно разделить по региону и названию.
Выделяю любую ячейку с данными, на вкладке «Данные» нажимаю «Из таблицы/диапазона».
Откроется редактор Power Query — здесь начинается волшебство!
Щёлкаю правой кнопкой по заголовку нужного столбца и выбираю «Разделить столбец» > «По разделителю».
В появившемся окне выбираю разделитель — в нашем случае пробел. Так как некоторые регионы состоят из двух слов, а суффиксы — из одного, выбираю разделение по последнему разделителю справа.
Жму «ОК» — и вижу, что названия команд поделились на два столбца. Переименовываю заголовки, чтобы всё было понятно.
Осталось нажать «Закрыть и загрузить» в левом верхнем углу — и обновлённые данные появятся на новом листе.
Если исходные данные изменятся, не забудьте обновить таблицу через вкладку «Конструктор таблиц» — данные обновятся и снова будут разбиты по заданным правилам.
Как очистить и импортировать данные с помощью Power Query в Excel
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете найти наши материалы в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru