Top 10 trong bảng tổng hợp năm 2023


Nội dung

  • Bài toán 1: Lập báo cáo doanh thu bán hàng của mỗi nhân viên bằng cách sử dụng PivotTable.
  • Bài toán 2: Sắp xếp doanh thu bán hàng của các nhân viên từ cao xuống thấp dựa vào báo cáo doanh thu của Bài toán 1
  • Bài toán 3: Dựa vào báo cáo doanh thu của Bài toán 1, lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất để trao thưởng.
  • Bài toán 4: Lập báo cáo doanh thu bán hàng theo tháng, dựa vào báo cáo của Bài toán 1?

Bài toán 1: Lập báo cáo doanh thu bán hàng của mỗi nhân viên bằng cách sử dụng PivotTable.

Giả sử dữ liệu bán hàng của các nhân viên như hình bên dưới:

Top 10 trong bảng tổng hợp năm 2023

Để sử dụng PivotTable lập báo cáo doanh thu bán hàng của mỗi nhân viên các bạn thực hiện như sau:

Bước 1: Xác định các cột dữ liệu liên quan đến báo cáo

  • Doanh thu bán hàng của nhân viên dựa vào cột Số tiền để tính
  • Đối tượng báo cáo ở đây là cột Nhân viên

Bước 2: Tiến hành lập báo cáo bằng PivotTable

1. Chọn bảng hoặc ô (bao gồm các tiêu đề cột) có chứa dữ liệu bạn muốn sử dụng.

Top 10 trong bảng tổng hợp năm 2023

2. Vào thẻ Insert -> chọn PivotTable trong nhóm Tables

Top 10 trong bảng tổng hợp năm 2023

3. Hộp thoại Create PivotTable xuất hiện, bạn chọn các tùy chọn sau:

Top 10 trong bảng tổng hợp năm 2023

  • Khung Table/Range: chứa địa chỉ bảng dữ liệu là Table1, có thể chọn lại nếu chưa đúng.
  • Tích chọn Use an external data source nếu sử dụng nguồn dữ liệu bên ngoài Excel, ví dụ các tập tin cơ sở dữ liệu hay XML…
  • Chọn New Worksheet để đặt PivotTable trong trang tính mới (nếu các bạn muốn báo cáo xuất hiện trong trang tính hiện tại hãy chọn Existing Worksheet, sau đó chọn một ô để đặt PivotTable).

4. Nhấp chọn OK và giao diện xây dựng PivotTable sẽ xuất hiện trên một bảng tính mới như sau:

Top 10 trong bảng tổng hợp năm 2023

5. Trong PivotTable Fields hãy chọn các cột liên quan đến báo cáo. Vì chúng ta muốn biết tổng số tiền mỗi nhân viên bán hàng bán được là bao nhiêu nên chúng ta sẽ chọn trường Số tiềnNhân viên bán hàng. Sau khi chọn trường Nhân viên bán hàng được thêm vào khu vực Rows, còn trường Số tiền được thêm vào khu vực Values. Ngoài ra, bạn có thể nhấp, giữ và kéo một trường đến khu vực mong muốn.

Top 10 trong bảng tổng hợp năm 2023

6. PivotTable sẽ tính toán và hiển thị doanh thu bán hàng của mỗi nhân viên như sau:

Trong báo cáo, đổi tên các trường dữ liệu:

A3: Row Labels –> Nhân viên bán hàng

B3: Sum of Số tiền –> Doanh Thu

A12: Grand Total –> Tổng doanh thu

Top 10 trong bảng tổng hợp năm 2023

Bài toán 2: Sắp xếp doanh thu bán hàng của các nhân viên từ cao xuống thấp dựa vào báo cáo doanh thu của Bài toán 1

1. Sau khi có được báo cáo doanh thu bán hàng của các nhân viên, bạn nhấp chuột chọn một ô bất kỳ trong cột Doanh thu (không nhấp chọn tiêu đề cột)

2. Vào thẻ Data -> chọn Sort Largest to Smallest trong nhóm Sort & Filter

3. Thu được kết quả là bảng doanh thu bán hàng đã được sắp xếp theo thứ tự doanh thu từ cao xuống thấp của các nhân viên

Top 10 trong bảng tổng hợp năm 2023

Bài toán 3: Dựa vào báo cáo doanh thu của Bài toán 1, lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất để trao thưởng.

Để tiến hành lọc ra TOP 3 nhân viên có doanh thu bán hàng cao nhất các bạn thực hiện các thao tác sau:

1. Trong cột Nhân viên bán hàng (ô A3), tại vị trí nút lọc (hình mũi tên trỏ xuống) -> nhấp chọn Value Filters -> chọn Top 10…

Top 10 trong bảng tổng hợp năm 2023

2. Hộp thoại Top 10 Filter xuất hiện, hiệu chỉnh số 10 thành số 3 và nhấp chọn OK (nếu muốn hiển thị TOP 5 thì bạn chỉnh số 10 thành số 5, còn muốn để TOP 10 thì dữ nguyên, …)

Top 10 trong bảng tổng hợp năm 2023

3. Kết quả thu được sau khi lọc là TOP 3, nếu muốn sắp xếp thì nhấp chọn Sort Largest to Smallest trong nhóm Sort & Filter của thẻ Data. Xem hình minh họa

Top 10 trong bảng tổng hợp năm 2023

Chú ý: Nếu bạn thay đổi bất kỳ dữ liệu nào trong bảng nguồn của mình, PivotTable sẽ không tự động cập nhật. Để cập nhật, bạn nhấp chọn PivotTable -> thẻ ngữ cảnh PivotTable Tools xuất hiện -> chọn tab Analyze -> chọn Refresh trong nhóm Data.

Bài toán 4: Lập báo cáo doanh thu bán hàng theo tháng, dựa vào báo cáo của Bài toán 1?

1. Yêu cầu của bài toán là lập doanh thu bán hàng theo tháng nên đối tượng báo cáo sẽ là Tháng.

Tại bảng PivotTable Fields của Bài toán 1 ta bỏ chọn trường Nhân viên và tích chọn trường Tháng.

Top 10 trong bảng tổng hợp năm 2023

2. PivotTable sẽ tự động điều chỉnh hoặc xoay trục để hiển thị dữ liệu mới. Bạn sẽ thu được báo cáo doanh thu bán hàng theo tháng như hình minh họa:

Cột Row Labels (ô A3) được đổi tên thành Tháng

Cột Sum of Order Amount (ô B3) đổi tên thành Doanh thu

Cột Grand Total (ô A7) đổi tên thành Tổng doanh thu

Top 10 trong bảng tổng hợp năm 2023

Top 10 trong bảng tổng hợp năm 2023

Image: muchomor, Getty Images/iStockphoto

In a previous TechRepublic article, How to highlight the top n values in a Microsoft Excel sheet, I explain two different conditional formatting methods to highlight the top n values in a data set in Microsoft Excel. Either is reasonable when you want to view all of the data. When you want to see only the top n records—filtering out all other records—you’ll need a different strategy.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

LEARN MORE: Office 365 Consumer pricing and features

Fortunately, Microsoft Excel has a built-in filter for PivotTables that will let you display the top (or bottom) n record. In this article, you’ll create a simple PivotTable and then use the built-in filter to display only the top 10 records in the data source. Then, we’ll discuss some problems with the results and possible solutions. It’s this latter section of the article that might provide the most new material for some of you.

I’m using (desktop) Office 365, but you can use earlier versions of the ribbon format. You can work with your own data or download the demonstration .xlsx file (this article isn’t applicable to the older .xls version). The browser edition will display the PivotTable, but you can’t run code in the browser.

How to build a PivotTable in Excel

This section is a tutorial on building a PivotTable. If you know how to build one, you can skip this section. To build the demonstration PivotTable using the data set in Figure A, do the following:

  1. Click anywhere inside the data set and then click the Insert tab. In the Tables group, click PivotTable.
  2. In the resulting dialog, click OK; we don’t need to change any default settings.
  3. Click inside the PivotTable frame. In the PivotTable field list pane, drag Product Name to the Rows section. Drag Unit Price to the Values section (Figure B). The frame will update accordingly as you add fields.

Figure A

Top 10 trong bảng tổng hợp năm 2023
How to build the demonstration PivotTable.

Figure B

Top 10 trong bảng tổng hợp năm 2023
The demonstration PivotTable has only two columns—the product and unit price.

By default, the PivotTable sums the unit price values by products, but our list of products is unique, so none of the values change. To format the unit price column, right-click the header cell and choose Number Format from the resulting submenu. (I chose Currency.)

As is, the PivotTable displays all the records. Let’s look at our first method for displaying the top n unit price values.

How to use the built-in option

Applying a filter is easy, and there are lots of options. The downside is they’re not dynamic, but we’ll discuss that in a bit. Now, let’s apply that filter:

  1. Click the Row Labels dropdown filter.
  2. Choose Value Filters (remember, we’re evaluating the unit price values, not the products), and then choose Top 10 (Figure C). There’s no dropdown for the unit price column because it’s a values column. There are lots of options, so you’ll want to explore those later.
  3. The resulting dialog has several options, but the default settings (Figure D) are perfect for our example: display the top 10 items in the Sum of Unit Price column.
  4. Click OK to see the results in Figure E.

Figure C

Top 10 trong bảng tổng hợp năm 2023
Choose the Top 10 filter.

Figure D

Top 10 trong bảng tổng hợp năm 2023
Apply settings for the Top 10 filter.

Figure E

Top 10 trong bảng tổng hợp năm 2023
The PivotTable displays only the top 10 unit price values.

You could use the resulting PivotTable as is, but there are a few issues you might want to consider first. Let’s take a look next.

Fixing a few small problems

Now, let’s tweak the results a bit. In this case, the totaling row at the bottom isn’t meaningful, so you can turn it off. To do so, click the contextual Design tab. In the Layout group, click the Grand Totals dropdown and choose Off for Rows and Columns.

Excel sorts by the row label—products—so it can group and sum multiple values correctly. In this case, it’s unnecessary and the product sort isn’t particularly meaningful. Most likely, you’ll want to sort by the unit price. To do so, right-click any unit price cell, choose Sort, and then choose Sort Largest to Smallest. The header text isn’t descriptive. To change it, simply click inside the cell and replace the default headers–or not. If you decide to hide the dropdown arrow, you can skip this step.

Most likely, you’ll not want viewers to change the filter. There are a few complex methods for doing so, but the easiest is to remove the filter dropdown. To do so, click anywhere inside the PivotTable and click the contextual PivotTable Analyze tab. Then, click the Options dropdown in the PivotTable group (to the far left), and choose Options. In the resulting dialog, click the Display tab and uncheck the Display field caption and filter drop downs option. Doing so will inhibit both the header text and the dropdown, which isn’t really a problem. Simply hide the row and add the appropriate text or title above the PivotTable. (This option isn’t used in the demonstration file.)

Nếu bạn không quen thuộc với PivotTables, thì đây là một hành vi quan trọng mà bạn cần nhớ: họ không tự động cập nhật.Nếu bạn thay đổi một giá trị trong dữ liệu nguồn sẽ ảnh hưởng đến 10 pivottable, pivottable sẽ không hiển thị thay đổi đó cho đến khi bạn làm mới nó.Nhưng có sự giúp đỡ, nếu không phải là một giải pháp hoàn hảo.

Một cách giải quyết không quá động

Để cập nhật một pivottable, nhấp chuột phải vào bất kỳ ô và chọn làm mới.Người xem đã giành chiến thắng để biết điều này, vì vậy màn hình có thể đứng sau các bản cập nhật.Giải pháp đơn giản nhất là buộc Excel cập nhật PivotTables khi ai đó mở sổ làm việc.Bạn có thể làm như vậy, như sau:

  1. Nhấp vào bất cứ nơi nào bên trong PivotTable và sau đó nhấp vào tab Phân tích PivotTable theo ngữ cảnh.
  2. Trong nhóm PivotTable (ở phía xa bên trái), nhấp vào các tùy chọn thả xuống (trong hộp tên có thể pivotT) và chọn các tùy chọn từ danh sách thả xuống.
  3. Trong hộp thoại kết quả, nhấp vào tab Dữ liệu.
  4. Kiểm tra dữ liệu làm mới khi mở tùy chọn tệp (Hình F).Figure F).
  5. Bấm OK.

Hình f

Top 10 trong bảng tổng hợp năm 2023
Excel có thể làm mới một pivottable khi bạn mở tệp.

Chọn tùy chọn này đảm bảo rằng người xem thấy thông tin cập nhật nhất, nhưng họ phải mở tệp, do đó, nó không phải là một sửa chữa hoàn hảo cho vấn đề.(Tùy chọn này được đặt trong tệp trình diễn để bạn có thể tự mình trải nghiệm hành vi.)Listing A.

Liệt kê a

Sub Worksheet_Deactivate ()

ThisWorkbook.RefreshAll

Kết thúc phụ

Thêm quy trình này vào mô -đun trang tính được gắn vào nguồn dữ liệu.Khi bạn cập nhật dữ liệu và sau đó rời khỏi bảng đó, phương thức hủy kích hoạt một phương thức làm mới.Giải pháp dễ dàng này đi kèm với một vài hạn chế.Đầu tiên, PivotTable và nguồn dữ liệu phải nằm trên các bảng khác nhau.Ngoài ra, phương pháp làm mới đều làm mới mọi thứ: tất cả các pivottables, truy vấn, v.v.Nếu bạn muốn giới hạn việc làm mới cho PivotTable, hãy sử dụng phương pháp làm mới sau: Thay vào đó:

SheetName.PivOTTABLES (

Hãy chắc chắn cập nhật SheetName và PivottableName phù hợp khi đăng ký vào công việc của riêng bạn.Tệp trình diễn chứa mã, nhưng nó đã nhận xét.Khi áp dụng mã cho công việc của riêng bạn, hãy sao chép từ trang web này vì Trình soạn thảo Visual Basic (VBE) sẽ không thể biên dịch các ký tự chưa từng thấy.Tự mình nhập mã hoặc sao chép mã vào Word (hoặc trình soạn thảo văn bản khác) và sau đó sao chép mã từ đó vào VBE.Ngoài ra, nếu bạn thêm mã vào sổ làm việc của mình, hãy chắc chắn lưu tệp dưới dạng sổ làm việc hỗ trợ macro (.xlsm).

Gửi cho tôi câu hỏi Microsoft Office của bạn

Tôi trả lời độc giả Câu hỏi khi tôi có thể, nhưng không có gì đảm bảo.Don lồng gửi các tập tin trừ khi được yêu cầu;Yêu cầu ban đầu để được trợ giúp đến với các tệp đính kèm sẽ bị xóa chưa đọc.Bạn có thể gửi ảnh chụp màn hình dữ liệu của mình để giúp làm rõ câu hỏi của bạn.Khi liên hệ với tôi, hãy cụ thể nhất có thể.Ví dụ:, xin vui lòng khắc phục sự cố sổ làm việc của tôi và sửa chữa những gì mà sai có lẽ đã giành được một câu trả lời, nhưng bạn có thể cho tôi biết tại sao công thức này lại không trả lại kết quả mong đợi?có thể.Vui lòng đề cập đến ứng dụng và phiên bản mà bạn sử dụng.Tôi không được TechRepublic hoàn trả cho thời gian hoặc chuyên môn của mình khi giúp đỡ độc giả, tôi cũng không yêu cầu một khoản phí từ độc giả tôi giúp đỡ.Bạn có thể liên hệ với tôi tại.

Làm cách nào để nhận được 10 kỷ lục top trong Excel?

Nhấp vào mũi tên bên phải của hộp thả xuống ID và chọn Bộ lọc giá trị> Top 10 từ menu bật lên.Khi cửa sổ Top 10 bộ lọc (ID thứ tự) xuất hiện, chọn trên cùng, 10, các mục và tổng số lượng trong các lần thả xuống tương ứng.Sau đó nhấp vào nút OK.

Làm cách nào để hiển thị top 3 trong một bảng xoay?

Summary..
Tạo một bảng trục mới trên cùng một bảng tính ..
Thêm trường tên vào khu vực hàng ..
Thêm trường điểm vào khu vực giá trị ..
Đổi tên trường Điểm từ "tổng số điểm" thành "điểm" (ghi chú không gian theo dõi).
Các giá trị lọc để hiển thị "3 mục hàng đầu theo điểm".
Đặt sắp xếp thành "giảm dần theo điểm".

Các bảng xoay có thể hiển thị top 5 không?

Bạn có thể dễ dàng tạo bảng Pivot trong Excel để tóm tắt dữ liệu của mình và sử dụng tùy chọn Bộ lọc để lấy các giá trị X trên/dưới từ dữ liệu.Bạn có thể sử dụng bộ lọc được xây dựng để hiển thị bảng Pivot Top 5 theo giá trị bán hàng.You can use the in-built filter to display the pivot table top 5 by sales value.