PplWare Mobile

Dicas do Microsoft Excel é ao Domingo…no Pplware – 1


Pedro Pinto

Pedro Pinto é Administrador do site. É licenciado em Engenharia Informática pelo Instituto Politécnico da Guarda (IPG) e obteve o grau de Mestre em Computação Móvel pela mesma Instituição. É administrador de sistemas no Centro de Informática do IPG, docente na área da tecnologia e responsável pela Academia Cisco do IPG.

Destaques PPLWARE

  1. beirao says:

    Na sequência deste post, peço ajuda para ultrapassar a seguinte dificuldade, certamente por aselhice minha. Situação: pauta de uma turma e quero calcular para cada aluno a média ponderada obtida em três testes. Num dos testes o aluno faltou e por isso coloco “Faltou” na respectiva célula. Como faço para que o Excel assuma o valor de “0” na célula onde coloquei “Faltou” e fazer os cálculos em conformidade? Não consigo que nestes casos o resultado seja outro senão “#VALOR!”. Obrigado.

    • André Lopes says:

      Boas! Penso que tenhas que colocar SE a célula for = faltou o valor é zero ou nulo.

      Mas por um lado estou um bocado confuso com a tua explicação.

      1- aluno tem notas, 10..12..14 – célula à parte para a media (3 células) para essas 3 notas do primeiro aluno calculas o mesmo
      2- aluno tem notas, 11..11..15 – aqui o mesmo
      3- aluno tem notas, 9..7..0 – e aqui novamente o mesmo

      Ou seja, não entendo a dificuldade.

      Penso que a sua dificuldade esteja em colocar faltou em vez do número 0

      • Paulo Correia says:

        Em português não sei a tradução, mas existe uma fórmula no Excel que permite calcular médias baseadas em critérios, que neste caso seria:

        =AVERAGEIF(A2:C2;”>0″)

        A média dos valores que sejam maiores de zero.

        Julgo que em português seja mediase() ou media.se()

      • paulo says:

        Se não tiver outros 0 pode sempre formatar o campo fazendo que quando é 0 apareça escrita faltou.

    • Hugo Cura says:

      Considerando que o Excel é em português, que as colunas de A a C são os testes 1 a 3 e que o 1º aluno se encontra na linha 2:
      =MÉDIA(SE($A2="Faltou";0;$A2);SE($B2="Faltou";0;$B2);
      SE($C2="Faltou";0;$C2))

      Para os restantes é só arrastar, as colunas estão já bloqueadas.

      • beirao says:

        Obrigado. A média é ponderada, mas foi uma boa ajuda. Obriga a criar mais colunas. Mas, repito, obrigado pela ajuda.

        • Leandro Pereira says:

          =SE($A1=”Faltou”;0;$A1*0.3)+SE($B1=”Faltou”;0;$B1*0.3)+SE($C1=”Faltou”;0;$C1*0.4)

          já entra com ponderação de 0,3 no primeiro 0,3 no segundo e 0,4 no último

        • Hugo Cura says:

          Sabendo que a função MÉDIA utiliza, neste caso, 1/3 para cada parte, supondo que se queria 0.5+0.25+0.25, basta uma regra de 3 simples para ajustar os factores:
          1.5, 0.75, 0.75

          Ficando então:
          =MÉDIA(SE($A2="Faltou";0;$A2*1.5);
          SE($B2="Faltou";0;$B2*0.75);
          SE($C2="Faltou";0;$C2*0.75))

          De outra forma, consegue-se fazer o mesmo facilmente com a função SOMARPRODUTO onde é necessário ter uma coluna com as ponderações e fazer =SOMARPRODUTO(#intervalo-cotações#,#intervalo-ponderações#)/SOMA(#intervalo-cotações#). Neste caso, pode ser mais trabalhoso prever a string “Faltou”, mas dará de certeza.

      • Pedro Soares says:

        A juntar ao que o resto do pessoal já disse, em relação ao SE para resolver a questão do faltou, só queria relembrar que existe a função SOMARPRODUTO que permite o cálculo de médias ponderadas. Esta função leva dois argumentos: um argumento é a lista de células com as notas do aluno, e o outro argumento leva as células (ou matriz) com as ponderações em formato decimal (0,3 para 30%, …).
        Quanto às colunas adicionais dá sempre para as esconder com CTRL+0, e assim a folha de cálculo fica mais apresentável. 🙂

    • Leandro Pereira says:

      Come é um caso simples, o modo mais rápido é mesmo “=soma(intervalo)/3”, a função soma assume qualquer valor numérico como zero.

    • José Augusto says:

      O Excel possui uma outra capacidade pouco explorada: as fórmulas matriciais que diferem das outras pois após a sua escrita valida-se com [Ctrl]+[Shift]+[Enter] em vez do tradicional [Enter]. Para resolver a questão da média ponderada, considerando a nota F (zero), considerem-se os pesos de cada teste colocados nas células C2, D2 e E2 (C2:E2) e considere-se nas células C5, D5 e E5 (C5;E5) as notas do aluno. A fórmula colocada em F5 que dá a média ponderada será então:

      =SOMA(SE(É.NÚM(C5:E5);C5:E5*(C$2:E$2);0))/SOMA(C$2:E$2)
      ou
      =SUM(IF(ISNUMBER(C5:E5),C5:E5*(C$2:E$2),0))/SUM(C$2:E$2)

      que após validação com [Ctrl]+[Shift]+[Enter] apresentará o seguinte aspecto
      {=SOMA(SE(É.NÚM(C5:E5);C5:E5*(C$2:E$2);0))/SOMA(C$2:E$2)}
      ou
      {=SUM(IF(ISNUMBER(C5:E5),C5:E5*(C$2:E$2),0))/SUM(C$2:E$2)}

      Espero que ajude

  2. Ricardo Costa says:

    Excelente.Continuem

  3. Renato Alberto says:

    Daqui a bocado mais vale mudar o nome da rubrica para “dicas do Microsoft Office” Hehe

  4. Miguel Goyanes says:

    E as do Word? Foram “abandonadas”?

    As de Excel também são bem vindas…

  5. tony says:

    a procura vertical é o mais indicado desde que tenha uma tabela auxiliar com na coluna A1 faltou e na coluna B2 0
    e assimm sucessivamente nas celulas seguintes e mito mais poderoso que o se leia as instruções desta função é a mais usada as tabelas IRS são feitas com ela agora tenha em atenção que esta função tanto trabalha por intervalos como com valores exactos depende se escreve verdadeiro ou falso

  6. Pedro Almeida says:

    Antes de mais parabéns pela iniciativa desta rubrica.

    Tenho um folha de cálculo em que um dos campos é formatado com data.
    O que pretendo é que os utilizadores evitem ter de colocar o “/” ou o “-” entre o dia, mês e ano.

    Quero introduzir: 211211
    Quero que apareça: 21-Dez-2012

    A maneira que eu formatei foi a seguinte:
    Fui á célula em causa, seleccionei formatar células, escolhi personalizado e coloquei o seguinte formato:
    00″-“00”-20″00

    Desta forma consigo que o utilizador coloque apenas os números 211211 evitando colocar o “-” que o resultado é 21-12-2011.

    O que pretendo é que no resultado final apareça Dez e não o número 12. Alguém sabe como fazê-lo?

    • F says:

      Para formatar a vista da entrada de data é só necessarioescolher a formatação data.
      Neste caso e no mesmo ano sera necessario colocar dia e mes e a ceula assumirá o ano actual.

      • Pedro Almeida says:

        Se escolher a formatação data obriga-me a introduzir manualmente o “-” que é qo que eu não pretendo.

        quero escrever 100112
        quero que o resultado apareça 10-Jan-12

        • ZLS says:

          Já considerou a hipótese de criar uma macro, que fizesse a conversão da string (os valores introduzidos devem ser em texto, sempre com 6 caracteres) em ‘data’, e, seguidamente que alterasse op formato da célula para data?

  7. paulo g. says:

    Se fizesses o mesmo tutorial em LibreOffice Calc será que todos os leitores o liam? Quem não o lesse perderia a tua explicação porque as funções são as mesmas e a forma de o fazer também poderá ser a mesma.
    O ideal seria mesmo colocar os 2 lado a lado e fazer a comparação directa. Eu não posso fazer essa comparação porque não comprei a licença de utilização de qualquer M$Office, mas como tenho conta no hotmail poderia fazê-lo, através do Skydrive (neste caso ainda bem que apareceu o Google Docs…).

  8. Ilidio Freire says:

    Tenho um problema que creio pode ser resolvido em Excel.

    SE A1>6 e <10 então (A1-6)x2,5 + (6×3)= X (X é a minha incógnita)
    Expondo o problema por palavras é assim:
    Se a céluna A1 for maior que 6 e menor que 10 então A1 menos
    6 vezez 2,5 mais 6 vezes 3 é igual a X.

    Alguem pode ajudar?

    • José Augusto says:

      Na versão Excel Portuguesa uma solução para o que pretende pode ser dada por

      =SE(E(A1>6;A16,A1<10),(A1-6)*2.5+(6*3),"????")

      em que, caso a célula A1 apresente um valor inferior a 6 ou superior a 10 dará ????. Caso contrário calculará o valor X pretendido.

  9. Paulo Gama says:

    Seguindo com as dúvidas sobre média ponderada no excel, gostaria de saber se alguém conhece solução para o seguinte:

    É possível calcular média móvel ponderada (usando tres períodos) excluindo-se valores de célula = zero? Explicando melhor: é possível desconsiderarmos uma célula com retorno zero no cálculo (a fórmula consideraria como denominador o retorno de célula de 2 períodos no lugar de 3)?

    Grato

    • José Augusto says:

      Considerem-se os pesos em A5:A7 e os valores em B5:B7. Então a fórmula poderia ser:

      =SOMARPRODUTO(A5:A7;B5:B7)/SOMA.SE(B5:B7;”0″;A5:A7)

      em que o denominador só conta com os pesos associados a valores não nulos.

      Espero que esta solução lhe sirva.

    • José Augusto says:

      Considerem-se os valores em (B5:B7) e três períodos.
      A fórmula [matricial] para o cálculo da média móvel ponderada pode ser a seguinte:

      =SOMARPRODUTO(B5:B7;{1;2;3})/SOMA((B5:B70)*{1;2;3})

      Esta fórmula só produz resultado correcto se for validada com em vez de . A fórmula aparecerá com o seguite aspecto:

      {=SOMARPRODUTO(B5:B7;{1;2;3})/SOMA((B5:B70)*{1;2;3})}

      Como exemplo assumam-se os valores 11 0 16.
      A fórmula dará (11×1+0x2+16×3)/(1+0+3)= 14,75.

    • José Augusto says:

      No post anterior não apareceu a maneira de validar a fórmula.
      A validação da fórmula é com ctrl + shift + enter em vez de enter

    • José Augusto says:

      O editor que nos disponibilizam prega-nos partidas. Os símbolos maior e menor pura e simplesmente não são aceites e o pior é que tudo o que está entre esses símbolos desaparece.
      Outra correcção ao post: em vez de B70 é B7 sinal de menor e sinal de maior (para fazer o símbolo de diferente) e só depois o zero. Em resumo B5:B7 diferente de zero.

      Espero agora que tudo esteja em condições.

  10. miguel says:

    boa tarde.
    tenho tentado fazer uma conta e não estou a achar solução.

    digamos que tenho 3 exames para fazer.
    no primeiro tive 15valores, no segundo tive 12valores… mas eu quero ter média de 15 valores no final.
    que nota tenho de ter no 3 exame para que a média seja a pretendida?
    (tentei 15v+12v+15(pretendido)/3 mas isto dá a média dos 3 e não o valor que preciso ter para o final ser 15. e claro que tento como nota máxima 20v.

    Tenho materias que fazem media 2 disciplinas e outras que fazem 3 disciplinas.

    para as duas disciplinas eu faço a média, inserindo a nota que tive, e a nota que pretendo ter e ele indica a nota final.

    mas para 3 exames eu não sei.
    o excel (2010) está em português.

    • José Augusto says:

      A1 <– 15
      B1 <– 12
      C1 <– =3*D1-A1-B1
      D1 <– 15 (média pretendida ou 14,5 para arredondar depois)
      C1 aparecerá com o valor 18 se D1 for 15 ou 16,5 se D1 for 14,5.

  11. José Augusto says:

    O editor que nos disponibilizam prega-nos partidas. Os símbolos maior e menor pura e simplesmente não são aceites e o pior é que tudo o que está entre esses símbolos desaparece.
    Outra correcção ao post: em vex de B70 é B7 sinal de menor e sinal de sinal de maior (para fazer o símbolo de diferente) e só depois o zero. Em resumo B5:B7 diferente de zero.

    Espero agora que tudo esteja em condições.

  12. Bruno Martins says:

    Ótima rubrica, espero vir a aprender algumas coisas com ela. Força nisso PplWare 🙂

  13. JGA says:

    E para achar numa lista de 10 numeros por exemplo os 3 menores.

    Ou seja , de 11 12 13 14 15 16 qual a formula para ele me dizer quais os 2 ou 3 menos ou maiores numeros?

    • José Augusto says:

      Usando o Excel versão portuguesa e considerando os valores colocados no intervalo C1:C6 pode usar a seguinte fórmula
      =MAIOR($C$1:$C$6;LIN(A1))
      que lhe dará como resultado o 1º maior valor
      Ao copiar a fórmula para as linhas seguintes obterá sucessivamente o segundo maior, o terceiro maior, …
      Pode também usar a função MENOR em vez de MAIOR.
      Espero ter ajudado.

  14. carolyne says:

    Tenho determinados valores em uma linha e preciso de uma fórmula que me dê o menor valor diferente de zero, como faço?

    • José Augusto says:

      Cara carolyne

      O que pretende consegue-se a partir de funções matriciais.
      Suponha que tem os valores ( 6 5 0 0 9 3 0 ) em D7:J7.
      Introduza a seguinte fórmula validando-a com CTRL+SHIFT+ENTER em vez de apenas ENTER:
      =MÍNIMO(SE(D7:J70;D7:J7;MÁXIMO(D7:J7)))
      a fórmula passará a ter o seguinte aspecto:
      {=MÍNIMO(SE(D7:J70;D7:J7;MÁXIMO(D7:J7)))}
      De acordo com o exemplo, obterá o valor 2.
      Cumprimentos.

    • José Augusto says:

      De acordo com o exemplo o valor obtido é 3.

    • José Augusto says:

      Na fórmula o símbolo menor “<" desapareceu!!

      =MÍNIMO(SE(D7:J7 < 0;D7:J7;MÁXIMO(D7:J7)))

  15. Vítor Rodrigues says:

    Alguém me sabe dizer como colocar o sub-total de uma coluna numa folha impressa numa impressora?

Deixe uma resposta

O seu endereço de email não será publicado.

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

Aviso: Todo e qualquer texto publicado na internet através deste sistema não reflete, necessariamente, a opinião deste site ou do(s) seu(s) autor(es). Os comentários publicados através deste sistema são de exclusiva e integral responsabilidade e autoria dos leitores que dele fizerem uso. A administração deste site reserva-se, desde já, no direito de excluir comentários e textos que julgar ofensivos, difamatórios, caluniosos, preconceituosos ou de alguma forma prejudiciais a terceiros. Textos de caráter promocional ou inseridos no sistema sem a devida identificação do seu autor (nome completo e endereço válido de email) também poderão ser excluídos.