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

Гледайте видео - Как да сортирате по фамилия в Excel

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

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

Но какво ще стане, ако искате сортирайте данните по фамилното име в Excel?

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

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

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

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

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

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

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

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

По -долу са стъпките за сортиране по фамилия:

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

Горните стъпки ще запазят фамилното име и ще премахнат всичко преди него. Това работи добре дори когато имате средни имена или префикси (като Mr. или Ms).

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

По -долу са стъпките за сортиране по фамилия:

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

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

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

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

Извличане и азбука по фамилия с помощта на формула

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

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

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

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

По -долу е формулата, която ще извлече фамилията от пълното име:

= НАДЯСНО (A2, LEN (A2) -FIND ("", A2))

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

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

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

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

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

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

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

= НАДЯСНО (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))

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

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

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

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

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

Text to Columns отново е лесен и лесен начин за разделяне на клетки в Excel.

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

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

По -долу са описани стъпките за използване на Текст в колона за сортиране по фамилното име:

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

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

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

Използване на Flash Fill

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

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

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

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

По -долу са описани стъпките за използване на Flash Fill за получаване на фамилията и след това сортиране с помощта на нея:

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

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

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

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

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

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

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