Jump to content
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

PT-Team

Formula para o Excel

Recommended Posts

PT-Team

Boas,

Gostaria de pedir a vossa ajuda para criar uma formula em excel que possa fazer o seguinte:

Da seguinte tabela pretendo encontrar e somar os 3 valores mais elevados da Coluna_B mas que sejam correspondentes na Coluna_A.

Coluna_A - Coluna_B

  AAA    -  476

  BBB    -  485

  FFF    -  412

  BBB    -  397

  BBB    -  498

  CCC    -  510

  FFF    -  295

  AAA    -  495

  DDD    -  430

  BBB    -  299

  AAA    -  410

RESULTADO

AAA = 1381

BBB = 1380

CCC =

DDD =

Share this post


Link to post
Share on other sites
jpaulino

Olá,

Assumindo que tens a tabela nas colunas A e B, podes utilizar a formula SUMIF(<area a procurar>; <critério>; <area a somar>)

Para o teu exemplo:

=SUMIF($A$1:$A$11;A13;$B$1:$B$11)

Sendo A13 = "AAA"

Share this post


Link to post
Share on other sites
PT-Team

Caro jpaulino

Obrigado pela rápida resposta, porem ainda me falta um critério nessa formula, o qual não consigo ultrapassar.

A soma só pode ser sobre os três valores mais altos. No caso de A13 existem apenas 3 valores, mas no caso de A14 excitem 4 valores e a formula só pode apresentar a soma de B2+B4+B5, uma vez que B10 é o menor valor dos 4

A formula que define que A15 e A16 ficam em branco não é relevante, pois já a fiz.

Share this post


Link to post
Share on other sites
jpaulino

Não entendi bem! Tu queres fazer o somatório, mas apenas dos que tiverem o resultado maior ?

Se for isso, fazes o cálculo de todas, e depois usas a função LARGE() para te dar o top 3

Share this post


Link to post
Share on other sites
PT-Team

Caro jpaulino,

O que eu preciso é mesmo o TOP 3, para que entendas melhor passo a explicar para que vai servir a formula.

Sou atleta de uma determinada modalidade amadora, e tenho verificado que no final das provas há sempre enganos com a classificação dos clubes vencedores da respectiva prova em cada uma das categorias (na ultima então nem se fala, parecia mais um espectáculo cómico do que uma atribuição de prémios) .

A pontuação das equipas é obtida pela soma dos totais dos 3 melhores atletas em prova desse clube.

Se um clube não tiver 3 ou mais atletas de uma categoria, não tem equipa, logo não é considerado.

Se um clube tiver mais que 3 atletas em de uma categoria em prova, só os 3 melhores são considerados.

Espero que tenhas entendido o que preciso que a formula faça, de qualquer forma vou experimentar a função que me disseste.

Share this post


Link to post
Share on other sites
jpaulino

Ok, acho que já entendi.

Podes usar como já te disse a função LARGE() mas como precisas de um critérios tens de utilizar um array. Sem ser assim só em VBA.

Então vamos por passos:

Primeiro defines um nome para os valores da coluna A - "atletas" e um nome para os valores da coluna B - "resultados" (Menu Insert - Name - Create). Istó é só para simplificar!

Depois utilizas a seguinte formula, estando "AAA" em A14:

=LARGE(IF(atletas=A14;resultados;0);1)+LARGE(IF(atletas=A14;resultados;0);2)+LARGE(IF(atletas=A14;resultados;0);3)

Carregando em CTRL+SHIFT+ENTER para inserir a formula. Ficará assim:

{=LARGE(IF(atletas=A14;resultados;0);1)+LARGE(IF(atletas=A14;resultados;0);2)+LARGE(IF(atletas=A14;resultados;0);3)}

Resumindo ele vai verificar nos atletas se cumprem um critério e caso cumpram devolve o valor mais alto, o segundo mais alto e o terceiro mais alto. Podes usar cada uma individualmente para testares.

Espero que ajude!

Share this post


Link to post
Share on other sites
PT-Team

Caro jpaulino

Era mesmo isso que eu precisava...

Já agora, e não querendo abusar da tua disponibilidade, há alguma formula com que eu consiga obter a posição do atleta, do tipo se a pontuação for a mais alta dá-me o valor 1, se for a segunda mais alta da-me o valor 2, etc...

eu fiz isso com o IF(), mas tenho que colocar tantos IF como atletas que estão em prova, o que é uma grande dor de cabeça, principalmente se nos enganarmos em alguma linha.

Desde já brigado pela ajuda.

Share this post


Link to post
Share on other sites
jpaulino

E como tens a informação, ou seja, como estão os dados onde vais buscar a classificação.

Share this post


Link to post
Share on other sites
PT-Team

os dados estão na Coluna_B (Resultados)

mas a Classificação aparece na Coluna_C (Classificação)

Share this post


Link to post
Share on other sites
jpaulino

O LARGE() dá-te o valor mais alto, e podes depois usar o VLOOKUP para ir buscar o nome.

Share this post


Link to post
Share on other sites
PT-Team

Eu peço desculpa mas não me fiz entender.

O que eu pretendo faer é o seguinte:

Tenho a seguinte tabela:

Coluna_A - Coluna_B

  AAA    -  476

  BBB    -  485

  FFF    -  412

  BBB    -  397

  BBB    -  498

  CCC    -  510

  FFF    -  295

  AAA    -  495

  DDD    -  430

  BBB    -  299

  AAA    -  410

eu pretendo que o Excel faça isto automaticamente:

Coluna_A - Coluna_B - Coluna_C

  AAA    -  476  -  5

  BBB    -  485  -  4

  FFF    -  412  -  7

  BBB    -  397  -  9

  BBB    -  498  -  2

  CCC    -  510  -  1

  FFF    -  295  -  11

  AAA    -  495  -  3

  DDD    -  430  -  6

  BBB    -  299  -  10

  AAA    -  410  -  8

A Coluna_C é a classificação dos atletas, começando pelo que têm a pontuação mais alta até ao que têm a pontuação mais baixa, e que vai variando conforme os valores da Coluna_B vão variando.

Share this post


Link to post
Share on other sites
jpaulino

Humm, já entendi!

Usa a formula RANK(). Na célula c1 coloca o seguinte:

=RANK(B1;$B$1:$B$20;0)

Share this post


Link to post
Share on other sites
PT-Team

Caro jpaulino

Obrigado pela tua ajuda, com ela já consegui atingir os objectivos que me propus atingir e que me fez aprender muita coisa que eu nem sonhava que o excel pudesse fazer.

Se não for abusar demais da tua boa vontade, será que me poderias ajudar em mais dois pequenos pormenores que, embora não sejam essenciais seriam a cereja em cima do bolo.

Eu passo a explicar o que pretendo:

Neste momento, a folha de calculo que criei tem uma folha de dados onde está o Numero do atleta, Nome, Clube, Categoria, Pontos obtidos na primeira parte, Pontos obtidos na segunda parte, Total de pontos, Total de Impactos, Total de Dezes, Total de Novez

Numa segunda folha, já coloquei todos os atletas ordenados por Categoria a que os atletas pertencem e por sua vez ordenados pela pontuação total que têm.

Nesta folha está tudo a funcionar perfeitamente, desde que não haja dois atletas da mesma categoria com os mesmos pontos, situação em que me aparece repetido o atleta que na ordem da primeira folha está primeiro.

a formula que estou a usar é: {=MAIOR(SE(BD_Cat=$L4;BD_Total;0);n)}

n = ordem (1,2,3,...)

$L4 = Categoria do Atleta.

Para se chegar a desempate, em primeiro lugar verifica-se o Total de impactos, se mesmo assim continuarem empatados, verifica-se o numero total de dezes, e se mesmo assim continuarem empatados verifica-se os noves, aquele que tiver mais é o que fica à frente do outro.

A minha pergunta é, será que de alguma forma se pode criar uma formula para verificar essa situação?

O segundo ponto é o seguinte:

Numa terceira folha, tenho a classificação das equipas, também nesta está a funcionar, à excepção de que tenho que introduzir a sigla de um clube para que me apareça o resultado.

A minha pergunta é, será que não consigo com alguma formula obter logo a sigla do clube que ficou em primeiro, segundo e terceiro lugar de cada categoria?

De salientar que para um clube tenha equipa numa determinada categoria necessita ter pelo menos 3 atletas dessa categoria na base de dados e que a soma dos pontos dos três melhores atletas de cada clube em cada categoria é a pontuação que a equipa obtêm no final.

Share this post


Link to post
Share on other sites
jpaulino

Eu compreendo essas questões, mas sem o ficheiro para testar é dificil recriar o exemplo. Podes criar um exemplo e disponibilizar o ficheiro ?

Share this post


Link to post
Share on other sites
PT-Team

Ainda...

Na ultima prova que organizamos a atribuição das classificações as equipas deu buraco...  😳

Apareceu uma equipa à ultima da hora, e eu não me apercebi disso, quando estavam a entregar os prémios é que se deu pelo erro.

Share this post


Link to post
Share on other sites
saltao

Boa tarde

Tenho uma situação identica ao do colega.

Nós organizamos diversos concurso de pesca desportiva. Para fazer a classificação e apuramento de resultados utilizo uma folha de excel.

Na atribuição de premios, temos premios individuais e colectivos.

Não consigo resolver o problema das equipas, A classificação é feita pela soma classificação dos melhores 4 elementos

Share this post


Link to post
Share on other sites
jpaulino

Boa tarde

Tenho uma situação identica ao do colega.

Nós organizamos diversos concurso de pesca desportiva. Para fazer a classificação e apuramento de resultados utilizo uma folha de excel.

Na atribuição de premios, temos premios individuais e colectivos.

Não consigo resolver o problema das equipas, A classificação é feita pela soma classificação dos melhores 4 elementos

Boas,

Abre uma nova questão e dá mais detalhes do problema. Se possivel mostra o ficheiro, que podes colocar num servidor do tipo rapidshare, easyshare, etc, e depois deixas aqui o link.

Share this post


Link to post
Share on other sites
PT-Team

O meu problema neste momento é só com os atletas empatados e com a forma de saber automaticamente que clubes é que têm equipas e quais não.

Se alguém me poder ajudar, agradeço.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.