Khi bạn sử dụng hàm Sumif với hàm ArrayFormula trong Google Sheets, bạn sẽ nhận được nhiều kết quả dòng.
Hãy xem một ví dụ đơn giản để hiểu rõ hơn.
Bảng dữ liệu #1
| Item | Month | Amount |
|---|---|---|
| Item1 | Jan | 100 |
| Item2 | Feb | 200 |
| Item3 | Mar | 150 |
| … | … | … |
| Item8 | Aug | 300 |
| Item9 | Sep | 250 |
| Item10 | Oct | 400 |
Bảng tóm tắt #2
| Summary | Formula |
|---|---|
| Criteria1 | =ArrayFormula(sumif(A2:A10,E2:C10)) |
| Criteria2 | =ArrayFormula(sumif(A2:A10,E3:C10)) |
| Criteria3 | =ArrayFormula(sumif(A2:A10,E4:C10)) |
Bằng cách sử dụng công thức Sumif với ArrayFormula trong ô F2, bạn sẽ nhận được kết quả như được hiển thị trong cột “Formula” của bảng #2 ở trên.
=ArrayFormula(sumif(A2:A10,E2:E4,C2:C10))Điều này cho phép bạn tính tổng cột “Amount” dựa trên nhiều điều kiện trên nhiều dòng.
Việc sử dụng Sumif với ArrayFormula trong Google Sheets đã được đề cập tới trong một số bài viết trước đây của tôi như sau:
- Cách sử dụng Sumif khi có nhiều điều kiện trên cùng một cột trong Google Sheets
- Công thức Sumif với nhiều điều kiện trong Google Sheets
Không cần thiết phải đọc hai bài viết trên để hiểu bài hướng dẫn này, nhưng có thể đáng để đọc.
Sumif với ArrayFormula trong dữ liệu đã lọc
Bây giờ, tôi sẽ lọc bảng #1. Để làm điều đó, hãy chọn dữ liệu (A1:C10) và sau đó nhấp vào Data > Create a filter.
Nhấp vào mũi tên xuống xuất hiện trong ô B1, bỏ chọn “Feb” và nhấp vào nút “OK”.
Lọc không ảnh hưởng theo mặc định?
Sau khi lọc dữ liệu cho tháng “Feb” (nằm trong cột 2) như trên, hãy xem liệu kết quả của công thức trên có thay đổi không.
Bạn sẽ không thấy bất kỳ thay đổi nào vì Lọc không ảnh hưởng đến kết quả của công thức trừ khi có sử dụng các công thức liên quan đến hàm Subtotal. Vì vậy, Sumif với ArrayFormula trong dữ liệu đã lọc không ảnh hưởng đến kết quả trong Google Sheets.
Để loại trừ các giá trị trong các dòng không hiển thị khỏi tổng Sumif, có một phương pháp đơn giản mà tôi đã đề cập ở đây – Sumif loại trừ các dòng ẩn trong Google Sheets. Nhưng không hoạt động với ArrayFormula theo yêu cầu của chúng ta.
Trong bài viết này, tôi sẽ cung cấp cho bạn một giải pháp để chỉ bao gồm các dòng hiển thị trong tổng Sumif của Array Formula trong Google Sheets. Hãy làm theo hướng dẫn từng bước dưới đây.
Sumif ArrayFormula bao gồm chỉ các dòng hiển thị trong dữ liệu đã lọc (Cách làm)
Trong ví dụ dưới đây, tôi có một Sumif ArrayFormula trong ô F2. Khi tôi lọc cột B cho các tháng “Jan” và “Mar” (loại bỏ “Feb”), công thức sẽ điều chỉnh tổng theo đúng cách.
Ý tôi là Sumif ArrayFormula trong ô F2 chỉ bao gồm các dòng hiển thị. Các giá trị trong các dòng đã lọc sẽ không được tính trong tổng của Sumif ArrayFormula.
Mặc dù chúng ta đang sử dụng hàm ArrayFormula, nhưng cần có một cột trợ giúp. Thực tế, tôi đã sử dụng cột D làm cột trợ giúp và ẩn các giá trị bằng cách thay đổi màu chữ thành màu trắng.
Hãy làm theo các “Bước” dưới đây để có công thức cột trợ giúp, công thức ô F2 và giải thích cho chúng.
Bước 1:
Chọn một cột bất kỳ trong phạm vi dữ liệu của bạn. Có thể là cột số, cột số, cột ngày hoặc bất kỳ cột nào có giá trị. Nhưng hãy đảm bảo rằng cột không chứa các ô trống trong phạm vi. Vì vậy, tôi sẽ lấy cột B làm ví dụ ở đây.
Bước 2:
Trong ô D2 (cột trợ giúp), hãy chèn công thức Subtotal =subtotal(103,B2) và kéo xuống để copy nó cho đến hàng cuối cùng trong phạm vi. Điều đó có nghĩa là các công thức sẽ nằm trong khoảng D2:D10 theo dữ liệu của tôi.
Bước 3:
Chèn công thức Sumif ArrayFormula sau đây vào ô F2 và xem kết quả thần kỳ!
=ArrayFormula(sumif(A2:A10&D2:D10,E2:E4&1,C2:C10))Giải thích về công thức
Subtotal
Công thức Sumif trên phụ thuộc nặng vào cột trợ giúp D để loại trừ các dòng đã lọc hoặc nói cách khác chỉ bao gồm các dòng hiển thị trong tổng dựa trên tiêu chí.
Liên quan đến các công thức cột trợ giúp, tức là các công thức Subtotal Count trong cột D, chúng trả về 1 trong các dòng hiển thị và 0 trong các dòng đã lọc.
Bạn có thể thay thế số 103 (số chức năng trong Subtotal) bằng số 3 nếu bạn chỉ muốn bao gồm hoặc loại trừ các dòng đã lọc. Số chức năng 103 bao gồm hoặc loại trừ các dòng ẩn theo bất kỳ phương pháp nào.
Sumif
Chúng ta nên làm cho công thức Sumif chỉ tổng các giá trị trong các dòng hiển thị. Điều đó có nghĩa là nếu D1:D10 lớn hơn 0. Làm sao có thể làm được điều đó?
Thực tế, công thức Sumif chỉ được sử dụng với tiêu chí cột đơn. Đối với nhiều cột, Sumifs có sẵn. Tuy nhiên, hàm sau không trả về một kết quả mảng với hàm ArrayFormula.
Sumifs Array Formula Expanding Issue and Alternative Formulas
Cách giải quyết ở đây là kết hợp cột bổ sung (cột trợ giúp) vào phạm vi Sumif và điều chỉnh phạm vi tiêu chí tương ứng. Đó là những gì tôi đã làm trong công thức Sumif với ArrayFormula trong dữ liệu đã lọc trong Google Sheets.
Cú pháp:
SUMIF(range, criteria, sum_range)Các đối số theo công thức:
range - A2:A10&D2:D10
criteria - E2:E4&1
sum_range - C2:C10Theo ví dụ, các hàng 5, 6 và 7 đã bị loại bỏ. Vì vậy, các giá trị trong A2:A10&D2:D10 (phạm vi Sumif) sẽ như sau:
Bảng #3
| Sumif Range |
|---|
| Item1,1 |
| Item2,0 |
| Item3,1 |
| … |
| Item8,1 |
| Item9,1 |
| Item10,0 |
Tiêu chí E2:E4&1 là:
Bảng #4
| Criteria |
|---|
| Jan1 |
| Mar1 |
Hy vọng rằng điều đó giải thích mọi thứ và bạn có thể hiểu cách sử dụng Sumif với ArrayFormula trong dữ liệu đã lọc trong Google Sheets.
Cảm ơn vì đã đọc, thật vui!
