{count, plural, one {Corso} other {Corsi}}
Google Sheets intermedio
{hours} ore
57.9K
A volte, la data science non riguarda il deep learning, i big data o i sistemi di raccomandazione. In certi casi, soprattutto se lavori in un settore come la finanza o in un'azienda che in passato ha fatto largo affidamento su Excel, è probabile che ti capiti spesso di lavorare con fogli di calcolo Excel. Lo ammetto: io rientro in quest'ultimo caso. Il mio datore di lavoro si basava molto su Excel e, di conseguenza, ho dovuto imparare parecchio sulle sue varie funzionalità per svolgere alcune delle mie responsabilità.
Anche se R e Python hanno vantaggi evidenti rispetto a Excel in molti ambiti, Excel è ancora uno strumento potente per certi compiti, come la creazione di report aziendali. In questo tutorial, però, mi concentrerò su una delle funzioni integrate di Excel che aiuta molto quando si lavora contemporaneamente con diverse tabelle di Excel.
La funzione a cui mi riferisco è CERCA.VERT. CERCA.VERT consente di mettere in relazione colonne diverse nello stesso foglio o tra fogli differenti della stessa cartella di lavoro. In sostanza, permette di cercare un valore in una colonna e restituirne il valore corrispondente da un'altra colonna. Questa funzionalità è molto utile se hai grandi tabelle di dati non ordinate e devi metterle in relazione in base a qualche colonna (idealmente contenente una chiave univoca).
Ti mostro come funziona l'uso di CERCA.VERT tra colonne nello stesso foglio con una tabella di esempio:
Quello che vedi nello screenshot sopra è una comune espressione con CERCA.VERT scritta come faresti per un report aziendale. I suoi componenti sono i seguenti:
- Funzione SE.ERRORE: usata per sostituire un errore con un trattino (-) se la chiave era vuota o non presente nella tabella dati. È semplicemente una buona pratica di gestione degli errori che rende il foglio più pulito quando si verifica un errore, ma non è certo necessaria per far funzionare CERCA.VERT.
- Argomenti di CERCA.VERT:
- E2 è il valore da cercare (in questo caso abbiamo usato KOG come esempio, ma potrebbe essere una qualsiasi delle chiavi). Questo valore deve trovarsi nella colonna più a sinistra dell'intervallo di tabella selezionato che vuoi cercare.
- B1:C18 è l'intervallo/array di tabella in cui cercare i valori. La colonna più a sinistra ha indice 1 come mostrato. Questa colonna deve anche essere quella contenente i valori di ricerca affinché la funzione funzioni.
- 2 è il numero di indice di colonna dell'intervallo di tabella selezionato da cui vuoi recuperare il valore. In questo esempio vogliamo recuperare la descrizione, quindi scegliamo la colonna 2 dell'array di tabella.
- FALSO è un flag per indicare che vuoi una corrispondenza esatta del valore da cercare. In questo caso, abbiamo scelto FALSO perché vuoi una corrispondenza esatta della chiave KOG. L'altra opzione è selezionare VERO, che cerca una corrispondenza approssimativa del valore da cercare ed è il valore predefinito se non specifichi questo argomento. In genere non si consiglia di selezionare VERO quando si confrontano chiavi o stringhe, perché può produrre risultati strani.
Ora, dopo aver scritto quell'istruzione CERCA.VERT, se premi INVIO in Excel, otterrai il seguente risultato:
Come puoi vedere, la funzione CERCA.VERT ha restituito proprio quello che ci si aspetterebbe, "King of Gondor", ovvero la descrizione associata alla chiave KOG. Questo vale per qualsiasi chiave che inserisci ora nella cella E2. Per esempio, vediamo cosa succede se inseriamo la chiave KIN.
The King in the North! Esatto: se inseriamo la chiave KIN ci aspettiamo di ottenere quella, dato che è la descrizione del nostro caro Jon (Snow).
Tuttavia, CERCA.VERT è utile anche oltre al recupero di un singolo valore in base a una chiave. Supponiamo per esempio di dividere quella tabella in due, ciascuna collocata su un foglio diverso della stessa cartella di lavoro.
Unire tabelle
 |
 |
Come vedi, ora i dati sono divisi in due fogli diversi (S1 e S2) e le chiavi nella tabella del secondo foglio (S2) sono in un ordine diverso. Per fortuna, la colonna delle chiavi è comune a entrambi i fogli indipendentemente dall'ordine, quindi possiamo unire i dati in S1 usando CERCA.VERT (puoi pensarla come una left join su una singola colonna). La sintassi per ottenere questo è la seguente:
Questa volta, il primo argomento, il valore da cercare, diventa B2, perché vogliamo far corrispondere la chiave B2 nella tabella su S2. L'intervallo di tabella ora diventa l'intera tabella dati in S2 con la prima colonna contenente le chiavi comuni. Noterai che l'intervallo di tabella ha dei simboli del dollaro. Questo si ottiene premendo F4 dopo aver selezionato l'intervallo e serve a bloccarlo in modo tale che, quando trascini la formula verso il basso in S1, la funzione CERCA.VERT continui a guardare l'intervallo originale senza spostarlo. Il numero di indice di colonna resta 2 perché vogliamo la descrizione, che è la seconda colonna in S2. Infine, l'ultimo argomento che vogliamo è FALSO, perché desideriamo una corrispondenza esatta per le chiavi.
L'ultimo passaggio è semplicemente trascinare la formula fino all'ultima voce della tabella. La tabella risultante in S1 è la seguente:
Voilà! Proprio così, abbiamo ricucito i dati usando CERCA.VERT. Unire tabelle in questo modo è un'operazione molto comune quando si lavora in un contesto business e SQL, R o Python non è lo strumento a cui i dirigenti senior sono abituati. Ad esempio, potresti avere due file contenenti dettagli diversi su opportunità di vendita qualificate che possono essere correlate usando un ID opportunità univoco per un report mensile di vendita in Excel (il mio miglior esempio di uso di Excel).
Usare corrispondenze approssimative
Finora avrai notato che abbiamo evitato di usare VERO come ultimo argomento (ovvero una corrispondenza approssimativa) in tutte le mie funzioni CERCA.VERT. Questo perché ci siamo concentrati sulla corrispondenza esatta di chiavi univoche. Tuttavia, puoi usare in sicurezza VERO come ultimo argomento in CERCA.VERT quando lavori con dati numerici.
Per esempio, immaginiamo di vendere pesci e di offrire i seguenti sconti quando i clienti acquistano certe quantità (Se qualcuno chiede perché i pesci, è semplice: i pesci sono fantastici!).
La funzione CERCA.VERT all'interno della cella F2 questa volta è:
Una volta impostata la formula, lo sconto sarà quello più vicino alle soglie nella tabella. Per esempio, se compriamo 25 pesci, il nostro sconto sarà del 5%, se ne compriamo 70 sarà del 10% e se ne compriamo 120, allora lo sconto sarà del 15%. Tutti questi esempi sono mostrati qui sotto:
 |
 |
 |
Introduzione a INDICE-CONFRONTA
CERCA.VERT è una funzione potente per mettere in relazione colonne diverse. Tuttavia, esiste un modo alternativo per farlo: la combinazione INDICE-CONFRONTA. A differenza di CERCA.VERT, che è una singola funzione, INDICE-CONFRONTA è una combinazione di due funzioni e ha diversi vantaggi rispetto alla CERCA.VERT standard, anche se può essere un po' più complessa da scrivere.
Per gli scopi di questo tutorial, vedremo un esempio semplice in cui usiamo INDICE-CONFRONTA per unire due tabelle in base a una chiave comune, come abbiamo fatto sopra con CERCA.VERT, e ne spiegheremo la sintassi (vedi sotto).
I componenti della combinazione INDICE-CONFRONTA sono i seguenti:
- Argomenti di INDICE:
- M2:M18: è l'intervallo di colonna i cui valori vuoi recuperare; in questo caso vogliamo la colonna Descrizione, quindi selezioniamo quell'intervallo. Nota che abbiamo usato F4 dopo aver selezionato l'intervallo per bloccarlo, così non si sposta quando trascini la formula.
- Argomenti di CONFRONTA:
- B2: è la chiave che vogliamo cercare. È l'equivalente del valore da cercare in CERCA.VERT.
- L2:L18: è l'intervallo di colonna contenente le chiavi in cui deve trovarsi il valore da cercare. Nota che abbiamo usato F4 anche qui.
- 0: è lo stesso di FALSO in CERCA.VERT. Ovvero un flag per una corrispondenza esatta.
Osservando la sintassi, si può già intuire uno dei principali vantaggi di INDICE-CONFRONTA rispetto a CERCA.VERT. Con INDICE-CONFRONTA abbiamo immunità alle colonne. Cioè, se decidessimo di aggiungere una colonna aggiuntiva tra Chiave e Descrizione nella tabella di destra sopra, non importerebbe, perché la formula si adeguerebbe comunque a fare riferimento alla colonna Descrizione. Puoi vederlo qui sotto:
Con CERCA.VERT questo non è possibile senza tornare nella formula, poiché ha un riferimento di colonna statico. In altre parole, nell'esempio sopra, inizialmente avresti messo 2 come numero di indice di colonna per recuperare la Descrizione. Tuttavia, dopo aver aggiunto la colonna Pesci, la funzione CERCA.VERT restituirebbe ora il valore nella colonna Pesci, perché quella è diventata la colonna 2 nell'intervallo di tabella. Devi tornare manualmente nella funzione CERCA.VERT e cambiare il numero di indice di colonna a 3 per farla funzionare di nuovo correttamente.
Detto questo, per unire la descrizione al nome nella tabella a sinistra usando INDICE-CONFRONTA la procedura è la stessa di CERCA.VERT. Trasciniamo semplicemente la formula come mostrato di seguito:
Nota che per unire tabelle tra fogli diversi della stessa cartella di lavoro la procedura è identica. L'unica differenza è che dovrai selezionare l'intervallo di colonna contenente le chiavi comuni e l'intervallo di colonna i cui valori vuoi recuperare dal secondo foglio.
Conclusione
Spero che il tutorial ti sia piaciuto. Queste funzioni, CERCA.VERT e INDICE-CONFRONTA, sono praticamente essenziali per chiunque lavori con Excel in ambito aziendale. Ti invito a esplorare INDICE-CONFRONTA oltre quanto trattato qui, perché offre molte funzionalità interessanti, come il confronto con distinzione tra maiuscole e minuscole (Sì, per impostazione predefinita INDICE-CONFRONTA e CERCA.VERT non fanno distinzione tra maiuscole e minuscole) o il confronto basato su più colonne. Continua a imparare: il cielo è il limite!
Se vuoi saperne di più su CERCA.VERT, segui il corso di DataCamp Data Analysis with Spreadsheets.
Argomenti
