Cách thêm cột xếp hạng vào Pivot Tables trong Excel

5/7/2020

Sau khi sử dụng Pivottables để phân tích dữ liệu, đôi khi bạn sẽ muốn xếp hạng các dữ liệu đã được pivottables nhằm mục đích so sánh. Bài viết dưới đây sẽ hướng dẫn cách bạn thêm cột xếp hạng vào Pivottables trong Excel.

Cách 1 Thêm cột Rank trong Pivottables

• Phiên bản Excel 2010 trở lên
• Phiên bản Excel 2007 hoặc cũ hơn

Cách 2 Sử dụng RANK.EQ VÀ RANK.AVG trong Source Data

Cách 3 Thêm một cột RANK riêng biệt trong Pivot Table

 

 

 

Cách 1 Thêm cột Rank trong Pivottables

Đối với phiên bản Excel 2010 trở lên

• Thêm trường giá trị bạn cần xếp hạng 2 lần trong pivot table

• Chọn ô bất kỳ từ cột dữ liệu thứ 2 và click chuột phải.
• Đi đến “Show Values As”. Bạn có thể tùy chọn 2 cách xếp hạng “Rank Smallest To Largest” (từ thấp đến cao) hoặc “Rank Largest To Smallest” (từ cao đến thấp)

• Cột thứ 2 sẽ được chuyển thành dạng dữ liệu xếp hạng.

​Lưu ý: khi sử dụng phương pháp này, thứ hạng sẽ thay đổi khi bạn lọc (filter) pivot table.

Đối với phiên bản Excel 2007 hoặc cũ hơn

• Sắp xếp dữ liệu theo thứ tự tăng dần mà bạn muốn tính thứ hạng.

• Mở hộp thoại calculated field và nhấn “=1” tại ô formula.

• Một trường mới sẽ được thêm vào pivot tables có giá là 1 tại tất cả các ô.

• Chọn ô bất kỳ tại trường vừa được thêm vào và click chuột phải.
• Chọn “Value Field”
• Tại hộp thoại value field setting đi đến tab “Show Values As”.
• Tại danh sách chọn, nhấn “Running Total In” và click OK.

• Bây giờ bạn đã có cột xếp hạng trong pivottables.

Lưu ý: Bạn cần đảm bảo là đã sort (sắp xếp) dữ liệu tăng dần hoặc giảm dần trước khi thực hiện cách này.

 

 

 

Cách 2 Sử dụng RANK.EQ VÀ RANK.AVG trong Source Data

Phương pháp phức tạp hơn một chút nhưng bạn không cần phải thêm trường phụ vào pivottables.

Cách làm như sau:

• Trước tiên, tại dữ liệu nguồn thêm một cột và đánh hàm sau: =IF(COUNTIF(C$2:C2,C2)>1,””,SUMIFS($E$2:$E$1507,$C$2:$C$1507,C2))

Công thức hoạt động như sau:

Công thức này sẽ thêm một tổng số duy nhất cho mỗi thể loại trong cột. Và, bạn có thể sử dụng tổng số đó để tính thứ hạng cho mỗi danh mục.

• Tiếp tục thêm 2 cột nữa vào cuối dữ liệu nguồn và đánh hàm sau:.

=IF(H2=””,0,RANK.EQ(H2,$H$2:$H$1507,1))

=IF(H=””,0,RANK.AVG(H2,$H$2:$H$1507,1))

Công thức hoạt động như sau:

Lúc này hàm RANK.EQ & RANK.AVG sẽ tính thứ hạng cho danh mục trong kết xuất dữ liệu của bạn. Chúng tôi đã sử dụng cả hai chức năng này để có được loại xếp hạng khác nhau.

• Tiếp theo, bạn tạo pivot table từ dữ liệu này, pivot tables tạo ra như hình bên dưới.

Lưu ý: Cách này sẽ không làm thay đổi thứ hạng khi lọc dữ liệu.

 

 

 

Cách 3 Thêm một cột RANK riêng biệt trong Pivot Table

Thay vì sử dụng các công thức hoặc bất kỳ phương pháp nào khác, bạn cũng có thể thêm một cột riêng bằng tay để xếp hạng.

Thao tác:

• Tạo pivot table và sort dữ liệu theo tăng dần hoặc giảm dần.

• Trong 2 cột tiếp theo, ngoài pivot table thêm dòng công thức sau:
  • =RANK.EQ(E4,$E$4:$E$15,1)
  • =RANK.AVG(E4,$E$4:$E$15,1)

• Bạn sẽ có kết quả như sau:

File thực hành: tải về