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

Soluzione E1.01:

La soluzione base è stata illustrata nelle slide "Concetto di algoritmo":

ovvero =SE(RESTO(C3;2)=0;"Pari";"Dispari")

Il controllo dell'errore complica la soluzione. Seguiamo il diagramma di flusso proposto sul foglio "E1.01" del file E1.Vuoto.xlsx che avete scaricato. Iniziamo con il prendere in esame la prima condizione che valuta la presenza di un dato in C3. La frase "La cella è vuota ?" in Excel diventa: =CELLA("Tipo";C3)="b".

Pertanto la prima formula intermedia che si ottiene è:

=SE(CELLA("tipo";C3)="b";"";"...")

I "..." indicano che ho lasciato in sospeso l'analisi relativa alla parte che gestisce i restanti output. Riprendiamo la scrittura della formula sostituendo i  "..." con SE(;;). Aggiungo subito, dopo il SE, le () per evitare di dimenticarmele e i ;; per ricordarmi che servono 3 argomenti. Ottengo quindi:

=SE(CELLA("tipo";C3)="b";"";SE(;;))

La condizione che inserisco in questo SE(;;) sarà quella che valuta se il contenuto di C3 è un numero oppure no.  La frase "La cella contiene un numero ?" in Excel corrisponde a: =CELLA("Tipo";C3)="v".

La successiva formula intermedia diventa quindi:

=SE(CELLA("tipo";C3)="b";"";SE(CELLA("tipo";C3)="v";"...";"Non so"))

 I "..." indicano che ho lasciato in sospeso la parte che gestisce l'eventuale input numerico. Proseguiamo sostituendo i "..." con SE(;;).

=SE(CELLA("tipo";C3)="b";"";SE(CELLA("tipo";C3)="v";SE(;;);"Non so"))

La condizione da inserire in questo SE(;;) deve testare se si tratta di un numero con decimali oppure se vale 0. Identifichiamo le istruzioni in EXCEL che implementano questi 2 test: =TRONCA(C3)<>C3 e =(C3=0). Essendo le 2 condizioni legate dalla congiunzione disgiuntiva "oppure" le uniremo utilizzando la funzione O(condizione1;condizione2).

La successiva formula intermedia diventa:

=SE(CELLA("tipo";C3)="b";"";SE(CELLA("tipo";C3)="v";SE(O(TRONCA(C3)<>C3;C3=0);"Non so";"...");"Non so"))

La parte relativa al controllo dell'errore è conclusa. I "..." restanti sono relativi alla parte finale dell'esercizio, quella che stabilisce se il numero in C3 è pari o dispari. Sostituiamo quindi i "..." con la formula iniziale, utilizzata per la soluzione base (quella senza controllo dell'errore!) ovvero  SE(RESTO(C3;2)=0;"Pari";"Dispari"). La soluzione finale diventa:

=SE(CELLA("tipo";C3)="b";"";SE(CELLA("tipo";C3)="v";SE(O(TRONCA(C3)<>C3;C3=0);"Non so";SE(RESTO(C3;2)=0;"Pari";"Dispari"));"Non so"))

Il diagramma di flusso proposto nel foglio "E1.01", decodificato con le formule in Excel diventa

Soluzione E1.02:ritorna all'indice

Sostituendo alle condizioni nel flowchart (all'interno del foglio dell'esercizio 1.02!) le corrispondenti formule in Excel ...

... otteniamo la seguente soluzione:

=SE(E(CELLA("Tipo";D3)="v";CELLA("Tipo";E3)="v");SE(D3>E3;"1";SE(D3<E3;"2";"X"));"?")

La presenza dei doppi apici " in corrispondenza di "1" e "2" sottolinea che si tratta di simboli privi di qualsiasi significato numerico (in termini tecnici "stringhe"!).

Le 2 formule sottostanti:

=E(CELLA("Tipo";D3)="v";CELLA("Tipo";E3)="v")
=O(CELLA("Tipo";D3)<>"v";CELLA("Tipo";E3)<>"v")

corrispondono rispettivamente alle affermazioni:

"Ho inserito l'esito della partita correttamente" ovvero ho inserito nelle celle D3 e E3 dei valori numerici (ricordo che il controllo sulla presenza di decimali non è richiesto in questo esercizio!)
"Non ho inserito l'esito della partita correttamente" ovvero il contenuto di una delle 2 celle non è numerico  (ricordo che il controllo sulla presenza di decimali non è richiesto in questo esercizio!).

pertanto un'affermazione è la negazione dell'altra. Potrò utilizzare l'una o l'altra (di solito si usa quella più breve!) come 1° parametro della funzione SE() ricordandoci però di impostare opportunamente il 2° ed il 3° argomento. Infatti se la formula corretta che usa la CondizioneX nel SE() è la seguente:

=SE(CondizioneX;ArgomentoA;argomentoB)

allora quella equivalente che usa la negazione di CondizioneX dovrà essere scritta in questo modo:

=SE(NON(CondizioneX);ArgomentoB;ArgomentoA)

Soluzione E1.03:ritorna all'indice

Analizziamo il flowchart presente all'interno del foglio dell'esercizio 1.03

Iniziamo prendendo in considerazione la condizione che controlla se "Non ho scritto nulla" e che corrisponde alla situazione 'esame "non sostenuto" '. In Excel la formula equivalente è =CELLA("Tipo",D3)="b" oppure =(D3="").

La prima formula intermedia che si ottiene è:

=SE(CELLA("tipo";D3)="b";"Non sostenuto";"...")

I "..." indicano che ho lasciato in sospeso l'analisi relativa alla parte che gestisce la presenza di un eventuale input. Riprendiamo la scrittura della formula sostituendo i "..." con SE(;;). Inserisco subito dopo il SE le () per evitare di dimenticarmi qualche parentesi e i ;; per ricordarmi che la funzione SE richiede 3 argomenti.

=SE(CELLA("tipo";D3)="b";"Non sostenuto";SE(;;)))

La funzione SE(;;) dovrà verificare se "la cella contiene un numero"  è in caso contrario dovrà segnalare un "Errore!". La formula di Excel equivalente a tale condizione è CELLA("Tipo";D3)="v".

La successiva formula intermedia diventa:

=SE(CELLA("tipo";D3)="b";"Non sostenuto";SE(CELLA("tipo";D3)="v";"...";"Errore!"))

I "..." indicano che ho lasciato in sospeso la parte che gestisce l'input di tipo numerico. Proseguiamo sostituendo i "..." con SE(;;).

=SE(CELLA("tipo";D3)="b";"Non sostenuto";SE(CELLA("tipo";D3)="v";SE(;;);"Errore!"))

La condizione da inserire nel SE(;;) deve testare (adesso che in D3 ho sicuramente un numero!) se "è un voto assurdo" ovvero se è esterno all'intervallo 1...30. In Excel la formula equivalente è O(D3<18;D3>30).

La formula intermedia successiva diventa:

=SE(CELLA("tipo";D3)="b";"Non sostenuto";SE(CELLA("tipo";D3)="v";SE(O(D3<1;D3>30);"Errore!";"...");"Errore!"))

I "..." indicano che ho lasciato in sospeso la parte che gestisce la casistica caratterizzata da un voto d'esame realistico. Proseguiamo sostituendo ancora "..." con SE(;;).

=SE(CELLA("tipo";D3)="b";"Non sostenuto";SE(CELLA("tipo";D3)="v";SE(O(D3<1;D3>30);"Errore!";SE(;;));"Errore!"))

La condizione inserita nell'ultimo SE(;;) deve testare (adesso che in D3 ho sicuramente un esito corretto!) se la votazione digitata è relativa ad un esito positivo oppure negativo.

per cui la soluzione finale diventa:

=SE(Cella("Tipo";D3)="b";    
    "Non sostenuto";         
    se(Cella("Tipo";D3)="v"; 
       Se(O(D3<1;D3>30);     
          "Errore!";         
          SE(D3>17;          
             "Superato";     
             "Non superato"  
            )                
          );                 
      "Errore!")             
    )                        

I rientri presenti in questa formula (si dice che la formula è indentata!) facilitano una corretta stesura (dal punto di vista sintattico!) della formula poichè semplificano l'accoppiamento tra l'apertura di una parentesi e la sua corrispondente chiusura.

Soluzione E1.04:ritorna all'indice

Partendo dall'ipotesi che i voti siano sempre corretti segue che ognuna delle 4 formule proposte rappresenti una possibile soluzione.

=SE(CONTA.NUMERI(C3:C12)=10;MEDIA(C3:C12);"P.S. incompleto")

=SE(CONTA.VUOTE(C3:C12)=0;MEDIA(C3:C12);"P.S. incompleto")

=SE(CONTA.VUOTE(C3:C12)<>0;"P.S. incompleto";MEDIA(C3:C12))

=SE(CONTA.NUMERI(C3:C12)=CONTA.VALORI(B3:B12);MEDIA(C3:C12);"P.S. incompleto")

la formula CONTA.VALORI(B3:B12) conta i titoli degli esami presenti nel piano studi (e deve coincidere con il numero di esiti!).

Partendo dal flowchart

scriviamo le seguenti funzioni nelle celle di supporto

Il contenuto della cella G8 =G4-G3 corrisponde a:

=CONTA.VALORI(AREA)-CONTA.NUMERI(AREA)

ed equivale ad una sorta di CONTA.LETTERE(AREA) che però in Excel non esiste!. La formula coincide con la frase "Ho un esito testuale" quando questa restituisce un risultato diverso da 0.

La somma dei risultati delle formule in G6 e G7:

=CONTA.SE(AREA;"<18")
=CONTA.SE(AREA;">30")

corrisponde alla frase ' "Sono fuori dall'intervallo" degli esiti accettabili' quando è diverso da 0.

Pertanto la condizione

(G6+G7+G8)>0

corrisponde alla condizione posta nella prima struttura selettiva del flowchart ovvero "Ho un esito testuale oppure sono fuori dall'intervallo". Si poteva utilizzare in alternativa la seguente formula:

O(G8<>0;G7<>0;G6<>0)

Successivamente per valutare se "manca qualche voto" basta controllare che nella cella G3 (che calcola quanti numeri sono presenti) il risultato sia diverso da 10 ovvero:

(G3<>10) => dove G3 richiama la formula CONTA.NUMERI(AREA)

oppure che il risultato in G5 (che calcola quante celle sono vuote) sia diverso da zero

(G5<>0) => dove G5 richiama la formula CONTA.VUOTE(AREA)

In C14 metto la soluzione richiesta basata sulle celle presenti nell'area di supporto G3:G8:

=SE((G6+G7+G8)>0;"Errore!";SE(G3<>10;"P.S. Incompleto";MEDIA(C3:C12)))

Una soluzione alternativa (mostrata in aula) poteva essere:

=SE(E(G3=10;G6=0;G7=0);
    MEDIA(C3:C12);
    SE(O(G8<>0;G6<>0;G7<>0);
       "Errore!";
       "P.S. Incompleto"
      )
   )

L'area F3:F12 contiene una serie di funzioni booleane che permettono di sommare (usando il SOMMA.SE) solo i CFU relativi ad esiti formalmente corretti.

Soluzione E1.05:ritorna all'indice

=SE(CELLA("Tipo";C3)="b";"";SE(CELLA("Tipo";C3)="v";SE(C3<0;"Errore";C3*SE( C3<5000; 0,04; SE(C3<20000; 0,05; 0,1)));

Ecco un'altra soluzione possibile (si osservi l'indentazione per controllare la corretta chiusura delle parentesi):

=SE(CELLA("Tipo";C3)="b";
    "";
    SE(CELLA("Tipo";C3)="v";
       SE(C3>20000;
          0,1*C3;
          SE(C3>5000;
             5%*C3;
             SE(C3>0;
                4%*C3;
                "Errore"
               )
            )
         );
       "Errore"
      )
   )

Una soluzione alternativa (illustrata nella figura sottostante) potrebbe essere quella di suddividere la formula su più celle in modo da semplificarne la scrittura.

Soluzione E1.06:ritorna all'indice

Nelle celle di supporto K3 e K5 metto le seguenti formule:

in K3 ==> =CONCATENA(C3; "/"; E3; "/"; G3)

oppure

=C3 & "/" & E3 & "/" & G3

mentre in K5 ==> =DATA.VALORE(K3)

In C5 (che è il riferimento dell'unione delle celle dell'area C5:G5 -  vedi successiva "Nota 2") metto la soluzione richiesta basata sulle celle di supporto K3 e K5:

=SE(E(C3="";E3="";G3="");"";SE(VAL.ERRORE(K5);"Errore!";SE(K5<OGGI();"PASSATO";SE(K5>OGGI();"FUTURO";"PRESENTE"))))

Il test che controlla se le celle C3, E3 e G3 siano vuote o meno può essere scritto in vari modi:

=E(C3="";E3="";G3="")
=E(CELLA("Tipo";C3)="b";CELLA("Tipo";E3)="b";CELLA("Tipo";G3)="b")
=((C3 & E3 & G3)="")

Soluzione E1.07:ritorna all'indice

Se immagino di aver rinominato le celle (H4 con X, B3 con X₁, C3 con X₂, D3 con X₃ e E4 con X₄) una soluzione accettabile è:

=O(X<X₁;E(X<X₃;X>X₂);X>X₄)

oppure:

=NON(O(E(X<X₂;X>X₁);E(X<X₄;X>X₃))

VARIANTE 1.07: In figura viene mostrato come ordinare un elenco di 4 numeri casuali appartenenti all'intervallo [3.5,11,2)

Soluzione E1.08:ritorna all'indice

- Sono dentro il rettangolo quando:

semplificando ottengo:

=E(X>a;X<b;Y<d;Y>c)

- Sono fuori dal rettangolo quando:

pertanto quando la seguente funzione restituisce VERO.

=O(X<a;X>b;Y>d;Y<c)

Se un punto (X,Y) non è dentro e neppure fuori dal rettangolo allora è sul bordo per cui non serve costruire la condizione associata (peraltro la più complessa delle tre!). La soluzione richiesta è:

=SE(E(I15<K12;I15>G12;C8<E6;C8>E10);"dentro";SE(O(I15>K12;I15<G12;C8>E6;C8<E10);"fuori";"sul bordo"))

Soluzione E1.09:ritorna all'indice

L'intersezione delle 3 aree sottostanti (dentro la circonferenza di raggio r₂, fuori dalla circonferenza di raggio r₁, nel 1° o nel 3° quadrante)

corrisponde all'area colorata di blu in figura

 pertanto la formula:

restituisce VERO quando il punto (X,Y) appartiene ad una delle 2 porzioni di corona circolare colorate di blu.

La soluzione richiesta è quindi:

=SE(E((C3*C3+D3*D3)>D8*D8;(C3*C3+D3*D3)<D12*D12;C3*D3>0);"E' dentro";"Non è dentro")

oppure

=SE(E((C3*C3+D3*D3)>D8*D8;(C3*C3+D3*D3)<D12*D12;C3*D3>0);"E'";"Non è") & " dentro"

Soluzione E1.010:- Non trattata a lezione e non propedeutica per i test finaliritorna all'indice

La soluzione è già inserita nel file che avete scaricato per svolgere l'esercitazione XLS.1