Formula generica
=MAX(INDEX(data,0,MATCH(column,header,0)))
Sommario
Per recuperare il valore massimo in un insieme di dati, dove la colonna è variabile, puoi usare INDICE e CONFRONTA insieme alla funzione MAX. Nell'esempio mostrato la formula in J5 è:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
dove i dati (B5: F15) e l' intestazione (B4: F4) sono intervalli denominati.
Spiegazione
Nota: se non conosci INDEX e MATCH, consulta: Come utilizzare INDEX e MATCH
In una configurazione standard, la funzione INDICE recupera un valore in una determinata riga e colonna. Ad esempio, per ottenere il valore nella riga 2 e nella colonna 3 in un determinato intervallo:
=INDEX(range,2,3) // get value at row 2, column 3
Tuttavia, INDICE ha un trucco speciale: la capacità di recuperare intere colonne e righe. La sintassi prevede di fornire zero per l'argomento "altro". Se vuoi un'intera colonna, fornisci riga come zero. Se vuoi un'intera riga, fornisci la colonna come zero:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Nell'esempio mostrato, vogliamo trovare il valore massimo in una data colonna. La svolta è che la colonna deve essere variabile in modo che possa essere facilmente modificata. In F5, la formula è:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Lavorando dall'interno verso l'esterno, utilizziamo prima la funzione CONFRONTA per ottenere l '"indice" della colonna richiesta nella cella J4:
MATCH(J4,header,0) // get column index
Con "Green" in J4, la funzione CONFRONTA restituisce 3, poiché Green è il terzo valore nell'intestazione dell'intervallo denominato . Dopo che MATCH ha restituito un risultato, la formula può essere semplificata in questo modo:
=MAX(INDEX(data,0,3))
Con zero fornito come numero di riga, INDICE restituisce tutti i valori nella colonna 3 dei dati dell'intervallo denominato . Il risultato viene restituito alla funzione MAX in un array come questo:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
E MAX restituisce il risultato finale, 93.
Valore minimo
Per ottenere il valore minimo con una colonna variabile, puoi semplicemente sostituire la funzione MAX con la funzione MIN. La formula in J6 è:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Con FILTER
La nuova funzione FILTER può essere utilizzata anche per risolvere questo problema, poiché FILTER può filtrare i dati per riga o per colonna. Il trucco è costruire un filtro logico che escluderà altre colonne. COUNTIF funziona bene in questo caso, ma deve essere configurato "all'indietro", con J4 come intervallo e intestazione per i criteri:
=MAX(FILTER(data,COUNTIF(J4,header)))
Dopo che COUNTIF viene eseguito, abbiamo:
=MAX(FILTER(data,(0,0,1,0,0)))
E FILTER fornisce la terza colonna a MAX, come la funzione INDICE sopra.
In alternativa a COUNTIF, puoi utilizzare ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
La funzione MATCH viene nuovamente impostata "all'indietro", in modo da ottenere un array con 5 valori che serviranno da filtro logico. Dopo ISNUMBER e MATCH run, abbiamo:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
E FILTER consegna di nuovo la terza colonna a MAX.
