Công thức chung
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Tóm lược
Để sắp xếp động và trích xuất các giá trị duy nhất từ danh sách dữ liệu, bạn có thể sử dụng công thức mảng để thiết lập thứ hạng trong cột trợ giúp, sau đó sử dụng công thức INDEX và MATCH được xây dựng đặc biệt để trích xuất các giá trị duy nhất. Trong ví dụ được hiển thị, công thức để thiết lập thứ hạng trong C5: C13 là:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
trong đó "dữ liệu" là phạm vi được đặt tên B5: B13.
Lưu ý: đây là công thức mảng nhiều ô, được nhập bằng control + shift + enter.
Giải trình
Lưu ý: ý tưởng cốt lõi của 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.
Ví dụ được hiển thị sử dụng một số công thức, được mô tả bên dưới. Ở cấp độ cao, hàm MMULT được sử dụng để tính xếp hạng số trong cột trợ giúp (cột C) và xếp hạng này sau đó được sử dụng bởi công thức INDEX và MATCH trong cột G để trích xuất các giá trị duy nhất.
Xếp hạng giá trị dữ liệu
Hàm MMULT thực hiện phép nhân ma trận và được sử dụng để gán thứ hạng số cho mỗi giá trị. Mảng đầu tiên được tạo với biểu thức sau:
--(data>TRANSPOSE(data))
Ở đây, chúng ta sử dụng hàm TRANSPOSE để tạo một mảng dữ liệu ngang và tất cả các giá trị được so sánh với nhau. Về bản chất, mỗi giá trị được so sánh với mọi giá trị khác để trả lời câu hỏi "giá trị này có lớn hơn mọi giá trị khác không". Điều này dẫn đến một mảng hai chiều, 9 cột x 9 hàng, chứa đầy các giá trị TRUE và FALSE. Dấu kép (-) được sử dụng để ép buộc các giá trị TRUE FALSE thành 1s và số không. Bạn có thể hình dung mảng kết quả như sau:
Ma trận của 1s và số không trên trở thành array1 bên trong hàm MMULT. Array2 được tạo với biểu thức này:
ROW(data)^0
Ở đây, mỗi số hàng trong "dữ liệu" được nâng lên bằng 0 để tạo mảng một chiều, 1 cột x 9 hàng, được lấp đầy bằng số 1. Sau đó, MMULT trả về tích ma trận của hai mảng, trở thành giá trị nhìn thấy trong cột xếp hạng.
Chúng tôi nhận lại tất cả 9 thứ hạng cùng một lúc trong một mảng, vì vậy chúng tôi cần đặt kết quả vào các ô khác nhau cùng một lúc. Nếu không, mỗi ô sẽ chỉ hiển thị giá trị xếp hạng đầu tiên trong mảng được trả về.
Lưu ý: đây là công thức mảng nhiều ô, được nhập bằng control + shift + enter, trong phạm vi C5: C13.
Xử lý các ô trống
Các ô trống được xử lý bằng phần này của công thức xếp hạng:
=IF(data="",ROWS(data)
Ở đây, trước khi chúng tôi chạy MMULT, chúng tôi kiểm tra xem ô hiện tại trong "dữ liệu" có trống không. Nếu vậy, chúng tôi chỉ định một giá trị xếp hạng bằng với số hàng trong dữ liệu. Điều này được thực hiện để buộc các ô trống ở cuối danh sách, nơi chúng có thể dễ dàng bị loại trừ sau này khi các giá trị duy nhất được trích xuất (giải thích bên dưới).
Đếm các giá trị duy nhất
Để đếm các giá trị duy nhất trong dữ liệu, công thức trong E5 là:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Vì công thức xếp hạng ở trên chỉ định thứ hạng số cho mỗi giá trị, chúng ta có thể sử dụng hàm FREQUENCY với SUM để đếm các giá trị duy nhất. Công thức này được giải thích chi tiết ở đây. Sau đó, chúng tôi trừ 1 từ kết quả nếu có bất kỳ ô trống nào trong dữ liệu:
-(blank>0)
trong đó "trống" là phạm vi được đặt tên E8 và chứa công thức này:
=COUNTBLANK(data)
Về cơ bản, chúng tôi giảm số lượng duy nhất đi một nếu có các ô trống trong dữ liệu, vì chúng tôi không bao gồm các ô này trong kết quả. Số lượng duy nhất trong ô E5 được đặt tên là "duy nhất" (dành cho số lượng duy nhất) và được sử dụng bởi công thức INDEX và MATCH để lọc ra các ô trống (mô tả bên dưới).
Trích xuất các giá trị duy nhất
Để trích xuất các giá trị duy nhất, G5 chứa công thức sau, được sao chép xuống:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Trước khi chúng tôi chạy công thức INDEX và MATCH, trước tiên chúng tôi kiểm tra xem số lượng hàng hiện tại trong khu vực trích xuất có lớn hơn số lượng duy nhất trong phạm vi được đặt tên "unique" (E5) hay không:
=IF(ROWS($G$5:G5)>unique,"",
Nếu vậy, chúng ta đã hoàn tất việc trích xuất các giá trị duy nhất và chúng ta trả về một chuỗi rỗng (""). Nếu không, chúng tôi chạy công thức trích xuất:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Lưu ý rằng có hai hàm MATCH ở đây, một hàm bên trong hàm kia. MATCH bên trong sử dụng phạm vi mở rộng cho một mảng và phạm vi được đặt tên là "dữ liệu" cho giá trị tra cứu:
MATCH(data,$G$4:G4,0)
Lưu ý rằng phạm vi mở rộng bắt đầu ở "hàng trên", hàng 4 trong ví dụ. Kết quả từ MATCH bên trong là một mảng, đối với mỗi giá trị trong dữ liệu, chứa một vị trí số (giá trị đã được trích xuất) hoặc lỗi # N / A (giá trị chưa được trích xuất). Sau đó, chúng tôi sử dụng IF và ISNA để lọc các kết quả này và trả về giá trị xếp hạng cho tất cả các giá trị trong "dữ liệu" chưa được trích xuất:
IF(ISNA(results),rank))
Hoạt động này dẫn đến một mảng, được đưa vào hàm MIN để nhận "giá trị xếp hạng tối thiểu" cho các giá trị dữ liệu chưa được trích xuất. Hàm MIN trả về giá trị này cho MATCH bên ngoài dưới dạng giá trị tra cứu và phạm vi được đặt tên "xếp hạng" dưới dạng mảng:
MATCH(min_not_extracted,rank)),rank,0)
Cuối cùng, MATCH trả về vị trí của giá trị xếp hạng thấp nhất cho INDEX dưới dạng số hàng và INDEX trả về giá trị dữ liệu trong hàng hiện tại của phạm vi trích xuất.
