NOTE RELATIVE ALLA SEZIONE EXCEL TRATTATE DURANTE LE LEZIONI - CORSO A-L

ATTENZIONE: Le seguenti note diventeranno definitive e non verrà apportata più alcuna modifica a partire dal 25/12/2023.

X1 - NOTE OPERATIVE

LEZIONE 18/09/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

>> L'interfaccia è la componente di un'applicazione che si occupa di gestire la comunicazione con l'utente. Viene detta anche Front-End. L'interfaccia standard delle applicazioni prevede che nel menu (ribbon) FILE vengano inseriti i comandi relativi al salvataggio, apertura e stampa dei documenti prodotti con quel software.

>> I documenti prodotti con un foglio elettronico si dicono "Cartelle di lavoro". Sono costituiti da un insieme di "Fogli di lavoro"  contenenti "celle" disposte in forma tabellare.

Precisazioni - Slide 12 - X1 - Note operative

>> Osservazione 1:

Le principali componenti che caratterizzano un computer sono:

CPU o processore: è il cervello del computer, capace di eseguire le istruzioni che compongono un programma

RAM: è la memoria del calcolatore. In Ram vengono caricati i file dati da modificare e i programmi che dovranno essere eseguiti dalla CPU. La Ram per memorizzare i dati ha bisogno di essere continuamente alimentata. Se il flusso di corrente elettrica si interrompe (ad esempio quando si spegne il pc) la RAM si azzera.

Programma o Software o Eseguibile : rappresenta l'esperienza del computer (capacità di risolvere problemi). E' composto da una sequenza di istruzioni che la CPU riconosce ed è in grado di eseguire.

Hard disk o HD: rappresenta la biblioteca. E' il serbatoio dove vengono immagazzinati i programmi e i file dati. Un programma per essere  utilizzato deve essere ricopiato dall'Hard disk all'interno della RAM. Analogamente anche i files dati per essere modificati devono essere trasferiti in RAM. Terminate le modifiche i file dati vanno aggiornati sull'hard disk mediante i comandi "Salva" e "Salva con nome". La memoria dell'Hard disk non si azzera quando il computer viene spento.

>> Osservazione 2:

L'avvio di Excel consiste nel caricare in RAM il programma presente nell'hard disk. La CPU lo esegue e le modifiche ai contenuti della cartella di lavoro vengono temporaneamente registrate in memoria RAM. Con l'operazione "salva/salva con nome" le modifiche vengono archiviate in modo duraturo all'interno del hard disk.

Nella figura viene illustrato cosa succede quando eseguo Excel e creo una nuova cartella di lavoro

Quindi i menu "salva" e "salva con nome" sono equivalenti quando si tratta di una nuova cartella di lavoro (ovvero mai registrata sull'hard disk!).

Per i documenti già salvati l'uso del menu "salva" comporta l'aggiornamento immediato su disco fisso delle modifiche apportate a video alla cartella di lavoro in RAM.

Il menu "salva con nome"  viene utilizzato per salvare con un nome differente una cartella di lavoro che è stata caricata aprendo un file già esistente sul disco fisso. In questo modo il file originale (quello aperto inizialmente!) resta invariato. Questo menu risulta utile quando il documento di partenza contiene un modello di calcolo vuoto che continuo a riutilizzare inserendo dati differenti a seconda delle necessità.

Nella figura viene illustrato cosa succede  quando eseguo Excel e apro una cartella di lavoro già esistente sul Hard disk.

Precisazioni - Slide 50, 51, 85-87 - X1 - Note operative

Shortcut di spostamento sul foglio:

ctrl + freccia in giù => si sposta sull'ultima cella in basso che contiene dei dati - se le celle sottostanti sono vuote va in fondo al foglio di lavoro
ctrl + freccia in alto => si sposta sulla prima cella a sinistra che contiene dei dati - se le celle a sinistra sono tutte vuote si sposta sulla prima colonna del foglio
ctrl + freccia a destra => si sposta sull'ultima cella a destra che contiene dei dati - se le celle a lato sono tutte vuote si sposta sull'ultima colonna a destra del foglio
ctrl + freccia a sinistra => si sposta sulla prima cella a sinistra che contiene dei dati - se le celle a sinistra sono tutte vuote si sposta sulla prima colonna del foglio

Shortcut di chiusura:

Alt-F4 => chiude l'applicazione attiva. Se non ho applicazioni attive in primo piano (ad esempio quando vado sul desktop con la combinazione di tasti "Bandierina+D") viene richiesta la chiusura di Windows

Shortcut di annullamento/ripetizione: - slide 50 - X1 Note operative

ctrl+z => annullamento dell'ultima operazione conclusa
ctrl+y => ripeto l'ultima operazione conclusa oppure ripristino la situazione prima dell'ultimo annullamento effettuato con ctrl+z
esc => annullo l'operazione in corso (quindi non ancora terminata!)

Shortcut di selezione sul foglio:

ctrl+shift+freccia in giù => seleziona dalla cella corrente fino all'ultima cella sottostante che risulta non vuota
ctrl+shift+freccia a destra => seleziona dalla cella corrente fino all'ultima cella a destra che risulta non vuota

Shortcut per il copia/taglia/incolla: - slide 51 - X1 Note operative

ctrl+c => copia
ctrl+ins => copia di sistema (Win)
ctrl+v => incolla
shift + ins => incolla di sistema (Win)
ctrl+x => taglia
shift + CANC => taglia di sistema (Win)

La combinazione copia + incolla viene usata per la duplicazione (in alternativa a ctrl+ (drag & drop) sull'oggetto da duplicare) mentre taglia + incolla per lo spostamento (oppure drag & drop sull'oggetto da spostare)

Precisazioni - sostitutiva delle Slide 97-98 - X1 - Note operative

>> Nelle applicazioni, la selezione specifica quale sarà l'oggetto che dovrà subire l'azione decisa dall'utente.
>> Per selezionare una singola cella basta cliccarci sopra.
>> Per selezionare più celle esistono 2 modalità:
- selezione estesa : clicco dapprima su una delle celle poste su uno degli angoli dell'area da selezionare, successivamente clicco sulla cella diagonalmente opposta tenendo premuto il tasto shift;
- selezione disgiunta :dopo aver selezionato la prima cella aggiungo le successive cliccandovi sopra e tenendo contemporaneamente premuto il tasto control)

Note per lo studente:

Nota per il docente: riprendere dalla slide 29 (le  slide 93-100 sono state ilustrate - mostrare la divisione del foglio in 4 frame)

LEZIONE 25/09/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

Precisazioni - Slide 29 - X1 - Note operative

Excel gestisce solo due tipologie di dato: numeri e testi. I dati che Excel riconosce come numerici sono allineati a destra mentre quelli testuali a sinistra!

Si può forzare l'interpretazione di un numero come se fosse testuale mettendo all'inizio un singolo apice '. Questa modalità è utile quando si deve inserire una sequenza di simboli numerici (numerale!) che inizia per 0 come ad esempio:
- la matricola di uno studente,
- il numero di telefono
- il CAP di una determinata località.

Si osservi l'allineamento a sinistra del dato 29/02/2015 (apparentemente cronologico!). Questo perché Excel ha capito che non si tratta di una data (il 2015 non è bisestile!) e pertanto la tratta come fosse una sequenza di simboli (in informatica tale sequenza prende il nome di stringa ovvero una sequenza di caratteri che usa la tabella ASCII come codifica!)

Precisazioni - Slide 99-100 - X1 - Note operative

>> La divisione dello schermo in sottofinestre consente:
      - di semplificare notevolmente la gestione dei fogli di lavoro estesi.
     -  di mantenere visibili le intestazioni di colonna e i dati significativi quando mi muovo all'interno di elenchi.

Precisazioni - Slide 30-32 - X1 - Note operative

Maschera di formato => permette di visualizzare un dato in modo differente da come è stato effettivamente registrato all'interno di una cella. La maschera di formato non modifica il contenuto di una cella ma lo mostra solo in modo differente. Ad esempio una cella che contiene la data 24/09/2021, utilizzando la maschera di formato gggg, mostrerà solo il nome del giorno della settimana (ovvero "venerdì").

Provare i seguenti esempi:

12 => 12/01/1900 (se la maschera di formato è gg/mm/aaaa)  - Vedi Osservazione successiva
 1000 => € 1.000,00 (se la maschera di formato è € #.##0,00)
07/11/2019 => 7 (se la maschera di formato è g)
07/11/2019 => 07 (se la maschera di formato è gg)
07/11/2019 => Gio (se la maschera di formato è ggg)
07/11/2019 => Giovedì (se la maschera di formato è gggg)
07/11/2019 => 11 (se la maschera di formato è m)
07/11/2019 => 11 (se la maschera di formato è mm)
07/11/2019 => nov (se la maschera di formato è mmm)
07/11/2019 => novembre (se la maschera di formato è mmmm)
07/11/2019 => 19 (se la maschera di formato è aa)
07/11/2019 => 2019 (se la maschera di formato è aaaa)
07/11/2019 => Brescia, giovedì 7 novembre 2019 (se la maschera di formato è "Brescia," gggg, g mmmm aaaa)
 12 => 00012 (se la maschera di formato è 00000)
 1000 => 1.000 (se la maschera di formato è #.##0)
1000 => Lire 1.000 (se la maschera di formato è \L\i\r\e #.##0)
01/01/1900 => 1 (se la maschera di formato è 0) Motivo? Le date sono registrate come numero di giorni rispetto alla data di riferimento 1/1/1900 (vedi Osservazione successiva). La maschera di formato 0 trasforma una data nel numero intero di giorni corrispondente.

Per effettuare le prove scrivete il valore in blu in una qualsiasi cella.

Con il tasto destro del mouse richiamate la voce "Formato celle ...". Si apre la finestra "Formato Celle". Selezionate il pannello "Numero" (A) e la categoria "Personalizzato" (B). Nella casella di testo "Tipo:" (C) incollate la corrispondente maschera di formato, evidenziata negli esempi con la scritta in rosso.

Dopo aver cliccato sul pulsante OK la cella visualizza il testo che negli esempi è scritto in verde.

Ulteriori esempi di maschera di formato sono presenti nella slide 52 della sezione "X1 - Note operative": da consultare per i test!

>> Osservazione:

Excel utilizza come codifica interna per le date il numero di giorni trascorsi rispetto ad una data di riferimento che è il 1/1/1900. Quindi il 1/1/1900 viene codificato con 1, il 2/1/1900 con 2 e così via (oggi ha  come codifica il numero intero 44663. Tanti sono i giorni trascorsi a partire dal 1/1/1900 fino alla data odierna) . Questa modalità permette ad Excel di calcolare la distanza in giorni che intercorre tra 2 date (Dataf e Datai), con una semplice differenza (Nr. giorni trascorsi=Dataf - Datai). Questa informazione è indispensabile in alcuni calcoli di natura finanziaria, correlati al trascorrere del tempo.

>> Osservazione - Rappresentazione interna di Excel per i dati orari

L'orario in Excel è registrato come un numero reale tra 0 e 1. Ad esempio il valore 0,5 corrisponde a mezzogiorno mentre 0,75 alle 18.00. Tale rappresentazione permette agli operatori algebrici di somma e differenza di fornire risultati cronologicamente plausibili. Infatti se alla codifica delle ore 12:00 sommo la codifica associata a 6 ore ottengo come risultato algebrico 0,75 ovvero la codifica delle 18:00 che dal punto di vista cronologico corrisponde effettivamente a 6 ore dopo le 12:00! In altre parole l'operatore algebrico di somma con questa codifica si comporta alla stessa stregua di un ipotetico operatore di somma oraria.

Precisazioni - Slide 33 - X1 - Note operative

Il termine font è sinonimo di carattere. I font presenti all'interno di un'applicazione (Excel ad esempio!) sono quelli forniti dal sistema operativo (Mac, Windows, Linux) del pc su cui lavoro. Pertanto se imposto in una cartella di lavoro un carattere particolare (presente sul mio PC!) e successivamente la riapro su un computer differente vedrò quel font solo se questo è installato anche sul sistema di destinazione. Nelle immagini sottostanti le cartelle WINDOWS e MAC che contengono i font di sistema.

Precisazioni - Slide 36 - X1 - Note operative

Per inserire un ritorno a capo all'interno di una cella basta premere ALT+INVIO

Precisazioni - Slide 39-40 - X1 - Note operative

Se unisco un insieme di celle il riferimento da utilizzare è quello della cella che nell'area è posta in alto a sinistra. Quindi per riferirsi all'unione delle celle B2:D4  dovrò usare come riferimento B2

Precisazioni - Slide 49 - X1 - Note operative

I-beam è la barretta verticale che appare quando modifico un testo ed evidenzia il punto dove verranno inseriti i caratteri che digito.

Quando modifico il contenuto di una cella (doppio click sulla cella!) con il tasto CANC elimino il carattere a destra dell'I-beam mentre con il BACKSPACE cancello il carattere che sta a sinistra dell'I-beam.

Precisazioni - Slide 62-63- X1 - Note operative

L'alfabeto di riferimento per il computer è la tabella ASCII ed è composto da 256 simboli.

Cosi come l'alfabeto, appreso alle elementari, determina l'ordinamento delle parole nel nostro vocabolario analogamente la codifica ASCII definisce la relazione d'ordine per le stringhe (sequenze di simboli ASCII!) all'interno del calcolatore. La relazione d'ordine per le stringhe viene detta "lessicografica". Nella tabella ASCII la relazione tra i simboli qui sotto indicata risulta vera:

... < "0" < "1" < ... < "9" < ... < "A" < "B" < ... < "Z" < ... < "a" < "b" < ... < "z" < ...

Pertanto le seguenti relazioni d'ordine sono per ASCII corrette:

"CASA" < "CASETTA" => le prime 3 lettere sono uguali per cui il confronto parte dalla 4° lettera!
"ZATTERA" < "casa" => la prima lettera "Z" è maggiore di "c" in ASCII!
"22/12/1900" > "22/11/2020" => I primi 4 simboli sono  uguali per cui il confronto parte dal 5° simbolo. L'ordinamento lessicografico sembra incompatibile con quello umanamente atteso per quanto riguarda i dati di natura cronologica.
 "1232" < "191" => La prima cifra è uguale per cui il confronto lessicografico parte dalla 2° cifra. L'ordinamento lessicografico sembra incompatibile con quello umanamente atteso per quanto riguarda i dati di natura numerica.

Il doppio apice " indica che la sequenza non ha un significato cronologico o numerico ma si tratta di una successione di simboli ASCII (in EXCEL al posto del " si usa il singolo apice iniziale ').

Il contenuto visualizzato in una cella quando scrivo la sequenza '00012 è identico a quello che vedo quando digito: ="00012". Excel in entrambi i casi considera quel contenuto come  un testo ASCII. Qualsiasi sequenza di questo tipo viene detta "stringa".

>> Osservazione 1: Ordinamenti lessicografici applicati a stringhe numeriche con EXCEL

Nell'esempio sottostante si osservi come la differente interpretazione del tipo di dato (numeri nell'elenco a sinistra e stringhe in quello a destra) determina nella 2° sequenza (quando uso, nel ribbon "dati", il pulsante "ordina"!) un ordinamento umanamente inatteso (corretto però dal punto di vista del calcolatore!).

>> Osservazione 2: Ordinamenti lessicografici applicati a stringhe cronologiche con EXCEL

In questo successivo esempio si osservi come l'utilizzo del tipo testo per rappresentare le date  (ad esempio "25/10/2021") non permette di ottenere un ordinamento  (quando uso, nel ribbon "dati", il pulsante "ordina"!)  cronologicamente corretto (però valido dal punto di vista del calcolatore!).

Se applico l'ordinamento lessicografico ad un elenco di date codificate come stringhe scrivendole in questo modo "AAAA/MM/GG" (pertanto scrivo prima l'anno, poi il mese ed infine il giorno - Ad esempio al posto di 8 novembre 2021 scriverò: "2021/11/08" !) ottengo un elenco che risulta ordinato anche dal punto di vista cronologico (vedi figura sottostante!). Quindi con tale codifica del tempo ("AAAA/MM/GG") l'ordinamento lessicografico diventa compatibile con quello cronologico.

Precisazioni - Slide 63-64 - X1 - Note operative

Excel è nato come programma per automatizzare le operazioni di calcolo. Comunque la sua struttura tabellare è adatta anche alla gestione di semplici archivi (flat data). Nel ribbon Dati troviamo 2 funzionalità tipiche dei programmi database: la ricerca (Filtro) e l'ordinamento (Ordina).

Precisazioni - Slide 64 - X1 - Note operative

Il contenuto di una cella è una formula quando inizia con =.

Esempio:
=A1 => con la seguente formula mostro nella cella che la contiene il valore inserito nella cella A1.
="Ciao" => mostra la stringa costante "Ciao" - equivalente a: 'Ciao
=SOMMA(A1:A10) => calcola la somma dei contenuti nell'area A1:A10

Qualora si scriva una formula senza iniziare con il simbolo = Excel la interpreta come una stringa (sequenza di caratteri ASCII!) e la ripropone pari pari senza elaborarla.

Operazioni algebriche con EXCEL

Vediamo il simbolismo utilizzato da Excel per le operazioni algebriche.

=A1+A2 => sommo le 2 celle
=A1*A2 => moltiplico il contenuto delle 2 celle
=A1/A2 => divido A1 per A2
=A1^A2 => potenza A1A2
=A1-A2 => differenza

>> Osservazione: (non richiesta nei test)

La funzione =TESTO.FORMULA(Indirizzo) mostra il testo della formula contenuta nella cella il cui indirizzo è passato come argomento.

Precisazioni - Slide 3- X3 - Macro

Quando voglio riferirmi ad una cella contenuta nello stesso foglio basta utilizzare l'indice di riga e di colonna. Ad esempio =A1.

Se devo riferirmi ad una cella contenuta in un altro foglio della stessa cartella di lavoro bisogna specificare anche il nome del foglio. Ad esempio per indicare la cella A1 del foglio di lavoro "Gennaio 2023" occorre scrivere:

='Gennaio 2023'!A1

Si evidenzia che la presenza del delimitatore ' (singolo apice) è obbligatorio solo se nell'etichetta del foglio sono presenti spazi. Qualora il nome del foglio non abbia spazi posso ometterlo come si vede in questo esempio:

=Gennaio2023!A1

Qualora la cella appartenga ad un'altra cartella di lavoro l'indirizzo da utilizzare assume una forma più estesa:

='PercorsoCartellaCheContieneIlFileXLSX[NomeFile.xlsx]Nome Foglio'!RiferimentoLocaleCella

come ad esempio:

='C:\Users\cormatron\Desktop\[Anno2023.xlsx]Resoconto 2023'!A1

Se la cartella di lavoro contenente la cella a cui voglio riferirmi risulta aperta all'interno di Excel allora l'indirizzo può essere scritto omettendo il percorso completo del file, lasciando solo il nome della cartella di lavoro, il nome del foglio e il riferimento locale alla cella

='[Anno2023.xlsx]Resoconto 2023'!A1

Precisazioni - Slide 65-66 - X1 - Note operative || Slide 3 - X3  Macro / Formule e funzioni

>> Osservazione:

Gli argomenti di una funzione sono separati dal ; .Gli argomenti possono essere delle  aree (B2:B7), delle singole celle (B10), delle costanti (33) oppure delle funzioni. Ad esempio:

=SOMMA( B2:B7; B10; B13;33 ; CASUALE.TRA(1;100) )

Un'area viene indicata in EXCEL in questo modo:

IndirizzoCellaInAltoASinistra:IndirizoCellaInBassoADestra

Ad esempio:

A1:B20

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=OGGI()
=ADESSO()
 =ORA() - non richiesta nei test

Note per lo studente:

Nota per il docente: riprendere dalla slide 66: ..

LEZIONE 02/10/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

Precisazioni -  Slide 65 - X1 - Note operative || Slide 3 - X3  Macro / Formule e funzioni

Precisazioni - Slide 66,68- X1 - Note operative

Lo spazio come separatore tra 2 aree di celle è utilizzato per identificarne la loro intersezione (vero in EXCEL). Se l'intersezione è vuota verrà visualizzato il codice di errore: #NULL!. Esempi:

(A1:F2 A1:B5 ) (A1:B2)

(A1:B2 C3:F5 ) #NULL!

Precisazioni - Slide 67- X1 - Note operative

>> Osservazione 1:

Analizziamo perchè le 2 formule, apparentemente simili, generano errori differenti.

=1+"Ciao" #VALORE! - si osservi la presenza degli apici!

=1+Ciao #NOME? - si osservi l'assenza degli apici!

Nel 1° caso l'errore è determinato dall'incompatibilità dei tipi sottoposti all'operazione di somma. Non ha senso sommare un numero con una sequenza di caratteri ASCII! Si ricorda che la notazione che prevede di scrivere una sequenza di simboli racchiusa tra " (doppi apici) indica che quella è una stringa ovvero una sequenza di caratteri ASCII! Pertanto  "123" , "AVE" sono stringhe!

Nel 2° caso, Ciao appare scritto senza doppi apici quindi non è  una stringa ma un potenziale nome associato ad una cella. Se non esistono celle che si chiamano in quel modo Excel segnala che non l'ha riconosciuta mostrando l'errore  #NOME?. E' una situazione simile a quando scrivo una formula male (ad esempio =SOMMMMMMA(A1:A10)) ed Excel, non riconoscendola, visualizza il medesimo errore: #NOME?.

Nell'immagine sottostante una formula analoga =1+AVE non determina alcun errore  poichè la cella B2 si chiama davvero AVE  ed inoltre il suo contenuto è un numero (2!) ovvero è compatibile con l'operatore di somma +.

Nell' immagine successiva compare l'errore #VALORE! poichè esiste una cella che si chiama AVE  (B2 !) ma il suo contenuto CIAO (che scritto all'interno di una cella è equivalente a ="CIAO"  o 'CIAO ovvero è ancora una stringa!) risulta incompatibile con l'operatore di somma +.

>> Osservazione 2:

Una cella vuota in una formula che richiede un calcolo di natura numerica viene interpretata come se contenesse zero.

Precisazioni - Slide 68- X1 - Note operative

L'errore #NUM! è segnalato quando il valore restituito non è rappresentabile all'interno di Excel oppure quando il parametro passato alla funzione non appartiene al suo dominio. Ad esempio:

a) quando calcolo la radice quadrata di un numero negativo

b) quando ottengo un risultato talmente elevato (=1000^1000) che Excel non riesce a rappresentare.

Ecco alcune formule che generano l'errore #NUM!.

=0^0
=1000^1000
 =RADQ(-1)
=LN(0)
=LOG(-1)
=DECIMALE.BINARIO(-513;10) => il valore è fuori dal range [-2n-1,2n-1-1] dove n=10

Precisazioni - Slide 69- X1 - Note operative

I file di Excel vengono progettati come se fossero schemi computazionali (privi dei dati!) da utilizzare per le operazioni di calcolo ricorrenti. Quando necessario, il file contenente lo schema di calcolo, viene "riempito" con dati specifici e salvato (usando il menu "Salva con nome") con un nome differente che ricorda il contesto al quale quei dati si riferiscono (FestaCollebeato.xlsx ad esempio!). Il file con lo schema rimane pertanto "pulito" ed è pronto per essere riutilizzato in una successiva sessione di calcolo. Quindi un file di Excel, seppur utilizzato per risolvere computi estemporanei fornisce maggiori benefici quando è pensato per soddisfare esigenze di calcolo che si ripetono periodicamente nel tempo.

>> Osservazione 1:

Scrivere in una cella 10% equivale a scrivere 0,1

>> Osservazione 2:

La stringa vuota o nulla è indicata con: ""  (2 doppi apici in sequenza senza alcun carattere nel mezzo!). Per valutare se la cella contenente il numero di invitati (B1) nello schema delle feste è vuota dovrò scrivere B1="".

Lo spazio viene rappresentato  con la stringa " "

Una sequenza di simboli racchiusa tra doppi apici (detta stringa) utilizza come codifica interna nel calcolatore la tabella ASCII. Pertanto scrivere "123456" è differente da 123456. La prima è una sequenza di simboli Ascii mentre la seconda è un numero.

METODI DI INDIRIZZAMENTO E COPIA ED INCOLLA POSIZIONALE

TIPOLOGIA DEI RIFERIMENTI:

Riferimento relativo:
Sono gli indirizzi delle celle privi del simbolo $. Un esempio è =A1. I riferimenti relativi cambiano nelle formule con l'operazione di "copia ed incolla" poiché il loro significato è di natura posizionale.

Riferimento assoluto:
I riferimenti assoluti sono riconoscibili per la presenza del $ nella scrittura dell'indirizzo sia nella coordinata di riga che di colonna. Pertanto l'indirizzo =$A$1 appartiene a questa tipologia. I riferimenti assoluti non cambiano con l'operazione di "copia ed incolla". Per trasformare un indirizzo relativo in un indirizzo assoluto si deve selezionare il riferimento alla cella e premere il tasto F4.

Riferimento misto:
Sono gli indirizzi delle celle dove solo una delle 2 coordinate presenta il  $. Un esempio è  =$A1 oppure  =A$1. Le coordinate che hanno il $ restano le stesse nelle operazioni di duplicazione (esempio copia ed incolla) e di spostamento (esempio taglia ed incolla) mentre le altre cambiano rispettando la modalità posizionale precedentemente descritta. I riferimenti misti vengono utilizzati quando i dati richiesti in una formula sono distribuiti lungo la stessa linea o la stessa ccolonna. Vediamo l'esempio della tavola pitagorica

COPIA ED INCOLLA POSIZIONALE.

Gli indirizzi relativi (quelli che non presentano nella loro codifica il simbolo $!), presenti all'interno di una formula, non vengono mai interpretati in modo assoluto ma  posizionale.

Esempio

Si supponga che la cella B2 contenga la formula "=A1". L'interpretazione posizionale che un foglio elettronico da alla formula "=A1" è la seguente: "visualizza il contenuto della cella posta una colonna a sinistra e una riga sopra rispetto alla cella contenente la formula in esame (B2)". L'interpretazione assoluta "=$A$1" invece considera la formula in questi termini: "Mostra il contenuto della cella con indirizzo A1".

Quando copio ed incollo la formula della cella B2 nella cella B3 il significato posizionale descritto ("mostra il contenuto della cella posta una colonna a sinistra e una riga in alto rispetto alla cella contenente la formula in esame") viene mantenuto. 

Questa modalità risulta estremamente utile quando occorre replicare delle formule in un elenco molto esteso.

Qualora i riferimenti generati con il copia ed incolla posizionale risultino inesistenti vedremo l'errore =#RIF! .

Esempio:

Nella cella A2 abbiamo la formula posizionale: "=Contenuto della cella che sta una riga sopra". L'incolla di tale formula posizionale in B1 produce l'errore indicato poichè non esiste una cella superiore a B1.

Precisazioni - Slide 70-72- X1 - Note operative

Definire un nome per una cella o un'area presenta 2 vantaggi:
1) Maggior leggibilità delle formule
2) Maggior versatilità: se cambio ad un nome gli abbinamenti alle celle associate automaticamente tutte le formule che adoperano quel nome utilizzeranno i nuovi riferimenti senza dover apportare ulteriori modifiche.

Precisazioni - Slide 73-75- X1 - Note operative

Il blocco delle celle e l'occultamento delle formule viene utilizzato principalmente per 2 motivi:

1) occultare le formule per metterle al riparo da occhi indiscreti  (a protezione del proprio know-how!)
2) evitare che utenti maldestri modifichino inavvertitamente il contenuto delle formule (ad esempio le cancellino involontariamente!).

Precisazioni - Slide 78-80 - X1 - Note operative

Per comportamento predefinito Excel ricalcola tutte le formule contenute nella cartella di lavoro corrente con l'obiettivo di fornire risultati sempre aggiornati.  In presenza di una grande quantità di formule il ricalcolo automatico rallenta considerevolmente il funzionamento di Excel. L'attesa tra un input e il successivo può diventare molto lunga per via dell'enorme mole di operazioni che vengono eseguite dal ricalcolo automatico ad ogni aggiornamento. Disattivare il ricalcolo automatico, come mostrato durante la lezione, potrebbe essere una valida soluzione al problema, soprattutto se l'insieme dei dati che sto inserendo produce esiti transitori che non sono l'oggetto finale della mia indagine.

Precisazioni - Slide 81-89 - X1 - Note operative

L'utilizzo dell'Input vincolato permette di uniformare la scrittura dei dati.
Esempio: per indicare il dipartimento di uno studente iscritto ad Ingegneria Gestionale possiamo utilizzare una tra le seguenti scritture:

Dipartimento di Ingegneria Meccanica e Industriale
Dip. Ing. Mecc. Ind.
DIMI
D.I.M.I

Per l'utente umano le 4 notazioni sono equivalenti (assumono il medesimo significato!) ma in una estrazione dati (filtro!) gli studenti del D.I.M.I, del Dip. Ing. Mecc. Ind. e del DIMI non verranno visualizzati quando ricerco con Dipartimento di Ingegneria Meccanica e Industriale.

Uniformare l'inserimento dei dati quindi garantisce una ricerca dell'informazione corretta.

Esempi presi dai test d'esame relativi alla sezione "X1 -  NOTE OPERATIVE":

X3 - MACRO

>> Osservazione:

CONDIZIONI:

Una condizione è una formula che restituisce solo 2 valori: VERO o FALSO (costanti booleane). Si ricorda che le costanti booleane non vanno mai racchiuse tra doppio apice poichè non sono delle stringhe (sequenze di simboli o caratteri ASCII!). Le condizioni sono importanti poichè rappresentano il principale argomento della funzioni selettive (SE(), CONTA.SE(), etc.)

Gli operatori di confronto che consentono la costruzione di semplici condizioni sono:

>
>=
<
<=
<>  diverso
=

Esempi di condizioni semplici:

=Altezza>100
=Larghezza<=10
=Esito>=18

Si ricorda che il confronto in Excel è case insensitive ovvero

="CIAO"="ciao"

restituisce VERO.

Precisazioni - Slide 35- X3 - Macro

La funzione SE()

La formula =SE(Condizione;"RispostaA";"RispostaB") corrisponde alla frase "Se la Condizione è vera allora scrivo 'RispostaA' altrimenti 'RispostaB'" e pertanto può essere rappresentata con il seguente diagramma a blocchi:

La funzione =SE() permette di replicare all'interno di un foglio di EXCEL la capacità umana di fare una scelta

>> Osservazione:

Ricordarsi che per gestire N output distinti occorre utilizzare N-1 formule SE()

Ad esempio quando gli output da gestire sono 3 un possibile schema di flusso potrebbe essere il seguente:

Si osservi che i blocchi di selezione con le relative condizioni sono 2.

Ulteriore esempio sulla funzione SE():

Valutare se il numero contenuto in una cella con nome mnemonico X è un valore positivo, zero oppure negativo.

Soluzione:

Il problema potrebbe essere schematizzato in questo modo:

Un algoritmo risolutivo potrebbe essere il seguente:

Se il valore di input è maggiore di zero
        scrivo 'Positivo',
Altrimenti
       Se il valore di input è uguale a 0
            scrivo 'Zero'
      Altrimenti
            scrivo 'Negativo

Formalizzandolo con un diagramma di flusso ottengo:

Occorre ora convertire l'algoritmo (codificato in un linguaggio naturale oppure formalizzato con un flowchart!) utilizzando le funzioni presenti nel foglio elettronico. Il quesito del nostro esempio, avendo 3 output possibili, richiede 2 funzioni SE().

Risolviamo il quesito per gradi! Partiamo considerando il flowchart che gestisce solo l'output "Positivo"

a cui corrisponde la formula =SE(X>0;"Positivo";"..."). Per indicare che ho lasciato in sospeso l'analisi relativa agli output "Negativo" e "Zero" utilizzo la stringa "..." (si osservi che è racchiusa tra doppi apici!!).

Adesso mi concentro sulla costruzione della formula che dovrà sostituire i "...". Per evitare di dimenticare le parentesi () e i ; parto sostituendo "..." con SE(;;) ottenendo =SE(X>0;"Positivo";SE(;;) ).

Completo questo SE(;;) con i due output rimasti in sospeso: "Positivo" e "Negativo".

Ottengo quindi la soluzione cercata:

=SE(X>0;"Positivo";SE(X=0;"Zero";"Negativo")).

Cambiando la condizione di partenza produco altre soluzioni equivalenti. Eccone alcune:

=SE(X<0;"Negativo";SE(X>0;"Positivo";"Zero"))
=SE(X=0;"Zero";SE(X<0;"Negativo";"Positivo"))
=SE(X<>0;SE(X<0;"Negativo";"Positivo");"Zero")

Le soluzioni proposte non gestiscono situazioni di errore come ad esempio la scrittura di un testo non numerico. Un possibile schema risolutivo che pone rimedio a tale mancanza è il seguente:

che corrisponde alla seguente formula:

 =SE(Cella("Tipo";X)="v";SE(X>0;"Positivo";SE(X=0;"Zero";"Negativo"));SE(Cella("Tipo";X)="b";"";"ERRORE"))

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=SOMMA(Arg1, Arg2, ...)
=CASUALE.TRA(InteroDA;InteroA)
=RADQ(Numero)
=ASS(Numero)
=SE(Condizione;RispostaSeVera;RispostaSeFalsa)

Note per lo studente:

Nota per il docente: riprendere dall'esercitazione XLS.1

LEZIONE 09/10/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

ESERCITAZIONE XLS1

Precisazioni - Slide 3 - Esercitazione XLS1 - Propedeutico E1.01A, E1.01B, E1.01C - Slide 41 XL3 Macro

>> Osservazione 1:  

La formula

=CELLA("Tipo";A1)

restituisce una delle seguenti costanti testuali a seconda del contenuto di A1.

"b" "l" "v"

La formula

=(CELLA("Tipo";A1)="b")

restituisce VERO se la cella A1 è vuota, FALSO altrimenti. Quindi il suo risultato è una delle 2 costanti booleane:  VERO FALSO.

E' pertanto un esempio di condizione.

>> Osservazione 2:

La condizione

=CELLA("Tipo";IndirizzoCella)="b"

non è possibile scriverla in questo modo:

=CELLA("Tipo";IndirizzoCella)=b

Infatti restituisce l'errore #NOME? poichè la b viene interpretata come se fosse il nome di una cella rinominata (vedi ribbon "Formule" - bottone "Definisci nome").

Precisazioni -  Slide 3 - Esercitazione XLS1 - Propedeutici Pr.E1.01A, Pr.E1.01B, Pr.E1.01C

>> Osservazione 1:

La formula

=CELLA("Tipo";IndirizzoCella)

restituisce "b" solo quando il contenuto della cella con IndirizzoCella è vuoto (quindi al suo interno non ci deve essere scritto assolutamente nulla!). Se la cella contiene una formula  come

=""

allora la funzione CELLA("Tipo";IndirizzoCella) restituisce "l" (del resto  "" è una stringa: esattamente la stringa nulla!).

>> Osservazione 3:  - Esercitazione XLS1 - Propedeutico E1.01A

La seguente condizione

=(CELLA("Tipo";IndirizzoCella)="b")

fornisce il medesimo risultato di:

=(IndirizzoCella="")

solo quando la cella IndirizzoCella è realmente vuota!

Una cella appare vuota se non ho inserito nulla oppure quando contiene una delle seguenti formule: ="" e ' (si vedano le celle B7, B9 e B11 dell'esempio sottostante)

Si osservi che le formule =CELLA("Tipo";B11)="b" e =B11="" restituiscono entrambe VERO quando la cella considerata (B11) è realmente vuota. Negli altri 2 casi (cella contenente ="" o ') le formule =CELLA("Tipo";B7) e =CELLA("Tipo";B9) restituiscono "l" (L minuscola!).

Pertanto:

>> la formula  =CELLA("Tipo";B11)="b" corrisponde alla condizione: "La cella B11 è vuota (non contiene realmente nulla!)?"
>> la formula  =B11="" coincide con la condizione "La cella B11 appare vuota?"

Precisazioni - Slide 3 - Esercitazione XLS1 - Propedeutico E1.01D

Supponiamo di voler scrivere una formula che restituisca VERO quando il contenuto di una cella è zero.

Possiamo osservare che una cella visualizza la cifra zero quando contiene una di queste formule: =0, 0, '0 e ="0".

 Per valutare se una cella contiene zero possiamo usare la formula =IndirizzoCella=0   (valida quando ho uno 0 numerico!) OPPURE  =IndirizzoCella="0" (valida quando ho uno "0" testuale). Pertanto quando il contenuto di una cella è zero (testuale o numerico!) la formula

=O(IndirizzoCella=0;IndirizzoCella="0")

restituisce VERO . La formula O() verrà illustrata in seguito.

Purtroppo EXCEL considera le celle vuote come se contenessero il valore 0. Pertanto la formula precedente

=O(IndirizzoCella=0;IndirizzoCella="0")

restituisce VERO anche quando la cella in esame è vuota (si veda esempio sottostante). Quindi per lo zero numerico non basta usare IndirizzoCella=0 ma occorre imporre  ANCHE IndirizzoCella<>"". Quindi la formula seguente:

=O(E(IndirizzoCella=0;IndirizzoCella<>"") ; IndirizzoCella="")

restituisce VERO solo quando il contenuto è davvero uno zero (non importa se numerico o testuale). La formula E() verrà illustrata in seguito.

Una soluzione alternativa potrebbe essere anche la seguente:

Osservazioni varie sull'uso della funzione SE()

>> Osservazione 1:

La formula

=SE(Condizione;VERO;FALSO)

che restituisce VERO quando la Condizione è vera FALSO altrimenti può essere così semplificata:

=Condizione

>> Osservazione 2:

Per stabilire l'esito di un esame posso scrivere:

=SE(Esito>=18;"Superato";"Non superato")

I doppi apici indicano che si tratta di una sequenza di caratteri ASCII che va riprodotta esattamente come scritta.

Attenzione: se scrivete gli esiti senza racchiuderli tra " (doppi apici)

=SE(Esito>=18;Superato;Non superato)

verrà visualizzato l'errore #NOME?. Questo perché Excel interpreta le sequenze di caratteri non racchiuse tra doppi apici come se fossero chiamate a funzioni oppure nomi di celle che abbiamo definito con il bottone "Definisci nome". Non trovando alcun riscontro di questo tipo Excel mostra l'errore associato a questo genere di situazione.

>> Osservazione 3:

Supponiamo che la cella A1 contenga una formula di tipo booleano. Consideriamo la seguente condizione: (A1=VERO). Attenzione l'= in una condizione va inteso come operatore di confronto e non di assegnamento! Si osservi che quando A1 restituisce VERO anche la condizione (A1=VERO) restituisce lo stesso risultato. Non solo, quando A1 restituisce FALSO anche (A1=VERO) restituisce FALSO . Ne consegue che la condizione (A1=VERO) sia equivalente a A1 e quindi la prima formula può essere semplificata scrivendo semplicemente la seconda:  A1.

Partendo dalle considerazioni fatte segue:

=SE(Condizione=VERO;...)

può essere così semplificata:

=SE(Condizione;...)

Quindi se supponiamo che nella cella A1 vi sia la formula Eta>=18 segue che la formula

=SE(A1=VERO;"Maggiorenne";"Minorenne")

possa essere riscritta in questo modo

=SE(A1;"Maggiorenne";"Minorenne")

>> Osservazione 4:

Attenzione! Quando una sequenza di cifre ha una valenza numerica (come nella formula sottostante!) è errato indicarla come stringa.

=SE(Eta>="18";"Maggiorenne";"Minorenne")

Infatti la condizione potrebbe darmi un risultato inatteso

La modalità corretta è

=SE(Eta>=18;"Maggiorenne";"Minorenne")

Il risultato in questo caso è quello atteso

>> Osservazione 5:

Analogamente FALSO e VERO sono costanti booleane mentre "FALSO" e "VERO" sono stringhe.

Supponiamo che nella cella C4 vi sia la formula Eta>=18.

Come si osserva scrivere:

=SE(C4="VERO";"Maggiorenne";"Minorenne")

fornisce un risultato errato! Quindi la modalità corretta è

=SE(C4=VERO;"Maggiorenne";"Minorenne")

o più semplicemente

=SE(C4;"Maggiorenne";"Minorenne")

FUNZIONI LOGICHE E(), O(), NON() - Implementazione di condizioni  complesse

Le condizioni semplici possono essere combinate tra loro mediante le funzioni logiche E(), O() e NON()

O(cond1; cond2; ....; cond255) => restituisce VERO se almeno una condizione condi è vera, altrimenti FALSO; La funzione O() viene generalmente utilizzata per valutare la non appartenenza di un valore X ad un intervallo [a,b] (intervallo esterno) ==> =O(X<a;X>b). In generale se ho n insiemi Ik la cui condizione di appartenenza è Ck allora la condizione di appartenenza all'unione di tali insiemi è rappresentata dalla condizione O(C1,C2,...,Ck,...,Cn).

E(cond1; cond2; ....; cond255) =>restituisce VERO se tutte le condizioni sono vere altrimenti restituisce FALSO; La funzione E() viene utilizzata per valutare l'appartenenza di un valore X ad un intervallo [a,b] (intervallo interno) ==> =E(X<=b;X>=a). In generale se ho n insiemi Ik la cui condizione di appartenenza è Ck allora la condizione di appartenenza all'intersezione di tali insiemi è rappresentata dalla condizione E(C1,C2,...,Ck,...,Cn).

NON(cond) => nega l'argomento quindi restituisce VERO se cond è FALSO. Viceversa FALSO se cond è VERO;

Le formule sottostanti sono tutte equivalenti:

=SE(O(A1<=a;A1>=b);"Esterno a (a,b)";"Appartiene a (a,b)")
=SE(E(A1>a;A1<b);"Appartiene a (a,b)";"Esterno a (a,b)")
=SE(NON(O(A1<=a;A1>=b));"Appartiene a (a,b)";"Esterno a (a,b)")

Osservazioni varie sulle funzioni E(), O() e NON()

Precisazioni -  Slide 74 - X3 - Macro // Formule e Funzioni

Sia F(X) la funzione che restituisce VERO se X appartiene all'insieme α FALSO altrimenti. Analogamente sia G(X) la funzione che restituisce VERO se X appartiene all'insieme β FALSO altrimenti. Allora:

=O(F(X);G(X)) restituisce VERO se X appartiene all'insieme unione α∪β FALSO altrimenti.
=E(F(X);G(X)) restituisce VERO se X appartiene all'insieme intersezione α∩β FALSO altrimenti.
=E(F(X);NON(G(X))) restituisce VERO se X appartiene all'insieme differenza α-β FALSO altrimenti.

>> Osservazione 1:

Attenzione! La seguente condizione

=E(X>5000;X<20000)

non può essere scritta in EXCEL nel seguente modo:

=(5000 < X < 20000)

>> Osservazione 2:

La formula

=E(Condizione1;E(Condizione2;Condizione3))

restituisce VERO se le 3 condizioni risultano vere pertanto può essere semplificata in questo modo:

=E(Condizione1;Condizione2;Condizione3)

Precisazioni -  Slide 7 - Esercitazione XLS1 - Propedeutico Pr.E1.02B

Nota 1 -  Implementazione dell'operatore E() mediante dei SE() in cascata

La seguente formula

=SE(E(Condizione1;Condizione2);"RispostaA";"RispostaB")

può essere implementata con 2 SE in cascata:

=SE(Condizione1;SE(Condizione2;"RispostaA";"RispostaB");"RispostaB")

quindi i 2 flowchart sottostanti sono equivalenti:

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=CELLA("Tipo";riferimentoCella)
=E(Condizione1; Condizione2; ...; CondizioneN)
=O(Condizione1; Condizione2; ...; CondizioneN)
=NON(Condizione)
=RESTO(Dividendo;Divisore)
=TRONCA(Argomento [;NrCifre])
=INT(Numero)
=ARROTONDA(Numero;NrCifre)
=SE(Condizione;RispostaSeVera;RispostaSeFalsa)

Note per lo studente:

Nota per il docente: assegnata esercitazione 1.03 agli studenti (vedere poi solo le formule principali)

LEZIONE 16/10/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

>> Osservazione

Le formule

=1*FALSO restituisce 0
=1*VERO restituisce 1.

pertanto una qualsiasi formula logica (condizione!) F (che restituisce VERO o FALSO!) costruita con operatori di relazione e funzioni E(), O(), NON() può essere trasformata in una formula che restituisce valori 1 o 0 semplicemente applicando il seguente prodotto

=1*F restituisce 0 se F restituisce FALSO
=1*F restituisce 1. se F restituisce VERO.

oppure usando la formula alternativa

=SE(F;1;0)

Precisazioni -  Slide 13 - Esercitazione XLS1 - Pr.E1.04A

La formula

=CONTA.NUMERI(Area)

conta le celle che contengono un numero oppure una data oppure un orario (si tratta comunque di numeri!). Le celle vuote che Excel interpreta solitamente come 0 (quando sono convertite in numero!!!) non vengono considerate nel conteggio.

Precisazioni -  Slide 13 - Esercitazione XLS1 - Pr.E1.04B, Pr.E1.04B-Vuote.Variante

La formula

=CONTA.VUOTE(Area)

conta le celle che risultano vuote oppure che contengono una formula che restituisce la stringa vuota (quindi anche quelle che contengono la formula ="").

Pertanto per sostituire la funzione =CONTA.VUOTE(Area) con la funzione SOMMA e SE occorre utilizzare come condizione la formula (IndirizzoCella="") invece di (CELLA("Tipo";IndirizzoCella)="b"), come si vede nella figura sottostante.

La formula

=SE(SOMMA(F6:F15)<>0;"SI";"NO")

può essere riscritta nel seguente modo:

=SE(SOMMA(F6:F15);"SI";"NO")

poichè qualsiasi valore diverso da zero viene interpretato da EXCEL come se fosse la costante booleana VERO.

Precisazioni -  Slide 13 - Esercitazione XLS1 - Propedeutico Pr.E1.04C

>> Osservazione 1:

La formula

=CONTA.VALORI(Area)

conta le celle che risultano non vuote ovvero quelle che contengono: un testo, un numero  (quindi anche una data!) oppure una formula (pertanto anche ="").

>> Osservazione 2:

La formula

=CONTA.STRINGHE(Area)

non esiste. Può comunque essere emulata con la seguente macro:

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

qualora si voglia considerare la formula ="" come una stringa

oppure con

=10-CONTA.VUOTE(Area)-CONTA.NUMERI(Area)

se si  considera la cella con la formula ="" alla stessa stregua di una cella vuota.

Precisazioni -  Slide 16 - Esercitazione XLS1 - E1.04 Var1

>> Osservazione 1:

La media che viene restituita dalla funzione MEDIA() è calcolata rispetto al numero di celle (associate ai suoi argomenti) che contengono valori numerici (quindi anche una cella contenente 0 verrà considerata nel calcolo!). La funzione media restituisce l'errore #DIV/0 quando le celle associate ai suoi argomenti non contengono alcun valore numerico.

 Posso controllare l'errore #DIV/0 con la formula sottostante:

=SE(CONTA.NUMERI(Area)=0;0;MEDIA(Area))

La funzione

=MEDIA(Area)

risulta equivalente a:

=SOMMA(Area)/CONTA.NUMERI(Area)

Precisazioni -  Slide 16 - Esercitazione XLS1 - Propedeutico E1.04 Variante 1

La funzione CONTA.SE(Area;"Condizione") richiede una stringa come 2° argomento quando nella condizione appare l'operatore di confronto. Ad esempio:

=CONTA.SE(Area;">18") ==> conto i valori maggiori di 18
=CONTA.SE(Area;"<>0") ==> conto i valori diversi da zero
=CONTA.SE(Area;"=0")  ==> conto i valori uguali a zero

altrimenti non è necessario

=CONTA.SE(Area;0)  ==> conto i valori uguali a zero
=CONTA.SE(Area;VERO) ==> conto i valori uguali a VERO

altrimenti non è necessario

>> OSSERVAZIONI SULLE FORMULE NUMERICHE INTERPRETATE COME CONDIZIONI

Considerando che in EXCEL qualsiasi valore diverso da 0, inserito in una formula booleana, viene convertito in VERO mentre lo 0 in FALSO segue che il risultato delle formule sottostanti sia corretto:

=SE(1;"Verità";"Falsità") restituisce la stringa: "Verità"
=SE(0;"Verità";"Falsità") restituisce la stringa: "Falsità"

Quindi la condizione sottostante:

=E(A1<>0;A2<>0;...;A10<>0)

è equivalente alla formula:

=E(A1;A2;...;A10)

Ecco ulteriori esempi:

=(A1=0) è equivalente a: =NON(A1)
=NON(1) è equivalente a: =FALSO
=NON(9) è equivalente a:  =FALSO
=NON(0) è equivalente a:  =VERO
=O(0;0) è equivalente a:  =FALSO
=O(0;1;0) è equivalente a:  =VERO

OSSERVAZIONI SULLE COSTANTI BOOLEANE TRATTATE COME NUMERI

>> Osservazione 1 - numeri trattati come costanti booleane

In Excel qualsiasi valore booleano inserito in un'espressione algebrica viene trattato come uno zero quando è FALSO e come un uno quando è VERO.

>> Osservazione 2 - E(), O() ed equivalenti algebrici

Riassumendo la funzione

E(Condizione1;Condizione2;...;CondizioneN)

può essere emulata con l'espressione algebrica

Condizione1*Condizione2*...*CondizioneN  Il <>0 può essere omesso essendo tutto ciò che è diverso da zero VERO!

Analogamente la funzione

O(Condizione1;Condizione2;...;CondizioneN)

può essere emulata con l'espressione algebrica

Condizione1+Condizione2+...+CondizioneN  Il <>0 può essere omesso essendo tutto ciò che è diverso da zero VERO!

L'equivalenza citata ha senso se le formule algebriche sono inserite in un contesto condizionale come ad esempio il 1° argomento della funzione SE(). Infatti in questo caso tutto ciò che risulta diverso da 0 diventa VERO mentre i risultati uguali a 0 si trasformano in FALSO.

>> Osservazione 3

Scrivere  =SE(X=Y;100;0) è equivalente a =(X=Y)*100.

Non usare mai i doppi apici " quando i valori restituiti devono essere considerati dei numeri. Ad esempio scrivere

 =SE(condizione1;"100";"0") > SE(condizione2;"9";"0")

restituisce FALSO quando le 2 condizioni sono VERE (risultato umanamente inatteso poichè mi aspetterei "100">"9"). E' invece corretto scrivere:

 =SE(condizione1;100;0) > SE(condizione2;9;0) ==> se le 2 condizioni sono vere restituisce VERO

GENERAZIONE DI NUMERI CASUALI  (verrà ripresa in seguito)

>> Osservazione:  Generazione di un valore casuale reale

La funzione CASUALE.TRA(A;B) genera un intero casuale nell'intervallo [A,B].

La funzione CASUALE() genera un valore reale casuale nell'intervallo semiaperto [0,1).

Per generare un valore reale casuale nell'intervallo [A,B) userò la seguente formula:

=(B-A)*CASUALE()+A

Precisazioni -  Slide 17 - Esercitazione XLS1 - E1.04 Var2.C e E1.04 Var2.D

Operazioni testuali con EXCEL

="AVE " & "STUDENTS" =>  & è l'operatore di concatenazione (ottengo infatti "AVE STUDENTS").

Nella 2° possibile soluzione del propedeutico E1.04.var2.C una formula che utilizza la concatenazione al posto della formula logica.

Nella 2° possibile soluzione del propedeutico E1.04.var2.D una ulteriore formula che utilizza la concatenazione al posto della formula logica.

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=CONTA.NUMERI(Area)
=CONTA.VALORI(Area)
=CONTA.VUOTE(Area)
=MEDIA(Area;...)
=CONTA.SE(Area;Condizione)
=SOMMA.SE(AreaValori;Condizione)
=SOMMA.SE(AreaDati;Condizione;AreaValori)
=SOMMA.PIU.SE(AreaSomma;AreaDati1;Condizione1;AreaDati2;Condizione2;...)
=CONTA.PIU.SE(AreaDaTestare1;Condizione1;AreaDaTestare2;Condizione2;...)
=CASUALE()
=CASUALE.TRA(A;B)
=Stringa1 & Stringa2 & ... & StringaN

Nota per il docente: riprendere dall'esercizio 1.05

LEZIONE 23/10/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

Precisazioni -  Slide 19 - Esercitazione XLS1 - E1.05

>> Osservazione:

Quando una cella contiene un valore in percentuale i% (come ad esempio 7%) Excel lo registra come i/100 come si può osservare nell'esempio sottostante.

Precisazioni -  Slide 24 - Esercitazione XLS1 - E1.06

>> Osservazione: esempi funzione DATA.VALORE()

La seguente formula =DATA.VALORE("23/10/2020") restituisce il valore 44127 ovvero il numero di giorni trascorsi rispetto alla data di riferimento che per EXCEL è il 1/1/1900. Vediamo altri esempi:

=DATA.VALORE("1/1/1900") >> restituisce il valore 1
=DATA.VALORE("29/02/2019") >> restituisce #VALORE! poichè non esiste il 29/2/2019 (non è bisestile!).
=DATA.VALORE(29/02/2020) >> restituisce #VALORE! poichè DATA.VALORE vuole come argomento una data scritta come stringa!
 =DATA.VALORE(OGGI()) >> restituisce #VALORE! poichè DATA.VALORE vuole come argomento una data scritta come stringa mentre la funzione OGGI() restituisce il numero seriale associato alla data odierna.

Per trasformare il numero seriale, restituito dalla funzione OGGI(), nella stringa associata occorre utilizzare la funzione TESTO() come si vede nell'esempio seguente.

 =DATA.VALORE(TESTO(OGGI();"gg/mm/aaaa"))

Precisazioni -  Slide 21 - Esercitazione XLS1 - E1.06a

Per testare se una Data è relativa al mese di febbraio possiamo utilizzare le seguenti formule:

=MESE(Data)=2 ==> si ricorda che MESE() restituisce un valore numerico
=TESTO(Data,"m")="2" ==> si ricorda che TESTO() restituisce una stringa e pertanto il 2 va tra doppi apici!
=TESTO(Data,"mm")="02"
=TESTO(Data,"mmm")="feb"
=TESTO(Data,"mmmm")="febbraio"

Precisazioni -  Slide 21 - Esercitazione XLS1 - E1.06b

Per testare se una Data è il primo giorno di un qualsiasi mese possiamo utilizzare le seguenti formule:

=GIORNO(Data)=1 ==> si ricorda che GIORNO() restituisce un valore numerico
=TESTO(Data,"g")="1" ==> si ricorda che TESTO() restituisce una stringa e pertanto il 2 va tra doppi apici!
=TESTO(Data,"gg")="01"

Precisazioni -  Slide 22 - Esercitazione XLS1 - E1.06d

Per testare se il contenuto della cella C4 è una data reale posso utilizzare la condizione:

=CELLA("Tipo";C4)="v" ==> si ricorda che per EXCEL una data è un numero intero!

oppure

=NON(VAL.ERRORE(DATA.VALORE(TESTO(C4;"gg/mm/aaaa")))) ==> si ricorda che DATA.VALORE() vuole una stringa come argomento!

Precisazioni -  Slide 22 - Esercitazione XLS1 - E1.06e

Per calcolare il numero di giorni trascorsi dal 1/1/1900 alla data indicata in C4 posso utilizzare le seguenti formule:

=DATA.VALORE(TESTO(C4;"gg/mm/aaaa")) ==> si ricorda che DATA.VALORE() restituisce un valore numerico pertanto se in C4 ho la data 1/1/1900 allora la formula restituisce 1!

oppure

=TESTO(C4;"0")) ==> si ricorda che TESTO() restituisce una stringa pertanto se in C4 ho la data 1/1/1900 restituisce "1"

>> Bug dell'anno bisestile.

Stranamente la formula

 =DATA.VALORE(TESTO(OGGI();"gg/mm/aaaa"))

restituisce 61. In realtà il 1900 non è un anno bisestile e pertanto il risultato dovrebbe essere 31+28+1=60.  Tale risultato è un bug di EXCEL noto con il nome di "Bug dell'anno bisestile". L'errore è un retaggio storico dovuto ad un antenato dei fogli elettronici: Lotus 1-2-3 che presupponeva che l'anno 1900 fosse bisestile (cosa in realtà falsa!). Quando Microsoft ha rilasciato Excel ha assunto che il 1900 fosse un anno bisestile in modo da consentire l'utilizzo dello stesso sistema di datazione utilizzato da Lotus 1-2-3 (al tempo Lotus 1-2-3 costituiva lo spreadsheet più diffuso in assoluto!). Anche se tecnicamente semplice da correggere, questo bug non venne sistemato in modo da garantire la compatibilità dell'Excel attuale con i file prodotti da versioni precedenti oppure da altri programmi.

FUNZIONE VALORE

>> Osservazione 1:

La formula

=VALORE("STRINGA_NUMERICA")

converte la stringa numerica passata come argomento nel numero corrispondente. Ad esempio:

=VALORE("2321") restituisce l'intero 2321

pertanto se la formula  :

=TESTO(OGGI();"0") restituisce la stringa "45222" (corrisponde al numero di giorni trascorsi rispetto alla data di riferimento 1/1/1900!)

allora la formula

=VALORE(TESTO(OGGI();"0")) restituisce il numero 45222

Il risultato coincide con il valore restituito dalla formula:

=DATA.VALORE(TESTO(OGGI();"gg/mm/aaaa"))

poichè corrisponde al numero di giorni trascorsi rispetto alla data di riferimento 1/1/1900.

Concludendo la formula

=VALORE(TESTO(OGGI();"0"))

è equivalente a:

=DATA.VALORE(TESTO(OGGI();"gg/mm/aaaa"))

>> Osservazione 2:

Sia la data attuale il 23 ottobre 2023. Le formule seguenti

=GIORNO(OGGI())
=MESE(OGGI())
=ANNO(OGGI())

sono equivalenti a:

=VALORE(TESTO(OGGI();"g"))
=VALORE(TESTO(OGGI();"m"))
=VALORE(TESTO(OGGI();"aaaa"))

infatti restituiscono rispettivamente i numeri:  23,  10, 2023.

Si ricorda che la funzione VALORE restituisce un numero mentre la funzione TESTO restituisce una stringa. Pertanto le  funzioni:

=TESTO(OGGI();"g")
=TESTO(OGGI();"m")
=TESTO(OGGI();"aaaa")

restituiscono rispettivamente le stringhe:  "23",  "10", "2023".

FUNZIONE TESTO

Precisazioni -  Slide 27-28 - X3 - Macro // Formule e Funzioni

La funzione TESTO() restituisce una stringa che corrisponde alle direttive associate alla maschera di formato indicata come 2° argomento. Pertanto il risultato restituito non è identico al valore passato come 1° argomento.

Esempio: Nell'immagine sottostante, il valore visualizzato in D4 (Venerdì) è ottenuto cambiando la visualizzazione mediante il pannello "Numero" dentro la finestra di dialogo "Formato Celle". Il valore in D4 resta quello originale (ADESSO())  ed infatti la formula =D4="Venerdì" in F4 restituisce FALSO. Invece il valore in D6 è il risultato ottenuto con la funzione TESTO() con maschera di formato "gggg". La dimostrazione che il valore visualizzato è differente rispetto a quello originale (ADESSO()) è visibile nella cella F6 dove il confronto  =D4="Venerdì" restituisce VERO.

Esempi: ecco alcuni esempi relativi alle date con la funzione TESTO()

=TESTO(ADESSO();"ggg") restituisce: "Ven"
=TESTO(ADESSO();"gggg") restituisce: "Venerdì"
=TESTO(OGGI();"aaaa") restituisce "2020"
=TESTO(OGGI();"aa") restituisce "20"
=TESTO(OGGI();"g") restituisce "9" se oggi è il 9 ottobre
=TESTO(OGGI();"gg") restituisce "09" se oggi è il 9 ottobre
=TESTO(ADESSO();"m") restituisce "1" se oggi è il 9 gennaio
=TESTO(OGGI();"mm") restituisce "01" se oggi è il 9 gennaio
=TESTO(ADESSO();"mmmm") restituisce "Ottobre"
=TESTO(ADESSO();"gggg, g mmmm aaaa") restituisce: "Venerdì, 23 ottobre 2020"
=TESTO(1;"gg/mm/aaaa") restituisce: "01/01/1900"
=TESTO(2,5;"gg/mm/aaaa hh:mm") restituisce: "02/01/1900 12:00"
=TESTO(ADESSO();"0,00") restituisce: "44127,75" se la data/ora attuale è: 23/10/2020 18:00:00
=TESTO(ADESSO();"0") restituisce: "44128" se la data/ora attuale è: 23/10/2020 18:00:00 (arrotonda per eccesso!)
=TESTO(ADESSO();"0") restituisce: "44127" se la data/ora attuale è: 23/10/2020 06:00:00 (arrotonda per difetto!)

Ecco ora quelli relativi ai numeri

=TESTO(1;"00000") restituisce: "00001"
=TESTO(10000;"#.##0") restituisce: "10.000" - ho separatore delle migliaia
=TESTO(1,12351;"0,000") restituisce: "1,124" -  arrotonda per eccesso
=TESTO(1,12350;"0,000") restituisce: "1,124" - arrotonda per eccesso
=TESTO(1,12349;"0,000") restituisce: "1,123" - arrotonda per difetto
=TESTO(10,5001;"0") restituisce: "11" - arrotonda per eccesso
=TESTO(10000,1253;"€ #.##0,00") restituisce: "€ 10.000,13" - arrotonda per eccesso
=TESTO(10000,293;"""Euro ""#.##0,00") restituisce "Euro 10.000,29"

Ecco ora degli esempi con stringhe costanti:

La formula seguente

=TESTO(OGGI();"""Brescia,"" g mmmm aaaa") => visualizza "Brescia, 23 ottobre 2023"

è equivalente a questa

=TESTO(OGGI();"\B\r\e\s\c\i\a\, g mmmm aaaa") => visualizza "Brescia, 23 ottobre 2023"

Attenzione alle seguenti formule

=TESTO(OGGI();"\m\m") => visualizza "mm"

=TESTO(OGGI();"""mm""") => visualizza "mm"

=TESTO(OGGI();"mm") => visualizza "10" se la data attuale è: 23/10/2023

>> Osservazione 1

Il " è utilizzato per delimitare le stringhe. Se si vuol visualizzare il carattere " in un testo occorre raddoppiarlo. In questo modo EXCEL non lo interpreta come carattere delimitatore e pertanto lo visualizza. Vediamo quale risultato appare all'interno di una cella quando contiene le seguenti formule:

=""  => Vedo una cella vuota (si tratta della stringa nulla!)
 =""""  => Vedo " (i due doppi apici esterni servono per delimitare la stringa "" per cui non appaiono, la coppia di caratteri "" viene invece interpretata come un singolo doppio apice).
="Brescia"  => Vedo Brescia (i due doppi apici esterni servono a delimitare la stringa "" per cui non appaiono).
 ="""Brescia"""  => Vedo "Brescia" (i due doppi apici esterni servono a delimitare la stringa "" per cui non appaiono, la coppia di caratteri "" viene invece interpretata come un singolo doppio apice).

="Il titolo è: ""INFORMATICA""." => visualizza: Il titolo è: "INFORMATICA".

Precisazioni -  Slide 24 - Esercitazione XLS1 - E1.06

>> Osservazione 1:

Confrontare una stringa con una data è errato poichè si sta mettendo a confronto 2 tipi di dato differenti. La prima "9/1/2020" è una stringa (sequenza di simboli Ascii!) mentre la seconda è una funzione che restituisce un dato di tipo cronologico rappresentato dal numero di giorni trascorsi rispetto ad una data di riferimento (il valore 1 corrisponde al 1/1/1900!). Pertanto la seguente condizione inserita in una funzione SE() è sbagliata!

=SE("9/1/2020">OGGI(); ...

Per un confronto corretto è necessario convertire la stringa in un dato cronologico ricorrendo alla funzione DATA.VALORE.  Qualora non si ricorra, nell'eser 1.06, alla funzione DATA.VALORE otteniamo un risultato assurdo come quello della figura sottostante (9/1/2020 data futura ?!??!).

Per un confronto corretto è necessario convertire la stringa in un dato cronologico ricorrendo alla funzione DATA.VALORE. La formula va quindi riscritta nel seguente modo:

=SE(DATA.VALORE("9/1/2020")>OGGI(); ...

>> Osservazione 2:

Excel quando trova in una cella qualcosa che assomiglia ad un dato cronologico lo decodifica immediatamente nel numero seriale corrispondente (numero di giorni trascorsi rispetto alla data di riferimento!). Invece se il dato cronologico viene ottenuto mediante una formula che restituisce una stringa (ad esempio con l'operatore di concatenazione &) questa decodifica automatica non avviene. A tale proposito si analizzi l'esempio sottostante

>> Osservazione 3:

La seguente condizione (che coincide con  "tutte le celle sono vuote?")

=E(C3="";E3="";G3="")

è equivalente a:

=(C3 & E3 & G3)=""

>> Osservazione 4:

La condizione  "almeno una cella contiene qualcosa?" può essere implementata in questo modo:

=O(C3<>"";E3<>"";G3<>"")

 oppure in questo modo equivalente:

=(C3 & E3 & G3)<>""

>> Osservazione 5:

La condizione  "almeno una cella è vuota?" può essere implementata in questo modo:

=O(C3="";E3="";G3="")

e corrisponde alla condizione "Nego che tutte le celle siano  piene?". Pertanto la seguente formula risulta equivalente:

=NON(E(C3<>"";E3<>"";G3<>"")

>> Osservazione 6:

La seguente formula

=CONCATENA(A1;A2;A3;A4;A5;A6)

è equivalente a scrivere:

=A1 & A2 & A3 & A4 & A5 & A6

oppure a:

=CONCAT(A1:A6)

>> Osservazione 3:

La funzione =TESTO.UNISCI("";VERO;B5:Q5) non è disponibile nelle versioni di EXCEL precedenti alla 2019. Può essere sostituita con la formula seguente

=CONCATENA(B5;C5;D5;E5;F5;G5;H5;I5;J5;K5;L5;M5;N5;O5;P5;Q5)

oppure con:

=B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5

Precisazioni -  Slide 25 - Esercitazione XLS1 - propedeutici E1.07A e E1.07C

>> Osservazione 1:

=MAX(Aree) => restituisce il valore massimo delle Aree passate come argomento.

=MIN(Aree) => restituisce il valore minimo delle Aree passate come argomento.

>> Osservazione 2: Le seguenti istruzioni sono equivalenti:

=MIN(AREA)
=PICCOLO(AREA;1)
=GRANDE(AREA;n)

dove n è il numero di celle in AREA. Analogamente anche le seguenti sono equivalenti:

=MAX(AREA)
=PICCOLO(AREA;n)
=GRANDE(AREA;1)

>> Osservazione 3:

Le funzioni PICCOLO() e GRANDE() possono essere utilizzate per ordinare degli elenchi numerici come si vede nella figura sottostante

GENERAZIONE DI NUMERI CASUALI

>> Osservazione:  Generazione di un valore casuale reale

La funzione CASUALE.TRA(A;B) genera un intero casuale nell'intervallo [A,B].

La funzione CASUALE() genera un valore reale casuale nell'intervallo [0,1).

Per generare un valore reale casuale nell'intervallo [A,B) userò la seguente formula:

=(B-A)*CASUALE()+A

Esempio 1 - uso funzioni CASUALE() e CASUALE.TRA():

Costruire una funzione che genera casualmente una delle 2 stringhe: "X" o "Y". La frequenza di uscita della parola "X" deve essere identica a quella della parola "Y"

=SE(CASUALE()<0,5;"X";"Y")

oppure

=SE(CASUALE.TRA(0;1)=0;"X";"Y")

Esempio 2 - uso funzioni CASUALE() e CASUALE.TRA():

Costruire una funzione che genera casualmente una delle 2 stringhe: "X" o "Y". La frequenza di uscita della parola "Y" deve essere tripla rispetto a quella della parola "X"

=SE(CASUALE()<0,25;"X";"Y")

oppure

=SE(CASUALE.TRA(1,4)=1;"X";"Y")

Esempio 2 - uso funzioni CASUALE() e CASUALE.TRA():

Costruire una funzione che genera casualmente una delle 2 stringhe: "Versamento" o "Prelievo". La frequenza di uscita della parola "Versamento" deve essere 4 volte superiore a quella della parola "Prelievo"

=SE(CASUALE()<1/5;"Prelievo";"Versamento")

Oppure

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

Esempio 3 - uso funzioni CASUALE() e CASUALE.TRA():

Costruire una funzione che generi casualmente un intero da 1 a 4. La frequenza di uscita di un qualsiasi numero k deve risultare doppia rispetto a quella del numero precedente k-1

Soluzione:

Possiamo quindi dire che ad ogni uscita del numero 1 debbano corrispondere in frequenza 2 uscite del 2, 4 uscite del 3 e 8 uscite del 4 su un totale di 15 estrazioni. Quindi avrò una distribuzione delle frequenze di uscita dei numeri 1, 2, 3 e 4 rispettivamente di 1/15, 2/15, 4/15 e 8/15.

Passiamo ad implementare la soluzione richiesta. Inseriamo nella cella A1 la formula  =CASUALE();

La formula seguente è una possibile soluzione:

=SE(A1<1/15;1;SE(A1<3/15;2;SE(A1<7/15;3;4)))

Se non utilizziamo celle di supporto dobbiamo utilizzare ripetutamente  la  funzione CASUALE() . Questa funzione genera sempre un nuovo valore compreso tra 0 e 1. La seguente formula è errata:

=SE(CASUALE()<1/15;1;SE(CASUALE()<3/15;2;SE(CASUALE()<7/15;3;4)))

Infatti lasciando inalterata la distribuzione iniziale delle frequenze di uscita abbiamo per il 2 una frequenza pari a 2,8/15 (14/15*3/15 - errata visto che è 2/15!) e per il 3 di 5,23/15 (14/15*12/15*7/15 - errata visto che è 4/15!). Pertanto dobbiamo risistemare ad ogni SE() la distribuzione delle soglie di uscita escludendo quelle casistiche abbinate ai valori già considerati nei precedenti SE().

Dovremo quindi utilizzare il seguente schema:

possiamo proporre la seguente formula

=SE(CASUALE()<1/15;1;SE(CASUALE()<1/7;2;SE(CASUALE()<1/3;3;4)))

Oppure utilizzando la funzione SCEGLI()

=SCEGLI(CASUALE.TRA(1;15);4;4;4;4;4;4;4;4;3;3;3;3;2;2;1)

Esempio 4 - uso funzioni CASUALE() e CASUALE.TRA():

Costruire una funzione che generi casualmente un intero da 1 a 3. La frequenza di uscita di un qualsiasi numero k deve risultare tripla rispetto a quella del numero precedente k-1

Soluzione:

Possiamo quindi dire che ad ogni uscita del numero 1 debbano corrispondere in frequenza 3 uscite del 2, 9 uscite del 3 per un totale di 13 estrazioni. Quindi avrò una distribuzione delle frequenze di uscita dei numeri 1, 2, e 3 rispettivamente di 1/13, 3/13 e 9/13.

Inseriamo nella cella A1 la formula  =CASUALE(); La formula seguente è una delle possibili soluzioni:

=SE(A1<1/13;1;SE(A1<4/13;2;3))

Senza l'ausilio di celle di supporto dobbiamo utilizzare più volte la funzione CASUALE(). Analizzando il seguente schema

possiamo proporre la seguente formula

=SE(CASUALE()<1/13;1;SE(CASUALE()<1/4;2;3))

Oppure utilizzando la funzione SCEGLI()

=SCEGLI(CASUALE.TRA(1;13);1;2;2;2;3;3;3;3;3;3;3;3;3)

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=VAL.ERRORE(Formula)
=MESE(Data)
=GIORNO(Data)
=CONCATENA(Stringa1;Stringa2;...;StringaN) oppure CONCAT(Area) oppure Stringa1 & Stringa2 & ... & StringaN
=DATA.VALORE(Stringa)
=PICCOLO(Area;k)
=GRANDE(Area;k)
=MAX(Area)
=MIN(Area)
=TESTO(Valore;MascheraDiFormato)
=VALORE(StringaNumerica)
=SCEGLI(Nr;Val1;Val2;...;ValM)
=CASUALE.TRA(A;B)
=CASUALE()

Nota per il docente: completati propedeutici 1.07

LEZIONE 06/11/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

FUNZIONE ORARIO.VALORE

Precisazione -  Slide 29 - X3 - Macro // Formule e Funzioni

La funzione ORARIO.VALORE restituisce il numero associato alla stringa oraria passata come argomento. Ad esempio

=ORARIO.VALORE("18:00") => visualizza il numero: 0,75
=ORARIO.VALORE("06:00") => visualizza il numero:  0,25
=ORARIO.VALORE("27/12/2023 06:00") => visualizza il numero:  0,25 (il giorno dell'anno viene ignorato!)

GENERAZIONE DI DATI TEMPORALI CASUALI

>> Osservazione - Esempio di quesiti nei test d'esame

  • Fornire la formula che genera una data casuale nel periodo che va dal 1/1/1900 ad oggi.

    =TESTO(CASUALE.TRA(1;OGGI());"gg/mm/aaaa") => basta generare un numero intero compreso tra 1 (codifica interna di 1/1/1900) e la codifica interna associata alla data di oggi.

  • Fornire la formula che genera una data casuale nel periodo che va dal 1/1/2020 ad oggi.

    =TESTO(CASUALE.TRA(DATA.VALORE("1/1/2020");OGGI());"gg/mm/aaaa") => basta generare un numero intero compreso tra la codifica interna di 1/1/2020 e la codifica interna associata alla data di oggi.

  • Fornire la formula che genera una data casuale nell'anno 2023

    =TESTO(DATA.VALORE("1/1/2023")+CASUALE.TRA(0;364);"gg/mm/aaaa")

oppure

=TESTO(CASUALE.TRA(DATA.VALORE("1/1/2023");DATA.VALORE("31/12/2023"));"gg/mm/aaaa")

  • Fornire la formula che genera una data casuale nell'anno corrente

    =TESTO(DATA.VALORE("1/1/" & ANNO(OGGI()))+CASUALE.TRA(0;364);"gg/mm/aaaa")

oppure

=TESTO(DATA.VALORE("1/1/" & TESTO(OGGI();"aaaa"))+CASUALE.TRA(0;364);"gg/mm/aaaa")

  • Fornire la formula che genera un orario casuale nell'arco dell'intera giornata

    =TESTO(CASUALE();"hh:mm:ss")

  • Fornire la formula che genera un orario casuale nel periodo che va dal 1/1/1900 00.00.00 all'istante attuale (ADESSO())

    =TESTO(CASUALE()*ADESSO();"gg/mm/aaaa hh:mm:ss") => basta generare un reale tra [0,1) dove 0 è la codifica delle ore 00.00.00 mentre 1 di un orario prossimo alle 24.00.00.

  • Fornire una formula che genera un orario casuale dalle 06:25 alle 14:12

    =TESTO((ORARIO.VALORE("14:12")-ORARIO.VALORE("06:25"))*CASUALE()+ORARIO.VALORE("06:25");"hh:mm") => basta generare un reale tra [A,B) dove A è ORARIO.VALORE("06:25") mentre B è ORARIO.VALORE("14:12")    .

  • Fornire la formula che genera un orario casuale dall'inizio della giornata odierna (ore 00:00:00) all'istante attuale (ADESSO())

    =TESTO(CASUALE()*(ADESSO()-OGGI());"hh:mm:ss") => basta generare un reale tra [0,A) dove A è l'istante attuale (quindi un valore inferiore a 1!) ovvero ADESSO()-OGGI().

  • Fornire la formula che genera un orario casuale dall'istante attuale fino alle ore 24:00:00 => devo generare un reale tra [A,1) dove A è l'istante attuale ovvero ADESSO()-OGGI() mentre 1 è un orario prossimo alle 24.00.00. Sapendo che per generare un reale tra [A,B) si usa la formula CASUALE()*(B-A)+A segue la formula sottostante

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

Precisazioni -  Slide 26 - Esercitazione XLS1 - E1.07

Vediamo 2 esempi analoghi ma più articolati.

>> Osservazione - 1° Esempio di quesito nei test d'esame

Supponiamo di avere una condizione F(x) che restituisce, lungo l'asse X (rappresentato dalla cella A1), i seguenti valori booleani.

Si chiede di fornire la formula in Excel che implementa la funzione F(x).

Soluzione:

Scriviamo dapprima le singole condizioni associate ad ogni zona che restituisce VERO.

Successivamente, sapendo che la funzione E() implementa l'intersezione mentre la O() l'unione dei singoli insiemi di verità, possiamo fornire come soluzione la seguente formula:

=O(A1<X1;A1=X2;E(A1<X4;A1>X3);E(A1<X6;A1>X5);A1>X7)

>> Osservazione - 2° Esempio di quesito nei test d'esame

Supponiamo di avere la seguente formula (dove A1 corrisponde ad un valore sull'asse X):

=E(O(A1<=X3;A1>=X5);A1<>X2; E(A1<=X6;A1>X1) )

Si chiede di evidenziare sull'asse reale i segmenti dove la funzione proposta restituisce VERO (insieme di verita!) .

Soluzione:

Iniziamo a considerare le singole formule booleane e i corrispondenti segmenti dove tali condizioni restituiscono VERO .

Essendo le singole  formule booleane legate dalla funzione E() segue che l'insieme di verità sia l'intersezione dei singoli segmenti. La soluzione richiesta pertanto diviene:

Lo schema ottenuto suggerisce che la formula possa essere riscritta nella seguente forma equivalente:

=O( E( A1<=X3;A1>X1;A1<>X2 ); E(A1<=X6;A1>=X5) )

Precisazioni -  Slide 27 - Esercitazione XLS1 - E1.08  || Slide 81-91 XL3 Macro

>> Osservazione 1:

Vediamo di comporre una condizione alternativa che restituisca VERO quando il punto (X,Y) è nel 1° oppure nel 3° quadrante del piano cartesiano.

La funzione f(x,y) deve restituire VERO quando il punto (X,Y) è nel 1° oppure nel 3° quadrante del piano cartesiano, FALSO altrimenti.

Sapendo che:

  • La condizione E(X>0;Y>0) restituisce VERO quando il punto (X,Y) è nel 1° quadrante.

  • La condizione E(X<0;Y<0) restituisce VERO quando il punto (X,Y) è nel 3° quadrante.

  • La regione colorata è l'unione dei 2 quadranti appena citati

segue che la condizione che restituisce VERO quando il punto (X,Y) è nel 1° oppure nel 3° quadrante è quella ottenuta legando le 2 condizioni di base con la funzione O().

=O(E(X>0;Y>0);E(X<0;X<0))

La condizione può essere semplificata ricorrendo alla seguente relazione algebrica:

=(X*Y>0)

Precisazioni -  Slide 28 - Esercitazione XLS1 - E1.09  || Slide 81-91 XL3 Macro

L'esercizio richiesto nel E1.09 consiste nel costruire una condizione che restituisce VERO quando il punto (x,y) appartiene alle zone colorate di azzurro della figura sottostante.

Soluzione:

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

corrisponde alle le zone colorate del quesito e pertanto la formula:

restituisce VERO quando il punto (X,Y) appartiene ad una delle 2 porzioni di corona circolare colorate di blu. Chiaramente scritta secondo la sintassi delle formule di Excel diventa:

=E( (X*X+Y*Y)<=R2*R2; (X*X+Y*Y)>=R1*R1; X*Y>=0 )

Esempi analoghi presi dai test d'esame

Esempio 1 - uso funzioni E(), O() ed NON():

Osservazioni:

Sono dentro il rettangolo quando:

semplificando ottengo:

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

Esempio 2 - uso funzioni E(), O() ed NON():

Osservazioni:

Sono fuori dal rettangolo quando:

pertanto quando la seguente funzione restituisce VERO.

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

Esempio 3 - uso funzioni E(), O() ed NON():

Fornire la formula che restituisce VERO se il punto (X,Y) risulta posto sui bordi del rettangolo il cui vertice in alto a sinistra è (a,d) mentre quello in basso a destra è (b,c) , FALSO altrimenti.

Soluzione:

Un punto (X,Y) è sul bordo se non è ne dentro il rettangolo e neppure fuori ( =NON(O(Dentro ; Fuori ) ) ). Sfruttando le formule dei 2 precedenti quesiti la condizione associata diventa:

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

Esempio 4 - uso funzioni E(), O() ed NON():

Osservazioni:

Infatti il triangolo del quesito corrisponde all'intersezione dei 3 semipiani

Esempio 5- uso funzioni E(), O() ed NON():

Si consideri il seguente quesito preso dai test d'esame

Soluzione:

L'area colorata di verde può essere intesa come l'unione delle seguenti regioni (Z1, Z2 e Z3) ...

 ... e pertanto potrà essere gestita con una formula del tipo O( Z1(x,y) , Z2(x,y) , Z3(x,y) ).

La prima regione Z1 può essere ottenuta come intersezione tra la regione a forma di cuore C(x,y) e il negato della regione rettangolare NON( R(x,y)) ...

... pertanto la seguente funzione E( C(x,y); NON(R(x,y) ) restituisce VERO se il punto (x,y) è all'interno di Z1.

La seconda regione Z2 corrisponde all'intersezione tra la regione a forma di cuore C(x,y) e la regione a forma di pentagono  P(x,y) ...

... e quindi la funzione booleana E( C(x,y); P(x,y) ) restituisce VERO se il punto (x,y) è all'interno della seconda regione.

L'ultima regione invece è ottenuta come intersezione tra la regione a forma di pentagono P(x,y) e il negato della regione rettangolare NON(R(x,y)) ...

... pertanto la seguente funzione E( P(x,y); NON(R(x,y) ) restituisce VERO se il punto (x,y) è all'interno di Z3.

Quindi la soluzione richiesta è: O ( E( C(x,y); NON(R(x,y) ) ,  E( C(x,y); P(x,y) ) , E( P(x,y); NON(R(x,y) ) ).

Esempio 6 - uso funzioni E(), O() ed NON():

Siano P una generica persona, U un corso universitario e R il nome di una regione italiana. Consideriamo le seguenti funzioni booleane che restituiscono VERO se:

d(P) => VERO se P è un docente
m(p) => VERO se P è un maschio
c(P) => VERO se P è un appassionato di ciclismo
i(P) => VERO se P è un cittadino italiano
x(P) => VERO se P è un cittadino extra comunitario
l(P,U) => VERO se P è un laureando iscritto al corso universitario U
r(P,R) => VERO se P è residente nella regione R
f(P) => VERO se P ha figli

Sfruttando queste funzioni fornire:

1) La condizione che restituisce VERO se P è un cittadino/a comunitario non italiano

Soluzione

Il problema può essere rappresentato graficamente in questo modo

pertanto se considero l'unione delle 2 condizioni x(P) e i(P):

e la nego ottengo la condizione richiesta

Potevo seguire un altro approccio (quello illustrato a lezione).

Considero la negazione sia della condizione associata al cittadino italiano che quella del cittadino extracomunitario. L'intersezione delle 2 condizioni negate è un'ulteriore soluzione

2) La condizione che restituisce VERO se P è una docente appassionata di ciclismo

E( d(P) ; c(P) ; NON( m(P) ) )

3) La condizione che restituisce VERO se P è un laureando/a in ingegneria sardo o siciliano senza figli

E( l(P,"Ingegneria") ; O ( r(P,"Sicilia");r(P,"Sardegna") ) ; NON( f(P) ) )

Esempio 7 - uso funzioni E(), O() ed NON():

Si consideri il seguente quesito preso dai test d'esame

Soluzione:

La prima condizione O(A1<3;A1>9;A2>4;A2<2) all'interno della funzione E() restituisce VERO nella zona evidenziata in ciano

mentre la seconda O( E(A1<9;A1>3) ; E(A2>2;A2<4) ) restituisce VERO nelle zone non bianche

La E() più esterna corrisponde all'intersezione tra le 2 zone associate alle condizioni precedenti

pertanto  le celle con il valore V saranno quelle della figura sottostante dove la colorazione risulta diversa da ciano e magenta

Esempio 8 - uso funzioni E(), O() ed NON():

Si consideri il seguente quesito preso dai test d'esame

Soluzione:

Basta trovare le condizioni associate alle zone contenenti la V e successivamente unirle con la funzione O()

oppure ricercare quelle abbinate alle aree contenenti la F, successivamente unirle con a funzione O() ed infine negare il tutto con la funzione NON().

>> Osservazione

Scrivere =SE(X=Y;1;0) è equivalente a =(X=Y)*1.

Non usare mai i doppi apici " se i valori vengono poi utilizzati nei calcoli. Ad esempio scrivere

 =A1*SE(condizione;"1";"0")

è errato poichè talvolta EXCEL sbaglia nell'applicare le conversioni automatiche fornendo risultati inaspettati!

E' invece corretto scrivere:

 =A1*SE(condizione;1;0).

Esempi presi dai test d'esame relativi alle "FUNZIONI E(), O() e NON()":

 

 

ESERCITAZIONE XLS2

Precisazioni -  Slide 5 - Esercitazione XLS2 - Propedeutico E2.03ProA

La funzione INDICE(Vettore;Posizione) o INDICE(Area;Riga;Colonna) restituisce #RIF! quando i parametri successivi ad Area portano fuori dalle dimensioni reali dell'Area/Vettore. Ad esempio:

=INDICE(A1:A10;11)

=INDICE(A1:C10;1;4)

Precisazioni -  Slide 5 - Esercitazione XLS2 - Propedeutico E2.03ProB

La funzione CONFRONTA(ValoreCercato;VettoreDeiValori;0) restituisce la posizione di ValoreCercato in VettoreDeiValori. Se non trova il valore restituisce #N/D!. Il significato del 3° argomento non è mai richiesto nei test d'esame e viene sempre considerato pari a  0 (il valore predefinito in realtà è 1 che corrisponde a: "minore di"). Con 0 (il confronto è esatto!) non è necessario che l'elenco sia ordinato.

=INDICE(Area;Riga;Colonna)
=INDICE(Vettore;Posizione)
=CONFRONTA(Valore;Area;0)
=ANNO(Data)
=ORARIO.VALORE(StringaOraria)
 =RIGHE(Area)
=COLONNE(Area)

Nota per il docente: completati propedeutici 2.03ProB

LEZIONE 13/11/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

Precisazioni -  Slide 6 - Esercitazione XLS2 - Propedeutico E2.03ProC

La funzione INDIRETTO("IndirizzoCella") restituisce il contenuto della cella il cui indirizzo è indicato come stringa nell'argomento. Vediamo degli esempi:

=INDIRETTO("A1") => è equivalente alla formula =A1.

=INDIRETTO("A0") => se il riferimento, passato come argomento, è inesistente la formula restituisce l'errore #RIF!.

=INDIRETTO(A0) => restituisce l'errore #NOME? poichè non esiste un riferimento che si chiama A0

=INDIRETTO(A1) => usa il contenuto della cella A1 come se fosse un riferimento ad una cella. Se questo valore è effettivamente un indirizzo la formula restituisce il valore presente nella cella identificata da quel riferimento altrimenti ritorna l'errore #RIF!.

>> Esempi:

=INDIRETTO("A" & CASUALE.TRA(1;10)) => estrae il contenuto di una cella a caso appartenente all'area A1:A10.

=SOMMA(INDIRETTO("A1:B10")) è equivalente a: =SOMMA(A1:B10).

=SOMMA(INDIRETTO(SCEGLI(K;"Area1";"Area2";...;"AreaN"))) permette di selezionare l'area dove applicare la somma sulla base di un numero K  probabilmente contenuto in una cella o generato da una formula. Gli argomenti "Area1", "Area2", ... , "AreaN" sono stringhe che identificano un'area del foglio come ad esempio "A1:B10"

Nella figura un esempio di utilizzo della funzione INDIRETTO: in base all'anno selezionato in C2 viene visualizzato il totale delle entrate di quell'anno.

Precisazioni -  Esercitazione XLS2 - Slide 8:  Propedeutico E2.03B - Slide 24:  Propedeutico E2.10B - Funzione CERCA()

La formula CERCA() ha la seguente sintassi:

=CERCA(Valore;VettoreDoveCerco;VettoreDiEstrazione)

Il numero di celle nei 2 vettori deve essere identico (la direzione non è importante - vedi slide 55 della sezione "XL3 - Macro")

La formula CERCA() richiede che l'elenco dove viene effettuata la ricerca (2° argomento) sia ordinato! Restituisce il valore Xk in VettoreDiEstrazione corrispondente all'ultimo valore nel vettore VettoreDoveCerco che è risultato più piccolo del Valore cercato (pertanto la ricerca  restituisce l'intervallo [Xk, Xk+1] a cui appartiene il Valore cercato)

Quando il Valore cercato (1° argomento!) è più piccolo del valore X1 presente nella 1° cella del vettore VettoreDoveCerco allora la formula CERCA()  genera l'errore #N/D!.

Precisazioni -  Slide 9 - Esercitazione XLS2 - Propedeutico E2.03C e E2.03D - Funzione CERCA.VERT

>> Osservazione 1

La formula

=CERCA.VERT(Valore;Area;NrColonna;FALSO)

restituisce l'errore #N/D! quando il  Valore indicato come 1° argomento non è presente all'interno della prima colonna di Area. Quando il 4° argomento è FALSO non serve che l'elenco nella 1° colonna di Area sia ordinato.

>> Osservazione 2

La formula

=CERCA.VERT(Valore;Area;NrColonna;VERO)

restituisce l'ultimo valore nella 1° colonna di Area che è risultato minore o uguale al Valore cercato.

CERCA.VERT() con il 4° argomento posto a VERO è utilizzato per trovare l'intervallo di appartenenza del valore posto come 1° argomento.

Quando il 4° argomento della funzione CERCA.VERT() è posto a VERO la prima colonna dell'area indicata come 2° argomento deve essere ordinata altrimenti il risultato ottenuto potrebbe essere imprevedibile.

Quando il 4° argomento è VERO la formula CERCA.VERT()  genera l'errore #N/D! se il Valore indicato come 1° argomento è più piccolo del valore X1 presente nella 1° cella della 1° colonna di Area. Vediamo un esempio:

>> Osservazione 3:

L'esempio sottostante dell'interesse a scaglioni evidenzia che se ho N aliquote differenti devo utilizzare N-1 funzioni SE(). Una soluzione basata su questa funzione diventa improponibile quando le aliquote presenti risultano numerose poichè è complesso gestire N-1 funzioni SE()  (vedi Esercitazione XLS1 - E1.05).

Vediamo come ottenere una soluzione utilizzando la funzione CERCA.VERT().

Abbiamo visto che CERCA.VERT(), con il 4° argomento a VERO, permette di effettuare ricerche per intervallo di appartenenza. Questa modalità ritorna utile per il nostro esempio, dove devo determinare il tasso di interesse a scalare in base alla fascia di appartenenza del Capitale versato. Ecco una possibile soluzione:

>> Osservazione 4 - soluzione alternativa dell'interesse a scalare usando la funzione CERCA()

La formula CERCA() ha un'ulteriore sintassi(con 2 argomenti!):

=CERCA(Valore;Area)

Sappiamo che la ricerca sulla 1° colonna di Area con CERCA()  permette di localizzare l'intervallo di appartenenza del parametro Valore. Il valore restituito dalla funzione CERCA()con questa sintassi è quello della cella posta sull'ultima colonna e sulla riga corrispondente all'intervallo selezionato.  Pertanto se Area ha n colonne la formula =CERCA(Valore;Area) è equivalente a:

=CERCA.VERT(Valore;Area;n;VERO)

Ecco l'esempio dell'interesse variabile risolto con la formula CERCA():

Precisazioni -  Slide 10 - Esercitazione XLS2 - Propedeutico E2.03F

>> Osservazione 1

Sappiamo che le seguenti relazioni sono vere nel confronto lessicografico (carattere per carattere):

"31/12/1900" > "01/01/2018" => questa codifica testuale  del tempo (GG/MM/AAAA) è incompatibile con la relazione d'ordine cronologica
"1900/12/31" < "2018/01/01"  => questa codifica testuale del tempo (AAAA/MM/GG) è compatibile con la reale relazione d'ordine cronologica

Per stabilire la stagione associata ad una data devo necessariamente escludere l'anno nel confronto cronologico! Infatti:

DATA.VALORE("01/01/2023") <= Inverno 2023 < DATA.VALORE("21/03/2023")=> questa condizione è vera solo nel 2023!
DATA.VALORE("01/01/" & TESTO(OGGI();"aaaa") <= Inverno attuale < DATA.VALORE("21/03/" & TESTO(OGGI();"aaaa")  => questa condizione è vera sempre ma gli intervalli temporali continuano a cambiare in funzione della data attuale!

>> Osservazione 2

CODIFICHE TESTUALI DEL TEMPO DOVE L'ORDINAMENTO LESSICOGRAFICO E QUELLO CRONOLOGICO SONO EQUIVALENTI

Le seguenti funzioni, applicate a dati cronologici, forniscono una codifica testuale che rende compatibile l'ordinamento lessicografico con quello cronologico:

=Testo(Data;"aaaammgg")
infatti si può dimostrare che Data1 < Data2 TESTO(Data1,"aaaammgg") < TESTO(Data2,"aaaammgg")

=Testo(Data;"mmgg")
infatti si può dimostrare che Data1 < Data2 TESTO(Data1,"mmgg") < TESTO(Data2,"mmgg") purché le 2 date Data1 e Data2 appartengano al medesimo anno solare (usata nella 1° soluzione proposta dell'esercizio propedeutico E2.03F dell'esercitazione XLS.2)

=Testo(Orario;"hhmm")
infatti si può dimostrare che Orario1 < Orario2 TESTO(Orario1,"hhmm") < TESTO(Orario2,"hhmm")

CODIFICHE NUMERICHE DEL TEMPO DOVE L'ORDINAMENTO NUMERICO E QUELLO CRONOLOGICO SONO EQUIVALENTI

Le seguenti funzioni, applicate a dati cronologici, forniscono una codifica numerica che rende compatibile l'ordinamento numerico con quello cronologico:

=ANNO(Data)*10000+MESE(Data)*100+GIORNO(Data)
infatti si può dimostrare che Data1 < Data2 Anno(Data1)*10000+Mese(Data1)*100+Giorno(Data1) < Anno(Data2)*10000+Mese(Data2)*100+Giorno(Data2)

=MESE(Data)*100+GIORNO(Data)
infatti si può dimostrare che Data1 < Data2 Mese(Data1)*100+Giorno(Data1) < Mese(Data2)*100+Giorno(Data2) purché le 2 date Data1 e Data2 appartengano al medesimo anno solare

=ORA(Orario)*60+MINUTO(Orario)
infatti si può dimostrare che Orario1 < Orario2 ORA(Orario1)*60+MINUTO(Orario1) < ORA(Orario2)*60+MINUTO(Orario2)

CODIFICHE TESTUALI DEI NUMERI DOVE L'ORDINAMENTO LESSICOGRAFICO E QUELLO NUMERICO SONO EQUIVALENTI

La seguente funzione, applicata ai numeri interi, fornisce una codifica testuale che rende compatibile l'ordinamento lessicografico con quello numerico:

=Testo(Intero;"00000")
infatti si può dimostrare che Intero1 < Intero2   TESTO(TESTO(Intero1,"00000") < TESTO(Intero2,"00000") purché il numero di cifre nei 2 valori Intero1 e Intero2 non risulti superiore al numero di zeri presenti nella maschera di formato

Esempio:

La relazione 9 < 123 è vera. Si può facilmente verificare che anche TESTO(9,"00000") < TESTO(123,"00000"). Si osservi che la formula TESTO(N,"00000") aggiunge al numero N degli zeri iniziali fino ad arrivare ad un numero complessivo di cifre pari a quelle presenti nella maschera di formato.

>> Osservazione 3 - Soluzione Esercitazione XLS2 - Propedeutico E2.03F - Funzione CERCA.VERT

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:

=TESTO(B1;"mmgg")

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

"stringa"data1 < "stringa"data2 <=> data1< data2  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 data1< data2 è 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) è:

=CERCA.VERT(TESTO(B1;"mmgg");D2:E6;2;VERO)

>> 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  numerodata tale che:

numerodata1 < numerodata2 <=> data1< data2  purché le 2 date appartengano allo stesso anno.

La relazione d'ordine utilizzata nella disuguaglianza numerodata1 < numerodata2 è quella numerica mentre quella della relazione data1< data2 è 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) è:

=CERCA.VERT(MESE(B1)*100+GIORNO(B1);D2:E6;2;VERO)

Precisazioni -  Slide 11-13 - Esercitazione XLS2 - Propedeutico E2.03Bis

>> Osservazione - Rappresentazione interna di Excel per i dati orari

L'orario in Excel è registrato come un numero reale tra 0 e 1. Ad esempio il valore 0,5 corrisponde a mezzogiorno mentre 0,75 alle 18.00. Tale rappresentazione permette agli operatori algebrici di somma e differenza di fornire risultati cronologicamente plausibili. Infatti se alla codifica delle ore 12:00 sommo la codifica associata a 6 ore ottengo come risultato algebrico 0,75 ovvero la codifica delle 18:00 che dal punto di vista cronologico corrisponde effettivamente a 6 ore dopo le 12:00! In altre parole l'operatore algebrico di somma con questa codifica si comporta alla stessa stregua di un ipotetico operatore di somma oraria.

ORARIO.VALORE("12:00")+ORARIO.VALORE("06:00")=> 0,5+0,25 =0,75 => ORARIO.VALORE("18:00")

Precisazioni -  Slide 12-13 - Esercitazione XLS2 - E2.03

Esempi  presi dall'Esercizio 2.03 - Clicca qui per visualizzare tutte soluzioni dello stesso esercizio

1) Giorni trascorsi dall'inizio dell'anno attuale ad oggi.

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

La funzione INT o TRONCA serve a trasformare il risultato in un numero in modo che Excel non lo visualizzi sotto forma di data. In alternativa posso usare la formula

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

2) Minuti trascorsi dall'inizio dell'anno 2020 fino all'istante attuale.

=TRONCA(( ADESSO()-DATA.VALORE("01/01/2020") )*24*60)

3) Minuti trascorsi dall'inizio della giornata

=ORA(ADESSO())*60+MINUTO(ADESSO())

oppure

=TRONCA((ADESSO()-OGGI())*24*60)

4) Minuti che mancano alla fine della giornata

=60*24-ORA(ADESSO())*60-MINUTO(ADESSO())

oppure

=60*24-TRONCA((ADESSO()-OGGI())*24*60)

La formula (ADESSO()-OGGI()) restituisce la codifica del tempo orario privata della codifica del giorno (ovvero elimina la parte intera!)

5) Costruire la condizione che restituisce VERO se la data odierna è l'ultimo giorno del mese.

=(MESE(OGGI())<>MESE(OGGI()+1)

Ricordarsi che le formule

=IERI()
=DOMANI()

non esistono!

6) Fornire la formula che restituisce il numero di giorni del mese precedente a quello attuale

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

7) Valutare se un certo ANNO è bisestile.

Un  modo per valutare se un anno è bisestile deriva dalla seguente considerazione:

"un anno è bisestile se è divisibile per 400 (RESTO(ANNO;400)=0) oppure (O(...;...)) se è divisibile per 4 ma non per 100 (E(RESTO(ANNO;4)=0;RESTO(ANNO;100)<>0))".

Pertanto una formula per determinare se un anno è bisestile è la seguente:

=SE(O(RESTO(ANNO;400)=0;E(RESTO(ANNO;4)=0;RESTO(ANNO;100)<>0));"";"Non ") & "è bisestile"

Un altro modo per valutare se un anno (suppongo ancora che sia stato scritto in una cella che è stata rinominata con ANNO!)  è bisestile è il seguente:

=SE(VAL.ERRORE(DATA.VALORE("29/02/" & ANNO));"non è bisestile";"è bisestile")

oppure

=SE(VAL.ERRORE(DATA.VALORE("29/02/" & ANNO));"non ";"") & "è bisestile"

Precisazioni (ulteriori) -  Slide 12-13 - Esercitazione XLS2 - Esercizio E2.03

>> Osservazione 1 - Rappresentazione interna di Excel per i dati orari

In Excel le date sono registrate come numero di giorni trascorsi dalla data di riferimento che è il 31/12/1899. Il valore 1 corrisponde alla data  1/1/1900 (in OpenCalc 1/1/1900 corrisponde al valore 2). Tale rappresentazione permette agli operatori algebrici di somma e differenza di fornire risultati cronologicamente plausibili:

OGGI()+1 => ottengo la rappresentazione associata a domani
OGGI()-1 => ottengo la rappresentazione associata a ieri
OGGI()-DATA.VALORE("01/01/" & ANNO(OGGI()))+1 => ottengo il numero di giornate trascorse fino ad oggi a partire dall'inizio dell'anno corrente.

E' quindi errato determinare il numero di giorni trascorsi tra 2 date espresse come stringhe con una semplice differenza.

="31/12/2020" - "01/01/2018" errato!

Occorre prima convertire le stringhe in un dato cronologico utilizzando la funzione DATA.VALORE(). Pertanto:

DATA.VALORE("31/12/2020") - DATA.VALORE("01/01/2018")

è la formula corretta!

>> Osservazione

Stranamente la formula appena presentata restituisce "è bisestile" quando metto come anno il 1900. Tale risultato è un bug di EXCEL noto con il nome di "Bug dell'anno bisestile". L'errore è un retaggio storico dovuto ad un antenato dei fogli elettronici: Lotus 1-2-3 che presupponeva che l'anno 1900 fosse bisestile. Quando Microsoft ha rilasciato Excel ha assunto che il 1900 fosse un anno bisestile in modo da consentire l'utilizzo dello stesso sistema di datazione utilizzato da Lotus 1-2-3 (al tempo Lotus 1-2-3 costituiva lo spreadsheet più diffuso in assoluto!). Anche se tecnicamente semplice da correggere, questo bug non viene sistemato in modo da garantire la compatibilità dell'Excel attuale con i file prodotti da versioni precedenti oppure da altri programmi.

EQUIVALENZA CERCA.VERT() ED INDICE()+CONFRONTA

=CERCA.VERT(VALORE;AREA;NRCOLONNA;FALSO)

è equivalente a:

=INDICE(AREA;CONFRONTA(VALORE;PRIMACOLONNADIAREA;0);NRCOLONNA)

dove PRIMACOLONNADIAREA è il riferimento alla prima colonna di AREA. Vediamo un esempio:

La scelta della 2° modalità risulta più adatta quando si devono estrarre diverse voci all'interno di una stessa riga contenuta in un elenco di dati, come si vede nell'esempio sottostante. Infatti per estrarre con il CERCA.VERT() il nominativo, l'anno di frequenza, il genere e il corso di studio devo effettuare per 4 volte una ricerca sulla matricola. La ricerca, nel caso di elenchi molto lunghi, diventa molto onerosa in termini di tempo e a maggior ragione se questa viene ripetuta più volte!

Inoltre è necessario predisporre i dati in modo che la zona dove effettuo la ricerca sia posizionata sulla 1° colonna dell'area indicata come 2° argomento nella funzione CERCA.VERT().

Usando invece CONFRONTA() la ricerca viene fatta una sola volta (con il CONFRONTA() ricerco la riga corrispondente alla matricola!). Stabilita la riga associata (la 233 nell'esempio!) al valore cercato (matricola!)  estraggo i dati richiesti sfruttando la funzione INDICE() (che non effettua alcuna ricerca!).

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=ORA(Orario)
=MINUTO(Orario)
=SECONDO(Istante)
=GIORNI(DataFinale;DataIniziale)
 =CERCA(Valore;VettoreRicerca;VettoreEstrazione)
=CERCA.VERT(CosaCerco;Area;NrColonna;TipoConfronto)
=INDIRETTO(StringaRiferimento)
=GIORNO.SETTIMANA(Data)
=RIGHE(Area)
=COLONNE(Area)

Note per lo studente:

  • Nell'esercitazione XLS.2, gli esercizi da E2.01 a E2.04 più tutti i propedeutici sono utilizzati come base per la costruzione dei quesiti nei test d'esame.

  • Le soluzioni degli esercizi da E2.01 a E2.04 dell'esercitazione XLS.2 sono  richiamabili utilizzando la voce soluzioni presente nel menu associato a questa esercitazione ma  possono essere visualizzate anche cliccando qui. (NB: gli esercizi E2.03 e E2.04 sono stati invertiti rispetto alle slide dell'esercitazione!)

  • Analizzare in dettaglio gli esempi contenuti nella slide 53 della sezione "X3 - Macro". Gli esempi con la ~ vanno ignorati.

Nota per il docente: riprendere dall'esempio CONFRONTA + Metacaratteri -

LEZIONE 20/11/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

Precisazioni -  Slide 53 - XL3 MACRO

Esempio - uso della funzione CONFRONTA() usando i caratteri speciali ? e *:

Questi esempi sono contenuti nella slide 53 della sezione "X3 - Macro // Formule e funzioni", relativi alla funzione CONFRONTA(), vanno analizzati poichè vengono utilizzati come riferimento per alcuni quesiti presenti nei test d'esame.

Precisazioni -  Slide 19 - Esercitazione XLS2 - E2.07 - E2.08

>> Nota relativa all'esercizio 2.06 e 2.07: ITERAZIONE

Oltre al costrutto selettivo, implementato in EXCEL con la formula nativa SE(), esiste un'ulteriore struttura di controllo detta iterazione. Il costrutto selettivo replica la capacità umana di fare delle scelte sulla base di una condizione. Il costrutto iterativo invece replica la capacità umana di ripetere un'operazione fino a quando necessario (dipende da una condizione!). Questi costrutti di controllo vengono utilizzati per aggregare istruzioni elementari dando vita a sequenze di esecuzione più complesse ed articolate. EXCEL non ha delle formule native che implementano la struttura di controllo iterativa. E' possibile comunque emularla ricorrendo alla replica delle formule su un'area di celle mediante l'uso del copia ed incolla posizionale (quindi il numero massimo di iterazioni deve essere noto a priori!).

Esempio:

Il seguente diagramma di flusso mostra la generazione dei primi 100 numeri interi. L'azione ripetuta (parte iterata) è costituita dalla visualizzazione del valore corrente i e dal suo successivo incremento. Chiaramente sarà necessaria una fase di inizializzazione, dove viene definito il valore di partenza, più una condizione di uscita che impedisca una situazione di loop infinito (ovvero un'esecuzione senza fine della parte iterata!).

Per implementare questa iterazione in Excel si impostano le seguenti formule:

Inizializzazione: metto in A1 la formula =0 (potevo scrivere semplicemente 0!)

Parte iterata: metto in A2 la formula =A1+1 e la replico con il copia ed incolla posizionale nelle celle sottostanti...

Condizione: ... fino a quando non supero la cella A101.

Gli esercizi 2.07 e 2.08 sono risolti utilizzando in modo opportuno la tecnica iterativa appena illustrata

ESEMPI PRESI DAI TEST RELATIVI ALLE FUNZIONI INDICE(), CONFRONTA() e RANGO():

>> Esempio 1: Determina il vincitore della seguente classifica a punti (la soluzione deve fornire un risultato corretto anche cambiando il punteggio!).

=INDICE(B1:G1;1;CONFRONTA(MAX(B2:G2);B2:G2;0))

>> Esempio 2: Determina la posizione in classifica del concorrente indicato nella cella I2 (la soluzione deve fornire un risultato corretto anche cambiando il punteggio!).

=RANGO.UG(INDICE(B2:G2;1;CONFRONTA(I2;B1:G1;0));B2:G2)

>> Esempio 3: Determina il nominativo della persona che occupa la posizione in classifica indicata nella cella I2 (la soluzione deve fornire il risultato corretto anche cambiando il punteggio!).

=INDICE(B1:G1;CONFRONTA(GRANDE(B2:G2;I2);B2:G2;0))

>> Osservazione 1

Ecco alcune formule che generano l'errore #RIF!

=INDICE(A1:D3;4;2)  => La riga indicata nel 2° argomento non esiste nell'area A1:D3 che ha dimensione 3x4
=CERCA.VERT("Casa";A1:B10;6;FALSO)  => La colonna 6 (3° argomento) non esiste nell'area A1:B10 di dimensione 10x2. Se "Casa" non è presente nell'area A1:A10 l'errore #N/D! ha priorità su #RIF!.
=INDIRETTO("A0") => Il riferimento A0 non esiste!

>> Osservazione 2

Ecco alcune formule che generano l'errore #N/D!

=CONFRONTA(Valore;Area)  => Quando il Valore indicato come 1° argomento non è presente all'interno di una delle celle di Area.
 =RANGO(Valore;Area)  => Quando il Valore indicato come 1° argomento non è presente all'interno di una delle celle di Area.

ESEMPI PRESI DAI TEST D'ESAME RELATIVI ALLE FUNZIONI CHE GESTISCONO LE STRINGHE:

>> Esempio 1: 

1) Fornire la formula che separa nel nominativo (contenuto nella cella A1)  il cognome dal nome. Per semplicità escludiamo i casi caratterizzati da cognomi o nomi composti da più parole. Supponiamo che il contenuto di A1 sia: "Rossi Mario"

=SINISTRA(A1;TROVA(" ";A1)-1) => Estrazione del cognome (Rossi)
=STRINGA.ESTRAI(A1;TROVA(" ";A1)+1;1000000) => Estrazione del nome (Mario)

>> Esempio 2: 

2) Estrarre il nome del foglio corrente utilizzando la funzione CELLA con il 1° argomento "NomeFile" e il 2° un qualsiasi indirizzo di cella.

La formula 

=CELLA("nomefile";A1)

restituisce il nome del foglio preceduto dal percorso su disco della cartella di lavoro. Ad esempio:

C:\Users\marco\Desktop\UNIBS\Esercitazioni\[E4.soluzioni.xlsx]E4.07

Per ricavare il nome del foglio sfrutto il carattere ] che delimita a destra il nome del file. Pertanto la formula sottostante estrae il testo che appare sull'etichetta del foglio corrente (quello contenente la formula!):

=STRINGA.ESTRAI(CELLA("nomefile";A1);TROVA("]";CELLA("nomefile";A1))+1;1000)

Attenzione! Se la cartella di lavoro non è stata ancora salvata su disco la funzione CELLA("nomefile";A1)restituisce la stringa nulla "".

>> Esempio 3: 

Fornire formule equivalenti tra SINISTRA(Stringa;p), DESTRA(Stringa;p) e STRINGA.ESTRAI(Stringa;q;p):

SINISTRA(Stringa;p)equivale a STRINGA.ESTRAI(Stringa;1;p)

DESTRA(Stringa;p)equivale a STRINGA.ESTRAI(Stringa;LUNGHEZZA(Stringa)-p+1;p)

STRINGA.ESTRAI(Stringa;q;p)equivale a DESTRA(SINISTRA(Stringa;q+p-1;p))

>> Osservazione 2: - Esempi con VAL.ERRORE+SE e SE.ERRORE

La seguente formula:

=SE(VAL.ERRORE(C1/C2);"Attenzione divisione per ZERO!";C1/C2)

equivale a:

=SE.ERRORE(C1/C2;"Attenzione divisione per ZERO!")

In generale la formula SE.ERRORE(...) può essere usata in alternativa a SE(VAL.ERRORE();...) quando la formula sottoposta a test con VAL.ERRORE() è la stessa presente nel 3° argomento della funzione SE(). Generalizzando:

=SE(VAL.ERRORE(Formula);RispostaInCasoDiErrore;Formula)

può essere sostituito con:

=SE.ERRORE(Formula;RispostaInCasoDiErrore)

Contrariamente la seguente formula

=SE(VAL.ERRORE(Formula);RispostaInCasoDiErrore;RispostaDiversaDalRisultatoDiFormula)

invece non potrà essere semplificata utilizzando la formula SE.ERRORE.

Note per lo studente:

  • Analizzare in dettaglio gli esempi contenuti nella slide 53 della sezione "X3 - Macro". Gli esempi con la ~ vanno ignorati.

  • La sezione "X4 - Controlli" e l'esercitazione XLS.3  associata non saranno utilizzati come base per la costruzione dei quesiti nei test d'esame.

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

=LUNGHEZZA(Stringa)
=SINISTRA(Stringa;n)
=DESTRA(Stringa;N)
=STRINGA.ESTRAI(Stringa;Posizione;N)
=MAIUSC(Stringa)
=MINUSC(Stringa)
=TROVA(CosaCerco;DoveCerco)
=RICERCA(CosaCerco;DoveCerco)
=SOSTITUISCI(StringaOriginale;CosaCerco;SostituiscoCon)
=CODICE(Stringa)
=CODICE.CARATT(Intero)
=CELLA("Nomefile";Indirizzo)
=RANGO(Valore;Area;TipoOrdinamento) o RANGO.UG(Valore;Area;TipoOrdinamento)
=CERCA.ORIZZ(CosaCerco;Area;NrRiga;TipoConfronto)
=SE.ERRORE(Formula;RispostaSeHoInFormulaUnErrore)

Esempi presi dai test d'esame relativi alla sezione "EXCEL- esercitazione XLS.1, XLS.2:



X5 - BASE DATI // ARCHIVI E SPREADSHEET

Precisazioni - Slide 3 - "X5- Base dati // Archivi e spreadsheet"

Excel è nato come programma per automatizzare le operazioni di calcolo. Comunque la sua struttura tabellare è adatta anche alla gestione di semplici archivi (flat data). Nel ribbon Dati troviamo 2 funzionalità tipiche dei programmi database: la ricerca (Filtro) e l'ordinamento (Ordina).

Precisazioni -  Slide 4 - Sezione "X5 - Base dati // Archivi e Spreadsheet"

>> Il termine database è sinonimo di archivio. Nei fogli elettronici l'archivio è costituito dalla stessa "cartella di lavoro".:

- Tabelle => contenitore di elementi omogenei (ovvero dotati delle medesime caratteristiche - esempio: tabella studenti) - Negli spreadsheet coincide con il "Foglio di lavoro";
- Campi => proprietà (caratteristiche!) che descrivono gli elementi contenuti in una tabella (esempio: matricola, cognome, nome, ....) - Negli spreadsheet coincide con la "singola colonna" del foglio di lavoro usato per contenere un elenco di dati;
- Record => singola istanza (elemento!) contenuta in una tabella (esempio: uno specifico studente rappresenta un record della tabella studenti) - Negli spreadsheet coincide con la "singola riga" del foglio di lavoro usato per contenere un elenco di dati;
- Relazioni => collegamenti logici tra tabelle (Esempio Studenti  Esiti  Appelli). Negli spreadsheet le relazioni tra i fogli (tabelle!) devono essere gestite in modo manuale mediante funzioni come CERCA.VERT, CONFRONTA, CERCA, etc.
- Indici => struttura dati  il cui scopo è quello di velocizzare le ricerche e gli ordinamenti. Negli spreadsheet questa funzionalità non è presente e pertanto le ricerche su archivi lunghi saranno lente.

L'insieme dei campi con le relative descrizioni si dice Struttura della Tabella. Nei fogli elettronici coincide con l'insieme delle intestazioni di tutte le colonne che compongono un elenco dati all'interno di un foglio di lavoro.

Precisazioni -  Slide 5 - Sezione "X5 - Base dati // Archivi e Spreadsheet"

Gli spreadsheet possono essere utilizzati in modo efficace solo nella gestione degli archivi monotabellari (quelli composti da una sola tabella oppure privi di relazioni!). Con questa tipologia di archivi la gestione dei dati con un foglio elettronico risulta semplice ed immediata.

Excel non è un programma adatto alla gestione degli archivi relazionali  (quelli composti da più tabelle collegate tra loro mediante delle relazioni! ) per i 3 seguenti limiti

1) non può definire un tipo di dato sui campi (colonne)
2) non gestisce in automatico le relazioni tra più tabelle
3) non utilizza gli indici. Pertanto le ricerche sui dati in Excel sono sempre molto lente poichè sono sequenziali (ricerca confrontando inizialmente il 1° record, poi il 2°  e così via, uno dopo l'altro, fino a quando trova il dato desiderato oppure arriva all'ultimo record della sequenza).

Nota  per il docente: Riprendere dalla slide 8 del set "X5 - Base dati"

LEZIONE 27/11/2023 15.00-18.00 - 3 ore - MLAB1-MLAB2) - Corso A-L

FUNZIONI ILLUSTRATE DURANTE LA LEZIONE:

Precisazioni -  Slide 18-25 - Sezione "X5 - Base dati // Archivi e Spreadsheet" - Algoritmi di ricerca

L'algoritmo di ricerca dicotomico o binario non è applicabile se l'elenco non è ordinato. Il numero di confronti massimo per localizzare un record all'interno di un elenco di N elementi è pari a log2(N).

L'algoritmo di ricerca sequenziale è sempre applicabile ma è molto lento. Il numero medio di confronti necessari a stabilire la presenza o meno di un record in un archivio di N elementi è pari a (N+1)/2.

Gli algoritmi di ricerca che utilizzano il B-Tree abbattono il numero di confronti massimo per localizzare un record all'interno di un elenco di N elementi a logm(N) dove m dipende dalla metodologia utilizzata.

QUESITI SUGLI INDICI 

1° Quesito relativo agli indici

Supponiamo di avere il seguente file dati (archivio!):

Costruire il file indice prima e dopo l'inserimento della lettera C

Soluzione

Il file indice contiene le posizioni delle lettere (evidenziate sopra ogni singolo carattere!) che devo seguire nella lettura al fine di ottenere un elenco ordinato.

Leggendo i dati secondo le posizioni indicate dall'indice otteniamo una sequenza ordinata.

3 => in posizione 3 ho la A
4 => in posizione 4 ho la B
6 => in posizione 6 ho la D
7 => in posizione 7 ho la E
1 => in posizione 1 ho la M
5 => in posizione 5 ho la T
2 => in posizione 2 ho la Z

Attenzione: la lettura in ordine inverso dell'indice produce una sequenza ordinata in modo decrescente per cui il verso dell'ordinamento non modifica il contenuto dell'indice!

Il record C viene aggiunto in fondo al file dati (APPEND MODE)

L'indice associato a questo file dati può essere ricostruito seguendo lo stesso procedimento visto precedentemente. In alternativa, sapendo che la C occupa la terza posizione nella sequenza ordinata, basta spostare il contenuto dell'indice originale di una posizione verso destra a partire dal terzo elemento. La terza posizione, che ora risulta liberata, dovrà essere riempita inserendo la posizione che occupa la lettera C ovvero 8.

2° Quesito relativo agli indici

Supponiamo di avere il seguente file indice:

costruire un qualsiasi file dati compatibile con tale indice.

Soluzione

Iniziamo con il numerare progressivamente 7 caselle vuote. Un file dati compatibile può essere ottenuto inserendo in sequenza le prime 7 lettere dell'alfabeto A, ... , G nelle posizioni indicate dall'indice

 

CODIFICA DEI DATI NON NATIVI

>> Osservazione 1

l calcolatore supporta come tipologie native (ovvero definite internamente nell'hardware stesso!) solo valori numerici (codificati in notazione posizionale o in virgola mobile!) e dati testuali (detti anche stringhe, basati sulla tabella Ascii!). Evidentemente il computer gestisce correttamente le relazioni d'ordine associate alle tipologie native (numerica: ad esempio 20>3 - lessicografica: ad esempio "20"<"3".) e i relativi operatori (somma, differenza, concatenazione, etc.).

Qualsiasi altra tipologia di informazione non nativa (dati cronologici, colori, costanti booleane, etc.), i relativi operatori e l'eventuale relazione d'ordine deve essere implementata codificando opportunamente i tipi nativi precedentemente citati.

La codifica di un tipo di dato non nativo (usando uno dei tipi nativi!) deve tenere presente 3 obiettivi.

1) leggibilità "umana"
2) compatibilità della relazione d'ordine nativa rispetto al tipo codificato
3) semantica degli operatori nativi compatibile con il tipo codificato

Vediamo ora degli esempi di codifica per dati cronologici

Esempio 1 - Codifica valori cronologici mediante stringhe - Metodo A

Possiamo rappresentare una data scrivendo la sequenza dei caratteri ASCII associati a: "giorno/mese/anno". Per le date che hanno il giorno del mese oppure il numero del mese inferiore a 10 aggiungiamo uno zero iniziale in modo da ottenere sempre una codifica composta da 10 caratteri. Pertanto la data di oggi diventa "19/11/2018" mentre il 9 luglio di quest'anno diventa "09/07/2018".  I doppi apici indicano che i simboli sono codificati in ASCII.

Vediamo se i 3 obiettivi da considerare in una codifica sono soddisfatti:

1) leggibilità: SI - per tutti è chiaro quale sia la data associata alla codifica "19/11/2018"!

2) compatibilità della relazione d'ordine nativa: NO infatti il confronto lessicografico (carattere per carattere!) tra le 2 codifiche "19/11/2018" e "30/12/1900" genera la seguente relazione d'ordine "19/11/2018" < "30/12/1900" che dal punto di vista cronologico è errata.

3) semantica dell'operatore nativo compatibile: NO infatti la concatenazione (operatore nativo!) "19/11/2018" & "1" = "19/11/20181" non produce la codifica del giorno successivo.

Solo un obiettivo è stato raggiunto pertanto la codifica presentata non può essere presa in considerazione.

Esempio 2 - Codifica valori cronologici mediante stringhe - Metodo B

Possiamo rappresentare una data scrivendo la sequenza dei caratteri ASCII associati a: "anno/mese/giorno". Per le date che hanno il giorno del mese oppure il numero del mese inferiore a 10 aggiungiamo uno zero iniziale in modo da ottenere sempre una codifica composta da 10 caratteri. Pertanto la data di oggi diventa "2018/11/19" mentre il 9 luglio di quest'anno diventa "2018/07/09". Vediamo se la codifica soddisfa i 3 obiettivi citati precedentemente:

1) leggibilità: SI - una volta nota la regola per tutti è chiaro quale sia la data associata alla codifica "2018/11/19"

2) compatibilità della relazione d'ordine nativa: SI infatti il confronto lessicografico tra le 2 codifiche "2019/11/19" e "1900/12/30" produce la seguente relazione "2018/11/19" > "1900/12/30" che è valida anche in senso cronologico. In realtà la presenza dell'anno e del mese all'inizio garantisce la compatibilità della relazione d'ordine nativa per tutte le codifiche di una data a partire dal primo giorno dell'anno 1000.

3) semantica dell'operatore nativo compatibile: NO infatti la concatenazione "2018/11/19" & "1" = "2018/11/191" non produce la codifica del giorno successivo.

Due obiettivi sono raggiunti. Alcuni programmi utilizzano questa codifica per rappresentare i dati cronologici.

Esempio 3 - Codifica valori cronologici mediante numeri - Metodo A

Possiamo rappresentare una data scrivendo  in sequenza i numeri associati al: giornomeseanno. Per evitare ambiguità (la codifica 1112018 è associata al 1 novembre o all'11 gennaio del 2018?) useremo la formula GG*1.000.000+MM*10.000+AAAA per ottenere la codifica della data GG/MM/AAAA. Pertanto la data di oggi diventa 19112018 (l'assenza di apici indica che la codifica interna è quella numerica ed esattamente in complemento a 2) mentre il 9 luglio di quest'anno diventa 9072018.  Vediamo se i 3 obiettivi richiesti per una buona codifica sono soddisfatti:

1) leggibilità: SI - Per tutti è chiaro quale sia la data associata a 19112018!

2) compatibilità della relazione d'ordine nativa: NO infatti il confronto numerico (relazione d'ordine nativa!) tra le 2 codifiche 19112018 e 30121900 produce questa disuguaglianza 19112018 < 30121900 che è incompatibile con la reale relazione cronologica.

3) semantica dell'operatore nativo compatibile: NO infatti l'addizione (operatore nativo!) 19112018+1 = 19112019 non produce la codifica del giorno successivo.

Solo un obiettivo è raggiunto pertanto questa codifica deve essere scartata

Esempio 4 - Codifica valori cronologici mediante numeri - Metodo B

Utilizziamo per codificare la data GG/MM/AAAA la formula AAAA*10.000+MM*100+GG. Pertanto la data di oggi verrà codificata con 20181119 mentre il 9 luglio di quest'anno con 20180709.  Vediamo se i 3 obiettivi per una buona codifica sono soddisfatti:

1) leggibilità: SI - una volta nota la regola per tutti è chiaro quale sia la data associata alla codifica 20181119!

2) compatibilità della relazione d'ordine nativa: SI infatti il confronto numerico tra le 2 codifiche 20181119 e 19001230 produce una relazione d'ordine 20181119 > 19001230 cronologicamente corretta. In realtà la presenza dell'anno e del mese all'inizio garantisce la compatibilità della relazione d'ordine nativa su tutte le codifiche a partire dal primo giorno dell'anno 0 in poi.

3) semantica dell'operatore nativo compatibile: NO - Il fatto che la somma 20181119+1 = 20181120 produca la codifica del giorno successivo è solo un caso, infatti l'operazione 20181130+1=20181131 restituisce una codifica che non può essere abbinata ad una data realmente esistente.

La codifica ha quindi raggiunto due dei tre obiettivi richiesti.

Esempio 5 - Codifica valori cronologici mediante numeri - Metodo utilizzato da EXCEL

Excel utilizza come codifica di una data il numero di giorni trascorsi rispetto ad una data di riferimento che è il 31/12/1899. Pertanto il 1/1/1900 è codificato con 1, il 2/1/1900 con 2 e così via . Vediamo se i 3 obiettivi che una codifica deve soddisfare sono stati raggiunti:

1) leggibilità: SI - Interpretare la codifica 43.788 come il 19/11/2018 non è certo una cosa semplice! Excel aggira questa scarsa leggibilità della codifica sfruttando le maschere di formato (ad esempio "gg/mm/aaaa") che trasformano quel numero in qualcosa di umanamente comprensibile. Pertanto possiamo considerare il primo obiettivo raggiunto.

2) compatibilità della relazione d'ordine nativa: SI - Supponiamo che il confronto numerico tra le codifiche di 2 date D1 e D2 soddisfi la seguente relazione d'ordine Codifica(D1) < Codifica(D2). Questo implica che la data D1 sia più vicina al 31/12/1899  rispetto a D2 ovvero D1 precede D2 e pertanto D1 < D2. Analogamente posso dimostrare che se D1 < D2 allora Codifica(D1) < Codifica(D2). Quindi la relazione d'ordine nativa (numerica!) emula correttamente quella cronologica.

3) semantica dell'operatore nativo compatibile: SI - la somma 43788 +1 = 43789  produce effettivamente la codifica del giorno successivo (domani "dista" effettivamente dalla data di riferimento un giorno in più rispetto ad oggi!).

I tre obiettivi sono stati raggiunti. Questa codifica rappresenta quindi un'ottima soluzione per implementare dati cronologici partendo dal tipo nativo numerico.

Si riveda a tale proposito anche la codifica RGB dei colori illustrata in una precedente lezione.

>> Osservazione 2

Quando la relazione d'ordine (numerica o lessicografica) associata al tipo nativo (numeri o stringhe) utilizzato per codificare un tipo non nativo (esempio il tipo cronologico) non risulta compatibile con l'ordinamento atteso sul tipo codificato otteniamo elenchi non correttamente ordinati. La mancanza di un ordinamento valido non permette l'utilizzo di algoritmi di ricerca indicizzata (esempio quello dicotomico). Pertanto sul tipo codificato l'unico metodo di ricerca possibile sarà quello di tipo sequenziale (quindi molto lento!).

Esempi presi dai test d'esame relativi alla sezione "X5 - BASE DATI - Archivi e Spreadsheet":

Note per lo studente:

  • Le slide 12-17 della sezione ""X5 - Base dati // Archivi e Spreadsheet"" sono da ignorare

  • Le slide 26-29 della sezione "X5 - Base dati // Archivi e Spreadsheet" sono da ignorare

X5 - BASE DATI // IMPORTAZIONI

>> Osservazione

Una grammatica per un linguaggio di markup che abbiamo inventato è un insieme di regole che indica quali vocaboli (elementi) sono ammessi e come (struttura) questi possono essere utilizzati all'interno del documento XML.

Attualmente esistono due approcci per definire la grammatica associata al linguaggio di markup utilizzato all'interno di un documento XML:

  • Dtd - Document Type Definition

  • XML Schema.

>> Osservazione

In XML il commento all'interno del file va racchiuso tra <!-- e -->. Ad esempio:

<!-- Ecco un commento in XML -->

>> Definizioni:

Add-In => modulo aggiuntivo che estende le funzioni disponibili all'interno di un'applicazione. Ad esempio Microsoft Excel dispone di una componente aggiuntiva chiamata "Risolutore" (Solver) che consente di risolvere problemi di ottimizzazione lineare (vedi slide relative cliccando qui - facoltativo).

ODBC => E' uno strumento che consente un accesso diretto (quindi senza che sia necessaria la creazione di un file di interscambio!) ai dati contenuti in un DB Server centrale.

Un esempio di utilizzo del driver ODBC con Excel è riportato nelle slide 6 e 7 della sezione X6 - Tabelle pivot (queste slide non sono richieste per il test d'esame)

Esempi presi dai test d'esame relativi alla sezione "X5 - BASE DATI - Importazioni":

Note per lo studente:

  • le slide dalla 3 alla 9 della sezione "X5 - Base dati // Importazioni" vanno studiate poichè presenti nei test d'esame.

  • le slide dalla 10 alla 27 della sezione  "X5 - Base dati // Importazioni", contenenti approfondimenti sul formato XML, non verranno utilizzate come riferimento per i quesiti presenti nei test d'esame.

  • le slide dalla 27 alla 55 della sezione  "X5 - Base dati // Importazioni" sono di natura operativa e non verranno utilizzate come riferimento per i quesiti presenti nei test d'esame.

X6 - TABELLE PIVOT

>> Osservazione 1

Le tabelle pivot risultano utili quando devo esprimere una statistica rispetto a due grandezze. Ad esempio il fatturato distinto per prodotto e per  zona di vendita.

>> Osservazione 2

PowerPivot => add-in che permette di unire dati provenienti da più tabelle. Tramite la definizione delle relazioni tra le tabelle è possibile aggregare in un'unica tabella pivot informazioni che risultano distribuite su più tabelle.

Note per lo studente:

  • le slide della sezione  "X6 Tabelle Pivot" sono di natura operativa e non verranno utilizzate come riferimento per i quesiti presenti nei test d'esame.

NOTE NON ANCORA ILLUSTRATE

Preview degli argomenti che verranno affrontati nelle prossime lezioni

ULTERIORI APPROFONDIMENTI - NON  SONO RICHIESTI NEI TEST D'ESAME

I successivi argomenti non saranno utilizzati nella definizione dei test d'esame

Per i teoremi di De Morgan ho che:

  • la condizione NON(O(A1;A2;A3)) è equivalente a E(NON(A1);NON(A2);NON(A3))

  • la condizione NON(E(A1;A2;A3)) è equivalente a O(NON(A1);NON(A2);NON(A3))

Le 2 relazioni sono facilmente dimostrabili in modo tabellare confrontando le rispettive tavole della verità.

ESEMPI PRESI DAI TEST RELATIVI AL TEMPO

>> Esempio 1: Determina il numero di giorni del prossimo mese

L'idea è quella di applicare la funzione GIORNO() alla data dell'ultimo giorno del prossimo mese.

=GIORNO(UltimoGiornoMeseProssimo)

Per ottenere l'ultimo giorno del mese prossimo costruisco la data corrispondente al 1° giorno del mese successivo al prossimo e sottraggo 1

UltimoGiornoMeseProssimo DATA.VALORE(1GiornoMeseSuccessivoAlProssimo)-1

Il primo giorno del mese successivo al prossimo può essere ottenuto con la seguente concatenazione:

1GiornoMeseSuccessivoAlProssimo "01/" & NrMeseSuccessivoAlProssimo & "/" & (ANNO(OGGI())+SE(MESE(OGGI())>10;1;0))

Il numero del mese successivo al prossimo si ottiene sommando 2 al mese corrente. Se il mese corrente è 11 (novembre) o 12 (dicembre) la somma risulterà rispettivamente 13 e 14 e pertanto, in questo caso, dovremo sottrarre 12.

MeseSuccessivoAlProssimo MESE(OGGI())+2-SE(MESE(OGGI())>10;12;0)

Aggregando tutte le formule viste otteniamo la soluzione richiesta:

=GIORNO(DATA.VALORE("01/"&(MESE(OGGI())+2-SE(MESE(OGGI())>10;12;0))&"/"&(ANNO(OGGI()))+SE(MESE(OGGI())>10;1;0))-1)

 

accessi: