Cách tính tổng các giá trị trên cùng hoặc dưới cùng N với tiêu chí

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

Trong bài trước chúng ta đã học cách tính tổng các giá trị N trên cùng hoặc dưới cùng.

Trong bài viết này, chúng tôi cố gắng tổng hợp các giá trị N trên cùng hoặc dưới cùng với một tiêu chí.

Tổng giá trị TOP N với tiêu chí

Làm thế nào để giải quyết vấn đề?

_Công thức chung: _

= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )

list: danh sách tiêu chí Tiêu chí: tiêu chí để đối sánh dải ô: dải ô giá trị: các số được phân tách bằng dấu phẩy như nếu bạn muốn tìm 3 giá trị hàng đầu, hãy sử dụng \ {1, 2, 3}.

Ví dụ:

Ở đây chúng tôi có các giá trị tập dữ liệu từ A1: D50.

Đầu tiên, chúng ta cần tìm năm giá trị hàng đầu bằng cách sử dụng hàm LARGE khớp với thành phố “Boston” và sau đó thực hiện phép tính tổng trên 5 giá trị đó. Bây giờ chúng ta sẽ sử dụng công thức sau để lấy tổng _Sử dụng công thức: _

= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )

_Quá trình: _

“Boston” của Thành phố phù hợp với phạm vi Thành phố đã đề cập. Điều này trả về một mảng true và false.

SUMPRODUCT \ {193, 149, 138, 134, 123} * Hàm SUMPRODUCT nhận một mảng 5 giá trị hàng đầu, với một mảng 5 số hàng đầu trả về SUM của các số đó.

Ở đây phạm vi Thành phố & số lượng được đưa ra là phạm vi đã đặt tên. Nhấn Enter để lấy SUM của 5 số hàng đầu.

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, tổng đó là 737. Tổng các giá trị 193 + 149 + 138 + 134 + 123 = 737.

Bạn có thể kiểm tra các giá trị trên trong tập dữ liệu bằng cách sử dụng tùy chọn bộ lọc excel.

Áp dụng bộ lọc cho tiêu đề Thành phố & số lượng và nhấp vào nút mũi tên trên tiêu đề thành phố xuất hiện. Làm theo các bước như hình dưới đây.

Các bước thực hiện:

Chọn ô tiêu đề Thành phố. Áp dụng bộ lọc bằng phím tắt Ctrl + Shift + L. Nhấp vào mũi tên xuất hiện dưới dạng tùy chọn bộ lọc.
Chọn tùy chọn (Chọn tất cả).
Chỉ chọn thành phố Boston.
Chọn tiêu đề số lượng ngay bây giờ.
Sắp xếp danh sách từ lớn nhất đến nhỏ nhất và bạn có thể xem tất cả 5 giá trị hàng đầu mà chúng tôi đã tính bằng công thức.

Như bạn có thể thấy trong gif ở trên, tất cả 5 giá trị phù hợp với các tiêu chí đã cho. Điều này cũng có nghĩa là công thức hoạt động tốt để đếm các giá trị này

số N LỚN

Quá trình trên được sử dụng để tính tổng của một vài số từ trên cùng. Nhưng để tính toán cho n (lớn) số giá trị trong một phạm vi dài.

Sử dụng công thức:

= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )

Ở đây, chúng tôi tạo ra tổng của 10 giá trị hàng đầu bằng cách nhận một mảng từ 1 đến 10 \ {1; 2; 3; 4 ; 5; 6; Số 7; số 8 ; Số 9; 10} bằng cách sử dụng các hàm ROW & INDIRECT trong Excel.

Ở đây chúng tôi có tổng hợp của 10 số hàng đầu cho kết quả là 1147.

Tổng giá trị N dưới cùng với tiêu chí

Làm thế nào để giải quyết vấn đề?

Công thức chung:

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }

Phạm vi: phạm vi giá trị Giá trị: các số được phân tách bằng dấu phẩy như nếu bạn muốn tìm 3 giá trị dưới cùng, hãy sử dụng \ {1, 2, 3}.

Ví dụ:

Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới.

Ở đây chúng tôi có một loạt các giá trị từ A1: D50.

Ở đây, phạm vi Thành phố & số lượng được đưa ra bằng cách sử dụng công cụ excel phạm vi được đặt tên.

Đầu tiên, chúng ta cần tìm năm giá trị dưới cùng bằng cách sử dụng hàm SMALL phù hợp với tiêu chí và sau đó phép tính tổng được thực hiện trên 5 giá trị đó. Bây giờ chúng ta sẽ sử dụng công thức sau để lấy tổng Sử dụng công thức:

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }

KHÔNG sử dụng dấu ngoặc nhọn theo cách thủ công. Áp dụng dấu ngoặc nhọn bằng cách sử dụng Ctrl Shift + Enter thay vì chỉ Enter.

Giải thích:

hàm với hàm IF trả về 5 giá trị số dưới cùng khớp với Thành phố “Boston” và trả về mảng cho hàm SUM.

SUM (\ {23, 27, 28, 28, 30}))

Hàm SUM nhận mảng 5 giá trị dưới cùng, mảng có 5 số dưới cùng trả về giá trị SUM của những số đó được sử dụng với CTRL + SHIFT + ENTER.

Ở đây phạm vi Thành phố & số lượng được đưa ra là phạm vi đã đặt tên. Nhấn Ctrl + Shift + Enter để lấy SUM của 5 số dưới cùng vì đây là công thức mảng.

Như bạn có thể thấy trong ảnh chụp ở trên, tổng là 136.

Quá trình trên được sử dụng để tính tổng của một vài số từ dưới cùng. Nhưng để tính toán cho n (lớn) số giá trị trong một phạm vi dài.

Sử dụng công thức:

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }

KHÔNG sử dụng dấu ngoặc nhọn theo cách thủ công. Sử dụng Ctrl + Shift + Enter thay vì sử dụng Enter.

Ở đây chúng tôi tạo tổng của 10 giá trị dưới cùng bằng cách nhận một mảng từ 1 đến 10 \ {1; 2; 3; 4 ; 5; 6; Số 7; số 8 ; Số 9; 10} bằng cách sử dụng các hàm ROW & INDIRECT trong Excel.

Ở đây chúng ta có tổng của 10 số dưới cùng sẽ cho kết quả là 155.

Dưới đây là một số lưu ý quan sát được hiển thị bên dưới.

Ghi chú:

Công thức chỉ hoạt động với số.
Công thức chỉ hoạt động khi không có bản sao trên bảng tra cứu. Hàm SUMPRODUCT coi các giá trị không phải số (như văn bản abc) và các giá trị lỗi (như #NUM !, #NULL!) Là giá trị rỗng.
Hàm SUMPRODUCT coi giá trị logic là TRUE là 1 và False là 0.
Mảng đối số phải có cùng độ dài với hàm.

Hy vọng bài viết này về cách Trả về Tổng của 5 giá trị trên cùng hoặc 5 giá trị dưới cùng với các tiêu chí trong Excel là giải thích. Tìm thêm các bài viết về hàm SUMPRODUCT tại đây. Vui lòng chia sẻ câu hỏi của bạn bên dưới trong hộp bình luận. Chúng tôi sẽ hỗ trợ bạn.

Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]

Các bài viết liên quan

Bài viết phổ biến

liên kết: / logic-công thức-nếu-hàm-với-ký tự đại diện [Nếu có ký tự đại diện]