Lijst in excel samenstellen met totalen

[HSV]

Geen idee waarom je dan die per se aangepast wil hebben, je hebt er namelijk twee goed werkende in staan.

=LET(x;VERT.STAPELEN(JANUARI:DECEMBER!D6:H14);y;FILTER(x;(INDEX(x;;1)<>"")*(INDEX(x;;1)<>"Selecteer"));KIES.KOLOMMEN(GROEPEREN.PER(KIES.KOLOMMEN(y;1;2;3;5)&"";INDEX(y;;4);SOM);1;2;3;5;4))

)

 

[bartvt78]

Het ene is om jaartotalen te maken, het laatste is om een jaaroverzicht te maken per maand.

 

[HSV]

Volgens mij heb je nu drie identieke resultaten.

 

[bartvt78]

Hey HSV, die 2 waren goed hoor, bedankt voor je bijdrage tot de derde. Maar de vraag was of er een formule bestond voor het stukje van cellen D37 tot R51, de grijsblauwe cellen. Met de data uit de grijsblauwe cellen van de maanden januari tot december (maar ingevuld tot maand April, maar nodig voor het ganse jaar.

 

[HSV]

Geen idee of je er blij van wordt van zo'n formule.

 

[peter59]

Maar een power query, hoe maak je dat, of hoe kan ik die aanpassen zodat deze query werkt in de echte file?

Gaat het je wel lukken om met de aangedragen formules van de andere helpers te werken?
Met andere woorden: als er iets wijzigt of aangepast moet worden, weet je dan hoe je de formules op de juiste manier moet aanpassen/implementeren?

 

[bartvt78]

Hey @HSV, blij is het misschien niet, maar tevreden zeker. En vooral, ik vind het machtig dat je zoiets met excel kan bereiken. Dus misschien is blij wel een goed woord bij gebrek aan beter.
@peter59, ik snap je bezorgdheid, maar ik ga mijn best doen om alle oplossingen te implementeren en aan te passen waar nodig. Ik heb basis kennis van excel, vandaar al mijn vragen. Maar men is nooit te oud om te leren. Echter, dit soort formules, ik zou er nooit zelf op gekomen hebben, dus ik ben zeer dankbaar voor de bijdragen van de verschillende forum leden.
Hartelijk dank aan iedereen die een bijdrage aan dit topic heeft geleverd.

 

[bartvt78]

Ik heb mijn formule verscheidene keren overlopen en vergeleken met de formule van @HSV, maar bij mij zit er een fout in. Mijn gemiddelde per maand is hetzelfde als het totaal per jaar, wat dus niet correct is. Bij het voorbeeld van @HSV geeft de formule wel het correcte totaal per jaar en gemiddelde per maand weer. Kan iemand mij vertellen waar mijn formule afwijkt van de oplossing? Ik zie het echt niet.

 

[HSV]

Bij deze.

 

[bartvt78]

Hey HSV, mag ik vragen waar ik fout zat? Want ik moet het nog op een andere plaats implementeren ook, en ik wil niet telkens jou hulp moeten vragen. Ik leer graag van mijn fouten.

 

[jverkerk]

@bartvt78
Zet de formules zonder = teken onder elkaar in aparte cellen, dan kun je zien wat er anders aan is.

 

[HSV]

De vorige groepering had 4 kolommen breed met y, vandaar de index 3; maar gaf mij teveel rijen aan.
De nieuwe formule met index(y;;1) heeft 2 kolommen breed vandaar de 2 bij Index in de groepering, maar gaf wel het juiste aantal rijen retour.
Waarom?; misschien was het resultaat puur toeval bij de vorige formule door het aantal rijen van L6:M15.

 

[bartvt78]

Nog een laatste. Het is zuiver formule werk. Ik heb geen voorbeeld gegenereerd, maar kan dat wel maken als het echt nodig is. Maar ergens doe ik dus een conversie fout, zodat mijn aangepaste formule niet werkt. Ik werk met zoek en vervang in notepad voor de bereiken aan te passen.

Voor E8:F33 werkt de formule

=ALS.FOUT(LET(x;VERT.STAPELEN(JANUARIECEMBER!E8:F33);y;FILTER(x;(INDEX(x;;1)<>"")*(INDEX(x;;1)<>"selecteer"));u;UNIEK(INDEX(y;;1));jan;INDEX(JANUARI!F8:F33;VERGELIJKEN(u;JANUARI!E8:E33;0));feb;INDEX(FEBRUARI!F8:F33;VERGELIJKEN(u;FEBRUARI!E8:E33;0));mrt;INDEX(MAART!F8:F33;VERGELIJKEN(u;MAART!E8:E33;0));apr;INDEX(APRIL!F8:F33;VERGELIJKEN(u;APRIL!E8:E33;0));mei;INDEX(MEI!F8:F33;VERGELIJKEN(u;MEI!E8:E33;0));jun;INDEX(JUNI!F8:F33;VERGELIJKEN(u;JUNI!E8:E33;0));jul;INDEX(JULI!F8:F33;VERGELIJKEN(u;JULI!E8:E33;0));aug;INDEX(AUGUSTUS!F8:F33;VERGELIJKEN(u;AUGUSTUS!E8:E33;0));sep;INDEX(SEPTEMBER!F8:F33;VERGELIJKEN(u;SEPTEMBER!E8:E33;0));okt;INDEX(OKTOBER!F8:F33;VERGELIJKEN(u;OKTOBER!E8:E33;0));nov;INDEX(NOVEMBER!F8:F33;VERGELIJKEN(u;NOVEMBER!E8:E33;0));dec;INDEX(DECEMBER!F8:F33;VERGELIJKEN(u;DECEMBER!E8:E33;0));z;INDEX(y;;2);s;PRODUCTMAT(--(NAAR.RIJ(INDEX(y;;1))=u);z);r;SORTEREN(HOR.STAPELEN(u;jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec;s);1);HOR.STAPELEN(r;INDEX(r;;14)/INDEX(GROEPEREN.PER(INDEX(y;;1);INDEX(y;;1);AANTALARG;0;0);;2)));"")
Voor BA25:BB36 niet :-(

=ALS.FOUT(LET(x;VERT.STAPELEN(JANUARIECEMBER!BA25:BB36);y;FILTER(x;(INDEX(x;;1)<>"")*(INDEX(x;;1)<>"selecteer"));u;UNIEK(INDEX(y;;1));jan;INDEX(JANUARI!BB25:BB36;VERGELIJKEN(u;JANUARI!BA25:BA36;0));feb;INDEX(FEBRUARI!BB25:BB36;VERGELIJKEN(u;FEBRUARI!BA25:BA36;0));mrt;INDEX(MAART!BB25:BB36;VERGELIJKEN(u;MAART!BA25:BA36;0));apr;INDEX(APRIL!BB25:BB36;VERGELIJKEN(u;APRIL!BA25:BA36;0));mei;INDEX(MEI!BB25:BB36;VERGELIJKEN(u;MEI!BA25:BA36;0));jun;INDEX(JUNI!BB25:BB36;VERGELIJKEN(u;JUNI!BA25:BA36;0));jul;INDEX(JULI!BB25:BB36;VERGELIJKEN(u;JULI!BA25:BA36;0));aug;INDEX(AUGUSTUS!BB25:BB36;VERGELIJKEN(u;AUGUSTUS!BA25:BA36;0));sep;INDEX(SEPTEMBER!BB25:BB36;VERGELIJKEN(u;SEPTEMBER!BA25:BA36;0));okt;INDEX(OKTOBER!BB25:BB36;VERGELIJKEN(u;OKTOBER!BA25:BA36;0));nov;INDEX(NOVEMBER!BB25:BB36;VERGELIJKEN(u;NOVEMBER!BA25:BA36;0));dec;INDEX(DECEMBER!BB25:BB36;VERGELIJKEN(u;DECEMBER!BA25:BA36;0));z;INDEX(y;;2);s;PRODUCTMAT(--(NAAR.RIJ(INDEX(y;;1))=u);z);r;SORTEREN(HOR.STAPELEN(u;jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec;s);1);HOR.STAPELEN(r;INDEX(r;;14)/INDEX(GROEPEREN.PER(INDEX(y;;1);INDEX(y;;1);AANTALARG;0;0);;2)));"")

 

[HSV]

Zo op het eerste gezicht niets mis mee, maar ik ga geen bestanden nabootsen die jij al panklaar hebt liggen.

 

[bartvt78]

Het was inderdaad panklaar. Ik had te vroeg gepanikeerd. Mijn range stond niet goed. Mijn file is af op nog een paar schoonheidsfoutjes die ik moet oplossen. Hartelijk dank aan iedereen die mee geholpen heeft om een file vol fouten naar een (hopelijk nu) foutloze te transformeren. Ik waardeer alle hulp en denkwerk.

 

[bartvt78]

Goede avond allen. Voor mijn file heb ik nog 1 formule nodig. In bijgevoegde file staat de formule in het geel, wat perfect werkt. Nu wil ik echter de formule uitbreiden met 3 extra kolommen, nl Soort, Periodiciteit en nota. De formule in het geel gaat naar de cellen L6 tot M15 in de verschillende maanden. Voor de nieuwe formule zou de range gaan van D6 tot H15. De data is hetzelfde, maar dus met 3 extra kolommen. Ik heb een paar pogingen gedaan om zelf de formule aan te passen maar ik kom er niet uit. De maker van de formule, @HSV, heeft prachtig werk verricht met de originele formule te maken. @AlexCEL is ook een goede bijdrager geweest om mij goede formules te bezorgen. Ik hoop dat iemand mijn laatste benodigde formule kan geven om de file compleet te maken. Alvast hartelijk dank.

 

[emields]

en dat allemaal terwijl het ook gemakkelijk kan.

 

[peter59]

en dat allemaal terwijl het ook gemakkelijk kan.

Ja, waarom makkelijk als het ook zeer moeilijk kan.
Ga maar eens terug naar het hele prille begin dan zie dat er vele mogelijkheden zijn geboden waaronder door jezelf. TS blijft gewoon op de zeer moeilijk manier door borduren en dat gaat vroeg of laat fout.

 

[Verwijderd lid 501906]

Hmm je maakt het jezelf wel heel moeilijk, vind ik.
Ben het dus met peter59 #58 eens
De ellelange formules breken je op.

Ik snap dat je een beknopt overzicht wilt hebben van abonnementen, lidmaatschappen etc.
en of je in een bepaalde maand betalingen moet doen.
die betalingen zijn jaarlijks, maandelijks, wekelijks etc.
Ik gebruik zelf bijgevoegde “lasten” daar staan, bij mij, inmiddels 100 items in.

Ik maak per onderwerp een pagina aan copy van "basis"
Op deze pagina’s kan ik ook aanvullende info kwijt.
In kolom L heb ik overzicht over of er betalingen gedaan moeten worden d.m.v. voorwaardelijke opmaak
Als ik alle betalingen gedaan heb, dan update ik de data op achterliggende pagina’s [knop]
Pas op met aanpassen van kolommen ivm VBA-script

Ik hoop dat je hier iets aan hebt.

 

[bartvt78]

Beste collega forum leden, hartelijk dank voor de aangeboden oplossingen. Echter denk ik dat zo een formule het beste past in mijn file. Maar ik geef grif toe dat dit boven mijn petje gaat. Mij lukt het niet om zulke formules te maken. Ik zou wel willen om de file hier te plaatsen, dan zou het duidelijker worden waarom ik voor een formule zou gaan. Maar ik ben terughoudend omwille van de volgende reden: Die file heb ik ooit gekocht als onderdeel van een online cursus budget beheer. In de originele file staat een tabblad met waarschuwingen over onder andere intellectueel recht, verbod de file te verkopen, openbaar te maken, en nog vele andere waarschuwingen. Op zich is de file een goed begin, maar er stonden nog tientallen fouten in. Het enige dat ik behouden heb, is grote stukken lay-out, en sommige formules. De file was ook paswoord protected. De protectie heb ik weten weg te halen, en nu ben ik dus bezig met de file correct werkend te maken, de fouten eruit te halen. Mijn oplossing is beter dan het origineel maar ook nog niet perfect, dat besef ik maar al te goed. Maar de disclaimer weerhoud me ervan om de "echte" file hier als bijlage te plaatsen, en waarom ik sommige voorbeeldfiles hier gepost heb met voorbeeld data. Ik voeg de voorbeeldfile nog eens toe, met mijn aanpassingen aan de formule, maar dan geeft de formule geen data weer. Het is dus niet dat ik niet zelf probeer, maar dit soort formules is te complex voor mij, maar het is wel zulk een formule dat ik nodig heb. Met power query's of draaitabellen geeft dit niet het gewenste resultaat, hoe jammer het ook is.
Met vriendelijke groet,
Bart