Tóm lược
Để hiển thị danh sách tên, được xếp hạng theo giá trị số, bạn có thể sử dụng tập hợp công thức dựa trên LARGE, INDEX, MATCH, với sự trợ giúp từ hàm TEXT. Trong ví dụ được hiển thị, công thức trong G5 là:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Và công thức trong G10 là:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
trong đó khách hàng (B5: B17) ngày (C5: C17) và số tiền (C5: C17) là các phạm vi được đặt tên.
Lưu ý: đây là các công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Giải trình
Ví dụ này được thiết lập thành hai phần để rõ ràng: (1) công thức để xác định 3 số tiền hàng tháng và (2) công thức để truy xuất tên khách hàng cho mỗi trong số 3 số tiền hàng tháng cao nhất.
Lưu ý rằng không có thứ hạng thực tế trong dữ liệu nguồn. Thay vào đó, chúng tôi đang sử dụng hàm LARGE để làm việc trực tiếp với số tiền. Một cách tiếp cận khác là thêm thứ hạng vào dữ liệu nguồn bằng hàm RANK và sử dụng giá trị thứ hạng để truy xuất tên khách hàng.
Phần 1: truy xuất 3 số tiền hàng tháng
Để truy xuất 3 số tiền hàng đầu cho mỗi tuần, công thức trong G5 là:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Làm việc từ trong ra ngoài, trước tiên chúng tôi sử dụng hàm TEXT để lấy tên tháng cho mỗi ngày trong phạm vi ngày đã đặt tên :
TEXT(date,"mmmm") // get month names
Định dạng số tùy chỉnh "mmmm" sẽ trả về một chuỗi như "Tháng 4", "Tháng 5", "Tháng 6" cho mỗi tên trong phạm vi ngày được đặt tên . Kết quả là một mảng tên tháng như sau:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Hàm TEXT phân phối mảng này đến hàm IF, được định cấu hình để lọc ngày vào một tháng nhất định bằng cách kiểm tra tên tháng so với giá trị trong G4 (một tham chiếu hỗn hợp, vì vậy công thức có thể được sao chép xuống và xuyên qua):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Chỉ số tiền trong tháng Tư tồn tại và vượt qua IF; tất cả các giá trị khác là FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Cuối cùng, hàm LARGE sử dụng giá trị trong F5 (cũng là một tham chiếu hỗn hợp) để trả về giá trị lớn nhất "thứ n" còn lại. Trong ô G5, LARGE trả về 18.500, giá trị lớn nhất "đầu tiên". Khi công thức được sao chép xuống và trên bảng, hàm LARGE trả về 3 số tiền hàng đầu trong mỗi ba tháng.
Bây giờ chúng ta đã biết 3 giá trị hàng đầu trong mỗi tháng, chúng ta có thể sử dụng thông tin này như một "chìa khóa" để truy xuất tên khách hàng cho mỗi giá trị.
Phần 2: lấy tên khách hàng
Lưu ý: Đây là một ví dụ về việc sử dụng INDEX và MATCH với nhiều tiêu chí. Nếu khái niệm này là mới với bạn, đây là một ví dụ cơ bản.
Để truy xuất tên được liên kết với ba giá trị hàng đầu trong G5: I7, chúng tôi sử dụng INDEX và MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Làm việc từ trong ra ngoài, hàm MATCH được cấu hình để sử dụng logic Boolean như sau:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Giá trị tra cứu là 1 và mảng tra cứu được xây dựng với biểu thức sau:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Biểu thức tạo mảng tra cứu sử dụng logic Boolean để "lọc ra" số lượng (1) không có trong tháng 4 và (2) không phải giá trị trong G5 (18.500). Kết quả là một mảng 1 và 0 như thế này:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Với giá trị tra cứu là 1 và 0 cho loại đối sánh (để bắt buộc đối sánh chính xác) MATCH trả về 3 trực tiếp cho hàm INDEX:
=INDEX(client,3) // returns "Janus"
INDEX trả về giá trị thứ ba trong ứng dụng khách phạm vi được đặt tên, "Janus".
Khi công thức được sao chép xuống và trên bảng, công thức trả về 3 khách hàng hàng đầu trong mỗi ba tháng.
