FULLS DE CàLCUL ENLLAçATS A EXCEL

Quan realitzeu determinades tasques a Excel, de vegades heu de tractar diverses taules, que també estan relacionades entre si. És a dir, les dades d'una taula s'enganxen a l'altra i, quan canvien, es recalculen els valors de tots els intervals de taules relacionats.

Les taules enllaçades són molt útils per processar grans quantitats d’informació. No és molt convenient tenir tota la informació en una taula i, si no és homogènia. És difícil treballar amb aquests objectes i cercar-los. Aquest problema està destinat a eliminar taules relacionades, la informació entre la qual es distribueix, però al mateix temps està interrelacionada. Els intervals de taules enllaçats es poden localitzar no només dins d’un full o en un llibre, sinó també en llibres separats (fitxers). A la pràctica, les dues últimes opcions s’utilitzen amb més freqüència, ja que l’objectiu d’aquesta tecnologia és allunyar-se de l’acumulació de dades i acumular-les a la mateixa pàgina no resoldrà fonamentalment el problema. Aprenguem a crear i treballar amb aquest tipus de gestió de dades.

Creació de taules enllaçades

Primer de tot, reflexionem sobre la qüestió de com és possible crear un enllaç entre diferents rangs de taules.

Mètode 1: Enllaçar directament les taules amb una fórmula

La manera més senzilla d’enllaçar dades és utilitzar fórmules que enllacen amb altres rangs de taula. Es diu unió directa. Aquest mètode és intuïtiu, ja que amb ell la unió es realitza gairebé de la mateixa manera que la creació de referències a dades en un únic quadre de taula.

Vegem com un exemple pot formar un enllaç mitjançant unió directa. Tenim dues taules en dos fulls. En una taula, es calcula la nòmina utilitzant una fórmula multiplicant la taxa de treballadors per una tarifa única per a tots.

A la segona fulla hi ha un rang tabular en què hi ha una llista dels empleats amb els seus salaris. La llista d'empleats en ambdós casos es presenta en el mateix ordre.

Cal fer que les dades sobre les taxes de la segona fulla es puguin treure a les cel·les corresponents de la primera.

Al primer full, seleccioneu la primera cel·la de la columna. "Aposta". Posem la seva marca "=". A continuació, feu clic a l’etiqueta "Full 2"Que es troba a la part esquerra de la interfície d’Excel que hi ha sobre la barra d’estat.

Es mou a la segona àrea del document. Feu clic a la primera cel·la de la columna. "Aposta". A continuació, feu clic al botó. Introduïu al teclat per realitzar una entrada de dades a la cel·la en què anteriorment s'havia establert el signe igual.

Després hi ha una transició automàtica al primer full. Com podeu veure, la velocitat del primer empleat de la segona taula es troba a la cel·la adequada. Un cop posat el cursor a la cel·la que conté l’aposta, veiem que la fórmula habitual s’utilitza per mostrar dades a la pantalla. Però abans de les coordenades de la cel·la on es mostren les dades, hi ha una expressió "Full2!"que indica el nom de l’àrea del document on es troba. La fórmula general en el nostre cas és la següent:
= Full2! B2

Ara heu de transferir les dades sobre les tarifes de tots els altres empleats de l'empresa. Per descomptat, això es pot fer de la mateixa manera que hem aconseguit la tasca per al primer empleat, però atès que les dues llistes d’empleats s’organitzen en el mateix ordre, la tasca es pot simplificar significativament i accelerar la seva solució. Això es pot fer simplement copiant la fórmula a l’interval inferior. A causa del fet que els enllaços d’Excel són relatius per defecte, quan s’han copiat, els valors canvien, que és el que necessitem. El procediment de còpia en si es pot realitzar mitjançant el marcador d’ompliment.
Per tant, poseu el cursor a la part inferior dreta de l’element amb la fórmula. Després d'això, el cursor s'hauria de convertir en un farciment en forma de creu negra. Realitzem la pinça del botó esquerre del ratolí i arrosseguem el cursor fins al fons de la columna.

Totes les dades de la mateixa columna activades Full 2 es van tirar a la taula Full 1. Quan les dades canvien a Full 2 automàticament canviaran la primera.

Mètode 2: utilitzar un munt d’operadors INDEX - MATCH

Però, què passa si la llista d'empleats en matrius tabulars no està organitzada en el mateix ordre? En aquest cas, com es va esmentar anteriorment, una de les opcions és configurar la connexió entre cadascuna d’elles que s’hauria de vincular manualment. Però això només és adequat per a taules petites. Per a rangs massius, aquesta opció, en el millor dels casos, trigarà molt de temps a aplicar-se i, en el pitjor dels casos, a la pràctica no serà factible en absolut. Però podeu solucionar aquest problema amb un munt d’operadors ÍNDEX - PARTIT. Vegem com es pot fer això enllaçant les dades en rangs tabulars, que es van discutir en el mètode anterior.

Seleccioneu el primer element de la columna. "Aposta". Aneu a Auxiliar de funcionsfent clic a la icona "Insereix la funció".

In Auxiliar de funcions en un grup "Enllaços i matrius" busqueu i seleccioneu el nom ÍNDEX.

Aquest operador té dues formes: un formulari per treballar amb matrius i una referència. En el nostre cas, es requereix la primera opció, de manera que a la següent finestra de la selecció del formulari, que s'obrirà, la seleccionarem i feu clic al botó "D'acord".

S'ha executat la finestra d’argument d’operador. ÍNDEX. La tasca de la funció especificada és mostrar el valor que es troba a l’interval seleccionat en la línia amb el número especificat. Fórmula general de l'operador ÍNDEX és això:
= INDEX (matriu; nombre_liner; [número_columna])
"Matriu" - l’argument que conté l’adreça de l’interval des del qual s’extraurà la informació pel nombre de la cadena especificada.
"Número de línia" - l’argument que és el número d’aquesta línia. És important saber que el número de línia no s'ha d’especificar en relació amb el document sencer, sinó només pel que fa a la matriu seleccionada.
"Nombre de columna" - L'argument és opcional. Per resoldre el nostre problema específicament, no el farem servir i, per tant, no cal descriure la seva essència per separat.
Poseu el cursor al camp "Matriu". Després passem a Full 2 i, mantenint el botó esquerre del ratolí, seleccioneu tot el contingut de la columna "Aposta".

Després de mostrar les coordenades a la finestra de l’operador, poseu el cursor al camp "Número de línia". Mostra l’argument mitjançant l’operador PARTIT. Per tant, feu clic al triangle situat a l’esquerra de la línia de funció. S'obrirà una llista d’operadors utilitzats recentment. Si trobeu entre ells el nom "MATCH"llavors podeu fer-hi clic. En cas contrari, feu clic a l’element més recent de la llista: "Altres funcions ...".

S'inicia la finestra estàndard. Màsters de funcions. Aneu-hi al mateix grup. "Enllaços i matrius". Aquesta vegada a la llista, seleccioneu l’element "MATCH". Feu un clic al botó. "D'acord".

Activa els arguments de la finestra de l’operador PARTIT. La funció especificada està destinada a mostrar el nombre d'un valor en una matriu específica pel seu nom. Gràcies a aquesta oportunitat, calcularem el nombre de files d’un valor específic per a la funció. ÍNDEX. Sintaxi PARTIT presentat com:
= MATCH (valor de cerca; matriu de cerca; [tipus_partit])
"Valor buscat" - l’argument que conté el nom o l’adreça de la cel·la de l’àmbit de tercers en què es troba. És la posició d’aquest nom a l’abast de destinació que s’hauria de calcular. En el nostre cas, el primer argument serà referències de cel·les Full 1en què es troben els noms dels empleats.
"Tauler vist" - un argument que representa un enllaç a una matriu en què es busca el valor especificat per determinar la seva posició. Reproduirem aquesta columna d’adreça de rol "Nom on Full 2.
"Tipus de mapatge" - Un argument que és opcional, però, a diferència de la declaració anterior, necessitarem aquest argument opcional. Indica com l’operador coincidirà amb el valor desitjat amb la matriu. Aquest argument pot tenir un dels tres valors: -1; 0; 1. Per a matrius no ordenades, seleccioneu l’opció "0". Aquesta opció és adequada per al nostre cas.
Per tant, comencem a emplenar els camps de la finestra d’arguments. Poseu el cursor al camp "Valor buscat", feu clic a la primera cel·la de la columna "Nom" on Full 1.

Després de mostrar les coordenades, establiu el cursor al camp "Tauler vist" i continua a la drecera "Full 2"que es troba a la part inferior de la finestra d’Excel sobre la barra d’estat. Manteniu premut el botó esquerre del ratolí i ressalteu totes les cel·les de la columna. "Nom".

Després de mostrar les seves coordenades al camp "Tauler vist"anar al camp "Tipus de mapatge" i establiu el número des del teclat "0". Després d'això, tornarem al camp. "Tauler vist". El fet és que copiarem la fórmula, com vam fer en el mètode anterior. Hi haurà un desplaçament d’adreces, però hem de fixar les coordenades de la matriu que es visualitza. No hauria de canviar. Seleccioneu les coordenades del cursor i feu clic a la tecla de funció F4. Com podeu veure, un signe de dòlar va aparèixer davant de les coordenades, el que significa que l’enllaç del parent s’ha convertit en absolut. A continuació, feu clic al botó "D'acord".

El resultat es mostra a la primera cel·la de la columna. "Aposta". Però abans de copiar, hem de fixar una altra àrea, és a dir, el primer argument de la funció ÍNDEX. Per fer-ho, seleccioneu l'element de la columna que conté la fórmula i aneu a la barra de fórmules. Seleccioneu el primer argument de l’operador ÍNDEX (B2: B7) i feu clic al botó F4. Com podeu veure, el signe del dòlar va aparèixer a prop de les coordenades seleccionades. Feu clic al botó Introduïu. En general, la fórmula va prendre la forma següent:
= INDEX (Full2! $ B $ 2: $ B $ 7; MATCH (Full1! A4; Full2! $ A $ 2: $ A $ 7; 0))

Ara podeu copiar amb el marcador d’ompliment. Truqueu-lo de la mateixa manera que hem parlat anteriorment i estireu-lo fins al final de la taula.

Com podeu veure, tot i que l’ordre de les files de les dues taules relacionades no coincideix, però, tots els valors s’ajusten segons els noms dels treballadors. Això es va aconseguir mitjançant l'ús d'una combinació d'operadors ÍNDEX-PARTIT.

Vegeu també:
Funció Excel INDEX
La funció de coincidència a Excel

Mètode 3: Realitzeu operacions matemàtiques amb dades associades

La unió directa de dades també és bona en la mesura que permet no només mostrar valors que es mostren en altres rangs de taula en una de les taules, sinó també realitzar diverses operacions matemàtiques amb ells (addició, divisió, resta, multiplicació, etc.).

Vegem com es fa en la pràctica. Anem a fer-ho Full 3 es mostraran les dades generals de salaris d’empresa sense desglossament dels empleats. Per això, es retiraran les tarifes del personal Full 2, resumeix (utilitzant la funció SUMA) i multiplicat pel coeficient utilitzant la fórmula.

Seleccioneu la cel·la on es mostrarà la nòmina total Full 3. Feu clic al botó "Insereix la funció".

Hauria de llançar la finestra Màsters de funcions. Aneu al grup "Matemàtica" i trieu el nom allà "SUMM". A continuació, feu clic al botó "D'acord".

Passant a la finestra d’argument de la funció SUMAque està dissenyat per calcular la suma dels números seleccionats. Té la següent sintaxi:
= SUMA (nombre1; número2; ...)
Els camps de la finestra corresponen als arguments de la funció especificada. Tot i que el seu nombre pot arribar a 255 peces, per al nostre propòsit només n'hi haurà prou. Poseu el cursor al camp "Nombre1". Feu clic a l’etiqueta "Full 2" per sobre de la barra d’estat.

Després de moure'ns a la secció desitjada del llibre, seleccioneu la columna que s’haurà de resumir. El fem el cursor, mantenint el botó esquerre del ratolí. Com podeu veure, les coordenades de l'àrea seleccionada es mostren immediatament al camp de la finestra d’arguments. A continuació, feu clic al botó. "D'acord".

Després d'això, passem automàticament a Full 1. Com podeu veure, l’import total de les taxes salarials dels treballadors ja es mostra al’element corresponent.

Però això no és tot. Com recordem, el salari es calcula multiplicant el valor de la taxa pel coeficient. Per tant, seleccionem de nou la cel·la en què es troba el valor sumat. Després, aneu a la barra de fórmules. Afegim un signe de multiplicació a la seva fórmula (*), i després feu clic a l’element on es troba el coeficient. Per realitzar el càlcul, feu clic a Introduïu al teclat. Com podeu veure, el programa calculava el salari total per a l'empresa.

Torna a Full 2 i canviar la mida de la taxa de qualsevol empleat.

Després d'això, torneu a passar a la pàgina amb l'import total. Com podeu veure, a causa dels canvis en la taula relacionada, es va calcular automàticament el resultat del salari total.

Mètode 4: inserció especial

També podeu enllaçar matrius de taula en Excel amb una inserció especial.

Seleccioneu els valors que cal "reforçar" a una altra taula. En el nostre cas, aquest és el rang de columna. "Aposta" on Full 2. Feu clic al fragment seleccionat amb el botó dret del ratolí. A la llista que s’obre, seleccioneu l’element "Còpia". La combinació de tecles alternativa és Ctrl + C. Després d’aquest pas a Full 1.

Si aneu a la zona desitjada del llibre, seleccionarem les cel·les en què voleu treure els valors. En el nostre cas, es tracta d’una columna. "Aposta". Feu clic al fragment seleccionat amb el botó dret del ratolí. Al menú contextual de la barra d'eines "Opcions d'inserció" feu clic a la icona "Insereix un enllaç".
També hi ha una alternativa. Per cert, és l'únic per a versions antigues d'Excel. Al menú contextual, moveu el cursor a l’element "Enganxa especial". Al menú addicional que s'obre, seleccioneu l’element amb el mateix nom.

Després d'això, s'obrirà una finestra d'inserció especial. Premeu el botó "Insereix un enllaç" a la cantonada inferior esquerra de la cel·la.

Qualsevol que sigui l’opció que escolliu, els valors d’una matriu de taules s’inseriran en un altre. Quan canvieu les dades a l’origen, també canviaran automàticament l’interval introduït.

Lliçó: Enganxa especial a Excel

Mètode 5: relació entre taules en diversos llibres

A més, podeu organitzar la connexió entre els espais de taula en diferents llibres. Això utilitza l’eina d'inserció especial. Les accions seran absolutament similars a les que vam considerar en el mètode anterior, excepte que la navegació durant la introducció de les fórmules no haurà de passar entre àrees d’un llibre, sinó entre fitxers. Naturalment, tots els llibres relacionats haurien d'estar oberts.

Seleccioneu el rang de dades que voleu transferir a un altre llibre. Feu clic amb ell amb el botó dret del ratolí i seleccioneu la posició al menú que s'obre "Còpia".

Després passem al llibre en el qual s’han d’inserir aquestes dades. Seleccioneu l'interval desitjat. Feu clic al botó dret del ratolí. Al menú contextual del grup "Opcions d'inserció" tria un element "Insereix un enllaç".

Després d'això, s’inseriran els valors. Quan canvieu les dades del llibre d'origen, les matrius tabulars del llibre les trauran automàticament. I no és absolutament necessari que els dos llibres estiguin oberts a això. N’hi ha prou d’obrir només un llibre de treball i automàticament introduirà les dades del document enllaçat tancat, si anteriorment s’hagin fet canvis.

Però cal assenyalar que en aquest cas la inserció es farà en forma d’una matriu immutable. Si intenteu canviar qualsevol cel·la amb les dades inserides, apareixerà un missatge informant-vos que no és possible fer-ho.

Els canvis en aquesta matriu associats a un altre llibre només es poden fer trencant l’enllaç.

Desconnexió entre taules

De vegades és necessari trencar l’enllaç entre els rangs de taules. El motiu pot ser, com el cas descrit anteriorment, quan voleu canviar una matriu inserida des d’un altre llibre o simplement perquè l’usuari no vol que les dades d’una taula s’actualitzin automàticament d’una altra.

Mètode 1: desconnectar entre llibres

Podeu trencar la connexió entre llibres a totes les cel·les fent pràcticament una operació. Al mateix temps, les dades de les cel·les es mantindran, però ja seran valors estàtics no actualitzats que no depenen d'altres documents.

Al llibre, en què es treuen els valors d'altres fitxers, aneu a la pestanya "Dades". Feu clic a la icona "Edita enllaços"que es troba a la cinta del bloc d'eines "Connexions". Cal destacar que si el llibre actual no conté enllaços a altres fitxers, aquest botó està inactiu.

Es posa en marxa la finestra per canviar els enllaços. Seleccioneu de la llista de llibres relacionats (si hi ha diversos) el fitxer amb el qual volem trencar la connexió. Feu clic al botó "Trenca l'enllaç".

S'obrirà una finestra d’informació en la qual s’adverteix sobre les conseqüències d’unes altres accions. Si esteu segurs del que faríeu, feu clic al botó. "Llaços trencats".

Després d'això, totes les referències al fitxer especificat al document actual es substituiran per valors estàtics.

Mètode 2: Insereix valors

Però el mètode anterior només és adequat si necessiteu tallar completament tots els enllaços entre els dos llibres. Què fer si voleu desconnectar les taules relacionades que es troben dins del mateix fitxer? Podeu fer-ho copiant les dades i enganxant-lo al mateix lloc que els valors.Per cert, es pot utilitzar el mateix mètode per trencar la connexió entre rangs de dades separats de diferents llibres sense trencar la connexió general entre fitxers. Vegem com funciona aquest mètode en la pràctica.

Seleccioneu l’interval en què volem eliminar l’enllaç a una altra taula. Feu clic amb ell amb el botó dret del ratolí. Al menú que s'obre, seleccioneu l’element "Còpia". En lloc d’aquestes accions, podeu escriure una combinació de tecles d’alternativa alternativa. Ctrl + C.

Després, sense treure la selecció del mateix fragment, torneu a fer clic amb ell amb el botó dret del ratolí. Aquesta vegada a la llista d'accions farem clic a la icona "Valors"que es col·loca en un grup d’eines "Opcions d'inserció".

Després d'això, tots els enllaços de l’interval seleccionat seran substituïts per valors estàtics.

Com podeu veure, Excel té mètodes i eines per enllaçar diverses taules. En aquest cas, les dades tabulars poden estar en altres fulls i fins i tot en llibres diferents. Si cal, es pot trencar fàcilment aquesta connexió.