SUMIFS để tổng hợp nhiều cột tổng trong Google Sheets

GiGiKing·Tháng ba 26, 2024·0 comments

Trong Google Sheets, SUMIFS có thể xử lý nhiều cột tổng mà không cần sử dụng các công thức lồng nhau. Không cần tìm kiếm các phương pháp khác để thực hiện điều này.

Chúng ta thường sử dụng SUMIFS để tổng hợp một cột dựa trên tiêu chí trong một hoặc nhiều cột cùng kích thước. Đây là chức năng chính của nó.

Nhưng điều gì xảy ra nếu chúng ta muốn sử dụng nhiều cột tổng trong một hàm SUMIFS?

Ví dụ, hãy xem xét doanh số bán hàng hàng năm của một số sản phẩm cho các dự án khác nhau.

Tên sản phẩm được liệt kê trong cột A, tên dự án được liệt kê trong cột B, và số lượng cung cấp cho mỗi tháng được liệt kê trong mười hai cột tiếp theo.

Bạn muốn biết số lượng cung cấp của một sản phẩm cụ thể cho một dự án cụ thể trong năm đó.

Bạn có thể thêm một cột tổng ở cuối và sử dụng nó trong SUMIFS thay vì mười hai cột hàng tháng. Tuy nhiên, điều này có một giới hạn. Nếu bạn muốn tổng số lượng cho quý đầu tiên, điều này có thể không hữu ích.

Đây là lúc mà SUMIFS với nhiều cột tổng trở nên quan trọng.

Logic

Chúng ta sẽ bắt đầu với cú pháp để bạn có thể hiểu logic sử dụng hàm SUMIFS với nhiều cột tổng.

Cú pháp: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Cần lưu ý rằng kích thước của sum_range được xác định bởi kích thước của criteria_range1.

Nếu criteria_range1 là A1:A10, sum_range phải có kích thước tương tự, có nghĩa là nó phải bao gồm 10 ô dọc.

Vì vậy, nếu bạn muốn sử dụng nhiều cột tổng, bạn nên lặp lại cột tiêu chí theo nhu cầu.

Ví dụ, nếu sum_range là C1:E10, bạn nên lặp lại criteria_range1 (A1:A10) ba lần. Điều này có thể dễ dàng được thực hiện bằng cách sử dụng các hàm CHOOSECOLS và SEQUENCE, chúng ta sẽ thảo luận về chúng dưới đây.

SUMIFS để tổng hợp nhiều cột tổng: Một cột tiêu chí duy nhất

Dữ liệu mẫu của chúng tôi bao gồm 14 cột trong phạm vi A1:N10, trong đó A1:N1 chứa nhãn trường. Do đó, chúng ta sẽ sử dụng phạm vi A2:N10 trong các công thức của chúng tôi.

Đây là cấu trúc của dữ liệu mẫu chúng tôi cho kiểm tra nhiều cột tổng SUMIFS:

  • A2:A10: Tên sản phẩm (Road Base, Gravel 10-20 mm và Black Sand 3/16)
  • B2:B10: Địa điểm (Dự án 1, Dự án 2 và Dự án 3)
  • C2:N10: Số lượng cung cấp từ tháng 1 đến tháng 12

Bạn có thể nhấp vào nút bên dưới để xem trước và sao chép bảng mẫu.

Sample Sheet

Trong ví dụ trên, tôi muốn tìm tổng cung cấp của vật liệu Road Base từ tháng 1 đến tháng 12.

Làm thế nào chúng ta tìm được nó?

Công thức sau sẽ không hoạt động vì kích thước của phạm vi tiêu chí và phạm vi tổng không giống nhau. Nó sẽ cho kết quả là lỗi “Array arguments to SUMIFS are of different size” #VALUE!:

=SUMIFS(C2:N10, A2:A10, "Road Base")

Trong đó:

  • sum_range: C2:N10
  • criteria_range1: A2:A10
  • criterion1: “Road Base”

Để làm cho nó hoạt động, chúng ta cần đảm bảo rằng kích thước của phạm vi tiêu chí khớp với phạm vi tổng. Công thức sau thực hiện việc này:

ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0))

Trong công thức SUMIFS, thay thế A2:A10 bằng công thức mới này như sau:

=SUMIFS(C2:N10, ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0)), "Road Base")

Trong đó:

  • sum_range: C2:N10
  • criteria_range1: ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0))
  • criterion1: “Road Base”

Công thức sửa đổi này sẽ trả về đúng tổng cung cấp của vật liệu Road Base từ tháng 1 đến tháng 12.

Công thức SUMIFS này tổng hợp 12 cột tổng trong phạm vi C2:N10.

Cấu trúc của Công thức (Phạm vi tiêu chí)

Hàm CHOOSECOLS được sử dụng để trả về các cột được chọn từ một phạm vi.

Ví dụ:

  • CHOOSECOLS(A1:Z, 3) // trả về phạm vi C1:C
  • CHOOSECOLS(A1:Z, SEQUENCE(3)) // trả về phạm vi A1:C
  • CHOOSECOLS(A1:Z, {1, 2, 3}) // trả về phạm vi A1:C

Trong công thức SUMIFS của chúng ta, chúng ta chỉ có một cột, đó là phạm vi A2:A10, trong CHOOSECOLS. Hàm SEQUENCE trả về số 1, mười hai lần.

Vì vậy, hàm CHOOSECOLS sẽ trả về phạm vi A2:A10 mười hai lần. Như vậy, chúng ta có thể khớp kích thước của phạm vi tổng và phạm vi tiêu chí trong công thức SUMIFS.

Dưới đây là cách sử dụng công thức SUMIFS với nhiều cột tổng và nhiều cột tiêu chí, cụ thể với sum_range, criteria_range1 và criteria_range2.

SUMIFS để tổng hợp nhiều cột tổng: Nhiều cột tiêu chí

Lần này, tôi cần tìm tổng số lượng bán hàng của vật liệu Road Base chỉ cho Dự án 3.

Vì có hai cột tiêu chí, chúng ta cần xử lý việc này tương tự như ví dụ trên. Ngoài việc lặp lại criteria_range1, chúng ta cũng cần lặp lại criteria_range2 mười hai lần.

Dưới đây là công thức để làm được điều này:

=SUMIFS(C2:N10, ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0)), "Road Base", ArrayFormula(CHOOSECOLS(B2:B10, SEQUENCE(12)^0)), "Project 3")

Trong đó:

  • sum_range: C2:N10
  • criteria_range1: ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0))
  • criterion1: “Road Base”
  • criteria_range2: ArrayFormula(CHOOSECOLS(B2:B10, SEQUENCE(12)^0))
  • criterion2: “Project 3”

Lưu ý: Chúng ta chỉ cần một ArrayFormula trong trường hợp này, vì vậy chúng ta có thể rút gọn nó như sau:

=ArrayFormula(SUMIFS(C2:N10, CHOOSECOLS(A2:A10, SEQUENCE(12)^0), "Road Base", CHOOSECOLS(B2:B10, SEQUENCE(12)^0), "Project 3"))

SUMIFS và REGEXMATCH

Hàm SUMIFS được thiết kế để xử lý tiêu chí VÀ.

Một cách đơn giản, bạn có thể sử dụng nó để tổng hợp một sum_range khi criteria_range1 bằng criterion1 và criteria_range2 bằng criterion2, và cetera.

Để sử dụng tiêu chí HOẶC, một số người dùng thích lồng SUMIFS. Tuy nhiên, tôi thích sử dụng REGEXMATCH trong SUMIFS.

Ví dụ, bạn có thể sử dụng nó để tổng hợp một sum_range khi criteria_range1 phù hợp với criterion1a hoặc criterion1b, và criteria_range2 bằng criterion2, và cetera.

Chúng tôi đã giải thích sự kết hợp SUMIFS REGEXMATCH trước đó. Dưới đây là một ví dụ trước khi chúng tôi đi vào cách sử dụng SUMIFS và REGEXMATCH để tổng hợp nhiều cột:

=ArrayFormula(SUMIFS(C2:C10, A2:A10, "Road Base", REGEXMATCH(B2:B10, "Project 1|Project 3"), TRUE))

Trong đó:

  • sum_range: C2:C10
  • criteria_range1: A2:A10
  • criterion1: “Road Base”
  • criteria_range2: B2:B10
  • criterion2: “Project 1” hoặc “Project 3”

Công thức này trả về tổng cung cấp của ‘Road Base’ trong Tháng 1 trong Dự án 1 và Dự án 3.

Lưu ý: Khi sử dụng REGEXMATCH với SUMIFS, hãy chắc chắn sử dụng hàm ArrayFormula.

Bây giờ, hãy xem cách sử dụng SUMIFS với nhiều cột tổng trong Google Sheets.

Sử dụng cùng một công thức nhưng cho Tháng 1 đến Tháng 12, thực hiện các thay đổi sau:

  • Thay thế A2:A10 bằng ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0))
  • Thay thế B2:B10 bằng ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0))
  • Cuối cùng, thay thế C2:C10 bằng C2:N10.

Dưới đây là công thức SUMIFS cho nhiều cột tổng:

=ArrayFormula(SUMIFS(C2:N10, CHOOSECOLS(A2:A10, SEQUENCE(12)^0), "Road Base", REGEXMATCH(CHOOSECOLS(B2:B10, SEQUENCE(12)^0), "Project 1|Project 3"), TRUE))

Trong đó:

  • sum_range: C2:N10
  • criteria_range1: ArrayFormula(CHOOSECOLS(A2:A10, SEQUENCE(12)^0))
  • criterion1: “Road Base”
  • criteria_range2: ArrayFormula(CHOOSECOLS(B2:B10, SEQUENCE(12)^0))
  • criterion2: “Project 1” hoặc “Project 3”

Kết luận

Chúng tôi đã xem xét một số ví dụ về việc sử dụng SUMIFS với nhiều cột tổng trong Google Sheets. Tôi đã nhận thấy rằng một số người dùng phải sử dụng SUMIFS lồng nhau.

Việc sử dụng SUMIFS lồng nhau có thể hoạt động khi bạn có 2-3 cột tổng. Tuy nhiên, nó trở nên ít thực tế hơn khi số lượng cột tổng tăng, vì bạn phải kết hợp nhiều hàm SUMIFS.

Phương pháp mà chúng tôi đã mô tả ở trên cung cấp một giải pháp thông minh và hiệu quả cho vấn đề này.

Related: (Không có thông tin liên quan khác)

GiGiKing

Related posts