Corso Avanzato Excel (2)
-
Upload
sebastian-bujor -
Category
Documents
-
view
239 -
download
4
description
Transcript of Corso Avanzato Excel (2)
Corso Avanzato di Excel
Corso Avanzato di Excel11.Creazione ed Utilizzo delle Macro12.La Formattazione condizionale (Conditional Formating)32.1 La Formattazione condizionale con regole evidenziazione celle42.2 La Formattazione condizionale con set di icone53.1 Manipolazioni del testo73.2 Funzioni logiche83.3 Funzione Cerca.vert VLOOKUP (VERTICAL LOOKUP )93.Creazione e utilizzo Tabelle pivot114.Creazione e utilizzo grafici15
1. Creazione ed Utilizzo delle Macro
Le macro vengono utilizzate per automatizzare operazioni ripetitive effettuate su uno specifico foglio di lavoro.
(ad esempio la cancellazione di determinate info sullelenco di pratiche da consegnare agli operatori).
Per la creazione e lutilizzo delle Macro viene utilizzato il menu Sviluppo -Developer
E importante specificare che la macro ripete le operazioni in funzione della posizione specifica delle informazioni e non fa ragionamenti sul suo contenuto. Se ad esempio si indica di eliminare la colonna B (che nel file contiene il nome del debitore) questa operazione verr effettuata anche se la colonna B contiene dati diversi da quelli desiderati.
Per creare una nuova macro sufficiente cliccare sul tasto registra macro Dopo aver cliccato sul tasto si aprir la maschera riportata a lato dalla quale possibile:
Assegnare un nome alla macro
Assegnare dei tasti di scelta rapida per lesecuzione della macro (attenzione non utilizzare i tasti di scelta rapida comuni! Se ad esempio si assegna CRTL+C quando si preme questa combinazione, invece di copiare, verr lanciata la macro.
Decidere dove sar disponibile la macro
Cartella macro personale: la macro sar disponibile su tutti i fogli excel aperti dal pc che ha creato la macro. Attenzione se si utilizza questa modalit, quando si chiude excel il programma segnala che stata apportata una modifica al foglio PERSONAL, importante salvare la modifica altrimenti la macro viene persa
Questa cartella di lavoro: la macro sar disponibile solo sul file che si sta utilizzando
E quindi importante per un corretto utilizzo della funzione che lelenco di partenza abbia sempre la stessa struttura.
Esercizio ID1
Da questo momento il sistema registrer tutti i movimenti che effettuate sul file quindi importante, prima di avviare la registrazione , avere le idee chiare su cosa si deve fare.
Per interrompere la registrazione cliccare sullo stesso tasto utilizzato per avviarla che ora si presenter cosi Per lanciare una macro gi creata si utilizza il tasto macro presente nel menu, oppure i tasti di scelta rapida assegnati nel momento della creazione.2. La Formattazione condizionale (Conditional Formating)
La formattazione condizionale accessibile dal men Home
Questa funzione, come dice la parola stessa, permette di formattare determinate celle in funzione del verificarsi di una determinata condizione.
Nellesempio riportato a lato, si riporta lutilizzo di una formula dove viene utilizzata la funzione VERO/FALSO su cui si basa la formattazione condizionale.
Questa funzione restituisce il valore VERO se il confronto richiesto viene soddisfatto.
In questo esempio si chiesto di verificare se nella colonna F ci sono valori che superano i 100 euro.
Viene usato in genere per avere un impatto visivo su dati statistici (celle di colore diverso se si raggiunge o meno lobiettivo)Esercizio ID2
Nella formattazione condizionale, verr attivata la formattazione richiesta se risulta VERO il confronto effettuato.
Cliccando su Formattazione condizionale possibile scegliere la funzione desiderata:
Regole evidenziazione Celle viene in genere utilizzato se si desidera formattare i campi in funzione 1-2 valori massimo
Set di icone si utilizza invece se si desidera formattare i campi in funzione di 3-4 valori
In generale la formattazione si imposta sulla prima cella da trattare e si riporta sulle successive tramite la funzione Incolla formato
2.1 La Formattazione condizionale con regole evidenziazione celle
Una volta selezionato il tipo di confronto da effettuare, si potr impostare il valore di riferimento e la formattazione da applicare se la condizione VERA
Nel campo in cui si sta creando la formattazione condizionale possibile durante la creazione della regola visualizzare unanteprima della formattazione che verr visualizzata.
Come abbiamo gi detto possibile confrontare pi valori e definire una formattazione per ognuno di questi.
Sulla stessa cella ad esempio potremmo definire un riempimento giallo se il valore inferiore a 100
Loperazione andr quindi ripetuta per ogni tipo di formattazione che si desidera visualizzare e, solo dopo aver terminato si proceder ad incollare il formato sulle celle successive
Per visualizzare o modificare le regole impostate:
dal menu Formattazione condizionale selezionare gestisci regole
selezionare il criterio desiderato e cliccare su modifica regola per modificare i dati o il formato impostato in precedenza
2.2 La Formattazione condizionale con set di icone
Cliccando su set di icone vengono visualizzate le icone disponibili.
Il numero di icone disponibili per tipologia corrisponde al numero di criteri confrontabili.
Nei primi set di icone sono quindi confrontabili tre criteri, negli ultimi 5 criteri.
In genere vengono utilizzati i primi tre set di icone che ricordando un semaforo sono molto intuitive per chi legge i dati
Per impostare la formattazione condizionale:
Posizionarsi sulla prima cella da trattare e selezionare quindi il set di icone da utilizzare tramite la funzione formattazione condizionale
Sempre dal men formattazione condizionale selezionare gestisci regole
Nella schermata che verr mostrata verr richiesto:
Il valore dal quale va utilizzata licona
Il tipo di campo trattato (numero o percentuale)
Limpostazione di questi valori restituir il seguente risultato
Entrare quindi nella sezione modifica della riga visualizzata per impostare i valori da assegnare ad ogni icona
Esercizio ID3
Dal men home possibile accedere velocemente alle principali funzioni (somma/media/ ecc) oppure accedere alle sezione altre funzioni per utilizzare operazioni diverse.
Per utilizzare una delle funzioni standard visualizzate:
Posizionarsi su una cella vuota dove si intende creare la formula
Selezionare la funzione desiderata dallelenco (somma, media, ecc)
Selezionare i campi da considerare nelloperazione e premere invio
Principali funzioni di utilizzo comune
Per la creazione di formule pi complesse (accessibili dalla sezione Altre funzioni) la procedura simile.
Si riportano di seguito le funzioni pi comuni utilizzate dalla sezione Altre funzioni
3.1 Manipolazioni del testo
Le seguenti funzioni permettono di estrarre parte di un campo oppure unire in un solo campo il contenuto di pi celle
Funzione DESTRA oppure SINISTRA ( RIGHT LEFT )
Riporta allinterno della cella desiderata il numero di caratteri alla destra (oppure a sinistra del campo desiderato.
Sezione Testo: selezionare la cella da analizzare
Sezione Num_caratt: indicare il numero di caratteri da riportare
Funzione STRINGA.ESTRAI (MID)
Riporta allinterno della cella desiderata parte del contenuto di unaltra cella (che non alla destra o alla sinistra)
Sezione Testo: selezionare la cella da analizzare
Inizio: indicare da che punto dei caratteri presenti cominciare a prendere
Sezione Num_caratt: indicare il numero di caratteri da prendere
Funzione CONCATENA (CONCATENATE)
Riporta allinterno della cella desiderata le informazioni contenute in pi celle oltre al testo inserito dallutente
In questo esempio, tramite il contenuto delle celle CONTRATTO E IMPORTO EURO si genera una nota che pu ad esempio essere riportata sui nostri sistemi
Per riportare un testo: sufficiente inserire nella cella il testo desiderato
Per riportare linformazione contenuta in una cella, si procede a selezionare la stessa
Esercizio ID4
Funzione MAIUSC oppure MAIUSC.INIZ UPPER Sau PROPER
Riporta allinterno della cella desiderata le informazioni in maiuscolo oppure con la sola iniziale maiuscola
3.2 Funzioni logiche
Funzione SE ho IF
Questa funzione sfrutta il principio di VERO e FALSO gi affrontato nella formattazione condizionale. Permette quindi di visualizzare un determinato testo a seconda dellesito del confronto.
Test: inserire il confronto da effettuare
Inserire il testo o linformazione da visualizzare se il confronto ha esito VERO TRUE
Inserire il testo o linformazione da visualizzare se il confronto ha esito FALSO
Esercizio ID5
Funzione CONTA.SE E CONTA.VUOTE COUNTIF or COUNTBLANK
Queste funzioni permettondo di avere di contare le sole informazioni che soddisfano il criterio. Vengono in genere utilizzate sui fogli presenze come nellesempio allegato.
Per il contaSE:
Intervallo: selezionare le celle da analizzare
Criteri: indicare il valore da considerare nel conteggio
Questa funzione viene utilizzata per ricavare i primi due conteggi (ferie e medicale)
Per ricavare gli operatori presenti si utilizza invece la funzione CONTA.VUOTE selezionando lintervallo da analizzare
3.3 Funzione Cerca.vert VLOOKUP (VERTICAL LOOKUP )
Questa funzione permette di paragonare i dati di due elenchi (anche su due sheet diversi) e cercare quali valori sono contenuti in entrambi.
La funzione pu essere utilizzata esclusivamente se entrambi gli elenchi contengono un codice comune con cui effettuare il confronto (es. cod contratto)
Gli elenchi devono essere presenti sullo stesso file (anche su fogli diversi)
Nellesempio sotto riportato si vuole verificare quali pratiche dellelenco 1 (tutte le pratiche in gestione) sono presenti nellelenco 2 (pratiche con incasso)
Valore: il valore della cella del primo elenco (numero, testo, ) che si vuole cercare nel secondo elenco
Matrice_tabella: la colonna del secondo elenco in cui si vuole cercare il valore selezionato
Indice: sempre 1
Intervallo: sempre FALSO per trovare una corrispondenza esatta
La formula restituisce lo stesso valore cercato se presente; altrimenti restituisce #N/D
Una volta creata la formula dovra essere riportata su tutte le righe da verificare.
Esercizio ID6
3. Creazione e utilizzo Tabelle pivot
La tabella pivot uno strumento per visualizzare un elenco di dati riepilogati in base a specifiche funzioni.
Questo vuol dire che vengono sommati, contati, moltiplicati... in base alle nostre esigenze.
I dati visualizzati dalle tabelle pivot possono avere origini diverse:
1. Dati provenienti dai db aziendali vengono create esclusivamente dai programmatori
2. Dati presenti in un foglio excel possono essere generate in autonomia
Per creare una tabella pivot:
Selezionare le colonne da trattare
Selezionare tabella pivot dal menu inserisci
Verr mostrata in automatico la pivot vuota e lelenco dei campi che possibile trattare:
Filtro rapporto(rapporto filtro): inserire i campi su cui prevedete di impostare dei filtri (ad esempio cod. sottogruppo per visualizzare solo la propria piattaforma)
Etichetta di colonna e di riga(row labels): inserire i campi che si vuole visualizzare sulle colonne e sulle righe
Questi dati potranno essere successivamente spostati finche non si raggiunge la visualizzazione desiderata
Valori (values): inserire i campi che si desidera effettuare il calcolo ( possibile utilizzare somma/conteggio/min/max/media)
Per impostare i campi nelle aree della tabella pivot sufficiente trascinarli nellapposita sezione desiderata.
Esercizio ID7
A2
A1
Impostazione dei campi valori
Una volta trascinato il campo nella sezione valori, sar necessario impostare il tipo di calcolo desiderato ed il formato del campo (se necessario)
Cliccare sul campo e scegliere quindi impostazioni campo valore
Si aprir un apposita schermata (A1) dove sar possibile:
Il tipo di calcolo da effettuare (somma, media, ecc..)
Il tipo di formato del campo (numerico, valuta, ecc). Il formato pu essere variato anche direttamente dal foglio excel ma ad ogni aggiornamento della pivot lo stesso verr perso.
Decidere se visualizzare il campo come una percentuale sul totale delle righe o delle colonne (ad esempio se si desidera calcolare il peso del valore sul totale)
A1
Oltre ad utilizzare i campi esistenti possibile crearne dei nuovi tramite la funzione Inserisci (quindi inserisci campo calcolato) presente nel men home. Ad esempio se si dispone dellimporto affidato e di quello recuperato possibile creare la % di positivit
E inoltre possibile raggruppare le informazioni visualizzate su righe e colonne in funzione di esigenze specifiche.Per raggruppare il dato si selezionano le righe o le colonne da raggruppare, quindi dal menu del tasto destro si sceglie raggruppa. Per tornare alla situazione iniziale sufficiente scegliere separa.
Una volta effettuata questa operazione sar possibile assegnare un nome al gruppo appena creato.
Nellesempio sotto riportato, lelenco degli operatori stato suddiviso in due gruppi : best performer e operatori medi.
Esercizio ID8
4. Creazione e utilizzo grafici
I grafici sono strumenti utili a rappresentare il dato in modo visivo e quindi di veloce impatto per chi legge (soprattutto se non una persona operativa o del settore)
Per la creazione di grafici su dati che sono tutti presenti in un elenco possibile selezionare i dati da trattare e quindi selezionare i l grafico che si desidera inserire dal menu Inserisci
Il primo grafico stato creato selezionando i dati presenti nelle righe 2 e 3 e cliccando quindi sul grafico desideratoNel secondo grafico i dati non sono continui stata fatta quindi una prima selezione dei dati nella colonna A, lasciando premuto il tasto CTRL si quindi selezionato i dati presenti nella colonna X. Successivamente si scelto il grafico desiderato
Quando utilizzare le varie tipologie di grafico:Grafice cu 2 serii de date de la stanga la dreapta ( linear si cu bare )
Grafico Linee or line
Utilizzato quando i dati da rappresentare sono molti e di varie tipologie
Grafico a Torta or Pie
Utilizzato per rappresentare lincidenza dei dati su un totale. Ad esempio la percentuale di stati su un affidamento
Grafico Istogramma (2 maxim 3 serii)
Utilizzato quando i dati da rappresentare sono contenuti e di varie tipologie
Una volta creato il grafico possiamo utilizzare questi due men per personalizzare lo stesso
Menu progettazione: permette di cambiare i ltipo di grafico; modificarne il layout o i colori secondo stili preimpostati
Menu Formato, permette di modificare i colori a nostro piacere
Per modificare o aggiungere dati al grafico, necessario posizionarsi sul grafico quindi cliccare su seleziona dati dal menu visibile dal tasto destro del mouse
Il tasto modifica su etichette asse orizzontale: permette di modificare i dati da visualizzare nella riga in basso;
Il tasto modifica su voci legenda: permette di:
modificare il nome della serie
modificare la selezione dei dati visualizzati
E inoltre possibile tramite il tasto aggiungi inserire una nuova serie di dati
Esercizio ID8
Cliccando sui vari elementi che compongono il grafico ed accedendo al menu disponibile con il tasto destro del mouse, si ha la possibilit di effettuare ulteriori personalizzazioni:
Dai valori: aggiungere etichette dati o una linea di tendenza
Dallasse verticale: modificare il formato e i valori minimi e massimi
Mailings
Gineta si monica
35