Преобразование данных в Excel

Question

У меня две проблемы с моими данными в Excel.

1. У меня есть эта таблица, поэтому для каждого дня в году у меня есть 24 значения, то есть 365 столбцов.

image1 http://imageshack.us/a/img600/1773/dataxz.jpg

Мне нужно написать макрос, который преобразует данные вертикально, друг под другом. Как в другой таблице. Таким образом, 1.1.1996 находится в (B4:B28), 2.1.1996 (B29:B53) и т.д. Я сделал простой макрос, но я не знаю, как продолжить его автоматически.

2. Вторая проблема - это сводная таблица, или, как легко составить, например, ежедневные / еженедельные / ежемесячные средние или суммы для каждого столбца в отдельности. И чем график. Я сделал один стол, но это делает меня неправильно в среднем

image2 http://imageshack.us/a/img690/5305/pivottable.png

Answer 1

Вы можете сделать транспонирование без какого-либо макроса:

Прямой способ для вашего первого шага - использовать функцию TRANSPOSE , то есть выбрать диапазон, который равен 365rowsx24cols, ввести =TRANSPOSE(B4:NB27) и нажать Ctrl+Shift+Enter.

Однако я бы порекомендовал решить проблемы 1 и 2 немного по-другому: переведите ваши данные в список, то есть в таблицу с 365x24 строками x 3 столбцами: Date, Hour, Value. Эта таблица может быть легко использована в качестве источника сводной таблицы!

Для этого вы можете использовать два вспомогательных столбца - первый, начинающийся с 1 и увеличивающий каждую строку на одну, пока не станет 24, а затем сбрасывается (в A2: 1 , в A3: =IF(A2=24,1,A2+1)), вторая начинается с 1 и увеличивается на 1 каждые 24 строки (B2: 1 , B3: =IF(A3=1,B2+1,B2) . В качестве альтернативы, вы можете просто использовать функцию ROW() (которая возвращает строку строки с формулой, если не указан параметр), чтобы получить число от 1 до 24 с помощью MOD(ROW(),24)-1 и 1 до 365 с INT((ROW()-2)/24)+1 . (+1 и -2 предназначены для корректировки таблицы данных, начиная со строки 2).

Используя функцию INDEX с этими двумя параметрами, вы теперь можете легко получить данные для 3 столбцов:

Дата: =INDEX($B$3:$NB$3,B2) (или без вспомогательных столбцов: =INDEX($B$3:$NB$3,INT((ROW()-2)/24)+1))

Час: =INDEX($A$4:$A$27,A2)(или =INDEX($A$4:$A$27,MOD(ROW(),24)-1))

Значение: =INDEX($B$4:$NB$27,A2,B2)(или =INDEX($B$4:$NB$27,MOD(ROW(),24)-1),INT((ROW()-2)/24)+1))