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
  • 2021-07-02-09:43 #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.
      2021-07-02-19:40 #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.
        2021-07-12-14:46 #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

          2021-07-12-15:58 #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

            2021-07-15-10:36 #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

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