[Resolved] Gyertya diagramhoz előkészítő pivot tábla

Szia!

Nem foglalkoztam még a témával, de azért írok pár dolgot.
Feltételezem, hogy te is valamilyen árfolyam napi alakulásának ábrázolásához szeretnéd használni.
A gyertya diagramhoz speciálisan előkészített forrás tábla kell, ami 5 oszlopos.
Első oszlop egy dátum, és utána sorban, hogy azon a napon hogyan viselkedett az árfolyam:
nyitó, legmagasabb, legalacsonyabb, záró

Ennek előállításához szükség van egy olyan forrás táblára, ami a napon belül x időnként tartalmazza az aktuális árfolyamot.
Erre elvileg egy 2 oszlopos tábla elég, és a napon belül nem tudom mennyi mintavétel. Ha óránként nézzük, akkor a tőzsde nyitás és zárás között óránként egy érték.

Például ilyesmi 2 napra

Dátum		Idő	árfolyam
----------------------------------
2022.02.15	8:00	96
2022.02.15	9:00	83
2022.02.15	10:00	105
2022.02.15	11:00	115
2022.02.15	12:00	129
2022.02.15	13:00	116
2022.02.15	14:00	98
2022.02.15	15:00	77
2022.02.15	16:00	105
2022.02.16	8:00	79
2022.02.16	9:00	107
2022.02.16	10:00	105
2022.02.16	11:00	77
2022.02.16	12:00	75
2022.02.16	13:00	98
2022.02.16	14:00	86
2022.02.16	15:00	92
2022.02.16	16:00	121
stb...

És akkor ebből kell előállítani ezt

Nap		nyit	max	Min	Zár
---------------------------------------------
2022.02.15	96	129	77	105
2022.02.16	79	121	75	121

A napi MAX és MIN értékek egyszerűek, simán MAXHA és MINHA függvényekkel megcsinálhatók.
A nyitó és a záró már trükkösebb, bár ha naponta mindig ugyanakkor nyit és ugyanakkor zár az adat, akkor ez is sima ügy, két feltételes MAXHA és MINHA

De fogalmazhatok úgy is pl. az első napnál, hogy keresse meg február 15-én a legkisebb időpontot, és adja vissza hozzá az árfolyamot abból a sorból. Ez már egy nagyobb kihívás. INDEX/MATCH és tömbképlet kell hozzá.

=INDEX($D$3:$D$20;MATCH(1;($B$3:$B$20=G3)*($C$3:$C$20=MIN($C$3:$C$20));0))

D oszlopban vannak az árfolyamok, B oszlopban vannak a napok, C oszlopban vannak az órák.
G3-ban van az alsó táblából a 2022.02.15
A fenti képlet a feb 15-i nyitó árfolyamot adja, azaz a 96-ot.

Remélem ez segít.

Imre

Igen, jogos!
Pontos egyezés = első előfordulás, közelítő egyezés = utolsó előfordulás rendezettség esetén. 🙂

Már csak az a kérdés, hogy @MrJosa adatkészlete így néz-e ki, vagy ilyenné tehető vagy sem.

Szia!

Tetszik amit csináltál, de a végső tábla dinamizmusa valóban nem megoldott így. 🙂
Rákérdeznék, hogy milyen Excel verziót használsz?
Ha 365-öt, akkor dinamikus tömbképletekkel és XKERES függvénnyel és egy dinamikus névvel megoldható amit szeretnél.

Ha 365-öd van, akkor el tudom küldeni, nagyon tanulságos, a dinamikus tömbök új dimenziókat nyitnak az Excelben.

Imre

Nem tudom mire gondolsz a számítot mező esetén. Ha esetleg FKERES-t akarnál használni, azt nem lehet.
„”references,Names,Arrays are not supported in pivottable formulas”

Számított mezővel csak az aktuális Pivot-ban létező mezők felhasználásával lehet új mezőt csinálni.
Pár függvény megengedett (Pl. IF) de csak mezőre hivatkozhatsz, kifelé nem.

Excel natív eszközökkel 2016-ban szerintem nem lehet dinamizálni a jobb oldali tábládat.
Makróval igen, illetve PowerQuery-vel is valószínűleg megoldható.

Imre

Szia!

Alakul a dolog 🙂

Szép megoldások. Majd ha váltasz (mert előbb-utóbb fogsz) újabb Excelre, meglátod, hogy a tömbképletek mennyivel egyszerűbbek lettek.

Gyertya diagramot (sem) lehet csinálni sajnos Pivot tábla alapján. Van még pár ilyen.
Erre azt szoktam csinálni, hogy dinamikus névtartományt húzok a Pivotra, a kezdő celláját (Nálad ez G1) fixnek tételezve.

Nálad most az „L” oszlop végét kellene megtalálni, és máris megvan a G1:L_oszlop_vége tartomány.
Ha az L oszlopban nem pontos egyezőséggel keresünk egy jó nagy számot, ami biztosan nagyobb az összes előforduló számnál, akkor a szokásos függvények az utolsót fogják megtalálni, így tesz a HOL.VAN is, visszaadva a 39.sort
HOL.VAN(9999999;L:L;1)

Ha ezt odaadod az INDEX-nek, az megtalálja a 39. sorban lévő értéket.
INDEX($L:$L;HOL.VAN(9999999;$L:$L;1))

És most jön a lényeg, hogy az INDEX fv valójában mindig a megtalált elemre mutató referenciát ad vissza, nem magát az értéket, tehát az L oszlop utolsó sorában lévő érték címét, azaz jelen esetben „L39”-et
Tehát a teljes PIVOT tartománya jelenleg: „G1:L39”, ami természetesen követi a Pivot méretét, ha ebben az oszloptartományban marad különben.

Utolsó lépésként csináltam egy ennek megfelelő Nevet a névkezelőben, (Pivot_adat) ami így néz ki:
=Munka1!$G$1:INDEX(Munka1!$L:$L;HOL.VAN(9999999;Munka1!$L:$L;1))

Végül a Chart létrehozása, ami még egy csavar, mert előre ki kellene jelölni az adatokat, hogy Diagramot csinálj, a Pivot alapján meg nem lehet. Szóval én értékként kimásoltam a Pivot táblát, megcsináltam a diagramot, majd módosítottam az adatforrását, hogy a fent létrehozott névre mutasson.
=Munka1!Pivot_adat

A „Munka1” munkalapnév fontos benne, mert a Chart adatforrásként csak akkor fogad el nevet, ha a nevet tartalmazó munkalap is bele van írva. Nem tudom miért, de így van.

Nagyon érdekes, mert valahogy érzékeli, hogy Pivot-ból van, és a szeletelő mindkettőt vezérli.
Utólag pedig valami miatt átvette az előző Chart forrását és nem lehet módosítani a forrását.
Kicsit még nézegetem…

Visszacsatolom, és köszönöm ezt a jó kis tanulságos kihívást!

Imre

• A hozzászólás módosításra került: 2 years, 9 months telt el-horvimi.
• A hozzászólás módosításra került: 2 years, 9 months telt el-horvimi.