О. Марка з Канзасу надіслала запитання 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 вище. Тепер, коли ви використовуєте ці дані для зведеної таблиці, ви маєте повний контроль над даними, як зазвичай.
