Sorbarendezés formulával - nem a legegyszerűbb módszer, de érdekes

2017. november 11. - Office Guru

A közelmúlt egyik Facebookon érkezett kérdését továbbgondolva a mai posztban a sorbarendezés formulával kezelhető módszerét fogom körbejárni röviden, hiszen alapvetően az emberek többsége (és be kell vallanom, hogy én is) a legnagyobb vagy legkisebb értékek megkeresésére a Home ribbonfül Editing szekciójának Sort & Filter almenüjét használná - sorbarendezne, szűrne és így tovább. Esetenként még be lehet vetni egy PIVOT-táblát is, de a legritkább esetben nyúlunk formulákhoz, pont ezért fogok most két problémára két lehetséges választ adni, nem azért, mert mostantól enélkül nem lehet élni - egyszerűen azért, hogy új lehetőségekkel is megismerkedjünk.

Adott a következő tábla:

rank1.JPGLáthatjuk, hogy három különböző megye járásait és a rájuk vonatkozó bevételadatokat tartalmazza az első három oszlop, a két üres oszlop fejléce pedig megmutatja, hogy pontosan mit is keresünk. Kezdésként a "Városok sorrendje" feliratú oszlopba szeretnénk beírni 1-től 19-ig a számokat a bevétel nagysága szerint.

Ez azért elég egyszerű történet, simán használjuk a beépített RANK (vagy RANK.EQ vagy RANK.AVG) függvényt:

rank2.JPGLátható, hogy alapvetően két paramétert kell megadnunk, egyrészt magát az értéket az első paraméterben, amit be akarunk rangsorolni, másrészt második paraméterben azt a tartományt, amin belül rangsorolni akarunk. Van egy opcionális harmadik paraméter is, a csökkenő/növekvő sorrend megadására adva lehetőséget.

Látható is, hogy ez megfelelően működik is:

rank3.JPGA másik kérdés egy kicsit azért komplexebb, hiszen a "Megyén belüli városok sorrendje" oszlopban a B oszlopban szereplő megyék szerint szeretnénk rangsorolni, azaz azt akarjuk megmondani, hogy mondjuk Caazapá megyében melyik járásban volt a legmagasabb a bevétel, melyikben a második legmagasabb és így tovább - mindezt értelemszerűen megyénként.

rank1_1.JPGA feladathoz mindössze a COUNTIFS függvényre van szükségünk, amely a COUNTIF "többesszámú" verziója és azon cellák számát adja vissza nekünk, amelyek egy vagy több feltételnek megfelelnek. Használhatunk dátum, szám vagy akár szöveges feltételeket is, plusz logikai operátorokat, mint < vagy >. És egyébként ez már meg is mutatja, hogy ez valójában majd nem az adott értéket rangsorolja, mint a RANK, hanem azt írja majd ki, hogy hány darab nagyobb érték van az adott cellánk értékénél a megadott feltételek (jelen esetben Megye) alapján.

Figyeljünk rá, hogy mivel a legnagyobb értéknél 0 nagyobb érték van, a végeredményhez, majd +1-et mindig hozzá kell adnunk. Szóval ez lenne a megoldás:

rank4.JPG=COUNTIFS($B$2:$B$20,B2,$D$2:$D$20,">"&D2)+1

Az első paraméter tehát a megyék oszlopa, ez ugyanis az első feltétel-tartományunk, a második paraméter a megyék oszlopának első értéke, hiszen mindig az aktuális sor megyéje lesz a feltétel, a harmadik paraméter a bevételek oszlopa, hiszen ez a második feltétel-tartományunk, a negyedik paraméter pedig egy logikai operátorral az aktuális sorunk bevétel értéke. Látható, hogy megnézzük, hogy az adott megyén belül az adott bevételértékhez képest hány darab nagyobb érték van még - és hogy pontos értékünk legyen, egyet mindenképpen adjunk hozzá a végeredményhez.

rank5.JPG

A bejegyzés trackback címe:

http://officeguru.blog.hu/api/trackback/id/tr8713234831

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben.

Nincsenek hozzászólások.