Come usare la funzione SE in MICROSOFT EXCEL (prima puntata)

La funzione logica principale presente in Microsoft Excel è sicuramente la funzione SE. Si tratta di una funzione basilare per chi vuole iniziare ad utilizzare questo o qualsiasi altro foglio elettronico cercando di sfruttarne alcune delle tantissime potenzialità.

Quindi inauguriamo con questo post una serie di articoli su didaLGo dedicati alle funzioni di Excel ed in particolare a quelle logiche.

Iniziamo in questo primo "POSTLEZIONE" a vedere come "funziona" questa funzione e quindi come può esserci utile.

Prima di tutto dobbiamo sapere che questa funzione può essere utilizzata nella sua versione base oppure combinata con altre funzioni SE o altre funzioni logiche o di altro tipo al fine di ottenere anche risultati molto complessi.

Per il momento partiamo con la modalità più semplice.

In questo caso la funzione SE ci permette di far scegliere automaticamente al nostro foglio elettronico quale testo, numero, formula o funzione inserire in una determinata cella a seconda del risultato di un determinato test.

Quindi potremmo per esempio far apparire la scritta "bravo" a tutti gli agenti della nostra ditta che hanno realizzato più di 100 contratti nel mese e la scritta "non bravo" a tutti quelli che ne hanno realizzati meno di cento. O ancora far apparire il numero 100 che rappresenta un premio in euro dato a tutti i nostri agenti considerati bravi ed un valore di 10 euro a quelli non bravi.

Le possibilità sono davvero tante. La cosa interessante e che possiamo far scegliere velocemente e senza errori al computer senza rischiare di sbagliare o di perdere tanto tempo a digitare dei testi o degli importi.

Come tutte le funzioni dobbiamo iniziare digitando il simbolo = quindi il nome della funzione ovvero SE: 

=SE (attenzione a non lasciare spazi tra l'uguale e il nome della funzione, mentre usate tranquillamente le lettere maiuscole o minuscole)

Questa funzione ha tre argomenti. Il primo è il test, il secondo argomento è il valore, numero, funzione, formula o riferimento di cella che deve essere inserito nella nostra cella se il test si verifica ovvero se è vero, il terzo argomento è il valore, numero, funzione, formula o riferimento di cella che deve essere inserito nella nostra cella se il test non si verifica ovvero se è falso.

Proviamo con un esempio. Come possiamo vedere nella tabella qui sotto ci sono dei nostri amici fumatori ed altri no. Noi vogliamo far apparire in automatico un commento per ciascun amico. Vogliamo quindi far apparire la parola SCIOCCO se il nostro amico è un fumatore e la parola FURBO se NON è un fumatore. Possiamo usare la funzione SE dicendo al computer di verificare se nella colonna C trova la parola SI e quindi deve inserire la voce SCIOCCO o se non la trova e quindi deve inserire la voce FURBO.

Vediamo la funzione in concreto. Proviamo a digitare la funzione in D2:

=SE(C2="SI";"SCIOCCO";"FURBO")

Ora analizziamola nel dettaglio: 

Naturalmente tutto inizia con l'uguale e poi con il nome della funzione ovvero SE. Quindi si apre la parentesi e si inseriscono i tre argomenti:

Il primo è il test. In questo caso diciamo ad Excel, "guarda nella cella C2 e controlla se quello che c'è digitato è uguale alla parola SI". 

Il secondo argomento è il SE VERO ovvero diciamo ad Excel cosa deve fare se il test risulta vero. In questo caso diciamo ad Excel: "se il test è vero inserisci nella cella la parola SCIOCCO". 

Il terzo argomento è il SE FALSO ovvero diciamo ad Excel cosa deve fare se il test non si è verificato. In questo caso gli diciamo: "se il test è falso inserisci la parola FURBO".

Attenzione a non lasciare mai spazi nella nostra funzione. 

Attenzione ad inserire i testi (come nel nostro esempio le parole SI, SCIOCCO e FURBO) tra virgolette in maniera da distinguerli dai riferimenti di cella, dalle formule o dalle funzioni.

Attenzione ad utilizzare il PUNTO E VIRGOLA ; per separare tra di loro i tre argomenti.

Attenzione, ricordatevi di aprire la parentesi subito dopo il nome della funzione e di chiuderla al termine di essa.

Capito tutto?

Spero di SI. In effetti è meno complicato di quanto non sembri.

Beh ci sarebbero ancora tante cose da dire sulla funzione SE, ma riserviamole ai prossimi POST dove approfondiremo sempre più questo argomento.

Scopriremo infatti cosa succede se non inseriamo l'argomento SE VERO o l'argomento SE FALSO, scopriremo che si possono inserire anche numeri, formule, funzioni e riferimenti di cella come argomenti SE VERO e SE FALSO e scopriremo che si possono inserire più funzioni SE una dentro l'altra (in gergo nidificare delle funzioni) e quindi ottenere risultati davvero complessi ed interessanti.

A presto e buone funzioni SE...

Grafico Excel con voti degli studenti e media della classe

Oggi cerchiamo di imparare a crerare un grafico come questo: lo scopo dovrebbe essere quello di aiutarci a visualizzare facilmente gli allievi i cui risultati sono inferiori alla media della classe. Penso che creare un grafico con i risultati dei nostri allievi (un istogramma) non sia troppo difficile. La difficoltà potrebbe derivare dal fatto che vogliamo una linea orizzontale a segnalare il voto medio. Be', non credo di dovervi dire che non si tratta di creare una linea e di sistemarla sopra il grafico, più o meno nel punto corrispondente alla media! :-) Troveremo una soluzione che definirei più flessibile, ossia se cambiano i voti, automaticamente la linea si sposta, e forse -oserei dire- più elegante.
Cominciamo digitando i dati: nomi e voti. Ora nella colonna C dobbiamo visualizzare una formula con la media della classe. Ossia in C3 digitiamo la seguente formula (in precedenza abbiamo parlato del calcolo della media):
=MEDIA($B$3:$B$8)

Attenzione a inserire anche i simboli del dollaro nella formula, cioè a usare dei riferimenti assoluti. In tal modo, dopo aver creato la formula e verificato che il risultato sia corretto, possiamo ricopiare la formula contenuta nella cella C3 fino alla cella C8 (basta fare doppio clic sul quadratino nero in basso a destra della cella C3). In tal modo avremo ottenuto questo:

Bene. Ora possiamo creare il grafico. Selezioniamo l'intervallo A2:C8. Fatto? Adesso andiamo sul menu Inserisci-Grafico e creiamo un semplice grafico a istogramma. Perfetto. Manca soltanto un'ultima operazione: dobbiamo dire a Excel che la serie relativa alla media della classe la vogliamo come un grafico a linee. Eh già, perché è possibile creare grafici "mescolati", con 2 diversi tipi di grafico. Come si fa?

Fate un clic su un istogramma qualunque in colore rosso, se corrisponde anche per voi alla media della classe. Dovreste ottenere ciò che vedete nell'immagine qui sopra: tutti gli istogrammni relativi alla serie "Media classe" appariranno selezionati. Ora clic con il tasto destro del mouse su uno qualunque degli istogrammi selezionati e scegliete Cambia tipo grafico... Selezionate Grafico a linee e... voilà! Il gioco è fatto. Non rimane eventualmente che da formattare diversamente il colore della linea.

 

Buon lavoro!

Emanuele 

 

 

 

 

Calcolo della media

Eccoci di nuovo insieme. Beh, forse qualcuno penserà che almeno questa volta il post sarà facile...:-) E allora partiamo con le cose semplici e poi vedremo... Calcolare la media in Excel è molto semplice, perché esiste una funzione che fa al caso nostro: MEDIA. La sintassi è la seguente:

=MEDIA(num1;num2;...) 
dove num1, num2... sono le celle di cui calcolare la media. Possiamo anche naturalmente inserire un intervallo, per non essere costretti a elencare tutte le singole celle una per volta. Per avere la media delle celle in B2,B3,B4,B5 possiamo scrivere:
=MEDIA(B2;B3;B4;B5) oppure  =MEDIA(B2:B5)

Fin qui penso che non vi sia alcunché di complesso, basta provare.
questo secondo caso che vi propongo potrebbe creare qualche difficoltà.
Supponiamo di non avere tutti i dati delle vendite dei nostri prodotti.

La nostra funzione MEDIA non darà più il risultato, perché una delle celle da sommare contiene un valore di errore. Che fare? Escludo, naturalmente, l'idea di selezionare a mano le singole celle di cui calcolare la media (ossia scrivere in D8:   =MEDIA(D2;D3;D5), perché se i dati dovessero cambiare, dovrei ogni volta modificare la formula.
Possiamo ipotizzare una prima soluzione (ce ne sono anche altre). Nella colonna E inseriamo una formula che ci dica se il valore nella colonna D è un errore. Excel ha la funzione VAL.ERRORE che ci informa se una cella contiene un valore di errore. A questo punto potremo calcolare la MEDIA usando una funzione simile, che si chiama però MEDIA.SE. E' possibile calcolare la MEDIA se viene rispettata una condizione. Diremo a Excel di calcolare la media della colonna D solo se nella colonna E compare FALSO (ossia la cella corrispondente in D non contiene un errore).

ci siamo ancora tutti?! ;-)   Molto bene.
Allora, in E2 la formula è:
=VAL.ERRORE(D2)
poi ricopiamo la formula nelle celle fino a E5.
Perfetto. Ora in E8 inseriamo la funzione MEDIA.SE(intervallo;criterio;[int_media]).
La funzione ha 3 argomenti: il primo corrisponde all'intervallo che contiene i nostri criteri VERO/FALSO, perciò sarà E2:E5.
il secondo corrisponde al criterio in base al quale decidere se includere il valore nella media; per noi sarà FALSO (che significa che nella cella della colonna D non ci sono errori); il terzo argomento è D2:D5, ossia l'intervallo di celle di cui calcolare la media. Nota che questo terzo argomento è posto tra parentesi []. Ciò significa che è opzionale. Se viene omesso, Excel calcola la media usando come intervallo di celle l'intervallo impostato come prinmo argomento (nel nostro caso sarebbe E2:E5 ma sarebbe sbagliato!)
La formula in E8 sarà:
=MEDIA.SE(E2:E5;FALSO;D2:D5)

Un'altra volta vedremo come ottenere questo risulato senza la funzione MEDIA.SE, che non esiste in Excel2003.

bye bye...
Emanuele

Come eliminare i doppioni in Excel

Non mancano post sulla rete che spieghino come eliminare i valori ripetuti, così ho deciso di fornire il mio contributo. In Excel 2010 il nostro problema sarebbe molto semplice da risolvere, perché esiste un comando che ci aiuta. Noi troveremo una soluzione che va bene in qualunque versione di Excel, imparando a usare la funzione CONTA.SE.

Supponiamo di avere un elenco con i nomi delle città ordinate alfabeticamente (è importante che i valori siano ordinati, perché in tal modo i valori ripetuti vengono a trovarsi in celle adiacenti). Ora, se vogliamo eliminare i valori che occorrono più di una volta, dobbiamo comunque lasciarne almeno uno, ovviamente :-). Una possibile soluzione al problema è quella di usare la funzione CONTA.SE, con un piccolo artificio. La funzione ha due argomenti: l'intervallo e i criteri. L'intervallo corrisponde alle celle da contare, i criteri determinano quali celle saranno contate. Facciamo un esempio: se in B3 scriviamo:

= CONTA.SE(A4:A15;"Ancona")

otteniamo come risultato 2, ossia Ancona compare 2 volte nell'elenco.

Primo passo: cerchiamo di avere nella colonna B, accanto a ogni valore della colonna A il numero di volte in cui compare. Otteniamo il risultato scrivendo nella cella B4:

=CONTA.SE($A$4:$A$15;A4)

Attenzione ai dollari, che impostano il riferimento assoluto. Ora ricopiamo la formula nelle celle sottostanti e otteniamo il risultato in figura.

Bene, ora sappiamo quante volte sono ripetuti i nomi delle città, però non abbiamo modo di eliminare i ripetuti, lasciando il singolo valore. Alla fin fine, voglio ottenere Ancona, Genova, Milano ecc.

Secondo passo: A tale scopo, dobbiamo modificare leggermente la nostra formula, in modo da ottenere questo:

come vedete, accanto al nome di ogni città compare un numero che segnala se è la prima occorrenza, la seconda, la terza ecc. La formula da usare è la seguente: in B4 scriviamo:

= CONTA.SE($A$4:$A4;A4)

Molta attenzione ai dollari!

Fatto? Ottimo. Siamo ormai vicini alla soluzione: adesso si tratta di riuscire a eliminare tutte le righe che hanno un valore diverso da 1, che poi significa un valore maggiore di 1.

Terzo passo: Per farlo è sufficiente impostare un filtro automatico sulla colonna Ripetuti e nel filtro personalizzato visualizzare solo le righe in cui Ripetuti è maggiore di 1.

Quarto passo: A questo punto è sufficiente selezionare le righe che rimangono visibili e cancellarle.

Quinto passo: selezioniamo le righe restanti e ordiniamo in ordine alfabetico sulla colonna Elenco.

 Se non vi siete persi... abbiamo imparato a usare la funzione Conta.Se, con un piccolo artificio grazie all'uso dei riferimenti assoluti e relativi, e siamo riusciti a eliminare i doppioni dal nostro elenco.
Troppo difficile? Ormai i lettori di didaLGo stanno diventando dei knowledge workers...!
Buon lavoro a tutti
Emanuele

Calcolo data ricevuta del pedaggio

Il mese scorso ho dovuto prendere l'autostrada e ho richiesto l'emissione dell'attestato di transito. Immaginavo di trovare stampata la data e l'ora e invece, con mio stupore, c'era solo l'ora. La data è espressa come un numero progressivo. Nel caso che vedete nell'immagine il giorno è il 356. Bene. E che giorno sarebbe? Ricorriamo a Excel per una rapida soluzione del problema, a meno di avere un calendario che indichi il giorno progressivo sull'anno o chissà, magari qualcuno di voi lettori avrebbe un'altra soluzione?  In Excel possiamo digitare in una cella, per esempio A1, il primo dell'anno, nel mio caso 1/1/2011. Poi in A2 digitiamo 356. E in A3 dobbiamo solo più fare la somma, ossia digitare =A1+A2.
Risultato: 23/12/2011. Mmm.. be', certo potrebbe essere giusto, in realtà mi ricordo che ero andato il 22/12/2011! E come mai questo scarto? Attenzione! Perche' Excel non calcola anche il giorno di partenza, ossia 1/1/2011, ma comincia a contare dal giorno 2. Perciò bisogna sottrarre 1 al risultato finale. La formula corretta da inserire in A3 è:
=A1+A2-1
Qualche dubbio? Un esempio più facile da verificare. Se sull'attestato di transito risulta scritto 5, significa che sono passato il 5 gennaio. Ma se io aggiungo 5  a 1/1/2011 ottengo 6/1/2011, perché non viene contato il primo giorno.
Spero di avervi chiarito le idee... :-) e soprattutto di avervi fatto notare che, come al solito, bisogna controllare!! sempre! Excel, alla fin fine, esegue i calcoli che gli diciamo di fare... ma non è che ragioni... ;.-)

Emanuele

Calcolo della variazione in percentuale (2 di 3)

Abbiamo visto in un precedente post, Calcolo della variazione in percentuale (1 di 3), come calcolare la variazione in percentuale tra due numeri: potrebbero essere per esempio i dati relativi all'incremento delle vendite in 3 nazioni diverse. Se il numero è positivo, il triangolino è con il vertice in alto e verde: sono aumentate rispetto al mese precedente! Ora vogliamo aggiungere anche un triangolino colorato, che ci aiuti  visualizzare subito a occhio le variazioni positive e negative. Cominciamo subito dal triangolino.
1) Come si fa a inserire il triangolino? Non è un "disegno" o un'immagine. Si tratta piuttosto di un carattere che appartiene a un tipo di carattere particolare, costituito in realtà tutto da simboli. Il font in questione si chiama Wingdings 3. Usando il comando Inserisci - Simbolo, possiamo trovare, dopo aver impostato il font Wingdings 3, il simbolo che corrisponde al triangolo con il vertice verso l'alto. Una volta inserito, se andiamo sulla cella e controlliamo la barra della formula, troviamo una "p". Ora, se andiamo nella cella e scriviamo q, il triangolino avrà il vertice rivolto verso il basso. Insomma, p e q sono i caratteri che formattati come Wingdings 3 corrispondono a triangolini.
2) Come facciamo a visualizzare il triangolino con il vertice in alto o in basso? Si tratta di controllare il valore che compare in E: se è maggiore di 0 voglio il triangolo con il vertice in alto, altrimento con il vertice in basso. Dobbiamo usare la funzione SE. Tale funzione ha 3 argomenti:
SE(test; se_vero; se_falso).

• Test è la condizione che vogliamo verificare: nel nostro caso, se il valore nella colonna E è maggiore di 0
• se_vero: ciò che vogliamo avere nella colonna F se il test dà come risultato VERO: vogliamo la lettera p
• se_falso: ciò che vogliamo avere nella colonna F se il test dà come risultato FALSO, nel nostro caso una q.

In pratica, andiamo in F3 e digitiamo:
=SE(E3>0;"p";"q")  occhio alle virgolette!
3) occorre solo ricopiare la formula, come spiegato nel precedente post e dovremmo ottenere: in F3 q, in G3 e in H3: p
4) ora selezioniamo l'intervallo F3:H3 e impostiamo il carattere Wingdings 3.
Fatto? Perfetto, dovremmo vedere tutti i nostri 3 triangolini di colore nero.
L'ultima operazione è ottenere che sia verde o rosso a seconda che ci sia un incremento o un decremento.
Sarà il pane del prossimo post! E useremo la formattazione condizionale.
A presto

Emanuele

Calcolo della variazione in percentuale (1 di 3)

Oggi vorrei vedere come calcolare una variazione in percentuale tra due valori e come esprimere tale variazione con un indicatore grafico colorato (tipo il triangolino verde o rosso). Insomma, quello che vedete nell'immagine! Tenete conto che i triangolini colorati sono una funzionalità che Excel 2007 integra automaticamente, dunque è più facile ottenerli. Nel mio post terrò conto di tutti quelli -e sono sicuramente molti- che usano ancora Excel 2003. Seguitemi nei primi passi:
1) ricopiate tutto quello che si vede nell'immagine, ma non le percentuali nella colonna E, che sono una formula! e tanto meno inserirete a mano i triangolini ;-)
2) per calcolare la variazione mensile, ci collochiamo in E3 e digitiamo la seguente formula:
=D3/C3-1
Come risultato avremo qualcosa tipo: -0,210084034
3) Bene, ora impostiamo il formato percentuale, premendo sul pulsante %
4) Per finire ricopiamo la formula nelle celle sottostanti. Il metodo più semplice potrebbe essere di posizionarsi con il mouse sull'angolo in basso a destra della cella E3 (dovreste notare che il puntatore del mouse cambia forma e diventa un piccolo più nero) e fare doppio clic.
5) Ora abbiamo la formula ripetuta sulle 3 righe con i valori espressi in percentuale. Eventualmente possiamo aumentare il numero di cifre decimali, per ottenere un valore più preciso.
Fin qui tutto semplice, direi. E i triangolini? Aspettate i prossimi post :-) Nel frattempo vi lascio un primo suggerimento, così qualcuno di voi potrebbe trovare una soluzione: si tratta di usare la funzione SE... Ma non solo!
A presto!

Emanuele

[Calcolo della variazione in percentuale (2 di 3)]

Un grafico dinamico

[questo post fa riferimento a una funzionalità spiegata in http://didalgo.blogspot.com/2011/10/filtri-multipli-in-excel.html]

Creando grafici in Excel, in alcune occasioni farebbe comodo che Excel automaticamente modificasse l'origine dati per includere le nuove serie che aggiungiamo mano mano. Per esempio, nell'immagine ho creato il grafico disponendo dei dati del primo trimestre. quando aggiungo i dati di aprile, mi piacerebbe che il grafico si "aggiornasse" includendo questo mese. Ciò è possibile se abbiamo creato una "tabella" ([word 2007]: Inserisci - Tabella). Riepilogando:

1) digita i dati necessari per il grafico
2) [word 2007]: Inserisci - Tabella, premi Ok
3) crea il grafico selezionando questo intervallo di celle

Ora prova ad aggiungere una riga di dati: vedrai che il grafico si "aggiorna" includendola!

Emanuele

Filtri multipli in Excel

Usando Excel ho usato più volte i filtri. Molto comodo per visualizzare solo le righe che rispondono ai criteri voluti. In Excel 2007 li trovi su Dati-Filtro. Prima ti metti su una cella dell'intervallo e poi premi il pulsante. In Excel 2003 li trovi sul Menu Dati-Filtro-Filtro automatico, usando lo stesso procedimento.

Il problema è che voglio usarli su più di un intervallo di dati. Per esempio:

Qui sopra ho attivato il filtro con il procedimento che ho spiegato prima. Se però ho un altro intervallo nello stesso foglio a cui applicare il filtro, non posso perché il pulsante (in Excel 2007) / la voce di menu (in Excel 2003) Filtro risulta già attiva. E dunque? la soluzione è di usare il comando tabella, che consente di segnalare a Excel che un certo intervallo di celle per noi è una "tabella": tale comando tra l'altro ci consente di avere la possibilità di filtrare più volte sullo stesso foglio. Come si fa?

1) seleziona l'intervallo di celle a cui applicare tale comando
2a) Excel 2007: Inserisci-Tabella. Premere Ok
2b) Excel 2003: Dati-Elenco-Crea elenco. Premere Ok

Et voilà! Ora possiamo filtrare a piacere. E non solo... se guardate bene, infatti, in Excel 2003 è comparso un *... Inoltre è ora visibile una barra degli strumenti... Chi conosce Access forse può immaginare che cosa rappresenti l'asterisco... In Excel 2007 non si vede l'asterisco, ma un sottile angolino blu nella cella in basso a destra dell'intervallo "tabella"... In un prossimo post scopriremo a che cosa può servire segnalare a Excel che un dato intervallo è una "tabella".

Dimenticavo! E per tornare indietro? Se non voglio più la "tabella"?

In Excel 2007: Dati-Strumenti in tabella (in alto)-Converti in intervallo. Ora sono rimasti i formati colorati alle celle. Per toglierli, basta selezionare l'intervallo e poi Home-
In Excel 2003: Dati-Elenco-Converti in intervallo

A presto!

Emanuele

Riferimenti assoluti

Abbiamo imparato pochi giorni fa che esistono i riferimenti relativi. Il più delle volte vanno bene nelle nostre formule, ma non sempre. Nel caso che vediamo avremo bisogno di un riferimento assoluto. Il problema è questo: abbiamo un listino prezzi che va aumentato del 5%. Dobbiamo intanto capire come aumentare i prezzi del 5%. Possiamo avere la soluzione chiara come il sole. Oppure chiederci come fare... allora rispolveriamo le proporzioni. Ricordate ancora tutti? Potremmo dire che...

3,45 sta a X come 5 sta a 100. Cioè

3,45 : x = 5 : 100 da cui

x=3,45 per 100 diviso 5 ossia

3,45 per 100 /5 ossia

3,45 x 0,05 ossia

3,45 x 5%
Insomma, i modi sono tanti!

Andiamo in D5. La soluzione sarà scrivere nella cella =C5*E1. Infatti in C5 c'è il prezzo da aumentare e in E1 la percentuale di aumento. Risultato: 0,1725

Abbiamo anche recentemente imparato che si possono ricopiare le formule, vero? E allora facciamolo! Selezioniamo la cella D5, poi mettendoci sul quadratino nero in basso a destra trasciniamo per ricopiare in giù di due celle.

Oh oh. Cos'è successo? Perché l'aumento è uguale a 0? Selezioniamo la cella D6 e cerchiamo di capire. Sulla barra della formula c'è scritto: C6 * E2. Dunque, C6 è giusto perché la formula che abbiamo ricopiato conteneva C5, un riferimento relativo che -spostandoci in giù di una cella- si è modificato in C5+1, ossia C6. Tutto bene. Poi c'è E2. Eh già, perché E1, sempre riferimento relativo, è diventato E1+1, ossia E2. Solo che così stiamo facendo C6*E2, ossia 3,48 * 0. E naturalmente il risultato è 0. Il problema sta nel fatto che nella formula che abbiamo scritto in D5 va specificato che E1 è un riferimento assoluto, cioè non dovrà mai cambiare. Quindi la formula in D5 diventerà: =C5*$E$1
Ora possiamo ricopiarla in giù e tutto funzionerà correttamente. In D6 avremo 0,174 e in D7 avremo 0,0105.

Tutto chiaro?

Emanuele

Estrazione di testo da una cella

Tempo fa mi è stato chiesto se possiamo separare il contenuto di una cella distribuendolo nelle celle adiacenti, un carattere alla volta. Per esempio: in A1 c'è scritto: P16379. Voglio avere in A2: P, in B2: 1; in C2: 6; in D2: 3; in E2: 7 e in F2: 9. Solitamente si risolve il problema con codice Visual Basic. Ma ora troviamo una soluzione più semplice, che ci consente di imparare due funzioni, una delle quali appartiene a quelle che così da sole non servono a nulla, ma in questo caso...

Allora, avete messo in A1 il testo da elaborare? Ok.

Come possiamo avere in A2 il primo carattere contenuto nella cella A1? Ci servono due funzioni: STRINGA.ESTRAI e RIF.COLONNA(). Con la prima posso estrarre un certo numero di caratteri a partire da una determinata posizione. Nel nostro caso devo per cominciare estrarre un solo carattere nella prima posizione. quindi in A2 scriverò: =STRINGA.ESTRAI(A1;1;1), cioè dico a Excel di estrarre un solo carattere dal testo contenuto in A1 a partire dal primo carattere. In pratica ottengo P. Poi in B2 scriverò: =STRINGA.ESTRAI(A1;2;1) e otterrò 1. Poi in C2 scriverò: =STRINGA.ESTRAI(A1;3;1) e otterrò 6 e così via. Mmm. c'è un modo migliore? Certo. Avete notato che il numero da cui comincio a estrarre corrisponde alla colonna in cui mi trovo? Osservate il secondo argomento. In A2 parto dalla colonna 1 (e la colonna A è la PRIMA colonna); in B2 parto dalla colonna 2 (e la colonna B è la SECONDA colonna) è così via. E dunque? E allora posso usare una funzione che mi dica in quale colonna mi trovo, così "generalizzo" la mia funzione: una sola funzione andrà bene per tutte le colonne. RIF.COLONNA() mi restituisce il numero della colonna in cui sono. Attenzione che questa funzione non ha argomenti, ma sono necessarie le due parentesi. Per esempio, se vado in D4 e digito: =RIF.COLONNA() ottengo 4. Perché? Ma perché la colonna D è la quarta (A,B,C,D). La formula che ho usato in A2 perciò potrebbe essere:

=STRINGA.ESTRAI(A1;RIF.COLONNA();1).

Quando Excel la elabora, la risolve un pezzo alla volta e sostituisce RIF.COLONNA() con 1, perché siamo nella colonna A. Perciò questa formula può andare bene per tutte le colonne, purché usiamo un riferimento assoluto, così possiamo ricopiare la cella, trascinandola dal quadratino in basso a destra. La formula finale sarà in A2:=STRINGA.ESTRAI($A$1;RIF.COLONNA();1)

Possiamo trascinarla a destra di quanto ci serve e otterremo la suddivisione del testo contenuto in A1, un carattere alla volta nelle celle adiacenti.

Il punto fondamentale è questo: usando Excel bisogna cercare di generalizzare le formule che scriviamo, in modo tale da evitare errori e ridurre il numero delle formule.

Emanuele

La funzione SINISTRA in Excel

Tranquilli, esiste anche DESTRA e in certo modo anche CENTRO... Cerco di tenermi pronto per la par condicio... :-)

Sono funzioni raggruppate da Excel come funzioni di tipo TESTO. Servono per leggere il contenuto di una cella e recuperarne solo una parte. Supponiamo di aver ricevuto un file che contiene nella stessa colonna sia la sigla della provincia sia il CAP. In A1 abbiamo: MI20100; in A2: TO10100; in A3: RM06100 e così via. Mi è capitato molto spesso di dover manipolare dati "sporchi", cioè con tutto appiccicato. In casi come questi, se voglio avere in una cella la provincia e in un'altra il CAP, basta mettersi in B1 e scrivere: =SINISTRA(A1;2). La funzione SINISTRA restituisce dalla cella A1 tanti caratteri quanti sono indicati nel secondo argomento.

Gli argomenti sono ciò su cui lavora la funzione e sono separati da ; Nella funzione SOMMA, tanto per fare un esempio, gli argomenti sono le celle da sommare, il più delle volte un intervallo, esempio: =SOMMA(A30:A50), cioè somma tutte le celle da A30 fino a A50, ossia 21 celle. Oppure sono singole celle. Es. =SOMMA(A20;A40;A50) Qui sommo tre celle: A20, A40 e A50.

Il risultato in B1 sarà: MI i primi due caratteri partendo da sinistra contenuti nella cella A1. Poi andiamo in C1 e scriviamo: =DESTRA(A1;5). Otterremo con lo stesso criterio, ma partendo da destra, i primi 5 caratteri e avremo: 20100.

Ora non rimane che selezionare l'intervallo B1:C1. Hai visto il quadratino nero in basso a destra? Bene, posizionati sopra con il mouse che diventerà una piccola croce nera. Ora premi il tasto sinistro e tieni premuto trascinando in basso fino alla C3. Rilascia il pulsante del mouse e così abbiamo tutti i nostri dati ben leggibili.
Alla prossima!
Emanuele

Riferimenti relativi in Excel

In un'epoca in cui tutti sono alla ricerca di riferimenti... anche Excel dice la sua! Il cuore di Excel è l'uso delle formule. Esse ci consentono di lavorare con i valori presenti nelle celle e ottenere risultati senza riscrivere i numeri. Supponiamo di avere i dati che vedete nell'immagine. Vogliamo il totale della prima colonna. Sarà sufficiente usare la funzione SOMMA. Possiamo farlo utilizzando il pulsante della Somma automatica (il sigma, quella specie di E che troviamo sulla barra degli strumenti formattazione o sulla scheda Home in Excel2007). Basta posizionarsi in B7 e premere il pulsante. Excel inserisce la funzione SOMMA e individua l'intervallo di celle che secondo lui vanno usate per dare il totale:

=SOMMA(B3:B6)

questa cella sarà uguale al risultato della funzione SOMMA, che ha un argomento: l'intervallo di celle da utilizzare, in pratica gli addendi. Il segno dei due punti : serve a indicare un intervallo, ossia nel nostro caso dalla cella B3 fino alla cella B6 incluse. Se è tutto corretto, dobbiamo solo premere Invio ed è fatto. Abbiamo il nostro totale (67). B3, B6 sono riferimenti relativi, cioè sono riferimenti che variano se viene ricopiata la cella che li contiene. Proviamo e sarà facile capire. Selezioniamo con un clic la cella B7. Vedete in basso a destra della cella un piccolo quadratino? Bene, posizionatevi sopra con il mouse. Come vedete il puntatore del mouse, di solito una grossa croce bianca, diventa una piccola croce nera. Ora tenete premuto il tasto sinistro del mouse e trascinate verso destra fino alla cella D7 e poi rilasciate il tasto. Tutto fatto. Abbiamo i nostri totali anche per febbraio e marzo. Se selezionate la cella C7 vedrete in alto sulla barra della formula che Excel ha "adattato" i riferimenti relativi e qui sta facendo la somma di C3:C7 e così via nella colonna successiva. I riferimenti relativi sveltiscono molto l'inserimento delle formule... ma se la prima formula è sbagliata, altrettanto velocemente facciamo tanti errori, quindi occhio!

Emanuele

Generatedata - Generare automaticamente tabelle in Excel

Era un po' che non trovavo un servizio on line davvero originale e tanto interessante per gli insegnanti.

Questo strumento (per ora purtroppo solo in lingua inglese o olandese) permette di generare delle tabelle di dati (dati random ovvero a caso) in pochissimi istanti.

Una manna per chi deve insegnare l'uso di Excel o di altri fogli elettronici. Spesso infatti per fare degli esempi o per mettere alla prova i nostri allievi si spende un sacco di tempo per creare delle tabelle abbastanza grandi o comunque coerenti con il tipo di esercizio.

Con GENERATEDATA.COM possiamo invece in pochissimi istanti avere tabelle di dati anche molto grosse.

Il suo utilizzo è assolutamente semplice ed intuitivo. Unica pecca, come accennavo, il fatto che i dati vengono generati non in italiano. Poco male, con un po' di fantasia la tabella la aggiustiamo comunque.

Vediamo come funziona: naturalmente prima di tutto dobbiamo raggiungere la home page del sito (clicchiamo QUI).

Ci appare la pagina informativa sul sito (about) dove ci viene raccontato (in inglese) quali servizi offre il sito e soprattutto ci viene detto che si tratta di un codice scritto in JavaScript, PHP e MySQL e che ci permette di generare tabelle di dati. Si può utilizzare il servizio direttamente on line, scaricare il tutto con licenza GNU o sostenere economicamente il servizio.

Ma torniamo al nostro servizio on line. Bisogna scegliere la linguetta GENERATOR. Il gioco è già quasi praticamente fatto. Dobbiamo scegliere quali e quante colonne di dati ci interessa avere. Possiamo avere al massimo 5 colonne (naturalmente nulla ci impedisce di generare due tabelle da 5 colonne ed unirle in una sola da 10).

Possiamo scegliere i nomi da dare alle colonne indicandoli nella colonna COLUMN TITLE. Poi dobbiamo scegliere il tipo di dati. Abbiamo la possibilità di scegliere HUMAN DATI come ad esempio il nome, o l'indirizzo mail, o la via ecc... oppure (e qui arriva il bello) possiamo scegliere TEXT che ci offre la possibilità di generare un testo con un numero fisso di parole o con un numero a caso di parole compreso in un determinato range, ed infine possiamo scegliere il tipo dati CUSTOM che ci dona la possibilità di generare elenchi numerici che si autoincrementano come ci pare a noi oppure numeri all'interno di un range o codici alfanumerici.

Insomma abbiamo la possibilità di generare tabelle anche complesse e che possono assolutamente rispondere alle esigenze di chi in pochi secondi vuole creare tabelle anche di grandi dimensioni per i propri esercizi.

Naturalmente il programma ci chiede quante righe (ROWS) vogliamo nella tabella ed il formato che ci interessa. Possiamo avere una tabella, bella pronta in EXCEL oppure in HTML, XML, CSV o SQL.

Non mi resta che dire: buone tabelle a tutti...

Formattazione condizionale

La formattazione condizionale consente di cambiare automaticamente il formato alle celle di Excel in base al contenuto. Vediamo un po' di capire.

Supponiamo di avere un elenco che contiene i dati anagrafici degli studenti. Vogliamo mettere in evidenza tutti quelli che sono nati a Torino. La soluzione più semplice è di usare la formattazione condizionale.

Prepara la tabella che vedi a sinistra e poi fai così:

1) Seleziona l'intervallo B3:B6

2) Menu Formato-Formattazione condizionale

3) per noi la condizione sarà: "il valore della cella è" poi invece di "tra" dobbiamo scegliere "uguale a" e nella casella vuota che compare accanto scriviamo Torino (bada che Excel generalmente non tiene conto delle maiuscole/minuscole)

4) ora scegliamo il formato che vogliamo per le celle che contengono Torino. premiamo il pulsante formato...

5) possiamo impostare il colore del carattere, cioè come viene visualizzato il testo della cella; oppure il bordo della cella, oppure il motivo, cioè lo sfondo della cella. io scelgo il motivo.

6) ora clicco sul colore che voglio, per esempio il giallo

7) dobbiamo solo piu' premere Ok e poi Ok

8) ora le righe con Torino vengono impostate con il fondo giallo.

La formattazione è condizionale, perché se proviamo a cambiare la città di Enrico e scriviamo Cuneo, la cella non avrà più il fondo giallo. Automagically!
Questa funzionalità si può usare naturalmente anche con celle che contengono i numeri. Per esempio se vogliamo evidenziare in un listinio prezzi tutti gli articoli che hanno un valore superiore a 100 euro, oppure tutti quelli inferiori a 50, o quelli compresi tra 60 e 100 eccetera.
Avremo modo di fare altri esempi!
Emanuele

I voti dei miei studenti

Se gli studenti in classe sono molti, Excel risulta utile per tenere conto dei voti e delle medie. Cominciamo a prepare in un foglio Excel la tabellina che vedete qui a sinistra.

Fatto? Ok, ora possiamo occuparci intanto di impostare lo stesso numero di decimali per tutte le celle, altrimenti diventa scomodo legegre i valori, perché non sono allineati correttamente.

1) seleziona le celle che contengono i voti: sarebbe l'intervallo B3:D6.

2) In alto, sulla barra degli strumenti formatatzione troverai il pulsante che consente di aumentare i decimali (è suvito dopo quello dell'euro). Se lo fai, ora avrai tutte le celle con due decimali (sono stati aumentati di 1, ma c'era già 7,5 perciò tutte le celle ne hanno due. Se vuoi toglierne uno, il pulsante ancora destra serve a diminuire i decimali).

Bene. Ora possiamo occuparci di calcolare la MEDIA. L'operazione è molto semplice:

1) posizionati in E3.

2) Fai clic sulla freccettina nera a destra del pulsante con il simbolo della sommatoria (il sigma),. Si aprirà un elenco con alcune delle funzioni più utilizzate.

3) clicca su Media. Vedrai che Excel ti propone di calcolare la media dell'intervallo tratteggiato: B3:E3. E' ciò che vogliamo.

4) Premi il tasto Invio.
Ora non rimane che ripetere l'operazione per tutte le righe. Ma se gsli studenti sono molti... conviene ricopiare la cella. Così:

1) clicca sulla cella E6 in modo che sia selezionata.

2) posizionati con il mouse sull'angolo in basso a destra della cella, dove vedi un quadratino nero. Vedrai che il puntatore del m ouse diventa un "più nero"

3) Tieni premuto il tasto del mouse e trascinalo fino alla cella E6.

4) Rilascia il tasto del mouse. E voilà!

Ecco le nostre medie!
La prossima volta cercheremo di impostare automaticamente (!) le celle con i voti superiori alla media in modo che abbiano lo sfondo verde, così troviamo subito gli studenti migliori!
A presto!
Emanuele

Excel e i riferimenti tra fogli di lavoro

In Excel siamo abituati a usare formule che fanno riferimento allo stesso foglio di lavoro in cui sono inserite. Probabilmente nel Foglio 1 -se non ne abbiamo cambiato il nome- se abbiamo in B2:B5 un elenco di valori da sommare, il totale lo avremo in BA7 con una formula tipo:

=SOMMA(B2:B5).

Stiamo cioè utilizzando riferimenti interni al foglio di lavoro. Tuttavia, quando creiamo una nuova cartella di lavoro -un nuovo file Excel-, ci troviamo a disposizione 3 fogli. Possiamo inserire i dati sui diversi fogli e poi collegarli tra loro, usando formule che si riferiscono a celle contenute nei diversi fogli. Facciamo un esempio.

• il foglio 1 potrebbe chiamarsi: Gennaio (si rinomina un foglio facendo doppioclic sul nome del foglio e poi digitando il nome del nuovo foglio)


• il foglio 2: Febbraio


• il foglio 3: Marzo.

Potremo aggiungere un nuovo foglio (Inserisci-Foglio di lavoro) e nominarlo: Trimestre 1. Supponiamo di avere in ogni foglio dei valori che corrispondono a voci di spesa del mese relativo. In ciascuno di questi fogli avremo il totale del mese. Il nostro obiettivo è avere il totale del trimestre, visualizzato nel foglio Trimestre 1.

Come si fa? Se abbiamo i 3 totali mensili sempre nella stessa cella, per esempio sempre in B7, potremmo ottenere il totale trimestrale posizionandoci su C1 del foglio Trimestre 1 e digitando questa formula:

=Gennaio!B7 + Febbraio!B7 + Marzo!B7

Come vedete, si tratta solo di aggiungere prima del riferimento di cella il nome del foglio e poi inserire un !. Se il nome del foglio contiene spazi, bisogna racchiuderlo tra singoli apici. Esempio:
='Spese di Gennaio'!B7 + Febbraio!B7 + Marzo!B7.

Buon lavoro!
Emanuele

Excel e l'ultimo giorno del mese

Se abbiamo letto il post su Excel e il primo giorno del mese http://didalgo.blogspot.com/2009/10/excel-e-il-primo-giorno-del-mese.html, capire come trovare l'ultimo giorno del mese non è difficile. Supponiamo sempre di avere 28/04/2000 in A1. Per calcolare l'ultimo del mese, è sufficiente utilizzare le stesse funzioni che abbiamo già visto (DATA, ANNO, MESE) in questo modo. In B1 scriviamo:

=DATA(Anno(A1);Mese(A1);0)

Impostando a 0 il giorno la data non esiste: sarebbe infatti 0/4/2000. Siamo aiutati da Excel, che corregge automaticamente e sposta la data indietro di un giorno. Così otteniamo la data di fine mese. Non potremmo farlo noi mettendo, che so, 31 come giorno, perché non tutti i mesi hanno 31 giorni :-)
Tutto chiaro?

Emanuele

Excel e il primo giorno del mese

Torniamo a problema delle date: potremmo ricavarne il capitolo di un libro! :-)

Può capitare che partendo da una data, ci serva individuare il primo giorno del mese successivo. Immaginiamo di avere in A1 la seguente data: 28/04/2000. In B1 voglio ottenere 01/05/2000 (tra l'altro, questo credo risolva il problema del calcolo dei mesi che intercorrono tra due date -vedi commenti al post http://didalgo.blogspot.com/2009/09/calcolare-i-mesi-in-excel.html. Basterebbe ricavarsi come mese di fine la data del primo del mese successivo).

Come si può fare? Ci servono tre funzioni.

1) La prima è la funzione Anno. Ha un solo argomento: la data da cui ricavare l'anno. Esempio:=Anno(28/04/2000) mi darà come risultato 2000.

2) La seconda è la funzione Mese. Ha un solo argomento: la data da cui ricavare il mese. Esempio:=Mese(28/04/2000) mi darà come risultato 4.

3) La terza è la funzione DATA. Un po' particolare... Serve a costruire una data, usando anno, mese, giorno. Gli argomenti sono infatti tre: Anno, Mese, Giorno.

Tanto per capire come funziona, proviamo a scrivere in C20:

=DATA(2000,01/10) Risultato? 10/01/2000. A che cosa serve? Non bastava scrivere subito la data?! Serve a risolvere il problema di individuare il primo giorno del mese successivo.

Avete sempre in A1 la data 28/04/2000? Molto bene. Ora in B1 scriviamo:

=DATA(Anno(A1);Mese(A1)+1;1)

Risultato: 01/05/2000

Come avrete notato, ho scritto Mese(A1), che darebbe come risultato 4, ma poi ho aggiunto 1, quindi è come se il secondo argomento della funzione fosse 5. Infine ho digitato 1 come valore per l'argomento giorno. E il gioco è fatto. Se lo fate con una data di dicembre, automaticamente Excel visualizzerà la data del 1 gennaio dell'anno successivo.
La prossima volta troveremo la data di fine mese.
Emanuele

Calcolare i mesi in Excel

Tempo fa abbiamo parlato dei calcoli con le date in Excel. Un argomento sempre interessante (vedi i post precedenti: http://didalgo.blogspot.com/2008/01/calcoli-con-le-date-in-excel.html e http://didalgo.blogspot.com/2007/07/operazioni-con-le-date-in-excel.html). Ora vogliamo scoprire se si può calcolare una differenza tra date per ottenere come risultato non i giorni (l'abbiamo già visto in uno dei post che ho citato prima), ma i mesi (o gli anni o altro ancora). Si può fare. Dobbiamo utilizzare la funzione DATA.DIFF, che è una di quelle "non documentate". Che cosa significa? Che se la cerchiamo in Excel, non compare nella guida! Vai a sapere le stranezze di Excel...

Comunque la funzione è molto semplice. DATA.DIFF ha 3 argomenti: il primo è la data più vecchia, il secondo è la data più recente, il terzo indica come vuoi che Excel calcoli la differenza. E' possibile usare come terzo argomento: "d" (differenza in giorni), "m" (differenza in mesi), "y" (differenza in anni), "md" (differenza in giorni, ma ignora mesi e anni), "ym" (differenza in mesi, ma ignora giorni e anni); "yd" (differenza in giorni, ma ignora anni e mesi).

Attenzione perché il terzo argomento va racchiuso tra virgolette.

Qualche esempio? In B3 scrivete 10/05/2004 e in C3 09/05/2009.

• In D3 scrivete: =Date.Diff(B3;C3;"yd") risultato: 364 (differenza in giorni senza contare anni: provate a mettere anche 1999 come data di inizio e non cambia il risultato)
• In D3 scrivete: =Date.Diff(B3;C3;"m") risultato: 59 (differenza in mesi)
• In D3 scrivete: =Date.Diff(B3;C3;"md") risultato: 29 (differenza in giorni senza contare anni o mesi). Per capirci: 29 sarebbero i giorni che mancano per andare dal 10 al 9 del mese successivo... Un po' contorto, ma può anche servire...

Provate le varie combinazioni e... buon lavoro, in particolare a chi mi ha chiesto informazioni sul problema!

Emanuele