Tạo PivotTable để phân tích dữ liệu trang tính
PivotTable là một công cụ vô cùng mạnh mẽ, giúp bạn tính toán, tóm tắt và phân tích dữ liệu một cách thông minh. Nó cho phép bạn so sánh, xem mẫu hình và nhận biết xu hướng trong dữ liệu của mình. Tuy nhiên, cách hoạt động của PivotTable có thể khác nhau tùy vào nền tảng Excel mà bạn đang sử dụng.
Lựa chọn các ô mà bạn muốn sử dụng để tạo Bảng tổng hợp.
Lưu ý: Dữ liệu của bạn sẽ được sắp xếp theo các cột với một hàng tiêu đề duy nhất. Hãy xem phần Mẹo và thủ thuật định dạng dữ liệu để biết thêm chi tiết.
Chọn Chèn > PivotTable.
Thao tác này sẽ tạo PivotTable dựa trên bảng hoặc dải ô hiện có.
Lưu ý: Việc chọn Thêm dữ liệu này vào Mô hình Dữ liệu sẽ thêm bảng hoặc phạm vi đang được dùng cho PivotTable này vào Mô hình Dữ liệu của sổ làm việc. Tìm hiểu thêm.
Hãy lựa chọn vị trí để đặt báo cáo PivotTable. Bạn có thể chọn Trang tính Mới để đặt PivotTable vào một trang tính mới hoặc chọn Trang tính Hiện có và xác định vị trí mà bạn muốn PivotTable mới xuất hiện.
Nhấn vào OK.
Bằng cách nhấp vào mũi tên xuống trên nút đó, bạn có thể chọn từ các nguồn khác cho PivotTable của mình. Ngoài việc sử dụng bảng hoặc dải ô hiện có, bạn còn có ba nguồn khác mà bạn có thể chọn để nhập PivotTable của mình.
Lưu ý: Tuỳ thuộc vào cấu hình công nghệ thông tin trong tổ chức của bạn, bạn có thể thấy tên tổ chức của mình xuất hiện trong nút. Ví dụ: “Từ Power BI (Microsoft)”.
Lấy từ nguồn thông tin bên ngoài.
Sử dụng thuật toán Mô hình Dữ liệu.
Dùng tùy chọn này nếu sổ làm việc của bạn chứa Mô hình Dữ liệu và bạn muốn tạo PivotTable từ nhiều Bảng, hãy cải thiện PivotTable bằng các số đo tùy chỉnh hoặc đang làm việc với các tập dữ liệu rất lớn.
Sử dụng từ Power BI.
Nếu tổ chức của bạn đang sử dụng Power BI và muốn khám phá và kết nối với bộ dữ liệu đám mây được chứng thực mà bạn có quyền truy cập, hãy sử dụng tùy chọn này.
Để thêm trường vào PivotTable của bạn, hãy chọn hộp kiểm tên trường trong ngăn Trường PivotTable .
Lưu ý: Các trường được chọn sẽ được thêm vào vùng mặc định của chúng: các trường không phải là số được thêm vào Hàng, các phân cấp ngày và thời gian sẽ được thêm vào Cột và các trường số sẽ được thêm vào Giá trị.
Để chuyển trường từ vùng này sang vùng khác, hãy kéo trường đến vùng đích.
Khi thêm dữ liệu mới vào nguồn dữ liệu PivotTable, tất cả các PivotTable xây dựng trên nguồn dữ liệu đó sẽ cần được làm mới. Để làm mới một PivotTable, bạn có thể chuột phải vào bất kỳ đâu trong dải ô PivotTable và chọn Làm mới. Nếu bạn có nhiều PivotTable, hãy chọn một ô bất kỳ trong PivotTable và trên Dải băng, điều hướng đến Phân tích PivotTable, sau đó nhấn vào mũi tên bên dưới nút Làm mới và chọn Làm mới tất cả.
Tóm tắt ý nghĩa của Theo.
Mặc định, các trường PivotTable được đặt trong khu vực Giá trị sẽ được hiển thị dưới dạng TỔNG. Trường hợp dữ liệu của bạn là văn bản, Excel sẽ hiển thị dữ liệu dưới dạng SỐ LƯỢNG. Điều này là quan trọng để đảm bảo không kết hợp các kiểu dữ liệu cho các trường giá trị. Để thay đổi tính toán mặc định, bạn có thể bấm vào mũi tên bên phải tên trường, sau đó chọn tùy chọn Thiết đặt Trường Giá trị.
Tiếp theo, bạn có thể thay đổi cách tính toán trong mục Tóm tắt Giá trị Theo. Hãy lưu ý rằng khi bạn thay đổi phương pháp tính toán, Excel sẽ tự động áp dụng phương pháp đó vào mục Tên Tùy chỉnh, ví dụ như “Tổng Tên Trường”, nhưng bạn cũng có thể thay đổi mục đó. Nếu bạn nhấp vào nút Định dạng Số, bạn cũng có thể thay đổi định dạng số cho toàn bộ trường.
Mẹo: Để tránh việc thay đổi tên các trường PivotTable trong mục Tóm tắt Giá trị Theo, hãy hoàn thành việc thiết lập PivotTable trước khi thay đổi tính toán. Bạn có thể sử dụng Tìm & Thay thế (Ctrl+H) để thay thế tất cả các trường “Tổng của” bằng không thay vì nhập lại từng trường một.
Hiển thị Giá trị Dưới hình thức.
Thay vì sử dụng một phép tính để tóm tắt dữ liệu, bạn cũng có thể thể hiện dữ liệu dưới dạng phần trăm của trường. Trong ví dụ sau đây, chúng tôi đã thay đổi số chi phí gia đình để hiển thị dưới dạng phần trăm của tổng cuối cùng thay vì tổng các giá trị.
Sau khi mở hộp thoại Thiết đặt Trường Giá trị, bạn có thể tùy chọn tab Hiển thị Giá trị Dưới dạng để đưa ra lựa chọn của mình.
Hiển thị một giá trị cùng với phép toán và Tỷ lệ phần trăm.
Hãy kéo mục vào mục Giá trị hai lần, sau đó đặt các tùy chọn Tóm tắt Giá trị Theo và Hiển thị Giá trị Dưới dạng cho từng mục.
Bạn có thể chọn một bảng hoặc phạm vi dữ liệu trong trang tính của mình và sau đó chọn Chèn > PivotTable để mở ngăn Chèn PivotTable.
Bạn có thể tự tạo PivotTable theo cách thủ công hoặc lựa chọn một PivotTable được đề xuất để tạo sẵn. Dưới đây là các bước để thực hiện:
Trên thẻ Tạo PivotTable của bạn, hãy lựa chọn Trang tính mới hoặc Trang tính hiện có để lựa chọn đích của PivotTable.
Trên PivotTable được đề xuất, hãy lựa chọn Trang tính mới hoặc Trang tính hiện tại để chọn đích của PivotTable.
Lưu ý: PivotTable được đề xuất chỉ sẵn có cho người dùng đăng ký Microsoft 365.
Bạn có thể thay đổi nguồn thông tin cho bảng dữ liệu PivotTable khi tạo dữ liệu.
Trên ngăn Chèn PivotTable, hãy chọn hộp văn bản dưới phần Nguồn. Khi bạn thay đổi Nguồn, các thẻ trong ngăn sẽ không được sử dụng.
Thực hiện chọn dữ liệu trên mạng hoặc nhập một phạm vi vào ô văn bản.
Hãy bấm Enter trên bàn phím hoặc nút để xác nhận lựa chọn của bạn. Ngăn này sẽ cập nhật các PivotTable được đề xuất mới dựa trên nguồn dữ liệu mới.
Sử dụng từ Power BI.
Nếu tổ chức của bạn đang sử dụng Power BI và muốn khám phá và kết nối với bộ dữ liệu đám mây được chứng thực mà bạn có quyền truy cập, hãy sử dụng tùy chọn này.
Trong ngăn Trường PivotTable, chọn hộp kiểm cho trường mong muốn để thêm vào PivotTable của bạn.
Theo quy định, các trường không phải là số sẽ được thêm vào phần Hàng, các trường ngày và giờ sẽ được thêm vào phần Cột và các trường số sẽ được thêm vào phần Giá trị.
Bạn có thể tự do kéo và thả bất kỳ mục nào vào bảng PivotTable hoặc xóa mục đó nếu bạn không muốn sử dụng nó.
Tóm tắt ý nghĩa của Theo.
Theo mặc định, trong khu vực Giá trị của trường PivotTable sẽ được hiển thị như là SUM. Trong trường hợp dữ liệu của bạn được hiểu là văn bản, thì dữ liệu sẽ được hiển thị như là SỐ LƯỢNG. Điều này là lí do quan trọng vì bạn cần đảm bảo không kết hợp các kiểu dữ liệu khác nhau cho các trường giá trị.
Bằng cách nhấp chuột phải vào bất kỳ giá trị nào trong hàng, bạn có thể thay đổi tính toán mặc định bằng cách chọn tùy chọn “Tóm tắt Giá trị Theo”.
Hiển thị Giá trị Dưới hình thức.
Thay vì sử dụng một phép tính để tóm tắt dữ liệu, bạn cũng có thể thể hiện dữ liệu dưới dạng phần trăm của trường. Trong ví dụ sau đây, chúng tôi đã thay đổi số chi phí gia đình để hiển thị dưới dạng phần trăm của tổng cuối cùng thay vì tổng các giá trị.
Bấm chuột phải vào một giá trị trong cột để hiển thị giá trị. Chọn tùy chọn “Hiển thị Giá trị Như” từ menu. Danh sách các giá trị sẵn có sẽ xuất hiện.
Lựa chọn của bạn từ danh sách được thực hiện.
Để hiển thị phần trăm của Tổng Cha mẹ, hãy di chuột qua mục tương ứng trong danh sách và lựa chọn trường mẹ bạn muốn sử dụng để tính toán.
Khi bạn thêm dữ liệu mới vào nguồn dữ liệu PivotTable của mình, bạn cần phải làm mới tất cả các PivotTable được xây dựng trên nguồn dữ liệu đó. Để làm điều này, bạn chỉ cần bấm chuột phải vào bất kỳ vị trí nào trong dải ô PivotTable và chọn Làm mới.
Nếu bạn đã tạo một PivotTable và quyết định không sử dụng nó nữa, bạn có thể chọn toàn bộ dải ô PivotTable và nhấn Delete. Điều này sẽ không ảnh hưởng đến các dữ liệu khác hoặc PivotTable hay biểu đồ xung quanh. Nếu PivotTable của bạn nằm trên một trang tính riêng và bạn không muốn giữ lại dữ liệu khác trên trang tính đó, bạn có thể nhanh chóng xóa trang tính để loại bỏ PivotTable.
Dữ liệu của bạn nên được sắp xếp theo định dạng bảng và không có hàng hoặc cột trống. Tốt nhất là sử dụng Bảng Excel như ví dụ trên.
Bảng là một công cụ tuyệt vời để tạo PivotTable vì khi bạn làm mới dữ liệu, các hàng sẽ tự động được thêm vào PivotTable và các cột mới cũng sẽ được thêm vào Danh sách Trường PivotTable. Nếu không, bạn có thể thay đổi nguồn dữ liệu của PivotTable hoặc sử dụng công thức phạm vi có tên động.
Cột nên có cùng loại dữ liệu. Không nên kết hợp ngày tháng và văn bản trong cùng một cột.
PivotTable hoạt động ngay lập tức với dữ liệu của bạn, được gọi là bộ đệm ẩn nên không làm thay đổi dữ liệu thực tế của bạn theo bất kỳ cách nào.
Nếu bạn đã có kinh nghiệm hạn chế với PivotTable hoặc không chắc chắn làm thế nào để bắt đầu, bạn có thể xem PivotTable như một lựa chọn tốt. Khi sử dụng tính năng này, Excel sẽ tự động tìm ra cấu trúc ý nghĩa bằng cách so khớp dữ liệu với các khu vực phù hợp nhất trong PivotTable. Điều này sẽ giúp bạn có một điểm khởi đầu để thử nghiệm thêm. Sau khi tạo PivotTable, bạn có thể khám phá các hướng khác nhau và sắp xếp lại các trường để đạt được kết quả cụ thể của bạn. Bạn cũng có thể tải xuống hướng dẫn Tạo PivotTable đầu tiên mang tính tương tác của chúng tôi.
Nhấp vào một ô trong chuỗi ô của bảng hoặc nguồn dữ liệu.
Đi tới Chèn > PivotTable được đề xuất.
PivotTable được Đề xuất để Excel tạo một PivotTable cho bạn”>
Excel sẽ phân tích dữ liệu của bạn và hiển thị cho bạn một số tùy chọn, chẳng hạn như trong ví dụ sử dụng dữ liệu chi phí hộ gia đình này.
Hãy chọn PivotTable phù hợp và bấm OK. Excel sẽ tạo một PivotTable mới trên một trang tính và hiển thị danh sách các trường trong PivotTable.
Nhấp vào một ô trong chuỗi ô của bảng hoặc nguồn dữ liệu.
Đi tới Chèn > PivotTable.
Excel sẽ hiển thị hộp thoại Tạo PivotTable với tên dải ô hoặc bảng được lựa chọn. Trong trường hợp này, chúng tôi sử dụng bảng có tên “tbl_HouseholdExpenses”.
Mục Chọn vị trí cho báo cáo PivotTable cho phép bạn lựa chọn Trang tính mới hoặc Trang tính hiện có. Đối với Trang tính hiện có, hãy chọn ô mà bạn muốn đặt PivotTable vào.
Bấm vào OK và Excel sẽ tạo một PivotTable trống, đồng thời hiển thị danh sách Trường PivotTable.
Danh sách Trường Bảng Tổng hợp.
Trong vùng Tên Trường ở trên cùng, hãy chọn hộp kiểm tương ứng với trường bất kỳ mà bạn muốn thêm vào PivotTable. Theo mặc định, các trường không phải là số sẽ được thêm vào vùng Hàng, các trường ngày và giờ sẽ được thêm vào vùng Cột và các trường số sẽ được thêm vào vùng Giá trị. Bạn cũng có thể kéo và thả thủ công bất cứ mục có sẵn nào vào bất cứ trường PivotTable nào hoặc nếu bạn không muốn có mục nào đó trong PivotTable nữa, chỉ cần kéo mục đó ra khỏi danh sách Trường hoặc bỏ chọn mục đó. Khả năng sắp xếp lại các mục Trường là một trong các tính năng PivotTable giúp bạn dễ dàng thay đổi hình thức PivotTable một cách nhanh chóng.
Danh sách Trường Bảng Tổng hợp.
Để tóm tắt,
Theo mặc định, các trường PivotTable được đặt trong khu vực Giá trị sẽ được hiển thị dưới dạng SUM. Nếu Excel hiểu dữ liệu của bạn dưới dạng văn bản thì dữ liệu sẽ được hiển thị dưới dạng SỐ LƯỢNG. Đây là lý do tại sao bạn cần đảm bảo không kết hợp các kiểu dữ liệu cho các trường giá trị. Bạn có thể thay đổi tính toán mặc định bằng cách bấm vào mũi tên bên phải tên trường, rồi chọn tùy chọn Cài đặt trường.
Tiếp theo, thay đổi tính toán trong mục Tóm tắt theo. Lưu ý rằng khi bạn thay đổi phương pháp tính toán, Excel sẽ tự động gắn thêm phương pháp đó vào mục Tên tùy chỉnh, như “Tổng của tên_trường” nhưng bạn có thể thay đổi mục đó. Nếu bạn bấm vào nút Số…, bạn có thể thay đổi định dạng số cho toàn bộ trường.
Mẹo: Do việc thay đổi tính toán trong mục Tóm tắt theo sẽ thay đổi tên trường PivotTable nên tốt nhất bạn không nên đổi tên các trường PivotTable cho đến khi bạn hoàn tất việc thiết lập PivotTable của mình. Có một mẹo là bấm vào Thay thế (trên menu Chỉnh sửa) >Tìm gì > “Tổng của“, rồi Thay thế bằng > để trống để thay thế tất cả cùng một lúc thay vì nhập lại bằng cách thủ công.
Hiển thị dữ liệu dưới dạng
Thay vì sử dụng một phép tính để tóm tắt dữ liệu, bạn cũng có thể thể hiện dữ liệu dưới dạng phần trăm của trường. Trong ví dụ sau đây, chúng tôi đã thay đổi số chi phí gia đình để hiển thị dưới dạng phần trăm của tổng cuối cùng thay vì tổng các giá trị.
Sau khi bạn đã mở hộp thoại Cài đặt trường, bạn có thể đưa ra lựa chọn của mình từ tab Hiển thị dữ liệu dưới dạng.
Hiển thị một số liệu cả dưới dạng phép toán và tỷ lệ phần trăm.
Chỉ cần kéo mục vào mục Giá trị hai lần, bấm chuột phải vào giá trị và chọn Cài đặt trường, rồi đặt các tùy chọn Tóm tắt theo và Hiển thị dữ liệu dưới dạng cho từng mục.
Khi thêm dữ liệu mới vào nguồn dữ liệu PivotTable, tất cả các PivotTable xây dựng trên nguồn dữ liệu đó sẽ cần được làm mới. Để làm mới một PivotTable, bạn có thể chuột phải vào bất kỳ đâu trong dải ô PivotTable và chọn Làm mới. Nếu bạn có nhiều PivotTable, hãy chọn một ô bất kỳ trong PivotTable và trên Dải băng, điều hướng đến Phân tích PivotTable, sau đó nhấn vào mũi tên bên dưới nút Làm mới và chọn Làm mới tất cả.
Nếu bạn đã tạo một PivotTable và quyết định không sử dụng nó nữa, bạn chỉ cần chọn toàn bộ phạm vi ô của PivotTable và nhấn Xóa. Thao tác này sẽ không ảnh hưởng đến dữ liệu khác hoặc các PivotTable hoặc biểu đồ xung quanh. Nếu PivotTable của bạn nằm trên một trang tính riêng không chứa bất kỳ dữ liệu quan trọng nào mà bạn muốn giữ, việc xóa trang tính đó sẽ là cách nhanh nhất để loại bỏ PivotTable.
Mẹo và thủ thuật định dạng dữ liệu
Sử dụng dữ liệu dạng bảng ngắn gọn để đạt được kết quả tốt nhất.
Sắp xếp dữ liệu của bạn theo cột, không theo dòng.
Hãy đảm bảo tất cả các cột đều có tiêu đề, với một hàng duy nhất, không trống nhãn cho mỗi cột. Tránh hàng kép của tiêu đề hoặc ô đã phối.
Bạn có thể sử dụng định dạng bảng Excel để tổ chức dữ liệu của mình. Hãy chọn một vị trí trong dữ liệu của bạn và sau đó chọn Chèn > Bảng từ dải băng để tạo một bảng Excel.
Hãy sử dụng Power Query để chuyển đổi dữ liệu phức tạp hoặc lồng nhau, ví dụ như bỏ xoay dữ liệu, để dữ liệu được sắp xếp trong các cột với một hàng tiêu đề duy nhất.
Bạn cần thêm trợ giúp?
Bạn có thể luôn luôn tìm kiếm sự giúp đỡ từ chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận được sự hỗ trợ từ Cộng đồng trả lời.
Bài viết liên quan
Tạo PivotChart.
Sử dụng công cụ chia nhỏ để lọc dữ liệu PivotTable.
Tạo bảng thời gian PivotTable để lọc ngày.
Tạo PivotTable bằng Mô hình Dữ liệu để phân tích thông tin trong nhiều bảng.
Tạo PivotTable được liên kết với Tập dữ liệu Power BI.
Sử dụng Danh sách Trường để sắp xếp các trường trong PivotTable.
Thay đổi dữ liệu gốc cho PivotTable.
Tính toán giá trị trong một Bảng Tổng hợp dữ liệu.
Xóa PivotTable.
Bạn cần thêm trợ giúp?
Bạn muốn xem các tùy chọn khác?
Khám phá những ưu điểm của gói đăng ký, khám phá những khóa học đào tạo, tìm hiểu cách bảo vệ thiết bị của bạn và nhiều hơn thế nữa.
Cộng đồng sẽ hỗ trợ bạn trong việc đặt và trả lời các câu hỏi, đồng thời cung cấp phản hồi và lắng nghe ý kiến từ những chuyên gia có kiến thức phong phú.