Как да сортирате по фамилно име в Excel (Лесно ръководство)

Ако работите с набор от имена, сортирането му е една от обичайните задачи, които трябва да изпълнявате често.

Лесно е да сортирате данните по азбучен ред по пълно име, където Excel използва първия знак от името за сортиране.

Въпреки това, ако искатеСортирайте данните по фамилия в Excelкачи сепоследователносткакво да правя?

Въпреки че не е толкова просто, все още може да се направи (също зависи много от това как са структурирани данните за името).

Който и метод да използвате, трябва по някакъв начин да извлечете фамилното име от пълното име и да го поставите в отделна колона.След това можете да използвате тази колона, за да сортирате данните по азбучен ред по фамилия.

В този урок за Excel ще ви покажа как да сортирате колона с имена въз основа на фамилията.

Така че нека започваме!

Извлечете и сортирайте по фамилия с помощта на Find and Replace

Първата стъпка за сортиране по фамилно име е да получите фамилното име в отделна колона.

Можете да направите това, като замените всичко преди фамилното име с празни места, така че да останете само с фамилното име.

Да предположим, че имате набор от данни като този по-долу и искате да сортирате тези данни по азбучен ред по фамилия.

Ето стъпките за сортиране по фамилия:

1. Изберете набора от данни, който съдържа заглавието (в този случай ще бъде A1:A10)
2. копирайте го в съседната колона (ако съседната колона не е празна, вмъкнете нова колона, след което копирайте имената)
3. Преименувайте копираните заглавки на колони.В този пример ще го кръстя „Фамилно име“
4. Изберете всички копирани имена (не избирайте заглавия)
5. Задръжте натиснат клавиша Control и след това натиснете клавиша H.Това ще отвори диалоговия прозорец Търсене и замяна.
6. В полето Find what въведете * (звездичка, последвана от знак за интервал)
7. Оставете полето Замяна с празно
8. Щракнете върху Замяна на всички.Това ще замени всички имена наведнъж и ще останете само с фамилни имена.

Горните стъпки ще запазят фамилното име и ще изтрият всичко преди него.Това работи дори ако имате средно име или префикс като г-н или г-жа.

След като имате фамилни имена в съседни колони, можете лесно да сортирате набора от данни (включително пълните имена) по азбучен ред по фамилия.

Ето стъпките за сортиране по фамилия:

1. Изберете целия набор от данни със заглавия (включително пълни имена и извлечени фамилни имена).Можете също да включите други колони за сортиране с името
2. Щракнете върху раздела Данни
3. Кликнете, за да сортирате
4. В диалоговия прозорец Сортиране се уверете, че Моите данни имат заглавки е отметнато.
5. В опцията "Сортиране по" изберете името на колоната само с фамилия
6. В Сортиране по изберете Стойност на клетката
7. В опцията "Поръчка" изберете "А до Я"
8. Щракнете върху OK

Горните стъпки ще сортират целия избран набор от данни въз основа на фамилията.

След като сте готови, можете да изпуснете колоната с фамилното име.

Професионални съвети: Във всеки момент, в който смятате, че може да се наложи да се върнете към оригиналните данни, трябва да имате начин да премахнете сортирането на този набор от данни.За това в съседни колони (лява или дясна) има поредни номера преди сортиране.Сега, ако трябва да върнете оригиналните данни, можете да ги получите чрез сортиране въз основа на числа.

Извлечете и сортирайте по азбучен ред по фамилно име, като използвате формула

Докато методът, показан по-горе (използване на намиране и замяна), е това, което предпочитам да получа всички фамилни имена и да сортирам въз основа на него, едно от неговите ограничения е, че получените данни са статични.

Това означава, че ако добавя още собствени имена към списъка, ще трябва да премина отново през същия процес, за да получа фамилното име.

Ако това не е това, което искате, можете да използвате метод на формула, за да сортирате данните по фамилно име.

Да предположим, че имате набора от данни, показан по-долу.

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

=ДЯСНО(A2,LEN(A2)-НАМИРАНЕ(" ",A2))

Горната формула разчита на модел с пълни имена (в този пример само име и фамилия).Моделът е между името и фамилията да има интервал.

Функцията FIND се използва за получаване на позицията на знака за интервал.След това тази стойност се изважда от общата дължина на първото име, за да се получи общият брой знаци във фамилното име.

След това използвайте тази стойност във функцията RIGHT, за да получите фамилното име.

След като имате колоната за фамилно име, можете да сортирате тези данни (това е описано подробно в първия метод).

Горната формула ще работи, когато имате само име и фамилия.

Но какво ще стане, ако имате и второ име.или може да има поздрав пред името (напр. г-н или г-жа)

В този случай трябва да използвате следната формула:

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

Горната формула намира позицията на последния знак за интервал и след това го използва, за да извлече фамилното име.

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

Забележка: Тези две формули разчитат на условието, че има само един интервал между всеки елемент на името.Тази формула ще даде неправилни резултати, ако има двойни интервали или начални/крайни интервали.В този случай най-добре е първо да използвате функцията TRIM, за да премахнете всички водещи, завършващи и двойни интервали и след това да използвате горната формула.

Въпреки че това може да изглежда като сложен подход, ползата от използването на формули е, че прави резултатите динамични.Ако добавите още имена към списъка, всичко, което трябва да направите, е да копирате формулата и тя ще ви даде фамилията.

Използвайте текст към колони

Текст към колони е още един лесен начин за разделяне на клетки в Excel.

Можете да посочите разделител (като запетая или интервал) и да го използвате, за да разделите съдържанието на клетка.След като разделите елементите в отделни колони, можете да използвате колоната с фамилното име, за да подредите данните по азбучен ред.

Да предположим, че имате набор от данни, който изглежда така:

Ето стъпките за сортиране по фамилно име с помощта на Текст в колона:

1. Изберете колони с имена (без заглавки)
2. Щракнете върху раздела Данни
3. В групата Инструменти за данни щракнете върху опцията Текст към колони.Това ще отвори съветника Текст към колони
4. В стъпка 1 от съветника за преобразуване на текст в колони изберете Разделени и щракнете върху Напред
5. В стъпка 2 изберете „Интервал“ като разделител (премахнете отметката от всичко друго, ако е отметнато) и щракнете върху бутона Напред.
6. В стъпка 3 изберете първата колона с име във визуализацията на данните, след което изберете опцията „Не импортирайте колона (пропускане)“.Това гарантира, че първото име не е част от резултата, получавате само фамилията.
7. Също така в стъпка 3 променете целевата клетка на клетката, съседна на оригиналните данни.Това ще гарантира, че ще получите само фамилното име и че оригиналните данни за името са пълни.
8. Щракнете върху Готово

След като получите резултатите, можете да сортирате по фамилия.

Можете също да използвате текст към колона, за да разделите имената и фамилиите, когато имате запетаи като разделители.

Напълнете с блясък

Друг бърз начин да получите фамилното име е да използвате функцията Flash Fill.

Въведен в Excel 2013, Flash Fill помага за манипулиране на данни чрез идентифициране на модели.За да направите това, трябва да покажете Flash Fill няколко пъти за резултата, който очаквате.

След като разпознае модела, той бързо ще направи останалото вместо вас.

Да предположим, че имате следния набор от имена.

Ето стъпките, за да получите фамилното име с помощта на Flash Fill и да го използвате за сортиране:

1. В клетка B2 въведете текста "Maury".Това е, което бихте очаквали в клетка.
2. Отидете до следващата клетка и въведете фамилното име на името в съседната клетка (Elliot в този случай).
3. изберете две клетки
4. Задръжте курсора върху долната дясна част на селекцията.Ще забележите, че курсорът се променя на икона плюс.
5. Щракнете двукратно върху него (или щракнете и плъзнете надолу).Това ще ви даде някакъв резултат в клетката (малко вероятно този, който искате)
6. Щракнете върху иконата Опции за автоматично попълване.
7. Щракнете върху Бързо попълване

Това ще ви даде резултат, който може да е последното име във всички клетки.

Казвам може би, защото Flash Fill може да не работи в някои случаи.Тъй като зависи от режима на разпознаване, може не винаги да прави това.Или понякога моделът, който дешифрира, може да не е правилният модел.

В този случай трябва да въведете очаквания резултат в една или повече клетки и след това да преминете през стъпки 4-7.

След като имате всички фамилни имена в колоната, можете да сортирате данните въз основа на тези фамилни имена.

Следователно можете да сортирате данните по фамилно име по четири различни начина.Най-добрият начин е да използвате техника за намиране и замяна, но ако искате да направите резултатите си динамични, методът на формулата е правилният начин.

Надяваме се, че сте намерили този урок за полезен.