Công thức chung
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Tóm lược
Để tính tổng n giá trị hàng đầu trong một tiêu chí đối sánh phạm vi, bạn có thể sử dụng công thức dựa trên hàm LARGE, được bao bọc bên trong hàm SUMPRODUCT. Ở dạng chung của công thức (ở trên), dải ô biểu thị một dải ô được so sánh với tiêu chí , giá trị biểu thị giá trị số mà từ đó các giá trị hàng đầu được truy xuất và N biểu thị ý tưởng của giá trị thứ N.
Trong ví dụ, ô hiện hoạt chứa công thức này:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Trong đó màu là phạm vi được đặt tên là B5: B12 và giá trị là phạm vi được đặt tên là C5: C12.
Giải trình
Ở dạng đơn giản nhất, LARGE trả về giá trị "lớn thứ N" trong một dải ô có cấu trúc này:
=LARGE (range,N)
Ví dụ:
=LARGE (C5:C12,2)
sẽ trả về giá trị lớn thứ 2 trong phạm vi C5: C12, là 12 trong ví dụ được hiển thị.
Tuy nhiên, nếu bạn cung cấp "hằng số mảng" (ví dụ: hằng số ở dạng (1,2,3)) thành LARGE làm đối số thứ hai, LARGE sẽ trả về một mảng kết quả thay vì một kết quả duy nhất. Vì vậy, công thức:
=LARGE (C5:C12, (1,2,3))
sẽ trả về giá trị lớn nhất thứ 1, thứ 2 và thứ 3 là C5: C12 trong một mảng như sau: (12,12,10)
Vì vậy, mẹo ở đây là lọc các giá trị dựa trên màu sắc trước khi LARGE chạy. Chúng tôi làm điều này với biểu thức:
(color=E5)
Dẫn đến một mảng các giá trị TRUE / FALSE. Trong quá trình nhân, các giá trị này bị ép buộc thành các giá trị đơn và không:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Vì vậy, kết quả cuối cùng là chỉ các giá trị được liên kết với màu "đỏ" tồn tại trong hoạt động:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
và các giá trị khác buộc phải bằng không.
Lưu ý: công thức này sẽ không xử lý văn bản trong phạm vi giá trị. Xem bên dưới.
Xử lý văn bản trong các giá trị
Nếu bạn có văn bản ở bất kỳ đâu trong các phạm vi giá trị, hàm LARGE sẽ xuất hiện lỗi #VALUE và dừng công thức hoạt động.
Để xử lý văn bản trong phạm vi giá trị, bạn có thể thêm hàm IFERROR như sau:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Ở đây, chúng tôi bẫy lỗi từ LỚN do các giá trị văn bản gây ra và thay thế bằng 0. Sử dụng IF bên trong LARGE yêu cầu nhập công thức bằng control + shift + enter, vì vậy chúng tôi chuyển sang SUM thay vì SUMPRODUCT.
Lưu ý: Tôi tình cờ gặp công thức này được đăng bởi Barry Houdini tuyệt vời trên stackoverflow.
