La funzione INDIRETTO è una delle funzioni che si possono utilizzare nel momento in cui si devono creare report in Excel e gestire collegamenti tra fogli in modo sicuro ed efficiente. Insieme alla funzione INDIRETTO è spesso affiancato un set di funzioni quali INDIRIZZO, RIF.RIGA e RIF.COLONNA. Vediamo con un esempio come utilizzare questo interessante mix di funzioni.
CREARE REPORT IN EXCEL CON UN MIX DELLLE FUNZIONI INDIRETTO, INDIRIZZO, RIF.RIGA E RIF.COLONNA
Negli ultimi anni molte più persone si trovano nella situazione di dover gestire fogli dicalcolo complessi e di dover trasformare i dati in informazioni. Scopo di questo tutorial è quello di accompagnare gli utenti già esperti di Excel alla scoperta di alcune funzioni che “mischiate” tra di loro possono riusltare molto utili nella generazione di fogli di sintesi. Dato che utilizzeremo tutte le funzioni citate scopriamone insieme la sintassi ed il loro utilizzo, prima prese singolarmente e poi in un mix.
FUNZIONE RIF.RIGA
La funzione RIF.RIGA restituisce il numero di riga di un particolare riferimento a cella. Per esempio scriviamo la seguente formula:
=RIF.RIGA(B3)
Il risultato che la funzione produce è 3 poiché il numero di riga del valore della cella B3
FUNZIONE RIF.COLONNA
La funzione RIF.COLONNA restituisce il numero di colonna di un particolare riferimento a cella. Per esempio scriviamo la seguente formula:
=RIF.COLONNA(B3)
Il risultato che la funzione produce è 2 poiché il numero di colonna del valore della cella B3
Se nell’utilizzo della funzione RIF.RIGA ci si può aspettare un risultato sottoforma di numero, quello che può suscitare una poca di sorpresa è il risultato della funzione RIF.COLONNA. Dobbiamo precisare infatti che la funzione non restituisce il valore classico (una lettera) delle colonne, ma un numero. La colonna B diventa in questo senso il numero 2; la colonna C diventa il numero 3 e così via.
Questa caratteristica è particolarmente utile nel momento in cui si vada ad utilizzare la funzione INDIRIZZO.
FUNZIONE INDIRIZZO
Vediamo per prima cosa la sintassi della funzione INDIRIZZO, che è la seguente:
INDIRIZZO(riga; col; [ass]; [a1]; [foglio])
Sappiamo che la sintassi di una funzione mette tra parentesi quadre gli argomenti la cui specifica è opzionale da parte dell’utente. e per i quali, comunque, non è obbligatorio l’inserimento di un valore che, in caso di omissione, viene impostato su un parametro di default da Excel. Guardando la sintassi della funzione INDIRIZZO si comprende come sia indispensabili soltanto due valori: il riferimento di riga ed il riferimento di colonna.
Se scrivessimo la funzione =INDIRIZZO(3;4) il suo effetto sarebbe quello di restituire la coordinata $D$3 in una forma di stringa utilizzabile all’interno della funzione INDIRETTO.
La funzione INDIRIZZO genera una stringa di testo che corrisponde ad un indirizzo di cella in modalità riferimento assoluto.
Ma perché proprio riferimento assoluto? Perché se non viene specificato un terzo parametro della funzione INDIRIZZO, il riferimento testuale generato è ASSOLUTO. Il terzo parametro della funzione INDIRIZZO, quello che nella sintassi della formula troviamo come [ass], è quindi personalizzabile in base alla seguente legenda presa direttamente dal sito Microsoft
| [ass] | Tipo di riferimento restituito |
| 1 od omesso | Assoluto |
| 2 | Assoluto (riga), Relativo (colonna) |
| 3 | Relativo (riga), Assoluto (colonna) |
| 4 | Relativo |
Se scrivessimo, quindi =INDIRIZZO(3;2;4) il risultato sarebbe una stringa di testo rappresentante un indirizzo relativo. Si avrebbe infatti come risultato il testo B3. Questa volta, il riferimento sarebbe relativo e non assoluto.
Ma cosa accade se desideriamo generare una stringa di testo che abbia come riferimento una determinata cella di un determinato foglio? Sappiamo bene che i riferimenti ad una cella, se non contengono la specifica di riferimento ad un foglio in particolare, vengono sempre assunti come riferiti al foglio in cui nascono. La risposta la si trova nel quinto parametro della funzione INDIRIZZO, quello indicato da [foglio] con il quale si ha la possibilità di specificare il nome del foglio a cui il riferimento deve puntare. In base a quanto espresso, se scrivessimo la funzione =INDIRIZZO(3;2;4;;”PIPPO”) il risultato sarebbe il seguente testo: PIPPO!B3.
Vi chiederete cosa ne sia del quarto parametro della funzione INDIRIZZO di cui non abbiamo parlato, quello che nella sintassi è indicato come [a1]. Beh… è forse il più strano perché permette di cambiare il sistema di riferimento alle celle da standard a R1C1. In tale sistema di riferimento non esistono lettere, ma solo numeri: un numero identifica la riga e l’altro identifica la colonna. Affronteremo questo topic in un apposito articolo. In questo esempio daremo per scontato l’utilizzo del tradizionale sistema di riferimento alle celle che prevede l’utilizzo delle lettere per le colonne e dei numeri per le righe.
Nella funzione =INDIRIZZO(3;2;4;;”PIPPO”) il quarto parametro è intenzionalmente lasciato vuoto perché in assenza di una specifica viene generato un riferimento standard.
FUNZIONE INDIRETTO
Vediamo adesso un caso pratico utilizzato per creare report in Excel:
Creare report con funzione INDIRETTO
Si tratta di un piccolo prospetto in cui vengono elencate le ore lavorate in une mese per quattro reparti diversi. La nostra esigenza è quella di generare un prospetto sintetico tipo quello presente nell’intervallo G1:I5. Una simile attività può sembrare molto più facile di quello che invece è se lo si deve fare con delle formule e non tramite dei manuali copia incolla. Il copia incolla non porta a nessun risultato se i reparti fossero ad esempio una cinquantina…
Partiamo dalla cella F2 e vediamo quale sia la formula inserita:
=INDIRETTO(INDIRIZZO(RIF.RIGA($B$1)+6*(RIF.RIGA(B1)-RIF.RIGA($B$1));RIF.COLONNA(B1)))
Come si può notare, la complicazione è nel riferimento di riga che deve aumentare di sei ad ogni passaggio da F2 a F3 a F4 e fino a F5. È stato utilizzato un piccolo espediente che permette di generare con semplicità dei multipli di 6 ad ogni passaggio tra una riga e l’altra. La formula deve essere ricopiata in verticale.
Vediamo adesso la formula che inseriremo in G1
=INDIRETTO(INDIRIZZO(RIF.RIGA(B3)+1*(RIF.COLONNA(B3)-RIF.COLONNA($B$3));RIF.COLONNA($B$3)))
Questa volta la difficoltà è dovuta al fatto che la formula deve essere copiata in orizzontale e, per contro, i riferimenti devono scorrere in verticale. In genere non si è molto abituati a “tirare” una formula verso destra ed ottenere un incremento degli indici di riga e non quelli di colonna. Per ottenere questo “strampalato” risultato si è usata una formula simile alla precedente che usa gli scostamenti sull’asse orizzontale per generare degli incrementi di riga e cioè verticali. Ricopiando la formula verso destra, e cioè da G1 a H1 e fino ad H1 gli unici riferimenti che scorrono sono quelli di riga.
Vediamo adesso la formula che inseriamo in G2
=INDIRETTO(INDIRIZZO(RIF.RIGA($D$3)+6*(RIF.RIGA(D3)-RIF.RIGA($D$3));RIF.COLONNA($D3)))
Questa formula è del tutto simile a quella contenuta in F2 poiché deve sfruttare gli stessi principi. Si ricorda che questa formula andrà ricopiata in verticale.
Vediamo adesso la formula che inseriamo in H2
=INDIRETTO(INDIRIZZO(RIF.RIGA($D$4)+6*(RIF.RIGA(E4)-RIF.RIGA($D$4));RIF.COLONNA($D4)))
Questa formula è del tutto simile a quella contenuta in F2 poiché deve sfruttare gli stessi principi. Si ricorda che questa formula andrà ricopiata in verticale.
Vediamo adesso la formula che inseriamo in I2
=INDIRETTO(INDIRIZZO(RIF.RIGA($D$5)+6*(RIF.RIGA(F5)-RIF.RIGA($D$5));RIF.COLONNA($D5)))
Questa formula è del tutto simile a quella contenuta in F2 poiché deve sfruttare gli stessi principi. Si ricorda che questa formula andrà ricopiata in verticale.
Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna F:
Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna G:
Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna H:
Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna I:
I risultati generati per creare report in Excel saranno i seguenti:
Utilizzando in modo leggermente diverso le funzioni, saremmo stati in grado di produrre gli stessi risultati, ma su di un foglio apposito, in modo da separare i dati di partenza dal report finale.
Al termine corso di Excel Avanzato a Firenze che si svolge presso Mummu Academy saprai creare report in Excel di questa tipologia di report e molti altri.
Solo gli studenti di Mummu Academy dispongono della piattaforma di studio riservata ai nostri studenti.
