При работе с таблицами Excel иногда нужно разбить определенную ячейку на две части. Но, это не так просто, как кажется на первый взгляд. Давайте разберемся, как разбить ячейку на две части в программе Microsoft Excel, и как её разделить по диагонали.
Сразу нужно отметить, что ячейки в программе Microsoft Excel являются первичными структурными элементами, и они не подлежат делению на более мелкие части, если до этого не были объединены. Но, что делать, если нам, например, нужно создать сложную шапку таблицы, один из разделов который, делится на два подраздела? В этом случае, можно применить небольшие хитрости.
Способ 1: объединение ячеек
Для того, чтобы определенные ячейки казались разделенными, следует объединить другие ячейки таблицы.
Как видим, несмотря на то, что мы ничего не делили, а наоборот соединяли, создается иллюзия разделенной ячейки.
Способ 2: разделение объединенных ячеек
Если нам нужно разделить ячейку не в шапке, а в середине таблицы, то в этом случае, легче объединить все ячейки двух соседних столбцов, а уже потом произвести разделение нужной ячейки.
Таким образом, мы получили разделенную ячейку. Но, нужно учесть, что Excel воспринимает этим способом разделенную ячейку, как единый элемент.
Способ 3: разделение по диагонали путем форматирования
А вот, по диагонали можно разделить даже обычную ячейку.
После этого, ячейка будет разделена косой чертой по диагонали. Но, нужно учесть, что Excel воспринимает этим способом разделенную ячейку, как единый элемент.
Способ 4: разделение по диагонали через вставку фигуры
Следующий способ подойдет для разделения ячейки по диагонали только в том случае, если она большая, или создана путем объединения нескольких ячеек.
Как видим, несмотря на то, что в программе Microsoft Excel не существует стандартных способов разбить первичную ячейку на части, с помощью нескольких способов можно достигнуть требуемого результата.
Иногда нужно зрительно разделить ячейку таблицы Excel на две. Фактически делить ячейку в экселе нельзя, но добиться визуального разделения ячейки в таблице можно и таких способов несколько.
Для начала надо определиться, для каких целей нужно разделить ячейку. Если данные в разделенной ячейке будут использоваться по отдельности, то разделять ячейку можно методом объединением ячеек. Если же требуется просто визуально расчертить ячейку пополам, не используя данные в ней в дальнейшем, то алгоритм действий будет другой.
Делим ячейку на две методом объединения
Допустим, требуется создать такую таблицу.
Добиться этого можно объединив ячейки снизу и сверху. Для этого используем кнопу «объединить ячейки».
Останется только подобрать нужную ширину объединенных ячеек.
Разделенная на две части ячейка на самом деле состоит из двух столбцов.
Разделить ячейку на две: второй способ
Допустим, необходимо визуально разделить ячейку в экселе и каждая из половинок такой ячейки не будет использоваться в дальнейших расчетах.
Для создания такого эффекта используется вставка в ячейку автофигура «Линия».
Чтобы правильно расположить данные в ячейки сделаем изменения в настройке формата ячейки.
Разделить по горизонтали ячейку можно и еще одним способом. Кликом правой кнопки по ячейке вызываем редактор «Формат ячейки» и переходим на вкладку «Шрифт». Изменяем подчеркивание на «Одинарное, по ячейке».
Не забываем сделать выравнивание текста в «Формате ячеек» с теми же параметрами, как на предыдущем рисунке.
Как разделить ячейку в экселе на две по диагонали
Для этого тоже можно использовать два способа. Для первого понадобится вставка автофигуры «Линия», как описано выше. Только рисовать линию надо из угла в угол ячейки.
Для второго способа вызываем редактор «Формат ячеек» и переходим во вкладку «Граница». Нажав кнопку, как показано на рисунке, получим разделение ячейки эксель по диагонали.
Рассмотренные выше способы помогут разделить ячейку таблицы Excel на две. Сделать это, как уже понятно, несложно. Стоит только отметить тот факт, что объединение ячеек не всегда полезно. Если в разрабатываемом документе будет использоваться сортировка или различные циклы по ячейкам, то не рекомендуется объединять ячейки в таблице без особой надобности. В этом случае визуально разделить ячейку можно другими способами.
Вызов команды:
-группа Ячейки/Диапазоны
-Диапазоны
-Разбить по строкам/столбцам
Эта команда разбивает данные во всех выделенных ячейках на части, используя в качестве разделителя указанные символ/символы. Главная особенность - в отличие от стандартной команды Excel "Разбить по столбцам", данная команда заносит результат разбиения данных в строки либо столбцы в одну строку/столбец, а не каждую часть в свой столбец. К тому же в стандартной команде Excel нет возможности указать несколько символов - только один.
Диапазон значений
- указывается диапазон ячеек, данные которых необходимо разбить. Может быть как одним столбцом/строкой, так и диапазоном, состоящим из нескольких строк и нескольких столбцов.
Разделитель
- указывается символ (или группа символов)
, который будет применен в качестве разделителя. Если разделитель не указан, то появится сообщение, информирующее об этом. В сообщении будет предложено использовать разделитель по умолчанию (Пробел
)
. В случае отказа Вы сможете указать необходимый разделитель. Данные, внесенные ранее не будут утеряны.
Поместить результат:
Разделитель результата:
Доступно только если в качестве вывода результата указана одна ячейка. По умолчанию разделителем является перенос на строки. В качестве разделителя можно указать любой символ или группу символов.
Транспонировать - если Диапазон значений представляет собой столбец, то данные после обработки будут помещены в одну строку. Если Диапазон значений - строка, то данные будут помещены в столбец. Недоступно при выводе результата в одну ячейку.
Раннее мы рассматривали возможность на примере деления ФИО на составные части. Для этого мы использовали инструмент в Excel «Текст по столбцам».
Несомненно, это очень важный и полезный и инструмент в Excel, который значительно может упростить множество задач. Но у данного способа есть небольшой недостаток. Если вам, например, постоянно присылают данные в определенном виде, а вам постоянно необходимо их делить, то это занимает определенное время, кроме того, если данные вам прислали заново, то вам снова нужно будет проделать все операции.
Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию и , которую мы рассматривали в прошлых статьях. В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера.
У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.
Попробуем очень подробно описать план действия и разобьем решение задачи на несколько этапов.
Первым делом добавим вспомогательные столбцы, для промежуточных вычислений, чтобы вам было понятнее, а в конце все формулы объединим в одну.
Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу
НАЙТИ(" ";A2;1)
Теперь нам необходимо найти порядковый номер второго пробела. Формула будет такая же, но с небольшим отличием. Если прописать такую же формулу, то функция найдет нам первый пробел, а нам нужен второй пробел. Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст. Мы видим, что второй пробел находится в любом случае после первого пробела, а позицию первого пробела мы уже нашли, значит прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ искать пробел начиная с первой буквы после первого пробела. Функция будет выглядеть следующим образом:
Приступаем к делению первой части текста — Фамилии
Для этого мы воспользуемся функцией , напомню синтаксис данной функции:
ПСТР(текст ; начальная_позиция ; число_знаков ), где
- текст — это ФИО, в нашем примере это ячейка A2;
- начальная_позиция - в нашем случае это 1, то есть начиная с первой буквы;
- число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.
Формула будет выглядеть следующим образом:
ПСТР(A2 ;1 ;H2-1 )
Приступаем к делению второй части текста — Имя
Снова используем функцию =ПСТР(текст ; начальная_позиция ; число_знаков ), где
- текст — это тот же текст ФИО, в нашем примере это ячейка A2;
- начальная_позиция - в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1 ;
- число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2
Получаем итоговую формулу:
ПСТР(A2 ;H2+1 ;I2-H2 )
Приступаем к делению третьей части текста — Отчество
И снова функция =ПСТР(текст ; начальная_позиция ; число_знаков ), где
- текст — это тот же текст ФИО, в нашем примере это ячейка A2 ;
- начальная_позиция - Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1 ;
- число_знаков — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50
Получаем функцию
ПСТР(A2 ;I2+1 ;50 )
Далее выделяем все три ячейки и протягиваем формулы вниз и получаем нужный нам результат. На этом можно закончить, а можно промежуточные расчеты позиции пробелов прописать в сами формулы деления текста. Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 - НАЙТИ(" ";A2;1) , а расчет второго пробела в ячейке I2 — НАЙТИ(" ";A2;H2 +1) . Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу
Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается 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 для работы с текстом и как они позволяют делить текст автоматически с помощью формул однотипные данные. Если вам понравилась статья, то будем благодарны за нажатие на +1 и мне нравится. Подписывайтесь и вступайте в нашу группу вконтакте.
Пример 2. Как разделить текст по столбцам в Excel с помощью формулы
Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.
Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.
Напомним, что вручную (без формул) это задача очень просто решается с помощью , который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул.
Для начала необходимо найти общий разделить, по которому мы будет разбивать текст. В нашем случае это запятая, но например в первой задаче мы делили ФИО и разделитель был пробел. Наш второй пример более универсальный (более удобный при большом количестве данных), так например мы удобно могли бы делить не только ФИО по отдельным ячейкам, а целое предложение — каждое слово в отдельную ячейку. Собственно такой вопрос поступил в комментариях, поэтому было решено дополнить эту статью.
Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.
Теперь основная суть приема.
Шаг 1 . В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки
НАЙТИ(B1;A1;1 )
То есть ищем запятую, в тексте, начиная с позиции 1
НАЙТИ($B1 ;$A1;D1+1 )
Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 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 разделит клетку на составляющие ее ячейки.
При необходимости разбить ячейку, которая не является составной, придется проделать несколько большее число операций. Вам надо сделать составными соседние ячейки таблицы, создав впечатление, что нужная ячейка разбита на несколько секций. Если ячейку надо разделить горизонтально, объединять придется ячейки в строке, а для вертикального разбиения это надо проделать с ячейками колонки.
Начните с выделения при создании таблицы нужных элементов в строке или колонке. Например, если одна ячейка в колонке должна быть разбита на три вертикальные секции, выделите ячейки в трех соседних колонках на высоту таблицы.
Объедините по строкам выделенный диапазон. На вкладке «Главная» раскройте выпадающий список кнопки «Объединить и поместить в центре» из группы команд «Выравнивание» и выберите пункт «Объединить по строкам».
Измените ширину объединенной колонки - сделайте ее равной соседним столбцам. Для этого выделите все объединенные столбцы - в используемом примере их три. Наведите курсор на границу между заголовками любых двух выделенных колонок и перетащите ее до нужной ширины столбца. Горизонтальный размер всех выделенных колонок при этом будет изменяться синхронно.
Выделите ту ячейку, ради которой производятся все эти манипуляции. Еще раз раскройте выпадающий список кнопки «Объединить и поместить в центре», но на этот раз выберите команду «Отменить объединение ячеек». Это последняя операция, нужная ячейка после нее окажется разбитой на заданное количество частей. При необходимости разделять на горизонтальные секции все описанные действия надо производить со строками.
