Нека сортираме таблица, състояща се от 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.

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

Възходящ ред на сортиране:

Числата ще бъдат сортирани по ред от най-малкото отрицателно до най-голямото положително число.

Стойностите за дата и час ще бъдат сортирани от най-ранната до най-новата стойност.

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

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

Стойностите на грешката ще бъдат сортирани в реда, в който са били открити (от гледна точка на сортиране всички те са еднакви).

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

Низходящ ред на сортиране:

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

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

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

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

За да сортирате списък, поставете показалеца на клетката вътре в списъка и изпълнете командата Данни – Сортиране.

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

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

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

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

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

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

Можете също да изберете да сортирате колони с диапазон вместо редове. За да направите това, щракнете върху бутона „Опции“ в диалоговия прозорец „Диапазон за сортиране“ и в диалоговия прозорец „Опции за сортиране“, в групата „Сортиране“, поставете превключвателя на позиция „Колони за диапазон“.

26. Филтриране на данни в Excel.

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

Автофилтър

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

Вмъкване на автоматичен филтър

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

2. В подменю Данни – Филтър изберете командата „Автофилтър”. Бутоните със стрелки ще се появят до имената на полетата, върху които можете да щракнете, за да отворите списъка.

3. Отворете списъка за полето, чиято стойност искате да използвате като филтър (критерий за избор). Списъкът ще покаже стойностите на клетките на избраното поле.

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

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

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

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

За да покажете отново всички записи в списъка на екрана, изпълнете командата „Покажи всички“ от подменюто Данни – Филтър.

Можете да премахнете критерия за филтриране за отделно поле, като изберете елемента „Всички“ в списъка за автоматично филтриране за това поле.

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

Прилагане на персонализиран автоматичен филтър

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

Вмъкнете автофилтър в списъка, като изберете командата Данни – Филтър – Автофилтър.

Отворете списъка с автофилтри за желаното поле и изберете елемента (Условие) в него.

В диалоговия прозорец „Персонализиран автофилтър“, който се отваря (фиг. 6.3.27.), посочете първия критерий.

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

Диалогов прозорец „Персонализиран автоматичен филтър“.

Можете да зададете един или два критерия за отделно поле в персонализиран автоматичен филтър. В последния случай те могат да се комбинират с логическия оператор “И” или “ИЛИ”.

Задайте втория критерий.

Щракнете върху бутона „OK“. Excel ще филтрира записите въз основа на зададените от вас критерии.

Разширен филтър

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

Диапазон от критерии

Критериите могат да бъдат зададени във всяко свободно място на работния лист. В обхвата на критериите можете да въведете и комбинирате два вида критерии:

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

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

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

Редът(овете) непосредствено под реда с имена на полета е мястото, където посочвате критериите. Excel не е чувствителен към главни и малки букви, когато задава критерии. Можете ли да използвате заместващи знаци, когато задавате критерии? И *.

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

Прилагане на разширен филтър

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

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

2. Изпълнете командата Данни – Филтър – Разширен филтър. Поставете курсора за въвеждане в полето „Диапазон на условията“ и маркирайте съответния диапазон в работния лист.

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

Можете да приложите само един разширен филтър на работен лист.

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

За да настроите показването на всички записи в списъка в работния лист след филтриране, изпълнете командата Данни – Филтър – Показване на всички.

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

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

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

Три основни алгоритма за сортиране в Excel.

1. числовистойностите се сортират според принципа „от най-малкото към най-голямото“ или обратно.

2. Колони с клетки, съдържащи текстстойностите са сортирани по азбучен ред в посоки напред или назад.

3. Колони с клетки, съдържащи дати, се сортират на принципа „от най-старите към най-новите“ или обратно.

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

Нека продължим да работим с базата данни DB2 „Производство на метални конструкции по обект № 2“, създадена в статията „ ».

Разглежданата база данни за обучение се състои само от 6 полета (колони) и 10 записа (редове). Реалните бази данни обикновено съдържат повече от дузина полета и хиляди записи! Намирането на необходимата информация в такава таблица не е лесно! Именно през призмата на такова разбиране трябва да гледаме на следващите си действия.

По-нататъшната работа ще бъде структурирана по следния начин: в тази и следващите статии от тази серия ще използваме различните възможности на програмата Г-ЦА Excel , формулират незабавни отговори на различни въпроси и задачи, които възникват в практическата дейност на ръководителя на производствения обект. Тоест ще решим реални производствени проблеми!

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

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

Най-простото сортиране.

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

Задача №1:

Определете: кой от произведените продукти е най-тежкият и каква е неговата маса? Кога е произведен този продукт?

1. Отворете файла в MS Excel.

2. Активираме, като щракнете върху клетка E7 със заглавие на колоната „Тегло на 1 брой, t“ (можете да активирате всяка клетка в колоната, която ни интересува).

3. Щракнете върху бутона „Сортиране в низходящ ред“ в лентата с инструменти „Стандартно“.

4. Четем отговора на зададения въпрос в най-горния ред на базата данни (ред № 8). Най-тежкият продукт в базата данни е Beam 045 от поръчка № 2. Beam 045 е произведен от 23 април до 25 април 2014 г. (вижте записи в Excel редове № 8-10).

5. Можете да върнете базата данни във формуляра преди сортиране в Excel (ако е необходимо), като щракнете върху бутона „Отказ“ на същата лента с инструменти „Стандарт“. Или можете да приложите сортиране по възходящ ред към колоната за дата на базата данни.

Сортиране в Excel по множество колони.

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

Задача #2:

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

1. Активираме всяка клетка от база данни, като щракнете с мишката (например клетка C11).

2. Щракнете върху бутона на главното меню „Данни“ и изберете „Сортиране...“.

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

4. Задача № 2 изпълнена. Записите са, първо, сортирани по номера на поръчки и, второ, във всяка поръчка са подредени по азбучен ред по имена на продукти.

Резултати.

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

Честно казано, на практика рядко се налага да прибягвате до сортиране. Защо? Отговорът на този въпрос ще намерите в следващите статии от поредицата, които ще говорят за други, включително по-модерни и мощни механизми на Excel за бърза и ефективна обработка на данни.

аз моля почтителен авторска работа Абонирай се за съобщения за статии в прозореца, намиращ се в края на всяка статия или в прозореца в горната част на страницата!

Уважаеми читатели, напишете своите отзиви и коментари в коментарите.

Сортирането на данни, разположени в областта на редове и колони на обобщена таблица, се извършва по подразбиране във възходящ ред (фиг. 1а) или с помощта на персонализирани списъци за сортиране. Това не винаги отговаря на потребителя. Например, когато искате да покажете клиенти с най-висок доход в горната част на списъка (фиг. 1b). Ако вашата обобщена таблица е сортирана във възходящ (низходящ) ред, трябва да създадете правило, което контролира реда на сортиране по поле. Освен това, това правило (за това поле) ще бъде приложено дори след добавяне на нови полета към обобщената таблица (фиг. 1c).

Ориз. 1. Сортиране по поле Клиент: (а) по подразбиране – от А до Я; б) в низходящ ред на приходите; (c) подреждане по поле Клиентне се промени при добавяне на полето Сектор

Изтеглете бележката в или формат, примери във формат

Сортиране на клиентите в низходящ ред на доходите

За да сортирате редовете на обобщената таблица в низходящ ред на приходите, изберете която и да е клетка в колоната Сума по поле Приходи, например E4 (но не и заглавието), и щракнете върху иконата JAразположен на раздела Данни(фиг. 2). Това сортиране наподобява стандартното, но това е само повърхностна прилика. Когато сортирате обобщена таблица, Excel създава правило, което ще продължи да работи, след като направите допълнителни промени в обобщената таблица.

Като използвате примера на обобщена таблица, разположена в колони G:I (фиг. 1c), можете да видите какво ще се случи след добавяне на ново поле за външен ред Сектор. Обобщената таблица продължава да сортира данните в низходящ ред на приходите във всеки сектор. Например в сектора производство General Motors е на първо място с приходи от $750 163. Ford следва плътно след него с приходи от $622 794. Дори да изтриете полето Клиентот обобщената таблица, направете допълнителни настройки и върнете това поле обратно, но в областта на колоните Excel ще запомни сортирането на клиентите в низходящ ред на дохода.

Така че в обобщената таблица, разположена в колони G:I (фиг. 1c), секторите също са сортирани в низходящ ред на доходите, можете да отидете по един от трите начина:

• Изберете клетка G4, щракнете с десния бутон и изберете Свиване на всичкиполеза скриване на всички елементи, които се отнасят до клиента. След като на екрана се покаже само един сектор, изберете клетка I4 и щракнете върху иконата YA в раздела Данниза извършване на сортиране в низходящ ред. Това ще създаде правило за сортиране за полето Сектор. Изберете отново клетка G4, щракнете с десния бутон и изберете Разгънете всичкиполе.
• Временно премахнете полето Клиентот осевата таблица, сортирайте таблицата в низходящ ред на приходите (използвайки метода, описан на фиг. 2), и след това върнете полето отново Клиент.
• Възползвайте се от възможностите на екипа Допълнителеннастроикисортиране(Използвам точно този метод). За да извикате командата: (a) Изберете клетка G4, щракнете с десния бутон и изберете Сортиране → Допълнителеннастроикисортиране(Фиг. 3) или (b) щракнете върху иконата с триъгълник в полето Сектори след това изберете Допълнителеннастроикисортиране(фиг. 4). И в двата случая ще се отвори прозорец Сортиране(фиг. 5). Поставете превключвателя на позиция низходящи изберете линията Сума по поле Приходи.

Ориз. 3. Извикване на команда ДопълнителеннастроикисортиранеКликнете с десния бутон

Ориз. 4. Извикване на команда Допълнителеннастроикисортиранес помощта на менюто Сортиране и филтриполета Сектор

Ориз. 5. Задаване на параметри в прозореца Сектор

В долния ляв ъгъл на диалоговия прозорец Сортиранеима бутон Разширени... След като щракнете върху този бутон, на екрана ще се появи диалогов прозорец . В този прозорец можете: (a) да дефинирате персонализиран списък, който ще се използва за сортиране по първия ключ (вижте подробности по-долу); б) вместо колона общоИзберете друга колона като основна колона за сортиране.

Например, за обобщената таблица, показана на фиг. 6, можете да зададете сортирането не по общ доход, а по доход от продажба на един вид стоки, например, устройства(имайте предвид, че клиентите не са сортирани по колона F, а по колона C).

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

За сортиране по този начин:

1. Разгънете списъка с клиенти, разположен в клетка A4.
2. Изберете опцията Още опции за сортиране.
3. В диалоговия прозорец Сортиране (клиент)щракнете върху бутона Допълнително…
4. В диалоговия прозорец Допълнителни опции за сортиране (Клиент)Изберете раздел Ред на сортиранеи задайте превключвателя Стойности в маркираната колона.
5. Щракнете в полето за връзка и след това изберете клетка C5. Имайте предвид, че трябва да щракнете върху една от клетките със стойност устройство, защото на загл устройствоКлетка C4 не може да бъде щракната.
6. За да завършите настройката на параметрите, щракнете два пъти върху OK.

Не се тревожете, описанието на този алгоритъм стъпка по стъпка е по-скоро с образователна цел. Започвайки с Excel 2013, сортирането на данни от обобщена таблица стана много по-лесно. Сега бутоните YA и AYA са в раздела Данниизползвайте интелигентни алгоритми за сортиране. Когато се опитвате да сортирате с помощта на тези бутони, програмата ще се опита да предвиди намеренията на потребителя въз основа на това коя клетка е маркирана, преди да натисне бутона за сортиране (Фигура 7):

• A1, C1, D1, E1, F1, F2, A30, F30 – не се предлага
• A2:A29 – ще подреди по азбучен ред имената на клиентите в колона A
• В1, В2, С2, D2, E2 – ще подреди по азбучен ред имената на стоките в ред 2
• B30, C30, D30, E30 – ще подреди в низходящ (възходящ) ред сумата на дохода в ред 30
• възходящи (низходящи) продажби B3:B29 – модули, C3:C29 – устройства, D3:D29 – части, E3:E29 – лекарства, F3:F29 – общо.

Ръчно сортиране

Моля, обърнете внимание, че в диалоговия прозорец Сортиране(вижте фиг. 5) можете ръчно да дефинирате правила за сортиране на данни. Но ръчното сортиране на обобщена таблица също се извършва по различен, доста необичаен начин. В отчета с обобщена таблица на фиг. 8а показва последователността от продуктови категории, подредени по азбучен ред: Част, Модул, ПодготовкаИ устройство. Моля, обърнете внимание, че обемът на продадените продукти по категория детайл, не най-великият. И едва ли си струва първо да показвате тази категория. Поставете показалеца на мишката в клетка E4 и въведете думата детайл. Просто натиснете клавиш ВъведетеКак Excel ще определи, че сте решили да преместите колона детайлкъм последната колона на таблицата. Всички числени стойности, свързани с тази продуктова категория, ще се преместят от колона B в колона E. Стойностите, свързани с други продуктови категории, ще се преместят вляво. Това поведение изглежда нелогично и е типично само за обобщените таблици на Excel. Обикновен набор от данни на Excel не може да бъде пренареден по този начин. На фиг. Фигура 8b показва обобщената таблица след преместване на заглавката на новата колона в клетка E4.

Ориз. 8. Ръчно сортиране: (a) продуктовите категории се сортират по азбучен ред, (b) категорията Част се поставя последна

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

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

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

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

1. В зоната без данни на работния лист въведете имената на продуктовите категории в последователността, която съответства на персонализирания списък, който създавате. Във всяка клетка въведете по едно име и поставете имената в една колона (фиг. 9).
2. Изберете получения списък с имена на продуктови категории (клетки A10:A13).
3. Изберете раздел на лента Файли в долната част на лентата за навигация, показана в прозореца отляво, щракнете върху бутона Настроикиза да отворите диалогов прозорец Опции на Excel.
4. Избери категория Допълнително, отидете на раздел са често срещании щракнете върху бутона Редактиране на списъци.
5. В диалоговия прозорец Списъцив полето се показва адресът на диапазона, съдържащ предварително избрания списък от заглавия Импортиране на списък от клетки(фиг. 10). Щракнете върху бутона Импортиранеза генериране на нов списък с продуктови категории въз основа на посочените данни. Нов списък се добавя в долната част на областта Списъци.
6. Щракнете върху OK, за да затворите диалоговия прозорец Списъци. Щракнете върху OK отново, за да затворите диалоговия прозорец Опции на Excel.

Ориз. 10. Прозорец Списъци

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

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

1. Разширете списъка с полета Продукти изберете опция Допълнителни опции за сортиране.
2. В диалоговия прозорец Сортиране (продукт)бутон за избор възходящо (от А до Я) по поле, и от падащия списък изберете Продукт.
3. Щракнете върху бутона Допълнително…
4. В диалоговия прозорец Допълнителни опции за сортиране (продукт)премахнете отметката от квадратчето Автоматично сортиране.
5. Разширете списъка Сортиране по първи ключи изберете списък, който включва имената на продуктовите категории (фиг. 12).
6. Щракнете двукратно върху OK.

Бележката е написана по книгата на Бил Джелен, Майкъл Александър. . Глава 4.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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