Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

46
22046
Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

O Excel, enquanto ferramenta dedicada ao trabalho com análise de dados, permite que o usuário execute diversas atividades diferentes com os recursos disponíveis no software. Útil tanto para usuários iniciantes quanto para os mais avançados, o programa permite desde o simples acompanhamento das despesas da casa, até a execução de gigantes planilhas empresariais.

Um dos recursos mais utilizados no Excel, em diversos ambientes diferentes, é o sorteio. Existe uma forma de fazer sorteios sem repetição usando as funções ALEATÓRIOENTRE e ORDEM, e é esta forma de fazer sorteios que iremos te ensinar a partir de agora neste post.

O que são as funções aleatorioentre e ordem

Antes de entrarmos no processo de execução do sorteio vamos primeiro detalhar como funcionam cada uma das funções.

1 – aleatórioentre

Em primeiro lugar, a função ALEATÓRIOENTRE. Quando utilizada corretamente, esta função retorna um número aleatório inteiro, entre o intervalo de números especificado. E cada vez que a planilha for calculada, um novo número inteiro será retornado. Sua sintaxe é a seguinte:

ALEATÓRIOENTRE (número inicial, número final)

Traduzindo o significado de cada um dos termos:

1 – número inicial: número onde se inicia o intervalo especificado.

2 – número final: número que encerra o intervalo.

Para tornar mais fácil o entendimento, sua sintaxe e funcionamento, vamos ver um exemplo. Neste caso a função vai exibir um número aleatório entre 1 e 100, toda vez que a planilha for calculada.

Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

2 – ordem

Assim como a ALEATÓRIOENTRE, a função ORDEM também é bastante simples. Basicamente ela retorna a posição de um número em uma lista de números. A ordem de um número revela seu tamanho em relação aos outros valores da lista. Ou seja, se você fosse classificar manualmente, a ordem seria sua posição). Sua sintaxe é a seguinte:

ORDEM (núm, ref, [ordem])

Traduzindo o significado de cada um dos termos:

1 – núm: O número cuja posição na lista se deseja encontrar. Valor obrigatório.

2 – ref: Uma matriz ou referência a uma lista de números – valores não numéricos serão ignorados. Valor obrigatório.

3 – ordem: um número que especifica como posicionar um número em uma ordem. Valor opcional.

Para tornar mais fácil o entendimento, sua sintaxe e funcionamento, vamos ver um exemplo. Neste caso a lista contém 5 números e nós queremos inicialmente qual posição o 3,5 ocupa. Para isso aplicamos a fórmula e o resultado é 3, ou seja, o número 3,5 ocupa a 3ª posição na lista. Da mesma forma, no segundo exemplo queremos a posição do número 7 na lista. Resultado: 5, ou 5ª posição.

Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

Fazendo sorteios sem repetição

Para começarmos um sorteio precisamos de uma lista, como esta:

Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

Nosso objetivo é sortear entre esses nomes, sem repetição. Se usarmos apenas a função ALEATÓRIOENTRE, é possível que alguns valores retornem repetidos. A alternativa que usamos aqui para que os números não voltem repetidos é adicionar uma fração ao número sorteado. Dessa forma, o decimal sempre irá diferenciar os números inteiros. Veja como fica a função.

Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

Depois de sortear, nós precisamos classificar cada um dos números gerados.

Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

Tendo a classificação, basta usar a função PROCV para localizarmos qual o nome correspondente àquela posição.

Como fazer sorteio sem repetição com as funções aleatorioentre e ordem

E pronto! Você tem uma nova lista devidamente sorteada, e sem repetição. Lembrando que, a cada vez que uma fórmula for executada, o sorteio será refeito e um novo resultado será gerado.

 

COMPARTILHAR
Artigo anteriorMapa Estratégico do BSC
Próximo artigoComo Calcular Adicional Noturno no Excel
Rafael Ávila
Administrador e turismólogo, apaixonado pelas possibilidades que o Excel dá para gestores, empreendedores e estudantes. É sócio diretor da área de planilhas na LUZ - Planilhas Empresariais (luz.vc) e é o responsável pelo desenvolvimento das mais de 150 planilhas com foco empresarial e pessoal comercializadas em seu site. Também ministra os cursos online de Excel (cursos.luz.vc) e gosta de ajudar milhares de planilheiros por meio do Fórum e Blog da LUZ.

46 COMENTÁRIOS

  1. Vou usar as dicas acima para definir quais os itens de estoque a serem contados diariamente (contagem periódica) de forma aleatória, pelo menos uma vez no período e sem repetição. Obrigado.

    • Essa é uma boa aplicação para essa fórmula. Só tome cuidado com o recálculo da fórmula toda vez que uma ação é executada. Nesses casos eu costumo ir na guia FÓRMULA e deixar o cálculo manual, assim não corro o risco de me perder nas mudanças dos itens aleatórios.

  2. Boa noite Rafael, eu poderia usar essa formula, para fazer um sorteio de brindes para clientes também? é que minha mãe tem uma loja virtual, e ela as vezes dá brindes para os clientes, gostaria de dar essa idéia para ela se for possível usar a formula para isso, acho que poderia ser feito algo assim, o que você acha? ah, e outra coisa,sempre as ultimas células vão ter o erro #N/D?

    At,

    • Oi Cláudio, desculpa pela demora, mas não havia visto essa sua mensagem. Pode usar para fazer sorteios sim, é uma maneira simples e prática.

      Em relação ao N/D, normalmente ele ocorre quando você busca um valor não definido, então é um erro por estar buscando um valor onde você não inseriu nada

  3. Alguém aí pode me ajudar, sei que vcs são capazes. Por saber várias funções no Excel, como sou iniciante, vai levar tempo até aprender.

    Quero fazer uma planilha Sudoku só que com números completando as linhas e colunas de 1a100, quem conhece o sudoku sabe que não pode repetir números na mesma coluna e na linha, se alguém conseguir e me enviar o arquivo no Excel já pronto, fico agradecido..
    Mas se não for possível gostaria de saber quais função utilizar para fazer tal planilha.
    Meu email é. Md-santos@hotmail.com

  4. Ola Rafael, parabéns pela dica, deu muito certo aqui. Veja se você pode me ajudar: tem 9 nomes e pretendo distribui-los durante uma semana inteira em 3 momentos do dia (3×7=21) ai quando uso esse método que você ensinou ele sempre repete mais de uma vez os números. Fiz a função para tentar barrar essa repetição:
    se(cont.se(números sorteados)>1;0;aleatórioentre(1;21)).
    mas ele so conta e não impede de repetir os numeros ja sorteados se alguem poder me ajudar sera um grande aprendizado para todos.

  5. Muito bom! Só tem um problema. O sorteio não fica justo. Desse jeito você tende a fazer com que os nomes mais baixos fiquem mais atrás no sorteio. Dependendo do seu objetivo e se existirem muitos números dá até pra ignorar. Abraço!

    • Oi Jackson, obrigado pela dica. Infelizmente essa é a forma que eu conheço, caso você conheça alguma solução melhor para compartilhar aqui com o pessoal do blog será ótimo. Abraços

      • Estava usando uma planilha antiga e refiz esse problema e tive uma ideia que envolve 2 (ou 3) pequenas mudanças:
        1. Quando escolher a faixa de números aleatórios, escolher uma faixa enorme (um milhão ou mais).
        2. Somar ao valor da ordem um valor aleatório. Por exemplo:
        =ALEATÓRIOENTRE(1;1000000)/1000000
        3. (opcional) Só depois desses passos somar as frações da função Ordem. De preferência usar uma fração ainda menor (dividir por exemplo por 10.000.000)

        Isso faz com que a chance de um empate fique em 1 em 1 milhão. E mesmo no caso de empate o modo de desempate será aleatório o que mantém o sorteio justo. Existe a minúscula chance (1 em um bilhão) do número aleatório também sair igual o que acaba favorecendo o nome mais alto.

        A vantagem é que qualquer um pode usar apenas o 1º passo se o seu problema não for tão complexo, ou só os primeiros dois. E nada impede de usar números ainda maiores para aumentar ainda mais a chance de um sorteio justo.

        Enfim, achei razoavelmente simples de implementar e já atende sorteios de amigo oculto, loterias e a maioria esmagadora dos casos.

  6. Me deparei com o problema do Jackson sobre a justiça do sorteio. Foi bastante fácil resolver: na coluna A relacionei os 18 nomes que precisava ordenar aleatoriamente. Na coluna B, utilizei a fórmula aleatorioentre (1, 10000). Apliquei a mesma fórmula nas colunas C e D, ou seja, fiz três sorteios para cada nome. Na coluna E, somei os três resultados e dividi por 30, pra deixar os valores finais numa escala de 0 a 1000. Copiei os dados da coluna E e colei como valor na coluna F. Apesar de ser possível usar a fórmula ordem, simplesmente apliquei um filtro na última coluna e organizei toda a tabela do menor para o maior valor. Não fiz os cálculos para saber a probabilidade de um número de repetir nesse caso (são 54 sorteios, três pra cada um dos 18 nomes), mas sei que são baixas.

    • Acabei de descobrir, inclusive, que sou muito burro. Basta usar a fórmula aleatório(), que retornará um número qualquer entre 0 e 1, copiar e colar valores, ordenar do menor para o maior (ou vice-versa) e pronto. As chances de repetição são mínimas. Sendo o suficiente somente um sorteio.

    • Oi Rodrigo, realmente para uma quantidade grande de dados assim é um pouco mais complicado e as repetições acabam ocorrendo. Não sei outra maneira de te ajudar nesse momento.

  7. Ola, preciso fazer uma tabela de números aleatórios, porem com apenas números indicados como por exemplo: 1,3,5,45,23,60,58,7.. Serão apenas estes números que irão ser escolhidos aleatórios. Como proceder?

    • Oi Teo, não sei se é possível utilizar o aleatório entre usando um intervalo de células onde você pode colocar esses valores. Acho que não é possível…mas vale tentar

      • é preciso montar uma lógica. Seguindo seus número de exemplo. cria-se uma lista sequencial tipo A1 1; A2 3; A3 5; A4 45; A5 23, A6 60; A7 58; A8 7… lembrando que os números 1,3,5,45,23,60,58,7.. estarão na coluna B… (B1 até B8 respectivamente)… depois sorteia-se a coluna A… e quem corresponder (na coluna B) será o sorteado. Espero que tenha entendido…

    • é preciso montar uma lógica. Seguindo seus número de exemplo. cria-se uma lista sequencial tipo A1 1; A2 3; A3 5; A4 45; A5 23, A6 60; A7 58; A8 7… lembrando que os números 1,3,5,45,23,60,58,7.. estarão na coluna B… (B1 até B8 respectivamente)… depois sorteia-se a coluna A… e quem corresponder (na coluna B) será o sorteado. Espero que tenha entendido…

    • É preciso montar uma lógica Seguindo seu número de exemplo. Cria-se uma lista sequencial tipo A1 1; A2 3; A3 5; A4 45; A5 23, A6 60; A7 58; A8 7 … lembrando que os números 1,3,5,45,23,60,58,7 .. estarão na coluna B … (B1 até B8 respectivamente) … depois sorteia-se uma coluna A. .. e quem corresponder (na coluna B) será o sorteado. Espero que tenha entendido

  8. Boa noite

    Gostaria de saber se é possivel…
    fazer uma lista com mais de 300 itens

    e da lista gerar uma tabela
    sem repetir nomes com pelo menos 15 itens
    tipo um bingo de Itens…!!!???

    • Oi Thiago, acredito ser possível sim. Você pode seguir o passo a passo desse post para ver exatamente como fazer esse processo. Normalmente listas sem repetições são mais chatinhas de se fazer.

    • O jeito mais simples ao meu ver é o seguinte: coloque em uma mesma coluna os 300 itens (300 linhas). Na coluna ao lado, use a função =ALEATÓRIOENTRE(1;300) e copie para todas as 300 linhas. Depois, selecione a coluna que contém a função ALEATÓRIOENTRE e clique na opção “classificar do menor ao maior”>>”expandir seleção”. Pronto, os dados da coluna que contém os itens foram sorteados. Sempre que quiser realizar um novo sorteio, basta fazer essa classificação novamente.

  9. Oi, boa tarde.
    Eu tenho uma lista de números de 1 à 20. Deste 20 números, escolhi 10 números aleatórios sem repetições em outro coluna. Gostaria de saber se tem alguma fórmula que traga os outros 10 números que restaram para uma outra coluna, preenchendo o número de células correspondente e sem repetições… Pode ser com nomes tbm. Atenciosamente, Paula Oliveira. meu email: paula.oliverh@gmail.com

    • Oi Paula,

      você pode utilizar um PROCV para buscar os valores que foram sorteados e usar a condicional SE junto para avaliar esse valor. Se ele tiver sido encontrado, então retorna zero, se não, pode retornar o valor da primeira lista

  10. Estou criando uma planilha de nomes para sorteio, teria a planilha de Sorteio, de nomes dos participantes, e a planilha de sorteados, assim a cada vês que uma pessoa for sorteada ira para automaticamente para a planilha de sorteados na planilha de sorteio teria o botao de Sortear e na planilha de sorteados um botão para limpar nomes. ja fis ate certo mas os nomes estao repetindo e os que estao sendo sorteados nao estao indo para a planilha de sorteados poderia me ajudar obrigado Sergio

    • Oi Sergio,

      você pode criar uma macro para aplicar a função aleatórioentre do seu intervalo e colocar o nome sorteado na planilha dos sorteados. Você também pode criar uma macro com a funcionalidade de limpar os nomes. Agora, essa parte de criar um mecanismo para os nomes não se repetirem é um pouco mais complicada. Você pode tentar usar uma condicional para validar se o nome sorteado não está na lista dos sorteados ainda.

  11. ola boa noite por favor peço sua ajuda em um comando no excel: aleatório entre 1 e 90 que os números não se repitam na mesma linha e na coluna, sao 15 numeros ao todo em cada linha, sao 6 linhas e 15 colunas quero fazer 6 linhas e 15 colunas depois mais 6 linhas e 15 colunas e assim por diante 4 vezes é uma amostragem pro meu trabalho de estatistica os numeros podem aparecer na proxima linha e coluna mas cada bloco tem que ter numeros unicos, por favor estou aflita com esse trabalho.

    • Oi Cissa, acredito que você possa fazer a lógica similar a que mostramos no post, fazendo uma modificação na fórmula da aleatoriedade e gerando o ordenamento de todos os valores sorteados e depois colocando eles nesses campos

  12. olá Rafael eu sou novo em excel e gostaria de saber se da para fazer um sorteio de algo para um ganhador só, é que, assim, pelo que eu entendi, esse sistema serveria para 6 ganhadores? Lembrando que eu sou um noob.
    gostei muito desse metodo reproduzi compreendi as formulas so nao entendi como extrair um ganhad

  13. Bom dia, consigo aplicar as funções ensinadas para montar uma tabela com um numero X de pessoas e um numero X de atividades, onde uma pessoa não possa repetir mais de 2x ?

DEIXE UMA RESPOSTA

Por favor, escreva o seu comentário
Por favor, digite seu nome