Neste artigo você aprenderá como fazer sorteio usando fórmulas no Excel passo-a-passo com download da planilha gratuita.
1. Introdução Fazer Sorteio Usando Fórmulas no Excel
Imagine que você possui uma lista de valores (números ou textos) e deseja que o Excel escolha aleatoriamente um destes. Como proceder para que o Excel processe e devolva um resultado randomicamente dentre os disponíveis na lista?
No Excel podemos fazer esta tarefa de várias formas, incluindo o uso do VBA. Podemos também fazer uma série de sorteios sem repetição, o que é útil, por exemplo, para criar jogos, como um bingo.
Para fins de demonstração, neste artigo, faremos o sorteio com fórmulas, sem tratar de repetições posteriores. Tomaremos como base a lista a seguir, inserida em um intervalo formatado como tabela, chamada tabUF, disposta ao longo da coluna B.
2. Fórmulas para o sorteio
Primordialmente, as fórmulas aqui propostas serão baseadas em busca e referência, uma vez que os valores desejados já existem e precisam apenas ser retornados (devolvidos), sem necessidade de cálculo ou processamento. Em outras palavras: precisamos escolher um valor dentre os escolhidos.
2.1. A aleatorização
O mecanismo por trás da escolha será uma aleatorização que permite gerar randomicamente uma posição dentre as posições possíveis dos dados. Tendo em vista que a nossa lista possui 27 elementos, precisamos retornar e escolher as posições entre 1 e 27.
Com isso, precisamos de algum artifício que gere aleatoriamente números de 1 a 27. Para isso, a solução mais objetiva que podemos elencar é o uso da função ALEATÓRIOENTRE. Esta função requer apenas os 2 argumentos respectivamente: limite inferior, que é o menor número a gerar, e o limite superior, que é o maior número a gerar. Estes 2 argumentos não poderão ser invertidos (devemos informar o menor e o maior, nesta ordem).
Desta forma, a nossa fórmula ficará: =ALEATÓRIOENTRE(1;27). Sendo assim, o Excel retornará aleatoriamente algum valor inteiro entre 1 e 27. O Excel sempre recalculará esta fórmula, podendo gerar (e na maior parte das vezes vai) um novo valor quando a planilha for recalculada. Isto é provocado a cada atualização de valores na pasta de trabalho ou quando provocamos o recálculo com a tecla F9 ou ainda os próprios comandos de recálculo, presentes no grupo Cálculo da guia Fórmulas, como exibe a imagem seguinte (comandos Calcular Agora e Calcular Planilha). O
2.2. Vinculando o valor sorteado ao resultado desejado
Quando =ALEATÓRIOENTRE(1;27) gera um resultado, compreendido entre 1 e 27, o número não é exatamente o que desejamos. Precisamos de “trocar” o número gerado pela UF da posição correspondente nos dados. Desta forma, se o valor gerado for 3, devemos retornar AM (pois é a 3ª UF na lista), bem como devemos retornar TO quando o valor gerado for 27 (o maior valor deve fazer retornar o último texto, que é a última UF).
Uma primeira fórmula definitiva para o nosso fim é baseada na função ÍNDICE. Para tanto, basta que o 1º argumento de ÍNDICE seja a lista de textos a serem sorteados, ou seja, a nossa tabela com a coluna das UFs. No 2º argumento de ÍNDICE devemos colocar a fórmula pronta, comentada acima, =ALEATÓRIOENTRE(1;27).
Desta forma, dentre todas as UFs, ÍNDICE devolverá alguma UF dentre a 1ª e o 27ª. A fórmula final definitiva será: =ÍNDICE(tabUF[Nomes];ALEATÓRIOENTRE(1;27)).
A imagem mostra que o resultado gerado foi DF. Como este item é o 7º da lista, isto implica dizer que a aleatorização realizada com ALEATÓRIOENTRE(1;27) retornou o valor 7. Vale destacar que o recálculo da planilha fará novo processamento que certamente modificará o valor fornecido por ALEATÓRIOENTRE(1;27) e por consequência resultará em UF distinta.
Uma fórmula alternativa pode ser baseada em DESLOC. Ela parte do nome da coluna, em B2, e desloca para baixo o número de linhas que ALEATÓRIOENTRE(1;27) calcular. A fórmula alternativa sugerida é =DESLOC(B2;ALEATÓRIOENTRE(1;27);).
2.3. Otimizando as fórmulas
O trecho da fórmula ALEATÓRIOENTRE(1;27) pode ser problemático caso a lista de valores a serem retornados possa mudar em termos de quantidade de elementos. Como esta fórmula prevê um máximo de 27 itens, caso a lista seja alterada para conter mais ou menos de 26 itens, poderemos ter erros.
Sendo assim, o 1º argumento de ALEATÓRIOENTRE poderá ser mantido igual 1 (número absoluto mínimo de itens), mas o seu 2º argumento deverá ser dinâmico e variável.
O ideal para evitar este problema é tornar dinâmico o valor máximo a ser gerado. A contagem dinâmica de itens da lista poderá resolver este problema. Tomando como base o fato de que todos os valores estão preenchidos, podemos usar CONT.VALORES. Podemos também usar a função LINS para contar o número de linhas da tabela, independentemente de haver dados preenchidos.
Desta forma, poderíamos ter as seguintes fórmulas mais inteligentes: =ÍNDICE(tabUF[Nomes];ALEATÓRIOENTRE(1;CONT.VALORES(tabUF[Nomes]))) e =ÍNDICE(tabUF[Nomes];ALEATÓRIOENTRE(1;LINS(tabUF[Nomes]))).
A imagem a seguir mostra erro na fórmula inicial, em que o valor 27 é fixo, quando a tabela foi reduzida e agora possui apenas 10 itens. Neste caso com certeza ALEATÓRIOENTRE resultou em valor superior a 10.
P.S: as 2 fórmulas em que não há erro resultam em UFs distintas (SE e BA) porque a função ALEATÓRIOENTRE de ambas não apresenta relação entre si, gerando valores independentes. Será mera coincidência caso retorne o mesmo valor.