none
SOMASES com 8 criterios lento... RRS feed

  • Pergunta

  • Estou com um sério problema de processamento em um arquivo de Excel que montei. Estou utilizando a formula SOMASES com 8 critérios em 3 bases com +ou- 100 mil linhas cada. o arquivo já está com 25.000 Kb etão pode imaginar a demora... conhecem alguma maneira de melhorar isso? 
    quarta-feira, 4 de fevereiro de 2015 12:11

Respostas

  • "O que me deixa bem curioso seria utilizar o mesmo recurso da tabela para realizar a soma com a mesma velocidade, mas ainda não encontrei um estudo especifico sobre isso."

    Garanto que você só tem a ganhar. Seu cenário de trabalho é o melhor possível para usar uma tabela dinâmica, você não irá se arrepender.

    Se quiser uma introdução, veja: https://www.youtube.com/watch?v=BTYIVr_5Kv4


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

    quarta-feira, 18 de março de 2015 23:55
    Moderador
  • Olá Igor

    O Felipe já sugeriu a melhor solução, uma Tabela Dinâmica.

    Ainda assim, gostaria de comentar que não entendi porque você está usando INDIRETO dentro da função SOMASES. 

    INDIRETO é uma função volátil e por isso deve ser evitada. Funções voláteis são executadas a cada recálculo da planilha, independentemente das células tratadas pela fórmula terem sido alteradas ou não. Isto pode causar sérios problemas de performance - lentidão nos cálculos.

    Posso estar enganado, mas no seu caso não vejo necessidade de usar INDIRETO para se referir aos intervalos de soma e de critérios

    Por que não usar simplesmente as Referências Estruturadas de Tabela na função SOMASES?

    Em caso de dúvida dê uma olhada em

    https://support.office.com/pt-pt/article/Utilizar-refer%C3%AAncias-estruturadas-nas-f%C3%B3rmulas-de-tabelas-do-Excel-75fb07d3-826a-449c-b76f-363057e3d16f?ui=pt-PT&rs=pt-PT&ad=PT

    Espero ter ajudado

    M.

    ps: não consegui baixar o seu arquivo.



    sexta-feira, 20 de março de 2015 03:42

Todas as Respostas

  • Boa noite Igor.

    Poste a fórmula e um trecho dos seus dados para analisarmos melhor.

    Da forma que está fica muito genérico.

    Abraço.


    Filipe Magno

    quarta-feira, 4 de fevereiro de 2015 23:26
  • =SOMASES(

    INDIRETO(TABELA01&"[Movimento]");

    INDIRETO(TABELA01&"[Mês]");I$2;

    INDIRETO(TABELA01&"[Ano]");ANO_INICIAL;

    INDIRETO(TABELA01&"[Mascara_DRE]");SE(NÚM.CARACT($C650)>13;$C650;$C650&"*");

    INDIRETO(TABELA01&"[Filial]");I$3;

    INDIRETO(TABELA01&"[Filial]");I$4;

    INDIRETO(TABELA01&"[Empresa]");EMP_SGC;

    INDIRETO(TABELA01&"[Unid]");UNIDADE)

    Abaixo segue layout da tabela com 3 registros onde a fórmula indireto faz referencia.

    Id; Empresa; Unid; Filial; Mascara_DRE; Desc_DRE; Centro_Custo; Desc_Centro_Custo; Conta_Ctb; Desc_Ctb; Mês; Ano; Flag; Data; Descrição; Contr; Chave; Debito; Credito; Saldo; Movimento; Origem
    1_1_1_4.1.1.01.0001; 1; 1; 1; 4.1.1.01.0001; Tratores Novos; ; ; 4.1.1.01.0001; Tratores Novos; 1; 2015; ; 01/01/2015; Tratores Novos; ; ; ; 520125,087468976; ; -520125,087468976; 0
    1_1_1_4.1.1.01.0001; 1; 1; 1; 4.1.1.01.0001; Tratores Novos; ; ; 4.1.1.01.0001; Tratores Novos; 2; 2015; ; 01/02/2015; Tratores Novos; ; ; ; 650442,142351312; ; -650442,142351312; 0
    1_1_1_4.1.1.01.0001; 1; 1; 1; 4.1.1.01.0001; Tratores Novos; ; ; 4.1.1.01.0001; Tratores Novos; 3; 2015; ; 01/03/2015; Tratores Novos; ; ; ; 1354154,23871593; ; -1354154,23871593; 0

    quinta-feira, 5 de fevereiro de 2015 10:20
  • Boa noite Igor.

    Não sei como vc precisa desse resultado, mas se vc estiver aplicando essa fórmula para muitas consultas (estou supondo que sua tabela de dados tenha mais de 100mil linhas mas os resultados não necessariamente), talvez a melhor saída seja compor um relatório de tabela dinâmica. Já trabalhei com bases maior de 250mil linhas e o processamento é extremamente rápido.

    Uma outra alternativa, como teste, seria utilizar fórmulas matriciais, ao invés dessa função que utilizou, mas não sei te adiantar se melhoraria.

    Enfim, não consigo pensar nada diferente disso no momento...

    Se me lembrar de mais alguma coisa te aviso.

    Vlw.


    Filipe Magno

    sexta-feira, 6 de fevereiro de 2015 02:19
  • Igor, acho que tenho uma solução para o seu problema.

    No entanto, poderia disponibilizar uma pasta de trabalho com alguns dados (umas 100 linhas) para teste? apague os valores confidenciais da pasta de trabalho, obviamente.


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

    sábado, 7 de fevereiro de 2015 19:10
    Moderador
  • Olá Felipe, segue arquivo com modelo

    Obrigado!!!

    https://onedrive.live.com/redir?resid=8740E321F078ADED%214800

    terça-feira, 17 de março de 2015 23:12
  • -Coloque em formato de Tabela cada uma das tabelas que estão na planilha FILTERS.

    -A planilha RESUMO com todas as fórmulas deve ser reconstruída. Utilize uma tabela dinâmica ao invés de criar essa quantidade grande de fórmulas. Você sabe usar tabelas dinâmicas? Vale a pena pesquisar, você iria ter grandes ganhos ao usar.


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

    quarta-feira, 18 de março de 2015 14:05
    Moderador
  • Não tenho muita habilidade com as tabelas dinâmicas com análise verticais e horizontais nos campos calculados, em DRE's Demonstrativos de resultados, por isto utilizo desta forma.

    O que me deixa bem curioso seria utilizar o mesmo recurso da tabela para realizar a soma com a mesma velocidade, mas ainda não encontrei um estudo especifico sobre isso. Mas em todo caso Obrigado.
    quarta-feira, 18 de março de 2015 22:44
  • "O que me deixa bem curioso seria utilizar o mesmo recurso da tabela para realizar a soma com a mesma velocidade, mas ainda não encontrei um estudo especifico sobre isso."

    Garanto que você só tem a ganhar. Seu cenário de trabalho é o melhor possível para usar uma tabela dinâmica, você não irá se arrepender.

    Se quiser uma introdução, veja: https://www.youtube.com/watch?v=BTYIVr_5Kv4


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

    quarta-feira, 18 de março de 2015 23:55
    Moderador
  • Olá Igor

    O Felipe já sugeriu a melhor solução, uma Tabela Dinâmica.

    Ainda assim, gostaria de comentar que não entendi porque você está usando INDIRETO dentro da função SOMASES. 

    INDIRETO é uma função volátil e por isso deve ser evitada. Funções voláteis são executadas a cada recálculo da planilha, independentemente das células tratadas pela fórmula terem sido alteradas ou não. Isto pode causar sérios problemas de performance - lentidão nos cálculos.

    Posso estar enganado, mas no seu caso não vejo necessidade de usar INDIRETO para se referir aos intervalos de soma e de critérios

    Por que não usar simplesmente as Referências Estruturadas de Tabela na função SOMASES?

    Em caso de dúvida dê uma olhada em

    https://support.office.com/pt-pt/article/Utilizar-refer%C3%AAncias-estruturadas-nas-f%C3%B3rmulas-de-tabelas-do-Excel-75fb07d3-826a-449c-b76f-363057e3d16f?ui=pt-PT&rs=pt-PT&ad=PT

    Espero ter ajudado

    M.

    ps: não consegui baixar o seu arquivo.



    sexta-feira, 20 de março de 2015 03:42