Công thức chung
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Tóm lược
Để sắp xếp động dữ liệu chỉ chứa các giá trị số, bạn có thể sử dụng cột trợ giúp và công thức được tạo bằng hàm RANK và COUNTIF. Trong ví dụ được hiển thị, công thức trong D5 là:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
trong đó "doanh số" là phạm vi được đặt tên C5: C11.
Giải trình
Lưu ý: công thức này là thiết lập cho một công thức có thể trích xuất và hiển thị dữ liệu bằng cách sử dụng thứ tự sắp xếp được xác định trước trong cột trợ giúp. Một ví dụ ở đây.
Cốt lõi của công thức này là hàm RANK, được sử dụng để tạo thứ hạng giá trị bán hàng, trong đó số cao nhất được xếp hạng # 1:
=RANK(C5,sales)
Ở đây, RANK sử dụng phạm vi được đặt tên là "doanh số bán hàng" (C5: C11) để thuận tiện. Theo mặc định, RANK sẽ gán 1 cho giá trị cao nhất, 2 cho giá trị cao thứ hai, v.v. Điều này hoạt động hoàn hảo miễn là các giá trị số là duy nhất. Tuy nhiên, để xử lý các giá trị số có chứa trùng lặp, chúng ta cần sử dụng hàm COUNTIF để phá vỡ mối quan hệ. Điều này được thực hiện bằng cách thêm kết quả của đoạn mã này vào giá trị được trả về bởi RANK:
COUNTIF($C$5:C5,C5)-1
Lưu ý rằng phạm vi được nhập dưới dạng tham chiếu hỗn hợp sẽ mở rộng khi công thức được sao chép xuống bảng. Như đã viết, tham chiếu này sẽ bao gồm hàng hiện tại, vì vậy chúng tôi trừ 1 để "không xuất hiện" lần xuất hiện đầu tiên. Điều này có nghĩa là biểu thức sẽ trả về 0 cho mỗi giá trị số cho đến khi gặp một bản sao. Ở trường hợp thứ hai, biểu thức sẽ trả về 1, ở trường hợp thứ ba, nó sẽ trả về 2, v.v. Điều này phá vỡ mối quan hệ một cách hiệu quả và cho phép công thức tạo ra một danh sách liên tiếp các số không có khoảng trống.
Khi công thức đã có, dữ liệu có thể được sắp xếp theo cột trợ giúp. Nó cũng có thể được truy xuất bằng INDEX bằng cách sử dụng các giá trị trong cột trợ giúp.
Lưu ý: Công thức này được phỏng theo một ví dụ trong cuốn sách xuất sắc Control + Shift + Enter của Mike Girvin.
