Lijst in excel samenstellen met totalen

[bartvt78]

Goedemiddag toppers,

Ik ben weer op een probleem gestoten waarvoor ik de juiste formule(s?) niet kan vinden. Op elke maand tab staan verscheidene lidgelden, abonnementen en verzekeringen. In het eerste tabblad wil ik dus een lijst, hoeft niet chronologisch, met alle verschillende lidgelden/abonnementen/verzerkeringen, met achteraan het totaal bedrag per jaar dat voor elk item is uitgegeven en eventuele nota. Ik vermoed dat er met tussenstappen gewerkt gaat moeten worden, maar zonder zou uiteraard nog beter zijn. Ik denk dat de voorbeeldfile duidelijk is zoals ik hem heb gemaakt. Indien er nog info nodig is hoor ik het graag. Mijn office/excel versie is 365.
Hartelijk dank bij voorbaat.

 

[emields]

Maak idpv voor elke maand een blad 1 lijst en gebruik cel A1. Hou er rekening mee dat excel een rekenblad is en geen kleurboek.

 

[bartvt78]

De kleuren zijn er om het duidelijk te maken. In de "echte" file gebruik ik geen kleur opmaak. De maanden zijn nodig voor allerlei andere visualisaties en berekeningen. En wat als alles op 1 tabblad zou komen te staan als tussenstap?

 

[peter59]

Komt dit al een beetje in de buurt?
Hiervoor power query gebruikt.

 

[AD1957]

Misschien moet je de "echte file" met fictieve gegevens maar eens plaatsen.

 

[bartvt78]

Hey Peter, dat is wat ik nodig heb. Maar een power query, hoe maak je dat, of hoe kan ik die aanpassen zodat deze query werkt in de echte file? Ik heb nog nooit met een power qeury gewerkt.

 

[CorVerm]

Kijk maar op deze site

Power Query - Ophalen, Bewerken en Inlezen van gegevens in Excel!

Met Power Query kun je gegevens uit diverse bronnen in Excel inlezen en bewerken. Power Query minimaliseert de kans op fouten. Gebruik het!

www.excelacademy.nl

 

[peter59]

@bartvt78
Hier mee dien je in jouw geval eens mee te beginnen.
https://exceloffthegrid.com/power-query-import-data-from-the-current-workbook/

 

[HSV]

Office 365 formule.

 

[bartvt78]

Hey HSV, ik kijk er binnen een uurtje in detail naar, maar op het eerste zicht komt de nota niet mee in het overzicht. Zowel de inboedelverzekering als de autoverzekering heeft een nota in mijn voorbeeld, en bij jou uitkomst staat er bij nota overal 0. Ik ga straks ook proberen of ik een powerquery kan maken, maar dat is helemaal nieuw voor mij.

 

[HSV]

In de herkansing.

 

[bartvt78]

Hartelijk dank, dit is perfect wat ik nodig heb. Ik had er zelf niet opgekomen.

 

[bartvt78]

Ik heb te vroeg gejuicht. Ik dacht dat het gewoon de range aanpassen was, en klaar. Echter komt er na het aanpassen van de range nu #overlopen te staan. En ik versta nog niets van deze formule, dus ik weet niet wat er nog moet aangepast worden. Dit aangevuld voorbeeld is met de cellen die ik nodig heb in mijn file. Dus als deze werkt is het gewoon copy paste naar mijn file normaal. Hartelijk dank.

 

[jverkerk]

Je moet geen samengevoegde cellen gebruiken.

 

[bartvt78]

Dank voor de feedback. Ik zal dan wat aanpassingen moeten doen om het werkend te krijgen. (samengevoegde cellen terug splitsen).

 

[peter59]

En zorg dat ieder tabblad dezelfde lay-out heeft en gelijke kolomkopnamen enz.
Want als de basis niet goed is dan gaat het in het vervolg traject vroeg of laat behoorlijk fout.
Nogmaals een poging met power query.

 

[bartvt78]

Hartelijk dank Peter. Ik ga me in de power query verdiepen. Misschien lukt dit wel met samengevoegde cellen?

 

[snb]

Niets eenvoudiger dan:

Sub M_snb()
  cells.unmerge
End sub

Samengevoegde cellen dienen geen enkel doel.

 

[bartvt78]

Ik heb voortgebouwd op het antwoord van jverkerk. Nu probeer ik al 2 dagen die formule op te splitsen in verstaanbare delen om ze op andere data te kunnen gebruiken. Maar ik kom er niet uit met mijn beperkte kennis. Ik heb een bestandje gemaakt waarbij ik eerst de formule van jverkerk toepas, wat 99,99% is wat ik nodig heb. Ik heb dan geprobeerd om die formule op te splitsen in begrijpbare delen voor mij, maar zelfs die delen werken niet. Ik weet ook niet precies waar ik de mist in ga. Zoals ik zei, ik ben zeker geen formule expert. Kan iemand mij begrijpbaar op weg helpen met behulp van mijn tussenstappen? En zeggen waar ik verkeerd ben?

 

[peter59]

Ik zelf heb ook geen idee maar dit is wat ChatGPT er over zegt.

Deze Excel-formule is een complexe constructie die meerdere functies combineert, waaronder ALS, LET, VERT.STAPELEN, FILTER, KIES.KOLOMMEN, en GROEPEREN.PER. Hieronder een overzicht van wat de formule doet:

1. Context
De formule lijkt een dynamische tabel of dataset te verwerken die zich bevindt in het bereik $A$2:$E$15. Het doel is waarschijnlijk om de gegevens te groeperen, te filteren, en mogelijk een andere dataset op te bouwen of specifieke resultaten te tonen op basis van een bepaalde voorwaarde.

2. Onderdeel voor onderdeel
LET-functie
De LET-functie wordt gebruikt om variabelen te definiëren en herhaald gebruik te optimaliseren.

x: Definieert een gestapelde versie van de gegevens uit $A$2:$E$15 met VERT.STAPELEN. Dit kan worden gebruikt om lege rijen of kolommen te verwijderen en een samenhangende dataset te creëren.
y: Filtert de gestapelde gegevens (x) om alleen rijen op te nemen waar de eerste kolom (INDEX(x;;1)) niet leeg is.
GROEPEREN.PER
De functie GROEPEREN.PER groepeert gegevens op basis van bepaalde kolommen:

KIES.KOLOMMEN(y;1;2;3;5): Selecteert kolommen 1, 2, 3 en 5 van y als groepscriteria.
INDEX(y;;4): Gebruikt kolom 4 (bijvoorbeeld bedragen) voor aggregatie met de SOM-functie.
KIES.KOLOMMEN
De KIES.KOLOMMEN-functie herschikt of selecteert specifieke kolommen in de gegroepeerde data om de uiteindelijke output samen te stellen.

VERT.STAPELEN
De tabel wordt opnieuw opgebouwd met VERT.STAPELEN, en een kopregel wordt toegevoegd ({"Soort", "Naam", "Periodiciteit", "Nota", "Bedrag"}).

ALS-Functie
Controleert of de output van de bewerking gelijk is aan "SELECTEER". Als dit het geval is, retourneert de formule een lege waarde (""), anders voert hij de LET-bewerking opnieuw uit en retourneert de volledige dataset.
3. Samenvatting
De formule:

Neemt gegevens uit $A$2:$E$15.
Filtert lege rijen en herstructureert de gegevens.
Groepeert gegevens op basis van geselecteerde kolommen en een aggregatiefunctie (SOM).
Voegt een kopregel toe aan de gegroepeerde gegevens.
Controleert of het resultaat gelijk is aan "SELECTEER" en retourneert anders de bewerkte dataset.
4. Praktisch nut
De formule lijkt bedoeld om een dataset dynamisch te transformeren en te groeperen, bijvoorbeeld:

Groeperen van financiële gegevens per categorie of klant.
Filteren van onvolledige gegevens.
Presenteren van een samenvattingstabel.