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

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

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

Можете също да правите сортиране на колони на няколко нива (т.е. колона A, след това колона B) и сортиране на редове (отляво надясно).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комбинация от клавиши– Ако често се налага да сортирате данни в Excel, препоръчвам да научите клавишните комбинации, които отварят диалоговия прозорец за сортиране.това еALT + A + S + S

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

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

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

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

Имайте предвид, че тези опции за сортиране се променят въз основа на данните в колоната.Така че, ако имате текст, той ще покаже опцията за сортиране от А до Я или Я до А, но ако имате числа, ще покаже опцията за сортиране от най-голямо към най-малко или от най-малко към най-голямо.

Опции за щракване с десния бутон

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Това е истинско.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Например числото 44196.125 представлява 2020 декември 12 г. в 31 часа сутринта.Докато цялата част на числото представлява целия ден, дробната част представлява времето.

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

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

Ето стъпките за сортиране на тези данни въз основа на дата:

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

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

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

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

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

Често имам набори от данни, които анализирам ръчно и подчертавам клетките, както го правя.Например прегледах списъка със статии в този блог (имам лист в 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:

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. В падащото меню Поръчка изберете AZ (можете да използвате и персонализиран сортиран списък, ако желаете)
10. Щракнете върху OK.

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

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

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

Сортиране според главните букви в Excel

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

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

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

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

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

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

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

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

Ще получите данни за резултатите като това:

Когато сортирате от А до Я, текстът с малки букви е над текста с главни букви.

Вземете оригиналния ред на сортиране

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

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

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

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

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

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

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

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

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

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

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

прост. .нали?

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

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

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

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

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

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

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

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

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

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

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

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

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

Празен ред/колона не е разпознат

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ето формула, която ще ми даде фамилното ми име:

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

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

Сега можете да сортирате тези данни с помощта на колоната Фамилно име.

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

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

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

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

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

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

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

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

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

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

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

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

Сортирайте с VBA

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

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

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

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

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