Cách tính tổng bằng cách so khớp hàng và cột trong Excel
Cho đến nay, chúng ta đã học cách liên kết: / math-functions-how-to-sum-column-in-a-excel-by-match-header [tính tổng toàn bộ cột phù hợp] của một bảng trong Excel. Nhưng làm cách nào để chúng ta tính tổng các giá trị khi chúng ta cần đối sánh cột và hàng. Trong bài này, chúng ta sẽ học cách tính tổng của các hàng và cột phù hợp.
Có hai công thức để làm điều này nhưng trước tiên, hãy để chúng tôi kịch bản.
Ở đây, tôi có một bảng ghi lại doanh số bán hàng của các nhân viên trong các tháng khác nhau. Tên của nhân viên có thể lặp lại. Xem hình bên dưới:
Chúng ta cần lấy tổng của tháng 5 trong đó người bán hàng là Donald.
Cột: * Đây là phạm vi 2 chiều của các cột mà bạn muốn tính tổng. Nó không được chứa tiêu đề. Trong bảng trên, nó là C3: N7.
column_headers: Là phạm vi tiêu đề của các cột * mà bạn muốn tính tổng. Trong dữ liệu trên, nó là C2: N2.
column_heading: * Đây là tiêu đề mà bạn muốn đối sánh. Trong ví dụ trên, nó nằm trong B13.
Nếu không có thêm sự chậm trễ, hãy sử dụng công thức.
row_headers: Đây là dải ô tiêu đề * mà bạn muốn tính tổng.
Trong dữ liệu trên, nó là B3: B10.
row_heading: * Đây là tiêu đề mà bạn muốn đối sánh trong các hàng. Trong ví dụ trên, nó nằm trong F13.
Nếu không có thêm sự chậm trễ, hãy sử dụng công thức.
Viết công thức này vào ô D13 và để excel thực hiện nó là phép thuật (không có thứ gọi là phép thuật) …
Điều này trả về giá trị:
Bây giờ khi bạn thay đổi tháng hoặc người bán hàng, tổng sẽ thay đổi theo tiêu đề hàng và tiêu đề cột.
Nó hoạt động như thế nào?
Logic boolean đơn giản này.
(C2: N2 = B13): Câu lệnh này sẽ trả về một mảng TRUE và FALSE.
Tất cả các giá trị phù hợp trong cột có true và các giá trị khác sẽ có false. Trong trường hợp này, chúng ta sẽ chỉ có một True vì phạm vi C2: N2 chỉ chứa một trường hợp May tại 5 ^ th ^ Location.
(B3: B10 = E13): Thao tác này sẽ hoạt động tương tự như trên và sẽ trả về mảng TRUE và FALSE. Tất cả các giá trị phù hợp sẽ có TRUE và các giá trị khác sẽ có FALSE. Trong trường hợp này, chúng ta sẽ có 2 TRUE vì phạm vi B3: B10 có hai trường hợp “Donald”.
(C2: N2 = B13) (B3: B10 = E13): * Bây giờ chúng ta đang nhân các mảng được trả về bởi các câu lệnh. Điều này sẽ thực hiện và logic và chúng tôi sẽ nhận được một mảng 1 và 0. Bây giờ chúng ta sẽ có một mảng 2D chứa 2 số 1 và số 0 còn lại.
(C3: N10) (C2: N2 = B13) (B3: B10 = E13) = Cuối cùng chúng ta nhân mảng 2D với bảng 2D. Nó sẽ trả về một lần nữa một mảng 0 và các số phù hợp với tiêu chí.
Cuối cùng, hàm SUMPRODUCT sẽ tổng hợp mảng để tạo ra kết quả mong muốn.
Cách 2: Tính tổng tiêu đề cột và tiêu đề hàng phù hợp Sử dụng hàm SUM và IF Công thức chung để tính tổng tiêu đề cột và tiêu đề phù hợp bằng cách sử dụng hàm SUM và IF Excel là:
Tất cả các biến đều giống như trong phương pháp đã giải thích ở trên. Ở đây, chúng chỉ cần được sử dụng theo một thứ tự khác.
Viết công thức này vào ô D13:
Điều này trả về câu trả lời chính xác. Xem ảnh chụp màn hình bên dưới:
Nó hoạt động như thế nào?
Logic giống như phương pháp SUMPRODCUT đầu tiên chỉ khác về cơ chế. Nếu tôi giải thích ngắn gọn, thì liên kết bên trong : / tips-if-condition-in-excel [Hàm IF] trả về một mảng 2D có cùng thứ nguyên với bảng. Mảng này chứa số lượng hai hàng phù hợp. Sau đó, liên kết bên trong : / tips-if-condition-in-excel [Hàm IF]
khớp với các tiêu đề hai cột trong mảng này và trả về mảng 2D chỉ chứa các số khớp với cả hai, cột và tiêu đề. Tất cả các phần tử khác của mảng sẽ là FALSE.
Vâng, các bạn, đây là cách bạn có thể tổng hợp các hàng và cột phù hợp từ một bảng trong excel. Tôi hy vọng nó là giải thích và hữu ích cho bạn. Nếu bạn có bất kỳ nghi ngờ nào về chủ đề này hoặc có bất kỳ nghi ngờ nào khác liên quan đến excel / VBA, hãy hỏi trong phần nhận xét bên dưới.
Bài viết liên quan:
Bài viết phổ biến:
Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.