Có bao giờ bạn gặp khó khăn trong việc thống kê các dữ liệu ở công việc không? Nếu có thì bài viết này dành cho bạn. Hôm nay Blog Phần Mềm sẽ giới thiệu bạn về cách sử dụng Pivot Table trong Excel nhanh tiện nhé!
Bạn đang xem bài viết: Cách sử dụng Pivot Table trong Excel
Nội dung bài viết
Pivot Table là gì?
Pivot Table trong Excel là một trong công cụ thống kê dữ liệu thật tự tin trọng Excel. Đặc biệt đạt kết quả tốt khi bạn cần tổng hợp và thống kê nhanh để báo cáo trên các khối dữ liệu lớn. Pivot Table trong Excel cho phép bạn đo đạt theo một hay nhiều đòi hỏi cụ thể nào đó như trích xuất dữ liệu, tạo báo cáo, thống kê…
Xem thêm: Hướng dẫn cách lọc dữ liệu trong Excel
Pivot Table trong Excel là một trong các chức năng hữu dụng và mạnh mẽ nhất của Excel sử dụng để tổng hợp, trích lọc, đo đạt dữ liệu đơn giản và nhanh chóng.
Kết quả của Pivot Table có khả năng bắt đầu được sử dụng để tính toán ra các chỉ tiêu mới nhằm phục vụ các mục tiêu khác nhau.
Hướng dẫn sử dụng hàm Pivot Table cơ bản để lập bảng thống kê
– Đầu tiên, bạn cần có bảng dữ liệu như sau:
– Bạn chọn toàn bộ dữ liệu từ B3:G15, sau đó nhấp vào biểu tượng Pivot Table như hình bên dưới:
– Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.
Bảng pivot table hiện ra. trước tiên để tính tổng số tiền bán được của mỗi nhóm sản phẩm, thì bạn làm theo các bước
– Kéo cột group SP vào vùng Row Labels
– Kéo cột Tháng vào vùng Column Labels
– Kéo cột Số Tiền vào vùng Values area
– Kéo cột Khu Vực vào vùng Report Filter
– Và đây là kết quả sau khi hoàn thành các bước trên.
Hướng dẫn sử dụng Pivot Table trong Excel nâng cao
1. Sử dụng Slicer để lọc dữ liệu với Pivottable
Slicer là một công cụ mới xuất hiện từ Excel 2010 mang đến các nút mà Bạn có thể nhấn vào để lọc dữ liệu bảng hoặc dữ liệu PivotTable. Ngoài việc lọc nhanh, slicer còn thể hiện trạng thái lọc hiện tại, giúp bạn đơn giản hiểu thông tin chuẩn xác được hiển thị trong PivotTable đã lọc.
Để có khả năng kích hoạt công dụng này, Bạn có thể làm như sau:
Bước 1: Chọn 1 PivotTable bạn mong muốn Áp dụng -> chọn thẻ Insert của thanh Ribbon -> Chọn Slicer
Bước 2: Từ đây, bạn có thể chọn 1 trường mà bạn muốn thể hiện các dữ liệu để tiến hành filter sau này. Bạn có thể chọn 1 hoặc nhiều trường cùng một lúc. Giả sử trong hình ảnh dưới đây tôi chọn trường Department và Status. Và tôi sẽ có kết quả như sau:
Bạn có thể thấy là toàn bộ giá trị của trường Department và Status sẽ hiện lên rõ nét cho bạn. Và khi bạn phải cần lọc gì, bạn chỉ cần bấm nút tương ứng thay vì chọn Filter như cách truyền thống.
2. Sử dụng Calculated Field để thêm trường tính toán trong pivot table
Calculated Field là một công dụng vô cùng hay khác của PivotTable. Nó giúp Bạn có thể thực hiện tính toán những số liệu khác phụ thuộc vào các trường mà bạn có sẵn. VD, nếu như bạn có trường Ngày công và Lương, thay vì bạn kéo thành 2 cột Ngày công và Lương trong PivotTable để rồi sau đó tính toán Bằng thao tác theo kiểu =Lương*Ngày công, bạn có thể sử dụng Calculated Field để trực tiếp tạo một field mới theo đúng bí quyết mà bạn cần.
Bạn có thể thực hiện bằng cách chọn vào PivotTable, sau đấy chọn thẻ Options trên thanh Ribbon -> chọn Fields, Items & Sets -> Calculated Field.
Một hộp thoại sẽ hiện lên và bạn gõ tên field cùng công thức tương ứng -> chọn OK.
Điểm tốt của phương thức này chính là việc bạn tạo mới được 1 field và nó sẽ cùng với bạn tạo nên những báo cáo tùy chỉnh kéo thả. Bởi vì, nếu bạn tính toán bằng tay dựa trên công thức từ bên ngoài PivotTable, khi PivotTable bạn thay đổi khi kéo thả, chắc chắn bí quyết tay này sẽ không còn đúng nữa.
Ngoài ra, một vài yếu tố khác cũng cần lưu ý như bẫy Average trong PivotTable mà bạn có thể dễ mắc phải nếu không sử dụng Calculated Field này.
3. Dùng Conditional Formatting cho PivotTable để định dạng dữ liệu theo điều kiện
Đối với chuyên mục thủ thuật excel ở chế độ tính toán thường rất nhiều bạn biết dùng Conditional Formatting để định dạng dữ liệu theo điều kiện. Một điều khá hay là mặc dù bạn đang xài PivotTable, Conditional Formatting vẫn tỏ ra dùng được như bình thường. Bạn chỉ phải vào thẻ Home trên thanh Ribbon -> chọn Conditional Formatting và thực hiện toàn bộ như bạn làm với ô, bạn sẽ có kết quả như bạn ước muốn.
Giống như ví dụ của tôi, tôi chọn Conditional Formatting là Data Bars để coi phần trăm về dữ liệu của những người có trong danh sách.
Ưu điểm chính là việc Conditional Formatting sẽ theo sát PivotTable của chúng ta. Có nghĩa là kể cả khi bạn kéo thả, Conditional Formatting sẽ chạy theo đúng với field mà bạn đã thiết lập từ trước và bạn sẽ không phải tốn công để thay đổi lại điều kiện như phải làm với ô.
Cách Tạo Pivot Table Trong Excel
1. Cách Tạo Pivot Table Theo Mẫu Có Sẵn Trong Excel Bằng Chức Năng Recommended PivotTables
Để tạo một bảng Pivot theo mẫu có sẵn trong Excel:
- Chọn một ô bất kỳ trong bảng dữ liệu.
- Trên thanh công cụ, chọn Insert >> Recommened PivotTables ở mục Table.
Cửa sổ gợi ý hiện lên các mẫu Pivot Table, bạn chỉ cần lựa chọn bảng phù hợp nhất với nhu cầu của mình:
2. Tạo Pivot Table Để Tùy Chỉnh Phân Tích Dữ Liệu
Dùng Pivot Table sẵn có theo mẫu nhanh tuy nhiên không mang lại được nhiều kiểu thông tin và trường dữ liệu như cách để tạo bảng tùy chỉnh.
Cách tạo Pivot Table Trống
- Chọn một ô bất kỳ trong bảng dữ liệu. chú ý bảng dữ liệu:
- Dữ liệu tổ chức dạng bảng, vẫn chưa có hàng/ cột trống
- Hàng trước tiên chứa tên trường dữ liệu (tháng/ khu vực/ sản phẩm…)
- Mỗi cột chỉ được chứa một kiểu dữ liệu (số/ văn bản/ ngày tháng…)
- Trên thanh công cụ, chọn Insert >> PivotTable ở mục Tables.
> PivotTable ở mục Tables” style=”height: auto; max-width: 100%; box-sizing: border-box; border: none; border-radius: 0px; box-shadow: none; vertical-align: middle; display: inline-block;”>
- Hộp thoại Create Pivot Table hiện lên: Excel mặc định đặt Pivot Table ở một trang tính mới tại mục New worksheet.
Trường hợp bạn muốn đặt Pivot Table tại một vị trí cụ thể: chọn mục Existing Worksheet >> chọn ô bắt đầu bảng Pivot trong mục Location
- Sau khi ấn OK hoặc phím Enter, bảng Pivot trống được tạo.
Xem thêm: 6 lỗi #VALUE! trong Excel và cách khắc phục
Cách Tùy Chỉnh Pivot Table Fields Để Phân Tích Dữ Liệu
Khi mà bạn click vào một ô bất kỳ trong bảng Pivot, Pivot Table Fields sẽ hiện ra ở phía bên phải giao diện Excel với hai thành phần chính là Fields và Areas:
Fields gồm các trường dữ liệu để cấu tạo nên Pivot Table. Các trường dữ liệu được chọn có thể được bôi đậm và có dấu tick ở ô vuông bên cạnh.
Để tìm nhanh một trường dữ liệu, có thể nhập tên vào ô “Search Box”.
- Areas là nơi kéo các trường dữ liệu vào các vùng sau:
- Vùng Filters: dữ liệu của trường được kéo vào vùng này sẽ được sử dụng làm bộ lọc cho cả bảng Pivot
- Vùng Rows & Columns: hai vùng quyết định các hàng và cột của bảng Pivot chứa các trường nào cho mục đích sắp xếp bảng dữ liệu
- Vùng VALUES: trường nào được kéo vào vùng này sẽ được tổng hợp bới các phép toán sum/ count/ average/ max/ min/ product…
Ví dụ Pivot Tables trong Excel
Thay Đổi Rows và Columns Cho Mục Đích Phân Tích Dữ Liệu
Trường hợp bạn cần tổng hợp doanh thu khu vực theo từng loại sản phẩm, các trường dữ liệu cần sử dụng là “Doanh thu”, “Khu vực”, “Sản phẩm”.
Kế đến, hãy hình dung về vị trí của các trường dữ liệu. VD, bạn muốn các khu vực sẽ được đặt ở đầu mỗi hàng, những loại sản phẩm có thể được đặt ở đầu mỗi cột.
Cuối cùng, bạn chỉ cần kéo các trường vào các vùng như sau:
- Values: kéo cột Doanh thu vào và chọn phép toán SUM
- Rows: chọn Khu Vực
- Columns: chọn Sản phẩm.
Bạn có thể đổi vị trị Rows và Columns, khi đó bảng Pivot chỉ đơn giản là chuyển đổi hàng sang cột và ngược lại.
Sử Dụng Filters Trong Pivot Table
Các ví dụ trên đều xử lý các bài toán tổng hợp, đo đạt toàn bộ 1 cột. Trường hợp cần phân tích một giá trị trong một cột thì sao?
Vùng Filter sẽ xử lý giúp bạn câu hỏi này.
Để tổng hợp doanh thu khu vực hàng tháng theo một loại hàng hóa cụ thể, bạn đặt trường sản phẩm vào Filter để lọc khi cần phân tích một sản phẩm trong trường này:
- Filters: chọn hàng hóa
- Values: chọn Doanh thu và chọn phép toán SUM
- Rows: chọn Khu Vực
- Columns: chọn Tháng.
Tương tự, để giải quyết câu hỏi ở đầu mục 3, bạn muốn được biết doanh thu kinh doanh khu vực miền Bắc theo hàng hóa từng tháng: một khi nắm rõ ràng trường dữ liệu là “Khu vực”, “Sản phẩm”, “Tháng” và “Doanh thu”, kéo/ chọn vùng như sau:
- Filters: chọn Khu vực
- Values: chọn Doanh thu và chọn phép toán SUM
- Rows: chọn Sản phẩm
- Columns: chọn Tháng.
Filter Khu vực hiện ra ở đầu bảng Pivot, chọn mũi tên thả xuống, danh sách các giá trị trong cột Khu vực hiện ra: Miền Bắc, Miền Nam, Miền Trung.
Excel mặc định chỉ chọn 1 giá trị. Để chọn từ 2 giá trị trở lên ở Filter, tick Select Multiple Items.
Chọn Miền Bắc, ấn OK, dữ liệu cần tổng hợp và phân tích được thể hiện như sau:
Tổng kết
Qua bài viết này, hy vọng đã giải đáp thắc mắc về Pivot Table trong Excel. Tiếp tục theo dõi blog phần mềm để cập nhật thêm nhiều thông tin hữu ích nhé. Cảm ơn các bạn đã theo dõi bài viết của blogphanmem.vn
