Công thức chung
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Tóm lược
Để sắp xếp động dữ liệu với cả số và văn bản theo thứ tự bảng chữ cái, bạn có thể sử dụng công thức để tạo thứ hạng số trong cột trợ giúp, sau đó sử dụng INDEX và MATCH để hiển thị các giá trị dựa trên thứ hạng. Trong ví dụ được hiển thị, công thức ở C5 là:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
trong đó "dữ liệu" là phạm vi được đặt tên B5: B13.
Giải trình
Công thức này trước tiên tạo ra một giá trị xếp hạng bằng cách sử dụng một biểu thức dựa trên COUNTIF:
=COUNTIF(data,"<="&B5)
được giải thích chi tiết hơn ở đây. Nếu dữ liệu chứa tất cả các giá trị văn bản hoặc tất cả các giá trị số, thì xếp hạng sẽ đúng. Tuy nhiên, nếu dữ liệu bao gồm cả văn bản và số, chúng ta cần "thay đổi" thứ hạng của tất cả các giá trị văn bản để tính cho các giá trị số. Điều này được thực hiện với phần thứ hai của công thức ở đây:
+(COUNT(data)*ISTEXT(B7))
Ở đây, chúng ta sử dụng hàm COUNT để lấy tổng số các giá trị số trong dữ liệu, sau đó nhân kết quả với kết quả logic của ISTEXT, hàm này sẽ kiểm tra xem giá trị có phải là văn bản hay không và trả về TRUE hoặc FALSE. Điều này có hiệu quả loại bỏ kết quả COUNT khi chúng tôi đang làm việc với một số trong hàng hiện tại.
Xử lý các bản sao
Nếu dữ liệu có chứa trùng lặp, công thức có thể được thay đổi như được hiển thị bên dưới để gán thứ hạng tuần tự cho các giá trị xuất hiện nhiều lần:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Phiên bản này điều chỉnh logic của hàm COUNTIF ban đầu và thêm một COUNTIF khác với tham chiếu mở rộng để tăng các bản sao.
Hiển thị các giá trị được sắp xếp
Để truy xuất và hiển thị các giá trị các giá trị được sắp xếp theo thứ tự bảng chữ cái sử dụng giá trị xếp hạng được tính toán, E5 chứa công thức INDEX và MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
trong đó "dữ liệu" là phạm vi được đặt tên B5: B13 và "xếp hạng" là phạm vi được đặt tên C5: C13.
Để biết thêm thông tin về cách hoạt động của công thức này, hãy xem ví dụ ở đây.
Xử lý khoảng trống
Các ô trống sẽ tạo ra một thứ hạng bằng không. Giả sử bạn muốn bỏ qua các ô trống, điều này hoạt động tốt vì công thức INDEX và MATCH ở trên bắt đầu bằng 1. Tuy nhiên, bạn sẽ thấy lỗi # N / A ở cuối các giá trị đã sắp xếp, một lỗi cho mỗi ô trống. Một cách dễ dàng để xử lý điều này là đặt công thức INDEX và MATCH trong IFERROR như sau:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
