Trong các hàm như Vlookup, Sumif,… khi bạn chọn các tiêu chí trong nhiều hàng khác nhau, bạn có thể muốn biết làm thế nào để loại bỏ các ô trống dư thừa trong kết quả ArrayFormula. Vì việc sử dụng các tiêu chí trong phạm vi vô hạn có thể gây ra các ô trống thừa ở cuối kết quả công thức của bạn.
Vấn đề này dẫn đến một vấn đề chính. Ngay cả khi các ô trông dưới kết quả, không phải dưới cả hàng, khi bạn nhập giá trị vào bất kỳ trong số những ô đó, công thức sẽ trả về lỗi #REF!.
Nguyên nhân của các ô trống dư thừa trong kết quả ArrayFormula ở cuối
Tôi sẽ giải thích điều này bằng cách sử dụng hàm SUMIF. Điều này cũng áp dụng cho các công thức khác. Dưới đây là công thức SUMIF đang được sử dụng trong ô G2.
=ArrayFormula(if(len(F2:F),(sumif(B2:B,F2:F,C2:C)),))
Tại sao tôi lại sử dụng công thức dưới đây trong SUMIF?
if(len(F2:F)
Hãy xem điều gì xảy ra khi tôi không sử dụng công thức trên trong SUMIF.
Thực sự, không có tiêu chí nào trong F4:F. Nhưng SUMIF xem phạm vi này chứa các tiêu chí trống và tổng hợp cột C theo đúng như vậy.
Làm thế nào để loại bỏ các ô trống trong phạm vi tiêu chí
Để loại bỏ các ô trống trong phạm vi tiêu chí (không phải trong kết quả công thức), bạn có thể sử dụng cả IF+LEN hoặc IF+ISBLANK.
Bạn đã thấy ví dụ IF+LEN trong SUMIF. Dưới đây là ví dụ tương đương với IF+ISBLANK.
=ArrayFormula(if(isblank(F2:F),””,(sumif(B2:B,F2:F,C2:C))))
Nguyên nhân chính của vấn đề mà tôi đã đề cập trong tiêu đề liên quan đến IF+LEN hoặc IF+ISBLANK combo này. Combo này giúp chúng ta hạn chế kết quả công thức một cách trực quan trong các ô chứa tiêu chí. Nhưng không may trả về các ô trống phía dưới kết quả.
Nếu bạn hỏi tôi làm thế nào để loại bỏ các ô trống dư thừa trong kết quả ArrayFormula, câu trả lời của tôi là nó phụ thuộc vào công thức cụ thể.
Dưới đây là một số ví dụ công thức bắt đầu bằng SUMIF trên.
Làm thế nào để loại bỏ các ô trống dư thừa trong kết quả ArrayFormula
Tôi đưa ra một số ví dụ công thức mà bao gồm một số hàm phổ biến.
Loại bỏ các ô trống dư thừa trong SUMIF trong Google Sheet
Tôi sử dụng hàm Filter để lọc các ô trống trong tiêu chí gây ra các số 0 dư thừa và cuối cùng là các ô trống.
=ArrayFormula(sumif(B2:B,filter(F2:F,F2:F<>””),C2:C))
Hãy chắc chắn rằng bạn không để lại bất kỳ ô trống nào giữa phạm vi tiêu chí được chọn.
Loại bỏ các ô trống dư thừa trong Vlookup trong Google Sheets
Vlookup trả về #N/A thay vì các ô trống phía dưới kết quả công thức. Đây là nơi chúng ta thường sử dụng IFERROR để chuyển đổi các lỗi #N/A này thành giá trị trống.
=iferror(ArrayFormula(vlookup(E2:E9,A2:C9,2,FALSE)))
Ở đây, chúng ta cũng có thể sử dụng hàm Filter để loại bỏ các ô trống phía dưới kết quả công thức.
=ArrayFormula(vlookup(FILTER(E2:E,E2:E<>””),A1:C,2,FALSE))
Hàm Filter không hoạt động trong tất cả các tình huống. Dưới đây là một số ví dụ và giải pháp.
Loại bỏ các ô trống dư thừa trong ArrayFormula kết quả hàng dọc
Như bạn có thể biết, hàm ROW có thể điền các số tuần tự trong một cột.
Ví dụ;
=ArrayFormula(row(A1:A10))
Công thức này sẽ trả về số tuần tự từ 1 đến 10 trong một cột.
Bạn có thể kết hợp IF+LEN với công thức này.
Công thức Row này cũng sẽ trả về các ô trống dư thừa từ ô A8 trở đi. Ở đây, chúng ta có thể sử dụng Query hoặc Sortn để loại bỏ các ô trống phía dưới kết quả.
=Query(ArrayFormula(if(len(B2:B),row(B1:B),)),”Select * where Col1 is not null”,0)
Việc sử dụng SORTN để loại bỏ các ô trống dư thừa là phức tạp. Dễ sử dụng nhưng nó sắp xếp kết quả công thức. Vì vậy, nếu bạn không quan tâm đến việc sắp xếp, hãy sử dụng SORTN để loại bỏ các ô trống dư thừa phía dưới kết quả.
Đây là công thức dựa trên SORTN.
=sortn(if(len(B2:B),row(B1:B),),max(if(len(B2:B),row(B1:B),),0))
Sortn này sẽ giới hạn số hàng tối đa đạt đến đầu ra tại 6. Điều đó có nghĩa là sẽ có giá trị trong 6 ô trong đầu ra hàm hàng và đó là mục đích của nó.
Mẹo để loại bỏ hàng dư thừa trong phạm vi dữ liệu chính
Các hàm như SORT, UNIQUE không có tiêu chí. Để loại bỏ các ô hoặc hàng trống phía dưới các kết quả công thức như vậy, chúng ta có thể tuân thủ mẹo dưới đây.
Ở đây, tôi xem xét hàm UNIQUE là ví dụ.
Bước 1: Lọc dữ liệu nguồn để loại bỏ các ô hoặc hàng trống phía dưới.
=filter(A1:A,len(A1:A))
Bước 2: Sử dụng dữ liệu này như dữ liệu nguồn trong hàm Unique.
=unique(filter(A1:A,len(A1:A)))
Kết luận:
Tóm lại, không có một công thức đơn lẻ nào mà chúng ta có thể dựa vào để loại bỏ các ô trống dư thừa trong kết quả của công thức ArrayFormula trong Google Sheets.
Trong một số trường hợp, chúng ta có thể sử dụng Filter, và trong một số trường hợp khác, chúng ta có thể sử dụng Query hoặc SORTN. Quan trọng là, trong tất cả các sự kết hợp, hàm LEN đóng một vai trò quan trọng!
