Как разъединить данные в ячейках эксель. Как разделить текст в excel с помощью формулы. Разбиение ячеек по диагонали посредством вставки фигуры

Пользователи, привыкшие к работе с формулами в табличном процессоре «Эксель» от «Майкрософт», порой пытаются найти готовую функцию для деления одного числа на другое. Но такой функции нет, и использовать знак двоеточия так же не получится - он в «Эксель» предназначен для обозначения границ диапазонов. Формула деление в Excel - это просто знак обратного слэша (/).

Как разделить одно число на другое

На рабочем листе книги «Эксель» введите в разные ячейки любые два числа. Они помогут разобраться с тем, как сделать формулу деления в Excel. Затем в свободной ячейке наберите знак начала формулы (равно =) и укажите первую ячейку с делимым. Далее наберите знак деления в формуле Excel - обратный слэш - и укажите ячейку с делителем.

Удобно, что при попытке деления на ячейку, содержимое которой представляет собой 0, «Эксель» выведет соответствующую ошибку: #ДЕЛ/0!.

Важно! Эксель позволяет делить между собой любые числа, в том числе и даты. А вот при попытке выполнить деление над ячейками, содержащими текст, вы получите ошибку «#ЗНАЧ!», о есть неверное значение.

Деление дат

Очень интересно делить даты. Попробуйте разделить «01.01.2017» на «01.01.2016». Думаете, у вас ничего не получится? Это не так. Дело в том, что «Эксель» автоматически преобразует все даты в число, отсчитывая количество прошедших дней с 01 января 1900 года. Дата «01.01.1900» - это число 1, «02.01.1900» - 2. Вы легко можете это проверить, преобразуя форматы ячеек с указанными датами с формата «Дата» на формат «Числовой». Таким образом, 1 января 2017 года - это число 42736, именно столько дней прошло с того же числа и месяца 1900 года. Поэтому при делении дат одну на другую мы можем получить вполне конкретный результат.

Логично, что вы можете делить не только дату на дату, но и любое число на дату и наоборот. Поскольку формула «деление» в Excel все равно представит вашу дату в число. А им можно оперировать каким угодно образом.

Таблица деления в «Эксель»

Построим таблицу в «Эксель», в ячейки А2:А6 поместим числа, которые будем делить (3, 7, 9, 12, 15), а в ячейки В1:Е1 - числа, НА которые будем выполнять деление (1, 2, 3, 4).

Чтобы составить таблицу деления, важно вспомнить про в «Эксель». Формула деление в Excel при составлении такой таблицы предполагает использование смешанных ссылок. Так, чтобы адреса ячеек при копировании не смещались, надо закрепить в делимом адресе номер столбца, а в делителе - номер строки. Таким образом, введите в ячейку В2 формулу «=$A2/B$1» и протяните ее до конца строки? а потом и на всю таблицу. Таблица заполнится данными. Встаньте теперь на любую ячейку, допустим, на D5. Формула ее будет следующая: «=$A5/D$1». Все сделали правильно.

Деление с остатком

При выполнении деления в большинстве случаев мы получаем дробное число. Для упрощения восприятия таких чисел есть в «Эксель» два важных момента.

Ячейка, в которую записывается результат, по умолчанию имеет формат «Общий». Если перейти на вкладке «Главная» в раздел «Число», то можно из выпадающего списка выбрать пункт смены формата «Другие числовые форматы» и задать формат «Числовой». По данному формату можно присвоить для ячейки количество отображаемых десятичных знаков. Так, при делении 7/3 мы обычно видим число 2,333333333, а с помощью ограничения точности на три десятичных знака оно станет выглядеть как 2,333. Что, согласитесь, довольно удобно.

В «Эксель» есть две отличные функции для получения частного и остатка от деления. Они носят название «Частное» и «Остаток» и обе принимают два обязательных аргумента - числитель и знаменатель. Разберемся с ними подробнее.

Введите в «Эксель» два числа - 7 и 3 (в ячейки D9 и D10 в нашем случае), и правее от них - формулы:

Как видим, частное от деления равно двум, остаток - 1. В любую свободную ячейку введите формулу:

E9 & " " & E10 & "/" & D10

Здесь знак амперсанда помогает присоединить к содержимому одной ячейки содержимое другой в виде текста.

Результат выполнения данной формулы будет следующий: 2 1/3 - две целых и одна треть.

Как видим, формула деление в Excel не представляет собой ничего сложного. Главное помнить, что при вычислении формул «Эксель» использует стандартные правила математики.

Лист в Microsoft Excel – сам по себе большая таблица, состоящая из ячеек, которым присвоен определенный адрес (цифра, соответствующая строке, и буква, соответствующая столбцу). По этому адресу можно обращаться к данным, то есть, чтобы использовать их в формулах. Соответственно, поделить ячейку напрямую, как скажем в Ворде, не получится. Поскольку адрес А5 не станет А/1-5 и А/2-5. Так что же можно сделать?

Если у Вас еще не набрана таблица и данные не вписаны, тогда изначально хорошо все продумайте, а потом просто объедините определенные ячейки оставив, например, их разделенными только в шапке или в каком-нибудь ряду.

А вот как поступить, если таблица готова, давайте расскажу в данной статье. Разберемся, как разделить ячейку в Экселе на две и больше по горизонтали и вертикали, а также по диагонали.

Для примера у нас будет такая таблица. В ней нужно после Названия указать еще и Производителя.

Поскольку новые столбцы добавляются слева от текущего выделения, выбираю любое значение в столбце С и на вкладке «Главная» вставляю столбцы.

Итак, после Названия появился еще один вертикальный столбец, который назову Производитель.

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

Начнем с Яблока. Выделяем блок сразу под данным названием, кликаем правой кнопкой мыши и выбираем в контекстном меню «Вставить» .

Отмечаем маркером в окошке «строку» и кликаем «ОК» .

В нужном нам месте добавится дополнительная строка. Таким же способом добавляются еще строки, если Производителей больше.

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

Про различные способы объединения ячеек в Эксель можно прочесть в соответствующей статье.

Заполняем данными поле Производитель и Цена.

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

С Грушей можно сделать все так же. Добавляем строчки, объединяем там, где Номер и Название, и только два рядка в столбце Количество (поскольку значение будет одинаковое). Потом вписываем данные и получаем как-бы поделенную ячейку в поле Производитель и Цена.

Обратите внимание, в объединенных блоках нужно указывать в формуле адрес верхнего левого. Например, из D2:D3 это будет D2, а из В4:В6 – В4.

Теперь давайте рассмотрим, как разбить одну ячейку на 2 или больше по вертикали. Это необходимо, если хотите добавить к названию столбца несколько подназваний, и уже потом заполнять их данными.

Для примера поделим столбец Количество. Со второго рядка выделяем любую не деленную ячейку, это может быть С2 или Е2 . Вызываем контекстное меню и вставляем дополнительную строку.

С помощью соответствующей кнопки объединяем D1 и E1 , поместив значение в центре.

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

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

Чтобы разбить по диагонали, выделяем ячейку, вызываем контекстное меню кликом правой кнопки и жмем «Формат» .

Выбранный блок будет поделен.

Можно сделать и по-другому. Вверху откройте «Вставка» , нажмите «Иллюстрации» – «Фигуры» и выберите из них прямую линию.

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

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

Например, в таблице сотрудников на рис.1, требуется отсортировать данные по фамилиям, а затем по именам сотрудников (для упорядочения однофамильцев). Для этого следует распределить фамилии, имена и отчества по отдельным столбцам .

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

Для этого необходимо выполнить следующие действия:

1. Справа от разделяемого диапазона вставить несколько пустых столбцов по числу отделяемых данных. Это делается, чтобы избежать замены данных в соседних столбцах. В нашем случае вставим два новых столбца (рис.2).

2. Выделить столбец, который содержит разделяемые данные (Фамилия Имя Отчество).

3. На панели Работа с данными вкладки Данные нажимаем кнопку Текст по столбцам.

В открывшемся диалоговом окне Мастер текстов (разбор) – шаг 1 из 3 (рис.3) в области Формат исходных данных указать вариант разбиения:
— с разделителями – если текст в ячейках разделен пробелами, запятыми, точками с запятой, знаками табуляции и т.п. (это наш случай – разделение пробелами );
— фиксированной ширины – если мы разделяем данные на равные (по столбцам) части.

5. В окне Мастер текстов (разбор) – шаг 2 из 3 (рис.4) выбираем символ-разделитель — пробел . Если среди предложенного набора отсутствует нужный знак, следует установить флажок другой и в поле ввести требуемый символ. При этом в нижней части окна будет приведен образец разбиения.

6. Нажатием кнопки Далее переходим в следующее диалоговое окно: Мастер текстов (разбор) – шаг 3 из 3 (рис.5), которое позволяет установить формат данных для каждого нового столбца. Кнопка Подробнее открывает диалоговое окно, в котором можно установить дополнительные настройки числовых данных.
Можно при разбиении исключить отдельный столбец. Для этого следует выделить его в образце разбора и включить опцию Пропустить столбец .

7. После установки всех требуемых параметров нажимаем кнопку Готово , в результате чего содержимое выделенного диапазона будет разбито на три столбца (рис.6).

Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части.

Для этого мы использовали инструмент в Excel «Текст по столбцам».

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6)

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

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

• 1 Пример 1. Делим текст с ФИО по столбцам с помощью формул
• 1.1 Приступаем к делению первой части текста - Фамилии
• 1.2 Приступаем к делению второй части текста - Имя
• 1.3 Приступаем к делению третьей части текста - Отчество
• 2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

Пример 1. Делим текст с ФИО по столбцам с помощью формул Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях.

В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера. У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.

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

Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу

НАЙТИ(» «-A2-1)

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

Значит на необходимо поменять третий аргумент в функции НАЙТИ - начальная позиция - то есть позиция с которой функция будет искать искомый текст.

Мы видим, что второй пробел находится в любом случае после первого пробела, а позицию первого пробела мы уже нашли, значит прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ искать пробел начиная с первой буквы после первого пробела.

Функция будет выглядеть следующим образом:

Приступаем к делению первой части текста - Фамилии

Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:

ПСТР(текст- начальная_позиция- число_знаков), где

• текст - это ФИО, в нашем примере это ячейка A2;
• начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
• число_знаков - мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.

Формула будет выглядеть следующим образом:

ПСТР(A2-1-H2-1)

Приступаем к делению второй части текста - Имя

Снова используем функцию =ПСТР(текст- начальная_позиция- число_знаков), где

• начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
• число_знаков - число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2

Получаем итоговую формулу:

ПСТР(A2-H2+1-I2-H2)

Приступаем к делению третьей части текста - Отчество

И снова функция =ПСТР(текст- начальная_позиция- число_знаков), где

• текст - это тот же текст ФИО, в нашем примере это ячейка A2;
• начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
• - в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом - 50

Получаем функцию

Видео: Формулы в Эксель

Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «-A2-1), а расчет второго пробела в ячейке I2 - НАЙТИ(» «-A2-H2+1) .

Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)

Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией

• Фамилия =ПСТР(A2-1-H2-1) получаем =ПСТР(A2-1-НАЙТИ(» «-A2-1)-1)
• Имя =ПСТР(A2-H2+1-I2-H2) получаем =ПСТР(A2-НАЙТИ(» «-A2-1)+1; НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)-НАЙТИ(» «-A2-1))
• Отчество =ПСТР(A2-I2+1-50) получаем =ПСТР(A2-НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)+1-50)

Теперь промежуточные вычисления позиции пробелом можно смело удалить. Это один из приемов, когда для простоты сначала ищутся промежуточные данные, а потом функцию вкладывают одну в другую. Согласитесь, если писать такую большую формулу сразу, то легко запутаться и ошибиться.

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

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

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

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

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

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

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

Теперь основная суть приема.

Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки

НАЙТИ(B1-A1-1)

То есть ищем запятую, в тексте, начиная с позиции 1

НАЙТИ($B1-$A1-D1+1)

1. Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A - либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
2. Во-вторых: третий аргумент - начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.

Пропишем формулу и протянем ее вниз.

Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее

Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.

ПСТР(A1-1-D1-1)

Начальная позиция у нас 1, количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз

Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1

ПСТР($A1-D1+1-E1-D1-1)

Начальная позиция второго числа у нас начинается после первой запятой. Позиция первой запятой у нас есть в ячейке D1, прибавим единицу и получим начальную позицию нашего второго числа.

Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1 Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.

Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.

Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением.

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

ЕСЛИОШИБКА(ПСТР($A1-D1+1-E1-D1-1)- «»)

Примечание. Первую позицию разделителя и первое деление слова мы делали отлично от других и из-за этого могли протянуть формулу только со вторых значений.

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

НАЙТИ($B1-$A1-C1+1)

а первого текста как

ПСТР($A1-C1+1-D1-C1-1)

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

В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.

Внимание! В комментариях заметили, что так как в конце текста у нас нет разделителя, то у нас не считается количество символов от последнего разделителя до конца строки, поэтому последний разделенный текст отсутствует.

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

Либо второе решение - это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.

ЕСЛИОШИБКА(НАЙТИ($B1-$A1-C1+1)-1000)

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

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

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

Инструмент «Текст по столбцам » действительно очень удобен, когда нужно разделить данные из одного столбца по нескольким в Excel 2013, 2010, 2007 или 2003.

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

Разбиваем текстовые данные с разделителями по столбцам в Excel

Предположим, есть список участников, приглашённых на конференцию или какое-то другое мероприятие. На рисунке ниже видно, что в столбце Participant (Участник) перечислены имена участников, государство и ожидаемая дата прибытия:

Необходимо разбить этот текст на отдельные столбцы, чтобы таблица имела следующие данные (слева направо): First Name (Имя), Last Name (Фамилия), Country (Страна), Arrival Date (Ожидаемая дата прибытия) и Status (Статус).

Примечание: Если у Вас нет столбцов, следующих непосредственно за тем, что Вы хотите разбить, то необходимость в этом шаге отпадает и его можно пропустить. Главное не упустите, что пустых столбцов должно быть не меньше, чем количество столбцов, на которое вы хотите разделить данные.

Совет: Если Вы не хотите импортировать какой-то столбец (столбцы), который показан в области Data preview (Образец разбора данных), то выделите его и выберите вариант Do not import column (Пропустить столбец) в разделе Column data format (Формат данных столбца).

Разбиваем текст фиксированной ширины по нескольким столбцам

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

К примеру, есть список товаров с ID и наименованием, причем идентификатор товара – это 9 символов, которые стоят перед наименованием этого товара:

Вот что Вам нужно сделать, чтобы разбить такой столбец на два:

Разбиваем объединённые ячейки в Excel

Если Вы объединили несколько ячеек на листе Excel и теперь хотите вновь разбить их по отдельным столбцам, откройте вкладку Home (Главная) и в группе команд Alignment (Выравнивание) нажмите маленькую чёрную стрелку рядом с кнопкой Merge & Center (Объединить и поместить в центре). Далее из выпадающего списка выберите Unmerge Cells (Отменить объединение ячеек).

Таким образом объединение ячеек будет отменено, но удовольствие от результата будет испорчено тем, что все данные останутся в левом столбце. Думаю, Вы догадались, что нужно снова использовать функцию Text to Columns (Текст по столбцам), чтобы разбить данные из одного столбца на два или более столбцов.

Разделяем данные на несколько столбцов в Excel 2013 при помощи мгновенного заполнения

Если Вы уже обновились до Excel 2013, то можете воспользоваться преимуществами нового инструмента «Мгновенное заполнение » и заставить Excel автоматически заполнять (в нашем случае – разбивать) данные, при обнаружении определенной закономерности.

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

Таким образом, при помощи этого инструмента Вы можете взять какую-то часть данных, находящихся в одном или нескольких столбцах, и ввести их в новый столбец. Думаю, Вы лучше поймёте о чём я говорю из следующего примера.

Первым делом, убедитесь, что инструмент «Мгновенное заполнение» включен. Вы найдёте этот параметр на вкладке File (Файл) > Options (Параметры) > Advanced (Дополнительно) > Automatically Flash Fill (Автоматически выполнять мгновенное заполнение).

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

Как видите, я ввёл только пару имён в столбец B , и «Мгновенное заполнение» автоматически заполнило остальные ячейки именами из столбца A . Если вы довольны результатом, просто нажмите Enter , и весь столбец будет заполнен именами. Очень умный инструмент, не правда ли?

Если «Мгновенное заполнение» включено, но не предлагает никаких вариантов, которые соответствуют определённому шаблону, Вы можете запустить этот инструмент вручную на вкладке Data (Данные) > Flash Fill (Мгновенное заполнение) или нажав сочетание клавиш Ctrl+E .

Как в Excel разбивать ячейки при помощи формул

Существуют формулы, которые могут быть очень полезны, когда возникает необходимость разбить ячейки или столбцы с данными в Excel. На самом деле, следующих шести функций будет достаточно в большинстве случаев – LEFT (ЛЕВСИМВ), MID (ПСТР), RIGHT (ПРАВСИМВ), FIND (НАЙТИ), SEARCH (ПОИСК) и LEN (ДЛСТР). Далее в этом разделе я кратко объясню назначение каждой из этих функций и приведу примеры, которые Вы сможете использовать в своих книгах Excel.

Пример 1

Самая распространённая ситуация, когда могут понадобится эти формулы – это необходимость разделить имена из одного столбца по нескольким. На рисунке ниже показано, какого результата мы пытаемся достичь:

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

• Извлекаем имя (столбец First name):

  LEFT(A2,SEARCH(" ",A2,1)-1)
  =ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)-1)

• Извлекаем фамилию (столбец Last name):

  RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
  =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1))

Для тех, кому интересно, что означают эти формулы, я попробую объяснить более подробно.

SEARCH (ПОИСК) или FIND (НАЙТИ) – это абсолютно идентичные функции, которые выполняют поиск позиции определенной текстовой строки в заданной ячейке. Синтаксис формулы:

SEARCH(find_text,within_text,)
=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])

В качестве аргументов Вы должны указать: что нужно найти, где нужно искать, а также позицию символа, с которого следует начать поиск. В нашем примере SEARCH(” “,A2,1) или ПОИСК(” “;A2;1) говорит о том, что мы хотим найти символ пробела в ячейке A2 и начнём поиск с первого символа.

Замечание: Если поиск начинается с первого символа, Вы можете вообще пропустить аргумент start_num (нач_позиция) в формуле и упростить её до такого вида:

LEFT(A2,SEARCH(" ",A2)-1)

LEFT (ЛЕВСИМВ) и RIGHT (ПРАВСИМВ) – возвращает левую или правую часть текста из заданной ячейки соответственно. Синтаксис формулы:

LEFT(text,)
=ЛЕВСИМВ(текст;[количество_знаков])

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

LEFT(A2,SEARCH(" ",A2)-1)
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)

LEN (ДЛСТР) – считает длину строки, то есть количество символов в заданной ячейке. Синтаксис формулы:

LEN(text)
=ДЛСТР(текст)

Следующая формула считает количество символов в ячейке A2 :

LEN(A2)
=ДЛСТР(A2)

Если имена в Вашей таблице содержат отчества или суффиксы, то потребуются немного более сложные формулы с использованием функции MID (ПСТР).

Пример 2

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

• Извлекаем имя:

  LEFT(A2,FIND(" ",A2,1)-1)
  

• Извлекаем отчество:

  
  

• Извлекаем фамилию:

  
  

Функция MID (ПСТР) – извлекает часть текстовой строки (то есть заданное количество символов). Синтаксис:

MID(text,start_num,num_chars)
=ПСТР(текст;начальная_позиция;количество_знаков)

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

Пример 3

Вы можете использовать аналогичные формулы, чтобы разбить имена с суффиксами в конце:

• Извлекаем имя:

  LEFT(A2,FIND(" ",A2,1)-1)
  =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-1)

• Извлекаем фамилию:

  MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
  =ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1))

• Извлекаем суффикс:

  RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))
  =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))

Пример 4

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

• Извлекаем имя:

  MID(A2,SEARCH(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
  =ПСТР(A2;ПОИСК(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1))

• Извлекаем отчество:

  RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))
  =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))

• Извлекаем фамилию:

  LEFT(A2,FIND(" ",A2,1)-2)
  =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-2)

Пример 5

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

• Извлекаем название штата:

  LEFT(A2,SEARCH(",",A2)-1)
  =ЛЕВСИМВ(A2;ПОИСК(",";A2)-1)

• Извлекаем аббревиатуру штата:

  MID(A2,SEARCH(",",A2)+2,SEARCH(",",A2,SEARCH(",",A2)+2)-SEARCH(",",A2)-2)
  =ПСТР(A2;ПОИСК(",";A2)+2;ПОИСК(",";A2;ПОИСК(",";A2)+2)-ПОИСК(",";A2)-2)

• Извлекаем столицу штата:

  RIGHT(A2,LEN(A2)-(SEARCH(",",A2,SEARCH(",",A2)+1)+1))
  =ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(",";A2;ПОИСК(",";A2)+1)+1))

А вот пример реальных данных из Excel 2010. Данные из первого столбца разбиты на три отдельных столбца: