[EXCEL] Z-SCORE CON EXCEL

Scopriamo con un semplice esempio come l’uso degli Z-SCORE con Excel possa essere utile nell’Analisi dei Dati.

In una gara, di una serie di 5 disputate, viene registrato il “Record di tutta la serie di cinque gare”. È il punteggio associato a questo record anche la migliore prestazione relativa tra le gare?
Analogamente, quale considerazione possiamo fare relativamente al peggior punteggio ottenute nelle 5 edizioni della competizione?
Se relativizziamo i massimi ed i minimi punteggi registrati, all’andamento generale della gara a cui appartengono, possiamo dare una risposta alle domande precedenti.
In questo articolo introduciamo gli Z-SCORE nella loro forma più semplice. Ci chiediamo come valori appartenenti a serie diverse possano essere efficacemente confrontati tra di loro.

Lo Z-SCORE di un valore dato dà una indicazione di quanto egli sia lontano dalla media della popolazione a cui appartiene. Più tecnicamente è una misura di “quante deviazioni standard sotto o sopra la media si trovi il valore analizzato”.
Vediamo adesso quali siano le formule e come possiamo calcolare gli Z-SCORE con Excel.
Le due formule seguenti ci ricordano che esistono casi in cui si dispongono i valori di tutta la popolazione ed altri in cui si conoscono quelli di un campione:

Z-SCORE conExcel Z-SCORE con Excel

Gli Z-SCORE, inoltre, godono di tre interessanti proprietà:

  • La loro Media è Zero
  • Hanno una deviazione Standard pari ad 1
  • Per il modo con cui sono calcolati consentono la comparazione di serie di valori con scale di misura diverse

Z-SCORE CON EXCEL: UN CASO PRATICO

Per lo svolgimento del nostro esercizio è disponibile al seguente link il relativo data-set.

Cercheremo un indice dell’entità del punteggio più alto e del punteggio più basso in ciascuna gara, rendendoci conto di quanto “pesante” sia quel valore nella e tra le competizioni.

Di seguito vediamoli applicati alle nostre cinque gare, prima osservando i valori e poi identificando le loro formule di determinazione.

Z-SCORE conExcel

 

Z-SCORE Con Excel

Possiamo facilmente dedurre quanto segue:

 

L’articolo [EXCEL] Z-SCORE CON EXCEL proviene da Mummu Academy.

[EXCEL] Grafici Boxplot Con Excel

Se conosciamo il significato di Quartile e più genericamente di Percentile, possiamo avventurarci nella realizzazione dei GRAFICI BOXPLOT CON EXCEL.

Scopriamo insieme, passo dopo passo quanto affascinante possa essere il “dare forma” a degli indici numerici, comunicandone agli altri il loro significato intrinseco. 

Si tratta di un tipo di grafico molto affascinante, capace di sintetizzare in un’unico prospetto molte importanti informazioni. Il risultato del lavoro svolto in questo articolo sarà il seguente:

Grafici BoxPlot

 

1 – INTERVALLO INTERQUARTILE

Di particolare interesse sarà il calcolo di una misurazione legata alla differenza tra il Q3 ed il Q1 che si definisce Intervallo Interquartile.

Quest’ultimo indice, per sua natura, è particolarmente robusto: non è influenzato da quei valori estremi che vengono definiti “Outliers”.

Il significato dell’Intervallo Interquartile è simile a quello della deviazione standard: maggiore è il suo valore tanto più variabile può essere considerata una distribuzione.

Possiamo usare i dati generati nell’articolo in cui abbiamo scoperto i Percentili ed i Quartili con Excel. Per procedere nella lettura si raccomanda di leggere con attenzione l’articolo di cui al link.

Grafici BoxPlot con Excel

2 – OUTLIERS

Possiamo determinare se un valore sia così atipico da essere definito un Outlier.

Per essere un Outlier, un valore deve trovarsi “oltre” dei valori di limite individuabili tramite i quartili e gli Intervalli Interquartili definiti come IQR.

Possiamo calcolare infatti il limite inferiore e superiore per l’individuazione degli Outlier con le seguenti formule:

    1. Limite Inferiore = Q1 – (1,5 * IQR)
    2. Limite Superiore = Q3+ (1,5 * IQR)

I valori che cadono sotto il limite inferiore e sopra al limite superiore possono essere considerati degli Outlier.

Nelle figure sottostanti sono state nascoste le righe dei valori analizzati, dalla 3 alla 33 comprese, per lasciare lo spazio necessario alla visualizzazione delle sintesi.

Diamo subito uno sguardo alle formule utilizzate:

E adesso vediamo i risultati:

Nel nostro caso non esistono degli “Outliers” veri e propri in quanto sia i minimi che i massimi delle distribuzioni ricadono entro i limiti.

La Mediana, il primo ed il terzo quartile sono robusti ai valori di picco, ma non ci forniscono una indicazione su quali siano i valori di minimo e di massimo della distribuzione a cui appartengono.

Per una corretta analisi statistica dovremmo poter avere una rappresentazione grafica dei seguenti Valori di Interesse:

    1. MINIMO
    2. Q1
    3. MEDIANA
    4. Q3
    5. MASSIMO

3 – GRAFICI BOXPLOT CON EXCEL

Il grafico che permette di analizzare contemporaneamente le voci di interesse si chiama GRAFICO BOXPLOT.

L’ultima versione di Excel permette di realizzare in automatico il grafico ma ad oggi sono molte gli utilizzatori dei fogli di calcolo che possiedono versioni precedenti.

Vediamo adesso realizzare il grafico step by step, indipendentemente dalla versione del software. Potremo così meglio comprendere il vero significato di quello che stiamo realizzando.

Immaginiamo di mettere in colonna, una sopra all’altro, i valori dei quartili. Avremmo una torre, che blocco per blocco, sarà del seguente aspetto:

Grafici BoxPlot Con Excel

La zona che copre l’intervallo Q1-Q3 comprende la Mediana (che coincide con Q2) ed è robusta ai picchi.

Le zone residue sono le più influenzate dagli “Outliers” e non saranno rappresentate come “superfici” colorate nel BOXPLOT, ma da dei segmenti.

Per realizzare il grafico dobbiamo posizionare nel giusto ordine i valori che ci serviranno.

3.1 – DEFINIZIONE DEI BLOCCHI

Procederemo individuando le altezze di quelli che definiamo BLOCCHI:

Partendo dalla figura precedente andiamo a ricavare la tabella che ci serve per realizzare il grafico. I valori di base da cui ricavare tutto sono i seguenti:

    1. MIN
    2. Q1
    3. Q2
    4. Q3
    5. MAX

Andremo così a realizzare la tabella che segue con le formule specificate in prima colonna (da notare che iniziamo dalla colonna G):

3.2 – GRAFICI BOXPLOT CON EXCEL: INIZIAMO CON UN GRAFICO A PILA

Selezioniamo i valori presenti nella zona arancione ed ineriamo un Grafico a Colonne in Pila:

Si ottiene il seguente grafico:

Con un click sx del mouse selezioniamo la parte inferiore delle pile, quella azzurra

Con un click destro del mouse selezioniamo la voce “Formato serie di Dati

Scegliamo la voce “nessun riempimento” dal menu Formato serie di Dati:

3.3 – GRAFICI BOXPLOT CON EXCEL: LE BARRE DI ERRORE

Facciamo click sulla parte Arancione

Seguiamo i menù come da immagine seguente

Clicchiamo su “Altre Opzioni” e poi su “Personalizzato” e “Specifica Valore”

Otteniamo

Seguiamo adesso le figure con estrema attenzione per capire cosa selezionare per i valori di errore:

  • Selezioniamo per primi i Valori di Errore Positivi, quelli cioè che stanno nella parte superiore della pila

  • Selezioniamo adesso i valori che stanno nella parte inferiore della pila, ovvero i Valori di Errore Negativi

Ecco il risultato:

3.4 – GRAFICI BOXPLOT CON EXCEL: I NOMI ALLE SERIE DI DATI

Diamo adesso i nomi alle serie ed alla legenda. Click destro su grafico e poi seleziona dati

Clicchiamo su Modifica della sezione “Etichette asse orizzontale”:

e poi

Selezioniamo adesso le celle da cui prendere il nome delle Etichette:

Confermando vediamo con sia possibile ottenere dei bellissimi Grafici BoxPlot con Excel:

Grafici BoxPlot Con Excel

Se hai trovato interessante questo articolo potresti provare a leggere anche:

Potresti inoltre trovare utili i seguenti corsi:

L’autore di questo è Gabriele di Ruvo, appassionato di informatica e dell’utilizzo di Excel come insostituibile strumento di Analisi dei Dati.

L’articolo [EXCEL] Grafici Boxplot Con Excel proviene da Mummu Academy.

Percentili e Quartili Con Excel

In questo articolo cerchiamo di meglio comprendere i Percentili e Quartili con Excel.

Quando parliamo dei PERCENTILI ed identifichiamo un valore come rappresentante del percentile “K”, stiamo dicendo che il “K-percento” dei valori rilevati nella distribuzione, sono al di sotto o uguali al valore di riferimento osservato ed associato a “K”.

In altri termini, un valore rappresentante una posizione percentile pari al 54%, ha il 54% dei valori che sono più piccoli o pari a sé.

In tal senso, è facilmente intuibile, che la MEDIANA rappresenta il 50-esimo percentile.

I quartili rappresentano dei particolari percentili che dividono la popolazione in quarti:

  • Q1 è il primo quartile e si colloca al 25%
  • Q2 è il secondo quartile e si colloca al 50%
  • Q3 è il terzo quartile e si colloca al 75%
  • Q4 è il quarto quartile e si colloca al 100%

1) PERCENTILI E QUARTILI CON EXCEL: DISTRIBUZIONI NON RAGGRUPPATE IN CLASSI

Applichiamo il concetto dei quartili al nostro esercizio, utilizzando questo data-set.

Percentili e Quartili con Excel - Fig. 1

Le formule utilizzate sono le seguenti:

Percentili e Quartili con Excel - Fig. 2

2) CALCOLO PER DISTRIBUZIONI DI FREQUENZA RAGGRUPPATE IN CLASSI

Avendo chiaro il concetto di Percentile e Quartile spiegato in precedenza, possiamo dire che nel nostro caso diventa fondamentale il concetto di FREQUENZA CUMULATA:

  • La Mediana, ad esempio, sarà determinata prendendo come base il punto in cui si trova il 50% delle frequenze cumulate.
  • Il primo Quartile sarà in corrispondenza del 25% e così via per gli altri indici.

2.1) STEP LOGICI

Cerchiamo adesso di suddividere in step logici i punti da svolgere per arrivare alla determinazione degli indici desiderati:

  1. Calcoliamo le Frequenze Cumulate affiancando le Classi e le loro frequenze
  2. Determiniamo le Ampiezze delle Classi
  3. Individuiamo  il valore del Percentile desiderato: lo si determina moltiplicando il Totale delle Frequenze per la percentuale di riferimento del Percentile (per il Primo Quartile = N * 25%)
  4. Il valore del punto 3 si troverà all’interno di una classe facilmente identificabile nel prospetto
  5. Il valore del punto 3 “sconfinerà” nella singola classe di appartenenza per una determinazione pari alla differenza tra il valore stesso e la base della classe
  6. La classe presa in considerazione ha una frequenza che viene messa percentualmente in confronto con il valore della differenza di cui al punto 5
  7. La percentuale del punto 6 viene applicata sull’ampiezza della classe di appartenenza identificando il valore decimale di copertura della classe di appartenenza
  8. Si somma adesso il valore del punto 7 a quello che identifica la base della classe e si determina così il Primo Quartile
  9. Si procede in modo analogo per tutti gli altri Percentili o Quartili

2.2) ESERCIZIO GUIDATO

Vediamo adesso come si applicano i punti precedenti ad un caso pratico: è qui possibile scaricare il dataset

Percentili e Quartili con Excel - Fig. 3

Cerchiamo adesso di riportare logicamente i valori dell’intervallo B27:B32 riferendoli ai  9 punti precedenti:

  1. Nella cella B27 (VALORE) moltiplica 125 (tot delle freq in C23) per il 25% (primo quartile)
  2. Nella cella B28 (SCONFINAMENTO) si vede di quando 31,25 entra nella sua classe di appartenenza. Si effettua la differenza tra 31,25 e 15 (il valore 15 è il “pavimento” della classe)
  3. Nella cella B29 (COPERTURA CLASSE) ci si chiede quanto percentualmente il valore di sconfinamento pari a 16,25 copre il valore delle frequenze della classe che è pari a 17. Si capisce che la frequenza della classe viene coperta per una percentuale del 95,59% che corrisponde a 16,25 su 17.
  4. Nella cella B30 (AMPIEZZA CLASSE) precisiamo l’ampiezza della classe che nel nostro caso è pari a 5
  5. Nella cella B31 (COP. AMP. CLASSE) ci chiediamo quanto sia il 95,59% di una classe con ampiezza pari a 5. In sostanza, le percentuali definite fin qui solo sui valori di frequenza vengono traslate sull’ampiezza della classe, ottenendo un valore pari a 4,78 sui 5 che rappresentano l’ampiezza della classe.
  6. Nella cella B32 (ALTEZZA) si è in grado di capire quale sia la “stima” dell’altezza corrispondente al Primo Quartile. Si somma infatti 4,78 alla base della classe, ottenendo un valore pari 179,78. Si è appena determinato per interpolazione il valore del Primo Quartile in una distribuzione di frequenza per classi.

Mettiamo adesso a disposizione le formule utilizzate:

Percentili e Quartili con Excel - Fig. 4

Se il calcolo Percentili e Quartili con Excel ti ha interessato, possiamo consigliarti anche il seguente in cui si tratta la MEDIA, la MODA e la MEDIANA, sempre con Excel.

L’articolo Percentili e Quartili Con Excel proviene da Mummu Academy.

[EXCEL] VARIANZA E DEVIAZIONE STANDARD

A chi non è capitato di dover sintetizzare, con un unico valore, una serie di valori? Quale indice scegliere? Media Varianza e Deviazione Standard sono idonei?

Prima o poi tutti ci imbattiamo in questa esigenza e la soluzione che più spesso viene utilizzata è calcolare la MEDIA ARITMETICA.

La MEDIA, in realtà non è in grado di darci un’idea di come i valori siano distribuiti intorno ad essa stessa. Un brevissimo esempio può aiutarci a capire meglio.

Immaginiamo che due persone svolgano nel tempo 10 compiti scritti ciascuno e che la loro media dei voti sia pari a 6. Siamo in grado, disponendo della sola media, di definire il loro rendimento nel tempo? La risposta è no: il primo studente potrebbe avere 6 come media perché ha preso un voto pari a 6 per ciascun compito, mentre il secondo potrebbe aver preso delle insufficienze alternate a degli ottimi voti.

Con l’esempio precedente percepiamo il limite  di un indice di posizione centrale quale la MEDIA.

Per fortuna esistono gli indici di dispersione quali la VARIANZA e la DEVIAZIONE STANDARD che sono in grado di superare il limite appena identificato. Tali indici ci danno un precisa valutazione di come gli indici siano distribuiti intorno alla MEDIA della distribuzione a cui appartengono.

VARIANZA E DEVIAZIONE STANDARD: PRIMI PASSI

Passiamo adesso all’azione con un esercizio guidato che usi Excel come strumento per capire davvero come stiano le cose.

Utilizziamo adesso il Data-set disponibile a questo link: su ogni riga si hanno i risultati di giocatori diversi su cinque gare da loro disputate.

Nella figura abbiamo già calcolato la MEDIA, la MEDIANA e la MODA, anche se per adesso utilizzeremo la MEDIA.

VARIANZA E DEVIAZIONE STANDARD: GLI SCARTI DALLA MEDIA

Dal momento che desideriamo conoscere come i dati si distribuiscono intorno alla MEDIA, per ciascuno di essi, calcoliamo il relativo SCARTO DALLA MEDIA:

Notiamo che la SOMMA DEGLI SCARTI è pari a zero, sperimentando in modo pratico una delle principali proprietà della MEDIA.

In realtà la somma degli scarti pari a zero, ci dà qualche problema nel momento in cui desiderassimo calcolare la MEDIA DEGLI SCARTI. Il numeratore della formula, infatti, andrebbe zero per qualsiasi distribuzione.

Capiamo adesso perché nel prossimo passaggio eleveremo a quadrato ogni scarto: sicuramente saranno valori positivi.

VARIANZA E DEVIAZIONE STANDARD: GLI SCARTI AL QUADRATO

La SOMMA DEGLI SCARTI AL QUADRATO non è pari a zero a meno che tutti gli scarti siano zero.

LA MEDIA DEGLI SCARTI AL QUADRATO

Calcolo adesso la MEDIA DEGLI SCARTI AL QUADRATO: divido la loro somma per il numero dei partecipanti alla gara di riferimento:

Nel passaggio precedente abbiamo scoperto la VARIANZA delle distribuzione esaminate.

Adesso, a posteriori, vediamo quale sia la formula che ne sintetizza i passaggi:

Al numeratore abbiamo la sommatoria degli scarti, mentre al denominatore il loro numero totale.

La VARIANZA ha tuttavia una scala diversa da quella dei numeri originari in quanto deriva da un elevamento al quadrato. Capiamo perché ne calcoliamo la radice quadrata, ottenendo la DEVIAZIONE STANDARD o SCARTO QUADRATICO MEDIO:

LA RADICE QUADRATA DELLA VARIANZA

Di seguito i valori ed i loro indici per ciascuna delle singole serie:

Se osserviamo la DEVIAZIONE STANDARD notiamo che esiste un bel divario tra la terza serie e la quinta. Cosa significa?

La terza serie, avendo una DEVIAZIONE  STANDARD più bassa sarà quella i cui valori sono più schiacciati intorno alla media, soprattutto nei confronti della quinta che ha il valore più alto.

In questo tutorial non abbiamo utilizzato intenzionalmente le formule che in Excel permettono il calcolo diretto della Varianza e della Deviazione Standard. Lo scopo era quello di spiegare passaggio dopo passaggio il loro significato con l’aiuto di Excel.

Se sei interessato a questi argomenti potresti trovare utile il nostro corso per un UTILIZZO AVANZATO DI EXCEL. Tieni presente che i principali indici statistici sono molto utili anche per chi intende utilizzare al meglio i SOCIAL per promuovere la propria attività.

 

 

L’articolo [EXCEL] VARIANZA E DEVIAZIONE STANDARD proviene da Mummu Academy.

[excel] Dati N elementi scrivere tutte le combinazioni con ripetizioni possibili

Partiamo da un problema semplice. Dati N elementi vogliamo trovare con Excel tutte le combinazioni possibili, tenendo conto delle ripetizioni e del fatto che l’ordine non conti (in matematica si parlerebbe di combinazioni con ripetizioni). 

Ammettiamo per esempio di avere 3 elementi: A, B e C. Da questi 3 elementi possiamo ottenere 10 combinazioni con ripetizioni in totale: AAB, AAC, ABB, ABC, ACC, BBB, BBC, BCC, CCC, AAA

Notiamo che non sono presenti combinazioni come ABA oppure BAA che sono equivalenti a AAB, dal momento che l’ordine non conta.

Vediamo adesso come generare questi dati in Excel. Per motivi computazionali limiteremo l’esercizio ad un massimo di 6 elementi.

Anzitutto scriviamo i nostri elementi, noi lo faremo nel modo seguente partendo dalla cella N1.

In questo modo abbiamo creato un riferimento numerico (N2:N7) con gli oggetti che vogliamo combinare (O2:O7).

Adesso vogliamo scrivere le combinazioni a partire dagli elementi numerici, mettendo in colonne i singoli valori combinati. Anzitutto scriveremo tutte le combinazioni in colonne. Partendo da questo presupposto nella prima colonna il primo valore si ripeterà N^(N-1) volte. Se avessimo N = 2 il primo elemento si ripeterebbe solo 2 volte, se avessimo N = 3 il primo elemento si ripeterebbe per 9 volte ecc. Il secondo elemento si ripeterà N^(N-2) volte e così via per le colonne successive.

Spostiamoci in A1 e digitiamo quindi la seguente formula:

=SE(RIF.COLONNA()<=CONTA.VALORI($O:$O)-1;SE(RIF.RIGA()<=POTENZA(CONTA.VALORI($O:$O)-1;CONTA.VALORI($O:$O)-1);(RESTO(ARROTONDA.PER.DIF(RIF.RIGA()/(POTENZA(CONTA.VALORI($O:$O)-1;CONTA.VALORI($O:$O)-1-RIF.COLONNA()));0);CONTA.VALORI($O:$O)-1)+1);"");"")

Con il SE verifichiamo se mostrare o meno i valori in base alla quantità di elementi inseriti. Ricordiamoci che vogliamo sviluppare l’esercizio per un massimo di 6 elementi possibili.

Espandiamo la formula sulle colonne A:F, per ridurre l’impatto sulle prestazioni possiamo inserire la formula solo in A1:F52000 in modo da coprire il massimo numero di combinazioni possibili (6*6^5 = 46.656).

Fatto questo abbiamo generato tutte le combinazioni possibili con gli elementi dati.

Adesso vogliamo contare le ripetizioni. Per farlo distribuiamo i 6 parametri in altrettante colonne e per ogni riga contiamo quante volte appare il parametro. Se avessimo ABC come valori, allora la combinazione AAB corrisponderebbe ad un codice tipo 210, dove 2 è il numero di volte di A, 1 quelle di B e 0 quelle di C. Nello stesso esempio ABA = 210 e AAC = 201 ecc.

Posizioniamoci quindi in G1 e scriviamo:

=CONTA.SE($A1:$F1;RIF.COLONNA()-6)

Espandiamo la formula su G1:l52000.

Per comodità in M1 scriviamo le 6 colonne concatenate usando:

=G1&H1&I1&J1&K1&L1

Espandiamo la formula su M1:M52000.

In R1 traduciamo i numeri nei valori che abbiamo scelto come oggetti usando:

=SE.ERRORE(CERCA.VERT(A1;$N$1:$O$7;2;FALSO);"")&SE.ERRORE(CERCA.VERT(B1;$N$1:$O$7;2;FALSO);"")&SE.ERRORE(CERCA.VERT(C1;$N$1:$O$7;2;FALSO);"")&SE.ERRORE(CERCA.VERT(D1;$N$1:$O$7;2;FALSO);"")&SE.ERRORE(CERCA.VERT(E1;$N$1:$O$7;2;FALSO);"")&SE.ERRORE(CERCA.VERT(F1;$N$1:$O$7;2;FALSO);"")

E infine in Q1 estraiamo solo le combinazioni univoche, utilizzando:

=SE(CONTA.SE($M$1:M1;M1)<2;R1;"")

Contiamo cioè se la combinazione corrente è presente 2 o più volte nelle combinazioni precedenti. Se lo è non la mostriamo (è già stata mostrata!) altrimenti la mostriamo.

Espandiamo tutto fino alla riga 52000.

Infine verifichiamo il tutto inserendo in N9 il conteggio dei valori trovati:

{=SOMMA(SE(LUNGHEZZA(Q:Q)=CONTA.VALORI(O:O)-1;1;0))}

Attenzione! Le parentesi graffe sottintendono il calcolo matriciale, per cui per eseguire la formula bisogna farlo premendo CTRL+SHIFT+INVIO.

E in N11 inseriamo il valore teorico datoci dal calcolo combinatorio rispetto al numero di oggetti interessati:

=FATTORIALE(2*(CONTA.VALORI(O:O)-1)-1)/(FATTORIALE(CONTA.VALORI(O:O)-1)*FATTORIALE(CONTA.VALORI(O:O)-2))

Se tutto è andato bene nella colonna Q otteniamo le combinazioni filtrate che desideravamo.

Per completezza qui si può scaricare il file excel con l’esercizio svolto.

L’articolo [excel] Dati N elementi scrivere tutte le combinazioni con ripetizioni possibili proviene da Mummu Academy.

Creare un calendario per l’anno 2020 utilizzando Microsoft Excel

Vediamo come creare un calendario per l’anno 2020 utilizzando Microsoft Excel, dividendo i mesi e segnando i giorni feriali e festivi per ogni mese in modo automatico.

1. Apriamo Excel ed in un foglio nuovo “uniamo e centriamo” le celle da A1 ad A7 in modo da avere un’intestazione per il nome del mese.

Creare un calendario per l'anno 2020 con Excel- Step 01

2. Ci posizioniamo adesso nella cella A2 da dove, in orizzontale, andiamo a scrivere i giorni della settimana.

3. Il primo giorno della prima settimana del 2020 è il 30/12/2019 e possiamo quindi inserire la data 30/12/2019 nella cella A3

4. In orizzontale, da sinistra verso destra, i giorni incrementano di uno. Si passa infatti dal 30 dicembre al 1° gennaio e così via.
Gestiamo adesso questo incremento unitario con una formula: ci posizioniamo in B2 dove scriviamo la formula =A3+1.

5. Dopo aver premuto invio e convalidato la formula, copiamo ed incolliamo verso destra generando i seguenti valori

6. In verticale ci si muove di sette giorni alla volta e quindi scriveremo in A4 la formula =A3+7
Risulta infatti che il lunedì successivo a quello di partenza sarà sette giorni dopo, e lo stesso accade per tutti gli altri giorni.

7. Dopo aver convalidato la formula eseguiamo un copia incolla verso destra ottenendo i seguenti valor

8. Partendo adesso dalla cella A4 e seguiamo dei copia incolla sia in verticale che in orizzontale.
In questo modo, completiamo la matrice del mese di gennaio del calendario 2020

9. Per creare la matrice del mese successivo a quello considerato, selezionare e copiare tutto il mese corrente

In pochi passi il primo mese è a punto! Adesso manca poco per creare un calendario per l’anno 2020 utilizzando Microsoft Excel. Continua a leggere con attenzione i prossimi punti, a partire dal seguente.

10. Lasciare una riga vuota ed incollare rispettando gli incolonnamenti

11. Rinominiamo adesso il mese nel modo appropriato

12. Il nuovo mese appena creato non è coerente: elenca ancora i giorni membri del mese precedente.
Inseriamo manualmente, senza cioè nessuna formula, il primo giorno della prima settimana dello stesso.

Usando altre parole, si deve digitare il corretto e coerente valore nella prima cella in alto a sinistra della matrice dei giorni.

Nel nostro caso il primo giorno della prima settimana del mese generato, e cioè Febbraio, è il 27/01/2020.

Anche se appartiene al mese precedente è comunque il primo giorno della settimana del nuovo mese.

Non appena digitiamo il valore nella prima cella in alto a sinistra del mese di riferimento, Excel popola gli altri giorni della matrice.

13. Per completare tutto il calendario dobbiamo ripetere i punti da 9 a 12 fino alla creazione de mese di dicembre 2020.

Passiamo adesso alla formattazione dei giorni di pertinenza dei mesi e dei festivi.

Come nei calendari cartacei, formatteremo dei colori appropriati i giorni che non appartengono al mese di riferimento.

Quasi sempre, infatti, nella matrice di un mese avremo dei giorni di “disturbo e collegamento” sia verso il mese precedente che quello successivo.

Alla base del punto precedente vi è la considerazione che intenzionalmente non generiamo celle vuote.

Utilizzo semplicemente i colori idonei e della gradazione desiderata, oltre che alla giusta dimensione del carattere.

La linea tratteggiata verticale dell’ultima colonna della figura può apparire dopo un’anteprima di stampa, ma non verrebbe stampata comunque.

La prima fase di realizzazione può essere ritenuta conclusa, ma, se lo desideri, puoi leggere ancora qualche riga. Scoprirai alcune interessanti formattazioni per creare un calendario per l’anno 2020 utilizzando Microsoft Excel.

14. Nel caso in cui desideri un calendario con i soli numeri dei giorni procedo come segue. Seleziono tutto il blocco delle date

15. Faccio adesso un click destro su di un punto qualsiasi della selezione e dal menu che appare scelgo FORMATO CELLE  NUMERO  PERSONALIZZATO

16. Al posto di Standard scrivo “gg” (senza virgolette).

17. Clicco adesso su ok ed ottengo il seguente risultato

Complimenti! Se sei arrivato fin qui significa che sarai riuscito a creare un calendario per l’anno 2020 utilizzando Microsoft Excel.

Questo articolo ti è piaciuto?  Ne puoi trovare altri, sempre per Excel cliccando qui.

Ricorda che se sei uno studente di uno dei corsi inerenti al mondo Excel Mummu Academy, puoi accedere al tuo spazio personale nella nostra piattaforma di E-Learning. Nell’area riservata saranno disponibili interessanti video ed altri materiali di approfondimento.

L’articolo Creare un calendario per l’anno 2020 utilizzando Microsoft Excel proviene da Mummu Academy.

[Excel] Sommare tariffe giornaliere diverse per data usando le matrici in Excel

Partiamo con uno dei nostri più classici esercizi in Excel. Immaginiamo di avere la seguente tabella di dati, riguardanti i pernottamenti di un gruppo di clienti in un albergo.

elenco clienti albergo excel

Da un’altra parte abbiamo una tabella con le tariffe divise per mese (per comodità abbiamo i mesi già numerati).

A questo punto vogliamo calcolare quanto spenderà ogni cliente sapendo la sua data di ingresso nell’albergo e il numero di pernottamenti.

La soluzione più banale, sebbene non perfettamente corretta, sarebbe quella di prendere come tariffa di riferimento la tariffa di ingresso e moltiplicarla per il numero di notti. Nel caso specifico nella cella D2 andrebbe la seguente formula:

=CERCA.VERT(MESE(B2);$F$2:$G$13;2;FALSO)*C2

Da questo, applicando la formula su D2:D6, otterremmo un risultato come il seguente:

Inutile ribadire come questo risultato non sia corretto, perché in molti casi al passaggio di mese cambia anche la tariffa. Per esempio Chiara Celesti, che comincia il suo soggiorno il 15 dicembre 2019, lo finirebbe l’8 gennaio 2020. Nel caso specifico pagherebbe 45€ per 17 notti e 50€ per 8 notti, per un totale di 1.165€ anziché 1.125€.

Come possiamo quindi risolvere questo problema?

Una possibile soluzione deriva dall’utilizzo delle matrici, con il seguente ragionamento.

Anzitutto spostiamoci in K1 e creiamo la seguente tabella di supporto:

Inseriamo in K2 uno 0, e in K3 la formula  =K2+1  trascinando fino a K42 in modo da ottenere come massimo valore 40. Questo sarà anche il numero massimo di pernottamenti + 1 che potrà fare una persone. Cioè calcoleremo i pernottamenti fino ad un massimo di 41 notti (partendo da 0 fino a 40 compresi). Se volessimo prendere in considerazione più pernottamenti sarebbe sufficiente allungare ulteriormente la formula.

Il ragionamento che voglio fare è il seguente: confrontare l’elenco dei giorni di pernottamento con le date e sovrapporli ad una matrice di costi. Il punto di arrivo è una MATRICE come la seguente:

Figura 1

Questa matrice è riferita ai pernottamenti di Anna Bianchi. Lei comincia la vacanza il 20 dicembre e la finisce il 3 gennaio. Significa che copre 12 giorni di dicembre e 3 di gennaio. Usando la matrice di K2:K42 sommiamo alla data di partenza da 0 fino a n giorni, ovvero da 0 a 14 giorni, arrivando così ad avere tutte le date dal 20 dicembre al 3 gennaio.

Rispetto alla matrice precedente avremmo una tabella come quella che segue:

Matrici in Excel
Matrici in Excel
Figura 2

Nella prima colonna abbiamo la data di ingresso ripetuta su tutta la durata, nella seconda colonna abbiamo i giorni da 0 a 14, fino cioè a 15 escluso (che è il numero di pernottamenti). Se sommiamo alla data di ingresso ciascun giorno otteniamo le relative date di pernottamento. Usando la funzione MESE() su ciascuna data otteniamo quindi la quarta colonna con la codifica del mese. Infine eguagliamo la colonna dei mesi di pernottamento con la riga di tutti i mesi, ottenendo la matrice di uguaglianze (vero e falso) come di sopra.

Fatto questo andremo a prendere una matrice di prezzi fatta nel modo seguente:

Matrici in Excel
Matrici in Excel
Figura 3

Sovrapponendo le due matrici, solo dove nella prima abbiamo VERO otterremo una matrice così:

Imparare utilizzare Excel
Imparare utilizzare Excel
Figura 4

Ai FALSO abbiamo sostituito uno 0. A questo punto vediamo che facendo un SOMMA() su questa matrice possiamo calcolare esattamente la tariffa che dovrà pagare Anna Bianchi.

Per realizzare tutto questo con una singola formula utilizzeremo le MATRICI di Excel nel modo seguente.

Anzitutto otteniamo la prima colonna dei mesi come nella Figura 1 utilizzando la seguente formula:

=MESE(B2+$K$2:$K$42)

Se premiamo F9 sulla formula evidenziata dovremmo vedere una matrice come la seguente per il primo caso (quello del 21 giugno 2019)

{6.6.6.6.6.6.6.6.6.6.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7.7}

Qui vediamo che vengono calcolati tutti i mesi per i prossimi 40 giorni a partire dalla data di partenza, mentre Mario Rossi rimane solo per 5 giorni. Vogliamo quindi a 0 i giorni superflui e integriamo con la seguente formula:

=SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)

Il risultato diventerà:

{6.6.6.6.6.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0}

Così abbiamo ottenuto la colonna dei mesi della Figura 1. Per ottenere la matrice la eguagliamo ai mesi in F2:F13, siccome però vogliamo ottenere una matrice rettangolare dobbiamo eguagliarli ad una matrice riga, mentre F2:F13 è una matrice colonna. A tale proposito useremo la funzione MATR.TRASPOSTA() che traspone una matrice colonna in una matrice riga.

=SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)=MATR.TRASPOSTA($F$2:$F$13)

Ancora F9 e otteniamo:

{FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\VERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO.FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO}

In questo ginepraio di FALSO possiamo notare alcuni VERO all’inizio, in corrispondenza con il mese in colonna e quello in riga.

A questo punto usiamo il SE nel modo per cui se nella matrice c’è un VERO allora prendi il prezzo dalla matrice dei prezzi, simile a quella della Figura 3 che creiamo usando:

=MATR.PRODOTTO($L$2:$L$42;MATR.TRASPOSTA($G$2:$G$13))

Questa particolare operazione darebbe infatti questo risultato:

{50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45.50\50\55\55\55\60\60\60\50\50\45\45}

Uniamo le due cose nel modo seguente:

=SE(SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)=MATR.TRASPOSTA($F$2:$F$13);MATR.PRODOTTO($L$2:$L$42;MATR.TRASPOSTA($G$2:$G$13));0)

Questa formula darebbe (sempre usando F9):

{0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\60\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0.0\0\0\0\0\0\0\0\0\0\0\0}

Da notare come compaiono esattamente 5 numeri 60, corrispondenti alla tariffa di giugno che deve pagare Mario Rossi.

Applichiamo a questa matrice la funzione SOMMA() e premiamo SHIFT+CTRL+INVIO per applicare il calcolo matriciale al risultato.

La formula finale sarà:

=SOMMA(SE(SE($K$2:$K$42<C2;MESE(B2+$K$2:$K$42);0)=MATR.TRASPOSTA($F$2:$F$13);MATR.PRODOTTO($L$2:$L$42;MATR.TRASPOSTA($G$2:$G$13));0))

E dovremmo vedere qualcosa di simile nel nostro foglio:

Imparare utilizzare Excel
Imparare utilizzare Excel
Se abbiamo fatto tutto bene il numero risultato sarà dunque:

Imparare utilizzare Excel
Imparare utilizzare Excel
Con i valori corretti per ogni cliente su qualunque pernottamento.

Otto Motivi per cui dovresti fare un corso Excel a Firenze!

Maggiori Informazioni sul corso Excel a Firenze!!

Corso Excel Avanzato

[EXCEL] MEDIA, MEDIANA E MODA IN EXCEL

Davanti ad un report stracolmo di dati, anche se ben elencati e categorizzati, non è semplice raccogliere delle informazioni. Dato ed informazione sono due concetti con numerose intersezioni, ma con molte differenze. Il dato, di per sé non è detto che rappresenti un’informazione. Potrebbe, infatti, essere rappresentativo di determinazioni non pertinenti alla mia analisi oppure non essere aggregato in una forma di sintesi idonea.

La STATISTICA DESCRITTIVA è un ottimo alleato per raggiungere il nostro scopo. Esaminiamo adesso in modo semplice  alcuni basilari indici di posizione che la Statistica descrittiva ci offre.

Cerchiamo di essere più chiari con un semplice esempio. Se esportassi dal mio gestionale un lungo elenco di prodotti venduti con le relative quantità avrei soltanto dei dati. Se riuscissi ad aggregare gli stessi, ottenendo il numero di volte che li ho venduti in un determinato periodo e identificando anche le relative quantità vendute, avrei quelle che per me sarebbero delle informazioni sulle vendite. Esercitiamoci con la figura di seguito riportata.Media Aritmetica, Mediana, Moda in Excel - Mummu Academy

Cliccando qui potrai effettuare il download del file.

In questo esempio abbiamo dei record relativi alle vendite di occhiali. Proviamo ad allenarci i più usati INDICI DI POSIZIONE DI BASE della statistica descrittiva. Notiamo che in questo esempio trattiamo dei dati che non sono raggruppati in classi di appartenenza. Tratteremo gli indici di posizione per dati raggruppati per classi in un apposito articolo.

MEDIA ARITMETICA

Si tratta del valore di posizione più utilizzato. Dato un insieme di “n” elementi, la media aritmetica, utilizzata per le variabili quantitative, è quel valore che sostituito nella somma degli “n” elementi lascia invariato il risultato finale. Nell’esempio riportato nella figura, la media aritmetica è rappresentata dalla somma dei valori delle determinazioni divise per il numero univo degli elementi stessi. La più immediata applicazione di questo indice nel nostro esempio è il calcolo della media aritmetica dell’importo delle vendite. Sono state eseguite “n” vendite dove n=24. Il numero di occhiali venduti è infatti pari a 24. La somma delle “n” determinazioni è pari ad € 2.886,00. La media aritmetica è facilmente determinabile dividendo l’importo di € 2.886,00 per 24, ottenendo come risultato un valore pari ad € 119,42

In Excel è sufficiente individuare la serie di valori di cui si desidera la MEDIA ARITMETICA utilizzando la seguente funzione:

=MEDIA(E2:E25) il cui risultato è pari ad € 119,42

Proviamo adesso a verificare il risultato identificando con altre formule i singoli fattori. Per determinare il numero degli elementi di cui desideriamo la media utilizziamo una semplice funzione di conteggio:

=CONTA.NUMERI(E2:E25) il cui risultato è pari a 24

Si tratta di una funzione che conteggia il numero di volte che in un intervallo di celle il loro contenuto è rappresentato da dei numeri

Utilizzerò la funzione di SOMMA per ottenere la somma delle “n” determinazioni appartenenti all’insieme dei valori di cui desideriamo calcolare la media

=SOMMA(E2:E25) il cui risultato è pari ad € 2.886,00

La funzione che indica quanti valori numerici appartengono all’insieme è invece la seguente:

=CONTA.NUMERI(E2:E25) il cui risultato è pari a 24

Posso procedere alla verifica del risultato della media mediante la seguente formula

=SOMMA(E2:E25)/CONTA.NUMERI(E2:E25) il cui risultato è pari ad € 119,42 e coincide il precedente calcolo della media aritmetica.

INTERNALITÀ DELLA MEDIA ARITMETICA

Una delle principali caratteristiche della media aritmetica è quella delle INTERNALITÀ. Il valore della media è sempre compreso tra il minimo ed il massimo dei valori appartenenti all’insieme di calcolo.

Possiamo verificare la precedente affermazione utilizzando le funzioni di MINIMO e MASSIMO.

=MAX(E2:E25) il cui risultato è pari ad € 150,00

=MIN(E2:E25) il cui risultato è pari ad € 90,00

Effettivamente il valore della media aritmetica pari ad € 119,42 è compreso tra i due valori di minimo e di massimo.

TRASFERIBILITÀ DELLA MEDIA ARITMETICA

Si parla di TRASFERIBILITÀ’ della media aritmetica quando si dice che sostituendo il valore della media a quello delle singole determinazioni, il risultato non cambia. Lo possiamo dimostrare con la seguente equazione

=MEDIA(E2:E25)*CONTA.NUMERI(E2:E25)=SOMMA(E2:E25) il cui risultato è pari a VERO

MEDIA ARITMETICA COME BARICENTRO DELL’INSIEME DEI VALORI

Sarà divertente verificare anche un’altra proprietà della media aritmetica, ovvero il BARICENTRO: se sommiamo tutti gli scarti prodotti tra i singoli valori e la media dell’insieme, la loro somma sarà pari a 0.

Potremmo verificare il risultato con una formula matriciale, ma questo tipo di formule verranno introdotte in altri articoli del nostro percorso. Per il momento risolviamo la nostra verifica aggiungendo una colonna alla nostra tabella in cui calcoliamo le singole differenze tra gli elementi dell’insieme e la loro media: gli SCARTI. Nel nostro esercizio esiste una infinitesimale approssimazione di calcolo praticata da Excel che disturba il valore di verifica se si considerano più di 12 decimali. Useremo quindi la seguente formula:

=TRONCA(SOMMA(F2:F25);12) il cui valore è pari a 0

MEDIANA

Dopo aver scoperto la MEDIA ARITMETICA ed alcune delle sue proprietà, continuiamo il nostro viaggio analizzando l’altro indice di posizione della statistica descrittiva, la MEDIANA. Si tratta di un indice che, dati i valori numerici di un insieme ordinati in modo crescente, determina quali di questi sia in una posizione centrale della serie crescente e che abbia lo stesso numero di valori “sotto” e “sopra”.

Nel nostro esempio possiamo calcolare la MEDIANA per gli IMPORTI. Si deve procedere all’ordinamento delle righe in base alla colonna E, quella degli IMPORTI.

Prima di procedere all’ordinamento aggiungo una colonna che identifichi le vendite prima di riordinarle

Per ordinare la matrice selezioniamo l’intervallo A1:G25 e clicchiamo sul pulsante ordina della scheda DATI e seleziono poi come criterio di ordinamento il campo IMPORTO in modo crescente.

Il risultato dell’ordinamento sarà il seguente:

La colonna ID mi permetterà in qualsiasi momento di tornare alla distribuzione di valori iniziale (alla fine della lettura, per esercizio, provate a riordinare il file utilizzando il campi ID… è probabile che troverete alcune simpatiche difficoltà da superare…)

Utilizziamo adesso la funzione MEDIANA di Excel, scrivendo:

=MEDIANA(E2:E25) il cui risultato è PARI ad € 119,50

Abbiamo identificato il valore di mediana ma se controlliamo la nostra colonna “E” non troviamo un tale valore. La spiegazione risiede nel fatto che la mediana di un insieme pari di valori è pari alla semisomma dei “due” valori centrali della serie ordinata. Nel nostro caso Excel ha effettuato la media tra il valore 119 ed il valore 120 presenti rispettivamente in riga 12 e 13 della nostra matrice. Attenzione al fatto che si contano le righe della matrice selezionata e non del foglio di Excel. La nostra matrice ha la prima riga in quella che è la seconda del foglio di calcolo. Le righe sono colorate a manualmente, rinunciando alla FORMATTAZIONE CONDIZIONALE BASATA SU FORMULE.

Possiamo usare questa caratteristica della funzione MEDIANA per eseguire una verifica manuale come abbiamo fatto per la MEDIA. Abbiamo compreso che per la MEDIANA abbiamo bisogno di un elenco ordinato di valori e del loro INDICE DI RIGA. Possiamo aggiungere, per comodità, una colonna in cui viene riportato il numero di riga del valore. Utilizziamo la formula

=RIF.RIGA(E2)-1 poiché le righe della nostra matrice sono sfalsate di una posizione nei confronti del foglio

Per poter svolgere manualmente il calcolo della MEDIANA è determinante conoscere se il numero dei valori presenti nell’insieme è pari o dispari. Possiamo dire che se il numero dei valori diviso per due non ha resto, allora sono un numero pari, diversamente sono dispari. Utilizziamo la funzione SE e la funzione RESTO nella cella E35:

=SE(RESTO(CONTA.NUMERI(E2:E25);2)=0;”PARI”;”DISPARI”) che darà PARI come risultato

Dato che le righe sono in numero PARI la MEDIANA sarà la semisomma del valore presente in riga 12 e riga 13 ed in colonna E.

Come possiamo prendere i due valori in modo automatico e dipendentemente che il numero dei valori sia PARI o DISPARI? Utilizziamo la funzione seguente:

=SE(E35=”PARI”;(INDICE(E2:E25;E31/2)+INDICE(E2:E25;E31/2)+1)/2;INDICE(E2:E25;INT(E31/2)+1))

In sintesi la funzione appena descritta ragiona nel modo che segue: se il numero di valori sono PARI effettua la semisomma tra i due valori centrali, mentre se sono DISPARI prende soltanto il valore centrale

MODA

L’ultimo indice di posizione che esaminiamo è la MODA

La moda ci dice qual è il valore dell’insieme che ha la maggiore frequenza e che quindi si ripete più volte.

Nel nostro esempio possiamo adesso lavorare su dei valori qualitativi e sulle loro frequenze. Useremo la funzione CONTA.SE per identificare quante volte sono stati venduti gli occhiali ai tre diversi soggetti acquirenti: Bambino, Ragazzo, Adulto. Scriviamo, in L2, la seguente formula per calcolare la frequenza delle vendite ai diversi tipi di cliente:

=CONTA.SE($C$2:$C$25;K2)

Copieremo verso il basso la formula in modo che copra i tre possibili clienti. Otterremo così quanto in figura:

Il valore massimo presente nell’intervallo L2:L4 rappresenta la MODA delle vendite per tipo cliente.

Esaminando i valori presenti in L2:L4 si intuisce come la MODA a cui è riferito il tipo cliente Ragazzo.

Quale formula potrebbe restituire in modo automatico il valore descrittivo associabile al valore di MODA?

Per rispondere dobbiamo usare la funzione CONFRONTA e la funzione INDICE.

La funzione CONFRONTA restituisce il numero di riga di una matrice selezionata che contiene il valore selezionato: se cercassimo il valore 12 in L2:L4 lo troveremmo in seconda riga.

La funzione INDICE restituisce il valore presente in una certa posizione, nel nostro caso la riga, di una matrice.

Esaminando la matrice K2:L4 il Tipo Cliente abbinato al valore di moda si trova nella seconda riga della colonna K.

Il valore di riga viene determinato dalla seguente formula:

=CONFRONTA(E38;L2:L4) che restituisce un valore pari a 2

La voce descrittiva da abbinare al valore di MODA si trova quindi sulla riga 2 appena individuata, ma nella colonna K. Preleveremo tale voce con la seguente formula:

=INDICE(K2:K4;CONFRONTA(E38;L2:L4)

Come per la MEDIANA esiste anche la funzione MODA, ma possiamo utilizzarla solo per i valori numerici. Se per esempio volessimo determinare qual è l’importo di vendita più frequente, potremmo utilizzare la funzione di MODA scritta nel modo seguente:

=MODA(E2:E25) che restituisce un risultato pari a 90

È importante notare come questa funzione non tiene in considerazione le distribuzioni di dati MULTIMODALI. Per questa casistica ci si deve appoggiare ad una funzione che si chiama MODA.MULT e che rientra nelle funzioni matriciali che affronteremo più avanti.

MEDIA, MEDIANA E MODA. QUALE SCEGLIERE?

Non si può dire a priori quale degli indici esaminati possa essere migliore degli altri. La pratica più idonea consiste nel calcolarli tutti per poi fare alcune riflessioni.

Prendiamo per esempio il seguente insieme di dati: 2,3,3,4,5,6,7,8,9,100.

Se ne calcoliamo la media aritmetica dei valori espressi si ottiene un risultato pari a 14,7. Ci si rende immediatamente conto che dei 10 numeri analizzati, 9 sono sotto la media aritmetica. E’ facile intuire che la media aritmetica non è per niente rappresentativo della serie.

La mediana della serie dei valori è invece pari a 5,5 e ci sembra un valore assolutamente più rappresentativo della serie di numeri espressa.

La media aritmetica è troppo spesso influenzata da “valori anomali”.

Se fossimo dei commercianti, molto probabilmente, ci interesserebbe sapere qual è il numero “più venduto” (supponendo, per assurdo, che si possano vendere i numeri!). Nel nostro caso il numero “che va più di moda” è il numero 3; è infatti l’unico numero “venduto” due volte.

L’articolo [EXCEL] MEDIA, MEDIANA E MODA IN EXCEL proviene da Mummu Academy.

[Excel] Creare Report in Excel con INDIRETTO

La funzione INDIRETTO è una delle funzioni che si possono utilizzare nel momento in cui si devono creare report  in Excel e gestire collegamenti tra fogli in modo sicuro ed efficiente. Insieme alla funzione INDIRETTO è spesso affiancato un set di funzioni quali INDIRIZZO, RIF.RIGA e RIF.COLONNA. Vediamo con un esempio come utilizzare questo interessante mix di funzioni.

CREARE REPORT IN EXCEL CON UN MIX DELLLE FUNZIONI INDIRETTO, INDIRIZZO, RIF.RIGA E RIF.COLONNA

Negli ultimi anni molte più persone si trovano nella situazione di dover gestire fogli dicalcolo complessi e di dover trasformare i dati in informazioni. Scopo di questo tutorial è quello di accompagnare gli utenti già esperti di Excel alla scoperta di alcune funzioni che “mischiate” tra di loro possono riusltare molto utili nella generazione di fogli di sintesi. Dato che utilizzeremo tutte le funzioni citate  scopriamone insieme la sintassi ed il loro utilizzo, prima prese singolarmente e poi in un mix.

FUNZIONE RIF.RIGA

La funzione RIF.RIGA restituisce il numero di riga di un particolare riferimento a cella. Per esempio scriviamo la seguente formula:

=RIF.RIGA(B3)

Il risultato che la funzione produce è 3 poiché il numero di riga del valore della cella B3

FUNZIONE RIF.COLONNA

La funzione RIF.COLONNA restituisce il numero di colonna di un particolare riferimento a cella. Per esempio scriviamo la seguente formula:

=RIF.COLONNA(B3)

Il risultato che la funzione produce è 2 poiché il numero di colonna del valore della cella B3

Se nell’utilizzo della funzione RIF.RIGA ci si può aspettare un risultato sottoforma di numero, quello che può suscitare una poca di sorpresa è il risultato della funzione RIF.COLONNA. Dobbiamo precisare infatti che la funzione non restituisce il valore classico (una lettera) delle colonne, ma un numero. La colonna B diventa in questo senso il numero 2; la colonna C diventa il numero 3 e così via.

Questa caratteristica è particolarmente utile nel momento in cui si vada ad utilizzare la funzione INDIRIZZO.

FUNZIONE INDIRIZZO

Vediamo per prima cosa la sintassi della funzione INDIRIZZO, che è la seguente:

INDIRIZZO(riga; col; [ass]; [a1]; [foglio])

Sappiamo che la sintassi di una funzione mette tra parentesi quadre gli argomenti la cui specifica è opzionale da parte dell’utente. e per i quali, comunque, non è obbligatorio l’inserimento di un valore che, in caso di omissione, viene impostato su un parametro di default da Excel. Guardando la sintassi della funzione INDIRIZZO si comprende come sia indispensabili soltanto due valori: il riferimento di riga ed il riferimento di colonna.

Se scrivessimo la funzione =INDIRIZZO(3;4) il suo effetto sarebbe quello di restituire la coordinata $D$3 in una forma di stringa utilizzabile all’interno della funzione INDIRETTO.

La funzione INDIRIZZO genera una stringa di testo che corrisponde ad un indirizzo di cella in modalità riferimento assoluto.

Ma perché proprio riferimento assoluto? Perché se non viene specificato un terzo parametro della funzione INDIRIZZO, il riferimento testuale generato è ASSOLUTO. Il terzo parametro della funzione INDIRIZZO, quello che nella sintassi della formula troviamo come [ass], è quindi personalizzabile in base alla seguente legenda presa direttamente dal sito Microsoft

[ass] Tipo di riferimento restituito
1 od omesso Assoluto
2 Assoluto (riga), Relativo (colonna)
3 Relativo (riga), Assoluto (colonna)
4 Relativo

Se scrivessimo, quindi =INDIRIZZO(3;2;4) il risultato sarebbe una stringa di testo rappresentante un indirizzo relativo. Si avrebbe infatti come risultato il testo B3. Questa volta, il riferimento sarebbe relativo e non assoluto.

Ma cosa accade se desideriamo generare una stringa di testo che abbia come riferimento una determinata cella di un determinato foglio? Sappiamo bene che i riferimenti ad una cella, se non contengono la specifica di riferimento ad un foglio in particolare, vengono sempre assunti come riferiti al foglio in cui nascono. La risposta la si trova nel quinto parametro della funzione INDIRIZZO, quello indicato da [foglio] con il quale si ha la possibilità di specificare il nome del foglio a cui il riferimento deve puntare. In base a quanto espresso, se scrivessimo la funzione =INDIRIZZO(3;2;4;;”PIPPO”) il risultato sarebbe il seguente testo: PIPPO!B3.

Vi chiederete cosa ne sia del quarto parametro della funzione INDIRIZZO di cui non abbiamo parlato, quello che nella sintassi è indicato come [a1].  Beh… è forse il più strano perché permette di cambiare il sistema di riferimento alle celle da standard a R1C1. In tale sistema di riferimento non esistono lettere, ma solo numeri: un numero identifica la riga e l’altro identifica la colonna. Affronteremo questo topic in un apposito articolo. In questo esempio daremo per scontato l’utilizzo del tradizionale sistema di riferimento alle celle che prevede l’utilizzo delle lettere per le colonne e dei numeri per le righe.

Nella funzione =INDIRIZZO(3;2;4;;”PIPPO”) il quarto parametro è intenzionalmente lasciato vuoto perché in assenza di una specifica viene generato un riferimento standard.

FUNZIONE INDIRETTO

Vediamo adesso un caso pratico utilizzato per creare report in Excel:

Creare report con funzione INDIRETTO
Creare report con funzione INDIRETTO

Si tratta di un piccolo prospetto in cui vengono elencate le ore lavorate in une mese per quattro reparti diversi. La nostra esigenza è quella di generare un prospetto sintetico tipo quello presente nell’intervallo G1:I5. Una simile attività può sembrare molto più facile di quello che invece è se lo si deve fare con delle formule e non tramite dei manuali copia incolla. Il copia incolla non porta a nessun risultato se i reparti fossero ad esempio una cinquantina…

Partiamo dalla cella F2 e vediamo quale sia la formula inserita:

=INDIRETTO(INDIRIZZO(RIF.RIGA($B$1)+6*(RIF.RIGA(B1)-RIF.RIGA($B$1));RIF.COLONNA(B1)))

Come si può notare, la complicazione è nel riferimento di riga che deve aumentare di sei ad ogni passaggio da F2 a F3 a F4 e fino a F5. È stato utilizzato un piccolo espediente che permette di generare con semplicità dei multipli di 6 ad ogni passaggio tra una riga e l’altra. La formula deve essere ricopiata in verticale.

Vediamo adesso la formula che inseriremo in G1

=INDIRETTO(INDIRIZZO(RIF.RIGA(B3)+1*(RIF.COLONNA(B3)-RIF.COLONNA($B$3));RIF.COLONNA($B$3)))

Questa volta la difficoltà è dovuta al fatto che la formula deve essere copiata in orizzontale e, per contro, i riferimenti devono scorrere in verticale. In genere non si è molto abituati a “tirare” una formula verso destra ed ottenere un incremento degli indici di riga e non quelli di colonna. Per ottenere questo “strampalato” risultato si è usata una formula simile alla precedente che usa gli scostamenti sull’asse orizzontale per generare degli incrementi di riga e cioè verticali. Ricopiando la formula verso destra, e cioè da G1 a H1 e fino ad H1 gli unici riferimenti che scorrono sono quelli di riga.

Vediamo adesso la formula che inseriamo in G2

=INDIRETTO(INDIRIZZO(RIF.RIGA($D$3)+6*(RIF.RIGA(D3)-RIF.RIGA($D$3));RIF.COLONNA($D3)))

Questa formula è del tutto simile a quella contenuta in F2 poiché deve sfruttare gli stessi principi. Si ricorda che questa formula andrà ricopiata in verticale.

Vediamo adesso la formula che inseriamo in H2

=INDIRETTO(INDIRIZZO(RIF.RIGA($D$4)+6*(RIF.RIGA(E4)-RIF.RIGA($D$4));RIF.COLONNA($D4)))

Questa formula è del tutto simile a quella contenuta in F2 poiché deve sfruttare gli stessi principi. Si ricorda che questa formula andrà ricopiata in verticale.

Vediamo adesso la formula che inseriamo in I2

=INDIRETTO(INDIRIZZO(RIF.RIGA($D$5)+6*(RIF.RIGA(F5)-RIF.RIGA($D$5));RIF.COLONNA($D5)))

Questa formula è del tutto simile a quella contenuta in F2 poiché deve sfruttare gli stessi principi. Si ricorda che questa formula andrà ricopiata in verticale.

Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna F:

Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna G:

Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna H:

Se si eseguono le operazioni in modo corretto si ottengono le seguenti formule per la colonna I:

I risultati generati per creare report in Excel saranno i seguenti:

Corso Excel Avanzato a Firenze - Creare Report con la funzione INDIRETTO

Utilizzando in modo leggermente diverso le funzioni, saremmo stati in grado di produrre gli stessi risultati, ma su di un foglio apposito, in modo da separare i dati di partenza dal report finale.

Al termine corso di Excel Avanzato a Firenze che si svolge presso Mummu Academy saprai creare report in Excel di  questa tipologia di report e molti altri.

Solo gli studenti di Mummu Academy dispongono della piattaforma di studio riservata ai nostri studenti.

 

 

 

L’articolo [Excel] Creare Report in Excel con INDIRETTO proviene da Mummu Academy.

[Excel] Formattazione Condizionale Basata su Formule

La formattazione condizionale è un indispensabile funzione che permette all’utente di evidenziare in modo diverso le celle appartenenti ad un intervallo selezionato solo se rispondono positivamente ad un criterio. Ad esempio potresti desiderare di colorare di rosso soltanto le celle che ospitano dei valori maggiori od uguali di 18. Scopriamo insieme come usare la sua versione più potente: la FORMATTAZIONE CONDIZIONALE BASATA SU FORMULE

FORMATTAZIONE CONDIZIONALE CLASSICA

Per prima cosa generiamo una piccola matrice di dati contenente dei valori idonei allo scopo.

 

Formattazione Condizionale in Excel
Formattazione Condizionale in Excel

 

Usando quanto appreso nel tutorial della FUNZIONE SE abbiamo inserito una formula in grado di scrivere se lo studente abbia o meno superato l’esame.

La nostra preoccupazione potrebbe essere quella di aumentare l’impatto visivo di coloro che non abbiano superato l’esame. A tale scopo proviamo a colorare di rosso lo sfondo delle celle che occupano valori minori di 18.

Selezioniamo quindi l’intervallo B2:B10 e chiamiamo il menu della Formattazione Condizionale

 

Formattazione Condizionale in Excel
Formattazione Condizionale in Excel

 

Clicchiamo adesso sul comando “Nuova Regola” e selezioniamo poi la seconda voce presente nel menu: “Formatta solo le celle che contengono

 

Formattazione Condizionale in Excel
Formattazione Condizionale in Excel

 

Dalla figura notiamo quali siano i parametri impostati per il valore e quale sia la formattazione desiderata (sfondo rosso della cella)

Cliccando su Ok si produce adesso il seguente risultato

 

Formattazione Condizionale in Excel
Formattazione Condizionale in Excel

 

Il limite di questa formattazione risiede nel suo troppo ristretto campo di applicazione: posso condizionare l’aspetto delle celle in base ai valori che solo esse stesse contengono. Cosa accade se, per esempio, desiderassimo colorare di rosso le righe di color che sono “respinti”?

FORMATTAZIONE CONDIZIONALE BASATA SU FORMULE

Certamente non possiamo pensare di selezionare l’intervallo A2:C10 e comportarci come prima esposto. Le celle delle colonne A e C non contengono numeri e non possono essere quindi formattate in base al fatto che il numero presente sia minore di 18. Non è semplicemente possibile.

Possiamo usare, tuttavia, le celle della colonna B come “TRIGGER“, come, cioè, dei grilletti in grado di determinare il formato non solo di sé, ma anche di altre celle. Nel nostro caso dovremmo chiedere ad Excel quanto segue:

  1. colorare di rosso la cella A2 di rosso quando “è vero che” il contenuto della cella B2 sia minore di 18. Allo stesso modo
  2. colorare di rosso la cella B2 di rosso quando “è vero che” il contenuto della cella B2 sia minore di 18. Allo stesso modo
  3. colorare di rosso la cella C2 di rosso quando “è vero che” il contenuto della cella B2 sia minore di 18. Allo stesso modo

Si può ben comprendere che la cella B2 funziona da “Trigger” per le altre della stessa riga. Si deve notare in fatti che desideriamo formattare in modo diverso le righe, anche se in funzione di determinate condizioni.

Tuttavia, quello che ci interessa è che tutte le righe dell’intervallo contenente NOMI, VOTI, ed ESITI siano colorate di rosso in base al valore presente in colonna B: quella del voto.

In particolare, per ogni riga “n”  (per “n” un numero compreso tra l’indice di riga inferiore e superiore dell’intervallo considerato) possiamo asserire che le nelle “An” e “Bn” e “Cn” siano colorate di rosso quando “è vero che” il valore della cella Bn è minore od uguale di 18.

Per Bn<=18 si ha che “An” e “Bn” e “Cn” hanno lo sfondo ROSSO

La stessa enunciazione può essere scritta anche come

QUANDO E’ VERO CHE Bn<=18 ALLORA le celle “An” e “Bn” e “Cn” hanno lo sfondo ROSSO

Si capisce quindi che si rende necessario l’uso di una formula, specifica per la formattazione, in ogni cella della riga “n” da formattare in funzione di Bn. Siamo di fronte a quella che per molti noi una grossa sorpresa in Excel: in ogni cella possono essere coesistere più di una formula. Nel nostro caso, una definisce il valore nella cella e l’altra l’aspetto di quest’ultima.

Ma dove inseriamo la formula della formattazione?

La formula in oggetto deve essere inserita una sola volta e lasciare che Excel la copi e la incolli in tutte le celle che vogliamo subordinare a livello di formattazione.

Selezioniamo quindi l’intervallo A2:B10 dall’alto a sinistra verso il basso a destra. Il modo con cui si selezionano le celle non è ininfluente per la corretta propagazione della formula. Una corretta abitudine è in genere quella di selezionare gli intervalli sempre nel modo appena espresso.

Dopo la selezione si richiami il menù di formattazione condizionale basato su formule. Nell’esempio di seguito, abbiamo richiamato un menu particolare: “GESTIONE REGOLE

 

Formattazione Condizionale Basata su Formule in Excel
Formattazione Condizionale Basata su Formule

 

Possiamo notare che per la selezione corrente A2:B10 esiste una formattazione condizionale pre-esistente. La possiamo adesso cancella re con l’apposito pulsante

avremo adesso un insieme di celle prive di altre formattazioni condizionali.

Ripetiamo l’operazione di selezione delle celle e creazione della formattazione condizionale

Clicchiamo su “NUOVA REGOLA” e scegliamo la voce “Utilizza una formula per determinare le celle da formattare

 

Formattazione Condizionale Basata su Formule in Excel
Formattazione Condizionale Basata su Formule

 

Come tutte le formule anche questa inizia con il simbolo “=”.

Ricordiamo che, trattandosi di un comando di formato, possiamo leggere il segno “=” come “QUANDO E’ VERO CHE”. Ricordiamo infatti che:

QUANDO E’ VERO CHE Bn<=18 ALLORA le celle “An” e “Bn” e “Cn” hanno lo sfondo ROSSO

Si ha che la formula da scrivere è la seguente

=$B2<18

La parte della formula che asserisce “ALLORA le celle “An” e “Bn” e “Cn” hanno lo sfondo ROSSO” non è da scrivere ma da “selezionare” attraverso la voce “Formato”: selezioniamo uno sfondo rosso dopo aver cliccato su Formato

 

Formattazione Condizionale Basata su Formule in Excel
Formattazione Condizionale Basata su Formule in Excel

 

Usiamo il riferimento misto $B2 perché la formula di formato è copiata ed incollata da una mano invisibile di Excel in tutte le altre celle della selezione, seguendo le stesse regole di ogni altro copia-incolla di formula.

Se immaginiamo cosa accadrebbe alla formula se non usassimo il simbolo del $ per la lettera B ci renderemmo conto che spostandoci in orizzontale (da A2 in B2) la formula diventerebbe

=C2<18

e poi così via, diventando

=D2<18

al successivo copia incolla da B2 a C2. Capiamo che in questo modo che la cella “trigger”, identificata in B2, ad ogni copia-incolla sull’asse orizzontale, traslerebbe verso la lettera successiva a B2.

Lo stesso elemento di disturbo non lo si ha copiando ed incollando la formula di formato =$B2<18 verso il basso (quando cioè si copia-incolla dalla riga 2 alla tre e poi alla quattro e così via)

Se clicchiamo su OK possiamo vedere come Excel colori tutta la riga in presenza di valori minori di 18 nella colonna del VOTO

Formattazione Condizionale Basata su Formule in Excel
Formattazione Condizionale Basata su Formule in Excel

 

Se hai trovato utile questo tutorial puoi scoprire la nostra sezione apposita del sito www.mummuacademy.it

Gli studenti dei corsi di Excel di Mummu Academy hanno un esclusivo accesso alla piattaforma di studio nella quale trovare interessante materiale di studio ed approfondimenti.

 

L’articolo [Excel] Formattazione Condizionale Basata su Formule proviene da Mummu Academy.