PIVOT TABLE trong excel từ Cơ bản – Nâng cao (Chuyên gia Excel)

Pivot table là gì? 30 cách sử dụng Pivot Table trong excel từ cơ bản tới nâng cao. Có sẵn bài tập để bạn tải về và thực hành theo. Nếu bạn thấy hữu ích đừng tiếc 1 like để Trường có thêm động lực nhé. Ngoài ra, các bạn có thể học Excel từ cơ bản tới nâng cao thông qua chương trình đào tạo Miễn phí tại Webkynang Việt Nam. +70 mẫu file excel đẳng cấp: Tải miễn phí tại đây List 30 thủ thuật dùng với Pivot table Lưu ý: Trường sẽ hoàn thiện dần từng phần trong danh sách 30 phần này, mỗi tuần một vài chủ đề. PV là gì Cách tạo Pivot đơn giản Chỉnh sửa PV Dùng phím tắt để tạo PV Ẩn/ Hiện fields list Đổi tên cột trong bảng Pivot Làm mới dữ liệu Dùng phím tắt để làm mới dữ liệu Cách làm mới tất cả dữ liệu bằng phím tắt Cách tạo tính năng làm mới dữ liệu của pivot table mỗi khi file được mở lên Thêm dòng/ cột trong vùng dữ liệu nguồn thì làm thế nào để bảng Pivot cập nhật dữ liệu Sắp xếp dữ liệu ở dạng Tabular view Dùng bảng dữ liệu để tạo Pivot Calculated fields: Ý nghĩa và cách tạo Calculated fields: Cách xóa Định dạng số Đếm các giá trị duy nhất Bật/ Tắt tổng phụ (Sub-total, Grand-total) Thêm data bar Thêm Icon sets Cách Thêm color scales Tạo Pivot chart Tạo slicer Cách Tạo timeline Cách ẩn các nút bấm trong pivot chart Chèn dòng trống sau mỗi mục tự động như thế nào? Lặp tiêu đề mỗi nhóm dữ liệu Pivot table tự động co giãn độ rộng cột: Làm thế nào để chặn việc này Tạo % của mỗi mục trên số tổng Tạo % trên số tổng phụ 1. Pivot table là gì? Pivot table là một công cụ có sẵn trong office giúp chúng ta dễ dàng tổng hợp và truy xuất dữ liệu theo 1 vài chỉ tiêu từ một bảng dữ liệu KHỦNG. KHỦNG là có thể lên tới cả triệu dòng đó bạn ạ. Chỉ dùng hàm excel thì việc xử lý hàng chục nghìn dòng đã là nhiệm vụ rất khó khăn, nói chi tới triệu dòng. PivotTable (PV) trong Excel 2010, 2013, 2016 và các phiên bản sau, còn giúp các bạn tạo ra những báo cáo động rất linh hoạt và hữu ích. Thật khó để giải thích cho bạn tính VI DIỆU của PV, tốt nhất là chúng nên đi vào thực tế. Ở các phần sau, Trường sẽ thực hành làm một vài tác vụ để bạn thấy được tác dụng của PV. 2. Cách tạo Pivot table Cách tạo Pivot table ở Excel 2003 hoàn toàn khác so với các phiên bản sau gồm: Excel 2007, 2010, 2013, 2017 Các bản từ 2007 có cách tạo tương tự nhau do sự khác biệt ở thanh Menu và Ribbon Trường sẽ hướng dẫn bạn cách tạo trên Excel 2010 nhé. B1: Bôi đen chọn vùng dữ liệu dùng để tạo Pivot Table – Ta còn gọi là Data source B2 + 3: Mở thẻ Insert trên thanh ribbon rồi chọn PivotTable ngay phía bên trái. B4 + 5 + 6: Chọn lại vùng dữ liệu để tạo pivot nếu cần tiếp theo ta chọn nơi lưu Pivot. Có 2 phương án là: + Lưu Pivot table trực tiếp trên sheet hiện hành (Existing worksheet) + New worksheet: Lưu trên 1 sheet hoàn toàn mới Lưu ý rằng, dù lưu ở đâu đi nữa thì bạn cũng nên chọn ô cần lưu của sheet bạn cần. Mặc định là ô A1 nhé các bạn. Cuối cùng bạn nhấn OK để hoàn tất B6: Kéo thả các cột (fields) thuộc vùng dữ liệu nguồn (data source) xuống các phần điều khiển (7 -> 8) Kết quả của việc kéo thả đó được thể hiện trực tiếp tại 9. Và đây là thành quả của Trường Các bạn thấy hình dưới không ạ. Chỉ trong nháy mắt bạn đã có thể tổng hợp được chi phí của tất cả các Sub-Divison và chi tiết cho từng Group segment, từng tháng. 3. Chỉnh sửa trong Pivot table như thế nào Để thực hiện việc chỉnh sửa PV bạn làm như sau: B1: Click vào một ô bất kỳ trong vùng bảng PV (kết quả) B2: Click chuột phải hoặc chọn thẻ Option/ Design trên thanh ribbon để chỉnh sửa điều bạn cần. Lưu ý rằng: Thường thì thao tác chỉnh sửa chủ yếu là: Change Data source: Mở rộng hoặc thu hẹp vùng dữ liệu của Pivot table Refresh: Làm mới dữ liệu cho bảng Pivot khi dữ liệu tại Data source thay đổi Còn các phần chỉnh sửa khác các bạn xem ở các phần dưới đây. 4. Pivot table nâng cao trong excel Dưới đây là các kỹ thuật nâng cao khi sử dụng Pivot mà bạn nên biết. Nếu bạn không tìm thấy trong thẻ Options Pivot của bạn thì do phiên bản office của bạn chưa hỗ trợ, bạn cần cài lên bản cao hơn nhé. Slicers (Hướng dẫn cách tạo Slicer trong Excel) Đây là một trong những tính năng tuyệt với của PV giúp chúng ta tạo biểu đồ, báo cáo động một cách dễ dàng. Đơn cử như báo cáo sau được lấy từ chương trình Đào tạo Excel nâng cao do Webkynang tổ chức tại các doanh nghiệp. Khi bạn chọn 1 hoặc 1 vài nhân viên Sale (Sale person), các biểu đồ bên trái sẽ tự động đổi số liệu theo. Từ đó bạn đánh giá được tổng quan tình hình bán hàng của nhân viên đó theo vùng, theo số khách hàng, lượng giao dịch. Tabular view Thông thường khi tạo Pivot table, Excel sẽ chọn loại hiển thị Compact. Nhưng khi đó việc lập các công thức hoặc xử lý dữ liệu có dữ liệu nguồn là bảng Pivot vừa tạo sẽ gặp khá nhiều khó khăn. Để thuận lợi cho việc tính toán và tìm kiếm, ta nên chuyển sang dạng Tabular form. Bạn tham khảo hình ảnh so sánh dạng hiển thị dữ liệu giữa 2 form dưới đây. Để chuyển đổi định dạng hiển thị, ta chỉ cần thực hiện đơn giản như sau: + B1: Chọn bảng Pivot cần chỉnh + B2: Chọn thẻ Design trên thanh ribbon + B3: Chọn Report Layout, sau đó chọn Show in Tabular form Các bạn tham khảo hình dưới đây để xem cách chọn nhé. Tạo Pivot table bằng phím tắt Ẩn hiện Pivot table fields list Bạn xem trong hình sau, mục số 2 chính là Pivot table fields list. Tại đây các bạn có thể Kéo thả các cột, dòng từ dữ liệu nguồn vào bảng Pivot. Nhiều khi các bạn không biết thì Phần PivotTable fields này bị ẩn đi nên không biết cách thêm fields vào pivot như thế nào. Nguyên nhân: PivotTable fields bị ẩn Tương đương với mục số (1) hình dưới đây không sáng. Để hiện lên mục số (2), bạn chỉ cần nhấn nút Field List cho sáng lên là được bạn nhé.