Excel: seleção aleatória dentro da tabela que atende a dois critérios

Question

Aqui está um exemplo dos meus dados:

Name:        Office:   Years:

John Smith   Canada      1

Bob Smith    Canada      1

Jake Smith   Canada      1

Sarah Smith  Canada      1

Em outra planilha, quero gerar um nome aleatório baseado no Office e no Years. Como eu arrasto a fórmula para baixo eu quero dar um nome diferente a cada vez (isto é onde eu estou preso). Eu tentei várias fórmulas e esta é a mais próxima.

=INDEX($B$2:$B$436,MATCH(SMALL(IF($D$2:$D$436&$F$2:$F$436=$H$3&$I$3,$G$2:$G$436),1),$G$2:$G$436,0))

Coluna B = Coluna de Nome D = Coluna do Office F = Anos, então H3 e I3 são meus valores de pesquisa

coluna G é um conjunto de números aleatórios = rand ()

    

Answer 1

Vou colocar a randomização na própria fórmula. Você pode colocar isso em outra coluna se quiser.

Coluna A é nome
Coluna B é escritório
Coluna C é o ano

Cell G2 é o escritório em que você está interessado
Cell G3 é o ano em que você está interessado

=INDIRECT("A"&LARGE(IF($B$2:$B$7=G2,IF($C$2:$C$7=G3,ROW($A$2:$A$7),0),0), RANDBETWEEN(1,COUNTIFS(B:B,G2,C:C,G3))))

Esta é uma fórmula de matriz e precisa ser digitada usando Ctrl + Deslocamento + Enter .

Funciona criando uma matriz que possui os números de linha (se o critério for correspondido) ou zero, se não corresponder. Em seguida, leva o maior valor n th, onde n é um número aleatório entre 1 e o número de linhas que correspondem aos critérios.

    

Answer 2

Supondo que você deseja retornar nomes em A2 para baixo (em que A1 é um cabeçalho ou em branco), use esta fórmula de matriz em A2

=INDEX($B$2:$B$436,SMALL(IF(($D$2:$D$436=$H$3)*($F$2:$F$436=$I$3)*(COUNTIF(A$1:A1,$B$2:$B$436)=0),ROW($B$2:$B$436)-ROW($B$2)+1),RANDBETWEEN(1,SUM(($D$2:$D$436=$H$3)*($F$2:$F$436=$I$3)*(COUNTIF(A$1:A1,$B$2:$B$436)=0)))))

confirme com CTRL + SHIFT + CTRL e copie para baixo - altere o intervalo A$1:A1 nas funções COUNTIF para corresponder ao célula acima da sua célula inicial.

Nota: - você não precisa de uma coluna de números aleatórios com este método