Excel: In che chakra sei ? - Utilizzo del cerca.vert

Con questo tutorial possiamo vedere un'applicazione della funzione CERCA.VERT() e CONFRONTA().

Obiettivo

In base alla data di nascita in che chakra sono ?

Cosa impariamo

Un esempio di utilizzo della funzione CERCA.VERT(), CONFRONTA(), alcune funzioni per le date e la protezione dei fogli.

Impostazioni

Quello che ci serve per realizzare questo esercizio è

• la tabella-chakra.csv con i dati che trovate allegata alla fine del tutorial.

I file in formato CSV vengono subito riconosciuti da Excel. Infatti quando facciamo doppio click su questo file appare:

Per il lavoro che vogliamo fare ci conviene avere la tabella "trasposta" in modo che per ogni riga ci siano le informazioni relative a quel chakra.

Nella tabella che invece abbiamo importato queste informazioni sono riportate per colonna.

A questo punto inseriamo un nuovo foglio che chiamiamo tabella.

Torniamo su tabella-chakra, selezioniamo tutti i dati e facciamo copia.

Passiamo sul foglio tabella e dalla scheda Home facciamo click sulla piccola freccia nera sotto incolla, e scegliamo Incolla Speciale.

In questo menu selezioniamo Trasponi e diamo OK.

Il risultato è il seguente.

Le righe seguenti a quella di intestazione, contengono le informazioni relative ad ogni singolo chakra.

Possiamo migliorare la lettura della tabella, selezionandola tutta con il quadratino in alto a sinistra , e poi portandoci tra B e C quando appare facciamo doppio click.

Il risultato è il seguente:

Creiamo un nuovo foglio che chiamiamo responso.

Nella colonna A riportiamo le informazioni che ci servono: chakra, Nome Sanscrito, Significato, Localizzazione, Elemento, Funzione, Ghiandole, Colore, Cibi, Verbo, Pietra.

Quindi nel momento in cui nella cella B1 scrivo 1 voglio che appaia il Nome Sanscrito, il suo significato ... e tutte le altre informazioni nella colonna B.

Torniamo nel foglio Tabella e selezioniamo l'area con le informazioni sui chakra (da A2 fino a K7).

Posso scrivere il nome tabella nella campo delle etichette e dare INVIO.

In questo modo l'area selezionata si chiama tabella. Questo semplificherà molto la scrittura delle formule.

La stessa cosa la facciamo nel foglio responso. selezioniamo la cella B1 e la chiamiamo chakra

Nella cella B2 inseriamo la formula =CERCA.VERT(chakra;tabella;2)

Il significato della formula è: cerca il contenuto della cella chakra, nell'esempio 1 nella tabella.

Il numero 2 indica di prendere le informazioni dalla colonna 2 quindi Muladhara.

Un altro modo per inserire la formula è quello di usare il pulsante nella zona delle formule .

CERCA.VERT si trova nelle funzioni di Ricerca e Riferimento. La finestra che appare aiuta meglio a comprendere gli argomenti della funzione, e inoltre mi visualizza in anteprima il risultato.

Confermo con INVIO.

Se trascino in giù la formula però non ottengo il risultato voluto.

Questo perché vado sempre ad estrarre le informazioni dalla 2° colonna in tabella.

Facciamoci aiutare. Inseriamo una colonna a sinistra della A.

e inseriamo una numerazione che parte da 2 fino a 11. Per fare questo scriviamo 2 in A2 e trasciniamo un giù il quadratino nero tenendo premuto il testo CTRL.

Il risultato è il seguente:

A questo punto possiamo riscrivere la formula utilizzando questa colonna. Al posto di 2 facciamo riferimento alla cella A2.

Se trasciniamo in giù la formula otteniamo il risultato voluto.

Se al posto 1 nella cella C1 metto 2 i risultati cambieranno.

Inseriamo una nuova riga sopra la prima.

L'idea è quella di calcolare in che chakra mi trovo in base all'età

Inseriamo un nuovo foglio che chiamiamo età-chakra.

Il compito di questo foglio è convertire l'età nel chakra corrispondente.

Nei primi sette anni la corrispondenza è 0 anni -> 1° chakra, 1 anno -> 2° chakra, ... 6 anni -> 7° chakra.

Questo può essere ottenuto con una serie.

Al 7° anno siamo ancora nel 7° chakra. Negli anni successivi si riscende fino al 1°.

A questo punto si riparte ancora dal chakra 1. La soluzione più semplice e inserire una formula che copia quello che è scritto nella cella B1.

Non rimane che inserire 14 nella cella A15 e trascinare il contenuto della riga.

Possiamo arrivare fino a 100 anni.

Ottenendo infine la corrispondenza fra età (colonna A) e chakra (colonna B). Selezioniamo questi dati (da A1 fino a B101) e nominiamo quest'area età_chakra.

Torniamo nel foglio responso. A questo punto possiamo calcolare in che chakra siamo in base all'età utilizzando la tabella appena costruita. Quello che ci serve è ancora CERCA.VERT().

in Valore inserisco l'età si trova nella cella C1.

Per quanto riguarda Matrice_tabella, se non ricordo il nome dell'etichetta che ho dato alla tabella di conversione tra età e chakra, posso farmi aiutare da Excel andando in nella scheda Formule > Usa nella formula e seleziono età_chakra.

Infine in indice metto 2 corrispondente alla 2° colonna.

Per verificare il risultato poso fare vari test cambiando l'età.

Calcolare l'età in base alla data di oggi

Il prossimo passo è inserire l'anno di nascita  e calcolare l'età in base alla data di oggi. Inseriamo due nuove righe all'inizio del foglio.

Nella nuova cella C1 inseriamo la funzione OGGI(), che restituisce la data odierna.

Nella cella C2 invece inserisco la data di nascita.

Per calcolare la differenza in anni usiamo la funzione DATA.DIFF()

La tabella-chakra iniziale può essere cancellata.

Il risultato finale tabella-chakra-risolto.xlsx è scaricabile in fondo al tutorial.

A voi come abbellirlo graficamente.

Utilizzo della funzione CONFRONTA()

Un punto debole del percorso che abbiamo visto è la colonna A che ci è servita per la formula CERCA.VERT()

Se la colonna A venisse cancellata inavvertitamente, la formula non funzionerebbe più.

Ci può venire in aiuto la funzione CONFRONTA().

Andiamo nel foglio "tabella", selezioniamo da A1 fino ad K1 e definiamo un nuovo nome : "etichette"

A questo punto possiamo usare la formula CONFRONTA(). Con questa funzione possiamo andare a cercare la posizione dei vari elementi "Nome Sanscrito, Significato, Localizzazione ..." all'interno delle "etichette" che avevamo definito sopra.

Ad esempio

=CERCA.VERT(C$4;tabella;CONFRONTA(B5;etichette;0))

permette di cercare B5 quindi "Nome Sanscrito" che si trova in 2° posizione. Il risultato quindi è 2 esattamente come in valore che avevano messo nella colonna .

Per finire possiamo trascinare in giù la formula appena inserita e volendo eliminare la colonna A.

Protezione del foglio

Infine se volgiamo evitare che chi utilizza questa cartella Excel rovini il lavoro fatto, possiamo proteggere i fogli.

Selezioniamo la cella C2 del foglio responso. Questa cella deve poter essere modificabile dal nostro utente

Prima di proteggere il foglio quindi rendiamola selezionabile e modificabile.

Con CTRL + 1 oppure da HOME e clic sulla freccia possiamo accedere al menù Formato celle.

Nella scheda Protezione spegniamo Bloccata.

Proteggiamo il foglio responso. Nella scheda Revisione facciamo click su Proteggi foglio.

Inseriamo una password per proteggere il foglio. e permettiamo solo la selezione delle celle sbloccate.

In questo modo l'utente potrà scrivere solo la data di nascita.

Per sicurezza Excel ci richiede di inserire una seconda volta la password scelta

Possiamo ripetere il procedimento anche per gli altri due fogli: tabella, età-chaka.

In questo caso però impediamo al nostro utente qualsiasi azione spegnendo tutte le opzioni.

Abbiamo terminato.

Rimozione della protezione del foglio.

Se volessimo apportare delle modifiche al nostro lavoro, abbiamo bisogno di togliere la protezione del foglio.

Sempre dalla scheda Revisione facciamo click su Rimuovi protezione foglio e inseriamo la password che avevamo scelto.

Cosa ho imparato

• la copia trasposta di una tabella di dati
• l'utilizzo delle etichette per semplificare la comprensione delle formule
• la funzione CERCA.VERT() per ottenere le informazioni contenute in una riga di una tabella
• la funzione OGGI() per sapere la data corrente
• le funzioni MESE() e GIORNO() per estrarre il mese e il giorno da una data
• la funzione GIORNO() per creare una data
• la funzione SE() per effettuare dei test e avere valori diversi in base al risultato del test
• la creazione di formule complesse partendo prima dalla scomposizione del problema e successivamente copiando e incollando le formule intermedie
• come proteggere un foglio