Guru do Excel e Power BI

Boa noite a todos

Preciso muito de ajuda para resolver um erro numa formula que estou a usar numa query, que corre sobre olap pivots.

Eu não sei VBA e os meus conhecimentos de excel são limitados, portanto não consigo perceber o que estou a fazer mal. Peço por isso a vossa ajuda de especialistas para saber o que se passa com a formula.

O ficheiro que estou a usar tem milhares de linhas e tem informação confidencial sobre dados de voos, portanto não posso anexá-lo mas vou tentar resumir o que contem.

O ficheiro tem vários olap pivots e tem uma query, que corre sobre essas pivots. Mas a formula que dá erro trabalha só com o dados de duas pivots:

- uma pivot que está na sheet "REDE"
- outra pivot que está na sheet "REDE_CNL"

Ambas as pivots têm, entre outras colunas, uma coluna "B" que tem datas do ano inteiro desde 2007 até ao presente (por ex: 01-01-2017, 02-01-2017, etc, etc) e outras colunas "C", "D", "E", "H", etc, com os dados que quero extrair .

Actualmente, já tenho uma formula na query que vai buscar dados a cada pivot se uma determinada data (mês-ano) for encontrada na coluna B dessa pivot.

Esta formula corre bem e soma os dados que preciso.

O problema é quando tento usar a mesma formula mas , em vez de ter como condição o "mês-ano", tenho um range de dias desse mês mas referente ao ano anterior, e os dias são desde o dia 01 até ao dia em que se estiver (por exemplo, estamos hoje a 19-04-2017, portanto deveria extrair o total dos dados entre os dias 01 e 19 de abril de 2016).

Estas datas minima e maxima são obtidas através das seguintes formulas:

- Minima : = CONCATENATE("01";"-";Z36)
- Máxima: =CONCATENATE((DAY(TODAY()));"-";Z36)

sendo que Z36 é a célula onde está o "mês-ano" que mencionei acima e que, por sua vez, aglutina (=CONCATENATE(X$3;"-";Y$4)) o mês/ano indicado pelo utilizador nas células X e Y.

As células com as datas estão formatadas com formato General.

A formula que está a funcionar é a seguinte:

=SUMIFS(REDE!C:C;REDE!B:B;"*"&Z36)+SUMIFS(REDE!D:D;REDE!B:B;"*"&Z36))/((SUMIFS(REDE_CNL!C:C;REDE_CNL!B:B;"*"&Z36)+SUMIFS(REDE_CNL!D:D;REDE_CNL!B:B;"*"&Z36)+SUMIFS(REDE_CNL!E:E;REDE_CNL!B:B;"*"&Z36)+SUMIFS(REDE_CNL!H:H;REDE_CNL!B:B;"*"&Z36))-(IFERROR(VLOOKUP(AND(Z37;Z38);REDE_CNL!B:C;2;FALSE);0)))

Já tentei três versões desta formula com o range de dias e todas dão a mesma mensagem de erro. São estas as versões:

Versão um:

=(SUMIFS(REDE!C:C;REDE!B:B;">="&Z37;REDE!B:B;"<="&Z38)+SUMIFS(REDE!D:D;REDE!B:B;">="&Z37;REDE!B:B;"<="&Z38))/(SUMIFS(REDE_CNL!C:C;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&Z38)+SUMIFS(REDE_CNL!D:D;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&Z38)+SUMIFS(REDE_CNL!E:E;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&Z38)+SUMIFS(REDE_CNL!H:H;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&Z38)-(IFERROR(VLOOKUP(AND(REDE_CNL!B:B>=Z37;REDE_CNL!B:B<=Z38);REDE_CNL!B:C;2;FALSE);0)))

Versão dois:

=(SUMIFS(REDE!C:C;REDE!B:B;">="&Z37;REDE!B:B;"<="&V38)+SUMIFS(REDE!D:D;REDE!B:B;">="&Z37;REDE!B:B;"<="&V38))/(SUMIFS(REDE_CNL!C:C;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&V38)+SUMIFS(REDE_CNL!D:D;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&V38)+SUMIFS(REDE_CNL!E:E;REDE_CNL!B:B;">="&Z397;REDE!B:B;"<="&V38)+SUMIFS(REDE_CNL!H:H;REDE_CNL!B:B;">="&Z37;REDE!B:B;"<="&V38)-(IFERROR(VLOOKUP(REDE_CNL!B:B>=Z37&REDE_CNL!B:B<=V38;REDE_CNL!B:C;2;FALSE);0)))

Versão três:

=(SUMIFS(REDE!C:C;REDE!B:B;">="&Z38;REDE!B:B;"<="&V38)+SUMIFS(REDE!D:D;REDE!B:B;">="&Z38;REDE!B:B;"<="&V38))/(SUMIFS(REDE_CNL!C:C;REDE_CNL!B:B;">="&Z39;REDE!B:B;"<="&V38)+SUMIFS(REDE_CNL!D:D;REDE_CNL!B:B;">="&Z38;REDE!B:B;"<="&V)+SUMIFS(REDE_CNL!E:E;REDE_CNL!B:B;">="&Z399;REDE!B:B;"<="&V40)+SUMIFS(REDE_CNL!H:H;REDE_CNL!B:B;">="&Z39;REDE!B:B;"<="&V40)-(IFERROR(VLOOKUP(AND(REDE_CNL!B:B<=MIN(Z38;V38);REDE_CNL!B:B>=MAX(Z38;V38));REDE_CNL!B:C;2;FALSE);0)))

Ando há muitos dias de volta com este problema, já acordo durante a noite a pensar nisto e não consigo mesmo perceber onde está o erro. E preciso de conseguir urgente esta informação .

Por favor, ajudem-me!!

Obrigada desde já pela vossa disponibilidade
Cumprimentos,
Cristina

Boa noite Cristina,

Não há possibilidade de fazer uma cópia dos arquivos, apagar todas as linhas e alterar os dados? Colocar pelo menos umas 3 linhas com informações fictícias?

Aparentemente não é problema muito complexo, talvez conseguimos até reduzir os números de formulas aninhadas.

Fico no aguardo,

Boa noite mdiego, posso anexar uns print screen das sheets, mas não consigo enviar o ficheiro porque vai buscar os dados ao globalflight (b.dados olap) e, portanto, sem a ligação ao cube, não iria abrir. Os print screen servem? Obrigada

Boa noite,

Já serve, da pra simular os dados... Mas ai não irá ficar ruim pra você?

Porque com os print você acaba divulgando dados...

Sds,

Eu talvez não consiga responder agora porque estou indo correr, mas assim que voltar eu tento resolver... Mas aqui tem vários experts no assunto.

Vou enviar uns print só com as primeiras linhas de cada sheet, espero que não haja problema. Boa corrida, muito obrigada pelo interesse e por querer ajudar

Envio um ficheiro com print screens das pivots e da query e também das formulas. Desculpem mas não posso divulgar mais dados

Muito obrigada pela ajuda!

É um pouco complexo mesmo... Eu fixei a coluna com cifrão $ nas referêcias... Talvez já ajude em algo.

Acho que é mais simples começar do 0. Se for falar assim: Preciso somar todos os dados da coluna C com base em periodo de 01/10/2016 - 10/11/2017 e somar os dados da coluna. Depois Dividir C por D e assim por diante... Entendeu?

Sds,

Vou mandar um exemplo aqui, talvez você consiga colocar alguns dados fictícios da onde você vai puxar informações e tudo mais, colunas...