Công thức chung
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Tóm lược
Để tính tổng n giá trị dưới cùng trong một tiêu chí đối sánh phạm vi, bạn có thể sử dụng công thức mảng dựa trên hàm SMALL, được bao bọc bên trong hàm SUM. Ở dạng chung của công thức (ở trên), range1 biểu thị phạm vi ô so với tiêu chí , range2 chứa các giá trị số mà từ đó các giá trị dưới cùng được truy xuất và N đại diện cho "thứ n".
Trong ví dụ, ô hiện hoạt chứa công thức này:
=SUM(SMALL(IF(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.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Ở dạng đơn giản nhất, SMALL trả về giá trị "nhỏ nhất thứ N" trong một phạm vi có cấu trúc này:
=SMALL (range,N)
Ví dụ:
=SMALL (C5:C12,2)
sẽ trả về giá trị nhỏ thứ 2 trong phạm vi C5: C12, là 5 trong ví dụ được hiển thị.
Tuy nhiên, nếu bạn cung cấp một "hằng số mảng" (ví dụ một hằng số ở dạng (1,2,3)) thành SMALL làm đối số thứ hai, thì SMALL 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:
=SMALL (C5:C12, (1,2,3))
sẽ trả về giá trị nhỏ nhất thứ 1, thứ 2 và thứ 3 là C5: C12 trong một mảng như sau: (4,5,7).
Vì vậy, mẹo ở đây là lọc các giá trị dựa trên màu sắc trước khi chạy NHỎ. Chúng tôi làm điều này với một biểu thức dựa trên hàm IF:
IF(color=E5,value)
Điều này xây dựng mảng giá trị được đưa vào SMALL. Về cơ bản, chỉ các giá trị được liên kết với màu đỏ mới đưa nó vào mảng. Trong đó màu bằng "đỏ", mảng chứa một số và nơi màu không phải là màu đỏ, mảng chứa FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Hàm SMALL bỏ qua các giá trị FALSE và trả về 3 giá trị nhỏ nhất trong mảng: (4,8,10). Hàm SUM trả về kết quả cuối cùng, 22.
