Това ръководство ще ви покаже как да подредите по азбучен ред в Excel с помощта на функцията за сортиране и филтриране и формули за подреждане и форматиране на данните в ред.
Microsoft Excel е ефективен софтуер за организиране на важни данни, като финанси, данни за продажби, информация за клиенти и т.н. Подреждането по азбучен ред на данните в работни листове е един от най-добрите начини за бързо организиране, достъп и справка с тези данни. Азбучен ред на диапазон от клетки означава сортиране на диапазон по азбучен ред в excel. Данните могат да бъдат сортирани и по двата начина, или във възходящ ред, или в низходящ ред.
Например, да приемем, че имате голям набор от данни за информация за поръчките на клиента и искате да намерите историята на поръчките на конкретен клиент. Разресването на целия списък ще стане доста скучна работа. Вместо да губите време за превъртане, можете да подредите колоните си по азбучен ред, за да намерите бързо данните си.
Има различни начини, които можете да използвате за азбучен ред на данните в Excel, в зависимост от типа набор от данни, върху който работите. Excel ви позволява да сортирате една колона или един ред, или избран диапазон или цял работен лист, както и опции за групиране на няколко колони или редове заедно, докато ги сортирате. Нека научим как да подреждаме данните в електронни таблици на Excel по редове или колони.
Защо да подреждате данните по азбучен ред в Excel
Азбучен ред на данните ще има много предимства:
- Това прави данните по-разумни и улеснява четенето им.
- Това улеснява потребителя да търси конкретна стойност или име на клиент в лист с данни на Excel.
- Той помага на потребителя да идентифицира визуално дублиращи се записи, за да можем да предотвратим грешки при въвеждане на данни.
- Той позволява на потребителите лесно да групират колоните или списъците заедно, така че потребителите да могат да ги разглеждат един до друг.
Подреждането по азбучен ред (сортиране по азбучен ред) е лесен, често срещан начин за бързо сортиране на вашата информация. Той е особено полезен за много големи масиви от данни. Като цяло има 4 начина за азбучен ред на данните в Excel: бутон A-Z или Z-A, функция за сортиране, функция за филтриране и формули.
Азбучен ред на колона в Excel
Най-бързият начин за сортиране по азбучен ред в Excel е с помощта на вградената функция за сортиране.
Първо отворете електронната таблица, която съдържа набора от данни. Изберете клетките или колоната, които искате да сортирате, и се уверете, че не сте избрали празни клетки. След това отидете в раздела „Данни“ и щракнете върху „A-Z“, за да сортирате възходящо, или „Z-A“, за да сортирате низходящо в групата Сортиране и филтриране.
Ако има празни клетки между тях, Excel приема, че всички данни са сортирани и спира над черната клетка. Ако искате да сортирате цялата колона, просто изберете която и да е клетка в колоната и използвайте горната опция.
Същите опции могат да бъдат достъпни и под инструмента „Сортиране и филтриране“ в секцията „Редактиране“ на раздела „Начало“.
След като щракнете върху бутона, excel незабавно подредете списъка си по азбучен ред.
Как да сортирате и поддържате редовете заедно в Excel
Можете лесно да сортирате колона, ако имате само една колона в работния си лист, но става малко сложно, когато вашият работен лист съдържа няколко колони до колоната, която искате да сортирате. Също така понякога работим върху електронни таблици, където трябва да запазим съответните редове непокътнати след сортиране на колоната.
Например, ако имаме електронна таблица със списък с оценки на учениците. Когато сортираме колоната с имена на ученици по азбучен ред, искаме също така и оценките, въведени в редовете до тях, също да се движат съответно.
В такива случаи можете да използвате бутона „A-Z“ или „Z-A“, за да сортирате една от колоните по азбучен ред и тя автоматично ще подреди данните в други колони, запазвайки редовете заедно. Нека видим как става това с друг пример.
Първо изберете първата колона, според която искате да сортирате други колони. След това изберете „A-Z“ или „Z-A“ в раздела „Данни“ или раздела „Начало“, за да сортирате данните в една колона и Excel автоматично ще пренареди съответно другите колони.
След като щракнете върху бутона за сортиране, ще се появи диалогов прозорец с предупреждение за сортиране. Изберете бутона за избор за „Разширяване на избора“ и щракнете върху бутона „Сортиране“.
Това ще запази редовете заедно, докато сортирате колони, без да има несъответствие на данни (както е показано по-долу).
Ако изберете опцията „Продължи с текущата селекция“, Excel ще сортира само избраната колона.
Как да сортирате по азбучен ред по множество колони
Ако искате да сортирате по азбучен ред набор от данни по повече от една колона, можете да направите това с функцията за сортиране на Excel.
Например, искаме да подредим следната таблица по азбучен ред първо по държава, а след това по име:
Първо изберете цялата таблица, която искате да сортирате по азбучен ред, след това отидете на раздела „Данни“ и в групата Сортиране и филтриране щракнете върху „Сортиране“.
Или щракнете върху иконата „Сортиране и филтриране“ в секцията „Редактиране“ на раздела „Начало“ и изберете „Персонализирано сортиране“, за да получите достъп до същата функция.
Ще се покаже диалоговият прозорец Сортиране и ще покаже първата колона (параметър), в която ще бъде сортирана таблицата. В падащото поле „Сортиране по“ изберете основната колона, по която първо искате да подредите данните по азбучен ред, „Държава“ в нашия случай, а в падащото меню „Поръчка“ изберете или „А до Я“ или „Я до А“ .
След това щракнете върху бутона „Добавяне на ниво“, за да добавите второто ниво на сортиране. Изберете втората колона (в нашия случай Име), по която искате да подредите данните по азбучен ред в полето „Сортиране по“ и изберете „А до Я“ или „Я до А“. Ако вашата таблица има заглавки в горната част, поставете отметка в квадратчето „Моите данни имат заглавки“, за да може да пропусне заглавките, докато ги сортира. Добавете още нива на сортиране, ако е необходимо, и щракнете върху „OK“.
Таблицата е подредена по азбучен ред: първо по държава, а след това по име, както е показано по-долу.
Как да сортирате редовете по азбучен ред в Excel
Понякога може да искате да подредите по азбучен ред редове в Excel, а не колони. Това може да стане с помощта на функцията за сортиране на Excel. Например, да приемем, че имате електронна таблица, където колоната от B до T от първия ред съдържа имената на градовете, където броят на магазините, налични при различни видове търговия на дребно, и колона A съдържа тип търговец на дребно. Клетките се използват, за да следят колко магазина във всеки град в различни категории търговци на дребно.
Първо изберете всички редове, които искате да подредите по азбучен ред; Ако не искате да местите етикетите на редовете, оставете ги извън избора си. Сега отидете до раздела „Данни“ и щракнете върху „Сортиране“ в групата Сортиране и филтриране.
В диалоговия прозорец Сортиране щракнете върху бутона „Опции“.
Ще се появи друг малък диалогов прозорец, който ви дава възможност да изберете „Сортиране отляво надясно“ или „Сортиране отгоре надолу“. Изберете опцията „Сортиране отляво надясно“, за да подредите по азбучен ред, и щракнете върху „OK“, за да се върнете в диалоговия прозорец Сортиране.
Сега изберете реда, който искате да подредите по азбучен ред, в падащия списък „Сортиране по“ (ред 1 в този пример). В другото поле запазете стойностите на клетките в полето „Сортиране по“ и изберете „А до Я“ (възходящ ред) в полето „Поръчка“. След това щракнете върху „OK“, за да затворите диалоговия прозорец.
Сега първият ред в нашата таблица е сортиран по азбучен (възходящ) ред, а останалите редове са пренаредени съответно, както е показано по-долу.
Как да сортирате колона по азбучен ред с помощта на филтър
Друг бърз начин за сортиране по азбучен ред в Excel е използването на функцията за филтриране. След като приложите филтри към колони, опциите за сортиране за всички колони могат да бъдат достъпни само с едно щракване на мишката.
За да добавите филтър към колони, изберете една или повече колони и щракнете върху опцията „Филтър“ в групата Сортиране и филтриране в раздела „Данни“. Ако искате да приложите филтъра към цялата таблица, изберете която и да е клетка в таблицата и щракнете върху опцията „Филтър“.
Можете също да получите достъп до опцията за филтриране, като щракнете върху инструмента „Сортиране и филтриране“ в групата Редактиране на раздела „Начало“ и изберете „Филтър“.
Във всяка от заглавките на колоните ще се появи малка стрелка за падащо меню. Щракнете върху стрелката на всяка колона, която искате да сортирате по азбучен ред, и изберете „Сортиране от А до Я“ или „Сортиране от Я до А“.
Това ще подреди колоната, която искате, в избрания от вас азбучен ред и ако изберете „Сортиране от A до Z“, върху бутона за филтър ще се появи малка стрелка нагоре, указваща реда на сортиране (възходящ):
Разширено сортиране в персонализирана поръчка
Сортирането по азбучен ред не е идеално за всички данни по азбучен ред. Понякога данните могат да бъдат подредени по азбучен ред, но това не е най-ефективният начин за сортирането им.
Нека си представим ситуация, в която може да имате списък, съдържащ имената на месеци или дни от седмицата, сортирането по азбучен ред не е полезно в тази ситуация. По-разумно е този списък да бъде сортиран хронологично. Ако сортирате от А до Я, това ще подреди месеците по азбучен ред, първо ще дойде април, след това август, февруари, юни и т.н. Но това не е това, което искате. За щастие е много лесно да подредите хронологично в Excel, като използвате опцията Разширено персонализирано сортиране.
Започнете, като изберете таблицата, която искате да сортирате. След това изберете „Персонализирано сортиране“ под „Сортиране и филтриране“ в секцията Редактиране на раздела Начало.
В диалоговия прозорец Сортиране изберете колоната, която съдържа месеци от годината в секцията с колони, сортирайте по „Стойности“, както обикновено, и в секцията „Поръчка“ изберете „Персонализиран списък“.
В диалоговия прозорец Персонализирани списъци можете да създадете свой собствен списък. Има някои персонализирани списъци по подразбиране, включително дни от седмиците, съкратени месеци, месеци от годината и т.н. Изберете подходящата опция за сортиране според вашите нужди (в нашия случай януари, февруари, ..) и щракнете върху „OK“. След това щракнете отново върху „OK“, за да сортирате списъка си в хронологичен ред.
Ще забележите, че успешно сте сортирали списъка според реда на месеца.
Как да сортирате по азбучен ред в Excel с помощта на формули на Excel
Ако сте фен на формулите, тогава можете да използвате формули на Excel, за да подредите списък по азбучен ред. Двете формули, които могат да се използват за азбучен ред на данните, са COUNTIF и VLOOKUP.
Например, имаме списък с имена, които искаме да подредим по азбучен ред с помощта на формула.
За да сортираме това, ще добавим временна колона, наречена този „Ред на сортиране“ към съществуващата таблица.
В клетката (A2) до данните използвайте следната формула COUNTIF:
=COUNTIF($B$2:$B$20,"<="&B2) Горната формула сравнява текстова стойност в клетка B2 с всички други текстови стойности (B3:B20) и връща нейния относителен ранг. Например, в клетка B2 той връща 11, тъй като има 11 текстови стойности, които са по-ниски или равни на текста „Нанси“ по азбучен ред. Резултатът е реда на сортиране на името на служителя в клетка B2.
След това използвайте манипулатора за запълване, плъзнете тази формула, за да я попълните в целия диапазон. Това дава реда на сортиране на всяко от имената в списъка.
Сега трябва да подредим данните, показани на горната екранна снимка, въз основа на номера на реда за сортиране и за да направим това, ще използваме функция VLOOKUP.
Синтаксисът:
=VLOOKUP(,A:B,2,0)Тук „номер за сортиране“ представлява числата във възходящ ред от 1 – 20. За низходящ ред числата трябва да са от 20 – 1.
Тук имаме колона D за сортирани имена. За клетка D2 въведете следната формула VLOOKUP:
=VLOOKUP(1,A:B,2,0)По същия начин за втората и третата клетка трябва да използвате формулата като:
=VLOOKUP(2,A:B,2,0) и =VLOOKUP(3,A:B,2,0) и така нататък…
След въвеждане на тази формула VLOOKUP във всяка клетка до данните, списъкът се подрежда по азбучен ред.
Вместо да въвеждате ръчно гореспоменатата формула (1-20) във всяка клетка, можете също да използвате функцията за ред, за да улесните работата си. Функцията Row () връща номера на реда на текущата клетка. И така, с помощта на функцията за ред, формулата ще бъде:
=VLOOKUP(ROW()-1,A:B,2,0)Тази формула ще ви даде същия резултат като горната формула.
И след това използвайте манипулатора за запълване, за да плъзнете тази формула, за да я попълните в целия диапазон.
Как да подредите по азбучен ред записи по фамилно име
Понякога често се налага да сортираме по азбучен ред листове с данни с фамилни имена. В такива случаи, въпреки че имената започват с първите имена, трябва да ги подредите по азбучен ред по фамилното име. Можете да направите това с текстови формули на Excel.
За да направите това, първо ще трябва да извлечете първото и фамилното име от пълните имена в две различни колони. След това обърнете имената, сортирайте ги и след това ги върнете обратно в първоначалния им вид.
С пълно име в A2 въведете следните формули в две различни клетки (B2 и C2) и след това копирайте формулите надолу по колоните (използвайки манипулатор за запълване) до последната клетка с данни:
За да извлечете първото име, въведете тази формула в клетка C2:
=ЛЯВО(A2,ТЪРСЕНЕ(” “,A2)-1)
За да извлечете фамилното име, въведете тази формула в клетка D2:
=НАДЯСНО(A2,LEN(A2)-ТЪРСЕНЕ(" ",A2,1))
И след това, в клетка E2, конкатенира името и фамилията в обратен ред, разделени със запетая:
=D2&", "&C2
Разделихме името и фамилията и ги обърнахме. Сега трябва да ги подредим по азбучен ред и да ги обърнем обратно. Както можете да видите по-горе, когато извличаме имена с помощта на формулата, колоните C, D и E всъщност съдържат формули, но те са форматирани да изглеждат като стойности. Така че трябва да преобразуваме формулата в стойности.
За да направите това, първо изберете всички клетки с формула (E1:E31) и натиснете Ctrl + C да ги копирам. Щракнете с десния бутон върху избраните клетки, щракнете върху иконата „Стойности“ под „Опции за поставяне“ и натиснете клавиша „Enter“.
След това изберете произволна клетка в получената колона и щракнете върху опцията „А до Я“ или „Я до А“ в раздела „Данни“ или „Начало“ според вашите нужди.
След като щракнете върху бутона, ще се появи диалогов прозорец с предупреждение за сортиране. Изберете опцията „Разширяване на избора“ и щракнете върху бутона „Сортиране“.
Както можете да видите, цялата колона е подредена по азбучен ред по фамилното име.
Сега, ако искате да го върнете обратно към оригиналния „формат на фамилията на първо име“, просто трябва да въведете същите формули, но с различни препратки към клетки.
Трябва отново да разделите името (E2) на две части, като използвате следните формули:
В G2 извлечете първото име:
=ДЯСНО(E2,LEN(E2)-ТЪРСЕНЕ(" ",E2))
В G2 издърпайте фамилното име:
=ЛЯВО(E2,ТЪРСЕНЕ(" ",E2)-2)
И съединете двете части заедно, за да получите оригиналното пълно име:
=G2&" "&H2
Всеки път, когато въвеждате формулата в първата клетка, не забравяйте да копирате формулата надолу по колоната (използвайки манипулатор за попълване) до последната клетка с данни.
Сега всичко, което трябва да направите, е да преобразувате формулите в преобразуване на стойности още веднъж, както направихме по-горе, и сте готови. това е направено и.