Rendere "variabili" i riferimenti degli intervalli usati come argomenti nelle funzioni sul foglio di lavoro.

Uso delle funzioni : INDIRETTO() - INDIRIZZO() - RIF.RIGA()

Premessa.

Un recente problemino mi ha complicato un pò l'esistenza: come poter rendere variabili i riferimenti degli intervalli usati come argomenti in molte funzioni, come ad esempio =SOMMA(A1:A50) dove A1 e A50 sono i riferimenti (alle celle), argomenti della funzione,  che formano gli estremi dell'intervallo di cui eseguire la somma.

Visto che in vba, quando si vogliono rendere variabili gli indici di riga o di colonna, si ricorre alla sintassi Cells(indiceriga, indice colonna) dove possiamo sostituire uno o i due indici con altrettante variabili, il passaggio alla corrispondente sintassi, usando una funzione del foglio di lavoro, ci porta ad usare la funzione INDIRIZZO() :

Sintassi della funzione INDIRIZZO():

  • =INDIRIZZO(rif.riga;rif.colonna;ass;a1;foglio) che restituisce un indirizzo di cella in formato testo in base ai numeri di riga e di colonna specificati; i primi due argomenti della funzione sono obbligatori, mentre i rimanenti si possono omettere (rimando il lettore alle spiegazioni sulla funzione nella guida in linea);

usando quindi la funzione con i soli due primi argomenti, potremmo usare per esempio =INDIRIZZO(2;3) e otterremo in restituzione il riferimento alla cella C2 (riga2, colonna3), oppure, se in una cella (la F1 p.e.) abbiamo un valore numerico (8 p.e.) possiamo usare il riferimento F1 come primo argomento, per definire la riga nella funzione INDIRIZZO(), così:  =INDIRIZZO(F1;3) ed otterremo in restituzione il riferimento alla cella C8 ; va da se che se in F1 cambieremo il numero, cambierà anche il riferimento alla cella restituita dalla funzione; se lo stesso meccanismo lo usiamo anche/o con il secondo argomento della funzione, cioè per il rif.colonna, otterremo anche in questo caso la variabilità nei riferimenti usati dalla funzione.

Attenzione: ricordiamoci che il risultato ottenuto dalla funzione (cioè un riferimento cella, quel C2 o C8 degli esempi) è in formato testo, e noi non li vediamo, ma Excel si, è come se C2 o C8 fossero compresi tra due doppi apici, come "C2" o "C8".

Dato che il tema dell'articolo è quello di usare riferimenti variabili come intervalli in altre funzioni, la precisazione è necessaria perchè se noi si usasse ad esempio una formula del genere per sommare l'intervallo che va da A1 a A50 e si scrivesse:

  • =SOMMA(INDIRIZZO(1;1):INDIRIZZO(50;1))  otterremo un bel messaggio errore in quanto Excel leggerebbe l'istruzione come se fosse =SOMMA("A1":"A50"), cioè gli estremi dell'intervallo sono inaccettabili, non riconosciuti come sintassi esatta, in quanto compresi tra doppi apici.

Ed è qui che ci viene in aiuto la Funzione INDIRETTO() che restituisce il riferimento specificato da una stringa di testo. In pratica restituisce il contenuto di una cella o di una funzione usata come argomento della funzione stessa, senza i doppi apici. Vediamo la sua sintassi:

  • INDIRETTO(rif;a1) - due sono gli argomenti di questa funzione, il primo è obbligatorio mentre il secondo è facoltativo: se a1 è VERO o è omesso, rif verrà interpretato come un riferimento di tipo A1. Se in una cella (la D5 p.e.) abbiamo il numero 26, la formula =INDIRETTO(D5) restituirà 26, se in H2 abbiamo scritto E25 (quindi un riferimento ad una cella scritto come testo) la formula =INDIRETTO(H2) restituirà E25 SENZA doppi apici.

Soluzione: dovremo unire quindi le due funzioni spiegate sopra per ottenere la variabilità negli intervalli, così:

  • =SOMMA(INDIRETTO(INDIRIZZO(1;1)):INDIRETTO(INDIRIZZO(50;1))) ecco quindi che ora excel leggerà l'istruzione come =SOMMA(A1:A50).

Ricordo che gli argomenti della funzione INDIRIZZO() possono essere valori (numeri) , oppure funzioni che restituiscano valori. Altra cosa da precisare è che a differenza di altre funzioni dove se si usa il "trascinamento" della formula otteniamo l'aggiornamento automatico dei riferimenti, nel caso della funzione INDIRIZZO NON otteniamo nessun aggiornamento.

Passiamo agli esempi, che forse chiariranno meglio come usare la variabilità degli intervalli:

Supponiamo di avere una tabella come questa sotto:

  A B C D E
1

1520

0

     
2

280

0

     
3

1000

0

     
4

750

3

     
5

12

0

     
6

2500

0

     
7

87

0

     
8

980

5

     
9

550

0

     
10

1800

0

     

Nella colonna A abbiamo dei valori; nella colonna B abbiamo degli indicatori rappresentati da numeri; desideriamo ottenere il valore MINIMO (Funzione =MIN()) tra i valori della colonna A, compresi tra la riga dove si trova ogni indicatore nella colonna B, e tante righe sopra quante rappresentate dal valore dell'indicatore stesso: quindi ad esempio, sulla riga 4, in B, troviamo il valore 3: vorremo il valore minimo nell'intervallo A4 -3 = A1 ovverosia A1:A4. il risultato della funzione =MIN() o vogliamo stessa riga del valore letto in B (quindi riga 4) della colonna C, un risultato così:

  A B C D E
1

1520

0

 

   
2

280

0

 

   
3

1000

0

 

   
4

750

3

280

   
5

12

0

 

   
6

2500

0

 

   
7

87

0

 

   
8

980

5

12

   
9

550

0

 

   
10

1800

0

 

   

dove 280 è il valore minimo compreso tra A1:A4 e 12 è il valore minimo compreso tra A8:A3 (A8 - 5 (valore in B8) = A3).

In una situazione del genere, abbiamo bisogno di conoscere su quale riga, nella colonna B, si trova un valore diverso da zero (0), e possiamo quindi usare la Funzione

  • = RIF.RIGA(rif)  che restituisce il numero di riga indicato nell'argomento, e in mancanza dell'argomento, verrà restituita la riga su cui la funzione risiede; esempio: se noi posizioniamo la funzione nella cella C4 e scriviamo =RIF.RIGA(C8) otterremo come risultato 8 perchè è la riga dell'argomento C8, se invece, sempre in C4 scriviamo =RIF.RIGA()  senza scrivere il riferimento, otteniamo 4 in quanto la funzione risiede nella cella C, riga 4. Chiaro, no?

Ma come la "montiamo" questa ulteriore funzione? intanto la useremo come primo argomento ("variabile") della seconda funzione INDIRIZZO, quella che definisce la fine dell'intervallo. Ricordate che abbiamo detto sopra che come argomenti di questa funzione possiamo usare numeri oppure funzioni che restituiscano numeri? quindi la nostra formula per ora la vedremo così: evidenzio in grassetto solo la parte con la nuova funzione

  • =MIN(INDIRETTO(INDIRIZZO(1;1)):INDIRETTO(INDIRIZZO(RIF.RIGA();1)))  - se questa formula la inseriamo in C4, dove vogliamo sia restituita la funzione MIN(), il valore restituito dalla funzione RIF.CELLA() sarà 4 e quindi e come se si chiedesse il minimo tra i valori che sono nell'intervallo A1:A4.

Andiamo avanti: perchè abbiamo dovuto creare un riferimento variabile ? perchè ponendo le formule per ottenere il MINIMO nella colonna C, partendo dalla cella C1 e "trascinando" non sapremo su quale riga troveremo un valore diverso da zero, e la funzione RIF.CELLA() ci garantisce di ottenere la riga sulla quale si trova detto valore.

Ma la stessa variabilità la dovremo creare anche per il primo dei riferimenti dell'intervallo su cui cercare il MINIMO; infatti questo riferimento è in stretta dipendenza dal numero trovato nella colonna B; infatti vogliamo il MINIMO dei valori in A, nell'intervallo compreso tra la riga dove compare una valore diverso da zero, e tante righe indietro quante rappresentate dal valore stesso.

Basta quindi sottrarre dal valore ottenuto con la funzione RIF.RIGA() il valore stesso della cella in questione; quindi la formula posta in C4, sarà: (in grassetto la modifica)

  • =MIN(INDIRETTO(INDIRIZZO(RIF.RIGA()-B4;1)):INDIRETTO(INDIRIZZO(RIF.RIGA();1)) - infatti RIF.RIGA() restituisce 4, a cui si sottrae il valore che è in B4 , che è 3, ed otteniamo 1, ovverosia corrisponde ad avere scritto INDIRIZZO(1;1) cioè A1.

A questo punto completiamo la formula che inseriremo a inizio cella C1 con la funzione =SE() : se la cella B1 è maggiore di 0, applichiamo la funzione MIN() altrimenti lasciamo C1 vuota (""). Poi "trasciniamo" la formula per quanto sarà la nostra necessità,  ed excel provvederà ad aggiornare il riferimento B1, in B2, B3, B4 ecc. ecc..

Prima Formula definitiva:

  • =SE(B1>0;MIN(INDIRETTO(INDIRIZZO(RIF.RIGA()-B1;1)):INDIRETTO(INDIRIZZO(RIF.RIGA();1)));"")

Unico accorgimento: se il primo valore diverso da zero che si trova nella colonna B, è un valore maggiore rispetto alla differenza righe tra la riga del valore stesso e l'inizio righe, per capirci : se nella riga 4 vista nella tabella sopra, anzichè 3, avessimo 5, si genererebbe un errore perchè riga 4 meno 5 restituisce una riga inesistente sul foglio (-1), come pure si genera l'errore se il numero fosse uguale al numero di riga, infatti la sottrazione prevista con RIF.RIGA() - il valore nella cella, in questo caso restituisce 0, e non esiste una riga 0 sul foglio di lavoro.

Dovremo quindi prevedere una condizione del genere (usando un'altra funzione =SE()) : se il valore nella cella è maggiore o uguale del RIF.RIGA(), facciamo restituire 1, che corrisponderebbe alla riga 1 (non scordiamoci che stiamo definendo il primo argomento della funzione INDIRIZZO(primo argom; secondo argom), altrimenti facciamo restituire la differenza tra il RIF.RIGA() ed il valore contenuto nella cella; questa la formula finale (in verde le istruzioni aggiunte):

  • =SE(B1>0;MIN(INDIRETTO(INDIRIZZO(SE(B1>=RIF.RIGA();1;RIF.RIGA()-B1);1)):INDIRETTO(INDIRIZZO(RIF.RIGA();1)));"")

Ricapitolando: INDIRETTO ci serve per ottenere un riferimento valido da usare come riferimento di intervallo, riferimento ottenuto tramite INDIRIZZO, il quale chiede di indicare un numero di riga e un numero di colonna, entrambi fornibili o come numero intero, o tramite una funzione che restituisca un numero intero.

Morale: accidentaccio alle formule chilometriche (e quest'ultima non è neanche tanto lunga), io preferisco il vba.

 

Comunque: buon lavoro.

prelevato sul sito www.ennius.altervista.org