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

Hands on Excel

Spostarsi nei fogli di lavoro

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

Operazioni di copia, incolla e selezione

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 celle in Excel

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.

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:

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:

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:

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.

Creare una tavola pitagorica

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

Ora scriviamo una tavola pitagorica partendo dalla cella M3.

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 click (indicatore dimensione) le colonne si adattano alla scritta.

Completare la tavola pitagorica

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

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…

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 nomache si trova nella barra in alto a sinistra e scrivere direttamente lì cella in cui vogliamo spostarci. Utilizzo & per concatenare del testo.

Esempio:

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:

Outer function

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:

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.

Comando matriciale nelle versioni precedenti a Excel 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.

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.

Usare la funzione CONTA.SE per riclassificare variabili

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.
• 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).

Calcolare le frequenze assolute

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.

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.

Uso della funzione CONFRONTA

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
• 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.

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)

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