Sử dụng SUMPRODUCT để đếm với nhiều hoặc nhiều tiêu chí
Như tôi đã đề cập trong nhiều blog của mình rằng hàm SUMPRODUCT là một chức năng rất linh hoạt và có thể được sử dụng cho nhiều mục đích. Trong bài này, chúng ta sẽ xem cách chúng ta có thể sử dụng hàm này để đếm các giá trị có nhiều tiêu chí OR.
Liên kết chung : / summing-excel-sumproduct-function [SUMPRODUCT]
Công thức đếm với nhiều hoặc nhiều tiêu chí
Criteria1: Đây là bất kỳ tiêu chí nào trả về một mảng TRUE và FALSE.
Criteria2: Đây là tiêu chí tiếp theo mà bạn muốn kiểm tra.
Tương tự, bạn có thể có bao nhiêu tiêu chí tùy thích.
Công thức chung trên thường được sửa đổi để phù hợp với yêu cầu đếm với nhiều tiêu chí OR. Nhưng công thức cơ bản là thế này. Trước tiên, chúng ta sẽ xem cách này hoạt động như thế nào thông qua một ví dụ và sau đó chúng ta sẽ thảo luận về các tình huống khác mà bạn sẽ cần sửa đổi công thức này một chút.
Ví dụ: Đếm số người dùng nếu mã đại lý hoặc năm trùng khớp sử dụng SUMPRODUCT
Vì vậy, ở đây chúng tôi có một tập dữ liệu về nhân viên bán hàng. Dữ liệu chứa nhiều cột. Việc chúng ta cần làm là đếm số lượng người dùng có mã “INKA” hoặc năm là “2016”. Đảm bảo rằng nếu ai đó có cả hai (mã là “inka” và năm 2016) thì sẽ được tính là 1.
Vì vậy, ở đây chúng tôi có hai tiêu chí. Chúng tôi sử dụng công thức SUMPRODUCT đã đề cập ở trên:
Ở đây, mã và năm là phạm vi được đặt tên.
Điều này trả về 7. Trong dữ liệu, chúng ta có 5 bản ghi mã INKA và 4 bản ghi của năm 2016.
Nhưng 2 bản ghi có cả “INKA” và 2016 là mã và năm tương ứng.
Các bản ghi này được tính là 1. Và đây là cách chúng ta nhận được 7.
Nó hoạt động như thế nào?
Vì vậy, chúng ta hãy xem cách công thức được giải quyết từng bước, sau đó tôi sẽ thảo luận về cách nó hoạt động.
|
5⇒7 |
Trong bước đầu tiên, giá trị của I3 (“INKA”) được so sánh với mỗi ô trong dải mã. Điều này trả về một mảng TRUE và FALSE. ĐÚNG cho mỗi trận đấu. Để tiết kiệm dung lượng, tôi đã không hiển thị tất cả TRUE-FALSE. Tương tự, giá trị của K3 (2016) được khớp với từng ô trong phạm vi năm.
Trong bước tiếp theo, chúng tôi thêm hai mảng này dẫn đến một mảng giá trị số mới. Như bạn có thể biết, TRUE được coi là 1 và FALSE là 0 trong Excel. Vì vậy, khi TRUE và TRUE được thêm vào, chúng ta nhận được 2 và phần còn lại bạn có thể hiểu.
Trong bước tiếp theo, chúng tôi kiểm tra giá trị nào lớn hơn 0 trong mảng.
Điều này một lần nữa chuyển đổi mảng thành một mảng đúng sai. Đối với mỗi giá trị 0 mà chúng ta nhận được, Sai và phần còn lại được chuyển thành true. Bây giờ số giá trị TRUE trong mảng là câu trả lời của chúng tôi. Nhưng chúng ta đếm chúng như thế nào? Đây là cách thực hiện.
Vì vậy, mỗi giá trị TRUE trong mảng được chuyển đổi thành 1 và FALSE thành 0. Trong bước cuối cùng, hàm SUMPRODUCT tổng hợp mảng này và chúng tôi nhận được câu trả lời là 7.
Thêm nhiều hơn hoặc tiêu chí để đếm bằng cách sử dụng hàm SUMPRODUCT Vì vậy, nếu bạn cần thêm nhiều hơn hoặc tiêu chí để đếm, bạn chỉ có thể thêm tiêu chí bằng cách sử dụng dấu + vào hàm. Ví dụ: nếu bạn muốn thêm một tiêu chí khác vào công thức trên để nó thêm số lượng nhân viên đã bán nhiều hơn 5 sản phẩm.
Công thức SUMPRODUCT sẽ đơn giản như sau:
Đơn giản! phải không
Nhưng giả sử bạn muốn có hai tiêu chí từ dải Mã *. Giả sử bạn muốn đếm “INKB”. Vì vậy, làm thế nào để bạn làm điều này? Một phương pháp đang sử dụng kỹ thuật trên nhưng điều đó sẽ lặp đi lặp lại. Giả sử tôi muốn thêm 10 tiêu chí khác từ cùng một phạm vi. Trong những trường hợp như vậy, kỹ thuật này không phải là thông minh để đếm với SUMPRODUCT.
Giả sử chúng ta có dữ liệu được sắp xếp như thế này.
Các mã tiêu chí nằm trong một hàng I2: J2. Việc sắp xếp dữ liệu là quan trọng ở đây. Công thức SUMPRODUCT cho 3 cài đặt số tiêu chí HOẶC sẽ là:
Đây là công thức SUMPRODUCT để đếm với nhiều tiêu chí khi nhiều tiêu chí từ một phạm vi được viết liên tiếp. Điều này trả về câu trả lời đúng là 10.
Nếu bạn nhập bất kỳ năm nào vào J3, công thức cũng sẽ thêm số đó.
Điều này được sử dụng khi các tiêu chí nằm trong một hàng. Nó sẽ hoạt động khi các tiêu chí nằm trong một cột cho cùng một phạm vi? Không. Nó sẽ không.
Trong ví dụ này, chúng ta có nhiều mã để đếm nhưng các mã loại này được viết trong một cột. Khi chúng ta sử dụng công thức SUMPRODUCT ở trên, chúng ta gặp lỗi ans # N / A. Chúng tôi sẽ không tìm hiểu nguyên nhân của lỗi này vì điều này sẽ làm cho bài viết này quá dài. Hãy xem làm thế nào chúng ta có thể làm cho nó hoạt động.
Đây là công thức để đếm với nhiều hoặc điều kiện trong cùng một phạm vi khi tiêu chí được liệt kê trong một cột.
Vâng, anh bạn, tôi hy vọng tôi đã đủ rõ ràng và nó có ý nghĩa. Tôi hy vọng nó phục vụ mục đích của bạn ở đây. Nếu công thức này không giải quyết được vấn đề của bạn, hãy cho tôi biết yêu cầu của bạn trong phần nhận xét bên dưới. Tôi sẽ rất sẵn lòng giúp đỡ bạn bằng mọi cách. Bạn có thể đề cập đến bất kỳ nghi ngờ nào liên quan đến Excel / VBA. Cho đến nay, hãy tiếp tục học hỏi, tiếp tục xuất sắc.
Các bài viết liên quan
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 để đếm các 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.