In che chakra sei ? - Utilizzo del cerca.vert

Argomento

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

Per i dettagli su cosa sono i chakra si rimanda a www.reiki.it

 

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 è

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

Tabella dei chakra in formato CSV

 

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.

Nuovo foglio

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

Copia della tabella dei chakra

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

Incolla speciale

In questo menu selezioniamo Trasponi e diamo OK.

Incolla la tabella dei chakra trasposta

Il risultato è il seguente.

Risultato della tabella trasposta

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 seleziona tutto il foglio Ecel, e poi portandoci tra B e C quando appare Adatta le colonne di Excel facciamo doppio click.

Adattare la tabella dei chakra

Il risultato è il seguente:

Tabelle adattate automaticamente nel foglio Excel

Creiamo un nuovo foglio che chiamiamo responso.

Nuovo foglio Escel

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

Colonna con le informazioni sul Chakra

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).

Selezione delle informazioni dei Chakra

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

Definizione di un etichetta in Excel

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

Definizione dell'etichetta chakra

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

Formula cerca.vert in Excel

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.

come funziona cerva.vert in Excel

Un altro modo per inserire la formula è quello di usare il pulsante Pulsante funzione in Excel nella zona delle formule Zona formule in Excel.

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.

Funzione CERVA.VERT in Excel

Confermo con INVIO.

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

Trascinamento della formula cerca.vert()

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

numero colanna

Facciamoci aiutare. Inseriamo una colonna a sinistra della A.

Inserimento colonna in Excel

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.

Serie numerica

Il risultato è il seguente:

Serie numerica con Excel

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

CERCA.VERT() utilizzando un indice

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

Formula CERCA.VERT() con i risulati corretti

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

modifica del numero del chakra e enalisi dei risultati

Inseriamo una nuova riga sopra la prima.

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

Nuova riga in Excel

Inseriamo un nuovo foglio che chiamiamo età-chakra.

Nuovo foglio Excel 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.

Serie in Excel

 

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

Seconda parte della serie chakra

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.

Ripetizione della sequenza dei Chakra

 

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

Serie in Excel con le righe

Possiamo arrivare fino a 100 anni.

Trascinamento 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.

etichetta per la corrispondenza 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().

CERCA.VERT per calcolare in che chakra siamo in base all'età

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.

Ricerca delle etichette da applicare nelle formule in Excel

Infine in indice metto 2 corrispondente alla 2° colonna.

Formula CERCA.VERT() corretta

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

Test di verifica con varie 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.

Inserimento di nuove righe in Excel

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

inserimento della data con la funzione OGGI()

Nella cella C2 invece inserisco la data di nascita.

Inserimento della data di nascita

Della data odierna mi serve solo l'anno. Per questo c'è la funzione ANNO() che inserisco in D1.

Calcolo dell'anno della data odierna con =ANNO()

La formula è trascinabile, così ricavo anche l'anno della data di nascita. 

trascinamento della formula per il calcolo dell'anno

A questo punto posso calcolare l'età: anno corrente - anno di nascita.

Calcolo dell'età con Excel

Questa formula però non funziona quando la persona non ha ancora compiuto il compleanno nell'anno corrente. Nell'esempio dovrebbe apparire 56 e non 57.

Età errata

Per risolvere questo errore occorre capire se la persona ha compiuto il compleanno nell'anno corrente. In pratica se giorno/mese dell'anno di nascita sono inferiori a giorno/mese della data corrente occorre togliere 1 al calcolo fatto in precedenza. Per togliere l'anno ed effettuare i calcoli possiamo "normalizzare" entrambe le tate al 1900. Le funzioni DATA(), MESE() e GIORNO() ci possono aiutare.

La formula =DATA(1900;MESE(C1);GIORNO(C1)) prende giorno e mese dalla cella C1 (data corrente) e aggiunge l'anno 1900.

Normalizzazione della data

Se trasciniamo la formula, otteniamo lo stesso effetto anche per la data di nascita. Il risultato è il seguente:

trascinamento della formula per normalizzare le date

Ora è possibile se capire se la persona ha compiuto il compleanno nell'anno corrente. Ricorriamo alla funzione =SE().

Se la data di nascita "normalizzata" F1 è minore della data corrente normalizzata F2 occorrerà togliere 1 al calcolo dell'età E1, altrimenti il calcolo è corretto.

La formula è =SE(F1<F2:E1-1:E1)

Funzione SE() in Excel

A questo punto invece di mettere manualmente l'età nella cella C3 possiamo utilizzare il calcolo fatto in G1.

Età calcolata

Come sempre possiamo fare dei test inserendo una data di nascita dove la persona ha compiuto il compleanno nell'anno corrente es 01/04/1962

Test con un'altra data

I calcoli sono corretti.

Possiamo ritenerci soddisfatti. Per quanto riguarda la colonna A possiamo nasconderla. Se volessimo togliere le formule nelle colonne D, E, F e G possiamo procedere in questo modo.

Ritorniamo nella nostra formula in G1 =SE(F1<F2:E1-1:E1). Cominciamo con sostituire F1.

Creazione di una formula unica primo passo

Andiamo a vedere la formula in F1 . possiamo copiare DATA(1900;MESE(C1);GIORNO(C1))

Creazione di una formula unica: cosa c'è in F1

e incollarlo al posto di F1 nella formula in G1.

Creazione di una formula unica sostituzione di F1

Sostituiamo ora F2 andando a vedere la formula inserita nella cella F2. Copiare DATA(1900;MESE(C2);GIORNO(C2))

Creazione di una formula unica primo passo copia della formula F2

e incolliamolo al posto di F2 nella formula in G1.

Creazione di una formula unica primo passo. Sostituzione di F2

Sostituiamo ora D1. La sua formula è ANNO(C1) che copiamo.

Creazione di una formula unica primo passo. Copia della formula in D1

e incolliamolo al posto di D1 nella formula in G1.

Creazione di una formula unica primo passo. Sostituzione di D1

Sostituiamo infine D2. La sua formula è ANNO(C2) che copiamo.

Creazione di una formula unica primo passo. Copia della formula in D2

e incolliamolo al posto di D2 nella formula in G1.

Creazione di una formula unica primo passo. Incolliamolo al posto di D2 nella formula in G1.

Come si vede questa formula non dipende più da quelle inserite nelle colonne D,E,F.

Per finire possiamo copiarla e sostituirla nella cella C3.

Creazione di una formula unica primo passo in Excel

A questo punto il contenuto delle colonne D,E,F,G può essere cancellato senza che ci siano errori

Cancellazione delle colonne

Il risultato è:

In che chakra sei ? Risultato finale

La formula inserita è quindi:

=SE(DATA(1900;MESE(C1);GIORNO(C1))<DATA(1900;MESE(C2);GIORNO(C2));ANNO(C1)-ANNO(C2)-1;ANNO(C1)-ANNO(C2))

che può essere semplificata anche come:

=ANNO(C1)-ANNO(C2)-SE(DATA(1900;MESE(C1);GIORNO(C1))<DATA(1900;MESE(C2);GIORNO(C2));1;0)

In che chakra sei ? Formula semplificata

La tabella-cakra iniziale può essere cancellata.

Cancellazione della tabella-chake

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()

Punto debole è la colonna A in 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"

Creazione di 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 .

Utilizzo della funzione CONFRONTA()

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

Trascinamento della formula per determinare il testo associato al chakra

 

 

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

Protezione fogli Excel. Selezione delle celle da non proteggere

Prima di proteggere il foglio quindi rendiamola selezionabile e modificabile.

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

Nella scheda Protezione spegniamo Bloccata.

Menù formato celle in Excel

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.

Protezione del foglio resposo in Excel

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

RIpetizione della password

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.

Protezione fogli con Excel

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.

Rimozione della protezione del foglio

 

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
  • e se ho seguito il link ho scoperto cosa sono i chakra 🙂