Bảng Pivot của Excel là một trong những công cụ mạnh mẽ nhất có thể giúp bạn tóm tắt và phân tích các tập dữ liệu lớn một cách hiệu quả.
Bảng tổng hợp là một trong những công cụ tóm tắt dữ liệu mạnh mẽ và hữu ích nhất trong Excel cho phép bạn nhanh chóng tóm tắt, sắp xếp, tổ chức lại, phân tích, nhóm, đếm các tập dữ liệu lớn.
Bảng tổng hợp cho phép bạn xoay (hoặc xoay) dữ liệu được lưu trữ trong bảng để xem nó từ các góc độ khác nhau và hiểu rõ ràng về các tập dữ liệu lớn.
Hướng dẫn này sẽ cung cấp cho bạn hướng dẫn từng bước về cách tạo và sử dụng bảng tổng hợp trong Excel.
Tổ chức dữ liệu của bạn
Để tạo bảng tổng hợp, dữ liệu của bạn phải có cấu trúc bảng hoặc cơ sở dữ liệu. Vì vậy, bạn cần tổ chức dữ liệu của mình thành các hàng và cột. Để chuyển đổi phạm vi dữ liệu của bạn thành một bảng, hãy chọn tất cả dữ liệu, chuyển đến tab "Chèn" và nhấp vào "Bảng". Trong hộp thoại Tạo bảng, nhấp vào ‘OK’ để chuyển đổi tập dữ liệu thành bảng.
Sử dụng Bảng Excel làm tập dữ liệu nguồn để tạo bảng tổng hợp, làm cho bảng tổng hợp của bạn động. Khi bạn thêm hoặc xóa các mục nhập trong bảng Excel, dữ liệu trong pivot sẽ cập nhật cùng với nó.
Giả sử bạn có một tập dữ liệu lớn như hình dưới đây, nó bao gồm hơn 500 bản ghi và 7 trường. Ngày, Khu vực, Loại nhà bán lẻ, Công ty, Số lượng, Doanh thu và Lợi nhuận.
Chèn bảng tổng hợp
Trước tiên, hãy chọn tất cả các ô có chứa dữ liệu và chuyển đến tab ‘Chèn’ và nhấp vào ‘PivotChart’. Sau đó, chọn tùy chọn ‘PivotChart & PivotTable’ từ trình đơn thả xuống.
Hộp thoại Tạo PivotTable sẽ mở ra. Excel sẽ tự động xác định và điền vào phạm vi chính xác trong 'Trường bảng / phạm vi', nếu không, hãy chọn đúng bảng hoặc phạm vi ô. Sau đó, chỉ định vị trí đích cho bảng tổng hợp Excel của bạn, nó có thể là ‘Trang tính mới’ hoặc ‘Trang tính hiện có’ và nhấp vào ‘OK’.
Nếu bạn chọn 'Trang tính mới', một trang tính mới có bảng tổng hợp trống và biểu đồ tổng hợp sẽ được tạo trong một trang tính riêng biệt.
Tạo bảng tổng hợp của bạn
Trong trang tính mới, bạn sẽ thấy một bảng tổng hợp trống ở bên trái cửa sổ Excel và ngăn 'Trường bảng tổng hợp' ở cạnh bên phải của cửa sổ Excel, tại đây, bạn sẽ tìm thấy tất cả các tùy chọn để định cấu hình bảng tổng hợp của bạn.
Ngăn Trường PivotTable được chia thành hai phần ngang: Phần trường (đầu ngăn) và phần Bố trí (cuối ngăn)
- Các Mục trường liệt kê tất cả các trường (cột) mà bạn đã thêm vào bảng của mình. Các tên trường này là tất cả các tên cột từ bảng nguồn của bạn.
- Các Phần bố cục có 4 khu vực tức là Bộ lọc, Cột, Hàng và Giá trị, nơi bạn có thể sắp xếp và bố trí lại các trường.
Thêm trường vào bảng tổng hợp
Để tạo bảng tổng hợp, hãy kéo và thả các trường từ phần Trường vào các vùng của phần Bố cục. Bạn cũng có thể kéo các trường giữa các khu vực.
Thêm hàng
Chúng ta sẽ bắt đầu bằng cách thêm trường "Công ty" vào phần Hàng. Thông thường, các trường không phải số được thêm vào vùng Hàng của Bố cục. Chỉ cần Kéo và thả trường ‘Comapny’ vào vùng ‘Row’.
Tất cả các tên công ty từ cột ‘Công ty’ trong bảng nguồn sẽ được thêm dưới dạng các hàng trong bảng tổng hợp và chúng sẽ được sắp xếp theo thứ tự tăng dần, nhưng bạn có thể nhấp vào nút thả xuống trong ô Nhãn Hàng để thay đổi thứ tự.
Thêm giá trị
Bạn đã thêm một hàng, bây giờ hãy thêm một giá trị vào bảng đó để biến nó thành bảng một chiều. Bạn có thể tạo bảng tổng hợp một chiều bằng cách chỉ thêm hàng hoặc nhãn cột và các giá trị tương ứng của chúng vào các vùng. Vùng giá trị là nơi các phép tính / giá trị được lưu trữ.
Trong ảnh chụp màn hình ví dụ trên, chúng tôi có một hàng công ty, nhưng chúng tôi muốn tìm hiểu tổng doanh thu của mỗi công ty. Để có được điều đó, chỉ cần kéo và thả trường "Doanh thu" vào hộp "Giá trị".
Nếu bạn muốn xóa bất kỳ trường nào khỏi phần Khu vực, chỉ cần bỏ chọn hộp bên cạnh trường trong phần Trường.
Bây giờ, chúng ta có một bảng một chiều về các công ty (nhãn hàng) cùng với tổng doanh thu.
Thêm cột
Bảng hai chiều
Các Hàng và Cột kết hợp với nhau sẽ tạo ra một bảng hai chiều và điền vào các ô với chiều thứ ba của các giá trị. Giả sử bạn muốn tạo bảng tổng hợp bằng cách liệt kê tên công ty dưới dạng hàng và sử dụng các cột để hiển thị Ngày và điền vào các ô có tổng doanh thu.
Khi bạn thêm trường ‘Ngày’ vào Vùng ‘Cột’, Excel sẽ tự động thêm ‘Hàng quý’ và ‘Năm’ vào các trường cột, để tính toán và tóm tắt dữ liệu tốt hơn.
Bây giờ, chúng ta có bảng hai chiều với ba chiều giá trị.
Thêm bộ lọc
Trong trường hợp bạn muốn lọc ra dữ liệu theo ‘Vùng’, bạn có thể kéo và thả trường ‘Vùng’ vào vùng Bộ lọc.
Điều này thêm một menu thả xuống phía trên Bảng Pivot của bạn với ‘Trường Bộ lọc’ đã chọn. Cùng với đó, bạn có thể lọc ra doanh thu của các công ty hàng năm theo khu vực.
Theo mặc định, tất cả các vùng đều được chọn, bỏ chọn chúng và chỉ chọn vùng bạn muốn lọc dữ liệu. Nếu bạn muốn lọc bảng theo nhiều mục nhập, hãy chọn hộp kiểm bên cạnh 'Chọn nhiều mục' ở cuối trình đơn thả xuống. Và chọn nhiều vùng.
Kết quả:
Sắp xếp
Nếu bạn muốn sắp xếp giá trị bảng theo thứ tự tăng dần hoặc giảm dần, hãy nhấp chuột phải vào bất kỳ ô nào bên trong cột Tổng doanh thu, sau đó mở rộng ‘Sắp xếp’ và chọn thứ tự.
Kết quả:
Phân nhóm
Giả sử bạn có dữ liệu được liệt kê theo tháng trong bảng tổng hợp nhưng bạn không muốn xem dữ liệu đó hàng tháng, thay vào đó, bạn muốn sắp xếp lại dữ liệu thành các quý tài chính. Bạn có thể làm điều đó trong bảng tổng hợp của mình.
Đầu tiên hãy chọn các cột và nhấp chuột phải vào nó. Sau đó, chọn tùy chọn ‘Nhóm’ từ trình đơn thả xuống.
Trong cửa sổ Nhóm, hãy chọn "Quarters" và "Years", vì chúng tôi muốn chúng được tổ chức thành các quý tài chính hàng năm. Sau đó, nhấp vào ‘OK’.
Giờ đây, dữ liệu của bạn được sắp xếp thành các quý tài chính hàng năm.
Cài đặt trường giá trị
Theo mặc định, bảng tổng hợp tóm tắt các giá trị số bằng hàm Sum. Nhưng bạn có thể thay đổi kiểu tính toán được sử dụng trong vùng Giá trị.
Để thay đổi chức năng tóm tắt, hãy nhấp chuột phải vào bất kỳ dữ liệu nào trong bảng, nhấp vào ‘Tóm tắt các giá trị theo’ và chọn tùy chọn của bạn.
Ngoài ra, bạn có thể nhấp vào mũi tên xuống bên cạnh ‘Tổng của ..’ trong vùng giá trị của phần trường và chọn ‘Cài đặt Trường Giá trị’.
Trong 'Cài đặt trường giá trị', hãy chọn chức năng tóm tắt dữ liệu của bạn. Sau đó, nhấp vào ‘OK’. Ví dụ của chúng tôi, chúng tôi đang chọn 'Đếm' để đếm số lượng lợi nhuận.
Kết quả:
Bảng tổng hợp của Excel cũng cho phép bạn hiển thị các giá trị theo nhiều cách khác nhau, chẳng hạn như hiển thị Tổng cộng dưới dạng phần trăm hoặc Tổng số cột dưới dạng phần trăm hoặc Tổng hàng dưới dạng phần trăm hoặc giá trị thứ tự từ nhỏ nhất đến lớn nhất và ngược lại, nhiều hơn nữa.
Để hiển thị giá trị dưới dạng tỷ lệ phần trăm, hãy nhấp chuột phải vào bất kỳ đâu trên bảng, sau đó nhấp vào ‘Hiển thị giá trị dưới dạng’ và chọn tùy chọn của bạn.
Khi chúng tôi chọn '% của Tổng số Cột', kết quả sẽ như thế này,
Làm mới bảng Tổng hợp
Mặc dù báo cáo bảng tổng hợp là động, khi thực hiện các thay đổi trong bảng nguồn, Excel không tự động làm mới dữ liệu trong bảng tổng hợp. Nó cần được 'làm mới' theo cách thủ công để cập nhật dữ liệu.
Nhấp vào bất kỳ đâu trong bảng tổng hợp và chuyển đến tab ‘Phân tích’, nhấp vào nút ‘Làm mới’ trong nhóm Dữ liệu. Để làm mới bảng tổng hợp hiện tại trong trang tính, hãy nhấp vào tùy chọn 'Làm mới'. Nếu bạn muốn làm mới tất cả các bảng tổng hợp trong sổ làm việc, hãy bấm vào ‘Làm mới tất cả’.
Ngoài ra, bạn có thể nhấp chuột phải vào bảng và chọn tùy chọn 'Làm mới'.
Đó là nó. Chúng tôi hy vọng bài viết này cung cấp cho bạn tổng quan chi tiết về Excel’s Pivot Tables và giúp bạn tạo một bảng.