Sommario
Questo esempio mostra come recuperare la prima e l'ultima data associate a un progetto. Nell'esempio mostrato, le formule in H5 e I5 sono:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
dove "dati" è una tabella di Excel come mostrato e i nomi dei progetti nella colonna G corrispondono a quelli della colonna B.
Nota: MINIFS e MAXIFS sono disponibili solo in Excel 365 ed Excel 2019. In altre versioni di Excel, è possibile utilizzare una semplice formula di matrice, come spiegato di seguito.
introduzione
Il compito qui è trovare le prime e le ultime date associate a un determinato progetto. Le prime date provengono dalla colonna Inizio e le date più recenti provengono dalla colonna Fine .
Potresti essere tentato di utilizzare una funzione di ricerca come CERCA.VERT, XLOOKUP o INDICE e CONFRONTA. Tuttavia, poiché ogni progetto ha più di una voce e le voci potrebbero non essere sempre ordinate per data, questo diventa difficile.
Un approccio migliore consiste nell'utilizzare il processo di eliminazione: scartare le date per altri progetti e lavorare solo con le date rimanenti.
Spiegazione
La funzione MINIFS restituisce il valore numerico più piccolo che soddisfa i criteri forniti e la funzione MAXIFS restituisce il valore numerico più grande che soddisfa i criteri forniti.
Come COUNTIFS e SUMIFS, queste funzioni utilizzano "coppie" intervallo / criteri per applicare le condizioni. Per entrambe le formule è necessaria una sola condizione: il nome del progetto deve essere uguale al nome nella colonna G:
data(Project),G5 // condition
Per ottenere la prima data di inizio, utilizziamo:
=MINIFS(data(Start),data(Project),G5) // earliest date
Qui, MINIFS restituisce il valore minimo nella colonna Start dove il progetto è uguale a "Omega" (dalla cella G5). Poiché le date di Excel sono solo numeri, la data minima è la stessa della prima data.
Per ottenere l'ultima data di fine, utilizziamo:
=MAXIFS(data(End),data(Project),G5) // latest date
Qui, MAXIFS restituisce il valore massimo nella colonna Fine dove il progetto è uguale a "Omega". Come sopra, il valore massimo è lo stesso dell'ultima data.
Alternativa alla formula di matrice
Se non hai MINIFS e MAXIFS, puoi usare semplici formule di matrice, basate sulle funzioni MIN e MAX, per ottenere lo stesso risultato. Per la prima data di inizio:
(=MIN(IF(data(Project)=G5,data(Start))))
Per l'ultima data di fine:
(=MAX(IF(data(Project)=G5,data(End))))
Nota: entrambe le formule sono formule di matrice e devono essere inserite con CTRL + MAIUSC + INVIO, in Excel 2019 o versioni precedenti. Con Excel 365, puoi inserire le formule normalmente, poiché le formule di matrice sono native.
In entrambi i casi, la funzione IF viene utilizzata per "filtrare" i valori di data in questo modo:
IF(data(Project)=G5,data(End)) // filter dates by project
Quando G5 è "Omega", IF restituisce la data di fine. In caso contrario, IF restituisce FALSE. Poiché stiamo testando tutti i nomi di progetto nella tabella contemporaneamente, il risultato è un array di valori come questo:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
I grandi numeri di serie sono date Excel associate al progetto Omega. Gli altri valori sono FALSE, poiché il progetto non è Omega. Poiché MIN e MAX sono programmati per ignorare i valori logici VERO e FALSO, funzionano solo sui valori rimanenti. MIN restituisce la data più piccola (la prima) e MAX restituisce la data più grande (la più recente).
