Fórmula genérica
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Resumo
Para classificar aleatoriamente os valores existentes com uma fórmula, você pode usar uma fórmula INDEX e MATCH junto com colunas auxiliares, conforme mostrado na captura de tela. No exemplo mostrado, a fórmula em E5 é:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
onde "nomes" é o intervalo nomeado B5: B11, "rand" é o intervalo nomeado C5: C11 e "ordenar" é o intervalo nomeado D5: D11.
Explicação
Esta fórmula depende de duas colunas auxiliares. A primeira coluna auxiliar contém valores aleatórios criados com a função RAND (). A fórmula em C5, copiada é:
=RAND()
A função RAND gera um valor aleatório em cada linha.
Nota: RAND é uma função volátil e irá gerar novos valores a cada alteração da planilha.
A segunda coluna auxiliar contém os números usados para classificar os dados, gerados com uma fórmula. A fórmula em D5 é:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Veja esta página para uma explicação desta fórmula.
A fórmula em E5 é:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Aqui, a função INDEX é usada para recuperar valores no intervalo nomeado "nomes", usando os valores de classificação no intervalo nomeado "classificar". O trabalho real de descobrir qual valor recuperar é feito pela função MATCH neste snippet:
MATCH(ROWS($D$5:$D5),sort,0)
Dentro de MATCH, a função ROWS recebe um intervalo de expansão como valor de pesquisa, que começa como uma célula e se expande conforme a fórmula é copiada na coluna. Isso aumenta o valor de pesquisa, começando em 1 e continuando até 7. MATCH retorna então a posição do valor de pesquisa na lista.
A posição é alimentada para INDEX como o número da linha, e INDEX recupera o nome nessa posição.
