☰

Transforme uma linha em várias linhas no Excel

tag-icon • tag-icon microsoft-excel
Transforme uma linha em várias linhas no Excel

Tenho vários itens disponíveis em vários centros de distribuição (ou seja, um relacionamento muitos-para-muitos). Atualmente existe uma linha por item, com uma coluna para cada centro de distribuição. Uma célula na linha do item Xe a coluna do centro de distribuição Sestá marcado com o código do centro de distribuiçãoSse item Xestá disponível lá e em branco caso contrário. Um item com vários centros de distribuição terá vários códigos de centro de distribuição (em suas respectivas colunas). Portanto, a planilha atual se parece com:

    |   A    |         B         |*|                      S-AJ                      |
1   |  ID #  |   Description     |…|              Distribution Centers              |
2   |   17   |   Ginkgo Biloba   |…|      |      |      |      |      |      |  SE  |
3   |   42   |   Ginseng         |…|      |  MP  |  MS  |      |  NW  |      |      |
                     ︙

As colunas Ccontêm Routros atributos dos itens, como código UPC, custo e preço, que não são relevantes para esta questão. Minha planilha atual tem 18 centros de distribuição, abrangendo Scolunas AJ; Reduzi isso para que o exemplo caiba na janela do Stack Exchange.

Preciso ter uma única coluna de centro de distribuição, com um único código de distribuição por linha, e depois duplicar as linhas conforme necessário para itens que atualmente contêm vários códigos. O resultado deve ser parecido com:

    |   A    |         B         |*|   S  |
1   |  ID #  |   Description     |…|  DC  |
2   |   17   |   Ginkgo Biloba   |…|  SE  |
3   |   42   |   Ginseng         |…|  MP  |
4   |   42   |   Ginseng         |…|  MS  |
5   |   42   |   Ginseng         |…|  NW  |
                     ︙

onde as células A3:R3, A4:R4e A5:R5, contêm as mesmas informações.

A única maneira que consigo pensar em fazer isso, o que consumiria muito tempo, seria copiar o número do item em várias linhas; e na coluna que tem o código de distribuição eu mudaria o código do item que está disponível em cada centro de distribuição. Farei isso por 900 itens. Existe uma maneira mais fácil de fazer isso?

Responder1

  1. Crie uma nova planilha. Copie as linhas do cabeçalho, as larguras das colunas e os formatos, exceto não copie Columns T- AJ. Pode ser mais fácil copiar a planilha inteira, excluir todas as linhas, exceto 1, e desfazer a mesclagem de Colunas S- AJ.

  2. Primeiro, queremos replicar cada linha de item Sheet118 Sheet2 vezes – uma vez para cada centro de distribuição. Digitar .  =INDEX(Sheet1!A:A, INT((ROW()-2)/18)+2, 1) & ""​ mapeia as linhas 2 a 19 para a linha 2 em , as linhas 20 a 37 para a linha 3 em , etc. Isso faz com que o Excel exiba um espaço em branco ao fazer referência a uma célula em branco em . Se você não tiver espaços em branco , poderá deixar isso desativado. Se você não gostar desta solução específica, poderá usar uma das outras soluções de Sheet2!A2INT((ROW()-2)/18)+2Sheet2Sheet1Sheet2Sheet1& ""Sheet1Sheet1Exibir em branco ao fazer referência a células em branco no Excel.

    Arraste/preencha para a direita, para cell R2.

  3. Entrar =INDEX(Sheet1!$S:$AJ, INT((ROW()-2)/18)+2, MOD(ROW()-2, 18)+1) em Sheet2!S2. Isso faz referência à mesma linha Sheet1da fórmula acima, mas Sheet2!S2obtém o valor de Sheet1!S2, obtém Sheet2!S3o valor de Sheet1!T2, Sheet2!S4obtém o valor de Sheet1!U2, etc.0
  4. Selecione a linha inteira A2:S2e arraste/preencha para obter todos os seus dados. Isso precisará ser 18 vezes mais linhas do que você possui Sheet1; ou seja, 18×900=16200.
  5. Copie todos Sheet2e cole os valores.
  6. Coluna de filtro S. Exiba apenas os zeros. Exclua todas as linhas (exceto a linha 1). Remova o filtro.

Feito.

Responder2

Solução mais fácil, mas com mais esforço manual: Copie os IDs exclusivos x nº de tempo (onde x é o número de colunas a serem convertidas em linhas) para cada iteração, aplique vlookup com a coluna a ser convertida em linhas a cada vez

informação relacionada