Sammendrag
Dette eksemplet viser hvordan du kan hente de tidligste og siste datoene knyttet til et prosjekt. I eksemplet vist er formlene i H5 og I5:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
der "data" er en Excel-tabell som vist, og prosjektnavn i kolonne G samsvarer med kolonne B.
Merk: MINIFS og MAXIFS er bare tilgjengelig i Excel 365 og Excel 2019. I andre versjoner av Excel kan du bruke en enkel matriseformel, som forklart nedenfor.
Introduksjon
Oppgaven her er å finne de tidligste og siste datoene knyttet til et gitt prosjekt. De tidligste datoene kommer fra Start- kolonnen, og de siste datoene kommer fra Slutt- kolonnen.
Du kan bli fristet til å bruke en oppslagsfunksjon som VLOOKUP, XLOOKUP eller INDEX og MATCH. Men siden hvert prosjekt har mer enn en oppføring, og oppføringene ikke alltid er sortert etter dato, blir dette utfordrende.
En bedre tilnærming er å bruke eliminasjonsprosessen: Kast datoer for andre prosjekter, og arbeid bare med datoene som er igjen.
Forklaring
MINIFS-funksjonen returnerer den minste numeriske verdien som oppfyller de oppgitte kriteriene, og MAXIFS-funksjonen returnerer den største numeriske verdien som oppfyller de oppgitte kriteriene.
Som COUNTIFS og SUMIFS bruker disse funksjonene rekkevidde / kriterier "par" for å anvende betingelser. For begge formlene trenger vi bare en betingelse: prosjektnavnet må være lik navnet i kolonne G:
data(Project),G5 // condition
For å få den tidligste startdatoen bruker vi:
=MINIFS(data(Start),data(Project),G5) // earliest date
Her returnerer MINIFS minimumsverdien i Start- kolonnen der prosjektet er lik "Omega" (fra celle G5). Siden Excel-datoer bare er tall, er minimumsdatoen den samme som den tidligste datoen.
For å få den siste sluttdatoen bruker vi:
=MAXIFS(data(End),data(Project),G5) // latest date
Her MAXIFS returnerer maksimumsverdien i slutten kolonnen der prosjektet er lik "Omega". Som ovenfor er maksimumsverdien den samme som den siste datoen.
Array formel alternativ
Hvis du ikke har MINIFS og MAXIFS, kan du bruke enkle matriseformler, basert på MIN og MAX-funksjonene, for å få det samme resultatet. For den tidligste startdatoen:
(=MIN(IF(data(Project)=G5,data(Start))))
For siste sluttdato:
(=MAX(IF(data(Project)=G5,data(End))))
Merk: begge formlene er matriseformler og må legges inn med kontroll + skift + enter, i Excel 2019 eller tidligere. Med Excel 365 kan du angi formlene normalt, siden matriseformler er innfødte.
I begge tilfeller brukes IF-funksjonen til å "filtrere" datoverdier som dette:
IF(data(Project)=G5,data(End)) // filter dates by project
Når G5 er "Omega", returnerer IF sluttdatoen. Ellers returnerer IF FALSE. Siden vi tester alle prosjektnavn i tabellen samtidig, er resultatet en rekke verdier som dette:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
De store serienumrene er Excel-datoer knyttet til prosjektet Omega. De andre verdiene er FALSE, siden prosjektet ikke er Omega. Fordi MIN og MAX er programmert til å ignorere de logiske verdiene SANN og FALSK, fungerer de bare på de gjenværende verdiene. MIN returnerer den minste (tidligste) datoen, og MAX returnerer den største (siste) datoen.
