Пт. Марк из Канзаса прислал на этой неделе вопрос Excel:
Сводные таблицы Excel работают лучше всего, когда данные разбиты на максимально возможное количество записей, но это не всегда самый интуитивно понятный способ загрузки данных в Excel. Например, загрузка данных, как на рисунке 1, интуитивно понятна, но лучший способ анализа данных - как на рисунке 2.
Во-первых, я рассмотрю далеко не идеальный способ работы Excel с несколькими полями данных. Во-вторых, я продемонстрирую простой и быстрый макрос для преобразования рисунка 1 в рисунок 2.
Если ваши данные похожи на рисунок 1, во время шага 3 из 4 мастера сводных таблиц можно перетащить все 4 квартальных поля в область данных сводной таблицы, как показано справа.
Вот не идеальный результат. По умолчанию в Excel есть несколько полей данных, идущих вниз по странице. Вы можете нажать на серую кнопку «Данные» и перетащить ее вверх и вправо, чтобы четверти проходили по странице. Однако вам не хватает итогов для каждого региона, и итоги за квартал всегда будут казаться неуместными.
Итак, о. Марк попал в точку, когда сказал, что данные действительно должны быть в формате, показанном на рисунке 2, чтобы их можно было правильно проанализировать. Ниже приведен макрос, который быстро перемещает данные в формате, показанном на Рисунке 1 на Листе 1, на Лист2 в формате Рисунка 2. Этот макрос недостаточно универсален для работы с любым набором данных. Тем не менее, его должно быть относительно легко настроить для вашей конкретной ситуации.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
После запуска этого макроса данные будут в более удобном для анализа формате, показанном на рисунке 2 выше. Теперь, когда вы используете эти данные для сводной таблицы, у вас есть полный контроль над данными, как обычно.
