7 FUNZIONI DI EXCEL PER IL CONTROLLO DI GESTIONE
Qualche tempo fa mi hanno detto che l’Excel è la morte della digitalizzazione….
In parte è vero, perché Excel è un’applicazione locale che non viene condivisa e può lavorare su un set di dati di dimensione non troppo grande.
Però per chi si occupa di controllo di Gestione Excel rimane un software flessibile, veloce e comodissimo per elaborare i dati, riclassificare bilanci, fare budget, recuperare indici e KPI e così via.
Microsoft ha aggiornato in modo importante le caratteristiche di Excel aggiungendo modalità evolute di trattamento dei dati come Power Query o Power Pivot che permettono di fare elaborazioni veloci dei dati che prima necessitavano di Macro anche complesse.
Il mio consiglio è di imparare a usare il foglio elettronico in maniera evoluta perché permette di risparmiare moltissimo tempo nell’elaborazione dei dati e aumentare la produttività del lavoro.
Excel ha una miriade di funzioni utili che vale la pena di conoscere, ne presento 7 che uso frequentemente e sono molto interessanti.
La prima è abbastanza conosciuta (o dovrebbe esserlo)
SOMMA.SE
La sintassi di somma.se è
Intervallo: sono le celle di un elenco o database in cui andare a ricercare il criterio che vogliamo estrarre
$A$45:$A$78 è l’intervallo in cui ricerco il cod 54-55 nella tabella a sinistra segnato in giallo che si trova nella cella A78; l’intervallo ha il dollaro davanti perché mi serve il riferimento assoluto per copiare poi la formula in basso velocemente.
Il criterio è il codice che vogliamo estrarre; si trova nella cella I52 ed è il cod 54-55
[int_somma] è l’intervallo dove sono contenuti i valori che vogliamo estrarre che corrispondono al criterio; nel nostro esempio l’intervallo è F45:F78 dove in corrispondenza del cod 54-55 in riga 78 e noi troviamo il valore 436.960,19 che viene riportato nella tabella a destra.
Questo semplice esempio che ho fatto è quello che io uso per estrarre i dati dalla contabilità, la tabella a sinistra e riclassificarli nel conto economico a valore aggiunto a destra.
Una volta impostata la tabella estraggo il bilancio riclassificato in una manciata di secondi, semplicemente importando l’estrazione contabile nel mio foglio di lavoro.
Un secondo esempio è l’estrazione da un database di vendita
In questo caso estraggo dal database il valore del mese specificato nella cella j83, che posso cambiare a piacimento.
Poi ci si può sbizzarrire…..
Ad es
Qui estraggo il valore di tutti gli alimenti che finiscono per e (in riga D10; *e); poi faccio la somma dei valori che corrispondono alla cella e11, in questo caso la somma di tutte le verdure.
La funzione somma.se come vedi è utilissima per estrarre valori da un database e sommarli e per copiare le formule velocemente
SCEGLI
La funzione scegli permette di scegliere tra un elenco predefinito di argomenti quello che ci interessa di volta in volta.
La sintassi di scegli è:
=SCEGLI(indice; valore1; valore2; …; valore254)
Indice: E’ un numero compreso tra 1 e 254 che specifica quale valore estrarre dall’elenco.
Valore1, valore2, …, valore254: E’ l’elenco di valori tra cui scegliere.
Il valore restituito sarà il valore in questa posizione specificata dall’indice.
Nell’esempio che vedi indice è associato alla cella che ho nominato Tipo_ricerca.
La cella viene alimentata dalla casella di selezione.
Che aumenta il numero di indice
L’indice seleziona la colonna relativa al prodotto del database
Ad es in questo momento ho selezionato il fatturato del prodotto Nuovo
Con la funzione Somma.se posso scegliere, selezionandolo dalla casella di selezione il mese e il prodotto che voglio sia visualizzato semplicemente con un click.
Un altro modo di usare scegli potrebbe essere questo:
Inseriamo nella funzione somma la funzione scegli.
In questo caso l’indice 3 mi permette di sommare i primi tre trimestri di fatturato, da b2 a j2.
Posso gestire la somma facilmente cambiando l’indice.
CERCA.X
Una volta si diceva che se conoscevi cerca.vert eri un esperto di excel….
Oggi cerca.x manda in pensione il cerca.vert
o comunque aggiunge funzionalità molto interessanti….
facciamo un confronto
La funzione CERCA.VERT è una funzione di ricerca verticale in Microsoft Excel.
Questa funzione è ampiamente utilizzata per cercare un valore specifico in una colonna di dati e restituire un valore corrispondente dalla stessa riga in un’altra colonna.
La sintassi di base della funzione è la seguente:
valore: Il valore che si desidera cercare nella prima colonna dell’intervallo della tabella.
matrice_tabella: L’area di dati in cui effettuare la ricerca, che deve contenere almeno la colonna contenente il valore da cercare e la colonna da cui restituire il risultato.
indice: Il numero della colonna nell’intervallo_tabella da cui restituire il valore corrispondente.
Ad esempio, se il valore desiderato si trova nella terza colonna dell’intervallo_tabella, il numero_colonna sarà 3.
[intervallo]: Un argomento opzionale che specifica se si desidera una corrispondenza esatta. Se è TRUE o viene omesso, CERCA.VERT cercherà una corrispondenza approssimata. Se è FALSE, cercherà una corrispondenza esatta.
In questo caso estraiamo le vendite di Luca e attraverso la funzione Confronta abbiamo un indice di colonna dinamico che ci permette di copiare la formula nelle celle vicine.
La funzione CERCA.X è una funzione di ricerca in Microsoft Excel.
Questa funzione è simile a CERCA.VERT, ma anziché cercare in una colonna, effettua la ricerca in una matrice di dati e restituisce un valore corrispondente dalla stessa colonna in un’altra riga.
La sintassi di base della funzione è la seguente:
Valore_cercato: Il valore che si desidera cercare nella matrice_ricerca.
Matrice_ricerca: L’intervallo di celle in cui viene eseguita la ricerca. nel nostro caso è limitato alla colonna dei venditori
Matrice_restituita: L’intervallo di celle da cui viene restituito il valore corrispondente. Se omesso, la funzione CERCA.X restituisce il valore dalla stessa riga della matrice_ricerca. Qui usiamo un’altra cerca.x per trovare dinamicamente la matrice restituita
Se_non_trovato (opzionale): Il valore da restituire se non viene trovata alcuna corrispondenza.
Modalità_confronto (opzionale): Specifica se la ricerca deve essere esatta o approssimativa.
Modalità_ricerca (opzionale): Specifica se la ricerca deve essere eseguita per righe o per colonne.
La funzione in alto a destra è cerca.vert, quella in basso è cerca.x:
come vedi ritornano lo stesso risultato, ma se non voglio imputare a mano l’indice di cerca.vert, devo usare la funzione confronta, che in base al campo di ricerca mi restituisce l’indice.
Nella seconda funzione posso usare il cerca.x in orizzontale, senza usare altre funzioni.
In realtà la superiorità di cerca.x si verifica in un caso come questo:
Cerca.vert funziona solo da sinistra verso destra, se deve cercare verso sinistra non funziona; cerca.x invece fa il suo lavoro normalmente.
CERCA VERT. APPROSSIMATO
C’è un caso in cui cerca.vert sostituisce egregiamente la funzione se; è una funzionalità che non è molto conosciuta ma è veramente interessante ed è la ricerca approssimata.
In questo caso la funzione cerca.vert è impostata in modalità approssimata (1);
La funzione confronta i valori in colonna E con la tabella delle provvigioni I30:J37 e trova il valore approssimato più vicino
Un fatturato fino a 100.000 corrisponde a una provvigione uguale a zero; dai 100.000 fino a 149.999 scatta la provvigione del 1% e così via.
GRANDE-PICCOLO
La funzione Grande chiede una matrice da ordinare e un indice di ordinamento e restituisce i valori secondo l’indice di ordinamento.
Ad esempio in questo caso otteniamo i primi tre fatturati del database e con la funzione cerca.x abbiamo la corrispondenza con il venditore
La funzione Piccolo lavora allo stesso modo e sotto abbiamo i tre fatturati dal più piccolo con i venditori associati.
Le due funzioni sono dinamiche; se inseriamo due righe con importi più grandi e piu’ piccoli, l’ordinamento cambia
Vedete che adesso l’ordinamento è cambiato
Se voglio evitare di aggiungere una colonna con gli indici di ordinamento mi basta inserire la funzione RIF.RIGA() in F4 e sottrarre 3 e mi viene restituito l’indice 1
SCARTO
La funzione SCARTO di Excel è una funzione di ricerca e riferimento che consente di restituire 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.
La funzione SCARTO ha questi argomenti:
rif: è la cella a partire della quale excel calcola lo scarto
righe: rappresenta il nr di righe di differenza rispetto alla cella iniziale
colonne: : rappresenta il nr di colonne di differenza rispetto alla cella iniziale
altezza: rappresenta il numero di righe che devono essere restituite dalla formula
largh: rappresenta il numero di righe che devono essere restituite dalla formula
ad es
Questa funzione restituisce i primi quattro mesi di vendita, partendo dalla cella c4 (incassi), si muove nella stessa riga (0), si sposta di una colonna, con una larghezza di 4 colonne e una larghezza di riga di 1
Una cosa interessante è che potrei fare la somma
In questo caso ottengo la somma dei primi 4 mesi
Mi basta cambiare la tabella di input per ottenere somme diverse
C’è un altro modo di utilizzare la funzione SCARTO ( in realtà ce ne sono molti altri)
Prendiamo questa tabella:
Ordiniamo il fatturato con la funzione GRANDE
Combiniamo la funzione SCARTO con la funzione CONTA.VALORI e con la funzione SE.ERRORE
Combiniamo ora CERCA.VERT, SCARTO e CONFRONTA per abbinare al fatturato ordinato con i record corrispondenti.
La cosa magica è che se aggiungiamo record alla prima tabella, la seconda si aggiorna automaticamente
INDIRETTO
La funzione INDIRETTO in Excel permette di creare un riferimento a un’altra cella in base al contenuto di una cella diversa.
Questo può essere molto utile quando si hanno dati distribuiti su diversi fogli di lavoro o se si desidera creare riferimenti dinamici che cambiano in base a determinate condizioni.
Vediamo come utilizzarla:
Sintassi della funzione INDIRETTO:
L’argomento Rif è un riferimento a una cella che contiene un riferimento di tipo A1 o di tipo R1C1, un nome definito come riferimento (intervallo denominato) o un riferimento a una cella come stringa di testo.
L’argomento A1 è un valore logico che specifica lo stile di riferimento (A1 oppure R1C1) inserito nell’argomento Rif.
Se omesso, lo stile di riferimento predefinito è A1
In questa Tabella ho creato i nomi di cella associando le città ai venditori
se digito Roma, Excel mi restituisce i nomi dei venditori associati
Quindi se nella cella E19 c’è “Firenze” e in I18 digito =indiretto(E19;0)
Ottengo i venditori di Firenze, evidenziati in giallo.
Questo è molto utile perché posso creare una convalida dati nella cella E19 con tutte le città di vendita
A questo punto posso andare nella cella F19 e creare una convalida con la funzione indiretto che punta a E19, cioè alla città che seleziono.
Convalida dinamica
Questo mi permette di selezionare i venditori delle città selezionata
Se Seleziono la città di Mestre posso avere il venditore corrispondente e a questo punto utilizzando una funzione come CERCA.X o CERCA.VERT abbiamo il fatturato del venditore
La funzione INDIRETTO è utilissima per avere delle informazioni dinamiche sui report che volete sviluppare.
Se ti è piaciuto l’articolo aiutami a diffonderlo tra le tue conoscenze
