Công thức chung
=COUNTIF(range,"<="&A1)
Tóm lược
Để sắp xếp động các giá trị văn bản theo thứ tự bảng chữ cái, bạn có thể sử dụng công thức dựa trên hàm COUNTIF. Trong ví dụ được hiển thị, công thức trong C5 là:
=COUNTIF(countries,"<="&B5)
trong đó "quốc gia" là phạm vi được đặt tên B4: B13
Giải trình
Công thức này sử dụng toán tử "lớn hơn hoặc bằng" với văn bản, điều mà bạn có thể chưa thử trước đây. Khi Excel so sánh văn bản, nó sẽ quyết định giá trị nào "lớn hơn" so với các quy tắc dựa trên khác tuân theo sơ đồ đánh số ASCII.
Bên trong COUNTIF, đối số phạm vi được cung cấp dưới dạng phạm vi được đặt tên là "quốc gia" (B4: B13) và tiêu chí được cung cấp là "nhỏ hơn hoặc bằng" giá trị trong C5. Trong mỗi hàng, COUNTIFS trả về số lượng giá trị nhỏ hơn hoặc bằng giá trị hiện tại, tạo danh sách liên tiếp các số (tức là xếp hạng) trong cột trợ giúp.
Liệt kê các giá trị được sắp xếp
Cột trợ giúp có thể được sử dụng để lấy các giá trị được sắp xếp theo thứ hạng. Trong E5, công thức được sử dụng để truy xuất giá trị là:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
Đây là công thức INDEX và MATCH sử dụng tham chiếu mở rộng để tạo các số tuần tự, được đưa vào MATCH dưới dạng giá trị tra cứu. MATCH tìm ra vị trí tồn tại của mỗi số trong dữ liệu và INDEX truy xuất giá trị tại vị trí đó. Xem trang này để được giải thích chi tiết hơn.
Xử lý các bản sao
Nếu dữ liệu chứa các giá trị văn bản trùng lặp, chuỗi số sắp xếp cũng sẽ chứa các bản sao, điều này sẽ gây ra sự cố nếu bạn đang cố truy xuất các giá trị bằng hàm INDEX. Để khắc phục sự cố này, bạn có thể sử dụng một biến thể của công thức tăng dần số trùng lặp với COUNTIF thứ hai:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Lưu ý rằng toán tử logic trong hàm COUNTIF đầu tiên đã được điều chỉnh và phạm vi trong hàm COUNTIF thứ hai là một tham chiếu mở rộng.
