Mẹo và thủ thuật Pivot Table bạn cần biết

Bảng tổng hợp thật tuyệt vời! Chúng là một trong những tính năng mạnh mẽ nhất của Excel , chúng cho phép bạn nhanh chóng tóm tắt một lượng lớn dữ liệu chỉ trong vài giây. Bộ sưu tập các mẹo và thủ thuật tuyệt vời này sẽ giúp bạn thành thạo các bảng tổng hợp và trở thành một ninja dữ liệu!

Dữ liệu nguồn của bạn cần ở định dạng bảng

Khi sử dụng bảng tổng hợp, dữ liệu nguồn của bạn sẽ cần ở định dạng bảng . Điều này có nghĩa là dữ liệu của bạn nằm trong một bảng có hàng và cột.

• Hàng đầu tiên phải chứa các tiêu đề cột mô tả dữ liệu ngay bên dưới trong cột đó. Không nên có tiêu đề cột trống trong dữ liệu của bạn.
• Mỗi hàng sau tiêu đề cột phải liên quan đến chính xác một bản ghi trong dữ liệu của bạn. Ví dụ: nếu bảng của bạn chứa dữ liệu khách hàng thì mỗi hàng có thể có tên, địa chỉ đường phố, mã bưu điện và địa chỉ email của chính xác một khách hàng.

Ẩn hoặc Bỏ ẩn Cửa sổ Trường PivotTable

Bạn có thể nhận được nhiều không gian màn hình hơn bằng cách ẩn cửa sổ Trường PivotTable. Chọn một ô trong bảng tổng hợp của bạn rồi chuyển đến  tab Phân tích trong dải băng. Nhấn nút Danh sách Trường trong phần Hiển thị để bật hoặc tắt cửa sổ Trường PivotTable.

Bạn cũng có thể đóng cửa sổ bằng dấu X ở góc trên bên phải .

Bạn cũng có thể hiển thị hoặc ẩn cửa sổ Trường PivotTable bằng cách nhấp chuột phải vào bất kỳ đâu bên trong bảng tổng hợp của bạn, sau đó chọn Hiển thị Danh sách Trường hoặc Ẩn Danh sách Trường (tùy thuộc vào trạng thái hiện tại của cửa sổ Trường PivotTable của bạn).

Thay đổi Sắp xếp Mặc định của Cửa sổ Trường PivotTable

Nhấp vào bánh răng có mũi tên hướng xuống để thay đổi giao diện mặc định của cửa sổ Trường PivotTable .

Có năm tùy chọn có sẵn khác nhau mà bạn có thể chọn.

1. Phần trường và phần khu vực xếp chồng lên nhau
2. Phần trường và phần khu vực cạnh nhau
3. Chỉ Phần Trường
4. Chỉ phần Khu vực (2 nhân 2)
5. Chỉ phần Khu vực (1 x 4)

Thay đổi thứ tự sắp xếp của danh sách trường của bạn

Danh sách các trường dữ liệu sẽ hiển thị theo thứ tự như dữ liệu nguồn theo mặc định. Bạn có thể thay đổi điều này để hiển thị theo thứ tự bảng chữ cái ( A đến Z ) nếu muốn. Nhấp chuột trái vào menu tùy chọn trong cửa sổ Trường PivotTable để truy cập tùy chọn.

Chọn tùy chọn Sắp xếp từ A đến Z trong menu. Các trường của bạn bây giờ sẽ hiển thị theo thứ tự giảm dần!

Di chuyển, thay đổi kích thước và đóng cửa sổ trường PivotTable

Nhấp chuột phải vào mũi tên nhỏ hướng xuống ở bên phải tiêu đề Trường PivotTable để di chuyển , thay đổi kích thước hoặc đóng cửa sổ.

• Di chuyển – Thao tác này sẽ cho phép bạn mở khóa cửa sổ và di chuyển xung quanh bảng tính.
• Kích thước – Điều này cho phép bạn điều chỉnh chiều rộng và chiều cao (khi không được gắn) của cửa sổ.
• Đóng – Điều này cho phép bạn đóng cửa sổ. Bạn có thể mở lại từ tab Phân tích > lệnh Danh sách trường .

Phím tắt thuật sĩ PivotTable và PivotChart

Sử dụng phím tắt Alt + D + P để mở Trình hướng dẫn PivotTable và PivotChart . Điều này sẽ đưa bạn qua các bước để thiết lập bảng tổng hợp hoặc biểu đồ tổng hợp, chọn dữ liệu và vị trí cho bảng tổng hợp hoặc biểu đồ mới của bạn.

Tạo PivotTable bằng phím tắt

Sử dụng phím tắt lệnh ribbon Alt + N + V để nhanh chóng tạo bảng tổng hợp.

Hiển thị chi tiết đằng sau một giá trị

Nhấp đúp chuột phải vào một giá trị bên trong bảng tổng hợp để xem nhanh dữ liệu đằng sau giá trị tổng hợp đó. Một trang tính mới sẽ được tạo chỉ với dữ liệu liên quan đến giá trị đó.

Bạn cũng có thể truy cập tính năng này bằng cách nhấp chuột phải vào bất kỳ giá trị nào rồi chọn Hiển thị chi tiết .

Tắt Hiển thị chi tiết để tránh nhấp đúp vô tình

Nếu khả năng hiển thị dữ liệu chi tiết đằng sau kết quả bảng tổng hợp không làm bạn quan tâm thì bạn có thể tắt tính năng này. Điều này có nghĩa là bạn và có thể tránh tạo các trang tính mới có các bit dữ liệu trong đó do vô tình nhấp đúp.

Chọn bảng tổng hợp của bạn và chuyển đến tab Phân tích trong dải băng. Nhấn nút Tùy chọn trong phần PivotTable để mở menu tùy chọn.

Trong menu Tùy chọn PivotTable , hãy chuyển đến tab Dữ liệu và bỏ chọn hộp Bật chi tiết hiển thị để tắt tính năng này.

Hiển thị các mục không có dữ liệu

Trong ví dụ này, chúng tôi đã tạo một bảng tổng hợp với Tên khách hàng và Sản phẩm đã bán trong khu vực Hàng . Lưu ý rằng dưới mỗi khách hàng, không phải tất cả các sản phẩm có thể được liệt kê. Chỉ những người mà chúng tôi có một giao dịch trong dữ liệu của chúng tôi được liệt kê. Chúng tôi có thể thay đổi điều này để chúng tôi thấy tất cả các mục ngay cả khi không có dữ liệu.

Nhấp chuột phải và chọn Cài đặt trường từ menu.

Đánh dấu vào ô Show items with no data và nhấn nút OK .

Bây giờ chúng ta có thể thấy tất cả các mặt hàng có sẵn trong trường Sản phẩm đã bán ngay cả khi không có dữ liệu.

Xóa các mục khỏi bộ lọc bằng phím tắt

Đánh dấu các mục trong một hàng hoặc cột và nhấn Ctrl + – để xóa chúng khỏi bộ lọc. Bạn có thể chọn các ô không liền kề bằng cách giữ Ctrl rồi nhấp vào ô đó.

Thêm lựa chọn hiện tại vào bộ lọc

Bạn có thể sử dụng Tìm kiếm từ trong bộ lọc bảng tổng hợp để thêm các mục vào các mục đã chọn trước đó của mình. Về cơ bản, điều này giống như sử dụng điều kiện OR trong các tìm kiếm mục đã lọc của bạn.

1. Chọn nhóm mục đầu tiên của bạn để lọc theo cách thủ công hoặc sử dụng hộp tìm kiếm (với phương pháp tương tự như bước 2 & 3).
2. Sử dụng hộp Tìm kiếm để tìm kiếm và sau đó chọn nhóm mục thứ hai sẽ được lọc.
3. Chọn hộp được đánh dấu Thêm lựa chọn hiện tại vào bộ lọc sẽ xuất hiện khi sử dụng hộp tìm kiếm.
4. Nhấn nút OK.
5. Bây giờ nếu bạn xem bộ lọc , bạn sẽ thấy cả lựa chọn của mình từ bước 1 và lựa chọn từ bước 2 được bao gồm trong bộ lọc.

Sử dụng Chuyển đổi Chọn tất cả bộ lọc

Nhanh chóng chọn hoặc bỏ chọn tất cả các mục trong bộ lọc bằng cách sử dụng nút gạt Chọn tất cả bộ lọc. Điều này có thể rất tiện dụng khi xử lý một danh sách dài các mục . Bạn có thể nhanh chóng bỏ chọn tất cả rồi chọn thủ công một số ít mục hoặc chọn nhanh tất cả rồi bỏ chọn thủ công một số ít mục.

Trì hoãn cập nhật bố cục

Bạn có thể hoãn cập nhật bảng tổng hợp trong khi thực hiện các thay đổi trong cửa sổ Trường PivotTable. Điều này thường chỉ hữu ích nếu bảng của bạn được kết nối với nguồn dữ liệu rất lớn và bạn cần thực hiện nhiều thay đổi đối với bố cục. Tùy chọn này hữu ích hơn cho các kết nối với nguồn dữ liệu ngoài vì bảng tổng hợp với bất kỳ dữ liệu nào bạn có thể đưa vào Excel sẽ khá nhạy.

1. Chọn hộp Trì hoãn Cập nhật Bố cục trong cửa sổ Trường PivotTable.
2. Thực hiện các thay đổi đối với bố cục của bạn trong phần Bộ lọc , Cột , Hàng hoặc Giá trị . Bảng tổng hợp của bạn sẽ vẫn tĩnh.
3. Nhấn nút Cập nhật và bảng của bạn sẽ cập nhật để phản ánh tất cả các thay đổi đã thực hiện.

Thêm hoặc xóa các trường bằng hộp kiểm

Bạn có thể nhanh chóng thêm các trường vào bảng tổng hợp của mình bằng cách sử dụng hộp kiểm bên cạnh tên trường từ danh sách trường trong cửa sổ Trường PivotTable. Điều này có thể tiết kiệm thời gian nếu bạn có nhiều trường để thêm thay vì kéo và thả từng mục. Các trường chứa dữ liệu văn bản sẽ được thêm vào phần Hàng và các trường chứa dữ liệu số sẽ được thêm vào phần Giá trị khi sử dụng hộp kiểm.

Lọc các trường từ Cửa sổ trường PivotTable

Bạn có thể lọc các mục trong một trường từ danh sách trường trong cửa sổ Trường PivotTable. Bộ lọc sẽ chỉ áp dụng khi trường được thêm vào khu vực bộ lọc, cột hoặc hàng. Di chuột qua trường mong muốn và nhấp vào mũi tên nhỏ hướng xuống ở bên phải tên trường để mở menu bộ lọc.

Đổi tên bất kỳ nhãn nào

Bạn có thể đổi tên bất kỳ nhãn nào trong bảng tổng hợp chỉ bằng cách chọn ô và nhập vào ô đó . Bạn có thể thay đổi tên mục trong một trường, tiêu đề hàng, tiêu đề cột, nhãn bộ lọc, tổng hoặc nhãn tổng lớn. Điều kiện duy nhất là bạn không thể đổi tên nó thành tên đã tồn tại trong dữ liệu nguồn của mình và bạn không thể nhập giá trị. Điều này không thay đổi dữ liệu nguồn, nó chỉ thay đổi cách mục được gắn nhãn.

Đổi tên nhãn bằng dấu cách

Một điều bạn có thể muốn làm là thay đổi tiêu đề cột như cột “ Tổng số ” của chúng tôi xuất hiện dưới dạng “ Tổng của Tổng số ” thành chỉ hiển thị “ Tổng số ” trong bảng tổng hợp. Thật không may, điều này không thể thực hiện được vì “ Tổng số ” đã tồn tại trong dữ liệu nguồn. Nếu bạn cố gắng làm điều này, bạn sẽ nhận được cảnh báo bật lên cho biết “ Tên trường PivotTable đã tồn tại “. Chúng ta có thể giải quyết vấn đề này bằng cách thêm một ký tự khoảng trắng vào cuối tên. Tên này sẽ được tính là một tên khác nhưng về mặt trực quan, tên này sẽ giống với tên trường cũ.

Nhóm các mục lại với nhau trong một trường

Bạn có thể nhóm các mục trong một trường lại với nhau để tóm tắt thêm dữ liệu của mình. Đánh dấu các mục rồi nhấp chuột phải và chọn Nhóm từ menu. Bạn có thể chọn nhiều mục trường không liền kề bằng cách giữ phím Ctrl trong khi thực hiện lựa chọn của mình. Theo mặc định, tên được nhóm cho một tập hợp các mục sẽ là Group1 , Group2 , Group3 v.v… Nhưng bạn có thể thay đổi những tên này thành tên nào đó có ý nghĩa hơn.

Bạn cũng có thể hủy nhóm một trường đã nhóm. Chọn nó và nhấp chuột phải , sau đó chọn Ungroup từ menu.

Bạn sẽ nhận thấy một trường mới xuất hiện có cùng tên với trường được nhóm nhưng có thêm một số ở cuối. Đây là trường được nhóm mới được tạo và bạn có thể sử dụng nó giống như bất kỳ trường nào khác trong dữ liệu của mình. Bạn có thể di chuyển nó đến khu vực Bộ lọc , Hàng , Cột hoặc loại bỏ nó hoàn toàn khỏi bảng tổng hợp. Lưu ý rằng việc xóa nó khỏi bảng tổng hợp sẽ không hủy nhóm trường.

Nhóm các mục lại với nhau trong một trường bằng phím tắt

Bạn có thể nhanh chóng nhóm các mục lại với nhau trong một trường bằng cách tô sáng các mục bạn muốn nhóm, sau đó nhấn Alt + Shift + phím Mũi tên phải .

Bỏ nhóm các mục được nhóm bằng phím tắt

 

Ngày 4 Tháng Một, 2023 ( 623 )