Excel Avanzato - Dispensa 1: Analisi Funzioni

EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
ANALISI DI SPECIFICHE FUNZIONI
INTEGRAZIONE E NIDIFICAZIONE DELLE FUNZIONI.............................................................. 2
UTILIZZO DELLE MATRICI NELLE FUNZIONI ......................................................................... 2
ANALISI DI ALCUNE FUNZIONI DI RICERCA .......................................................................... 7
INDICE ...................................................................................................................................... 7
CONFRONTA ............................................................................................................................. 9
INDICE E CONFRONTA ............................................................................................................. 11
SCARTO .................................................................................................................................. 11
INDIRIZZO ............................................................................................................................... 13
INDIRETTO .............................................................................................................................. 14
INDIRIZZO ED INDIRETTO ........................................................................................................ 16
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
INTEGRAZIONE E NIDIFICAZIONE DELLE FUNZIONI
L’ampia disponibilità di funzioni è in grado di rispondere alla maggior parte dei quesiti di un utente di Excel.
Tuttavia, la potenza massima delle funzioni si sprigiona quando una funzione viene usata come argomento di
un’altra funzione per ragioni che possono essere intrinseche alla stesa funzione usata (ad esempio il SE nidificato) o
dinamiche (quando l’argomento di una funzione deve essere non un valore digitato a mano o fisso ma dinamico
ovvero in divenire) o di praticità.
In questo modo si entra nel processo mentale corretto ed in cui si utilizza al massimo grado la potenzialità di
Excel prima di ricorrere al grado più elevato rappresentato dal codice Vba: conoscere le funzioni per poterle
integrare tra di loro quando necessario e, quindi, nidificare una funzione dentro un’altra funzione. Inoltre, la
conoscenza approfondita delle funzioni permette anche di utilizzarle per amplificare al massimo le funzionalità di
comandi quali la convalida dati o la formattazione condizionale. In questi campi l’utente Excel si può sbizzarrire!
Il panorama di questa argomentazione ed i campi possibili di applicazione sono molto vasti. In questo paragrafo
l’obiettivo è semplicemente quello di stimolare ad un utilizzo integrato delle funzioni, mostrando qualche esempio
possibile. Altri esempi si possono trovare nei paragrafi dedicati alle funzioni di ricerca.
SE – E – O
CARATTEI JOLLY
OK UTILIZZO DELLE MATRICI NELLE FUNZIONI
L’altra grande potenzialità delle funzioni si esprime nelle cosiddette matrici o funzioni matriciali. Il concetto di
matrice significa ter cose in Excel:

Applicare in un colpo solo una formula a tutte le celle risultato e creare una matrice corrispondente ad
una colonna calcolata di una tabella di database.

Usare direttamente funzioni che si applicano ad una matrice.

Far accettare ad Excel come argomento di una funzione una matrice di dati (es. un intervallo celle) nelle
funzioni che non prevedono matrici di dati come argomento diretto.
Partiamo dal primo campo di utilizzo. Supponiamo di avere un prospetto che presenti 3 colonne con il budget
erogato mensilmente a ciascun settore aziendale, i soldi effettivamente spesi ed una colonna in cui va messa la
differenza tra il budget stanziato e le spese effettive. L’elenco presenta oltre 30.000 righe. Se l’intervallo è
formattato come tabella si può ricorrere alla creazione automatica della colonna calcolata. Se rimane un normale
elenco dati, per evitare di immettere una formula o funzione in una cella ed estenderla alle altre e per evitare il
rischio di poter modificare la funzione nelle singole celle, occorre selezionare in un colpo solo
All’atto pratico, se, ad esempio, i budget si trovano nell’intervallo celle C2:C30000 e le spese effettiva
nell’intervallo celle D2:D30000, nelle celle E2:E30000 preventivamente selezionate si opera come segue:
~2~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Digitare il segno di = che introduce le formule
Selezionare tutto l’intervallo di celle C2:C30000
Digitare il segno – per la sottrazione
Selezionare tutto l’intervallo di celle D2:D30000
Premere CTRL – SHIFT ed INVIO/ENTER per confermare l’immissione e creare la matrice
Excel aggiunge una parentesi graffa di apertura e chiusura per cui la formula risulta così digitata: {=C2:C30000D2:D30000}. Tutte le celle presentano la stessa formula e non è possibile modificare la formula in una singola cella:
Excel ci informa che non è possibile modificare solo la parte di una matrice. Per apportare la modifiche occorre
premere di nuovo CTRL CTRL – SHIFT ed INVIO/ENTER ed in questo modo si modificano le celle di tutta la colonna (di
qui il concetto di colonna calcolata). Non è nemmeno possibile eliminare righe dell’elenco dati in quanto è presente
una colonna che fa parte di una matrice.
La procedura può risultare ma ci sono casi in cui può tornare utile per ragioni di sicurezza (colonna calcolata) ed
anche per evitare di appesantire le dimensioni del file con tante formule diverse per ogni cella (nel nostro esempio
immettiamo 1 sola formula anziché 30000 formule diverse per ogni cella).
Un altro esempio semplice ma efficace: si ha una tabella dati a due variabili da riempire per conoscere il
risultato della formula che in base alla prima variabile (quantità acquistate) ed alla seconda variabile (prezzo
unitario) ed ad valore fisso di sconto (12%) restituisca in un colpo solo il prezzo finale scontato per ciascuna delle
combinazioni delle due variabili. La soluzione può essere la tabella dati, descritta in un’altra dispensa, ma per un
prospetto semplice come quello raffigurato
~3~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Come si può osservare nella Barra della formula, la funzione è la seguente :{=(B3:B15*C2:J2)(B3:B15*C2:J2*$K$2)}. La procedura è la seguente:
Selezionare tutte le celle risultato C3:J15
Digitare il segno di = che introduce le formule ed aprire la parentesi tonda.
Selezionare tutto l’intervallo di celle B3:B15 relativo alla variabile delle quantità
Digitare il segno * per la moltiplicazione
Selezionare tutto l’intervallo di celle C2:J2 relativo alla variabile dei prezzi unitari e chiudere la parentesi
tonda
Digitare il segno - per la sottrazione
Aprire la parentesi tonda e digitare di nuovo di nuovo tutta l’operazione B3:B15*C2:J2 (anche con il
copia ed incolla della precedente immissione)
Digitare il segno * per la moltiplicazione
Selezionare la cella K2 con lo sconto e bloccare il riferimento di cella affinché rimanga fisso in tutte le
formule. Chiudere la parentesi tonda.
Premere CTRL – SHIFT ed INVIO/ENTER per confermare l’immissione e creare la matrice
Un secondo campo di applicazione del concetto di matrice prevede l’utilizzo delle funzioni di matrice presenti
nella categoria Matematiche e trigonometriche. Le funzioni sono 4, ma quella che ci si potrebbe trovare ad utilizzare
più spesso è la funzione MATR.SOMMA.PRODOTTO. Il suo scopo è moltiplicare in un'unica funzione i valori presenti
in una colonna con quelli presenti in un’altra colonna è sommare i valori derivati dalle varie moltiplicazioni. Si prenda
l’esempio raffigurato dall’immagine seguente.
~4~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Per ciascuno dei 15 prodotti è indicata la quantità che è stata ordinata (colonna B) ed il prezzo unitario di un
prodotto (colonna C). Per conoscere l’ammontare complessivo dell’ordine in termini di prezzo, la soluzione normale
è inserire una nuova colonna (in questo caso la colonna D) in cui fare il calcolo delle quantità per il prezzo unitario di
ciascun prodotto (B2*C2 poi B3*C3 e via dicendo) e poi inserire sotto la riga dell’ultimo prodotto la funzione che
somma i prezzi complessivi di tuti i prodotti ovvero, nel nostro caso, =SOMMA(D2:D16) come mostrato
nell’immagine sotto.
Si può anche evitare di inserire una nuova colonna ed avere in un colpo solo il valore finale grazie alla funzione
MATR.SOMMA.PRODOTTO, la cui sintassi è MATR.SOMMA.PRODOTTO(matrice1; [matrice2]; [matrice3]; ...).
L’argomento unico è l’indicazione della matrice (o delle matrici se più di una) che contiene i valori da sottoporre a
calcolo: Excel provvede a fare le singole moltiplicazioni dei singoli prodotti per i corrispondenti prezzi unitari ed a
sommare i prodotti delle varie moltiplicazioni. Nel caso specifico la funzione da inserire nella cella desiderata è
=MATR.SOMMA.PRODOTTO(B2:B16;C2:C16).
Il terzo utilizzo delle matrici è quello più utile e più stimolante perché consente di risolvere quei numerosi casi in
cui le funzioni di Excel non accettano una matrice come argomento. Il caso più palese è la funzione SE utilizzata con
le funzioni E ed O. Un esempio chiarificatore abbastanza semplice, perché il problema è solo su una colonna.
Supponiamo di avere un file in cui siano segnalate delle pratiche di reclamo. Ci sono due colonne: la colonna A
“Delegato” che a volte contiene nomi a volte è vuota e la colonna B “Associazione” che funziona come la colonna
Delegato. Nella colonna C “Segnalazione pratica” occorre digitare la fonte da cui proviene la segnalazione della
pratica secondo le regole seguenti:
~5~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Se c’è un valore nella colonna “Delegato” e nessun valore nella colonna “Associazione” la segnalazione
della pratica proviene da un delegato.
Se sia la colonna “Delegato” che la colonna “Associazione” sono vuote, la segnalazione della pratica
proviene da un cliente.
Se la colonna “Associazione” presenta un valore, a prescindere se la colonna “Delegato” sia piena o
vuota, e questo valore è il nome di una delle associazioni presenti nel foglio di lavoro “Elenco Cncu”
allora la segnalazione della pratica proviene dal CNCU.
Se nella colonna “Associazione” c’è un valore diverso da quelli dell’”Elenco Cncu” la segnalazione della
pratica proviene da PMI.
Le pratiche sono più di 1200 e quindi è da escludere sia un lavoro manuale riga per riga che un lavoro applicato
su un filtro, che richiederebbero un dispendio eccesivo di tempo. Occorre la funzione SE nidificata.
Per le pratiche dei delegati è sufficiente nidificare una funzione E nella funzione SE che verifichi che la
colonna “Delegato” sia piena ovvero diversa da vuota e la colonna “Associazione” sia vuota:
E(A2<>””;B2=””).
~6~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Per le pratiche segnalate direttamente dal cliente è sufficiente verificare che entrambe le colonne siano
vuote: E(A2=””;B2=””).
Il problema è posto dalle pratiche Cncu. Innanzitutto occorre una funzione che restituisca vero se anche
una sola condizione sia vera e cioè il nome dell’associazione, ove presente nella colonna B, sia uno di
quelli presenti nell’Elenco Cncu. Questa è la funzione O. La funzione O non ammette matrici come
argomento per cui occorre fare il controllo associazione per associazione. In altre parole, la sintassi è:
O(B2=ElencoCncu!A2;B2=ElencoCncuA3…) e cosi facendo fino all’ultima associazione dell’elenco Cncu.
La cosa risulta abbastanza noiosa. Inutile affermare che se potessimo dire ad Excel di verificare non con
le associazioni una per una ma con tutto l’elenco l’operazione risulterebbe più rapida.
La soluzione alla questione dell’elenco sta nell’includere l’intera matrice delle associazioni Cncu nella funzione e,
per fare accettare la matrice come argomento, premere CTRL + SHIFT + INVIO/ENTER. L’argomento risulta
O(B2=ElencoCncu!A2:A15) mentre la funzione finale, inserita come matrice, è la seguente:
{=SE(E(A2<>””;B2=””);”Delegato”;SE(E(A2=””;B2=””);”Cliente”;SE(O(B2=ElencoCncuA2:A15);”Cncu”;”Pmi”}
ANALISI DI ALCUNE FUNZIONI DI RICERCA
INDICE
Descrizione
La funzione INDICE restituisce un valore o il riferimento a un valore da una tabella o un intervallo. In altre
parole, restituisce un valore che, in una matrice oppure in un intervallo di celle, si trova in una determinata cella
ovvero in una determinata riga e colonna. In base alla disposizione dei valori, può essere usata in due modi ovvero
con una matrice o con un riferimento.
Usare la forma per matrice se il primo argomento della funzione INDICE è una costante di matrice
Usare la forma per riferimento per restituire il riferimento della cella all'intersezione di una particolare
riga e colonna
Sintassi
INDICE(matrice; riga; [col])
Matrice. Obbligatorio. Intervallo di celle o costante di matrice.
Se una matrice contiene una sola riga o colonna, l'argomento Riga o Col corrispondente è facoltativo.
Se una matrice contiene più righe e colonne e si utilizza solo Riga o solo Col, INDICE restituisce una
matrice dell'intera riga o colonna in matrice.
Riga Obbligatorio. Seleziona la riga nella matrice da cui restituire un valore. Se Riga viene omesso, Col
è necessario.
Col Facoltativo. Seleziona la colonna nella matrice da cui restituire un valore. Se Col viene omesso,
Riga è necessario.
~7~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
INDICE(rif; riga; [col]; [area])
Rif Obbligatorio. Riferimento a uno o più intervalli di celle.
Se si immette un intervallo non adiacente per rif, racchiuderlo tra parentesi.
Se ogni area in rif contiene solo una riga o una colonna, l'argomento Riga o Col corrispondente sarà
facoltativo. Ad esempio, per un riferimento a una singola riga, utilizzare INDICE(rif;;col).
Riga Obbligatorio. Numero della riga in rif dal quale si desidera restituire un riferimento.
Col Facoltativo. Numero della colonna in rif dal quale si desidera restituire un riferimento.
Area Facoltativo. Seleziona un intervallo in rif dal quale restituire l'intersezione di Riga e di Col. La
prima area selezionata o immessa viene contrassegnata dal numero 1, la seconda dal numero 2 e così
via. Se Area viene omesso, INDICE utilizzerà l'area 1.
Osservazioni
Se gli argomenti Riga e Col vengono utilizzati entrambi, INDICE restituisce il valore nella cella
all'intersezione di Riga e Col.
Se Riga o Col viene impostato sul valore 0, INDICE restituirà il riferimento rispettivamente dell'intera
colonna o dell'intera riga. Per usare i valori restituiti come matrice, immettere la funzione INDICE come
formula di matrice in un intervallo orizzontale di celle per una riga e in un intervallo verticale di celle per
una colonna. Per immettere una formula in forma di matrice, premere CTRL+MAIUSC+INVIO.
È necessario che Riga e Col facciano riferimento a una cella all'interno della matrice o del riferimento,
altrimenti INDICE restituirà il valore di errore #RIF!.
Esempio
Nella cella I3 è stata immessa la formula che appare anche sull’immagine ovvero =INDICE(B3:G12;4;6). Il
risultato è 62 perché a partire dalla cella B3 (nell’intervallo che arriva fino a B12) restituisce il valore che si trova nella
cella G6 e cioè nella quarta riga (partendo dalla riga 3 ovvero la riga 6) e la sesta colonna (partendo dalla colonna B e
quindi la colonna G).
~8~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Ovviamente, se i numeri relativi gli argomenti Riga e Colonna devono essere digitati a mano dall’utente, la
funzione risulta poco efficace ed abbastanza inutile. Diventa, tuttavia, molto potente quando i numeri di riga e
colonna da inserire come argomenti risultano da altre funzioni e quindi sono dinamici. In particolare, l’uso
combinato con la funzione CONFRONTA fornisce ottimi risultati. Sull’argomento si torna più avanti.
CONFRONTA
Descrizione
La funzione CONFRONTA cerca un determinato elemento in un intervallo e restituisce la posizione relativa di
tale elemento nell'intervallo. Se anche il primo elemento si trova in un intervallo, di fatto la funzione confronta i due
intervalli e restituisce errore se nel primo intervallo non trova il valore presente nel secondo intervallo. Se invece i
valori si trovano in entrambe gli intervalli, specifica il numero della riga in cui si trova nel primo intervallo. Ad
esempio, se l'intervallo A1:A3 include i valori 5, 25 e 38, la formula =CONFRONTA(25;A1:A3;0) restituisce il numero 2
perché 25 è il secondo elemento dell'intervallo.
Sintassi
CONFRONTA(valore; matrice; [corrisp])
valore Obbligatorio. Valore del quale trovare il corrispondente in matrice. Se ad esempio si ricerca il
numero di telefono di una persona in una rubrica, si utilizza il nome della persona come valore di
ricerca, ma il valore desiderato è il numero di telefono. L'argomento valore può essere un valore
numerico, di testo o logico oppure un riferimento di cella a un numero, a testo o a un valore logico.
matrice Obbligatorio. Intervallo di celle all'interno del quale eseguire la ricerca.
corrisp Facoltativo. Numero -1, 0 o 1. L'argomento corrisp specifica il modo in cui Excel deve
confrontare valore con i valori contenuti in matrice. Il valore predefinito è 1.
Nella tabella seguente viene illustrato il modo il cui la funzione trova valori in base all'impostazione
dell'argomento corrisp.
Corrisp
Comportamento
1
CONFRONTA troverà il valore più grande che è minore o uguale a valore. È necessario che i valori
oppure
omesso
nell'argomentomatrice siano disposti in ordine crescente, ad esempio ...-2; -1; 0; 1; 2;...A-Z;
FALSO; VERO.
0
CONFRONTA troverà il primo valore che corrisponde esattamente a valore. I valori
nell'argomento matrice possono essere disposti in qualsiasi ordine.
-1
CONFRONTA troverà il valore più piccolo che è maggiore o uguale a valore. È necessario che i
valori dell'argomento matrice siano disposti in ordine decrescente, ad esempio VERO; FALSO; ZA;...2; 1; 0; -1; -2;... e così via.
Osservazioni
~9~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
La funzione CONFRONTA non rileva la differenza tra maiuscole e minuscole durante il confronto tra
valori di testo.
Se CONFRONTA non riesce a trovare un valore corrispondente, restituisce il valore di errore #N/D.
Se corrisp è uguale a 0 e valore è una stringa di testo, nell'argomento valore è possibile usare i caratteri
jolly, ovvero il punto interrogativo (?) e l'asterisco (*) per indicare rispettivamente un carattere singolo e
una sequenza di caratteri qualsiasi. Se si desidera trovare un punto interrogativo o un asterisco, digitare
una tilde (~) prima del carattere.
Esempio
Nella cella B1 è stata immessa la formula che appare anche a video ovvero =CONFRONTA(A1:Foglio3!A:A;0). Il
risultato è 11 perché il Prodotto 1 è presente nella riga 11 della colonna A nel Folio deononinato “Foglio3”, in cui si
trova un altro elenco di prodotti. L’errore generato dal Prodotto 2 deriva dal fatto che nell’elenco che ssta sulla
colonna A del Foglio 2 non c’è il Prodotto 2.
Un utilizzo molto utile della funzione CONFRONTA è quando la si nidifica come argomento “indice” della
funzione CERCA.VERT. Supponiamo che la matrice tabella del Cerca.Vert sia molto vasta e che la colonna dove si
trova il valore da restituire sia sulla diciottesima colonna. Dovremo usare il numero 18 nel Cerca.Vert dopo aver in
qualche modo contato le colonne. O supponiamo un caso ancora più complesso: usiamo il numero 18 digitato
manualmente ma poi viene aggiunta una nuova colonna prima della diciottesima, per cui il numero di colonna da
usare nel Cerca.Vert divante il 19 e dobbiamo andare a modificare le funzioni.
Il modo migliore e meno rischioso di immettere il numero di colonna nell’argomento Indice nel Cerca.Vert ed
avere, quindi, una colonna dinamica è usare la funzione CONFRONTA, come illustrato nell’esempio sotto. Viene
mostrata una tabella di 9 colonne che contiene un’anagrafica di Comuni italiani. Sono visibili solo le primissime righe
di un file che è costituito da 8093 righe (8092 Comuni più la riga di intestazione).
~ 10 ~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
In un’altra area del foglio di lavoro, c’è un prospetto come quello sotto illustrato: appena si sceglie un Comune
nel menu a tendina della cella K3, nelle celle da L3 ad O3 devono comparire il Cap, Il Codice Fisco, gli abitanti ed il
codice Istat del Comune presente nella cella C3. Per il Cap la funzione è stata immessa ed è quella visibile anche
nell’immagine: =CERCA.VERT(K3;B1:I8093;CONFRONTA(L2;B1:I1;0);0).
Concentriamo l’attenzione sulla funzione CONFRONTA che cerca la parola presente nella cella L2 (CAP) nella
riga che va da B1 ad I1 ed il tipo di ricerca deve essere una corrispondenza esatta. La parola Cap si trova nella cella
H1 che è la settima a partire da B1, per cui il risultato della funzione CONFRONTA è il numero 7. In questo modo può
essere usata come argomento dinamico del CERCA.VERT: infatti, se si inserisce una nuova colonna davanti alla
colonna CAP, automaticamente la funzione CONFRONTA dà come risultato 8 perché la parola Cap si sposta di una
colonna.
INDICE E CONFRONTA
Come visto, la funzione CONFRONTA può avere una sua utilità da sola oppure nidificata all’interno della
funzione CERCA.VERT mentre la funzione INDICE da sola non ha una grande utilità. L’uso combinato di queste due
funzioni garantisce, invece, risultati molto interessanti, soprattutto nel porre rimedio ad un duplice limite della
funzione CERCA.VERT: la colonna di ricerca della matrice tabelle deve per forza essere la prima e la colonna che
contiene il valore da restituire deve trovarsi a destra della colonna di ricerca.
http://www.tuttoexcel.it/2014/06/04/una-versione-piu-potente-di-cercaver/
SCARTO
Descrizione
Restituisce un riferimento a un intervallo spostato rispetto a una cella o a un intervallo di celle di un numero
specificato di righe e di colonne. Il riferimento restituito può riferirsi a una cella singola o a un intervallo. È possibile
specificare il numero di righe e di colonne dell'intervallo da restituire.
Sintassi
SCARTO(rif; righe; colonne; [altezza]; [largh])
Gli argomenti della sintassi della funzione SCARTO sono i seguenti:
~ 11 ~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Rif Obbligatorio. Riferimento da cui si desidera che inizi lo spostamento. Rif deve essere un
riferimento a una cella o a un intervallo di celle adiacenti. In caso contrario, SCARTO restituirà il valore
di errore #VALORE!.
Righe Obbligatorio. Numero di righe, verso l'alto o verso il basso, che si desidera come riferimento per
la cella superiore sinistra. Se righe è uguale a 5, significa che la cella superiore sinistra del riferimento si
trova cinque righe al di sotto di rif. Righe può essere un valore positivo, che indica le righe al di sotto del
riferimento iniziale, o negativo, che indica le righe al di sopra del riferimento iniziale.
Colonne Obbligatorio. Numero di colonne, a sinistra o a destra, che si desidera come riferimento per
la cella superiore sinistra. Se colonne è uguale a 5, significa che la cella superiore sinistra del riferimento
si trova cinque colonne a destra di rif. Colonne può essere un valore positivo, che indica le colonne a
destra del riferimento iniziale, o negativo, che indica le colonne a sinistra del riferimento iniziale.
Altezza Facoltativo. Altezza del riferimento restituito espressa in numero di righe. Altezza deve essere
un valore positivo.
Largh Facoltativo. Larghezza del riferimento restituito espressa in numero di colonne. Largh deve
essere un valore positivo.
Osservazioni
Se righe e colonne sono impostate in modo tale che lo spostamento di rif si estenda al di fuori del foglio
di lavoro, SCARTO restituirà il valore di errore #RIF!.
Se altezza o largh è omesso, verrà considerato uguale all'altezza o alla larghezza di rif.
La funzione SCARTO non effettua un reale spostamento di celle o una modifica della selezione, ma ne
restituisce soltanto un riferimento. È possibile usare SCARTO con qualsiasi funzione che necessiti di un
argomento rif. Ad esempio, la formula SOMMA(SCARTO(C2;1;2;3;1)) calcola la somma dei valori
contenuti in un intervallo di tre righe per una colonna situato una riga sotto e due colonne a destra della
cella C2.
Esempio
Copiare i dati di esempio contenuti nella tabella seguente e incollarli nella cella A1 di un nuovo foglio di lavoro
di Excel. Per visualizzare i risultati delle formule, selezionarle, premere F2 e quindi premere INVIO. Se necessario, è
possibile regolare la larghezza delle colonne per visualizzare tutti i dati.
Formula
Descrizione
Risultato
=SCARTO(D3,3,-2,1,1)
Visualizza il valore nella cella B6 (4)
4
=SOMMA(SCARTO(D3:F5,3,-2, 3, Somma l'intervallo B6:C8
34
3))
=SCARTO(D3, -3, -3)
Restituisce un errore perché il riferimento è a un intervallo
#RIF!
inesistente nel foglio di lavoro.
~ 12 ~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
Dati
Dati
4
10
8
3
3
6
INDIRIZZO
Descrizione
È possibile usare la funzione INDIRIZZO per ottenere l'indirizzo di una cella di un foglio di lavoro, in base a
numeri
di
riga
e
di
colonna
specificati. INDIRIZZO(2;3),
ad
esempio,
restituisce $C$2.
Oppure INDIRIZZO(77;300) restituisce $KN$77. È possibile usare altre funzioni, ad esempio RIF.RIGA e RIF.COLONNA,
per fornire gli argomenti per i numeri di riga e di colonna per la funzione INDIRIZZO.
Sintassi
INDIRIZZO(riga; col; [ass]; [a1]; [foglio])
Gli argomenti della sintassi della funzione INDIRIZZO sono i seguenti:
riga Obbligatorio. Valore numerico che specifica il numero di riga da usare nel riferimento di cella.
col Obbligatorio. Valore numerico che specifica il numero di colonna da usare nel riferimento di cella.
ass Facoltativo. Valore numerico che specifica il tipo di riferimento da restituire.
ass
Restituisce il seguente tipo di riferimento
1 oppure omesso
Assoluto
2
Assoluto (riga), relativo (colonna)
3
Relativo (riga), assoluto (colonna)
4
Relativo
A1 Facoltativo. Valore logico che specifica lo stile di riferimento A1 o R1C1. Nello stile A1 le colonne
vengono etichettate con caratteri alfabetici e le righe con caratteri numerici. Nello stile di riferimento
R1C1 le colonne e le righe vengono entrambe etichettate con caratteri numerici. Se l'argomento A1 è
VERO o viene omesso, la funzione INDIRIZZO restituirà un riferimento di tipo A1. Se invece è FALSO,
restituirà un riferimento di tipo R1C1.
NOTA : Per cambiare lo stile di riferimento usato da Excel, fare clic sulla scheda File, fare clic su Opzioni e quindi
su Formule. In Utilizzo delle formule selezionare o deselezionare la casella di controllo Stile di riferimento R1C1.
foglio Facoltativo. Valore di testo che specifica il nome del foglio di lavoro da usare come riferimento
esterno.
La
formula=INDIRIZZO(1;1;;;"Foglio2") ad
esempio
restituisce Foglio2!$A$1.
Se
~ 13 ~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
l'argomento foglio viene omesso, non verrà usato alcun nome di foglio e l'indirizzo restituito dalla
funzione farà riferimento a una cella del foglio corrente.
Esempio
Copiare i dati di esempio contenuti nella tabella seguente e incollarli nella cella A1 di un nuovo foglio di lavoro
di Excel. Per visualizzare i risultati delle formule, selezionarle, premere F2 e quindi premere INVIO. Se necessario, è
possibile regolare la larghezza delle colonne per visualizzare tutti i dati.
Formula
Descrizione
Risultato
=INDIRIZZO(2;3)
Riferimento assoluto
$C$2
=INDIRIZZO(2;3;2)
Assoluto (riga), relativo (colonna)
C$2
=INDIRIZZO(2;3;2;FALSO)
Riga assoluta, colonna relativa in R2C[3]
stile di riferimento R1C1
=INDIRIZZO(2;3;1;FALSO;"[Cartella1]Foglio1")
Riferimento assoluto a un'altra 'Cartella1]Foglio1'!R2C3
cartella di lavoro e a un altro foglio
di lavoro
=INDIRIZZO(2;3;1;FALSO;"FOGLIO DI EXCEL")
Riferimento assoluto a un altro 'FOGLIO DI EXCEL'!R2C3
foglio di lavoro
INDIRETTO
Descrizione
Restituisce il riferimento specificato da una stringa di testo. I riferimenti vengono calcolati immediatamente in
modo da visualizzarne il contenuto. Usare la funzione INDIRETTO quando si desidera cambiare il riferimento a una
cella all'interno di una formula senza modificare la formula stessa.
Sintassi
INDIRETTO(rif; [a1])
Gli argomenti della sintassi della funzione INDIRETTO sono i seguenti:
Rif Obbligatorio. Riferimento a una cella che contiene un riferimento di tipo A1, riferimento di tipo
R1C1, nome definito come riferimento oppure riferimento a una cella come stringa di testo. Se rif non è
un riferimento di cella valido, INDIRETTO restituirà il valore di errore #RIF!.
Se rif si riferisce a un'altra cartella di lavoro, ovvero è un riferimento esterno, questa cartella di lavoro
deve essere aperta. In caso contrario verrà restituito il valore di errore #RIF!. Nota
I riferimenti
esterni non sono supportati in Excel Web App.
Se rif si riferisce a un intervallo di celle esterno al limite di riga pari a 1.048.576 o al limite di colonna
pari a 16.384 (XFD), INDIRETTO restituirà un errore #RIF!. Nota Questo comportamento differisce
~ 14 ~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
dalle versioni di Excel precedenti a Microsoft Office Excel 2007, che ignorano il superamento del
limite e restituiscono un valore.
A1 Facoltativo. Valore logico che specifica il tipo di riferimento contenuto nella cella rif.
Se a1 è VERO o è omesso, rif verrà interpretato come un riferimento di tipo A1.
Se a1 è FALSO, rif verrà interpretato come un riferimento di tipo R1C1.
Esempio
Copiare i dati di esempio contenuti nella tabella seguente e incollarli nella cella A1 di un nuovo foglio di lavoro
Excel. Affinché le formule mostrino i risultati, selezionarle, premere il tasto F2 e quindi Invio. Se necessario, adattare
l'ampiezza delle colonne per visualizzare tutti i dati.
Dati
B2
1,333
B3
45
Giorgio
10
5
62
Formula
Descrizione
Risultato
'=INDIRETTO(A2)
Valore del riferimento nella cella A2. Il riferimento è alla cella B2 che
1,333
contiene il valore 1,333.
'=INDIRETTO(A3)
Valore del riferimento nella cella A3. Il riferimento è alla cella B3 che
45
contiene il valore 45.
'=INDIRETTO(A4)
Dato che la cella B4 ha il nome definito "Giorgio", il riferimento a tale
10
nome definito è alla cella B4 che contiene il valore 10.
'=INDIRETTO("B"&A5)
Combina "B" con il valore in A5, ossia 5. Questo fa a sua volta riferimento
62
alla cella B5 che contiene il valore 62.
https://support.office.com/it-it/article/Cercare-valori-con-CERCA-VERT-INDICE-o-CONFRONTA-68297403-7c3c4150-9e3c-4d348188976b?ui=it-IT&rs=it-IT&ad=IT
https://support.office.com/it-it/article/Formule-in-forma-di-matrice-indicazioni-ed-esempi-feeac856-88a14219-95a5-40107a1b4365?ui=it-IT&rs=it-IT&ad=IT
https://support.office.com/it-it/article/Cercare-valori-in-un-elenco-di-dati-c249efc5-5847-4329-bfeeecffead5ef88
~ 15 ~
EXCEL AVANZATO - DISPENSA 1
a cura di Roberto Guerrieri
INDIRIZZO ED INDIRETTO
SCHEDE TEMATICHE
~ 16 ~