О. Марк от Канзас изпратен в въпроса за 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 по-горе. Сега, когато използвате тези данни за обобщена таблица, имате пълен контрол върху данните като нормални.
