Vai all'esercizio: E2.02 - E2.03 - E2.04 - E2.05 - E2.06 - E2.07 - E2.08 - E2.09 - E2.10

Soluzione E2.01:

=CONTA.SE(AreaDegliEsiti;"<20") ==> nr. esami con voto inferiore a 20

=CONTA.SE(AreaDegliEsiti;">25") ==> nr. esami con voto superiore a 25

=CONTA.NUMERI(AreaDegliEsiti)
-CONTA.SE(AreaDegliEsiti;"<20")
-CONTA.SE(AreaDegliEsiti;">25") => nr. esiti compresi tra 20 e 25

Soluzione E2.02:ritorna all'indice

La soluzione con SOMMA.SE() è:

=SOMMA.SE(D5:D12;"versamento";C5:C12)-SOMMA.SE(D5:D12;"prelievo";C5:C12)

Per generare la stringa "Versamento" oppure "Prelievo" in modo casuale posso utilizzare la seguente formula:

=SE(CASUALE.TRA(0;1)=1;"Prelievo";"Versamento")

oppure:

=SE(CASUALE()<0,5;"Prelievo";"Versamento")

Se voglio che la frequenza dei versamenti risulti 3 volte superiore rispetto a quella dei prelievi scriverò la seguente formula:

=SE(CASUALE.TRA(1;4)=4;"Prelievo";"Versamento")

oppure:

=SE(CASUALE()<0,25;"Prelievo";"Versamento")

oppure:

=SE(CASUALE()<0,75;"Versamento";"Prelievo")

Soluzione E2.03:ritorna all'indice

Vediamo alcuni utili esempi per la risoluzione dell'esercizio

=SOSTITUISCI(E2;"i";"") => Toglie le "i" minuscole dalla stringa contenuta in E2 (quindi SOSTITUISCI() è case sensitive!).

=SOSTITUISCI(MAIUSC(E2);"I";"") => Toglie le "i", sia maiuscole che minuscole, dalla stringa contenuta in E2

=SOSTITUISCI(E2;CODICE.CARATT(10);"")  => Toglie gli invii dalla stringa contenuta in E2

=SOSTITUISCI(E2;" ";"")  => Toglie gli spazi dalla stringa contenuta in E2

=LUNGHEZZA(E2)-LUNGHEZZA(SOSTITUISCI(MAIUSC(E2);"I";"")) => Conta quante "i" (maiuscole o minuscole) ho in E2 - Viene calcolato come differenza tra la lunghezza della stringa originale e quella della stringa privata del carattere indicato nel 2° argomento nella funzione SOSTITUISCI().

=CODICE.CARATT(CodiceAscii) => Restituisce il simbolo (carattere!) corrispondente al codice Ascii passato come argomento

=LUNGHEZZA(E2)-LUNGHEZZA(SOSTITUISCI(E2;CODICE.CARATT(10);"")) => Conta gli invii in E2

Soluzione E2.04:ritorna all'indice

Ecco tutte le soluzioni (comprese anche quelle viste nella precedente lezione!).

A.1) Data e ora attuale:

=ADESSO()

A.2) Genera un orario casuale compreso tra il 1/1/1900 e l'istante attuale.

Per generare un valore compreso tra A e B devo usare la formula =CASUALE()*(B-A)+A. Sapendo che A è 1/1/1900 e che in Excel corrisponde al valore 1 segue la sottostante soluzione:

=TESTO(CASUALE()*(ADESSO()-1)+1;"gg/mm/aaaa hh:mm:ss")

A.3) Oggi è il mio compleanno ? (suppongo che in C4 vi sia la mia data di nascita!)

=SE(TESTO(OGGI();"ggmm")=TESTO(C4;"ggmm");"SI";"NO")

A.4) Fornire la formula che mostra il numero di giorni trascorsi dalla data in C2 ad oggi

="Tra la data in C2 (" & TESTO(C2;"gg/mm/aaaaa") & ") e oggi (" & TESTO(C3;"gg/mm/aaaa") & ") ci sono " & TESTO(ASS(TRONCA(C2;0)-TRONCA(C3;0));"#.##0") & " giorni di differenza"

B.1) Secondi trascorsi dall'inizio della giornata fino all'istante attuale (in C3 ho messo la formula =ADESSO()!)

=ORA(C3)*60*60+MINUTO(C3)*60+SECONDO(C3)

In alternativa posso usare anche la formula =INT(24*60*60*(C3-OGGI())).

La differenza ADESSO()-OGGI()fornisce un valore compreso tra 0 e 1 che rappresenta la frazione di giorno corrispondente all'istante attuale!

B.2) Numero di secondi che mancano da adesso al termine della giornata odierna (in C3 ho la data/ora attuale!).

=24*60*60-(ORA(C3)*60*60+MINUTO(C3)*60+SECONDO(C3))

 In alternativa posso usare la formula:

=INT(24*60*60-24*60*60*(C3-OGGI()))

B.3) Giorni trascorsi dall'inizio dell'anno ad oggi.

=INT(OGGI()-DATA.VALORE("01/01/" & ANNO(OGGI())))

La funzione INT o TRONCA non sono necessarie se applico alla cella un'opportuna maschera di formato. In alternativa posso usare la formula

 =GIORNI(OGGI();DATA.VALORE("01/01/" & ANNO(OGGI())))

B.4) Minuti trascorsi dall'inizio dell'anno fino all'istante attuale.

=INT((ADESSO()-DATA.VALORE("01/01/" & ANNO(OGGI())))*24*60)

In alternativa posso usare anche la formula

=GIORNI(OGGI();DATA.VALORE("01/01/" & ANNO(OGGI())))*24*60+ORA(C3)*60+MINUTO(C3)

C.1) Giorno del mese relativo alla data digitata in C2.

=GIORNO(C2)

In alternativa posso usare anche la formula  (in questo caso il risultato è testuale!)

=TESTO(C2;"g")

 C.2) Numero di giorni del mese precedente alla data digitata in C2.

=GIORNO(DATA.VALORE("01/" & TESTO(C2;"mm/aaaa"))-1)

DATA.VALORE("01/" & TESTO(C2;"mm/aaaa"))-1 è la codifica interna in EXCEL del giorno precedente al primo giorno del mese corrente (ovvero dell'ultimo giorno del mese precedente!).

C.3) Anno relativo alla data digitata in C2.

=ANNO(C2) 

In alternativa posso usare anche la formula (in questo caso il risultato è testuale!)

=TESTO(C2;"aaaa")

C.4) Prime 2 lettere del giorno della settimana (in italiano) della data in C2.

=SINISTRA(TESTO(C2;"ggg");2)

C.5) La data digitata in C2 è l'ultimo giorno del mese ?

Sapendo che il  mese di domani cambia se oggi è l'ultimo giorno del mese segue la formula:

=SE(MESE(C2)<>MESE(C2+1);"SI";"NO")

C.6) L'anno associato alla data in C2 è bisestile?

Sapendo che un anno è bisestile quando è divisibile per 400  oppure per 4 ma non per 100 segue la formula:

=SE(O( RESTO(ANNO(C2);400)=0 ;E(RESTO(ANNO(C2);100)<>0;RESTO(ANNO(C2);4)=0));"SI";"NO")

D.1) Nome del mese (in italiano) della data in C2.

=TESTO(C2;"mmmm")

D.2) Nome del mese (in francese) della data in C2 (nell'area G2:G13 abbiamo l'elenco dei nomi dei mesi in francese!).

=INDICE(G2:G13;MESE(C2);1)

In alternativa posso usare anche la formula

=INDIRETTO("G"&(MESE(C2)+1))

D.3) Giorno della settimana (in inglese) della data in C2.  (nell'area H2:H8 abbiamo l'elenco dei nomi dei giorni della settimana in inglese!).

=INDIRETTO("H" & (GIORNO.SETTIMANA(C2)+1))

D.4) In che fascia oraria siamo adesso ?

Possiamo sfruttare la ricerca per intervalli resa possibile grazie  alla funzione CERCA.VERT

=CERCA.VERT(TESTO(ADESSO();"hhmm");I2:J7;2;VERO)

D.5) Determinare in C24 la stagione associata alla data digitata nella cella C2.

Soluzione:

Per semplicità consideriamo l'esempio equivalente dove si chiede di estrarre in B5 la stagione associata alla data scritta in B1.

>> Metodo 1: Utilizzando una codifica testuale

Iniziamo aggiungendo le stringhe in grigio nell'area D2:D6 (vedremo successivamente il motivo)

Nella determinazione della stagione l'anno è ininfluente. Iniziamo a costruire una codifica testuale della data che escluda tale informazione ma al contempo continui a rimanere compatibile con l'ordinamento cronologico.

Trasformiamo la data in B1 in una stringa composta dalle cifre corrispondenti al mese e al giorno. Utilizziamo pertanto la formula:

Questa "codifica testuale" della data consente di ottenere una stringa_data tale che:

"stringa"_data1 < "stringa"_data2 <=> data₁< data₂  purché le 2 date appartengano allo stesso anno.

La relazione d'ordine utilizzata nella disuguaglianza "stringa"_data1 < "stringa"_data2 è quella alfanumerica (basata quindi sulla tabella ASCII!) mentre quella della relazione data₁< data₂ è quella temporale o cronologica. Il <=> evidenzia la compatibilità tra le due tipologie di relazione.

Quindi l'utilizzo della formula TESTO(Data,"mmgg") genera una codifica testuale della data (priva dell'anno!) che rende compatibile la relazione d'ordine tipica delle stringhe (alfanumerica) con quella cronologica.

Le stringhe inserite nell'area D2:E6, a fianco di ogni stagione, rappresentano la codifica testuale associata al primo giorno di ogni stagione.

La compatibilità della relazione d'ordine tipica delle date con quella usata usata dalla codifica rende l'elenco in D2:E6 ordinato sia in termini lessicografici che cronologici. Questo ci permette di effettuare una ricerca per intervalli testuali, sfruttando la formula CERCA.VERT, equivalente ad una analoga per intervalli temporali. CERCA.VERT (con il 4° argomento posto a VERO) restituisce l'ultima riga dell'area di ricerca (D2:E6) la cui cella in prima colonna (D) è risultata inferiore o uguale al valore cercato (ovvero alla codifica della data in B1). Pertanto restituisce la riga con l'ultima codifica (corrispondente ad una data di inizio di stagione) che è risultata minore o uguale alla codifica della data in B1 . Questa codifica corrisponde al 1° giorno della stagione dove troviamo la data scritta in B1. Ponendo il 3° argomento di CERCA.VERT a 2 si estrae da tale riga la 2° colonna (E) ovvero il nome della stagione richiesta.

Quindi la formula richiesta (senza usare la cella di supporto B3) è:

>> Metodo 2: Utilizzando una codifica numerica

Iniziamo aggiungendo i numeri in grigio nell'area D2:D6 (vedremo successivamente il motivo)

Nella determinazione della stagione l'anno è ininfluente. Iniziamo a costruire una codifica numerica della data che escluda tale informazione ma al contempo continui a rimanere compatibile con l'ordinamento cronologico.

Trasformiamo la data in B1 in un numero calcolato in funzione del mese e del giorno. Utilizziamo pertanto la formula:

=MESE(B1)*100+GIORNO(B1)

Questa "codifica numerica" della data consente di ottenere un  numero_data tale che:

numero_data1 < numero_data2 <=> data₁< data₂  purché le 2 date appartengano allo stesso anno_.

La relazione d'ordine utilizzata nella disuguaglianza numero_data1 < numero_data2 è quella numerica mentre quella della relazione data₁< data₂ è quella cronologica (umanamente attesa!). Il <=> evidenzia la compatibilità tra le due tipologie di relazione.

Quindi l'utilizzo della formula MESE(Data)*100+GIORNO(Data) genera una codifica numerica della data (priva dell'anno!) che rende la relazione d'ordine numerica compatibile con quella cronologica.

I numeri inseriti nell'area D2:E6, a fianco di ogni stagione, rappresentano la codifica numerica associata al primo giorno di ogni stagione.

La compatibilità della relazione d'ordine tipica delle date con quella usata usata dalla codifica rende l'elenco in D2:E6 ordinato sia in termini numerici che cronologici. Questo ci permette di effettuare una ricerca per intervalli numerici, sfruttando la formula CERCA.VERT, equivalente ad una analoga per intervalli temporali. CERCA.VERT (con il 4° argomento posto a VERO) restituisce l'ultima riga dell'area di ricerca (D2:E6) la cui cella in prima colonna (D) è risultata inferiore o uguale al valore cercato (ovvero alla codifica della data in B1). Pertanto restituisce la riga con l'ultima codifica (corrispondente ad una data di inizio di stagione) che è risultata minore o uguale alla codifica della data in B1 . Questa codifica corrisponde al 1° giorno della stagione della data scritta in B1. Ponendo il 3° argomento di CERCA.VERT a 2 si estrae da tale riga la 2° colonna (E) ovvero il nome della stagione richiesta.

Quindi la formula richiesta (senza usare la cella di supporto B3) è:

Soluzione E2.05:ritorna all'indice

Si osservi che in G4:G103 è stata inserita una numerazione progressiva da 1 a 100 che sostanzialmente rappresenta l'indice r associabile a ciascuna riga dell'elenco dei valori da ordinare. Iniziamo con lo scrivere in E4 la seguente formula

e pertanto verrà mostrato il più piccolo valore della sequenza (in G4 ho 1!). Per E4 la cella G4 è quella che si trova sulla "stessa linea ma due colonne più a destra". La replica della formula in E4, mediante copia ed incolla, in E5 (cella sottostante!) comporta la sostituzione da G4 a G5 del 2° argomento della funzione PICCOLO()  e questo per  conservare la semantica del riferimento relativo "sulla stessa linea ma 2 colonne più a destra". Poichè G5 contiene il numero 2 segue che in E5 venga mostrato il 2° valore più piccolo della sequenza originale. Iterando questa modalità di replica su tutta l'area E6:E103 otteniamo l'elenco, come richiesto dell'esercizio, ordinato in modo crescente .  Infatti il 2° argomento della generica funzione PICCOLO(), usando sempre la cella posta sulla stessa linea ma due colonne a destra, avrà progressivamente un valore r che cresce da 1 a 100 (visualizzando di conseguenza la sequenza che parte dal valore più piccolo e via via prosegue fino al più grande).

Il procedimento per la sequenza decrescente è identico con l'unica eccezione che la formula iniziale in questo caso è:

Per velocizzare l'operazione di replica delle formule conviene dividere la finestra in 2 parti ed effettuare, prima di incollare la formula, una "selezione multipla di elementi adiacenti" dell'area di destinazione come presentato nelle slide 89/90/91 del documento "EXCEL - X1 Note operative" relative al come "gestire velocemente la replica delle formule in presenza di fogli molto estesi".

Soluzione E2.06:ritorna all'indice

Una matrice X è simmetrica quando per ogni coppia di valori R (indice di riga) e C (indice di colonna) la seguente relazione X(R,C) = X(C,R) risulta sempre vera.

Una possibile strategia risolutiva potrebbe essere la seguente:

"... supponiamo di inserire nella matrice di supporto (area grigia!) una formula che mette 1 nella cella di riga R e colonna C quando l'elemento X(R,C) è uguale a X(C,R),  0 altrimenti. Se la matrice originale X è uguale alla sua trasposta X^T la relazione  X(R,C) = X(C,R) è sempre soddisfatta pertanto possiamo concludere che la matrice è simmetrica quando la somma dei valori nell'area grigia è pari a 100".

La formula che consente di mettere 1 quando l'elemento di riga R, colonna C è uguale al suo trasposto di riga C e colonna R , 0 altrimenti è la seguente:

oppure

Se non utilizzo particolari accorgimenti, la replica di questa formula in tutte le restanti 99 celle della matrice di supporto (area grigia!) deve avvenire digitando manualmente tutte le possibili combinazioni R e C. Per evitare questo inconveniente sopra e sul lato destro dell'area grigia è stata inserita la numerazione delle colonne (riga 2) e quella delle righe (colonna M).

Utilizzando queste numerazioni come indici di riga e colonna e bloccando con il dollaro la coordinata di colonna M e di riga 2 (uso quindi dei riferimenti misti come $M3 e N$2) riscrivo le formule precedenti nel seguente modo

oppure

Nel copia ed incolla i riferimenti di colonna M e di riga 2 si conservano per cui le formule replicate continueranno a riferirsi in modo coerente agli indici di riga e colonna precedentemente citati.

Soluzione E2.07:ritorna all'indice

Analizzare la soluzione pubblicata all'interno del file E2.Vuoto.

Soluzione E2.08:ritorna all'indice

Analizzare la soluzione pubblicata all'interno del file E2.Vuoto.

Soluzione E2.09:ritorna all'indice

Ecco lo schema risolutivo

Le formule effettive sulla riga 3 del foglio (nelle altre righe le formule mantengono la medesima semantica posizionale!) sono le seguenti:

1) in E3: =D3+F3/100 (con questa formula evito che vi siano esiti duplicati che mi impediscono l'utilizzo corretto della funzione RANGO())

2) in B3: =RANGO(E3;$E$3:$E$12)

3) in G3: =CERCA.VERT(F3;$B$3:$D$12;2;FALSO)
4) in H3: =CERCA.VERT(F3;$B$3:$D$12;3;FALSO)

Soluzione E2.10:ritorna all'indice

Analizzare la soluzione pubblicata all'interno del file E2.Vuoto.