Á hora de realizar determinadas tarefas en Excel, ás veces hai que tratar con varias táboas, que tamén están relacionadas entre si. É dicir, os datos dunha táboa son tirados para o outro e, cando cambian, recalculan os valores de todos os intervalos de táboa relacionados.
As táboas ligadas son moi útiles para procesar grandes cantidades de información. Non é moi cómodo ter toda a información nunha táboa e se non é homoxénea. É difícil traballar con tales obxectos e busca-los. Este problema está destinado a eliminar táboas relacionadas, a información entre a que se distribúe, pero á vez está relacionada. Os intervalos de táboas ligadas pódense localizar non só dentro dunha folla ou un libro, senón tamén situarse en libros separados (ficheiros). Na práctica, as dúas últimas opcións utilízanse con máis frecuencia, xa que o obxectivo desta tecnoloxía é afastarse da acumulación de datos e empilharse na mesma páxina non soluciona fundamentalmente o problema. Aprendemos a crear e traballar con este tipo de xestión de datos.
Crear táboas ligadas
Primeiro de todo, imos insistir na cuestión de como é posible crear unha ligazón entre diferentes intervalos de táboas.
Método 1: ligando directamente táboas cunha fórmula
O xeito máis sinxelo de enlazar datos é usar fórmulas que se unen a outros intervalos de táboa. Chámase enlace directo. Este método é intuitivo, xa que con el a unión realízase case do mesmo xeito que a creación de referencias a datos nunha única matriz de táboas.
Vexamos como un exemplo pode formar un vínculo mediante unión directa. Temos dúas táboas en dúas follas. Nunha táboa, calcúlase a folla de pagamento empregando unha fórmula multiplicando a taxa de traballadores cunha taxa única para todos.
Na segunda folla hai un rango tabular no que hai unha lista dos empregados cos seus salarios. A lista de empregados nos dous casos preséntase na mesma orde.
Cómpre facer que os datos sobre as taxas da segunda folla tráianse nas celas correspondentes do primeiro.
- Na primeira folla, seleccione a primeira cela. "Aposta". Poñemos a súa marca "=". A continuación, fai clic na etiqueta "Folla 2"Que se atopa no lado esquerdo da interface de Excel sobre a barra de estado.
- Móvese á segunda área do documento. Fai clic na primeira cela da columna. "Aposta". A continuación, faga clic no botón. Intro no teclado para realizar a entrada de datos na cela na que se definiu o signo anteriormente igual.
- Despois hai unha transición automática á primeira folla. Como podes ver, a taxa do primeiro empregado da segunda táboa entra na cela apropiada. Unha vez colocado o cursor sobre a cela que contén a aposta, vemos que a fórmula habitual é usada para mostrar datos na pantalla. Pero antes das coordenadas da cela onde se amosan os datos, hai unha expresión "Folla2!"que indica o nome da área do documento onde se atopan. A fórmula xeral no noso caso é a seguinte:
= Folla2! B2 - Agora ten que transferir os datos sobre as tarifas de todos os outros empregados da empresa. Por suposto, isto pódese facer do mesmo xeito que cumprimos a tarefa para o primeiro empregado, pero dado que ambas listas de empregados están organizadas na mesma orde, a tarefa pode simplificarse significativamente e acelerar a súa solución. Isto pódese facer simplemente copiando a fórmula ata o rango de abaixo. Debido ao feito de que as ligazóns en Excel son relativas por defecto, cando se copian, os valores cambian, que é o que necesitamos. O procedemento de copia en si pode realizarse usando o marcador de enchemento.
Entón, coloque o cursor na parte inferior dereita do elemento coa fórmula. Despois diso, o cursor debe converterse nun recheo en forma de cruz negra. Realizamos a pinza do botón esquerdo do rato e arrastra o cursor ata o fondo da columna.
- Todos os datos da mesma columna Folla 2 foron levados á mesa Folla 1. Cando se cambian os datos Folla 2 cambiarán automaticamente o primeiro.
Método 2: empregue un grupo de operadores INDEX - MATCH
Pero se a lista de empregados en tabular arrays non está organizada na mesma orde? Neste caso, como se mencionou anteriormente, unha das opcións é configurar a conexión entre cada unha desas celas que debería vincularse manualmente. Pero isto é adecuado só para mesas pequenas. Para as gamas masivas, esta opción, no mellor dos casos, levará moito tempo para implementarse e, no peor dos casos, na práctica non será viable. Pero podes resolver este problema cun grupo de operadores ÍNDICE - PARTIDO. Vexamos como se pode facer isto ligando datos en intervalos tabulares, que foron discutidos no método anterior.
- Seleccione o primeiro elemento na columna. "Aposta". Ir a Asistente de funciónsfacendo clic na icona "Inserir función".
- En Asistente de funcións nun grupo "Ligazóns e matrices" localice e seleccione o nome ÍNDICE.
- Este operador ten dúas formas: un formulario para traballar con matrices e unha referencia. No noso caso, a primeira opción é necesaria, polo que na seguinte ventá da selección do formulario, que se abrirá, seleccionámola e prememos no botón "OK".
- Executouse a xanela de argumento do operador. ÍNDICE. A tarefa da función especificada é mostrar o valor que está no intervalo seleccionado na liña co número especificado. Fórmula xeral do operador ÍNDICE é isto:
= INDEX (matriz; número_línea; [número_colo])"Matriz" - o argumento que contén o enderezo do intervalo desde o que extraeremos a información polo número da cadea especificada.
"Número de liña" - o argumento que é o número desta liña. É importante saber que o número de liña non debe especificarse en relación co documento completo, pero só con relación á matriz seleccionada.
"Número de columna" - O argumento é opcional. Para resolver o noso problema específicamente, non o usaremos e, polo tanto, non é necesario describir a súa esencia por separado.
Coloca o cursor no campo "Matriz". Despois de que vaia Folla 2 e, mantendo o botón esquerdo do rato, selecciona todo o contido da columna "Aposta".
- Despois de mostrar as coordenadas na ventá do operador, coloque o cursor no campo "Número de liña". Amosaremos este argumento usando o operador PARTIDO. Polo tanto, fai clic no triángulo situado á esquerda da liña de función. Ábrese unha lista de operadores usados recentemente. Se atopas entre eles o nome "MATCH"entón podes facer clic nel. Se non, faga clic no elemento máis recente da lista - "Outras funcións ...".
- Iníciase a xanela estándar. Mestría de funcións. Ir a el no mesmo grupo. "Ligazóns e matrices". Esta vez na lista, selecciona o elemento "MATCH". Fai un clic no botón. "OK".
- Activa os argumentos da xanela do operador PARTIDO. A función especificada pretende mostrar o número dun valor nunha matriz específica polo seu nome. Grazas a esta oportunidade, calcularemos o número de filas dun valor específico para a función. ÍNDICE. Sintaxe PARTIDO presentado como:
= MATCH (valor de busca; matriz de busca; [tipo_ match])"Valor buscado" - o argumento que contén o nome ou enderezo da cela de intervalo de terceiros na que se atopa. É a posición deste nome no rango de destino que debe calcularse. No noso caso, o primeiro argumento será referencias de cela Folla 1nos que se atopan os nomes dos empregados.
"Matriz visto" - un argumento que representa unha ligazón a unha matriz na que se busca o valor especificado para determinar a súa posición. Reproduciremos esta columna de dirección de rol "Nome en Folla 2.
"Tipo de mapeamento" - un argumento que é opcional, pero, a diferenza da afirmación anterior, necesitaremos este argumento opcional. Indica como o operador coincidirá co valor desexado coa matriz. Este argumento pode ter un dos tres valores: -1; 0; 1. Para as matrices non ordenadas, seleccione a opción "0". Esta opción é adecuada para o noso caso.
Entón, imos comezar a encher os campos da xanela de argumentos. Coloca o cursor no campo "Valor buscado", fai clic na primeira cela da columna "Nome" en Folla 1.
- Despois de que se amosen as coordenadas, estableza o cursor no campo "Matriz visto" e segue o atallo "Folla 2"que se atopa na parte inferior da xanela de Excel sobre a barra de estado. Manteña premido o botón esquerdo do rato e resalte todas as celas da columna. "Nome".
- Despois de que as súas coordenadas aparecen no campo "Matriz visto"ir ao campo "Tipo de mapeamento" e configura o número desde o teclado "0". Despois disto volveremos ao campo. "Matriz visto". O feito é que copiaremos a fórmula, como fixemos no método anterior. Haberá un desprazamento de enderezos, pero necesitamos fixar as coordenadas da matriz que se ven. Non debe cambiar. Seleccione as coordenadas do cursor e prema na tecla de función F4. Como podes ver, apareceu un sinal de dólar diante das coordenadas, o que significa que a ligazón do relativo volveuse absoluta. A continuación, faga clic no botón "OK".
- O resultado móstrase na primeira cela da columna. "Aposta". Pero antes de copiar necesitamos fixar outra área, é dicir, o primeiro argumento da función ÍNDICE. Para facelo, selecciona o elemento da columna que contén a fórmula e mova á barra de fórmulas. Selecciona o primeiro argumento do operador ÍNDICE (B2: B7) e prema no botón F4. Como podes ver, o signo de dólar apareceu preto das coordenadas seleccionadas. Fai clic no botón Intro. En xeral, a fórmula tomou a seguinte forma:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - Agora podes copiar usando o marcador de recheo. Chama-lo do mesmo xeito que falamos anteriormente e estíllea ata o final do rango de táboas.
- Como podes ver, a pesar do feito de que a orde das filas das dúas táboas relacionadas non coincide, con todo, todos os valores están máis axustados segundo os nomes dos traballadores. Isto logrouse mediante o uso dunha combinación de operadores ÍNDICE-PARTIDO.
Vexa tamén:
Función Excel INDEX
A función de coincidencia en Excel
Método 3: Realizar operacións matemáticas con datos asociados
A unión directa de datos tamén é boa en que non só se poden amosar valores que se mostran noutros intervalos de táboas nunha das táboas, senón tamén realizar varias operacións matemáticas con elas (adición, división, resta, multiplicación, etc.).
Vexamos como se fai isto na práctica. Imos facelo Folla 3 Os datos de salarios xerais da empresa aparecerán sen o desvío dos empregados. Para iso, retiraranse as tarifas do persoal Folla 2, resume (usando a función SUM) e multiplicado polo coeficiente usando a fórmula.
- Seleccione a cela na que se amosará a folla de pagamento total Folla 3. Fai clic no botón "Inserir función".
- Debería lanzar a xanela Mestría de funcións. Ir ao grupo "Matemática" e elixe o nome alí "SUMM". A continuación, fai clic no botón "OK".
- Pasando á xanela de argumento da función SUMque está deseñado para calcular a suma dos números seleccionados. Ten a seguinte sintaxe:
= SUMA (número 1; número2; ...)Os campos da xanela corresponden aos argumentos da función especificada. Aínda que o seu número pode chegar a 255 pezas, para o noso propósito só un será suficiente. Coloca o cursor no campo "Número1". Fai clic na etiqueta "Folla 2" por riba da barra de estado.
- Despois de pasar á sección desexada do libro, seleccione a columna que se debe resumir. Facemos o cursor, mantendo o botón esquerdo do rato. Como podes ver, as coordenadas da área seleccionada móstranse inmediatamente no campo da xanela de argumentos. A continuación, faga clic no botón. "OK".
- Despois diso, pasamos automaticamente a Folla 1. Como podes ver, o importe total dos salarios dos traballadores xa aparece no elemento correspondente.
- Pero iso non é todo. Como recordamos, o salario calcúlase multiplicando o valor da taxa polo coeficiente. Polo tanto, seleccionamos de novo a cela na que se atopa o valor sumado. Despois vaia á barra de fórmulas. Engadimos un signo de multiplicación á súa fórmula (*), e despois faga clic no elemento no que se atopa o coeficiente. Para realizar o cálculo faga clic en Intro no teclado. Como podes ver, o programa calculou o salario total para a empresa.
- Volve a Folla 2 e cambia o tamaño da taxa de calquera empregado.
- Despois disto, volva mover á páxina co importe total. Como podes ver, debido a cambios na táboa relacionada, o resultado do salario total foi recalculado automaticamente.
Método 4: inserción especial
Tamén pode ligar matrices de táboas en Excel cun inserto especial.
- Selecciona os valores que hai que "axustar" a outra táboa. No noso caso, esta é a franxa de columna. "Aposta" en Folla 2. Fai clic no fragmento seleccionado co botón dereito do rato. Na lista que se abre, selecciona o elemento "Copiar". A combinación de teclas alternativa é Ctrl + C. Despois diso Folla 1.
- Pasando á zona desexada do libro, seleccionamos as celas nas que desexa tirar os valores. No noso caso, esta é unha columna. "Aposta". Fai clic no fragmento seleccionado co botón dereito do rato. No menú de contexto da barra de ferramentas "Opcións de inserción" faga clic na icona "Inserir ligazón".
Hai tamén unha alternativa. Por certo, é o único para versións anteriores de Excel. No menú de contexto, move o cursor ata o elemento "Pegar especial". No menú adicional que se abre, selecciona o elemento co mesmo nome.
- Despois abre unha ventá de inserción especial. Pulsamos o botón "Inserir ligazón" na esquina inferior esquerda da cela.
- Sexa cal for a opción que elixas, os valores dunha matriz de tabela inseriranse noutro. Cando cambies os datos na fonte, tamén cambiarán automaticamente no intervalo inserido.
Lección: Pegar especial en Excel
Método 5: relación entre táboas en varios libros
Ademais, pode organizar a conexión entre os espazos de táboas en diferentes libros. Utiliza a ferramenta de inserción especial. As accións serán absolutamente similares ás que consideramos no método anterior, excepto que a navegación durante a introdución das fórmulas non terá que ocorrer entre áreas dun libro, senón entre ficheiros. Por suposto, todos os libros relacionados deben estar abertos.
- Seleccione o intervalo de datos que desexa transferir a outro libro. Prema nel co botón dereito do rato e selecciona a posición no menú que se abre "Copiar".
- Despois pasamos ao libro no que deberán inserirse estes datos. Seleccione o intervalo desexado. Fai clic co botón dereito do rato. No menú de contexto do grupo "Opcións de inserción" elixe un elemento "Inserir ligazón".
- Despois diso, inseriranse os valores. Cando cambia os datos do libro de orixe, a matriz tabular do libro extraerase automaticamente. E non é necesario que os dous libros estean abertos a isto. É suficiente abrir só un libro de traballo e automaticamente traerá os datos do documento ligado pechado, se se fixeron cambios nel.
Pero hai que ter en conta que neste caso a inserción farase en forma dunha matriz inmutable. Se tenta cambiar calquera cela con datos inseridos, aparecerá unha mensaxe informándolle de que non é posible facelo.
Os cambios nunha matriz asociada a outro libro só se poden facer rompendo a ligazón.
Desconexión entre táboas
Ás veces é necesario romper a conexión entre os intervalos de táboas. A razón para isto pode ser, como o caso descrito anteriormente, cando precisa cambiar unha matriz inserida doutro libro, ou simplemente porque o usuario non quere que os datos dunha táboa se actualicen automaticamente doutro.
Método 1: desconectar entre libros
Pode romper a conexión entre os libros de todas as celas realizando practicamente unha operación. Ao mesmo tempo, os datos das celas permanecerán, pero xa serán valores estáticos e non actualizados que non dependen doutros documentos.
- No libro, no que se tiran os valores doutros ficheiros, vaia á pestana "Datos". Fai clic na icona "Editar ligazóns"que se atopa na cinta do bloque de ferramentas "Conexións". Debe notarse que se o libro actual non contén ligazóns a outros ficheiros, este botón está inactivo.
- Iníciase a xanela para cambiar as ligazóns. Seleccione na lista de libros relacionados (se hai varios) o ficheiro co que queremos romper a conexión. Fai clic no botón "Rompe a ligazón".
- Ábrese unha ventá de información na que hai un aviso sobre as consecuencias de novas accións. Se estás seguro do que vas facer, fai clic no botón. "Vinculacións".
- Despois diso, todas as referencias ao ficheiro especificado no documento actual serán substituídas por valores estáticos.
Método 2: Inserir valores
Pero o método anterior só convén se precisa cortar completamente todos os enlaces entre os dous libros. Que facer se quere desconectar as táboas relacionadas que están dentro do mesmo ficheiro? Podes facelo copiando os datos e logo pegándoo no mesmo lugar que os valores.Por certo, pódese usar o mesmo método para romper a conexión entre intervalos de datos separados de diferentes libros sen romper a conexión xeral entre ficheiros. Vexamos como funciona este método na práctica.
- Seleccione o intervalo no que queremos eliminar a ligazón a outra táboa. Prema nel co botón dereito do rato. No menú que se abre, selecciona o elemento "Copiar". No canto destas accións, pode escribir unha combinación de teclas alternativas. Ctrl + C.
- Entón, sen eliminar a selección do mesmo fragmento, volvemos premer nel co botón dereito do rato. Esta vez na lista de accións facemos clic na icona "Valores"que se coloca nun grupo de ferramentas "Opcións de inserción".
- Despois diso, todos os enlaces no intervalo seleccionado substituiranse por valores estáticos.
Como podes ver, Excel ten métodos e ferramentas para conectar varias táboas xuntas. Neste caso, os datos tabulares poden estar noutras follas e mesmo en diferentes libros. Se é necesario, esta conexión pode romperse facilmente.
