#050 Сортировка с формули - първа част

Темата е как без помощта на стандартните възможности Excel да се сортират данните от дадена област... Сортирането ще бъде извършено на ново място! Задачата се решава в две стъпки:
- номериране (в помощна колонка) данните според техния ранг
- копиране на данните на ново място според техния ранг
Както беше споменато това ще бъде извършено без използването на макроси или използването на стандартното сортиране!

В този тема ще обсъдим номерирането (и покрай него как се намира ранг (честен и нечестен):):)

I. Номериране на данните според техния ранг

1. Използване на функцията Ranк

Използването на израза "ранг" веднага може да ни наведе на идеята да използваме функцията =Rank.... В клетка B2 записваме формулата: =RANK(A2;$A$2:$A$7;1)

На първо четене това ни върши работа, но има две големи НО-та!:))

- какво да правим с дублажите
- какво да правим ако данните са текстови
Решаваме да поровим в помощната информация и откриваме, че има две разновидности на Rank! Rank.EQ и Rank.Avg. Да видим дали няма да ни свършат работа!

Разликата, както се вижда, е при еднакви данни. Но и в двата случая функциите връщат едно и същи ранг (в единия случай може да е дробен!) при еднакви стойности... Това от статистическа гледна точка е "честно" но за нас това не ни върши работа защото ние искаме еднозначно да бъдат разграничени редовете... За да може после да ги извличаме като отделни обекти! Може да зададете въпроса защо ни е да правим разлика коя стойност ще вземем! Ние правим варианта универсален за може не само да вземаме водещата колонка, но и някакви други данни покрай нея... Именно за това ни трябва еднозначно "нечестно" последователно маркиране....

Като добавим, че функцията Rank(.(Eq|Adv)) не работи с текст я бракуваме! :) NB! Функцията е доста полезна в други ситуации за това не я забравяйте!!

2. Използване на условно броене!

Ще започнем от по-лесната за разбиране формула: =COUNTIF($A$2:$A$9;"<="&A2)

Тук няма голям трик (важно е да се сетите че ранга е всъщност броене!;) ... Броим колко стойности има по-малки и равни на дадената стойност! Просто се използва оператора & за долепяне на "<=" към стойността в клетката A2! Тази формула работи и при текстови данни!!

До тук добре... Решихме проблемите с текстовите данни, но остана "НО-то" с дублажите!

Да помислим какъв "ъпгрейд" трябва да направим на горната формула. От броенето трябва да изключим всички стойности равни на дадената стойност в СЛЕДВАЩИТЕ клетки!! Просто нали?:) (Тук е момента да осмислите предишната формула още веднъж!:)

Ето новата формула:
=COUNTIF($A$2:$A$7;"<="&A2)-COUNTIF(A3:$A$8;"="&A2)

Тук става малкоо по-сложно (не не много):) Трикът е, че половината от зоната A3:$A$B е относителна (променяща се) а втората е константа! Т.е. при влачене формулата ще взима "следващите" клетки! Има още един "крив" момент! Не случайно втората зона е малкоо по-дълга от зоната с данни (стига до A8 а не до A7!). Това се налага поради факта, че ако беше сложено само A7 втората част на формулата щеше да изглежда при достигане на края на зоната с данни (B7) по следния начин: COUNTIF(A8:$A$7;"="&A7)!! И CountIF се побърква:( За това и се наложи това "криво" решение....

Всъщност може и без него:):) Ха сега:) Ако искате се опитайте да го откриете сами...! Трябват знания по математика от първи клас и логика от трети;):):) Отново се опитайте да разберете какво сме правили до сега!

Та...

Формулата е ...

=COUNTIF($A$2:$A$7;"<="&A2)-COUNTIF(A2:$A$7;"="&A2)+1!

Просто броим (вадим) и стойността от текущия ред и след което добавяме единица! Така се избягва "кривата" формула с две различни по дължина зони. Единствено остава трикът с "полу" абсолютната зона във втората част на формулата!

Дава същите резултати ....

... и работи и с текст!

Супер!:) Постигнахме "нечестно" ранжиране:) Т.е. последователно номериране по принципа при равен ранг да се взема по-предното местоположение в таблицата.

To be continue (в следваща тема):)