Công thức chung
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Tóm lược
Để tính tổng các giá trị hàng đầu trong một 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), rng đại diện cho một dải ô chứa các giá trị số và N đại diện cho ý 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(B4:B13,(1,2,3)))
Giải trình
Ở dạng đơn giản nhất, LARGE sẽ trả về giá trị "lớn thứ N" trong một phạm vi. Ví dụ, công thức:
=LARGE(B4:B13, 2)
sẽ trả về giá trị lớn thứ 2 trong phạm vi B4: B13, trong ví dụ trên, là số 9.
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(B4:B13,(1,2,3))
sẽ trả về giá trị lớn nhất thứ 1, thứ 2 và thứ 3 trong phạm vi B4: B13. Trong ví dụ trên, trong đó B4: B13 chứa các số từ 1-10, kết quả từ LARGE sẽ là mảng (8,9,10). Sau đó SUMPRODUCT tính tổng các số trong mảng này và trả về tổng số là 27.
SUM thay vì SUMPRODUCT
SUMPRODUCT là một hàm linh hoạt cho phép bạn sử dụng tham chiếu ô cho k bên trong hàm LARGE.
Tuy nhiên, nếu bạn đang sử dụng một hằng số mảng được mã hóa cứng đơn giản như (1,2,3), bạn chỉ có thể sử dụng hàm SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Lưu ý rằng bạn phải nhập công thức này dưới dạng công thức mảng nếu bạn sử dụng tham chiếu ô chứ không phải hằng số mảng cho k bên trong LARGE.
Khi N trở nên lớn
Khi N trở nên lớn, việc tạo hằng số mảng bằng tay sẽ trở nên tẻ nhạt - Nếu bạn muốn tính tổng đến 20 hoặc 30 giá trị hàng đầu trong một danh sách lớn, việc gõ hằng số mảng có 20 hoặc 30 mục sẽ mất nhiều thời gian. Trong trường hợp này, bạn có thể sử dụng một phím tắt để xây dựng hằng số mảng sử dụng các hàm ROW và INDIRECT.
Ví dụ: nếu bạn muốn TỔNG 20 giá trị hàng đầu trong một phạm vi được gọi là "rng", bạn có thể viết công thức như sau:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Biến N
Với dữ liệu không đủ, N cố định có thể gây ra lỗi. Trong trường hợp này, bạn có thể thử một công thức như sau:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Ở đây, chúng tôi sử dụng MIN với COUNT để tính tổng 3 giá trị hàng đầu hoặc tổng số giá trị, nếu nhỏ hơn 3.
