Este fórum está sendo desativado

[mce]

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

[mdiego]

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,

[mce]

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

[mdiego]

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,

[mdiego]

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.

[mce]

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

[mce]

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!

[mdiego]

Código: Selecionar todos

=SUMIFS(REDE!C:C;REDE!B:B;"*"&$Z$36)+SUMIFS(REDE!D:D;REDE!B:B;"*"&$Z$36))/((SUMIFS(REDE_CNL!C:C;REDE_CNL!B:B;"*"&$Z$36)+SUMIFS(REDE_CNL!D:D;REDE_CNL!B:B;"*"&$Z$36)+SUMIFS(REDE_CNL!E:E;REDE_CNL!B:B;"*"&$Z$36)+SUMIFS(REDE_CNL!H:H;REDE_CNL!B:B;"*"&$Z$3$6))-(IFERROR(VLOOKUP(AND($Z$37;$Z$38);REDE_CNL!B:C;2;FALSE);0)))

É 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,

[mdiego]

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