fórmula "se"

Thiago Goulart · 24 de março de 2017

Olá, prezados,

Estou precisando de algumas ajudinhas para resolução de algumas fórmulas e para facilitar anexei o arquivo:

A primeira fórmula que preciso de auxilio encontra-se na coluna AG. Antes ela buscava somente valores nas colunas X, Z, AB, AD e AF, mas agora preciso que busque os números 1, 5, 6, 7 ou 8 (pode ser qualquer um deles) nas colunas W, Y, AA, AC e AE. Segue a antiga fórmula:

=CONT.VALORES(X11)+CONT.VALORES(Z11)+CONT.VALORES(AB11)+CONT.VALORES(AD11)+CONT.VALORES(AF11)

A segunda fórmula que preciso encontra-se na coluna AH. Antes ela buscava somente a letra "S" nas colunas W, Y, AA, AC e AE, mas agora preciso que busque os números 1, 5, 6, 7 ou 8 (pode ser qualquer um deles). Segue a antiga fórmula:

=SEERRO(SOMA(SE(W11="s";$X$10;0);SE(Y11="s";$Z$10;0);SE(AA11="s";$AB$10;0);SE(AC11="s";$AD$10;0);SE(AE11="s";$AF$10;0))+(SOMA(SE(W11="s";$X$10;0);SE(Y11="s";$Z$10;0);SE(AA11="s";$AB$10;0);SE(AC11="s";$AD$10;0);SE(AE11="s";$AF$10;0))*(SE(AG11=1;0;SE(AG11=2;0,1;SE(AG11=3;0,2;SE(AG11=4;0,4;SE(AG11=5;0,5;"")))))));0)

Desde já agradeço a todos que possam me ajudar.

Forte abraço.

Edição.xlsx

Patropi · 25 de março de 2017

Boa tarde

Lembre-se que podemos entender um pouco de Excel, mas estamos por fora do assunto da tua planilha.

Explique melhor o que você deseja.

[]s

Thiago Goulart · 25 de março de 2017

Olá Patropi, tudo bem?

Obrigado por responder.

Vou explicar melhor a minha demanda.

Tenho atividades que são realizadas somente aos domingos;

Tenho uma lista de prestadores de serviço (trabalhadores), onde não existe vínculo que obrigue a participação dos mesmos;

Todos os prestadores de serviço são convocados para as atividades, sendo que, para os que se fazem presentes, reservo o valor para pagamento (pago no início do mês subsequente);

Os pagamentos para cada domingo podem variar e são lançados conforme mês de exemplo do arquivo (Março), nas células X10, Z10, AB10, AD10 e AF10;

Os valores para pagamento são acumulados e pagos do dia 1 a 7 do mês subsequente, com objetivo de ampliar a presença dominical destes prestadores de serviço, através de pagamento meritocrático progressivo seguindo o critério de quantidade de domingos presentes (dentro do mês)..

Por exemplo na coluna W (primeiro domingo de Março), lanço o status de presença do mesmo, que pode ser 1 (confirmou e compareceu), 5 (confirmou ausência e mesmo assim compareceu), 6 (estava indefinido e compareceu), 7 (não respondeu o contato de convocação e compareceu e, por último, 8 (não convocados que compareceram);

A coluna AG precisa identificar estes status (1, 5, 6, 7 e 8) na coluna W, e retornar a quantidade de domingos do mês, como se fosse realizado um CONT.SE, só que, não sei fazer com mais de um critério;

Na coluna AH, eu tinha uma fórmula de registro da participação, que fazia uso do resultado da coluna AG e das colunas onde eu lanço os valores de cada semana (X10, Z10, AB10, AD10 e AF10), que realizava o cálculo de quantos domingos, valores de cada domingo e a progressão meritocrática adicional, que consta no corpo da fórmula, que são (20% - 2 domingos, 30% - 3 domingos, 40% - 4 domingos e para meses com 5 domingos, acréscimo de 50% no pagamento;

Antes eu preenchia a presença incluindo um "S" na coluna W, porque não distinguia com status (1,5,6,7,e 8), como faço hoje, então preciso que a fórmula faça leitura destes, para incluir o critério de cálculo da fórmula contida na coluna AH, portanto, além do simples critérios "S".

Não sei se consegui ser claro, mas se houver mais dúvidas, peço que questionem.

Desde já agradeço.

deciog · 26 de março de 2017

Fica muito mais fácil de resolver se postar um modelo com dados fictícios

Decio

Thiago Goulart · 26 de março de 2017

12 horas atrás, deciog disse:

Fica muito mais fácil de resolver se postar um modelo com dados fictícios

Decio

Olá Decio, tudo bem?

Na linha 11, a partir da coluna X, inclui um exemplo de como era o meu controle, onde eu só incluía o nome do Colaborador para contar quantos domingos ele se fez presente e a letra "S" para parametrizar os cálculos..

Edição.xlsx

Edson Luiz Branco · 27 de março de 2017

@Thiago Goulart, se entendi, na primeira fórmula você quer que a coluna AG, da linha 11 em diante, verifique se nas colunas W, Y, AA, AC e AE existe quaisquer desses valores (1, 5, 6, 7 e 8) e se existir, conte quantos têm. Se for isso uma maneira seria usar uma fórmula semelhante a essa em AG11:

=SOMA((W11={1;5;6;7;8})+(Y11={1;5;6;7;8})+(AA11={1;5;6;7;8})+(AC11={1;5;6;7;8})+(AE11={1;5;6;7;8})) ENTER e arraste

Ou você pode simplificar mais nomeando a constante matricial {1;5;6;7;8} e usar o nome dela no lugar: guia Fórmula> Definir Nome > defina um nome qualquer, digamos, "Status" - sem aspas - e na caixa refere-se a, limpe o que já está lá e digite "={1;5;6;7;8}" - sem aspas e OK. Aí é só substituir a constante matricial na fórmula acima e inserir a fórmula, só que agora tem que inserir como fórmula matricial:

=SOMA((W11=Status)+(Y11=Status)+(AA11=Status)+(AC11=Status)+(AE11=Status)) CTRL+SHIFT+ENTER e arraste

ou, se não quiser inserir como fórmula matricial, use a função SOMARPRODUTO:

=SOMARPRODUTO((W11=Status)+(Y11=Status)+(AA11=Status)+(AC11=Status)+(AE11=Status)) ENTER e arraste.

Obs.: Seria mais simples se o intervalo fosse adjacente.

Thiago Goulart · 28 de março de 2017

@Edson Luiz Branco,

Obrigado por responder minhas dúvidas.

A aplicação da primeira opção para a fórmula da coluna AG, atendeu perfeitamente a minha necessidade.

Agora para fins de ampliação de conhecimento, se não for pedir muito, poderia explicar melhor a opção de fórmula MATRICIAL e a função SOMARPRODUTO?

Desculpas pela minha dificuldade em interpretar a sua explicação, mas sou iniciante no uso desta ferramenta e não compreendi como nomear a constante matricial.

Referente a segunda fórmula, localizada na coluna AH, segui a lógica do que foi sugerido na fórmula da AG e fiz isso:

=SEERRO(SOMA(SE(W12={1;5;6;7;8};$X$10;0);SE(Y12={1;5;6;7;8};$Z$10;0);SE(AA12={1;5;6;7;8};$AB$10;0);SE(AC12={1;5;6;7;8};$AD$10;0);SE(AE12={1;5;6;7;8};$AF$10;0))+(SOMA(SE(W12={1;5;6;7;8};$X$10;0);SE(Y12={1;5;6;7;8};$Z$10;0);SE(AA12={1;5;6;7;8};$AB$10;0);SE(AC12={1;5;6;7;8};$AD$10;0);SE(AE12={1;5;6;7;8};$AF$10;0))*(SE(AG12=1;0;SE(AG12=2;0,1;SE(AG12=3;0,2;SE(AG12=4;0,4;SE(AG12=5;0,5;"")))))));0)

Aparentemente deu certo..

Aguardo seu retorno sobre a fórmula da AH e, se for possível, as explicações sobre fórmula MATRICIAL e a função SOMARPRODUTO.

Desde já sou muito grato.

Edson Luiz Branco · 28 de março de 2017

1 hora atrás, Thiago Goulart disse:

se não for pedir muito, poderia explicar melhor a opção de fórmula MATRICIAL e a função SOMARPRODUTO?

Desculpas pela minha dificuldade em interpretar a sua explicação, mas sou iniciante no uso desta ferramenta...

Uma fórmula matricial é uma fórmula que, ao invés de retornar um único valor, pode retornar mais de um valor numa só operação ou fazer diversas operações sem ter que ficar usando colunas auxiliares, etc., e podem ser extremamente poderosas. Num exemplo simples, digamos que você quisesse obter a soma de uma lista de compras. Ao invés de fazer uma coluna extra de totais multiplicando cada linha de quantidade (supor A2:A5) por cada linha de valor unitário (supor B2:B5) e depois somar esses totais, você poderia usar a função SOMA(A2:A5 * B2:B5), só que ao invés de dar Enter (daria erro #VALOR!), usaria ela na forma matricial, dando Control + Shift + Enter, tudo junto. Mas você poderia também, ao invés da função SOMA, usar a função SOMARPRODUTO(A2:A5 ; B2:B5) e dar só Enter. Para essa função não é necessário usar Control + Shift + Enter pois ela naturalmente já é uma função matricial. O Excel têm muitas funções que podem retornar uma matriz, vale a pena estudá-las.

Já o elemento {1;5;6;7;8} é uma constante matricial. Equivale a um intervalo vertical de cinco linhas com cada valor em uma linha que você pode usar normalmente também em fórmulas. Se quisesse simular um intervalo com também com colunas, separe colunas com "\" e as linhas com ";"

2 horas atrás, Thiago Goulart disse:

...não compreendi como nomear a constante matricial.

O que exatamente você não compreendeu? Seguiu a sequencia para criá-la?

2 horas atrás, Thiago Goulart disse:

Referente a segunda fórmula, localizada na coluna AH, segui a lógica do que foi sugerido na fórmula da AG...

Aparentemente deu certo..

...

Aguardo seu retorno sobre a fórmula da AH

Como diz o ditado, "Em time que tá ganhando não se mexe" rsrsr. Então deixe assim. Maas, se quiser simplificar um pouco... sugestão, em AH11:

=SOMA((W11={1;5;6;7;8})*$X$10;(Y11={1;5;6;7;8})*$Z$10;(AA11={1;5;6;7;8})*$AB$10;(AC11={1;5;6;7;8})*$AD$10;(AE11={1;5;6;7;8})*$AF$10)*ESCOLHER(AG11+1;0;1;1+10%;1+20%;1+40%;1+50%)

Thiago Goulart · 28 de março de 2017

@Edson Luiz Branco,

Vou ler e assistir alguns vídeos para me aprofundar sobre as fórmulas matriciais e funções de subproduto.

Sobre a minha falta de compreensão, eu não sei o caminho para "nomear" a fórmula matricial, já que, no meu entendimento, tu nomeia o intervalo, como sugerido em outra resposta sua, por exemplo, pela palavra "Status"..

Realmente, se estamos ganhando, não tem porque mexer, mas por curiosidade utilizei tua sugestão para suprimir ainda mais a fórmula, ficou muito bom.

Obrigado novamente.

Edson Luiz Branco · 28 de março de 2017

@Thiago Goulart, então, lá no YT tem bons canais sobre isso. Um que recomendaria seria o Planilheiros, ele tem diversas vídeo-aulas que tratam desse assunto, se não me engano na área "Excel Avançado"

.

26 minutos atrás, Thiago Goulart disse:

...Sobre a minha falta de compreensão, eu não sei o caminho para "nomear" a fórmula matricial, já que, no meu entendimento, tu nomeia o intervalo, como sugerido em outra resposta sua, por exemplo, pela palavra "Status"

Pois é, Status foi um nome que inventei naquela hora para aqueles valores. Siga exatamente o que disse (repetido em negrito abaixo) e não tem erro.

Em 26/03/2017 às 23:51, Edson Luiz Branco disse:

Ou você pode simplificar mais nomeando a constante matricial {1;5;6;7;8} e usar o nome dela no lugar: guia Fórmula> Definir Nome > defina um nome qualquer, digamos, "Status" - sem aspas - e na caixa refere-se a, limpe o que já está lá e digite "={1;5;6;7;8}" - sem aspas e OK. Aí é só substituir a constante matricial na fórmula acima e inserir a fórmula, só que agora tem que inserir como fórmula matricial:

Faça isso depois faça um teste: Numa célula qualquer, se você digitar =Status e der Enter, o conteúdo da célula mostrará logicamente somente o primeiro valor da constante matricial (1, no caso). Mas se você selecionar cinco células de uma coluna e digitar =Status e der Ctrl + Shift + Enter, todo o intervalo será preenchido, cada célula com um dos valores da constante.