none
Problemas com Fórmulas Matriciais em Tabelas (ListObject) RRS feed

  • Pergunta

  • Boa noite pessoal.

    Hoje me deparei com um problema que parece ser um Bug no Excel, pelo menos na versão 2010. Quando utilizo fórmulas matriciais (Ctrl+Shift+Enter) em tabelas (ListObject) o funcionamento inicial se comporta como esperado, e tudo funciona. Porém, quando a tabela é redimensionada e as fórmulas deveriam ser automaticamente expandidas, ocorre que algumas (isso mesmo, algumas!) fórmulas matriciais não expandem, ficando referenciadas ao tamanho anterior da tabela. Elas até são copiadas normalmente para as novas linhas, mas deixam de referenciar à toda coluna, ficando o cálculo incompleto. O mais estranho é que o comportamento não é o mesmo sempre e algumas linhas recebem a fórmula correta. Alguém já passou por isso, ou tem ideia de como contornar?

    Abaixo alguns exemplos:

    Observe que a fórmula se refere a toda coluna corretamente. Abaixo o que acontece quando a tabela é aumentada. Note que apenas (!) as células com indicadores verdes ficaram erradas (3 no total) e neste caso apenas as que já estavam corretas. As fórmulas adicionadas nas novas linhas estão corretas.

    Obs.: Nas imagens acima não aparece o "{ }" pelo fato de estar no modo edição, mas as fórmulas são matriciais sim!

    Obrigado.


    Filipe Magno


    • Editado FilipeMagno domingo, 27 de julho de 2014 02:50 Melhoria do texto
    domingo, 27 de julho de 2014 02:46

Respostas

  • Oi Filipe, desculpe a demora.

    Conversei com várias pessoas a respeito desse problema. Seguinte: tabelas contém bugs no Excel 2007, 2010 e 2013 e é sabido dos bugs.

    Esses bugs descobertos por você fazem parte de vários outros conjuntos de bug, que, pelo que pude ver, não serão resolvidos pelo time do Excel.

    Uma medida paliativa é você usar referências nomeadas ao invés de referências A1, não? Ainda não testei, mas creio que irá funcionar, mesmo com fórmulas matriciais.

    Obs.: referências nomeadas é, por exemplo, =SOMA(Tabela1[Quantidade]) ao invés de =SOMA(G13:G24)


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marcado como Resposta FilipeMagno sexta-feira, 5 de setembro de 2014 02:44
    sexta-feira, 5 de setembro de 2014 01:05
    Moderador

Todas as Respostas

  • Que estranho... Pelo menos ficaram marcadas as células com fórmulas fora do padrão. Menos pior. Eu ainda não consigo entender o padrão de fórmulas e formatações arrastadas automaticamente em tabelas. Até deixei de usar por isto... 

    Michel Macário

    domingo, 27 de julho de 2014 17:35
  • Pessoal, segue mais um indício que é mesmo um Bug do Excel:

    Gravei uma Macro para inserção da fórmula matricial, e em seguida apliquei a fórmula para toda a coluna da tabela. Para minha surpresa, a primeira ação funcionou como esperado, mas a segunda não:

    Range("T17").FormulaArray = _
            "=IF(RC[-1]<>"""",   MAX(IF((R6C4:R17C4=RC[-16])*(R6C5:R17C5=RC[-15]),   R6C19:R17C19,0)),   """")"

    Em seguida:

    Range("T6:T17").FormulaArray = _
            "=SE(RC[-1]<>"""",   MÁXIMO(SE((R6C4:R17C4=RC[-16])*(R6C5:R17C5=RC[-15]),   R6C19:R17C19,0)),   """")"

    Como podem ver, o segundo código possui as fórmulas em linguagem local, o que obviamente não funciona (confirmado ao tentar executar o código).

    Resumindo: se alguém souber como contornar ou corrigir o erro ficarei muito feliz com a contribuição.

    Obs.: Eu tenho duas formas de contornar parcialmente o problema, uma reescrevendo a fórmula por Macro, a cada redimensionamento, e outra criando uma UDF para substituir a fórmula matricial. Mas não gostaria de usar nenhuma dessas duas, já que imagino que deva existir uma forma melhor de contornar ou evitar esse Bug. Além disso, a solução completa fica um pouco mais complexa (tenho outras planilhas se referindo a esta tabela com fórmulas matriciais também) e gostaria de alguma outra sugestão antes de ter de ir por esse caminho.

    Vlw.


    Filipe Magno


    • Editado FilipeMagno segunda-feira, 28 de julho de 2014 02:07 Complementação da descrição.
    segunda-feira, 28 de julho de 2014 02:01
  • Bom dia Filipe,

    Vou dar uma olhada nesta situação e relatar um bug. Assim que eu receber uma resposta, volto a postar aqui.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quarta-feira, 30 de julho de 2014 09:47
    Moderador
  • Bom dia Felipe!

    Te agradeço muito pela disposição em ajudar. Se precisar, posso encaminhar a pasta de trabalho.

    Um grande abraço. Obrigado.


    Filipe Magno

    quarta-feira, 30 de julho de 2014 11:34
  • Olá Filipe

    Se puder encaminhar a pasta de trabalho, agradeço, já que não consigo reproduzir o problema de jeito nenhum.,


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quarta-feira, 30 de julho de 2014 14:05
    Moderador
  • Olá Felipe!

    Segue o Link: https://onedrive.live.com/redir?resid=AC9743CAAD52C72F!174&authkey=!AKjMKawz3jXQY68&ithint=file%2cxlsm

    Para reproduzir o erro, sugiro a Aba "Memorial para DV" que possui várias fórmula matriciais. Note que os erros não são homogêneos (às vezes alguma das coluna não apresenta erro) e sucessivos redimensionamentos provocam erros diferentes (às vezes no primeiro redimensionamento não ocorre erro). Os erros podem ser reproduzidos tanto pelo redimensionamento manual quanto pela Macro associada ao botão "Aumentar Lista" (por vezes o resultado final fica diferente).

    Obs.: Acabei de testar no Excel 2007 e também ocorre erro.

    Obs.2: O arquivo ainda não está finalizado, faltando terminar algumas ferramentas.

    Mais uma vez lhe agradeço a disposição em ajudar.


    Filipe Magno

    quarta-feira, 30 de julho de 2014 14:49
  • Boa noite Felipe.

    Gostaria de compartilhar mais um problema que verifiquei para a situação descrita. Percebi que a última linha adicionada nas tabelas sempre recebia a fórmula correta, então resolvi copiá-la para as demais manualmente através da macro de redimensionamento. Após redimensionar, acrescentei o seguinte código (depois da linha 79 e para a aba: "Memorial para DV"):

    pPlan.Range(Cells(Li, "S"), Cells(Lf + nL, "T")).Select
    pPlan.Cells(Lf + nL, "S").Activate
    Selection.FillUp

    Na primeira execução funciona perfeitamente, mas para minha surpresa a segunda execução em diante não, retornando o erro 1004. E, além de não funcionar, ainda provoca erro definitivo no Excel, de forma que só volta a funcionar corretamente ao reiniciar o aplicativo (não exibe mais as seleções de célula, como se o ScreenUpdating fosse desligado). Não basta fechar o arquivo, nem abrir outros projetos, é preciso reiniciar o aplicativo.

    Ou seja, parece que Tabelas não foram mesmo feitas para funcionar com Fórmulas Matriciais!

    Aproveitando, vc vislumbra alguma forma de contornar o uso dessas fórmulas matriciais, já que preciso fazer várias verificações condicionais?

    Obs.: Uso o Excel 2010.

    Desde já obrigado.


    Filipe Magno

    domingo, 17 de agosto de 2014 21:19
  • Oi Filipe, desculpe a demora.

    Conversei com várias pessoas a respeito desse problema. Seguinte: tabelas contém bugs no Excel 2007, 2010 e 2013 e é sabido dos bugs.

    Esses bugs descobertos por você fazem parte de vários outros conjuntos de bug, que, pelo que pude ver, não serão resolvidos pelo time do Excel.

    Uma medida paliativa é você usar referências nomeadas ao invés de referências A1, não? Ainda não testei, mas creio que irá funcionar, mesmo com fórmulas matriciais.

    Obs.: referências nomeadas é, por exemplo, =SOMA(Tabela1[Quantidade]) ao invés de =SOMA(G13:G24)


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marcado como Resposta FilipeMagno sexta-feira, 5 de setembro de 2014 02:44
    sexta-feira, 5 de setembro de 2014 01:05
    Moderador
  • Boa noite Felipe!

    Já estava desanimando de prosseguir com o projeto devido a esses bugs, já que a forma de contornar que estava vislumbrando era demasiada complicada, mas tenho que lhe dizer que sua sugestão caiu como uma luva! Funcionou perfeitamente!

    Fico triste em saber que existe um erro desse até mesmo na última versão do Excel, mas pelo menos da forma que vc propôs resolve 100% meu problema!

    Eu nunca gostei de utilizar os nomes das colunas das tabelas em fórmulas, mas até que utilizando agora me simpatizei (mas apenas para referências à toda a coluna e não apenas uma célula, rsrsrs).

    Penso que se nomeasse manualmente os dados das colunas também não encontraria problema, mas da forma que propôs é muito mais eficiente e elegante.

    Aproveitando, refiz o teste da gravação da macro: para uma única célula funciona, mas ao expandir para toda a coluna ocorre o mesmo erro, gravando com os nomes em português.

    Fico-lhe mais uma vez muito grato! Muito obrigado!


    Filipe Magno

    sexta-feira, 5 de setembro de 2014 02:44