Stupire capo e colleghi con excel (parte 2): funzioni avanzate
MS Excel

Con la recente guida di Excel abbiamo imparato ad usare delle funzioni molto utili che, seppure di livello base, possono ridurre in modo considerevole i tempi di lavoro su un foglio Excel e migliorare quindi l’efficienza sul lavoro.

Vogliamo ora fare un passo ulteriore per coloro che avessero ormai metabolizzato la precedente guida e introdurre delle funzioni più avanzate che, una volta entrate nel nostro repertorio, ci permetteranno non solo di stupire positivamente i colleghi ma anche di portare la nostra capacità di interrogazione e analisi dei dati ad un livello superiore. Le funzioni avanzate di Excel sono ovviamente molte, inizieremo quindi esplorandone 5, tra le più utili, cercando di semplificare al massimo la loro comprensione con l’aiuto di GIF animate.

Mettetevi comodi sulla vostra poltrona e provate a ripetere da soli le formule qui illustrate fino a che non riuscirete a padroneggiarle con dimestichezza.

Introduzione a Idice e Confronta

Iniziamo introducendo due funzioni chiamate INDICE e CONFRONTA. La funzione INDICE funziona così: indichiamo ad Excel un range di valori che può essere una matrice di numeri oppure possiamo indicare una singola riga o una singola colonna. Dopodiché gli diciamo da quale posizione vogliamo che estragga un valore. Se abbiamo indicato una matrice, dovremo ovviamente indicare 2 numeri: uno per la riga e l’altro per la colonna. Se invece abbiamo selezionato solo una riga o solo una colonna sarà sufficiente inserire un valore solo. Vedremo dopo un esempio concreto.

La funzione CONFRONTA invece ci dice in che riga o colonna si trova un valore da noi specificato. Si integra perfettamente con la funzione INDICE: infatti se CONFRONTA ci dice la riga o la colonna di un valore, questo stesso numero può essere usato come input nella funzione INDICE che richiede, appunto, che sia fornita una posizione in modo da dirci quale valore vi si trovi.

Vediamo ora come usare le due funzioni aiutandoci con degli esempi.

Indice

La funzione INDICE è in questa forma:

INDICE(matrice; riga; [col])

Dove i parametri da inserire sono:

  • Matrice: può essere una matrice di dati, una riga oppure una colonna.
  • Riga: il numero di riga da cui vogliamo estrarre un valore.
  • Col: il numero di colonna da cui vogliamo estrarre un valore.

Come si vede dall’esempio, è possibile indicare contemporaneamente la riga e la colonna oppure è anche possibile riportare solamente una riga o solo una colonna. L’importante è che almeno uno dei due valori (riga o colonna) sia inserito.

funzione indice excel

Mettiamo di avere una matrice di dati con: regione, capoluogo, abitanti capoluogo. Chiediamo ad Excel cosa si trova incrociando la quarta riga e la seconda colonna.
Dopodiché vogliamo sapere cosa si trova, all’interno della seconda riga, nella seconda colonna (questa volta invece che una matrice indichiamo direttamente una riga, selezionandola tutta, e poi indichiamo la colonna).

Confronta

La funzione CONFRONTA è in questa forma.

CONFRONTA(valore; matrice; [corrisp])

Dove i parametri da inserire sono:

  • Valore: questo è il valore che stiamo cercando.
  • Matrice: è l’intervallo di celle da cui estrarre il valore e può essere una riga oppure una colonna.
  • Corrisp: è il tipo di corrispondenza che vogliamo:
    • Se inseriamo “1” Excel restituisce, tra i valori minori o uguali al valore di ricerca, il valore più grande. Per utilizzare questa corrispondenza è necessario ordinare i valori dal più piccolo al più grande.
    • Se inseriamo “0” Excel restituisce il valore uguale al valore di ricerca.
    • Se inseriamo “-1” Excel restituisce, tra i valori maggiori o uguali al valore di ricerca, il valore più piccolo. Per utilizzare questa corrispondenza è necessario ordinare i valori dal più grande al più piccolo.
    • Se non si indica nulla Excel riporta il risultato che avremmo con 1 (vedi primo punto)

Nell’esempio, prendendo la città di Roma, chiediamo ad Excel di dirci prima in quale riga si trova e poi in quale colonna.

funzione confronta excel

vantaggi

Se vi state chiedendo a cosa possano concretamente servire, vediamo quali sono i due principali vantaggi che hanno le funzioni INDICE e CONFRONTA:

  • Cercare da destra a sinistra: nella precedente guida abbiamo approfondito la funzione CERCA.VERT che tuttavia non può essere utilizzata per cercare alla sinistra di un determinato valore. Vedremo di seguito che una delle caratteristiche più utili delle funzioni INDICE e CONFRONTA usate insieme è proprio quella di effettuare una ricerca verso sinistra.
  • Eliminare o aggiungere colonne: mentre con CERCA.VERT se eliminiamo o aggiungiamo una colonna nella matrice di dati alteriamo il risultato della formula, con INDICE E CONFRONTA possiamo liberamente inserire o togliere colonne senza alterare nulla.

Abbiamo detto che è possibile cercare verso sinistra utilizzando queste due funzioni: vediamo di seguito un esempio. Partendo dalla solita tabella, chiediamo ad Excel a quale regione appartiene il capoluogo con 117,295 abitanti. Come è evidente Excel deve muoversi verso sinistra essendo la colonna “Regione” a sinistra rispetto alla colonna “abitanti capoluogo”: cosa che non sarebbe possibile con CERCA.VERT.  La funzione costruita nell’esempio sottostante è:

=INDICE(A2:A5;CONFRONTA(E3;C2:C5;0))

Come si vede abbiamo utilizzato la funzione INDICE indicando ad Excel di cercare nella colonna “Regione” in corrispondeza della riga individuata dalla funzione CONFRONTA –  che è appunto la riga contenente il numero di abitanti cercato.

cerca a sinistra excel

Formule 3D

Questo tipo di formule ci permette di eseguire operazioni non con le celle ma utilizzando i fogli di Excel. Si tratta quindi di creare delle funzioni che non lavorino all’interno dello stesso foglio ma vadano invece a effettuare calcoli tra fogli diversi.

Nell’esempio sotto, abbiamo tre fogli contenenti ognuno lo stesso tipo di tabella e di dati ma ogni foglio è riferito ad un anno differente. Se vogliamo sommare lo stesso valore, ad esempio le vendite del prodotto P1, per i diversi anni possiamo partire dalla funzione SOMMA:

SOMMA(num1;[num2];…)

Il trucco è sostituire quelli che solitamente sono celle (num1, num2 etc…) con i fogli. Come si vede è sufficiente scrivere la seguente funzione:

=SOMMA(FOGLIO1:FOGLIO3!B3)

Dove il simbolo “:” (due punti) dice ad Excel di sommare dal foglio 1 fino al foglio 3 e di utilizzare sempre e solo la cella B3, che è proprio la cella contenente le vendite del prodotto 1.

somma 3d excel

Somma se

Con questa funzione possiamo effettuare una somma specificando con quali criteri Excel sceglie i valori da sommare. Ad esempio, se abbiamo una tabella con diversi lotti di frutta venduta e il relativo valore e vogliamo sapere a quanto ammonta il totale venduto per uno specifico tipo di frutta (mele), utilizziamo la funzione:

=SOMMA.SE(intervallo;criterio;[int_somma])
dove:

  • Intervallo: è l’intervallo in cui cercare i valori.
  • criterio: nel nostro caso è “mele” in quanto vogliamo conoscere il totale venduto per questo frutto.
  • Int_somma: indica le celle con i valori da sommare, in questo caso si tratta della colonna “Valore”.

funzione somma se excel

Conta se

La funzione CONTA.SE è molto utile se abbiamo un lungo elenco di valori diversi tra loro e vogliamo contare quanti sono quelli corrispondenti ad una caratteristica da noi scelta. Nell’esempio abbiamo diverse spedizioni per le quali la colonna “consegnato” ci dice se sono state consegnate oppure no. Se vogliamo conoscere quanti pacchi, tra i molti ordini, sono stati consegnati si possono contare solo le celle contenenti “si” utilizzando la seguente funzione:

=CONTA.SE(intervallo;criterio)

dove:

  • Intervallo: è l’intervallo di celle in cui contare i valori.
  • Criterio: è la condizione con cui contare i valori (“si” nel nostro caso).

funzione conta se excel

 

 

Qui alla Viking speriamo che questa breve guida su alcune funzioni avanzate di Excel possa tornarvi utile e risparmiarvi tempo prezioso quando dovrete lavorare con file Excel contenenti molti dati. Conosci altri trucchi e funzioni interessanti di Excel di cui non puoi fare a meno sul lavoro? Scrivici sulla pagina Facebook Viking Italia.