Astratto: Questa è la prima esercitazione di una serie progettata per acquisire familiarità con Excel e le relative caratteristiche di analisi e mash-up dei dati predefinite. In queste esercitazioni verrà creata e ottimizzata una cartella di lavoro di Excel partendo da zero, verrà quindi creato un modello di dati da cui si otterranno accattivanti report interattivi con Power View. Le esercitazioni sono progettate per illustrare le caratteristiche e le funzionalità di Business Intelligence Microsoft in Excel, nelle tabelle pivot, in Power Pivot e in Power View.
In queste esercitazioni viene illustrato come importare ed esplorare i dati in Excel, creare e perfezionare un modello di dati con Power Pivot e creare report interattivi con Power View che è possibile pubblicare, proteggere e condividere.
Questa serie include le esercitazioni seguenti:
- Importare dati in Excel 2016 e creare un modello di dati
- Estendere le relazioni del modello di dati usando Excel, Power Pivot e DAX
- Creare report Power View basati su mappe
- Incorporare dati di Internet e impostare i valori predefiniti per i report di Power View
- Guida di Power Pivot
- Creare report Power View accattivanti - Parte 2
In questa esercitazione si inizierà da una cartella di lavoro di Excel vuota.
Questa esercitazione include le sezioni seguenti:
- Importare dati da un database
- Importare dati da un foglio di calcolo
- Importare dati tramite operazioni di copia e incolla
- Creare una relazione tra i dati importati
- Verifica e test
Al termine dell'esercitazione sarà possibile verificare quanto appreso con un test.
In questa serie di esercitazioni vengono usati dati relativi alle medaglie olimpiche, ai paesi ospiti e a vari eventi sportivi legati alle Olimpiadi. È consigliabile eseguire le esercitazioni nell'ordine in cui sono presentate.
Importare dati da un database
Si inizierà l'esercitazione con una cartella di lavoro vuota. L'obiettivo della sezione è collegarsi a un'origine dati esterna e importarne i dati in Excel per attività di analisi.
Come prima operazione, occorre scaricare alcuni dati da Internet. Tali dati si riferiscono alle medaglie olimpiche e si trovano in un database di Microsoft Access.
Fare clic sui collegamenti seguenti per scaricare i file che verranno usati nella serie di esercitazioni. Scaricare ognuno dei quattro file in un percorso facilmente accessibile, ad esempio Download o Documenti, oppure in una nuova cartella creata:
> Database di Access OlympicMedals.accdb
> OlympicSports.xlsx cartella di lavoro di Excel
> Population.xlsx cartella di lavoro di Excel
> DiscImage_table.xlsx cartella di lavoro di ExcelAprire una cartella di lavoro vuota in Excel.
Fare clic su Recupera dati >> da database > da database di Microsoft Access. La barra multifunzione si adatta dinamicamente in base alla larghezza della cartella di lavoro, quindi i comandi della barra multifunzione potrebbero avere un aspetto leggermente diverso rispetto all'immagine seguente.
Selezionare il file OlympicMedals.accdb scaricato e fare clic su Importa. Viene visualizzata la finestra Strumento di navigazione seguente, in cui sono visualizzate le tabelle presenti nel database. Le tabelle di un database sono simili ai fogli di lavoro o alle tabelle di Excel. Selezionare la casella Seleziona più tabelle e selezionare tutte le tabelle. Quindi fare clic su Carica > in.
Viene visualizzata la finestra Importa dati.
Nota
Notare la casella di controllo nella parte inferiore della finestra che consente di aggiungere questi dati al modello di dati, come illustrato nell'immagine seguente. Un modello di dati viene creato automaticamente quando si importano o si usano due o più tabelle contemporaneamente. Un modello di dati integra le tabelle e consente un'analisi completa tramite tabelle pivot, Power Pivot e Power View. Quando si importano tabelle da un database, le relazioni esistenti tra tali tabelle vengono usate per creare il modello di dati in Excel. Il modello di dati è trasparente in Excel, ma è possibile visualizzarlo e modificarlo direttamente usando il componente aggiuntivo Power Pivot. Il modello di dati viene discusso in modo più dettagliato più avanti in questo tutorial.
Selezionare l'opzione Rapporto di tabella pivot , che importa le tabelle in Excel e prepara una tabella pivot per l'analisi delle tabelle importate, quindi fare clic su OK.
Al termine dell'importazione dei dati, viene creata una tabella pivot con le tabelle importate.
Un volta importati i dati in Excel e creato automaticamente il modello di dati, è possibile passare all'esplorazione dei dati.
Esplorare i dati mediante una tabella pivot
È facile esplorare i dati importati usando una tabella pivot. In una tabella pivot è possibile trascinare i campi (simili alle colonne di Excel) delle tabelle (come quelle importate dal database di Access) in aree diverse della tabella pivot per modificare la presentazione dei dati. Una tabella pivot include quattro aree: FILTRI, COLONNE, RIGHE e VALORI.
Per determinare l'area in cui trascinare un campo, potrebbero essere necessarie alcune prove. È possibile trascinare tutti i campi desiderati dalle tabelle finché la tabella pivot non presenta i dati nel modo desiderato. È possibile esplorare trascinando i campi in aree diverse della tabella pivot. i dati sottostanti non sono interessati dalla disposizione dei campi in una tabella pivot.
Passando ora ai dati relativi alle medaglie olimpiche nella tabella pivot, si inizierà dai vincitori organizzati per disciplina, tipo di medaglia e paese di provenienza dell'atleta.
In Campi tabella pivot espandere la tabella Medals facendo clic sulla freccia accanto al nome. Individuare il campo NOC_CountryRegion nella tabella Medals espansa e trascinarlo nell'area COLONNE. NOC significa National Olympic Committees, l'unità organizzativa per un paese o area geografica.
Quindi, dalla tabella Disciplines trascinare Discipline nell'area RIGHE.
Filtrare ora Disciplines in modo da visualizzare solo cinque sport: Archery, Diving, Fencing, Figure Skating e Speed Skating. È possibile eseguire questa operazione in Campi tabella pivot oppure con il filtro Etichette di riga all'interno della tabella pivot stessa.
- Fare clic in un punto qualsiasi della tabella pivot per assicurarsi che la tabella pivot di Excel sia selezionata. Nell'elenco Campi tabella pivot , in cui la tabella Disciplines è espansa, passare il puntatore del mouse sul relativo campo Discipline per visualizzare una freccia a discesa a destra del campo. Fai clic sull'elenco a discesa, fai clic su **(Seleziona tutto)**per rimuovere tutte le selezioni, quindi scorri verso il basso e seleziona Archery, Diving, Fencing, Figure Skating e Speed Skating. Fare clic su OK.
- In alternativa, nella sezione Etichette di riga della tabella pivot, fare clic sull'elenco a discesa vicino a Etichette di riga nella tabella pivot, fare clic su (Seleziona tutto) per rimuovere tutte le selezioni, quindi scorrere l'elenco e selezionare Archery, Diving, Fencing, Figure Skating e Speed Skating. Fare clic su OK.
In Campi tabella pivot, passare alla tabella Medals e trascinare Medal nell'area VALORI. Poiché l'area Valori deve contenere valori numerici, Excel modifica automaticamente Medal in Conteggio di Medal.
Nella tabella Medals selezionare nuovamente Medal e trascinarlo nell'area FILTRI.
È ora possibile filtrare la tabella pivot in modo da visualizzare solo i paesi o le aree geografiche con un numero totale di medaglie superiore a 90. Ecco come.
- Nella tabella pivot fare clic sulla freccia a destra di Etichette di colonna.
- Selezionare Filtri per valori e quindi Maggiore di.
- Digitare 90 nell'ultimo campo a destra. Fare clic su OK.
L'aspetto della tabella pivot sarà simile alla schermata seguente.
Con alcuni semplici passaggi è ora disponibile una tabella pivot di base in cui sono inclusi campi di tre tabelle diverse. Ciò che ha reso questa attività così semplice sono state le relazioni pre-esistenti tra le tabelle. Poiché esistevano relazioni tra le tabelle nel database di origine e tutte le tabelle sono state importante con una sola operazione, Excel è stato in grado di ricreare tali relazioni nel modello di dati.
Ma cosa accade se i dati provengono da varie origini o vengono importati in un momento successivo? In genere si possono creare relazioni con i nuovi dati in base alle colonne corrispondenti. Nel passaggio successivo si importeranno tabelle aggiuntive e si scoprirà come creare nuove relazioni.
Importare dati da un foglio di calcolo
Si importeranno ora alcuni dati di un'altra origine, una cartella di lavoro esistente, quindi si specificheranno le relazioni tra i dati esistenti e quelli nuovi. Le relazioni consentono di analizzare le raccolte di dati in Excel e di creare visualizzazioni interessanti e coinvolgenti dai dati importati.
Si inizierà creando un foglio di lavoro vuoto, quindi si importeranno i dati di una cartella di lavoro di Excel.
Inserire un nuovo foglio di lavoro di Excel e denominarlo Sports.
Passare alla cartella contenente i file di dati di esempio scaricati e aprire OlympicSports.xlsx.
Selezionare e copiare i dati in Foglio1. Se si seleziona una cella contenente dati, ad esempio la cella A1, è possibile premere CTRL + A per selezionare tutti i dati adiacenti. Chiudere la cartella di lavoro OlympicSports.xlsx.
Nel foglio di lavoro Sports posizionare il cursore nella cella A1 e incollare i dati.
Con i dati ancora evidenziati, premere CTRL + T per formattare i dati come tabella. È anche possibile formattare i dati come tabella dalla barra multifunzione selezionando > Home Formatta come tabella. Poiché i dati includono intestazioni, selezionare Tabella con intestazioni nella finestra Crea tabella visualizzata, come illustrato qui.
La formattazione dei dati come tabella ha molti vantaggi. È possibile assegnare un nome a una tabella per semplificarne l'identificazione. È anche possibile stabilire relazioni tra tabelle, abilitando l'esplorazione e l'analisi in tabelle pivot, Power Pivot e Power View.
Denominare la tabella. In PROPRIETÀ STRUTTURA > TABELLA individuare il campo Nome tabella e digitare Sports. La cartella di lavoro ha l'aspetto dell'immagine seguente.
Salvare la cartella di lavoro.
Importare dati tramite operazioni di copia e incolla
Dopo avere importato i dati da una cartella di lavoro di Excel, si importeranno ora dati da una tabella disponibile in una pagina Web e in un'altra origine da cui è possibile copiare e incollare i dati in Excel. Nei passaggi seguenti si aggiungeranno le città ospiti dei Giochi olimpici provenienti da una tabella.
- Inserire un nuovo foglio di lavoro di Excel e denominarlo Hosts.
- Selezionare e copiare la tabella seguente, incluse le intestazioni di colonna.
- Nel foglio di lavoro Hosts posizionare il cursore nella cella A1 e incollare i dati.
- Formattare i dati come tabella. Come descritto in precedenza in questa esercitazione, premere CTRL+T per formattare i dati come tabella o dal formato HOME > come tabella. Poiché i dati includono intestazioni, selezionare Tabella con intestazioni nella finestra Crea tabella visualizzata.
- Denominare la tabella. In PROPRIETÀ STRUTTURA > TABELLA individuare il campo Nome tabella e digitare Hosts.
- Selezionare la colonna Edition e nella scheda HOME formattarla come Numero con 0 posizioni decimali.
- Salvare la cartella di lavoro. L'aspetto della cartella di lavoro sarà quello illustrato nell'immagine seguente.
Ora che si dispone di una cartella di lavoro di Excel con tabelle, si possono creare relazioni tra di esse. La creazione di relazioni tra tabelle consente di raccogliere i dati provenienti da due tabelle.
Creare una relazione tra i dati importati
È possibile iniziare immediatamente a usare i campi della tabella pivot provenienti dalle tabelle importate. Se Excel non è in grado di determinare come incorporare un campo nella tabella pivot, occorre stabilire una relazione con il modello di dati esistente. Nei passaggi seguenti si apprenderà come creare una relazione tra i dati importati da origini diverse.
Nel Foglio1, nella parte superiore deicampi della tabella pivot, fare clic suTutti per visualizzare l'elenco completo delle tabelle disponibili, come illustrato nell'immagine seguente.
Scorrere l'elenco per vedere le nuove tabelle appena aggiunte.
Espandere Sports e selezionare Sport per aggiungere la colonna alla tabella pivot. Viene chiesto di creare una relazione, come mostrato nella schermata seguente.
Questa notifica viene visualizzata perché sono stati usati campi di una tabella che non fa parte del modello di dati sottostante. Per aggiungere una tabella a un modello di dati è possibile creare una relazione a una tabella già presente nel modello di dati. Per creare la relazione, una delle tabelle deve contenere una colonna con valori univoci non ripetuti. Nei dati di esempio, la tabella Disciplines importata dal database contiene un campo con i codici sport, denominato SportID. Gli stessi codici sono presenti come campo dei dati di Excel importati. A questo punto è possibile creare la relazione.Fare clic su CREA nell'area evidenziata della finestra Campi tabella pivot per aprire la finestra di dialogo Crea relazioni, come illustrato nella schermata seguente.
In Tabella scegliere Tabella modello di dati: Disciplines nell'elenco a discesa.
In Colonna (esterna) scegliere SportID.
In Tabella correlata scegliere Tabella modello di dati: Sports.
In Colonna correlata (principale) scegliere SportID.
Fare clic su OK.
La tabella pivot viene modificata per riflettere la nuova relazione. L'aspetto della tabella pivot non è però ancora ottimale, perché i campi nell'area RIGHE non sono ordinati in modo corretto. Discipline è una sottocategoria di un determinato sport, ma poiché Discipline è stato inserito sopra a Sport nell'area RIGHE, l'organizzazione non è corretta. Nella schermata seguente è illustrato l'ordinamento errato.
Nell'area RIGHE spostare Sport sopra a Discipline. Ora l'aspetto è decisamente migliore e la tabella pivot rappresenta i dati come desiderato, come mostra la schermata seguente.
Excel crea automaticamente un modello di dati che può essere usato nell'intera cartella di lavoro, in qualsiasi tabella pivot, grafico pivot, power pivot o report Power View. Le relazioni tra le tabelle sono alla base di un modello di dati e determinano i percorsi di esplorazione e di calcolo.
Nell'esercitazione successiva, Estendi relazioni del modello di dati usando Excel, Power Pivot** e DAX**, si basa su quanto appreso qui e si estenso il modello di dati usando un componente aggiuntivo di Excel potente e visivo denominato Power Pivot. Viene anche illustrato come calcolare le colonne in una tabella e usare tale colonna calcolata in modo da poter aggiungere una tabella altrimenti non correlata al modello di dati.
Verifica e test
Verificare le informazioni acquisite
Si dispone ora di una cartella di lavoro di Excel con una tabella pivot che accede ai dati di più tabelle, alcune delle quali sono state importate separatamente. Si è appreso come importare dati da un database, da un'altra cartella di lavoro di Excel e copiandoli e incollandoli in Excel.
Per combinare questi dati, è stato necessario creare relazioni tra tabelle utilizzate da Excel per collegare le righe. Si è anche appreso che la presenza di colonne in una tabella che siano correlate ai dati di un'altra tabella è essenziale per la creazione di relazioni e per la ricerca delle righe correlate.
A questo punto, è possibile passare all'esercitazione successiva della serie. Ecco il collegamento:
Esercitazione: Estendere le relazioni del modello di dati mediante Excel, Power Pivot e DAX
TEST
Vuoi vedere quanto bene ricordi quello che hai imparato? Ecco la tua occasione. Il test seguente evidenzia caratteristiche, funzionalità o requisiti appresi in questa esercitazione. Nella parte inferiore della pagina sono disponibili le risposte. Buona fortuna!
Domanda 1: Perché è importante convertire i dati importati in tabelle?
A: Non è necessario convertirli in tabelle perché tutti i dati importati vengono automaticamente convertiti in tabelle.
B: Se si convertono di dati importati in tabelle, questi verranno esclusi dal modello di dati. Solo se sono escluse dal modello di dati, sono disponibili nelle tabelle pivot, in Power Pivot e in Power View.
C: Se si convertono i dati importati in tabelle, possono essere inclusi nel modello di dati e resi disponibili per le tabelle pivot, Power Pivot e Power View.
D: Non è possibile convertire i dati importati in tabelle.
Domanda 2: Quale delle seguenti fonti di dati è possibile importare in Excel e includere nel modello di dati?
A: Database di Access e molti altri database.
B: File di Excel esistenti.
C: Qualsiasi dato che si possa copiare e incollare in Excel e formattare come tabella, incluse le tabelle di dati dei siti Web, i documenti o tutto ciò che può essere incollato in Excel.
D: Tutte le risposte sopra
Domanda 3: In una tabella pivot che cosa accade quando si ordinano i campi nelle quattro aree Campi tabella pivot?
A: Nulla. Non è possibile ordinare i campi dopo averli inseriti nelle aree Campi tabella pivot.
B: Il formato della tabella pivot cambia per riflettere il layout, ma i dati sottostanti rimangono invariati.
C: Il formato della tabella pivot cambia per riflettere il layout e i dati sottostanti vengono modificati definitivamente.
D: I dati sottostanti vengono modificati generando nuovi set di dati.
Domanda 4: Quando si crea una relazione tra tabelle, che cosa serve?
A: Nessuna delle due colonne può avere colonne contenenti valori univoci e non ripetuti.
B: Una tabella non deve fare parte della cartella di lavoro di Excel.
C: Le colonne non devono essere convertite in tabelle.
D: Nessuna delle risposte precedenti.
Risposte del test
- Risposta corretta: C
- Risposta corretta: D
- Risposta corretta: B
- Risposta corretta: D
Nota
I dati e le immagini di questa serie di esercitazioni si basano sulle fonti seguenti:
- Set di dati sulle Olimpiadi fornito da Guardian News & Media Ltd.
- Immagini di bandiere fornite da CIA Factbook (cia.gov)
- Dati sulla popolazione forniti da The World Bank (worldbank.org)
- Pittogrammi relativi agli sport olimpici forniti da Thadius856 e Parutakupiu