Új hozzászólás Aktív témák

• #20948 Mutt senior tag

  Új Válasz 2014-03-15 21:55:34 #20948

  Új hozzászólás

  Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra

  Privát üzenet küldése

  

  Mutt

  senior tag

  Sziasztok,

  Egy másik fórumon jelent meg egy olyan kérés, hogy meg kellene számolni egy oszlopban hogy egymás után hányszor szerepel pozitív illetve negatív szám, és a legtöbb előfordulást kellene megadni. Mindezt segédoszlopok nélkül. VBA megoldás született rá, de itt hadd mutassam be képlettel is.

  Pozitív számra ez a képlet (Excel 2010 vagy felette):
  =HA(DARABTELI(A:A;">0")=0;0;MAX(GYAKORISÁG(HA(A:A>0;SOR(A:A));HA(A:A<=0;SOR(A:A)))))

  Negatívra:
  =HA(DARABTELI(A:A;"<0")=0;0;MAX(GYAKORISÁG(HA(A:A<0;SOR(A:A));HA(A:A>=0;SOR(A:A)))))

  Természetesen Ctrl+Shift+Enter-el kell bevinni.

  A pozitív számokhoz egy példán keresztül megpróbálom elmagyarázni, hogy működik a képlet.
  

  A képen A-oszlopban az adatok, a C-F oszlopokban pedig a lenti magyarázathoz részeredmények láthatók.
  1. A képlet magja a GYAKORISÁG függvény, amely egy halmazból és egy keresési csoportból áll. A függvényről a súgóban lehet olvasni, példát látni. Ha az eredeti számokat adnánk meg, akkor csak azt tudnánk meg, hogy melyik szám hányszor fordul elő, ami most nem jó.
  2. A megoldás, hogy a szám helyett a sor számát írjuk ki, annyival megspékelve hogy ha negatív a szám, akkor hibát iratunk (most a leghosszabb pozitív számsort keressük). Ennek a képlete HA(A:A>0;SOR(A:A)) és eredményét a C-oszlopban látjuk. Ez lesz a gyakoriság függvény első fele.
  3. A második fele a gyakoriságnak pont az ellentétje a fentinek, tehát ha negtív szám van a cellában akkor a kell a sor száma. A képlet HA(A:A<=0;SOR(A:A)) és eredménye a D-oszlopban van.
  4. Most jön a gyakoriság, amely a keresési csoportokat nézi (D-oszlop emlékeztetőül) és onnan csak a számokat veszi figyelembe (a HIÁNYZIK-ot kihagyja) és megszámolja hogy két csoport között az adathalmazban hányszor fordul elő szám.
  A könnyebb érthetőség miatt az E-oszlopba beírtam a keresési csoportokat, amelyek számok a D-ből 2 eltéréssel: az első csoport (ami most az 1-et tartalmazza) az valójában kisebb és egyenlő 1 értelmezendő, a másik pedig hogy mindig eggyel több csoportot használ a függvény így a 13 után van még egy nagyobb mint 13 is.
  A fentiek alapján a C-oszlopban lévő számokat számolgatja, ezt az F-oszlopban látjátok.
  pl. a 6 melletti 4 azt jelenti, hogy 1 (az előző csoport) és 6 között 4 szám a C-oszlopban, vagy pl. 13-nál kettő nagyobb van.
  5. Ezek az értékek pedig pont a pozitív számok előfordulását jelentik negatív számok között. Így már csak a maximum kell nekünk innen.
  6. A végén a téves visszajelzés miatt - ami akkor lehet ha a listában egyetlen pozitív szám sincsen - van egy figyelés (DARABTELI(A:A;">0")=0) és egyből 0-t írunk.

  üdv

Új hozzászólás Aktív témák