L’11 maggio 2023 è iniziato il mio percorso formativo per approfondire uno dei programmi della suite Office più usato, ossia Excel. Il corso intermedio del famosissimo “gestionale delle tabelle” di Microsoft, mi ha visto impegnato per 5 giorni (11, 15, 17, 22 e 23 maggio). Il corso, a cura della ditta CISITA formazione di La Spezia https://www.cisita.it/ ha visto come responsabili della formazione, la sig.ra CLAUDIA AMOROSO e l’istruttore DOMENICO MUCCINI che con tanta pazienza e professionalità ha spiegato ad una nutrita e matura classe di personale militare e civile della Marina Militare, i fondamenti e gli approfondimenti di questo gestionale che è di fatto il più usato nel mondo. Ecco come si è svolto, nelle 5 giornate previste, questo mio corso intermedio di Excel.
Ci tengo a precisare che nel corso sono stati eseguiti degli esercizi su dei file Excel. Se volete, potete richiedermi via mail a info@paologarrisi.blog i file degli esercizi svolti. Nessun problema ve li invierò con piacere.
Indice
Giovedì 11 maggio 2023
Operato sul file denominato “conta_somma_media_SE MIO”
Excel nasce per i dati strutturati, dati statistici, grafici e analisi con funzioni.
Excel ha più di 400 formule.
Cliccando CTRL e lasciandolo premuto, possiamo selezionare celle, righe o colonne anche non contigue.
Il cursore in Excel attiva 3 modalità di funzionamento:
- modalità selezione: il cursore all’interno della cella, si configura in una croce bianca piena. Lasciando premuto il tasto sinistro del mouse possiamo selezionale tutte le celle contigue e non;
- modalità trascinamento o copia: cursore con 4 frecce, sul bordo della cella, trascina col tasto sinistro del mouse una cella. Con CTRL effettua la copia della cella;
- modalità riempimento automatico: cursore posizionato bordo in basso a destra (piccola croce nera) per copiare e incollare il contenuto nelle celle contigue.
Excel ha in memoria 200 anni di date: dal 01/01/1900 al 31/12/2100. Al di fuori di questi estremi, le date nelle celle vengono configurate come contenuto “Generale” e non come “Data”.
- Per cancellare e resettare il contenuto della cella selezionata: menu home – in alto a dx “cancella tutto”.
- Menu contestuale: è il menu che appare quando selezioniamo una cella e clicchiamo il tasto destro del mouse. Nel menu contestuale possiamo inserire celle, righe o colonne con la funzione “inserisci…”. Anche nel menu principale, in alto a sinistra, c’è la voce “Inserisci”.
- Le formule funzionano anche se non ci sono dati all’interno della cella. E’ importante sapere che possiamo anche proteggere le formule del foglio impostando la funzione “protezione del foglio” da Menu Revisione – imposta protezione del foglio.
- Un comando molto interessante quando abbiamo una tabella piena di dati e vogliamo fissare delle righe in alto (con dei titoli ad esempio) o delle colonne a sinistra con dei titoli è “Blocca riquadri”. Selezioniamo la cella che diventa il nostro blocco del riquadro e in pratica dal menu home – Visualizza – a destra c’è “Blocca riquadri” lo stesso comando diventa poi anche “Sblocca riquadri”. Blocca riquadri blocca nella tabella sia la riga sopra che le due colonne a sinistra.
- Il segno = mi serve per dire ad Excel che stiamo scrivendo una formula o funzione.
- All’interno di una funzione i riferimenti della cella possono essere:
- Riferimenti Relativi, il loro valore si incrementa: esempio F6*G6 (colonna e riga);
- Riferimenti Assoluti. Sono fissi e non incrementano. Per rendere assoluto un riferimento della cella all’interno della formula, posizionarsi in mezzo al riferimento (es: P I 4) e cliccare F4… apparirà il dollaro $P$4… ora il dato è assoluto. Un 2° metodo per rendere un dato assoluto è dargli un nome nella “casella del nome” in alto a sinistra (vedi esempio nel file conta_somma_media_SE MIO… la casella iva) e poi dare all’interno della funzione i riferimenti del nome del dato che abbiamo scelto.
- Una formula arriva a max 256 caratteri. Gli operatori matematici sono: + – * / ^ (elevamento a potenza).
- Nella formula per operare su + dati si usa il range : due punti.
- Come si scrive una formula =nome_funzione(par1;…parn) e invio. Il ; punto e virgola mi serve per inserire più parametri all’interno della formula. Ricordarsi di premere invio per attuare la formula.
La funzione SOMMA.SE
Guarda il file Excel conta_somma_media_SE MIO (nel totale ivato cella M3) c’è la formula con i parametri.
- C’è l’intervallo di celle da controllare;
- Cosa ci deve essere nella colonna interessata (venditori) “Rossi”;
- Dove sono i dati da controllare.
Così…. Metodo 1 =SOMMA.SE(C6:C162;”ROSSI”;H6:H162)
Oppure così… Metodo 2 =SOMMA.SE(C6:C162;L3;H6:H162)
Nel metodo 2 dando solo il riferimento nome della cella L3 senza inserire il dato “Rossi” vuol dire che potremmo modificare quel dato ma la funzione non cambierà.
La funzione data è composta da: 1 intervallo + 1 criterio + 1 intervallo somma
CONVALIDA DATI è un comando che mi permette di aggiungere avvisi di info oppure errori di immissione dei dati all’interno di intervalli prestabiliti. Il percorso per attivarlo è Menu home – Dati – Convalida dati (ricorda di selezionare l’intervallo). Nel menu a tendina “Convalida dati” abbiamo anche cerchia dati non validi.
Lunedì 15 maggio 2023
Abbiamo operato sul file denominato “fatture”
Impariamo a spacchettare una data.
Scritta una data (es. 15/10/2022) dentro una cella X (nel nostro caso C2), excel con la formula:
=giorno(cliccoX) e invio, mi scrive giorno 15
=mese(cliccoX) e invio, mi scrive mese 10
=anno(cliccoX) e invio, mi scrive 2022
Impariamo in una tabella a inserire il ritardo dei pagamenti delle fatture:
Formula da applicare:
=oggi()-C2
oppure come abbiamo fatto nell’esempio sopra, abbiamo impostato una certa data (15/05/2023) in una cella U2 e l’abbiamo reso assoluta con questa formula
=$U$2-C2
Formattazione condizionale
Possiamo dare anche dei colori particolari a dei punti critici, con la formattazione condizionale.
Menu home – Formattazione condizionale (al centro)
Possiamo creare anche una nuova regola alla formattazione condizionale con “Nuova regola…” e con “Gestisci regole…” possiamo amministrare e aggiungere regole.
Il nostro 15/05/2023 (giorno, mese e anno) con regole di data americana diventa 05/15/2023 (mese, giorno e anno). Come facciamo a trasformare una data americana in data italiana?
Con 4 funzioni…
SINISTRA E DESTRA, STRINGA.ESTRAI E CONCATENA.
Quindi abbiamo spacchettato in 3 celle la data americana 05/15/2023 (evidenziata in giallo) con le seguenti funzioni
=SINISTRA(A2;2)
=STRINGA.ESTRAI(A2;4;2)
=DESTRA(A2;4)
Poi con la Funzione CONCATENA(C2;”/”;B2;”/”;D2) , abbiamo ricreato la data italiana che ci interessa
Nel nostro file excel vogliamo ora sommare i fatturati del 2022 e 2023 e poi contare automaticamente quante fatture ci sono del 2022 e quante del 2023.
La formula da usare se il parametro di ricerca è uno (ossia 2022 o 2023) è questa =SOMMA.SE
=SOMMA.SE($F$2:$F$50;J3;$G$2:$G$50)
Spiegazione funzione: somma nella colonna “Anno” (da F2 a F50 rendendoli assoluti con F4) gli Imponibili (da G2 a G50 rendendoli assoluti con F4) del 2022 (nella cella J3).
Lo stesso varrà per le fatture 2023 con questa formula =SOMMA.SE($F$2:$F$50;J4;$G$2:$G$50)
La formula da usare se il parametro di ricerca è uno (ossia 2022 o 2023) è questa =CONTA.SE
=CONTA.SE($F$2:$F$50;J3)
Spiegazione funzione: conta nella colonna “Anno” (da F2 a F50 rendendoli assoluti con F4) le fatture del 2022 (nella cella J3).
Lo stesso varrà per le fatture 2023 con questa formula =CONTA.SE($F$2:$F$50;J4)
Nel nostro file excel vogliamo ora sommare i fatturati del 2022 e 2023 e poi contare automaticamente quante fatture ci sono del 2022 e quante del 2023, riferiti però questa volta rispettivamente ai sig. Rossi e Verdi (quindi non più un solo parametro anno ma anche parametro signor)
SOMMARE I FATTURATI
La formula da usare se il parametro di ricerca sono due (ossia 2022 o 2023 e poi Rossi o Verdi) è questa =SOMMA.PIÙ.SE
=SOMMA.PIÙ.SE($G$2:$G$50;$A$2:$A$50;K10;$F$2:$F$50;J10)
Spiegazione funzione: somma nella colonna Imponibili (da G2 a G50 rendendoli assoluti con F4) tra i clienti (da A2 a A50 rendendoli assoluti con F4) dammi Rossi (K10) e tra gli anni (da F2 a F50 rendendoli assoluti con F4) dammi il 2022 (nella cella J10).
Lo stesso varrà per le fatture 2023 con questa formula =SOMMA.PIÙ.SE($G$2:$G$50;$A$2:$A$50;K11;$F$2:$F$50;J11)
CONTARE LE FATTURE
La formula da usare se il parametro di ricerca sono due (ossia 2022 o 2023 riferiti a Rossi e Verdi) è questa
= CONTA.PIÙ.SE
=CONTA.PIÙ.SE($F$2:$F$50;J10;$A$2:$A$50;K10)
Spiegazione funzione: conta nella colonna “Anno” (da F2 a F50 rendendoli assoluti con F4) le fatture del 2022 (nella cella J10) e tra i clienti (da A2 a A50 rendendoli assoluti con F4) trovami Rossi (K10).
Lo stesso varrà per le fatture 2023 con questa formula =CONTA.PIÙ.SE($F$2:$F$50;J11;$A$2:$A$50;K11)
Creiamo ora un test per decidere una condizione oppure una seconda condizione. Per esempio nel punteggio di una partita, dato un risultato, far riportare a Excel il vincitore. Ecco come si fa usando questa funzione:
=SE(B2>C2;”ATLETA 1 VINCE”;”ATLETA 2 VINCE”)
Spiegazione: B2 e C2 sono i punteggi che possono cambiare ogni volta. Nella colonna vincitore il nome dell’atleta con il punteggio maggiore. La funzione dice che se il punteggio B2 è maggiore di C2 vince atleta 1, ma è anche vero (sottinteso??) che se B2 è minore di C2 vince atleta 2.
Mercoledì 17 maggio 2023
Operato sul file “marina excel”
Oggi abbiamo fatto esercizio su 4 tipi di esempi di tabelle denominate voti, edifici, trasmissioni e cinema.
Nella tabella voti, dati un tipo di esame, l’anno e il voto conseguito dobbiamo ricavare alcuni dati:
- Voto max…….……. =MAX(C4:C33)
- Voto min……..……. =MIN(C4:C33)
- E la media voti….. =MEDIA(C4:C33)
Ecco come si fa:
Il numero totale degli esami (in giallo) si trova con la funzione =CONTA.VALORI(A4:A33) che conta qualsiasi genere di valore dentro la cella.
Abbiamo poi usato la formula =CONTA.SE($C$4:$C$33;30) per contare quanti volte è stato preso un determinato voto.
E poi abbiamo (in giallo) sommato i voti presi con la fx =SOMMA(C39:C43)
Con la formula =MEDIA.SE($B$4:$B$33;B47;$C$4:$C$33) abbiamo trovato la media dei voti conseguiti negli anni, dove con il simbolo dei dollari rendiamo il valore assoluto (puoi cliccare anche F4 quando selezioni il valore)
Nel secondo esempio abbiamo trattato alcuni dettagli degli edifici più famosi nel mondo.
Innanzitutto abbiamo trovato il MIN e il MAX dei numeri di piani e altezza e poi la MEDIA, rispettivamente per anno di costruzione (potevamo anche ometterlo), numero di piani e altezza in m.
Funzioni usate:
- =MAX(D3:D42)
- =MIN(D3:D42)
- =MEDIA(D3:D42)
Ora abbiamo contato il numero totale degli edifici e poi abbiamo evidenziato quanti edifici ci sono in ogni singolo paese. Ecco la formula =CONTA.SE$C$3:$C$42;”USA”) e così via per gli altri paesi (sostituendo “USA” con “CANADA” ecc. ecc.)
Poi con la fx =B48-B49-B50-B51-B52 abbiamo sottratto dal totale edifici, i singoli paesi e così abbiamo dedotto “altri stati”.
Successivamente con una divisione abbiamo ricavato la percentuale. =B49/$B$48 ecc.ecc.
Abbiamo poi trovato il numero degli edifici costruiti per fasce di anni. Con le seguenti formule abbiamo calcolato:
=CONTA.PIÙ.SE($D$3:$D$42;”>=1930″;$D$3:$D$42;”<=1970″) per gli edifici costruiti dal 1930 al 1970
=CONTA.PIÙ.SE($D$3:$D$42;”>=1971″;$D$3:$D$42;”<=1990″) per gli edifici costruiti dal 1971 al 1990
=CONTA.PIÙ.SE($D$3:$D$42;”>=1991″;$D$3:$D$42;”<=2001″) per gli edifici costruiti dal 1991 al 2001
Abbiamo infine fatto la somma =SOMMA(B57:B59)
Nel tab “trasmissioni” abbiamo tenuto in considerazione le ore di trasmissione televisiva di Rai e Mediaset per canale, tipo di rete e di programma nell’anno 2000.
Ecco cosa abbiamo calcolato:
Totale ore di trasmissione per singola rete dei singoli tipi di programmi (per esempio RAI) =SOMMA(B5:B11)
Totale ore trasmesse (sempre RAI) =SOMMA(B5:B11)
Percentuale in ore dei singoli programmi =H5/$B$14 (RICORDA CHE BISOGNA FORMATTARE LA CELLA IN “PERCENTUALE” PER DARE IL VALORE IN PERCENTUALE).
Abbiamo poi sommato il totale delle ore di trasmissione di RAI (Rai 1 + Rai 2 + Rai 3) =SOMMA(B12:D12) e MEDIASET (Canale 5 + Italia 1 + Rete 4) =SOMMA(E12:G12).
Abbiamo infine creato un quiz/test… Chi ha vinto tra Rai e Mediaset a livello di ore di programmazione per ogni tipologia di programma? Questa la formula di esempio (considerare poi celle diverse per canali diversi)
=SE(H5>I5;”RAI”;”MEDIASET”)
Ora consideriamo il tab “cinema”. Teniamo in considerazione le spese per i biglietti venduti per il cinematografo, per capoluogo di provincia – Anno 1998.
Troviamo per primo la percentuale dei biglietti venduti per ogni capoluogo rispetto a tutta la Lombardia.
Es. Bergamo la formula è questa =B6/$B$17
Poi troviamo la spesa media per biglietto sempre come esempio Bergamo =D6/B6*1000000 (la divisione va poi moltiplicata per 1000000 perché la spesa è visualizzata in milioni di lire).
Abbiamo poi calcolato min, max e media per biglietti venduti e per spesa media biglietti.
Abbiamo poi con la funzione =CONTA.SE($B$6:$B$16;”>1.000.000″) Province con più di 1.000.000 biglietti venduti e con la fx =CONTA.SE($B$6:$B$16;”<700.000″) Province con meno di 700.000 biglietti venduti.
Ricorda che: Il simbolo del dollaro (F4) rende il valore assoluto nella funzione.
Lunedì 22 maggio 2023
Esercizi fatti sul file “se_12_test_mio”
Nella lezione di oggi sul file sopra specificato, abbiamo trattato le “funzioni annidate” nella funzione “SE”.
In questa schermata possiamo vedere le variabili delle città con il numero di abitanti e dobbiamo risolvere il quesito all’interno del rettangolo arancione ossia dare una caratteristica di misura ad ogni città considerando il numero di abitanti.
La funzione =SE che risponde al quesito è la cosiddetta funzione annidata (in grassetto):
‘=SE(C3<100000;”PICCOLA”;SE(C3<200000;”MEDIA”;”GRANDE”))
Con questa funzione abbiamo risposto al quesito richiesto nel test.
Da notare che attraverso la formattazione condizionata (Menu Home > Formattazione condizionale) abbiamo applicato dei colori ai singoli risultati del test:
Adesso applichiamo la funzione annidata su un altro esercizio: data una tabella con nomi, data di nascita ed età, selezioniamo i dipendenti (Idoneo o non idoneo) in base all’età. Ecco la funzione che risolverà il quesito:
=SE(E(D3>=30;D3<=40);”IDONEO”;”NON IDONEO”)
Ecco il risultato:
La particolarità di questa funzione è l’uso del connettivo logico “E” all’interno della funzione annidata.
Anche qui per evidenziare coi colori il risultato del test (idoneo, non idoneo) abbiamo applicato come sopra la formattazione condizionale.
Ecco il prossimo esercizio:
In questa tabella abbiamo applicato la seguente funzione annidata SE:
=SE(O(D3=”COMMERCIALE”;D3=”AMMINISTRAZIONE”);”IDONEO”;”NON IDONEO”)
A differenza dell’esempio sopra, qui abbiamo usato il connettivo logico “O” per risolvere la richiesta ossia selezionare i soli dipendenti idonei di amministrazione e commerciale. Il resto dei dipendenti (logistica) non sono idonei.
Ancora una volta, anche qui, abbiamo applicato la formattazione condizionale al risultato.
La Tabella Ordina e Subtotale
File Excel utilizzato FATTURAZIONE_mio
In questa tabella abbiamo riempito le colonne coi titoli in evidenza giallo. Dopodichè abbiamo ordinato i valori delle celle per cliente e per quadrimestre da Menu Dati > Ordina.
Una volta ordinati i dati della tabella abbiamo usato la funzione “Subtotale” da Menu Dati > Subtotale:
Evidenziando una cella piena qualsiasi abbiamo chiesto a Excel di creare un subtotale diviso per cliente e quadrimestre:
E ancora…
Tabella PIVOT
Duplichiamo adesso la tabella fatta per annunciare l’esecuzione della Tabella Pivot. Rimuoviamo prima però tutti i subtotali da Menu Dati > Subtotale e poi premere il pulsante “Rimuovi tutti”.
Una volta pulita la tabella da Menu Inserisci > Tabella pivot possiamo creare un altro foglio con una tabella di valori che in automatico possiamo spostare e regolare a piacimento. Una funzione molto comoda per distribuire e ricollocare i dati in una tabella.
Premere una cella qualsiasi del contenuto della tabella (basta che sia piena) e cliccare Tabella pivot.
Questo è il risultato:
Nella colonna a destra “Campi tabella pivot” possiamo spostare i valori come e dove vogliamo.
Con la funzione Inserisci > Grafico pivot, possiamo inserire oltre che alla tabella anche un bel grafico. Questo il risultato:
La funzione CERCA.VERT
Abbiamo infine analizzato la funzione =CERCA.VERT(F5;$A$2:$D$12;2;FALSO)
Data una tabella verde con alcuni dati di alcuni prodotti, con questa funzione è possibile creare una tabella che si comporrà automaticamente alla sola immissione (colonna azzurra) del codice prodotto. I valori delle celle rosse, cambiamo in base al numero colonna a cui fa riferimento la funzione (vedi 2, 3, 4 di colore rosso). Particolarmente utile questa funzione per creare tabelle in cui ci sono codici prodotti.
Martedì 23 maggio 2023
Sul file excel posta_elettronica_email_mio abbiamo creato un casellario di posta elettronica concatenando funzioni di testo e funzioni di logica SE.
Ecco come abbiamo fatto l’esercizio:
In giallo (a sinistra in basso) sono evidenziate le formule usate mentre in alto a destra il risultato pezzo per pezzo. Ecco le formule usate:
L =SINISTRA(B2;1)
DeRossi =SOSTITUISCI(A2;” “;””)
vr o vi =SE(C2=”Verona”;”vr”;”vi”)
Concatena fx =CONCATENA(E2;”.”;F2;”@”;G2;”.AZIENDA.IT”)
Rende minuscola =MINUSC(H2)
Formula completa =MINUSC(CONCATENA(SINISTRA(B2;1);”.”;SOSTITUISCI(A2;” “;””);”@”;SE(C2=”Verona”;”vr”;”vi”);”.azienda.it”))
Alla fine concatenando dall’inizio le varie formule arriviamo al risultato: l.derossi@vr.azienda.it
Poi passiamo ad un altro file File 1-pivotx_mio
Esercizio: creare tabelle pivot come da dati evidenziati in giallo.
Pivot 1
Pivot 2
Pivot 3
Pivot 4
Passiamo al file file finale_mio
Ecco le consegne:
1) Dare un nome alla cella gialla
2) Utilizzare il nome della cella per le formule nelle celle delle colonne C e D
3) Fare menu a tendina nelle celle A2:A7 che peschi dalle celle A10:A12
4) Caompilare le celle a2:a7 con i cognomi 2 per ognuno
5) Fare in modo che le celle A2:A7 si colorino automaticamente una volta scelto il nome del colore corrispondente
6) Fare in modo che le celle della colonna E accettino date comprese tra 01/01/2023 e 31/01/2023
7) Compilare con una formula le celle verdi
1 fatto
2 fatto
3 e 4 fatto
5 fatto con formattazione condizionale
6 fatto
7 fatto
Ed eccoci qui alla fine di questo corso intermedio Excel. Ringrazio ancora il formatore sig. Domenico Muccini per la grande disponibilità, professionalità e pazienza dimostrata durante le lezioni.
Se avete domande o volete fare richiesta dei file di lavoro Excel con cui ho redatto questo post, contattatemi via mail a info@paologarrisi.blog
Ciao a tutti.
