4 công thức tính tổng giá trị N hàng đầu trong Excel

Posted on: 2020年11月13日 2020年11月13日
Tags: summing

Nếu bạn muốn tính tổng 3 giá trị trong Excel, bạn có thể thực hiện bằng cách tính 3 giá trị hàng đầu bằng cách sử dụng hàm LARGE và tính tổng chúng.

Nhưng đó không phải là cách duy nhất. Bạn có thể tính tổng N giá trị hàng đầu trong Excel bằng nhiều công thức. Tất cả chúng sẽ sử dụng hàm LARGE làm hàm chính. Xem hình trên. Hãy cùng khám phá tất cả.

Tính tổng của 3 giá trị hàng đầu hoặc hàng đầu N sử dụng hàm SUMIF

Công thức chung

=SUMIF(range,”>=”&LARGE(range,n))

Phạm vi: * Phạm vi là phạm vi mà bạn muốn tính tổng N giá trị hàng đầu.

n: Đây là số giá trị hàng đầu mà bạn muốn tính tổng. Nếu bạn muốn tính tổng 3 giá trị hàng đầu thìn * là 3.

Vì vậy, hãy xem công thức này hoạt động.

Ví dụ 1: Sử dụng SUMIF để tính tổng 3 doanh thu hàng đầu. Chúng tôi có dữ liệu chứa doanh số bán hàng được thực hiện trong các tháng khác nhau. Chúng ta cần tổng hợp 3 doanh thu hàng đầu. Chúng tôi sử dụng công thức chung ở trên.

Phạm vi là C2: C13 vàn là 3. Sau đó, công thức sẽ là:

=SUMIF(C2:C13,”>=”&LARGE(C2:C13,3))

Điều này trả về 696020 là chính xác. Nếu bạn muốn tính tổng các giá trị N của biến hàng đầu và N được viết trong D2 thì công thức sẽ là:

=SUMIF(C2:C13,”>=”&LARGE(C2:C13,D2))

nó sẽ tổng hợp tất cả các giá trị lớn hơn hoặc bằng giá trị của giá trị N hàng đầu.

Nó hoạt động như thế nào? Hàm LARGE trả về giá trị thứ N hàng đầu trong phạm vi đã cho. Trong công thức trên, hàm LARGE trả về giá trị lớn thứ ba trong phạm viC2: C13 * là212940. Bây giờ công thức là:

=SUMIF(C2:C13,”>=”&212940)

Bây giờ hàm SUMIF chỉ đơn giản trả về SUM của các giá trị lớn hơn hoặc bằng212940, là 696020.

Nếu bạn muốn thêm nhiều điều kiện để tính tổng 3 hoặc N giá trị hàng đầu, hãy sử dụng hàm SUMIFS.

Tính tổng các giá trị Top 3 hoặc Top N Sử dụng hàm SUM Phương pháp trên khá dễ sử dụng và nhanh chóng khi cần tính tổng 3 hoặc N giá trị hàng đầu từ danh sách. Nhưng thật khó khăn khi nói đến tính tổng các giá trị N tùy ý hàng đầu, chẳng hạn như giá trị hàng đầu thứ 2, 4 và 6 từ một phạm vi Excel. Tại đây, hàm SUM hoặc SUMPRODUCT sẽ hoàn thành công việc.

Công thức chung:

=SUM(LARGE(range,\{1,2,3}))

hoặc

=SUMPRODUCT(LARGE(range,\{1,2,3}))

Phạm vi: * Phạm vi là phạm vi mà bạn muốn tính tổng N giá trị hàng đầu.

\ {1,2,3}: * Đây là những giá trị hàng đầu mà bạn muốn tính tổng. Đây là thứ 1, thứ 2 và thứ 3.

Vì vậy, hãy sử dụng công thức này trong ví dụ trên.

Ví dụ 2: Tính tổng các giá trị thứ nhất, thứ 2 và thứ 3 từ một Dải ô tô Excel. Vì vậy, chúng ta đang sử dụng cùng một bảng mà chúng ta đã sử dụng trong ví dụ trên. Phạm vi vẫn là C2: C13 và 3 giá trị hàng đầu mà chúng tôi muốn tính tổng là các giá trị lớn nhất thứ 1, thứ 2 và thứ 3 trong phạm vi.

=SUMPRODUCT(LARGE(C2:C13,\{1,2,3}))

Nó trả về kết quả giống như trước đó, là 696020. Nếu bạn muốn tính tổng các giá trị trên cùng thứ 2 và thứ 4 thì công thức sẽ là:

=SUMPRODUCT(LARGE(C2:C13,\{2,4}))

Và kết quả sẽ là425360.

Bạn có thể thay thế hàm SUMPRODUCT bằng hàm SUM trong trường hợp trên. Nhưng nếu bạn muốn cung cấp tham chiếu cho các giá trị hàng đầu thì bạn sẽ cần phải sử dụng CTRL + SHIF + ENTER để biến nó thành công thức mảng.

\{=SUM(LARGE(C2:C13,E2:E4))}

Nó hoạt động như thế nào?

Hàm LARGE chấp nhận một mảng các giá trị khóa và trả về một mảng các giá trị hàng đầu đó. Ở đây, hàm lớn trả về các giá trị \ {241540; 183820; 38740} dưới dạng các giá trị thứ 2, 4 và 6 hàng đầu. Bây giờ công thức trở thành:

=SUM(\{241540;183820;38740})

hoặc

=SUMPRODUCT(\{241540;183820;38740})

Điều này tổng hợp các giá trị được trả về bởi hàm LARGE.

Tổng hợp một loạt các giá trị hàng đầu trong Excel Giả sử bạn muốn tính tổng các giá trị các giá trị 10-2o hàng đầu, bao gồm tất cả. Trong trường hợp này, các phương pháp nêu trên sẽ trở nên cầu kỳ và lâu hơn. Cách tốt nhất là sử dụng công thức chung dưới đây để tổng hợp các giá trị hàng đầu:

=SUMPRODUCT(LARGE(range,ROW(INDIRECT(“start:end”))))

range: * Phạm vi là phạm vi mà bạn muốn tính tổng N giá trị hàng đầu.

“start: end”: * Đây là văn bản của phạm vi các giá trị hàng đầu mà bạn muốn tính tổng. Nếu bạn muốn một số giá trị từ 3 đến 6 đầu thì start: end sẽ là “3: 6”.

Hãy xem công thức này đang hoạt động:

Ví dụ 3: Tính tổng 3 đến 6 giá trị hàng đầu từ doanh số bán hàng:

Vì vậy, chúng ta có cùng một bảng như ví dụ trên. Truy vấn duy nhất là khác nhau. Sử dụng công thức chung ở trên, chúng tôi viết công thức này:

=SUMPRODUCT(LARGE(C2:C13,ROW(INDIRECT(“3:6”))))

Điều này trả về giá trị534040. Đó là tổng của giá trị lớn nhất thứ 3, 4, 5 và 6 trong phạm vi C2: C13.

Nó hoạt động như thế nào?

Đầu tiên, hàm INDIRECT biến văn bản “3: 6” thành phạm vi thực tế là 3: 6.

Về sau, hàm ROW trả về một mảng số hàng trong phạm vi 3: 6, sẽ là \ {3,4,5,6}. Tiếp theo, hàm LARGE trả về giá trị lớn thứ 3, 4, 5 và 6 từ phạm vi C2: C13. Cuối cùng, hàm SUMPRODUCT tổng hợp các giá trị này và chúng tôi nhận được kết quả.

=SUMPRODUCT(LARGE(C2:C13,ROW(INDIRECT(“3:6”))))

=SUMPRODUCT(LARGE(C2:C13,\{3,4,5,6}))*

=SUMPRODUCT(\{212940;183820;98540;38740})

=534040

Phần thưởng: Tính tổng N giá trị hàng đầu bằng cách sử dụng hàm SEQUENCE Hàm SEQUENCE được giới thiệu trong Excel 2019 và 365. Nó trả về một chuỗi số. Chúng ta có thể sử dụng nó để lấy N giá trị hàng đầu và sau đó tổng hợp chúng lại.

Công thức chung:

=SUMPRODUCT(LARGE(range,SEQUENCE(num_values,,[start_num], [steps]))))

range: * Phạm vi là phạm vi mà bạn muốn tính tổng N giá trị hàng đầu.

num_values: Là số giá trị hàng đầu mà bạn muốn tính tổng.

[start_num]: * Đây là số bắt đầu của chuỗi. Nó là tùy chọn. Nếu bỏ phần này, bộ truyện sẽ bắt đầu từ 1.

[bước]: Là sự khác biệt giữa số tiếp theo với số hiện tại. Theo mặc định, nó là 1.

Nếu chúng ta sử dụng công thức chung này để nhận được kết quả giống như chúng ta đã làm trong ví dụ trước, công thức sẽ là:

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))

Nó sẽ trả về giá trị534040.

Nó hoạt động như thế nào?

Nó đơn giản. Hàm SEQUENCE trả về một chuỗi 4 giá trị bắt đầu bằng 3 với khoảng 1. Nó trả về mảng \ {3; 4; 5; 6}. Bây giờ hàm LARGE trả về giá trị lớn nhất của các số tương ứng. Cuối cùng, hàm SUM tổng hợp các giá trị này và chúng tôi nhận được kết quả là 534040.

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))*

=SUM(LARGE(C2:C13,\{3;4;5;6}))*

=SUM(\{212940;183820;98540;38740})*

=534040

Vì vậy, các bạn, đây là cách bạn có thể tính tổng các giá trị 3, 5, 10, … N hàng đầu trong Excel. Tôi đã cố gắng giải thích. Tôi hy vọng nó sẽ giúp bạn. Nếu bạn có bất kỳ nghi ngờ nào về chủ đề này hoặc bất kỳ chủ đề nào khác liên quan đến VBA excel, hãy hỏi trong phần nhận xét bên dưới. Tôi trả lời các câu hỏi thường xuyên.

Bài viết liên quan:

khi chúng ta cần lấy tổng của 5 hoặc n số cuối cùng từ cột.

Bạn có thể thực hiện giải pháp cho vấn đề này một cách dễ dàng bằng cách sử dụng các hàm excel.

Bài viết phổ biến:

liên kết: / công thức-và-chức-năng-giới-thiệu-của-vlookup-chức-năng [Excel]

Bạn không cần phải lọc dữ liệu của mình để tính giá trị cụ thể. Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.

Bài viết phổ biến:

liên kết: / công thức-và-chức-năng-giới-thiệu-của-vlookup-chức-năng [Excel]

Bạn không cần phải lọc dữ liệu của mình để tính giá trị cụ thể. Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.