Как в excel данные из одного столбца разделить на три
Очень часто сталкиваюсь с ситуацией, когда требуется разделить (разбить) ячейки в таблице «Excel» вертикально или горизонтально.
Стандартного инструмента для деления ячеек в «Excel» найти нет, соответственно приходится использовать другие, похожие инструменты, в зависимости от ситуации.
Далее расскажу о том, как произвести разделение ячейки «Excel» стандартными инструментами без написания дополнительных макросов.
Предположим, существует таблица, созданная в программе «Excel», в которой в одном из столбцов размещена информация с городами и наименованиями улиц.
Точка | Адрес | Бюджет |
Магазин телефонов | Самара; ул. Алтухого | 700 руб. |
Ремонт ноутбуков | Уфа; ул. Спиридонова | 850 руб. |
Ремонт обуви | Кострома; ул. Седина | 1050 руб. |
Пошив одежды | Москва; ул. Энтузиастов | 2000 руб. |
Необходимо разделить ячейки таким образом, чтобы в одной были названия городов в другой названия улиц.
Каким образом выполняется разбивка ячеек:
- Сначала нужно поставить курсор на разделяемую ячейку.
- Далее при помощи клика правой кнопкой мыши открыть контекстное меню. В меню выбрать пункт «Вставить» и выбрать, что вы хотите вставить:
- Для деления нужно снова поставить курсор в ячейку с информацией.
- Войти во вкладку «Данные» на панели инструментов.
- Нажать кнопку «Текст по столбцам».
Выделенная ячейка будет разделена и часть текста перейдет в соседнюю пустую ячейку.
Результат деления ячеек
Например, в таблице сотрудников на рис.1, требуется отсортировать данные по фамилиям, а затем по именам сотрудников (для упорядочения однофамильцев). Для этого следует распределить фамилии, имена и отчества по отдельным столбцам.
исходная таблица для разделения данных
Когда требуется разделить данные, находящиеся в одном столбце можно использовать встроенную возможность Excel – разбиение содержимого одного столбца на несколько.
Для этого необходимо выполнить следующие действия:
1. Справа от разделяемого диапазона вставить несколько пустых столбцов по числу отделяемых данных. Это делается, чтобы избежать замены данных в соседних столбцах. В нашем случае вставим два новых столбца (рис.2).
добавляем пустые столбцы
2. Выделить столбец, который содержит разделяемые данные (Фамилия Имя Отчество).
3. На панели Работа с данными вкладки Данные нажимаем кнопку Текст по столбцам.
мастер разделения данных Excel
4. Нажимаем кнопку Далее.
указываем признак разделения данных
6. Нажатием кнопки Далее переходим в следующее диалоговое окно: Мастер текстов (разбор) – шаг 3 из 3 (рис.5), которое позволяет установить формат данных для каждого нового столбца. Кнопка Подробнее открывает диалоговое окно, в котором можно установить дополнительные настройки числовых данных.
Можно при разбиении исключить отдельный столбец. Для этого следует выделить его в образце разбора и включить опцию Пропустить столбец.
указываем признак разделения данных
7. После установки всех требуемых параметров нажимаем кнопку Готово, в результате чего содержимое выделенного диапазона будет разбито на три столбца (рис.6).
применяем разделение данных
Теперь можно выполнять сортировку по фамилиям и именам сотрудников (рис.7). Для однофамильцев имена будут располагаться в порядке возрастания алфавита.
Разделение текста из одной ячейки по нескольким столбцам с сохранением исходной информации и приведением ее к нормальному состоянию – это проблема, с которой может столкнуться однажды каждый из пользователей Excel. Для разбивки текста по столбцам используются различные методы, которые определяются исходя из предложенной информации, необходимости получения конечного результата и степени профессионализма пользователя.
Необходимо разделить ФИО по отдельным столбцам
Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:
От эксперта! Для разделения текста могут быть использованы запятые, точки, двоеточия, точки с запятой, пробелы и другие знаки.
- Затем нужно определить формат данных столбца. По умолчанию установлено «Общий». Для нашей информации этот формат наиболее уместен.
- В таблице выбираем ячейку, куда будет помещаться отформатированный текст. Отступим от исходного текста один столбец и пропишем соответствующий адресат в адресации ячейки. По окончанию нажимаем «Готово».
Замечание эксперта! Размещенный отформатированный текст из-за разного количества символов в ФИО может не вмещаться в выбранные ячейки, поэтому полученная таблица нуждается в корректировке. Для этого используется расширение размеров ячейки.
Разделение текста с помощью формулы
Для самостоятельного разделения текста могут быть использованы сложные формулы. Они необходимы для точного расчета позиции слов в ячейке, обнаружения пробелов и деления каждого слова на отдельные столбцы. Для примера будем также использовать таблицу с ФИО. Чтобы произвести разделение, потребуется выполнить три этапа действий.
Этап №1. Переносим фамилии
Чтобы отделить первое слово, потребуется меньше всего времени, потому что для определения правильной позиции необходимо оттолкнуться только от одного пробела. Далее разберем пошаговую инструкцию, чтобы понять для чего нужны вычисления в конкретном случае.
- Таблица с вписанными ФИО уже создана. Для удобства выполнения разделения информации создайте в отдельной области 3 столбца и вверху напишите определение. Проведите корректировку ячеек по размерам.
- Выберите ячейку, где будет записываться информация о фамилии сотрудника. Активируйте ее нажатием ЛКМ.
- Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
- Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».
- Далее находим продолжение формулы ЛЕВСИМВ и кликаем по этой строке. Соглашаемся с выполненными действиями нажатием кнопки «ОК».
- Появляется новое окно, где нужно указать адресацию ячейки, нуждающейся в корректировке. Для этого нажмите на графу «Текст» и активируйте необходимую ячейку. Адресация вносится автоматически.
- Чтобы указать необходимое количество знаков, можно посчитать их вручную и вписать данные в соответствующую графу либо воспользоваться еще одной формулой: ПОИСК().
- После этого формула отобразится в тексте ячейки. Кликните по ней, чтобы открыть следующее окно.
- Находим поле «Искомый текст» и кликаем по разделителю, указанному в тексте. В нашем случае это пробел.
- В поле «Текст для поиска» нужно активировать редактируемую ячейку в результате чего произойдет автоматический перенос адресации.
- Активируйте первую функцию для возврата к ее редактированию. Это действие автоматически укажет количество символов до пробела.
- Соглашаемся и кликаем по кнопке «ОК».
В результате можно видеть, что ячейка откорректирована и фамилия внесена корректно. Чтобы изменения вступили в силу на всех строках, потяните маркер выделения вниз.
Этап №2. Переносим имена
Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.
- В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.
- Выбираем ячейку и указываем позицию, где прописан основной текст.
- Переходим к графе «Начальная позиция» и вписываем формулу ПОИСК().
- Переходим к ней, используя предыдущую инструкцию.
- В строке «Искомый текст» указываем пробел.
- Кликнув по «Текст для поиска», активируем ячейку.
- Возвращаемся к формуле =ПСТР в верхней части экрана.
- В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.
- Переходим к определению количества знаков – вписываем формулу ПОИСК().
- Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
- Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
- Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
- Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.
Этап №3. Ставим Отчество
Примечание эксперта! Формула определит автоматически количество символов.
Заключение
В статье прошло ознакомление с двумя распространенными способами разделения информации в ячейках по столбцам. Следуя нехитрым инструкциям, можно с легкостью освоить владение данными способами и использовать их на практике. Сложность разделения по столбцам, используя формулы, может оттолкнуть с первого раза неопытных пользователей Excel, но практическое применение метода, поможет привыкнуть к нему и применять его в дальнейшем без каких-либо проблем.
Если ваши данные в Microsoft Excel плохо структурированы и размещены в одном столбце, вы можете разделить его на несколько столбцов. Этот пост предлагает простые для понимания инструкции о том, как разбить один длинный столбец на несколько столбцов в Excel.
Разделить один столбец на несколько столбцов в Excel
В зависимости от количества набора данных пользователь может выбрать один или несколько разделителей, чтобы разделить текст в столбце.
- Используйте мастер преобразования текста в столбцы
- Выберите разделители для ваших данных
- Выберите направление
Вы можете взять текст в одном столбце и разделить его на несколько столбцов с помощью мастера преобразования текста в столбцы.
1]Используйте мастер преобразования текста в столбцы
Откройте лист Excel, в котором вы хотите разделить один столбец на несколько столбцов.
2]Выберите разделители для ваших данных
Здесь укажите разделители для ваших данных. Например, запятая и пробел.
По умолчанию Excel попытается разделить данные в одном столбце по каждой найденной вкладке. Это нормально, но вы можете настроить формат данных столбца,
- Общий
- Дата
- Текст и многое другое
General преобразует числовые значения в числа, значения дат в даты и все остальные значения в текст. Здесь мы используем данные, разделенные точкой с запятой.
3]Выберите пункт назначения
Как вы можете видеть на изображении выше, один столбец будет разделен на несколько (2 столбца), и данные будут выглядеть хорошо структурированными и организованными.
Читайте также: