Samenvatting
Dit voorbeeld laat zien hoe u de vroegste en laatste datums ophaalt die aan een project zijn gekoppeld. In het getoonde voorbeeld zijn de formules in H5 en I5:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
waarbij "data" een Excel-tabel is zoals getoond, en projectnamen in kolom G komen overeen met die kolom B.
Opmerking: MINIFS en MAXIFS zijn alleen beschikbaar in Excel 365 en Excel 2019. In andere versies van Excel kunt u een eenvoudige matrixformule gebruiken, zoals hieronder wordt uitgelegd.
Invoering
De taak hier is om de vroegste en laatste datums te vinden die bij een bepaald project horen. De vroegste datums komen uit de kolom Start en de laatste datums komen uit de kolom Eind .
U kunt in de verleiding komen om een opzoekfunctie zoals VERT.ZOEKEN, XLOOKUP of INDEX en MATCH te gebruiken. Aangezien elk project echter meer dan één item heeft en items niet altijd op datum zijn gesorteerd, wordt dit een uitdaging.
Een betere benadering is om het eliminatieproces te gebruiken: gooi datums weg voor andere projecten en werk alleen met de resterende datums.
Uitleg
De MINIFS-functie retourneert de kleinste numerieke waarde die voldoet aan de opgegeven criteria, en de MAXIFS-functie retourneert de grootste numerieke waarde die voldoet aan de opgegeven criteria.
Net als COUNTIFS en SUMIFS gebruiken deze functies bereik / criterium "paren" om voorwaarden toe te passen. Voor beide formules hebben we slechts één voorwaarde nodig: de projectnaam moet gelijk zijn aan de naam in kolom G:
data(Project),G5 // condition
Om de vroegste startdatum te krijgen, gebruiken we:
=MINIFS(data(Start),data(Project),G5) // earliest date
Hier retourneert MINIFS de minimumwaarde in de kolom Start waar het project gelijk is aan "Omega" (uit cel G5). Omdat Excel-datums slechts getallen zijn, is de minimumdatum dezelfde als de vroegste datum.
Om de laatste einddatum te krijgen, gebruiken we:
=MAXIFS(data(End),data(Project),G5) // latest date
Hier retourneert MAXIFS de maximale waarde in de kolom Eind waar het project gelijk is aan "Omega". Zoals hierboven is de maximale waarde hetzelfde als de laatste datum.
Matrixformule alternatief
Als u geen MINIFS en MAXIFS hebt, kunt u eenvoudige matrixformules gebruiken, gebaseerd op de MIN- en MAX-functies, om hetzelfde resultaat te krijgen. Voor de vroegste startdatum:
(=MIN(IF(data(Project)=G5,data(Start))))
Voor de laatste einddatum:
(=MAX(IF(data(Project)=G5,data(End))))
Let op: beide formules zijn matrixformules en moeten ingevoerd worden met control + shift + enter, in Excel 2019 of eerder. Met Excel 365 kunt u de formules normaal invoeren, aangezien matrixformules native zijn.
In beide gevallen wordt de ALS-functie gebruikt om datumwaarden als volgt te "filteren":
IF(data(Project)=G5,data(End)) // filter dates by project
Als G5 "Omega" is, geeft IF de einddatum terug. Anders retourneert IF FALSE. Omdat we alle projectnamen in de tabel tegelijkertijd testen, is het resultaat een reeks waarden zoals deze:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
De grote serienummers zijn Excel-datums die horen bij project Omega. De andere waarden zijn FALSE, aangezien het project niet Omega is. Omdat MIN en MAX zijn geprogrammeerd om de logische waarden TRUE en FALSE te negeren, werken ze alleen op de overige waarden. MIN retourneert de kleinste (vroegste) datum en MAX geeft de grootste (laatste) datum terug.
