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

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

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

Однако, если вы хотитеСортировка данных по фамилии в Excelпровестипоследовательность怎麼 辦？

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

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

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

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

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

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

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

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

Вот шаги для сортировки по фамилии:

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

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

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

Вот шаги для сортировки по фамилии:

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

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

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

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

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

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

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

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

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

Вот формула извлечения фамилии из полного имени:

= ПРАВО (A2; LEN (A2) -FIND (""; A2))

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

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

Затем используйте это значение в функции RIGHT, чтобы получить фамилию.

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

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

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

В этом случае нужно использовать следующую формулу:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Приведенная выше формула находит позицию последнего пробела, а затем использует ее для извлечения фамилии.

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

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

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

Использовать текст в столбцах

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

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

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

Вот шаги для сортировки по фамилии с помощью Text to Column:

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

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

Вы также можете использовать text-to-column для разделения имен и фамилий, если в качестве разделителей используются запятые.

Наполнить блестками

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

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

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

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

Вот шаги, чтобы получить фамилию с помощью Flash Fill и использовать ее для сортировки:

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

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

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

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

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

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

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