Как да сортирате в Excel (по редове, колони, цветове, дати и числа)

Сортирането на данни в Excel е станало доста лесно с всички вградени опции.

Можете лесно да сортирате данните си по азбучен ред въз основа на стойността в клетките или по цвят на клетката и шрифта.

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

И ако това не беше достатъчно, Excel също така ви позволява да създавате свои собствени персонализирани списъци и да сортирате въз основа на това (колко готино е това). Така че можете да сортирате данни въз основа на размерите на ризата (XL, L, M, S) или отговорите (напълно съгласен, съгласен, несъгласен) или интензивността (висока, средна, ниска)

В крайна сметка - има твърде много опции за сортиране, които имате на разположение при работа с Excel.

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

Тъй като това е огромен урок с много теми, по -долу предоставям съдържание. Можете да кликнете върху някоя от темите и тя незабавно ще ви отведе там.

Достъп до опциите за сортиране в Excel

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

Бутони за сортиране в лентата

Най -бързият начин за достъп до опциите за сортиране е чрез използване на бутоните за сортиране в лентите.

Когато щракнете върху раздела Данни в лентата, ще видите опциите „Сортиране и филтриране“. Трите бутона вляво в тази група са за сортиране на данните.

Двата малки бутона ви позволяват да сортирате данните си веднага щом кликнете върху тях.

Например, ако имате набор от имена от данни, можете просто да изберете целия набор от данни и да щракнете върху някой от двата бутона, за да сортирате данните. Бутонът A до Z сортира данните по азбучен ред от най -ниските до най -високите, а бутонът от Z до A сортира данните по азбучен ред от най -високите до най -ниските.

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

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

Диалогов прозорец за сортиране

В раздела Данни на лентата има друга икона на бутона за сортиране в групата за сортиране.

Когато щракнете върху тази икона на бутона за сортиране, тя отваря диалоговия прозорец за сортиране (нещо, както е показано по -долу).

Диалоговият прозорец за сортиране е най -пълното решение за сортиране в Excel. Всички опции, свързани със сортирането, могат да бъдат достъпни през този диалогов прозорец.

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

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

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

В този урок ще ме намерите най -вече с помощта на диалоговия прозорец за сортиране на данните. Това е така, защото някои от нещата, които обхващам в определени раздели (като например многостепенна сортировка или сортиране отляво надясно), могат да се извършват само с помощта на диалоговия прозорец.Клавишна комбинация - Ако трябва да сортирате данни често в Excel, препоръчвам ви да научите клавишната комбинация, за да отворите диалоговия прозорец за сортиране. Това е ALT + A + S + S

Опции за сортиране в менюто Филтър

Ако сте приложили филтри към вашия набор от данни, можете също да намерите опциите за сортиране заедно с опциите за филтриране. Филтър може да се приложи, като изберете всяка клетка в набора от данни, щракнете върху раздела Данни и щракнете върху иконата Филтър.

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

Когато щракнете върху иконата на филтъра за която и да е колона (това е малката надолу триъгълна икона вдясно от клетката на заглавката на колоната), ще видите и някои от опциите за сортиране там.

Обърнете внимание, че тези опции за сортиране се променят въз основа на данните в колоната. Така че, ако имате текст, той ще ви покаже опцията за сортиране от A до Z или Z до A, но ако имате числа, ще ви покаже опции за сортиране от най -голямото до най -малкото или най -малкото до най -голямото.

Щракнете с десния бутон върху опциите

Освен използването на диалоговия прозорец, използването на десен бутон е друг метод, който понякога използвам (също е супер бърз, тъй като отнема само две кликвания).

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

Обърнете внимание, че виждате някои опции, които не виждате в лентата или в опциите на филтъра. Въпреки че има обичайната опция за сортиране по стойност и персонализирана опция за сортиране (която отваря диалоговия прозорец Сортиране), можете също да видите опции като Поставете избрания цвят на клетката/Цвет на шрифта/Иконата за форматиране в горната част.

Намирам тази опция за доста полезна, тъй като ми позволява бързо да събера всички цветни клетки (или клетки с различен цвят на шрифта) заедно в горната част. Често имам данни за месечните разходи, които преглеждам и маркирам някои клетки ръчно. След това мога да използвам тази опция за бързо събиране на всички тези клетки в горната част.

Сега, когато разгледах всички начини за достъп до опциите за сортиране в Excel, нека да видим как да ги използваме за сортиране на данни в различни сценарии.

Сортиране на данни в Excel (текст, числа, дати)

Протест: В повечето случаи сортирането работи дори когато изберете една клетка в набора от данни. Но в някои случаи може да завършите с проблеми (когато имате празни клетки/редове/колони във вашия набор от данни). Когато сортирате данни, най -добре е да изберете целия набор от данни и след това да ги сортирате - само за да избегнете всякакви шансове за проблеми.

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

Сортиране по текст

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

По -долу са стъпките за сортиране на тези текстови данни по азбучен ред:

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

Горните стъпки ще сортират целия набор от данни и ще дадат резултат, както е показано по -долу.

Защо просто не използвате бутоните в лентата?

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

И това е вярно.

Горният метод е по -дълъг, но има няма шанс за грешка.

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

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

В примера по -долу, тъй като Excel не може да разпознае, че има ред на заглавка, той сортира целия набор от данни, включително заглавката.

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

Тъй като използването на диалоговия прозорец Сортиране елиминира вероятността от грешки, препоръчвам ви да го използвате вместо всички други методи за сортиране в Excel

Сортиране по числа

Предполагам, че вече сте разбрали как работи сортирането на текст (обхванато над този раздел).

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

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

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

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

Горните стъпки ще сортират целия набор от данни и ще дадат резултат, както е показано по -долу.

Сортиране по дата/час

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

Например в Excel числото 44196 ще бъде стойността на датата за 31 декември 2020 г. Можете да форматирате този номер, за да изглежда като дата, но в задната част в Excel той все още остава номер.

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

Същото важи и за времето в Excel.

Например числото 44196.125 представлява 3 часа сутринта на 31 декември 2021-2022 г. Докато целочислената част на числото представлява цял ден, десетичната част ще ви даде време.

И тъй като датата и часът са числа, можете да ги сортирате като числа.

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

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

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

Горните стъпки ще сортират целия набор от данни и ще дадат резултат, както е показано по -долу.

Имайте предвид, че макар датата и часът да са числа, Excel все още разпознава, че те се различават по начина, по който се показват. Така че, когато сортирате по дата, той показва критериите за сортиране „От най -старото до най -новото“ и „Най -новото към най -старото“, но когато използвате числа, то показва „От най -голямото до най -малкото“ или „От най -малкото до най -голямото“. Такива малки неща, които правят Excel страхотен инструмент за електронни таблици (PS: Google Sheets не показва толкова много подробности, а само лекото сортиране по A-Z или Z-A)

Сортиране по цвят на клетката / цвят на шрифта

Тази опция е невероятна и я използвам през цялото време (може би малко прекалено).

Често имам набори от данни, които анализирам ръчно и маркирам клетки, докато го правя. Например, преглеждах списък със статии в този блог (който имам в лист на Excel) и подчертах тези, които трябва да подобря.

И след като приключа, мога бързо да сортирам тези данни въз основа на цветовете на клетките. Това ми помага да събера всички тези маркирани клетки/редове заедно в горната част.

И за да добавите към страхотността, можете да сортирате въз основа на множество цветове. Така че, ако подчертая клетки с имена на статии, които се нуждаят от незабавно внимание в червено, и някои, които могат да бъдат разгледани по -късно с жълто, мога да сортирам данните, за да покажа всички червени редове, последвани от жълтото.

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

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

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

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

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

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

Сортиране на данни на много нива

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

Вашите могат да се простират до хиляди редове и стотици колони.

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

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

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

1. Регионът
2. Продажбите

Резултатът от това сортиране въз основа на горните два критерия ще ви даде набор от данни, както е показано по -долу.

В горния пример имаме първо данни, сортирани по регионите, а след това във всеки регион, данните са допълнително сортирани по стойността на продажбите.

Това бързо ни позволява да видим кои търговски представители се справят отлично във всеки регион или кои са лоши.

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

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

Професионален съвет: Диалоговият прозорец Сортиране има функция за „Ниво на копиране“. Това бързо копира избраното ниво на сортиране и след това можете лесно да го промените. Добре е да знаете функцията и може да ви спести време в случай, че трябва да сортирате въз основа на множество колони.

По-долу е видеоклип, в който показвам как да направя многостепенно сортиране в Excel:

Сортиране въз основа на персонализиран списък

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

За да ви дам пример, да предположим, че имам следния набор от данни:

Сега, ако го сортирам въз основа на региона по азбучен ред, имам две възможности - от А до Я или от З до А. По -долу е това, което получавам, когато сортирам тези данни по азбучен ред от А до Я, използвайки колоната регион.

Но какво ще стане, ако искам този ред да бъде Изток, Запад, Север, Юг?

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

Правилният начин да направите това ще бъде използването на персонализирани списъци.

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

Някои примери, където персонализирани списъци могат да бъдат полезни, включват:

• Сортиране на данните въз основа на името на регион/град
• Сортиране въз основа на размери на тениски - малки, средни, големи, изключително големи
• Сортиране въз основа на отговорите на анкетата - напълно съгласен, съгласен, неутрален, несъгласен
• Сортиране въз основа на вероятността - висока, средна, ниска

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

Стъпки за създаване на персонализиран списък в Excel:

1. Щракнете върху раздела Файл
2. Кликнете върху Опции
3. В диалоговия прозорец за опции на Excel изберете „Разширени“ от списъка в левия прозорец.
4. В рамките на избраните Разширени превъртете надолу и изберете „Редактиране на персонализиран списък“.
5. В диалоговия прозорец Персонализирани списъци въведете критериите си в полето, озаглавено Списъчни записи. Въведете критериите си, разделени със запетая (Изток, Запад, Север, Юг) [можете също да импортирате критериите си, ако сте го включили в списъка].
6. Щракнете върху Добавяне
7. Щракнете върху OK

След като изпълните горните стъпки, Excel ще създаде и съхранява персонализиран списък, който можете да използвате за сортиране на данни.

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

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

Стъпки за сортиране с помощта на персонализиран списък

Да предположим, че имате набора от данни, както е показано по -долу, и искате да го сортирате според регионите (редът за сортиране е Изток, Запад, Север и Юг)

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

Ето стъпките за сортиране на набор от данни с помощта на персонализиран списък:

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

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

Забележка: Не е необходимо предварително да създавате персонализиран списък, за да сортирате данните въз основа на него. Можете също да го създадете, докато сте в диалоговия прозорец Сортиране. Когато щракнете върху Персонализиран списък (в стъпка 7 по -горе), той отваря диалоговия прозорец за персонализиран списък. Можете също да създадете персонализиран списък там.

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

Сортиране отляво надясно

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

Например в набора от данни по -долу искам да го сортирам въз основа на стойностите в реда Регион.

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

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

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

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

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

Excel не разпознава (или дори ви позволява да посочите) заглавките при сортиране отляво надясно. Така че трябва да се уверите, че клетките на заглавката ви не са избрани, когато сортирате данните. Ако изберете и заглавни клетки, те ще бъдат сортирани въз основа на стойността в тях.

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

По -долу е видео, където показвам как да сортирам данни отляво надясно в Excel

Чувствително към сортиране сортиране в Excel

Досега във всички примери по -горе сортирането е независимо от регистъра.

Но какво ще стане, ако искате да направите сортирането чувствително към регистъра.

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

Забележка: Въпреки че в повечето случаи не е нужно да се притеснявате за сортирането, чувствително към регистъра, това може да бъде полезно, когато получавате данните си от база данни, като Salesforce, или ръчно събирате данните и различните хора въвеждат един и същ текст в различни случаи. Наличието на регистър с регистър може да ви помогне да съхранявате всички записи от едно и също лице/база данни заедно.

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

По -долу са посочени стъпките за сортиране на данните по азбучен ред, както и за чувствителността им към главни букви:

1. Изберете целия набор от данни
2. Щракнете върху раздела Данни
3. Щракнете върху иконата Сортиране. Това ще отвори диалоговия прозорец Сортиране.
4. В диалоговия прозорец Сортиране се уверете, че моите данни имат заглавки. В случай, че данните ви нямат заглавки, можете да премахнете отметката от това.
5. Щракнете върху бутона Опции
6. В диалоговия прозорец Опции за сортиране проверете опцията „Чувствителни към малки и големи букви“
7. Щракнете върху OK.
8. В падащото меню „Сортиране по“ изберете „Регион“
9. В падащото меню „Сортиране на“ изберете „Стойности на клетката“.
10. В падащото меню за поръчка изберете от A до Z
11. Щракнете върху OK.

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

Ще получите получените данни, както е показано по -долу:

Когато сортирате от A до Z, малкият текст се поставя над главния.

Получаване на оригиналния ред за сортиране

Често, когато сортирате данни в Excel, може да искате да се върнете към по -ранния или първоначалния ред на сортиране и да започнете отначало,

Въпреки че можете да използвате функцията за отмяна в Excel (с помощта на Control Z), за да се върнете стъпка назад, това може да е объркващо, ако вече сте направили няколко неща след сортирането на данните.

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

Ето два прости начина да се уверите, че няма да загубите първоначалния ред за сортиране и да го върнете дори след сортиране на данните:

1. Направете копие на оригиналния набор от данни. Това е нещо, което препоръчвам да направите, дори ако нямате нужда от оригиналния ред за сортиране. Можете да имате работна книга с всички данни и след това просто да създадете копие на работната книга и да работите върху копието. Когато работя върху критични набори от данни, правя копие всеки ден (с датата или номера на версията като част от името на работната книга).
2. Добавете колона с поредица от числа. Тази поредица от числа се смесват, когато сортирате данните, но в случай, че искате да се върнете към първоначалните данни, можете да сортирате въз основа на тази поредица от числа.

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

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

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

Ето урок с различни начини за бързо добавяне на колона с числа

Когато имате помощната колона, не забравяйте да я включите, докато сортирате този набор от данни.

Да предположим, че сортирам тези данни въз основа на региона и в крайна сметка получавам данните, както е показано по -долу:

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

Просто … нали?

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

Някои често срещани проблеми при сортиране на данни в Excel

В началото на тази статия ви показах различни начини за сортиране на данни в Excel (включително бутоните за сортиране в лентата, опциите за десен бутон, опцията за филтриране и диалоговия прозорец за сортиране).

И за да повторим, използването на диалоговия прозорец за сортиране минимизира шансовете или евентуалните проблеми или грешки.

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

Не се идентифицират заглавките на колоните

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

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

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

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

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

Забележка: Този проблем възниква, когато имам набора от данни и добавям и форматирам заглавките и го сортирам. Обикновено Excel би бил достатъчно умен, за да установи, че има заглавки в набор от данни (особено когато типът на данните от заглавката и данните в колоната са различни). Но в този случай не успя да го направи, когато добавих заглавката и сортирах веднага. Ако запазя тази работна книга, затворя я и я отворя отново, Excel по някакъв начин успява да идентифицира първия ред и заглавката.

Въпреки че това може да не е проблем в повечето случаи, все още има шанс, когато използвате иконите за сортиране в лентата, Използването на диалогов прозорец елиминира този проблем, тъй като можете да посочите, че имате заглавки.

Не се идентифицират празни редове/колони

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

Да предположим, че имате набор от данни, както е показано по -долу. Обърнете внимание, че ред номер 6 е скрит (и това е празен ред).

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

И освен ако не го търсите конкретно, вероятно ще пропуснете тази ужасна грешка.

Как да се уверите, че няма да направите тази грешка при сортирането?

За да сте сигурни, че няма да станете жертва на този проблем, трябва да проверите набора от данни, преди да го сортирате.

Изберете целия набор от данни, преди да сортирате данните.

Можете да направите това, като изберете произволна клетка в набора от данни и след това използвате клавишната комбинация - Control + A.

Ако във вашия набор от данни има празни редове/колони, целият набор от данни няма да бъде избран. Можете да идентифицирате това, като бързо сканирате очертанията на селекцията.

Ако видите, че има останали данни извън селекцията, можете да ги изберете ръчно.

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

Частично сортиране (въз основа на фамилното име)

Понякога може да имате набор от данни, който искате да сортирате въз основа на част от текста.

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

За да направя това, трябва да отделя тези данни, така че да имам само фамилните имена в една колона. Когато го имам, мога да използвам тази колона за сортиране на данните.

По -долу е формула, която може да ми даде фамилното име:

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

Това ще ви даде резултат, както е показано по -долу.

Сега можете да използвате колоната „Фамилия“, за да сортирате тези данни.

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

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

Това е само един пример за сортиране въз основа на частични данни. Други примери могат да включват сортиране въз основа на градове в адрес или идентификационен номер на служител въз основа на кодовете на отделите.

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

Други примери за сортиране (използване на формула и VBA)

Досега в този урок разгледах примерите, в които използвахме вградената функционалност за сортиране в Excel.

Автоматично сортиране с помощта на формула

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

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

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

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

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

Сортиране с помощта на VBA

И накрая, ако искате да заобиколите всички диалогови прозорци за сортиране или други опции за сортиране, можете да използвате VBA, за да сортирате данните си.

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

Ето подробен урок, където обхващам как да сортирам данни с помощта на VBA и да създам нещо, както е показано по -горе.

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

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