8 способов разделить ячейку Excel на две или более
Как разделить ячейку в Excel? С помощью функций "Текст по столбцам", "Мгновенная заливка", "Формулы" или путем вставки в него фигуры. В этом руководстве описаны все варианты, чтобы помочь вам выбрать технику, которая лучше всего подходит для вашей задачи.
Как правило, в Excel может возникнуть необходимость разделить ячейки в двух случаях. Чаще всего, когда вы импортируете данные из какого-либо внешнего источника, где вся информация находится в одном столбце, а вы хотите, чтобы она была в отдельных столбцах. Или вы можете захотеть разделить отдельные элементы в существующей таблице для лучшей фильтрации, сортировки или детального анализа
1. Как разбить ячейки с помощью разделителей.
Функция "Текст в столбец" очень полезна, когда вам нужно разделить одну ячейку на две или более в Excel 2019, 2016, 2013 и более ранних версиях. Он позволяет разделять текстовые строки с помощью разделителя по вашему выбору, например, запятой, точки с запятой или пробела, а также разбивать на подстроки фиксированной длины. Давайте посмотрим, как работает каждый сценарий.
Предположим, у вас есть список участников, в котором в одной колонке указаны их имена, город проживания, страна и предполагаемая дата прибытия:
Мы хотим разделить данные в одной ячейке на несколько: имя, город, страна и дата. Для этого выполните следующие действия:
- Если вы хотите разместить разделенные ячейки в середине таблицы, начните со вставки нового столбца (столбцов), чтобы избежать перезаписи существующих данных.
В данном примере мы вставляем 4 новых столбца, как показано на скриншоте ниже:
Если у вас нет данных рядом со столбцом, который вы хотите разделить, просто пропустите этот шаг.
- Выделите область, которую вы хотите разделить, и нажмите кнопку "Текст по столбцам" .
- На первом шаге мастера преобразования мы выбираем, будут ли ячейки разделены разделителем или заданной шириной.
В нашем случае нужное содержимое разделяется пробелами и запятыми. Поэтому мы выбираем формат "delimited" и нажимаем Next.
- На следующем шаге вы указываете эти разделители и, если необходимо, разделитель строк.
Вы можете выбрать один или несколько разделителей или ввести свой собственный в поле "Другое". В этом примере мы выбираем пробел и запятую:
Руководство:
- Считать последовательные разделители как одно целое число. Не забудьте выбрать этот параметр, если ваши данные могут содержать два или более последовательных разделителей, например, когда между словами есть несколько последовательных пробелов, или когда данные разделены запятой и пробелом, например, "Пробел".
- Укажите разделитель строк. Используйте этот параметр, если у вас есть текст, заключенный в одинарные или двойные кавычки, и вы хотите, чтобы такие части текста не разрывались. Например, если выбрать запятую (,) в качестве разделителя и перевернутую запятую (") в качестве разделителя текста, все слова, заключенные в перевернутые запятые, такие как "Минск, Беларусь", будут помещены в одну ячейку. Если в качестве разделителя строк выбрано {no}, то "Минск" будет помещен в одну ячейку (вместе с открывающей перевернутой запятой), а "Беларусь" - в другую (вместе с закрывающей перевернутой запятой).
- Данные предварительного просмотра. Прежде чем нажать кнопку Далее, стоит просмотреть окно Разбор образца данных, чтобы убедиться, что Excel правильно разделил все содержимое ячеек.
- Вам остается сделать две вещи - выбрать формат данных и указать, куда их вставить:
-
- Формат. По умолчанию для всех столбцов используется общий формат, который хорошо работает в большинстве случаев. В нашем примере нам нужен определенный формат для дат. В противном случае они будут представлены в виде чисел. Чтобы изменить формат для конкретного столбца, щелкните столбец в разделе Parsing Pattern, чтобы выбрать его, а затем выберите один из форматов в разделе Column Data Format.
- Цель. Чтобы указать Excel, куда вы хотите вывести разделенные данные, выберите самую левую верхнюю позицию диапазона назначения или введите ссылку на него непосредственно в поле "Поместить в ...". Будьте очень осторожны с этой опцией и убедитесь, что справа после целевого адреса достаточно пустого пространства для вставки.
Внимание:
- Если вы не хотите импортировать какой-либо столбец, который отображается в предварительном просмотре данных, выберите его и установите флажок "Игнорировать ...". в разделе Формат.
- Вы не можете импортировать результат своей работы в другую электронную таблицу или книгу. Если вы попытаетесь сделать это, вы получите ошибку недопустимого назначения.
2. Как разделить на фрагменты фиксированной ширины.
В этом разделе объясняется, как разделить ячейку в Excel на основе определенного количества символов. Чтобы это было легче понять, рассмотрим следующий пример.
Предположим, у вас есть артикулы товаров и их названия в одном столбце. Вы хотите извлечь статьи в отдельный столбец:
Поскольку все идентификаторы продуктов содержат 9 символов, вариант с фиксированной шириной идеально подходит для этой задачи:
- Запустите мастер преобразования, как описано в примере выше. На первом шаге выберите "Фиксированная ширина" и нажмите "Далее" .
- Установите ширину каждого столбца в окне предварительного просмотра данных. Как видно на рисунке ниже, вертикальная линия отмечает границу столбца. Чтобы создать новую линию разрыва, просто щелкните в нужной позиции (в нашем случае 9 символов):
- Чтобы убрать пробел, дважды щелкните на разделителе; чтобы переместить его в другое положение, просто перетащите мышь.
На следующем этапе выберите формат данных и место назначения для разделенных ячеек точно так же, как мы делали это в предыдущем примере, и нажмите "Готово", чтобы завершить разделение.
3. Используем инструмент «Мгновенное заполнение».
Если вы используете любую версию Excel от 2013 до Excel 2019, вы можете использовать функцию Instant Fill, которая может не только автоматически заполнить диапазон данными, но и разделить его содержимое.
Если вы еще не пользовались этой функцией, позвольте мне быстро объяснить, как она работает. Программа анализирует данные, которые вы вводите в таблицу, и пытается определить, есть ли в них закономерность. Как только Excel вычисляет закономерность, он тут же предлагает вариацию.
Таким образом, вы можете использовать этот инструмент, чтобы взять некоторые данные и вставить их в новый столбец. На примере это будет яснее и понятнее.
Во-первых, убедитесь, что инструмент "Мгновенная заливка" включен. Этот параметр можно найти на вкладке Файл > Параметры > Дополнительно > Автоматически выполнять мгновенную заливку.
Теперь давайте посмотрим, как его можно использовать для автоматического разбиения данных на несколько столбцов. То есть, вы начинаете вводить данные, которые хотите сохранить в отдельных элементах. По мере ввода Excel будет пытаться распознать закономерность во введенных вами значениях, и когда он поймет ее, данные будут автоматически вставлены в другие позиции ниже. Чтобы понять, как это работает, посмотрите на рисунок ниже:
Давайте возьмем столбец данных из нашего первого примера и посмотрим, как мгновенная заливка Excel может помочь нам разделить ячейку пополам:
- Вставьте новый столбец рядом с исходным столбцом данных и введите нужный текст в первую ячейку (в данном примере это имя сотрудника).
- Введите текст в следующие несколько ячеек. Как только программа определит деталь, в которую вы вводите значения, она предложит вам заполнить остальные. В нашем случае потребовалось всего две записи, чтобы обнаружить закономерность:
- Если вас устраивает то, что вы видите, нажмите клавишу Enter, и все имена будут вставлены непосредственно ниже.
Если функция Instant Fill включена, но не предлагает никаких опций, вы можете вызвать этот инструмент вручную на вкладке Data или нажав Ctrl+E.
4. Как разделить по диагонали при помощи формата.
Давайте пошагово рассмотрим, как разделить ячейку по диагонали с помощью форматирования:
- Вызовите меню Формат ячеек. Используйте вкладку Шрифт, чтобы указать используемый шрифт и его размер.
- На вкладке Граница выберите тип линии.
- Здесь также нажмите на диагональную линию в поле "Разделить".
- При необходимости можно красиво расположить текст так, чтобы он казался разделенным диагональной линией. Это часто используется для заголовка таблицы в первом столбце.
На вкладке "Выравнивание" в окне "Ориентация" можно изменить наклон текста, чтобы получить нечто подобное тому, что показано на скриншоте выше.
- Подтвердите, нажав кнопку "OK".
Примечание: Excel по-прежнему рассматривает объект как одну ячейку, когда вы это делаете.
5. Как разделить ячейку по диагонали при помощи вставки фигуры
Этот вариант похож на предыдущий и также подходит для больших ячеек. Вместо форматирования мы переходим на вкладку "Вставка" и выбираем линию из различных фигур. Она должна быть нарисована по диагонали.
Текст хорошо выстраивается, если изменить его горизонтальную ориентацию, как описано выше. Вы можете разделить его на строки внутри самой ячейки таблицы с помощью ALT + Enter.
Думаю, вы догадались, что таким же образом линия может разделить клетку на две по горизонтали.
6. Как разбить ячейку с помощью формул
Независимо от разнообразия информации, которую содержит ваша электронная таблица, формула для разделения ячейки в Excel сводится к поиску положения разделителя (запятой, пробела и т.д.). А затем извлечение подстроки до, после или между разделителями. В общем случае вам нужно использовать функцию ПОИСКПОЗ или ПОИСКПОЗ для нахождения разделителя, а также одну из текстовых функций (LEFT, RIGHT или FAST) для получения подстроки.
Например, для разбиения данных в A2, разделенных пробелом, следует использовать следующие формулы (см. скриншот ниже):
Чтобы извлечь статью в B2:
=LEFTSIMV(A2;SEARCH(" ";A2;1))
Чтобы извлечь название продукта в C2:
=PRAVSIMV(A2;DLSTR(A2)-SEARCH(" ";A2;1))
Подробнее о разбиении ячейки с помощью формул читайте здесь.
7. Разделяем ранее объединённые ячейки.
Ранее мы писали о том, как можно объединить клетки и содержащуюся в них информацию. Однако иногда, по разным причинам, вам необходимо объединить их обратно. Для этого установите курсор на область, которую нужно объединить, затем нажмите стрелку рядом с кнопкой
на ленте Главная.
Затем выберите Unmerge. Весь текст будет размещен в самой левой ячейке, а остальные ячейки будут пустыми.
И этот объединенный текст может быть разделен любым из способов, описанных нами ранее в этом учебнике.
8. Разделение ячеек с помощью инструмента разделения текста Split text.
Теперь, когда вы знакомы со встроенными функциями, позвольте мне показать вам альтернативный способ разделения ячеек в Excel. Я имею в виду инструмент Split text, включенный в Ultimate Suite для Excel . Он может выполнять следующие операции:
- Разделение ячейки по символам
- Разделить ячейку по строкам
- Разделение ячейки по маске (шаблону)
Например, разделение участника из одной ячейки на несколько ячеек можно выполнить в 2 быстрых шага. Вот пример исходных данных:
- Выделите ячейки, которые нужно разделить, и нажмите значок "Разделить текст" на вкладке Data Ablebits в разделе "Текст".
- На панели Разделить текст настройте следующие параметры:
- Выберите запятую и пробел в качестве разделителей.
- Отметьте опцию Count consecutive delimiters как один из вариантов.
- В разделе "Выберите способ разделения ячеек" выберите "Разделить по столбцам".
- Нажмите кнопку "Разделить по столбцам".
Готово! Четыре новых разделенных столбца вставляются между исходными столбцами, и вам просто нужно дать им соответствующие имена:
Совет: Инструмент "Разделить текст" предоставляет еще два варианта разделения ячейки в Excel. Для получения дополнительной информации см. раздел "Как разделить ячейки". Для получения дополнительной информации см. разделы "Как разделить ячейки по символу-разделителю" и "Как разделить ячейки по маске".
Если вы хотите увидеть "Разделенный текст" и "Разделенные имена" в действии, вы можете загрузить 14-дневное пробное и ознакомительное руководство по разделенным ячейкам. Вы можете загрузить 14-дневную пробную версию Ultimate Suite for Excel.
