Bảng Pivot là một tính năng cực kỳ hữu ích của Excel. Sử dụng tính năng này, người dùng có thể thao tác bảng dữ liệu theo ý muốn. Nếu bạn tò mò muốn biết cách bạn có thể phân tích dữ liệu trong Excel sử dụng Bảng Pivot, bài viết này có thể hữu ích cho bạn. Trong bài viết này, chúng ta sẽ chỉ cho bạn cách bạn phân tích dữ liệu trong Excel sử dụng Bảng Pivot với giải thích chi tiết.
Tải xuống sổ làm việc thực hành bên dưới.
Tổng quan về Bảng Pivot
Như đã nêu trước đó, Bảng Pivot là một công cụ mạnh mẽ để phân tích dữ liệu. Bảng Pivot cho phép người dùng sửa đổi bảng dữ liệu hiện có theo ý muốn của họ. Việc cắt chỉ các cột quan trọng hoặc cần thiết từ Bảng và sau đó phân tích chúng là chủ đề chính của Bảng Pivot. Trước khi chúng ta đi sâu vào Phân tích Dữ liệu, chúng ta cần làm quen với môi trường Bảng Pivot.
- Sau khi chúng ta khởi tạo Bảng Pivot, chúng ta sẽ thấy ba tab riêng biệt trên ribbon ngay lập tức. Power Pivot, Phân tích Bảng Pivot và Thiết kế.
- Chúng ta cũng có một bảng điều khiển bên cạnh của Các trường. Các trường về cơ bản là các cột mà chúng ta có trong tập dữ liệu chính.
- Chúng ta có thể kéo các trường vào các khu vực dưới đây, được đặt tên là Bộ lọc, Cột, Hàng và Giá trị.
- Kéo các trường vào hàng sẽ hiển thị chúng ở cột ngoài cùng bên trái dưới dạng hàng trong Bảng Pivot.
- Và thêm chúng vào cột sẽ đặt các giá trị trong cột trong Bảng Pivot.
- Bạn có thể thay đổi cài đặt trường giá trị, cho dù bạn muốn hiển thị Giá trị trung bình/Tối đa/Tối thiểu v.v. Trong khu vực Trường Giá trị.
Các Bước để Tạo Bảng Pivot trong Excel
Chúng ta sẽ ban đầu chèn Bảng Pivot từ tab Chèn.
- Từ đó nhấp vào Bảng Pivot > Từ Bảng/Phạm vi.
- Sau đó trong hộp thoại mới, nhấp vào hộp phạm vi Bảng/Phạm vi và sau đó chọn tùy chọn Trang tính mới.
- Việc làm này sẽ chọn bảng và sẽ mở Bảng Pivot trong một trang mới.
- Nhấp OK sau khi này.
- Ngay lập tức sau khi nhấp OK, bạn sẽ nhận thấy một trang tính mới mở ra với cài đặt Trường Bảng Pivot.
- Trang tính mới sẽ trông giống như hình ảnh bên dưới.
9 Ví dụ Phù hợp để Phân tích Dữ liệu trong Excel Sử dụng Bảng Pivot
Với mục đích trình diễn, chúng ta sẽ sử dụng tập dữ liệu bên dưới. Đây là dữ liệu bán hàng giả định của một tổ chức tưởng tượng, nơi chúng ta có thể thấy ID của các sản phẩm mà họ đã bán cho đến nay, Khu vực nào họ bán, Loại của chúng, Số lượng, Chi phí sản xuất, Đánh giá, Doanh thu và Biên lợi nhuận. Chúng ta sẽ triển khai Bảng Pivot để phân tích và trích xuất những thông tin quan trọng về bảng dữ liệu.
1. Chọn Các Trường Khác Nhau để Phân tích Dữ liệu trong Bảng Pivot
Chúng ta có thể thêm nhiều trường khác nhau vào các khu vực dưới đây để tạo các bảng với hình dạng và tiêu chí khác nhau.
Ví dụ. Chúng ta kéo Khu vực vào trường hàng và sau đó kéo Doanh thu vào trường Giá trị.
Một Bảng Pivot sẽ được tạo theo các trường này trong các khu vực Bảng Pivot.
Các Bước
- Chúng ta đã thu nhỏ bảng ban đầu xuống hình dạng tối thiểu, nơi chúng ta có thể thấy Tổng Doanh thu kiếm được ở mỗi Khu vực. Điều này giúp chúng ta cực kỳ lớn để đánh giá hiệu suất của mỗi Khu vực.
- Chúng ta có thể thêm nhiều tiêu chí hoặc trường giá trị hơn vào khu vực để đánh giá hiệu suất của từng khu vực trong các lĩnh vực khác nhau.
- Ví dụ, chúng ta có thể kéo Chi phí và Số lượng vào khu vực để mở rộng bảng hiện có của chúng ta.
- Từ bảng này, chúng ta có thể thấy tổng chi phí sản xuất ở mỗi Khu vực. Tổng Số lượng sản phẩm được sản xuất ở mỗi Khu vực cũng có ở đây.
- Loại bảng này cho phép chúng ta phân tích dữ liệu hiệu quả hơn và giúp giữ cho chúng không bị lộn xộn.
- Đối với tất cả các trường giá trị, các trường được hiển thị dưới dạng Tổng của các trường. Nhưng người dùng có thể thay đổi hoạt động của trường rất dễ dàng.
- Ví dụ, chúng ta có thể thêm trường Đánh giá vào khu vực. Giá trị trong Bảng Pivot sẽ được hiển thị là Tổng của Đánh giá trong mỗi Khu vực. Nhưng điều đó không có ý nghĩa. Chúng ta không được hưởng lợi từ tổng của Đánh giá, chúng ta cần Đánh giá trung bình của mỗi Khu vực.
- Để thay đổi cài đặt đánh giá trường, chúng ta sẽ nhấp vào trường trong khu vực, sau đó chuột phải.
- Sau đó từ menu, chọn Cài đặt Trường Giá trị.
- Sẽ có một hộp thoại mới tên là Cài đặt Trường Giá trị.
- Trong hộp thoại đó, chọn Trung bình từ nhóm Tóm tắt trường giá trị theo.
- Nhấp OK sau khi này.
- Bây giờ chúng ta sẽ nhận thấy rằng Đánh giá hiện được trình bày dưới dạng giá trị trung bình thay vì tổng SUM trong phạm vi ô E4:E11.
- Với dữ liệu này, chúng ta có thể phân tích Khu vực nào sản phẩm nhận được đánh giá khách hàng tốt hơn so với các khu vực khác.
- Từ bảng trên, khá rõ ràng rằng Khu vực Châu Á hoạt động tốt so với các Khu vực khác.
- Tiếp theo, chúng ta sẽ thêm trường Biên lợi nhuận vào khu vực. Vì cài đặt giá trị mặc định được đặt là Tổng, hãy chuyển nó sang Trung bình, để xem Trung bình của Biên lợi nhuận của mỗi Khu vực.
- Việc làm này sẽ thêm biên lợi nhuận của mỗi Khu vực trong phạm vi ô F4:F12
- Nhưng Biên lợi nhuận đang ở dạng phân số, chúng ta cần chuyển đổi chúng sang phần trăm.
- Để làm điều này, chúng ta sẽ chọn phạm vi ô F4:F12 và sau đó nhấp vào biểu tượng Phần trăm trong nhóm Số.
- Bây giờ chúng ta có thể thấy Biên lợi nhuận trung bình ở định dạng phần trăm của mỗi Khu vực trong phạm vi ô F4:F11.
- Với bảng tóm tắt này, chúng ta có thể phân tích rằng Khu vực Châu Phi đang hoạt động tốt về mặt Biên lợi nhuận. Mặt khác, chúng ta có thể kết luận rằng Trung Đông - Châu Á hoạt động kém nhất về mặt Biên lợi nhuận. về cơ bản họ đang lỗ tiền khi bán sản phẩm.
2. Lồng Nhiều Trường
Chúng ta có thể nhóm nhiều trường lại với nhau để tạo một lồng tiêu chí trong Bảng Pivot. Trong trường lồng nhau hoặc nhóm, trường đầu tiên sẽ trình bày dữ liệu trước. Sau đó dưới trường đầu tiên, tất cả các trường cấp hai sẽ có mặt. Giống như hình ảnh bên dưới.
Các Bước
- Để làm điều này, kéo trường Danh mục ngay bên dưới trường Khu vực trong khu vực Hàng.
- Sau đó, bạn sẽ nhận thấy Danh mục sẽ được lồng bên dưới mỗi trường Khu vực.
- Sử dụng bảng, chúng ta có thể phân biệt không chỉ các Khu vực với nhau mà còn các trường bên trong của mỗi Khu vực.
- Bây giờ chúng ta có thể kéo Khu vực đến vị trí thứ hai. Việc làm này đặt Danh mục ở vị trí đầu tiên trong việc lồng nhau. Như hiển thị trong hình ảnh bên dưới.
3. Lọc Dữ liệu trong Bảng Pivot
Sau khi tạo Bảng Pivot, chúng ta có thể thêm bộ lọc vào lựa chọn để thêm sự linh hoạt cho phân tích của chúng ta.
Các Bước
- Để làm điều này, kéo trường Danh mục vào khu vực Bộ lọc.
- Ngay sau khi kéo trường Danh mục, sẽ có một Biểu tượng Bộ lọc ở góc trên bên trái của trang tính.
- Nhấp vào Biểu tượng Bộ lọc. Và tích chọn Chọn nhiều mục.
- Sau đó chọn Điện tử và nhấp OK.
- Bây giờ, bảng lọc ra tất cả các mục nhập khác ngoài Điện tử, cập nhật bảng tự động.
- Bảng cập nhật được hiển thị bên dưới. Ở đây chỉ có Khu vực Châu Phi, Châu Âu, và Nam Á có sản phẩm dưới Danh mục Điện tử. Do đó chỉ có các Khu vực này được hiển thị ở đây.
4. Sắp xếp Dữ liệu trong Bảng Pivot
Ngoài việc lọc tập dữ liệu, chúng ta cũng có thể sắp xếp dữ liệu theo yêu cầu của mình. Như chúng ta có thể sắp xếp chúng theo thứ tự bảng chữ cái hoặc từ nhỏ đến lớn.
Các Bước
- Để sắp xếp Bảng Pivot, nhấp vào biểu tượng sắp xếp ở trên cùng của Các cấp Hàng một menu thả xuống sẽ xuất hiện. Từ menu thả xuống đó, nhấp vào Tùy chọn Sắp xếp thêm.
- Sau đó, một hộp thoại mới sẽ mở ra, nơi bạn sẽ chọn tên trường mà bạn sẽ thực hiện sắp xếp. Trong trường hợp này, chúng ta chọn Khu vực.
- Theo mặc định, tùy chọn sắp xếp được đặt là Tăng dần (A đến Z) theo:
- Vì vậy Các Khu vực sẽ được tổ chức theo thứ tự bảng chữ cái.
- Bảng mới được tổ chức sẽ trông giống như thế này.
- Nhưng chúng ta không chỉ có thể Sắp xếp Các Khu vực mà còn theo bất kỳ trường nào theo bất kỳ tiêu chí nào.
- Ví dụ, bạn có thể sắp xếp Bảng Pivot theo tổng Số lượng sản phẩm trong mỗi Khu vực được sản xuất.
- Để làm điều này, chọn biểu tượng sắp xếp, và sau đó trong hộp thoại Sắp xếp, chọn Tổng Số lượng trong menu thả xuống.
- Sau đó, nhấp vào Tùy chọn thêm ở góc dưới bên phải của hộp thoại.
- Trong hộp thoại tiếp theo, nhấp vào Tổng cộng trong tùy chọn Sắp xếp theo. Nghĩa là chúng ta sẽ về cơ bản Sắp xếp bảng theo Số lượng từ nhỏ đến lớn. Nhấp OK sau khi này.
- Bây giờ bạn sẽ quay lại hộp thoại trước đó. Nhấp OK trên hộp đó cũng vậy.
- Sau khi nhấp vào OK, Bạn sẽ nhận thấy rằng Bảng Pivot bây giờ được sắp xếp theo Số lượng nhỏ nhất của sản phẩm đến Số lượng lớn nhất của sản phẩm.
- Tính năng này giúp rất lớn trong khi phân tích bảng, vì bảng đã được tổ chức khá tốt. Chúng ta không chỉ có số lượng Sản phẩm được sắp xếp theo mỗi Khu vực mà chúng cũng có thể được Sắp xếp để chúng ta có thể đánh giá dễ dàng Khu vực nào đã sản xuất nhiều sản phẩm hơn.
Và đây là cách chúng ta phân tích dữ liệu bằng cách Sắp xếp chúng trong Bảng Pivot trong Excel.
Đọc thêm: Cách Phân tích Tập dữ liệu Lớn trong Excel (6 Phương pháp Hiệu quả)
Đọc thêm tương tự
- Cách Phân tích Dữ liệu Thang Likert trong Excel (với Các Bước Nhanh)
- Phân tích Dữ liệu Định tính từ Bảng hỏi trong Excel
- Cách Phân tích Dữ liệu Theo Thời gian trong Excel (Với Các Bước Dễ Dàng)
- Phân tích Dữ liệu Chuỗi Thời gian trong Excel (Với Các Bước Dễ Dàng)
- Cách Thêm Phân tích Dữ liệu trong Excel (với 2 Bước Nhanh)
5. Sử dụng Slicer (Bộ lọc trực quan) trong Bảng Pivot
Một Slicer là một công cụ tiện lợi để lọc ra thông tin cần thiết một cách hiệu quả.
Các Bước
- Ở bước tiếp theo, chúng ta có thể thêm một vài slicer vào Bảng Pivot.
- Để thêm slicer, nhấp vào lệnh Chèn Slicer từ tab Phân tích Bảng Pivot.
- Sau khi nhấp vào lệnh Chèn Slicer, sẽ có một hộp thoại mới tên Chèn Slicers.
- Trên hộp thoại đó, chọn các slicer mà bạn sẽ thêm bằng cách tích chọn các hộp kiểm. Trong trường hợp này, chúng ta tích chọn Danh mục, Khu vực và Đánh giá.
- Sau khi hoàn thành việc tích chọn, nhấp vào OK.
- Ngay sau khi nhấp vào OK, sẽ có 3 slicer khác nhau xuất hiện trong trang tính. Mỗi cái cho mỗi trường và các mục nhập trong các trường đó.
- Nhấp vào Châu Á và Nam Á trong slicer đầu tiên. Điều này sẽ loại bỏ các mục nhập khác có Khu vực khác ngoài Châu Á và Nam Á.
- Sau đó chọn Điện tử và Thiết bị gia đình.
- Điều này sẽ chỉ chọn sản phẩm trong hai Danh mục đó.
- Sau đó chọn Đánh giá 4 và 4.5.
- bảng sẽ cập nhật thường xuyên khi chúng ta nhấp vào Slicer.
- Cuối cùng, chúng ta có các sản phẩm khác nhau và các thông số hiệu suất khác nhau chỉ từ Châu Á và Nam Á Khu vựcs. Danh sách được lọc thêm chỉ bằng hai Danh mục sản phẩm. Sau đó chúng ta tiến hành chọn chỉ 4 và 4.5 Đánh giá. Đó là cách chúng ta thu hẹp sản phẩm theo các thông số hiệu suất.
Và đây là cách chúng ta phân tích dữ liệu bằng cách thêm các slicer trong Bảng Pivot trong Excel.
6. Phân tích Dữ liệu với Biểu đồ Pivot
Chúng ta có thể nâng cao thêm phân tích của mình bằng cách hiển thị Bảng Pivot của mình với một số biểu đồ. Ví dụ, chúng ta có thể sử dụng biểu đồ cột chồng với trục kép để trực quan hóa Tổng Doanh thu và Biên lợi nhuận liên quan đến Các Khu vực.
Các Bước
- Chúng ta cần trực quan hóa cách Biên lợi nhuận kiếm được bởi mỗi Khu vực liên quan đến doanh thu do họ tạo ra. Chúng có tỷ lệ thuận với nhau, hay chúng hoạt động ngược nhau?
- Để làm điều này, trước hết, chúng ta cần đi đến tab Chèn và sau đó nhấp vào Biểu đồ Pivot. Sau đó từ menu thả xuống nhấp vào Biểu đồ Pivot.
- Sẽ có một hộp thoại mới tên Chèn Biểu đồ, nơi bạn có thể chọn loại biểu đồ yêu thích của mình.
- Từ tùy chọn đó, chúng ta chọn tùy chọn Combo, vì chúng ta cần hiển thị hai giá trị riêng biệt trên trục dọc.
- Trong menu đó, cho Tổng Doanh thu, chọn loại biểu đồ là Cột nhóm từ menu thả xuống.
- Sau đó cho Trung bình của Biên lợi nhuận, chọn loại biểu đồ Đường. Và cũng đặt biểu đồ này làm trục thứ hai bằng cách tích chọn hộp ở bên phải của menu thả xuống.
- Sau khi nhấp OK, chúng ta sẽ nhận thấy biểu đồ mới hiện đã có mặt. Với hai trục, bên trái là cho Tổng Doanh thu và bên phải là cho Biên lợi nhuận.
Từ biểu đồ, chúng ta có thể thấy Biên lợi nhuận và Tổng Doanh thu không chính xác tỷ lệ thuận ở đây. Doanh thu cao hơn không có nghĩa là Biên lợi nhuận sẽ cao hơn tương ứng.
- Các biểu đồ có bộ lọc mặc định trong nó. Ví dụ, bạn có thể thấy bộ lọc Danh mục đã được đặt ở góc trên bên trái của biểu đồ. Nhấp vào nó và xem rằng tất cả Danh mục sản phẩm hiện đã được chọn.
- Nếu bạn muốn chọn một số Danh mục và bỏ qua phần còn lại, chọn cái bạn muốn và sau đó nhấp OK. Ví dụ, chúng ta chọn Nội thất và Thiết bị gia đình và sau đó nhấp OK.
- Sau khi nhấp OK, bạn sẽ nhận thấy biểu đồ bây giờ chỉ hiển thị giá trị Nội thất và Thiết bị gia đình. Và chỉ 4 Khu vực thực sự có sản phẩm Danh mục đó. Đó là lý do biểu đồ hiện đang thu nhỏ và chỉ hiển thị dữ liệu 4 Khu vực.
Đây là cách chúng ta có thể phân tích và trực quan hóa Bảng Pivot trong Excel và sau đó thu hẹp dữ liệu bằng tùy chọn lọc trong biểu đồ.
7. Cập nhật Dữ liệu trong Bảng Pivot Hiện Có
Cập nhật dữ liệu được coi là công việc hàng ngày trong bất kỳ dự án phân tích dữ liệu nào. Giả sử chúng ta có một số dữ liệu bán hàng mới thô, như được hiển thị trong phần được làm nổi bật của bảng bên dưới. Bây giờ tạo một Bảng Pivot mới từ đầu có thể là một việc nhàm chán. Để giải quyết vấn đề này, chúng ta sẽ cập nhật nguồn dữ liệu bên trong bằng cách giữ nguyên cấu trúc Bảng Pivot ban đầu.
Các Bước
- Đầu tiên, từ tab Phân tích Bảng Pivot, nhấp vào lệnh Thay đổi Nguồn Dữ liệu.
- Sau đó từ menu thả xuống, nhấp vào Thay đổi Nguồn Dữ liệu.
- Sau đó trong hộp phạm vi Di chuyển Bảng Pivot, chọn toàn bộ phạm vi của bảng đã cập nhật.
- Sau khi chọn toàn bộ phạm vi, bạn sẽ thấy rằng toàn bộ phạm vi hiện đã được thêm vào Bảng Pivot hiện có, và nó cũng được cập nhật.
Đọc thêm: [Đã sửa:] Phân tích Dữ liệu Không Hiển thị trong Excel (2 Giải pháp Hiệu quả)
8. Lấy 10 Giá trị Hàng Đầu từ Bảng Pivot
Bạn có thể trích xuất 10 hàng đầu hoặc 20 hàng đầu giá trị trong một tập dữ liệu, sử dụng biểu tượng Sắp xếp ở góc trên bên trái.
Các Bước
- Để làm điều này, trước tiên, nhấp vào biểu tượng thả xuống ở trên cùng của Các cấp Hàng.
- Sau đó từ menu thả xuống, nhấp vào Bộ lọc Giá trị > 10 Hàng Đầu.
- Có một hộp thoại mới tên là Bộ lọc 10 Hàng Đầu (ID Sản phẩm).
- Trong hộp thả xuống đầu tiên, chọn xem bạn muốn giá trị 10 hàng đầu hay 10 hàng cuối. Chúng ta chọn Hàng đầu.
- Từ cửa sổ đó, chọn xem bạn muốn chọn 10 hàng đầu hay 20. Chúng ta chọn 10.
- Sau đó chọn Mục từ hộp thả xuống tiếp theo.
- Sau đó chọn dựa trên cột nào bạn sẽ lọc 10 hàng đầu giá trị. Chúng ta chọn Trung bình của Biên lợi nhuận.
- Nhấp OK sau khi này.
Sau khi nhấp OK, bạn sẽ nhận thấy 10 Giá trị Hàng Đầu theo Trung bình của Biên lợi nhuận đang hiển thị.
Và đây là cách chúng ta phân tích dữ liệu bằng cách cắt giảm 10 dữ liệu hàng đầu trong Bảng Pivot trong Excel.
9. Nhóm Dữ liệu trong Bảng Pivot
Trong Bảng Pivot, cũng có thể nhóm dữ liệu lại với nhau.
Các Bước
- Để làm điều này, chọn nhiều giá trị bằng cách nhấn Ctrl, sau đó chuột phải.
- Sau đó từ menu ngữ cảnh, chọn Nhóm.
- Sau khi nhấp lệnh Nhóm, chúng ta có thể thấy ID Sản phẩm bây giờ đã được gom lại với nhau.
- Chúng ta cũng có thể nhóm dữ liệu theo Khu vực. Ví dụ, chúng ta có thể gom toàn bộ Khu vực Châu Á dưới một nhóm. Sau đó nhóm các cái khác làm nhóm phần còn lại của thế giới
- Để làm điều này, trước tiên, tạo nhóm như đã hiển thị trước đó.
- Sau đó chọn ô và nhấn F2.
- Việc làm này sẽ khiến ô chuyển vào chế độ chỉnh sửa.
- Sau đó chúng ta nhập tên nhóm "Phần còn lại của Thế giới".
- Lặp lại quy trình tương tự cho Nhóm 2.
- Ở đây chúng ta sẽ nhập Khu vực Châu Á, làm tên của nhóm.
- Cuối cùng, tất cả chúng sẽ trông giống như thế này.
Và đây là cách chúng ta phân tích dữ liệu bằng cách nhóm chúng trong Bảng Pivot trong Excel.
Kết luận
Tóm lại, câu hỏi "cách phân tích dữ liệu trong excel sử dụng bảng pivot" được trả lời ở đây với 11 quy trình khác nhau. Những quy trình đó có thể làm cho bất kỳ dự án phân tích dữ liệu nào trở nên suôn sẻ với Bảng Pivot.
Đối với vấn đề này, một sổ làm việc có sẵn để tải xuống nơi bạn có thể thực hành các phương pháp này.
Đừng ngần ngại đặt bất kỳ câu hỏi hoặc phản hồi nào qua phần bình luận. Bất kỳ đề xuất nào để cải thiện cộng đồng ExcelDemy sẽ được trân trọng cao.
Các Bài Viết Liên Quan
- Phân tích Dữ liệu Định tính trong Excel (với Các Bước Dễ Dàng)
- Cách Phân tích Dữ liệu qPCR trong Excel (2 Phương pháp Dễ Dàng)
- Thực hiện Nghiên cứu Case Study Sử dụng Phân tích Dữ liệu Excel
- Cách Phân tích Dữ liệu Văn bản trong Excel (5 Cách Phù hợp)
- Cách Chuyển đổi Dữ liệu Định tính sang Định lượng trong Excel