Amostragem aleatória no Excel com base em critérios específicos de outras colunas

Question

Eu tenho uma lista de números de arquivos na Coluna A , um código associado ao arquivo na coluna B e um número de dias na coluna I . Exemplo:

File      Code     Days
12345     AAAA     100
54321     AAAA      91
67890     BBBB     115
09876     CCCC     150

Existem aproximadamente mais de 100 linhas de dados. Eu gostaria de fazer uma amostragem aleatória de números de arquivos com base nos seguintes critérios:

• Pelo menos 20 arquivos ou 20%, o que for maior.
• Pelo menos 1 arquivo de cada código.
• Todos os arquivos que têm um código não compartilhado com outros arquivos.
• Todos os arquivos em que o Days excede 150.

Isso é possível?

    

Answer 1

A dificuldade é que existem muitas maneiras possíveis de a amostragem ser possível devido às proporções desconhecidas de coisas como a contagem de arquivos versus o número de códigos exclusivos em comparação com códigos com um único arquivo em relação a arquivos > 150 dias vs. distribuição do número de arquivos por código, etc. Em vez de tentar projetar uma solução definitiva para todos os casos possíveis, descreverei um processo que levará você à solução. Cada etapa tem inúmeras maneiras simples de fazer isso no Excel.

Pense nisso como três potes:

• Pote 1 você preencherá com suas amostras selecionadas ao longo do exercício (esse pote estará na forma de uma lista que você construir com os nomes de arquivo selecionados). Vamos chamar isso de lista de amostras.

• O Pote 2 contém a coleção de códigos exclusivos (esse pote estará na forma de uma lista de códigos exclusivos; procure como extrair os valores exclusivos de uma lista que contenha duplicatas se você não souber como ). Isto irá conter os códigos que ainda não foram representados por uma amostra selecionada. Vamos chamar isso de lista de códigos.

• O pote 3 contém "tokens" para o número necessário de amostras restantes (esse pote será apenas um número em uma célula que você diminui ou calcula quando você seleciona as amostras). Vamos chamar isso de contar as amostras restantes. Calcule o maior de 20 ou 20% e coloque esse número nesse pote.

Aqui estão os passos:

1. Decida qual limite de oversampling usar. Sua meta é 20% dos arquivos, mas a amostragem aleatória não fornece 20% uniformes em todos os códigos (a menos que você adicione um requisito adicional para isso). Alguns códigos terão menos de 20% selecionados, alguns mais. As regras não aleatórias não terão relação com 20%. Códigos com um único arquivo serão 100% amostrados. A regra de > 150 dias pode selecionar mais de 20% dos arquivos para um determinado código. Se você selecionar muito acima de 20% por essa regra, esse código já estará sobre-representado, portanto, você provavelmente não desejará amostrar aleatoriamente ainda mais esse código. Decida uma porcentagem máxima de amostragem (ou contagem de amostras) para a regra de > 150 dias além da qual você exclui esse código da amostragem aleatória.

2. Selecione todos os arquivos com dias > 150 e mova-os para a lista de amostras. Decrementar as amostras restantes contam pela contagem desses arquivos. Remova os códigos representados por esses arquivos da lista de códigos (com base nos códigos exclusivos para essa coleção de arquivos).

3. Se esta etapa resultar em códigos excedendo o limite de super amostragem, remova todos os arquivos associados a esses códigos da lista de fontes. Note que a remoção é figurativa. A intenção é garantir que nenhum desses arquivos seja amostrado mais tarde. Você pode codificar isso de uma maneira que não exija a remoção dos arquivos.

4. Dos arquivos e códigos remanescentes na lista de códigos, mova para a lista de amostras quaisquer arquivos onde esse seja o único arquivo para esse código. Decrementar as amostras restantes contam pela contagem desses arquivos. Remova os códigos representados por esses arquivos da lista de códigos. Note que este passo não é estritamente necessário dependendo de como o passo 5 é implementado. Eu decifrei isso como uma etapa separada para clareza e para que você não precise selecionar aleatoriamente de uma lista de um.

5. Para cada código restante na lista de códigos, selecione aleatoriamente um arquivo para esse código e mova-o para a lista de amostras. Decrementar as amostras restantes contam pela contagem desses arquivos. Remova os códigos representados por esses arquivos da lista de códigos (remover os códigos pode ser opcional, dependendo de como você o implementa; depois que essa etapa for concluída, a lista de códigos não será mais necessária).

6. Para o número de arquivos na contagem de amostras restantes, selecione aleatoriamente um arquivo dos arquivos restantes e mova-o para a lista de amostras.

E pronto!

Está além do escopo pretendido de uma pergunta neste site projetar todo o aplicativo para você. Há muitas maneiras de abordar cada etapa e as fórmulas e métodos para realizá-las estão bem representadas em perguntas anteriores no site. Se você ficar preso, peça uma nova pergunta para obter ajuda com o problema específico. Por exemplo, "Como eu aleatoriamente seleciono um valor em uma lista?" seria a escala apropriada de uma questão.