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

• #51359 Mutt senior tag ben11 #51356

  Új Válasz 2023-08-28 11:37:21 #51359

  Ú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

  válasz ben11 #51356 üzenetére

  Szia,

  "...mi alapján működik a tartományok elkülönítése?"

  A DARABHATÖBB esetén az első két páros feltétel ($B:$B;$B2 és $D:$D;$D2) válogatja ki, hogy az adott sor melyik tartományba kerül, mivel mindig az aktuális sor értékelőivel ($B2 és $D2 feltétel, ami lefelé húzva mindig az aktuális sorhoz fog igazodni) megegyező értékeket mutat. A DARABTELI/DARABHATÖBB kevésbé használt funkciója, hogy nem csak a konkrét egyezéseket lehet megszámolni hanem lehet kisebb vagy nagyobb relációt is használni és ezzel sorrendet is lehet számolni.

  A korrelációhoz más megoldás kell, mivel oda cellatartományt (range) kell megadni, amit INDEX / ELTOLÁS / XKERES-al szoktunk meghatározni dinamikusan. Itt a segít az ÖSSZESÍT függvény, amely tud tömb-műveleteket elvégezni (ezek olyan műveletek amikor egy lépésben több cellával dolgozunk és eredményként az összes cellához kapott értéket megkapjuk). Előnye még ennek a függvénynek, hogy nem kell Control+Shift+Enter (CSE)-rel bevinni (a SZORZATÖSSZEG még ilyen), a többi függvényhez viszont Excel 2021-ig kell használni. Végül pedig egy hasznos opciója az ÖSSZESÍT-nek hogy a hibás értékeket tudja kezelni, ez a második paraméter (a 6 azt jelenti, hogy ugorja át a hibás értékeket).
  A képletben HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0) a lényeg a tartományok kiválasztásánál, ugyanúgy mint a DARABHATÖBB-nél az aktuális sor értékelőit keresi meg a B és D-oszlopokban (pontosabban az első 10 ezer sorában). Ha egyezik akkor 1-et ad vissza, ha nem akkor 0-t. A két feltétel között szorzás van, ami az ÉS kapcsolatnak felel meg (csábító lenne az ÉS függvény használata helyette, de az nem add vissza tömböt. Megjegyzés: haa VAGY kapcsolatra lenne szükség akkor plusz jel kellene ide.). Szóval kapunk 10 ezer db eredményt amik között lesz 1 és 0, ezzel ha elosztjuk a sorszámokat, akkor ahol egyezés van ott számot kapunk minden más esetben a 0-val osztás miatt hibát. Ekkor jön a 6-os opciója az ÖSSZESÍT-nek, ami csak a számokat tartja meg ahonnan már csak a legkisebb és legnagyobb számokat kell megtartanunk hogy tudjuk mettől-meddig tart a tartomány.
  
  A végén az INDEX-el használom ezeket (ELTOLÁS is lehetne ahogy Fferi mutatja, de ezzel az a gond, hogy lassítja a füzetet, miert minden esetben újraszámolja az értékeket feleslegesen. Angolul [L:https://www.youtube.com/watch?v=Jev5ATXwnOs]Mynda videóját[/L] javaslom erről).
  
  üdv

• #51357 Fferi50 Topikgazda ben11 #51356

  Új Válasz 2023-08-27 23:02:20 #51357

  Ú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

  

  Fferi50

  Topikgazda

  válasz ben11 #51356 üzenetére

  Szia!
  Csak hogy még jobban főjön a fejed, itt egy másik megoldás:
  A könnyebb képletezés miatt két tartományt a névkezelőben neveztem el:
  értékelő1 =ELTOLÁS(Munka1!$B$1;0;0;DARAB2(Munka1!$B$1:$B$3000);1)
  értékelő2 =ELTOLÁS(Munka1!$D$1;0;0;DARAB2(Munka1!$D$1:$D$3001);1) A két név mindig az adott oszlop aktuálisan kitöltött részére hivatkozik. Ha 3000-nél több sorodban lenne adat, akkor légy szíves módosítani a 3000-t a végén. FIgyelj a $ jelekre kérlek.
  Ezután a képletek:
  F2 cella =RANG.ÁTL($C2;INDEX(ELTOLÁS($C$1;HOL.VAN($B2;értékelő1;0)-1;0;DARABHA(értékelő1;$B2);1);0;1);0)
  G2 cella
  =RANG.ÁTL($E2;INDEX(ELTOLÁS($E$1;HOL.VAN($D2;értékelő2;0)-1;0;DARABHA(értékelő2;$D2);1);0;1);0)
  A két képlet húzható lefelé, ameddig adatod van.
  A DARABHA függvény korábbi Excel verziókban DARABTELI névre hallgatott, de úgy gondolom nálad már ez van. Mielőtt a korreláció képletet mutatnám, a tartományok szétválasztásának módszeréről írok:
  A magyarázat az F2 cella függvényeit veszi alapul:
  Az INDEX függvény képes tartományt visszaadni a következőképpen: Ha a sor paraméter értékét 0 -ra állítjuk be, akkor a tartomány adott oszlopát, ha az oszlop paraméter értéke 0, akkor a teljes sort adja vissza. (Figyelem, nem elhagyjuk a sort/oszlopot, hanem 0-t írunk be paraméternek.)
  A számunkra szükséges tartományt pedig az ELTOLÁS (lánykori nevén OFFSET) függvény és a HOL.VAN (MATCH) függvény használatával kaphatjuk meg:
  Az ELTOLÁS függvény paraméterei:
  
  1. A cella címe, ahonnan a tartományt elmozdítjuk. (A fix pont, ahonnan Arkhimédész kimozdítaná a Földet... )
  Ez nálunk a C1 cella, mivel itt kezdődnek az értékelő1 által adott eredmények
  2. A sorok száma, amennyivel lejjebb-feljebb tesszük a kezdősor értékét.
  Ez lesz annak a sornak a száma, ahol az értékelő1 először fordul elő a B oszlopban (az értékelő1 elnevezésű tartományban). HOL.VAN($B2;értékelő1;0)-1 Azért kell a végére -1, mert a függvény a paraméter úgy használja, hogy a kiinduló sorhoz hozzáadja a sor eltolás mértékét. MIvel az első találat a 2. sorban lesz és az elsőből indulunk, ezért vonunk le 1-et.
  3. Az oszlopok száma: esetünkben 0, mert a kiinduló oszlopban maradunk
  4. Magasság: Az új tartomány sorainak száma: DARABHA(értékelő1;$B2) ahányszor előfordul az első értékelő neve a tartományban.
  5. Szélesség: Az új tartomány oszlopainak száma: esetünkben 1, mivel maradunk az eddig oszlopban.
  Így az új tartományunk a C2 cellában kezdődik és a C7 cellában végződik. Ezt az oszlop tartományt adja vissza nekünk az INDEX függvény, mivel a sor paraméternek 0-t adtunk meg.
  Most pedig a korreláció függvény: Egyszerűbb képlet:
  H7 cellába =KORREL($F$2:$F$7;$G$2:$G$7)
  H11 cellába =KORREL($F$8:$F$11;$G$8:$G$11)
  Ezeket gondolom nem okozna nagy problémát beírni akár egyenként.
  De lehet automatizálni:
  I2 cella képlete:
  =KORREL(INDEX(ELTOLÁS($F$1;HOL.VAN($B2;értékelő1;0)-1;0;DARABHA(értékelő1;$B2);1);0;1);INDEX(ELTOLÁS($G$1;HOL.VAN($B2;értékelő1;0)-1;0;DARABHA(értékelő1;$B2);1);0;1))
  Ez húzható lefelé. Ezután az ízlésednek megfelelő cellában hagyod meg a képletet.
  FIGYELEM!
  A tartományok ilyetén szétválasztása csak akkor működik, ha az értékelők szerint sorba van rendezve a táblázatod.
  Ne ijedj meg, megcsinálni sokkal könnyebb és rövidebb ideig tart, mint ezt az egészet elolvasni.
  Üdv.

• #51355 Mutt senior tag ben11 #51352

  Új Válasz 2023-08-27 18:11:18 #51355

  Ú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

  válasz ben11 #51352 üzenetére

  Szia,

  Milyen Excel verziót használsz?

  Egy 2010-től működő megoldás két segédoszloppal, hogy rövidebbek legyenek a képletek.
  

  Az F2-ben az alábbi képlet van:
  =DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;">"&$C2)+1/DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2)

  (Ez egyenlőség esetén nem pont azt a számot mutatja, mint a mintádban van 1,5 v. 2,5. Ha a te számod kell akkor ez a képlet:
  =DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;">"&$C2)+HA(DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2)>1;1+1/DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2);1)

  A G2-ben a képlet majdnem ugyanaz csak a ">" és "=" jelek után a C2-t E2-re kell cserélned.

  A korrellációhoz tudnunk kell hogy melyik sortól kezdődig és melyik sorig tart az összefüggő tartomány, erre van a két segédoszlop (min/max).
  I2-ben a képlet:
  =ÖSSZESÍT(15;6;SOR($C$2:$C$10000)/HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0);1)
  J2-ben majdnem ugyanaz, csak a NAGY függvényt kell használni.
  =ÖSSZESÍT(14;6;SOR($C$2:$C$10000)/HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0);1)

  Ezek után H2-ben a képlet:
  =HA(SOR()=$J2;KORREL(INDEX($F$1:$F$10000;$I2):INDEX($F$1:$F$10000;$J2);INDEX($G$1:$G$10000;$I2):INDEX($G$1:$G$10000;$J2));"")

  üdv

• #51353 Delila_1 veterán ben11 #51352

  Új Válasz 2023-08-27 12:14:20 #51353

  Ú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

  

  Delila_1

  veterán

  válasz ben11 #51352 üzenetére

  Szia!
  Az értékelő oszlopból készíts egy másolatot, majd alkalmazd az Ismétlődések eltávolítása menüpontot. Ide már a DARABHA függvénnyel meg tudod adni, melyik értékelő hányszor szerepel a B oszlopban.

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