Scarica Appunti Excel - Computer science Bocconi e più Appunti in PDF di Elementi di Informatica solo su Docsity!
Lezioni 1-2, 30424 Computer Science – CLEACC A.A. 2021/2022 Benedetta Chiaro
Lezione 1 e 2
CASO CINEMAX – ripasso delle basi Mi interessa il foglio data 1 e data 2 (nel documento dati Eurostat) Ho bisogno di riportare nel foglio budget i dati. Per lavorare correttamente prendo i due fogli dei dati Eurostat e li porto in budget. Due strade:
- Ignorante: Copia e incolla, ignorante perché copio le celle e non il foglio. Il foglio può avere altre info che io non vedo.
- AVERE I DUE FILE APARTI, STRUMENTI TASTO DESTRO SU NOME FOGLIO COPIA O SPOSTA ALLA FINE In Excel si conferma con invio A questo punto rinomino i due file spostati o copiati. Il foglio di calcolo riesce a gestire tanti calcoli con tanto spazio Comando per il calcolo = 12000/ SI CONFERMA CON INVIO NON SI CLICCA A CASO perché INCASINA TUTTO I comandamento: Excel è un perfetto idiota, fa quello che diciamo noi ma dobbiamo farlo nella maniera corretta. Io non devo usare i numeri ma i riferimenti di cella. Le operazioni sono tra contenuto di celle diverse, così cambiando il contenuto di una cella cambia il contenuto di tutte le altre = vuol dire attenzione Excel devi calcolare qualcosa QUADRATINO A DESTRA – TIRO GIU MI FA LA STESSA OPERAZIONE PER TUTTE SOMMA Si fa in tanti modi (b4+b5… non è un modo utile perché devo sommare tutto a mano e c’è un limite, non posso permettermelo) Si usano delle funzioni: la formula prevede un’iscrizione tramite segni, la funzione invece è una formula pre-impostata, salvata. In Excel sono 500, altre si possono acquistare o creare. Come inserire una funzione? Mi posiziono nella cella dove voglio una formula, vado su formule, scheda formule Uno dei modi più “stupidi” è trovare la categoria andando a tentoni. SOMMA: somma i numeri presenti in una o più intervalli di celle. Si aprirà una finestra di inserimento guidato Il fatto che proponga un intervallo di cellule è deleterio. Modo 2: inserisci funzione (pulsantone) La barra di ricerca non funziona sempre. Se io dico ok mi ritrovo nella situazione di prima. Sintassi della funzione =NOMEFX(arg 1 ; arg 2 ; ...) =SOMMA(B4:B6) Si può usare sia maiuscolo minuscolo, le parentesi sono solo tonde, la sintassi è solo italiana. La sintassi serve perché devo riuscire a capire dove c’è un errore per poi imparare a scriverle a mano.
Lezioni 1-2, 30424 Computer Science – CLEACC A.A. 2021/2022 Benedetta Chiaro
Metodo più veloce, scrivo somma e poi vado sulla finestra di inserimento guidato (tramite fx in alto a sinistra). Il sistema più ignorante è la somma automatica: prenderebbe anche b7, cella vuota. È un errore brutto: c’è una differenza forte tra tabella di Excel e tabella di un database. Il valore di sintesi deve essere sempre staccato pertanto nel b7 non scriverò mai un valore perché deve mantenerlo staccato. La funzione può essere portata a destra o copiata. ANALISI DI MERCATO Strumento trova e seleziona per cercare i dati di “Barcelona”. BLOCCA RIQUADRI PER BLOCCARE LA PRIMA COLONNA = clicco su posti cinema EU e clicco sulla cella che voglio avere (devo fare così perché se copio e incollo quando i dati cambieranno non lo saprò) - stessa cosa per posti e riquadri. Per calcolare la quota di mercato potenziale si deve ricorrere a una formula: non esiste la funzione percentuale! La percentuale va scritta come numero decimale (vd sopra) e poi lo formatto come percentuale. Questo va bene ma quando lo estendo alle celle sotto non funziona più. Questo succede perché esistono riferimenti di cella di tre tipi:
- Riferimenti relativi: la posizione delle celle relativamente a quella dove sto scrivendo. Quindi quando li copio Excel mantiene le stesse distanze (b3, f8...) 2 - Riferimenti assoluti: $B$3 il dollaro dice di bloccare la cella (schiacciando F4 cambia da relativo ad assoluto, è un comando circolare perché se continuo a schiacciare continua a cambiare da una cosa all’altra) 3 - Riferimenti misti: $B3, F$8 – BLOCCO SOLO UNO DEI DUE MOVIMENTI. Si usa =b2/b$7 rimane bloccata la riga 7 ma b si muove quando scendi in basso non devi cambiare, quando vai a destra si Conteggio: =CONT
- conta numeri conta le celle non vuote che contengono numeri
- conta valori conta le celle non vuote
- conta vuote =MEDIA dà la media =MAX dà il massimo =MIN dà il minimo QUOTA DI MERCATO NEL 2018: Numero di posti nel 2018/N. posti totali. Questo secondo valore è quello che devo bloccare perché i posti nel 2018 di tutte le città vanno divisi per i posti totali che sono sempre la stessa casella (b23). Posso quindi bloccare riga e colonna con $B£23 o in maniera più elegante solo la riga b$23. VARIAZIONE: Posti nel 2018 – Posti 2017 (c5-b5) VARIAZIONE IN VALORE ASSOLUTO: =ASS la funzione porta tutto quello che c’è nella parentesi in valore assoluto.
Lezioni 3, 30424 Computer Science – CLEACC A.A. 2021/2022 Benedetta Chiaro
Lezione 3 - Strutturare ed elaborare i dati nel foglio elettronico Spesso i dati di Excel sono fuori dal foglio di calcolo, oppure sono inutilizzabili. Argomento più importante: funzioni logiche e nidificate. SOLAR PANELS I dati vengono spesso forniti in .txt poiché si tratta di un formato di scambio, leggibile da tutti i computer. Per risolvere il problema dobbiamo:
- Importare in Excel i dati che ci servono
- Pulire i dati per renderli utilizzabili
- Elaborare i dati per produrre informazioni utili
- Importare i dati che ci servono Per importare i dati, ci sono molte opzioni: operativamente utilizzeremo la più semplice, il copia-incolla. Dati>Recupera e trasforma dati – posso importare i dati da fonti diverse, con il vantaggio che se uso queste figure di importazione guidata. Il vantaggio di utilizzare queste figure guidate è che viene lasciato un legame tra i dati che ho dentro il foglio e l’origine. Non utilizziamo questo metodo operativamente perché ci sono differenze tra i vari computer, se ci sono dati incompatibili come un numero decimale scritto con il punto Excel li conta ad esempio come date o orari. Però questo metodo va conosciuto a livello teorico. .csv come .txt è un file di scambio, sta per “comma separated value”. Sono file che pesano poco e che sono leggibili da ogni dispositivo. A questo punto inizia una procedura di trasformazione dei dati. Vado su Dati>Testo in colonna, è identica a quella dell’importazione precedente ma usiamo questa per i nuovi aggiornamenti. Step 1: Sono delimitati o a larghezza fissa? Guardo la riga 1. Delimitato significa che c’è un delimitatore, un punto e virgola ad esempio; se non c’è delimitato dveo usare larghezza fissa (5, 10 colonne) Step 2: DEVO PRIMA TOGLIERE TUTTI I DELIMITATORI SELEZIONATI PER METTERNE SOLO UNO. La parte a destra la ignoro.
Lezioni 3, 30424 Computer Science – CLEACC A.A. 2021/2022 Benedetta Chiaro
Step 3: Più importante. La parte sinistra mi chiede che formato voglio per i miei dati per colonna. La cosa più importante da ricordare è come comportarsi con la data: se c’è una data bisogna specificare il formato di data (il problema si crea ad esempio con le date all’americana). Avanzate serve per specificare a Excel come i dati sono intesi nei dati che sto convertendo e vale per l’intero file. 2, Pulire i dati. Abbiamo creato la nostra tabella ma ci sono ancora dei problemi da risolvere:
- I “List price” sono stati letti da Excel come testo
- Nella colonna producer lo stesso producer compare scritto diversamente e questo è importante da notare perché per Excel saranno due cose diverse
- I decimali nelle percentuali non sono uguali List price Devo selezionarli tutti (i dati nella colonna H, non la colonna H) e fare due Trova e sostituisci: Il primo è il sostituire punto e virgola; il secondo il $ con niente: I passaggi vanno fatti con questo ordine altrimenti non capisce che sono valute. A questo punto formatto tutto per renderlo più leggibile, il problema dei decimali nella percentuale si risolve ricliccando su percentuale. Ora bisogna risolvere il problema dei troppi spazi. Questi dati per essere manipolati o cambiati, oppure uso una funzione sulla colonna L =ANNULLA.SPAZI Mi sono messa su una cella sola perché le funzioni si inseriscono su una cella sola e poi si copiano a destra o sotto. Questa regola vale anche sul che cosa mettere nel testo. L’operazione si fa, per la maggior parte delle funzioni, sulla riga. Per questo qui come argomento della funzione (Argomento unico) è b2. Doppio click sul pallino vuol dire: estendi fino a che c’è la tabella. Di questi dati non me ne faccio nulla se non li sovrascrivo. ATTENZIONE: generalmente noi consideriamo i dati di partenza come sacri, questo è l’unico caso in cui sovrascriviamo. Per questo motivo, copiamo l’intervallo ma non facciamo l’incolla brutale, ci mettiamo su b2 e abbiamo un sacco di opzioni tirando giù la tendina. Andiamo su incolla speciale a vederle. Usiamo incolla valori. Elimino quindi la colonna L.
Lezione 4 – Analisi dei problemi e applicazione degli strumenti informatici: un caso di decision making SOFTWARE SELECTION Primo step: quale software scegliere? Ho un elenco di lavori e ho messo anche un peso, cioè ho dato una certa importanza a una determinata caratteristica. Devo fare una media ponderata. Uso la funzione Matrice somma prodotto. =MATR.SOMMA.PRODOTTO Se non ho la stessa dimensione nei due vettori, che possono avere fino a 255 righe. Attenzione a ricordare di bloccare la seconda colonna! Formatto con i simboli americanata per evidenziare il software che scelgo. Formattazione condizionale (da usare all’esame) A questo punto voglio andare su DB Aziende e mettere in evidenza le aziende che usano il Software Intertax, che è quello che ha avuto la performance migliore. A me interessa quindi evidenziare l’azienda! Step 1: Inizio selezionando tutte le celle che potenzialmente vengono colorate (da A2 alla fine della cella). Mai selezionare l’intera colonna; mai selezionare l’intestazione! In una selezione, la cella bianca è quella da cui ho iniziato a selezionare, si chiama cella attiva ed è quella su cui si può scrivere, si può “dare la regola”. Perciò per non sbagliare dopo aver selezionato devo tornare su per guardare la cella attiva, in modo da assicurarmi che la formula sia corretta anche per la spalmatura verso il basso. Formattazione condizionale>Nuova Regola>Utilizza una formula per determinare le celle da formattare A questo punto formatto i valori per cui questa formula restituisce vero con un colore: la logica è la stessa della funzione se. Per una condizione ci sono sempre tre parti: Dove, Come, Quanto. D2 = “Intertax” In automatico lui blocca il d$2$, togliamo il blocco ($). FUNZIONE CONTA.SE, MEDIA.SE, E SOMMA.SE Voglio fare una sintesi di questa tabella e sapere quante aziende usano ciascun tipo di software. Non si fa con trova e sostituisci e conta valori! Perché se cambio l’ordine della tabella lui considera la posizione non i nomi. Devo fare un conteggio dietro condizione.
Voglio sostanzialmente un nuovo foglio, chiamato Calcoli, che contenga nella prima colonna l’elenco dei software, il conteggio azienda per software, somma dei dipendenti per le aziende che utilizzano un determinato software, somma fatturato. RIMUOVI DUPLICATI Copio e incollo la colonna dei software sul nuovo foglio creato. Vado su Dati > Strumenti dati > Rimuovi duplicati. C’è il flag su dati con intestazioni, quindi va bene selezionare tutta la colonna. NUMERO AZIENDE A questo punto uso la funzione =CONTA.SE Uso la funzione =CONTA.SE. Nell’inserimento abbiamo un intervallo e un criterio. Abbiamo detto che per una condizione ci sono sempre tre parti: Dove, Come, Quanto Come e quanto finiscono nel criterio, dove finisce nell’intervallo. Intervallo è quello della condizione; intervallo somma è una volta trovato l’intervallo sommo gli elementi corrispondenti. Gli chiedo di andare a cercare sul foglio DB Aziende nell’intervallo $d$2:$d$112. Attenzione! L’intervallo è da bloccare. Il criterio potrei scriverlo (nella prima riga GSS) ma ciò non ha senso perché voglio portare giù, diconseguenza come criterio userò la casella A2 contenente il nome del software per poter portare giù efficacemente. TOTALE DIPENDENTI Devo fare una somma dietro condizione =SOMMA.SE. In questo caso ho tre argomenti: Intervallo: da DB Aziende prendo l’elenco di tutti i software $d$2:$d$ Criterio: Anche questa volta mi appoggio su A2 per lo stesso motivo e do già un’occhiata ai primi valori per vedere se sto cercando nella colonna giusta. Int.somma: al momento da zero perché non può sommare testi. Seleziono un altro vettore in DB Aziende con le stesse dimensioni, quello del numero dei dipendenti e anche in questo caso blocco l’intervallo.
GRAFICI CON LINEA DI TENDENZA, MISTI E CON DUE ASSI
A volte Excel non capisce gli anni: aggiungerò quindi le etichette dell’asse orizzontale dopo. Aggiungere una linea di tendenza Seleziono il grafico>Progettazione>Aggiungi elemento grafico>Linea di tendenza (in genere si sceglie quella lineare) GRAFICO MISTO A DUE ASSI Il problema di questo grafico è che, pur avendo io selezionato due serie di dati, ne vedo solo una perché una è nell’ordine dei milioni, l’altra delle centinaia. Devo trovare un’altra strada. Seleziono le serie>Inserisci>Tutti i grafici>Tutti i grafici (non quelli consigliati)>Combinati Posso scegliere che tipo di grafici avere e quale usare per l’asse secondario. Rimane il problema delle etichette degli anni e in questo caso faccio lo stesso procedimento visto prima, MA sto attenda a modificare le etichette DUE VOLTE, in questo caso sia per i dipendenti che per il fatturato.
Lezione 5 - Analisi di simulazione: risoluzione problemi finanziamento e di investimento Le funzioni finanziarie in Excel servono per eseguire dei calcoli tipici della matematica finanziaria e della finanza aziendale. Nel parziale ci sono solo RATA e VAL.FUT, nel generale si possono trovare tutte e 7 o 8. Valutazione Noi usiamo l’ammortamento alla francese. Fisso una rata cui dentro stanno una quota di capitale e una quota di interessi. Se il tasso è fisso, la rata sarà sempre uguale, il tasso variabile (tipologia standard), in cui il tasso si aggancia a un determinato valore di mercato. Se io pongo un “Cap”, cioè un limite, continuo a pagare ma nel momento in cui sforo la parte che sta sopra il cap me la paga un’assicurazione. Dobbiamo quindi decidere tra le due tipologie cosa ci piace di più. Capire quanto pago La funzione che calcola la rata si chiama =RATA. Ha 5 argomenti: Il tasso, se non c’è scritto nulla, si intende annuale. Il valore attuale è la somma del mutuo o del prestito, non c’entre niente con la def che da lui. Il valore futuro si usa per il leasing, non ci interessa. Se la rata è mensile e abbiamo tutti i dati in annuale dobbiamo moltiplicare tutto per 12. L’esborso completo è frutto di una moltiplicazione di rata per gli anni per 12.
In questo screenshot io avevo già cambiato B3, infatti è brutto che si veda così. Se io clicco sul nome della Casella in alto a destra posso cambiare il nome. Se io faccio = e scrivo t adesso sotto le funzioni avrò i nomi. Il significato è che quando vado su Gestioni Scenari e voglio modificare il valore mi da non b3 ma tasso_di_interesse. Adesso faccio lo stesso per b10 che chiamo risparmio_rata. A questo punto posso rifare il riepilogo corretto. ATTENZIONE: se io sbaglio il nome, non posso semplicemente riscriverlo perché a quel punto avrei più nomi che caselle. Per modificare o eliminare i nomi vado su Formule>Gestione nomi. Valore futuro La funzione è =VAL.FUT Essendo qui tutto in anni non devo moltiplicare per 12. E se adesso io volessi un valore futuro di 1 milione? C’è solo il metodo ignorante di cambiare il valore dei versamenti, solo che lo facciamo fare a lui. Lo facciamo tramite Dati>Analisi di simulazione>Ricerca obiettivo:
Lezione 6 - Analisi dei dati con il foglio elettronico FUNZIONI DI TESTO Le funzioni di testo servono, rispetto a una stringa, per:
- Estrarre – es. SINISTRA, DESTRA, STRINGA.ESTRAI
- Unire – es. CONCATENA - &
- Formattare – es. MAIUSC, MINUSC, MAIUSC.INIZ
- Informative – es. LUNGHEZZA, RICERCA SINISTRA: tira fuori i caratteri da sinistra - restituisce un determinato numero di caratteri a partire dall’inizio di una stringa di testo, cioè della sua sinistra DESTRA: tira fuori i caratteri da destra - restituisce un determinato numero di caratteri a partire da destra STRINGA.ESTRAI: è una funzione sinistra modificata che mi permette di non iniziare necessariamente dal primo carattere – estrae un numero determinato di caratteri da una stringa di testo a partire da una posizione specifica =STRINGA.ESTRAI(testo; inizio; num_caratt) =SINISTRA(testo; [num_caratt] MAIUSC E MINUSC trasformano tutto in maiuscolo/minuscolo MAIUSC.INIZ: convertono l’iniziale in maiuscola e le altre rimangono minuscole CONCATENA: usare questa e non concat mette insieme pezzi diversi scrivendo a mano o cliccando nelle celle. I contenuti dinamici (quelli prese dalle celle) devono essere lasciati da soli. Unisce due o più stringhe di testo in un’unica stringa di testo. Fino a 255 argomenti. LUNGHEZZA: restituisce il numero di caratteri – compresi gli spazi – della cella =LUNGHEZZA(testo) RICERCA: mi dice a che carattere inizia il testo che sto cercando (ad esempio cerco il carattere spazio “ ”) individua la posizione del primo carattere di una stringa di testo all’interno di un’altra stringa di testo. C’è anche qui un problema: devo escludere lo spazio, quindi aggiungo a num_ - 1 Andando a fare lo stesso passaggio per il cognome, e quindi usando =DESTRA sorge un problema perché ricerca lavora da sinistra, quindi facciamo la differenza. =DESTRA-RICERCA per il cognome CONTA.PIUSE Lo uso quando ho più criteri, le regole sono gli stessi (intervalli bloccati criteri no ecc)
Lezione 7 - Gestione di date e ore con il foglio elettronico Excel conta le date a partire dallo “0/1/1900”. L’unità di misura in Excel sono i giorni. OGGI La funzione oggi non ha argomenti, restituisce la data del giorno corrente. Un'altra funzione senza argomenti è la funzione Pi greco. ADESSO La funzione =adesso restituisce il giorno e l’ora correnti. GIORNO, MESE, ANNO, ORA, MINUTO, SECONDO Supponiamo che, ad esempio per tirare fuori un codice univoco, io abbia bisogno del mese o dell’anno. Se provo a usare destra esce fuori il numero che lui vede. Ho tre funzioni, =GIORNO, =MESE, =ANNO che servono per ricavare giorno mese e anno corrispondente a un determinato seriale. Lo stesso vale per =ORA, =MINUTO, =SECONDO Questo se non va bene! Non confronta date ma divide 31 su 12 su 2021. Per confrontare date abbiamo una regola generale:
- Faccio il calcolo del numero per come lo vede lui e lo inserisco al posto di 31/12/2021, un metodo sbagliato perché se cambia la data di controllo è un problema.
- Uso riferimento di cella: uso la cella di controllo, punto alla cella (bloccata) anziché scrivere la data a mano
- Uso la funzione =DATA (METODO CORRETTO!!) Se devo fare questo come criterio di un somma più se NON POSSO SCRIVERE LA FUNZIONE DATA, SE SCRIVO LA DATA IN MODO IGNORANTE LUI STRANAMENTE CAPISCE. Il criterio con data e con ora nelle funzioni più se e se si scrive come lo intendiamo noi ma tra virgolette. DATA.DIFF Voglio sapere l’età di una persona partendo dalla nascita. La funzione esiste ma è stata tolta dall’elenco. La sintassi quindi è da ricordare a memoria: DATA.DIFF(data_vecchia;data_recente;"unità misura") Per unità di misura si usa l’iniziale in inglese dell’unità di misura che ci serve (ad esempio “y” di years) SE VOGLIO LA DISTANZA IN GIORNI CON I DECIMALI FACCIO UNA SEMPLICE DIFFERENZA, USO DATA.DIFF PER MESI E ANNI LA FUNZIONE =GIORNI MI DA LA DIFFERENZA DEI GIORNI SENZA DECIMALI (anche data.diff lo da senza decimali). SE.ERRORE Se la funzione di una cella va in errore, ci dice cosa scrivere. Questa funzione è importante perché l’errore deve poter essere gestibile. Sapendo prima che può venir fuori l’errore bisogna essere in grado di gestirlo. Per portare giù devo fare A+1 IN A10 (quindi il secondo del mese). Ma questo non basta, perché non tutti i mesi hanno la stessa lunghezza. Aggiungo un se:
A Febbraio potrei ancora avere problemi, aggiungo un se.errore: =GIORNO.SETTIMANA C’è un problema, lui conta a partire dalla domenica. Una volta inserito il numero, lo formatto con formattazione personalizzata (se scrivo ggg mi da MAR, se scrivo gggg MARTEDÌ). Se voglio dare una formattazione condizionale, dove ad esempio voglio evidenziare la domenica, non devo scrivere domenica ma 1 che è il numero del giorno corrispondente. Se voglio che il lunedì sia uno (mi servo da usare nella funzione se) al tipo (seconda voce inserimento giorno.settimana) uso il tipo 2. CALCOLO ORE DI LAVORO Dal momento che la retribuzione è in ore, devo passare dai giorni alle ore, quindi faccio * (Per i secondi 2460*60 – al contrario /) !LA PAUSA PRANZO È GIÀ IN ORE! Esercizio practice da vedere: 5) In una nuova colonna del foglio di lavoro Vendite online con intestazione Efficienza utilizzare una funzione per mostrare, a fianco di ciascuna vendita:
- il testo “Ok domenica” se la vendita è avvenuta di domenica (utilizzare la colonna Data e ora di vendita) e il tempo (espresso come numero di ore) trascorso tra la data e l’ora di vendita e quella di spedizione è stata uguale o inferiore a 50 ore
- il testo “Ok settimana” se la vendita è avvenuta in un altro giorno della settimana e il tempo trascorso tra la data e l’ora di vendita e quella di spedizione è stata uguale o inferiore a 30 ore
- una cella vuota in tutti gli altri casi Formattare le celle del campo Articolo del foglio Vendite online in modo che siano evidenziate automaticamente con un riempimento a piacere le celle che corrispondono a vendite effettuate la mattina tra le 10:00 (comprese) e le 12: (escluse).
intervallo di numeri, ordine è un numero che determina come classificare il numero indicato nell’argomento. Quando assume valore 0 la posizione del numero indicato nell’argomento num viene calcolata in base a un ordine decrescente, quando assume valore 1 crescente MACRO Impostazioni – centro protezione Si usa l’impostazione disabilita tutte le macro con notifica Per salvare la macro: cartella di lavoro con attivazione di macro .xlsm QUESTO DEVE ESSERE IL FORMATO Visualizza – Macro – Registra Macro TASTO DI SCELTA RAPIDA Ad esempio CTRL + MAIUSC + M Noi la salviamo sempre dentro il file (Questa cartella di lavoro) Qualunque cosa io faccia la sta registrando fino a che non dico stop. Prima cosa da fare: cliccare sul nome del foglio perché se lego la macro a un foglio le operazioni saranno sempre legate a un foglio.
