Cha Đánh dấu từ Kansas được gửi trong câu hỏi Excel của tuần này:
Bảng tổng hợp Excel hoạt động tốt nhất khi dữ liệu được chia nhỏ thành nhiều bản ghi nhất có thể, nhưng đây không phải lúc nào cũng là cách trực quan nhất để tải dữ liệu vào Excel. Ví dụ, tải dữ liệu như Hình 1 thì trực quan, nhưng cách tốt nhất để phân tích dữ liệu là như trong Hình 2.
Đầu tiên, tôi sẽ xem qua cách xử lý kém hoàn hảo của Excel với nhiều trường dữ liệu. Thứ hai, tôi sẽ giới thiệu một macro đơn giản và nhanh chóng để chuyển đổi hình 1 thành hình 2.
Nếu dữ liệu của bạn giống như hình 1, trong bước 3/4 của Trình hướng dẫn PivotTable, bạn có thể kéo tất cả 4 trường phần tư vào vùng dữ liệu của bảng tổng hợp, như được hiển thị ở bên phải.
Đây là kết quả không thể hoàn hảo hơn. Theo mặc định, Excel có nhiều trường dữ liệu đi xuống trang. Bạn có thể nhấp vào nút "Dữ liệu" màu xám và kéo nó lên và sang phải để làm cho các phần tư đi khắp trang. Tuy nhiên, bạn đang thiếu tổng số cho từng khu vực và tổng số Quý sẽ luôn có vẻ không đúng.
Vì vậy, Fr. Mark đã đánh một cái đinh vào đầu khi anh ta nói rằng dữ liệu thực sự cần phải ở định dạng của Hình 2 để phân tích nó một cách chính xác. Dưới đây là macro sẽ nhanh chóng di chuyển dữ liệu theo định dạng của Hình 1 trên Sheet1 sang Sheet2 theo định dạng của Hình 2. Macro này không đủ chung để làm việc với bất kỳ tập dữ liệu nào. Tuy nhiên, nó sẽ tương đối dễ dàng để tùy chỉnh nó theo tình huống cụ thể của bạn.
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
Sau khi chạy macro này, dữ liệu sẽ ở định dạng dễ phân tích hơn như trong hình 2 ở trên. Bây giờ, khi bạn sử dụng dữ liệu này cho bảng tổng hợp, bạn có toàn quyền kiểm soát dữ liệu như bình thường.
