Dipartimento di Ingegneria Meccanica e Industriale
Informatica
Corso di studi: Medicina e Chirurgia
DIPARTIMENTO DI SCIENZE CLINICHE E SPERIMENTALI
Università degli Studi di Brescia
INFORMATICA
EXCEL
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia Docente: Marco Sechi
E‐mail: [email protected]
Vers. 26/09/2016* #MC#
D.S.C.S. ‐ A.A. 2016/2017
2
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Archivi e fogli
elettronici
Archivi monotabellari
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
3
Terminologia degli Archivi
Molto spesso abbiamo la necessità di creare un archivio, ad esempio per classificare i nostri libri oppure per catalogare le merci in un magazzino o per altre esigenze analoghe.
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Il programma specifico è Access della suite di Microsoft Office oppure il suo equivalente gratuito Base di OpenOffice. Entrambi i programmi, per quanto utili, non sono propriamente user friendly e possono mettere in difficoltà un utente alle prime armi.
Chi ha necessità "limitate" può utilizzare, in loro sostituzione, una serie di funzionalità messe a disposizione sia da Excel che da Calc.
Il database è uno strumento
davvero indispensabile per gestire ordinatamente ed efficacemente i propri dati. Prima di procedere nella spiegazione si rende necessaria una piccola premessa volta ad illustrare cosa sia e a cosa serva un database.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
4
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Un archivio rappresenta il contenitore dove inserire tutte le informazioni che descrivono il fenomeno che intendiamo memorizzare. Un archivio reale è solitamente composto da dati non omogenei (ad esempio Clienti, Prodotti, Ordini, Pazienti, etc. ). Ogni gruppo di dati omogenei viene registrato all'interno di uno stesso contenitore
detto tabella. Le proprietà che caratterizzano ogni singolo elemento (record) della stessa tabella vengono definite campi. Pensando ad un "foglio di lavoro" (contenente dati!) le righe rappresentano i record mentre le colonne i campi. L'insieme delle caratteristiche che descrivono i singoli campi (nome, dimensione, tipo ...) prende il nome di struttura della tabella.
Un database composto da una sola tabella si dice monotabellare. Per gestire un db monotabellare possiamo utilizzare anche il foglio elettronico:
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
5
L'uso di Excel/Calc per gestire degli archivi monotabellari è una pratica molto diffusa. I fogli elettronici, pur non essendo dei gestori di archivi ("programmi database") hanno un apposito menu/ribbon "DATI" che permette di eseguire le 2 funzioni principali, tipiche dei programmi database: ricerca (Filtri) ed ordinamento.
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia In realtà i fogli elettronici presentano 3 limiti che non permettono una gestione efficiente nel caso di archivi complessi e voluminosi:
Nei fogli elettronici non è possibile definire relazioni tra le singole tabelle. Le relazioni attivano meccanismi automatici che ottimizzano la gestione dell'archivio.
Negli spreadsheet non è possibile definire indici sui campi. Gli indici consentono di velocizzare le ricerche e gli ordinamenti
Non è possibile definire in modo esplicito il tipo di dato di un campo. La "non tipizzazione" dei dati implica ordinamenti errati e pertanto performance scadenti nella ricerca.
RELAZIONI TRA TABELLE
Un DB composto da diverse tabelle in relazione tra loro si dice Relazionale. Le relazioni tra le tabelle permettono di manipolare i dati più facilmente e soprattutto evitano la duplicazione delle informazioni (ridondanza) che è inevitabile quando si opera solo con database monotabellari.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
6
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia ESEMPIO DI TABELLE IN RELAZIONE
=CERCA.VERT()
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
7
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Un Diagramma Entità‐Relazioni (ERD o Entity‐Relationship Diagram) come quello sottostante evidenzia i collegamenti logici tra le tabelle del database.
INDICI SUI CAMPI
L’indicizzazione è una delle funzionalità più importanti nei programmi che gestiscono gli archivi (database). Tale funzionalità non è presente nei fogli elettronici. Un indice è una struttura dati (aggregato di dati tra loro logicamente connessi) che permette di velocizzare le ricerche e gli ordinamenti. L'indice viene implementato sui campi soggetti a ricerche ed ordinamenti.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
8
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Se leggo l'archivio DATI seguendo le posizioni indicate dall'indice relativo all' "Età" ottengo un elenco ordinato rispetto a quel campo
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
9
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Se invece leggo l'archivio DATI seguendo le posizioni indicate dall'indice relativo al "Nome" ottengo un elenco ordinato alfabeticamente:
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
10
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Il prezzo dell'indicizzazione è rappresentato da una minor velocità nelle operazioni di aggiornamento dell'archivio (aggiunta/modifica) poiché, oltre alle modifiche sui dati, occorre aggiornare tutti gli indici associati. Nella figura viene mostrato come l'aggiunta di un nuovo record (registrato in coda all'elenco dei dati) determini una serie di operazioni aggiuntive (spostamenti dei contenuti dell'indice per far spazio alla nuova posizione relativa all'ultimo dato aggiunto!) sull'indice (A, B, C, ... , M) che hanno lo scopo di mantenerlo aggiornato.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
11
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia TIPI DI DATO
Quando gli elenchi sono ordinati è possibile applicare una serie di algoritmi di ricerca che permettono di localizzare il dato in tempi brevissimi. L'ordinamento rispetta la relazione d'ordine esistente tra i singoli elementi e viene descritta mediante gli operatori di confronto. La relazione d'ordine è strettamente legata al tipo di dato adottato.
Tipi di dato non adeguati alle nostre esigenze possono determinare ordinamenti inaspettati.
operatore Significato
>
Maggiore di
>=
Maggiore o uguale a
<
Minore di
<=
Minore o uguale a
<>
Diverso =
Uguale a
In un vocabolario cartaceo l'ordinamento è definito dall'alfabeto. Le parole che iniziano con una lettera posta all'inizio dell'alfabeto appaiono prima rispetto alle parole la cui iniziale è una lettera che invece si trova in fondo all'alfabeto (ordinamento lessicografico). Quando 2 parole iniziano con la stessa lettera l'ordinamento viene determinato dalla 2°
lettera e cosi via … Questo modo di confrontare le parole permette di definire una relazione d'ordine tra i vocaboli e quindi un ordinamento
abaco < abadessa < abanese < abano < abarica < abarico < abate < abatino < abatterica
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
12
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Sequenze di simboli alfanumerici (lettere, numeri, punteggiatura, spazi, parentesi etc.) nel calcolatore vengono ordinati utilizzando come alfabeto la tabella ASCII. Quindi la seguente relazione è corretta:
"Cosa" < "casa"
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
13
Sequenze di simboli numerici possono essere interpretate in 2 modi:
A) in termini numerici – solitamente su tali informazioni si effettuano successivamente dei calcoli o delle statistiche
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Voto esame 28 calcolo la media dei miei esami
Peso 72,38 trovo il peso totale della merce acquistata
B) Come semplice sequenza di simboli (caratteri!) dove non effettuo mai dei calcoli
CAP "25192"
Telefono "0302918312"
Password "123456"
Partita IVA "12345678901"
Si osservi che nella notazione informatica, per evidenziare questa seconda interpretazione, la sequenza viene racchiusa tra doppi apici ". Tale sequenza viene indicata con il termine stringa
Quando una sequenza di numeri è interpretata come valore numerico la relazione d'ordine tra 2 valori è quella classica e pertanto la relazione seguente è vera.
1230 > 129 Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
14
Quando invece la sequenza ha una valenza simbolica, priva di significati numerici, subentra il confronto lessicografico basato sull' "alfabeto" ASCII
e pertanto la relazione precedente diventa:
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia "1230" < "129" Infatti le prime 2 cifre sono uguali nelle 2 sequenze per cui il confronto parte con il terzo simbolo. Essendo "3" < "9" segue la relazione indicata. Diverse relazioni d'ordine (esempio lessicografico e numerico) determinano ordini differenti, entrambi corretti!
Sequenza ordinata di valori numerici
Sequenza ordinata di stringhe numeriche (numerali)
RAPPRESENTAZIONE DEI DATI CRONOLOGICI
La notazione in base e la codifica ASCII consentono, all'interno del calcolatore, la registrazione delle due tipologie di dato più semplici: i numeri e i caratteri. Vediamo ora come è possibile codificare informazioni di natura temporale impiegando questi semplici tipi di dato.
Utilizzando il tipo alfanumerico una data potrebbe essere rappresentata con la sequenza di simboli numerici corrispondenti al "giorno/mese/anno".
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
15
Utilizzando questa codifica il 3 gennaio 1964 viene rappresentato mediante la stringa: "03/01/1964". L'ordinamento lessicografico, tipico delle stringhe, non risulta però compatibile con quello cronologico ed infatti: "03/01/1964" > "02/01/2016" Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Se però rovesciamo la scrittura della data indicando "anno/mese/giorno" l'ordinamento lessicografico diventa compatibile con quello cronologico ed infatti:
"1964/01/03" < "2016/01/02"
Il separatore "/" tra anno e mese e tra mese e giorno in realtà può essere un qualsiasi carattere. Il vantaggio di questa rappresentazione è la leggibilità (intuisco immediatamente di quale data si tratta!). Lo svantaggio (che la rende poco pratica!) invece è che non permette di implementare semplicemente operazioni come le seguenti:
OGGI+1 Data di domani
OGGI‐1 Data di ieri
OGGI‐DATADINASCITA totale dei giorni vissuti fino a oggi
Un'altra modalità per rappresentare le date è quella che utilizza il tipo numerico con un procedimento simile a quello appena visto per il tipo testuale. Se registriamo una data con il numero intero ottenuto calcolando la seguente formula:
∗ 10000
∗ 100
otteniamo un valore corrispondente alla data scritta in modo capovolto. Infatti utilizzando questa codifica il 3 gennaio 1964 viene rappresentato con il numero:
1964*10000+1*100+3=19640103
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
16
Tale rappresentazione garantisce la compatibilità tra ordinamento numerico e cronologico. Come nel caso alfanumerico abbiamo il vantaggio della leggibilità ma non possiamo effettuare operazioni come quelle precedentemente descritte. Infatti
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia 20181231+1=20171232
non rappresenta la codifica del giorno successivo!
Un'altra soluzione che sfrutta la codifica numerica è quella che rappresenta una data con il numero di giorni trascorsi (o mancanti!) rispetto ad una data di rifermento come ad esempio il 1/1/1900. In questo caso il numero 43491 corrisponde al giorno 26/01/2019. Tale codifica della data non è chiaramente umanamente leggibile (non è immediato capire quale sia la data associata a 43491!) ma il problema viene risolto grazie all'interfaccia grafica di Excel che visualizza la rappresentazione interna in una data comprensibile. Il grosso vantaggio di questo approccio è che le operazioni di somma e differenza assumono un senso cronologico corretto. Infatti
26/1/2019 43491 + 1 43492 27/1/2019 giorno successivo
26/1/2019 43491 ‐ 1 43490 25/1/2019
26/1/2019 – 26/2/2018 43491 – 43157 =334
giorno precedente
giorno trascorsi
Excel utilizza proprio questa modalità di codifica per rappresentare le date. Infatti se all'interno di una cella scriviamo la data 26/1/2019 e successivamente impostiamo una maschera di formato numerica come "0" otteniamo 43491. Le ore vengono codificate come frazione di giorno pertanto le 18.00 corrispondono a 0,75.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
17
Algoritmi di ricerca (cenni)
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Negli elenchi ordinati/indicizzati è possibile applicare una serie di algoritmi che abbattono i tempi di risposta nelle ricerche. In un elenco non ordinato l'unica ricerca possibile è quella sequenziale.
Esempio reale: Consideriamo l'elenco telefonico cartaceo dove immaginiamo, per semplicità, che venga gestita un'unica località. L'indice (ordinamento!) implementato è sul nominativo per cui la localizzazione di un numero di telefono in base al cognome risulta immediata. Immaginiamo ora di voler conoscere il nominativo della persona che risponde ad un determinato numero di telefono. In questo caso l'indice non ci supporta per cui siamo costretti a scorrere l'elenco, partendo dall'inizio, un numero dopo l'altro fino a che non si trova quello richiesto (ricerca sequenziale). I tempi di risposta in questo caso potrebbero essere anche molto lunghi.
Analizziamo ora due metodi (algoritmi) di ricerca.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
18
RICERCA SEQUENZIALE
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia La ricerca sequenziale o completa consiste nella scansione in sequenza dei record dal primo fino all'ultimo e si interrompe quando il valore cercato è stato trovato oppure quando si è sicuri che il record voluto non esista in elenco. Ha il vantaggio di essere sempre applicabile, anche su archivi non ordinati, ma presenta come rovescio della medaglia un'estrema lentezza.
Quando consulto un archivio, un criterio per valutare la velocità di ricerca è rappresentato dal numero di letture (confronti) che devo effettuare prima di localizzare il record che mi interessa. Il numero medio di letture necessarie per individuare un record con un particolare algoritmo di ricerca quindi può essere considerato un valido parametro per misurare la bontà del metodo.
Le casistiche possibili che posso avere ricercando un nominativo all'interno di un archivio di N record sono:
•
•
•
•
1° casistica: trovo il record cercato dopo una sola lettura (sono fortunato! L'ho trovato al primo colpo!)
2° casistica: il record viene localizzato dopo 2 letture
... N° casistica: il record viene localizzato dopo N letture (ad esempio quando il nominativo cercato è in fondo all'archivio) oppure non è in elenco.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
19
Il numero complessivo di confronti effettuato nelle casistiche citate è pari a:
1
2
…
1
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Per induzione è possibile dimostrare la relazione:
1
2
…
·
1
1
2
Il numero medio di letture si ottiene dividendo il numero totale di confronti in tutte le casistiche per il numero di casistiche da cui segue:
numero medio di letture
1
2
…
·
1
2·
1
2
La formula ottenuta evidenzia come la media dei confronti (letture) aumenti linearmente al crescere dell'archivio rendendo la ricerca sequenziale improponibile in archivi di grandi dimensioni. Ad esempio in un archivio di un 1.000.000 di record sono necessari, in media, circa 500.000 confronti prima di estrarre il valore cercato.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
20
RICERCA CON INDICE (ALGORITMO DICOTOMICO)
RICERCA CON INDICE: In un archivio indicizzato il numero massimo di letture scende a:
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia La base m del logaritmo dipende dal tipo di algoritmo di ricerca utilizzato.
Un esempio di ricerca con indice è rappresentato dall'algoritmo dicotomico (o binario) che è molto simile al metodo impiegato per trovare una parola in un dizionario. Sapendo che il vocabolario è ordinato alfabeticamente non inizio dal primo elemento ma parto da quello centrale, cioè a metà del dizionario. Nella ricerca dicotomica m vale 2. Quindi con un 1.000.000 di record il numero massimo di confronti che devo effettuare per individuare un record con la ricerca binaria è pari a: log2 (1.000.000)=ln(1.000.000)/ln(2) ≈ 19,93 confronti. Una cifra irrisoria rispetto ai 500.000 confronti medi della ricerca sequenziale!
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
21
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia L'algoritmo dicotomico/binario può essere cosi riassunto
1) Indico con P (estremo sinistro) il primo elemento e con U l'ultimo (estremo destro) della sequenza dove cercherò E.
2) Determino l'elemento centrale M della sequenza che parte da P e finisce con U;
3) Se M < E significa che tutti gli elementi da P a M possono essere scartati (la sequenza è ordinata per cui sono sicuramente inferiori di E essendo minori di M!) e pertanto sposto l'estremo sinistro P ad M+1 ovvero pongo P=M+1 e salto al punto 6)
4) Se M > E significa che tutti gli elementi da M a U devono essere scartati. Sposto quindi l'estremo destro U su M‐1 ovvero pongo U=M‐1 e passo al punto 6)
5)
Se M=E allora l'elemento E è stato trovato e passo al punto 8) 6) Se P (estremo sinistro) è maggiore di U
(estremo destro) allora l'elenco si è esaurito e l'elemento E non è stato trovato. L'algoritmo termina e pertanto salto al punto 8)
7) Ritorno al punto 2)
8) FINE
Analizzando il processo di ricerca si osserva che ad ogni confronto viene scartata metà della sequenza di record che restava da analizzare. Segue quindi che dopo k confronti restano N/2k
record da analizzare. Pertanto la sequenza si esaurisce quando k= log2(N) Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia Schema di flusso della ricerca dicotomica.
D.S.C.S. ‐ A.A. 2016/2017
22
Vediamo un esempio di ricerca all'interno di un vocabolario che abbatte ulteriormente il numero massimo di confronti. L'idea è quella di raggruppare i vocaboli in base alle prime k lettere. In questo modo la ricerca viene effettuata solo su un sottoinsieme ridotto di parole e non sulla totalità dei vocaboli.
Indice sulla prima lettera
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Indici sulla seconda lettera
o o o
Effettuo una ricerca binaria sui singoli
sottoelenchi
Effettuo una ricerca binaria sui singoli
sottoelenchi
Effettuo una ricerca binaria sui singoli
sottoelenchi
Effettuo una ricerca binaria sui singoli
sottoelenchi
Con il primo livello (indice sulla 1° lettera) escludo dalla ricerca circa i 25/26‐esimi (dipende dalla distribuzione delle lettere iniziali nel vocabolario italiano!) del totale di tutti i vocaboli presenti nella lingua italiana. Al successivo livello ottengo un'ulteriore riduzione di circa 25/26‐esimi e pertanto l'insieme finale su cui realmente applico l'algoritmo binario di ricerca risulta drasticamente ridotto.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
23
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Il foglio elettronico è un programma nato per automatizzare i calcoli. Il suo aspetto tabellare ha fatto si che molti utenti vedessero in lui un valido strumento per trasporre digitalmente i propri elenchi contenti dati.
L'uso diffuso e sistematico del foglio elettronico come gestore di database (monotabellari!) ha portato le case produttrici ad inserire (ormai da tantissimi anni) un menu ad hoc per la gestione dei dati.
Con il pulsante "Ordina" è possibile ordinare i dati contenuti su un foglio
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
24
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Con il pulsante "Filtro" è possibile visualizzare solo le righe che soddisfano una determinata ricerca.
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
25
Dipartimento di Scienze Cliniche e Sperimentali – Corso di studi: Medicina e Chirurgia Utilizzando il pulsante modulo
possiamo passare dalla visualizzazione "Lista" (tabellare) alla visione "Scheda" (singolo record) che consente di trattare i dati contenuti nel foglio con le modalità tipiche dei "database". Per aggiungere il pulsante "Modulo" in 2016 occorre personalizzare la barra di "accesso rapido" selezionando la voce "Altri comandi"
Docente: Marco Sechi ‐ Informatica – Università degli studi di Brescia D.S.C.S. ‐ A.A. 2016/2017
