Guru do Excel e Power BI

Boa tarde pessoal,

estou com uma dificuldade para conseguir realizar algumas funções entre pastas de trabalhos diferentes.

Por exemplo:

Possuo um banco de dados em uma pasta de trabalho chamada "Analítico", nela constam todas as informações possíveis para realizar um painel ou dashboard;
Na outra pasta de trabalho, chamada "Paínel", há o painel, com as variáveis que seleciono para obter o resultado.

Acontece que quando adiciono formulas que possuem o SE, por exemplo: SOMASE, SOMASES, CONT.SE, CONT.SES , e fecho a pasta de trabalho Analítico, as informações na pasta de trabalho Painel ficam apresentando erros, diferentemente de quando realizo uma fórmula sem o SE ou deixo o arquivo Analítico aberto.

Normalmente eu trabalho com meu banco de dados e o dashboard dentro da mesma pasta de trabalho, porém desta vez, preciso trabalhar com Pastas de Trabalho separadas. Fiz a montagem de um Dashboard como estou habituado e o resultado foi um arquivo de 112MB, demorando de 3 a 5 minutos para abrir e também a lentidão para a navegação dentro dele. Não irei disponibilizar o dashboard pois trata-se de informações de trabalho, mas criei um exemplo para facilitar o entendimento.

Bom pessoal, desde já agradeço a todos e um forte abraço

Boa noite Thiago,

sua fórmula está funcionando normalmente aqui, porém ela tem que ser matricial, para isso entre na fórmula e ao sair aperte CTRL + SHIFT + ENTER.

Note que ela fica entre chaves {}.

Kledison, obrigado pelo retorno, mas também já tentei anteriormente transformar a fórmula em matricial e não rolou. Tentei novamente agora e continua sem sucesso. o Painel não funciona com a Pasta de Trabalho Analítico fechado.

Olá thiagoypu!

Essas operações entre pastas de trabalho não são muito comuns. Talvez por isso pode existir muita dúvida! Eu demorei um tempo para achar uma referência. Encontrei aqui uma solução: https://www.ablebits.com/office-addins- ... -workbook/

A ideia é a forma como a referência externa é feita. Tente da seguinte forma:

'C:\USERS\SEUUSUÁRIO\PASTAONDEESTÁOARQUIVO\[Analítico.xlsx]Analítico'!B:B

No meu caso ficou assim:

=SOMARPRODUTO(--('C:\Users\Felipe\Desktop\[Analítico.xlsx]Analítico'!B:B=$C$5);--('C:\Users\Felipe\Desktop\[Analítico.xlsx]Analítico'!C:C=$C$7);'C:\Users\Felipe\Desktop\[Analítico.xlsx]Analítico'!F:F)

Funcionou perfeitamente, inclusive, ao abrir a Pasta de Trabalho Analítico e referência altera de formato para:

Analítico.xlsx!B:B

E a função ficou assim:

=SOMARPRODUTO(--(Analítico.xlsx!B:B=$C$5);--(Analítico.xlsx!C:C=$C$7);Analítico.xlsx!F:F)

O desafio é, sem sombra de dúvidas, reduzir o processamento. Isso por que a referência externa não aceita o formato de tabela, nem mesmo intervalos nomeados quando a outra Pasta de Trabalho está fechada. E, vc deve ter percebido que eu referenciei B:B. Talvez vc possa aprimorar ela referenciando um intervalo menor, uma previsão mais amistosa, tipo B1:B200.

Ahhh,

Para atualizar o vínculo com a pasta externa vá em Dados>Conexões>Editar Links>Atualizar Valores ou Alt, S, E.

Vaz, muito obrigado pela a ajuda, é exatamente isso que estou procurando. odes me ajudar em mais uma coisa? acho que matei a aula aonde explica sobre o --

a) Como faço para aplicar esta regra em outras fórmulas como CONT.SES e SOMA.SES, é possível?
b) Se não houver a possibilidade de aplicar as fórmulas acima, como consigo colocar mais de uma condição dentro de SOMARPRODUTO utilizando a regra que você explicou?

Mais uma vez, obrigado.

Olá Thiago!!

Para aplicar condições dentro da função SOMARPRODUTO, em geral, utilize o padrão:

=SOMARPRODUTO(--(IntervaloCritério1=$Coluna$linhadocritério1);--(IntervaloCritério2=$Coluna$linhadocritério2);--(IntervaloCritérioN=$Coluna$linhadocritérioN);IntervaloSoma)

Esses 2 negativos servem para converter a matriz VERDADEIRO/FALSO em 1/0, imagine que:

Se um IntervaloCritério1 for IGUAL $Coluna$linhadocritério1 iria aparecer {VERDADEIRO,FALSO,VERDADEIRO,VERDADEIRO}, por exemplo. Dessa forma, SOMARPRODUTO não consegue executar. Daíh, ao colocar os 2 negativos ficaria {1,0,1,1}, multiplicando pelos valores do IntervaloSoma, naqueles que tem o dígito 1, automaticamente ficaria um intervalo interpretado para a soma. {10,0,10,10}, resultando em 30.

Agora, quanto à SOMASE e CONT.SES, não consegui. Vou pesquisar melhor!

Thiago!

A soluçao para SOMASES e CONT.SES é SOMA e as funções CONTAR (CONT.NÚM, VALORES, etc), associado à SE. Veja aqui: https://support.microsoft.com/pt-br/kb/237188

Tente, finalizando a fórmula com Ctrl+Shift+Enter:

{=SOMA(SE([Analítico.xlsx]Analítico!!B:B=$C$5;[Analítico.xlsx]Analítico!!D:D,0))}

Informe aqui como foi por gentileza.

Ainda, de acordo com o Fórum do MrExcel: "COUNTIF and SUMIF cannot reference a closed workbook . If you need this functionality use...{=COUNT(IF('path[filename]tab'!$AH$11:$AH$300="yes",1))}, ou seja, não há como fazer uma referência (sem que a pasta externa esteja aberta), à SOMASES e COUNT.SES, veja mais em: http://www.mrexcel.com/forum/excel-ques ... kbook.html

Felipe, sem palavras.... consegui.

Para fazer o CONT(SE ou SES), coloquei ao final do analítico uma coluna com o valor 1, e fiz a SOMA(SE, funcionou perfeitamente.

Vou deixar o anexo com a resolução para podermos compartilhar esta solução.

Obrigado mais uma vez.... Abçs!!!