Bảng tổng hợp 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 trên bảng dữ liệu theo ý muốn của mình. 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 bằng Pivot Table, thì bài viết này có thể hữu ích cho bạn. Trong bài viết này, chúng tôi sẽ giới thiệu cách bạn phân tích dữ liệu trong Excel sử dụng Bảng tổng hợp với một lời giải thích cặn kẽ.
Tải xuống sách bài tập thực hành này bên dưới.
Tổng quan về bảng tổng hợp
Như đã nêu trước đây, Bảng tổng hợp là một công cụ mạnh để phân tích dữ liệu. A Bảng tổng hợp cho phép người dùng sửa đổi Bảng dữ liệu hiện có theo ý muốn của họ. Chỉ cắt 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 tổng hợp . Trước khi chúng tôi đi sâu vào Phân tích dữ liệu , chúng tôi cần làm quen với Bảng tổng hợp môi trường.
- Sau khi chúng tôi bắt đầu Bảng tổng hợp , chúng ta sẽ thấy ba tab riêng biệt trong ruy-băng ngay lập tức. Power Pivot , Phân tích PivotTable và Thiết kế .
- Chúng tôi cũng có một bảng điều khiển bên cạnh các Trường. Các trường về cơ bản là các cột mà chúng tôi đã có trong tập dữ liệu chính.
- Chúng tôi có thể kéo các trường vào các khu vực bên dưới, được đặt tên là Bộ lọc , Cột , Hàng, và Giá trị .
- Kéo các trường trên hàng sẽ hiển thị chúng ở cột ngoài cùng bên trái dưới dạng các hàng trong Bảng tổng hợp .
- Và thêm chúng vào các cột sẽ đặt các giá trị trong cột đó trong Bảng tổng hợp .
- 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 giá trị, v.v. Trong Giá trị khu vực thực địa.
Các bước tạo bảng tổng hợp trong Excel
Ban đầu, chúng tôi sẽ chèn Bảng tổng hợp từ Chèn tab.
- Từ đó, nhấp vào PivotTable > Từ Bảng / Phạm vi.
- Sau đó, trong hộp thoại mới, nhấp vào Bảng / Phạm vi hộp phạm vi và sau đó chọn Trang tính mới tùy chọn.
- Thực hiện việc này sẽ chọn bảng và sẽ mở Bảng tổng hợp trong một trang tính mới.
- Nhấp vào OK sau cái này.
- Ngay sau khi nhấp vào OK, bạn sẽ nhận thấy rằng một trang tính mới được mở bằng Trường PivotTable cài đặt .
- Trang tính mới sẽ giống như hình dưới đây.
9 Ví dụ Thích 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 tôi sẽ sử dụng tập dữ liệu dưới đây. Đâ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 họ đã bán, Loại của họ , Số lượng , Chi phí sản xuất, Xếp hạng , Doanh thu, và Biên lợi nhuận. Chúng tôi sẽ triển khai Bảng tổng hợp để phân tích và trích xuất những thông tin chi tiết 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 tổng hợp
Chúng ta có thể thêm các trường khác nhau vào các vùng bên dưới để tạo bảng với các hình dạng và tiêu chí khác nhau.
Ví dụ. Chúng tôi đã kéo Khu vực vào các trường hàng và sau đó kéo Doanh thu thành Giá trị trường.
Bảng Pivot sẽ được tạo theo các trường này trong Pivot khu vực.
Các bước
- Chúng tôi đã cắt bớt bảng ban đầu thành hình dạng tối thiểu, nơi chúng tôi có thể thấy Doanh thu tổng số tiền kiếm được ở mỗi Khu vực . Điều này giúp chúng tôi rất nhiều để truy cập cách từng Khu vực biểu diễn.
- Chúng tôi có thể thêm nhiều tiêu chí hoặc trường giá trị hơn trong khu vực để đánh giá hiệu suất của từng tiêu chí trong các lĩnh vực khác nhau.
- Ví dụ:chúng tôi có thể kéo Chi phí và Số lượng trong lĩnh vực này để mở rộng bảng hiện có của chúng tôi.
- Từ bảng này, chúng ta có thể thấy tổng chi phí sản xuất ở mỗi Vùng . Tổng số Số lượng của các sản phẩm được sản xuất ở mỗi Vùng cũng ở đây.
- Loại bảng này cho phép chúng tôi phân tích dữ liệu hiệu quả hơn và giúp 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 lĩnh vực. Nhưng người dùng có thể thay đổi thao tác trường rất dễ dàng.
- Ví dụ:chúng tôi có thể thêm Xếp hạng lĩnh vực trên địa bàn. Giá trị trong Bảng tổng hợp sẽ được hiển thị dưới dạng Tổng trong số Xếp hạng ở mỗi Khu vực . Nhưng nó không có ý nghĩa. Chúng tôi không được hưởng lợi từ tổng số Xếp hạng , chúng tôi cần Xếp hạng trung bình của từng Khu vực.
- Để thay đổi cài đặt đánh giá hiện trường, chúng tôi sẽ nhấp vào trường trong khu vực, sau đó nhấp chuột phải vào chuột.
- Sau đó, từ trình đơn, hãy chọn Cài đặt Trường Giá trị.
- Sẽ có một hộp thoại mới có tên Cài đặt Trường Giá trị .
- Trong hộp thoại đó, chọn Trung bình từ trường Tóm tắt giá trị bằng nhóm.
- Nhấp vào OK sau cái này.
- Bây giờ, chúng tôi sẽ nhận thấy rằng Xếp hạng hiện được trình bày dưới dạng giá trị trung bình thay vì SUM tổng số trong phạm vi ô E4:E11.
- Với dữ liệu này, chúng tôi có thể phân tích Khu vực nào sản phẩm nhận được đánh giá tốt hơn của khách hàng so với những sản phẩm khác.
- Từ bảng trên, rõ ràng là 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 tôi sẽ thêm Biên lợi nhuận lĩnh vực trên địa bàn. Vì cài đặt giá trị mặc định được đặt thành Tổng, hãy chuyển nó thành Trung bình, để xem Trung bình trong số Biên lợi nhuận của từng Khu vực .
- Làm điều này sẽ thêm tỷ suất lợi nhuận của từng Khu vực trong phạm vi ô F4:F12
- Nhưng Biên lợi nhuận ở dạng phân số, chúng ta cần chuyển đổi chúng thành phần trăm.
- Để thực hiện việc này, chúng tôi sẽ chọn phạm vi ô F4:F12 và sau đó nhấp vào Phần trăm trong Số nhóm.
- Giờ đây, chúng tôi có thể thấy Biên lợi nhuận trung bình ở định dạng phần trăm của từng Khu vực trong phạm vi ô F4:F11.
- Với bảng tóm tắt này, chúng tôi có thể phân tích rằng Châu Phi Vùng đ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 Á đang hoạt động kém nhất về Biên lợi nhuận. về cơ bản họ đang mất tiền khi bán sản phẩm.
2. Lồng nhiều trường
Chúng tôi có thể nhóm một số trường hoàn toàn để tạo một nhóm tiêu chí trong Bảng tổng hợp . Trong lồng nhau hoặc được nhóm trường đầu tiên sẽ hiển thị dữ liệu đầu tiên. Sau đó, bên dưới trường đầu tiên, tất cả các trường cấp hai sẽ hiện diện. Giống như hình ảnh bên dưới.
Các bước
- Để thực hiện việc này, hãy kéo Danh mục trường ngay bên dưới Vùng trong trường Hàng khu vực.
- Sau đó, bạn sẽ thấy Danh mục sẽ được lồng vào bên dưới mỗi trường Vùng.
- Sử dụng bảng, chúng tôi không chỉ có thể phân biệt Khu vực từ nhau mà còn là trường bên trong của mỗi Vùng .
- Giờ đây, chúng tôi có thể kéo Khu vực lên vị trí thứ hai. Làm điều đó sẽ đặt Danh mục ở vị trí đầu tiên trong tổ. Như hình dưới đây.
3. Lọc dữ liệu trong Pivot Table
Sau khi tạo Bảng tổng hợp chúng tôi có thể thêm bộ lọc vào lựa chọn để thêm tính linh hoạt hơn cho phân tích của chúng tôi .
Các bước
- Để thực hiện việc này, hãy kéo Danh mục trong trường Bộ lọc khu vực.
- Ngay sau khi kéo Danh mục , sẽ có một Bộ lọc ở góc trên cùng bên trái của trang tính.
- Nhấp vào Bộ lọc biểu tượng. Và đánh dấu chọn vào mục Chọn nhiều mục.
- Sau đó, chọn Điện tử và nhấp vào OK.
- Bây giờ, bảng lọc ra tất cả các mục nhập ngoài Điện tử , tự động cập nhật bảng.
- Bảng cập nhật được hiển thị bên dưới. Ở đây chỉ có Châu Phi , Châu Âu, và Khu vực Nam Á s có các sản phẩm trong Điện tử Danh mục . Do đó, chỉ những Khu vực này được hiển thị ở đây.
4. Sắp xếp dữ liệu trong bảng tổng hợp
Bên cạnh 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. Giống như chúng ta có thể sắp xếp chúng theo thứ tự bảng chữ cái hoặc từ nhỏ nhất đến lớn nhất.
Các bước
- Để sắp xếp Bảng tổng hợp , nhấp vào biểu tượng sắp xếp ở đầu Hàng trình đơn thả xuống sẽ xuất hiện. từ trình đơn thả xuống đó, hãy nhấp vào Tùy chọn sắp xếp khác.
- 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 tôi chọn Khu vực .
- Theo mặc định, tùy chọn sắp xếp được đặt thành Tăng dần (A đến Z) theo:
- Vì vậy, Khu vực sẽ được sắp xếp theo thứ tự bảng chữ cái.
- Bảng mới được sắp xếp sẽ trông giống như thế này.
- Nhưng chúng tôi không chỉ có thể Sắp xếp 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 tổng hợp bằng tổng số Số lượng sản phẩm ở mỗi Khu vực được sản xuất.
- Để thực hiện việc này, hãy chọn biểu tượng sắp xếp, sau đó trong Sắp xếp hộp thoại, chọn Tổng số lượng trong menu thả xuống.
- Sau đó, nhấp vào Tùy chọn khác ở dưới cùng bên phải của hộp thoại.
- Trong hộp thoại tiếp theo, nhấp vào Tổng số trong Sắp xếp theo quyền mua. Điều này có nghĩa là về cơ bản chúng tôi sẽ Sắp xếp bảng Số lượng từ nhỏ nhất đến lớn nhất. Nhấp vào OK sau cái này.
- Bây giờ bạn sẽ quay lại hộp thoại trước đó. Nhấp vào OK trên hộp đó nữa.
- Sau khi nhấp vào OK, Bạn sẽ nhận thấy rằng Bảng tổng hợp hiện được sắp xếp theo Số lượng nhỏ nhất của một sản phẩm với Số lượng lớn nhất của sản phẩm.
- Tính năng này giúp ích rất nhiều trong khi phân tích bảng, vì bảng đã được sắp xếp khá ngăn nắp. Chúng tôi không chỉ có số lượng Sản phẩm sắp xếp theo từng Vùng nhưng chúng cũng có thể được Sắp xếp để chúng tôi có thể đánh giá Khu vực nào đã tạo ra nhiều sản phẩm dễ dàng hơn.
Và đây là cách chúng tôi phân tích dữ liệu bằng cách Sắp xếp chúng trong Bảng tổng hợp 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ả)
Bài đọc tương tự
- Cách Phân tích Dữ liệu Quy mô Likert trong Excel (với các Bước Nhanh)
- Phân tích Dữ liệu Định tính từ Bảng câu hỏi trong Excel
- Cách phân tích dữ liệu theo tỷ lệ 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 trong Pivot Table
A Slicer là một công cụ tiện lợi để lọc ra các thông tin cần thiết một cách hiệu quả.
Các bước
- Trong bước tiếp theo, chúng tôi có thể thêm một vài công cụ cắt vào Bảng tổng hợp.
- Để thêm bộ cắt, hãy nhấp vào Chèn bộ cắt lệnh từ PivotTable Phân tích tab.
- After clicking the Insert Slicer command, there will be a new dialog box named Insert Slicers.
- On that dialog box, select the slicers that you are going to add by tick marking the checkboxes. In this case, we checked the Category , Region , and Rating boxes.
- After finishing the ticked marking, click on OK.
- Right after clicking on the OK, there will be 3 different slicers spawned in the worksheet. Each one for every field and the entries in these fields.
- Click on Asia and South Asia in the first slicer. This will eliminate other entries which have Regions other than Asia and South Asia.
- Then select the Electronics and the Home Appliances.
- This will select only products only in that two Category.
- Then choose Ratings 4 and 4.5 .
- the table is going to update regularly as we click on Slicer .
- Finally, we got different products and their different performance parameter only from the Asia and the South Asia Vùng s. The list is further filtered by the only two Category of products. Then we proceed to choose only 4 and 4.5 Ratings . That is how we narrow down products according to performance parameters.
And this is how we analyze the data by adding the slicers in the Pivot tables trong Excel.
6. Analyzing Data with PivotChart
We can further enhance our analysis by showing our Pivot Table with some charts. For instance, we can use the stacked bar chart with dual-axis to visualize the Sum of Revenue and the Profit Margin with respect to the Regions.
Các bước
- We need to visualize how the Profit Margin earned by each Region is related to the revenue generated by them. Are they in proportion to each other, or do they act inversely?
- In order to do this, first of all, we need to go to the Insert tab and then click on the PivotChart. Then from the drop-down menu click on the PivotChart.
- There will be a new dialog box named Insert Chart, where you can choose your favorite types of charts.
- From that option, we choose the Combo option, as we need to show two separate values on the vertical axis.
- In that menu, for the Sum of Revenue , choose the chart type as Clustered Column from the dropdown menu.
- Then for the Average of Profit Margin, choose the Line chart type. And also set this chart as the secondary axis by ticking the box right side of the dropdown menu.
- After clicking OK, we will notice that the new chart is now present. With the two axes, The left side one iis for the Sum of Revenue and the right side one is for the Profit Margin.
From the chart, we can see that the Profit Margin and the Sum of the Revenue are not exactly proportional here. Higher revenue doesn’t mean that the Profit Margin would be higher accordingly.
- The charts have filters by default in it. For example, you can see the Category filter already set in the top left corner of the chart. Click on it and see that all the products Category are now selected.
- If you want to select some Categories and ignore the rest of them, select your desired one and then click OK. For example, we choose Furniture and Home Appliances và sau đó nhấp vào OK.
- After clicking OK, you will notice that the chart now shows only the Furniture and the Home Appliances các giá trị. And only 4 Regions actually have that Category sản phẩm. That is why the chart is now shrunken and showing only the 4 Region data.
This is how we can analyze and visualize the Pivot Tables in Excel and then narrow down the data using the filter option in the chart.
7. Updating Data in Existing Pivot Table
Updating the data is considered to be routine work in any kind of data analysis dự án. Suppose we got some new raw sales data , as shown in the highlighted part of the table below. Now creating a new Pivot Table from the scratch can be a tedious thing to do. To resolve this, we will update the data source internally by keeping the original Pivot Table structure intact.
Các bước
- In the beginning, from the Pivot Table Analysis tab, click on the Change Data Source command.
- Then from the drop-down menu, click on the Change Data Source.
- Then in the Move PivotTable range box, select the full range of the updated table.
- After selecting the full range, you are going to see that the full range is now added to the existing PivotTable , and it’s updated also.
Đọ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. Getting Top 10 Values from Pivot Table
You can extract the top 10 or 20 values in a dataset, using the Sort icon on the top left corner.
Các bước
- In order to do this, first, click on the dropdown icon on top of the Row Levels.
- Then from the drop-down menu, click on the Value Filters> Top 10.
- Ther is a new dialog box whose name is Top 10 Filter (Product Id).
- In the first dropdown box, select whether you want the top 10 value or the bottom 10 giá trị. We choose Top.
- From that window, select whether you want to select the top 10 or 20. We choose 10.
- Then choose Items from the next dropdown box.
- Then choose based on which column are you going to filter the top 10 các giá trị. We select Average of Profit Margin.
- Click OK sau cái này.
After clicking OK, you will notice that the Top 10 values according to the Average of the Profit Margin đang hiển thị.
And this is how we analyze the data by trimming the top 10 data in the Pivot tables in Excel.
9. Grouping Data in Pivot Table
In the Pivot Table , it is also possible to group data altogether.
Các bước
- In order to do this, select multiple values by pressing Ctrl , and then right-click on the mouse.
- Then from the context menu, select Group .
- After clicking the Group command, we can see the Product ID which is now together.
- We can also group data by Region . For example, we can group the whole Asia Region under one group. And then group others as the rest of the world group
- To do this, first, create the group as shown before.
- Then select the cell and press F2.
- Doing this will make the cell enter into editing mode.
- Then we enter the group name “Rest of the World ”.
- Repeat the same process for Group 2 .
- Here we will enter Asia Region, as the name of the group.
- Finally, they all together will look like this.
And this is how we analyze the data by grouping them in the Pivot tables in Excel.
Kết luận
To sum it up, the question “how to analyze data in excel using pivot tables ” is answered here with 11 different processes. Those processes can make any kind of data analysis project seamless with the Pivot Table.
For this problem, a workbook is available for download where you can practice these methods.
Vui lòng đặt bất kỳ câu hỏi hoặc phản hồi thông qua phần bình luận. Any suggestion for the betterment of the ExcelDemy cộng đồng sẽ được đánh giá 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 điển hình bằ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 Thích hợp)
- Cách chuyển đổi dữ liệu định tính thành dữ liệu định lượng trong Excel