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

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

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

Но что, если ты хочешь сортировать данные по фамилии в Excel?

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

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

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

Итак, приступим!

Извлечь и отсортировать по фамилии с помощью функции поиска и замены

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

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

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

Ниже приведены шаги для сортировки по фамилии:

1. Выберите набор данных, включая заголовок (в этом примере это будет A1: A10)
2. Скопируйте его в соседний столбец (если соседний столбец не пустой, вставьте новый столбец, а затем скопируйте эти имена)
3. Переименуйте скопированный заголовок столбца. В этом примере я назову «Фамилия».
4. Выделите все скопированные имена (не выбирайте заголовок)
5. Удерживая клавишу Ctrl, нажмите клавишу H. Откроется диалоговое окно «Найти и заменить».
6. В поле «Найти» введите * (символ звездочки, за которым следует пробел).
7. Оставьте поле «Заменить на» пустым.
8. Нажмите «Заменить все». Это мгновенно заменит все имя, и у вас останутся только фамилии.

Вышеупомянутые шаги сохранят фамилию и удалят все, что перед ней. Это хорошо работает, даже если у вас есть отчество или префиксы (например, Mr. или Ms).

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

Ниже приведены шаги для сортировки по фамилии:

1. Выберите весь набор данных с заголовками (включая полные имена и извлеченные фамилии). Вы также можете включить другие столбцы, которые хотите отсортировать, вместе с именами
2. Перейдите на вкладку «Данные».
3. Нажмите на Сортировать
4. Убедитесь, что в диалоговом окне «Сортировка» выбрано «Мои данные имеют заголовки».
5. В параметре «Сортировать по» выберите имя столбца, в котором есть только фамилия.
6. В поле «Сортировка по» выберите «Значения ячеек».
7. В разделе «Порядок» выберите «От А до Я».
8. Нажмите ОК.

Вышеупомянутые шаги сортируют весь выбранный набор данных по фамилии.

После этого вы можете удалить столбец с фамилией.

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

Извлечь и расположить в алфавитном порядке по фамилии с помощью формулы

Хотя я предпочитаю получать все фамилии и сортировать на их основе метод, показанный выше (с использованием поиска и замены), одним из его ограничений является то, что результирующие данные статичны.

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

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

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

Ниже приведена формула, которая извлечет фамилию из полного имени:
= ВПРАВО (A2; LEN (A2) -FIND (""; A2))

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

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

Это значение затем используется в функции ВПРАВО для получения фамилии.

Когда у вас есть столбец с фамилией, вы можете отсортировать эти данные (это подробно рассматривается в первом методе).

Приведенная выше формула будет работать, если у вас есть только имя и фамилия.

Но что, если у вас есть отчество? Или перед именем может стоять приветствие (например, г-н или г-жа).

В таком случае вам нужно использовать следующую формулу:
= ПРАВО (A2; LEN (A2) -НАЙТИ ("@", ПОДСТАВИТЬ (A2, "", "@", LEN (A2) -LEN (ПОДСТАВИТЬ (A2, "", ""))))))
Приведенная выше формула находит позицию последнего символа пробела и затем использует ее для извлечения фамилии.

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

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

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

Использование текста в столбцы

Текст в столбцы — это снова простой и легкий способ разбить ячейки в Excel.

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

Предположим, у вас есть набор данных, как показано ниже:

Ниже приведены шаги по использованию текста в столбец для сортировки по фамилии:

1. Выберите столбец с именем (без заголовка)
2. Перейдите на вкладку «Данные».
3. В группе «Инструменты для работы с данными» выберите параметр «Преобразование текста в столбцы». Откроется мастер текста в столбцы.
4. На шаге 1 «Мастера преобразования текста в столбцы» выберите «С разделителями» и нажмите «Далее».
5. На шаге 2 выберите «Пробел» в качестве разделителя (и снимите все флажки, если они выбраны), а затем нажмите кнопку «Далее».
6. На шаге 3 выберите первый столбец имени в окне предварительного просмотра данных, а затем выберите параметр «Не импортировать столбцы (пропустить)». Это гарантирует, что имя не будет частью результата, а вы получите только фамилию.
7. Также на шаге 3 измените целевую ячейку на ту, которая находится рядом с исходными данными. Это гарантирует, что вы получите фамилию отдельно, а исходные данные об именах останутся нетронутыми.
8. Нажмите Готово

Получив результат, вы можете отсортировать его по фамилии.

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

Использование Flash Fill

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

Flash Fill был представлен в Excel 2013 и помогает манипулировать данными путем выявления шаблонов. Чтобы это сработало, вам нужно пару раз показать ожидаемый результат Flash Fill.

Как только он определит шаблон, он быстро сделает остальную работу за вас.

Предположим, у вас есть набор данных с именами ниже.

Ниже приведены шаги по использованию Flash Fill для получения фамилии и последующей сортировки по ней:

1. В ячейке B2 введите текст «Мори». Это результат, который вы ожидаете в ячейке.
2. Перейдите в следующую ячейку и введите фамилию для имени в соседней ячейке (Эллиот в этом примере).
3. Выберите обе ячейки
4. Наведите курсор на правую нижнюю часть выделения. Вы заметите, что курсор изменится на значок плюса.
5. Дважды щелкните по нему (или щелкните и перетащите его вниз). Это даст вам какой-то результат в ячейках (вряд ли это будет тот результат, который вам нужен)
6. Щелкните значок «Параметры автозаполнения».
7. Нажмите на Flash Fill.

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

Я говорю «вероятно», поскольку в некоторых случаях Flash Fill может не работать. Поскольку это зависит от определения шаблона, это не всегда удается. Или иногда, шаблон, который он расшифровывает, может быть неправильным.

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

Когда у вас есть все фамилии в столбце, вы можете отсортировать данные на основе этих фамилий.

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

Надеюсь, вы нашли этот урок полезным.