Christian có một phạm vi gồm 18 ô chứa các giá trị. Anh ta muốn tìm tổng của 12 giá trị lớn nhất trong phạm vi đó và tự hỏi làm thế nào để hoàn thành nhiệm vụ.

Có một số cách bạn có thể tiếp cận vấn đề này. Ví dụ: bạn có thể lọc các giá trị để bạn chỉ có mười hai giá trị hàng đầu và sau đó tính tổng các giá trị đó. Các cách tiếp cận khác liên quan đến việc sử dụng các cột bổ sung để lưu trữ các giá trị trung gian, nhưng tôi giả sử rằng bạn sẽ thích một cách tiếp cận không sử dụng các cột bổ sung.

Để bắt đầu, hãy giả sử rằng phạm vi 18 ô của bạn là A1: A18. Bạn có thể sử dụng hàm LARGE để tìm các giá trị lớn nhất. Ví dụ: sử dụng công thức này sẽ tìm giá trị lớn thứ hai trong phạm vi:

=LARGE(A1:A18,2)

Đây là tham số thứ hai của hàm chỉ định giá trị lớn nhất, theo thứ tự, bạn muốn. Do đó, bạn có thể tìm ra tổng của 12 giá trị lớn nhất bằng cách sử dụng công thức như sau:

=LARGE(A1:A18,1)+LARGE(A1:A18,2)+LARGE(A1:A18,3)+LARGE(A1:A18,4)

+LARGE(A1:A18,5)+LARGE(A1:A18,6)+LARGE(A1:A18,7)+LARGE(A1:A18,8)

+LARGE(A1:A18,9)+LARGE(A1:A18,10)+LARGE(A1:A18,11)+LARGE(A1:A18,12)

Tuy nhiên, có những công thức ngắn hơn bạn có thể sử dụng để hoàn thành nhiệm vụ.

Ví dụ: bạn có thể chỉ cần trừ sáu giá trị nhỏ nhất khỏi tổng của phạm vi, theo cách này:

=SUM(A1:A18)-SMALL(A1:A18,1)-SMALL(A1:A18,2)-SMALL(A1:A18,3)

-SMALL(A1:A18,4)-SMALL(A1:A18,5)-SMALL(A1:A18,6)

Bạn cũng có thể, nếu muốn, sử dụng hàm SUMIF để so sánh các giá trị và tính tổng chúng chỉ khi đáp ứng tiêu chí bạn chỉ định. Ví dụ, hãy xem xét hai công thức sau:

=SUMIF(A1:A18,">="&LARGE(A1:A18,12))

=SUMIF(A1:A18,">"&SMALL(A1:A18,6))

Công thức đầu tiên sẽ tính tổng tất cả các giá trị lớn hơn hoặc bằng giá trị lớn nhất thứ mười hai trong phạm vi. Thứ hai có hiệu lực tương tự; nó tính tổng tất cả các giá trị lớn hơn giá trị nhỏ nhất thứ sáu.

Hai công thức này hoạt động tốt nếu không có giá trị trùng lặp nào ở “ranh giới” được thiết lập. Tuy nhiên, nếu có nhiều hơn một giá trị đủ điều kiện là giá trị lớn nhất thứ mười hai hoặc giá trị nhỏ nhất thứ sáu, thì công thức sẽ không trả về số tiền bạn mong đợi. Trong công thức đầu tiên, tổng sẽ quá lớn (vì tất cả các giá trị trùng lặp được thêm vào tổng) và công thức thứ hai, tổng sẽ quá nhỏ (vì tất cả các giá trị trùng lặp đều bị loại trừ khỏi tổng).

Cách giải quyết vấn đề này là quay lại một trong các công thức trước đó (những công thức không sử dụng SUMIF) hoặc sửa đổi công thức SUMIF để có thể tính đến khả năng trùng lặp các giá trị:

=SUMIF(A1:A18,">"&LARGE(A1:A18,12))+LARGE(A1:A18,12)

* (12-COUNTIF(A1:A18,">"&LARGE(A1:A18,12)))

Có lẽ công thức tốt nhất để có được kết quả mong muốn thay đổi cách sử dụng hàm LARGE trước đây:

=SUM(LARGE(A1:A18,{1,2,3,4,5,6,7,8,9,10,11,12}))

Công thức này sử dụng một mảng (phần bên trong dấu ngoặc nhọn), nhưng nó không phải là một công thức mảng. Những gì nó làm là sử dụng mảng làm tham số thứ hai của hàm LARGE, do đó trả về tất cả mười hai giá trị lớn nhất. Sau đó, chúng được tổng hợp và trả về một giá trị duy nhất.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9420) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: