Táboas relacionadas en Excel: instrucións detalladas

Ao realizar certas tarefas en Excel ás veces teñen que tratar con varias táboas que tamén están relacionadas entre si. É dicir, os datos dunha táboa están axustados a outros e os valores en todas as táboas relacionadas son recalculadas cando se cambian.

As táboas relacionadas son moi convenientes de usar para manexar unha gran cantidade de información. Coloca toda a información nunha táboa, ademais, se non é homoxénea, non é moi conveniente. É difícil traballar con tales obxectos e buscar por eles. O problema especificado está só deseñado para eliminar as táboas relacionadas, a información entre as que se distribúe, pero ao mesmo tempo está interconectada. As táboas relacionadas poden non ser só dentro dunha folla ou un libro, senón tamén estar situado en libros separados (ficheiros). As dúas últimas opcións na práctica úsanse máis frecuentemente, xa que o propósito desta tecnoloxía é só para afastarse da acumulación de datos e as súas axentes nunha páxina non resolven un xeito fundamentalmente. Aprendemos a crear e como traballar con tal tipo de xestión de datos.

Creación de táboas relacionadas

Primeiro de todo, imos centrarnos na pregunta, de que forma é posible crear unha conexión entre varias táboas.

Método 1: Fórmula de táboas de vinculación directa

A forma máis sinxela de vincular datos é o uso de fórmulas nas que hai referencias a outras táboas. Chámase vinculante directa. Este método é intuitivo, xa que cando se une case como crear referencias a datos nunha matriz de mesa.

Vexamos como o exemplo pode formar a comunicación por unha vinculación directa. Temos dúas táboas en dúas follas. Na mesma mesa, o salario calcúlase usando a fórmula multiplicando as taxas de empregados por un único coeficiente.

Na segunda folla hai un rango de mesa no que hai unha lista de empregados co seu salario. A lista de empregados en ambos casos preséntase nunha orde.

É necesario facer que os datos sobre as apostas da segunda folla se axusten ás células correspondentes do primeiro.

1. Na primeira folla, destinamos a primeira cela da columna "Aposta". Poñemos nel o sinal "=". A continuación, faga clic na etiqueta "Sheet 2", que se coloca na parte esquerda da interface de Excel sobre a barra de estado.



2. Hai un movemento na segunda área do documento. Fai clic na primeira cela da columna "Aposta". A continuación, faga clic no botón Intro no teclado para introducir os datos na cela na que o sinal "igual" estaba previamente instalado.



3. A continuación, hai unha transición automática á primeira folla. Como podemos ver, o valor do primeiro empregado da segunda táboa é levado á cela correspondente. Ao instalar o cursor nunha cela que contén unha aposta, vemos que a fórmula habitual úsase para mostrar datos na pantalla. Pero diante das coordenadas da cela, de onde os datos están de saída, hai un "list2" Expresión, que indica o nome da zona do documento onde están localizados. A fórmula xeral no noso caso parece así:

   = LISTA2! B2



4. Agora tes que transferir datos sobre as taxas de todos os outros empregados da empresa. Por suposto, isto pódese facer da mesma forma que cumprimos a tarefa do primeiro empregado, pero tendo en conta que ambas listas de empregados están situadas no mesmo orde, a tarefa pode ser significativamente simplificada e acelerada pola súa decisión. Isto pódese facer simplemente copiando a fórmula ao intervalo de abaixo. Debido ao feito de que as referencias a Excel son relativas, ao copiar os seus valores, o cambio de valores cambia que necesitamos. O propio procedemento de copia pode realizarse usando un marcador de recheo.

   Entón, poñemos o cursor á área inferior dereita do elemento coa fórmula. Despois diso, o cursor debe converterse no marcador de recheo en forma de cruz negra. Realizamos a abrazadera do botón esquerdo do rato e tira o cursor ao número da columna.



5. Todos os datos dunha columna similar nunha folla 2 foron levados a unha táboa nunha folla 1. Cando os datos cambian nunha folla 2, cambiarán automaticamente o primeiro.

Método 2: Usando o erro de Índice de operadores: busca

Pero que facer se a lista de empregados en matrices de mesa non está situada na mesma orde? Neste caso, como se indicou anteriormente, unha das opcións é instalar a relación entre cada unha destas células que deben asociarse manualmente. Pero é adecuado, excepto por pequenas mesas. Para os intervalos masivos, esta opción fará mellor ter moito tempo na implementación e, no peor dos casos, en práctica, xeralmente non será realista. Pero este problema pode ser resolto usando un grupo de operadores Índice - busca. Vexamos como se pode facer a través da tilación dos datos nas táboas sobre as que a conversa estaba no método anterior.

1. Destacamos o primeiro elemento da columna "Aposta". Ir ao asistente de funcións premendo na icona "Inserir función".



2. No asistente de funcións no grupo "Ligazóns e arrays" atopamos e reservamos o nome "Índice".



3. Este operador ten dúas formas: un formulario para traballar con arrays e referencia. No noso caso, é necesaria a primeira opción, polo que na seguinte fiestra de selección de formularios que se abre, seleccione-lo e faga clic no botón "Aceptar".



4. O índice de argumentos do operador comeza a funcionar. A tarefa da función especificada é a saída do valor situado no intervalo seleccionado na liña co número especificado. Índice xeral do operador de fórmula tal:

   = Índice (matriz; number_name; [number_stolbits])

   A "matriz" é un argumento que contén o rango do rango desde o que extraeremos a información polo número da fila especificada.

   "Número de fila" é un argumento que é o número desta liña. É importante saber que o número de liña debe especificarse non en relación a todo o documento, senón só en relación á matriz asignada.

   O "número da columna" é un argumento que é opcional. Para resolver o específico da nosa tarefa, non o usaremos e, polo tanto, non é necesario describilo por separado.

   Poñemos o cursor no campo "Array". Despois diso, vai á folla 2 e, mantendo o botón esquerdo do rato, seleccione o contido completo da columna "Prezo".



5. Despois de que se mostren as coordenadas na xanela do operador, poñemos o cursor no campo "Número de fila". Retiraremos este argumento usando o operador de busca. Polo tanto, fai clic nun triángulo situado á esquerda da cadea de funcións. Abre unha lista de operadores recentemente utilizados. Se atopa o nome "Buscar empresa" entre eles, pode facer clic nel. No caso contrario, fai clic no último punto da lista - "Outras funcións ...".



6. Iníciase a xanela de asistente de fiestra estándar. Vaia a el no mesmo grupo "Ligazóns e arrays". Esta vez na lista, elixe a "empresa de busca" do artigo. Realice un clic no botón "Aceptar".



7. A activación dos argumentos dos argumentos do operador de busca realízase. A función especificada está deseñada para producir o número de valor nunha matriz específica polo seu nome. É grazas a esta característica que calculamos o número dunha cadea de valor específico para a función función. Preséntase a sintaxe da tarxeta de busca:

   = Buscar bordo (Search_name; visualización__nassive; [type_station])

   "O desexado" é un argumento que contén o nome ou enderezo da célula do rango de terceiro no que se atopa. É a posición deste nome no rango de destino e debe calcularse. No noso caso, o papel do primeiro argumento será referenciado ás células nunha folla 1 na que se localizan os empregados.

   "Array lingüística" é un argumento, que é unha referencia a unha matriz, que realiza a busca do valor especificado para determinar a súa posición. Teremos este papel para executar a dirección da columna "Nome" nunha folla 2.

   "Tipo de comparación": un argumento que é opcional, pero, a diferenza do operador anterior, será necesario este argumento opcional. Indica como coincidir co operador é o valor desexado cunha matriz. Este argumento pode ter un dos tres valores: -1; 0; 1. Para as matrices desordenadas, seleccione a opción "0". Esta opción é adecuada para o noso caso.

   Entón, proceda a cubrir os campos da xanela do argumento. Poñemos o cursor no campo "Valor NOGULAR", faga clic na columna de "nome" da primeira cela nunha folla 1.



8. Despois de que se mostren as coordenadas, configure o cursor no campo "Listado masivo" e vaia á etiqueta "Sheet 2", que está situada na parte inferior da xanela Excel por riba da barra de estado. Clementar o botón esquerdo do rato e resaltar o cursor todas as celas da columna "Nome".



9. Despois de que as súas coordenadas se amosan no campo "Listado masivo", diríxase ao campo "Tipo de mapeamento" e configure o número "0" do teclado. Despois diso, volvemos volver ao campo "Mirando pola matriz". O feito é que imos realizar copiando a fórmula, como fixemos no método anterior. Haberá un cambio de enderezos, pero aquí as coordenadas da matriz visualizamos que necesitamos. Non debe cambiar. Destacamos as coordenadas co cursor e prema na tecla F4 FUND. Como podes ver, o sinal de dólar apareceu ante as coordenadas, o que significa que a referencia do parente converteuse en absoluta. A continuación, faga clic no botón "Aceptar".



10. O resultado móstrase na primeira cela da columna "aposta". Pero antes de copiar, necesitamos solucionar outra área, é dicir, o primeiro índice de función do argumento. Para facelo, seleccione o elemento da columna, que contén unha fórmula e trasládase á cadea de fórmulas. Asignar o primeiro argumento do Índice do operador (B2: B7) e prema no botón F4. Como podes ver, o sinal de dólar apareceu preto das coordenadas seleccionadas. Fai clic na tecla Intro. En xeral, a fórmula tomou a seguinte forma:

    = Índice (folla2 $ b $ 2: $ B $ 7; BUSCA (Sheet1! A4; List2! $ A $ 2: $ a $ 7; 0))



11. Agora podes copiar usando un marcador de recheo. Chamámolo do mesmo xeito que falamos antes e estendemos ao final do rango tabular.



12. Como podes ver, a pesar de que a orde das cordas en dúas táboas relacionadas non coincide, con todo, todos os valores están reforzados segundo os nomes dos traballadores. Isto conseguiuse grazas ao uso da combinación de busca de Índices dos operadores.

Método 4: Inserción especial

As matrices de táboa de empate en Excel tamén poden usar unha inserción especial.

1. Seleccione os valores que desexa "apertar" a outra táboa. No noso caso, este é o rango de columna "Aposta" nunha folla 2. Fai clic no fragmento dedicado co botón dereito do rato. Na lista que se abre, seleccione o elemento "Copiar". Unha combinación alternativa é a combinación de teclas Ctrl + C. Despois diso, pasamos á folla 1.



2. Movéndose á área do libro que necesitas, asignar as células nas que terán que apretar os valores. No noso caso, esta é a columna "oferta". Fai clic sobre o fragmento dedicado co botón dereito do rato. No menú contextual na barra de ferramentas "Inserir parámetros", faga clic na icona "Inserir comunicación".

   

   Hai tamén unha alternativa. El, por certo, é o único para versións máis antigas de Excel. No menú contextual, traemos o cursor ao elemento "Inserir especial". No menú adicional que se abre, seleccione a posición co mesmo nome.



3. Despois diso, ábrese unha xanela de inserción especial. Fai clic no botón "Inserir comunicación" na esquina inferior esquerda da cela.



4. Sexa cal for a opción que elixas, os valores dunha matriz de mesa serán inseridos noutro. Ao cambiar de datos na fonte, tamén cambiarán automaticamente no rango inserido.

Lección: Inserción especial en Excel

Método 5: Comunicación entre táboas en varios libros

Ademais, pode organizar unha conexión entre as áreas de mesa en diferentes libros. Isto usa unha ferramenta de inserción especial. As accións serán absolutamente similares ás que consideramos no método anterior, agás que a navegación durante as fórmulas non terá entre as áreas dun libro, senón entre os ficheiros. Por suposto, deben abrirse todos os libros relacionados.

1. Seleccione o intervalo de datos para ser transferido a outro libro. Fai clic no botón dereito do rato e selecciona a posición "Copiar" no menú aberto.



2. A continuación, pasamos ao libro no que se deben inserir estes datos. Seleccione o intervalo desexado. Fai clic co botón dereito do rato. No menú contextual no grupo "Inserir Configuración", seleccione o elemento "Inserir Comunicación".



3. Despois diso, os valores serán inseridos. Ao cambiar de datos no libro de orixe, unha matriz de mesa do libro de traballo axústelos automaticamente. E non é nada necesario asegurar que ambos libros están abertos. É o suficiente para abrir unha única carpeta de traballo, e que vai datos automaticamente tailage dun documento relacionado pechado se houbo cambios anteriores na mesma.

Pero hai que ter en conta que neste caso a inserción será producida en forma de matriz inalterada. Ao tentar cambiar calquera cela cos datos inseridos, unha mensaxe estará poboada informando sobre a incapacidade de facelo.

Os cambios nunha matriz asociada a outro libro só poden romper a conexión.

Breaks de título entre as táboas

Ás veces é necesario romper a conexión entre as táboas. A razón para isto pode ser o anterior descrito cando desexa cambiar unha matriz inserida desde outro libro e simplemente a renuencia do usuario para que os datos da mesma táboa se actualicen automaticamente a partir do outro.

Método 1: rompe a comunicación entre libros

Para romper a conexión entre libros en todas as celas, realizando realmente unha operación. Neste caso, permanecerán os datos nas células, pero xa serán valores estatais non actualizados que non dependen doutros documentos.

1. No libro no que se reforzarán os valores doutros ficheiros, vai á pestana de datos. Fai clic na icona "Cambiar ligazóns", que está situado na cinta na barra de ferramentas "Conexión". Nótese que se o libro actual non contén conexións con outros ficheiros, entón este botón está inactivo.



2. A xanela de cambio de ligazón é lanzada. Seleccione a partir da lista de libros relacionados (se hai varios deles) o ficheiro co que queremos romper a conexión. Fai clic no botón "Rompe a conexión".



3. Abre unha xanela de información, que proporciona unha advertencia sobre as consecuencias de outras accións. Se está seguro de que vai facer, prema no botón "Break Communication".



4. Despois diso, todas as referencias ao ficheiro especificado do documento actual serán substituídas por valores estáticos.

Método 2: Inserción de valores

Pero o método anterior é adecuado só se precisa romper completamente todas as ligazóns entre os dous libros. E se precisa desconectar as táboas asociadas dentro do mesmo ficheiro? Podes facelo copiando os datos e, a continuación, inserir o mesmo lugar que os valores. Por certo, este método pode ser ruptrado entre os rangos de datos individuais de varios libros sen romper unha relación común entre os ficheiros. Vexamos como funciona este método na práctica.

1. Destacamos o rango no que desexamos eliminar a comunicación con outra táboa. Fai clic no botón dereito do rato. No menú aberto, seleccione o elemento "Copiar". En lugar das accións especificadas, pode marcar unha combinación alternativa de teclas rápidas Ctrl + C.



2. A continuación, sen eliminar a selección do mesmo fragmento, de novo prema nel co botón dereito do rato. Nesta ocasión na lista de acción, faga clic na icona "Valor", que está publicada no grupo de parámetros de inserción.



3. Despois diso, todas as referencias do rango dedicado serán substituídas por valores estáticos.

Como podes ver, Excel ten formas e ferramentas para asociar varias táboas entre si. Ao mesmo tempo, os datos tabulares poden estar noutras follas e mesmo en libros diferentes. Se é necesario, esta conexión pode ser facilmente rota.