Как отсортировать по фамилии в Excel (простое руководство)

Если вы работаете с наборами данных имён, сортировка — одна из распространённых задач, которые вам придётся часто выполнять.

Сортировать данные в алфавитном порядке по полному имени довольно просто, а Excel использует первый символ имени для сортировки. Но что, если вы хотите сортировать данные по фамилии в Excel?

Хотя это не так просто, но всё же можно сделать (многое также зависит от того, как структурированы данные об именах). Независимо от того, какой метод вы используете, вам придётся каким-то образом извлечь фамилию из полного имени и поместить её в отдельный столбец. Затем этот столбец можно использовать для сортировки данных по фамилии в алфавитном порядке.

Способ 1: Извлечь и отсортировать по фамилии через «Найти и заменить»

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

1

Выберите набор данных, включая заголовок (например, A1:A10)

2

Скопируйте его в соседний столбец (если он не пустой — сначала вставьте новый столбец)

3

Переименуйте скопированный заголовок столбца, например «Фамилия»

4

Выделите все скопированные имена (без заголовка)

5

Удерживая клавишу Ctrl, нажмите H — откроется диалоговое окно «Найти и заменить»

6

В поле «Найти» введите *  (символ звёздочки, за которым следует пробел)

7

Оставьте поле «Заменить на» пустым

8

Нажмите «Заменить все» — это мгновенно удалит имя, оставив только фамилии

Когда фамилии окажутся в соседнем столбце, можно легко отсортировать набор данных (включая полные имена) в алфавитном порядке по фамилии:

После сортировки столбец с фамилией можно удалить.

Способ 2: Извлечь фамилию с помощью формулы

Метод «Найти и заменить» даёт статичный результат — если добавить новые имена в список, процесс придётся повторить заново. Если это неудобно, используйте формулу.

Формула, которая извлечёт фамилию из полного имени (для случая только «имя + фамилия»):

=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2))

Формула основана на том, что между именем и фамилией есть пробел. Функция НАЙТИ определяет позицию пробела, это значение вычитается из общей длины строки, чтобы получить количество символов фамилии, а функция ПРАВСИМВ извлекает их.

Но что если есть отчество, или перед именем стоит обращение (например, «г-н» или «г-жа»)? В таком случае нужна формула посложнее — она находит позицию последнего пробела:

=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("@";ПОДСТАВИТЬ(A2;" ";"@";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;" ";"")))))

Преимущество формулы — результат динамичен. Если добавить новые имена в список, достаточно скопировать формулу вниз, и она сразу даст фамилию.

Способ 3: Текст по столбцам

«Текст по столбцам» — ещё один простой способ разбить содержимое ячеек в Excel. Можно указать разделитель (например, запятую или пробел) и разбить содержимое ячейки на отдельные столбцы. Затем полученный столбец с фамилией используется для сортировки в алфавитном порядке.

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

Способ 4: Flash Fill

Ещё один быстрый способ получить фамилии — использовать Flash Fill (Мгновенное заполнение). Эта функция появилась в Excel 2013 и помогает распознавать шаблон в данных, показанный вами пару раз вручную, а затем достраивает результат сама.

Итак, это четыре разных способа сортировки данных по фамилии. Метод «Найти и заменить» — самый быстрый, но если нужен динамичный результат — лучше подойдёт метод с формулой.