Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

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

Importante: 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

Importante: 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 ...
  • Super Formula Bar (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-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. 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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Pradeep · 1 years ago
    Hi, How to do this for Colms to rows. Thank you
    • To post as a guest, your comment is unpublished.
      Jan steihn · 1 years ago
      I've rewritten the command to this one:
      =INDEX($A:$A,ROW(A1)+((COLUMN(A1)-2)*5))
      This executes it inverted.
      So:
      1
      2
      3
      4
      5
      6
      7
      8
      9
      ...
      becomes
      1, 6
      2, 7
      3, 8
      4, 9
      5, ...
  • To post as a guest, your comment is unpublished.
    Maha · 2 years ago
    Thanks alot _/\_ Awesome job with =INDEX($A:$A,ROW(B1)*5-5+COLUMN(B1))
    worked perfectly
  • To post as a guest, your comment is unpublished.
    Arun Balaji VN · 2 years ago
    I tried only the the formula option and it worked great. Till now have been using (Transpose formula with ctrl+shift+enter) but there the disadvantage for we have delete many extra rows. when the rows to be transposed for large in the order of lakhs, then deleting them in excel is either impossible or takes a lot of time.
    this approach made the life easier by only copying the required rows... Thanks
  • To post as a guest, your comment is unpublished.
    Sian · 3 years ago
    This is fricking AWESOME!!! Thank you SO much. Your example was exactly what I needed and it worked beautifully.
  • To post as a guest, your comment is unpublished.
    hamidreza · 3 years ago
    =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))
    It was very simple and really useful. Thank you so much
  • To post as a guest, your comment is unpublished.
    Thank · 3 years ago
    It was great! Thank you! You saved me hours of manual work!
  • To post as a guest, your comment is unpublished.
    xplode · 4 years ago
    Thank you so much!
    It was really helpful. :)
  • To post as a guest, your comment is unpublished.
    MTVN · 4 years ago
    What formula to transpose every 5 rows to a single column?
  • To post as a guest, your comment is unpublished.
    hgimongu · 4 years ago
    Hi!

    May I ask you how to add Blank cell delimits records, instead of 5 as the data I am handling hasn't got fix number of rows. I believe the part which I should change may be around this part of the code:

    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

    Best
    • To post as a guest, your comment is unpublished.
      Mark.lewis2020 · 1 years ago
      I have the same issue , where the columns are not fixed, and there are empty lines, but I have a same character and end of each data set , that is "=" can I use this as delimiter , that breaks the loop, and next line is considered as new data set ?
    • To post as a guest, your comment is unpublished.
      Mark · 1 years ago
      did you get an answer for the same as i have the same issue , columns are not fixed in numbers, what should I do , can I use a particular character as a delimiter that breaks the loop and count next line and new data set ?