Guru do Excel e Power BI

Criei uma planilha de controle de estoque e dentro da planilha existe uma tabela com diversos campos, sendo alguns deles com fórmulas que fazem referência à células de forma relativa. Acontece que quando transformo a região de células em uma tabela, na criação de uma nova linha as fórmulas são alteradas e passam de referência relativa para referência absoluta, invalidando os cálculos.


Esta é uma das Fórmulas =SE(H998<>"";SEERRO(PROCV(H998&"-"&CONT.SE($H$1:H998;H998)-1;$D$1:O998;10;FALSO);0);0)


Se eu estiver na linha 1001 por exemplo e criar a linha 1002 o Excel está alterando as células das linhas superiores da tabela da forma a seguir (COM ERRO):


LINHA 999 =SE(H999<>"";SEERRO(PROCV(H999&"-"&CONT.SE($H$1:H999;H999)-1;$D$1:O999;10;FALSO);0);0) 'CERTO ATÉ ENTÃO

LINHA 1000 =SE(H1000<>"";SEERRO(PROCV(H1000&"-"&CONT.SE($H$1:H1002;H1000)-1;$D$1:O1002;10;FALSO);0);0) 'ERRO

LINHA 1001 =SE(H1001<>"";SEERRO(PROCV(H1001&"-"&CONT.SE($H$1:H1002;H1001)-1;$D$1:O1002;10;FALSO);0);0) 'ERRO DE REFERÊNCIA CIRCULAR

LINHA 1002 =SE(H1002<>"";SEERRO(PROCV(H1002&"-"&CONT.SE($H$1:H1002;H1002)-1;$D$1:O1002;10;FALSO);0);0) CERTO PARA ESTA LINHA


Se eu não transformar a região em Tabela e selecionar a região da última linha e colar para a próxima não acontece o erro. A utilização como tabela facilitaria em muito a utilização, por isso acho importante.


Obrigado


Henrique

Bom dia!!

Seria bom se tivesse seu arquivo modelo!

Em qual linha é inserida essa formula?

Att

Bom dia a todos..
Henrique, 1° ponto.... na sua procv, isto aqui é o que encontrei como critério de pesquisa de sua fórmula modelo:
H999&"-"&CONT.SE($H$1:H999;H999)-1
é isto mesmo? Ficaria algo como H999-CONT.SE($H$1:H999;H999)-1
2ºponto ... até o que eu saiba, & (e-comercial) traz resultados em texto... colocando, POR EXEMPLO, número 20 em A1, sinal de menos - em A2 e número 15 em A3,... então em qualquer outra célula vc inserisse: =CONCATENAR(A1;A2;A3) o resultado esperado, talvez fosse o número 5.. que é o resultado que teríamos se digitássemos apenas =20-15 ;mas não é isto que teremos como resultado naquilo ali, mas sim: 20-15 (um texto)
3°ponto - independente das colocações anteriores, o que vc quer é somente fazer com que a range seja dinâmica e não fixa?.. então tenho um recurso que utilizei em uma outra situação semelhante: (vou colocar em imagem)

Alexandre,

Segue a planilha com as considerações.

O erro acontece quando se cria uma linha nova da tabela. O recurso do Excel para copiar as fórmulas é que está falhando.

Vidal, obrigado pelas considerações. Para você entender melhor o que está acontecendo acesse o arquivo.

"A aba está protegida sem senha. É só desproteger."

Abraço

para conseguir que a range seja dinâmica... use uma coluna auxiliar e insira as informações conforme demonstrado... usando o Ctrl+C e Ctrl+V fará com a range seja dinâmica... agora vá em sua fórmula e troque.. onde estava a range troque pela referência de célula onde as ranges estão.. vc pode fazer isso na coluna auxiliar usando a range ou toda a funão CONT.SE fica a teu critério.

Vidal. Obrigado pelas dicas, mas não é este o problema.

Neste caso a formatação que uso é propositalmente em formato "String".

O problema está acontecendo no momento em que se insere uma linha na tabela, onde o Excel faz a "cópia" das fórmulas que encontram-se nas colunas da região superior da tabela e cola para a linha nova. Neste caso é que ele está se perdendo e alterando a fórmula das 2 linhas superiores da tabela e resultando em ERRO.

Abra o arquivo que contém mais detalhes.

pois é.. acho que não estou compreendendo direito a situação Henrique.. eu até abri a planilha e simulei a situação: inseri uma nova linha entre as células vermelhas indicadas por ti..e copiei as fórmulas para baixo.. mas aqui pra mim deu sequência. Veja:

Vidal,

O problema é exatamente este!

Se eu não utilizar a facilidade do Excel de atribuição de uma região da planilha à uma tabela e efetuar cópia e colagem normal com <CTRL>+C e <CTRL>+V vai funcionar normalmente, mas o erro acontece somente quando atribuir a região à uma TABELA. (Como isso facilita muito a utilização da planilha, eu gostaria de poder usar este recurso do Excel).

A construção da fórmula desta maneira é para poder recuperar os saldos dos itens do estoque que estão lançados nas linhas superiores, portanto a fórmula só deve executar até a linha corrente de cada linha e não até o final da tabela.

PS.: A versão do Excel utilizada é a Office 2013 Professional Plus. O erro acontece também no Office 2013 Home Edition, as demais versões não testei.

Se alguém tiver o Office 2016 e puder testar para me retornar se este erro foi corrigido por favor eu agradeço.

Henrique.. perdoe a minha ignorância, mas.. não estou entendendo esta parte quando vc diz "..atribuir a região à uma TABELA." O que seria isso? Me deu um branco...

Vidal.

É quando você formata como TABELA.



Não tenho certeza, mas acredito que este erro só acontece na versão 2013, por isso gostaria de saber a posição em outras versões do Office.

Obrigado.

Hummm.. ok! Então somente poderei ver isto após as 17h.. lá em casa.. aqui no escritório só tem Br Calc não chega aos pés do Excel.. fazer o quê não é mesmo? Se até lá essa situação pra ti não tiver tido solução eu vejo lá, ok?! Por enquanto, agradeço a explicação sobre a atribuição de uma região a uma tabela

Obrigado Vidal.

Toda ajuda é muito bem vinda!!!

Abraço a todos.

Alexandre,

Boa tarde!

Você tem alguma ideia de como sanar esta questão?

Boa tarde!!!

Sinceramente eu não estou vendo problemas

Talvez eu não tenha entendido.

Att

Creio eu que o erro pode ou não acontecer dependendo da maneira como for introduzido/adicionado valores.
Se incluir um valor manualmente no final da lista, automaticamente será "adicionado" na tabela, porem se apos essa operação adicionar uma linha (incluir) entre linha existentes, sem haver salvo a modificação anterior, então a range ficará confusa, pois aparentemente não foi incorporado a ultima inclusão.
Experimente repetir seus passos, porem salvando a planilha após a primeira inclusão ou imediatamente antes da próxima inclusão.

Pessoal,

Para melhor demonstrar o problema, segue vídeo.

Alexandre, o erro é no momento da criação de uma nova linha.

Obrigado Reinaldo. É exatamente aí que o Excel está errando. Veja o vídeo.

https://www.youtube.com/watch?v=mj9ekod ... e=youtu.be

Boa tarde!!

henrique, onde eu trabalho, não consigo ter acesso a determinados portais!!
Mas quando vc diz inserir uma linha, seria a linha da tabela (Tab no final da tabela), ou uma linha em uma guia cuja nesta mesma guia está sua tabela?


Att

Boa tarde Alexandre!

É na linha da tabela, ao final, quando adiciono novas informações o Excel faz a colagem das fórmulas de forma automática e está alterando as fórmulas das células superiores (não é sempre da mesma região, as vezes pega 2 linhas acima, outras 1 linha e até mesmo já vi a coluna inteira).

Para quem não consegue ver o vídeo no youtube, onde demonstro o problema vou passar as imagens com uma descrição mais detalhada.
EDITANDO A LINHA 39


EDITANDO A LINHA 40

Olhem como era a fórmula antes da inserção da nova linha.

PS.: Para inserir nova linha na tabela, ela tem que estar desprotegida, senão não irá inserir nova linha e o erro não vai aparecer.

EDITANDO A LINHA 40 NOVAMENTE