Gần đây, một khách hàng đã chia sẻ với tôi dữ liệu đó rõ ràng là một bảng tổng hợp và muốn tôi tạo một bảng tổng hợp khác từ đó bằng Excel. Đây là một vấn đề phổ biến và khó chịu mà tất cả chúng ta đều gặp phải khi làm việc với Excel. Hôm nay, hãy để tôi chia sẻ các phương pháp tạo bản tổng hợp từ một báo cáo tổng hợp khác bằng Excel.
Tùy chọn 1: Nếu bạn có quyền truy cập vào dữ liệu “gốc”
Ha, tôi biết, nhưng chúng ta Có thể mơ à? Vì vậy, nếu bạn có quyền truy cập vào dữ liệu gốc mà trục được tạo ra, chỉ cần sử dụng dữ liệu đó và tạo trục mới theo ý muốn.
Tùy chọn 2: Tạo một Pivot từ một Pivot khác khi bạn không có quyền truy cập vào dữ liệu gốc
Tbh, đây là kịch bản thực sự đối với hầu hết chúng ta. Chúng tôi có một trụ cột và không có quyền truy cập vào dữ liệu đã được sử dụng để tạo ra nó. Bây giờ chúng ta cần thực hiện một trục xoay khác. Trong trường hợp này, hãy làm theo các bước dưới đây.
Xoay vòng từ một trục xoay khác – Mẫu Excel MIỄN PHÍ
Tôi đã tạo một mẫu Excel miễn phí để hướng dẫn bạn thực hiện quy trình bằng dữ liệu mẫu. Tải nó ở đây và sử dụng dữ liệu mẫu để hiểu rõ hơn về quy trình.
Bước 1: Chọn và đặt tên cho phạm vi trụ của bạn
- Chọn toàn bộ bảng tổng hợp (bao gồm mọi tiêu đề) trong Excel.
- Đi tới hộp tên (cạnh thanh công thức ở bên trái)
- Nhập tên “pivot_range”
- Mẹo chuyên nghiệp: Nếu bạn có nhiều trụ, bạn có thể sử dụng các tên như “pivot_range1”, “pivot_range2”
Xem hình minh họa này để biết bước này.
Bước 2: Đi tới Dải băng dữ liệu và tải “trục xoay” vào Power Query
- Giữ bảng tổng hợp được chọn
- Chuyển đến Dải băng dữ liệu
- Nhấp vào tùy chọn “Từ Bảng/Phạm vi” trong khu vực Nhận & Chuyển đổi Dữ liệu
Thao tác này sẽ tải Trình soạn thảo Power Query với Dữ liệu bảng tổng hợp của bạn.
Bước 3: Hãy “bỏ tổng hợp” Bảng tổng hợp bằng Power Query
Giờ đây, “bảng tổng hợp” của chúng tôi đã có trong Power Query, chúng tôi có thể “hủy tổng hợp” bảng đó và tạo một bảng thông thường. Điều này có thể được sử dụng để tạo bảng tổng hợp mới của chúng tôi.
Dưới đây là ảnh chụp nhanh về giao diện của trình soạn thảo Power Query với dữ liệu Pivot_range.
[optional step] Quảng bá tiêu đề nếu cần
Tùy thuộc vào cách thiết lập Pivot nguồn của bạn, bạn có thể cần điều chỉnh tiêu đề cột trong Power Query. Ví dụ: trong trường hợp của tôi, tôi cần quảng bá các tiêu đề. Để thực hiện việc này, hãy nhấp vào nút “Sử dụng hàng đầu tiên làm tiêu đề” trong dải băng Trang chủ của trình soạn thảo Power Query.
Xem hình minh họa bên dưới.
Bước 4: Thay thế “null” bằng giá trị ở trên
Trong trục mẫu của tôi, bạn có thể thấy tên Đại diện không được in ở tất cả các hàng, chỉ ở hàng đầu tiên. Điều này hiển thị như vô giá trị trong trình soạn thảo Power Query cho các hàng còn lại. Chúng ta chỉ cần điền những thứ này dựa trên giá trị hàng đầu.
- Chọn (các) cột có vấn đề này
- Đi tới dải băng “Chuyển đổi” trong Trình soạn thảo Power Query
- Nhấp vào “Fill” và chọn Down để điền vào tất cả các giá trị rỗng với giá trị ở trên
Bước 5: Xóa các hàng có “tổng” & “tổng phụ”
Chúng ta không cần tổng hoặc tổng phụ nữa. Chúng tôi sẽ tính toán chúng trong trục mới nếu cần. Bây giờ, hãy xóa tất cả các hàng và cột có tổng.
- Chọn cột đầu tiên có nhãn “tổng”
- Bấm vào nút “bộ lọc”
- Bỏ chọn bất kỳ nhãn tổng nào.
- Lặp lại các bước cho bất kỳ hàng nào khác cần bước dọn dẹp này.
- Mẹo chuyên nghiệp: Sử dụng Bộ lọc văn bản > Không chứa để lọc tất cả các hàng có từ “tổng” trong đó.
Bước 6: Loại bỏ cột Tổng cộng/Tổng phụ (nếu có)
Chúng ta cũng hãy xóa bất kỳ cột “tổng cộng” và cột “tổng phụ” nào khỏi báo cáo tổng hợp của chúng tôi. Nhấp chuột phải vào cột có tổng số và chọn “xóa” để loại bỏ cột này.
Bước 7: Bỏ xoay dữ liệu
Cuối cùng, báo cáo tổng hợp của chúng tôi đã sẵn sàng để bỏ tổng hợp.
- Chọn (các) cột có nhãn hàng. Trong ví dụ trên, tôi đã chọn cột “đại diện” và “ngày trong tuần”
- Mẹo chuyên nghiệp: Giữ SHIFT hoặc CTRL để chọn nhiều cột cùng một lúc.
- Nhấp chuột phải vào tiêu đề cột của một trong hai cột.
- Chọn “bỏ xoay các cột khác”
- Điều này sẽ định hình lại dữ liệu được xoay vòng sang định dạng không được xoay vòng.
- Xem bản demo nhanh này (GIF):
Bước 8: Đổi tên cột “thuộc tính” & “giá trị” mới
Nhấp đúp vào tiêu đề cột “thuộc tính” và “giá trị” mới được thêm vào để đổi tên chúng thành nhãn thích hợp. Trong trường hợp của tôi, tôi đặt tên chúng là – Giới tính & Cuộc gọi.
Bước 9: Load dữ liệu trở lại Excel để chúng ta làm trục xoay
Được rồi. Chúng ta đã xong. Chỉ cần tải dữ liệu trở lại Excel. Để thực hiện việc này, hãy đi tới dải băng “Trang chủ” và nhấp vào nút “Đóng & Tải”.
Bước 10: Tạo Pivot từ dữ liệu đã tải
Khi dữ liệu đã có trong Excel, chỉ cần chọn bất kỳ ô nào trong dữ liệu, đi tới Insert > Pivot Table (phím tắt: ALT NVT) và thiết lập trục xoay theo nhu cầu của bạn. Trong trường hợp của tôi, tôi cần báo cáo tổng hợp với số lượng cuộc gọi theo Ngày trong Tuần và Người đại diện. Vì vậy, đây là cách tôi thực hiện nó (xem video demo nhanh).
Những điều cần ghi nhớ:
Cách tiếp cận dựa trên Power Query để tạo trục từ một trục khác là tuyệt vời, nhưng bạn cần lưu ý một số điều.
- Không hoạt động ở mức trung bình: Nếu bảng tổng hợp ban đầu của bạn có “trung bình” thay vì “tổng”, bảng tổng hợp mới sẽ không chính xác. Điều này là do bạn sẽ mắc sai lầm về “trung bình trung bình”. Kỹ thuật này chỉ hoạt động tốt khi tính tổng và đếm. Bất kỳ thước đo nào khác như trung bình / trung vị / phút / tối đa, bạn cần dữ liệu “thực tế” để tạo điểm xoay mới.
- Các bước Power Query có thể trở nên phức tạp: Nếu trục ban đầu của bạn có bố cục lồng nhau, rất phức tạp thì các bước PQ cần thiết để “chuyển đổi” dữ liệu có thể phức tạp (nhưng không phải là không thể). Tôi khuyên bạn nên tìm hiểu cách sử dụng Power Query để giải quyết những vấn đề như vậy. Tham khảo bài viết này hoặc băng hình để bắt đầu hành trình PQ của bạn.
- Cần làm mới để thay đổi dữ liệu: Nếu bảng Pivot ban đầu của bạn thay đổi (giá trị mới hoặc hàng/cột mới), bạn cần cập nhật phạm vi được đặt tên là “pivot_range” và làm mới dữ liệu truy vấn nguồn.
- Để cập nhật phạm vi được đặt tên: Đi tới dải băng Công thức trong Excel và nhấp vào “Trình quản lý tên”. Chọn tên “pivot_range” và chỉnh sửa nó. Điều chỉnh các tham chiếu ô theo trục mới được cập nhật của bạn.
- Để làm mới Power Query: Nhấp chuột phải vào dữ liệu Power Query bạn đã tải ở Bước 9. Chọn “Làm mới’ để cập nhật dữ liệu đã tải với các thay đổi mới. Bây giờ, hãy chuyển đến trục xoay bạn đã thực hiện (ở bước 10) và làm mới cả dữ liệu đó (bạn đoán đúng rồi! Nhấp chuột phải và Làm mới).
Phải làm gì nếu bạn gặp lỗi (trong Power Query):
Lỗi có thể xảy ra trong quá trình ban đầu (bước 3 đến 9) hoặc khi bạn làm mới kết nối truy vấn nguồn. Việc giải quyết lỗi phụ thuộc vào bố cục bảng tổng hợp chính xác của bạn và những thay đổi đã được thực hiện. Nhưng đây là những lý do rất có thể gây ra lỗi.
- Tên cột đã thay đổi: Bạn sẽ gặp lỗi nếu các cột của bạn (trong trục ban đầu) bị thay đổi giữa các lần cập nhật. Điều chỉnh tên trong trục ban đầu hoặc đi tới trình soạn thảo Power Query, xác định bước xảy ra lỗi và điều chỉnh tên ở đó.
- Vấn đề về kiểu dữ liệu: Nếu vì lý do nào đó, các giá trị của trục ban đầu được Power Query đọc dưới dạng “văn bản”, thì điều đó có thể tạo ra sự cố. Nhấp chuột phải vào các cột có số và chuyển đổi rõ ràng chúng thành số trong PQ.
- Thay đổi bố cục: Nếu bố cục trục của bạn thay đổi (giả sử thay vì 2 cột thì giờ đây nó có 3 cột nhãn hàng), thì việc làm mới của bạn sẽ không thành công. Bạn cần chọn thêm một cột trước khi hủy xoay vòng (bước 7).
- Các vấn đề khác: Để lại nhận xét về vấn đề/lỗi bạn đang gặp phải để tôi có thể trợ giúp.
Xoay vòng từ một trục xoay khác – Mẫu Excel MIỄN PHÍ
Tôi đã tạo một mẫu Excel miễn phí để hướng dẫn bạn thực hiện quy trình bằng dữ liệu mẫu. Tải nó ở đây và hiểu rõ hơn về quá trình này.
Tóm lại:
Power Query trong Excel cung cấp một cách đơn giản và dễ dàng để giải quyết vấn đề khó chịu khi sử dụng “bảng tổng hợp” làm nguồn dữ liệu. Tôi đã thành công với phương pháp này và tôi hy vọng cũng vậy. Nếu bạn có bất kỳ câu hỏi hoặc vấn đề nào gặp phải trong quá trình thực hiện, hãy để lại nhận xét.