Excel: gera mais linhas (calculadas) com base em um único critério

Question

Eu quero dividir uma única linha de informação quando ela atende a determinados critérios, em vários.

No exemplo em anexo, gostaria de dividir as linhas com um número de 2 ou mais na coluna "Registros", mantendo o número 1 em cada nova linha; mas o "Valor" deve ser distribuído dentro das novas linhas também.

Anexei um arquivo com as informações e o resultado esperado no seguinte link: link e é colado abaixo:

    

Answer 1

Aqui está uma solução que usa apenas funções básicas do Excel. Digamos que a tabela de origem mostrada em seu link inicie na coluna A e que a primeira linha de dados seja a linha 2. Primeiro, adicione uma coluna auxiliar começando em H2. Esta coluna calcula a linha relativa em que cada novo nome será iniciado para a nova tabela.

Digite 1 na célula H2. A fórmula em H3 seria:

    =IF(ISBLANK(A3),"",SUM(C$2:C2)+1)

Copie a coluna para baixo para todas as linhas de dados de origem e tantas outras quanto você quiser preencher previamente. Ele permanecerá em branco quando não for necessário.

Sua tabela de saída começa na coluna I com a linha 2 sendo os primeiros dados. Como o seu exemplo tem cinco linhas de dados, baseamos as fórmulas da solução nisso. Basta alterar os intervalos de referência conforme apropriado. A fórmula em I2 (sua coluna Número) seria:

    =IF((ROW()-1)>SUM(C$2:C$6),"",MATCH(ROW()-1,H$2:H$6,1))

Você pode copiar essa coluna para baixo um intervalo arbitrariamente grande para pré-preencher as células que você pode eventualmente usar. Esta e todas as outras entradas na tabela permanecerão em branco até serem necessárias.

A coluna Data é J. A fórmula em J2 é:

    =IF(I2="","",VLOOKUP(I2,$A$2:$F$6,2,))

As colunas Nome e ID são semelhantes, apenas VLOOKUPs em uma coluna diferente, portanto as fórmulas em M2 e N2 são:

    =IF(I2="","",VLOOKUP(I2,$A$2:$F$6,5,))   (cell M2)
    =IF(I2="","",VLOOKUP(I2,$A$2:$F$6,6,))   (cell N2)

Você pode simplesmente copiar a fórmula J2 e editar a coluna VLOOKUP (último parâmetro).

Na tabela de saída, a coluna Registros sempre será 1 , então a célula K2 é:

    =IF(I2="","",1)

A coluna restante é Valor, que usa dois VLOOKUPs para o numerador e denominador de sua divisão. A célula L2 seria:

    =IF(I2="","",VLOOKUP(I2,$A$2:$F$6,4,)/VLOOKUP(I2,$A$2:$F$6,3,))

Se a coluna de data aparecer como um número, basta alterar o formato da célula para o formato de data desejado. Agora você pode copiar todas essas células como você fez para a coluna I.

Isso lhe dará a saída mostrada em seu exemplo.

    

Answer 2

Eu usaria o suplemento de consulta de energia para isso. Tem uma função List.Generate que pode conseguir o que você quer.

Eu criei uma solução usando seus dados que você pode visualizar ou fazer o download - é a seguinte: "Demonstração do Power Query - gere várias linhas de valores da célula.xlsx" no meu One Drive:

link

A maior parte disso foi criada usando a interface de usuário do Power Query point-and-click. A função List.Generate está documentada aqui:

link

Não existem muitos exemplos práticos na Web, e não consegui descobrir como passar o valor de Registros, portanto estou gerando apenas 10 linhas e depois filtrando em uma etapa posterior.