Разделение ФИО в Excel на три столбца¶
Разделение ФИО в Excel на три столбца — это преобразование данных, при котором строка, содержащая фамилию, имя и отчество (обычно записанные через пробел в одной ячейке), раскладывается на три отдельных столбца. Операция выполняется средствами Microsoft Excel и применяется при обработке списков сотрудников, клиентов, студентов и других персонифицированных данных.
¶Способы разделения
В Excel предусмотрено несколько подходов к разделению текста: инструмент «Текст по столбцам», формулы (ЛЕВСИМВ, ПРАВСИМВ, НАЙТИ, СРЕЗ), функция ПРОБЕЛЫ (для Excel 365 и Excel онлайн) и Power Query. Выбор зависит от версии программы, объёма данных и требований к результату.
¶Инструмент «Текст по столбцам»
Самый распространённый способ — мастер «Текст по столбцам» (в русской локализации Excel — «Данные» → «Текст по столбцам»).
Порядок действий:
- Выделить диапазон с ФИО.
- На вкладке «Данные» нажать кнопку «Текст по столбцам».
- В мастере выбрать тип разделителя «Пробел».
- Нажать «Далее», затем «Готово».
Результат: содержимое ячейки раскладывается по соседним столбцам справа. Если справа уже есть данные, Excel предупредит о перезаписи.
¶Особенности работы с отчеством
Если в исходных данных записаны только фамилия и имя (без отчества), третий столбец останется пустым. Если отчество записано с дефисом (например, «Петров-Водкин Михаил Александрович»), мастер может разбить его на два столбца. В таких случаях используют ручную корректировку или формулы.
¶Разделение с помощью формул
Для автоматического разделения при последующем добавлении новых строк удобнее использовать формулы. Пусть в ячейке A1 записано полное ФИО.
¶Фамилия
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1) — извлекает текст до первого пробела.
¶Имя
=СРЕЗ(A1;НАЙТИ(" ";A1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1)+1)-НАЙТИ(" ";A1)-1) — извлекает текст между первым и вторым пробелами.
¶Отчество
=ПРАВСИМВ(A1;ДЛИНСТРОКИ(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1)+1)) — извлекает всё после второго пробела.
Формулы корректно работают при условии, что в ФИО нет лишних пробелов в начале или конце строки. Для их удаления применяется функция =СЖПРОБЕЛЫ(A1).
¶Функция ПРОБЕЛЫ (Excel 365)
В актуальных версиях Microsoft 365 появилась функция ПРОБЕЛЫ (SPLIT), которая делит текст по указанному разделителю и возвращает массив значений. Формула =ПРОБЕЛЫ(A1;" ") автоматически раскладывает ФИО по соседним ячейкам. Функция доступна в Excel 365, Excel онлайн и некоторых версиях Excel для Mac, но отсутствует в настольных версиях Excel 2016 и 2019.
¶Power Query
Для больших массивов данных (тысячи строк) используется встроенный редактор Power Query:
- Выделить диапазон и выбрать «Данные» → «Из таблицы/диапазона».
- В редакторе выделить столбец с ФИО.
- Выбрать «Разделить столбцы» → «По разделителю» → «Пробел».
- Указать количество столбцов (3) или выбрать «По пробелам слева направо».
- Нажать «Закрыть и загрузить».
Power Query запоминает шаги преобразования: при обновлении источника данных разделение выполняется автоматически.
¶VBA-макрос
Для массовой обработки можно записать макрос на VBA. Пример кода для выделения фамилии, имени и отчества из столбца A в столбцы B, C, D:
``vba Sub SplitFIO() Dim i As Long, parts() As String For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row parts = Split(Trim(Cells(i, 1).Value), " ") Cells(i, 2).Value = parts(0) If UBound(parts) >= 1 Then Cells(i, 3).Value = parts(1) If UBound(parts) >= 2 Then Cells(i, 4).Value = parts(2) Next i End Sub ``
Макрос устойчив к строкам без отчества: обращение к элементам массива проверяется через функцию UBound.
¶Типичные ошибки
- Лишние пробелы между словами приводят к появлению пустых столбцов — устраняется функцией СЖПРОБЕЛЫ.
- Данные записаны в формате «Имя Фамилия» вместо «Фамилия Имя» — порядок столбцов меняется местами.
- Смешанный формат записей в одном столбце — требует предварительной сортировки или ручной правки.
- Использование «Текст по столбцам» при наличии в ФИО дефисов — дефис не является разделителем, но при выборе типа «Пробел» проблем не возникает.
¶Применение
Разделённые ФИО используются для сортировки по фамилиям, формирования почтовых рассылок, объединения с базами данных, подготовки отчётов и печати документов. Для обратного объединения применяется функция СЦЕПИТЬ или оператор «&»: =B1&" "&C1&" "&D1.
¶Источники
- Документация Microsoft Excel — support.microsoft.com
- «Excel 2019: Подробный курс», Джоан Ламберт, Джойс Кокс
- «Большая книга Excel», Александр Фролов