Início / computer / Perguntas / 1424891
Accepted
Jorda
Asked: 2019-04-13 14:10:10 +0800 CST

Empilhe cada enésima coluna uma sob a outra

  • 772

Isso pode ser um problema trivial, mas não consigo descobrir. Eu tenho um conjunto de dados que possui colunas duplicadas e preciso descobrir uma maneira de empilhar cada n-ésima coluna uma abaixo da outra. Então, se considerarmos isso:

dados originais

Podemos ver que temos colunas duplicadas, preciso que meus dados sejam empilhados um embaixo do outro assim:

depois

Minhas colunas duplicadas são uma constante, então sempre terei 5 colunas duplicadas. Qualquer ajuda neste assunto seria altamente apreciada!

microsoft-excel macros

1 respostas

  1. Best Answer

    OFFSETVocê precisa de uma fórmula cíclica aqui.

    CONFIGURAR

    Portanto, temos uma linha de cabeçalho e dados, digamos, A2:ZZZZ38. Vamos apenas colocar isso Sheet1. Vamos configurar nossa saída em Sheet2.

    PRECIS

    Conceitualmente, podemos entender facilmente que queremos Sheet2!A2(doravante chamaremos esta célula apenas A2para simplificar) reproduzir Sheet1!A2. Em A3queremos a próxima célula abaixo, e assim por diante - até que tenhamos examinado todos os registros em Sheet1!A, e então queremos voltar para a primeira linha, mas em 5 colunas.

    Este é claramente um caso para OFFSET.

    FÓRMULA

    =OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

    DEMO

    Meus dados são um pouco menores que os seus; apenas grande o suficiente para provar o conceito.

    Aqui está Sheet1:

    insira a descrição da imagem aqui

    E a saída Sheet2:

    insira a descrição da imagem aqui

    EXPLICAÇÃO

    OFFSETtem três partes. Onde começar, quantas linhas descer (deslocamento vertical) e quantas colunas ultrapassar (deslocamento horizontal).

    A- por onde começar

    Sheet1!A$2

    Bem direto.

    B - deslocamento de linhas

    MOD(ROW()-2,COUNTA(Sheet1!A:A)-1)

    Descubra em que linha estamos e subtraia 2 (estamos começando em A2, e queremos A2ser o deslocamento zero). Divida isso por quantos registros estamos contando (o -1aqui representa a exibição da linha de cabeçalho) e pegue o restante. Isso simplesmente produz uma função que conta de 0até [n-1]onde n é o número de linhas e começa novamente em 0.

    Em outras palavras, para uma tabela com 8 registros, isso retorna:

    0, 1, 2, 3, 4, 5, 6, 7, 0, 1, 2, 3, 4, 5, 6, 7, 0 ...

    C - deslocamento de colunas

    ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5

    Pegue ROW()-2novamente e divida pela contagem de registros novamente - mas desta vez, em vez de pegar o MOD(restante) desse relacionamento, queremos apenas arredondá-lo para o inteiro mais próximo. Isso nos dá uma sequência que consiste em um número de 0 igual à contagem de registros, seguido por um número de 1, 2, etc. Em seguida, multiplique por 5 (consulte a observação abaixo). Isso fornece nosso deslocamento de coluna - toda vez que a contagem cíclica de linhas é redefinida, saltamos outras 5 colunas.

    Em outras palavras, para uma tabela com 8 registros, isso retorna:

    0, 0, 0, 0, 0, 0, 0, 0, 5, 5, 5, 5, 5, 5, 5, 5, 10 ...

    FÓRMULA, NOVAMENTE:

    =OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

    Isso entra Sheet2!A2e copia para A:Ee para baixo sem restrição.

    NB

    1. O 5 é codificado, porque você declarou como uma propriedade de seus dados que as colunas se repetem por 5. Isso pode ser expandido em uma função dinâmica, se necessário (ou, é claro, simplesmente alterado se houver um número fixo de repetidos colunas que não são 5). Isso pode ser uma pergunta para outro dia.
    2. Apesar do enorme tamanho do arquivo, essa fórmula não deve demorar para ser processada. Ele inclui uma referência de coluna inteira, mas dentro de uma COUNTAnão acho que isso irá atrasá-lo. Em caso afirmativo, você pode alterar facilmente cada instância de COUNTA(Sheet1!A:A)para COUNTA(Sheet1!A2:A38), pois tecnicamente já sabemos o tamanho dos dados, ou simplesmente codificar esse valor para 37, o que funcionará desde que você tenha exatamente 37 linhas.
    3. Metade do tempo que levei para acertar isso foi sendo cuidadoso com a ordem das operações aritméticas e dividindo as partes da fórmula em colunas diferentes para solucionar o problema que estava tendo - que acabou sendo um par de parênteses ausente. Na verdade, isso é muito simples; é apenas uma questão de saber que OFFSETexiste e então elaborar a lógica/aritmética para entender como produzir a sequência de pares que você precisa compensar.
    • 3

relate perguntas