Database Excel: creazione di tabelle relazionali

Excel possiede poteri formidabili per il database. La creazione di un database relazionale inizia con una tabella Master che la collega a subordinati, chiamate tabelle (stranamente) Slave, Child o Detail.

Prima di immergerti, ti abbiamo fornito una cartella di lavoro esemplificativa che puoi utilizzare per seguire le nostre procedure e esercitarti in ogni fase. Basta fare clic sulla freccia o sul link qui sotto per avviare il download.

scarica la cartella di lavoro per database relazionali, report e tabelle pivot. Si tratta di una cartella di lavoro con più fogli di lavoro il cui contenuto può essere utilizzato per esercitarsi nelle attività di Excel in database relazionali, report e tabelle pivot. JD Sartain

Come funzionano i database relazionali

La struttura organizzativa di Excel si presta bene al funzionamento dei database. Un database è una raccolta di elementi collegati (foglio di calcolo) che, quando associati, crea un singolo record (riga) all'interno di un gruppo di più record (tabella).

[Ulteriori letture: il tuo nuovo PC ha bisogno di questi 15 programmi gratuiti, eccellenti]

Un foglio di calcolo, da solo, è un database, ma non uno relazionale. Il database relazionale è una combinazione della tabella del foglio di lavoro principale e di tutte le sue tabelle o fogli di calcolo slave.

Microsoft Office Home and Business 2016 Vedi su Amazon

Ad esempio, la tua patente di guida è un singolo record in un appartamento -file database che risiede su un computer presso il Department of Motor Vehicles (DMV). La tua licenza contiene il tuo nome, sesso, altezza, peso, colore degli occhi e dei capelli, indirizzo, data di nascita, numero di licenza e data di scadenza, classe di licenza.

Perché è possibile avere più persone con lo stesso nome, indirizzo, genere e descrizione (come un padre e un figlio), le patenti di guida hanno tutte numeri di licenza. Questo numero univoco è chiamato Key Field, che viene utilizzato per connettere un database flat-file ad altri database associati (chiamati database relazionali).

JD Sartain / PC World

Un database di fogli di calcolo di file flat di base

Questo database master di base (in questo esempio) contiene tutte le informazioni contenute nella tua patente di guida. Uno o più database correlati, denominati database Slave, Child o Detail, contengono informazioni aggiuntive correlate ai singoli driver (identificati dal numero di licenza del campo chiave univoco). Ad esempio, un database Slave potrebbe contenere tutte le violazioni di guida individuali, mentre un altro potrebbe contenere tutti gli indirizzi precedenti dei singoli driver. Questo tipo di database relazionale viene chiamato una relazione uno-a-molti, il che significa che ogni driver può avere più violazioni o più indirizzi, ma i database delle violazioni e / o degli indirizzi non possono avere più driver.

Altri tipi di relazioni includono One- to-one e molti-a-molti. In un database di vendita in cui i clienti acquistano prodotti, alcuni clienti potrebbero ricevere speciali tassi di sconto (come vendita all'ingrosso, vendita al dettaglio, esenzione fiscale, ecc.), Che richiederebbero un database di tassi di sconto. Sarebbe una relazione uno-a-uno con il database dei clienti. Quando un cliente può acquistare molti prodotti e lo stesso prodotto può essere acquistato da molti clienti, si ha una relazione molti-a-molti.

JD Sartain / PC World

Master e slave di database relazionali

Chi ha bisogno di un database relazionale

Ci sono molti motivi per creare database relazionali. La cosa più ovvia è che non ripeti gli stessi dati su tutti i fogli di lavoro della tua cartella di lavoro. Ad esempio, è ridondante, richiede molto tempo e richiede molte risorse per ridigitare tutti i nomi dei driver e le date di nascita dal database master ai più database slave. E anche se copi le informazioni da una all'altra, utilizza ancora spazio su disco e memoria inutili, il che rallenta tutto. Inoltre, il nome e la data di nascita non sono davvero unici. Ad esempio, ci sono probabilmente centinaia di Jack Taylors e John Smiths, e alcuni di questi potrebbero facilmente avere la stessa data di nascita. Ma se usi semplicemente il numero di licenza, non puoi identificare i driver, quindi sei obbligato (per sicurezza) a utilizzare tutti e tre i campi (almeno).

La ragione più importante per i database relazionali consiste nel creare query e filtri in grado di estrarre informazioni specifiche e stampare rapporti. Supponiamo che il tuo capo desideri un rapporto che mostri tutti gli abitanti della Contea di Santa Rosa, in Florida, con multe stradali superiori a $ 300; o quanti piloti a Pensacola hanno date di rinnovo della licenza a dicembre? Senza la capacità del database relazionale, dovresti tagliare e incollare queste informazioni insieme da tre o più fogli di calcolo su un quarto foglio di lavoro, quindi sperare - quando hai finito - il tuo capo non annuncia ", ho detto Sarasota County, non Contea di Santa Rosa. "

JD Sartain / PC World

Report di database relazionali - multe per il traffico

Ulteriore formazione di Excel

Se vuoi approfondire la padronanza di Excel, esistono numerosi corsi online per espandere le tue conoscenze . Ecco le nostre prime scelte per iniziare:

Coursera - Excel Skills for Business: Essentials

EDX - Analisi e visualizzazione dei dati con Excel

Lynda.com - Excel: Scenario Pianificazione e analisi

Udacity - Fondamenti di Excel

Creazione di un database relazionale

Creiamo un database relazionale con diverse tabelle o fogli di calcolo separati.

Creazione della tabella master

1. Utilizza gli stessi campi mostrati nella prima schermata sopra ("Un database di fogli di calcolo di file flat di base"), quindi compila 12-15 record con i tuoi dati (o usa il nostro). Tali campi sono: Numero di licenza, Nome, Indirizzo, Città, Stato, Zip, Data di nascita, Data di scadenza e Sesso.

2. Al termine, evidenzia l'intera tabella incluse le intestazioni; quello è A1 attraverso I15 (A1: I15). Ricorda che CTRL + A evidenzia l'intera tabella in un colpo solo, a condizione che il cursore sia posizionato da qualche parte / dovunque all'interno dell'intervallo della tabella.

3. Selezionare la scheda e il pulsante: Inserisci > Tabella

4. Se l'intervallo è evidenziato (quale dovrebbe essere), l'indirizzo di intervallo appare nel campo chiamato: Dove sono i dati per la tabella nella finestra di dialogo Crea tabella . Seleziona la casella , quindi fai clic su OK . JD Sartain / PC World

Crea / Inserisci la tabella master

5. Non appena si chiude la finestra di dialogo, ASSICURI di assegnare un nome alla tabella nel campo

Nome tabella (angolo in alto a sinistra) nel gruppo Proprietà (nella schermata corrente). Digitare MASTER, quindi premere il tasto Invio. 6. Si noti che la tabella ora mostra le frecce in basso accanto a ciascun nome di campo. Fai clic su una delle frecce verso il basso (ad es. Città) e nota la finestra di dialogo che appare. Questa casella contiene le opzioni di ordinamento che includono i filtri, che consentono di visualizzare informazioni specifiche in base al filtro selezionato. Ad esempio, se deselezioni la casella di testo

Seleziona tutto , quindi seleziona solo le caselle di campo che dicono Miami e Destin , Excel esclude TUTTE le città TRANNE a Miami e Destin. NOTA:

Non farti prendere dal panico quando tutti i record rimanenti scompaiono. Non sono spariti, solo nascosti. Per visualizzarli nuovamente, fai clic sulla freccia giù accanto al campo Città, quindi fai clic sul pulsante Tutti e saranno immediatamente visibili di nuovo. SUGGERIMENTO:

Fai clic su Filtro testo < L'opzione s nel menu a discesa Filtri per selezionare le opzioni di filtro dall'elenco popup (al contrario dei soli campi corrispondenti dall'elenco caselle di controllo). Le opzioni includono Uguale, Non uguale, Inizia con, Termina con, Contiene, Non contiene, Filtro personalizzato. 7. Ricorda di cambiare la scheda (che dice Foglio1) nella parte inferiore del foglio di calcolo per dire

Master . Fare doppio clic sulla scheda del foglio di calcolo per modificare rapidamente il nome. JD Sartain / PC World

Utilizzare i filtri per estrarre dati specifici dalla tabella del foglio di calcolo

Tabelle slave

1. Quindi, crea le tabelle slave. Premere il + (segno più) sulla barra di navigazione per aggiungere due fogli di calcolo aggiuntivi. Indica una di queste violazioni e gli altri indirizzi.

2. Copia colonna A (numeri di licenza) per la colonna A in entrambi gli altri fogli di lavoro. Regola le larghezze delle colonne su 11.

NOTA:

Ricorda che la tabella Slave in questo esempio è uno-a-molti, il che significa che ci sono più record per driver, quindi il campo Chiave (Numero di licenza) può- e sarà ripetuto molte volte. Lo stiamo copiando sulle tabelle slave in modo da non dover reinserire i dati. Per ogni voce multipla, è sufficiente ricopiare il numero di licenza prima di inserire i dati nel resto di ciascun record. 3. Utilizzare gli stessi campi mostrati nella seguente schermata "Database relazionale: tabella slave violazioni" di seguito, e quindi inserire i propri dati (o usare il nostro). Tali campi sono: Numero di licenza (nella colonna A, ovviamente), Tipo di violazione, Tariffa di violazione, Data di violazione, Data di scadenza, Data pagamento a pagamento

4. Inserisci circa 30 record con numeri di licenza ripetuti. Ad esempio: il numero di licenza F298-6588 viene ripetuto tre volte. Ciò significa che Roger Anderson (il conducente con questo numero di licenza) ha tre violazioni del conducente.

5. Al termine, evidenzia l'intera tabella includendo le intestazioni: A1: H31.

6. Selezionare

Inserisci > Tabella 7. L'indirizzo dell'intervallo viene visualizzato nel campo denominato:

Dove si trovano i dati per la tabella nella finestra di dialogo Crea tabella . Seleziona la casella che dice: La mia tabella ha intestazioni , quindi fai clic su OK. 8. Non appena si chiude la finestra di dialogo, ASSICURI di assegnare un nome alla tabella nel campo

Nome tabella (angolo in alto a sinistra) nel gruppo Proprietà (nella schermata corrente). Immettere VIOLATIONS, quindi premere il tasto Return. 9. Fare clic su una delle frecce verso il basso accanto a una delle colonne / campi. Si noti la finestra di dialogo che appare. Sperimenta con l'ordinamento e i filtri per visualizzare i risultati diversi da questa tabella.

JD Sartain / PC World

Crea il foglio di calcolo Tabella delle violazioni slave

10. Per la tabella degli slave degli indirizzi, tornare al master e copiare le colonne C, D, E e F (indirizzo, città, stato, zip) alle colonne B, C, D ed E nella tabella degli indirizzi (ricordare, numero di licenza- il campo chiave univoco - è già nella colonna A).

11. Regola le larghezze delle colonne per contenere i dati.

12. Inserisci circa 30 record con numeri di licenza ripetuti. Ad esempio: il numero di licenza F298-6588 viene ripetuto tre volte. Ciò significa che Roger Anderson (il conducente con questo numero di licenza) ha tre indirizzi precedenti.

13. Ripetere le istruzioni da 5 a 9 sopra, questa volta denominando gli indirizzi tabella e definendo l'intervallo come A1: E31. E questo è tutto quello che c'è da fare.

JD Sartain / PC World

Crea il foglio di calcolo della tabella slave degli indirizzi