photosdematures.com

Què és el model de dades a Excel?

Model de dades en excel és un tipus de taula de dades on dues o més de dues taules estem en relació entre si mitjançant una sèrie de dades comuna o més, en les taules de models de dades i les dades de diversos fulls o fonts s’uneixen per formar una taula única que pot tenir accés a les dades de totes les taules.

Explicació

• Permet integrar dades de diverses taules creant relacions basades en una columna comuna.
• Els models de dades s’utilitzen de manera transparent, proporcionant dades tabulars que es poden utilitzar a la taula dinàmica Excel i als gràfics dinàmics Excel. Integra les taules, permetent una anàlisi exhaustiva mitjançant taules dinàmiques, pivot avançat i Power View a Excel.
• El model de dades permet carregar dades a la memòria d'Excel.
• Es desa a la memòria on no el podem veure directament. A continuació, es pot demanar a Excel que relacioni dades entre si mitjançant una columna comuna. La part "Model" del model de dades fa referència a la relació de totes les taules.
• El model de dades pot accedir a tota la informació que necessita fins i tot quan la informació es troba en diverses taules. Després de crear el model de dades, Excel té les dades disponibles a la memòria. Amb les dades a la memòria, es pot accedir a les dades de moltes maneres.

Exemples

Podeu descarregar aquesta plantilla Excel de model de dades aquí - Plantilla Excel de model de dades

Exemple 1

Si tenim tres conjunts de dades relacionats amb el venedor: el primer que conté informació sobre els ingressos, un segon que conté els ingressos del venedor i el tercer que conté les despeses del venedor.

Per connectar aquests tres conjunts de dades i establir una relació amb aquests, fem un model de dades amb els passos següents:

• Convertiu els conjunts de dades en objectes de taula:

No podem crear una relació amb conjunts de dades normals. El model de dades només funciona amb objectes Excel Tables. Fer això:

• Pas 1 - Feu clic a qualsevol lloc del conjunt de dades, feu clic a la pestanya "Insereix" i, a continuació, feu clic a "Taula" al grup "Taules".

• Pas 2: Marqueu o desmarqueu l'opció: "La meva taula té capçaleres" i feu clic a D'acord.

• Pas 3: Amb la nova taula seleccionada, introduïu el nom de la taula al "Nom de la taula" al grup "Eines".

• Pas 4: Ara podem veure que el primer conjunt de dades es converteix en objecte ‘Taula’. En repetir aquests passos per als altres dos conjunts de dades, veiem que també es converteixen en objectes "Taula" com es mostra a continuació:

Afegir els objectes "Taula" al model de dades: Mitjançant connexions o relacions.

Via Connexions

• Seleccioneu una taula i feu clic a la pestanya "Dades" i, a continuació, feu clic a "Connexions".

• Al quadre de diàleg resultant, hi ha una icona "Afegeix". Amplieu el menú desplegable "Afegeix" i feu clic a "Afegeix al model de dades".

• Feu clic a "Taules" al quadre de diàleg resultant i, a continuació, seleccioneu una de les taules i feu clic a "Obre".

En fer-ho, es crearia un model de dades del llibre amb una taula i apareixerà un quadre de diàleg de la següent manera:

Per tant, si repetim aquests passos també per a les altres dues taules, el model de dades contindrà les tres taules.

Ara podem veure que les tres taules apareixen a Workbook Connections.

Via relacions

Creeu la relació: un cop els dos conjunts de dades siguin objectes de taula, podem crear una relació entre ells. Fer això:

• Feu clic a la pestanya "Dades" i, a continuació, feu clic a "Relacions".

• Veurem un quadre de diàleg buit ja que no hi ha connexions actuals.

• Feu clic a "Nou" i apareixerà un altre quadre de diàleg.

• Amplieu els desplegables "Taula" i "Taula relacionada": apareix el quadre de diàleg "Crea una relació" per triar les taules i les columnes que voleu utilitzar per a una relació. A l'expansió de "Taules", seleccioneu el conjunt de dades que volem analitzar d'alguna manera i, a "Taula relacionada", seleccioneu el conjunt de dades que tingui valors de cerca.
• La taula de cerca a Excel és la taula més petita en cas que hi hagi una a moltes relacions i no conté cap valor repetit a la columna comuna. A l’expansió de "Columna (estrangera)", seleccioneu la columna comuna a la taula principal, a "Columna relacionada (primària)", seleccioneu la columna comuna a la taula relacionada.

• Amb tots aquests quatre paràmetres seleccionats, feu clic a "D'acord". Apareix un quadre de diàleg de la següent manera en fer clic a "D'acord".

Si repetim aquests passos per relacionar altres dues taules: Taula d’ingressos amb taula de despeses, també es relacionen al model de dades de la següent manera:

Ara Excel crea la relació entre bastidors combinant dades al model de dades basat en una columna comuna: identificador de venedor (en aquest cas).

Exemple 2

Ara, diguem a l'exemple anterior que volem crear una taula dinàmica que avalui o analitzi els objectes de la taula:

• Feu clic a "Insereix" -> "Taula dinàmica".

• Al quadre de diàleg resultant, feu clic a l'opció que indica: "Utilitzeu una font de dades externa" i, a continuació, feu clic a "Tria connexió".

• Feu clic a "Taules" al quadre de diàleg resultant i seleccioneu el model de dades del llibre de treball que conté tres taules i feu clic a "Obre".

• Seleccioneu l'opció "Full de treball nou" a la ubicació i feu clic a "D'acord".

• El panell Camps de taula dinàmica mostrarà objectes de taula.

• Ara els canvis a la taula dinàmica es poden fer en conseqüència per analitzar els objectes de la taula segons calgui.

Per exemple, en aquest cas, si volem trobar els ingressos o ingressos totals per a un venedor en particular, es crea una taula dinàmica de la següent manera:

Això és d’immensa ajuda en el cas d’un model / taula que conté un gran nombre d’observacions.

Per tant, podem veure que la taula dinàmica utilitza instantàniament el model de dades (escollint-lo triant la connexió) a la memòria Excel per mostrar relacions entre taules.

Coses que cal recordar

• Mitjançant el model de dades, podem analitzar dades de diverses taules alhora.
• En crear relacions amb el model de dades, superem la necessitat d’utilitzar fórmules VLOOKUP, SUMIF, INDEX i MATCH, ja que no necessitem obtenir totes les columnes d’una sola taula.
• Quan els conjunts de dades s’importen a Excel des de fonts externes, els models es creen implícitament.
• Les relacions de taula es poden crear automàticament si importem taules relacionades que tenen relacions de clau primària i externa.
• En crear relacions, les columnes que connectem a les taules haurien de tenir el mateix tipus de dades.
• Amb les taules dinàmiques creades amb el model de dades, també podem afegir talls i tallar les taules dinàmiques en qualsevol camp que vulguem.
• L’avantatge del model de dades sobre les funcions LOOKUP () és que requereix substancialment menys memòria.
• Excel 2013 només admet una a una o una a moltes relacions, és a dir, una de les taules no ha de tenir cap valor duplicat a la columna a la qual enllacem.