Để tạo một công thức mảng SUMIFS mở rộng kết quả, bạn cần sử dụng một hàm Lambda trong Google Sheets.
SUMIF có thể mở rộng kết quả bằng cách sử dụng hàm ARRAYFORMULA, nhưng SUMIFS không thể làm được. Một hạn chế của SUMIF là nó chỉ có thể xử lý một phạm vi tiêu chí duy nhất.
Tuy nhiên, trong SUMIF, chúng ta có thể kết hợp các tiêu chí và phạm vi tiêu chí như một giải pháp thay thế cho vấn đề công thức mảng SUMIFS. Cách tiếp cận này không luôn hoạt động, đặc biệt là khi xử lý các phạm vi ngày tháng làm tiêu chí.
Một cách khác để giải quyết vấn đề này là sử dụng QUERY. Phương pháp này cũng có thể có một số hạn chế tương tự như SUMIF. Trong bài viết này, tôi sẽ hướng dẫn tất cả các phương pháp và mẹo liên quan.
Công thức mảng SUMIFS để mở rộng kết quả trong Google Sheets
Ví dụ này thể hiện việc sử dụng phạm vi ngày tháng làm tiêu chí trong một công thức mảng SUMIFS trong Google Sheets.
Dưới đây là cú pháp của SUMIFS để bạn tham khảo:
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])Giả sử bạn có các ngày giao hàng của các sản phẩm khác nhau trong cột A, số lượng của chúng trong cột B và xác nhận giao hàng trong cột C. Một dấu “x” trong cột C cho biết giao hàng của sản phẩm đó đã được xác nhận bởi nhà cung cấp.
Trong tình huống này, bạn muốn tính tổng số lượng giao hàng đã được xác nhận giữa 01/11/2023 và 05/11/2023. Tiêu chí cho hai ngày này nằm trong ô D2 và E2, và tiêu chí khác, “x”, nằm trong ô F2.
Ngoài ra, còn một bộ tiêu chí khác trong các ô D3:F3, là 06/11/2023 và 10/11/2023 trong các ô D3:E3 và “x” trong ô F3.
Dưới đây là công thức không sử dụng mảng SUMIFS trong ô G2:
=SUMIFS(B2:B14,A2:A14,">="&D2,A2:A14,"<="&E2,C2:C14,F2)Để mở rộng kết quả trong các ô, chúng ta cần sử dụng một công thức mảng SUMIFS.
Trong Microsoft 365 Excel, công thức mở rộng do hành vi mảng động của nó. Bạn chỉ cần xác định phạm vi tiêu chí trong công thức như sau:
=SUMIFS(B2:B14,A2:A14,">="&D2:D3,A2:A14,"<="&E2:E3,C2:C14,F2:F3)Tuy nhiên, điều này không hoạt động khi sử dụng SUMIFS trong Google Sheets. Dưới đây là cách sử dụng công thức mảng SUMIFS để mở rộng kết quả của nó trong Google Sheets.
SUMIFS với MAP Lambda để mở rộng kết quả mảng
Đầu tiên, cho tôi giải thích cách dễ dàng chuyển đổi một công thức SUMIFS không dùng mảng thành một công thức mảng SUMIFS sử dụng hàm Lambda MAP trong Google Sheets:
Bạn nên chỉ định các phạm vi tiêu chí trong MAP một cách riêng biệt, đặt tên chúng trong hàm Lambda và thay thế các tiêu chí hiện có trong SUMIFS bằng các tên được gán.
Các phạm vi tiêu chí trong trường hợp này là D2:D3, E2:E3 và F2:F3.
Hãy chỉ định chúng trong MAP như sau:
=MAP(D2:D3,E2:E3,F2:F3, LAMBDA(criterion1, criterion2, criterion3, sumifs_formula))Thay thế sumifs_formula bằng công thức không dùng mảng và trong đó thay thế D2 bằng criterion1, E2 bằng criterion2 và F2 bằng criterion3.
Dưới đây là công thức mảng SUMIFS cuối cùng để mở rộng kết quả:
=MAP(D2:D3,E2:E3,F2:F3, LAMBDA(criterion1, criterion2, criterion3, SUMIFS(B2:B14,A2:A14,">="&criterion1,A2:A14,"<="&criterion2,C2:C14,criterion3)))Cấu trúc công thức
Hãy phân tích cấu trúc công thức để giúp bạn hiểu logic:
Cú pháp của hàm MAP:
MAP(array1, [array2, …], LAMBDA([name, …], formula_expression))Trong công thức của chúng ta, các đối số là như sau:
- array1: D2:D3
- array2: E2:E3
- array3: F2:F3
Trong hàm LAMBDA:
- Tên: criterion1, criterion2, criterion3 (tương ứng với array1, array2 và array3)
- Biểu thức công thức: SUMIFS(B2:B14, A2:A14, “>=”&criterion1, A2:A14, “<=”&criterion2, C2:C14, criterion3)
Hàm MAP áp dụng hàm LAMBDA cho mỗi tập hợp criterion1, criterion2 và criterion3 được cung cấp trong D2:D3, E2:E3 và F2:F3, tương ứng.
Điều này dẫn đến một danh sách các tổng dựa trên các tập hợp tiêu chí khác nhau này.
Các phương pháp thay thế công thức mảng SUMIFS
Công thức mảng SUMIFS trên không dễ dàng để thay thế bằng các công thức thông thường. Do đó, tôi khuyên bạn nên tiếp tục sử dụng nó.
Tuy nhiên, trong một số tình huống cụ thể, bạn có thể xem xét sử dụng các công thức khác như các phương pháp thay thế cho công thức mảng SUMIFS. Dưới đây là một số ví dụ.
SUMIF (Tiêu chí trong một cột duy nhất)
Nếu bạn muốn áp dụng nhiều tiêu chí trong một cột duy nhất, bạn có thể sử dụng SUMIF, có khả năng mở rộng kết quả.
Dưới đây là cú pháp của SUMIF để bạn tham khảo:
SUMIF(range, criterion, [sum_range])Ví dụ:
- Vấn đề: Tổng doanh số trong quý 1 (E3) và quý 2 (E4) khi chỉ định quý trong A2:A và số tiền doanh số được chỉ định trong C2:C.
- Giải pháp: =ARRAYFORMULA(SUMIF(A2:A9,E3:E4,C2:C9))
SUMIF (Tiêu chí trong nhiều cột)
Lần này, chúng ta có tên các tháng (dưới dạng văn bản) trong cột A, tên các loại trái cây trong cột B và số tiền doanh số trong cột C.
Mục tiêu là tính tổng doanh số cho trái cây Apple và Orange trong tháng 1 một cách riêng biệt.
Các tiêu chí được đặt như sau:
- E3 và E4 chứa “January”
- F3 và F4 chứa “Apple” và “Orange”
Công thức:
=ARRAYFORMULA(SUMIF(A2:A&B2:B,E3:E4&F3:F4,C2:C))Vì SUMIF có thể xử lý các tiêu chí trong một cột đơn, chúng ta đã kết hợp các tiêu chí trong hai cột cùng với phạm vi tương ứng.
Tuy nhiên, cần lưu ý rằng phương pháp thay thế công thức dựa trên SUMIF này có thể không hoạt động trong tất cả các tình huống, đặc biệt là khi xử lý các tiêu chí liên quan đến so sánh.
Dưới đây là phương pháp thay thế MAP + SUMIFS:
=MAP(E3:E4,F3:F4,lambda(criteriaSet1,criteriaSet2, sumifs(C2:C,A2:A,criteriaSet1,B2:B,criteriaSet2)))Sử dụng QUERY như một phương pháp thay thế cho SUMIFS để mở rộng kết quả mảng
QUERY là một trong những hàm tốt nhất để xử lý và trực quan hóa dữ liệu.
Đầu ra của QUERY có thể được cấu trúc, giúp tạo bảng tính dựa trên công thức và biểu đồ.
Dưới đây là một ví dụ về cách giải quyết vấn đề mở rộng công thức mảng SUMIFS bằng công thức QUERY. Chúng ta sẽ sử dụng dữ liệu về các loại trái cây cho ví dụ này.
Công thức:
=QUERY(A2:C,"Select A,B, sum(C) where A Matches 'January' and B matches 'Apple|Orange' group by A,B label sum(C)''")Xin lưu ý rằng công thức này phân biệt chữ hoa, và tôi đã chỉ định các tiêu chí là các giá trị được ghi cố định.
Kết luận
Trong một số tình huống, bạn có thể muốn sử dụng hàm ARRAYFORMULA với SUMIFS, không để mở rộng kết quả mà để mở rộng một hàm được sử dụng trong đó, đặc biệt là các hàm liên quan đến ngày tháng.
Dưới đây là một ví dụ:
=ARRAYFORMULA(SUMIFS(C2:C,YEAR(A2:A),F2,B2:B,E2)Trong ví dụ trên, bạn có thể thấy hàm ngày YEAR. Vì nó là một hàm không phải mảng, bạn nên sử dụng ARRAYFORMULA với SUMIFS.
Thực tế, ARRAYFORMULA được sử dụng để hỗ trợ hàm YEAR. Điều đó có nghĩa là bạn cũng có thể sử dụng công thức trên như sau:
=SUMIFS(C2:C,ARRAYFORMULA(YEAR(A2:A)),F2,B2:B,E2)Hãy thử các phương pháp và công thức mà tôi đã chia sẻ trong bài viết này để mở rộng kết quả của bạn trong Google Sheets. Hy vọng chúng sẽ giúp bạn làm việc hiệu quả và tiết kiệm thời gian!
