Công thức chung
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Tóm lược
Để tính tổng n giá trị thấp nhất trong một phạm vi, bạn có thể sử dụng công thức dựa trên hàm SMALL và hàm SUMPRODUCT. Ở dạng chung của công thức (ở trên), rng đại diện cho một phạm vi ô chứa các giá trị số và n đại diện cho số giá trị thấp nhất để tính tổng. Trong ví dụ được hiển thị, E5 chứa công thức này:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
trả về tổng của ba giá trị nhỏ nhất trong B5: B14, 60
Giải trình
Ở dạng đơn giản nhất, SMALL sẽ trả về giá trị "nhỏ nhất thứ n" trong một phạm vi. Ví dụ:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
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í dụ:
=SMALL(A1:A10,(1,2,3))
sẽ trả về giá trị nhỏ nhất thứ 1, thứ 2 và thứ 3 trong phạm vi A1: A10.
Làm việc từ trong ra ngoài trong ví dụ được hiển thị, SMALL trả về 3 giá trị nhỏ nhất trong phạm vi B5: B14:
=SMALL(B4:B14,(1,2,3))
Kết quả là một mảng như thế này:
(10,20,30)
Mảng này được trả về trực tiếp cho hàm SUMPRODUCT, hàm này tính tổng các số và trả về tổng:
SUMPRODUCT((10,20,30)) // returns 60
Công thức mảng với SUM
Người ta thường sử dụng SUMPRODUCT như trên vì nó có thể xử lý mảng nguyên bản mà không cần nhập dưới dạng công thức mảng. Tuy nhiên, bạn cũng có thể viết công thức mảng với hàm SUM như sau:
(=SUM(SMALL(B4:B13,(1,2,3))))
Đâ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.
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 trở nên tẻ nhạt - 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ụ: để SUM 20 giá trị dưới cùng trong một phạm vi được gọi là "rng", bạn có thể viết một công thức như sau:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Ở đây, INDIRECT chuyển đổi chuỗi "1:20" thành phạm vi 1:20, được trả về trực tiếp thành SMALL.
Biến n
Để thiết lập công thức trong đó n là một biến trong một ô khác, bạn có thể nối bên trong INDIRECT. Ví dụ: nếu A1 chứa N, bạn có thể sử dụng:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Điều này cho phép người dùng thay đổi giá trị của n trực tiếp trên trang tính.
