Solicitando um conjunto de dados distribuídos aleatoriamente

Question

Eu tenho um conjunto de dados espalhados aleatoriamente, como pode ser visto na imagem abaixo. Eu gostaria de coletar e colocá-los em ordem de grupos (group1, group2, etc.). No exemplo abaixo, se o valor "1" estiver presente em qualquer uma das colunas A: R, então todos os "1" devem ser alinhados no grupo1. se não houver 1s, ele deve aparecer em branco. Existe uma maneira de fazer isso no excel com uma fórmula ou macro? Eu prefiro fórmula embora. Obrigado.

Este é o link para a pasta de trabalho de exemplo (editável): link

    

Answer 1

Em primeiro lugar, os grupos devem ter o mesmo tamanho de dados (para o caso de a linha ter apenas um número). Então, o grupo 1 deve ser de T para AK. Na célula T2, insira a fórmula:

=IFERROR(INDEX($A$2:$R$2,SMALL(IF($A$2:$R$2=1,COLUMN($A$2:$R$2)),COLUMN(A:A))),"")

Digite a fórmula e pressione Ctrl + Shift + Enter para inserir uma fórmula de matriz. Então, ao copiar e colar nas colunas U a AK, mostrará no Grupo1 todas as ocorrências do 1.

Algumas dicas para melhorar:

• Se você colocar na célula T1 apenas o número 1 e, em seguida, formatá-lo para mostrar "Group " 0 , poderá alterar o 1 na fórmula para o endereço T1. Então, quando copiá-lo para os outros grupos, a fórmula deve se ajustar aos outros números (você deve colocar 1 para cada coluna de T para AK, mas mostrar apenas um deles e, das colunas AM a BD, deve haver o número 2 e assim por diante).
• Se o seu Excel for 2010 ou anterior, você não terá a função IFERROR() . A solução alternativa é usar um IF() e um ISERROR() : IF(ISERROR(...),"",(...)) . O (...) é a fórmula no primeiro argumento do IFERROR() .
• Para copiar a fórmula para as outras linhas, você pode alterar a função ÍNDICE para retornar uma linha e uma coluna. A única diferença é que a linha de retorno é a linha da célula e a coluna é a fórmula (...) .