Formula generica
=MATCH(bignum,range)
Sommario
Per ottenere l'ultima posizione relativa (ovvero l'ultima riga, l'ultima colonna) per i dati numerici (con o senza celle vuote), è possibile utilizzare la funzione CONFRONTA con un cosiddetto "numero grande".
Nell'esempio mostrato, la formula in E5 è:
=MATCH(9.99E+307,B4:B9)
Ultima posizione * relativa *, non riga sul foglio di lavoro
Quando si creano formule avanzate che creano intervalli dinamici, è spesso necessario individuare l'ultima posizione dei dati in un elenco. A seconda dei dati, questa potrebbe essere l'ultima riga con i dati, l'ultima colonna con i dati o l'intersezione di entrambi. Nota: vogliamo l'ultima * posizione relativa * all'interno di un dato intervallo, non il numero di riga sul foglio di lavoro:
Spiegazione
Questa formula utilizza la funzione CONFRONTA in modalità di corrispondenza approssimativa per individuare l'ultimo valore numerico in un intervallo. Corrispondenza approssimativa abilitata impostando il 3 ° argomento in MATCH su 1 o omettendo questo argomento, il cui valore predefinito è 1.
Il valore di ricerca è un cosiddetto "numero grande" (a volte abbreviato "bignum") che è intenzionalmente più grande di qualsiasi valore che apparirà nell'intervallo.
Il risultato è che MATCH "tornerà indietro" all'ultimo valore numerico nell'intervallo e restituirà quella posizione.
Nota: questo approccio funziona bene con celle vuote nell'intervallo, ma non è affidabile con dati misti che includono numeri e testo.
A proposito di bignum
Il numero massimo che Excel può gestire è 9,99999999999999E + 307.
Quando si utilizza CONFRONTA in questo modo, è possibile utilizzare qualsiasi numero elevato che sia garantito essere maggiore di qualsiasi valore nell'intervallo, ad esempio:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
Il vantaggio dell'utilizzo di 9.99E + 307 o simili è che è (1) un numero enorme e (2) riconoscibile come segnaposto per un "numero grande". Lo vedrai utilizzato in varie formule avanzate di Excel.
Gamma dinamica
È possibile utilizzare questa formula per creare un intervallo dinamico con altre funzioni come INDICE e OFFSET. Vedere i collegamenti seguenti per esempi e spiegazioni:
- Gamma dinamica con INDICE e CONTA.VALORI
- Gamma dinamica con OFFSET e COUNTA
L'ispirazione per questo articolo è venuta dall'eccellente libro di Mike Girvin Control + Shift + Enter, dove Mike spiega il concetto di "ultima posizione relativa".
