Guru do Excel e Power BI

Bom dia!

Estou criando uma planilha de comparativo de resultados projetados e reais. No entanto, não estou obtendo sucesso ao tentar estabelecer um valor sobre um intervalo X. Estou utilizando uma base de dados externa, mas apenas quero pegar um intervalo que comporte o ano 2018 em três cenários:

1. REAL: mês atual;
2. Year To Date (YTD): 1º mês até o atual;
3. YTD + Projeção: 1º mês até o atual (real) + a projeção do mês posterior até o último.

O poderia utilizar um SOMASES para isto, mas quando fecho a planilha os valores somem (#VALOR), e o que desejo é uma planilha que possa analisar sem ter que abrir sua base (além de usar uma fórmula mais leve que o SOMASES). Para isto, tentei utilizar o fórmula SOMA + ÍNDICE + CORRESP, mas quando determino um intervalo de data nos cenários 2 e 3 com a fórmula DATA, as fórmulas voltam a dar erro ao fechar a base externa.

Exemplo:

Anexei à mensagem uma planilha com duas abas, BASE EXTERNA e RELATÓRIO, simulando o que eu preciso. Então, partindo de uma dt. base X (neste caso foi utilizado 02/2018), dentro de um histórico que vai de 01/2017 à 12/2018, estou tentando calcular, dentro da mesma fórmula, utilizando um SE + SOMA + ÍNDICE + CORRESP, um valor que me dê os seguintes resultados:

1. REAL: 02/2018
2. YTD: 01/2018 à 02/2018
3. FORECAST: 01/2018 à 12/2018

Os itens estão dentro de uma validação de dados (lista), alterando o cenário e obtendo o resultados necessário.

Fiz abas na planilha para melhor exemplificar, pois conseguirão verificar que a fórmula está correta e o resultado aparece, portanto que planilha base esteja aberta, mas ao separar as abas, transformando em planilha separadas, os resultados YTD e FORECAST desaparecerem (#VALOR).

Espero ter sido claro.

Att.

Júlio Nascimento

A sua base externa é um outro arquivo Excel? Se sim, a fórmula SOMaSE não funciona com a pasta de trabalho fechada. Uma opção é usar a função SOMARPRODUTO.

Veja este vídeo do MVP Felipe Gualberto a respeito. https://www.youtube.com/watch?v=uyIejmQUPk0

Sim, a base é uma planilha externa. Eu concluí que não é possível com o SOMASE, mas como pode ver nos anexos, eu não utilizei esta fórmula, mas desenvolvi outra com o mesmo objetivo na expectativa que ela não desse erro (#VALOR). A fórmula SOMARPRODUTO vai me obrigar a modificar a base, o que eu não queria. Não teria como efetuar uma SOMA com maiores critérios, de forma que ela não desse erro ao fechar a planilha? Poderia ser algo como detalhado acima, SOMA + ÍNDICE + CORRESP, parecido com o alagarmento de colunas disponível no DESLOC (não usei DESLOC porque tem o mesmo problema de dar erro ao fechar a base).

babdallas escreveu:A sua base externa é um outro arquivo Excel? Se sim, a fórmula SOMaSE não funciona com a pasta de trabalho fechada. Uma opção é usar a função SOMARPRODUTO.

Veja este vídeo do MVP Felipe Gualberto a respeito. https://www.youtube.com/watch?v=uyIejmQUPk0

Sim, a base é uma planilha externa. Eu concluí que não é possível com o SOMASE, mas como pode ver nos anexos, eu não utilizei esta fórmula, mas desenvolvi outra com o mesmo objetivo na expectativa que ela não desse erro (#VALOR). A fórmula SOMARPRODUTO vai me obrigar a modificar a base, o que eu não queria. Não teria como efetuar uma SOMA com maiores critérios, de forma que ela não desse erro ao fechar a planilha? Poderia ser algo como detalhado acima, SOMA + ÍNDICE + CORRESP, parecido com o alagarmento de colunas disponível no DESLOC (não usei DESLOC porque tem o mesmo problema de dar erro ao fechar a base).

Vai precisar alterar, nenhuma das funções que disse trabalham com a planilha fechada, o melhor mesmo é usar a função SOMARPRODUTO nesse caso

Bom noite, Julio.

Confirma se formula que está no arquivo resolve a sua questão.




Att,
Salavacanda

salavacanda, a função ÍNDICE não funciona com base externa fechada, no seu caso funciona, devido as abas estarem na mesma planilha, mas ao buscar de outra planilha e fechar o arquivo, se não abrir o arquivo, ele não irá buscar os valores, irá dar erro.

FelipeMGarcia escreveu:salavacanda, a função ÍNDICE não funciona com base externa fechada, no seu caso funciona, devido as abas estarem na mesma planilha, mas ao buscar de outra planilha e fechar o arquivo, se não abrir o arquivo, ele não irá buscar os valores, irá dar erro.

A Fórmula Índice funciona com fontes externas, pois tenho diversas assim.