Laboratorio Informatico per le Decisioni Aziendali, Data mining, primo parziale

HANDS ON EXCEL

Apriamo il file excel abc

Per spostarsi da un foglio di lavoro all’altro uso:

CTRL + pag↑ (tasto 9 a lato, ci deve essere inserito il bloc num)

 CTRL + pag↓ (tasto 3 a lato, ci deve essere inserito il bloc num)



Per spostarsi da una casella qualsiasi alla casella A1 uso:

CTRL + ↖ (tasto 7 a lato, ci deve essere inserito il bloc num)



Per spostarsi da una casella qualsiasi all’inizio della riga:

FN + ↖ (tasto 7 a lato, ci deve essere inserito il bloc num)



Per spostarsi da una casella qualsiasi alla fine dell’area di lavoro uso:

CTRL + Fine (tasto 1 a lato, ci deve essere inserito il bloc num)



Per spostarci in modo rapido, per esempio da un numero all’altro, senza passare per le caselle

vuote utilizzo:

CTRL + FRECCE



Per copiare, incollare, tagliare, annullare uso:

CTRL + C → copio

 CTRL + V → incolla

 CTRL + X → taglia

 CTRL + Z → annulla

 CTRL + Y → ripristina



Per selezionare uso:

CTRL + SPACE → seleziono colonna intera

 SHIFT + SPACE → seleziono riga intera

 CTRL + SHIFT + SPACE → seleziono area

 CTRL + SHIFT + FRECCE → seleziono area che mi interessa con numeri scritti



Per inserire o togliere righe / colonne :

CTRL + + → inserisci

 CTRL + - → elimina

 CTRL + SPACE + + → inserisci riga



Fissare uno o più elementi

A B C D E F

Esempio 1

35

36

37 cost 2

38

39 num 1 2 3 4

40 num+cost 3 4 5 6 1

Se voglio sommare la costante a un numero, devo selezionare la formula giusta e copiarla per tutti gli

altri numeri. In questo esempio devo fissare la costante.

Mi posiziono sulla cella gialla: =C39+$C$37

Per avere $C$37 devo andare sulla casella C37 e premere F4 , il simbolo dollaro serve per segnare

quel preciso numero. Così facendo, copio la casella C40 e la incollo in D40, E40, F40.

Vediamo meglio le operazioni di copia e incolla.

Cliccando su F2 posso visualizzare il calcolo presente nella cella.

Prendiamo per esempio due numeri:

Se io volessi copiare il 5 in E22 e 6 in F22 cosa devo fare? Faccio un’operazione di copia e incolla

con riferimento relativo:

A B C D E F

22 5 6 5 6

Mi posiziono in E22: =B22 , copio questa casella, mi posiziono in F22 e incollo.

Così facendo ottengo le caselle gialle.

Se, invece, volessi copiare il 5 sia in E25 e F25? Faccio un’operazione di copia e incolla con

riferimento assoluto e mantengo il riferimento della colonna:

A B C D E F

25 5 6 5 5

Mi posiziono in E22: =$B25

Per avere $B25 premo =, mi sposto fino a B25, premo F4 fino ad avere $B25 . In questo modo avrò 5 in

E22 e copiando questa cella e incollandola in F25 avrò ancora 5.

Se, invece, volessi copiare il 5 sia in E28, E29 e F28? Faccio un’operazione di copia e incolla con

riferimento assoluto e mantengo il riferimento sia della colonna che della riga:

A B C D E F

28 5 6 5 5

29 5

Mi posiziono in E22: =$B$25

Per avere $B$25 premo =, mi sposto fino a B25, premo F2 per fissare la casella e premo F4 fino ad

avere $B$25. Con $B fisso la colonna e con $25 fisso la riga. In questo modo avrò 5 in E28 e copiando

questa cella e incollandola in F28 e in E29 avrò ancora 5.

Ora vediamo un altro esempio

Per scrivere Esempio 1 in grassetto prima scrivo, seleziono e premo CTRL + G

Ora scriviamo una tavola pitagorica partendo dalla cella M3. 2

Scrivo 1, 2 e poi dall’angolino destro della cella tiro verso il basso e così facendo continuano in

automatico i numeri successivi. Seleziono questi numeri da 1 a 12 e li facciamo in rosso e in grassetto.

Per avere gli stessi numeri sulla riga seleziono i numeri, copio,

premo il simbolo che corrisponde al tasto destro del mouse

(posizionato tra alt gr e ctrl oppure SHIFT + F10) e clicco su

incolla speciale. Si apre questa tendina: devo selezionare

Trasponi , essendo la T sottolineata, premo ALT + T e si

seleziona. Il carattere essendo grassetto e rosso, seleziono in alto

tutto.

Così facendo si sono copiati i numeri anche sulla colonna.

Se le colonne sono troppo larghe, le seleziono cliccando CTRL + SPACE e CTRL +SHIFT + FRECCIA DX e

con il mouse riducendo una colonna, si riducono tutte. Inoltre facendo un doppio clip (indicatore

dimensione) le colonne si adattano alla scritta.

Ora per poter costruire la tavola pitagorica, devo fissare le giuste celle:

M N O P Q R S T U V W X Y

1 2 3 4 5 6 7 8 9 10 11 12

5 1

6 1 2 3 4 5 6 7 8 9 10 11 12

2

7 2 4 6 8 10 12 14 16 18 20 22 24

3

8 3 6 9 12 15 18 21 24 27 30 33 36

4

9 4 8 12 16 20 24 28 32 36 40 44 48

5

10 5 10 15 20 25 30 35 40 45 50 55 60

6

11 6 12 18 24 30 36 42 48 54 60 66 72

7

12 7 14 21 28 35 42 49 56 63 70 77 84

8

13 8 16 24 32 40 48 56 64 72 80 88 96

9

14 9 18 27 36 45 54 63 72 81 90 99 108

10

15 10 20 30 40 50 60 70 80 90 100 110 120

11

16 11 22 33 44 55 66 77 88 99 110 121 132

12

17 12 24 36 48 60 72 84 96 108 120 132 144

Mi posiziono in N6: =$M6*N$5

In questo caso devo fissare le celle giuste per completare la tavola più velocemente. Se parto da

=M6*N5 , premo F2 per vedere la formula e fisso la colonna M in quanto sono presenti i numeri da 1 a

10 : =$M6 ; inoltre tutti i numeri orizzontali sono nella riga 5 quindi devo fissare anche la riga: = N$5.

Ora devo copiare questa cella e incollarla in tutte le celle, utilizzo SHIFT + FRECCE per selezionare le

colonne e righe interessate, incollo e ho tutta la colonna.

Per andare da M17 a M6 preso CTRL + ↑ , per muovermi da M6 a Y6 premo CTRL + → ecc …

In matematica la tavola pitagorica sarebbe un prodotto cartesiano dell’insieme dei valori della x e

dell’insieme dei valori della y. Viene valutata una funzione a due posti, nel nostro caso abbiamo

considerato il prodotto tra x e y: . 3

Dal punto di vista informatico una funzione a due posti viene definita come outer functions e anche in

excel possiamo ottenere tutti i valori che la nostra funzione assume in corrispondenza del prodotto

cartesiano dell’insieme x e y applicando la funzione al prodotto cartesiano . La nostra funzione

è un’applicazione da questo insieme definito come prodotto cartesiano all’insieme dei numeri reali.

Vediamo un esempio di tavola pitagorica con comando matriciale (outer functions)

Copiamo gli elementi da 1 a 12 che ci interessano a partire da M34:

M N O P Q R S T U V W X Y

1 2 3 4 5 6 7 8 9 10 11 12

33 1

34 1 2 3 4 5 6 7 8 9 10 11 12

Mi posiziono in N34: =M34:M45 * N33:Y33

In questo caso non voglio selezionare solo M34 ma tutte le righe quindi andiamo su M34 e premiamo

CTRL + SHIFT + ↓ e ottengo M34:M45, questo lo vogliamo moltiplicare per tutte le colonne quindi

andiamo su N33 , clicco CTRL + SHIFT + → e ottengo N33:Y33. Diamo l’invio e excel compila in

automatico tutta la tabella.

NB: questo funziona solo con una versione di excel successiva al 2020.

Cosa possiamo fare se abbiamo una versione precedente al 2020?

Possiamo usare un comando matriciale: selezioniamo l’insieme delle celle su cui vogliamo che agisca il

comando matriciale, poi premiamo F2 per poter scrivere la nostra funziona.

Quindi in N34 abbiamo: = M34:M45*N33:Y33 , ottenuto come sopra, ma in questo caso non dobbiamo

premere invio perché vogliamo che agisca su tutte le celle quindi premiamo CTRL + SHIFT + INVIO .

Questo è un comando matriciale.

Funzioni di concatenamento del testo

Se abbiamo un output e vogliamo riportare una descrizione prendendo dei risultati che figurano

all’interno del output aggiungendo del testo, dobbiamo effettuare una cosiddetta concatenazione.

Andiamo nella cella T37, se vogliamo andare

direttamente lì possiamo andare nella casella noma

che si trova nella barra in alto a sinistra e scrivere

direttamente lì cella in cui vogliamo spostarci.

Utilizzo & per concatenare del testo.



Esempio AA AB AC AA AB AC

28 a b c 28 a b c

29 abc 29 a b c 4

Mi posiziono in AA29: =AA28&AB28&AC28

Per fare questo mi posiziono da AA29 clicco = ↑ & ↑→ & ↑→→ e premio invio, così ottengo abc.

Se fosse necessario aggiungere degli spazi, rientro nella formula con F2 e definisco gli spazi attraverso

l’inserimento di apici: =AA28 & “ ” & AB28 & “ ” & AC28

Ora tornando alla struttura precedente, se voglio riportare nella casella AB29 la struttura che abbiamo

utilizzato cosa faccio?

Rientro nella formula con F2, selezioniamo il contenuto con SHIFT+HOME (↖) , copio il contenuto con

CTRL+C e incollando ottengo ancora abc ed entrando nella formula, con F2, posso dire ad excel che

voglio riportare questa formula con una stringa di testo, come faccio? Uso un apice prima dell’uguale

ossia: ‘=AA28&AB28&AC28

Così facendo viene riportata la formula secondo la sua struttura:

AA AB AC

28 a b c

AA28&AB28&AC28

29 a b c

Ora vediamo un altro esempio di outer function.

Vediamo una funzione sempre applicata alle x e y che sono i numeri da 1 a 12.

AA AB AC AD AE AF AG AH AI AJ AK AL AM

1 2 3 4 5 6 7 8 9 10 11 12

33 1

34 f(1,1) f(1,2) f(1,3) f(1,4) f(1,5) f(1,6) f(1,7) f(1,8) f(1,9) f(1,10) f(1,11) f(1,12)

2

35 f(2,1) f(2,2) f(2,3) f(2,4) f(2,5) f(2,6) f(2,7) f(2,8) f(2,9) f(2,10) f(2,11) f(2,12)

3

36 f(3,1) f(3,2) f(3,3) f(3,4) f(3,5) f(3,6) f(3,7) f(3,8) f(3,9) f(3,10) f(3,11) f(3,12)

4

37 f(4,1) f(4,2) f(4,3) f(4,4) f(4,5) f(4,6) f(4,7) f(4,8) f(4,9) f(4,10) f(4,11) f(4,12)

5

38 f(5,1) f(5,2) f(5,3) f(5,4) f(5,5) f(5,6) f(5,7) f(5,8) f(5,9) f(5,10) f(5,11) f(5,12)

6

39 f(6,1) f(6,2) f(6,3) f(6,4) f(6,5) f(6,6) f(6,7) f(6,8) f(6,9) f(6,10) f(6,11) f(6,12)

7

40 f(7,1) f(7,2) f(7,3) f(7,4) f(7,5) f(7,6) f(7,7) f(7,8) f(7,9) f(7,10) f(7,11) f(7,12)

8

41 f(8,1) f(8,2) f(8,3) f(8,4) f(8,5) f(8,6) f(8,7) f(8,8) f(8,9) f(8,10) f(8,11) f(8,12)

9

42 f(9,1) f(9,2) f(9,3) f(9,4) f(9,5) f(9,6) f(9,7) f(9,8) f(9,9) f(9,10) f(9,11) f(9,12)

10

43 f(10,1) f(10,2) f(10,3) f(10,4) f(10,5) f(10,6) f(10,7) f(10,8) f(10,9) f(10,10) f(10,11) f(10,12)

11

44 f(11,1) f(11,2) f(11,3) f(11,4) f(11,5) f(11,6) f(11,7) f(11,8) f(11,9) f(11,10) f(11,11) f(11,12)

12

45 f(12,1) f(12,2) f(12,3) f(12,4) f(12,5) f(12,6) f(12,7) f(12,8) f(12,9) f(12,10) f(12,11) f(12,12)

Abbiamo una funzione f applicata ad x e y , se ci posizionassimo in AA34 avremmo , se fossimo

posizionati in AE36 avremmo ecc…

Quindi quello che dobbiamo fare è utilizzare le funzioni di concatenamento del testo in modo da

arrivare a riempire la tabella con queste funzioni. Come facciamo?

Selezioniamo l’area di nostro interesse, premiamo in F2 e così facendo ci posizioniamo in AB34:

=”f(“&AA34:AA45&”,”&AB33:AM33&”)”

Non devo premere invio, ma CTRL + SHIFT + INVIO. 5

Per avere più spazio su excel, possiamo comprimere la barra superiore e successivamente premendo

ALT usciranno sui vari comandi delle lettere/numeri, cliccando quelle specifiche lettere/numeri

possiamo aprire il comando che ci interessa.

File: automobilifix

Ora vogliamo riclassificare la variabile ORIGINE

Ci posizioniamo nella cella O3 in cui digitiamo ORIGINE, sappiamo che l’origine può essere di 3 tipi: 1,

2 o 3, noi vogliamo conteggiare il numero di unità statistiche di automobili che hanno origine 1,

origine 2 oppure origine 3.

Possiamo usare la funzione: CONTA.SE che conta i numeri di celle in un intervallo che corrispondono

al criterio dato. A questo punto abbiamo due argomenti quindi metteremo l’intervallo di celle di

interesse e il criterio. Come possiamo operare?

L’intervallo di celle è l’insieme delle celle che contengono i nostri dati, a noi serve la colonna

§ ORIGINE quindi andiamo in una cella qualsiasi della colonna, conviene sempre partire dall’ultima

cella quindi usando CTRL + ↓ arriviamo nella cella H392 e facendo CTRL + SHIFT + ↑ seleziono

tutta la colonna con i dati ma non dobbiamo comprendere anche la scritta origine quindi faccio

SHIFT + ↓ e così ho il primo elemento H2:H392

Dobbiamo fare questa operazione anche per origine 2 e origine 3 quindi dovremo copiare e incollare

la funzione, ma per farlo dobbiamo bloccare i riferimenti di riga e quindi con F4 blocchiamo i numeri

di riga: H$2:H$392

Il criterio conteggia le celle che soddisfano un certo criterio, noi vogliamo utilizzare in questa riga

§ le automobili che hanno il numero 1 quindi facciamo ← siamo sull’1 (cella O4) e premiamo invio.

Formula finale: =CONTA.SE(H$2:H$392;O4)

Questa funzione la copiamo e incolliamo per le righe sottostanti, origini 2 e 3.

Se vogliamo fare la somma, basta usare la funzione =SOMMA(P4;P6) Q

O P

Q

O P f. ass ni

3 ORIGINE ni

Frequenza assoluta ni

3 ORIGINE ni 244

4 1 244

=FREQUENZA(H2:H392;O4:O6)

4 1 =CONTA.SE(H$2:H$392;O4) 68

5 2 68

=FREQUENZA(H2:H392;O4:O6)

5 2 =CONTA.SE(H$2:H$392;O5) 79

6 3 79

=FREQUENZA(H2:H392;O4:O6)

6 3 =CONTA.SE(H$2:H$392;O6) 391

7 391

=SOMMA(Q4:Q6)

7 =SOMMA(P4:P6)

Ora proviamo a calcolare le frequenze assolute in un modo che accomuna sia la costruzione delle

frequenze assolute per le serie statistiche in cui abbiamo dei valori nella prima colonna e vogliamo

trovare dei soggetti che corrispondono a quel valore, sia la situazione delle seriazioni statistiche in cui

abbiamo le classi, per ora usiamo la funzione CONTA.SE e FREQUENZA. 6

La funzione FREQUENZA è un comando matriciale, in primo luogo quindi selezioniamo l’insieme di

celle su cui vogliamo che il comando agisca. Premo F2 per inserire la formula.

In questa funzione abbiamo:

La matrice dei dati che contiene le origini quindi selezioniamo tutta la colonna ORIGINE quindi

§ quindi andiamo in una cella qualsiasi della colonna, conviene sempre partire dall’ultima cella

quindi usando CTRL + ↓ arriviamo nella cella H392 e facendo CTRL + SHIFT + ↑ seleziono tutta la

colonna con i dati ma non dobbiamo comprendere anche la scritta origine quindi faccio SHIFT + ↓ e

così ho la matrice dei dati H2:H392

La matrice classi in una seriazione statistica indica la matrice che contiene gli estremi superiori

§ delle classi, in questo caso abbiamo 1,2,3 e quindi gli estremi superiori sono le celle che vanno da

O4 a O6, cosa verrà conteggiato nella prima riga? Tutte le celle che hanno un valore minore ≤ 1 ;

nella seconda riga verranno conteggiate tutte le celle che hanno un valore minore < 2 ; nella terza

riga verranno conteggiate tutte le celle che hanno un valore ≤ 3.

Comando: =FREQUENZA(H2:H392;O4:O6) (comando matriciale)

Essendo un comando matriciale, non premiamo invio ma CTRL + SHIFT + INVIO .

Estrazione di informazioni per un certo soggetto

Ora riportiamo delle funzioni che ci consentono di estrarre delle informazioni riferite a un certo

soggetto. Lavoriamo dalla cella T1.

Se per esempio andiamo a considerare il soggetto che ha un ID pari a 4 vediamo che il suo peso è pari

(

a 1144 kg e si trova nella quinta riga ( = 5) e quinta colonna = 5) nella tabella/matrice che è

$A$1:$392, considerando la matrice comprensiva delle intestazioni di colonna.

Se nel testo voglio colorare la parola quinta di blu cosa faccio? Premo F2 per rientrare nella formula,

così facendo sono alla fine della scritta, per tornare indietro più velocemente uso CTRL + ← per

spostarmi direttamente da una parola all’altra e quando arrivo all’inizio di quinta premo CTRL +

SHIFT + → per selezionarla e poi la coloro.

Ora vediamo di estrarre queste informazioni in maniera automatica, vogliamo il peso.

Andiamo nella cella T5 e scriviamo ID, nella cella T6 scriviamo 4 perché vogliamo le informazioni del

soggetto con ID=4 , vogliamo il peso di questo soggetto.

Procediamo in due step:

1) Ricerchiamo la riga in cui sta il nostro soggetto, quindi usiamo una funzione che ci dica che sta

nella quinta riga.

Usiamo la funzione CONFRONTA che comprende:

Il primo argomento è il valore quindi per noi sarà T4

§ 7

Il secondo argomento è la matrice che è l’insieme dei nostri ID quindi andiamo nella cella A1 con

§ CTRL + ↖ e selezioniamo la colonna con i dati che ci interessano, conviene sempre partire

dall’ultima cella quindi usando CTRL + ↓ arriviamo nella cella A392 e facendo CTRL + SHIFT + ↑

seleziono tutta la colonna con i dati A1:A392, selezioniamo anche la prima riga ID in modo tale che

vediamo che le informazioni del nostro soggetto stanno nella quinta riga.

Il terzo elemento è la corrispondenza, in questo caso vogliamo che la corrispondenza sia il numero

§ esatto, il 4, e quindi mettiamo 0.

Funzione: =CONFRONTA(T6;A1:A392;0)

Premendo invio, questo ci ha restituito proprio il numero 5. T U

T U 5 ID Trova riga (indice i)

5 ID Trova riga (indice i) 6 4 5

6 4 =CONFRONTA(T6;A1:A392;0)

Se invece volessi calcolare la stessa informazione anche per ID=8 allora devo modificare la formula

fissando le informazioni relative alle celle della matrice quindi con F4 blocchiamo $A$1:$A$392 :

Funzione: =CONFRONTA(T6;$A$1:$A$392;0) T U

T U 5 ID Trova riga (indice i)

5 ID Trova riga (indice i) 6 4 5

6 4 =CONFRONTA(T6;$A$1:$A$392;0) 7 8 9

7 8 =CONFRONTA(T7;$A$1:$A$392;0)

Quindi questa funzione CONFRONTA, all’interno di un vettore va a ricercare la prima corrispondenza

esatta di un certo elemento.

2) Ora vogliamo ricavare l’indice di colonna, come facciamo? Sappiamo che le informazioni sono nella

quinta colonna.

La funzione che permette di andare ad estrarre un elemento da una matrice conoscendo l’indice di

riga e l’indice di colonna, si chiama INDICE che restituisce un valore o un riferimento della cella

all’intersezione di una particolare riga e colonna in un dato intervallo. Comprende:

Il primo elemento è la matrice di riferimento e quindi andiamo a selezionare tutti i nostri dati ossia

§ andiamo nella cella A1 con CTRL + ↖ e per selezionare tutto l’insieme d