Ao realizar certas tarefas en Excel, ás veces tes que tratar con varias táboas, que tamén están relacionadas. É dicir, os datos dunha táboa tíranse noutra e cando se cambian, os valores recalculanse en todos os intervalos de táboas relacionados.
As táboas ligadas son moi convenientes de usar para procesar unha gran cantidade de información. Colocar toda a información nunha táboa, ademais, se non é homoxénea, non é moi conveniente. É difícil traballar con tales obxectos e buscalos. O problema indicado está deseñado para ser eliminado por táboas ligadas, a información entre as que se distribúe, pero ao mesmo tempo está interconectada. Os intervalos de táboas enlazadas pódense localizar non só nunha folla ou nun libro, senón que tamén se poden localizar en libros (ficheiros) separados. As dúas últimas opcións son máis frecuentemente empregadas na práctica, xa que o obxectivo desta tecnoloxía é afastarse da acumulación de datos, e amontalas nunha páxina non resolve fundamentalmente o problema. Imos aprender a crear e como traballar con este tipo de xestión de datos.
Creación de táboas ligadas
Primeiro de todo, analicemos a cuestión de que métodos hai unha oportunidade para crear unha relación entre os distintos intervalos de táboas.
Método 1: enlaza directamente táboas cunha fórmula
O xeito máis sinxelo de vincular datos é usar fórmulas que fan referencia a outros intervalos de táboas. Chámase encadernación directa. Este método é intuitivo, xa que con el o enlace realízase case do mesmo xeito que a creación de ligazóns a datos nunha matriz de táboas.
Vexamos como, por exemplo, un enlace pode formarse por unión directa. Temos dúas táboas en dúas follas. Nunha táboa, a nómina calcúlase mediante a fórmula multiplicando a taxa de empregados por un único coeficiente para todos.
Na segunda folla atópase unha táboa que contén unha lista de empregados cos seus salarios. A lista de empregados en ambos os casos preséntase na mesma orde.
Hai que asegurarse de que os datos sobre as taxas da segunda folla sexan tirados nas celas correspondentes da primeira.
- Na primeira folla, seleccione a primeira cela da columna Poxa. Colocamos un cartel nel "=". A continuación, faga clic no atallo "Folla 2", que está situado no lado esquerdo da interface de Excel enriba da barra de estado.
- Desprázase á segunda área do documento. Facemos clic na primeira cela da columna Poxa. Despois faga clic no botón Introduza no teclado para introducir datos na cela na que se estableceu o sinal anteriormente igual.
- Despois hai unha transición automática á primeira folla. Como podes ver, a taxa do primeiro empregado da segunda táboa tácase na cela correspondente. Colocando o cursor na cela que contén a aposta, vemos que a fórmula habitual se usa para mostrar datos na pantalla. Pero antes das coordenadas da cela desde onde saen 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 parece a seguinte:
= Folla2! B2 - Agora necesitas transferir os datos sobre as taxas de todos os demais empregados da empresa. Por suposto, isto pódese facer do mesmo xeito que completamos a tarefa para o primeiro empregado, pero dado que ambas listas de empregados están ordenadas na mesma orde, a tarefa pode simplificarse significativamente e acelerar a súa solución. Pódese facer simplemente copiando a fórmula no intervalo seguinte. Debido a que as ligazóns en Excel son relativas por defecto, cando se copian, os valores son desprazados, que é o que necesitamos. O propio procedemento de copia pódese facer usando o marcador de recheo.
Entón, coloque o cursor na área inferior dereita do elemento coa fórmula. Despois diso, o cursor debe converterse nun marcador de recheo en forma de cruz negra. Fija o botón esquerdo do rato e arrastre o cursor ata o fondo da columna.
- Todos os datos dunha columna similar activada Folla 2 foron levados a unha mesa Folla 1. Ao cambiar os datos a Folla 2 cambiarán automaticamente no primeiro.
Método 2: empregando un montón de operadores INDICE - BUSCAR
Pero e se a lista de empregados das matrices non está na mesma orde? Neste caso, como mencionamos anteriormente, unha das opcións é establecer unha conexión entre cada unha desas celas que deberían conectarse manualmente. Pero isto só é adecuado para mesas pequenas. Para os intervalos masivos, tal opción no mellor dos casos levará moito tempo para implementala e, no peor dos casos, na práctica non será viable. Pero este problema pódese resolver usando unha chea de operadores ÍNDICE - BÚSQUEDA. Vexamos como se pode facer isto ligando os datos nos intervalos de táboas que se discutiron no método anterior.
- Seleccione o primeiro elemento de columna Poxa. Vaia a Asistente de característicaspremendo na icona "Función de inserción".
- En Asistente de funcións no grupo Referencias e Arrays busque e resalte o nome ÍNDICE.
- Este operador ten dúas formas: un formulario para traballar con matrices e outro de referencia. No noso caso, a primeira opción é necesaria, polo tanto, na seguinte xanela para seleccionar o formulario que se abre, seleccionalo e faga clic no botón "Está ben".
- Lanzouse a xanela de argumentos do operador ÍNDICE. A tarefa desta función é producir un valor que se atopa no rango seleccionado na liña co número especificado. Fórmula xeral do operador ÍNDICE tal é:
= ÍNDICE (matriz; número de fila; [número de columna])Array - un argumento que conteña a dirección do intervalo do que extraeremos información polo número da liña especificada.
Número de liña - o argumento, que é o número desta mesma liña. É importante saber que o número de liña non se debe especificar en relación ao documento completo, senón só relativo á matriz seleccionada.
Número de columna - un argumento que é opcional. Non o usaremos para resolver o noso problema específico e, polo tanto, non é necesario describir a súa esencia por separado.
Pon o cursor no campo Array. Despois diso, vai a Folla 2 e mantendo o botón esquerdo do rato, selecciona todo o contido da columna Poxa.
- Despois de que as coordenadas aparezan na xanela do operador, coloque o cursor no campo Número de liña. Emitiremos este argumento usando o operador BÚSQUEDA. Polo tanto, facemos clic no triángulo, que está situado á esquerda da liña de función. Ábrese unha lista de operadores usados recentemente. Se atopas un nome entre eles "BUSCAR"entón podes facer clic nel. Se non, fai clic no último elemento da lista - "Outras funcións ...".
- A xanela estándar inicia Asistentes de funcións. Pasamos nel ao mesmo grupo Referencias e Arrays. Esta vez, seleccione o elemento da lista "BUSCAR". Fai clic no botón. "Está ben".
- A xanela de argumentos do operador está activada BÚSQUEDA. A función especificada está destinada a mostrar o seu nome o número dun valor nunha matriz específica. Grazas a esta función, calcularemos o número de liña dun certo valor para a función ÍNDICE. Sintaxe BÚSQUEDA representado do seguinte xeito:
= SEARCH (valor de busca; busca_array; [tipo de combinación])"Buscando valor" - un argumento que conteña o nome ou o enderezo da cela do intervalo de terceiros no que se atopa. A posición deste nome no rango de destino debe ser calculada. No noso caso, o primeiro argumento será as referencias ás celas activadas Folla 1onde se atopan os nomes dos empregados.
Array visto - un argumento que representa unha referencia a unha matriz na que se busca o valor especificado para determinar a súa posición. A dirección da columna "xogará este papel aquí".Nome en diante Folla 2.
Tipo de coincidencia - un argumento, que é opcional, pero, a diferenza da afirmación anterior, necesitaremos este argumento opcional. Indica como o operador coincidirá co valor de busca coa matriz. Este argumento pode ter un dos tres valores: -1; 0; 1. Para as matrices non ordenadas, seleccione "0". Esta opción é axeitada para o noso caso.
Entón, imos comezar a encher os campos da xanela de argumentos. Pon o cursor no campo "Buscando valor"fai clic na primeira cela da columna "Nome" en diante Folla 1.
- Despois de que se mostren as coordenadas, estableza o cursor no campo Array visto e faga clic no atallo "Folla 2", que está situado na parte inferior da xanela de Excel enriba da barra de estado. Manteña o botón esquerdo do rato e selecciona todas as celas da columna co cursor "Nome".
- Despois de que as súas coordenadas aparezan no campo Array vistovai ao campo Tipo de coincidencia e estableza o número alí dende o teclado "0". Despois, volvemos ao campo de novo Array visto. O certo é que imos copiar a fórmula, como fixemos no método anterior. O desprazamento de enderezos producirase, pero aquí debemos fixar as coordenadas da matriz que se está a ver. Non se debe desprazar. Seleccione as coordenadas co cursor e prema a tecla de función F4. Como podes ver, o signo do dólar apareceu diante das coordenadas, o que significa que a ligazón pasou de relativa a absoluta. Despois faga clic no botón "Está ben".
- O resultado móstrase na primeira cela da columna. Poxa. Pero antes de copiar, precisamos corrixir outra área, é dicir, o primeiro argumento da función ÍNDICE. Para iso, selecciona o elemento de columna que contén a fórmula e móvese á liña de fórmulas. Seleccionamos o primeiro argumento do operador ÍNDICE (B2: B7) e fai clic no botón F4. Como podes ver, o signo do dólar apareceu preto das coordenadas seleccionadas. Fai clic no botón Introduza. En xeral, a fórmula tomou a seguinte forma:
= ÍNDICE (Folla2! $ B $ 2: $ B $ 7; BÚSQUEDA (Folla1! A4; Folla2! $ A $ 2: $ A $ 7; 0)) - Agora podes copiar usando o marcador de recheo. Chamámolo do mesmo xeito que falamos anteriormente e estendémolo ao final do intervalo da táboa.
- Como podes ver, a pesar de que a orde de filas das dúas táboas relacionadas non coincide, con todo, todos os valores son levados segundo os nomes dos empregados. Conseguiuse a través dunha combinación de operadores ÍNDICE-BÚSQUEDA.
Lea tamén:
Función EXEX en Excel
Función EXCEL en Excel
Método 3: realizar operacións matemáticas con datos relacionados
A vinculación directa dos datos tamén é boa porque permite que non só mostres valores que se amosan noutros intervalos de táboas nunha das táboas, senón que tamén realice con elas varias operacións matemáticas (adición, división, resta, multiplicación, etc.).
Vexamos como se implementa isto na práctica. Imos adiante Folla 3 os datos xerais salariais da empresa mostraranse sen reparto por parte dos empregados. Para iso, eliminaranse as taxas dos empregados Folla 2, resumido (usando a función RESUMO) e multiplica por un coeficiente usando a fórmula.
- Seleccione a cela onde se mostrará o resultado do cálculo da nómina. Folla 3. Fai clic no botón. "Función de inserción".
- A xanela debería comezar Asistentes de funcións. Vai ao grupo "Matemático" e selecciona o nome alí RESUMO. A continuación, fai clic no botón "Está ben".
- Os argumentos de función móvense á xanela RESUMO, que está deseñado para calcular a suma dos números seleccionados. Ten a seguinte sintaxe:
= SUMA (número1; número2; ...)Os campos da xanela corresponden aos argumentos da función especificada. Aínda que o seu número pode chegar a 255, só un será suficiente para o noso propósito. Pon o cursor no campo "Número 1". Fai clic no atallo "Folla 2" por riba da barra de estado.
- Despois de mudarnos á sección desexada do libro, seleccionamos a columna que se debería resumir. Facemos isto co 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. Despois faga clic no botón "Está ben".
- Despois diso, pasamos automaticamente a Folla 1. Como podes ver, o importe total das ofertas das empregadas xa se amosa no elemento correspondente.
- Pero iso non é todo. Como recordamos, o salario calcúlase multiplicando o valor da taxa por un factor. Polo tanto, volvemos seleccionar a cela na que se atopa o valor sumado. Despois pasamos á liña de fórmulas. Engade á fórmula nel un signo de multiplicación (*) e logo faga clic no elemento no que se atopa o indicador de coeficiente. Para realizar o cálculo, fai clic no botón Introduza no teclado. Como podes ver, o programa calculou o salario total da empresa.
- Volver a Folla 2 e cambiar a taxa de calquera empregado.
- Despois diso, volvemos pasar á páxina co importe total. Como podes ver, debido a cambios na táboa ligada, o resultado do salario total recalculouse automaticamente.
Método 4: inserción personalizada
Tamén pode ligar as matrices de táboas en Excel usando un inserto especial.
- Seleccionamos os valores que haberá que "tirar" noutra táboa. No noso caso, este é o rango da columna Poxa en diante Folla 2. Facemos clic no fragmento seleccionado co botón dereito do rato. Na lista que se abre, seleccione Copiar. Hai un atallo de teclado alternativo Ctrl + C. Despois pasamos a Folla 1.
- Tras mudarnos á área do libro que necesitamos, seleccionamos as celas nas que necesitaremos para sacar os valores. No noso caso, trátase dunha columna Poxa. Facemos clic no fragmento seleccionado co botón dereito do rato. No menú contextual do bloque de ferramentas Opcións de inserción fai clic na icona Pegar enlace.
Tamén hai unha alternativa. Por certo, é o único para versións máis antigas de Excel. No menú contextual, pasa o cursor "Inserción especial". No menú adicional que se abre, seleccione a posición co mesmo nome.
- Despois, ábrese a xanela de inserción especial. Fai clic no botón Pegar enlace na esquina inferior esquerda da cela.
- Calquera opción que escollas, os valores dunha matriz de táboa serán inseridos noutra. Ao cambiar os datos na fonte, tamén cambiarán automaticamente o rango inserido.
Lección: Inserir especial en Excel
Método 5: ligazón entre táboas en varios libros
Ademais, pode organizar a comunicación entre as áreas da mesa en diferentes libros. Utilízase unha ferramenta especial de inserción. As accións serán absolutamente similares ás que consideramos no método anterior, agás que terás que navegar mentres realizas fórmulas non entre áreas do mesmo libro, senón entre ficheiros. Por suposto, todos os libros relacionados deberían estar abertos.
- Seleccione o intervalo de datos que desexa transferir a outro libro. Fai clic co botón dereito sobre el e selecciona a posición no menú que se abre. Copiar.
- A continuación, mudámonos ao libro no que deberán inserir estes datos. Seleccione o rango desexado. Fai clic co botón dereito do rato No menú contextual do grupo Opcións de inserción selecciona elemento Pegar enlace.
- Despois, inseriranse os valores. Cando os datos do caderno de traballo de orixe cambien, a matriz do cadro de traballo extraeralla automaticamente. Ademais, non é necesario que ambos libros estean abertos para iso. Basta con abrir só un libro de traballo e sacará automaticamente os datos dun documento pechado ligado se se fixeron cambios antes.
Pero hai que destacar que neste caso a inserción farase como unha matriz inmutable. Cando intenta cambiar calquera cela cos datos inseridos, aparecerá unha mensaxe informándolle que é imposible facelo.
Os cambios nunha matriz asociada a outro libro só poderán realizarse rompendo a ligazón.
Ficheiro entre táboas
Ás veces é necesario romper a conexión entre intervalos de táboas. A razón para isto pode ser o caso descrito anteriormente, cando necesitas cambiar unha matriz inserida doutro libro ou simplemente a reticencia do usuario de que os datos dunha táboa se actualicen automaticamente doutra.
Método 1: romper a conexión entre libros
Pode romper a conexión entre os libros en todas as celas realizando practicamente unha operación. Ao mesmo tempo, os datos das celas permanecerán, pero xa serán valores estáticos non actualizables, que de ningún xeito dependen doutros documentos.
- No libro, no que se extraen valores doutros ficheiros, vaia á lapela "Datos". Fai clic na icona "Cambiar as comunicacións"situado na cinta da caixa de ferramentas Conexións. Nótese que se o libro actual non contén ligazóns a outros ficheiros, este botón está inactivo.
- Comeza a xanela de cambios de ligazón. Seleccionamos o ficheiro co que queremos romper a conexión da lista de libros relacionados (se hai varios). Fai clic no botón Rompe o enlace.
- Ábrese unha ventá de información na que hai un aviso sobre as consecuencias de novas accións. Se está seguro de que vai facer, faga clic no botón "Rompendo lazos".
- Despois diso, todas as ligazóns ao ficheiro especificado no documento actual substituiranse por valores estáticos.
Método 2: Inserir valores
Pero o método anterior só é adecuado se precisa romper completamente todas as ligazóns entre os dous libros. Que facer se necesitas separar táboas relacionadas que están dentro do mesmo ficheiro? Podes facelo copiando os datos e pegándoos no mesmo lugar que os valores. Por certo, do mesmo xeito, pode romper a conexión entre os intervalos de datos individuais de diferentes libros sen romper a conexión xeral entre os ficheiros. Vexamos como funciona este método na práctica.
- Seleccionamos o rango no que queremos eliminar a ligazón a outra táboa. Facemos clic nel co botón dereito do rato. No menú que se abre, seleccione Copiar. En lugar destas accións, pode escribir unha combinación alternativa de teclas de acceso rápido Ctrl + C.
- Ademais, sen eliminar a selección do mesmo fragmento, volva facer clic co botón dereito sobre el. Esta vez na lista de accións, faga clic na icona "Valores"que está situado no grupo de ferramentas Opcións de inserción.
- Despois diso, todas as ligazóns do rango seleccionado substituiranse por valores estáticos.
Como podes ver, en Excel hai xeitos e ferramentas para ligar varias táboas entre si. Ao mesmo tempo, os datos tabulares poden estar noutras follas e incluso en diferentes libros. Se é necesario, esta conexión pode romperse facilmente.
