Сортирането на данни в Excel е много полезна функция, но трябва да се използва с повишено внимание. Ако голяма таблица съдържа сложни формули и функции, тогава е по-добре да извършите операцията за сортиране върху копие на тази таблица.

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

Сортиране на данни в Excel

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

Подготовка на таблица за правилно и безопасно сортиране на данни:

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

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

Данните са сортирани в таблицата спрямо колоната „Нетна печалба“.



Как да сортирате колона в Excel

Сега нека сортираме само една колона без препратка към други колони и цялата таблица:

Колоната се сортира независимо от другите колони в таблицата.

Сортиране по цвят на клетка в Excel

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

Копието на таблицата вече съдържа стойностите и форматите. Нека сортираме по цвят:

1. Изберете таблицата и изберете инструмента „Данни“ - „Сортиране“.
2. В опциите за сортиране отново поставете отметка в квадратчето „Моите данни съдържат заглавия на колони“ и посочете: „Колона“ – Нетна печалба; “Сортиране” – Цвят на клетките; „Поръчка“ – червено, отгоре. И щракнете върху OK.

На върха сега имаме най-лошо представящите се данни за нетната печалба.

Забележка. След това можете да изберете диапазона A4:F12 в тази таблица и да повторите втората стъпка от този раздел, като посочите само розово отгоре. Така първо ще отидат клетките с цвят, а след това нормалните.

Нека сортираме таблица, състояща се от 2 колони, използвайки формули. Ще сортираме по една от колоните на таблицата (ще решим 2 задачи: сортиране на таблицата по числова и сортиране по текстова колона). Ще настроим формулите за сортиране, така че когато нови данни се добавят към изходната таблица, сортираната таблица се променя динамично. Това ще ви позволи винаги да имате сортирана таблица без намесата на потребителя. Ще направим и сортиране на две нива: първо по числова, след това (за повтарящи се числа) по текстова колона.

Нека има таблица, състояща се от 2 колони. Една колона е текст: Списък с плодове; а вторият е числов Обем на продажбите(вижте примерния файл).

Задача 1 (Сортиране на таблица по числова колона)

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

За по-голяма яснота, стойностите в колоната Обем на продажбитеподчертано с (). Повтарящите се стойности също са маркирани в жълто.

Забележка: Проблемът със сортирането на една колона (списък) беше решен в статии и.

Решение1

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

• Сортирайте цифровата колона с помощта на функцията LARGE() (вижте статията);
• Функцията VLOOKUP() или група функции ИНДЕКС()+МАЧ()изберете стойности от текстова колона по съответната й цифрова стойност.

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

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

ИНДЕКС(Продажби;
ROUND(REMAT(LARGEST(
---(COUNTIF(Продажби;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
РЕД()-РЕД($E$6));1)*1000;0)
)

Тази формула сортира колоната Обем на продажбите(динамичен диапазон Продажби) в низходящ ред. Не се допускат пропуски в изходната таблица. Броят на редовете в изходната таблица трябва да е по-малък от 1000.

Нека разгледаме формулата по-подробно:

• Формула COUNTIF(Продажби;"<"&Продажи) връща масива (4:5:0:2:7:1:3:5). Това означава, че числото 64 (от кл B7 оригиналната таблица, т.е. първото число от диапазона Продажби) повече от 4 стойности от същия диапазон; номер 74 (от клетката B8 оригиналната таблица, т.е. второ число от диапазона Продажби) повече от 5 стойности от същия диапазон; следващото число 23 е най-малкото (не е по-голямо от никой) и т.н.
• Сега нека превърнем горния масив от цели числа в масив от числа с дробна част, където дробната част ще съдържа номера на позицията на числото в масива: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008 ). Това се изпълнява от израза &","&REPEAT("0";3-LENGTH(ROW(Продажби)-ROW($E$6)))&ROW(Продажби)-ROW($E$6))Именно в тази част на формулата има ограничение за не повече от 1000 реда в изходната таблица (виж по-горе). Може лесно да се промени, ако желаете, но това е безсмислено (вижте раздела за изчислителната скорост по-долу).
• Функцията LARGE() сортира горния масив.
• Функцията ROD() връща дробната част от числото, което е номера на позиции/1000, като например 0,005.
• Функцията ROUND() след умножаване по 1000 закръгля до цяло число и връща номера на позицията. Сега всички номера на позиции съответстват на номера на колони Обем на продажбитесортирани в низходящ ред.
• Функцията INDEX(), при даден номер на позиция, връща номера, съответстващ на нея.

Подобна формула може да бъде написана за показване на стойности в колона Плодове=ИНДЕКС(плодове,КРЪГ(...))

В примерния файл, поради съображения за скорост на изчисление (вижте по-долу), същият тип част от формулата, т.е. всичко вътре във функцията ROUND() се поставя в отделна колона Дж . Следователно крайните формули в сортираната таблица изглеждат така: =ИНДЕКС(Плодове,J7)И =ИНДЕКС(Продажби,J7)

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

За по-голяма яснота, стойностите в колоната Обем на продажбитеподчертано с ( Начало/ Стилове/ Условно форматиране/ Хистограми). Както можете да видите, сортирането работи.

Тестване

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

1. Към клетката A15 изходна таблица въведете думата Морков;
2. Към клетката B15 влизам Обем на продажбитеМоркови = 25;
3. След като въведете стойностите в колоните г И д автоматично ще се покаже таблица, сортирана в низходящ ред;
4. В сортирана таблица новият ред ще се покаже предпоследен.

Скорост на изчисляване на формулата

На „среден“ компютър по отношение на производителността, преизчисляване на чифт такива формули за масиви,разположен в 100 реда, практически не се вижда. За таблици с 300 реда времето за преизчисляване отнема 2-3 секунди, което е неудобно. Или трябва да деактивирате автоматичното преизчисляване на листа ( Формули/Изчисления/Опции за изчисление) и натискайте клавиша периодично F9, или изоставете използването на формули за масиви, като ги замените с колони със съответните формули, или напълно изоставете динамичното сортиране в полза на използването на стандартни подходи (вижте следващия раздел).

Алтернативни подходи за сортиране на таблици

Нека сортираме редовете на изходната таблица с помощта на стандартен филтър (изберете заглавките на изходната таблица и щракнете върху CTRL+SHIFT+L). Изберете необходимото сортиране от падащия списък.

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

Можете също да използвате инструмента за сортиране ( Данни/Сортиране и Филтриране/Сортиране). За да направите това, трябва да изберете всички стойности на изходната таблица, без да включвате заглавката, да извикате инструмента за сортиране, да изберете колоната, по която искате да сортирате, и опцията за сортиране.

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

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

За да сортирате таблицата, ще трябва да създадете 2 служебни колони (D и E).

=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1

Тази формула е аналогична на текстовите стойности (позицията на стойността спрямо други стойности в списъка). Текстова стойност по-ниско в азбуката има по-висок "ранг". Например стойността Ябълки съответства на максимален „ранг“ от 7 (включително повторения).

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

=COUNTIF($D$6:D6,D7)+D7

Тази формула взема предвид повторенията на текстовите стойности и коригира "ранга". Сега различните стойности на ябълките съответстват на различни „рангове“ - 7 и 8. Това ви позволява да покажете списък с сортирани стойности. За да направите това, използвайте формулата (колона G):

=ИНДЕКС($B$7:$B$14,МАЧ(РЕД()-РЕД($G$6),$E$7:$E$14,0))

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

Задача 2.1 (Сортиране на две нива)

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

За да направим това, ще използваме резултатите от задача 1.1 и задача 2.

Подробности в примерния файл на лист Задача2.

За удобство при работа с големи количества данни в таблици, те постоянно трябва да бъдат организирани по определен критерий. Освен това, за изпълнение на конкретни цели понякога не е необходим целият масив от данни, а само отделни редове. Следователно, за да не се объркате в огромното количество информация, рационално решение би било да организирате данните и да ги филтрирате от други резултати. Нека разберем как да сортирате и филтрирате данни в Microsoft Excel.

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

Можете да сортирате данни в Microsoft Excel с помощта на бутона „Сортиране и филтриране“, който се намира в раздела „Начало“ на лентата в блока с инструменти „Редактиране“. Но първо трябва да щракнем върху която и да е клетка от колоната, по която ще сортираме.

Например в таблицата по-долу служителите трябва да бъдат сортирани по азбучен ред. Отидете до която и да е клетка в колоната „Име“ и щракнете върху бутона „Сортиране и филтриране“. За да сортирате имената по азбучен ред, изберете „Сортиране от А до Я“ от списъка, който се показва.

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

За да сортирате в обратен ред, в същото меню изберете бутона Сортиране от Я до А.”

Списъкът се преустройва в обратен ред.

Трябва да се отбележи, че този тип сортиране е посочен само за формат на текстови данни. Например при числов формат сортирането е зададено като „От минимум към максимум“ (и обратно), а при формат на дата – „От старо към ново“ (и обратно).

Персонализирано сортиране

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

Но какво ще стане, ако искаме да сортираме имената по азбучен ред, но например, ако името съвпада, се уверете, че данните са подредени по дата? За да направим това, както и да използваме някои други функции, всички в едно и също меню „Сортиране и филтриране“, трябва да отидем до елемента „Персонализирано сортиране...“.

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

В полето „Колона“ посочете името на колоната, по която ще се извърши сортирането. В нашия случай това е колоната „Име“. Полето „Сортиране“ показва по какъв тип съдържание ще бъде сортирано. Има четири опции:

• Ценности;
• Цвят на клетката;
• Цвят на шрифта;
• Икона на клетка.

Но в по-голямата част от случаите се използва елементът „Стойности“. Той е зададен по подразбиране. В нашия случай ще използваме и тази конкретна точка.

В колоната „Ред“ трябва да посочим в какъв ред ще бъдат подредени данните: „От А до Я“ или обратно. Изберете стойността „От А до Я“.

И така, настроили сме сортиране по една от колоните. За да конфигурирате сортиране по друга колона, щракнете върху бутона „Добавяне на ниво“.

Появява се друг набор от полета, които трябва да се попълнят, за да се сортират по друга колона. В нашия случай, според колоната „Дата“. Тъй като форматът на датата е зададен в тези клетки, в полето „Поръчка“ задаваме стойностите не „От А до Я“, а „От старо към ново“ или „От ново към старо“.

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

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

Но това не са всички възможности за персонализирано сортиране. Ако желаете, в този прозорец можете да конфигурирате сортиране не по колони, а по редове. За да направите това, щракнете върху бутона „Опции“.

В прозореца с опции за сортиране, който се отваря, преместете превключвателя от позиция „Редове на диапазон“ на позиция „Колони за диапазон“. Кликнете върху бутона "OK".

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

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

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

Филтър

Освен това Microsoft Excel има функция за филтриране на данни. Тя ви позволява да оставите видими само данните, които смятате за необходими, а останалите да скриете. Ако е необходимо, скритите данни винаги могат да бъдат върнати във видим режим.

За да използвате тази функция, застанете на която и да е клетка в таблицата (за предпочитане в заглавката), щракнете отново върху бутона „Сортиране и филтриране“ в блока с инструменти „Редактиране“. Но този път в менюто, което се показва, изберете елемента „Филтър“. Можете също така просто да натиснете клавишната комбинация Ctrl+Shift+L вместо тези действия.

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

Кликнете върху тази икона в колоната, по която ще филтрираме. В нашия случай решихме да филтрираме по име. Например, трябва да оставим данните само на служителя Николаев. Затова премахваме отметките от имената на всички останали служители.

Когато процедурата приключи, щракнете върху бутона „OK“.

Както можете да видите, в таблицата остават само редовете с името на служителя на Николаев.

Нека да усложним задачата и да оставим в таблицата само данните, които се отнасят за Николаев за третото тримесечие на 2016 г. За да направите това, щракнете върху иконата в клетката „Дата“. В списъка, който се отваря, премахнете отметката от месеците „май“, „юни“ и „октомври“, тъй като те не принадлежат към третото тримесечие, и кликнете върху бутона „OK“.

Както можете да видите, остават само данните, от които се нуждаем.

За да премахнете филтър за конкретна колона и да покажете скрити данни, щракнете отново върху иконата, намираща се в клетката със заглавието на тази колона. В менюто, което се отваря, щракнете върху елемента „Премахване на филтъра от...“.

Ако искате да нулирате филтъра за таблицата като цяло, трябва да кликнете върху бутона „Сортиране и филтриране“ на лентата и да изберете елемента „Изчистване“.

Ако трябва да премахнете напълно филтъра, тогава, както при стартирането му, трябва да изберете елемента „Филтър“ в същото меню или да въведете клавишната комбинация Ctrl+Shift+L.

Освен това трябва да се отбележи, че след като сме активирали функцията „Филтър“, когато щракнете върху съответната икона в клетките на заглавката на таблицата, функциите за сортиране, които обсъдихме по-горе, стават достъпни в менюто, което се появява: „Сортиране от А до Я” , „Сортиране от Я до А” и „Сортиране по цвят”.

Умна маса

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

Има два начина за създаване на интелигентна маса. За да използвате първия от тях, изберете цялата област на таблицата и, като сте в раздела „Начало“, кликнете върху бутона „Форматиране като таблица“ на лентата. Този бутон се намира в блока с инструменти „Стилове“.

След това се отваря диалогов прозорец, в който можете да промените координатите на таблицата. Но ако преди това сте избрали областта правилно, тогава не е необходимо да правите нищо друго. Основното е да се уверите, че има отметка до параметъра „Таблица със заглавки“. След това просто щракнете върху бутона „OK“.

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

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

Независимо кой метод използвате за създаване на „интелигентна таблица“, ще получите таблица, в заглавните клетки на която иконите за филтър, които описахме по-рано, вече ще бъдат инсталирани.

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

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

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

Типове данни за сортиране и ред на сортиране

Сортирайте числови стойности в Excel

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

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

Сортиране на текстови стойности в Excel

"Сортиране от А до Я" - сортиране на данните във възходящ ред;

"Сортиране от Я до А" - сортиране на данните в низходящ ред.

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

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

Можете да сортирате текстови данни по начин, чувствителен към малки и главни букви. За да направите това, трябва да поставите отметка в квадратчето „Match case“ в опциите за сортиране.

Обикновено главните букви имат по-малки числа от малките букви.

Сортиране на стойности за дата и час

„Сортиране от старо към ново“ е за сортиране на стойностите за дата и час от най-ранната до най-новата стойност.

„Сортиране от ново към старо“ е за сортиране на стойностите за дата и час от най-новата стойност до най-ранната стойност.

Сортиране на формати

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

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

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

Опции за сортиране

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

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

Сортиране по низ

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

Многостепенно сортиране

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

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

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

Добавка за сортиране на данни в Excel

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

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

Числовите стойности могат да бъдат сортирани във възходящ и низходящ ред, текстовите стойности могат да бъдат сортирани по азбучен ред и в обратен ред. Предлагат се опции - по цвят и шрифт, в произволен ред, според няколко условия. Колоните и редовете са сортирани.

Ред на сортиране в Excel

Има два начина за отваряне на менюто за сортиране:

1. Щракнете с десния бутон върху масата. Изберете „Сортиране“ и метод.
2. Отворете раздела „Данни“ - диалоговия прозорец „Сортиране“.

Често използваните методи за сортиране са представени с един бутон в лентата на задачите:

Сортиране на таблица по една колона:

1. За да може програмата да изпълни правилно задачата, изберете необходимата колона в диапазона от данни.
2. След това действаме в зависимост от поставената задача. Ако трябва да извършите просто сортиране във възходящ/низходящ ред (по азбучен ред или обратно), просто щракнете върху съответния бутон в лентата на задачите. Когато диапазон съдържа повече от една колона, Excel отваря диалогов прозорец, който изглежда така: За да запазите стойностите в редовете последователни, изберете действието „автоматично разширяване на избрания диапазон“. В противен случай само избраната колона ще бъде сортирана и структурата на таблицата ще бъде нарушена.

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

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

Excel предоставя на потребителя богати опции за форматиране. Следователно можете да работите с различни формати.

Нека създадем колона „Общо“ в тренировъчната таблица и да попълним клетките със стойности с различни нюанси. Нека сортираме по цвят:

1. Изберете колоната - десен бутон на мишката - „Сортиране“.
2. От предоставения списък изберете „Първо клетки с подчертан цвят“.
3. Съгласни сме „автоматично да разширим обхвата“.

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

В прозореца, който се отваря, въведете необходимите параметри:

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

Данните се сортират по шрифт по същия принцип.

Сортирайте по няколко колони в Excel

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

1. Отворете менюто „Персонализирано сортиране“. Задаваме първия критерий.
2. Щракнете върху бутона „Добавяне на ниво“.
3. Появяват се прозорци за въвеждане на данни за следващото условие за сортиране. Нека ги попълним.

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

Сортиране на редове в Excel

По подразбиране данните се сортират по колони. Как да сортирате по редове в Excel:

1. В диалоговия прозорец Персонализирано сортиране щракнете върху бутона Опции.
2. В менюто, което се отваря, изберете „Колони за диапазон“.
3. Натиснете OK. В прозореца „Сортиране“ ще се появят полета за попълване на условия по ред.

Ето как сортирате таблица в Excel според няколко параметъра.

Произволно сортиране в Excel

Вградените опции за сортиране не ви позволяват произволно подреждане на данните в колона. Функцията RAND ще се справи с тази задача.

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

Поставете курсора в съседната клетка (ляво или дясно, няма значение). Въведете RAND() в лентата за формули. Натиснете Enter. Копираме формулата в цялата колона - получаваме набор от произволни числа.

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

Динамично сортиране на таблици в MS Excel

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

1. Има набор от прости числа, които трябва да бъдат сортирани във възходящ ред.
2. Поставете курсора в съседната клетка и въведете формулата: =SMALL(A:A;ROW(A1)). точно така Посочваме цялата колона като диапазон. И като коефициент - функцията ROW с връзка към първата клетка.
3. Нека променим числото 7 на 25 в оригиналния диапазон - „сортирането“ във възходящ ред също ще се промени.

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

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

1. Първоначалните данни са списък с определени имена в произволен ред. В нашия пример списък с плодове.
2. Изберете колоната и я дайте име „Плодове“. За да направите това, в полето за имена, което се намира до лентата с формули, въведете името, което трябва да присвоим на избрания диапазон от клетки.
3. В следващата клетка (в примера - в B5) записваме формулата: Тъй като това е формула за масив, натиснете комбинацията Ctrl + Shift + Enter. Умножаваме формулата по цялата колона.
4. Ако редовете ще бъдат добавени към оригиналната колона, тогава въведете леко модифицирана формула: Нека добавим друга стойност „помело“ към диапазона „плод“ и проверете:

Изтеглете формули за сортиране на данни в Excel

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

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

Номера за поръчка

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

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

За да направите това, изберете колоната на таблицата „Цена, триене“. и отидете в раздела „Начало“. В блока „Редактиране“ има група инструменти „Сортиране и филтриране“. Изберете елемента „Сортиране във възходящ ред“ от менюто.

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

Избираме „Разширяване“, тъй като се интересуваме от подреждането на пълните записи на таблицата, и кликваме върху „Сортиране“. Записите са подредени във възходящ ред на разходите за живот.

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

За да сте сигурни, че числата са сортирани правилно, обърнете внимание на правилния формат на данните в колоната. Ако стойностите са въведени в таблицата като текст, а не като числа, подравняването няма да се случи в очаквания ред. Таблицата показва пример за сортиране на числата 10, 11, 100, 15, 110, 132 в зависимост от формата на данните.

Подреждане на текст

В Excel сортирането на колони, съдържащи текст, е същото като сортирането на числа. Нека изпълним тази команда в колоната на таблицата с цените на стаите в хотела „Храна“, която вече познаваме. Изберете колона, като щракнете върху нейната заглавка и отворете лентата с инструменти „Редактиране“ на раздела „Начало“. Сега в падащото меню, вместо сортиране на команди във възходящ и низходящ ред, има команди за сортиране от А до Я и обратно.

Изграждаме клетки от А до Я, което отново показва разширяването на обхвата на действие.

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

За форматирани таблици сортирането в Excel е още по-лесно. Когато отидете в контекстното меню на заглавката на колоната, ще видите и команди за изграждане на клетки в ред от А до Я и обратно.

Дати за поръчка

Всички горепосочени операции, които се прилагат за числа и информация за низове, се използват и за конструиране на дати и часове. Единствената разлика е в името на командата; за този тип данни тя се нарича „Сортиране от старо към ново“ и обратно. Пример е показан по-долу.

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

Персонализирани списъци

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

За да извършите това сортиране, програмата предоставя възможност за използване на персонализирани списъци. MS Excel има редица предварително зададени списъци, един от които е списък с дни от седмицата. За да ги видите, отидете в раздела Данни и намерете лентата с инструменти за сортиране и филтриране на Excel. Бутонът „Сортиране“ отваря прозорец за извършване на операции с няколко колони.

В полето „Поръчка“ изберете елемента „Списък по избор“. Изберете дните от седмицата от списъка и щракнете върху OK.

Организирайте по цвят и икони

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

За да направите това, отидете на персонализирано сортиране. В допълнение към метода, описан по-горе, можете да използвате менюто на панела „Редактиране“ на раздела „Начало“. Изберете елемента „Персонализирано сортиране“ от списъка с команди „Сортиране и филтриране“.

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

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

По-горе описахме как да сортирате данни от различни типове в Excel, като използвате цвета на клетките и шрифта на текста и маркировките в редовете. Надяваме се, че тази информация ще ви помогне при работата с програмата.

Събитието Worksheet_Change не отговаря на преизчисляването на формулата.

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

Private Sub Worksheet_Calculate() Application.EnableEvents = False .CurrentRegion.Sort, xlDescending, Header:=xlYes Application.EnableEvents = True End Sub

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

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

ДНЕС()

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

Ако формулите не се преизчисляват често, можете да използвате събитието за активиране на лист - Worksheet_Activate - успоредно с Worksheet_Change