Note: The other languages of the website are Google-translated. Back to English

Como transpor a cada 5 ou n linhas de uma coluna para várias colunas?

doc transpor a cada 5 linhas 1

Suponha que você tenha dados longos na coluna A e agora queira transpor a cada 5 linhas da coluna A para várias colunas, como transpor A1: A5 para C6: G6, A6: A10 para C7: G7 e assim por diante como seguinte captura de tela mostrada. Como você poderia lidar com essa tarefa sem copiar e colar repetidamente no Excel?

Transponha a cada 5 ou n linhas de uma coluna para várias colunas com fórmula

Transponha a cada 5 ou n linhas de uma coluna para várias colunas com código VBA

Transponha a cada 5 ou n linhas de uma coluna para várias colunas com o Kutools para Excel


No Excel, você pode aplicar a seguinte fórmula para transpor a cada n linhas de uma coluna para várias colunas. Faça o seguinte:

1. Insira a seguinte fórmula em uma célula em branco onde deseja colocar o resultado, C1, por exemplo, = ÍNDICE ($ A: $ A, LINHA (A1) * 5-5 + COLUNA (A1)), veja a captura de tela:

doc transpor a cada 5 linhas 2

Nota: Na fórmula acima, A: A é a referência da coluna que você deseja transpor, e A1 é a primeira célula da coluna usada, o número 5 indica o número de colunas que seus dados irão localizar, você pode alterá-las conforme sua necessidade. E a primeira célula da lista deve estar localizada na primeira linha da planilha.

2. Em seguida, arraste a alça de preenchimento para a direita para cinco células e continue arrastando a alça de preenchimento para o intervalo de células até exibir 0, veja a captura de tela:

doc transpor a cada 5 linhas 3


Transforme a cada 5 ou n linhas de uma coluna em várias colunas:

Para transformar a cada 5 ou n linhas de uma coluna em várias colunas, Kutools for Excel's Faixa de transformação O utilitário pode ajudá-lo a resolver esse trabalho o mais rápido possível. Também pode ajudá-lo a transpor um intervalo de dados para uma única linha ou coluna. Clique para baixar o Kutools para Excel!

doc transpor a cada 5 linhas 10

Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!


Se você não pode aplicar a fórmula corretamente, o seguinte código VBA também pode ajudá-lo.

1. Segure o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.

Código VBA: transponha a cada 5 ou n linhas de uma coluna para várias colunas:

Public Sub TransposeData()
'updateby Extendoffice
    Dim xLRow As Long
    Dim xNRow As Long
    Dim i As Long
    Dim xUpdate As Boolean
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select data range(only one column):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count > 1) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range only contain one column", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xLRow = xRg.Rows.Count
    For i = 1 To xLRow Step 5
        xRg.Cells(i).Resize(5).Copy
        xOutRg.Offset(xNRow, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        xNRow = xNRow + 1
    Next
    Application.ScreenUpdating = xUpdate
End Sub

3. Depois de colar o código, pressione F5 para executá-lo e uma caixa de prompt aparecerá para lembrá-lo de selecionar a coluna que deseja transpor, consulte a captura de tela:

doc transpor a cada 5 linhas 4

4. Então clique OKe selecione uma célula onde deseja colocar o resultado em outra caixa exibida, consulte a captura de tela:

doc transpor a cada 5 linhas 5

5. E clique em OK, os dados na coluna foram convertidos em cinco colunas de que você precisa, consulte a captura de tela:

doc transpor a cada 5 linhas 6

Nota: No código acima, você pode alterar o número 5 para outro número que você precisa.


Se você tem Kutools for Excel, Com o seu Faixa de transformação utilitário, você pode transpor rapidamente uma coluna ou linha para várias colunas e linhas.

Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. 

Depois de instalar Kutools for Excel, faça o seguinte:

1. Selecione os dados na coluna e clique em Kutools > Variação > Faixa de transformação, veja a captura de tela:

2. No Faixa de transformação caixa de diálogo, selecione Coluna única para intervalo opção sob o Tipo de transformação, e então verifique Valor fixo sob a Linhas por registroe especifique o número de colunas que deseja transpor no Valor fixo caixa, veja a captura de tela:

doc transpor a cada 5 linhas 8

3. E, em seguida, clique em Ok botão, na caixa exibida, selecione uma célula para gerar o resultado, consulte a captura de tela:

doc transpor a cada 5 linhas 9

4. Então clique OK botão, e os dados da coluna foram transpostos a cada 5 linhas, conforme mostrado a seguir:

doc transpor a cada 5 linhas 6

Baixe e teste grátis Kutools para Excel agora!


Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (15)
Avaliado 5 fora do 5 · classificações 1
Este comentário foi feito pelo moderador no site
Oi! Posso perguntar como adicionar registros de delimitação de células em branco, em vez de 5, pois os dados que estou manipulando não têm um número fixo de linhas. Acredito que a parte que devo alterar pode estar em torno desta parte do código: For i = 1 To xLRow Step 5 xRg.Cells(i).Resize(5).Copy xOutRg.Offset(xNRow, 0).PasteSpecial Paste: =xlPasteAll, Transpose:=True xNRow = xNRow + 1 Melhor
Este comentário foi feito pelo moderador no site
você obteve uma resposta para o mesmo que eu tenho o mesmo problema, as colunas não são fixas em números, o que devo fazer, posso usar um caractere específico como delimitador que quebra o loop e conta a próxima linha e o novo conjunto de dados?
Este comentário foi feito pelo moderador no site
Eu tenho o mesmo problema, onde as colunas não são fixas, e há linhas vazias, mas tenho um mesmo caractere e final de cada conjunto de dados, ou seja, "=" posso usar isso como delimitador, que quebra o loop e próxima linha é considerada como novo conjunto de dados?
Este comentário foi feito pelo moderador no site
Qual fórmula para transpor a cada 5 linhas para uma única coluna?
Este comentário foi feito pelo moderador no site
Muito obrigado! Foi realmente útil. :)
Este comentário foi feito pelo moderador no site
Foi ótimo! Obrigada! Você me salvou horas de trabalho manual!
Este comentário foi feito pelo moderador no site
= ÍNDICE ($ A: $ A, LINHA (A1) * 5-5 + COLUNA (A1))
Foi muito simples e muito útil. Muito obrigado
Este comentário foi feito pelo moderador no site
Isso é MARAVILHOSO!!! Muito obrigado. Seu exemplo era exatamente o que eu precisava e funcionou lindamente.
Este comentário foi feito pelo moderador no site
Eu tentei apenas a opção de fórmula e funcionou muito bem. Até agora tenho usado (Fórmula de transposição com ctrl + shift + enter), mas há a desvantagem de excluir muitas linhas extras. quando as linhas a serem transpostas para grandes na ordem de lakhs, excluí-las no Excel é impossível ou leva muito tempo.
essa abordagem facilitou a vida copiando apenas as linhas necessárias... Obrigado
Este comentário foi feito pelo moderador no site
Muito obrigado _/\_ Trabalho incrível com =INDEX($A:$A,ROW(B1)*5-5+COLUMN(B1))
funcionou perfeitamente
Este comentário foi feito pelo moderador no site
Oi, Como fazer isso para Colms para linhas. Obrigada
Este comentário foi feito pelo moderador no site
Eu reescrevi o comando para este:
=ÍNDICE($A:$A,LINHA(A1)+((COLUNA(A1)-2)*5))
Isso o executa invertido.
Assim:
1
2
3
4
5
6
7
8
9
...
torna-se
1, 6
2, 7
3, 8
4, 9
5, ...
Este comentário foi feito pelo moderador no site
Eu tento fazer isso como está escrito, e tudo o que recebo é Error:508
Este comentário foi feito pelo moderador no site
Você é um génio. Obrigado!
Avaliado 5 fora do 5
Este comentário foi feito pelo moderador no site
Bom Dia,

J'ai, dans mon unique colonne de données, des cellules placées aléatoirement toutes les x lignes et qui contiennent une suite de caractères.

Eu voudrait que esta célula está na primeira coluna da linha transposta, avec, nas colunas seguintes, a transposição das linhas seguintes, apenas a repetição de uma cadeia de caracteres, o que significa a estreia da linha seguinte. ..

Bref, Heeelllp...

Merci d'avance
Stéphane
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL