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

• #40995 Mutt senior tag Lev07 #40986

  Új Válasz 2019-05-05 11:49:18 #40995

  Ú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 Lev07 #40986 üzenetére

  Szia,

  Ha van ÖSSZESÍT függvény, akkor van Power Query is.

  Lépések:
  1. Legyen két táblázat a fájlban, az első tartalmazza a játék eredményeket, a második pedig hogy melyik csapat melyik eredménye érdekel fordított sorrendben. Lehet akár több csapat is eltérő adatokkal. A táblázatok neve Table1 és Table2 nálam.
  

  2. Töltsük be Power Query elöbb a második táblát. Álljunk bele a táblázatba és Adatok -> Adatok beolvasása -> Táblázatból.

  3. Jelöld ki a második oszlopot és Kezdőlap -> Oszlop felosztása -> Elválasztó alapján.
  

  4. Menjünk visssza Excel-be, Kezdőlap -> Bezárás és betöltés -> .. adott helyre. A képernyőn "Csak kapcsolat létrehozása" legyen kiválasztva.
  

  5. Menjünk át a másik táblázatba és azt is töltsük be Power Query-be.

  6. Első lépésként adjunk a listához egy index oszlopot, Oszlop hozzáadása -> Indexoszlop.
  

  7. Az indexoszlop csökkenő sorrendbe rendezésével megfordítjuk a listát, vagyis előre kerülnek az utolsó eredmények. Oszlopot jelöld ki, majd Kezdőlap -> Rendezés -> Csökkenő sorrend.

  8. Válasszuk ki a minket érdeklő adatokat, Kezdőlap -> Összevonás -> Lekérdezések egyesítése.
  Table2 -t alulra kiválasztod, majd a kulcsmezőket megadod és végül az egyező sorokat beállítjuk.
  

  9. Ezek után csapatonként kell létrehozni egy újabb sorrendet. Ehhez előbb a Kezdőlap -> Csoportosítást használjuk.
  

  A képletben ha az each _ részt lecseréljük each Table.AddIndexColumn(_, "Index1", 1) -re, akkor minden csoportban lesz relatív sorrendünk.
  

  A képlet javítása után ez lesz az eredmény:
  

  10. Az utolsó oszlop jobb felső sarkába kattintva jelenítsük meg a rúgott és az index1 értékeket.
  

  11. A 8-as lépést újra játszuk el, annyi különbséggel hogy most már a melyik oszlopot és az index1-et is kiválasztjuk.
  

  12. Jön az utolsó csoportosítás a rúgótt gólokra.
  

  13. A végeredményt visszatöltjük Excelbe, Kezdőlap -> Bezárás és betöltéssel.

  

  üdv

• #40994 Mutt senior tag Lev07 #40986

  Új Válasz 2019-05-05 10:06:41 #40994

  Ú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 Lev07 #40986 üzenetére

  Szia,

  Az ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);1) megmondja az utolsó sor számát, ezt a sort kell kizárni a SZORZATÖSSZEG-ből.

  =SZORZATÖSSZEG(($S$2:$S$1000=$X$2)*(SOR($S$2:$S$1000)>=MIN(HAHIBA(ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);{1;2;3;4;5;6});2^99)))*(SOR($S$2:$S$1000)<ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);1))*($T$2:$T$1000))

  Vagy itt van egy általánosabb képlet, ahhoz hogy az N-edik előforduláshoz tartozó gólok számád megkapd:

  =HAHIBA(SZUM(INDEX($T$1:$T$500;ÖSSZESÍT(14;6;SOR($S$2:$S$500)/($S$2:$S$500=$X$2);N);0));0)

  Ha szükséged van hátulról a második és negyedik eredményre, akkor csak össze kell adnod őket:
  =HAHIBA(SZUM(INDEX($T$1:$T$500;ÖSSZESÍT(14;6;SOR($S$2:$S$500)/($S$2:$S$500=$X$2);2);0));0)+HAHIBA(SZUM(INDEX($T$1:$T$500;ÖSSZESÍT(14;6;SOR($S$2:$S$500)/($S$2:$S$500=$X$2);4);0));0)

  üdv

• #40902 Mutt senior tag Lev07 #40887

  Új Válasz 2019-04-25 19:46:13 #40902

  Ú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 Lev07 #40887 üzenetére

  Szia,

  Szeretném egy adott csapat 6 utolsó hazai meccsén a hazai csapat által rúgott gólok számát összegezni.

  Remélem Excel 2010 vagy frissebb verziód van, mert akkor ez a képlet működni fog (az első 1000 sorra, ha többed van akkor nyugodtan terjeszd ki a tartományt, de teljes oszlopra nem fog működni):
  =SZORZATÖSSZEG(($S$2:$S$1000=$X$2)*(SOR($S$2:$S$1000)>=MIN(HAHIBA(ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);{1;2;3;4;5;6});2^99)))*($T$2:$T$1000))

  

  Hogy működik?

  1. A csak Excel 2010-től elérhető ÖSSZESÍT (AGGREGATE) függvénnyel megkeressük, hogy az X2 cellában található csapat mely sorokban van az S-oszlopban és abból a 6 legnagyobb értéket visszakérjük.
  Pl. AS Roma esetén 24 és 4-es sorokat fogjuk megkapni a mutatott minta alapján.

  2. Az előbb kapott eredmény alapján tudjuk már, hogy a 4-es sortól lefelé akárhol fordul elő az AS Roma az az utolsó 6 meccsét fogja jelenteni. Ezért van az ÖSSZESÍT egy MIN függvénybe téve, illetve ha nincs 6 meccse akkor egy HAHIBA függvénnyel ezt is kezeljük.

  3. Ezek után már csak összegeznünk kell azokakat a sorokat, ahol AS Roma van az S oszlopoban és a sor száma nagyobb vagy egyenlő mint a 2-es lépésben kapott sorszám. A SZORZATÖSSZEG (SUMPRODUCT) tud ebben segíteni.

  üdv

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