[Megoldva] Cella tartomány megadása képlettel

Táblázatkezelő
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

[Megoldva] Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Sziasztok!

A következő problémába ütköztem és ebben kérném a segítségeteket: szeretném függvényben a szükséges cellatartományt képlettel megadni. Konkrétan arra gondolok, hogy a Munkalap37.A4:O142 formátum helyett olyan módszerre lenne szükségem, mellyel pl. az A4-ből a 4 helyett A(4+Munkalap38.C5) szerepeljen. Megoldható ez?
A hozzászólást 1 alkalommal szerkesztették, utoljára Viktor08 2020. június 1., hétfő 22:13-kor.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

Igen.

Az INDIREKT() függvénnyel például.
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

De lehet, hogy az OFFSET() függvény is jó lenne neked erre a feladatra.

(bocs, én az angol függvényneveket használom: inkább azokat tudom fejből.)

De a pontosabb információkat - a függvény használatának részleteit - csak a te mintadokumentumodon keresztül tudom számodra is átláthatóan, érthetően megadni. Töltesz fel egyet ide?
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Lefaragtam az felesleges munkalapokat és csak a feladat szempontjából lényeges részt hagytam meg. Színekkel jelöltem milyen adatnak kellene az 1-es munkalapból a 2-es munkalap azonos színű részére kerülnie. Ez lenne a végső cél, de az is jó lenne ha évek szerint nem lenne szeparálva a 2-es munkalapon, csak egymás után hozva a tételeket. Eredetileg a HOL.VAN() és INDEX() függvények segítségével szerettem volna megoldani, de ahhoz fel kellene ismerni, hogy az adott tétel már fel van tüntetve. Az INDEX() függvénynél gondoltam a tartományt a HOL.VAN() függvényből kinyert értékkel szűkíteni (a már megtalált sor alatt keresve az újabb értéket).
Csatolmányok
Minta_1.ods
(26.33 KiB) Letöltve 329 alkalommal.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

Hááát?!

Nem nagyon értem, hogy mi az elsődleges adat a második táblában, amihez a többi adatot bármilyen kereső vagy ofszet függvénnyel hozzá akarod rendelni. És az az elsődleges adat hogyan kerül oda? Beírod kézzel?

Tulajdonképpen azt se értem, hogy mi alapján kerültek egy sorba az adatok az első táblában, és egyáltalán hogy az csak véletlen, vagy van valami logikája (a hasonló dátumon kívül - de még az se mindenhol azonos)?

És ha a "B-szv-8 / I." (és ehhez hasonló szöveges adatok) RÉSZEIT akarod vizsgálni a második táblában (aszerint kell szeparálni az adatokat, ha jól látom), akkor a részek miért vannak egyetlen cellában "összefűzve" az első táblában? Nem lenne jobb ott is külön kezelni azokat??

És miért van két hármas oszlopcsoport a 2019. év számára, és miért van csak egy a 2018. év számára? És lehet, hogy később meg 3 (vagy több) db hármas oszlopcsoportra lesz szükség valamelyik évben?
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Elsődleges adat a dátum (Munkalap1, "I" oszlopában lévő) lenne, majd a dátumhoz tartozó két azonosító.

A Munkalap1 teljes egészében mechanikusan felvitt adatokat tartalmaz. A lényege, hogy a javításra kiszerelt eszközöknek van egy egyedi azonosítója (Munkalap1, "E" oszlop). Minden eszköz be van építve (ill. ez esetben be volt építve) egy üzemeltetési helyre, melynek szintén van egy egyedi azonosítója (Munkalap1, "H" oszlop). Munkalap1 alatt az "I" oszlopban tüntettem fel a konkrét kiszerelés dátumát. Munkalap1, "A" oszlopában tüntettem fel az "E" oszlopban felsorolt eszközök helyett, ugyanarra az üzemeltetési helyre beépített berendezéseket. Mellette a "D" oszlopban a beüzemelés dátumát (ez ritkán tér el az "I" oszlopban lévőtől).

A "B-sz-8 / I." egy konkrét üzemeltetési hely egyedi azonosítója, lehetne igazából bármi, csak számomra így ránézésre megmutatja mi is az és nincs szükség hosszan kiírni a pontos helyet.

Az évekhez tartozó oszlopok száma sajnos nem fix adat, ezért gondoltam azt, hogy talán nem is lenne fontos évekre lebontani, csak egymás mellett hozni a Munkalap2-ben a dátumhoz tartozó berendezés azonosítókat, így könnyen leszűrhetem mikor melyik eszköz üzemelt az adott helyen és mikor melyikre lett lecserélve.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Azt nem írtam len, hogy a Munkalap1-ben kell figyelni a beépítési hely azonosítókat "H" oszlop, az megadja a Munkalap2-ben melyik sorba kell a hozzá tartozó dátum és berendezés azonosítót írni.
Egyenlőre csak a "B-szv-" helyek vannak feltüntetve, még nem készítettem el a "D-szv-", "H-szv-", stb. kezdetűeket. Azokat vagy alá szánom, vagy külön lapfülre.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

Azt nem írtam len, hogy a Munkalap1-ben kell figyelni a beépítési hely azonosítókat "H" oszlop, az megadja a Munkalap2-ben melyik sorba kell a hozzá tartozó dátum és berendezés azonosítót írni.
Egyenlőre csak a "B-szv-" helyek vannak feltüntetve, még nem készítettem el a "D-szv-", "H-szv-", stb. kezdetűeket. Azokat vagy alá szánom, vagy külön lapfülre.
No, hát erről beszéltem: valószínűleg nem lehetetlen megcsinálni, de biztosan sokkal-sokkal bonyolultabb képletek kellenek hozzá, ha egyik lapon "szétszedve" a másikon "egyben" szerepelnek ezek az azonosítók.

Más szavakkal: az a gond az első munkalapon lévő "adatbázisoddal" (tudjuk, hogy valójában nem az), hogy túlságosan az "emberi szemnek készült", és nem a további adatfeldolgozás céljából.
Például, amit a VLOOKUP() (magyarul: FKERES()) függvénnyel kerestetni akarsz egy több oszlopos táblában, azt az adatot tábla BAL oldalára, a legszélső oszlopba kell tenni. Ha azt megtalálja, akkor a VLOOKUP függvény paramétereiben megadható, hogy hányadik oszlopban található adatra van valójában szükséged. Tehát, ha ki akarod keresni a "B-sz-8 / I." szöveges adatot, akkor annak a BAL oldalon kell szerepelni. Aztán, ha megtalálta a függvény akkor azzal, vagy a jobbra lévő következő, vagy az azt követő, ..., ... oszlopban lévő adattal tér vissza.
Az évekhez tartozó oszlopok száma sajnos nem fix adat,
No, ez szinte biztosan így nem fog menni cellaképletekkel, ehhez már makró kell!
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Mi lenne, ha a következő módon gondolkodnék: a Munkalap2 fül alatt a "C21" cellába (8 / I. kiszerelt rovatába) a következő képlet kerülne: INDEX(Munkalap1.A4:I75;HOL.VAN("B-szv-8 / I.";Munkalap1.H4:H75;0);5). Ez eddig működik is. A probléma az "F21" cellával lesz, hiszen abban a "C21" tartalma fog megjelenni. Az ötletem a következő lenne: HOL.VAN("B-szv-8 / I.";Munkalap1.H4:H75;0) függvénnyel megkeresve a 35. sorban van az első "B-szv-8 / I."-es érték. Az "F21" cellába tulajdonképpen már jó lenne a korábban vázolt függvény, ha a keresési tartomány nem "A4"-el indulna, hanem "A(4+35)" értékkel, hiszen, akkor már az első "B-szv-8 / I." ki is esne. Tehát úgy kellene mind a HOL.VAN() függvénynél, mind a INDEX() függvénynél, hogy a tartomány "A(4+ekőző HOL.VAN())" értékre váltson. Kérdés, hogy ez meghatározható-e valami módon.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

További probléma, hogy alapesetben egy listából az esetleg ismétlődő elemek közül CELLAFÜGGVÉNNYEL csak egyet (keresési irányban az elsőt) lehet megtalálni, a többit nem.

Többnek a megtalálásához vagy nagyon bonyolult cellafüggvény-sor, vagy Szűrő, vagy Makró szükséges.
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Ezért jutott eszembe az, hogy mindig megtalálom az elsőt és a következő keresést már egy sorral alatta folytatom, így meglenne az összes egyszerűen. Ehhez sajnos a keresési tartományt kellene minden lépésnél lefelé haladva szűkíteni. Sehol sem találok megoldást arra, hogy az említette cellatartományt ne fix értékkel, hanem képlettel is meg tudja adni.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

Ezért jutott eszembe az, hogy mindig megtalálom az elsőt és a következő keresést már egy sorral alatta folytatom, így meglenne az összes egyszerűen. Ehhez sajnos a keresési tartományt kellene minden lépésnél lefelé haladva szűkíteni. Sehol sem találok megoldást arra, hogy az említette cellatartományt ne fix értékkel, hanem képlettel is meg tudja adni.
Erre jó az INDIRECT() függvény. De honnan fogja tudni a soron következő képleted, hogy hanyadik találat után kell folytatnia a keresést? Le kell valahol tárolni a találatok sor-számát, de abból se lehet megállapítani egyszerűen, hogy melyik következik.
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Kicsit átalakítottam a minta táblázatomat úgy, ahogy jól működne, csak kézzel írtam át a tartományokban az értékeket. A C21, C22, D21, D22, E21 és E22 cellákban a függvények jól működnek. Ebből kiindulva az F1 cellában a korábbi képletben szereplő: INDEX(Munkalap1.A4:I75;HOL.VAN("B-szv-8 / I.";Munkalap1.H4:H75;0);5) függvényen annyit változtattam, hogy a HOL.VAN("B-szv-8 / I.";Munkalap1.H4:H75;0) függvény C23-as cellában látható 35-ös értékével növelten a keresési tartomány kezdő celláját, így lett az mindkét függvény esetében A39. E módosított függvény így néz ki: INDEX(Munkalap1.A39:I75;HOL.VAN("B-szv-8 / I.";Munkalap1.H39:H75;0);5). Ez már megtalálja a B-szv-8 / I. keresési feltételt ismét az első helyen. Ez így folytatnám az I21 cellában is, csak ahhoz a bizonyos A39-es kezdő cella értékéhez hozzá kell adni az F23 cellában lévő HOL.VAN() függvény újabb értékét, vagyis 5-öt és így kiesik a sorban másodszor szereplő B-szv-8 / I. érték is, így következő keresésnél az I21 cellában már #hiányzik hibaüzenet szerepel, vagyis nem talált A44:I75 tartományban újabbat.
Amennyiben megoldható lenne, hogy ezt az A4:I75 tartomány meghatározásban a 4-es értéke mindig növelhető legyen a HOL.VAN() függvény értékével, akkor jól működne az egész.
Csatolmányok
Minta_1.ods
(22.38 KiB) Letöltve 324 alkalommal.
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

Csinálom a módosított mintát az Indirect függvénnyel, de azt látom, hogy te direkt nehezíted az én dolgomat (de legfőképpen a saját dolgodat). Mert az a "formázás" és struktúra, amit kialakítottál a második lapon az mindenre jó és szép lehet, csak hatékony munkára nem való.
Nem tudsz egy egyszer megírt képletet továbbmásolni se vízszintes, se függőleges irányban, mert vagy a cellaszín, vagy a cellaszegély biztosan elromlik.
A konstansként bevitt szöveges keresési értéket is mindig be akarod gépelni? Jobb lenne azt egyszer összeállítani egy sorban, és aztán az összes oszlopban arra a cellára hivatkozni!
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

No, itt a módosított minta. A 8-as sort csináltam meg (persze továbbra is kellenek a segédcellák, amik most elfoglalják a páratlan sort!), és a másik kép példát, ami lentebb látható, már egyszerű másolással vittem tovább.
A segédcellák azért kellenek, hogy a bennük eltárolt értékeket ne kelljen MINDEN egyes képletben újra meg újra kiszámoltatni, hanem csak hivatkozni kell az aktuális segédcellára. A segédcellák elhelyezhetők egy másik munkalapon is, de ugyanolyan struktúrát kell ott is alkalmaznod, hogy a továbbmásolások rendben működjenek.

Amit az előbb írtam, az látszik a táblán: hiába írsz olyan univerzális szuper képletet (amit csak odébb kell másolni, semmit nem kell változtatni rajta), nem tudod ezt megtenni anélkül, hogy el ne cseszd a buta, fölösleges formázást. Nyilván nem javítottam ki a formázást, mert NEM.
Stílusokat mindenképpen (KERETEK nélkül!), és esetleg feltételes formázást lehetne használni egy jobban kezelhető, rugalmasabb, de azért vizuálisan segítő jellegű formázás elérése érdekében.
Minta_1_INDIRECT.ods
(25.18 KiB) Letöltve 327 alkalommal.
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Viktor08
Önkéntesek
Hozzászólások: 66
Csatlakozott: 2015. november 17., kedd 16:53

Re: Cella tartomány megadása képlettel

Hozzászólás Szerző: Viktor08 »

Szerintem szép megoldás és tökéletesen működik! Sokat lehet belőle tanulni azoknak az embereknek, akik szeretnek foglalkozni, vagy foglalkozniuk kell ezzel a témakörrel.
Köszönöm a segítséget!
OpenOffice 3.2.1.és OpenOffice 4.1.10., Windows 10
Avatar
Zizi64
Globális moderátorok
Hozzászólások: 4031
Csatlakozott: 2008. november 12., szerda 21:22
Tartózkodási hely: Budapest

Re: [Megoldva] Cella tartomány megadása képlettel

Hozzászólás Szerző: Zizi64 »

Egyéb gondolatok:

A segédcellákat átteheted (CTRL-X) ugyanazon a lapon jócskán jobbra, de ugyanabba a sorba, amiben fel fogod használni azokat (például az általad beállított nyomtatási tartományon kívülre - már ha egyáltalán akarod nyomtatni a lapot). Akkor nem foglalja el a használni kívánt sorokat.


Azt továbbra sem látom át, hogy az egy sorban lévő hármas csoportok hogyan jöhetnének létre automatikusan. Azaz, hogy ne neked kelljen annyiszor odébb másolnod a képletet, amíg csak hibajelzés nem lesz a tartalma...

Egyetlen módot látok:
Feltételezed, hogy maximum 5 darab ilyen hármas egységre lesz szükséged, és azt a területet kitöltöd a képletekkel, de vagy
- a képletek elejére teszel egy vizsgálatot, amit "eltünteti" a hibajelzéseket (üres stringet ad eredményül), vagy
- feltételes formázás funkcióval figyelteted a cellák tartalmát, és olyan cellastílust alkalmazol a hibajelzés fennállásának esetére, aminek eredményeképpen a hibajelzés NEM LÁTSZIK a táblában. Ez megint csak jóval nehézkesebb a te formázásod mellett, mert a keretes (cellánként eltérő kompozícióban létező keretvonalas) megoldás és két soronként eltérő háttérszín miatt rengeteg különböző vonal- és háttér beállítású "eltüntető" Stílusra lesz szükséged!
Sok sikert!
Kovács Tibor (W10x64Pro/W7x64P: 7.5.8;
winPenPack/PortableApps: LO3.3.1-7.6.2, AOO4.1.14
Ha megoldódott a probléma, jelöld a témaindító hozzászólásod címének szerkesztésével. Írd elé: [Megoldva].
Válasz küldése