Tạo PivotTable để phân tích dữ liệu trang tính
PivotTable là một công cụ mạnh mẽ để tính toán, tóm tắt và phân tích dữ liệu. Nó cho phép bạn xem, so sánh, và phân tích mẫu hình và xu hướng trong dữ liệu của bạn. Cách PivotTable hoạt động có thể hơi khác nhau tùy thuộc vào nền tảng bạn đang sử dụng để chạy Excel.
Lựa chọn các ô bạn muốn sử dụng để tạo PivotTable.
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.
Bạn có thể lựa chọn nơi để đặt báo cáo PivotTable. Nếu bạn muốn đặt PivotTable vào một trang tính mới, hãy chọn “Trang tính Mới”. Nếu bạn muốn đặt PivotTable vào một trang tính hiện có, hãy chọn “Trang tính Hiện có” và sau đó chọn vị trí mà bạn muốn PivotTable xuất hiện.
Nhấn vào OK.
Bạn có thể chọn từ các nguồn khác nhau cho PivotTable của mình bằng cách bấm vào mũi tên xuống trên nút đó. Ngoài việc sử dụng bảng hoặc dải ô hiện có, bạn cũng có thể chọn từ ba nguồn khác để nhập PivotTable của mình.
Chú ý: Tùy 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 được đưa vào nút. Ví dụ: “Từ Power BI (Microsoft)”.
Lấy từ Nguồn Thông tin Ngoại viện.
Lấy từ Khung 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.
Lấy từ Power BI.
Nếu tổ chức của bạn sử dụng Power BI và bạn 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 được xây dựng trên nguồn dữ liệu đó cần phải đượ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ỳ vị trí nào trong dải ô PivotTable và chọn Làm mới. Nếu bạn có nhiều PivotTable, trước tiên hãy chọn một ô bất kỳ trong bất kỳ PivotTable nào, sau đó trên Dải băng, đi đến Phân tích PivotTable > nhấn vào mũi tên bên dưới nút Làm mới, sau đó chọn Làm mới tất cả.
Tóm lại, ý nghĩa của Theo.
Theo mặc định, khi PivotTable được đặt trong khu vực Giá trị, nó sẽ hiển thị dưới dạng TỔNG. Nếu dữ liệu của bạn được Excel nhận diện là văn bản, nó sẽ được hiển thị dưới dạng SỐ LƯỢNG. Điều này giải thích tại sao việc không kết hợp các kiểu dữ liệu cho các trường giá trị là quan trọng. Bạn có thể thay đổi tính toán mặc định bằng cách nhấp 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, hãy thay đổi cách tính toán trong phần 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 phần Tên Tùy chỉnh, ví dụ như “Tổng Tên Trường”. Tuy nhiên, bạn có thể thay đổi phần đó nếu muốn. Nếu bạn nhấp vào nút Định dạng Số, bạn có thể thay đổi định dạng số cho tất cả các trường.
Mẹo: Để tránh việc thay đổi tên trường PivotTable khi tính toán trong mục Tóm tắt Giá trị Theo, hãy hoàn tất việc thiết lập PivotTable trước khi thay đổi tên trường. Bạn có thể sử dụng tính năng Tìm & Thay thế (Ctrl+H) > Tìm > “Tổng của”, sau đó Thay thế bằng > để trống để thay đổi 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ị Giá trị Dưới hình thức.
Thay vì sử dụng phép tính để tóm tắt dữ liệu, bạn có thể hiển thị dữ liệu dưới dạng phần trăm của trường. Ví dụ, chúng tôi đã thay đổi số chi phí gia đình để hiển thị dưới dạng % Tổng Cuối thay vì tổng các giá trị.
Sau khi bạn mở hộp thoại Thiết đặt Trường Giá trị, bạn có thể lựa chọn hiển thị giá trị dưới dạng từ tab Hiển thị Giá trị.
Hiển thị một số liệu cả dưới dạng toán học và phần trăm.
Hãy kéo mục vào mục “Giá trị” hai lần, sau đó chọn 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.
Chọn một bảng hoặc phạm vi dữ liệu trong trang tính của bạn, sau đó chọn Chèn > PivotTable để mở ngăn Chèn PivotTable.
Bạn có thể tạo PivotTable của riêng mình bằng cách thủ công hoặc chọn một PivotTable được đề xuất sẵn để tạo. Dưới đây là những thao tác bạn có thể thực hiện:
Trên thẻ Tạo PivotTable của bạn, hãy 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 đã tồn tại để chọn đích của PivotTable.
Chú ý: PivotTable được đề xuất chỉ khả dụng cho người dùng đã đăng ký Microsoft 365.
Bạn có thể thay đổi nguồn thông tin cho bảng tổng hợp khi tạo dữ liệu.
Trên khung PivotTable, hãy chọn ô văn bản dưới Nguồn. Khi thay đổi Nguồn, các thẻ trong khung sẽ không có sẵn.
Thực hiện chọn lọc thông tin trên lưới hoặc nhập một phạm vi vào hộp văn bản.
Nhấn Enter 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 mới dựa trên nguồn dữ liệu mới.
Lấy từ Power BI.
Nếu tổ chức của bạn sử dụng Power BI và bạn 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 phần ngăn Trường PivotTable, lựa chọn hộp kiểm cho trường bất kỳ mà bạn mong muốn thêm vào PivotTable của bạn.
Mặc định, các trường không phải là số sẽ được thêm vào khu vực Hàng, các trường ngày và giờ sẽ được thêm vào khu vực Cột và các trường số sẽ được thêm vào khu vực Giá trị.
Bạn có thể tự do kéo và thả bất kỳ mục nào vào bất kỳ trường PivotTable nào. Nếu bạn không muốn sử dụng một mục trong PivotTable, hãy kéo mục đó ra khỏi danh sách hoặc bỏ chọn nó.
Tóm lại, ý nghĩa của Theo.
Mặc định, trong khu vực Giá trị, các trường PivotTable sẽ được hiển thị bằng hàm SUM. Nếu Excel nhận biết dữ liệu của bạn là văn bản, thì dữ liệu sẽ được hiển thị dưới dạng SỐ LƯỢNG. Điều này là 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ấm chuột phải vào bất kỳ giá trị nào trong hàng và chọn Tóm tắt Giá trị Theo để thay đổi tính toán mặc định.
Hiển thị Giá trị Dưới hình thức.
Thay vì sử dụng phép tính để tóm tắt dữ liệu, bạn có thể hiển thị dữ liệu dưới dạng phần trăm của trường. Ví dụ, chúng tôi đã thay đổi số chi phí gia đình để hiển thị dưới dạng % Tổng Cuối thay vì tổng các giá trị.
Bấm chuột phải vào bất kỳ giá trị nào trong cột mà bạn muốn hiển thị giá trị. Sau đó, chọn tùy chọn “Hiển thị Giá trị Như” trong menu. Danh sách các giá trị sẵn dùng sẽ được hiển thị.
Chọn một tùy chọn từ danh sách theo ý muốn.
Để hiển thị dưới dạng phần trăm của Tổng Cha mẹ, bạn chỉ cần di chuột qua mục đó trong danh sách và chọn trường mẹ để tính toán.
Để thêm dữ liệu mới vào PivotTable, bạn cần 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, hãy nhấp chuột phải vào bất kỳ ô nào trong PivotTable và chọn “Làm mới”.
Nếu bạn đã tạo PivotTable và muốn xóa nó, hãy chọn toàn bộ dải ô của PivotTable và nhấn Delete. Dữ liệu này sẽ không ảnh hưởng đến các dữ liệu hoặc PivotTable và biểu đồ khác xung quanh. Nếu PivotTable của bạn nằm trên một trang tính riêng không có dữ liệu khác mà bạn muốn giữ lại, bạn có thể nhanh chóng xóa trang tính để loại bỏ PivotTable.
Tốt nhất là bạn nên tổ chức dữ liệu của mình theo định dạng bảng và không để bất kỳ hàng hoặc cột trống nào. Một cách tốt nhất để làm điều này là sử dụng Bảng Excel giống 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à bất kỳ cột mới nào cũng sẽ được thêm vào Danh sách Trường PivotTable. Nếu không, bạn cần thay đổi dữ liệu nguồn cho PivotTable hoặc sử dụng công thức phạm vi có tên động.
Dữ liệu trong cột phải được thống nhất về kiểu. Ví dụ: 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, vì vậy dữ liệu thực tế của bạn không bị ảnh hưởng bởi bất kỳ thay đổi 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, thì PivotTable được đề xuất là một lựa chọn tốt. Khi sử dụng tính năng này, Excel sẽ tự động tạo một bố cục có ý nghĩa bằng cách phân loại dữ liệu vào các khu vực phù hợp nhất trong PivotTable. Điều này sẽ cung cấp cho bạn 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 từ chúng tôi.
Nhấp vào một ô trong dãy ô 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 lựa chọn PivotTable phù hợp nhất với bạn và nhấn nút OK. Excel sẽ tự động tạo một PivotTable trên một trang tính mới và hiển thị danh sách các trường của PivotTable.
Nhấp vào một ô trong dãy ô của bảng hoặc nguồn dữ liệu.
Đi tới Chèn > PivotTable.
Hộp thoại Tạo PivotTable trong Excel sẽ xuất hiện khi chọn tên dải ô hoặc bảng. Trong ví dụ này, chúng ta đang sử dụng bảng “tbl_HouseholdExpenses”.
Mục Chọn nơi đặt báo cáo PivotTable cho phép bạn lựa chọn giữa Trang tính mới hoặc Trang tính hiện có. Nếu bạn chọn Trang tính hiện có, hãy chọn ô để đặt PivotTable.
Nhấp 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.
Trên vùng Tên Trường, hãy chọn hộp kiểm của trường mà bạn muốn thêm vào PivotTable. 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 kỳ mục nào vào bất kỳ 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 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 lược như sau.
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 phép tính để tóm tắt dữ liệu, bạn có thể hiển thị dữ liệu dưới dạng phần trăm của trường. Ví dụ, chúng tôi đã thay đổi số chi phí gia đình để hiển thị dưới dạng % Tổng Cuối 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ố giá trị cùng với phép tính 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 được xây dựng trên nguồn dữ liệu đó cần phải đượ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ỳ vị trí nào trong dải ô PivotTable và chọn Làm mới. Nếu bạn có nhiều PivotTable, trước tiên hãy chọn một ô bất kỳ trong bất kỳ PivotTable nào, sau đó trên Dải băng, đi đến Phân tích PivotTable > nhấn vào mũi tên bên dưới nút Làm mới, sau đó 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 có thể xóa toàn bộ dải ô PivotTable bằng cách chọn và nhấn Xóa. Thao tác này sẽ không ảnh hưởng đến dữ liệu khác hoặc PivotTable và biểu đồ xung quanh nó. Nếu PivotTable của bạn nằm trên một trang tính riêng không chứa dữ liệu khác mà bạn muốn giữ, bạn có thể nhanh chóng loại bỏ trang tính đó.
Mẹo và thủ thuật định dạng dữ liệu
Sử dụng dữ liệu định dạng bảng ngăn nắp để đạ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 hà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ể chọn định dạng dữ liệu của bạn dưới dạng bảng Excel. Hãy chọn vị trí bất kỳ trong dữ liệu và sau đó chọn Chèn > Bảng từ dải băng.
Hãy sử dụng Power Query nếu bạn đang làm việc với dữ liệu phức tạp hoặc lồng nhau. Power Query giúp bạn chuyển đổi dữ liệu, ví dụ như 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 đặt câu hỏi cho một chuyên gia trong cộng đồng Kỹ thuật Excel hoặc nhận được 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ụ slicer để lọc dữ liệu của PivotTable.
Tạo một bảng tổng hợp thời gian PivotTable để lọc theo ngày.
Tạo PivotTable bằng Mô hình Dữ liệu để phân tích dữ liệu trong nhiều bảng.
Tạo bảng Pivot đượ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.
Sửa đổi dữ liệu gốc cho PivotTable.
Tính toán giá trị trong một bảng tổng hợp.
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 tiện ích của gói đăng ký, khám phá các khóa học, 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ú.
