Cách tạo bảng tổng hợp trong Excel

Nhưng đợi đã. Trước khi bạn tạo bảng tổng hợp, hãy đảm bảo rằng tất cả các cột đều có tiêu đề.

Nếu bất kỳ tiêu đề cột nào bị bỏ trống, bảng tổng hợp sẽ không được tạo và sẽ xuất hiện thông báo lỗi.

Yêu cầu 1: Tất cả các cột phải có một tiêu đề để bắt đầu với Pivot Tables trong Excel

Bạn nên sắp xếp dữ liệu của mình với tiêu đề thích hợp. Sau khi có nó, bạn có thể chèn bảng tổng hợp.

Để chèn bảng tổng hợp từ menu, hãy làm theo các bước sau:

Đây là một phím tắt tuần tự để mở hộp tùy chọn Tạo Bảng Pivot.

Nhấn nút Alt và thả nó ra. Nhấn N và thả nó ra. Nhấn V và thả nó ra. Hộp tùy chọn Tạo Bảng Pivot sẽ mở ra.

Bây giờ chỉ cần làm theo quy trình trên để tạo bảng tổng hợp trong excel.

Một điều tôi thích nhất ở Microsoft Excel là trong mỗi phiên bản Excel mới, họ giới thiệu các tính năng mới nhưng họ không loại bỏ các tính năng cũ (giống như MS đã làm với win 8. Thật là thảm hại). Điều này * cho phép người dùng cũ làm việc bình thường trên các phiên bản mới như họ đã từng làm việc trên các phiên bản cũ hơn.

Nếu bạn nhấn liên tiếp ALT, D và P trên bàn phím, Excel sẽ mở ra để tạo trình hướng dẫn bảng tổng hợp.

Chọn tùy chọn thích hợp. Tùy chọn đã chọn trong ảnh chụp màn hình ở trên sẽ dẫn chúng ta tạo một bảng tổng hợp như chúng ta đã tạo trước đó.

Nhấn Enter hoặc nhấp vào Tiếp theo nếu bạn muốn kiểm tra phạm vi đã chọn của mình.

Nhấn Enter một lần nữa.

Chọn một trang tính mới hoặc bất kỳ nơi nào bạn muốn bảng tổng hợp của mình Nhấn Enter. Và nó đã hoàn thành.

Bây giờ bạn đã biết cách chèn bảng tổng hợp. Hãy chuẩn bị để tạo báo cáo bằng cách sử dụng bảng tổng hợp trong vài phút.

Chúng tôi có dữ liệu cho đơn đặt hàng tĩnh.

Các trường cột là:

Ngày đặt hàng: Ngày đặt hàng (Trước đó)

Khu vực: Khu vực đặt hàng trong quốc gia Khách hàng: Tên khách hàng (_ có thể khác) _ Mặt hàng: Mặt hàng đã đặt hàng Đơn vị: Số lượng đơn vị của một mặt hàng đã đặt hàng Đơn vị chi phí: Mỗi đơn vị chi phí Tổng: Tổng chi phí của đơn hàng (đơn vị Giá cả).

Khu vực tổng hợp: 4 khu vực này được sử dụng để hiển thị dữ liệu của bạn theo cách có quy tắc.

BỘ LỌC: Đặt các trường ở đây mà bạn muốn áp dụng các bộ lọc trong báo cáo của mình.

COLUMNS: Đưa vào đây các trường mà bạn muốn vào các cột của mình trong báo cáo: (Tốt hơn là hiển thị hơn là giải thích)

ROWS: Kéo các trường mà bạn muốn hiển thị thô như trong hình trên, tôi đã hiển thị Vùng trong ROWS.

GIÁ TRỊ: Chọn một trường để nhận Đếm, Tổng, Trung bình, Phần trăm (và nhiều hơn nữa), v.v. bạn sẽ muốn xem.

Bây giờ bằng cách sử dụng thông tin trên, chúng tôi đã chuẩn bị báo cáo tổng hợp nhanh này cho biết số lượng đơn đặt hàng được đặt từ khu vực nào cho mỗi mặt hàng.

Bây giờ bạn đã hiểu dữ liệu của mình và các trường Pivot (dù sao thì bạn cũng thông minh), hãy nhanh chóng trả lời một số câu hỏi liên quan đến dữ liệu này bằng cách sử dụng bảng tổng hợp.

Bảng tổng hợp ban đầu để trống, như thể hiện trong hình ảnh bên dưới.

Bạn cần chọn các trường (tên cột) trong các khu vực thích hợp để xem tóm tắt hoặc chi tiết của trường đó.

Bây giờ, để trả lời câu hỏi trên, tôi sẽ chọn Mục (chọn bất kỳ cột nào, chỉ cần đảm bảo rằng cột đó không có ô trống ở giữa) trong các trường giá trị.

Chọn Mục từ danh sách trường và kéo nó vào trường Giá trị.

Chúng tôi có câu trả lời của chúng tôi. Bảng tổng hợp cho tôi biết rằng có tổng cộng 43 đơn đặt hàng. Chính xác.

Mẹo chuyên nghiệp: Bạn nên kiểm tra dữ liệu của mình xem nó có đúng hay không. Nếu số này không khớp với dữ liệu thì có nghĩa là bạn đã chọn một số phạm vi không chính xác hoặc trường đó có các ô trống.

Thông tin: Trường giá trị theo mặc định đếm số mục nhập trong một cột nếu nó chứa văn bản và tổng nếu trường chỉ chứa giá trị.

Bạn có thể thay đổi điều này trong cài đặt trường giá trị. Làm sao? Chúng tôi sẽ gặp bạn ở phần sau trong Hướng dẫn Pivot Table này.

Bây giờ tôi đã chọn OrderDate trong Valuesarea, nó hiển thị * 42.

Điều này có nghĩa là OrderDate có một ô trống vì chúng ta biết rằng tổng số đơn đặt hàng là 43 *.

Pivot Table có thể giúp bạn tìm thông tin không chính xác trong dữ liệu.

Phần lớn thời gian dữ liệu của chúng tôi được chuẩn bị bởi các nhà khai thác nhập dữ liệu hoặc bởi những người dùng thường không thường xuyên và cần được làm sạch để chuẩn bị báo cáo và phân tích chính xác.

Bạn phải luôn làm sạch và chuẩn bị dữ liệu của mình một cách có quy củ trước khi chuẩn bị bất kỳ báo cáo nào. Nhưng đôi khi chỉ sau khi chuẩn bị báo cáo, chúng tôi mới biết rằng dữ liệu của chúng tôi có một số bất thường. Nào, tôi sẽ chỉ cho bạn cách…

Bây giờ để trả lời câu hỏi này:

Chọn Khu vực và kéo nó vào Khu vực hàng và Mục vào Khu vực giá trị.

Chúng tôi sẽ nhận được dữ liệu được chia theo Vùng-Khôn ngoan. Chúng tôi có thể trả lời từ khu vực nào có bao nhiêu đơn đặt hàng.

Có tổng cộng 4 khu vực trong dữ liệu của chúng tôi theo bảng tổng hợp.

Nhưng chờ đã, Lưu ý rằng khu vực Trung tâm và Trung tâm. Chúng tôi biết rằng Centrle nên là Trung tâm. Có một sự bất thường. Chúng tôi cần truy cập dữ liệu của mình và làm sạch dữ liệu.

Để làm sạch dữ liệu trong trường vùng, chúng tôi lọc ra tên vùng không chính xác (Trung tâm) và sửa nó (Trung tâm).

Bây giờ hãy quay lại dữ liệu tổng hợp của bạn.

Nhấp chuột phải vào bất kỳ đâu trên bảng tổng hợp và nhấp vào Làm mới.

Báo cáo của bạn hiện đã được cập nhật.

THÔNG TIN: Bất kể bạn thực hiện thay đổi gì trong dữ liệu nguồn của mình, bảng tổng hợp sẽ tiếp tục hoạt động trên dữ liệu cũ cho đến khi bạn làm mới nó. Excel tạo bộ đệm tổng hợp và bảng tổng hợp chạy trên bộ đệm đó. Khi được làm mới, bộ đệm cũ được thay đổi bằng dữ liệu mới.

Bây giờ, bạn có thể thấy rằng trên thực tế chỉ có 3 vùng.

Đôi khi bạn sẽ cần các báo cáo như hình trên. Điều này giúp bạn dễ dàng xem dữ liệu của mình theo cách có cấu trúc. Bạn có thể dễ dàng biết có bao nhiêu mặt hàng được đặt từ khu vực nào. Hãy xem cách bạn có thể làm điều này.

Di chuyển Vùng và Mục sang vùng ROWS. Đảm bảo rằng khu vực ở trên cùng và các mục ở dưới cùng như trong hình ảnh.

Kéo Mục cho Vùng Giá trị.

Kết quả là bạn sẽ nhận được báo cáo này.

Nó sẽ làm. Nhưng đôi khi sếp của bạn muốn báo cáo dưới dạng bảng mà không có tổng phụ. Để thực hiện việc này, chúng ta cần định dạng Pivot Table của mình.

Làm theo các bước sau:

Bạn có thể thấy rằng không có tổng phụ bây giờ.

Khỏe. Nhưng nó vẫn không ở dạng bảng. Khu vực và Mục được hiển thị trong một cột duy nhất. Hiển thị chúng một cách riêng biệt.

Bây giờ để hiển thị các Khu vực và Mục trong các cột khác nhau, hãy làm theo các bước sau:

Bây giờ chúng tôi biết tổng số đơn đặt hàng cho từng mặt hàng từ mỗi khu vực. Nó được hiển thị trong cột Đếm Mục.

Vui lòng thay đổi tên cột thành Đơn hàng.

Bây giờ có vẻ tốt hơn.

Để trả lời câu hỏi này, chúng ta cần một tổng các Đơn vị. Để làm như vậy, chỉ cần di chuyển trường Đơn vị thành Giá trị. Nó sẽ tự động tổng hợp số lượng đơn vị cho mỗi mục. Nếu một cột trong Pivot Table chỉ chứa các giá trị, thì theo mặc định, Pivot Table sẽ hiển thị tổng các giá trị đó. Nhưng nó có thể được thay đổi từ cài đặt trường giá trị. Làm sao? Tôi sẽ chỉ cho bạn cái sau.

Trong dữ liệu mẫu của chúng tôi, giá của một mặt hàng là khác nhau đối với các đơn đặt hàng khác nhau. Ví dụ, hãy xem Binders.

Tôi muốn biết chi phí trung bình của mỗi mục trong Pivot Table. Để tìm hiểu điều này, hãy kéo Đơn giá vào Trường Giá trị. Nó sẽ hiển thị Tổng chi phí đơn vị.

Chúng tôi không muốn Tổng chi phí đơn vị, chúng tôi muốn Giá trung bình. Để làm như vậy …​

Cuối cùng, bạn sẽ có Báo cáo Pivot này:

Một trong những chức năng hữu ích nhất của Pivot Table là các trường được tính toán của nó. Các trường được tính toán là các trường có được bằng một số thao tác trên các cột có sẵn.

Hãy hiểu cách chèn các trường được tính toán trong Pivot Table với một ví dụ:

Dựa trên dữ liệu của chúng tôi, chúng tôi đã chuẩn bị báo cáo này.

Ở đây, chúng ta có Tổng đơn vị và Tổng chi phí. Tôi vừa chuyển cột tổng sang trường Giá trị và sau đó đổi tên nó thành Tổng chi phí. Bây giờ tôi muốn biết giá trung bình của một đơn vị mặt hàng của từng khu vực.

Và đó sẽ là:

Hãy chèn một trường vào bảng tổng hợp hiển thị giá trung bình của từng khu vực mặt hàng khôn ngoan:

Làm theo các bước sau để chèn một trường được tính toán vào Pivot Table 1. Nhấp vào bất kỳ đâu trên Pivot Table và đi tới Tab phân tích

Bạn có thể viết điều này theo cách thủ công từ bàn phím hoặc bạn có thể bấm đúp vào tên trường được liệt kê trong vùng Trường để thực hiện các thao tác.