Dividir coluna em várias colunas - no sentido horário

Question

Eu tenho uma coluna no Excel com 3.652 valores. Como faço para dividir essa coluna em datas, de modo que eu tenha dez colunas, uma para cada ano, começando em uma data específica?

Dos comentários:
Eu estou trabalhando com dados climatológicos diários de cerca de 20 anos. Isso cria uma série temporal longa de duas colunas, primeiro contendo data e segundo contendo valores. Eu tenho que calcular médias anuais e mensais para as quais eu tenho que usar copiar e colar função para organizar o ano ou mês, o que leva muito tempo e esforço. Mas li sobre as funções Macros e Matriz, mas não sei como escrevê-las.

    

Answer 1

Você pode fazer isso com uma fórmula de matriz na planilha.

Continue e crie seus cabeçalhos de coluna manualmente (rotule como o ano, por exemplo, 2013 , porque isso será usado na fórmula).

Na primeira linha abaixo do primeiro cabeçalho, insira a seguinte fórmula como uma fórmula de matriz (cole a fórmula na barra de fórmulas e pressione Ctrl + Deslocamento + Digite ), onde A2:A3653 é sua grande coluna de datas e B1 é o cabeçalho da coluna do primeiro ano:

=IFERROR(TEXT(SMALL(IF(YEAR($A$2:$A$3653)=B$1,$A$2:$A$3653,""),ROW()-1),"m/d/yyyy"),"")

Neste exemplo, a fórmula é inserida na linha 2. Se as colunas de saída começarem em uma linha diferente, será necessário ajustar a parte ROW() - 1 da fórmula para que ROW() - x seja igual a 1 para a primeira data (por exemplo, se os cabeçalhos estiverem na linha 3 e a primeira linha de datas estiver na linha 4, você deverá alterar essa parte da fórmula para ROW()-3 .

Esta fórmula pode ser preenchida por todas as 10 colunas. Por fim, preencha o que você precisa (provavelmente entre 400 e 500 linhas, dependendo da distribuição das datas). Fórmulas de matriz fazem muito trabalho computacional, então isso provavelmente levará alguns segundos para ser calculado.

Quando seus dados estiverem divididos, talvez você queira copiar todas essas células e colar os valores especiais no mesmo intervalo. Isso removerá as fórmulas e melhorará o desempenho da sua planilha.

    

Answer 2

REVISADO com base em informações adicionais fornecidas pelo OP

Pelo que entendi, você deseja transformar uma coluna de valores em uma tabela, com cada coluna representando o valor de um ano de valores a partir de uma data especificada. Ao desenvolver minha resposta, fiz três suposições para lidar com as possibilidades que sua pergunta suscita, mas não aborda:

1. Os valores na coluna original já estão associados a datas específicas, ou seja, o primeiro valor está vinculado a uma data específica, digamos, 1º de janeiro de 2013, o segundo valor para 2 de janeiro etc.

2. Esses valores podem ser dados numéricos ou podem ser datas consecutivas.

3. A data especificada pode ser qualquer data no ano.

A fórmula a seguir produzirá a saída desejada. Ele deve ser inserido na célula inicial da tabela de saída copiada para um total de dez colunas para os dez anos de valores e para baixo nas 366 linhas de valores para cada ano, incluindo uma linha para o dia extra em anos bissextos. (A configuração completa da planilha é mostrada nas fotos abaixo.)

=IFERROR
        (
         INDEX
              (
               Data1,
               IF
                 (
                  AND
                     (
                      DATE(YEAR(E$2)+1,MONTH(E$2),DAY(E$2))-E$2=365,
                      ROW(E3)-ROWS(E$2)-1=366
                     ),
                  "-",
                  E$2-$C$2+COUNTA(E$2:E2)
                 ),
               1
              ),
         "-"
        )

Essa expressão é essencialmente uma fórmula INDEX , elaborada para lidar com o que mostrar na 366ª linha da tabela de saída quando o ano não é um ano bissexto. Data1 é o intervalo nomeado de valores de dados; a instrução IF retorna o índice da linha, exceto em anos não bissexuais, quando substitui com um traço (-) o índice de linhas calculado para a 366ª linha da tabela de saída.

A tabela de saída propriamente dita começa na célula E2. Se sua tabela for colocada de maneira diferente na planilha, você precisará ajustar as referências ancoradas E $ 2, $ E $ 2 e $ C $ 2 na fórmula para refletir onde a primeira célula na linha de cabeçalho da tabela começa e onde a data para o primeiro valor é.

A figura abaixo mostra os valores (neste caso, apenas um conjunto de datas que começam em 1/1/2013), as outras duas entradas necessárias e a tabela de saída resultante com uma data inicial de 1/4/2013.

A seção intermediária da imagem mostra a parte inferior da tabela de saída, que exibe corretamente um valor extra para o ano bissexto de 2016.

A seção inferior mostra que os valores de dados 3652 são preenchidos com o caractere de traço, que é usado para indicar quando nenhum outro valor permanece no décimo ano, devido a uma data de início após o início do ano.

Aimagemabaixomostraamesmaplanilhausandovalores(aleatórios)diferentesdedatascomoentrada.AúnicaalteraçãonasfórmulasdesaídaéousodointervalodevaloresnomeadosData2emvezdeData1.

    

Answer 3

Pela informação extra na sua pergunta, adicionaria 3 colunas extras e criaria uma tabela dinâmica

Estou supondo que a data esteja na coluna A e os dados na coluna B.
por exemplo.

Date       Rainfall
1/1/1994   0.04
1/2/1994   0.02
...

as colunas extras tornariam a tabela assim:

Date       Rainfall  Month       Year       YYYY-MM
1/1/1994   0.04      =MONTH(A2)  =YEAR(A2)  =TEXT(A2,"YYYY-MM")
1/2/1994   0.02      =MONTH(A3)  =YEAR(A3)  =TEXT(A3,"YYYY-MM")
...

Em seguida, posso criar um gráfico dinâmico com coisas como uma comparação mês / ano (meses ao longo de um eixo e anos ao longo de outro) e também permite fazer totais anuais e mensais, colocando as colunas apropriadas ao longo do eixo