Разделить столбец на несколько столбцов - по дате

Question

У меня есть столбец в Excel с 3652 значениями. Как разделить этот столбец по дате, чтобы у меня было десять столбцов, по одному на каждый год, начиная с указанной даты?

Из комментариев:
Я работаю с ежедневными климатологическими данными около 20 лет. Это создает длинный временной ряд из 2 столбцов, первый из которых содержит дату, а второй содержит значения. Мне нужно рассчитать среднегодовые и месячные средние значения, для которых я должен использовать функцию копирования и вставки, чтобы упорядочить год или месяц, что требует много времени и усилий. Но я читал о функциях Macros и Array, но не знаю, как их написать.

Answer 1

ПЕРЕСМОТРЕН на основании дополнительной информации, предоставленной ОП

Насколько я понимаю, вы хотите преобразовать столбец значений в таблицу, в которой каждый столбец представляет значение года, начиная с указанной даты. Разрабатывая свой ответ, я сделал три предположения, чтобы рассмотреть возможности, которые возникают в связи с вашим вопросом:

1. Значения в исходном столбце уже связаны с конкретными датами, т. Е. Первое значение привязано к определенной дате, например, 1 января 2013 г., второе значение - 2 января и т.д.

2. Эти значения могут быть числовыми данными или последовательными датами.

3. Указанная дата может быть любой датой в году.

Следующая формула даст желаемый результат. Он должен быть введен в начальную ячейку выходной таблицы, скопированной в общей сложности для десяти столбцов для десятилетних значений, и вниз по 366 строкам значений для каждого года, включая строку для дополнительного дня в високосных годах. (Полная настройка рабочего листа показана на рисунках ниже.)

=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
              ),
         "-"
        )

Это выражение по сути является формулой INDEX , разработанной для того, чтобы показать, что показывать в 366-й строке выходной таблицы, когда год не является високосным. Data1 - именованный диапазон значений данных; оператор IF возвращает индекс строки, за исключением не високосных лет, когда он заменяет тире (-) вычисленный индекс строки для 366-й строки выходной таблицы.

Собственно выходная таблица начинается в ячейке E2. Если ваша таблица размещается по-разному на листе, вам нужно настроить привязанные ссылки E $ 2, $ E $ 2 и $ C $ 2 в формуле, чтобы отразить, где начинается первая ячейка в строке заголовка таблицы и где дата первое значение

На рисунке ниже показаны значения (в данном случае просто набор дат, начинающийся с 01.01.2013), два других необходимых ввода и итоговая выходная таблица с датой начала 04.01.2013.

Средняя часть рисунка показывает нижнюю часть выходной таблицы, которая правильно отображает одно дополнительное значение для високосного года 2016 года.

Нижний раздел показывает, что значения данных 3652 дополняются символом тире, который используется, чтобы указать, когда в десятом году больше не осталось значений из-за даты начала после начала года.

На рисунке ниже показана та же самая рабочая таблица с использованием (случайных) значений, отличных от даты, в качестве входных данных. Единственным изменением в выходных формулах является использование именованного диапазона значений Data2 вместо Data1 .

Answer 2

К дополнительной информации в вашем вопросе я бы добавил 3 дополнительных столбца и сделал бы сводную таблицу

Я предполагаю, что дата в столбце A, а данные в столбце B.
например

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

дополнительные столбцы будут выглядеть так:

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")
...

Затем я мог бы составить сводную диаграмму, которая бы имела такие вещи, как сравнение месяца / года (месяцы по одной оси и годы по другой), а также позволяет мне составлять годовые и месячные итоги, помещая соответствующие столбцы вдоль оси

Answer 3

Вы можете сделать это с помощью формулы массива на листе.

Создайте заголовки столбцов вручную (пометьте как год, например, 2013 , потому что это будет использоваться в формуле).

В первой строке под первым заголовком введите следующую формулу в виде формулы массива (вставьте формулу в строку формул и нажмите Ctrl+Shift+Enter), где A2:A3653 - ваш большой столбец дат, а B1 - заголовок для колонка первого года:

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

В этом примере формула вводится в строку 2. Если ваши выходные столбцы начинаются с другой строки, вам нужно настроить часть формулы ROW() - 1 так, чтобы ROW() - x равнялся 1 для первой даты (например, если ваши заголовки находятся в строке 3). и первая строка дат находится в строке 4, вам нужно изменить эту часть формулы на ROW()-3 .

Эта формула может быть заполнена через все 10 столбцов. Наконец, заполните столько, сколько вам нужно (возможно, 400-500 строк, в зависимости от распределения дат). Формулы массива выполняют большую вычислительную работу, поэтому для их вычисления, вероятно, потребуется несколько секунд.

Как только ваши данные разделены, вы можете скопировать все эти ячейки и вставить специальные значения обратно в тот же диапазон. Это удалит формулы и улучшит производительность вашего рабочего листа.