Bài viết này, chúng tôi sẽ hướng dẫn bạn chi tiết từng bước một để tạo ra một pivot table cấp cao trong Excel. Lưu ý rằng những chỉ dẫn trong bài viết chỉ áp dụng được với phiên bản Excel hiện hành [2007 và lớn hơn].
Ở ví dụ bên dưới có sử dụng bảng tính, trong đó bao gồm doanh số bán hàng của một công ty trong quý I năm 2016. Bảng tính ghi lại ngày bán hàng [date], hóa đơn tương ứng [invoice ref], tổng hóa đơn, tên người bán hàng [sales rep.], và khu vực bán hàng [region].
Tham khảo thêm: Hướng dẫn sử dụng Excel 2007 toàn tập
Trong ví dụ là một pivot table biểu diễn tổng doanh thu từng tháng trong năm, được chia nhỏ theo khu vực và người bán hàng. Quá trình tạo ra pivot table này được thực hiện theo các bước như sau:
- Chọn một ô bất kì hoặc chọn toàn bộ dải dữ liệu trong pivot table. Lưu ý: Nếu bạn chỉ chọn một ô duy nhất, Excek sẽ tự động xác định và chọn cả dải dữ liệu cho pivot table của bạn.
- Nhấn chuột vào nút Pivot Table trong nhãn ‘Insert‘ trên thanh công cụ.
3. Hộp thoại ‘Create PivotTable‘ xuất hiện. Hãy chắc chắn rằng dải dữ liệu bạn đã chọn sẽ tương ứng với dải ô mà bạn muốn sử dụng cho pivot table của bạn. Cũng sẽ có một tùy chọn yêu cầu nơi bạn muốn đặt pivot table, điều này cho phép bạn pivot table trong một bảng tính cụ thể. Nếu không chắc chắn hãy chọn ‘New worksheet‘ [bảng tính mới]. Cuối cùng chọn ‘Ok‘.
4. Một Pivot Table trống sẽ xuất hiện cùng với bảng tác vụ ‘Pivot Table Field List‘ chứa các trường dữ liệu, lưu ý đây là những tiêu đề từ bảng dữ liệu ban đầu của bạn. Từ bảng tác vụ ‘Pivot Table Field List‘, thực hiện:
- Kéo ‘Date‘ [ngày] vào vùng ‘Row Labels‘ [hoặc ‘Rows’] [Dãy nhãn hoặc dãy]
- Kéo ‘Amount‘ [số lượng] vào vùng ‘Σ Values‘ [giá trị]
- Kéo ‘Region‘ vào vùng ‘Column Labels‘ [or ‘Columns’] [Cột nhãn hoặc cột]
- Kéo ‘Sales Rep.‘ vào vùng ‘Column Labels‘ [or ‘Columns’]
5. Kết quả sẽ được tính bằng tổng doanh thu hàng ngày tại mỗi khu vực bán hàng và mỗi người bán hàng như hình dưới:
Tuy nhiên, để nhóm ngày lại thành tháng cần thực hiện:
- Nhấn chuột phải vào bất kì ngày nào trong cột bên trái trong pivot table.
- Chọn ‘Group…‘ [Nhóm…] cho ngày sẽ xuất hiện.
- Hộp thoại ‘Grouping‘ cho các ngày sẽ xuất hiện. Chọn ‘Months‘ [Tháng] [lưu ý rằng bạn cũng có thể nhóm các ngày và giờ theo các khoảng thời gian khác như quý,…
- Chọn ‘Ok‘.
Như yêu cầu, pivot table cuối cùng sẽ biểu diễn tổng doanh thu theo tháng, chia nhỏ theo khu vực và người bán hàng:
Nếu bạn muốn hoàn thiện pivot table, bạn có thể định dạng cột B-G theo đơn vị tiền tệ. Cách dễ nhất để định dạng một dải ô về đơn vị tiền tệ là chọn dải ô muốn định dạng, sau đó nhấn chuột vào nút định dạng tiền tệ nằm ở nhóm ‘Number‘ [số] trong nhãn ‘Home‘ trên thanh công cụ.
Bộ lọc báo cáo Pivot Table
Bộ lọc báo cáo Pivot Table cho phép bạn quan sát dữ liệu của từng giá trị, từng vùng chọn của những giá trị xác định trong vùng dữ liệu.
Ví dụ, trong pivot table phía trên, bạn chỉ có thể quan sát dữ liệu doanh thu của khu vực phía Bắc hoặc phía Nam. Để quan sát được dữ liệu doanh thu vùng phía Bắc [North], quay trở lại bảng tác vụ ‘Pivot Table Field List‘ và kéo mục ‘Region‘ vào vùng ‘Report Filter‘ [hoặc ‘Filters’] [Bộ lọc báo cáo hoặc bộ lọc].
Bạn sẽ thấy ‘Region‘ xuất hiện ở đầu pivot table. Sử dụng drop-down list [danh sách xổ xuống] của vùng này để chọn ‘North‘. Kết quả pivot table sẽ chỉ biểu diễn doanh thu của vùng phía Bắc:
Tìm hiểu ngay: lớp học Excel kế toán tại Hà Nội
Để có thể xử lý hoặc thống kê hàng triệu dữ liệu trong Microsoft Excel không phải là việc dễ dàng gì, do đó Pivot Table ra đời để hỗ trợ người dùng phân tích, thống kê dữ liệu một cách nhanh nhất và chính xác nhất. Bài viết này Canh Rau sẽ giới thiệu cho các bạn Pivot Table là gì, và cách sử dụng Pivot Table trong Excel một cách dễ dàng nhất.
Pivot Table là gì ?
Pivot Table trong Excel là công cụ hỗ trợ thống kê dữ liệu bậc nhất trong Excel, với Pivot Table chúng ta có thể thu gọn dữ liệu tùy theo mục đích đề ra như tạo báo cáo, thống kê từ hàng ngàn, hàng tỷ dữ liệu Excel có sẵn. Pivot Table hỗ trợ Excel 2007, 2010, 2013, 2016, 2019…
Bạn có thể tưởng tượng Pivot Table như một bản báo cáo. Tuy nhiên, không giống như một báo cáo tĩnh. Pivot Table cung cấp chế độ xem tương tác với dữ liệu của bạn và bạn không cần thao tác quá nhiều lần để xem các dữ liệu này. Bạn có thể nhóm dữ liệu của mình thành các danh mục, lọc dữ liệu để bao gồm hoặc loại trừ các danh mục đó, kể cả việc vẽ biểu đồ.
Mục đích chính của Pivot Table là có thể giúp bạn có thể tương tác dữ liệu của mình theo nhiều cách khác nhau.
Hướng dẫn cách sử dụng Pivot Table trong Excel
Bước 1: Để có thể sử dụng Pivot Table, chúng ta lựa chọn bảng dữ liệu mà mình đang cần thống kê, tiếp theo chọn Insert -> Pivot Table.
Bước 2: Hộp thoại Pivot Table xuất hiện. Bạn kiểm tra vị trí dữ liệu đã chính xác trong mục Select a table of range chưa. Sau đó tại phía dưới chúng ta sẽ chọn New Worksheet để tạo Pivot Table trong một Sheet mới.
Bước 3: Tiếp theo, bạn cần phải thêm các giá trị vào các bảng Filters, Columns, Rows, Values.
Filters: Lọc những giá trị có trong nguồn dữ liệu đưa vào để hiển thị thống kê. Ví dụ, bạn chọn Tên Sản Phẩm.
Columns: Cột hiển thị dữ liệu đưa vào. Ví dụ chọn Tháng.
Rows: Dòng hiển thị dữ liệu đưa vào. Ví dụ chọn Nhà sản xuất.
Values: Cách thức tính dữ liệu đưa vào [SUM, AVERAGE, MAX, MIN, COUNT …]. Ví dụ chọn Thành tiền.
Bước 4: Sau khi đã thêm toàn bộ dữ liệu vào thì chúng ta có một bảng dữ liệu hoàn chỉnh như sau:
Ngoài ra bạn có thể thống kê dữ liệu theo tháng, bằng cách nhấn vào biểu tượng Tam Giác Ngược của Column Labels sau đó lựa chọn tháng mà muốn thực hiện thống kê. Cuối cùng chọn OK.
Tương tự như vậy, chúng ta có thể lựa chọn thống kê theo Nhà Sản Xuất.
Với Filter, các bạn sẽ chọn được Tên Sản Phẩm muốn thống kê.
Cách dùng Pivot Table trong Excel để phân tích dữ liệu nâng cao
Với cơ sở dữ liệu như trên, thì mình sẽ hướng dẫn các bạn cách sử dụng Pivot Table nâng cao để thống kê tốt và chi tiết hơn.
Ví dụ: Tính trung bình thành tiền tất cả các tháng của mỗi nhà sản xuất.
Mặc định thì Pivot Table sẽ tính tổng giá trị mà chúng ta đưa dữ liệu vào, do đó để có thể thay đổi cách tính khác thì các bạn làm như sau. Chuột trái vào giá trị trong cột Values, chọn Value Field Settings.
Trong hộp thoại này, chúng ta sẽ lựa chọn cách tính là Average sau đó nhấn OK.
Đây là kết quả đạt được.
Ví dụ: Lọc ra Thành Tiền cao nhất trong tháng 6 của tất cả Nhà Sản Xuất.
Đầu tiên, để lọc ra Thành Tiền cao nhất thì chúng ta sẽ chọn Thành Tiền trong cột Values.
Sau đó chọn Max để lọc ra giá trị cao nhất trong cột Thành Tiền, chọn OK.
Tiếp theo đó, bạn chọn dấu tam giác ngược bên cạnh Column Labels, tắt đánh dấu Tháng 7 Tháng 8.
Cuối cùng, bạn cũng chọn biểu tượng Tam giác ngược phía bên phải Row Labels. Tiếp theo chọn [Select All] để chọn tất cả Nhà Sản Xuất.
Đây là kết quả của chúng ta. Lý do chỉ có 2 nhà sản xuất Galax, Asus là vì các nhà sản xuất khác họ chỉ bán được trong tháng 7 và tháng 8 và không bán trong tháng 6.
Qua 2 ví dụ trên Canhrau.com đã hướng dẫn các bạn cách sử dụng Pivot Table từ cơ bản đến nâng cao để thống kê dữ liệu trong Excel.
Có thể bạn quan tâm: