Come utilizzare un intervallo dinamico in Excel con COUNTIF e INDIRECT

2024 Autore: Abigail Brown | [email protected]. Ultima modifica: 2023-12-17 06:52

Cosa sapere

• La funzione INDIRETTO cambia l'intervallo di riferimenti di cella in una formula senza modificare la formula.
• Usa INDIRETTO come argomento per COUNTIF per creare un intervallo dinamico di celle che soddisfino i criteri specificati.
• I criteri vengono stabiliti dalla funzione INDIRETTO e vengono contate solo le celle che soddisfano i criteri.

Questo articolo spiega come utilizzare la funzione INDIRETTO nelle formule di Excel per modificare l'intervallo di riferimenti di cella utilizzati in una formula senza dover modificare la formula stessa. Ciò garantisce che vengano utilizzate le stesse celle, anche quando il foglio di calcolo cambia. Le informazioni si applicano a Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel per Mac ed Excel Online.

Utilizzare un intervallo dinamico con la formula COUNTIF - INDIRETTO

La funzione INDIRETTO può essere utilizzata con numerose funzioni che accettano un riferimento di cella come argomento, come le funzioni SOMMA e CONTA. SE.

L'utilizzo di INDIRETTO come argomento per COUNTIF crea un intervallo dinamico di riferimenti di cella che possono essere contati dalla funzione se i valori di cella soddisfano un criterio. Lo fa trasformando i dati di testo, a volte indicati come una stringa di testo, in un riferimento di cella.

Questo esempio si basa sui dati mostrati nell'immagine sopra. La formula COUNTIF - INDIRETTO creata nel tutorial è:

=COUNTIF(INDIRETTO(E1&":"&E2), ">10")

In questa formula, l'argomento per la funzione INDIRETTO contiene:

• La cella fa riferimento a E1 e E2, che contengono i dati di testo D1 e D6.
• L'operatore di intervallo, i due punti (:) racchiusi tra virgolette doppie (" ") che trasforma i due punti in un testo stringa.
• Due e commerciali (&) utilizzate per concatenare o unire i due punti con i riferimenti di cella E1 ed E2.

Il risultato è che INDIRETTO converte la stringa di testo D1:D6 in un riferimento di cella e la passa alla funzione COUNTIF da contare se le celle di riferimento sono maggiori di 10.

La funzione INDIRETTO accetta qualsiasi input di testo. Possono essere celle nel foglio di lavoro che contengono testo o riferimenti a celle di testo che vengono immessi direttamente nella funzione.

Modifica dinamicamente l'intervallo della formula

Ricorda, l'obiettivo è creare una formula con una gamma dinamica. È possibile modificare un intervallo dinamico senza modificare la formula stessa.

Modificando i dati di testo situati nelle celle E1 ed E2, da D1 e D6 a D3 e D7, l'intervallo sommato dalla funzione può essere facilmente modificato da D1:D6 a D3:D7. Ciò elimina la necessità di modificare direttamente la formula nella cella G1.

La funzione CONTA. SE in questo esempio conta solo le celle contenenti numeri se sono maggiori di 10. Anche se quattro delle cinque celle nell'intervallo di D1:D6 contengono dati, solo tre celle contengono numeri. Le celle vuote o contenenti dati di testo vengono ignorate dalla funzione.

Conteggio del testo con COUNTIF

La funzione COUNTIF non si limita al conteggio di dati numerici. Conta anche le celle che contengono testo controllando se corrispondono a un determinato testo.

A tale scopo, nella cella G2 viene inserita la seguente formula:

=COUNTIF(INDIRETTO(E1&":"&E2), "due")

In questa formula, la funzione INDIRETTO fa riferimento alle celle da B1 a B6. La funzione COUNTIF somma il numero di celle che contengono il valore di testo due.

In questo caso, il risultato è 1.

COUNTA, COUNTBLANK e INDIRETTO

Altre due funzioni di conteggio di Excel sono COUNTA, che conta le celle contenenti qualsiasi tipo di dati ignorando solo le celle vuote o vuote, e COUNTBLANK, che conta solo le celle vuote o vuote in un intervallo.

Poiché entrambe queste funzioni hanno una sintassi simile alla funzione CONTA. SE, nell'esempio precedente possono essere sostituite con INDIRETTO per creare le seguenti formule:

=COUNTA(INDIRETTO(E1&":"&E2))
=COUNTBLANK(INDIRETTO(E1&":"&E2)
Per l'intervallo D1:D6, COUNTA restituisce una risposta di 4, poiché quattro delle cinque celle contengono dati. COUNTBLANK restituisce una risposta di 1 poiché c'è solo una cella vuota nell'intervallo.

Perché usare una funzione INDIRETTA?
Il vantaggio dell'utilizzo della funzione INDIRETTO in tutte queste formule è che è possibile inserire nuove celle in qualsiasi punto dell'intervallo.
La gamma si sposta dinamicamente all'interno delle varie funzioni e i risultati si aggiornano di conseguenza.





Senza la funzione INDIRETTO, ogni funzione dovrebbe essere modificata per includere tutte e 7 le celle, inclusa quella nuova.
I vantaggi della funzione INDIRETTO sono che i valori di testo possono essere inseriti come riferimenti di cella e che aggiorna dinamicamente gli intervalli ogni volta che il foglio di calcolo cambia.
Ciò rende molto più semplice la manutenzione generale del foglio di calcolo, specialmente per i fogli di calcolo molto grandi.