Ao executar certas tarefas no Excel, às vezes tem que lidar com várias tabelas que também estão relacionadas entre si. Ou seja, os dados de uma tabela são apertados para os outros e os valores em todas as tabelas relacionadas são recalculados quando são alterados.
As tabelas relacionadas são muito convenientes de usar para lidar com uma grande quantidade de informações. Coloque todas as informações em uma mesa, além disso, se não é homogênea, não muito conveniente. É difícil trabalhar com tais objetos e procurar por eles. O problema especificado é projetado apenas para eliminar tabelas relacionadas, as informações entre as quais são distribuídas, mas ao mesmo tempo é interconectada. Tabelas relacionadas podem não ser apenas dentro de uma folha ou um livro, mas também para ser localizado em livros separados (arquivos). As últimas duas opções na prática são mais usadas, uma vez que o objetivo desta tecnologia é apenas se afastar do acúmulo de dados, e sua assentimento em uma página não resolve fundamentalmente. Vamos aprender como criar e como trabalhar com esse tipo de gerenciamento de dados.
Criando tabelas relacionadas
Primeiro de tudo, vamos nos concentrar na pergunta, de que maneira é possível criar uma conexão entre várias tabelas.Método 1: Fórmula de tabelas de ligação direta
A maneira mais fácil de ligar dados é o uso de fórmulas em que há referências a outras tabelas. É chamado de ligação direta. Esse método é intuitivo, já que o liga quase como criar referências a dados em uma matriz de tabela.
Vamos ver como o exemplo que você pode formar comunicação por ligação direta. Temos duas mesas em duas folhas. Na mesma tabela, o salário é calculado usando a fórmula multiplicando as taxas de funcionários para um único coeficiente.
Na segunda folha há uma faixa de mesa em que há uma lista de funcionários com seu salário. A lista de funcionários em ambos os casos é apresentada em uma ordem.
É necessário fazer com que os dados sobre as apostas da segunda folha se aperte nas células correspondentes do primeiro.
- Na primeira folha, alocamos a primeira célula da coluna "Aposta". Nós colocamos nele o sinal "=". Em seguida, clique no rótulo "Folha 2", que é colocado à parte esquerda da interface do Excel sobre a barra de status.
- Existe um movimento na segunda área do documento. Clique na primeira célula da coluna "Aposta". Em seguida, clique no botão Enter no teclado para inserir os dados na célula na qual o sinal "igual" foi instalado anteriormente.
- Em seguida, há uma transição automática para a primeira folha. Como podemos ver, o valor do primeiro funcionário da segunda tabela é puxado para a célula correspondente. Ao instalar o cursor em uma célula contendo uma aposta, vemos que a fórmula usual é usada para exibir dados na tela. Mas na frente das coordenadas da célula, de onde os dados são de saída, há uma expressão "List2!", Que indica o nome da área do documento onde eles estão localizados. A fórmula geral no nosso caso é assim:
= List2! B2
- Agora você precisa transferir dados sobre as taxas de todos os outros funcionários da empresa. Claro, isso pode ser feito da mesma maneira que cumprimos a tarefa para o primeiro funcionário, mas considerando que ambas as listas de funcionários estão localizadas na mesma ordem, a tarefa pode ser significativamente simplificada e acelerada pela sua decisão. Isso pode ser feito simplesmente copiando a fórmula para o intervalo abaixo. Devido ao fato de que as referências ao Excel são relativas, ao copiar seus valores, a mudança dos valores é deslocada de que precisamos. O procedimento de cópia em si pode ser feito usando um marcador de enchimento.
Então, colocamos o cursor para a área inferior direita do elemento com a fórmula. Depois disso, o cursor deve converter para o marcador de enchimento na forma de uma cruz preta. Realizamos o grampo do botão esquerdo do mouse e puxamos o cursor para o número da coluna.
- Todos os dados de uma coluna semelhante em uma folha 2 foram puxados em uma tabela em uma folha 1. Quando os dados são alterados em uma folha 2, elas serão alteradas automaticamente no primeiro.
Método 2: Usando o Blundering of Operators Index - Pesquisar
Mas o que fazer se a lista de funcionários em matrizes de tabela não estiver localizada na mesma ordem? Nesse caso, como declarado anteriormente, uma das opções é instalar a relação entre cada uma dessas células que devem ser associadas manualmente. Mas é adequado, exceto por pequenas mesas. Para intervalos enormes, essa opção, na melhor das hipóteses, levará muito tempo na implementação, e na pior das hipóteses - na prática, geralmente será irrealista. Mas esse problema pode ser resolvido usando um monte de operadores - pesquisa. Vamos ver como isso pode ser feito por lavrando os dados nas tabelas sobre o qual a conversa estava no método anterior.
- Destacamos o primeiro elemento da coluna "Aposta". Vá para o Assistente de funções clicando no ícone "Inserir função".
- No assistente de funções no grupo "links e matrizes" encontramos e alocamos o nome "índice".
- Este operador tem duas formas: um formulário para trabalhar com matrizes e referência. No nosso caso, a primeira opção é necessária, portanto, na próxima janela de seleção de formulário que abre, selecione-a e clique no botão "OK".
- O índice dos argumentos do operador começam a funcionar. A tarefa da função especificada é a saída do valor localizado no intervalo selecionado na linha com o número especificado. Índice geral do operador da fórmula tal:
= Índice (matriz; number_name; [number_stolbits])
A "matriz" é um argumento contendo o intervalo do intervalo da qual extrairemos informações pelo número da linha especificada.
"Número da linha" é um argumento que é o número dessa linha. É importante saber que o número da linha deve ser especificado não em relação ao documento inteiro, mas apenas em relação à matriz alocada.
O "número da coluna" é um argumento que é opcional. Para resolver o especificamente da nossa tarefa, não vamos usá-lo e, portanto, não é necessário descrevê-lo separadamente.
Colocamos o cursor no campo "Array". Depois disso, vá para a folha 2 e, segurando o botão esquerdo do mouse, selecione todo o conteúdo da coluna "Taxa".
- Depois que as coordenadas forem exibidas na janela do operador, colocamos o cursor no campo "Número da linha". Vamos retirar esse argumento usando o operador de pesquisa. Portanto, clique em um triângulo que esteja localizado à esquerda da string de função. Uma lista de operadores recém-usados é aberta. Se você encontrar o nome "Search Company" entre eles, você pode clicar nele. No caso oposto, clique no último ponto da lista - "Outras funções ...".
- A janela do Assistente de Janela Standard é iniciada. Vá para isso no mesmo grupo "links e matrizes". Desta vez na lista, escolha o item "Search Company". Execute um clique no botão "OK".
- A ativação dos argumentos dos argumentos do operador de busca é realizada. A função especificada é projetada para gerar o número do valor em uma matriz específica por seu nome. É graças a esse recurso que calculamos o número de uma string de um valor específico para a função de função. A sintaxe da placa de pesquisa é apresentada:
= Board de pesquisa (pesquisa_name; visualização__nassive; [Type_Station])
"O desejado" é um argumento contendo o nome ou endereço da célula do intervalo de terceiros em que está localizado. É a posição desse nome no intervalo de alvo e deve ser calculada. No nosso caso, o papel do primeiro argumento será referenciado às células em uma folha 1, no qual os funcionários estão localizados.
"Array listul" é um argumento, que é uma referência a uma matriz, que realiza a busca pelo valor especificado para determinar sua posição. Teremos essa função para executar o endereço da coluna "Nome" em uma folha 2.
"Tipo de comparação" - um argumento que é opcional, mas, ao contrário do operador anterior, esse argumento opcional será necessário. Indica como corresponder ao operador é o valor desejado com uma matriz. Este argumento pode ter um dos três valores: -1; 0; 1. Para matrizes desordenadas, selecione a opção "0". Esta opção é adequada para o nosso caso.
Portanto, prossiga para preencher os campos da janela de argumentos. Colocamos o cursor no campo "Valor Fogular", clique na primeira coluna "Nome" da célula em uma folha 1.
- Depois que as coordenadas forem exibidas, defina o cursor no campo "Listing Massive" e vá para o rótulo "Folha 2", que está localizado na parte inferior da janela do Excel acima da barra de status. Clementar o botão esquerdo do mouse e realce o cursor todas as células da coluna "Nome".
- Depois que suas coordenadas forem exibidas no campo "Listing Massive", vá para o campo "Tipo de mapeamento" e defina o número "0" no teclado. Depois disso, voltamos novamente ao campo "olhando pela matriz". O fato é que vamos realizar copiar a fórmula, como fizemos no método anterior. Haverá uma mudança de endereços, mas aqui as coordenadas da matriz vistos precisamos segurar. Ele não deve mudar. Destacamos as coordenadas com o cursor e clique na tecla F4 Function. Como você pode ver, o cifrão apareceu antes das coordenadas, o que significa que referência de parente transformada em absoluto. Em seguida, clique no botão "OK".
- O resultado é exibido na primeira célula da coluna "Aposta". Mas antes de copiar, precisamos consertar outra área, nomeadamente o primeiro índice de função de argumentos. Para fazer isso, selecione o elemento Coluna, que contém uma fórmula e vá para a seqüência de fórmula. Aloque o primeiro argumento do índice do operador (B2: B7) e clique no botão F4. Como você pode ver, o cifrão apareceu perto das coordenadas selecionadas. Clique na tecla Enter. Em geral, a fórmula assumiu a seguinte forma:
= Índice (Sheet2! $ B $ 2: $ B $ 7; Placa de pesquisa (folha1! A4; Lista2! $ A $ 2: $ A $ 7; 0)
- Agora você pode copiar usando um marcador de enchimento. Nós chamamos da mesma maneira que falamos mais cedo e estendemos até o final do intervalo tabular.
- Como você pode ver, apesar do fato de que a ordem de strings em duas tabelas relacionadas não coincide, mesmo assim, todos os valores são apertados de acordo com os nomes dos trabalhadores. Isso foi conseguido graças ao uso da combinação de busca de índice de operadores.
Método 4: Inserção Especial
Arrays da tabela de gravata no Excel também podem usar uma inserção especial.
- Selecione os valores que você deseja "apertar" para outra tabela. No nosso caso, esta é a faixa de coluna "aposta" em uma folha 2. Clique no fragmento dedicado com o botão direito do mouse. Na lista que abre, selecione o item "Copiar". Uma combinação alternativa é a combinação de teclas CTRL + C. Depois disso, nos mudamos para a folha 1.
- Movendo-se para a área do livro que você precisa, alocar as células nas quais os valores precisarão ser apertados. No nosso caso, esta é a coluna "lance". Clique no fragmento dedicado com o botão direito do mouse. No menu de contexto na barra de ferramentas "Inserir parâmetros", clique no ícone "Inserir Comunicação".
Há também uma alternativa. Ele, a propósito, é o único para versões mais antigas do Excel. No menu de contexto, trazemos o cursor para o item "Inserção Especial". No menu adicional que abre, selecione a posição com o mesmo nome.
- Depois disso, uma janela especial de inserção é aberta. Clique no botão "Inserir Comunicação" no canto inferior esquerdo da célula.
- Seja qual for a opção que você escolher, os valores de uma matriz de mesa serão inseridos em outro. Ao alterar os dados na fonte, eles também serão automaticamente alterados no intervalo inserido.
Lição: Inserção especial no Excel
Método 5: Comunicação entre tabelas em vários livros
Além disso, você pode organizar um link entre as áreas de tabela em livros diferentes. Isso usa uma ferramenta especial de inserção. As ações serão absolutamente semelhantes àquelas que consideradas no método anterior, exceto que a navegação durante as fórmulas não terá entre as áreas de um livro, mas entre os arquivos. Naturalmente, todos os livros relacionados devem ser abertos.
- Selecione o intervalo de dados a ser transferido para outro livro. Clique no botão direito do mouse e selecione a posição "Copiar" no menu aberto.
- Em seguida, passamos para o livro no qual esses dados devem ser inseridos. Selecione o intervalo desejado. Clique no botão direito do mouse. No menu de contexto no grupo "Inserir configurações", selecione o item "Inserir Communication".
- Depois disso, os valores serão inseridos. Ao alterar os dados no livro de origem, uma matriz da tabela do livro de trabalho os apertará automaticamente. E não é necessário garantir que ambos os livros estão abertos. É o suficiente para abrir uma única pasta de trabalho, e irá automaticamente adaptar dados de um documento relacionado fechado se houvesse alterações anteriores nele.
Mas deve-se notar que, neste caso, a inserção será produzida sob a forma de uma matriz inalterada. Ao tentar alterar qualquer célula com os dados inseridos, uma mensagem será preenchida informando sobre a incapacidade de fazer isso.
Mudanças em tal matriz associada a outro livro só podem quebrar a conexão.
Ruptures de título entre tabelas
Às vezes é necessário quebrar a conexão entre as tabelas. O motivo para isso pode ser o acima descrito quando você deseja alterar uma matriz inserida de outro livro e simplesmente a relutância do usuário para que os dados da mesma tabela sejam atualizados automaticamente do outro.Método 1: Quebras de comunicação entre livros
Para quebrar a conexão entre livros em todas as células, realizando realmente uma operação. Nesse caso, os dados nas células permanecerão, mas eles já serão valores não atualizados que não dependem de outros documentos.
- No livro no qual os valores de outros arquivos são apertados, vá para a guia Dados. Clique no ícone "Alterar links", que está localizado na fita na barra de ferramentas "Conexão". Deve-se notar que, se o livro atual não contiver conexões com outros arquivos, esse botão estiver inativo.
- A janela Alterar Link é lançada. Selecione a partir da lista de livros relacionados (se houver vários deles) o arquivo com o qual queremos quebrar a conexão. Clique no botão "Quebre a conexão".
- Uma janela de informações é aberta, o que fornece um aviso sobre as conseqüências de outras ações. Se você tiver certeza de que vai fazer, clique no botão "Break Communication".
- Depois disso, todas as referências ao arquivo especificado no documento atual serão substituídas por valores estáticos.
Método 2: Inserindo valores
Mas o método acima é adequado apenas se você precisar quebrar completamente todos os links entre os dois livros. E se você precisar desconectar as tabelas associadas dentro do mesmo arquivo? Você pode fazer isso copiando os dados e, em seguida, inserindo o mesmo lugar que os valores. A propósito, esse método pode ser rupturado entre os intervalos individuais de dados de vários livros sem quebrar uma relação comum entre os arquivos. Vamos ver como esse método funciona na prática.
- Destacamos o intervalo em que desejamos excluir a comunicação com outra tabela. Clique no botão direito do mouse. No menu Abrir, selecione o item "Copiar". Em vez das ações especificadas, você pode discar uma combinação alternativa de teclas de atalho Ctrl + C.
- Em seguida, sem remover a seleção do mesmo fragmento, novamente clique nele com o botão direito do mouse. Desta vez na lista de ação, clique no ícone "Valor", que é publicado no grupo Inserir Parâmetros.
- Depois disso, todas as referências no intervalo dedicado serão substituídas por valores estáticos.
Como você pode ver, o Excel tem maneiras e ferramentas para associar várias mesas entre si. Ao mesmo tempo, os dados tabulares podem estar em outras folhas e até mesmo em livros diferentes. Se necessário, esta conexão pode ser facilmente quebrada.