Excel

Få de tidligste og seneste projektdatoer

Get Earliest Latest Project Dates

Excel -formel: Få de tidligste og seneste projektdatoerResumé

Dette eksempel viser, hvordan man henter de tidligste og seneste datoer, der er knyttet til et projekt. I det viste eksempel er formlerne i H5 og I5:





 
= MINIFS (data[Start],data[Project],G5) // earliest = MAXIFS (data[End],data[Project],G5) // latest

hvor 'data' er en Excel -tabel som vist, og projektnavne i kolonne G matcher kolonne B.

Bemærk: MINIFS og MAXIFS er kun tilgængelige i Excel 365 og Excel 2019. I andre versioner af Excel kan du bruge en simpel matrixformel, som forklaret nedenfor.





Introduktion

Opgaven her er at finde de tidligste og seneste datoer i forbindelse med et givet projekt. De tidligste datoer stammer fra Start kolonne, og de seneste datoer kommer fra Ende kolonne.

Du kan blive fristet til at bruge en opslagsfunktion som VLOOKUP , XLOOKUP , eller INDEX og MATCH. Men da hvert projekt har mere end én post, og poster ikke altid er sorteret efter dato, bliver dette udfordrende.



En bedre tilgang er at bruge elimineringsprocessen: kassér datoer for andre projekter, og arbejd kun med de datoer, der er tilbage.

Forklaring

Det MINIFS -funktion returnerer mindste numerisk værdi, der opfylder de angivne kriterier, og MAXIFS funktion returnerer største numerisk værdi, der opfylder de angivne kriterier.

Ligesom COUNTIFS og SUMIFS bruger disse funktioner række/kriterier 'par' til at anvende betingelser. For begge formler har vi kun brug for en betingelse: projektnavnet skal svare til navnet i kolonne G:

 
data[Project],G5 // condition

For at få tidligst startdato, bruger vi:

 
= MINIFS (data[Start],data[Project],G5) // earliest date

Her returnerer MINIFS minimum værdi i Start kolonne, hvor projektet er lig med 'Omega' (fra celle G5). Siden Excel -datoer er kun tal , minimumsdatoen er den samme som den tidligste dato.

hvordan man bruger hvis og og fungerer i Excel

For at få seneste slutdato, bruger vi:

 
= MAXIFS (data[End],data[Project],G5) // latest date

Her returnerer MAXIFS maksimum værdi i Ende kolonne, hvor projektet er lig med 'Omega'. Som ovenfor er den maksimale værdi den samme som den seneste dato.

Array formel alternativ

Hvis du ikke har MINIFS og MAXIFS, kan du bruge enkle matrixformler baseret på MIN og MAX funktioner, for at få det samme resultat. For den tidligste startdato:

 
{= MIN ( IF (data[Project]=G5,data[Start]))}

For den sidste slutdato:

 
{= MAX ( IF (data[Project]=G5,data[End]))}

Bemærk: begge formler er array formler og skal indtastes med kontrol + skift + enter, i Excel 2019 eller tidligere. Med Excel 365 kan du indtaste formlerne normalt, da matrixformler er native .

I begge tilfælde er IF -funktion bruges til at 'filtrere' datoværdier som denne:

 
 IF (data[Project]=G5,data[End]) // filter dates by project

Når G5 er 'Omega', returnerer IF slutdatoen. Ellers returnerer IF FALSK. Da vi tester alle projektnavne i tabellen på samme tid, er resultatet en række værdier som dette:

 
{439364398343990FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE}

De store serienumre er Excel -datoer tilknyttet projekt Omega. De andre værdier er FALSE, da projektet ikke er Omega. Fordi MIN og MAX er programmeret til at ignorere de logiske værdier SAND og FALSK, fungerer de kun på de resterende værdier. MIN returnerer den mindste (tidligste) dato, og MAX returnerer den største (seneste) dato.

Vedhæftede filer Fil få de tidligste og seneste projektdatoer. xlsx Forfatter Dave Bruns


^