Fóruns
Microsoft Excel - 2...
2024 - Fórmulas Ava...
Índice + Corresp ba...
 
Notifications
Clear all

Índice + Corresp baseado em condições

 
2024 - Fórmulas Avançadas - Funções Microsoft Excel
13 Posts
1 Usuários
0 Reactions
2,249 Visualizações
RSS
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Pessoal,

Tenho um bom desafio, ou pelo menos se puderem me dar uma luz

Na planilha anexa Tenho uma aba de dados e uma pivot (tabela dinâmica) onde organizo os dados da forma que quero

Bem tem duas validações a primeira é por Célula ele vai retornar 1 ou 0, o mesmo para Presencial vai retornar 1 ou 0, bem não sei ao certo se a segunda eu realmente vou precisar.

Na aba template é onde funciona toda a mágica,

Eu distribuo as três células, aquele que tiver 0 na validação, eu posso começar a fórmuma como =SE(Pivot!H13<>0; até ai tudo bem, o que preciso é que ele retorne os valores HC, HNC, SDF no bloco Horas Utilizadas de acordo com a célula (ATENDIMENTO, SOLUCIONADOR, ESPECIALISTA) correspondente

Neste exemplo temos Presencial t ou f, mas vai haver Pivots que somente haverá um deles, logo a Pivot será menor eu não consegui a lógica para avaliar essa condição

Consegui explanar?

Grato e []s

 
Postado : 03/02/2012 7:13 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Não sei se entendi,
mas de uma olhada no anexo, se é por esse caminho

 
Postado : 03/02/2012 8:52 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Reinaldo achei interessante o uso do índice + corresp com linha e coluna

Porém na célula Template!F6 que deveria trazer os dados da Pivot!E6 como faria?

[]s

 
Postado : 03/02/2012 9:44 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

O ideal é ter as colunas com mesmo label, assim fica mais "automatico".

Não sendo possivel, pode ser utilizada uma composição tipo

=ÍNDICE(Pivot!$A$6:$Y$7;CORRESP($A6;Pivot!$A$6:$A$7;0);CORRESP("Ponto "&F$4;Pivot!$A$5:$Y$5;0))

ou então identificar a coluna

ÍNDICE(Pivot!$A$6:$Y$7;CORRESP($A6;Pivot!$A$6:$A$7;0);5)

 
Postado : 03/02/2012 10:20 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Reinaldo,

Obrigado pelo apoio,

Vai ficar bem automático porque os dados de label são fixos apenas os valores vão variar,

Eu tentei a fórmula 

=ÍNDICE(Pivot!$A$6:$S$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora Comercial";Pivot!$A$5:$S$5;0)&CORRESP("f";Pivot!$A$4:$S$4;0))

Todavia ela traz #REF de resposta eu queria colocar a condição da linha 4 de ser t ou f

[]s

Summary Pontos Forum.xlsx
 
Postado : 03/02/2012 11:27 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Célula E9 coloca essa fórmula:

=DESLOC(Pivot!$A$6;CORRESP($D9;Pivot!$A$6:$A$8;0)-1;MÍNIMO(SE(("Soma de Hora Comercial"=Pivot!$A$5:$S$5)*("f"=Pivot!$A$4:$S$4);COL(Pivot!$A$5:$S$5)))-1)

Ctrl + Shift + Enter

Qualquer coisa da o grito.

 
Postado : 03/02/2012 12:15 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

E para a célula do lado HNC e SDF só mudo a string?

 
Postado : 03/02/2012 12:21 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Mas o restante está calculando normal, não?

 
Postado : 03/02/2012 12:26 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Não! substitui lá pra ver! vou ter que usar sua lógica em todas, porém na primeira ok nas demais dá #REF

 
Postado : 03/02/2012 12:28 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Mas essa fórmula era somente para a célula E9.

As outras me pareciam estar calculando correto.
Ou quer todas as fórmulas seguindo a mesma lógica?

 
Postado : 03/02/2012 12:32 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Exatamente todas fórmulas devem seguir esta mesma lógica
Se vc analisar a pivot vai ver um grupo sob o f (false) e um mesmo grupo HN, HNC, SDF sob o t (true)
Entendeu?

 
Postado : 03/02/2012 12:35 pm
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Pessoal,

Eu consegui desta forma, não sei se é a forma mais enxuta:

Sheet Template

Na célula E9 usei:

=SE(Pivot!$B$4="f";ÍNDICE(Pivot!$B$6:$G$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora Comercial";Pivot!$B$5:$G$5;0));SE(Pivot!$H$4="f";ÍNDICE(Pivot!$H$6:$M$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora Comercial";Pivot!$H$5:$M$5;0));"ERRO"))

Na célula F9

=SE(Pivot!$B$4="f";ÍNDICE(Pivot!$B$6:$G$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora Noturno";Pivot!$B$5:$G$5;0));SE(Pivot!$H$4="f";ÍNDICE(Pivot!$H$6:$M$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora Noturno";Pivot!$H$5:$M$5;0));"ERRO"))

Na célula G9

=SE(Pivot!$B$4="f";ÍNDICE(Pivot!$B$6:$G$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora SDF";Pivot!$B$5:$G$5;0));SE(Pivot!$H$4="f";ÍNDICE(Pivot!$H$6:$M$9;CORRESP($D9;Pivot!$A$6:$A$8;0);CORRESP("Soma de Hora SDF";Pivot!$H$5:$M$5;0));"ERRO"))

I9, J9 e K9 tiveram apenas mudança na String de comparação

E para M9:O9 e Q9:S9 apenas mudei no SE de "f" para "t"

Vou deixar mais um dia aberto se alguém conseguir algo mais enxuto, caso contrário encerrarei o tópico

Obg a todos pela Luz!

[]s

 
Postado : 06/02/2012 8:14 am
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
Topic starter
 

Tópico fechado!

 
Postado : 07/02/2012 10:49 am
Forum Jump:
  Tópico Anterior
Próximo Tópico