Để Tổng một cột trong Google Sheets, nếu ngày nằm giữa ngày bắt đầu và ngày kết thúc, chúng ta có thể sử dụng Sumifs. Đó được gọi là tổng điều kiện của một khoảng ngày. Nếu bạn hỏi tôi về một công thức mảng để tổng điều kiện các khoảng ngày trong Google Sheets, tôi sẽ giới thiệu MMULT.
Tại sao chúng ta không sử dụng Sumifs để tổng một khoảng ngày?
Lý do không sử dụng Sumifs là nó không mở rộng kết quả như Sumif, “anh em” của nó. Nhưng Sumif có một hạn chế. Nó có thể mở rộng kết quả bằng cách sử dụng ArrayFormula nhưng các điều kiện phải đến từ một cột duy nhất.
Một lần nữa, chúng ta có thể vượt qua ràng buộc Sumif đó bằng cách kết hợp ảo các cột và tiêu chí. Nhưng không hữu ích khi các tiêu chí là các khoảng ngày.
Bạn Có Thể Thích: Sumif Multiple Columns Criteria – Nó hoạt động trong Google Sheets.
Nhiều Khoảng ngày là Tiêu chí trong Sumifs
Chúng ta không thể sử dụng Sumifs để mở rộng kết quả cho các khoảng ngày như dưới đây.
Vì vậy chúng ta cần tìm một giải pháp thay thế. Một công thức mảng để tổng có điều kiện các khoảng ngày.
Trước đó, tôi đã thảo luận về cách mở rộng kết quả Sumifs trong Google Sheets sử dụng các hàm thay thế. Trong đó, tôi chưa bao gồm cách tổng có điều kiện nhiều khoảng ngày trong Google Sheets. Tôi nghĩ rằng tôi nên thảo luận chi tiết về điều đó và bài học này là kết quả của quá trình suy nghĩ đó.
Công thức mảng mà tôi sẽ sử dụng để tổng có điều kiện các giá trị trong một cột nếu ngày nằm giữa hai ngày, là MMULT. Trước khi điều đó hãy xem công thức Sumifs không mảng để điều kiện tổng một khoảng ngày.
=sumifs($D$2:$D,$C$2:$C,I2,$B$2:$B,”>=”&G2,$B$2:$B,”<=”&H2)
Nhập công thức này vào ô J2 và kéo xuống.
Thay vì Sumifs, bạn có thể sử dụng MMULT trong ô J2 là một công thức mảng để tổng có điều kiện các khoảng ngày trong Google Sheets. Vì vậy không cần kéo công thức đó xuống để bao phủ các khoảng ngày bổ sung.
Công thức mảng để tổng có điều kiện nhiều khoảng ngày
Như bạn có thể thấy, tôi đã sử dụng MMULT để tổng có điều kiện nhiều khoảng ngày. Đây là công thức mạnh mẽ đó.
=ArrayFormula(if(len(G2:G),(mmult((I2:I=transpose(C2:C))*((transpose(B2:B)>=G2:G)*(transpose(B2:B)<=H2:H)),N(D2:D))),))
Trong Google Sheets, hàm MMULT tính tích của hai ma trận được xác định dưới dạng mảng/phạm vi.
Cú pháp:
MMULT(matrix1, matrix2)
Hãy xem cách MMULT có thể tổng có điều kiện các khoảng ngày trong Google Sheets? Xem phần giải thích công thức dưới đây.
MMULT để Tổng có Điều kiện trong Google Sheets
Giải thích công thức:
Trong phần giải thích công thức, tôi sẽ sử dụng các phạm vi hữu hạn. Vì vậy không cần sử dụng công thức IF + LEN ở đầu (combo IF + LEN đó nổi tiếng trong Google Sheets để giới hạn kết quả công thức mảng thành các ô không trống).
Xem phiên bản công thức MMULT trên cho các phạm vi hữu hạn này.
=ArrayFormula(mmult((I2:I3=transpose(C2:C10))*((transpose(B2:B10)>=G2:G3)*(transpose(B2:B10)<=H2:H3)),N(D2:D10)))
Tôi chia công thức này thành bốn phần. Ba phần đầu tạo ra Ma trận 1 và phần cuối tạo ra Ma trận 2 (xem hình ảnh phía trên).
Đây là những ma trận đó.
Trong đó Ma trận 1 xử lý các điều kiện và Ma trận 2 là cột tổng.
Công thức tạo ra Ma trận 1:
=ArrayFormula((I2:I3=transpose(C2:C10))*((transpose(B2:B10)>=G2:G3)*(transpose(B2:B10)<=H2:H3)))
Công thức này trả về một đầu ra hai dòng như đã nêu ở trên. Trong đầu ra đó, các số 1 trong dòng đầu tiên có nghĩa là các điều kiện sau đây được đáp ứng (xem phạm vi G2:I2 trong ảnh đầu tiên).
Product: AppleDate between 02/01/2019 and 07/01/2019
Dòng thứ hai (xem phạm vi G3:I3 trong ảnh đầu tiên):
Product: BananaDate between 04/01/2019 and 09/01/2019
Tôi biết bạn có thể có một số câu hỏi liên quan đến Ma trận 1 trong tâm trí của bạn. Xem Q&A đó.
Q&A liên quan đến Ma trận 1
Câu hỏi: Tại sao tôi đã chuyển đổi (thay đổi hướng) dữ liệu để kiểm tra điều kiện?
Trả lời: Tôi có thể kiểm tra phạm vi C2:C10 với điều kiện trong I2 như sau.
=ArrayFormula(C2:C10=I2)
Nó sẽ trả về TRUE/FALSE trong một cột. Nhưng tôi có các điều kiện trong I2:I3. Vì vậy công thức này không hoạt động.
=ArrayFormula(C2:C10=I2:I3)
Bạn nên sử dụng nó như sau:
=ArrayFormula(C2:C10=transpose(I2:I3))
hoặc
=ArrayFormula(transpose(C2:C10)=I2:I3)
Chúng ta cần dùng cái sau để sử dụng trong MMULT.
Câu hỏi: Sử dụng dấu hoa thị giữa mỗi kiểm tra có ý nghĩa gì?
Trả lời: Nó tương đương với toán tử logic AND. Điều đó có nghĩa là tất cả các điều kiện phải trùng khớp (trả về 1).
Câu hỏi tiếp theo liên quan đến việc sử dụng hàm N trong Ma trận 2. Tôi đã sử dụng nó để trả về giá trị 0 nếu ô trong phạm vi trống.
Với bài học này, tôi hy vọng, tôi có thể trả lời các câu hỏi sau của bạn.
- Có một công thức mảng để tổng có điều kiện các khoảng ngày trong Google Sheets.
- Tổng nếu các ngày nằm giữa một ngày bắt đầu và kết thúc.
- Công thức MMULT là một phương thức thay thế cho Sumifs.
- Công thức MMULT để tổng các giá trị giữa nhiều khoảng ngày.
Cám ơn vì đã đọc. Thưởng thức nào!
