Excel - Transposição baseada em fórmulas de valores separados por vírgula para linhas individuais

Question

Mantenho meu inventário de CD / DVD em um arquivo do Excel (Excel 2013). Os dados são organizados conforme mostrado no instantâneo abaixo.

Em cada linha, há a primeira célula mencionando o rótulo do CD e a próxima célula contém a lista de aplicativos separados por vírgula que eu escrevi.

No entanto, estou interessado em uma visualização em que os valores separados por vírgulas são transpostos para linhas, de modo que haja apenas um item por célula e por linha, conforme mostrado abaixo.

Eu entendo, o VBA pode ajudar aqui facilmente e eu tenho conhecimento básico de VBA, no entanto, existe uma solução baseada em fórmula possível para transpor os dados de entrada para saída? Eu quero evitar macros e também quer evitar o processo manual de texto para coluna, em seguida, copiar e colar transpor.

Também deve aparar o espaço extra, se houver após a vírgula.

    

Answer 1

Para fazer isso com uma fórmula, com mais antigo que o Office 365 Excel, você precisará de uma coluna auxiliar.

Próximo à sua lista delimitada por vírgulas (minha lista começa em C2) put:

=LEN(C1)-LEN(SUBSTITUTE(C1,",",""))+1+D1

Isso manterá um total em execução do número de palavras.

Emseguida,vocêfazreferênciaaessasduascolunascomINDEX/MATCHparafazerreferênciaàcélulacorretaàmedidaqueafórmulalongaéarrastadaparabaixo.

Afórmulalongaé:

=TRIM(MID(SUBSTITUTE(INDEX(C:C,MATCH(ROW(1:1),D:D)),",",REPT(" ",99)),(ROW(1:1)-INDEX(D:D,MATCH(ROW(1:1),D:D)))*99 +1,99))

Em seguida, dois INDEX / MATCH retornam as células corretas a serem processadas.

O SUBSTITUTO adiciona muito espaço em branco dando um grande alvo para o Mid encontrar.

O Mid divide a longa string no espaço em branco.

O TRIM remove o espaço em branco estranho.

ComoOffice365,vocêpodepularacolunaauxiliareusarTEXTJOIN()seacadeianãoformuitolonga.

=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$C$2:$C$5),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99))

Isso substitui o INDEX / MATCH por TEXTJOIN, caso contrário, ele age como acima.

    

Answer 2

Eu gostaria de sugerir um simples método VBA (Macro), que é muito fácil de usar & melhor do que qualquer fórmula tediosa.

PrivateSubCommandButton1_Click()DimxArr()AsStringDimxAddressAsStringDimRgAsRangeDimRg1AsRangeOnErrorResumeNextxAddress=Application.ActiveWindow.RangeSelection.AddressSetRg=Application.InputBox("please select the data range:", "Column to Row", xAddress, , , , , 8)

    If Rg Is Nothing Then Exit Sub

    Set Rg = Application.Intersect(Rg, Rg.Parent.UsedRange)

    If Rg Is Nothing Then Exit Sub

    Set Rg1 = Application.InputBox("please select output cell:", "Column to Row", , , , , , 8)

    If Rg1 Is Nothing Then Exit Sub
    xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")
    Rg1.Resize(UBound(xArr) + 1) = Application.Transpose(xArr)
    Rg1.Parent.Activate
   Rg1.Resize(UBound(xArr) + 1).Select

Call TrimXcessSpaces

End Sub

Como funciona:

• Clique em Developer TAB , clique em Design e então em Inserir .
• Selecione o botão de comando na categoria ActiveX e desenhe em qualquer lugar na folha.
• Clique com o botão direito do mouse em botão de comando e selecione Propriedade .
• Localize a Legenda na lista e altere-a para Coluna para linha.
• Fechar janela da propriedade & retornar para a planilha.
• Clique duas vezes no botão de comando para acessar a janela do VB Editor.
• Copiar & Cole este Código entre Sub CommandButton1_Click () e End Sub e clique em Salvar ícone .
• Na janela Editor VB , clique em Comando Arquivo e clique em Fechar & Retornar ao Microsoft Excel.
• Clique no Modo de design para desativá-lo .

Agora, clique no botão de comando na planilha:

• A primeira Caixa de entrada será exibida e, em seguida, selecione Lista separada por vírgula em (B4: B6) & termine com Ok.

• Segunda Caixa de entrada aparecerá selecione qualquer célula única como eu fiz E4 & terminar com o botão Ok.

Você encontra a lista em linhas, como mostrado na captura de tela.

N.B. Ajuste as referências de células conforme necessário.

EDITADO:

Eu editei a postagem desde que PeterH sugeriu que eu removesse White Space da saída da coluna E .

1. Forme a planilha Pressione ALT + F11 para abrir o Editor do VB.
2. Localize o nome da planilha no Windows Explorer do projeto à esquerda.
3. Selecione & Clique com o botão direito do mouse no nome da planilha.
4. Encontre Inserir no menu e clique no comando Módulo .

5. Copiar & Cole este código & Salvar para retornar a planilha.

    Option Explicit
   
    Sub TrimXcessSpaces()
   
     Dim cl As Variant
   
     For Each cl In Selection
        If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
            cl.Value = WorksheetFunction.Trim(cl)
        End If
     Next cl
   End Sub
   

Observação: adicione este código antes do End Sub no código anterior, (verifique o   código acima).

Call TrimXcessSpaces

6. Da folha Clique no botão de comando , a saída será semelhante à mostrada abaixo.