Como gerar números aleatórios sem duplicatas no Excel

Nos últimos meses, escrevi sobre várias das novas funções de matriz dinâmica do Excel. Eles são poderosos e fáceis de implementar – aliviando-nos de uma construção de expressão muito cansativa que era necessária antes. Neste artigo, combinaremos algumas dessas novas funções de matriz dinâmica para gerar uma matriz de valores aleatórios exclusivos. É fácil gerar valores aleatórios; é tão fácil limitar os limites inferior e superior para um conjunto de valores aleatórios. O que não é tão fácil, como você verá, é gerar um array de valores aleatórios únicos. Felizmente, usando funções de matriz dinâmica, você só precisa pular alguns os.

VEJA: 83 dicas do Excel que todo usuário deve dominar (TechRepublic)

Estou usando o Microsoft 365 em um sistema Windows 10 de 64 bits. As funções de matriz dinâmica são adas pelo 365, Excel para a Web, Excel 2021, Excel para iPad e iPhone, Excel para tablets e telefones Android. Não há arquivo de demonstração; você não vai precisar de um.

Sobre funções aleatórias

O Excel tem três funções de valor aleatório: RAND(), RANDBETWEEN() e RANDARRAY(). RAND() gera valores aleatórios entre 0 e 1, portanto, valores decimais aleatórios. RANDBETWEEN() permite especificar os valores inferior e superior. Por exemplo, você pode usar
RANDBETWEEN(10,1000)
para gerar números inteiros aleatórios entre 10 e 1.000.

Por fim, RANDARRAY() retorna uma matriz de valores aleatórios usando a seguinte sintaxe:
RANDARRAY([rows], [columns], [min], [max], [integer])
Observe que todos os argumentos são opcionais. Esses argumentos permitem especificar o número de linhas e colunas a serem preenchidas, os valores mínimo e máximo a serem retornados e se devem ser retornados números inteiros ou decimais. O argumento integer é false por padrão, que retorna valores decimais. Para retornar números inteiros, você deve especificar TRUE. Além disso, min e max são valores inclusivos. Por exemplo, se esses dois argumentos forem 1 e 5, respectivamente, a função retornará 1, 2, 3, 4 e 5, não apenas 2, 3 e 4.

Ao considerar argumentos, tenha em mente as seguintes regras:

• RANDARRAY() funciona como RAND(), retornando um valor entre 0 e 1 se você omitir todos os argumentos.
• RANDARRAY() funciona como RAND(), retornando um valor entre 0 e 1 se você omitir linhas e colunas.
• RANDARRAY() retornará 0 ou 1 respectivamente, se você omitir min e max.
• O argumento min deve ser menor que max.
• RANDARRAY() incluirá valores decimais se você não definir explicitamente inteiro como TRUE.
• RANDARRAY() criará o intervalo de tamanho apropriado para concluir os cálculos. Esse intervalo é chamado de . Se não houver células adequadas nesse intervalo, RANDARRAY() retornará um erro de spill. Para saber mais sobre o intervalo de derramamento do Excel, você pode ler Como usar o intervalo de derramamento no Excel
• RANDARRAY() atualizará os resultados e redimensionará quando os dados de origem forem alterados se os dados de origem estiverem em um objeto Table.
• Atualmente, se a fonte de dados estiver em outra pasta de trabalho, ambas as pastas de trabalho devem estar abertas para que RANDARRAY() funcione corretamente.
• Para gerar uma matriz aleatória de valores não duplicados, usaremos três funções dinâmicas de matriz: RANDARRAY(), UNIQUE() e SEQUENCE().

Como gerar valores aleatórios sem duplicatas, mais ou menos

RANDARRAY() sozinho tem o potencial de retornar valores duplicados, assim como todas as funções aleatórias. Se você tiver tempo, há uma maneira simples de obter valores aleatórios sem duplicatas. Converta os valores no intervalo de spill em valores estáticos (também substituindo a função) e remova quaisquer duplicatas usando a ferramenta Remover Duplicatas na guia Dados. É fácil, mas não é uma maneira razoável de trabalhar, a menos que você esteja fazendo um e único.

Você não pode forçar RANDARRAY() a retornar apenas valores únicos, mas pode combiná-lo com UNIQUE() para obter os resultados desejados. Por exemplo, a seguinte função, mostrada em Figura Aretornará uma única coluna de cinco (aparentemente) valores exclusivos entre 1 e 20:

=ÚNICO(RANDARRAY(5, 1, 1, 20,TRUE))

Duas coisas podem acontecer: 1.) Você vê uma lista de valores únicos aleatórios que é menor do que você especificou; 2.) Você vê um (à direita em Figura A).

Quando RANDARRAY() repete valores suficientes para que a expressão não possa retornar cinco valores exclusivos do conjunto de valores aleatórios de RANDARRAY(), ela retornará o que puder—que pode ser um valor ou dois curtos. As chances de ter tantos valores repetidos nesse conjunto de 20 (os valores mínimo e máximo de 1 e 20, respectivamente) que você não pode retornar uma lista de cinco são pequenas, mas não impossíveis. Pressione F9 e observe a atualização da expressão; eventualmente, você verá uma matriz com menos de cinco dígitos.

O tiro à direita (Figura A) mostra a expressão na barra de fórmulas onde calculei manualmente RANDARRAY() selecionando essa função e pressionando F9. Como você pode ver, o array repete um valor, o valor 1. Assim, a expressão pode retornar apenas quatro valores únicos.

Figura A

Figura B mostra um erro de derramamento, . Mudei o argumento rows de 5 para 19, forçando que isso aconteça com mais frequência, mas se você pressionar F9 com frequência suficiente, o argumento rows de 5 acabará retornando um erro de derramamento. Tem a ver com a natureza volátil dessas funções – às vezes uma calcula mais rápido que a outra. Uma função recalcula toda vez que uma alteração é feita.

Figura B

Uma maneira de garantir que a expressão não retorne menos valores do que o especificado é criar um enorme conjunto de valores aleatórios. Talvez a maneira mais fácil seja alterar o argumento rows de um valor específico para uma expressão curta: ^2. Por exemplo,

=ÚNICO(RANDARRAY(5^2, 1, 1, 20,TRUE))

A expressão de linha, 5^2, retornará 25 valores aleatórios, mas agora a expressão retorna 25 valores (linhas) e você ainda está suscetível a um erro ocasional de intervalo de derramamento. No entanto, é improvável que retorne menos de cinco linhas.

Se você pode viver com todos esses comportamentos, você pode parar por aqui. As chances são, porém, que você vai querer algo mais estável.

Como gerar valores aleatórios sem duplicatas

Na última seção, você aprendeu muito sobre RANDARRAY() e UNIQUE() e como eles funcionam juntos, mesmo que os resultados não sejam estáveis. Suas necessidades determinarão se você precisa levar esse problema para o próximo nível.

No momento, temos uma expressão que nem sempre retorna o número correto de valores e tem o potencial de retornar um erro de derramamento. Para evitar esses dois problemas, você precisa de uma expressão mais complexa na forma

=INDEX(UNIQUE(RANDARRAY(linhas^2, colunas, min, max, TRUE)), SEQUENCE(linhas))

Parece horrível, mas não se preocupe. Uma vez que você entenda como funciona, fará sentido e você terá pouca dificuldade em aplicá-lo ao seu próprio trabalho.

RANDARRAY() gera uma matriz de valores aleatórios com base em min e max. O argumento rows elevado a 2 gera um grande conjunto de valores aleatórios – muito maior do que apenas o próprio valor das linhas. UNIQUE() remove todos os valores duplicados e retorna um array de valores únicos. Chegamos até aqui na última seção.

A diferença desta vez é que UNIQUE() não retorna o array para a planilha. Depois que UNIQUE() remove duplicatas, INDEX() usa o número de linhas expressas em SEQUENCE() para determinar quantos valores aleatórios únicos devem ser retornados como uma matriz para a planilha.

Figura C mostra esta expressão usando nossos valores de argumento anteriores:

=ÍNDICE(ÚNICO(RANDARRAY(5^2,1,1,10,TRUE)), SEQUÊNCIA(5))

Primeiro, RANDARRAY() retorna um array de coluna única de 25 inteiros aleatórios entre 1 e 10. UNIQUE() remove todas as duplicatas desses resultados. INDEX() retorna os cinco principais valores únicos e aleatórios, conforme determinado por SEQUENCE(5).

Figura C

Tenha em mente que min e max ainda importam. Por exemplo, se min e max são 1 e 5, respectivamente, você não pode retornar uma matriz de 10 valores — não há muitos valores exclusivos entre 1 e 5. A expressão retornará cinco valores exclusivos, mas as células restantes na faixa de derramamento exibirá erros. Isso é uma simplificação excessiva, mas faz o ponto.

Além disso, matrizes grandes levam tempo. Considere um valor de linhas de 2000^2. Internamente, o Excel está calculando 4.000.000 números aleatórios! Você provavelmente não precisa de uma piscina tão grande. Em vez disso, multiplique o argumento rows por outro valor, como 10. Isso retorna 20.000 valores aleatórios, o que pode ser suficiente, dependendo dos valores mínimo e máximo.

Você terá que experimentar para determinar se um pool é grande o suficiente. Não existe uma verdadeira bala de prata por causa do relacionamento: a chance de retornar um intervalo de derramamento incompleto aumenta à medida que o número de números aleatórios ados ​​para UNIQUE() diminui.

É hora da confissão. Esta é uma expressão criativa e poderosa que aproveita as novas funções de matriz dinâmica do Excel. Eu gostaria de reivindicar o crédito total por criar essa expressão, mas ela já está na internet há algum tempo. Se eu pudesse creditar o gênio criativo que surgiu com isso pela primeira vez, eu daria.

Para saber mais sobre funções de matriz dinâmica, leia os seguintes artigos: