Xin chào các bạn! Hôm nay mình sẽ giới thiệu về cách tìm xếp hạng của một mục dựa trên vị trí của nó trong mỗi cột trong Google Sheets. Đề tài này có vẻ khá thú vị đúng không nào? Trước khi bắt đầu, mình sẽ cho các bạn hiểu cơ bản về đề tài này.
Giả sử, mình đã cho ba người dùng/khách hàng một danh sách gồm tên của bốn món ăn và yêu cầu họ xếp hàng các món ăn đó.
Sau đó, mình đã chuẩn bị một bảng trong Google Sheets dựa trên phản hồi/xếp hạng của họ, như dưới đây:
Rank Tom Clara Michael
Bây giờ, mình muốn kiểm tra xếp hạng của một món ăn, ví dụ như “Maryland crabcakes”, trong mỗi cột trong bảng này trong Google Sheets.
Với số lượng mục chỉ là 4 và số lượng người dùng chỉ là 3, chúng ta có thể dễ dàng tìm thấy các xếp hạng một cách thủ công. Nó sẽ như sau:
Tom: 1
Clara: 2
Michael: 2
Chúng ta cũng có thể tự động hóa điều này với một công thức trong Google Sheets. Điều này giúp chúng ta tìm thấy xếp hạng của nhiều mục dựa trên vị trí của chúng trong mỗi cột trong Google Sheets.
Hãy nhìn vào hình ảnh dưới đây để hiểu rõ hơn về điều tôi đang nói. Dựa trên ví dụ của tôi, các mục thức ăn nằm trong ô F2:F5 và xếp hạng của chúng nằm trong ba cột kế tiếp.
Có quan tâm không? Hãy làm theo các bước dưới đây.
Các bước để tìm xếp hạng của mục trong mỗi cột trong Google Sheets
Có bốn bước (chính) liên quan đến việc tìm xếp hạng của các mục trong mỗi cột trong Google Sheets. Bạn có thể tìm hiểu chúng dưới các tiêu đề khác nhau sau đây.
Tôi sẽ bắt đầu viết công thức trong ô G2 và kết quả của nó sẽ mở rộng thành các cột và hàng kề. Trong mỗi bước, tôi sẽ sửa đổi công thức này để đạt được công thức cuối cùng.
1. So khớp một Mục trong Nhiều Cột trong Google Sheets
Chúng ta có thể sử dụng hàm REGEXMATCH để so khớp một mục trong nhiều cột trong Google Sheets.
Ví dụ: Nếu chúng ta sử dụng công thức REGEXMATCH sau đây trong danh sách trên, kết quả sẽ là một bảng với các giá trị TRUE | FALSE.
=MảngCôngThức(so khớp lệnh chính quy($B$2:$D$5,F2))
Công thức REGEXMATCH trả về TRUE, nơi mà mục “Maryland crabcakes” khớp trong bảng bên trái.
2. Tìm Vị trí của Mục trong Mỗi Cột trong Google Sheets
Để tìm xếp hạng của các mục trên trong mỗi cột, trước tiên chúng ta phải lấy được vị trí của chúng trong mỗi cột. Để làm điều đó, chúng ta có thể sử dụng một công thức kết hợp. Tất nhiên, công thức REGEXMATCH ở trên sẽ là một phần của nó.
Một số bạn có thể hỏi, tại sao tôi không sử dụng hàm MATCH để tìm vị trí. Câu trả lời là MATCH dành cho một cột hoặc hàng duy nhất.
Nếu chúng ta sử dụng MATCH, cho ba người dùng, chúng ta phải sử dụng ba công thức MATCH kết hợp như sau trong một ô duy nhất hoặc trong ba ô khác nhau. Dưới đây là công thức kết hợp cho sự tham khảo của bạn:
= {match($F$2,B2:B5,0),match($F$2,C2:C5,0),match($F$2,D2:D5,0)}
Chúng ta không thể dễ dàng sửa đổi công thức này hoặc không thể áp dụng nếu có nhiều người dùng/cột hơn. Vì vậy, tôi đang sử dụng một cách tiếp cận khác.
Tôi chỉ đơn giản chuyển các giá trị TRUE thành vị trí tương ứng. Để làm điều này, tôi sử dụng hàm ADDRESS như dưới đây trong Google Sheets.
=MảngCôngThức(nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),))
Bạn có thể thấy giá trị -1 được sử dụng trong công thức. Điều này bởi vì phạm vi bắt đầu từ hàng thứ hai. Nếu phạm vi là B3:D6 thay vì B2:D5, hãy thay đổi -1 thành -2.
Công thức ADDRESS trong trên thực chất trả về các địa chỉ ô của các giá trị TRUE tương ứng trong phạm vi.
Lệnh REGEXEXTRACT bên ngoài nó loại bỏ các chuỗi không cần thiết và chỉ trả về các vị trí liên quan trong các cột khác nhau. Bạn có thể tham khảo ảnh ở trên.
Kết hợp các Giá trị Ô Rời ra và Chia làm Một Hàng Đơn
Xếp hạng của món “Maryland crabcakes” trong mỗi cột là rời rạc, đúng không?
Chúng ta chỉ cần kết hợp chúng và chia nhỏ để tạo thành một hàng duy nhất. Trước đó, hãy chuyển đổi (thay đổi hướng dữ liệu) đầu ra của công thức trên bằng TRANSPOSE (chuyển đổi hàng thành cột). Tôi sẽ nói sau đây tại sao bước này là cần thiết.
=MảngCôngThức(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))
Sau bước này, hãy sử dụng hàm TEXTJOIN, kết hợp các số và sử dụng ký tự “|” như là ký tự phân tách.
=MảngCôngThức(textjoin("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))))
Kết quả trong ô G2: 1|4|4
Tiếp theo, chia nhỏ nó thành các cột và hoàn thành!
=MảngCôngThức(chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|")
Lý do sử dụng TRANSPOSE trước TEXTJOIN
Bạn có thể muốn biết tại sao tôi đã chuyển đổi các vị trí của mục trước TEXTJOIN. Đây là câu trả lời của tôi!
Trong bước tiếp theo, tôi sẽ kết hợp tên người dùng với đầu ra của công thức trên. (Tên người dùng) sẽ không khớp với các cột xếp hạng (đầu ra SPLIT) nếu không sử dụng chuyển đổi. Vẫn còn nghi ngờ, phải không?
Hãy xem ví dụ ‘mới’ dưới đây để biết cách TEXTJOIN xử lý các giá trị (đầu ra D1 và D2).
Nó trước tiên kết hợp các giá trị trong hàng đầu tiên (A1:B1) và sau đó hàng thứ hai (A2:B2). Bạn có thể thấy điều này trong ô D1.
Nhưng điều chúng ta muốn là kết hợp các giá trị trong cột đầu tiên (A1:A2) và sau đó cột thứ hai (B1:B2) theo kết quả trong ô D2. Vì chúng ta muốn gán tiêu đề cột (tên người dùng) cho các xếp hạng trong các cột tương ứng.
Ví dụ ‘mới’ trên chỉ để giúp bạn hiểu rõ hơn về hàm TEXTJOIN. Vì vậy, xin vui lòng bỏ qua nó trong hướng dẫn này.
3. Kết hợp Tiêu đề Cột với Xếp hạng của Một Mục trong Mỗi Cột
Chỉ đơn giản là kết hợp các tên người dùng (tiêu đề cột) với công thức trên (công thức ô G2) bằng dấu ngoặc nhọn (CURLY BRACES) được sử dụng để tạo ra các mảng ảo trong Google Sheets. Để thực hiện việc này, chỉnh sửa công thức ô G2 như sau:
=MảngCôngThức({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})
Kết quả sẽ là điều bạn đang tìm kiếm.
Chúng ta đã tìm thấy xếp hạng của món “Maryland crabcakes” trong mỗi cột trong Google Sheets. Nhưng nó có hai vấn đề. Đó là gì?
- Chúng ta chưa sắp xếp xếp hạng, mặc dù điều này không cần thiết cho mục này vì nó đã được sắp xếp theo thứ tự tăng dần.
- Đầu ra chiếm hai hàng và ba cột. Chúng ta muốn nó chỉ trong một hàng để chúng ta có thể kéo công thức xuống để tìm xếp hạng của nhiều mục trong mỗi cột trong Google Sheets.
4. Sắp xếp xếp hạng của mục theo thứ tự tăng dần
Chúng ta muốn sắp xếp xếp hạng, nằm trong hàng đầu của kết quả. Hàm SORT trong Google Sheets để sắp xếp các cột, không phải hàng. Vì vậy, trước tiên hãy chuyển đổi đầu ra trên và sau đó sắp xếp.
Công thức ở ô G2:
=sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1}))
Ghi chú: Xóa hàm ARRAYFORMULA vì nó không cần thiết với SORT.
Kết quả:
Dưới đây là các bước cuối cùng để tìm xếp hạng của một mục trong mỗi cột trong Google Sheets.
Bằng cách sử dụng hai công thức INDEX, chúng ta có thể chia nhỏ các cột trên thành hai phần – phần một là xếp hạng và phần hai là tên.
Xếp hạng:
=chỉ số(sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})),0,1)
Tên:
=chỉ số(sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})),0,2)
Kết hợp cả hai công thức này bằng cách đặt &”] “& trong giữa trong ô G2.
=MảngCôngThức(chuyển đổi(chỉ số(sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})),0,1)&"] "&chỉ số(sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})),0,2))
Sau đó, chuyển đổi lại thành Hàng.
=chuyển đổi(MảngCôngThức(chuyển đổi(chỉ số(sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})),0,1)&"] "&chỉ số(sắp xếp(chuyển đổi({chia(nối văn bản("|",đúng,(chuyển đổi(đúng nếu(So khớp lệnh chính quy($B$2:$D$5,F2)=TRUE,Trích xuất lệnh chính quy(địa chỉ(hàng($A$2:$A$5)-1,cột($B$2:$D$2)),"[0-9]+"),)))),"|");$B$1:$D$1})),0,2)))
Bạn có thể tham khảo hình ảnh dưới đây để xem kết quả (G2:I2).
Làm thế nào để Tìm Xếp hạng của Nhiều Mục Dựa trên Vị trí trong Mỗi Cột trong Google Sheets
Bước:
- Nhập tên các mục vào ô F2:F.
- Kéo công thức ở ô G2 xuống.
Ghi chú:
- Công thức trên dành cho phạm vi B2:D5. Để mở rộng số hàng (số mục), thay tất cả các vị trí của $B$2:$D$5 trong công thức bằng $B$2:$D$ và $A$2:$A$5 bằng $A$2:$A$. Vui lòng xóa các hàng không sử dụng (các hàng trống) khỏi bảng tính.
- Nếu có nhiều người dùng hơn, ví dụ: 4 người dùng, hãy thay tất cả các vị trí của $B$2:$D$2 trong công thức bằng $B$2:$E$2.
- Mục phải là duy nhất trong mỗi cột và phải xuất hiện trong mỗi cột. Ví dụ, nếu chúng ta muốn tìm xếp hạng của mục “Maryland crabcakes” trong mỗi cột, ví dụ: B đến D, nó phải xuất hiện trong mỗi cột và không được lặp lại.
Ghi chú về việc sử dụng
Công thức trên chỉ dành cho phạm vi B2:D5. Để mở rộng số hàng (số mục), hãy thay tất cả các vị trí của $B$2:$D$5 trong công thức bằng $B$2:$D$ và $A$2:$A$5 bằng $A$2:$A$. Hãy chắc chắn xóa bỏ các hàng không sử dụng (các hàng trống) khỏi bảng tính.
Nếu có nhiều người dùng hơn, ví dụ: 4 người dùng, hãy thay tất cả các vị trí của $B$2:$D$2 trong công thức bằng $B$2:$E$2.
Mục phải là duy nhất trong mỗi cột và phải xuất hiện trong mỗi cột. Ví dụ, nếu chúng ta muốn tìm xếp hạng của mục “Maryland crabcakes” trong mỗi cột, ví dụ: B đến D, nó phải xuất hiện trong mỗi cột và không được lặp lại.
Tài liệu tham khảo thêm về cùng chủ đề
- Cách tìm xếp hạng của một số không tồn tại trong một phạm vi dữ liệu đã có.
- Cách sử dụng hàm RANK trong Google Sheets – Công thức ví dụ.
- Công thức mảng linh hoạt để xếp hạng mà không có các mục trùng lặp trong Google Sheets.
- Cách sử dụng hàm RANK.AVG trong Google Sheets.
- Cách xếp hạng theo nhóm trong Google Sheets trong nhóm đã sắp xếp hoặc chưa sắp xếp.
- Xếp hạng 10 hàng đầu mà không có tên trùng lặp trong Google Sheets.
- Các hàm PERCENTRANK trong Google Sheets.
- Định dạng điều kiện phân vị trong Google Sheets.
- So sánh và làm nổi bật lên và xuống xếp hạng trong Google Sheets.
