Hướng dẫn làm phim với pivot nâng cao

Bảng tổng hợp (hay còn được gọi là Pivot Table) là một trong những công cụ xử lý dữ liệu lớn hiệu quả, đơn giản nhất được tạo ra từ Microsoft cho phép người dùng cập nhật và thay đổi dữ liệu nguồn chỉ với một vài thao tác đơn giản.

Hôm nay, Uniace Việt Nam sẽ chia sẻ đến các bạn đọc về 3 nguyên tắc cơ bản nhất định phải nắm rõ trong PivotTable.

1. Sử dụng phím tắt để tạo bảng tổng hợp PivotTable

Thông thường, để tạo ra bảng Pivot thì bạn cần bấm chọn vào 1 ô trong vùng dữ liệu, sau đó chọn Insert → chọn PivotTable. Với cách này chúng ta tốn đến 3 click.

Nếu chúng ta muốn tạo bảng tổng hợp nhanh và khi chúng ta đã quen dùng PivotTable, chúng ta có thể sử dụng phím tắt bằng cách:

• Đầu tiên, chọn 1 ô trong vùng dữ liệu sau đó nhấn phím Alt, lúc này Excel sẽ hiện các chữ viết tắt cả các thẻ trên.
  Sử dụng phím tắt tạo bảng tổng hợp trong excel 1
• Tiếp tục nhấm chữ N trên bàn phím → Xuất hiện chữ V bên dưới ô PivotTable → Nhấm V trên bàn phím để tạo PivotTable.

Sử dụng phím tắt tạo bảng tổng hợp trong excel 2

Như vậy, bạn có thể dễ dàng tạo PivotTable bằng cách nhấn lần lượt 3 phím tắt trên bàn phím: Alt → N → V

Xem thêm: 40 phím tắt cơ bản mà dân văn phòng cần phải biết

2. Thay đổi các cột/ Fields đã có trong bảng tổng hợp

Đối với PivotTable như chúng ta đã tạo ra, nếu bạn muốn thay đổi các cột/Fields đã kéo-thả ở trong PivotTable Fields chúng ta sẽ làm như thế nào?

Cách 1

Bạn có thể vào lại Fields chọn untick những cột nào mà bạn muốn gỡ ra. Ví dụ bạn muốn gỡ “Diễn giải” ra khỏi bảng tổng hợp. Bạn sẽ untick vào ô “Diễn giải” trong PivotTable Fields.

Bỏ chọn (untick) cột “Diễn giải” trong PivotTable Fields

Cách 2.

Bạn có thể kéo-thả Fields bạn muốn xóa khỏi PivotTable ra vùng trống ngoài Worksheet.

Cách 3.

Bạn có thể thực hiện gỡ cột/Fields ra khỏi PivotTable bằng cách, ví dụ bạn muốn xóa “Loại chi phí” ra khỏi PivotTable, ở trong ô Rows → Nhấm vào phím mũi tên bên cạnh “Loại chi phí” → Chọn Remove Fields.

Nhấn “Remove Field” để xóa cột trong PivotTable Fields

Xem thêm: Chia sẻ cách dùng hàm vlookup giữa 2 sheet

3.Thay đổi nguồn và cập nhật bảng tổng hợp PivotTable

Giả sử dữ liệu ví dụ trên có thay đổi, cụ thể Uniace sẽ thêm 1 cột “Số tiền (Chữ giống số)” – cột này nhìn giống như là số nhưng thực tế đây là 1 cột dữ liệu ở dạng chữ.

Cách cập nhật mới dữ liệu trong bảng tổng hợp/ Làm mới dữ liệu trong pivottable

Lúc này PivotTable ban đầu không tự động thêm cột mới, mà chỉ là 4 cột như ban đầu. Nếu mình muốn cập nhật PivotTable để nó lấy dữ liệu mới nhất sau khi mình đã điều chỉnh trên dữ liệu đó thực hiện các bước sau:

Bước 1.

Chọn vào 1 ô trong PivotTable → chọn PivotTable Tool / PivotTable Analyze → chọn Change Data Source → Hiện ra 1 hộp thoại cho phép chúng ta chọn lại vùng dữ liệu mới → Sau khi chọn lại vùng dữ liệu nhấn OK.

B1- Thay đổi dữ liệu nguồn cho pivottable

Bước 2.

Trong 1 số tình huống nhất định đôi lúc PivotTable không tự cập nhật sau khi chúng ta thực hiện Change Data Source, chọn 1 ô nào đó bất kì trong PivotTable → click chuột phải → Refresh – sẽ cập nhật lại PivotTable để đảm bảo là PivotTable mới đã được cập nhật từ dữ liệu vừa điều chỉnh.

Bạn không nên đánh giá thấp các pivot table trong Excel. Đó là bởi vì PivotTables của Excel sẽ cung cấp mọi thứ bạn cần để phân tích dữ liệu. Từ việc cô đọng các hàng dữ liệu để tính tổng và nhóm các cột tương tự, pivot table có tất cả mọi thứ.

Tuy nhiên, việc phân tích dữ liệu không hề đơn giản. Vì vậy, để tăng cường dữ liệu của mình, bạn có thể sử dụng một loạt các hàm PivotTable nâng cao trong Excel.

Nếu đã quen thuộc với PivotTable và đang muốn tiến xa hơn với một số hàm nâng cao, bạn có thể xem các hàm PivotTable nâng cao sau đây.

Chuẩn bị tập dữ liệu

Để hiểu các hàm nâng cao, trước tiên bạn phải chuẩn bị tập dữ liệu ban đầu. Để thuận tiện, hãy sử dụng những hàng và cột sau để minh họa các hàm PivotTable nâng cao.

Ảnh chụp nhanh dữ liệu trong Excel hiển thị các trường khác nhau

Bạn có thể tải xuống dữ liệu này trực tiếp từ nguồn để thực hành các hàm được liệt kê bên dưới.

• Tải Sample Superstore (Miễn phí)

Cách tạo một pivot table trong Excel

Chọn dữ liệu cho Excel PivotTable của bạn. Để chèn pivot table, hãy bấm vào tab Insert ở trên cùng, tiếp theo là tùy chọn PivotTable.

Chọn tùy chọn From Table/Range.

Hộp thoại pivot table trong MS Excel

Vì bạn đã chọn phạm vi dữ liệu trước khi chèn pivot, nên phạm vi dữ liệu được cập nhật tự động trong hộp thoại pivot. Bạn có thể chọn đích của pivot table - đặt nó trên một trang tính hiện có hoặc một trang tính mới.

Sau khi nhấp vào Ok, pivot của bạn đã sẵn sàng để xem xét. Bạn có thể thêm các dạng xem hàng/cột/giá trị cần thiết từ danh sách trường.

Ngoài ra, bạn có thể tự động tạo pivot table trong Excel bằng VBA để tiết kiệm thời gian và công sức.

4 chức năng PivotTable nâng cao để phân tích dữ liệu Excel

1. Slicer

Slicer được giới thiệu như một phần của bộ Excel 2010 và có sẵn trong các phiên bản MS Office cao hơn. tiếp tục phát hành các hàm Excel mới để giúp người dùng tận dụng tối đa những tác vụ dữ liệu thông thường của họ.

Giống như một số hàm Excel khác, thậm chí slicer còn là một cách tuyệt vời để kiểm soát các pivot của bạn thông qua bộ lọc pivot bên ngoài.

Không giống như bộ lọc pivot tích hợp, bạn có thể tùy chỉnh các bộ lọc và hiển thị nội dung mọi lúc. Người dùng thấy Slicer khá hiệu quả vì bạn có thể lọc nhiều mục và hiển thị chúng cho mọi người xem.

Thêm slicer trong PivotTable

Một trang tính Excel với các slicer và một pivot table

Để thêm slicer vào pivot table, hãy nhấp vào pivot table và điều hướng đến tab PivotTable Analyze, sau đó là tùy chọn Insert Slicer.

Khi nhấp vào nút Slicer, bạn có thể chọn một hoặc nhiều cột để sử dụng làm slicer. Tất cả các tùy chọn bạn chọn được hiển thị trong những hộp lọc riêng biệt bên cạnh pivot để thuận tiện. Các mục của slicer lọc pivot table và hiển thị bất kỳ thứ gì có liên quan.

Slicer mang lại nhiều lợi ích, đặc biệt là khi bạn muốn điều khiển nhiều pivot table đồng thời với các cửa sổ slicer (bộ lọc).

2. Calculated field

Calculated field là một cách tuyệt vời để thêm những phép tính vào pivot table của bạn, điều này rất cần thiết khi làm việc với nhiều cột số, để tính toán nhanh các giá trị mới.

Thay vì phải vật lộn với các tùy chọn PivotTable, bạn có thể tạo (các) cột mới trong pivot table của mình. Bất cứ khi nào bạn thực hiện thay đổi đối với dữ liệu vốn có, các thay đổi sẽ tự động phản ánh trong pivot table. Tuy nhiên, bạn cần phải refresh pivot table trước.

Cách tạo một calculated field trong pivot table

Giả sử bạn muốn tính tổng doanh số cho từng danh mục con. Công thức rõ ràng là lấy tích số của các trường bán hàng và số lượng.

Để thực hiện các phép tính như vậy một cách hiệu quả, bạn có thể tạo một calculated field trong pivot table của mình.

Để tạo một calculated field, hãy nhấp vào pivot table của bạn và điều hướng đến tab PivotTable Analyze. Bấm vào nút Fields, Items, & Sets.

Nút Fields, Items, & Sets trong Excel

Nhấp vào Calculated Field từ menu drop-down. Trong hộp thoại, bên trong cột Name, hãy chỉ định tiêu đề cột. Tiếp theo, từ danh sách Fields, hãy chọn tên cột bạn muốn sử dụng trong công thức.

Tìm tùy chọn Sales. Bấm vào nút Insert Field. Tùy chọn Sales được tự động thêm vào cột Formula. Tương tự, bạn có thể nhấp vào Quantity, sau đó là nút Insert Field.

Thêm biểu tượng dấu hoa thị (*) giữa hai tên trường trong thanh công thức. Bấm vào nút Add.

Hộp thoại Calculated field trong Excel Pivot

Trong pivot table, cột mới được tạo có sẵn.

Bố cục pivot table Excel hiển thị các trường pivot table

Tương tự, bạn có thể tạo nhiều calculated field trong pivot table để nâng cao khả năng tính toán của mình.

3. Cách refresh nhiều pivot table

Khi bạn xử lý dữ liệu phức tạp, có khả năng cao bạn sẽ tạo nhiều pivot table đồng thời.

Tùy thuộc vào cấu trúc dữ liệu của bạn, bạn có thể thích đặt một vài pivot trên một trang tính, trong khi nhiều pivot khác có thể nằm trên các trang tính khác trong cùng một workbook.

Có hai cách để bạn có thể làm mới nhiều pivot table trong workbook của mình. Tùy chọn đầu tiên là sử dụng Excel VBA để tự động hóa quá trình refresh.

Ngoài ra, bạn cũng có thể thực hiện việc này theo cách thủ công. Bấm vào bất kỳ pivot table nào và điều hướng đến tab PivotTable Analyze ở menu ribbon trên cùng. Nhấp vào menu drop-down của nút Refresh. Chọn Refresh All từ danh sách.

Refresh nhiều pivot table

Bạn sẽ nhận thấy tất cả các pivot table xuất hiện trong workbook ngay sau khi bạn bấm vào nút này. Mọi thay đổi mới sẽ phản ánh trong pivot table.

4. Nhóm các trường liên quan lại với nhau

Bạn thường muốn nhóm các điểm dữ liệu cụ thể để người dùng cuối có thể hiểu cấu trúc dữ liệu tốt hơn. Là một chuyên gia, bạn có thể hiểu rõ dữ liệu và cách nó liên quan đến những danh mục rộng hơn. Ngược lại, người dùng cuối thường có thể khó hiểu các nhóm này trong pivot table.

Để giúp quá trình này trở nên dễ dàng, bạn có thể sử dụng chức năng Group để nhóm các danh mục con lại với nhau và đổi tên nhóm để tạo ra những thông tin chi tiết có ý nghĩa.

Để tạo một nhóm, hãy nhấn phím Ctrl và chọn nhiều mục từ danh sách các mục trong một lần. Nhấp chuột phải trong pivot table và nhấp vào tùy chọn Group.