Kwartaalregistratie

[wheel]

Pff.. korte nacht als je ligt te malen over een probleem.

Ik heb in een doorlopende rapportage een aantal rijen met datums en codes. Nu wil ik die codes graag per maand per item hebben opgeteld op blad2.

Hoe kan ik dit het beste aanpakken? Het is waarschijnlijk iets met AANTAL.ALS maar er moet ergens ook een SOM in komen, denk ik.

Wie kan mij hiermee helpen?

Thnx, Wheel.

 

[gelens]

Wheel

Kijk eens naar Som.als

 

[gelens]

Wheel

Ik denk dat het volgende wel gaat werken :

=SOMPRODUCT((MAAND(Blad1!$A$2:$A$26)=MAAND(Blad2!$B$3))*(Blad1!$B$2:$B$26=Blad2!$A6))

Voorwaarde is wel dat de maand op blad 2 als datum ingegevenis, dus iets als : 01-11-2010
Met celeigenschappen kan je daar wel weer November van maken.

 

[wheel]

Hoi,

Indrukwekkende formule, al begrijp ik er geen snars van.

Ik heb gedaan wat je zei, maar volgens mij gaat er ergens iets niet goed. En moet ik die formule doorvoeren in alle maanden?

Zie bestandje.

Groeten, Wheel.

 

[gelens]

Wheel,

Je had een klein foutje gemaakt met het kopieeren.
In jouw eerste bestand starte de gegevens op blad2 op regel 4.
In het nieuwe bestand starten deze op regel 2, daardoor liep het mis.

 

[wheel]

Hoi,

Het lijkt zo gemakkelijk.

Op blad1 staan er gegevens in kolom C en D. Ook in rij 28 staan enkele gegevens. Hoe moet ik die interpreteren?

De rapportage is doorlopend. Het houdt dus niet op na deze 26 rijen, maar gaat door. Het is uiteindelijk een jaarrapportage met vier kwartaalregistraties.

Hoe los ik dat op?

Thnx, Wheel.

 

[gelens]

Wheel,

De gegevens in kolom C en D was ik vergeten te verwijderen. Ook de gegevens in rij 28 was ik vergeten.

Ik heb nu 2 dynamische bereiken aangemaakt : Datum en Code
Deze bereiken "groeien " mee als er gegevens in kolom A en B worden toegevoegd.

De formule zoekt naar de MAAND in het bereik Datum, kolom A, en vergelijkt deze met de gewenste maand, rij 1 op blad 2.
Daarnaast wordt er gekeken of in het bereik CODE, kolom B, de code uit kolom A van Blad2 voorkomt.

=SOMPRODUCT((MAAND(Datum)=MAAND(Blad2!B$1))*(Code=Blad2!$A3))

 

[wheel]

Wat de formule doet, is me echt een raadsel. Ik zie alleen maar dat ie iets van blad2 afhaalt, terwijl de data op blad1 worden ingevoerd. Tenminste, zo interpreteer ik het.

Als ik de formule in mijn uiteindelijke rapport probeer over te hevelen, krijg ik foutmeldingen. Wat doe ik verkeerd?

Ik zie door de bomen het bos niet meer. Los van het feit dat ik het niet helemaal snap.

Bedankt, Wheel.

 

[popipipo]

Ik zie door de bomen het bos niet meer. Los van het feit dat ik het niet helemaal snap

Probeer dit wel te begrijpen want niets is zo vervelend als met een programma werken wat je niet snapt. Krijg je problemen dan zit je meteen met je handen in het haar en baal je als een stekker

Ik heb nu 2 dynamische bereiken aangemaakt : Datum en Code
Deze bereiken "groeien " mee als er gegevens in kolom A en B worden toegevoegd.

Deze 2 bereiken ontbreken in je voorbeeldje.
Via CTRL F3 kun je deze definiëren.

 

[cow18]

geen formules, alleen een draaitabel

 

[wheel]

Probeer dit wel te begrijpen want niets is zo vervelend als met een programma werken wat je niet snapt. Krijg je problemen dan zit je meteen met je handen in het haar en baal je als een stekker

Deze 2 bereiken ontbreken in je voorbeeldje.
Via CTRL F3 kun je deze definiëren.

Hoi popipipo,

Ik probeer ook te begrijpen wat de formules doen, maar dan zou enige uitleg erbij wel handig zijn. Bijvoorbeeld wat doet welk deel van de formule. Daar leer ik van. :thumb: Ik wil nu vooral niet arrogant overkomen door dit zo te stellen. Begrijp me niet verkeerd. Ik ben ontzettend blij met jullie input. :thumb:

Het voorbeeldje over de 2 bereiken is niet van mij, maar van gelens. Wat bedoel je met de 2 bereiken definiëren met CTRL F3? Ik heb wel bereiken gemaakt om de validatie te kunnen doen.

Groeten, Wheel.

 

[wheel]

geen formules, alleen een draaitabel

Hoi cow18,

Bedankt voor je bijdrage. :thumb:

Ik weet helaas ook nog niet om te gaan met draaitabellen. Daar moet ik ook nog eens induiken.

Het is de bedoeling dat de registratie in zijn geheel zichtbaar is, weergegeven met alle items en maanden en de daarbij behorende aantallen en totalen.

Groeten, Wheel.

 

[popipipo]

In het bestand van gelens staat op blad2 cel B1 de formule:

=SUMPRODUCT((MONTH([COLOR="red"]Datum[/COLOR])=MONTH(Blad2!B$1))*(Code=Blad2!$A2))

Daar zie je datum staan.
Dit is een bereik.
Dit bereik is gedefinieerd als:

=OFFSET(Blad1!$A$1;1;0;COUNTA(Blad1!$A:$A)-1;1)

Dit vind je terug via CTRL F3

Dmv het gebruik van OFFSET en COUNTA maak je het bereik dynamisch.
dwz hoe meer gegevens er staan hoe groter het bereik en hoe minder gegevens hoe kleiner het bereik.
Het bereik past zich dus aan met de hoeveelheid gegevens.

Dit is belangrijk voor de SUMPRODUCT formule want het wordt lastig als in het bereik lege cellen zitten. Zo hoef je de SUMPRODUCT formule dus niet steeds aan te passen.

 

[cow18]

zoals gevraagd
bijkomend gesorteerd om aantal

 

[wheel]

In het bestand van gelens staat op blad2 cel B1 de formule:

=SUMPRODUCT((MONTH([COLOR="red"]Datum[/COLOR])=MONTH(Blad2!B$1))*(Code=Blad2!$A2))

Daar zie je datum staan.
Dit is een bereik.
Dit bereik is gedefinieerd als:

=OFFSET(Blad1!$A$1;1;0;COUNTA(Blad1!$A:$A)-1;1)

Dit vind je terug via CTRL F3

Dmv het gebruik van OFFSET en COUNTA maak je het bereik dynamisch.
dwz hoe meer gegevens er staan hoe groter het bereik en hoe minder gegevens hoe kleiner het bereik.
Het bereik past zich dus aan met de hoeveelheid gegevens.

Dit is belangrijk voor de SUMPRODUCT formule want het wordt lastig als in het bereik lege cellen zitten. Zo hoef je de SUMPRODUCT formule dus niet steeds aan te passen.

Toen je het had over CTRL F3 en de bereiken waarover gelens het had, begon me iets te dagen waar ik het moest zoeken.

Ik heb het idee dat ik er dicht tegenaan zit, maar kom er niet uit. Ik ben aan de slag gegaan met dynamische bereiken onder Datum en Code. Op het blad Probleem gaat het vervolgens fout.

Kan ik trouwens de codes (blad Rapport kolom G en blad Probleem kolom B) ook helemaal weglaten en alleen met de items/problemen werken uit kolom E van blad Rapport en kolom A van blad Probleem?

Groeten, Wheel.

 

[cow18]

je gedefinieerde namen klopten niet. Als je bovendien maakt dat de 2 kolommen van elkaar afhankelijk zijn kwa aantal, dan kan het daar ook al niet fout gaan. In dit geval bepaalt alleen de kolom van de datums de diepte van beide bereiken

Code	=VERSCHUIVING(Datum;;6;;)
Datum	=VERSCHUIVING(Rapport!$A$5;;;AANTALARG(Rapport!$A$5:$A$1000);1)

 

[wheel]

je gedefinieerde namen klopten niet. Als je bovendien maakt dat de 2 kolommen van elkaar afhankelijk zijn kwa aantal, dan kan het daar ook al niet fout gaan. In dit geval bepaalt alleen de kolom van de datums de diepte van beide bereiken

Code	=VERSCHUIVING(Datum;;6;;)
Datum	=VERSCHUIVING(Rapport!$A$5;;;AANTALARG(Rapport!$A$5:$A$1000);1)

Hoi cow18,

Ik had, naar voorbeeld van gelens, de bereiken van zijn bestand vertaald naar het mijne en toch ging het niet goed.

Ik begrijp uit deze codes dat de range is beperkt tot 1000. Kan die ook op oneindig worden gezet middels $A:$A

Groeten, Wheel.

 

[cow18]

maak van die 1000 dan 65.000 in Excel 2003 en 1.000.000 in >=Excel2007.
Niet tot de laatste rij anders krijg je moeilijkheden als je straks rijen wil tussenvoegen.
Of iets moeilijker

=VERSCHUIVING(Rapport!$A$5;;;AANTALARG(Rapport!$A:$A)-AANTALARG(Rapport!$A$1:$A$4);1)

Let op : in alle versies moeten al je datums netjes onder elkaar aaneensluiten, dus mag je nooit een lege cel in je A-kolom staan hebben als er verderop nog cellen in de A-kolom gevuld zijn. Anders riskeer je fouten in de formule !!!

 

[wheel]

maak van die 1000 dan 65.000 in Excel 2003 en 1.000.000 in >=Excel2007.
Niet tot de laatste rij anders krijg je moeilijkheden als je straks rijen wil tussenvoegen.
Of iets moeilijker

=VERSCHUIVING(Rapport!$A$5;;;AANTALARG(Rapport!$A:$A)-AANTALARG(Rapport!$A$1:$A$4);1)

Let op : in alle versies moeten al je datums netjes onder elkaar aaneensluiten, dus mag je nooit een lege cel in je A-kolom staan hebben als er verderop nog cellen in de A-kolom gevuld zijn. Anders riskeer je fouten in de formule !!!

Voorlopig werkt de eerste test op het werk perfect. Hulde! :thumb:

Dank aan iedereen die heeft meegedacht en geholpen! Top! :thumb:

Wheel, die weer iets heeft geleerd.

 

[cow18]

gevoelsmatig was ik meer voorstander van de draaitabel, want die rekent 1 maal alles uit bij het vernieuwen en anders belast die verder je computer bij het herrekenen, dus je werkmap wordt sneller. Maar dat is mijn bescheiden mening ... .