Doorgaan naar hoofdcontent

Excel: laatste datum voor een groep, draaitabel of matrixformule?

Via een Excel groep krijg ik de vraag hoe je de laatste datum voor een groep er uit kunt pikken. We geven hier even de voorbeelddata:


Voor zover ik kan zien zijn er in ieder geval twee mogelijkheden: met matrixformules en met een draaitabel.

Oplossing: draaitabel

We zullen het in dit voorbeeld maar even helemaal volgens de regels van de Excel kunst doen. Voor het maken van de draaitabel heb ik de lijst eerst omgezet naar een tabel (INVOEGEN => DRAAITABEL). De naam veranderen we dan even van Tabel1 in draaitabel. Vervolgens maken we de draaitabel.

Via Waardeveldinstellingen kiezen we dan voor het datumveld voor Max en bij Getalnotatie voor Datum. De kopjes zetten we even om naar Naam en Laatste datum. Klaar.

Oplossing: matrixformules

Voor dat we de matrixformules gaan maken, creëren we eerst namen met flexibele bereiken:

datum =VERSCHUIVING(Blad1!$B$2;0;0;AANTALARG(Blad1!$B:$B)-1;1)
naam =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A)-1;1)

Vervolgens typen we in het bereik D4:D9 aap t/m jet. Daarboven plaatsen we weer kopjes. Dan typen we in cel E4 de formule:

=MAX(ALS(naam=D4;datum))

We moeten hier een matrixformule van maken en dus afsluiten door op CTRL SHIFT ENTER te drukken. In de cel zien we dan:

{=MAX(ALS(naam=D4;datum))}

Deze matrixformule trekken we dan door tot D9.

Conclusie

De oplossing met de matrixformules is technisch meer een kunststukje. De oplossing met de draaitabel is eenvoudiger. De laatste oplossing groeit ook mee met de data. Het voordeel van de matrix formule is wel dat deze onmiddellijk wordt bijgewerkt. Dat maakt hem dynamischer dan een draaitabel.

Bijbehorende bestand ExcelMax.xlsx is te downloaden via:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Verdere tips: je kunt het bestand exceltips.pdf (laatst gewijzigd (12-9-2014) hier downloaden:


Reacties

Populaire posts van deze blog

Excel: gegevenslabel alleen bij de laatste waarde in een grafiek

Creatief met Corona Bij het maken van een grafiek over de voortschrijdende Corona cijfers in Nederland liep ik tegen een probleem aan. Als je bij een reeks gegevens in een grafiek de gegevenslabels aan zet, krijg je die labels bij elke waarde. Ik wilde dit label alleen bij de laatste waarde tonen. Nu kun je natuurlijk alle andere gegevenslabels afzonderlijk wissen, maar dat is een hoop werk. Op zoek naar een andere manier dus. De cijfers datum aantal 1-3-2020 3 2-3-2020 8 3-3-2020 5 4-3-2020 15 5-3-2020 44 6-3-2020 46 7-3-2020 60 8-3-2020 76 9-3-2020 57 10-3-2020 61 11-3-2020 121 12-3-2020 111 13-3-2020 190 14-3-2020 155 15-3-2020 176 16-3-2020 278 17-3-2020 292 18-3-2020 250 19-3-2020 20-3-2020 21-3-2020 22-3-2020 23-3-2020 24-3-2020 25-3-2020 ...

Vrouwen van het Friese verzet. Gevecht op vele fronten.

  Al toen mijn vorige boek, de biografie van Piet Oberman zijn voltooiing naderde, rees bij mij het idee onderzoek te doen naar de bijdrage van vrouwen aan het Friese verzet. Hoe groot was hun bijdrage eigenlijk geweest? En wie waren deze vrouwen precies? Eenmaal aan de slag werden me van alle kanten namen aangereikt. Of ik aan Wieke Bosch gedacht had? Nee, nooit van gehoord zelfs. Of Bouwina Meindertsma? Ja, die naam was ik eerder tegen-gekomen. Zelf ging ik ook op onderzoek uit en zo onstond gaande-weg een lijst met tweeëndertig namen van vrouwen die in de oorlog betrokken waren bij het verzet tegen de Duitse overheerser. Voor een deel afkomstig uit Friesland, maar voor een groot deel ook uit de rest van Nederland. Uit Groningen, Utrecht en Noord- en Zuid-Holland. En zelfs een jonge Joodse vrouw uit Duitsland. Ze moesten in de Tweede Wereldoorlog het hoofd bieden aan een wrede bezetter. Maar het boek gaat niet alleen over de oorlog.  De vrouwen moesten ook nog opboksen ...

Excel: Dynamische grafiek op basis van een geparametriseerde Power Query tabel

Dynamische grafieken in Excel Grafieken in Excel zijn dynamisch te maken met allerlei complexe formules (bijv. index, vergelijken en verschuiving). Makkelijker is het de grafiek te baseren op een Excel tabel (te maken via Invoegen => Tabel ). Nog leuker wordt het als een dergelijke tabel het resultaat is van een Power Query die we met behulp van keuzelijsten kunnen aansturen. Hieronder een voorbeeld. De tabel met parameters Deze heb ik linksboven geplaatst. De naam van deze tabel is Parameters (via Formules => Namen beheren ). Deze tabel wordt in Power Query gebruikt voor het oppikken van variabelen uit Excel, in dit geval DatumBegin en DatumEind . Deze variabelen worden via Gegevens => Gegevensvalidatie begrensd door de datum uit de eerste kolom van het blad Data . De gebruikte gegevens Deze staan op het blad Data en lopen van 1-1-2020 tot en met 20-8-2020. In de tweede kolom staan willekeurige bedragen. Deze gegevens zijn via From Table / Range ingeladen in Power Query ....