Telefonszámunk: 1-472-0679

[Resolved] összegzés kezdő és záró dátum között automatikusan

Kezdőlap Fórumok Excel témák [Resolved] összegzés kezdő és záró dátum között automatikusan

Topic Resolution: Resolved
5 bejegyzés megtekintése - 1-5 / 5
  • Szerző
    Bejegyzés
  • #8662
    dandras
    Felhasználó

    Adott a mellékelt táblázat, amiben az 1. munkalapon kell összegezni a 2. munkalapon található tételes listából az értékeket a kezdő és záró dátum közötti időszakra az adott azonosítóhoz. Ahogy látszik azonosítónként eltérő a kezdő és a záró dátum.
    Hogyan lehetne ezt úgy függvényezni (lehetőleg minden mezőben ugyanazzal a függvénnyel), hogy
    – a dátumtartományon kívüli időszakok nulla értéket kapjanak
    – a kezdő és záró dátum hónapjában csak a releváns napokra összegezzen
    – a kezdő és záró dátum időszakába eső teljes hónapokra a teljes hónapot összegezze
    A szum/index/hol.van kombinációval próbálkoztam. A teljes hónapokkal nincs gond, de a kezdő és záró dátum hónapjával elakadtam. A különböző időszakok kezelése (az időszakon kívüli rész, a kezdő dátum hónapja, az időszakon belüli rész, a záró dátum hónapja, gondolom, többszintű HA függvénnyel megoldható, csak az összegzést kellen jól megoldani.

    Excel 365-ben dolgozom.

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

    Szia!

    Ránéztem egy kicsit, azt hiszem értem, hogy mit akarsz.
    Mivel a sárga, zöld és narancs színű celláknak más a feltétele, így ugyanazzal a képlettel nyilván nem lehet megcsinálni, de talán arra gondoltál, hogy az azonos színűekben legyen azonos képlet.

    Szerintem meg lehet csinálni egy strukturális átalakítással, és néhány egyéb változtatással, amennyiben ez lehetséges.
    A struktúrális változás az lennem hogy a második munkalapon lévő táblát (adatok) UNPIVOT-álni kellene, és rendes dátum oszlopot tenni bele.

    Ezután el tudom képzelni, hogy SUMHATÖBB függvénnyel megoldható lenne a történet.

    Most csak a kérdés, hogy te el tudod-e képzelni egy másik munkalapra létrehozni egy átstruktúrált adathalmazt PowerQuery-vel?

    Csatoltam egy megoldást, amiben a Munka1-en a te adataid alá lemásoltam az első két sort, és oda beképleteztem a három különböző feltétellel, és ugyanazt hozta, mint a te sima minta SZUM függvényed.
    Úgyhogy szerintem jó.

    Előtte pedig megcsináltam a PowerQuery-vel az UNPIVOT-ot a Munka2-re.

    Szóval ha csak használni akarod, akkor a képletekben a cellacímeket át kell módosítani, hogy az első adatsorból dolgozzanak,
    (egy sárga, egy zöld, egy narancs) és utána ha megvan a színkódolás, akkor “csak” C/P.

    Imre

    Attachments:
    You must be logged in to view attached files.
    #8680
    dandras
    Felhasználó

    Szia!

    Köszönöm a megoldást! Az átstruktúrálás megoldható, az nem okoz gondot. Egyelőre ez csak részben jó, mert ezzel minden sorban nekem kell meghatároznom, hogy melyik oszlop értéke nulla, melyikbe kell beletenni a kezdő dátumnak, a teljes hónapnak és a záró dátumnak megfelelő képletet. Mivel várhatóan ez egy párezres lista lesz, ezért ez nem kivitelezhető, tehát ahogy először is írtam, minden mezőben ugyanannak a képletnek kellene lennie, vagyis a három időszak (kezdő, teljes, záró) képleteit össze kellene fűzni egy olyanba, ami bármelyik mezőbe a jó (nulla, kezdő hónaphoz, teljes hónap(ok)hoz és záró hónaphoz) tartozó összegeket jól kiszámolja. Ennek megfelelően öt különböző időszakot kell megkülönböztetni (csak egy dátumon keresztül a példák):
    1. ha a vizsgált hónap utolsó napja < az időszak kezdő dátuma => 0 (alulról a tartományon kívül esik)
    2. ha a vizsgált hónap első napja < kezdő dátum ÉS a vizsgált hónap utolsó napja > kezdő dátum => kezdő hónap (sárga képlet)
    3. ha a vizsgált hónap első napja > kezdő dátum ÉS a vizsgált hónap utolsó napja < záró dátum => teljes hónap (zöld képlet)
    4. ha a vizsgált hónap első napja < záró dátum ÉS a vizsgált hónap utolsó napja > záró dátum => záró hónap (narancs képlet)
    5. ha a vizsgált hónap első napja > záró dátum => 0 (felülről a tartományon kívül esik)

    Na, ezeket kellett egy képletbe összegyúrni! 🙂

    Ami így néz ki jelenleg:
    =HA(DÁTUMÉRTÉK(D$1&”.01″)<$B2;HA(HÓNAP.UTOLSÓ.NAP(DÁTUMÉRTÉK(D$1&”.01″);0)<$B2;0;SZUMHATÖBB(Munka2!$D$2:$D$30000;Munka2!$C$2:$C$30000;”>=”&$B2;Munka2!$C$2:$C$30000;”<=”&HÓNAP.UTOLSÓ.NAP(DÁTUMÉRTÉK(D$1&”.01″);0);Munka2!$B$2:$B$30000;$A2));HA(HÓNAP.UTOLSÓ.NAP(DÁTUMÉRTÉK(D$1&”.01″);0)<$C2;SZUMHATÖBB(Munka2!$D$2:$D$30000;Munka2!$C$2:$C$30000;”>=”&DÁTUMÉRTÉK(D$1&”.01″);Munka2!$C$2:$C$30000;”<=”&HÓNAP.UTOLSÓ.NAP(DÁTUMÉRTÉK(D$1&”.01″);0);Munka2!$B$2:$B$30000;$A2);HA(DÁTUMÉRTÉK(D$1&”.01″)<=$C2;SZUMHATÖBB(Munka2!$D$2:$D$30000;Munka2!$C$2:$C$30000;”>=”&DÁTUMÉRTÉK(D$1&”.01″);Munka2!$C$2:$C$30000;”<=”&$C2;Munka2!$B$2:$B$30000;$A2);0)))

    Előnye a táblázatneves megoldással szemben, hogy bármerre húzható a képlet (a táblázat nevesnél a vízszintes húzás cserélgeti a táblázat oszlopait).
    Hátránya, hogy fix 30000 sorra állítottam be, ha ennél több az adattartomány, akkor a 30000-et át kell írni, de cserével ez simán megoldható.

    András

    #8682
    horvimi
    Adminisztrátor

    Szia!

    Örülök, hogy sikerült.
    Az UNPIVOT-os részt használod?

    Ha egy táblázat oszlopára abszolút hivatkozást akarsz csinálni (jobbra másoláskor nem másszon el az oszlpnév), akkor így kell rá hivatkozni:

    Táblázatnév[[oszlopnev]:[oszlopnev]]

    Pl.
    =SZUM(Táblázat1[[Oszlop2]:[Oszlop2]])

    Imre

    #8691
    dandras
    Felhasználó

    Szia!

    Igen, az unpivotra megy az egész.
    Köszi az abszolút hivatkozásos kiegészítést! Ritkán használok táblázat elnevezést, ez új volt számomra. Alkalomadtán átalakítom ennek megfelelően a képletet.

    András

5 bejegyzés megtekintése - 1-5 / 5
  • Be kell jelentkezni a hozzászóláshoz.