Telefonszámunk: 1-472-0679

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

Kezdőlap Fórumok Excel témák [Resolved] Gyertya diagramhoz előkészítő pivot tábla

Topic Resolution: Resolved
10 bejegyzés megtekintése - 1-10 / 10
  • Szerző
    Bejegyzés
  • #8931
    MrJosa
    Felhasználó

      Üdvözlöm!
      Az lenne a kérdésem, hogy van-e arra lehetőség, a gyertya diagram az előkészítéstésére kimutatással. Itt ugye a csoportosításból kell 4 adatot kimutatni – első, maximum, minimum, és az utolsó adat a csoportban -, amiből az első (nyitó) és az utolsó (záró) adat kinyerésével van a probléma vagy problémám. Ha nem, akkor ezt hogyan lehet megoldani, hogy a dinamikusság is meglegyen (új adat a táblába).
      A választ előre is köszönöm.

      #8932
      horvimi
      Adminisztrátor

        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

        #8933
        delila
        Felhasználó

          Szia Imre!

          Mivel az időpontok (feltehetően) emelkedő sorrendben vannak, egyszerűbben is meghatározható a Max és Min érték.

          Delila

          Attachments:
          You must be logged in to view attached files.
          #8935
          horvimi
          Adminisztrátor

            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.

            #8936
            MrJosa
            Felhasználó

              Sziasztok!

              Először is köszönöm a válaszaitokat!
              Először is ez a feladat nem egy olyan feladat, ami a napi munkához kell, csupán tanulom az excelt és találtam egy cikket a deviza napi árfolyam mutatásról és felkeltette az érdeklődésem a téma, gondoltam belemerülök egy kicsit. És ha már az excelben van árfolyam diagram gondoltam az előkészítés is valahogy benne van. A legegyszerübb módját abban láttam, hogy kimutatásban hozom létre a négy adatot. Így dinamikusan működhetne. Sikerült is megoldani a témát, de nem dinamikusan. Ezért írtam. Most szeretném megosztani, hátha mást is érdekel, illetve hátha sikerül ezt dinamikussá tenni.

              A kiinduló pont ez volt:
              https://excel-bazis.hu/tutorial/fissitheto-napi-euro-arfolyam-excelben
              Ez önnálóan frissíti magát minden betöltéskor, ha a lekérdező végdátum nagyobb a pillanatnyi dátumnál. Ugye a táblához nem nyúlunk, mert az minden frissítéskor felülíródik.

              Először is csináltam egy kimutatást.
              A dátumot (Column1) a sorokhoz -Év, Hónap
              Az értékekhez (EUR euro 1) kétszer behúztam az elsőt MAX-ra állítottam, a másodikat MIN-re.
              Ezzel két érték már megvolt.

              Itt kezdődött a probléma!

              Készítettem egy másik kimutatást, amibe kihoztam a hónap első és utolsó adatának dátumát.
              A sorokhoz a dátumot, mint az elöbb (Column1) -Év, Hónap
              Az értékekhez is a dátumot (Column1) kétszer az elsőt MAX-ra állítottam, a másodikat MIN-re. Ezeket az oszlopokat Dátumra formáztam.
              Mostmár megvolt a két adathoz (nyitó és záró) tartozó dátumom. Eddig DINAMIKUSAN!

              Itt viszont elakadtam a dinamikával. Össze tudtam hozni az adatokat FKERES függvénnyel, de az új adat táblám már nem frissül, ha új adat kerül az eredeti kiinduló táblába.

              Elnézést a hosszú agymenésemért.

              Attachments:
              You must be logged in to view attached files.
              #8938
              horvimi
              Adminisztrátor

                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

                #8939
                MrJosa
                Felhasználó

                  Szia Imre!
                  Excel 2016-osom van, így az XKERES kiesik. Én mindenképp a kimutatásban látom a megoldást a számított mezővel. Így most azt bújom a google-val.

                  József

                  #8940
                  horvimi
                  Adminisztrátor

                    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

                    #8942
                    MrJosa
                    Felhasználó

                      Szia!

                      Végül sikerölt, ha nem is úgy ahogy elképzeltem.
                      Amit kihasználtam a táblázat dinamikus volta. Tehát, ha mellé-alá írok új sort-oszlopot, az hozzáadodik a tálázathoz. Így már csak a képletek maradtak.
                      Nyitó ár (tömbképlet):{=FKERES(KICSI(HA([Dátum]>HÓNAP.UTOLSÓ.NAP([@Dátum];-1);[Dátum]);1);A:B;2)}
                      Záró ár:=INDEX(A:B;HOL.VAN(HÓNAP.UTOLSÓ.NAP([@Dátum];0);A:A;1);2)
                      A többi az már kimutatás.

                      Az egyetlen gond, hogy én itt átalakítottam a régit, majd a diagramnál átírtam a Diagram adattartományát. Ha újonnan kezdtem felépíteni az egészet, akkor nem engedte az árfolyam diargamot.

                      József

                      Attachments:
                      You must be logged in to view attached files.
                      #8944
                      horvimi
                      Adminisztrátor

                        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, 1 month telt el-horvimi.
                        • A hozzászólás módosításra került: 2 years, 1 month telt el-horvimi.
                        Attachments:
                        You must be logged in to view attached files.
                      10 bejegyzés megtekintése - 1-10 / 10
                      • Be kell jelentkezni a hozzászóláshoz.