Neste artigo, vamos falar sobre algumas fórmulas diferentes para gerar números aleatórios sem repetição no Excel. Como você provavelmente sabe, o Microsoft Excel possui várias funções para gerar números aleatórios, como ALEATÓRIO, ALEATÓRIOENTRE e RANDARRAY. No entanto, não há garantia de que o resultado de qualquer função será duplicado.
Este tutorial explica algumas fórmulas para criar uma lista de números aleatórios exclusivos. Observe que algumas fórmulas funcionam apenas na versão mais recente do Office 365, enquanto outras podem ser usadas em qualquer versão do Excel 2019, 2016, 2013 e anteriores.
Obtenha uma lista de números aleatórios únicos com etapas predefinidas
Funciona apenas nas versões mais recentes do Excel 365 que oferecem suporte a matrizes dinâmicas. Se você tiver o Microsoft 365, a maneira mais fácil de obter uma lista de números aleatórios exclusivos é combinar 3 novas funções de matriz dinâmica: CLASSIFICARPOR, SEQUÊNCIA e RANDARRAY:
= CLASSIFICARPOR (SEQUÊNCIA (n); RANDARRAY (n))
Onde n é o número de valores aleatórios que você deseja obter.
Por exemplo, para criar uma lista de 5 números aleatórios, use 5 para n:
= CLASSIFICARPOR (SEQUÊNCIA (5), RANDARRAY (5))
Insira a fórmula na célula superior, pressione a tecla Enter e os resultados irão transbordar automaticamente sobre o número de células especificado.
Abaixo, a fórmula realmente classifica os números de 1 a 5 em ordem aleatória.
Na fórmula acima, só se define quantas linhas forem preenchidas. Todos os outros argumentos são deixados com seus valores padrão, o que significa que a lista começará em 1 e será incrementada em 1. Se quiser um primeiro número e um incremento diferente, defina seus próprios valores para o terceiro (início) e o quarto (etapa) argumentos da função SEQUÊNCIA.
Por exemplo, para começar em 100 e aumentar em 10, use esta fórmula:
= CLASSIFICARPOR(SEQUÊNCIA(5;;100;10);RANDARRAY(5))
Como funciona esta fórmula:
Trabalhando de dentro para fora, a fórmula faz o seguinte:
- A função SEQUÊNCIA cria uma matriz de números sequenciais com base no valor inicial especificado ou padrão e também no quarto argumento (etapa). Essa sequência vai para o argumento de matriz da função CLASSIFICARPOR.
- A função RANDARRAY cria uma matriz de números aleatórios do mesmo tamanho que a sequência (5 linhas e 1 coluna no caso do exemplo acima). Os valores mínimo e máximo não importam, portanto, podem ser deixados como padrão. Esta matriz vai para o argumento por_matriz de CLASSIFICARPOR.
- A função CLASSIFICARPOR classifica os números sequenciais gerados por SEQUÊNCIA usando uma matriz de números aleatórios produzidos por RANDARRAY.
Lembre-se de que esta fórmula cria uma lista de números aleatórios não repetidos com uma etapa predefinida. Para contornar essa limitação, use uma versão avançada da fórmula descrita abaixo.
Crie uma lista de números aleatórios sem repetição
Essa técnica funciona apenas nas versões mais recentes do Excel 365 que oferecem suporte a matrizes dinâmicas.
Para gerar números aleatórios no Excel sem duplicados, use uma das fórmulas abaixo.
Inteiros aleatórios:
=ÍNDICE(ÚNICO(RANDARRAY(n^2;1;min;max;VERDADEIRO));SEQUÊNCIA(n))
Decimais aleatórios:
=ÍNDICE(ÚNICO(RANDARRAY(n^2;1;min;max;FALSO));SEQUÊNCIA(n))
Onde:
- n é o número de valores a serem gerados.
- Min é o valor mínimo.
- Max é o valor máximo.
Por exemplo, para criar uma lista de 5 inteiros aleatórios de 1 a 100 sem repetições, use esta fórmula:
= ÍNDICE(ÚNICO(RANDARRAY(5^2;1;1;100;VERDADEIRO));SEQUÊNCIA(5))
Para gerar 5 números decimais aleatórios exclusivos, coloque FALSO no último argumento de RANDARRAY ou omita este argumento:
= ÍNDICE(ÚNICO(RANDARRAY(5^2;1;1;100));SEQUÊNCIA(5))
Crie um intervalo de números aleatórios sem repetições no Excel
Funciona apenas nas versões mais recentes do Excel 365 que oferecem suporte a matrizes dinâmicas.
Para gerar um intervalo de números aleatórios sem repetições, use a fórmula:
= ÍNDICE(ÚNICO(RANDARRAY(n^2;1;min;max));SEQUÊNCIA(linhas;colunas))
Onde:
- n é o número de células a serem preenchidas. Para evitar cálculos manuais, você pode fornecê-lo como (nº de linhas * nº de colunas). Por exemplo, para preencher 10 linhas e 5 colunas, use 50 ^ 2 ou (10 * 5) ^ 2.
- linhas é o número de linhas a serem preenchidas.
- colunas é o número de colunas a serem preenchidas.
- min é o valor mínimo.
- máx. é o valor máximo.
Note que a fórmula é basicamente a mesma do exemplo anterior. A única diferença é a função SEQUÊNCIA, que neste caso define o número de linhas e colunas.
Por exemplo, para preencher um intervalo de 10 linhas e 3 colunas com números aleatórios exclusivos de 1 a 100, use a fórmula:
= ÍNDICE(ÚNICO(RANDARRAY(30^2;1;1;100));SEQUÊNCIA(10;3))
Ela vai criar uma matriz de decimais aleatórios sem repetições:
Se você precisar trabalhar com números inteiros, defina o último argumento da função RANDARRAY como VERDADEIRO:
= ÍNDICE(ÚNICO(RANDARRAY(30^2;1;1;100;VERDADEIRO));SEQUÊNCIA(10;3))
Como impedir que números aleatórios mudem
Todas as funções de randomização no Excel, incluindo ALEATÓRIO, ALEATÓRIOENTRE e RANDARRAY, são voláteis, o que significa que elas são recalculadas toda vez que a planilha é alterada. Como resultado, novos valores aleatórios são produzidos a cada mudança. Para evitar a geração de novos números, use o recurso Colar especial > Valores para substituir fórmulas por valores estáticos. Veja como:
- Selecione todas as células com sua fórmula aleatória e pressione Ctrl + C para copiá-las.
- Clique com o botão direito no intervalo selecionado e clique em Colar especial> Valores.
Para saber detalhes de como trabalhar com as funções ALEATÓRIO e ALEATÓRIOENTRE consulte os artigos:
Como gerar números aleatórios no Excel
Como selecionar amostras aleatórias no Excel
Como gerar números aleatórios exclusivos no Excel 2019, 2016 e anteriores
Como nenhuma versão diferente do Excel 365 oferece suporte a matrizes dinâmicas, nenhuma das soluções acima funciona em versões anteriores do Excel. No entanto, isso não significa que não haja solução para sus problems, você apenas terá que realizar mais algumas etapas:
- Crie uma lista de números aleatórios. Com base em suas necessidades, use:
- A função ALEATÓRIO para gerar decimais aleatórios entre 0 e 1, ou
- A função ALEATÓRIOENTRE para produzir números inteiros aleatórios no intervalo que você especificar.
Certifique-se de gerar mais valores do que realmente precisa, porque alguns serão duplicados e você terá exclui-los posteriormente.
Para este exemplo, estamos criando uma lista de 10 inteiros aleatórios entre 1 e 20 usando a fórmula abaixo:
= ALEATÓRIOENTRE(1;20)
Para inserir a fórmula em várias células de uma vez, selecione todas as células (A2: A15 no nosso exemplo), digite a fórmula na barra de fórmulas e pressione Ctrl + Enter. Ou insira a fórmula na primeira célula como de costume e, em seguida, arrastá-la para quantas células forem necessárias.
De qualquer forma, o resultado será mais ou menos assim:
Note que inserimos a fórmula em 14 células, embora, eventualmente, precisemos apenas de 10 números aleatórios exclusivos.
2. Altere fórmulas para valores. Como ALEATÓRIO e ALEATÓRIOENTRE são recalculados a cada alteração na planilha, sua lista de números aleatórios mudará continuamente. Para evitar que isso aconteça, use Colar especial > Valores para converter fórmulas em valores.
Para ter certeza de que fez tudo certo, selecione qualquer número e observe a barra de fórmulas. Ele agora deve exibir um valor, não uma fórmula:
3. Exclua os duplicados. Para fazer isso, selecione todos os números, vá para a guia Dados> grupo Ferramentas de dados e clique em Remover duplicados. Na caixa de diálogo Remover Duplicados que aparece, simplesmente clique em OK sem alterar nada. Para obter as etapas detalhadas, consulte Como remover duplicados no Excel.
E pronto! Todas os duplicados se foram.
Conclusão
Neste artigo vimos como gerar números aleatórios no Excel sem repetição utilizando funções disponíveis em todas as versões do Excel
O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.
Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.
Portanto, continue lendo mais sobre Dicas de Excel aqui.
