PplWare Mobile

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

                                    
                                

Este artigo tem mais de um ano


Autor: Pedro Pinto


  1. João Marcelino says:

    5* !

  2. Vasco says:

    Muito obrigado, acabei de testar, excelente ! 🙂

  3. Cau says:

    Ola Boa tarde,
    Uma ajuda, tenho 2 coluna em que (coluna A tem valores em 5 linha e, Coluna B com outros valores em 5 linhas.)

    Queria ordenar pelos maiores valores da Coluna A e B ( ou seja, o 1º maior das duas colunas o 2º maior…. ate ao 10º maior) usei a função =maior(a2:a6;1), em que 1 nesta função é o 1º maior.
    O que quero saber é qual a função a usar para saber de que coluna é aquele valor (exemplo o 3º maior).

    Não sei se estou a a fazer a pergunta correcta ou a explicar, quem me poder ajudar envio o ficheiro por mail.

    Obrigado

    Esta iniciativa aos domingos é espetacular

  4. João Pinto says:

    Cau,

    Deixa ver se eu consigo explicar-te como resolveres este problema…
    1º vamos assumir que tens os dados nas células A2:B6
    2º na coluna C vamos colocar esta fórmula na célula C2 e copiar para baixo até à célula C11:

    =MAIOR(A$2:B$6,LIN(A2)-1)

    Isto vai dar-te uma lista do 1º maior até ao 10º valor.

    3º na coluna D, na célula D2, vamos colocar esta fórmula e copiar para baixo, até à célula D11:

    =SE(É.NÃO.DISP(CORRESP(C2,A$2:A$6,0)),ENDEREÇO(CORRESP(C2,B$2:B$6,0)+1,2),ENDEREÇO(CORRESP(C2,A$2:A$6,0)+1,1))

    Isto vai dar-te a informação do endereço da célula de onde os valores na coluna C vieram, isto é, vai devolver-te algo do género $B$4 que te indica que esse valor veio dessa célula.

    Espero que tenhas percebido como se faz isto. Foi um desafio, nunca tinha tido uma situação real destas, apenas com os valores numa só coluna, em duas, nunca tinha experimentado mas consegui resolver.

    Um abraço,

    João Pinto

  5. Cau says:

    Boas João Pinto

    Obrigado mais uma vez,

    resolveu claro, só mudei (,) por (;) na formula, e pedir o nome de cada coluna é possivel em vez de $B$4.

    para contornar fiz isto en E2
    =SEG.TEXTO(D2;2;1) , de me somente em cada linha A e B
    + 1 vez obrigado

  6. João Pinto says:

    O uso da , ou ; depende dos settings regionais do computador.

  7. José Augusto says:

    Desafio

    Embora não entenda a necessidade do Cau o que é certo é que este colocou um bom problema e a resolução do João Pinto faz-lhe essa honra.
    E se juntarmos ao problema inicial a possibilidade de haver números (inteiros) repetidos?
    Com o CORRESP apenas teremos a indicação do endereço do 1º número repetido e não o endereço do 2º número.
    Fica aqui o desafio.

    Bom resto de dia
    José Augusto

  8. Pedro Gameiro says:

    Boas João Pinto, preciso de ajuda. tenho uma coluna com varios valores em numerário e preciso de uma formula que identifique quais desses valores é que somados prefazem um outro valor. Do tipo: numa conta corrente de um cliente tenho varios valores em aberto e o cliente paga x valor mas não identifica quais os valores que prefazem esse x, o ideal era colocar numa celula o valor transferido pelo cliente e na coluna dos valores serem identificadas(realçadas com cor por ex.) quais as celulas que combinadas prefazem esse valor.

    • José Augusto says:

      Caro Pedro Gameiro

      Caso esteja interessado, segue uma primeira abordagem na construção de uma solução mais robusta do que aquela que agora proponho (Excel 2007 e posterior).

      Dados:
      Considere em D2 o valor recebido (45 para pagamento de 20 e 25)
      Considere de D5:D15 os valores a pagar (12, 25, 31, 18, 15, 20, … pode ficar vazio)

      Resolução:
      Coloque em E5 a fórmula
      =SE.ERRO(PROCV($D$2-D$5:D$15;$D$5:$D$10;1;0);””)
      Estenda a fórmula anterior, por cópia, para o domínio E5:E15
      coloque em E2 a seguinte fórmula
      =MAIOR($E$5:$E$15;LIN(A1)) e estenda por cópia a E3

      E2 e E3 terão os valores que somados darão D2 (o valor recebido) isto é 25 e 20 (por esta ordem por ter usado a função MAIOR)

      Completar a solução:
      Coloque em F2 e estenda a F3 a seguinte fórmula
      =CORRESP(E2;$D$5:$D$15;0)
      F2 e F3 terão a indicação da posição dos valores a serem considerados (2º e 6º valor da lista de atrasos)

      Querendo usar formatação condicional use a fórmula
      =É.NÚM(PROCV($D$2-D5:D15;$D$5:$D$15;1;0)) aplicada ao intervalo D5:D15.

      Problema da solução:
      Se o valor recebido fosse 40 em vez de 45, a solução apontaria ainda para os valores 25 e 20. O valor 15 que existe na lista só seria visualizado com a formatação condicional. A fórmula encontra as “soluções” 25+15 e 20+20 embora o 20 só apareça uma vez.

      Espero ter ajudado.

  9. José Augusto says:

    Caro Pedro Gameiro

    A noite não é boa conselheira. Relativamente ao meu post anterior devo algumas correcções:
    1) A referência à célula D$10 deve ser entendida como D$15 na fórmula PROCV.
    2) Para evitar uma solução com erro, em E3 deve colocar-se a fórmula =D2-E2
    A correcção 2 permite que seja encontrada uma solução de entre várias.

    • Pedro Gameiro says:

      Boa tarde José,desde já o meu muito obrigado pela sua ajuda.
      De facto com os valores que indica como exemplo, tudo funciona na perfeição. Mas se utilizar-mos outros valores,incluindo valores a negativo,já não funciona. Tome como exemplo os seguintes valores que vou indicar: Valor recebido: 276,59 Valores em aberto na conta corrente: 110,15 160,23 -25,99 32,20 40,55 17,98 10,50 ora o valor recebido é a soma dos 4 primeiros valores, no entanto a formula não encontra esta conjugação. Se por ex. o valor a pagar for 6,21, a formula deveria indicar o 3º e o 4º valor como solução -25,99+32,20=6,21 Se houver maneira de corrigir isto seria otimo.

  10. José Augusto says:

    Caro Pedro Gameiro

    Segue uma solução que resolve os problemas que colocou e que difere substancialmente da minha primeira abordagem.
    Considerei apenas um máximo de 9 facturas em débito o que irá envolver uma tabela com um nº de linhas igual a 2^9-1=511 e 11 colunas. Por este motivo resolvi criar essa tabela numa folha suplementar “Folha2” e considerar os dados na “Folha1”.

    Dados: (Folha1)
    D2 = Valor entregue = 276,59

    D5:D13 = Facturas
    = {110,15::160,23::-25,99::32,2::40,55::17,98::10,5::0::0}

    Tabela auxiliar: (Folha 2)

    Coloque em A4: =DECABIN(LIN(A1);9)
    Coloque em B4: =SEG.TEXTO($A4;COL(A$1);1)+0
    e por cópia estenda para B4:J4
    Coloque em K4: =SOMARPRODUTO($B$3:$J$3;B4:J4)
    Por cópia, estenda A4:K4 até A513:k513

    Seleccione as células B3:J3 e na barra de fórmulas introduza a seguinte fórmula matricial, terminando com + +

    =TRANSPOR(Folha1!D5:D13)

    A fórmula ficará transcrita entre chavetas e as células B3:J3 deverão ter ficado com os valores das facturas.

    Coloque em K1: =CORRESP(Folha1!$D$2;$K$4:$K$513;0)
    Coloque em A1: =ÍNDICE($A$4:$A$513;K1)
    Coloque em B1: =SEG.TEXTO($A1;COL(A$1);1)+0
    e por cópia estender para B1:J1

    Os valores obtidos na 1ª linha da folha auxiliar são a solução procurada.

    Na Folha1
    Seleccione as células F5:F13 e na barra de fórmulas introduza a seguinte fórmula matricial, terminando com + +

    =TRANSPOR(Folha2!$B$1:$J$1)

    As células F5:F13 ficarão preenchidas com 1 se a factura for para contabilizar ou 0 no caso contrário.

    Com o exemplo do seu post as células terão os valores
    1 1 1 1 0 0 0 0 0.
    Espero ter resolvido o seu problema.
    Cumprimentos

  11. José Augusto says:

    Caro Pedro Gameiro

    Acabei de ver o Portugal x Macedónia (0=0) e fiquei com a boca amarga. Para adoçá-la resolvi brincar um pouco com o Excel(ente passatempo) e simplifiquei o processo descrito nos post’s anteriores. Condensei tudo numa única fórmula matricial que resolve o seu problema.

    Quer experimentar?

    F1: Valor entregue: F1
    As 9 ou menos facturas: F3:F11

    Seleccione as células G3:G11 e copie para a barra de fórmulas a seguinte fórmula:

    =SEG.TEXTO(ÍNDICE(DECABIN(LIN(A1:A511);9); CORRESP($F$1;MATRIZ.MULT(SEG.TEXTO(DECABIN(LIN(A1:A511);9); COL($A1:$I1);1)+0;$F$3:$F$11);0);1);LIN(A$1:A$9);1)+0

    terminando não com o enter mas sim com ctrl + shift + enter

    Assim não precisa da Folha2 para nada.
    Cumprimentos e resto de um bom fim-de-semana.

  12. jose costa says:

    como posso no Excel resolver esta formula pois ela dá zero desde que o critério seja em relação a uma célula
    exp =contar se(a6:a23;<a$4)
    pois necessito saber quantas negativas tive em cada questão mas o valor da questão varia conforme o teste como tal não posso ter um valor fixo mas sim variável numa determinada célula
    se alguém souber agradeço que me explique

  13. Sérgio Possacos says:

    Viva!
    Tenho uma pequena dúvida: como poderei preencher células numa tabela do excel com determinados carateres, por exemplo ‘X’ ou ‘Z’ e no gráfico dessa tabela assumir valores numéricos, 1, 2, … correspondentes a esses carateres? Muito obrigado!

    Sérgio Possacos

Deixe um comentário

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.