Dúvidas sobre cálculos, funções simples e aninhadas, fórmulas matriciais, etc.
  • Avatar do usuário
Por matcamposilva
#63372
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.
Imagem

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.
Imagem
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.
Imagem
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!
Você não está autorizado a ver ou baixar esse anexo.
Avatar do usuário
Por Estevaoba
#63377
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!
Você não está autorizado a ver ou baixar esse anexo.
matcamposilva agradeceu por isso
Por matcamposilva
#63406
@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 :P ;)

- 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:
Imagem

-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!
Você não está autorizado a ver ou baixar esse anexo.
Por matcamposilva
#63412
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:

Imagem
Imagem
Editado pela última vez por matcamposilva em 03 Abr 2021 às 14:10, em um total de 1 vez.
Por matcamposilva
#63438
@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.
Avatar do usuário
Por Estevaoba
#63440
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 agradeceu por isso
Por matcamposilva
#63489
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))
Você não está autorizado a ver ou baixar esse anexo.
Avatar do usuário
Por Estevaoba
#63495
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.
matcamposilva agradeceu por isso

Bom dia! No ficheiro "Indicadores de Cronogr[…]

EXPE.TEXTO-PEGAR O ZERO

Boa noite, babdallas!! Tudo bom? Perfeito, era i[…]

Porcentagem em das porcentagens

=MÉDIA(A1:A6)

Perdão, anexei o arquivo errado sem querer.[…]

Oi gente tudo bem eu estou fazendo uma planilha on[…]

Pessoal, boa tarde!! Tenho uma planilha de horas […]

Boa tarde!! Alguém sabe uma fórmula[…]

Boa tarde!! Alguém consegue me ajuda com u[…]