Este fórum está sendo desativado

[matcamposilva]

Bom dia!
Pessoal, precisava de ajuda na correção das formulas da planilha que fiz para realizar um controle de quantidade de concessões dos sistemas que utilizamos. Está planilha será online no Sharepoint.

A Planilha está composta por abas individuas de cada mês e na ultima aba(consolidado) quero deixar o consolidado para que cada vez que insira uma linha na aba do mês respectivo atualize na aba consolidados para que não precise todo final de mês fazer o consolidado de todos os sistemas do mês. Tem como fazer isso e como ?
Fiz essa tentativa e não deu muito certo.


Vamos para as formulas que estão com problemas e o assunto principal, rs.
Na aba consolidado fiz a seguinte formula:

Código: Selecionar todos

=CONT.SES(JAN[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);JAN[SISTEMA];"DYNAMICS") + CONT.SES(FEV[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);FEV[SISTEMA];"DYNAMICS") + CONT.SES(MAR[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);MAR[SISTEMA];"DYNAMICS") + CONT.SES(ABR[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);ABR[SISTEMA];"DYNAMICS") + CONT.SES(MAIO[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);MAIO[SISTEMA];"DYNAMICS") + CONT.SES(JUN[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);JUN[SISTEMA];"DYNAMICS") + CONT.SES(JUL[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);JUL[SISTEMA];"DYNAMICS") + CONT.SES(AGO[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);AGO[SISTEMA];"DYNAMICS") + CONT.SES(SET[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);SET[SISTEMA];"DYNAMICS") + CONT.SES(OUT[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);OUT[SISTEMA];"DYNAMICS") + CONT.SES(NOV[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);NOV[SISTEMA];"DYNAMICS") + CONT.SES(DEZ[REFMES];PROCV($O$2;DADOS!$A$3:$B$14;2;0);DEZ[SISTEMA];"DYNAMICS")

Porém, como da pra ver ficou muito grande e acho um pouco inviável fazer e não é "funcional", pelos testes que fiz está tudo certo. Porém, ao transpor a na logica mês a mês conforme solução possível que fiz não deu muito certo. Essas tabelas da planilha mês a mês ficaram ocultas(J3;P49), por isso quero deixar no consolidado tudo.

Código: Selecionar todos

=CONT.SE(JAN!N5;PROCV(O2;DADOS!$A$3:$B$14;2;0)) + CONT.SE(FEV!N5;PROCV(O2;DADOS!$A$3:$B$14;2;0))

Além de cont.se tentei usar cont.valores e soma.se. Mas, como quero digitar na aba consolidados o mes(O2) ou a data(O11 e 28/01/2021) nenhuma delas e principalmente soma.se não da muito certo.

Observação um dos cálculos que quero fazer é que quando coloque um usuário que esteja na planilha ele me retorne a quantidade que ele solicitou de chamados no total de todos os meses(I45) fazer uma validação de dados não acho bacana até pq um usuário que esta fora do mapeamento atual pode solicitar.

na celula I50 aba consolidados gostaria de deixar em validação de dados e escolher o status conforme fiz para o calculo de cada mês.

Na planilha em anexo deixei em amarelo as formulas que fiz e que haviam dado certo.
Obrigado por enquanto! ABS!

[Estevaoba]

Na aba dados criei uma lista com os nomes das abas e no Gerenciador de Nomes da faixa Fórmulas criei um intervalo nomeado Abas referente a essa lista.
Também criei uma lista dos usuários e para as células com validação de dados referente a usuários, uso esta fórmula para incorporar novos usuários a serem acrescentados:

Código: Selecionar todos

=DESLOC(DADOS!$H$3;;;CONT.VALORES(DADOS!$H$3:$H$100);1)

Para a contagem total dos meses por sistema, uso esta fórmula em M13:

Código: Selecionar todos

=SOMARPRODUTO(CONT.SE(INDIRETO("'"&Abas&"'!B2:B1000");I13))

Confira no anexo se atende.

Good luck!

[matcamposilva]

@Estevaoba,
Atualizações muito boas, me serviram em partes. Deixei algumas considerações e comentários na própria planilha em anexo. Porém, vou comentar mais algumas coisas aqui

- Realizei algumas exclusões como a coluna que tinha colocado como referencia pro mês, vi que não é necessário na sua logica.
- Na plan ABRI, por exemplo, a celula STATUS SEMPRE fica pintada de Verde, não consigo ver nada na plan que esta ocasionando isso. Não consegui tirar isso para deixar sempre branco. Segue exemplo:


-Verifiquei que utilizou muita validação de dados, achei interessante, mas, em alguns casos como para calcular a quantidade de solicitações por usuário não sei se seria muito viavel. Pois, imaginemos que a plan DADOS ficará oculta e a outra pessoa que irá preencher não sabe ou não atualiza essa parte. Ai teremos problemas para essa seção na planilha, certo ?
Da mesma maneira tem um usuário MARCELO.DESOUZA, acabei incluindo na plan dados e atualizando o consolidado e acabou não computando o valor dele. Por essas questões acho que não seria interessante usar essa alternativa.

- Precisava de uma tabela que eu definisse o dia e me retornasse o resultado das concessões e outra das revogações.

Para os sistemas não sei se seria interessante utilizar validação(na plan não utilizamos) Pelo mesmo cenário do usuário pode ser que lá pra frente tenha um novo e será necessário atualizar a plan, novamente ?
A ideia é deixar ela mais automatizada para apenas inserir os campos do mês e diariamente ela é alimentada por aproximadamente 6 pessoas.

Estou aberto a dicas e orientações rsrsrs ...
Desde já muito obrigado!

[matcamposilva]

Opa!

Consegui fazer mais algumas.

QUANTITATIVO DE CONCESSÕES ACESSOS EMERGENCIAIS | DEFINIÇÃO USUÁRIO

Código: Selecionar todos

=SOMARPRODUTO(CONT.SES(INDIRETO("'"&Abas&"'!B2:B1000");I44;INDIRETO("'"&Abas&"'!C2:C1000");$N$41))

Marcia ainda me retorna um valor errado.

QUANTITATIVO DE CONCESSÕES ACESSOS EMERGENCIAIS | HOJE

Código: Selecionar todos

=SOMARPRODUTO(CONT.SES(INDIRETO("'"&Abas&"'!B2:B1000");I52;INDIRETO("'"&Abas&"'!D2:D1000");HOJE()))

QUANTITATIVO DE REVOGAÇÕES ACESSOS EMERGENCIAIS | HOJE

Código: Selecionar todos

=SOMARPRODUTO(CONT.SES(INDIRETO("'"&Abas&"'!B2:B1000");I62;INDIRETO("'"&Abas&"'!E2:E1000");HOJE()))

QUANTITATIVO DE CONCESSÕES ACESSOS EMERGENCIAIS | DEFINIÇÃO DE DATA INICIAL DO ACESSO

Código: Selecionar todos

=SOMARPRODUTO(CONT.SES(INDIRETO("'"&Abas&"'!B2:B1000");I24;INDIRETO("'"&Abas&"'!D2:D1000");$O$22))

QUANTITATIVO DE REVOGAÇÕES ACESSOS EMERGENCIAIS | DEFINIÇÃO DE DATA INICIAL DO ACESSO

Código: Selecionar todos

=SOMARPRODUTO(CONT.SES(INDIRETO("'"&Abas&"'!B2:B1000");I33;INDIRETO("'"&Abas&"'!E2:E1000");$O$31))

Planilha em anexo das formulas que alterei.
Verificando um pouco melhor as possibilidades, para mim não esta certo ou é impressão minha?
Pois, não houve ainda nenhuma liberação em abril, pois ainda estamos no mês de março. E na formula computou. Veja:

[Estevaoba]

Fiz adaptações das fórmulas e anotações na planilha.

Ab.

[matcamposilva]

@Estevaoba,

Foram suficientes e confirmaram com as que tinha imaginado anteriormente.
Uma única dúvida ainda, Existe como eu automatizar a consolidação das abas de cada mes para que quando eu adicionar uma nova linha no mês atualizasse automaticamente na consolidado ? tentei por power Query, mas não se tornou nada eficiente.

[Estevaoba]

As fórmulas na aba consolidado vão até a linha 1000.
=SOMARPRODUTO(CONT.SES(INDIRETO("'"&Abas&"'!B2:B1000");I44;INDIRETO("'"&Abas&"'!C2:C1000");$N$41))
Se precisar incorporar além de mil linhas, altere os intervalos nas fórmulas.

Ab.

[matcamposilva]

Deu certo em tudo, só na tabela dinâmica esta com diferencia de valores, onde nas formulas mostra o valor correto(96) e na dinâmica(101) rsrsrs... Fora, isso agora acho que está bom a plan.

Curiosidade, Como seria uma alternativa usando a formula cont.se usando os nomes das tabelas não pela colunas como usou ( ex: "'"&Abas&"'!B2:B1000")
na minha antiga tinha usado dessa forma, o que errei ?

Código: Selecionar todos

CONT.SE(JAN!N5;PROCV(O2;DADOS!$A$3:$B$14;2;0)) + CONT.SE(FEV!N5;PROCV(O2;DADOS!$A$3:$B$14;2;0))

[Estevaoba]

A parte "'"&Abas&"'!B2:B1000" na fórmula, no caso o intervalo nomeado "Abas" refere-se às planilhas de meses, não às colunas.
A função CONT.SES faz a contagem só na primeira planilha (aba JAN por exemplo). Combinada com SOMARPRODUTO faz a contagem em todas as abas de meses, no intervalo B2:B1000.

Ab.