Kezdőlap › Fórumok › Excel témák › [Resolved] összegzés kezdő és záró dátum között automatikusan
- This topic has 4 hozzászólás, 2 résztvevő, and was last updated 3 years, 3 months telt el by dandras.
-
SzerzőBejegyzés
-
2021-07-02-09:43 #8662
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 #8664Szia!
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 #8680Szia!
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 #8682Szia!
Ö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 #8691Szia!
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
- Be kell jelentkezni a hozzászóláshoz.