Podeu utilitzar una combinació de taules dinàmiques i connexions de dades d'Excel per interactuar directament amb el model de dades intern, sense el complement de Power Pivot. Això és útil si utilitzeu versions d'Excel que no s'inclouen amb el complement Power Pivot, com ara quan utilitzeu Microsoft Office, ja sigui l'edició Home o Small Business. Cada llibre de treball d'Excel 2013 i 2016 inclou un model de dades intern.
Imagineu que teniu la taula de transaccions que veieu aquí i en un altre full de treball teniu una taula d'empleats que conté informació sobre els empleats.
Aquesta taula mostra les transaccions per número d'empleat.
Aquesta taula proporciona informació sobre els empleats: nom, cognoms i càrrec.
Heu de crear una anàlisi que mostri les vendes per títol. Això normalment seria difícil atès que les vendes i el títol del lloc de treball es troben en dues taules separades. Però amb el model de dades internes, podeu seguir aquests senzills passos:
Feu clic a la taula de dades de transaccions i inicieu una nova taula dinàmica escollint Insereix ➪ Taula dinàmica a la cinta.
Al quadre de diàleg Crea una taula dinàmica, seleccioneu l'opció Afegeix aquestes dades al model de dades.
Quan creeu una taula dinàmica nova a partir de la taula de transaccions, assegureu-vos de seleccionar Afegeix aquestes dades al model de dades.
Feu clic a la taula de dades dels empleats i inicieu una nova taula dinàmica.
De nou, assegureu-vos de seleccionar l'opció Afegeix aquestes dades al model de dades, tal com es mostra.
Observeu que els quadres de diàleg Crea una taula dinàmica fan referència a intervals amb nom. És a dir, cada taula va rebre un nom específic. Quan afegiu dades al model de dades intern, és una bona pràctica donar nom a les taules de dades. D'aquesta manera, podeu reconèixer fàcilment les vostres taules al Model intern de dades. Si no poseu un nom a les vostres taules, el model de dades intern les mostra com a Interval1, Interval2, etc.
Creeu una taula dinàmica nova des de la taula Empleats i seleccioneu Afegeix aquestes dades al model de dades.
Per donar un nom a la taula de dades, simplement ressalteu totes les dades de la taula i, a continuació, seleccioneu l'ordre Fórmules → Definir nom de la cinta. Al quadre de diàleg, introduïu un nom per a la taula. Repetiu-ho per a totes les altres taules.
Després que les dues taules s'hagin afegit al model de dades intern, obriu la llista Camps de la taula dinàmica i trieu el selector TOT. Aquest pas mostra els dos intervals a la llista de camps.
Seleccioneu TOTS a la llista Camps de la taula dinàmica per veure ambdues taules al model de dades intern.
Construeix la taula dinàmica de manera normal. En aquest cas, Job_Title es col·loca a l'àrea Fila i Sales_Amount passa a l'àrea Valors.
Com podeu veure aquí, Excel reconeix immediatament que esteu utilitzant dues taules del Model de dades interns i us demana que creeu una relació entre elles. Teniu l'opció de deixar que Excel detecti automàticament les relacions entre les vostres taules o de fer clic al botó Crea. Creeu sempre les relacions vosaltres mateixos, per evitar qualsevol possibilitat que Excel s'equivoqui.
Quan Excel us demani, trieu crear la relació entre les dues taules.
Feu clic al botó Crear.
Excel obre el quadre de diàleg Crea una relació, que es mostra aquí. Allà, seleccioneu les taules i els camps que defineixen la relació. Podeu veure que la taula Transaccions té un camp Sales_Rep. Està relacionat amb la taula Empleats mitjançant el camp Employee_Number.
Creeu la relació adequada mitjançant les llistes desplegables Taula i Columna.
Després de crear la relació, teniu una única taula dinàmica que utilitza efectivament les dades d'ambdues taules per crear l'anàlisi que necessiteu. La figura següent il·lustra que, utilitzant el model de dades internes d'Excel, heu aconseguit l'objectiu de mostrar les vendes per títol de feina.
Heu assolit el vostre objectiu de mostrar les vendes per títol de feina.
Veureu que el menú desplegable inferior dret s'anomena Columna relacionada (principal). El terme primari significa que el model de dades intern utilitza aquest camp de la taula associada com a clau primària.
Una clau primària és un camp que només conté valors únics no nuls (sense duplicats ni espais en blanc). Els camps de clau primària són necessaris al model de dades per evitar errors d'agregació i duplicacions. Cada relació que creeu ha de tenir un camp designat com a clau primària.
La taula Empleats ha de tenir tots els valors únics al camp Employee_Number, sense espais en blanc ni valors nuls. Aquesta és l'única manera que Excel pot garantir la integritat de les dades quan s'uneix a diverses taules.
Després d'assignar taules al model de dades intern, és possible que hàgiu d'ajustar les relacions entre les taules. Per fer canvis a les relacions en un model de dades intern, feu clic a la pestanya Dades de la cinta i seleccioneu l'ordre Relacions. S'obre el quadre de diàleg Gestiona les relacions, que es mostra aquí.
El quadre de diàleg Gestiona les relacions us permet fer canvis a les relacions del model de dades intern.
Aquí trobareu les ordres següents:
- Nou: creeu una nova relació entre dues taules al model de dades internes.
- Edita: altera la relació seleccionada.
- Activar: imposar la relació seleccionada, dient a Excel que tingui en compte la relació a l'hora d'agregar i analitzar les dades al model de dades intern.
- Desactivar: desactiveu la relació seleccionada, dient a Excel que ignori la relació quan s'agregui i analitzeu les dades al model de dades intern.
- Suprimeix: elimina la relació seleccionada.