msoffice-prowork

Многие знают, что для того, чтобы объединить текст в двух ячейках достаточно воспользоваться функцией СЦЕПИТЬ (CONCATENATE), однако, как быть, если необходимо не объединить, а наоборот разделить текст в ячейке? Если количество символов, которое необходимо отделить известно (не важно справа или слева), тогда можно воспользоваться функциями ЛЕВСИМВ (LEFT) или ПРАВСИМВ (RIGHT), в зависимости с какой стороны необходимо выделить определенное количество символов.

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

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

Первый способ - супер быстрый.

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

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

В англоязычной версии данная команда звучит как "Text to Columns" вкладки "DATA".

После несложных подсказок мастера (на самом деле, в нашем примере после выбора разделителя - пробела можно смело жать "Готово")

А вот и, собственно, результат.

Второй способ - с использованием формул.

В такой ситуации понадобится сочетание функций: ПОИСК (SEARCH) и ПСТР (MID). Для начала, с помощью первой находим пробел между словами (между фамилией и именем и именем и отчеством), а потом подключаем вторую для того, чтобы выделить необходимое количество символов. Грубо говоря, первой функцией определяем количество символов, а второй - уже разделяем.

Кроме того, поскольку разделительного знака в конце строки нет, то количество символов в последнем слове (нашем случае - отчестве) вычислить не удастся, но это не проблема, достаточно указать заведомо большее количество символов в качестве аргумента "число знаков" функции ПСТР, например, 100.

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

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

Поскольку информация о количестве необходимых символах получена, следующим этапом будет использование функции ПСТР (MID).

Здесь, в качестве аргументов, используются промежуточные значения, полученные с помощью функции ПОИСК. Для последней колонки количество символов неизвестно, поэтому было взято заведомо бОльшее количество символов (в нашем случае 100).

Теперь попробуем соединить промежуточные расчеты в одну формулу.

Если текст в ячейке необходимо разделить лишь на две части, то необходимо произвести поиск лишь одного пробела (либо другого разделителя, который находится между словами), а для разделения на 4 и больше частей формулу придется усложнить поиском 3го, 4го и т.д. разделителей.

Приобрести продукты

Программа для редактирования данных. Информация в файле представлена в виде таблицы, которая состоит из строк и столбцов. Наименьшая единица измерения в документе Excel - ячейка. Эти элементы имеют условный номер, который определяется их отношением к столбцу и строке, например, A1 или D13. Вы можете изменять ширину и высоту ячеек, придавая им желаемый вид, чтобы форма, размер и соотношение сторон соответствовали требованиям. Кроме того, можно объединять соседние элементы с любой из сторон или отменять разделение, чтобы подстраивать структуры таблицы. К сожалению, так как ячейка является минимальной единицей файла в Excel, разделить её нельзя.

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

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

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

В некоторых случаях юзеру Excel нужно сделать так, чтобы в одном столбце было два или более значения для пересечения с одной из строк. Например, в том случае, когда та или иная вещь имеет несколько названий или номеров, а данные остальных умещаются в одну ячейку. Аналогично со строками, если один столбец включает в себя первичные категории (например, «программисты», «дизайнеры» и так далее), а другой - вторичные («программисты» - Иван, Пётр). Несмотря на то что в редакторе нельзя сделать это напрямую, ограничение можно обойти. Для этого нужно:

1. Заранее запланировать, какое максимальное количество значений будет включать в себя строка или столбец;
2. На этапе подготовки листа Excel к работе объединить те ячейки столбца или строки, которые будут использоваться как одинарные;
3. Таким образом, «разделённые» ячейки будут представлять собой самостоятельные элементы, а «целые» - соединённые, то есть результат получится визуальным (но он всё ещё будет соответствовать требованиям Excel).

Пример: в столбце A и строках 1–5 у вас расположено 5 фамилий, а соседний столбец B содержит должности этих людей в компании. Если один или несколько человек занимают 2 должности, впишите вторую в столбец C, а для остальных просто объедините B1 и C1, B2 и C2 и так далее. Аналогично в тех случаях, когда одному значению в первом столбце соответствует более 2 в последующих. Каждая ячейка будет иметь свой адрес в Excel и останется полностью функционирующей.

Как разбить ячейки, объединённые при планировании структуры

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

1. Откройте нужный лист, выделите все ячейки (или определённую часть) и перейдите во вкладку «Главная» на верхней панели Excel;
2. В области «Выравнивание» нажмите на стрелку и откройте выпадающий список с функциями «Объединить и поместить в центре», затем выберите «Отменить объединение ячеек»;
3. Элементы будут разбиты на одинарные, но все данные сместятся в левый верхний - распределить их по столбцам вы сможете с помощью функции «Текст по столбцам», которую мы рассмотрим дальше.

Как визуально разбить ячейку в таблице Excel, как разделить элемент по диагонали

Если вам достаточно разбить ячейку визуально, не изменяя её свойств и адреса в Excel, нужно:

1. Установить курсор на необходимый элемент либо выделить несколько (или весь лист).
2. Открыть вкладку «Главная», в области «Ячейки» кликнуть «Формат».
3. Откроется выпадающее меню, где вам нужно выбрать «Формат ячеек».
4. В новом окне необходимо перейти во вкладку «Граница» - здесь вы сможете самостоятельно нарисовать нужные рамки ячейки (вертикальные, горизонтальные и диагональные линии, несколько вариантов линий и множество цветов).
5. Есть и другой вариант - нужно кликнуть по выделенным ячейкам правой клавишей мыши, чтобы вызвать контекстное меню, затем выбрать «Формат ячеек», перейти во вкладку «Граница» и точно так же сделать линии.
6. Одна или несколько выбранных ячеек получит разметку, которую вы указали.

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

1. В окне «Формат ячеек», во вкладке «Граница» нарисовать любую диагональную линию, которая идёт из левого верхнего в правый нижний угол.
2. Применить форматирование.
3. Ввести текст в «верхнюю часть» ячейки (она разделена только визуально), которая будет соответствовать строке, например, «название»).
4. Выровнять его по левому или правому краю, расположить точнее с помощью пробелов.
5. Во время редактирования элемента нажать Alt + Enter, чтобы перейти на новую строку, затем ввести текст для столбца, например, «количество»;
6. Если текст расположен или выглядит не так, как нужно, необходимо поменять его местоположение с помощью пробела либо изменить размер и соотношение сторон ячеек.

Как разбить данные ячейки по столбцам таблицы Excel с помощью разделителя

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

Делается это так:

1. Создайте новые пустые столбцы, если справа от того, который содержит информацию, их недостаточно (их должно быть не меньше, чем количество категорий данных), иначе сведения будут записаны на другие, уже заполненные. Поставьте курсор мыши после искомого столбца на строке с латинскими буквами и нажмите по рамке таблицы правой клавишей мыши, в контекстном меню выберите «Вставить». Если нужно добавить несколько пустых столбцов, перед этим выберите аналогичное количество справа от искомого (нажмите на ячейку с латинской буквой и перетащите выделение).
2. Выделите тот столбец, который нужно разбить. Откройте «Данные» - «Работа с данными» - «Текст по столбцам».
3. В новом окне (Мастер распределения текста по столбцам) выберите формат данных. Если в искомом столбце информация разных категорий разделена пробелами или запятыми, выберите «С разделителями», если она имеет фиксированное количество данных - «Фиксированной ширины» (например, цифровой идентификатор - рассмотрим этот вариант позже), нажмите «Далее».
4. Затем укажите разделители, которые используются в массиве текста в столбце. Укажите их в «Символом-разделителем является» (если их несколько, перечислите все в поле «Другой»). Также укажите «Считать последовательные разделители одним», если в данных встречаются несколько типов подряд (например, два пробела подряд либо точка, которая говорит о сокращении слова, а не о конце предложения, и после неё идёт запятая).
5. Настройте ограничитель строк, если в тексте встречаются предложения, которые выделены кавычками, а в них встречаются разделители из прочего пункта, но разбивать их нельзя. К таким относятся предложения вроде «Россия, Москва» - адрес в таком случае должен оставаться целостным. Если вы не установите ограничитель, «Россия» и «Москва» окажутся в разных столбцах.
6. Выберите формат данных. По умолчанию он «Общий». Если в вашей информации есть даты или суммы денежных средств, укажите соответствующие для того столбцы, в которых они будут размещены. Здесь вы также можете указать, куда будут помещены те или иные данные. Кликните по иконке выбора диапазона справа от «Поместить в» и в качестве первого столбца укажите крайний левый из свободных, которые должны быть заполнены. К сожалению, данные нельзя переместить в другую книгу Excel и даже на другой лист, но вы можете разбить их на текущем, после чего просто скопировать в нужное место.
7. Нажмите «Готово» - все настройки будут применены. Сохраните документ, чтобы не утерять их.

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

Начиная с версии 2013 года, Microsoft Office Excel предлагает возможность утилизировать «Мгновенное заполнение». С помощью данной функции вы можете заставить редактор автоматически распределить данные в ячейки столбца , как только он заметит закономерность ввода.

Работает опция так: Excel начинает анализировать данные, которые вы вписываете в ячейки рабочего листа, и пытается выяснить, откуда они берутся, чему соответствуют и есть ли в них закономерность. Так, если в столбце A у вас указаны фамилии и имена людей, а в B вы вводите фамилии, с помощью «Мгновенного заполнения» утилита посчитает этот принцип и предложит автоматически распределить все значения в столбец B.

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

1. Убедитесь, что у вас активировано «Мгновенное заполнение» - оно находится во вкладке «Файл» - пункт «Параметры» - «Дополнительно» - «Автоматически выполнять мгновенное заполнение» (установите галочку, если её там нет).
2. Начните вводить в один из столбцов данные из другого, и редактор сам предложит распределить информацию массово. Если вам подходит то, что предлагает система Excel, нажмите Enter.
3. Если функция активирована, но не срабатывает в рамках определённого шаблона, запустите инструмент вручную в «Данные» - «Мгновенное заполнение» или нажмите Ctrl + «E».

Как распределить данные ячейки по столбцам таблицы Excel с помощью формул

В Excel есть формулы, которые позволяют упростить разбитие данных и сделать это более функционально. Как правило, обычно достаточно команд «ЛЕВСИМВ», «ПСТР», «ПРАВСИМВ», «НАЙТИ», «ПОИСК» И «ДЛСТР». Давайте рассмотрим, когда они нужны и как их использовать.

Как разделить имя и фамилию в 2 столбца

Один из самых частых случаев - потребность разделить имена и фамилии из столбца A в B и C соответственно. Для этого нужно сделать так, чтобы редактор сам нашёл пробел между значениями и разбил всё автоматически. Используйте формулу «=ЛЕВСИМВ(A2;ПОИСК(” “; A2;1)-1)». Она ищет пробелы в поисках, после чего принимает их за разделитель и отправляет, например, фамилии в левый из двух столбцов, а имена - в правый. Аналогично с другими значениями, которые разбиты пробелами. Указанная формула не подходит для более сложных ячеек, в том числе имён с фамилиями и отчествами, суффиксами и прочими данными.

Как разделить имя, фамилию и отчество в 3 столбца

Если вам нужно разбить на столбцы полное ФИО из трёх значений (при этом любое из них может быть только в виде буквенного инициала):

1. Используйте формулу «=ЛЕВСИМВ(A2;НАЙТИ(” “;A2;1)-1)», чтобы отделить имя;
2. Используйте «=ПСТР(A2;НАЙТИ(” “;A2;1)+1;НАЙТИ(” “;A2;НАЙТИ(” “;A2;1)+1)-(НАЙТИ(” “;A2;1)+1))», чтобы найти отчество (в записи вроде «Иванов Иван Иванович»)
3. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(” “;A2;НАЙТИ(” “;A2;1)+1))», чтобы извлечь фамилию.

Эти же формулы можно эксплуатировать для записей вроде «Иванов Иван младший» (на западный манер) или других, содержащих суффикс.

Как распределить данные, если они разделены запятой

Если данные в ячейках записаны в виде «Black, Bob Mark» (полное имя с фамилией спереди - в английском языке в таком случае требуется постановка запятой), разделить их в привычные нам «Bob Mark White» можно так:

1. Используйте «=ПСТР(A2;ПОИСК(” “;A2;1)+1;НАЙТИ(” “;A2;НАЙТИ(” “;A2;1)+1)-(НАЙТИ(” “;A2;1)+1))», чтобы выделить имя;
2. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(” “;A2;НАЙТИ(” “;A2;1)+1))», чтобы извлечь отчество;
3. Используйте «=ЛЕВСИМВ(A2;НАЙТИ(” “;A2;1)-2)», чтобы извлечь фамилию».

Другие формулы

Excel позволяет работать не только с именами и фамилиями людей, но и с другими типами данных . Другой часто встречающийся пример - адреса. Если в ячейке записана информация вида «Россия, Москва, улица Арбат», вы можете распределить значения по другим элементам, в качестве разделителя указав запятую, точку или другой произвольный символ. Чтобы разбить такой адрес из 3 частей (страна, город, улица):

1. Используйте «=ЛЕВСИМВ(A2;ПОИСК(“,”;A2)-1)», чтобы отделить страну;
2. Используйте «=ПСТР(A2;ПОИСК(“,”;A2)+2;ПОИСК(“,”;A2;ПОИСК(“,”;A2)+2)-ПОИСК(“,”;A2)-2)», чтобы выделить город;
3. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(“,”;A2;ПОИСК(“,”;A2)+1)+1))», чтобы отделить улицу.

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

Итог

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

Добрый день уважаемый читатель!

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

Разобрать слитый текст на необходимые составляющие возможно произвести с помощью:

Мастер разбора текстов

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

Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:

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

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

Рассоединяем текст с помощью формул

Для этого способа нам понадобятся возможности сочетаний функций ПОИСК и ПСТР. При помощи мы будем искать все пробелы, которые есть между словами (например, между фамилией, именем и отчеством). Потом выделяем необходимое количество символов для дальнейшего разделения.

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:

• Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(" ";B2;1), а для второго подойдет: =ПОИСК(" ";B2;C2+1);
• Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(" ";B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(" ";B2;1)+1;ПОИСК(" ";B2;ПОИСК(" ";B2;1)+1) -ПОИСК(" ";B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(" ";B2;ПОИСК(" ";B2;1)+1)+1;100).

В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

Выдергиваем слова с помощью макросов VBA

Рассмотрим два способа разделить текст в ячейке:

1. Выдергиваем отдельные слова по разделителю;
2. Делим текст без пробелов.

Способ №1.

Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью Alt+F11. (детальнее в статье « »).

Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

Function Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1 <= UBound(x) Then Substring = x(n - 1) Else Substring = "" End If End Function

Function Substring (Txt , Delimiter , n ) As String Dim x As Variant x = Split (Txt , Delimiter ) If n > 0 And n - 1 <= UBound (x ) Then Substring = x (n - 1 ) Else Substring = "" End If End Function

Теперь эта функция появилась в «Мастер функций» в разделе «Определенные пользователем». Синтаксис созданной функции очень прост и выглядит так:

SUBSTRING (_Txt_;_Delimeter_;_n_), где:

• – указываем ячейку текст в которой будем делить;
• – это символ который служит разделителем (запятая, тире, пробел и т.д);
• – ставим номер по порядку фрагмента, которой будем извлекать.

Пример:

Рассмотрим, как возможно изъять текст по разнообразным символам-разделителям. Для удобства и лучшей автоматизации не забывайтесь значения формулы .
Способ №2.

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

В этом поможет небольшая функция, которая автоматически сможет вставлять «пробел» перед всеми заглавными буквами в тексте. Используя Visual Basic, создайте новый модуль, как описано в первом способе и добавьте в него код:

Function CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Like "" And Mid(Txt, i + 1, 1) Like "" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End Function

Function CutWords (Txt As Range ) As String Dim Out $ If Len (Txt ) = 0 Then Exit Function Out = Mid (Txt , 1 , 1 ) For i = 2 To Len (Txt ) If Mid (Txt , i , 1 ) Like "" And Mid (Txt , i + 1 , 1 ) Like "" Then

Вызов команды:
-группа Ячейки/Диапазоны -Диапазоны -Разбить по строкам/столбцам

Эта команда разбивает данные во всех выделенных ячейках на части, используя в качестве разделителя указанные символ/символы. Главная особенность - в отличие от стандартной команды Excel "Разбить по столбцам", данная команда заносит результат разбиения данных в строки либо столбцы в одну строку/столбец, а не каждую часть в свой столбец. К тому же в стандартной команде 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 не представляет собой ничего сложного. Главное помнить, что при вычислении формул «Эксель» использует стандартные правила математики.