Solucionado (ver solução)
Solucionado
(ver solução)
8
respostas

Como ter o mesmo valor de uma tabela em outra de forma dinâmica?

Olá pessoal,

Necessito de uma ajuda. Estou desenvolvendo uma planilha de simulador de preço com base nos cursos de Excel.

A funcionalidade consiste em relacionar uma tabela com filtros e utilizar a mesma tabela com os flitros aplicados em outra aba.

Insira aqui a descrição dessa imagem para ajudar na acessibilidade

Como podem ver na imagem, tenho uma tabela onde aplicarei filtros, por produto ou por tamanho. Quero que essa tabela possa ser replicada, com os filtros, na Aba Planilha2. Como posso fazer isso?

8 respostas

Olá Luis, tudo bem? Espero que sim!

Os filtros de uma tabela não vão infuenciar em outra tabela de outra aba, ainda que os dados da segunda tabela estejam referenciando a primeira. Isso acontece porque quando aplicamos um filtro, o Excel oculta as linhas daquela aba que não desejamos visualizar, mas elas ainda estão localizadas nas mesmas células de referência.

Talvez a tabela dinâmica possa te ajudar de alguma forma. Você pode criar duas tabelas dinâmicas a partir da mesma base de dados e tentar utilizar a Conexões de relatório. Através da conexão de relatórios, duas tabelas dinâmicas podem ser filtradas através de um mesmo filtro. Sugiro assistir às seções 01 e 02 do curso Excel: tabelas dinâmicas e dashboards para que tenha entendimento de como criar uma tabela dinâmica e como fazer conexões de relatórios.

Espero que tenha tirado sua dúvida.

Estou à disposição. Bons estudos!

João, obrigado pelo retorno.

A ideia na verdade seria a primeira tabela ser editável ao usuário, para que ele possa selecionar os itens que ele deseja vender ao cliente. E a segunda aba com a tabela que ele selecionou no primeiro local, juntamente com outras informações para que ele possa montar uma proposta específica com os dados utilizados anteriormente. É possível?

Não sei se soube explicar exatamente o que gostaria.

Obrigado João!

Olá Luis, tudo bem? Espero que sim!

Eu não compreendi tão bem o problema que deseja resolver. Poderia dar mais detalhes, com um exemplo visual de como deveria ficar ao final?

Pensei em uma solução, mas dentro do meu entendimento. Há uma tabela com os dados e outra que vai ser construída a partir dos dados originais. Você pode filtrar os dados na tabela original, e ao encontrar o item, irá até a tabela na segunda planilha e selecionar o item desejado, apresentando as informações de seu desejo, que no caso de exemplo foi o valor do item.

Deixo aqui abaixo um exemplo, caso seja isso posso passar a forma de realizar através de um passo a passo.

Tabela de produtos no Excel com 4 produtos na primeira colula, 4 informações de volume na segunda coluna e 4 informações de preço na terceira coluna. Há uma segunda tabela auxiliar para filtrar o produto que desejamos retornando o valor do item

Estou à disposição para ajudar.

Olá João, achei incrível a ideia que sugeriu. O que eu estava pensando era algo um pouco diferente mas isso vai resolver o que quero implementar. Fico no aguardo do passo a passo.

Muito obrigado! Me ajudou muito!

Olá Luis, que bom que consegui encontrar algo que solucione o seu problema, ainda que de uma forma diferente.

O primeiro passo é utilizar a validação de dados com uma lista de itens. Os passos para a validação de dados são os seguintes:

1 - Primeiramente na Planilha 2, selecionamos o intervalo que desejamos para que fique "armazenado" os produtos 2 - Clicamos na opção que está na aba Dados -> Ferramentas de Dados -> Validação de dados. 3 - Selecionamos a opção Lista na caixa de combinação com o título Permitir, na nova janela que foi aberta.

Planilha Excel com indicações de como criar uma validação de dados. Está destacado o intervalo de células onde será aplicada a validação, da célula A2 até a A12, indicadas com uma seta vermelha. A opção de validação de dados está destacada através de uma seta vermelha, na aba Dados. A janela de opções de validação de dados está aberta com destaque para a opção lista na caixa de combinação com o título Permitir

Após isso, escolhemos a fonte de dados, para isso clicamos no ícone de seleção na caixa de texto com o título Fonte, mudamos para a Planilha 1 e selecionamos os dados dos produtos. Tenha certeza que a opção Menu suspenso na célula esteja selecinado. Dessa forma teremos um menu de seleção dos produtos da tabela original.

Planilha Excel com os passos para realização de uma validação de dados, sendo selecionado o intervalo de A2 a A12, selecionando a opção Validação de dados na aba Dados, escolhendo a opção lista na caixa de combinação com título Permitir e escolhendo a fonte de dados a partir da caixa de texto Fonte. A fonte de dados foi selecionada na Planilha 1, da célula A2 até a A5

O próximo passo é utilizar a fórmula PROCV para procurarmos os valores das células com menu suspenso na tabela original, retornando somente a informação de nosso desejo. Para isso, na Planilha 2, selecione a segunda célula da segunda coluna e escreva a função PROCV(A2;Planilha1!$A$1:$C$5;3;0) e copie a fórmula para as outras células logo abaixo. O primeiro parâmetro da fórmula PROCV é o valor que desejamos procurar. O segundo parâmetro da fórmula é a tabela onde queremos realizar a procura, que no caso é a nossa tabela de origem na Planilha 1. O terceiro parâmetro é a coluna em que se encontra a informação que desejamos, no caso escolhi o número 3 para retornar a 3ª coluna, referente aos preços. O último parâmetro é se a correspondência é exata ou aproximada. Como quero procurar o produto com nome exatamente igual, escolhi o valor 0, que retorna a correspondência exata.

Para que não mostre um erro na célula da fórmula quando não houver dados, usarei a fórmula SEERRO que irá retornar um texto caso apresente algum erro, colocarei o texto vazio através das aspas "". A fórmula final será SEERRO(PROCV(A2;Planilha1!$A$1:$C$5;3;0);"").

Fórmula Procv sendo utilizada na célula B2 de uma planila. O primeiro parâmetro foi escolhido como A2, o segundo parâmetro é o Planilha1!A1:C5. O terceiro parâmetro foi o valor 3 e o último parâmetro é o valor 0. Após isso a célula apresentou um erro e foi digitado a fórmula SEERRO contendo como primeiro parâmtro o Procv construído e segundo parâmetro "" 2 aspas duplas.

Você pode aplicar ao problema que está resolvendo, aplicando as devidas formatações e adequando aos dados que possui.

Caso tenha dúvidas de algum assunto, recorra aos cursos da plataforma, uma vez que eles explicam sobre as funções PROCV e sobre a validação de dados.

Bons estudos!

João, perfeito e muito bem explicado.

Porém gostaria também de relacionar os mililitros que estão na segunda coluna da planilha 1, pois eles terão preços diferentes. No caso que estou tentando, ao aplicar essa fórmula todas as opções de nome do produto acabam puxando o mesmo valor de mililitros. Como poderia resolver isso?

Envio imagens: A primeira imagem é da planilha que quero relacionar. Insira aqui a descrição dessa imagem para ajudar na acessibilidade

A segunda imagem, é o que acontece ao seguir o passo a passo. Acaba não identificando e atrelando o tamanho do produto. Insira aqui a descrição dessa imagem para ajudar na acessibilidade

solução!

Olá Luís,

Entendi agora, você precisa relacionar algo que depende não somente do nome do produto como também de uma segunda informação. Portanto é um problema um pouco mais complexo e precisamos pensar em uma solução um pouco diferente.

O PROCV funciona bem somente com registros únicos, uma vez que ele procura um texto em uma tabela e retorna o valor em uma coluna ao lado daquele texto. Ao possuir nomes repetidos, o PROCV retornará apenas o primeiro valor encontrado na tabela.

Seria interessante dessa forma, criar um código para cada item para que ele se torne único e utilizar esse código para fazer a procura no PROCV. Esse código pode ser um valor numérico, um valor com letras e números e assim por diante, desde que seja único. O problema que isso pode acarretar é que não fique intuitivo no momento de selecionar o produto, então vamos pensar em como criar um código único e intuitivo. Se juntarmos o nome do produto com o seu tamanho, esse registro se tornará diferente de todos os outros e poderemos usar no PROCV.

Novamente, vou apresentar um exemplo e você pode aplicar ao seu contexto. Talvez essa não seja a única solução para o problema e nem a melhor solução, porém foi a solução que pensei em utilizar.

Precisamo criar uma coluna à esquerda da sua tabela original, com o nome código, que contempla o nome do produto em conjunto com o tamanho do produto. Para isso, vamos usar a fórmula CONCAT e passar como primeiro argumento o nome do produto e como segundo argumento o tamanho do produto. Em versões anteriores do Excel existe a fórmula CONCATENAR. Na célula A2 escrevemos a fórmula =CONCAT(B2;C2) e copiamos para as células abaixo.

Planilha do Excel com 10 produtos , com nome do produto, tamanho do produto, uma coluna para quantidades e outra para o preço. Foi criada uma coluna à esquerda com o código do produto, que é a concatenação do nome do item com o seu tamanho, usando a função CONCAT B2;C2 na célula  A2 e a fórmula foi copiada até a célula A11

Na segunda planilha, vamos inserir a validação de dados para o nome do produto e para o tamanho, para que se torne mais simples e não ocorra erro de digitação em nenhum dos casos, que poderia trazer problemas. O passo para realizar a validação de dados para os produtos já está escrito previamente em uma resposta anterior, portanto vou seguir para a validação de dados do tamanho dos produtos. Tenha uma tabela somente com os nomes únicos dos produtos e do tamanho do produto para facilitar a fonte de dados da validação de dados.

Selecione a coluna do tamanho dos itens e selecione a opção de validação de dados, colocando como fonte os dados únicos de tamanho.

Validação de dados sendo criada em uma planilha do Excel, as células B2 a B13 foram selecionadas e a opção de validação de dados foi selecionada, a fonte de dados estava na planilha 3 de C2 a C5 contendo a lista de valores 30ml, 50ml, 250ml e 500ml

Feito isso, você pode utilizar a fórmula PROCV em conjunto com a fórmula CONCAT, não se esquecendo da fórmula SEERRO para não apresentar nenhum erro quando houver valores vazios. A fórmula =PROCV(CONCAT(A2;B2);Planilha1!$A$1:$E$11;5;0) escrita na célula C3 vai buscar o valor do preço de um produto, você pode adaptar ela conforme o seu desejo. O primeiro parâmetro da fórmula será o código do produto que consiste na concatenação (junção) do nome do produto e seu tamanho: CONCAT(A2;B2), o segundo parâmetro consiste na tabela da Planilha 1, não esqueça de colocar o símbolo de $ para travar a tabela quando for copiar a fórmula para as demais células da mesma coluna. O terceiro parâmetro é a coluna que desejamos retornar, que no caso foi a coluna 5, do preço. Por último temos o parâmetro 0 para que a correspondência seja exata.

A fórmula final com o SEERRO é =SEERRO(PROCV(CONCAT(A2;B2);Planilha1!$A$1:$E$11;5;0);"")

Função procv sendo criada na célula C2 com o primeiro parâmetro sendo CONCAT A2;B2, o segundo parâmetro sendo a tabela contida na planilha 1 de A1 até E11, o terceiro parâmetro foi o valor 5 para representar a 5ª coluna e o último parâmetro foi o valor 0 para correspondência exata

Espero que tenha ajudado.

Boa noite João!

Muito obrigado por sua colaboração! Acredito que foi uma ótima ideia e deu muito certo. Que bom que consegui externar o que precisava para você me ajudar.

Desculpe qualquer incomodo e agradeço a paciência.

Abraços!

Quer mergulhar em tecnologia e aprendizagem?

Receba a newsletter que o nosso CEO escreve pessoalmente, com insights do mercado de trabalho, ciência e desenvolvimento de software