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

• #19058 Mutt senior tag dudikpal #19057

  Új Válasz 2013-09-25 20:55:13 #19058

  Ú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 dudikpal #19057 üzenetére

  Hello,

  Én néztem be, mivel jól írtad le a kérést.

  Az eredeti probléma megoldásához az INDEX függvény kell, amelynek a képlete INDEX(tartomány;kívánt sor száma).

  Röviden ezek a képletek működnek 2007 vagy újabb Excelben:
  A1-be:
  =HAHIBA(INDEX(VÁLASZT(ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1;E:E;F:F;G:G);DARABHATÖBB(B$1:B1;B1;C$1:C1;C1)+2);"")

  B1-be:
  =HAHIBA(INDEX($E$1:$G$1;ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1);"")

  C1-be:
  =HAHIBA(INDEX($E$2:$G$2;ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1);"")

  Felraktam mindkét változtatot ide.

  

  Hosszabban:
  Az INDEX-nél nem a tartománnyal van gond igazán, hanem hogy mindig a szükséges sort adja vissza a tartományból. Itt tehát ennek a képlete a kulcs, hiszen a példádon is látható hogy először 1-től 6-ig kell egy sorszámot visszadni, majd után 1 és 3 és végül 1 és 5 között kell nekünk egy szám.

  Na erre a problémára ezt a képletet alkottam:
  ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))

  Hozzáteszem, hogy természetesen van egyszerűbb változat is (HA függvények egymásba ágyazása), de annak bővíthetősége nehézkes.

  A fenti képlet valójában 3x ugyanaz, nézzük egy példán:
  ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))

  A függvény 3 értéket adhat vissza (-1;0;1) attól függően, hogy az éppen vizsgált sor száma (SOR függvény) az E:F tartományban lévő számokat tartalmazó cellák számánál több-e vagy kevesebb vagy egyenlő.
  Konkrétan: ha az 5-ös sorban vagyunk, akkor 0-t fogunk kapni (=(5-1)/9), ami azt jelenti hogy az E:F oszlopokból elég választanunk cellát, mivel abban 9 van de nekünk ebből a 9-ből csak az 5 kell.
  Ha a 14-es sorban vagyunk akkor pedig 1 lesz, az érték ami azt jelenti hogy nem az E:F oszlopokban van a keresett cella.

  A 3 db előjeles képlet csak a DARAB függvény paraméterében változik, az elsőben csak az E-oszlopokat nézzük, a másodikban már az E:F oszlopokat és a harmadikban már az E:G oszlopokat. Mindegyik rész 0-t vagy 1-et fog visszadni a fentiek szerint, de példánál maradva az első 6 sorra 0-t kapunk, 7-9-es sorokra 1-et, 10-14-es sorokra 2-öt, felette pedig 3-at.

  Ezzel a függvénnyel a neheze már meg is van, de nézzük meg a többit.

  Kezdem a B-oszlophoz tartozóval, mert ez az egyszerűbb.
  Itt semmi más nem kell mint az E1:G1 tartományból kiírni vagy az első vagy a második vagy a harmadik értéket. A kulcs függvényünk pont erre lett kifejlesztve, így a képlet gyorsan meg is van:

  =INDEX($E$1:$G$1;ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1)

  A C-oszlophoz ugyanez a logika kell csak ott E2:G2 tartományból kell kivenni az értéket, a képlet csak ebben változik.

  Az A-oszlop ezek után már könnyedebb dolog lesz, de itt az a gond hogy eleinte E-oszlopból kell érték, majd F-ből és később G-ből. Itt az a megoldásom, hogy a VÁLASZT függvényt használjuk. Amelynek a szintaxisa: VÁLASZT(index szám;érték1;érték2.....).
  Egy példán keresztül: VÁLASZT(2;"Férfi";"Nő";"Ismeretlen") eredménye "Nő" lesz mivel a második értéket kértük vissza a függvénytől.
  A kulcs függvényünk pont egy sorszámot ad vissza - valójában egyet hozzá kell adnom, mert a tömb nem a nulladik hanem az első elemmel indul -, vagyis először E:E oszlop kell nekünk, majd F és végül G:G. Ezek alapján a
  VÁLASZT(ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1;E:E;F:F;G:G)
  attól függően, hogy melyik sorban vagyunk E:E; F vagy G:G-t fog visszadni, amit az INDEX függvényben azonnal fel is tudunk használni.

  Az INDEX-nek azonban kell egy sorszám hogy hanyadik értéket kell a bemeneti tartományból visszadni. Erre kell egy újabb képlet. Itt a DARABHATÖBB-et használtam (Excel 2007 alatt SZORZATÖSSZEG segít).
  Egyszerűen megszámolom az aktuális sorban hogy mellette a B és C-oszlopban szereplő értékek hányszor szerepeltek. A példára visszatérve pl. az első sorban az "a" és a "b" először szerepel, így E-oszlopból az első szám kell, a 4-ik sorban pedig már 4x szerepel így 4. szám kell az E-oszlopból.
  A képlet így néz ki: DARABHATÖBB(B$1:B1;B1;C$1:C1;C1)
  Itt fontos, hogy mindegyik esetben a tartomány első fele fixen van rögzítve a második pedig fut!

  Megis vagyunk mert tudjuk a tartományt és hogy hanyadikelem - itt kettőt hozzáadtam mert az első kettőt figyelmen kívül kell hagynunk - kell belőle. A képlet vagyis ez lesz:
  INDEX(VÁLASZT(ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1;E:E;F:F;G:G);DARABHATÖBB(B$1:B1;B1;C$1:C1;C1)+2)

  Ezzel vége is, persze egy kis HAHIBA függvénnyel kozmetikáztam.

  üdv.

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